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

          Activity

            bar Alexander Barkov created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value
            bar Alexander Barkov made changes -
            serg Sergei Golubchik made changes -
            Sprint 10.2.11 [ 203 ]
            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 | +-------+-----------------------------------------------------------------------------------------------------------------------+
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Affects Version/s 10.4 [ 22408 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 83607 ] MariaDB v4 [ 140498 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.2 [ 14601 ]

            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.