Strange parsing of common table expression when combined with delta change tables

[lj.ekman]

Hi

I was testing some common table expressions on h2 and got a parsing error message which to my mind does not look correct.

I attempted to reduce the query to a more minimalistic query and got up with this which reproduced the exception,

CREATE TABLE T1(T1_ID integer auto_increment primary key, A integer, B integer);

WITH X as (

    select 1 from final table (

        insert into t1 (a,b) SELECT 1, R.X FROM SYSTEM_RANGE(1,1000) R

    )

) select count(*) from X

This query produces the following stack trace using the code from the master branch (7c2ac84fd53)

Exception in thread "main" org.h2.jdbc.JdbcSQLSyntaxErrorException: View "(

    SELECT

        1

    FROM FINAL TABLE (null)

        /* table scan */

)" is invalid: "Syntax error in SQL statement ""SELECT

    1

FROM FINAL TABLE (NULL[*])

                    ""; expected ""INSERT, UPDATE, MERGE"" [42001-200]"; SQL statement:

WITH X as (    select 1 from final table (      insert into t1 (a,b) SELECT 1, R.X FROM SYSTEM_RANGE(1,1000) R    ) ) select count(*) from X [90109-200]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:577)

at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)

at org.h2.message.DbException.get(DbException.java:194)

at org.h2.table.TableView.getScanIndex(TableView.java:465)

at org.h2.table.TableFilter.getBestPlanItem(TableFilter.java:232)

at org.h2.table.Plan.calculateCost(Plan.java:116)

at org.h2.command.dml.Optimizer.testPlan(Optimizer.java:181)

at org.h2.command.dml.Optimizer.calculateBestPlan(Optimizer.java:82)

at org.h2.command.dml.Optimizer.optimize(Optimizer.java:240)

at org.h2.command.dml.Select.preparePlan(Select.java:1357)

at org.h2.command.dml.Select.prepare(Select.java:1219)

at org.h2.command.Parser.prepareCommand(Parser.java:758)

at org.h2.engine.Session.prepareLocal(Session.java:657)

at org.h2.engine.Session.prepareCommand(Session.java:595)

at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1235)

at org.h2.jdbc.JdbcStatement.executeQuery(JdbcStatement.java:78)

If extracting the query from the CTE i.e.

select 1 from final table (

        insert into t1 (a,b) SELECT 1, R.X FROM SYSTEM_RANGE(1,1000) R

    )

This part parses and returns the expected result.

Also when attempting to debug and see where the parsing looses track of the delta change table I also tried using a delete statement in place of the insert the query passes and returns the count of deleted rows:

WITH X as (

	select 1 from old table (  delete from t1 where a < 10 )
) select count(*) from X ;

Are there any ideas on why the "select from final table ( insert ... )" could not be used within the common table expression ?

[Evgenij Ryazanov]

Hello.

CTEs are experimental in H2 and have known issues, you can find some on GitHub.

Support of data change delta tables is a relatively new functionality in H2 and I think nobody tested them in combination with CTE earlier.

[Evgenij Ryazanov]

The issue with your query was fixed.

[lj.ekman]

Thank you for very fast response and for extending the functionality for common table expressions and data delta change tables.

I have tested it with more complex statements and it looks good.