[11.5.362 Pro] - Info on table

[11.5.362 Pro] - Info on table

Having an existing table in a query in the editor SA retrieves the table information correctly and allows inserting parts of it into the editor from the popups:

When having a CREATE TABLE xyz AS SELECT... statement in the editor, there are several problems with it:

#1 Regardless of SA knowing there's (or at least will be) a table named t_service_journey_call, and it offers it in the popup (though it shows it as a local variable),

it does not insert its columns as it would when selecting a table from the popup, it simply inserts its name. So I have to write SELECT col1, col2, etc FROM `t_service_journey_call` as tsjc myself, and after that SA does recognize it as a table when adding new parts to the query. Which leads to...

#2 SA tries to guess the future structure of that table. Alas, that guess is not a very good one. The connection SE uses in the screenshot is made to a MariaDB database, and the type varchar(max) is not a valid type there.
#3 Furthermore, the columns SA guesses the t_service_journey_call table will have are not the actual ones that are used to construct that table but all session variables that are set at the top of the editor contents instead.

#4 and those will be the ones further popups will offer as columns from the table t_service_journey_call

I think the answer to your questions / observations is quite simple, when it comes to new table declarations in scripts the Intellisense doesn't recognize yet columns in a table defined using CREATE TABLE ... AS SELECT... It picks up the object name, but doesn't get further than that. As in 11.5, it does recognize CREATE TABLE ... (...) and also DECLARE @table_var TABLE(....).

I checked few other things, and I think it's worth noting that with SQL Server environments it also recognizes SELECT ... INTO ... FROM ..

I see. That means the whole shebang will start work properly as soon as I start defining the new tables explicitly instead of shortcutting the process by using the CREATE TABLE ... AS SELECT ... statement, right? Do you plan to implement recognition of that statement?

Yes, there are planned enhancements for the next major version 12 to enhance intellisense for many types of DDL statements, including CTAS (a.k.a CREATE TABLE AS subquery).

That's great! Do you have a rough ETA for v12 perhaps? Can you share a list of planned changes?

I think the ETA is 5 to 6 months. There are a lot of things still being evaluated, big appetite for adding new enhancements, improvements, and new major features to the next major version code branch. The roadmap for 12.x refers to significant intellisense enhancements for all kinds of DDL operations, expanding Data Factory to a full blown ETL framework, full multi-threading for all tools and internally executed queries to enable more things to run concurrently with less waits, big internal changes in the database catalog caching to resolve current shortcomings, including static caching to files to deal with monstrous and notoriously slow Oracle data dictionaries, SQL profilers for Oracle and DB2, and so on... What would actually make to the initial 12.0 release is not yet known, it's pretty dynamic at this point. The work on 12.0 has already started.