| Bug #72335 | INCORRECT BIT COLUMN VALUE RETURNED IN THE STORED PROCEDURE OUT PARAM | ||
|---|---|---|---|
| Submitted: | 14 Apr 2014 6:16 | Modified: | 21 Apr 2014 8:47 |
| Reporter: | Ashish Kumar | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S2 (Serious) |
| Version: | OS: | Any | |
| Assigned to: | CPU Architecture: | Any | |
[15 Apr 2014 9:55]
MySQL Verification Team
Hello Ashish,
Thank you for the report.
Could you try below SP?
delimiter //
DROP PROCEDURE IF EXISTS atstgt.SXRGXT_GETDESC;
CREATE PROCEDURE atstgt.SXRGXT_GETDESC (out flagout BIT(64))
BEGIN
select flag into flagout from atstgt.SXRGXT_XSYSTEM where STATE ='FL';
END //
delimiter ;
^^ removed BIN() from SELECT
Imho, you are copying into a BIT type var and hence no need to use bin(flag) explicitly..
// I tried it on 5.6.17 and works for me..
================================================
mysql> create database atstgt;
Query OK, 1 row affected (0.00 sec)
mysql> use atstgt;
Database changed
mysql>
mysql> DROP TABLE IF EXISTS atstgt.SXRGXT_XSYSTEM;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE atstgt.SXRGXT_XSYSTEM ( STATE CHAR(2),DESCRIPTION VARCHAR(50), flag bit(8) );
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO atstgt.SXRGXT_XSYSTEM VALUES ('FL' ,'Florida',b'1');
Query OK, 1 row affected (0.01 sec)
mysql> delimiter //
mysql> DROP PROCEDURE IF EXISTS atstgt.SXRGXT_GETDESC;
-> CREATE PROCEDURE atstgt.SXRGXT_GETDESC (out flagout BIT(64))
-> BEGIN
-> select flag into flagout from atstgt.SXRGXT_XSYSTEM where STATE ='FL';
-> END //
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call atstgt.SXRGXT_GETDESC(@flagout);
Query OK, 1 row affected (0.00 sec)
mysql> select @flagout;
+----------+
| @flagout |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
================================================
[21 Apr 2014 7:50]
Ashish Kumar
Thank you for update. So for using bit in stored procedure outparam , do we always need to define its length as 64 like below ? Will it not work for smaller lengths ? CREATE PROCEDURE atstgt.SXRGXT_GETDESC (out flagout BIT(64)) I still have to test it for the boundary values. Thanks,
[21 Apr 2014 8:47]
MySQL Verification Team
You may have to test boundary values, but if you assign a value to a BIT(64) column that is less than 64 bits long, the value is padded on the left with zeros. For example, assigning a value of b'101' to a BIT(6) column is, in effect, the same as assigning b'000101'. We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug. Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/ Thank you for your interest in MySQL.
Description: We have implemented SQLEXEC stored procedure in the OGG replicat. We are experiencing problems in stored procedure while getting the bit column data in the stored procedure out parameter. The same behavior we are seeing when using MySQL command line. If we try to return the bit column value in the stored procedure parameter , then instead of it giving the bit column value , it gives the ascii value of every bit field. In the data each bit field is preceded by 0x30. We get the same when trying on MySQL command line as below, CREATE TABLE atstgt.SXRGXT_XSYSTEM ( STATE CHAR(2) , DESCRIPTION VARCHAR(50), flag bit(8) ); INSERT INTO atstgt.SXRGXT_XSYSTEM VALUES ('FL' ,'Florida',b'1'); delimiter // DROP PROCEDURE IF EXISTS atstgt.SXRGXT_GETDESC; CREATE PROCEDURE atstgt.SXRGXT_GETDESC (out flagout BIT(8)) BEGIN select bin(flag) into flagout from atstgt.SXRGXT_XSYSTEM where STATE ='FL'; END // delimiter ; If I try to get the bit column value in a simple query , it is correct. mysql> select bin(flag) into @flagout from atstgt.SXRGXT_XSYSTEM where STATE =' FL'; Query OK, 1 row affected (0.00 sec) mysql> select @flagout; +----------+ | @flagout | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) But the same value if we try to retrieve using a stored procedure , it is not returning correct value, mysql> call atstgt.SXRGXT_GETDESC(@out); Query OK, 1 row affected (0.00 sec) mysql> select @out; +------+ | @out | +------+ | 49 | +------+ 1 row in set (0.00 sec) mysql> select bin(@out); +-----------+ | bin(@out) | +-----------+ | 110001 | +-----------+ 1 row in set (0.00 sec) Moreover , if we try to retrieve the bit column value of more than one bit , the stored procedure returns an error. In MySQL , we have bit field as 64 bits long and each bit can be set and retrieved. But in the stored procedure returns error if we try to retrieve more than 1 bit. mysql> insert into atstgt.SXRGXT_XSYSTEM VALUES ('FL' ,'Florida',b'11'); Query OK, 1 row affected (0.00 sec) mysql> call atstgt.SXRGXT_GETDESC(@out); ERROR 1406 (22001): Data too long for column 'flagout' at row 1 mysql> It works fine in a normal query, mysql> select bin(flag) into @flagout from atstgt.SXRGXT_XSYSTEM where STATE =' FL'; Query OK, 1 row affected (0.00 sec) mysql> select @flagout; +----------+ | @flagout | +----------+ | 11 | +----------+ 1 row in set (0.00 sec) I think this is a problem in MySQL stored procedure in returning the bit column values as an outparam. Please suggest the needful. Thanks, Ashish K. How to repeat: CREATE TABLE atstgt.SXRGXT_XSYSTEM ( STATE CHAR(2) , DESCRIPTION VARCHAR(50), flag bit(8) ); INSERT INTO atstgt.SXRGXT_XSYSTEM VALUES ('FL' ,'Florida',b'1'); delimiter // DROP PROCEDURE IF EXISTS atstgt.SXRGXT_GETDESC; CREATE PROCEDURE atstgt.SXRGXT_GETDESC (out flagout BIT(8)) BEGIN select bin(flag) into flagout from atstgt.SXRGXT_XSYSTEM where STATE ='FL'; END // delimiter ; If I try to get the bit column value in a simple query , it is correct. mysql> select bin(flag) into @flagout from atstgt.SXRGXT_XSYSTEM where STATE =' FL'; Query OK, 1 row affected (0.00 sec) mysql> select @flagout; +----------+ | @flagout | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) But the same value if we try to retrieve using a stored procedure , it is not returning correct value, mysql> call atstgt.SXRGXT_GETDESC(@out); Query OK, 1 row affected (0.00 sec) mysql> select @out; +------+ | @out | +------+ | 49 | +------+ 1 row in set (0.00 sec) mysql> select bin(@out); +-----------+ | bin(@out) | +-----------+ | 110001 | +-----------+ 1 row in set (0.00 sec) Moreover , if we try to retrieve the bit column value of more than one bit , the stored procedure returns an error. In MySQL , we have bit field as 64 bits long and each bit can be set and retrieved. But in the stored procedure returns error if we try to retrieve more than 1 bit. mysql> insert into atstgt.SXRGXT_XSYSTEM VALUES ('FL' ,'Florida',b'11'); Query OK, 1 row affected (0.00 sec) mysql> call atstgt.SXRGXT_GETDESC(@out); ERROR 1406 (22001): Data too long for column 'flagout' at row 1 mysql> It works fine in a normal query, mysql> select bin(flag) into @flagout from atstgt.SXRGXT_XSYSTEM where STATE =' FL'; Query OK, 1 row affected (0.00 sec) mysql> select @flagout; +----------+ | @flagout | +----------+ | 11 | +----------+ 1 row in set (0.00 sec) I think this is a problem in MySQL stored procedure in returning the bit column values as an outparam.