Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <814849516.1251991377524.JavaMail.jira@brutus>
Date: Thu, 3 Sep 2009 08:22:57 -0700 (PDT)
From: "Knut Anders Hatlen (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-2916) Change/error? in 'Ordered null
 semantics' output from 'SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()' in
 lang/wisconsin.java
In-Reply-To: <4877764.1184097605209.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


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