revision-id: 862a97749d5a36218ba4d55c26eef30cd7b2e3cb (mariadb-10.2.16-93-g862a977) parent(s): 4eac5df3fcebb1adf52e33d9d88dc05bc1e339ce author: Igor Babaev committer: Igor Babaev timestamp: 2018-08-17 19:29:01 -0700 message: MDEV-17011 “condition_pushdown_for_derived” optimization not used when using INSERT INTO This patch allows condition pushdown into a materialized derived / view when this table is used in INSERT SELECT, multi-table UPDATE and multi-table DELETE. --- mysql-test/r/derived_cond_pushdown.result | 143 ++++++++++++++++++++++++++++++ mysql-test/t/derived_cond_pushdown.test | 44 +++++++++ sql/sql_select.cc | 3 +- 3 files changed, 188 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result index 22b81ef..839ae58 100644 --- a/mysql-test/r/derived_cond_pushdown.result +++ b/mysql-test/r/derived_cond_pushdown.result @@ -9874,3 +9874,146 @@ SELECT a FROM (SELECT "aa" a) t WHERE f1(t.a, (SELECT MAX('aa') FROM DUAL LIMIT a aa DROP FUNCTION f1; +# +# MDEV-17011: condition pushdown into materialized derived used +# in INSERT SELECT, multi-table UPDATE and DELETE +# +CREATE TABLE t1 (a int ,b int) ENGINE=MyISAM; +INSERT INTO t1 VALUES +(1, 1), (1, 2), (2, 1), (2, 2), (3,1), (3,3), (4,2); +CREATE TABLE t2 (a int) ENGINE MYISAM; +INSERT INTO t2 VALUES +(3), (7), (1), (4), (1); +CREATE TABLE t3 (a int, b int) ENGINE MYISAM; +EXPLAIN FORMAT=JSON INSERT INTO t3 +SELECT * FROM (SELECT a, count(*) as c FROM t1 GROUP BY a) t WHERE a<=2; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "rows": 7, + "filtered": 100, + "attached_condition": "t.a <= 2", + "materialized": { + "query_block": { + "select_id": 2, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 7, + "filtered": 100, + "attached_condition": "t1.a <= 2" + } + } + } + } + } + } + } +} +INSERT INTO t3 +SELECT * FROM (SELECT a, count(*) as c FROM t1 GROUP BY a) t WHERE a<=2; +SELECT * FROM t3; +a b +1 2 +2 2 +EXPLAIN FORMAT=JSON UPDATE t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t SET t2.a=t.c+10 +WHERE t2.a= t.c and t.a>=3; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t2.a is not null" + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "8", + "used_key_parts": ["c"], + "ref": ["test.t2.a"], + "rows": 2, + "filtered": 100, + "attached_condition": "t2.a = t.c and t.a >= 3", + "materialized": { + "query_block": { + "select_id": 2, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 7, + "filtered": 100, + "attached_condition": "t1.a >= 3" + } + } + } + } + } + } + } +} +UPDATE t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t SET t2.a=t.c+10 +WHERE t2.a= t.c and t.a>=3; +SELECT * FROM t2; +a +3 +7 +11 +4 +11 +EXPLAIN FORMAT=JSON DELETE t2 FROM t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t +WHERE t2.a= t.c+9 and t.a=2; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + }, + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "rows": 7, + "filtered": 100, + "attached_condition": "t.a = 2 and t2.a = t.c + 9", + "materialized": { + "query_block": { + "select_id": 2, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 7, + "filtered": 100, + "attached_condition": "t1.a = 2" + } + } + } + } + } +} +DELETE t2 FROM t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t +WHERE t2.a= t.c+9 and t.a=2; +SELECT * FROM t2; +a +3 +7 +4 +DROP TABLE t1,t2,t3; diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test index d9d767f..f85b301 100644 --- a/mysql-test/t/derived_cond_pushdown.test +++ b/mysql-test/t/derived_cond_pushdown.test @@ -1920,3 +1920,47 @@ END;$$ DELIMITER ;$$ SELECT a FROM (SELECT "aa" a) t WHERE f1(t.a, (SELECT MAX('aa') FROM DUAL LIMIT 1)); DROP FUNCTION f1; + +--echo # +--echo # MDEV-17011: condition pushdown into materialized derived used +--echo # in INSERT SELECT, multi-table UPDATE and DELETE +--echo # + +CREATE TABLE t1 (a int ,b int) ENGINE=MyISAM; +INSERT INTO t1 VALUES + (1, 1), (1, 2), (2, 1), (2, 2), (3,1), (3,3), (4,2); + +CREATE TABLE t2 (a int) ENGINE MYISAM; +INSERT INTO t2 VALUES + (3), (7), (1), (4), (1); + +CREATE TABLE t3 (a int, b int) ENGINE MYISAM; + +let $q1= +INSERT INTO t3 +SELECT * FROM (SELECT a, count(*) as c FROM t1 GROUP BY a) t WHERE a<=2; + +eval EXPLAIN FORMAT=JSON $q1; +eval $q1; + +SELECT * FROM t3; + +let $q2= +UPDATE t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t SET t2.a=t.c+10 + WHERE t2.a= t.c and t.a>=3; + +eval EXPLAIN FORMAT=JSON $q2; +eval $q2; + +SELECT * FROM t2; + +let $q3= +DELETE t2 FROM t2, (SELECT a, count(*) as c FROM t1 GROUP BY a) t + WHERE t2.a= t.c+9 and t.a=2; + +eval EXPLAIN FORMAT=JSON $q3; +eval $q3; + +SELECT * FROM t2; + +DROP TABLE t1,t2,t3; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 643ddfe..b94ca49 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1375,8 +1375,7 @@ JOIN::optimize_inner() DBUG_RETURN(1); } - if (thd->lex->sql_command == SQLCOM_SELECT && - optimizer_flag(thd, OPTIMIZER_SWITCH_COND_PUSHDOWN_FOR_DERIVED)) + if (optimizer_flag(thd, OPTIMIZER_SWITCH_COND_PUSHDOWN_FOR_DERIVED)) { TABLE_LIST *tbl; List_iterator_fast<TABLE_LIST> li(select_lex->leaf_tables);