diff options
author | Igor Babaev <igor@askmonty.org> | 2018-01-18 09:20:55 -0800 |
---|---|---|
committer | Igor Babaev <igor@askmonty.org> | 2018-01-18 09:57:25 -0800 |
commit | 4f96b401d9dd9f876c2d3e6e266e8670d30ca2c8 (patch) | |
tree | 335a4d4c9b8023cac033853a43d7fc9528a582a4 | |
parent | fafdac3365f4943e73bcefd0e0d07d69997a9724 (diff) | |
download | mariadb-git-4f96b401d9dd9f876c2d3e6e266e8670d30ca2c8.tar.gz |
Fixed mdev-14960 [ERROR] mysqld got signal 11 with join_buffer and join_cache
In the function JOIN::shrink_join_buffers the iteration over joined
tables was organized in a wrong way. This could cause a crash if
the optimizer chose to materialize a semi-join that used join caches
for which the sizes must be adjusted.
-rw-r--r-- | mysql-test/r/join_cache.result | 58 | ||||
-rw-r--r-- | mysql-test/t/join_cache.test | 45 | ||||
-rw-r--r-- | sql/sql_select.cc | 5 |
3 files changed, 107 insertions, 1 deletions
diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index cc64393f975..386f7119bc8 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -5813,4 +5813,62 @@ id select_type table type possible_keys key key_len ref rows Extra set join_buffer_size=default; set join_cache_level = default; DROP TABLE t1,t2; +# +# MDEV-14960: BNLH used for materialized semi-join +# +CREATE TABLE t1 (i1 int); +CREATE TABLE t2 (e1 int); +CREATE TABLE t4 (e1 int); +CREATE TABLE t5 (e1 int); +INSERT INTO t1 VALUES +(1),(2),(3),(4),(5),(6),(7),(8); +INSERT INTO t1 SELECT i1+8 FROM t1; +INSERT INTO t1 SELECT i1+16 FROM t1; +INSERT INTO t1 SELECT i1+32 FROM t1; +INSERT INTO t1 SELECT i1+64 FROM t1; +INSERT INTO t2 SELECT * FROM t1; +INSERT INTO t4 SELECT * FROM t1; +INSERT INTO t5 SELECT * FROM t1; +set @save_optimizer_switch= @@optimizer_switch; +SET join_cache_level = 6; +SET join_buffer_size=4096; +SET join_buffer_space_limit=4096; +SET optimizer_switch = 'join_cache_hashed=on,optimize_join_buffer_size=on'; +EXPLAIN SELECT * FROM t1 +WHERE +i1 < 10 AND +i1 IN +(SELECT i1 FROM +(SELECT (t4.e1) i1 FROM t4 +LEFT JOIN t5 ON t4.e1 = t5.e1 +LEFT JOIN (SELECT e1 FROM t2 ) AS d ON t4.e1 = d.e1) a); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 128 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +2 MATERIALIZED t4 ALL NULL NULL NULL NULL 128 +2 MATERIALIZED t5 hash_ALL NULL #hash#$hj 5 test.t4.e1 128 Using where; Using join buffer (flat, BNLH join) +2 MATERIALIZED t2 hash_ALL NULL #hash#$hj 5 test.t4.e1 128 Using where; Using join buffer (incremental, BNLH join) +SELECT * FROM t1 +WHERE +i1 < 10 AND +i1 IN +(SELECT i1 FROM +(SELECT (t4.e1) i1 FROM t4 +LEFT JOIN t5 ON t4.e1 = t5.e1 +LEFT JOIN (SELECT e1 FROM t2 ) AS d ON t4.e1 = d.e1) a); +i1 +1 +2 +3 +4 +5 +6 +7 +8 +9 +SET join_cache_level = default; +SET join_buffer_size = default; +SET join_buffer_space_limit= default; +set optimizer_switch=@save_optimizer_switch; +DROP TABLE t1,t4,t5,t2; set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test index 77e8fce0d27..58a7b885356 100644 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -3791,5 +3791,50 @@ set join_cache_level = default; DROP TABLE t1,t2; +--echo # +--echo # MDEV-14960: BNLH used for materialized semi-join +--echo # + +CREATE TABLE t1 (i1 int); +CREATE TABLE t2 (e1 int); +CREATE TABLE t4 (e1 int); +CREATE TABLE t5 (e1 int); + +INSERT INTO t1 VALUES + (1),(2),(3),(4),(5),(6),(7),(8); +INSERT INTO t1 SELECT i1+8 FROM t1; +INSERT INTO t1 SELECT i1+16 FROM t1; +INSERT INTO t1 SELECT i1+32 FROM t1; +INSERT INTO t1 SELECT i1+64 FROM t1; +INSERT INTO t2 SELECT * FROM t1; +INSERT INTO t4 SELECT * FROM t1; +INSERT INTO t5 SELECT * FROM t1; + +set @save_optimizer_switch= @@optimizer_switch; +SET join_cache_level = 6; +SET join_buffer_size=4096; +SET join_buffer_space_limit=4096; +SET optimizer_switch = 'join_cache_hashed=on,optimize_join_buffer_size=on'; + +let $q= +SELECT * FROM t1 +WHERE + i1 < 10 AND + i1 IN + (SELECT i1 FROM + (SELECT (t4.e1) i1 FROM t4 + LEFT JOIN t5 ON t4.e1 = t5.e1 + LEFT JOIN (SELECT e1 FROM t2 ) AS d ON t4.e1 = d.e1) a); + +eval EXPLAIN $q; +eval $q; + +SET join_cache_level = default; +SET join_buffer_size = default; +SET join_buffer_space_limit= default; +set optimizer_switch=@save_optimizer_switch; + +DROP TABLE t1,t4,t5,t2; + # this must be the last command in the file set @@optimizer_switch=@save_optimizer_switch; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 9f89a261540..42b3420a9b6 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2085,8 +2085,11 @@ bool JOIN::shrink_join_buffers(JOIN_TAB *jt, ulonglong curr_space, ulonglong needed_space) { + JOIN_TAB *tab; JOIN_CACHE *cache; - for (JOIN_TAB *tab= join_tab+const_tables; tab < jt; tab++) + for (tab= first_linear_tab(this, WITHOUT_BUSH_ROOTS, WITHOUT_CONST_TABLES); + tab != jt; + tab= next_linear_tab(this, tab, WITHOUT_BUSH_ROOTS)) { cache= tab->cache; if (cache) |