[
https://issues.apache.org/jira/browse/DERBY-4284?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12750731#action_12750731
]
Knut Anders Hatlen commented on DERBY-4284:
-------------------------------------------
Thanks for that hint, Bryan. It looks like my changes to wisconsin.out are the
exact reverse of the following commit:
------------------------------------------------------------------------
r562113 | djd | 2007-08-02 16:10:47 +0200 (to, 02 aug 2007) | 2 lines
DERBY-2916 Fix wisconsin canon file due to changes that allowed a valid
optimization to the plan.
------------------------------------------------------------------------
If the root cause of DERBY-2916 is found and fixed, it will hopefully be
possible to revert the changes I have suggested for
JoinNode.getMatchingColumn(), as it feels somewhat dirty to have to set the
nullability two different places (both buildRCL() and getMatchingColumn()). For
the record, I did try to move setting the nullability from buildRCL(), which is
called from bindResultColumn(), to bindExpressions() which is called before
bindResultColumn() and also before the cast node is bound. That seemed to fix
the issues for most queries, but for multi-level nested outer joins the
left/right child result sets hadn't always initialized their RCLs at that time,
so that broke down.
I didn't find any single place to put the nullability code so that it was
executed sufficiently early so that all nodes on top of it would see the
correct nullability, and at the same time sufficiently late so that the child
result sets were properly bound. Though I must admit I don't fully understand
JoinNode's bind logic with bindExpressions() followed by bindResultColumns()
again followed by deferredBindExpressions().
It sounds very likely that the problem with the cast node not seeing the
correct nullability is caused by the DERBY-2775 changes. Before DERBY-2775,
when the nullability of the result column in the join node was changed, the
change in nullability would automatically cascade up to the cast node since it
had a reference to the exact same data type descriptor.
> All Columns become Nullable when Using left join
> ------------------------------------------------
>
> Key: DERBY-4284
> URL: https://issues.apache.org/jira/browse/DERBY-4284
> Project: Derby
> Issue Type: Bug
> Components: JDBC, SQL
> Affects Versions: 10.5.1.1
> Environment: Microsoft Windows XP SP3, Sun JDK 6 Update 14
> Reporter: Chua Chee Seng
> Assignee: Knut Anders Hatlen
> Attachments: derby-4284-1a.diff, derby-4284-1b.diff,
> derby-4284-1b.stat, derby-4284-1c.diff, derby-4284-1c.stat, Main.java
>
>
> Consider following:-
> create table person (
> id varchar(20) not null,
> name varchar(100) not null
> );
> create table car (
> id varchar(20) not null,
> person_id varchar(20) not null,
> model varchar(100) not null,
> plat_no varchar(100) not null
> );
> When select :-
> select
> p.name,
> c.model,
> c.plat_no
> from person p
> left join car c on (p.id = c.person_id);
> From the ResultSet, get the ResultSetMetaData and inspect each column's
> isNullable() value, which is always = 1 (always nullable). Expected : column
> 'p.name' isNullable = 0 (not nullable), but I get 'p.name' isNullable = 1
> (nullable)
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
