[h2] org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "recursive queries without UNION ALL"; expected {1}; SQL statement:

[Michael]

check out this example (reproduced on H2 Console v 1.4.196):

DROP TABLE IF EXISTS population;
CREATE TABLE population(
       ID INT PRIMARY KEY,
    PARENT_ID INT
);

INSERT INTO population VALUES(1, null);
INSERT INTO population VALUES(2, 1);
INSERT INTO population VALUES(3, 1);
INSERT INTO population VALUES(4, 2);

WITH RECURSIVE ancestors(PARENT_ID) AS (
       SELECT p.PARENT_ID
     FROM population p
      WHERE p.id = 4
UNION --ALL
      SELECT p.PARENT_ID
     FROM population p
              JOIN ancestors a ON a.PARENT_ID = p.ID
)
SELECT a.PARENT_ID
FROM ancestors a;



expected result:

PARENT_ID
2
1
null
(3 rows, 1 ms)



note how the query works with `UNION ALL`, yet fails if I use `UNION`:

Syntax error in SQL statement "recursive queries without UNION ALL"; 
expected {1}; SQL statement:
WITH RECURSIVE ancestors(PARENT_ID) AS (
   SELECT p.PARENT_ID
   FROM population p
   WHERE p.id = 4
UNION --ALL
   SELECT p.PARENT_ID
   FROM population p
       JOIN ancestors a ON a.PARENT_ID = p.ID
)
SELECT a.PARENT_ID
FROM ancestors a [42001-196] 42001/42001 (Hilfe)
org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "recursive 
queries without UNION ALL"; expected {1}; SQL statement:
WITH RECURSIVE ancestors(PARENT_ID) AS (
   SELECT p.PARENT_ID
   FROM population p
   WHERE p.id = 4
UNION --ALL
   SELECT p.PARENT_ID
   FROM population p
       JOIN ancestors a ON a.PARENT_ID = p.ID
)
SELECT a.PARENT_ID
FROM ancestors a [42001-196]
   at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
   at org.h2.message.DbException.get(DbException.java:179)
   at org.h2.message.DbException.get(DbException.java:155)
   at org.h2.index.ViewIndex.findRecursive(ViewIndex.java:203)
   at org.h2.index.ViewIndex.find(ViewIndex.java:288)
   at org.h2.index.ViewIndex.find(ViewIndex.java:161)
   at org.h2.index.BaseIndex.find(BaseIndex.java:128)
   at org.h2.index.IndexCursor.find(IndexCursor.java:169)
   at org.h2.table.TableFilter.next(TableFilter.java:468)
   at org.h2.command.dml.Select$LazyResultQueryFlat.fetchNextRow(Select.java
:1452)
   at org.h2.result.LazyResult.hasNext(LazyResult.java:79)
   at org.h2.result.LazyResult.next(LazyResult.java:59)
   at org.h2.command.dml.Select.queryFlat(Select.java:519)
   at org.h2.command.dml.Select.queryWithoutCache(Select.java:625)
   at org.h2.command.dml.Query.queryWithoutCacheLazyCheck(Query.java:114)
   at org.h2.command.dml.Query.query(Query.java:371)
   at org.h2.command.dml.Query.query(Query.java:333)
   at org.h2.command.CommandContainer.query(CommandContainer.java:113)
   at org.h2.command.Command.executeQuery(Command.java:201)
   at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:186)
   at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:164)
   at org.h2.server.web.WebApp.getResult(WebApp.java:1380)
   at org.h2.server.web.WebApp.query(WebApp.java:1053)
   at org.h2.server.web.WebApp$1.next(WebApp.java:1015)
   at org.h2.server.web.WebApp$1.next(WebApp.java:1002)
   at org.h2.server.web.WebThread.process(WebThread.java:164)
   at org.h2.server.web.WebThread.run(WebThread.java:89)
   at java.lang.Thread.run(Thread.java:745)



why this is important: setup with this data instead (you'll have a never 
returning query due to a loop in the query execution, unless you use simple 
`UNION`)

INSERT INTO population VALUES(1, 2);
INSERT INTO population VALUES(2, 1);

-- 
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 h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.