Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-14270

Dynamic CREATE TABLE does not preserve the data type of SP variables with NULL value

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.2(EOL), 10.3(EOL), 10.4(EOL)
    • 10.3(EOL)
    • None
    • None
    • 10.2.11

    Description

      DELIMITER $$
      		 
      BEGIN NOT ATOMIC
      		 
        DECLARE d0 DATETIME DEFAULT NULL;
      		 
        DECLARE d1 DATETIME DEFAULT '2001-01-01 00:00:00';
      		 
        DECLARE i0 INT DEFAULT NULL;
      		 
        DECLARE i1 INT DEFAULT 10;
      		 
        PREPARE stmt FROM 'CREATE OR REPLACE TABLE t1 AS SELECT ? AS c1,? AS c2,? AS c3,? AS c4';
      		 
        EXECUTE stmt USING d0,d1,i0,i1;
      		 
        DEALLOCATE PREPARE stmt;
      		 
        SHOW CREATE TABLE t1;
      		 
      END;
      		 
      $$
      		 
      DELIMITER ;


      +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      		 
      | Table | Create Table                                                                                                                                                                      |
      		 
      +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      		 
      | t1    | CREATE TABLE `t1` (
      		 
        `c1` binary(0) DEFAULT NULL,
      		 
        `c2` datetime DEFAULT NULL,
      		 
        `c3` binary(0) DEFAULT NULL,
      		 
        `c4` bigint(21) NOT NULL
      		 
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      		 
      +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      The expected data type for c0 should be DATETIME (like for c1).
      The expected data type for c3 should be BIGINT (like for c3).

      Attachments

        Issue Links

          is blocked by

          Task - A task that needs to be done. MDEV-14467 Item_param: replace {INT|DECIMAL|REAL|STRING|TIME}_VALUE with Type_handler

          • Major - Major loss of function.
          • Closed
          relates to

          Technical task - A technical task. MDEV-10709 Expressions as parameters to Dynamic SQL

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14271 Dynamic SQL: TIMESTAMP parameter value is not preserved on timezone change

          • Major - Major loss of function.
          • Open

          Activity

            bar Alexander Barkov added a comment - - edited

            The same problem is repeatable with functions returning NULL:

            DELIMITER $$
            		 
            BEGIN NOT ATOMIC
            		 
              PREPARE stmt FROM 'CREATE OR REPLACE TABLE t1 AS SELECT ? AS d0,? AS d1';
            		 
              EXECUTE stmt USING
            		 
                DATE_ADD(TIMESTAMP'9999-12-31 23:59:59', INTERVAL 0 SECOND),
            		 
                DATE_ADD(TIMESTAMP'9999-12-31 23:59:59', INTERVAL 1 SECOND);
            		 
              DEALLOCATE PREPARE stmt;
            		 
              SHOW CREATE TABLE t1;
            		 
            END;
            		 
            $$
            		 
            DELIMITER ;


            +-------+------------------------------------------------------------------------------------------------------------------------+
            		 
            | Table | Create Table                                                                                                           |
            		 
            +-------+------------------------------------------------------------------------------------------------------------------------+
            		 
            | t1    | CREATE TABLE `t1` (
            		 
              `d0` datetime DEFAULT NULL,
            		 
              `d1` binary(0) DEFAULT NULL
            		 
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
            		 
            +-------+------------------------------------------------------------------------------------------------------------------------+

            Notice, the data type for d1 is wrong. It should be datetime.

            Note, this script, with direct non-prepared execution, correctly creates two datetime columns:

            SET sql_mode='';
            		 
            CREATE OR REPLACE TABLE t1 AS SELECT
            		 
              DATE_ADD(TIMESTAMP'9999-12-31 23:59:59', INTERVAL 0 SECOND) AS d0,
            		 
              DATE_ADD(TIMESTAMP'9999-12-31 23:59:59', INTERVAL 1 SECOND) AS d1;
            		 
            SHOW CREATE TABLE t1;


            +-------+-----------------------------------------------------------------------------------------------------------------------+
            		 
            | Table | Create Table                                                                                                          |
            		 
            +-------+-----------------------------------------------------------------------------------------------------------------------+
            		 
            | t1    | CREATE TABLE `t1` (
            		 
              `d0` datetime DEFAULT NULL,
            		 
              `d1` datetime DEFAULT NULL
            		 
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
            		 
            +-------+-----------------------------------------------------------------------------------------------------------------------+

            bar Alexander Barkov added a comment - - edited The same problem is repeatable with functions returning NULL : DELIMITER $$ BEGIN NOT ATOMIC PREPARE stmt FROM 'CREATE OR REPLACE TABLE t1 AS SELECT ? AS d0,? AS d1' ; EXECUTE stmt USING DATE_ADD( TIMESTAMP '9999-12-31 23:59:59' , INTERVAL 0 SECOND ), DATE_ADD( TIMESTAMP '9999-12-31 23:59:59' , INTERVAL 1 SECOND ); DEALLOCATE PREPARE stmt; SHOW CREATE TABLE t1; END ; $$ DELIMITER ; +-------+------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `d0` datetime DEFAULT NULL, `d1` binary(0) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+------------------------------------------------------------------------------------------------------------------------+ Notice, the data type for d1 is wrong. It should be datetime . Note, this script, with direct non-prepared execution, correctly creates two datetime columns: SET sql_mode= '' ; CREATE OR REPLACE TABLE t1 AS SELECT DATE_ADD( TIMESTAMP '9999-12-31 23:59:59' , INTERVAL 0 SECOND ) AS d0, DATE_ADD( TIMESTAMP '9999-12-31 23:59:59' , INTERVAL 1 SECOND ) AS d1; SHOW CREATE TABLE t1; +-------+-----------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `d0` datetime DEFAULT NULL, `d1` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+-----------------------------------------------------------------------------------------------------------------------+

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.