[
https://issues.apache.org/jira/browse/DERBY-2916?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12751053#action_12751053
]
Knut Anders Hatlen commented on DERBY-2916:
-------------------------------------------
The nullability of the columns on the right side of a left outer join is
modified rather late in the bind phase (in JoinNode.buildRCL()), and other
nodes higher up in the tree may already have picked up the original nullability
before it is changed. Before the DERBY-2775 changes, the change in nullability
would be propagated automatically. After the changes, they will still see the
old nullability after buildRCL() has done its work.
This caused problems when fixing DERBY-4284 because cast nodes that referred to
columns on the right side of a left outer join could end up non-nullable even
though they actually could have NULL values. DERBY-4284 therefore added a
workaround by setting the nullability in JoinNode.getMatchingColumn() in
addition to the existing code in buildRCL(). That workaround also made the
query plan mentioned in this issue go back to its original form.
I agree that the plan selected after the DERBY-2775 changes uses a valid
optimization. After DERBY-4284 it again picks a plan without that optimization,
which is unfortunate, but I believe the more optimized plan was picked just by
accident because the nullability was retrieved before the columns in the join
node were fully bound.
Perhaps we need to do two things before this issue is fully resolved:
1) Find a better place (earlier) in the bind phase for setting the nullability
of the right-side columns in a left outer join so that all the nodes above it
see the correct nullabililty. This will solve the problem for cast nodes seen
in DERBY-4284, and the workaround in JoinNode.getMatchingColumn() can be
removed, but I don't think that this will make the query plan pick up the
ordered nulls optimization again.
2) To re-enable the ordered nulls optimization, it may be necessary to change
the code that makes right side of left outer join nullable create new nodes on
top of the non-nullable columns instead of changing their nullability directly.
Since the column in question cannot be null down in the index scan (hence it's
ok for the plan to use the ordered null optimization), but it can be null from
the join node and up, the scan and the join should probably have different
physical result column objects and not share the same one. I haven't verified
that this is in fact the problem, but that's what I suspect.
> Change/error? in 'Ordered null semantics' output from
> 'SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()' in lang/wisconsin.java
> -----------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-2916
> URL: https://issues.apache.org/jira/browse/DERBY-2916
> Project: Derby
> Issue Type: Bug
> Components: Test
> Affects Versions: 10.4.1.3
> Environment: OS: All
> JVM: All
> Reporter: Ole Solberg
> Assignee: Daniel John Debrunner
> Priority: Minor
>
> SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS() on
> 'Statement Text:
> select * from TENKTUP1
> left outer join TENKTUP2 on
> (
> TENKTUP1.unique1 = TENKTUP2.unique1
> )
> left outer join ONEKTUP on
> (
> TENKTUP2.unique2 = ONEKTUP.unique2
> )
> left outer join BPRIME on
> (
> ONEKTUP.onePercent = BPRIME.onePercent
> )
> '
> now returns extra "0" in
> ' Ordered null semantics on the following columns:
> 0
> stop position:
> > on first 1 column(s).
> Ordered null semantics on the following columns:
> 0
> '
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
