db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bryan Pendleton <bpendle...@amberpoint.com>
Subject Re: Possible bug with IN clause using Derby-10.3.2.1
Date Mon, 07 Apr 2008 17:27:16 GMT
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









Mime
View raw message