VALUES Lists 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.
VALUES (1,'aaa'),(2,'bbb')

---
SQLステートメントに文法エラーがあります "VALUES 
(1,'aaa'),[*](2,'bbb')"
Syntax error in SQL statement "VALUES (1,'aaa'),[*](2,'bbb')"; SQL statement:
VALUES (1,'aaa'),(2,'bbb') [42000-140] 42000/42000 (ヘルプ)
org.h2.jdbc.JdbcSQLException: 
SQLステートメントに文法エラーがあります "VALUES 
(1,'aaa'),[*](2,'bbb')"
Syntax error in SQL statement "VALUES (1,'aaa'),[*](2,'bbb')"; SQL statement:
VALUES (1,'aaa'),(2,'bbb') [42000-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.message.DbException.getSyntaxError(DbException.java:179) 
    at org.h2.command.Parser.getSyntaxError(Parser.java:471) 
    at org.h2.command.Parser.prepareCommand(Parser.java:229) 
    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?

Output

C1 C2
-- ---
1  'aaa'
2  'bbb'

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?

Yes

select 1, 'aaa'
union all
select 2, 'bbb'

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.

PostgreSQL VALUES Lists 
(http://www.postgresql.org/docs/8.4/static/queries-values.html)

The syntax is
VALUES ( expression [, ...] ) [, ...]

---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 5:08

[GoogleCodeExporter]

For me, the definition "Defect" means the feature is documented within the H2 
documentation, however this feature isn't documented. Why do you think it 
should be supported? I can add it to the roadmap.

Original comment by thomas.t...@gmail.com on 14 Aug 2010 at 5:09

[GoogleCodeExporter]

Hi,

It is already in the roadmap, I will increase the priority:
"Compatibility with Derby and PostgreSQL: VALUES(1), (2); SELECT * FROM (VALUES 
(1), (2)) AS myTable(c1). Issue 221."

However please tell me why exactly you need this feature.

Original comment by thomas.t...@gmail.com on 14 Aug 2010 at 5:11

[GoogleCodeExporter]

Hi,

I am sorry I do not speak English. But I can use "Google Translate".

At the beginning.

"WITH Queries(CTE)" was a test.
To do so, "PostgreSQL document" was a reference.

http://wiki.postgresql.org/wiki/CTEReadme
http://www.postgresql.org/docs/8.4/static/queries-with.html

And, "parser.java" was reading the source code.
I found that "VALUES Lists" feature.
Sorry, we did not examine the document.

> However please tell me why exactly you need this feature.
Useful for porting from PostgreSQL.

However, SQL is easy to rewrite.
It is difficult to answer. ;)

Regards,
Kuroneko

Original comment by byouinza...@gmail.com on 15 Aug 2010 at 3:25

[GoogleCodeExporter]

This feature is already in the roadmap.

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

• Changed state: InRoadmap