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);
|
Bug
Major
