[MDEV-22776] wrong (at least inconsistent) expression evaluation in SQL_MODE "STRICT_ALL_TABLES" Created: 2020-06-02  Updated: 2020-06-24

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Procedure, Documentation, OTHER
Affects Version/s: 10.4.13
Fix Version/s: None

Type: Bug Priority: Major
Reporter: L. Schwarz Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None
Environment:

docker image "mariadb/server:10.4" on linux ubuntu 16.04.

Attachments: File expression-eval-bug.sql    

 Description   

This issue applies to stored functions or procedures that calculate expressions containing flow-control sub-expression like IF(,,) or CASE ... WHEN ... THEN etc: The bug is that the expression calculation may fail because of errors in paths of the expression that were not executed at all.

I found out that it seems to happen only if SQL_MODE "STRICT_ALL_TABLES" was enabled when function/procedure was defined.

Should this be by design (I hope it's not), then there are this issues:

  1. I found nothing in the documentation about that behaviour; namely that sub-expressions may cause errors even if they are currently not really evaluated.
  2. The behaviour is inconsistent because it depends on the order of operands

How to reproduce (the attached expression-eval-bug.sql contains all this commands):

-- preparing the context:
 
MariaDB [(none)]> create database boog;
 
Query OK, 1 row affected (0.01 sec)
 
 
 
MariaDB [(none)]> use boog;
 
Database changed
 
 
 
-- Define functions in non-strict mode:
 
MariaDB [boog]> set sql_mode="";
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
-- function t1 and t2 should calculate the same results; they only differ
 
-- in the IF(,,,) assuming IF(cc, aa, bb) === IF(NOT cc, bb, aa):
 
 
 
MariaDB [boog]> create function t1_tolrnt(JJ text) returns text return IF(JJ!="", JSON_TYPE(JJ), "NOPE");
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
MariaDB [boog]> create function t2_tolrnt(JJ text) returns text return IF(JJ="", "NOPE", JSON_TYPE(JJ));
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
-- function t3 checks the IF-operand before actually doing the IF:
 
 
 
MariaDB [boog]> create function t3_tolrnt(s text) returns text return case when s not regexp "^[0-9]+$" then s else if(s, "Y", "N") end;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
-- Define the same functions in strict mode:
 
MariaDB [boog]> set sql_mode="strict_all_tables";
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [boog]> create function t1_strict(JJ text) returns text return IF(JJ!="", JSON_TYPE(JJ), "NOPE");
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [boog]> create function t2_strict(JJ text) returns text return IF(JJ="", "NOPE", JSON_TYPE(JJ));
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [boog]> create function t3_strict(s text) returns text return case when s not regexp "^[0-9]+$" then s else if(s, "Y", "N") end;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
-- Now for the checks:
 
 
 
MariaDB [boog]> select t1_tolrnt("");
 
+---------------+
 
| t1_tolrnt("") |
 
+---------------+
 
| NOPE          |
 
+---------------+
 
1 row in set (0.00 sec)
 
-- this result is expected
 
 
 
MariaDB [boog]> select t2_tolrnt("");
 
+---------------+
 
| t2_tolrnt("") |
 
+---------------+
 
| NOPE          |
 
+---------------+
 
1 row in set, 1 warning (0.00 sec)
 
-- this result is expected
 
 
 
MariaDB [boog]> select t3_tolrnt("a");
 
+----------------+
 
| t3_tolrnt("a") |
 
+----------------+
 
| a              |
 
+----------------+
 
1 row in set, 1 warning (0.00 sec)
 
-- this result is expected
 
 
 
MariaDB [boog]> select t1_strict("");
 
+---------------+
 
| t1_strict("") |
 
+---------------+
 
| NOPE          |
 
+---------------+
 
1 row in set (0.01 sec)
 
-- this result is expected
 
 
 
MariaDB [boog]> select t2_strict("");
 
ERROR 4037 (HY000): Unexpected end of JSON text in argument 1 to function 'json_type'
 
-- this result is NOT expected. The expected result is "NOPE"
 
-- This behaviour is at least inconsistent because t1_strict() calculates the same thing without errors.
 
 
 
MariaDB [boog]> select t3_strict("a");
 
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'a'
 
MariaDB [boog]> 
-- this result is NOT expected. The expected result is "a".



 Comments   
Comment by Alice Sherepa [ 2020-06-02 ]

I could not reproduce the issue with t2_tolrnt/t2_strict - both work for me on a freshly installed 10.4.13.

MariaDB [test]> set sql_mode="";
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> create function t2_tolrnt(JJ text) returns text return IF(JJ="", "NOPE", JSON_TYPE(JJ));
 
Query OK, 0 rows affected (0.004 sec)
 
 
 
MariaDB [test]> set sql_mode="strict_all_tables";
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> create function t2_strict(JJ text) returns text return IF(JJ="", "NOPE", JSON_TYPE(JJ));
 
Query OK, 0 rows affected (0.003 sec)
 
 
 
MariaDB [test]> select t2_tolrnt("");
 
+---------------+
 
| t2_tolrnt("") |
 
+---------------+
 
| NOPE          |
 
+---------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> select t2_strict("");
 
+---------------+
 
| t2_strict("") |
 
+---------------+
 
| NOPE          |
 
+---------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]> select version();
 
+-----------------+
 
| version()       |
 
+-----------------+
 
| 10.4.13-MariaDB |
 
+-----------------+
 
1 row in set (0.000 sec)

For the t3_tolrnt when sql_mode="" there is a warning, while with STRICT_ALL_TABLES it results in error, seems to be an expected behaviour.

 MariaDB [test]>   select t3_tolrnt("a");
 
+----------------+
 
| t3_tolrnt("a") |
 
+----------------+
 
| a              |
 
+----------------+
 
1 row in set, 1 warning (0.000 sec)
 
 
 
Warning (Code 1292): Truncated incorrect DOUBLE value: 'a'
 
 
 
MariaDB [test]>   select t3_strict("a");
 
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'a'
 
Error (Code 1292): Truncated incorrect DOUBLE value: 'a'
 
Note (Code 4094): At line 2 in test.t3_strict

Comment by L. Schwarz [ 2020-06-03 ]

Thank you for checking this.
The difference between a fresh 10.4.13 and my 10.4.13 are the default character sets. In the /etc/mysql/mariadb.cnf I enabled the suggested utf8 character sets. So far I didn't think the issue depends on character set; otherwise I would have mentioned this change.

To make test case independent from configuration parameters, I did some investigations. I found out that from all the character-set-settings it is the character_set_connection that is important here.

Here are the steps to reproduce the issue starting from a fresh 10.4.13 without modifying /etc/mysql/mariadb.cnf:

MariaDB [(none)]> set character_set_connection=utf8mb4;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [(none)]> show variables like '%char%';
 
+--------------------------+----------------------------+
 
| Variable_name            | Value                      |
 
+--------------------------+----------------------------+
 
| character_set_client     | latin1                     |
 
| character_set_connection | utf8mb4                    |
 
| character_set_database   | latin1                     |
 
| character_set_filesystem | binary                     |
 
| character_set_results    | latin1                     |
 
| character_set_server     | latin1                     |
 
| character_set_system     | utf8                       |
 
| character_sets_dir       | /usr/share/mysql/charsets/ |
 
+--------------------------+----------------------------+
 
8 rows in set (0.001 sec)
 
 
 
MariaDB [(none)]> create database test;
 
Query OK, 1 row affected (0.000 sec)
 
 
 
MariaDB [(none)]> use test;
 
Database changed
 
MariaDB [test]> set sql_mode="";
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> create function t1_tolrnt(JJ text) returns text return IF(JJ!="", JSON_TYPE(JJ), "NOPE");
 
Query OK, 0 rows affected (0.001 sec)
 
 
 
MariaDB [test]> create function t2_tolrnt(JJ text) returns text return IF(JJ="", "NOPE", JSON_TYPE(JJ));
 
Query OK, 0 rows affected (0.001 sec)
 
 
 
MariaDB [test]> create function t3_tolrnt(s text) returns text return case when s not regexp "^[0-9]+$" then s else if(s, "Y", "N") end;
 
Query OK, 0 rows affected (0.001 sec)
 
 
 
MariaDB [test]> -- Define the same functions in strict mode:
 
MariaDB [test]> set sql_mode="strict_all_tables";
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> create function t1_strict(JJ text) returns text return IF(JJ!="", JSON_TYPE(JJ), "NOPE");
 
Query OK, 0 rows affected (0.001 sec)
 
 
 
MariaDB [test]> create function t2_strict(JJ text) returns text return IF(JJ="", "NOPE", JSON_TYPE(JJ));
 
Query OK, 0 rows affected (0.002 sec)
 
 
 
MariaDB [test]> create function t3_strict(s text) returns text return case when s not regexp "^[0-9]+$" then s else if(s, "Y", "N") end;
 
Query OK, 0 rows affected (0.002 sec)
 
 
 
MariaDB [test]> select t1_strict("");
 
+---------------+
 
| t1_strict("") |
 
+---------------+
 
| NOPE          |
 
+---------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]> select t2_strict("");
 
ERROR 4037 (HY000): Unexpected end of JSON text in argument 1 to function 'json_type'
 
 
 
MariaDB [test]> select version();
 
+----------------------------------------+
 
| version()                              |
 
+----------------------------------------+
 
| 10.4.13-MariaDB-1:10.4.13+maria~bionic |
 
+----------------------------------------+

It does not only happen with utf8mb4:

MariaDB [test]> set character_set_connection=utf8;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> create function t6_utf8(s text) returns text return if(s="a", s , if(s, "Y", "N"));
 
Query OK, 0 rows affected (0.001 sec)
 
 
 
MariaDB [test]> select t6_utf8("a");
 
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'a'
 
-- this result is not expected. Expected is "a"

Regarding t3_tolrnt("a") and t3_strict("a"):

To be honest I didn't noticed the warning that t3_tolrnt("a") generates.
I agree that it is expected behaviour that STRICT_ALL_TABLES turns the warning to an error, which is what happend with t3_strict("a").
But why the warning?
Should the sub-expression IF(s, "Y", "N") really be executed/evaluated to calculate the result then I agree the warning is expected behaviour.
But there is a condition in front of it to prevent execution/evaluation of sub-expressions that would generate warnings (or errors).
Unfortunately this condition is not honored in some cases. And this, as it seems now, may depend on character set used when defining the function/procedure.

Comment by L. Schwarz [ 2020-06-24 ]

I provided some feedback and now removed the need_feedback label.

Generated at Thu Feb 08 09:17:23 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.