Hi,
Yes, I can reproduce this problem. Thanks a lot for reporting! It happens
because of the (partially) descending index. It will be fixed in the next
release. A workaround is to use version 1.3.x, or (with 1.4.x), don't use
descending indexes.
Regards,
Thomas
On Monday, September 8, 2014, Damir Sultanbekov <damir.sultanbe...@gmail.com>
wrote:
> Small addition, I use h2 in Postgre mode, datasource is created as follows:
>
> <bean id="dataSource"
> class="org.springframework.jdbc.datasource.SimpleDriverDataSource">
> <property name="driverClass" value="org.h2.Driver"/>
> <property name="url"
> value="jdbc:h2:mem:test;MODE=PostgreSQL;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE"/>
> <property name="username" value="sa"/>
> <property name="password" value=""/>
> </bean>
>
> понедельник, 8 сентября 2014 г., 10:56:45 UTC+6 пользователь Damir
> Sultanbekov написал:
>>
>> Hello.
>>
>> Recently I spend about 3 hours trying to debug very simple query.
>> I use spring-jdbc whith junit and h2 v1.4.181 (latest from Maven Central).
>>
>> First I initialize db with following scripts:
>>
>> -- DDL
>> create table leaderboard (
>> player_id integer not null,
>> type integer not null,
>> distance integer not null,
>> constraint pk_leaderboard primary key (player_id, type),
>> constraint chk_leaderboard_type check (type in (1, 2, 3))
>> );
>>
>> create index i_leaderboard_type_distance on leaderboard(type, distance
>> desc);
>>
>> -- Data
>> insert into leaderboard (player_id, type, distance) values (1, 1, 101);
>> insert into leaderboard (player_id, type, distance) values (1, 2, 201);
>> insert into leaderboard (player_id, type, distance) values (2, 1, 102);
>>
>>
>> Then I invoke following code from my unit-test:
>>
>> List list = jdbcTemplate.queryForList("select * from leaderboard");
>> System.out.println(" NONE: " + list);
>> list = jdbcTemplate.queryForList("select * from leaderboard where type =
>> 1");
>> System.out.println(" 1: " + list);
>>
>> And output is very strange:
>> NONE: [{PLAYER_ID=1, TYPE=1, DISTANCE=101}, {PLAYER_ID=1, TYPE=2,
>> DISTANCE=201}, {PLAYER_ID=2, TYPE=1, DISTANCE=102}]
>> 1: []
>>
>> First query shows all three rows as expected, but second shows empty
>> result (2 rows expected).
>>
>> It tooks me about 3 hours to find out that index is the culprit. If I
>> remove 'create index' from DDL everything starts working:
>>
>> NONE: [{PLAYER_ID=1, TYPE=1, DISTANCE=101}, {PLAYER_ID=1, TYPE=2,
>> DISTANCE=201}, {PLAYER_ID=2, TYPE=1, DISTANCE=102}]
>> 1: [{PLAYER_ID=1, TYPE=1, DISTANCE=101}, {PLAYER_ID=2, TYPE=1,
>> DISTANCE=102}]
>>
>> It looks like bug for me. Is it true?
>>
> --
> 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+unsubscr...@googlegroups.com
> <javascript:_e(%7B%7D,'cvml','h2-database%2bunsubscr...@googlegroups.com');>
> .
> To post to this group, send email to h2-database@googlegroups.com
> <javascript:_e(%7B%7D,'cvml','h2-database@googlegroups.com');>.
> Visit this group at http://groups.google.com/group/h2-database.
> For more options, visit https://groups.google.com/d/optout.
>
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
