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-2370) EXISTS may return the wrong value for sub-queries involving set operations
Date Fri, 20 Apr 2007 22:53:15 GMT

     [ 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.


Mime
View raw message