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 Tue, 11 Sep 2007 16:17:32 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_v2.patch

Just before committing the d3061_v1.patch I realized that we need to add the appropriate "isInListProbePredicate()"
check to the *other* "if" branch in Predicate.compareTo(), as well.  Otherwise the probe predicate
might still be incorrectly sorted.

So I'm attaching d3061_v2.patch which adds the appropriate check, and also adds a few more
test cases to InListMultiProbeTest (one of which shows the need for the second isInListProbePredicate()
check mentioned above).

I then committed the _v2 patch with svn # 574635:

  URL: http://svn.apache.org/viewvc?rev=574635&view=rev

I'll look into porting this back to 10.3 once I can do the merge and run the regression tests
on that branch.

Many thanks to Bryan for feedback and review.  I'm always grateful for an extra pair of eyes!

> 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
>            Assignee: A B
>            Priority: Critical
>         Attachments: d3061_v1.patch, d3061_v1.stat, d3061_v2.patch
>
>
> 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