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

Performance degradation of nested NULLIF

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.1
    • Fix Version/s: 10.1.14
    • Component/s: Optimizer
    • Labels:
      None
    • Sprint:
      10.1.14

      Description

      Something appears to be wrong with processing of nested NULLIF in 10.1. The example below is exaggerated to the point of being senseless intentionally, to show the clear difference in query execution time.

      10.1

      MariaDB [test]> CREATE TABLE t1 (i INT);
      Query OK, 0 rows affected (0.09 sec)
       
      MariaDB [test]> INSERT INTO t1 VALUES (1),(2);
      Query OK, 2 rows affected (0.00 sec)
      Records: 2  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> 
      MariaDB [test]> SELECT * FROM t1 WHERE 
          -> NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(
          ->     NULLIF(1,1),
          ->     NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(1,1),1),1),1),1),1),1),1),1),1)
          -> ),1),1),1),1),1),1),1),1),1),1),1),1),1),1),1),1),1),1);
      Empty set (1 min 10.76 sec)
       
      MariaDB [test]> select @@version;
      +-----------------+
      | @@version       |
      +-----------------+
      | 10.1.12-MariaDB |
      +-----------------+
      1 row in set (0.00 sec)
      

      MySQL 5.6

      MySQL [test]> CREATE TABLE t1 (i INT);
      Query OK, 0 rows affected (0.28 sec)
       
      MySQL [test]> INSERT INTO t1 VALUES (1),(2);
      Query OK, 2 rows affected (0.00 sec)
      Records: 2  Duplicates: 0  Warnings: 0
       
      MySQL [test]> 
      MySQL [test]> SELECT * FROM t1 WHERE 
          -> NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(
          ->     NULLIF(1,1),
          ->     NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(1,1),1),1),1),1),1),1),1),1),1)
          -> ),1),1),1),1),1),1),1),1),1),1),1),1),1),1),1),1),1),1);
      Empty set (0.02 sec)
       
      MySQL [test]> select @@version;
      +-----------+
      | @@version |
      +-----------+
      | 5.6.28    |
      +-----------+
      1 row in set (0.00 sec)
      

      10.0

      MySQL [test]> CREATE TABLE t1 (i INT);
      Query OK, 0 rows affected (0.28 sec)
       
      MariaDB [test]> INSERT INTO t1 VALUES (1),(2);
      Query OK, 2 rows affected (0.39 sec)
      Records: 2  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> 
      MariaDB [test]> SELECT * FROM t1 WHERE 
          -> NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(
          ->     NULLIF(1,1),
          ->     NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(1,1),1),1),1),1),1),1),1),1),1)
          -> ),1),1),1),1),1),1),1),1),1),1),1),1),1),1),1),1),1),1);
      Empty set (0.02 sec)
       
      MariaDB [test]> select @@version;
      +-----------------+
      | @@version       |
      +-----------------+
      | 10.0.24-MariaDB |
      +-----------------+
      1 row in set (0.00 sec)
      

      Test case

      CREATE TABLE t1 (i INT);
      INSERT INTO t1 VALUES (1),(2);
       
      SELECT * FROM t1 WHERE 
      NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(
          NULLIF(1,1),
          NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(1,1),1),1),1),1),1),1),1),1),1)
      ),1),1),1),1),1),1),1),1),1),1),1),1),1),1),1),1),1),1);
      

        Attachments

          Activity

            People

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

              Dates

              Created:
              Updated:
              Resolved: