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 <[email protected]> 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 [email protected] > <javascript:_e(%7B%7D,'cvml','h2-database%[email protected]');> > . > To post to this group, send email to [email protected] > <javascript:_e(%7B%7D,'cvml','[email protected]');>. > 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
