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

Inconsistent evaluation of spvariable=0 in strict mode

    Details

      Description

      I create a table with a TIMESTAMP column and insert one record:

      SET sql_mode=STRICT_ALL_TABLES;
      CREATE OR REPLACE TABLE t1 (e TIMESTAMP(6));
      INSERT INTO t1 VALUES ('2001-01-01 10:20:30');
      

      Now I create some very similar stored functions:

      DELIMITER $$
      CREATE OR REPLACE FUNCTION f1(a VARBINARY(255))
      RETURNS INT
      DETERMINISTIC
      BEGIN
        RETURN a = timestamp'2038-01-19 03:14:07.999999'
            OR a = 0;
      END
      $$
      DELIMITER ;
       
      DELIMITER $$
      CREATE OR REPLACE FUNCTION f2(a VARBINARY(255))
      RETURNS INT
      DETERMINISTIC
      BEGIN
        RETURN a = 0;
      END
      $$
      DELIMITER ;
       
       
      DELIMITER $$
      CREATE OR REPLACE FUNCTION f3(a VARBINARY(255))
      RETURNS INT
      DETERMINISTIC
      BEGIN
        RETURN a = timestamp'2038-01-19 03:14:07.999999'
            OR a = sleep(0);
      END
      $$
      DELIMITER ;
      

      And finally run this script:

      SELECT f1(e) FROM t1;
      SELECT f2(e) FROM t1;
      SELECT f3(e) FROM t1;
      

      It returns the following output:

      MariaDB [test]> SELECT f1(e) FROM t1;
      +-------+
      | f1(e) |
      +-------+
      |     0 |
      +-------+
      1 row in set, 1 warning (0.00 sec)
       
      MariaDB [test]> SELECT f2(e) FROM t1;
      ERROR 1292 (22007): Truncated incorrect DOUBLE value: '2001-01-01 10:20:30'
      MariaDB [test]> SELECT f3(e) FROM t1;
      ERROR 1292 (22007): Truncated incorrect DOUBLE value: '2001-01-01 10:20:30'
      

      Notice:

      • f1 returned with a warning
      • f2 returned with an error
      • f3 returned with an error

      f1 seems to be wrong. It should also fail with the same error.

      The difference happens because in f1 the condition is evaluated at the optimization phase in Item_cond::fix_fields() called from sp_prepare_func_item() from sp_eval_expr(), before this code block:

        thd->count_cuted_fields= CHECK_FIELD_ERROR_FOR_NULL;                       │
        thd->abort_on_warning= thd->is_strict_mode();                              │
        thd->transaction.stmt.modified_non_trans_table= FALSE; 
      

      while in f2 and f3 it's evaluated in expr_item->save_in_field(), after the above code block.

      The sp_prepare_func_item() call should be moved after this code block, so both sp_prepare_func_item() and expr_item->save_in_field() are called with the same THD flags.

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: