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 Mon, 31 Aug 2009 12:03:32 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4284?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12749474#action_12749474
] 

Knut Anders Hatlen commented on DERBY-4284:
-------------------------------------------

At least one of the test failures shows an actual problem. store/xaOffline1.sql defines the
following view as a right outer join between two diagnostic tables:

create view lock_table as
select 
    cast(username as char(8)) as username,
    cast(t.type as char(8)) as trantype,
    cast(l.type as char(8)) as type,
    cast(lockcount as char(3)) as cnt,
    mode,
    cast(tablename as char(12)) as tabname,
    cast(lockname as char(10)) as lockname,
    state,
    status
from 
    syscs_diag.lock_table l  right outer join syscs_diag.transaction_table t
on l.xid = t.xid where l.tableType <> 'S' and t.type='UserTransaction';

Without the patch, all the columns returned by the view are nullable. That's not correct.
However, with the patch, the columns CNT, TABNAME and LOCKNAME are incorrectly reported as
non-nullable. Since those columns come from the left side of a right outer join, they should
be nullable.

> 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, 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