[#MDEV-15112] Inconsistent evaluation of spvariable=0 in strict mode

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.

[MDEV-15112] Inconsistent evaluation of spvariable=0 in strict mode Created: 2018-01-29 Updated: 2018-01-29 Resolved: 2018-01-29
Status:	Closed
Project:	MariaDB Server
Component/s:	Stored routines
Affects Version/s:	10.2
Fix Version/s:	10.3.5

[MDEV-15112] Inconsistent evaluation of spvariable=0 in strict mode Created: 2018-01-29 Updated: 2018-01-29 Resolved: 2018-01-29