Assertion failure preparing query with AND and OR in where clause

• Key: DERBY-2500
• Type: Bug
• Status: Resolved
• Resolution: Fixed
• Priority: Major
• Assignee: A B
• Reporter: Andrew McIntyre
• Component/s: SQL
• Affects Version/s: 10.3.1.4
• Fix Version/s: 10.3.1.4

Description

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.

Comments

[A B]

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.

[A B]

Realized after I uploaded the patch that the comments for the test case in DistinctTest need to be updated to reflect the changes made in this issue. Other than this change to the comments, v1 and v2 are identical.

[A B]

Committed d2500_v3.patch, which is the same as the _v2 patch except that the second new test case was corrected so that, in the absence of the fix for this issue, the query would actually return incorrect results.

Committed with svn # 525925:

  URL: http://svn.apache.org/viewvc?view=rev&rev=525925