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 ?
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/93b403b6-15fb-45f2-833a-ff571a886055%40googlegroups.com.
