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