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

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

Ascending order - Click to sort in descending order

Alexander Barkov added a comment - 2016-03-23 05:17 - edited

Execution spends a lot of time in:

Item_func_nullif::update_used_tables()
Item_func_nullif::walk(&Item::eval_not_null_tables), called from st_select_lex::update_used_tables(), called from JOIN::optimize_inner()
Item_func_nullif::walk(&Item::exists2in_processor), called from JOIN::optimize_inner()
Item_func_nullif::walk(&Item::is_expensive_processor), called from Item::is_expensive(), called from Item::remove_eq_conds()
Item_func_nullfull::propagate_equal_fields()

Alexander Barkov added a comment - 2016-03-23 05:17 - edited Execution spends a lot of time in: Item_func_nullif::update_used_tables() Item_func_nullif::walk(&Item::eval_not_null_tables), called from st_select_lex::update_used_tables(), called from JOIN::optimize_inner() Item_func_nullif::walk(&Item::exists2in_processor), called from JOIN::optimize_inner() Item_func_nullif::walk(&Item::is_expensive_processor), called from Item::is_expensive(), called from Item::remove_eq_conds() Item_func_nullfull::propagate_equal_fields()

Alexander Barkov added a comment - 2016-03-23 05:33

In debug build it takes 02 min 24 sec to run the above query on a Intel® Core™ i7-3770 CPU @ 3.40GHz × 8 machine.

Alexander Barkov added a comment - 2016-03-23 05:33 In debug build it takes 02 min 24 sec to run the above query on a Intel® Core™ i7-3770 CPU @ 3.40GHz × 8 machine.

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.

MariaDB Server