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