CTE: WITH clause (non-recursive) 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 t(n) as (
select 1
)
select * from t

---
with t(n) as ( 
select 1 
) 
select * from t;
SQLステートメントに文法エラーがあります "recursive queries 
without UNION ALL"; 期待されるステートメント {1}
Syntax error in SQL statement "recursive queries without UNION ALL"; expected 
{1}; SQL statement:
with t(n) as ( 
select 1 
) 
select * from t [42001-140] 42001/42001 (ヘルプ)
org.h2.jdbc.JdbcSQLException: 
SQLステートメントに文法エラーがあります "recursive queries 
without UNION ALL"; 期待されるステートメント {1}
Syntax error in SQL statement "recursive queries without UNION ALL"; expected 
{1}; SQL statement:
with t(n) as ( 
select 1 
) 
select * from t [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.get(DbException.java:144) 
    at org.h2.index.ViewIndex.find(ViewIndex.java:169) 
    at org.h2.index.IndexCursor.find(IndexCursor.java:136) 
    at org.h2.table.TableFilter.next(TableFilter.java:309) 
    at org.h2.command.dml.Select.queryFlat(Select.java:492) 
    at org.h2.command.dml.Select.queryWithoutCache(Select.java:579) 
    at org.h2.command.dml.Query.query(Query.java:241) 
    at org.h2.command.CommandContainer.query(CommandContainer.java:80) 
    at org.h2.command.Command.executeQuery(Command.java:132) 
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:172) 
    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?

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

[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 2 Aug 2010 at 12:17

[GoogleCodeExporter]

I have added the feature to the roadmap.

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

• Changed title: CTE: WITH clause (non-recursive) Error
• Changed state: InRoadmap

[GoogleCodeExporter]

Just to add to this one, but shouldn't this also work without the "(n)" after 
the t?

with t as (
select 1
)
select * from t

This also fails, but with a different error.

Syntax error in SQL statement "WITH T AS[*] (
SELECT 1
)
SELECT * FROM T "; expected "., ("; SQL statement:
with t as (
select 1
)
select * from t [42001-174] 42001/42001 (Help)

Perhaps this is related, but if not, maybe it can be addressed at the same time?

Original comment by tad...@gmail.com on 11 Nov 2013 at 9:23

[GoogleCodeExporter]

Not sure why this is low priority. I am sure at this point it will never be 
fixed, but a non-recursive CTE is a much more common use that the recursive 
version. How many lost developer hours will it take to bump this up a bit?

Original comment by elde2...@gmail.com on 6 Jan 2015 at 9:45