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