[
https://issues.apache.org/jira/browse/DERBY-3061?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
A B updated DERBY-3061:
-----------------------
Attachment: d3061_v1.stat
d3061_v1.patch
Attaching a patch for this issue. The change here is pretty small: just add
logic to the "if" statement to assign the correct "precedence" to an IN-list
probe predicate when sorting predicates for code generation. This ensures that
the probe predicate will be chosen as the start key _and_ as the stop key,
which gives us correct results. I also added the repro script attached to this
issue to the existing InListMultiProbeTest (thanks for creating the script,
Rick).
I ran derbyall and suites.All with ibm142 on Red Hat Linux. There were two
failures in derbyall with server tests, but they look unrelated to these
changes. suites.All ran cleanly.
Review comments are always appreciated; if I hear no feedback to the contrary,
I'll commit this early next week (probably Tuesday).
> Wrong results from query with two conjuncts
> -------------------------------------------
>
> Key: DERBY-3061
> URL: https://issues.apache.org/jira/browse/DERBY-3061
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.3.1.4
> Reporter: Rick Hillegas
> Priority: Critical
> Attachments: d3061_v1.patch, d3061_v1.stat
>
>
> Tim Dudgeon, on the user list, reports that the following query returns no
> results in 10.3.1.4 but works correctly in 10.2. I have verified that the
> query returns no results in the mainline as well. If you eliminate either of
> the the conjuncts, then the query returns the correct results:
> SELECT MYTABLE.MY_ID
> FROM MYTABLE
> WHERE MYTABLE.MY_ID < 100 AND MYTABLE.MY_ID IN (
> 2,15,19,20,21,48,49
> )
> Here is a more complete script which demonstrates the problem:
> drop table mytable;
> create table mytable ( id int primary key );
> insert into mytable ( id )
> values
> ( 0 ), ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 ), ( 6 ), ( 7 ), ( 8 ), ( 9 );
> insert into mytable select id + 10 from mytable;
> insert into mytable select id + 20 from mytable;
> insert into mytable select id + 40 from mytable;
> insert into mytable select id + 100 from mytable;
> select mytable.id
> from mytable
> where mytable.id < 100;
> select mytable.id
> from mytable
> where mytable.id in ( 2, 15, 19, 20, 21, 48, 49 );
> select mytable.id
> from mytable
> where mytable.id < 100
> and mytable.id in ( 2, 15, 19, 20, 21, 48, 49 );
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
