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 "Dyre Tjeldvoll (JIRA)" <ji...@apache.org> on 2007/02/23 10:15:05 UTC
[jira] Created: (DERBY-2370) EXISTS may return the wrong value for
sub-queries involving set operations
EXISTS may return the wrong value for sub-queries involving set operations
--------------------------------------------------------------------------
Key: DERBY-2370
URL: https://issues.apache.org/jira/browse/DERBY-2370
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.2.2.0
Reporter: Dyre Tjeldvoll
It seems like EXISTS on a SELECT returning zero rows returns false (as
expected), but EXISTS on INTERSECT of two disjunct sets returns true,
e.g EXISTS (values 1 intersect values 2).
Yip Ng wrote on derby-dev:
I believe its probably got to do with the EXISTS subquery transforming
the original RCL to
a TRUE boolean value for the INTERSECT. So during row comparison at
execution time
for INTERSECT processing since true == true(thus intersects), so it
will always return 'BAD'. Likewise,
select * from ( values 'OK' ) as T where exists (values 1 except values 2);
This supposedly should return 'OK' but because of the boolean
transformation mentioned
above for EXISTS subquery, it will return no rows for EXCEPT
processing.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (DERBY-2370) EXISTS may return the wrong value for
sub-queries involving set operations
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2370?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
A B resolved DERBY-2370.
------------------------
Resolution: Fixed
Derby Info: [Existing Application Impact, Release Note Needed] (was: [Existing Application Impact])
Marking issue resolved (again). I'll wait a few days before closing it in case there are comments on the release note...
> EXISTS may return the wrong value for sub-queries involving set operations
> --------------------------------------------------------------------------
>
> Key: DERBY-2370
> URL: https://issues.apache.org/jira/browse/DERBY-2370
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.2.0
> Reporter: Dyre Tjeldvoll
> Assigned To: A B
> Fix For: 10.3.0.0
>
> Attachments: d2370_engine_v1.patch, d2370_tests_v1.patch, d2370_v1.stat, d2370_writeup_v1.html, releaseNote.html, repro.sql
>
>
> It seems like EXISTS on a SELECT returning zero rows returns false (as
> expected), but EXISTS on INTERSECT of two disjunct sets returns true,
> e.g EXISTS (values 1 intersect values 2).
> Yip Ng wrote on derby-dev:
> I believe its probably got to do with the EXISTS subquery transforming
> the original RCL to
> a TRUE boolean value for the INTERSECT. So during row comparison at
> execution time
> for INTERSECT processing since true == true(thus intersects), so it
> will always return 'BAD'. Likewise,
> select * from ( values 'OK' ) as T where exists (values 1 except values 2);
> This supposedly should return 'OK' but because of the boolean
> transformation mentioned
> above for EXISTS subquery, it will return no rows for EXCEPT
> processing.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2370) EXISTS may return the wrong value for
sub-queries involving set operations
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2370?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-2370:
---------------------------------
Attachment: releaseNote.html
Scrub the html in the release note so that it can be digested by the SAX parser in the release note generator.
> EXISTS may return the wrong value for sub-queries involving set operations
> --------------------------------------------------------------------------
>
> Key: DERBY-2370
> URL: https://issues.apache.org/jira/browse/DERBY-2370
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.2.0
> Reporter: Dyre Tjeldvoll
> Assignee: A B
> Fix For: 10.3.0.0
>
> Attachments: d2370_engine_v1.patch, d2370_tests_v1.patch, d2370_v1.stat, d2370_writeup_v1.html, releaseNote.html, releaseNote.html, repro.sql
>
>
> It seems like EXISTS on a SELECT returning zero rows returns false (as
> expected), but EXISTS on INTERSECT of two disjunct sets returns true,
> e.g EXISTS (values 1 intersect values 2).
> Yip Ng wrote on derby-dev:
> I believe its probably got to do with the EXISTS subquery transforming
> the original RCL to
> a TRUE boolean value for the INTERSECT. So during row comparison at
> execution time
> for INTERSECT processing since true == true(thus intersects), so it
> will always return 'BAD'. Likewise,
> select * from ( values 'OK' ) as T where exists (values 1 except values 2);
> This supposedly should return 'OK' but because of the boolean
> transformation mentioned
> above for EXISTS subquery, it will return no rows for EXCEPT
> processing.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (DERBY-2370) EXISTS may return the wrong value for
sub-queries involving set operations
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2370?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
A B resolved DERBY-2370.
------------------------
Resolution: Fixed
Fix Version/s: 10.3.0.0
Derby Info: [Existing Application Impact] (was: [Existing Application Impact, Patch Available])
Thank you for the review, Dyre. I synced with the latest codeline and re-ran derbyall and suites.All as a sanity check, then committed the patch with svn #532509:
URL: http://svn.apache.org/viewvc?view=rev&rev=532509
As I haven't heard any comments regarding the new behavior for certain queries (namely, throwing an error where Derby used to succeed in some cases), I'm working under the assumption that this is okay and am marking the issue RESOLVED.
Many thanks for your time!
> EXISTS may return the wrong value for sub-queries involving set operations
> --------------------------------------------------------------------------
>
> Key: DERBY-2370
> URL: https://issues.apache.org/jira/browse/DERBY-2370
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.2.0
> Reporter: Dyre Tjeldvoll
> Assigned To: A B
> Fix For: 10.3.0.0
>
> Attachments: d2370_engine_v1.patch, d2370_tests_v1.patch, d2370_v1.stat, d2370_writeup_v1.html, repro.sql
>
>
> It seems like EXISTS on a SELECT returning zero rows returns false (as
> expected), but EXISTS on INTERSECT of two disjunct sets returns true,
> e.g EXISTS (values 1 intersect values 2).
> Yip Ng wrote on derby-dev:
> I believe its probably got to do with the EXISTS subquery transforming
> the original RCL to
> a TRUE boolean value for the INTERSECT. So during row comparison at
> execution time
> for INTERSECT processing since true == true(thus intersects), so it
> will always return 'BAD'. Likewise,
> select * from ( values 'OK' ) as T where exists (values 1 except values 2);
> This supposedly should return 'OK' but because of the boolean
> transformation mentioned
> above for EXISTS subquery, it will return no rows for EXCEPT
> processing.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Closed: (DERBY-2370) EXISTS may return the wrong value for
sub-queries involving set operations
Posted by "Dyre Tjeldvoll (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2370?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dyre Tjeldvoll closed DERBY-2370.
---------------------------------
> EXISTS may return the wrong value for sub-queries involving set operations
> --------------------------------------------------------------------------
>
> Key: DERBY-2370
> URL: https://issues.apache.org/jira/browse/DERBY-2370
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.2.0
> Reporter: Dyre Tjeldvoll
> Assigned To: A B
> Fix For: 10.3.0.0
>
> Attachments: d2370_engine_v1.patch, d2370_tests_v1.patch, d2370_v1.stat, d2370_writeup_v1.html, repro.sql
>
>
> It seems like EXISTS on a SELECT returning zero rows returns false (as
> expected), but EXISTS on INTERSECT of two disjunct sets returns true,
> e.g EXISTS (values 1 intersect values 2).
> Yip Ng wrote on derby-dev:
> I believe its probably got to do with the EXISTS subquery transforming
> the original RCL to
> a TRUE boolean value for the INTERSECT. So during row comparison at
> execution time
> for INTERSECT processing since true == true(thus intersects), so it
> will always return 'BAD'. Likewise,
> select * from ( values 'OK' ) as T where exists (values 1 except values 2);
> This supposedly should return 'OK' but because of the boolean
> transformation mentioned
> above for EXISTS subquery, it will return no rows for EXCEPT
> processing.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2370) EXISTS may return the wrong value for
sub-queries involving set operations
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2370?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
A B updated DERBY-2370:
-----------------------
Attachment: d2370_v1.stat
> EXISTS may return the wrong value for sub-queries involving set operations
> --------------------------------------------------------------------------
>
> Key: DERBY-2370
> URL: https://issues.apache.org/jira/browse/DERBY-2370
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.2.0
> Reporter: Dyre Tjeldvoll
> Assigned To: A B
> Attachments: d2370_engine_v1.patch, d2370_tests_v1.patch, d2370_v1.stat, d2370_writeup_v1.html, repro.sql
>
>
> It seems like EXISTS on a SELECT returning zero rows returns false (as
> expected), but EXISTS on INTERSECT of two disjunct sets returns true,
> e.g EXISTS (values 1 intersect values 2).
> Yip Ng wrote on derby-dev:
> I believe its probably got to do with the EXISTS subquery transforming
> the original RCL to
> a TRUE boolean value for the INTERSECT. So during row comparison at
> execution time
> for INTERSECT processing since true == true(thus intersects), so it
> will always return 'BAD'. Likewise,
> select * from ( values 'OK' ) as T where exists (values 1 except values 2);
> This supposedly should return 'OK' but because of the boolean
> transformation mentioned
> above for EXISTS subquery, it will return no rows for EXCEPT
> processing.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2370) EXISTS may return the wrong value for
sub-queries involving set operations
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2370?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
A B updated DERBY-2370:
-----------------------
Attachment: releaseNote.html
Attaching first draft of the release note.
> EXISTS may return the wrong value for sub-queries involving set operations
> --------------------------------------------------------------------------
>
> Key: DERBY-2370
> URL: https://issues.apache.org/jira/browse/DERBY-2370
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.2.0
> Reporter: Dyre Tjeldvoll
> Assigned To: A B
> Fix For: 10.3.0.0
>
> Attachments: d2370_engine_v1.patch, d2370_tests_v1.patch, d2370_v1.stat, d2370_writeup_v1.html, releaseNote.html, repro.sql
>
>
> It seems like EXISTS on a SELECT returning zero rows returns false (as
> expected), but EXISTS on INTERSECT of two disjunct sets returns true,
> e.g EXISTS (values 1 intersect values 2).
> Yip Ng wrote on derby-dev:
> I believe its probably got to do with the EXISTS subquery transforming
> the original RCL to
> a TRUE boolean value for the INTERSECT. So during row comparison at
> execution time
> for INTERSECT processing since true == true(thus intersects), so it
> will always return 'BAD'. Likewise,
> select * from ( values 'OK' ) as T where exists (values 1 except values 2);
> This supposedly should return 'OK' but because of the boolean
> transformation mentioned
> above for EXISTS subquery, it will return no rows for EXCEPT
> processing.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Assigned: (DERBY-2370) EXISTS may return the wrong value for
sub-queries involving set operations
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2370?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
A B reassigned DERBY-2370:
--------------------------
Assignee: A B
> EXISTS may return the wrong value for sub-queries involving set operations
> --------------------------------------------------------------------------
>
> Key: DERBY-2370
> URL: https://issues.apache.org/jira/browse/DERBY-2370
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.2.0
> Reporter: Dyre Tjeldvoll
> Assigned To: A B
> Attachments: repro.sql
>
>
> It seems like EXISTS on a SELECT returning zero rows returns false (as
> expected), but EXISTS on INTERSECT of two disjunct sets returns true,
> e.g EXISTS (values 1 intersect values 2).
> Yip Ng wrote on derby-dev:
> I believe its probably got to do with the EXISTS subquery transforming
> the original RCL to
> a TRUE boolean value for the INTERSECT. So during row comparison at
> execution time
> for INTERSECT processing since true == true(thus intersects), so it
> will always return 'BAD'. Likewise,
> select * from ( values 'OK' ) as T where exists (values 1 except values 2);
> This supposedly should return 'OK' but because of the boolean
> transformation mentioned
> above for EXISTS subquery, it will return no rows for EXCEPT
> processing.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Closed: (DERBY-2370) EXISTS may return the wrong value for
sub-queries involving set operations
Posted by "Dyre Tjeldvoll (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2370?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dyre Tjeldvoll closed DERBY-2370.
---------------------------------
> EXISTS may return the wrong value for sub-queries involving set operations
> --------------------------------------------------------------------------
>
> Key: DERBY-2370
> URL: https://issues.apache.org/jira/browse/DERBY-2370
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.2.0
> Reporter: Dyre Tjeldvoll
> Assignee: A B
> Fix For: 10.3.1.4
>
> Attachments: d2370_engine_v1.patch, d2370_tests_v1.patch, d2370_v1.stat, d2370_writeup_v1.html, releaseNote.html, releaseNote.html, repro.sql
>
>
> It seems like EXISTS on a SELECT returning zero rows returns false (as
> expected), but EXISTS on INTERSECT of two disjunct sets returns true,
> e.g EXISTS (values 1 intersect values 2).
> Yip Ng wrote on derby-dev:
> I believe its probably got to do with the EXISTS subquery transforming
> the original RCL to
> a TRUE boolean value for the INTERSECT. So during row comparison at
> execution time
> for INTERSECT processing since true == true(thus intersects), so it
> will always return 'BAD'. Likewise,
> select * from ( values 'OK' ) as T where exists (values 1 except values 2);
> This supposedly should return 'OK' but because of the boolean
> transformation mentioned
> above for EXISTS subquery, it will return no rows for EXCEPT
> processing.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Reopened: (DERBY-2370) EXISTS may return the wrong value for
sub-queries involving set operations
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2370?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
A B reopened DERBY-2370:
------------------------
Reopening to attach release note.
> EXISTS may return the wrong value for sub-queries involving set operations
> --------------------------------------------------------------------------
>
> Key: DERBY-2370
> URL: https://issues.apache.org/jira/browse/DERBY-2370
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.2.0
> Reporter: Dyre Tjeldvoll
> Assigned To: A B
> Fix For: 10.3.0.0
>
> Attachments: d2370_engine_v1.patch, d2370_tests_v1.patch, d2370_v1.stat, d2370_writeup_v1.html, repro.sql
>
>
> It seems like EXISTS on a SELECT returning zero rows returns false (as
> expected), but EXISTS on INTERSECT of two disjunct sets returns true,
> e.g EXISTS (values 1 intersect values 2).
> Yip Ng wrote on derby-dev:
> I believe its probably got to do with the EXISTS subquery transforming
> the original RCL to
> a TRUE boolean value for the INTERSECT. So during row comparison at
> execution time
> for INTERSECT processing since true == true(thus intersects), so it
> will always return 'BAD'. Likewise,
> select * from ( values 'OK' ) as T where exists (values 1 except values 2);
> This supposedly should return 'OK' but because of the boolean
> transformation mentioned
> above for EXISTS subquery, it will return no rows for EXCEPT
> processing.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2370) EXISTS may return the wrong value for
sub-queries involving set operations
Posted by "Dyre Tjeldvoll (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2370?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dyre Tjeldvoll updated DERBY-2370:
----------------------------------
Attachment: repro.sql
ij script to reproduce the bug.
> EXISTS may return the wrong value for sub-queries involving set operations
> --------------------------------------------------------------------------
>
> Key: DERBY-2370
> URL: https://issues.apache.org/jira/browse/DERBY-2370
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.2.0
> Reporter: Dyre Tjeldvoll
> Attachments: repro.sql
>
>
> It seems like EXISTS on a SELECT returning zero rows returns false (as
> expected), but EXISTS on INTERSECT of two disjunct sets returns true,
> e.g EXISTS (values 1 intersect values 2).
> Yip Ng wrote on derby-dev:
> I believe its probably got to do with the EXISTS subquery transforming
> the original RCL to
> a TRUE boolean value for the INTERSECT. So during row comparison at
> execution time
> for INTERSECT processing since true == true(thus intersects), so it
> will always return 'BAD'. Likewise,
> select * from ( values 'OK' ) as T where exists (values 1 except values 2);
> This supposedly should return 'OK' but because of the boolean
> transformation mentioned
> above for EXISTS subquery, it will return no rows for EXCEPT
> processing.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2370) EXISTS may return the wrong value for
sub-queries involving set operations
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2370?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
A B updated DERBY-2370:
-----------------------
Attachment: d2370_tests_v1.patch
d2370_engine_v1.patch
d2370_writeup_v1.html
Yip was exactly right in his diagnosis of the problem. I'm attaching a short writeup of the problem and a proposed solution as d2370_writeup_v1.patch.
I'm also attaching a patch, d2370_engine_v1.patch, that implements the change by doing the following:
1. Adds the ability to mark a FromList as "transparent", and updates FromList.bindExpressions() to
pass the outer FROM list down (instead of "this") if the FromList is transparent.
2. Updates FromList.expandAll(...) to account for the fact that outer FROM tables might now appear
in a nested FromList (as a result of "transparent" FromLists; see code comments for details).
3. Modifies the "setResultToBooleanTrue()" signature to return a ResultSetNode (it was "void" previously).
4. Modifies ResultSetNode.setResultToBooleanTrue() to always return "this".
5. Modifies SetOperatorNode.setResultToBooleanTrue() so that it now creates an internal "SELECT *" query
whose FROM list contains just the SetOperatorNode. Then we transform the "*" for the new SELECT into
"TRUE" and leave the SetOperatorNode's result columns UN-transformed. Finally, we mark the new
SelectNode's FromList as "transparent" and return the new SelectNode.
I've included a corresponding patch, d2370_tests_v1.patch, that contains slight modifications to two tests: lang/union.sql and lang/ResultSetsFromPreparedStatementTest. The latter changes are expected based on comments in the test; the former (lang/union.sql) has a couple of queries that now fail when they used to succeed. However, I think the failures are correct--i.e. that Derby should have been failing prior to these changes and was not--so I've updated lang/union.sql accordingly. I will send an email about this to derby-dev to see if I can get any feedback/suggestions around this. And finally, d2370_tests_v1.patch creates a new JUnit test, lang/ExistsWithSetOpsTest, which captures and builds on the repro.sql script attached to this issue.
I ran derbyall and suites.All with a single failure:
jdbcapi/parameterMetaDataJdbc30.java
This failure only occurred for the client framework and the diff showed a failure to connect--which doesn't seem related to my changes. When I ran the test independently it passed as expected. So I think it was just a fluke.
Reviews or other feedback would be greatly appreciated, as always.
> EXISTS may return the wrong value for sub-queries involving set operations
> --------------------------------------------------------------------------
>
> Key: DERBY-2370
> URL: https://issues.apache.org/jira/browse/DERBY-2370
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.2.0
> Reporter: Dyre Tjeldvoll
> Assigned To: A B
> Attachments: d2370_engine_v1.patch, d2370_tests_v1.patch, d2370_v1.stat, d2370_writeup_v1.html, repro.sql
>
>
> It seems like EXISTS on a SELECT returning zero rows returns false (as
> expected), but EXISTS on INTERSECT of two disjunct sets returns true,
> e.g EXISTS (values 1 intersect values 2).
> Yip Ng wrote on derby-dev:
> I believe its probably got to do with the EXISTS subquery transforming
> the original RCL to
> a TRUE boolean value for the INTERSECT. So during row comparison at
> execution time
> for INTERSECT processing since true == true(thus intersects), so it
> will always return 'BAD'. Likewise,
> select * from ( values 'OK' ) as T where exists (values 1 except values 2);
> This supposedly should return 'OK' but because of the boolean
> transformation mentioned
> above for EXISTS subquery, it will return no rows for EXCEPT
> processing.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2370) EXISTS may return the wrong value for
sub-queries involving set operations
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2370?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
A B updated DERBY-2370:
-----------------------
Derby Info: [Patch Available, Existing Application Impact]
> EXISTS may return the wrong value for sub-queries involving set operations
> --------------------------------------------------------------------------
>
> Key: DERBY-2370
> URL: https://issues.apache.org/jira/browse/DERBY-2370
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.2.0
> Reporter: Dyre Tjeldvoll
> Assigned To: A B
> Attachments: d2370_engine_v1.patch, d2370_tests_v1.patch, d2370_v1.stat, d2370_writeup_v1.html, repro.sql
>
>
> It seems like EXISTS on a SELECT returning zero rows returns false (as
> expected), but EXISTS on INTERSECT of two disjunct sets returns true,
> e.g EXISTS (values 1 intersect values 2).
> Yip Ng wrote on derby-dev:
> I believe its probably got to do with the EXISTS subquery transforming
> the original RCL to
> a TRUE boolean value for the INTERSECT. So during row comparison at
> execution time
> for INTERSECT processing since true == true(thus intersects), so it
> will always return 'BAD'. Likewise,
> select * from ( values 'OK' ) as T where exists (values 1 except values 2);
> This supposedly should return 'OK' but because of the boolean
> transformation mentioned
> above for EXISTS subquery, it will return no rows for EXCEPT
> processing.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2370) EXISTS may return the wrong value
for sub-queries involving set operations
Posted by "Dyre Tjeldvoll (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2370?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12491606 ]
Dyre Tjeldvoll commented on DERBY-2370:
---------------------------------------
I have looked at the writeup and and also looked at the
patch. While I am certainly no expert in this area I think the
explanation and solution in the writeup looks very sound. The
patch seem to implement what is suggested, it is clear, well
documented and tested. +1 from me.
> EXISTS may return the wrong value for sub-queries involving set operations
> --------------------------------------------------------------------------
>
> Key: DERBY-2370
> URL: https://issues.apache.org/jira/browse/DERBY-2370
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.2.0
> Reporter: Dyre Tjeldvoll
> Assigned To: A B
> Attachments: d2370_engine_v1.patch, d2370_tests_v1.patch, d2370_v1.stat, d2370_writeup_v1.html, repro.sql
>
>
> It seems like EXISTS on a SELECT returning zero rows returns false (as
> expected), but EXISTS on INTERSECT of two disjunct sets returns true,
> e.g EXISTS (values 1 intersect values 2).
> Yip Ng wrote on derby-dev:
> I believe its probably got to do with the EXISTS subquery transforming
> the original RCL to
> a TRUE boolean value for the INTERSECT. So during row comparison at
> execution time
> for INTERSECT processing since true == true(thus intersects), so it
> will always return 'BAD'. Likewise,
> select * from ( values 'OK' ) as T where exists (values 1 except values 2);
> This supposedly should return 'OK' but because of the boolean
> transformation mentioned
> above for EXISTS subquery, it will return no rows for EXCEPT
> processing.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.