[jira] Issue Comment Edited: (DERBY-3061) Wrong results from query with two conjuncts

["A B (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-3061?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12525805 ] 

army edited comment on DERBY-3061 at 9/7/07 12:14 PM:
-----------------------------------------------------

The incorrect results come from the fact that the the "probe predicate", which is treated as an equality predicate ("MYTABLE.MY_ID = ?") during compilation, is being chosen as the start key but is *not* being chosen as the stop key.  This happens because prior to generating the query plan, we "sort" the useful predicates based on index position.  In the query for this issue both the "probe predicate" and the predicate "MYTABLE.MY_ID < 100" have the same index position--i.e. they are both on the same column.  For the query in question, this causes the less than predicate to come first in the sorted order, which ultimately means "MYTABLE.MY_ID < 100" becomes the stop key instead of the probe predicate.

But if we look at the "compareTo()" method on Predicate.java, which is used for the sort, we see the following comment:

       /* Not all operators are "equal". If the predicates are on the
        * same key column, then a "=" opertor takes precedence over all
        * other operators.  This ensures that the "=" will be both the start
        * and stop predicates.  Otherwise, we could end up with it being one
        * but not the other and get incorrect results.
        */

In this case the probe predicate is an "equal" (MYTABLE.MY_ID = ?) and thus should have precedence over the less-than predicate.  That in turn would guarantee that the probe predicate is the start key *AND* the stop key, which would satisfy the execution-time requirements and fix the query results.

So the problem looks to be related to (directly or indirectly) the "compareTo()" method of Predicate.java.  That method should be giving the probe predicate the same precedence as a normal equality predicate, and it should therefore always put the probe predicate first in the sorted list.  If that happens, the probe predicate will correctly become the start key _and_ stop key, as needed.

More investigation is required to see what might be going wrong with the predicate comparison...

      was (Author: army):
    The incorrect results come from the fact that the the "probe predicate", which is treated as an equality predicate ("MYTABLE.MY_ID = ?") during compilation, is being chosen as the start key but is *not* being chosen as the stop key.  This happens because prior to generating the query plan, we "sort" the useful predicates based on index position.  In the query for this issue both the "probe predicate" and the predicate "MYTABLE.MY_ID < 100" have the same index position--i.e. they are both on the same column.  For the query in question, this causes the less than predicate to come first in the sorted order, which ultimately means "MYTABLE.MY_ID < 100" becomes the stop key instead of the probe predicate.

But if we look at the "compare()" method on Predicate.java, which is used for the sort, we see the following comment:

       /* Not all operators are "equal". If the predicates are on the
        * same key column, then a "=" opertor takes precedence over all
        * other operators.  This ensures that the "=" will be both the start
        * and stop predicates.  Otherwise, we could end up with it being one
        * but not the other and get incorrect results.
        */

In this case the probe predicate is an "equal" (MYTABLE.MY_ID = ?) and thus should have precedence over the less-than predicate.  That in turn would guarantee that the probe predicate is the start key *AND* the stop key, which would satisfy the execution-time requirements and fix the query results.

So the problem looks to be related to (directly or indirectly) the "compare()" method of Predicate.java.  That method should be giving the probe predicate the same precedence as a normal equality predicate, and it should therefore always put the probe predicate first in the sorted list.  If that happens, the probe predicate will correctly become the start key _and_ stop key, as needed.

More investigation is required to see what might be going wrong with the predicate comparison...
  
> 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
>
> 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.