Rownum: Incorrect result when selecting from view

[GoogleCodeExporter]

I'm using H2 as part of an analysis system. I readily admit to not being an SQL 
expert, so what I'm doing might not be the best way of doing things, but the 
results I'm getting seem wrong.

What steps will reproduce the problem? - Attached script sets up a test table 
and view.

What is the expected output? What do you see instead?

Running "select * from runpairs order by new_run" returns multiple rows, 
including:

PREV_RUN  NEW_RUN
32        24
2         25
40        26

However, running "select * from runpairs where new_run=25" returns no rows. I'd 
expect it to return the "2, 25" row.

Interestingly, running "select * from runpairs where new_run > 24 and new_run < 
26" returns a single row with *4* and 25.

"select * from runpairs where (new_run=25)=true" returns the expected row.

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

Windows 7. H2 version 1.3.169 (originally 1.3.168). Java 1.6.0_35-b10 (64 bit).

Do you know a workaround?

 - Use the slightly convoluted "where (x=y)=true" syntax.

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

 - Part of an analysis system generating an e-mail report.

How important/urgent is the problem for you?

 - With the workaround, not too urgent, but makes me wonder whether anything else is being affected by the same problem that I haven't spotted.

Please provide any additional information below.

Original issue reported on code.google.com by pmilli...@gmail.com on 6 Nov 2012 at 4:14

Attachments:

• testdata.sql

[GoogleCodeExporter]

I guess the problem is that no temporary table is created, so that the rownum 
is not 'materialized'. In that case, filtering doesn't work. Example:

select rownum() r from system_range(1, 10);
select rownum() r from system_range(1, 10) where rownum() = 2;

The second query doesn't return any row. See also 
http://h2database.com/html/functions.html#rownum

So I guess it's a bug (the behavior doesn't match the documentation), but I 
will currently not have time to look at the issue.

Could you try to simplify the test case as much as possible, so that the 
problem still occurs? That would help a lot.

Original comment by thomas.t...@gmail.com on 12 Feb 2013 at 7:25

[GoogleCodeExporter]

Original comment by thomas.t...@gmail.com on 19 Oct 2013 at 8:39

• Changed title: Rownum: Incorrect result when selecting from view