[jira] [Commented] (IGNITE-14188) WITH .. INSERT doesn't work

["Lukas Eder (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/IGNITE-14188?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17285073#comment-17285073 ] 

Lukas Eder commented on IGNITE-14188:
-------------------------------------

A similar error appears with UPDATE. I suspect none of these WITH .. DML statements really work:

 
{code:java}
INSERT INTO t (i, j) VALUES (1, 1);
WITH 
 a(b) AS (SELECT 2)
UPDATE t SET j = (SELECT b FROM a);
{code}
 

> WITH .. INSERT doesn't work
> ---------------------------
>
>                 Key: IGNITE-14188
>                 URL: https://issues.apache.org/jira/browse/IGNITE-14188
>             Project: Ignite
>          Issue Type: Bug
>          Components: sql
>    Affects Versions: 2.9.1
>            Reporter: Lukas Eder
>            Priority: Major
>
> This works perfectly fine on e.g. H2, PostgreSQL, SQL Server:
>  
> {code:java}
> CREATE TABLE t (i int PRIMARY KEY, j int);
> WITH 
>   a(b) AS (SELECT 1)
> INSERT INTO t (i, j)
> SELECT b, b FROM a;
> SELECT * FROM t;
> {code}
> The Ignite documentation claims it should work as well:
> https://ignite.apache.org/docs/latest/sql-reference/dml#with
> Alas, the WITH statement fails with this error:
> {noformat}
> SQL Error [1001] [42000]: Failed to parse query. Column "B" not found; SQL statement:
> SELECT
> B,
> B
> FROM (SELECT
> 1
> FROM SYSTEM_RANGE(1, 1)) A [42122-197]{noformat}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)