index sorting - ascending and descending?

[phildev]

Hi -
I've found that the H2 database can use an index to sort query
results, if the index column (or columns) are used properly in the
query's filter conditions. However, it appears that specifying the
reverse order in the query (asc or desc) causes H2 to not use the
index for the sort, even though logically it could.

For a simple example:

create table address (street varchar, state varchar, zip int);
create index address_zip on address(zip asc) ;

explain select * from address where zip>0 order by zip;
=> sorted, as expected.

explain select * from address where zip>0 order by zip desc
=> not sorted, but could be sorted using the index in reverse.

Is this the expected behavior of H2, or is there something that could
be done to allow H2 to use the index for sorting in this case?

thanks!
-phil

[Thomas Mueller]

Hi,

> specifying the
> reverse order in the query (asc or desc) causes H2 to not use the
> index for the sort, even though logically it could.

Yes, this is a limitation of H2. Currently b-tree indexes can only
scan forward. However you could create a descending index:

drop table address;
create table address(street varchar, state varchar, zip int);
create index address_zip_desc on address(zip desc) ;
explain select * from address where zip>0 order by zip desc;
--> FROM PUBLIC.ADDRESS /* PUBLIC.ADDRESS_ZIP_DESC: ZIP > 0 */
--> ORDER BY 3 DESC /* index sorted */

Unfortunately, if you create two indexes on the same column (one
ascending and one descending), then the wrong index is used in this
example (address_zip instead of address_zip_desc). I will add a
feature request for this "Allow to scan index backwards starting with
a value (to better support ORDER BY DESC)" but I can't tell you when
this will be implemented.

Regards,
Thomas