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

wrong (at least inconsistent) expression evaluation in SQL_MODE "STRICT_ALL_TABLES"

    XMLWordPrintable

Details

    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".
      

      Attachments

        Activity

          People

            Unassigned Unassigned
            yablacky L. Schwarz
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.