[MDEV-14435] Different UNSIGNED flag of out user variable for YEAR parameter for direct vs prepared CALL Created: 2017-11-17  Updated: 2017-11-18  Resolved: 2017-11-18

Status: Closed
Project: MariaDB Server
Component/s: Prepared Statements
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3
Fix Version/s: 10.2.11, 10.3.3

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None


 Description    

DROP PROCEDURE IF EXISTS p1;
 
DROP TABLE IF EXISTS t1;
 
CREATE PROCEDURE p1(OUT v YEAR) SET v = 2010;
 
CALL p1(@a);
 
PREPARE stmt FROM 'CALL p1(?)';
 
EXECUTE stmt USING @b;
 
DEALLOCATE PREPARE stmt;
 
CREATE TABLE t1 AS SELECT @a AS a, @b AS b;
 
SHOW CREATE TABLE t1;


+-------+----------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                     |
 
+-------+----------------------------------------------------------------------------------------------------------------------------------+
 
| t1    | CREATE TABLE `t1` (
 
  `a` bigint(20) unsigned DEFAULT NULL,
 
  `b` bigint(20) DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
+-------+----------------------------------------------------------------------------------------------------------------------------------+

Notice, the column b is missing the unsigned flag.

 Comments   
Comment by Alexander Barkov [ 2017-11-17 ]

The same problem is repeatable with the INT UNSIGNED data type:

DROP PROCEDURE IF EXISTS p1;
 
DROP TABLE IF EXISTS t1;
 
CREATE PROCEDURE p1(OUT v INT UNSIGNED) SET v = 2010;
 
CALL p1(@a);
 
PREPARE stmt FROM 'CALL p1(?)';
 
EXECUTE stmt USING @b;
 
DEALLOCATE PREPARE stmt;
 
CREATE TABLE t1 AS SELECT @a AS a, @b AS b;
 
SHOW CREATE TABLE t1;


+-------+----------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                     |
 
+-------+----------------------------------------------------------------------------------------------------------------------------------+
 
| t1    | CREATE TABLE `t1` (
 
  `a` bigint(20) unsigned DEFAULT NULL,
 
  `b` bigint(20) DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
+-------+----------------------------------------------------------------------------------------------------------------------------------+

Comment by Alexander Barkov [ 2017-11-17 ]

The same problem is repeatable with the BIT data type:

DROP PROCEDURE IF EXISTS p1;
 
DROP TABLE IF EXISTS t1;
 
CREATE PROCEDURE p1(OUT v BIT(16)) SET v = 2010;
 
CALL p1(@a);
 
PREPARE stmt FROM 'CALL p1(?)';
 
EXECUTE stmt USING @b;
 
DEALLOCATE PREPARE stmt;
 
CREATE TABLE t1 AS SELECT @a AS a, @b AS b;
 
SHOW CREATE TABLE t1;


+-------+----------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                     |
 
+-------+----------------------------------------------------------------------------------------------------------------------------------+
 
| t1    | CREATE TABLE `t1` (
 
  `a` bigint(20) unsigned DEFAULT NULL,
 
  `b` bigint(20) DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
+-------+----------------------------------------------------------------------------------------------------------------------------------+

Generated at Thu Feb 08 08:13:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.