CTE: WITH RECURSIVE cte_1 as (...), cte_2 as (...), cte_n as (...) Error

[GoogleCodeExporter]

Before submitting a bug, please check the FAQ:
http://www.h2database.com/html/faq.html

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1.
WITH RECURSIVE
  T1(N) AS (
SELECT 0
UNION ALL
SELECT T1.N + 1 FROM T1 WHERE T1.N < 9
)
, T2(N) AS (
SELECT 10
UNION ALL
SELECT T2.N + 1 FROM T2 WHERE T2.N < 19
)
SELECT T1.N FROM T1
UNION ALL
SELECT T2.N FROM T2

---
SQLステートメントに文法エラーがあります "WITH RECURSIVE 
  T1(N) AS ( 
SELECT 0 
UNION ALL 
SELECT T1.N + 1 FROM T1 WHERE T1.N < 9 
) 
,[*] T2(N) AS ( 
SELECT 10 
UNION ALL 
SELECT T2.N + 1 FROM T2 WHERE T2.N < 19 
) 
SELECT T1.N FROM T1 
UNION ALL 
SELECT T2.N FROM T2"; 期待されるステートメント "(, SELECT, FROM"
Syntax error in SQL statement "WITH RECURSIVE 
  T1(N) AS ( 
SELECT 0 
UNION ALL 
SELECT T1.N + 1 FROM T1 WHERE T1.N < 9 
) 
,[*] T2(N) AS ( 
SELECT 10 
UNION ALL 
SELECT T2.N + 1 FROM T2 WHERE T2.N < 19 
) 
SELECT T1.N FROM T1 
UNION ALL 
SELECT T2.N FROM T2"; expected "(, SELECT, FROM"; SQL statement:
WITH RECURSIVE 
  T1(N) AS ( 
SELECT 0 
UNION ALL 
SELECT T1.N + 1 FROM T1 WHERE T1.N < 9 
) 
, T2(N) AS ( 
SELECT 10 
UNION ALL 
SELECT T2.N + 1 FROM T2 WHERE T2.N < 19 
) 
SELECT T1.N FROM T1 
UNION ALL 
SELECT T2.N FROM T2 [42001-140] 42001/42001 (ヘルプ)
org.h2.jdbc.JdbcSQLException: 
SQLステートメントに文法エラーがあります "WITH RECURSIVE 
  T1(N) AS ( 
SELECT 0 
UNION ALL 
SELECT T1.N + 1 FROM T1 WHERE T1.N < 9 
) 
,[*] T2(N) AS ( 
SELECT 10 
UNION ALL 
SELECT T2.N + 1 FROM T2 WHERE T2.N < 19 
) 
SELECT T1.N FROM T1 
UNION ALL 
SELECT T2.N FROM T2"; 期待されるステートメント "(, SELECT, FROM"
Syntax error in SQL statement "WITH RECURSIVE 
  T1(N) AS ( 
SELECT 0 
UNION ALL 
SELECT T1.N + 1 FROM T1 WHERE T1.N < 9 
) 
,[*] T2(N) AS ( 
SELECT 10 
UNION ALL 
SELECT T2.N + 1 FROM T2 WHERE T2.N < 19 
) 
SELECT T1.N FROM T1 
UNION ALL 
SELECT T2.N FROM T2"; expected "(, SELECT, FROM"; SQL statement:
WITH RECURSIVE 
  T1(N) AS ( 
SELECT 0 
UNION ALL 
SELECT T1.N + 1 FROM T1 WHERE T1.N < 9 
) 
, T2(N) AS ( 
SELECT 10 
UNION ALL 
SELECT T2.N + 1 FROM T2 WHERE T2.N < 19 
) 
SELECT T1.N FROM T1 
UNION ALL 
SELECT T2.N FROM T2 [42001-140] 
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:327) 
    at org.h2.message.DbException.get(DbException.java:167) 
    at org.h2.message.DbException.getSyntaxError(DbException.java:192) 
    at org.h2.command.Parser.getSyntaxError(Parser.java:478) 
    at org.h2.command.Parser.parseSelectSimple(Parser.java:1662) 
    at org.h2.command.Parser.parseSelectSub(Parser.java:1571) 
    at org.h2.command.Parser.parseSelectUnion(Parser.java:1416) 
    at org.h2.command.Parser.parseSelect(Parser.java:1404) 
    at org.h2.command.Parser.parserWith(Parser.java:4022) 
    at org.h2.command.Parser.parsePrepared(Parser.java:426) 
    at org.h2.command.Parser.parse(Parser.java:275) 
    at org.h2.command.Parser.parse(Parser.java:251) 
    at org.h2.command.Parser.prepare(Parser.java:201) 
    at org.h2.command.Parser.prepareCommand(Parser.java:214) 
    at org.h2.engine.Session.prepareLocal(Session.java:434) 
    at org.h2.engine.Session.prepareCommand(Session.java:384) 
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1071) 
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:163) 
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:151) 
    at org.h2.server.web.WebApp.getResult(WebApp.java:1310) 
    at org.h2.server.web.WebApp.query(WebApp.java:1003) 
    at org.h2.server.web.WebApp$1.next(WebApp.java:966) 
    at org.h2.server.web.WebApp$1.next(WebApp.java:955) 
    at org.h2.server.web.WebThread.process(WebThread.java:161) 
    at org.h2.server.web.WebThread.run(WebThread.java:88) 
    at java.lang.Thread.run(Thread.java:619) 

What is the expected output? What do you see instead?

What version of the product are you using? On what operating system, file
system, and virtual machine?

H2: Head(r2846)
OS: Windows XP SP3
Java: 1.6.0_20

Do you know a workaround?

No

How important/urgent is the problem for you?

Not so important/urgent

In your view, is this a defect or a feature request?

Defect

Please provide any additional information below.

---h2w.bat---
@start %JAVA_HOME%/bin/javaw.exe -server -Xms512m -Xmx512m 
-Dh2.largeTransactions=true -cp "h2-1.2.140.jar;%H2DRIVERS%;%CLASSPATH%" 
org.h2.tools.Console %*
---JDBC URL---
jdbc:h2:test

Original issue reported on code.google.com by byouinza...@gmail.com on 11 Aug 2010 at 4:56

[GoogleCodeExporter]

Hi,

Thanks for reporting this issue. I have added it to the roadmap. It will be 
implemented eventually, but currently the priority is relatively low.

Original comment by thomas.t...@gmail.com on 15 Aug 2010 at 8:39

[GoogleCodeExporter]

I have added the feature to the roadmap.

Original comment by thomas.t...@gmail.com on 15 Aug 2010 at 8:45

• Changed title: CTE: WITH RECURSIVE cte_1 as (...), cte_2 as (...), cte_n as (...) Error
• Changed state: InRoadmap