David Butterworth wrote:
For anyone interested, the following select statements demonstrate the inconsistencies
Hi David,
I was able to reproduce the odd results that you experience,
using your database, and Derby 10.4.1.1 (still in beta testing).
I simplified your queries slightly, to:
select count(*) FROM spike.accounts account, spike.admin_units admin_unit,
spike.bookings booking
WHERE booking.child_id = 2 AND
admin_unit.admin_unit_id IN (1,21) AND
booking.booking_date_time_out >= 20080331000000 AND
booking.booking_date_time_in <= 20080406235900 AND
account.account_id = booking.account_id AND
admin_unit.admin_unit_id = account.admin_unit_id
versus the same thing, but with IN clause changed to:
account.admin_unit_id IN (1,21) AND
Interestingly, although the actual results do NOT contain any values
for admin_unit_id = 21, if I change the query to:
admin_unit.admin_unit_id IN (1)
or
account.admin_unit_id IN (1)
then the problem disappears -- I get 3 rows for both queries.
I also ran query plans for both the queries (in the IN (1,21) case)
and have pasted the (simplified) query plans at the end of this message.
I notice that in the case where the query gives 2 rows, which is
when we specify admin_unit.admin_unit_id in (1,21), the admin_unit_id
index scan output in the query plan contains:
qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
However, in the case where the query gives 3 rows, which is
when we specify account.admin_unit_id in (1,21), the admin_unit_id
index scan output in the query plan contains:
qualifiers:
None
I think it is the presence/absence of this qualifier on the query
scan which is causing the different results in the query, as in
the first case we see:
Number of rows qualified=2
Number of rows visited=3
but in the second case we see:
Number of rows qualified=3
Number of rows visited=3
I definitely don't have any explanation for why you are getting
this odd behavior; it certainly seems like a bug to me.
I think you should open an issue in JIRA, and provide as much
information as possible about the queries and how to reproduce the
problem. Ideally, if you can attach your sample database that would
be great, or if you can provide a standalone script or
test program that reproduces the problem from scratch that would
be great too.
Hopefully some other folks will have a chance to look at this, too,
and offer their opinions on what is happening here.
thanks,
bryan
******* Query plan for the query that returns 2 rows:
2008-04-07 16:44:14.401 GMT Thread[main,5,main] (XID = 3585), (SESSIONID = 0), select count(*) FROM spike.accounts account, spike.admin_units admin_unit, spike.bookings booking WHERE booking.child_id
= 2 AND admin_unit.admin_unit_id IN (1,21) AND booking.booking_date_time_out >= 20080331000000 AND booking.booking_date_time_in <= 20080406235900 AND account.account_id = booking.account_id AND
admin_unit.admin_unit_id = account.admin_unit_id ******* Project-Restrict ResultSet (11):
Number of opens = 1
Rows seen = 1
Rows filtered = 0
restriction = false
projection = true
optimizer estimated row count: 1.00
optimizer estimated cost: 42.65
Source result set:
Scalar Aggregate ResultSet:
Number of opens = 1
Rows input = 2
optimizer estimated row count: 0.33
optimizer estimated cost: 42.65
Index Key Optimization = false
Source result set:
Project-Restrict ResultSet (10):
Number of opens = 1
Rows seen = 2
Rows filtered = 0
restriction = false
projection = true
optimizer estimated row count: 0.33
optimizer estimated cost: 42.65
Source result set:
Nested Loop Exists Join ResultSet:
Number of opens = 1
Rows seen from the left = 3
Rows seen from the right = 2
Rows filtered = 0
Rows returned = 2
optimizer estimated row count: 0.33
optimizer estimated cost: 42.65
Left result set:
Nested Loop Exists Join ResultSet:
Number of opens = 1
Rows seen from the left = 3
Rows seen from the right = 3
Rows filtered = 0
Rows returned = 3
optimizer estimated row count: 0.33
optimizer estimated cost: 42.14
Left result set:
Project-Restrict ResultSet (6):
Number of opens = 1
Rows seen = 3
Rows filtered = 0
restriction = true
projection = true
optimizer estimated row count: 0.33
optimizer estimated cost: 40.57
Source result set:
Index Row to Base Row ResultSet for BOOKINGS:
Number of opens = 1
Rows seen = 3
Columns accessed from heap = {1, 2, 5, 6}
optimizer estimated row count: 0.33
optimizer estimated cost: 40.57
Index Scan ResultSet for BOOKINGS using constraint
bookings-children_fk at read committed isolation level using instantaneous
share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 3
Rows filtered = 0
Fetch Size = 16
scan information:
Bit set of columns fetched=All
Number of columns fetched=2
Number of deleted rows visited=1
Number of pages visited=1
Number of rows qualified=3
Number of rows visited=4
Scan type=btree
Tree height=-1
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
0
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
0
qualifiers:
None
optimizer estimated row count: 0.33
optimizer estimated cost: 40.57
Right result set:
Index Row to Base Row ResultSet for ACCOUNTS:
Number of opens = 3
Rows seen = 3
Columns accessed from heap = {11}
optimizer estimated row count: 0.33
optimizer estimated cost: 1.57
Index Scan ResultSet for ACCOUNTS using constraint ACCOUNT_ID_PK at
read committed isolation level using share row locking chosen by the optimizer
Number of opens = 3
Rows seen = 3
Rows filtered = 0
Fetch Size = 1
scan information:
Bit set of columns fetched=All
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=3
Number of rows qualified=3
Number of rows visited=3
Scan type=btree
Tree height=1
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
0
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
0
qualifiers:
None
optimizer estimated row count: 0.33
optimizer estimated cost: 1.57
Right result set:
Index Scan ResultSet for ADMIN_UNITS using constraint ADMIN_UNIT_ID_PK
at read committed isolation level using share row locking chosen by the
optimizer
Number of opens = 3
Rows seen = 2
Rows filtered = 0
Fetch Size = 1
scan information:
Bit set of columns fetched={0}
Number of columns fetched=1
Number of deleted rows visited=0
Number of pages visited=3
Number of rows qualified=2
Number of rows visited=3
Scan type=btree
Tree height=1
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
optimizer estimated row count: 0.33
optimizer estimated cost: 0.51
******* Query plan for the query format that returns 3 rows:
2008-04-07 16:44:18.838 GMT Thread[main,5,main] (XID = 3587), (SESSIONID = 0), select count(*) FROM spike.accounts account, spike.admin_units admin_unit, spike.bookings booking WHERE booking.child_id
= 2 AND account.admin_unit_id IN (1,21) AND booking.booking_date_time_out >= 20080331000000 AND booking.booking_date_time_in <= 20080406235900 AND account.account_id = booking.account_id AND
admin_unit.admin_unit_id = account.admin_unit_id ******* Project-Restrict ResultSet (12):
Number of opens = 1
Rows seen = 1
Rows filtered = 0
restriction = false
projection = true
optimizer estimated row count: 1.00
optimizer estimated cost: 42.65
Source result set:
Scalar Aggregate ResultSet:
Number of opens = 1
Rows input = 3
optimizer estimated row count: 0.33
optimizer estimated cost: 42.65
Index Key Optimization = false
Source result set:
Project-Restrict ResultSet (11):
Number of opens = 1
Rows seen = 3
Rows filtered = 0
restriction = false
projection = true
optimizer estimated row count: 0.33
optimizer estimated cost: 42.65
Source result set:
Nested Loop Exists Join ResultSet:
Number of opens = 1
Rows seen from the left = 3
Rows seen from the right = 3
Rows filtered = 0
Rows returned = 3
optimizer estimated row count: 0.33
optimizer estimated cost: 42.65
Left result set:
Nested Loop Exists Join ResultSet:
Number of opens = 1
Rows seen from the left = 3
Rows seen from the right = 3
Rows filtered = 0
Rows returned = 3
optimizer estimated row count: 0.33
optimizer estimated cost: 42.14
Left result set:
Project-Restrict ResultSet (6):
Number of opens = 1
Rows seen = 3
Rows filtered = 0
restriction = true
projection = true
optimizer estimated row count: 0.33
optimizer estimated cost: 40.57
Source result set:
Index Row to Base Row ResultSet for BOOKINGS:
Number of opens = 1
Rows seen = 3
Columns accessed from heap = {1, 2, 5, 6}
optimizer estimated row count: 0.33
optimizer estimated cost: 40.57
Index Scan ResultSet for BOOKINGS using constraint
bookings-children_fk at read committed isolation level using instantaneous
share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 3
Rows filtered = 0
Fetch Size = 16
scan information:
Bit set of columns fetched=All
Number of columns fetched=2
Number of deleted rows visited=1
Number of pages visited=1
Number of rows qualified=3
Number of rows visited=4
Scan type=btree
Tree height=-1
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
0
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
0
qualifiers:
None
optimizer estimated row count: 0.33
optimizer estimated cost: 40.57
Right result set:
Project-Restrict ResultSet (9):
Number of opens = 3
Rows seen = 3
Rows filtered = 0
restriction = true
projection = true
optimizer estimated row count: 0.33
optimizer estimated cost: 1.57
Source result set:
Index Row to Base Row ResultSet for ACCOUNTS:
Number of opens = 3
Rows seen = 3
Columns accessed from heap = {11}
optimizer estimated row count: 0.33
optimizer estimated cost: 1.57
Index Scan ResultSet for ACCOUNTS using constraint ACCOUNT_ID_PK
at read committed isolation level using share row locking chosen by the
optimizer
Number of opens = 3
Rows seen = 3
Rows filtered = 0
Fetch Size = 1
scan information:
Bit set of columns fetched=All
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=3
Number of rows qualified=3
Number of rows visited=3
Scan type=btree
Tree height=1
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
0
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
0
qualifiers:
None
optimizer estimated row count: 0.33
optimizer estimated cost: 1.57
Right result set:
Index Scan ResultSet for ADMIN_UNITS using constraint ADMIN_UNIT_ID_PK
at read committed isolation level using share row locking chosen by the
optimizer
Number of opens = 3
Rows seen = 3
Rows filtered = 0
Fetch Size = 1
scan information:
Bit set of columns fetched={0}
Number of columns fetched=1
Number of deleted rows visited=0
Number of pages visited=3
Number of rows qualified=3
Number of rows visited=3
Scan type=btree
Tree height=1
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
0
stop position:
> on first 1 column(s).
Ordered null semantics on the following columns:
0
qualifiers:
None
optimizer estimated row count: 0.33
optimizer estimated cost: 0.51
