db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "A B (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-3061) Wrong results from query with two conjuncts
Date Sat, 08 Sep 2007 19:26:30 GMT

     [ 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.


Mime
View raw message