diff options
| author | Igor Babaev <igor@askmonty.org> | 2017-09-09 20:35:05 -0700 |
|---|---|---|
| committer | Igor Babaev <igor@askmonty.org> | 2017-09-09 20:35:05 -0700 |
| commit | 61074d0426be2cae821cb18ae6bf5818a42ef27c (patch) | |
| tree | 6f6ee3b444e3623f8ab528d4fb4784b1a0a28752 | |
| parent | 5e4aa1a2f8a4c2be30bac06717505b3591af7965 (diff) | |
| download | mariadb-git-61074d0426be2cae821cb18ae6bf5818a42ef27c.tar.gz | |
Fixed the bug mdev-13710.
This patch corrects the code of the patch for mdev-13369 that
introduced the splitting technique when using materialized
derived tables / views with GROUP BY. The second actual parameters
of the call of the method JOIN::reoptimize() in the function
JOIN::push_splitting_cond_into_derived() was calculated incorrectly.
This could cause different failures for queries using derived tables
or views with GROUP BY when their FROM lists contained empty or
single-row tables.
| -rw-r--r-- | mysql-test/r/derived_cond_pushdown.result | 16 | ||||
| -rw-r--r-- | mysql-test/t/derived_cond_pushdown.test | 22 | ||||
| -rw-r--r-- | sql/sql_select.cc | 6 |
3 files changed, 40 insertions, 4 deletions
diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result index 925feb5411d..fea62302343 100644 --- a/mysql-test/r/derived_cond_pushdown.result +++ b/mysql-test/r/derived_cond_pushdown.result @@ -10149,3 +10149,19 @@ EXPLAIN } DROP VIEW v1; DROP TABLE t1,t2,t3,t4; +# +# MDEV-13710: Optimization for equi-joins of grouping derived tables +# (Splitting derived tables / views with GROUP BY) : +# FROM list of the derived table contains constant tables +# +CREATE TABLE t1 (a int, INDEX(a)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (9),(5),(1); +CREATE TABLE t2 (b int) ENGINE=MyISAM; +CREATE TABLE t3 (c varchar(8), d int) ENGINE=MyISAM; +INSERT INTO t3 VALUES ('foo',2),('bar',6); +CREATE VIEW v1 AS SELECT a FROM t1, t2 GROUP BY a; +SELECT * FROM t3 +WHERE d IN ( SELECT * FROM v1 ) AND c LIKE 'z%' OR c IS NULL; +c d +DROP VIEW v1; +DROP TABLE t1,t2,t3; diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test index 1919b4a1cc1..66d8ba3efa9 100644 --- a/mysql-test/t/derived_cond_pushdown.test +++ b/mysql-test/t/derived_cond_pushdown.test @@ -1794,3 +1794,25 @@ eval explain format=json $q1; DROP VIEW v1; DROP TABLE t1,t2,t3,t4; + +--echo # +--echo # MDEV-13710: Optimization for equi-joins of grouping derived tables +--echo # (Splitting derived tables / views with GROUP BY) : +--echo # FROM list of the derived table contains constant tables +--echo # + +CREATE TABLE t1 (a int, INDEX(a)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (9),(5),(1); + +CREATE TABLE t2 (b int) ENGINE=MyISAM; + +CREATE TABLE t3 (c varchar(8), d int) ENGINE=MyISAM; +INSERT INTO t3 VALUES ('foo',2),('bar',6); + +CREATE VIEW v1 AS SELECT a FROM t1, t2 GROUP BY a; + +SELECT * FROM t3 + WHERE d IN ( SELECT * FROM v1 ) AND c LIKE 'z%' OR c IS NULL; + +DROP VIEW v1; +DROP TABLE t1,t2,t3; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 530dc471b7c..ad7e333d41b 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -9073,12 +9073,10 @@ bool JOIN::push_splitting_cond_into_derived(THD *thd, Item *cond) { enum_reopt_result reopt_result= REOPT_NONE; table_map all_table_map= 0; - for (JOIN_TAB *tab= join_tab + const_tables; + for (JOIN_TAB *tab= join_tab; tab < join_tab + top_join_tab_count; tab++) - { all_table_map|= tab->table->map; - } - reopt_result= reoptimize(cond, all_table_map, NULL); + reopt_result= reoptimize(cond, all_table_map & ~const_table_map, NULL); if (reopt_result == REOPT_ERROR) return true; if (inject_cond_into_where(cond)) |