Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Null value incorrectly returned with native (local) protocol [JDBC271] #318

Closed
firebird-automations opened this issue Aug 25, 2012 · 17 comments

Comments

@firebird-automations
Copy link

Submitted by: @mrotteveel

Is related to CORE3913
Is related to CORE3475

Votes: 1

http://stackoverflow.com/q/12106422/466862

I'm running into a bit of a situation in corner cases when binding null to a PreparedStatement with Firebird's jaybird JDBC driver. Here's a sample statement:

Class.forName("org.firebirdsql.jdbc.FBDriver");

// Observe the "local" in the connection string!
Connection con = DriverManager.getConnection(
"jdbc:firebirdsql:local:C:/data/firebird/test.db", "TEST", "TEST");

// With this connection, I'm not able to reproduce the issue:
Connection con1 = DriverManager.getConnection(
"jdbc:firebirdsql:localhost:C:/data/firebird/test.db", "TEST", "TEST");

PreparedStatement stmt = con.prepareStatement(
"SELECT cast(? as varchar(1)) FROM rdb$database");
stmt.setObject(1, null);
ResultSet rs = stmt.executeQuery();
rs.next();
System.out.println(rs.getString(1));
System.out.println(rs.wasNull());

The output of the above program is

>
> false

The first line being an empty string. It really should be

> null
> true

Changing this line ...

stmt.setObject(1, null);

... into any of these lines ...

stmt.setString(1, null);
stmt.setNull(1, Types.VARCHAR);

... doesn't help either. A workaround is to inline null literals in SQL statements, instead of binding them to the prepared statement. What am I missing?

Details:

Database: Firebird WI\-V2\.5\.1\.26351
JDBC driver: jaybird\-2\.2\.0
Java version: JDK 1\.6\.0\_24
OS: Windows 7 x64
JDBC Connection String: See above\.

Commits: a7ac58e 627d124 988495e 606618d fa9b02e 01aee3a

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

Reproducible with jdbc:firebirdsql:local:D:/data/DB/testdatabase.fdb and jdbc:firebirdsql:native://localhost/D:/data/DB/testdatabase.fdb

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

If I insert into a table and read it back, then it does work:
public class BindValues2 {
private static final String CON_STRING_1 = "jdbc:firebirdsql://localhost/D:/data/DB/testdatabase.fdb";
private static final String CON_STRING_2 = "jdbc:firebirdsql:native://localhost/D:/data/DB/testdatabase.fdb";
private static final String CON_STRING_3 = "jdbc:firebirdsql:local:D:/data/DB/testdatabase.fdb";

public static void main\(String\[\] args\) throws SQLException \{
	try \(Connection con = DriverManager\.
			getConnection\(CON\_STRING\_3, "sysdba", "masterkey"\)\) \{
		PreparedStatement stmt = con
				\.prepareStatement\("INSERT INTO single\_varchar \(varcharcolumn\) VALUES \(?\)"\);
		stmt\.setObject\(1, null\);
		stmt\.execute\(\);
		stmt\.close\(\);
		stmt = con\.prepareStatement\("SELECT varcharcolumn FROM single\_varchar"\);
		ResultSet rs = stmt\.executeQuery\(\);
		rs\.next\(\);
		System\.out\.println\(rs\.getString\(1\)\);
		System\.out\.println\(rs\.wasNull\(\)\);
	\}
\}

}

Stepping through the code I see that the JNI layer sets sqlind to -1, but it also sets sqldata to a 4 byte array.

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

Executing:
stmt = con.prepareStatement("SELECT varcharcolumn FROM single_varchar WHERE varcharcolumn = ? OR ? IS NULL");
stmt.setObject(1, null);
stmt.setObject(2, null);
ResultSet rs = stmt.executeQuery();

Also yields the expected result, which makes me suspect that it is (at least partially) a bug in Firebird when the parameter is part of the select list.

@firebird-automations
Copy link
Author

Commented by: Lukas Eder (lukas.eder)

I think it is somehow related to the casting. Here's how to reproduce the issue in a similar fashion:

    con\.createStatement\(\)\.execute\("create table single\_int \(i integer\)"\);
    con\.createStatement\(\)\.execute\("insert into single\_int values \(1\)"\);
    PreparedStatement stmt = con\.prepareStatement\("update single\_int set i = cast\(? as integer\)"\);
    stmt\.setNull\(1, Types\.INTEGER\);
    stmt\.execute\(\);
    ResultSet rs = con\.createStatement\(\)\.executeQuery\("select \* from single\_int"\);
    rs\.next\(\);
    System\.out\.println\(rs\.getString\(1\)\);
    System\.out\.println\(rs\.wasNull\(\)\);

The above update statement does not actually update a NULL value into the table, but zero.

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

Looks like you are right, changing the query from my previous comment to:
stmt = con.prepareStatement("SELECT varcharcolumn FROM single_varchar WHERE varcharcolumn = CAST(? AS VARCHAR(1)) OR CAST(? AS VARCHAR(1)) IS NULL");

Will yield no rows with the native implementation, but it will yield rows with the java implementation.

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

Firebird uses a nullable bit in the sqltype of an xsqlvar to indicate if a column or parameter is nullable or not. When the parameter is described as not nullable, then the Java implementation of the wire protocol will simply send the parameter as if it is nullable, the native client library (fbclient.dll, http://libfbclient.so) on the other hand does not ignore the nullable bit and if it receives an xsqlvar that is setup to contain NULL, but has the nullable bit not set, then it will send the parameter as its non-null counterpart (eg empty string for a VARCHAR, 0 for INT).

The solution to this problem is to have Jaybird flip on the nullable bit of sqltype when the parameter to be sent is NULL.

I have also filed CORE3913 as an improvement request to have the fbclient library give precedence to the value sqlind over the nullabe bit of the sqltype when sending parameters from client to server.

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Link: This issue is related to CORE3913 [ CORE3913 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Link: This issue is related to CORE3475 [ CORE3475 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Fix Version: Jaybird 2.2.1 [ 10474 ]

Fix Version: Jaybird 2.3 [ 10440 ]

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

Committed fix in trunk, will need to add additional tests and backport to 2.2.1

@firebird-automations
Copy link
Author

Commented by: Lukas Eder (lukas.eder)

Awesome, thanks!

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

Reopened: while testing Jaybird 2.2.1 with native protocol under Java 5 the tests fail, placing the original jaybird22.dll makes the tests pass.

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

assignee: Roman Rokytskyy [ rrokytskyy ] => Mark Rotteveel [ avalanche1979 ]

status: Resolved [ 5 ] => Reopened [ 4 ]

resolution: Fixed [ 1 ] =>

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

Some additional changes I made created a buffer overflow in some cases. Reverted most of those changes and focussed on flipping the nullable bit.

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

status: Reopened [ 4 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment