diff options
author | Igor Babaev <igor@askmonty.org> | 2017-06-29 20:50:07 -0700 |
---|---|---|
committer | Igor Babaev <igor@askmonty.org> | 2017-06-29 20:50:26 -0700 |
commit | 9f0c1c0cf6a24a8613b3327c6f52a7d954e0e18b (patch) | |
tree | 30216680eca4a9c47dda7b9a1096b1d6a79d1000 | |
parent | 84e4e4506ffee95d71f1cac916176e9b64ba1bd8 (diff) | |
download | mariadb-git-9f0c1c0cf6a24a8613b3327c6f52a7d954e0e18b.tar.gz |
Fixed the bug mdev-13193.
When an equality that can be pushed into a materialized derived
table / view is extracted from multiple equalities and their
operands are cloned then if they have some pointers to Item_equal
objects those pointers must be set to NULL in the clones. Anyway
they are not valid in the pushed predicates.
-rw-r--r-- | mysql-test/r/derived_cond_pushdown.result | 57 | ||||
-rw-r--r-- | mysql-test/r/derived_view.result | 2 | ||||
-rw-r--r-- | mysql-test/t/derived_cond_pushdown.test | 22 | ||||
-rw-r--r-- | sql/table.cc | 4 |
4 files changed, 84 insertions, 1 deletions
diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result index c009a08b4f7..b94cb46acea 100644 --- a/mysql-test/r/derived_cond_pushdown.result +++ b/mysql-test/r/derived_cond_pushdown.result @@ -8709,3 +8709,60 @@ EXPLAIN } DROP VIEW v1; DROP TABLE t1; +# +# MDEV-13193: pushdown of equality extracted from multiple equality +# +CREATE TABLE t1 (i1 int, KEY(i1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (i2 int) ENGINE=MyISAM; +INSERT INTO t2 VALUES (2),(4); +CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; +SELECT * FROM t1, ( SELECT * FROM v2 ) AS sq +WHERE i1 = 1 AND ( i1 = i2 OR i1 = 2 ); +i1 i2 +explain format=json SELECT * FROM t1, ( SELECT * FROM v2 ) AS sq +WHERE i1 = 1 AND ( i1 = i2 OR i1 = 2 ); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ref", + "possible_keys": ["i1"], + "key": "i1", + "key_length": "5", + "used_key_parts": ["i1"], + "ref": ["const"], + "rows": 1, + "filtered": 100, + "using_index": true + }, + "block-nl-join": { + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "attached_condition": "v2.i2 = 1" + }, + "buffer_type": "flat", + "buffer_size": "256Kb", + "join_type": "BNL", + "materialized": { + "query_block": { + "select_id": 3, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "attached_condition": "t2.i2 = 1" + } + } + } + } + } +} +DROP VIEW v2; +DROP TABLE t1,t2; diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index f3964605b1a..df6ba084a87 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -1107,7 +1107,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ref a a 4 const 1 Using index 1 PRIMARY <derived2> ref key0 key0 8 const,const 1 -2 DERIVED t3 ALL NULL NULL NULL NULL 12 Using where; Using temporary; Using filesort +2 DERIVED t3 ALL NULL NULL NULL NULL 12 Using temporary; Using filesort SELECT * FROM t1, t2, v1 WHERE t2.a=t1.a AND t2.a=v1.a AND t2.a=v1.b; a a a b c c c c diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test index beeaa7350f7..de8a479614e 100644 --- a/mysql-test/t/derived_cond_pushdown.test +++ b/mysql-test/t/derived_cond_pushdown.test @@ -1526,3 +1526,25 @@ eval explain format=json $q; DROP VIEW v1; DROP TABLE t1; + +--echo # +--echo # MDEV-13193: pushdown of equality extracted from multiple equality +--echo # + +CREATE TABLE t1 (i1 int, KEY(i1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2 (i2 int) ENGINE=MyISAM; +INSERT INTO t2 VALUES (2),(4); + +CREATE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; + +let $q= +SELECT * FROM t1, ( SELECT * FROM v2 ) AS sq + WHERE i1 = 1 AND ( i1 = i2 OR i1 = 2 ); + +eval $q; +eval explain format=json $q; + +DROP VIEW v2; +DROP TABLE t1,t2; diff --git a/sql/table.cc b/sql/table.cc index 28fa34c5ad7..10a94dd8212 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -8237,8 +8237,12 @@ Item* TABLE_LIST::build_pushable_cond_for_table(THD *thd, Item *cond) Item *left_item_clone= left_item->build_clone(thd, thd->mem_root); Item *right_item_clone= item->build_clone(thd, thd->mem_root); if (left_item_clone && right_item_clone) + { + left_item_clone->set_item_equal(NULL); + right_item_clone->set_item_equal(NULL); eq= new (thd->mem_root) Item_func_eq(thd, right_item_clone, left_item_clone); + } if (eq) { i++; |