Problem with SELECT ... ORDER BY no data!

[fed]

Hi,

i have a strange problem with H2 (Version 1.1.110), i have this
table:

create table dipendenti_stampa(
id identity not null primary key,
id_dipendente bigint not null,
nome longvarchar not null,
cognome longvarchar not null,
matricola varchar(10) not null,
badge varchar(10) not null,
profilo_orario varchar(100),
presente tinyint,
timbratura_presenza varchar(100));

if on this table i do
"select * from dipendenti_stampa" i get the data back

but if i do

"select * from dipendenti_stampa order by id" i get NO data from it.


It's very strange.. i have to say that i had a similar problem with h2
when i tried to use "order by" on another table and on an index field
(in this case id too is an index field).

Another important consideration: i tested that if i have less than 59
rows in the table i get this behaviour, with more or equal to 59 all
works ok and i get the data from the query.

Bye thanks for the help.

[Thomas Mueller]

Hi,

Could you send me the content of the table as well, and the index
definitions? Probably the simples way is to run SCRIPT in the H2
Console and then send the result set. You can send it to the group or
to the support address 'db support at h2database dot com'. Or paste it
to http://www.h2database.com/p.html and then send me the link.

Regards,
Thomas

[Alex]

We had the same issue some time ago on the older version of H2
See http://groups.google.com/group/h2-database/browse_thread/thread/ee669f2d2bd211b1/565f05257287c514?lnk=gst&q=a701440#565f05257287c514
The only way we could recover was to recreate the index on the latest
version of H2 (at the time it was 107).
The funny thing was that the same index (exact copy of the index file)
worked just fine on the older version of H2...


On Apr 6, 12:04 am, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

> Hi,
>
> Could you send me the content of the table as well, and the index
> definitions? Probably the simples way is to run SCRIPT in the H2
> Console and then send the result set. You can send it to the group or
> to the support address 'db support at h2database dot com'. Or paste it

> tohttp://www.h2database.com/p.htmland then send me the link.
>
> Regards,
> Thomas

[Thomas Mueller]

Hi,

I found the problem. It's quite serious, and I will create a new
release later this week.

Queries that are ordered by an indexed column returned no rows in
certain cases (if all rows were deleted from the table previously, and
there is a low number of rows in the table, and when not using other
conditions, and when using the default b tree index).

Regards,
Thomas

[Mike Goodwin]

Just writing to add my voice to the chorus. I am fairly sure we have run into the same problem (version 1.1.103). It is quite dangerous for us as the only time we were ordering with no conditions on an indexed column was during backup ...

Anyway, I am glad to hear that it has been well diagnosed,

mike

[Mike Goodwin]

Actually I have a question. Is there a workaround? For example, would using 'where true' as a condition prevent the problem from occurring?

thanks,

mike

[Thomas Mueller]

Hi,

The workaround is to sort twice on the same row. My test case is:

create table test(id int primary key) as select x from system_range(1, 200);
delete from test;
insert into test(id) values(1);
select * from test order by id; -- fails
select * from test order by id, id; -- works
drop table test;

Regards,
Thomas

[Alex]

I now have a database that has the "order by" no rows problem.

I don't think that conditions that are outlined in the fix (Queries

that are ordered by an indexed column returned no rows in certain
cases (if all rows were deleted from the table previously, and there
is a low number of rows in the table, and when not using other

conditions, and when using the default b tree index).) are true for
this database.

Could it be that the same issue can be caused in another scenario.
The database I have shows the problem when opened with H2 1.1.108. The
same database no longer shows the problem when opened with H2 1.1.111.

I can send the database and the query that fails with 1.1.108 if
necessary.
Alex


On Apr 7, 2:06 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

> Hi,
>
> The workaround is to sort twice on the same row. My test case is:
>
> create table test(id int primary key) as select x from system_range(1, 200);
> delete from test;
> insert into test(id) values(1);
> select * from test order by id; -- fails
> select * from test order by id, id; -- works
> drop table test;
>
> Regards,
> Thomas
>

[Thomas Mueller]

Hi,

> I now have a database that has the "order by" no rows problem.

> I don't think that conditions that are outlined are true for

> this database.
> Could it be that the same issue can be caused in another scenario.

Yes, that's possible.

> I can send the database and the query that fails with 1.1.108 if
> necessary.

Could you post the query, and the query plan? To get the query plan, use:

EXPLAIN SELECT ...

See also http://www.h2database.com/html/grammar.html#explain

Regards,
Thomas