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-4284) All Columns become Nullable when Using left join
Date Wed, 02 Sep 2009 23:27:32 GMT

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


Mime
View raw message