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 <michael.kuech...@bluewin.ch> 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 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. > -- 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.
