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 "Thomas Nielsen (JIRA)" <ji...@apache.org> on 2008/01/24 12:51:35 UTC
[jira] Created: (DERBY-3349) Nested WHERE EXITS queries need
improved testing
Nested WHERE EXITS queries need improved testing
------------------------------------------------
Key: DERBY-3349
URL: https://issues.apache.org/jira/browse/DERBY-3349
Project: Derby
Issue Type: Improvement
Components: Test
Reporter: Thomas Nielsen
DERBY-3301 reported an error in subquery flattening of nested WHERE EXISTS clauses, or WHERE EXISTS subqueries. The query returned the wrong number of rows.
There is currently some tests for WHERE EXISTS, but none for nested WHERE EXISTS clauses.
See DERBY-3301 for a full sql repro.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3349) Nested WHERE EXISTS queries need
improved testing
Posted by "Dyre Tjeldvoll (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3349?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12569881#action_12569881 ]
Dyre Tjeldvoll commented on DERBY-3349:
---------------------------------------
Merged to 10.3 in revision 628704.
> Nested WHERE EXISTS queries need improved testing
> -------------------------------------------------
>
> Key: DERBY-3349
> URL: https://issues.apache.org/jira/browse/DERBY-3349
> Project: Derby
> Issue Type: Improvement
> Components: Test
> Affects Versions: 10.2.2.0, 10.3.2.1
> Reporter: Thomas Nielsen
> Assignee: Thomas Nielsen
> Fix For: 10.3.2.2, 10.4.0.0
>
> Attachments: d3349-1.diff, d3349-1.stat, d3349-2.diff, d3349-3.diff
>
>
> DERBY-3301 reported an error in subquery flattening of nested WHERE EXISTS clauses, or WHERE EXISTS subqueries. The query returned the wrong number of rows.
> There is currently some tests for WHERE EXISTS, but none for nested WHERE EXISTS clauses.
> See DERBY-3301 for a full sql repro.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Closed: (DERBY-3349) Nested WHERE EXISTS queries need
improved testing
Posted by "Thomas Nielsen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3349?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Thomas Nielsen closed DERBY-3349.
---------------------------------
Closing the issue now that the updated test has been added/merged to trunk (10.4) and 10.3.
Please feel free to reopen if you think there are more queries that needs to be tested.
> Nested WHERE EXISTS queries need improved testing
> -------------------------------------------------
>
> Key: DERBY-3349
> URL: https://issues.apache.org/jira/browse/DERBY-3349
> Project: Derby
> Issue Type: Improvement
> Components: Test
> Affects Versions: 10.2.2.0, 10.3.2.1
> Reporter: Thomas Nielsen
> Assignee: Thomas Nielsen
> Fix For: 10.3.2.2, 10.4.0.0
>
> Attachments: d3349-1.diff, d3349-1.stat, d3349-2.diff, d3349-3.diff
>
>
> DERBY-3301 reported an error in subquery flattening of nested WHERE EXISTS clauses, or WHERE EXISTS subqueries. The query returned the wrong number of rows.
> There is currently some tests for WHERE EXISTS, but none for nested WHERE EXISTS clauses.
> See DERBY-3301 for a full sql repro.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3349) Nested WHERE EXISTS queries need
improved testing
Posted by "Thomas Nielsen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3349?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12563229#action_12563229 ]
Thomas Nielsen commented on DERBY-3349:
---------------------------------------
DERBY-3301 adds a test with two variations over the repro script, but there are other combinations that needs testing:
Army> I think a minimal list of combinations to check should be:
Army > * WHERE EXISTS ... WHERE EXISTS (done with test-1.diff -- thanks!)
Army > * WHERE EXISTS ... WHERE IN (done with test-2.diff -- thanks!)
Army > * WHERE EXISTS .. WHERE ANY
Army > * WHERE IN ... WHERE IN (I posted an example in my previous post, but it's not included in the tests)
Army > * WHERE ANY ... WHERE ANY
> Nested WHERE EXISTS queries need improved testing
> -------------------------------------------------
>
> Key: DERBY-3349
> URL: https://issues.apache.org/jira/browse/DERBY-3349
> Project: Derby
> Issue Type: Improvement
> Components: Test
> Reporter: Thomas Nielsen
>
> DERBY-3301 reported an error in subquery flattening of nested WHERE EXISTS clauses, or WHERE EXISTS subqueries. The query returned the wrong number of rows.
> There is currently some tests for WHERE EXISTS, but none for nested WHERE EXISTS clauses.
> See DERBY-3301 for a full sql repro.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3349) Nested WHERE EXISTS queries need
improved testing
Posted by "Thomas Nielsen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3349?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Thomas Nielsen updated DERBY-3349:
----------------------------------
Attachment: d3349-3.diff
Attaching updated d3349-3.diff that runs the last two combinations
WHERE EXISTS ... WHERE ANY
WHERE ANY ... WHERE ANY
The test was made part of the suite as part of DERBY-3301.
The updated test passes when run against the trunk.
> Nested WHERE EXISTS queries need improved testing
> -------------------------------------------------
>
> Key: DERBY-3349
> URL: https://issues.apache.org/jira/browse/DERBY-3349
> Project: Derby
> Issue Type: Improvement
> Components: Test
> Reporter: Thomas Nielsen
> Attachments: d3349-1.diff, d3349-1.stat, d3349-2.diff, d3349-3.diff
>
>
> DERBY-3301 reported an error in subquery flattening of nested WHERE EXISTS clauses, or WHERE EXISTS subqueries. The query returned the wrong number of rows.
> There is currently some tests for WHERE EXISTS, but none for nested WHERE EXISTS clauses.
> See DERBY-3301 for a full sql repro.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3349) Nested WHERE EXISTS queries need
improved testing
Posted by "Thomas Nielsen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3349?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Thomas Nielsen updated DERBY-3349:
----------------------------------
Summary: Nested WHERE EXISTS queries need improved testing (was: Nested WHERE EXITS queries need improved testing)
> Nested WHERE EXISTS queries need improved testing
> -------------------------------------------------
>
> Key: DERBY-3349
> URL: https://issues.apache.org/jira/browse/DERBY-3349
> Project: Derby
> Issue Type: Improvement
> Components: Test
> Reporter: Thomas Nielsen
>
> DERBY-3301 reported an error in subquery flattening of nested WHERE EXISTS clauses, or WHERE EXISTS subqueries. The query returned the wrong number of rows.
> There is currently some tests for WHERE EXISTS, but none for nested WHERE EXISTS clauses.
> See DERBY-3301 for a full sql repro.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3349) Nested WHERE EXISTS queries need
improved testing
Posted by "Thomas Nielsen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3349?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Thomas Nielsen updated DERBY-3349:
----------------------------------
Attachment: d3349-2.diff
Army had added the WHERE IN ... WHERE IN variant earlier in 3301.
With the 2 patch applied we currently test these combinations:
WHERE EXISTS ... WHERE EXISTS
WHERE EXISTS ... WHERE IN
WHERE EXISTS ... WHERE EXISTS ... WHERE
WHERE EXISTS ... WHERE 1=1 AND EXISTS
WHERE IN ... WHERE IN
Still left to do:
WHERE EXISTS ... WHERE ANY
WHERE ANY ... WHERE ANY
> Nested WHERE EXISTS queries need improved testing
> -------------------------------------------------
>
> Key: DERBY-3349
> URL: https://issues.apache.org/jira/browse/DERBY-3349
> Project: Derby
> Issue Type: Improvement
> Components: Test
> Reporter: Thomas Nielsen
> Attachments: d3349-1.diff, d3349-1.stat, d3349-2.diff
>
>
> DERBY-3301 reported an error in subquery flattening of nested WHERE EXISTS clauses, or WHERE EXISTS subqueries. The query returned the wrong number of rows.
> There is currently some tests for WHERE EXISTS, but none for nested WHERE EXISTS clauses.
> See DERBY-3301 for a full sql repro.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3349) Nested WHERE EXISTS queries need
improved testing
Posted by "Thomas Nielsen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3349?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Thomas Nielsen updated DERBY-3349:
----------------------------------
Derby Info: [Patch Available]
Assignee: Thomas Nielsen
> Nested WHERE EXISTS queries need improved testing
> -------------------------------------------------
>
> Key: DERBY-3349
> URL: https://issues.apache.org/jira/browse/DERBY-3349
> Project: Derby
> Issue Type: Improvement
> Components: Test
> Reporter: Thomas Nielsen
> Assignee: Thomas Nielsen
> Attachments: d3349-1.diff, d3349-1.stat, d3349-2.diff, d3349-3.diff
>
>
> DERBY-3301 reported an error in subquery flattening of nested WHERE EXISTS clauses, or WHERE EXISTS subqueries. The query returned the wrong number of rows.
> There is currently some tests for WHERE EXISTS, but none for nested WHERE EXISTS clauses.
> See DERBY-3301 for a full sql repro.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3349) Nested WHERE EXISTS queries need
improved testing
Posted by "Dyre Tjeldvoll (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3349?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dyre Tjeldvoll updated DERBY-3349:
----------------------------------
Affects Version/s: 10.2.2.0
10.3.2.1
Fix Version/s: 10.4.0.0
10.3.2.2
Updating fix version in preparation for merge to 10.3
> Nested WHERE EXISTS queries need improved testing
> -------------------------------------------------
>
> Key: DERBY-3349
> URL: https://issues.apache.org/jira/browse/DERBY-3349
> Project: Derby
> Issue Type: Improvement
> Components: Test
> Affects Versions: 10.2.2.0, 10.3.2.1
> Reporter: Thomas Nielsen
> Assignee: Thomas Nielsen
> Fix For: 10.3.2.2, 10.4.0.0
>
> Attachments: d3349-1.diff, d3349-1.stat, d3349-2.diff, d3349-3.diff
>
>
> DERBY-3301 reported an error in subquery flattening of nested WHERE EXISTS clauses, or WHERE EXISTS subqueries. The query returned the wrong number of rows.
> There is currently some tests for WHERE EXISTS, but none for nested WHERE EXISTS clauses.
> See DERBY-3301 for a full sql repro.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3349) Nested WHERE EXISTS queries need
improved testing
Posted by "Thomas Nielsen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3349?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12564159#action_12564159 ]
Thomas Nielsen commented on DERBY-3349:
---------------------------------------
There are 4 more variations of problematic queries in the 'derby-3301-extra.sql' script attached to DERBY-3301.
> Nested WHERE EXISTS queries need improved testing
> -------------------------------------------------
>
> Key: DERBY-3349
> URL: https://issues.apache.org/jira/browse/DERBY-3349
> Project: Derby
> Issue Type: Improvement
> Components: Test
> Reporter: Thomas Nielsen
>
> DERBY-3301 reported an error in subquery flattening of nested WHERE EXISTS clauses, or WHERE EXISTS subqueries. The query returned the wrong number of rows.
> There is currently some tests for WHERE EXISTS, but none for nested WHERE EXISTS clauses.
> See DERBY-3301 for a full sql repro.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3349) Nested WHERE EXISTS queries need
improved testing
Posted by "Dyre Tjeldvoll (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3349?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dyre Tjeldvoll updated DERBY-3349:
----------------------------------
Derby Info: (was: [Patch Available])
Committed revision 628686.
> Nested WHERE EXISTS queries need improved testing
> -------------------------------------------------
>
> Key: DERBY-3349
> URL: https://issues.apache.org/jira/browse/DERBY-3349
> Project: Derby
> Issue Type: Improvement
> Components: Test
> Reporter: Thomas Nielsen
> Assignee: Thomas Nielsen
> Attachments: d3349-1.diff, d3349-1.stat, d3349-2.diff, d3349-3.diff
>
>
> DERBY-3301 reported an error in subquery flattening of nested WHERE EXISTS clauses, or WHERE EXISTS subqueries. The query returned the wrong number of rows.
> There is currently some tests for WHERE EXISTS, but none for nested WHERE EXISTS clauses.
> See DERBY-3301 for a full sql repro.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (DERBY-3349) Nested WHERE EXISTS queries need
improved testing
Posted by "Thomas Nielsen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3349?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Thomas Nielsen resolved DERBY-3349.
-----------------------------------
Resolution: Fixed
Thanks Dyre
> Nested WHERE EXISTS queries need improved testing
> -------------------------------------------------
>
> Key: DERBY-3349
> URL: https://issues.apache.org/jira/browse/DERBY-3349
> Project: Derby
> Issue Type: Improvement
> Components: Test
> Affects Versions: 10.2.2.0, 10.3.2.1
> Reporter: Thomas Nielsen
> Assignee: Thomas Nielsen
> Fix For: 10.3.2.2, 10.4.0.0
>
> Attachments: d3349-1.diff, d3349-1.stat, d3349-2.diff, d3349-3.diff
>
>
> DERBY-3301 reported an error in subquery flattening of nested WHERE EXISTS clauses, or WHERE EXISTS subqueries. The query returned the wrong number of rows.
> There is currently some tests for WHERE EXISTS, but none for nested WHERE EXISTS clauses.
> See DERBY-3301 for a full sql repro.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3349) Nested WHERE EXISTS queries need
improved testing
Posted by "Thomas Nielsen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3349?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Thomas Nielsen updated DERBY-3349:
----------------------------------
Attachment: d3349-1.diff
d3349-1.stat
I added the 5 other queries in the sql repro posted in DERBY-3301 to the new junit NestedWhereSubqueryTest.
The updated test pass with the 1 patch applied.
I think this update should be commited to be on the safe side. But there is still more work to be done to complete this issue, mainly around covering all the combinations Army listed earlier in 3301.
> Nested WHERE EXISTS queries need improved testing
> -------------------------------------------------
>
> Key: DERBY-3349
> URL: https://issues.apache.org/jira/browse/DERBY-3349
> Project: Derby
> Issue Type: Improvement
> Components: Test
> Reporter: Thomas Nielsen
> Attachments: d3349-1.diff, d3349-1.stat
>
>
> DERBY-3301 reported an error in subquery flattening of nested WHERE EXISTS clauses, or WHERE EXISTS subqueries. The query returned the wrong number of rows.
> There is currently some tests for WHERE EXISTS, but none for nested WHERE EXISTS clauses.
> See DERBY-3301 for a full sql repro.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.