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.
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.
