db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From David Butterworth <da...@economicoutlook.net>
Subject Possible bug with IN clause using Derby-10.3.2.1
Date Sat, 05 Apr 2008 11:10:59 GMT
I'm experiencing really bizarre behaviour with derby.
Could anyone explain to me why these 2 queries return different
results ?

The only difference between the queries is which side of a joined table
I am doing my 'IN' comparison against.

Not only that but if I reduce the number of items the 'IN' comparator
uses to two, then the query works as expected !


1) EXPECTED RESULT
------------------
ij> SELECT account.admin_unit_id, booking.booking_date_time_in, booking.booking_date_time_out,
booking.child_id FROM spike.accounts account, spike.admin_units admin_unit, spike.bookings
booking WHERE booking.child_id = 1300 AND account.admin_unit_id IN (1, 2, 3, 6) AND booking.booking_date_time_out
>= 20080414000000 AND booking.booking_date_time_in <= 20080420235900 AND account.account_id
= booking.account_id AND admin_unit.admin_unit_id = account.admin_unit_id ORDER BY booking.booking_date_time_in
ASC;
ADMIN_UNIT&|BOOKING_DATE_TIME_IN|BOOKING_DATE_TIME_O&|CHILD_ID   
-----------------------------------------------------------------
1          |20080416062500      |20080416140959      |1300       
1          |20080417024000      |20080417045959      |1300       
1          |20080417110000      |20080417144459      |1300       
1          |20080418102000      |20080418145459      |1300       

4 rows selected

2) UNEXPECTED RESULT
--------------------
ij> SELECT account.admin_unit_id, booking.booking_date_time_in, booking.booking_date_time_out,
booking.child_id FROM spike.accounts account, spike.admin_units admin_unit, spike.bookings
booking WHERE booking.child_id = 1300 AND admin_unit.admin_unit_id IN (1, 2, 3, 6) AND booking.booking_date_time_out
>= 20080414000000 AND booking.booking_date_time_in <= 20080420235900 AND account.account_id
= booking.account_id AND admin_unit.admin_unit_id = account.admin_unit_id ORDER BY booking.booking_date_time_in
ASC;
ADMIN_UNIT&|BOOKING_DATE_TIME_IN|BOOKING_DATE_TIME_O&|CHILD_ID   
-----------------------------------------------------------------
1          |20080416062500      |20080416140959      |1300       
1          |20080418102000      |20080418145459      |1300       

2 rows selected



Regards
David


Mime
View raw message