All Columns become Nullable when Using left join?

[Chua Chee Seng <ch...@kzensolutions.com>]

Hi all,

 

Consider the 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 

);

 

Then, use JDBC to execute the following Select query:-

 

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).  It returns
correct nullable meta value if no left join is in used.  I suspect this
should be a bug?

 

Thanks.

 

Best Regards,

 

Chua Chee Seng

Java Architect

Sun Certified Enterprise Architect for JEE 5 cum ACE for Flex 3 with AIR

Research & Development Devision

M: +60 12 3840727

 

KZEN Solutions Bhd

Suite B-07-07 & B-07-08,

7th Floor, Block B,

Plaza Mont' Kiara,

No. 2, Jalan Kiara,

50480 Kuala Lumpur,

Malaysia

T: +603 6201 0242

F: +603 6203 5037

W: www.kzensolutions.com <http://www.kzensolutions.com/>  

E: cheeseng@kzensolutions.com 

 

 

Re: All Columns become Nullable when Using left join?

[Bryan Pendleton <bp...@amberpoint.com>]

> 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).  It returns 
> correct nullable meta value if no left join is in used.  I suspect this 
> should be a bug?

Certainly the columns from car will be nullable in this query, since that's
the basic idea of an outer join.

But I am surprised that p.name is shown as nullable; I agree that it seems
like it should not be nullable.

thanks,

bryan