diff options
author | Igor Babaev <igor@askmonty.org> | 2017-01-24 13:11:26 -0800 |
---|---|---|
committer | Igor Babaev <igor@askmonty.org> | 2017-01-24 13:12:20 -0800 |
commit | 423b7da36f1cbe692be449caa8879d37a41b333b (patch) | |
tree | a9a35d9882b482b4e83ca72e3fd15b5c937fa89a /mysql-test/r/derived_cond_pushdown.result | |
parent | 35760c00004615dfa44ba5735dc1f8f9abe24483 (diff) | |
download | mariadb-git-423b7da36f1cbe692be449caa8879d37a41b333b.tar.gz |
Fixed bug mdev-11820.
The fields st_select_lex::cond_pushed_into_where and
st_select_lex::cond_pushed_into_having should be re-initialized
for the unit specifying a derived table at every re-execution
of the query that uses this derived table, because the result
of condition pushdown may be different for different executions.
Diffstat (limited to 'mysql-test/r/derived_cond_pushdown.result')
-rw-r--r-- | mysql-test/r/derived_cond_pushdown.result | 109 |
1 files changed, 109 insertions, 0 deletions
diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result index 0be577a9f64..fd58ee038c7 100644 --- a/mysql-test/r/derived_cond_pushdown.result +++ b/mysql-test/r/derived_cond_pushdown.result @@ -8241,3 +8241,112 @@ SELECT * FROM v1 WHERE v1.d IN ( SELECT MIN(d) FROM t2 WHERE 0 ); d DROP VIEW v1; DROP TABLE t1,t2; +# +# MDEV-11820: second execution of PS for query +# with false subquery predicate in WHERE +# +CREATE TABLE t1 (c VARCHAR(3)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('foo'),('bar'); +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; +CREATE TABLE t2 (a INT); +INSERT INTO t2 VALUES (3), (4); +PREPARE stmt1 FROM +" SELECT * FROM v1 WHERE 1 IN (SELECT a FROM t2) OR c = 'foo'"; +PREPARE stmt2 FROM +"EXPLAIN FORMAT=JSON + SELECT * FROM v1 WHERE 1 IN (SELECT a FROM t2) OR c = 'foo'"; +EXECUTE stmt1; +c +foo +EXECUTE stmt2; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "attached_condition": "v1.c = 'foo'", + "materialized": { + "query_block": { + "select_id": 3, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "attached_condition": "t1.c = 'foo'" + } + } + } + }, + "subqueries": [ + { + "query_block": { + "select_id": 2, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "attached_condition": "1 = t2.a" + } + } + } + ] + } +} +INSERT INTO t2 SELECT a+1 FROM t2; +INSERT INTO t2 SELECT a+1 FROM t2; +INSERT INTO t2 SELECT a+1 FROM t2; +INSERT INTO t2 SELECT a+1 FROM t2; +INSERT INTO t2 SELECT a+1 FROM t2; +INSERT INTO t2 SELECT a+1 FROM t2; +EXECUTE stmt1; +c +foo +EXECUTE stmt2; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "attached_condition": "<in_optimizer>(1,<exists>(subquery#2)) or v1.c = 'foo'", + "materialized": { + "query_block": { + "select_id": 3, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 2, + "filtered": 100 + } + } + } + }, + "subqueries": [ + { + "query_block": { + "select_id": 2, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 128, + "filtered": 100, + "attached_condition": "1 = t2.a" + } + } + } + ] + } +} +DEALLOCATE PREPARE stmt1; +DEALLOCATE PREPARE stmt2; +DROP VIEW v1; +DROP TABLE t1,t2; |