Wrong results when ORDER BY clause contains correlated scalar subquery

[Lukas Eder]

Hello,

The following query doesn't work correctly in H2:

select x from (

  select 1 x union 

  select 2   union

  select 3

) t 

order by (select -x) asc

The above should return

+----+

|   X|

+----+

|   3|

|   2|

|   1|

+----+

But instead returns:

+----+

|   X|

+----+

|   1|

|   2|

|   3|

+----+

For the record, this query returns the correct result:

select x from (

  select 1 x union 

  select 2   union

  select 3

) t 

order by -x asc

Cheers,

Lukas 

[Thomas Mueller]

Hi,


For H2, if you run "select x", this is expanded to "select x from dual". Therefore, this "x" is not referring to the outer "x". Could you use:

select x from (
select 1 x union
select 2 union
select 3
) t

order by (select -t.x) asc


Regards,
Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Lukas Eder]

Aha, I see, that's just bad luck - because the DUAL table has a column named X in H2 ;-) This query works fine as well:

select y from (

  select 1 y union 

  select 2   union

  select 3

) t 

order by (select -y) asc

Thanks,

Lukas

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/OiaVKbNwAx0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.