Michael - I created issue 549 and I'm testing a possible fix for this restriction:
Issue#549 Removed UNION ALL requirements for CTE <https://github.com/h2database/h2database/pull/550> On Mon, Jun 26, 2017 at 3:56 AM, Michael <[email protected]> wrote: > 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. > -- 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.
