db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-2916) Change/error? in 'Ordered null semantics' output from 'SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()' in lang/wisconsin.java
Date Thu, 03 Sep 2009 15:22:57 GMT

    [ 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:
>         Environment: OS: All
> JVM: All
>            Reporter: Ole Solberg
>            Assignee: Daniel John Debrunner
>            Priority: Minor
> '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.

View raw message