[
https://issues.apache.org/jira/browse/DERBY-3603?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12588404#action_12588404
]
Bryan Pendleton commented on DERBY-3603:
----------------------------------------
Thanks Thomas and Army for all the analysis and explanation; it is *very*
helpful!
If I am understanding it all correctly, then I believe this script reproduces
the problem.
Without Army's patch the first 2 queries return (a) count=1, and (b) 1 row, but
*with*
Army's patch all three queries return a result of 2 rows (count = 2), which I
believe
is the correct result.
I'll see if I can turn Army's patch and this repro script into a complete patch
and run
the tests, etc.
drop table a;
drop table c;
create table a (a_id integer, c_id integer);
create table c (c_id integer not null, primary key(c_id),
d_id integer, t_o bigint, t_i bigint);
insert into a (a_id, c_id) values (1, 1);
insert into a (a_id, c_id) values (2, 2);
insert into a (a_id, c_id) values (3, 1);
insert into c (c_id, d_id, t_o, t_i) values (1, 1, 1, 1);
insert into c (c_id, d_id, t_o, t_i) values (2, 2, 1, 1);
insert into c (c_id, d_id, t_o, t_i) values (21, 1, 1, 1);
select count(*) from a, c where a.a_id <> 2 and c.c_id in (1, 21)
and a.c_id = c.c_id;
select a.a_id from a, c where a.a_id <> 2 and c.c_id in (1, 21)
and a.c_id = c.c_id;
select a.a_id,c.d_id from a, c where a.a_id <> 2 and c.c_id in (1, 21)
and a.c_id = c.c_id;
Results without the patch:
ij> select count(*) from a, c where a.a_id <> 2 and c.c_id in (1, 21)
and a.c_id = c.c_id;
1
-----------
1
1 row selected
ij> select a.a_id from a, c where a.a_id <> 2 and c.c_id in (1, 21)
and a.c_id = c.c_id;
A_ID
-----------
1
1 row selected
ij> select a.a_id,c.d_id from a, c where a.a_id <> 2 and c.c_id in (1, 21)
and a.c_id = c.c_id;
A_ID |D_ID
-----------------------
1 |1
3 |1
Results with the patch:
ij> select count(*) from a, c where a.a_id <> 2 and c.c_id in (1, 21)
and a.c_id = c.c_id;
1
-----------
2
1 row selected
ij> select a.a_id from a, c where a.a_id <> 2 and c.c_id in (1, 21)
and a.c_id = c.c_id;
A_ID
-----------
1
3
2 rows selected
ij> select a.a_id,c.d_id from a, c where a.a_id <> 2 and c.c_id in (1, 21)
and a.c_id = c.c_id;
A_ID |D_ID
-----------------------
1 |1
3 |1
> 'IN' clause ignores valid results, incorrect qualifier handling suspected
> -------------------------------------------------------------------------
>
> Key: DERBY-3603
> URL: https://issues.apache.org/jira/browse/DERBY-3603
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.3.2.1, 10.4.1.1
> Reporter: David Butterworth
> Assignee: Bryan Pendleton
> Attachments: d3603_v1.patch, derbydb.jar, derbydb.tar.bz2
>
>
> Derbys' 'IN' clause is returning different results depending on which side of
> a joined table
> I am doing my 'IN' comparison against. This only occurs when the number of
> items within the 'IN' clause is greater then 1.
> This behaviour was also confirmed by Bryan Pendleton in this thread:
> http://mail-archives.apache.org/mod_mbox/db-derby-user/200804.mbox/[EMAIL
> PROTECTED]
> Using the test database attatched the following 2 queries produce the issue:
> ij> 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;
> 1
> -----------
> 2
> 1 row selected
> ij> 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;
> 1
> -----------
> 3
> 1 row selected
> ij>
> The only difference between the 2 statements is which side of a join the 'IN'
> clause is matched against.
> Bryan performed some initial testing and stated the following:
> --------------------- SNIP ------------------------
> 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.
> -------------END SNIP -----------------------
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
