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

Automatic statement repreparation changes query semantics

    Details

      Description

      This script:

      SET sql_mode=DEFAULT;
      CREATE OR REPLACE TABLE t1 (a TEXT);
      PREPARE stmt FROM 'INSERT INTO t1 (a) VALUES (2||3)';
      EXECUTE stmt;
       
      SET sql_mode=ORACLE;
      EXECUTE stmt;
       
      ALTER TABLE t1 ADD b INT;
      EXECUTE stmt;
       
      SELECT * FROM t1;
      

      returns:

      +------+------+
      | a    | b    |
      +------+------+
      | 1    | NULL |
      | 1    | NULL |
      | 23   | NULL |
      +------+------+
      

      Notice, changing sql_mode between EXECUTE and PREPARE does not change the result, || is still treated as boolean OR in the second EXECUTE. However, as soon as I do ALTER TABLE, it forces automatic statement repreparation and the meaning of || in the third EXECUTE changes to concatenation.

      This should be fixed to remember sql_mode with the prepared statement, so when the automatic repreparation happens it uses the same sql_mode which was used during PREPARE.

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: