[MDEV-9712] Performance degradation of nested NULLIF - Jira

XML

Word

Printable

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.1(EOL)
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:: Alexander Barkov

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2016-03-11 16:08

Updated:: 2016-05-09 10:10

Resolved:: 2016-05-05 11:45

Git Integration

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