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
