[IGNITE-19352] Sql. Create table from query result

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.0.0-beta1
• Component/s: sql
• Labels: ignite-3 sql
• Ignite Flags: Docs Required, Release Notes Required
• Assignee: Unassigned
• Reporter: Andrey Khitrin
• Created: 24/Apr/23 11:31
• Updated: 08/May/23 12:02

Description

Like in IGNITE-14555 for AI2, it would be useful to create tables from the result of the query:
 

CREATE TABLE <name> AS <SELECT QUERY>

In the AI3 beta1 and current `main` it's not possible:

sql-cli> create table xx (key_field INT PRIMARY KEY, field1 INT);
Updated 0 rows.
sql-cli> insert into xx values (1, 1);
Updated 1 rows.
sql-cli> insert into xx values (2, 10);
Updated 1 rows.
sql-cli> create table xy as select key_field, field1 from xx where field1 < 5;
SQL query execution error
Exception while executing query [query=create table xy as select key_field, field1 from xx where field1 < 5;]. Error message:IGN-SQL-3 TraceId:ff6bc711-14e5-42ab-b80c-5515a8b77c7c Failed to parse query: Encountered " "AS" "as "" at line 1, column 17.
Was expecting:
    "(" ...

Issue Links

• relates to: IGNITE-14555 Calcite engine. Create table from query result