CTE: Recursive query in subquery

[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.create table t1(id int)
2.insert into t1(id) select n from (with recursive t(n) as (select 1 union all 
select n + 1 from t where n < 100) select n from t) tn

---
insert into t1(id) select n from (with recursive t(n) as (select 1 union all 
select n + 1 from t where n < 100) select n from t) tn;
テーブル "T1" が見つかりません
Table "T1" not found; SQL statement:
insert into t1(id) select n from (with recursive t(n) as (select 1 union all 
select n + 1 from t where n < 100) select n from t) tn [42102-140] 42S02/42102 
(ヘルプ)
org.h2.jdbc.JdbcSQLException: テーブル "T1" が見つかりません
Table "T1" not found; SQL statement:
insert into t1(id) select n from (with recursive t(n) as (select 1 union all 
select n + 1 from t where n < 100) select n from t) tn [42102-140] 
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:327) 
    at org.h2.message.DbException.get(DbException.java:167) 
    at org.h2.message.DbException.get(DbException.java:144) 
    at org.h2.command.Parser.readTableOrView(Parser.java:4498) 
    at org.h2.command.Parser.readTableOrView(Parser.java:4478) 
    at org.h2.command.Parser.parseInsert(Parser.java:910) 
    at org.h2.command.Parser.parsePrepared(Parser.java:362) 
    at org.h2.command.Parser.parse(Parser.java:275) 
    at org.h2.command.Parser.parse(Parser.java:247) 
    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(r2839)
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 urgent

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

feature request

SQL:2003
T122: WITH (excluding RECURSIVE) in subquery
T132: Recursive query in subquery

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 28 Jul 2010 at 10:48

[GoogleCodeExporter]

Simple:
INSERT INTO T1(ID)
WITH RECURSIVE T(N) AS (
  SELECT 1
  UNION ALL
  SELECT N + 1 FROM T WHERE N < 10
)
SELECT N FROM T

Original comment by byouinza...@gmail.com on 2 Aug 2010 at 5:21

[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. For me, 
it is the most urgent from the list of "recursive query" limitations.

Original comment by thomas.t...@gmail.com on 2 Aug 2010 at 12:20

[GoogleCodeExporter]

I have added the feature to the roadmap.

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

• Changed title: CTE: Recursive query in subquery
• Changed state: InRoadmap