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-2500) Assertion failure preparing query with AND and OR in where clause
Date Thu, 29 Mar 2007 23:34:25 GMT

     [ https://issues.apache.org/jira/browse/DERBY-2500?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel

A B updated DERBY-2500:

    Attachment: d2500_v1.stat

Attaching d2500_v1.patch for review and commit.

The comments just before the ASSERT reported in this issue include the following:

 * ... Checks elsewhere in the code should ensure that no probe
 * predicates have made it this far, but if we're running in SANE
 * mode it doesn't hurt to verify.

As it turns out, the "checks elsewhere" do not actually exist--but they should (oops).  What
we need is to recognize when we're doing a hash join and to explicitly avoid generating probe
predicates in that case.  Failure to do so leads to this ASSERT failure in sane mode--and
in insane mode it could actually lead to incorrect results.

So the fix is, I think, fairly straightforward: we just need to add an appropriate check to
the "orderUsefulPredicates()" method of PredicateList.  That's what d2500_v1.patch does.

That said, I then noticed that the comments in HashJoinStrategy do not fully explain why we
need to disallow probe predicates.  In particular the existing comments do not (but should)
mention that the reason for such a restriction is simply one of "not-yet-implemented"--i.e.
the appropriate IN-list multi-probing logic has not been added to HashScanResultSet.  DERBY-47
added a new MultiProbeTableScanResult to handle multi-probing for nested loop joins, but did
not extend that functionality to hash joins.  As a result, hash joins with multi-probing predicates
will behave incorrectly, hence this Jira.

So if at some point such functionality *is* added then we should be able to remove the current
restriction regarding IN-list probing and hash joins.  As I think there could indeed be benefits
to doing so, I filed DERBY-2503 for that enhancement.  Until that is done, though, d2500_v1.patch
adds the necessary logic to prevent generation of probe predicates for hash joins, and also
updates code comments to more clearly state what is going on.  The patch also renables "testResultSetInOrderWhenUsingIndex()"
in lang/DistinctTest.java and adds another test case to demonstrate how, in the absence of
an ASSERT failure, Derby could have returned incorrect results before this issue was fixed.

I ran derbyall and suites.All with d2500_v1.patch applied and there were no failures.

> Assertion failure preparing query with AND and OR in where clause
> -----------------------------------------------------------------
>                 Key: DERBY-2500
>                 URL: https://issues.apache.org/jira/browse/DERBY-2500
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions:
>            Reporter: Andrew McIntyre
>         Assigned To: A B
>         Attachments: d2500_v1.patch, d2500_v1.stat
> See commented out test in DistinctTest, DistinctTest.testResultSetInOrderWhenUsingIndex.
> The actual assert is:
> ASSERT FAILED Found IN-list probing (NLR.LUSERNAME = <expr>) while generating HASH
join, which should not happen.
> I was able to simplify the query a little:
> SELECT DISTINCT nb.name AS name, nb.summary AS summary FROM netbutton1 nb, netbuttonlibraryrole1
nlr, library_netbutton ln WHERE nlr.netbuttonlibrary_id = ln.netbuttonlibrary_id AND (nlr.lusername
= ? OR nlr.lusername =?)
> Removing nlr.netbuttonlibrary_id = ln.netbuttonlibrary_id clause but leaving the 'nb.lname
= ln.lname' in front of the AND did not reproduce the problem.

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message