[CONJ-720] CallableStatements with Boolean OUT parameter Created: 2019-07-22  Updated: 2021-05-17  Resolved: 2021-05-17

Status: Closed
Project: MariaDB Connector/J
Component/s: MySQL compatibility
Affects Version/s: 2.2.5, 2.4.2
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Björn Raupach Assignee: Diego Dupin
Resolution: Won't Fix Votes: 0
Labels: None
Environment:

MySQL 5.6 database

Issue Links:
Relates
relates to MDEV-20212 Wrong stored procedure output BIT res... Stalled

 Description   

We observed an inconsistent behaviour from MySQL Connector/J to MariaDB Connector/J.

Steps to reproduce:

1) Create a SP that always returns 0 (false)

create procedure FalseFunc (OUT o_duplicate BIT(1)) begin set o_duplicate = 0; end

2) Create a Test case that calls the stored procedure

@Test
 
public void test_sp() throws SQLException {
 
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/mydb", "root", null);
 
CallableStatement cs = con.prepareCall("{call FalseFunc(?) }");
 
cs.registerOutParameter(1, Types.BOOLEAN);
 
cs.executeQuery();
 
 
 
boolean value = cs.getBoolean(1);
 
 
 
assertFalse(value);
 
 
 
con.close();
 
}

3) Calls the test case with the mariadb-java-client-2.2.5 and mysql-connector-java-5.1.47

4) The test case works with the MySQL JDBC Driver but fails with MariaDB JDBC Driver. It always returns true, even though it should return false.

 Comments   
Comment by Björn Raupach [ 2019-07-22 ]

If we change the OUT parameter to TINYINT(1) it works, it does not work with BIT(1).

Comment by Björn Raupach [ 2019-07-22 ]

Not sure if it helps, but if I look into BinaryRowProtocol.java and the method getInternalBoolean it checks if parseBit() != 0. I can only see parseBit returning 48 false and 49 true. Not 0. However I have no knowledge of the MySQL wire protocol. Just guessing.

Comment by Diego Dupin [ 2019-07-30 ]

I've created an issue server-side (MDEV-20212),
There is an issue for BIT parameter output when using a specific "binary" protocol that MariaDB driver use.
for your example parseBit() != 0 return false because, server return '0' ( = 0x30 = 48 ) not 0 (0x00).

A workaround is to use TINYINT(1) as you indicate.

Just for information:
mysql driver and MariaDB driver doesn't behave the same way at all for stored procedure with OUT parameter.

MariaDB connector will execute 

call other128(?,?)

MySQL under the hood will do more queries : 

SHOW CREATE PROCEDURE `testj`.`other128`
 
CALL other128(@com_mysql_jdbc_outparam_o_duplicate,@com_mysql_jdbc_outparam_o2) 
SELECT @com_mysql_jdbc_outparam_o_duplicate,@com_mysql_jdbc_outparam_o2

Comment by Diego Dupin [ 2021-05-17 ]

won't fix in connector, waiting for MDEV-20212

Generated at Thu Feb 08 03:17:49 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.