Обсуждение: ResultSetMetaData.isNullable(i) and outer joined columns
Hello,
Although the spec doesn't explicitly distinguish between result set and source table only the former seems sensible to me -- what I expect the isNullable return value to reflect is whether an invocation of the ResultSet.getXXX(i) methods always returns an object (or sensible primitive), if they may return null or if that's unknown.
Test case:
package tmdbctest;
import java.sql.*;
/*
CREATE TABLE employee (
id SERIAL PRIMARY KEY,
name TEXT UNIQUE,
salary NUMERIC,
hire_date TIMESTAMP,
fire_date TIMESTAMP,
picture BYTEA
)
WITHOUT OIDS;
INSERT INTO employee ( name, salary, hire_date, fire_date ) VALUES
( 'Foo Johnson', 50, '2007-02-05 09:00:00', null ),
( 'Bar Mannson', 60, '2007-08-02 09:00:00', null ),
( 'Bruce Woolloomooloo', 70, '2007-08-15 08:00:00', '2008-06-09 15:00:00' ),
( 'Janus Larsen', 65, '2006-08-15 08:30:00', '2008-03-14 16:30:00' );
CREATE TABLE worklog (
id SERIAL PRIMARY KEY,
employee_id INT4 REFERENCES employee(id) DEFERRABLE NOT NULL,
workday DATE NOT NULL,
hours_worked NUMERIC NOT NULL
)
WITHOUT OIDS;
INSERT INTO worklog ( employee_id, workday, hours_worked ) VALUES
( 1, '2007-10-15', 5 ),
( 1, '2007-10-16', 6 ),
( 1, '2007-10-17', 7 ),
( 1, '2007-10-18', 8 ),
( 2, '2007-10-16', 2 ),
( 2, '2007-10-17', 7 ),
( 2, '2007-10-18', 3 ),
( 2, '2007-10-19', 5 );
*/
public class Testcase {
static final String SQL_QUERY =
"SELECT employee.id, name, salary, worklog.id AS worklog_id, workday, hours_worked\n"+
" FROM employee\n"+
" LEFT JOIN worklog ON ( employee.id = worklog.employee_id )";
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(
"jdbc:postgresql://localhost/postgres?user=tmdbc_sample&password=tmdbc2008");
}
public static void main(String[] args) throws Exception {
Connection conn = getConnection();
PreparedStatement stmnt = conn.prepareStatement(SQL_QUERY);
ResultSetMetaData rsmd = stmnt.getMetaData();
int columnCount = rsmd.getColumnCount();
System.out.println( "Metadata:" );
for( int i = 1; i <= columnCount; i++ ){
System.out.println( rsmd.getColumnLabel(i) +": "+ parseNullable( rsmd.isNullable(i) ) );
}
ResultSet rs = stmnt.executeQuery();
System.out.println( "Data:" );
for( int i = 1; i <= columnCount; i++ ){
if( i != 1 )
System.out.print( "," );
System.out.print( rsmd.getColumnLabel(i) );
}
System.out.println();
while( rs.next() ){
for( int i = 1; i <= columnCount; i++ ){
if( i != 1 )
System.out.print( "," );
System.out.print( rs.getString(i) );
}
System.out.println();
}
}
public static String parseNullable(int i){
switch(i){
case ResultSetMetaData.columnNoNulls: return "NoNulls";
case ResultSetMetaData.columnNullable: return "Nullable";
case ResultSetMetaData.columnNullableUnknown: return "NullableUnknown";
default:
throw new IllegalArgumentException();
}
}
}
Output:
Metadata:
id: NoNulls
name: NoNulls
salary: Nullable
worklog_id: NoNulls
workday: NoNulls
hours_worked: NoNulls
Data:
id,name,salary,worklog_id,workday,hours_worked
1,Foo Johnson,50.00,1,2007-10-15,5
1,Foo Johnson,50.00,2,2007-10-16,6
1,Foo Johnson,50.00,3,2007-10-17,7
1,Foo Johnson,50.00,4,2007-10-18,8
2,Bar Mannson,60.00,5,2007-10-16,2
2,Bar Mannson,60.00,6,2007-10-17,7
2,Bar Mannson,60.00,7,2007-10-18,3
2,Bar Mannson,60.00,8,2007-10-19,5
4,Janus Larsen,65.00,null,null,null
3,Bruce Woolloomooloo,70.00,null,null,null
Tested with postgresql-9.1-903.jdbc4
Thanks,
Thor Michael Støre
On 10/12/2012 01:25 AM, Thor Michael Støre wrote:
Hello,I've just noticed that ResultSetMetaData.isNullable(i) in the PostgreSQL JDBC driver doesn't reflect whether fields of result set column i may be null, but the nullability of the table column it's selected from. These may differ for result set columns that originate from a outer joined table, in which case fields may of course be null even if the source table column is not nullable, but in such cases isNullable still indicates the column is not nullable. Surely this isn't right?Although the spec doesn't explicitly distinguish between result set and source table only the former seems sensible to me -- what I expect the isNullable return value to reflect is whether an invocation of the ResultSet.getXXX(i) methods always returns an object (or sensible primitive), if they may return null or if that's unknown.
It'll be interesting to see how other major vendors' drivers handle this.
Thanks for the test case; I'll try to find some other DBs to run it on.
--
Craig Ringer
I am fairly sure that there is no way to tell from the result set if the column isNullable or not. Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Thu, Oct 11, 2012 at 9:21 PM, Craig Ringer <ringerc@ringerc.id.au> wrote: > On 10/12/2012 01:25 AM, Thor Michael Støre wrote: > > Hello, > > I've just noticed that ResultSetMetaData.isNullable(i) in the PostgreSQL > JDBC driver doesn't reflect whether fields of result set column i may be > null, but the nullability of the table column it's selected from. These may > differ for result set columns that originate from a outer joined table, in > which case fields may of course be null even if the source table column is > not nullable, but in such cases isNullable still indicates the column is not > nullable. Surely this isn't right? > > http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSetMetaData.html#isNullable(int) > > Although the spec doesn't explicitly distinguish between result set and > source table only the former seems sensible to me -- what I expect the > isNullable return value to reflect is whether an invocation of the > ResultSet.getXXX(i) methods always returns an object (or sensible > primitive), if they may return null or if that's unknown. > > > It'll be interesting to see how other major vendors' drivers handle this. > > Thanks for the test case; I'll try to find some other DBs to run it on. > > -- > Craig Ringer