You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Rick Hillegas (JIRA)" <ji...@apache.org> on 2007/09/07 15:30:33 UTC
[jira] Created: (DERBY-3061) Wrong results from query with two
conjuncts
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
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.
[jira] Updated: (DERBY-3061) Wrong results from query with two
conjuncts
Posted by "A B (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Assigned: (DERBY-3061) Wrong results from query with two
conjuncts
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3061?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
A B reassigned DERBY-3061:
--------------------------
Assignee: A B
> 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
>
>
> 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.
[jira] Commented: (DERBY-3061) Wrong results from query with two
conjuncts
Posted by "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 ]
A B commented on DERBY-3061:
----------------------------
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.
[jira] Commented: (DERBY-3061) Wrong results from query with two
conjuncts
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3061?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12539801 ]
Rick Hillegas commented on DERBY-3061:
--------------------------------------
Hi Tim,
I see that you posted your question to derby-dev already. Please ignore
my previous post.
Regars,
-Rick
> 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
> Fix For: 10.3.1.5, 10.4.0.0
>
> 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.
[jira] Updated: (DERBY-3061) Wrong results from query with two
conjuncts
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3061?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden updated DERBY-3061:
----------------------------------
Derby Info: [Regression]
Verified that this is a regresssion. Running against 10.2.2.1.545663 I see:
ij> select mytable.id
from mytable
where mytable.id < 100
and mytable.id in ( 2, 15, 19, 20, 21, 48, 49 );
ID
-----------
2
15
19
20
21
48
49
7 rows selected
> 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
>
> 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.
[jira] Resolved: (DERBY-3061) Wrong results from query with two
conjuncts
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3061?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
A B resolved DERBY-3061.
------------------------
Resolution: Fixed
Fix Version/s: 10.4.0.0
10.3.1.5
Derby Info: [Regression] (was: [Regression, Patch Available])
The merge to 10.3 was clean and the regression tests ran without error (derbyall and suites.All) on Red Hat Linux:
svn merge -r 574634:574635 https://svn.apache.org/repos/asf/db/derby/code/trunk
So I committed d3061_v2.patch to the 10.3 branch with svn # 574730:
URL: http://svn.apache.org/viewvc?rev=574730&view=rev
I'm marking the issue as RESOLVED but will leave it up to Tim Dudgeon and/or Rick Hillegas to verify and either close or re-open as appropriate.
> 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
> Fix For: 10.3.1.5, 10.4.0.0
>
> 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.
[jira] Closed: (DERBY-3061) Wrong results from query with two
conjuncts
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3061?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas closed DERBY-3061.
--------------------------------
> 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
> Fix For: 10.3.2.1, 10.4.1.3
>
> 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.
[jira] Commented: (DERBY-3061) Wrong results from query with two
conjuncts
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3061?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12539800 ]
Rick Hillegas commented on DERBY-3061:
--------------------------------------
Hi Tim,
I would recommend posting your question to derby-dev. Real user interest
in a bug-fix release may prompt someone to volunteer to produce it. In
the past, bug-fix releases have turned up fairly soon after the IBM team
localizes new error messages introduced by the feature release. It takes
IBM about 2 months to do that work.
Hope this helps,
-Rick
> 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
> Fix For: 10.3.1.5, 10.4.0.0
>
> 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.
[jira] Updated: (DERBY-3061) Wrong results from query with two
conjuncts
Posted by "A B (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Updated: (DERBY-3061) Wrong results from query with two
conjuncts
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3061?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden updated DERBY-3061:
----------------------------------
Priority: Critical (was: Major)
Marking critical since this is wrong results and a regression
> 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.
[jira] Commented: (DERBY-3061) Wrong results from query with two
conjuncts
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3061?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12525970 ]
Bryan Pendleton commented on DERBY-3061:
----------------------------------------
Hi Army, the patch looks good to me. Your explanation makes sense, and is persuasive. The code change seems fine to me. Thanks for adding the comment to the code, as this is a tricky subject, and the comment helps considerably here. I applied the patch and tested, and the new test fails as expected without your code change, and passes as expected with your code change. Thanks for picking up this issue! +1 to commit.
> 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
>
>
> 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.
[jira] Commented: (DERBY-3061) Wrong results from query with two
conjuncts
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3061?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12525757 ]
A B commented on DERBY-3061:
----------------------------
I ran the repro with svn # 541822 ant it fails with an ASSERT failure:
shared.common.sanity.AssertFailure: ASSERT FAILED All multi-probing result sets are expected to have a single key that is both the start key AND the stop key, but that is not the case.
at shared.common.sanity.SanityManager.ASSERT(SanityManager.java(Compiled Code))
at impl.sql.execute.MultiProbeTableScanResultSet.<init>(MultiProbeTableScanResultSet.java:158)
...
But that ASSERT was removed as part of changes for DERBY-2740, hence no failure in 10.3.1.4 nor latest runk. It looks like that fix was intended to allow different start/stop keys to work (hence the assertion was removed), but apparently it does not do the correct thing after all.
> 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.
[jira] Commented: (DERBY-3061) Wrong results from query with two
conjuncts
Posted by "Tim Dudgeon (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3061?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12527172 ]
Tim Dudgeon commented on DERBY-3061:
------------------------------------
Any ideas when this fix will be available in a Derby 10.3.x release?
I need to rollback to the 10.2 releases unless a new 10.3.x release is coming soon.
> 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
> Fix For: 10.3.1.5, 10.4.0.0
>
> 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.
[jira] Updated: (DERBY-3061) Wrong results from query with two
conjuncts
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3061?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
A B updated DERBY-3061:
-----------------------
Derby Info: [Patch Available, Regression] (was: [Regression])
> 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
>
>
> 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.
[jira] Issue Comment Edited: (DERBY-3061) Wrong results from query
with two conjuncts
Posted by "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.