Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4.13
-
None
-
None
-
docker image "mariadb/server:10.4" on linux ubuntu 16.04.
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:
- I found nothing in the documentation about that behaviour; namely that sub-expressions may cause errors even if they are currently not really evaluated.
- 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". |