union clause + order by = NullPointerException

[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 a (id int) 
2. select * from a union select * from a ORDER BY a.id 

 more details on: http://groups.google.com/group/h2-database/browse_thread/thread/80604da9b6a5a1b0#

What is the expected output? What do you see instead?
I expected a ResultSet of rows, but throws NullPointerException

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

H2 1.3.164 on any OS and JVM

Do you know a workaround?

What is your use case, meaning why do you need this feature?

How important/urgent is the problem for you?

Please provide any additional information below.

Original issue reported on code.google.com by jose.ill...@gmail.com on 13 Mar 2012 at 2:08

[GoogleCodeExporter]

Should be fixed in H2 version 1.3.165.

Original comment by thomas.t...@gmail.com on 19 Mar 2012 at 7:56

• Changed state: Accepted

[GoogleCodeExporter]

Original comment by thomas.t...@gmail.com on 19 Mar 2012 at 7:56

• Changed state: Fixed

[GoogleCodeExporter]

I don't known if this is a "new bug" (previous versions never throwed): use an 
alias on union-selects and column name on order-by

  1. create table a (id int) 
  2. select a.id as identifier from a union select a.id as identifier from a ORDER BY a.id 

Throws Error: org.h2.jdbc.JdbcSQLException: Order by expression "a.id" must be 
in the result list in this case; SQL statement:

Original comment by jose.ill...@gmail.com on 20 Mar 2012 at 3:37

[GoogleCodeExporter]

Yes, H2 throws an exception, same as Apache Derby, HSQLDB, PostgreSQL, MySQL.

For questions, please use StackOverflow, not the issue tracker, because very 
few people look at the issue tracker. A lot more people use StackOverflow or 
the Google Group, and you will get a more detailed reply.

Original comment by thomas.t...@gmail.com on 21 Mar 2012 at 6:02