diff options
author | Igor Babaev <igor@askmonty.org> | 2010-11-19 06:20:28 -0800 |
---|---|---|
committer | Igor Babaev <igor@askmonty.org> | 2010-11-19 06:20:28 -0800 |
commit | 0a3922fca87b6f6122995f081ccab719e24354ca (patch) | |
tree | bdf636b1864aa4395c1a41142993f1514278dc62 | |
parent | e25ac681c935853b5b61dbac421d0b3b3c834475 (diff) | |
download | mariadb-git-0a3922fca87b6f6122995f081ccab719e24354ca.tar.gz |
Fixed LP #bug 660963.
The condition that was supposed to check whether a join table
is an inner table of a nested outer join or semi-join was not
quite correct in the code of the function check_join_cache_usage.
That's why some queries with nested outer joins triggered
an assertion failure.
Encapsulated this condition in the new method called
JOIN_TAB::is_nested_inner and provided a proper code for it.
Also corrected a bug in the code of check_join_cache_usage()
that caused a downgrade of not first join buffers of the
level 5 and 7 to level 4 and 6 correspondingly.
-rw-r--r-- | mysql-test/r/join_cache.result | 41 | ||||
-rw-r--r-- | mysql-test/t/join_cache.test | 30 | ||||
-rw-r--r-- | sql/sql_select.cc | 21 | ||||
-rw-r--r-- | sql/sql_select.h | 13 |
4 files changed, 90 insertions, 15 deletions
diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index d838b5bf7a4..36152af7898 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -2104,7 +2104,7 @@ CountryLanguage.Percentage > 50; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE CountryLanguage ALL PRIMARY,Percentage NULL NULL NULL 984 Using where 1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where; Using join buffer (flat, BKAH join) -1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using index condition(BKA); Using where; Using join buffer (incremental, BKA join) +1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using index condition(BKA); Using where; Using join buffer (flat, BKAH join) SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -3578,7 +3578,7 @@ CountryLanguage.Percentage > 50; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE CountryLanguage ALL PRIMARY,Percentage NULL NULL NULL 984 Using where 1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where; Using join buffer (flat, BKAH join) -1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using index condition(BKA); Using where; Using join buffer (incremental, BKA join) +1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using index condition(BKA); Using where; Using join buffer (flat, BKAH join) SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -4661,7 +4661,7 @@ t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 16384 Using where 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where; Using join buffer (flat, BKAH join) -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where; Using join buffer (incremental, BKA join) +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where; Using join buffer (flat, BKAH join) SELECT COUNT(*) FROM t1,t2,t3 WHERE t1.a=t2.a AND t2.a=t3.a AND t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL; @@ -5969,4 +5969,39 @@ a1 b1 c1 a2 a3 b3 c3 a4 b4 c4 SET SESSION optimizer_switch = 'outer_join_with_cache=off'; SET SESSION join_cache_level = DEFAULT; DROP TABLE t1,t2,t3,t4; +# +# Bug #660963: nested outer join with join_cache_level set to 5 +# +CREATE TABLE t1 (a1 int) ; +INSERT INTO t1 VALUES (0),(0); +CREATE TABLE t2 (a2 int, b2 int, PRIMARY KEY (a2)) ; +INSERT INTO t2 VALUES (2,1); +CREATE TABLE t3 (a3 int, b3 int, PRIMARY KEY (a3)) ; +INSERT INTO t3 VALUES (2,1); +SET SESSION optimizer_switch = 'outer_join_with_cache=on'; +SET SESSION join_cache_level = 6; +EXPLAIN +SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0 ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 1 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.a2 1 Using where; Using join buffer (incremental, BKA join) +SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0 ; +a1 a2 b2 a3 b3 +0 2 1 2 1 +0 2 1 2 1 +SET SESSION join_cache_level = 5; +EXPLAIN +SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0 ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 1 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.a2 1 Using where; Using join buffer (incremental, BNLH join) +SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0 ; +a1 a2 b2 a3 b3 +0 2 1 2 1 +0 2 1 2 1 +SET SESSION optimizer_switch = 'outer_join_with_cache=off'; +SET SESSION join_cache_level = DEFAULT; +DROP TABLE t1,t2,t3; set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test index c7acf434011..bb0d1b56a3d 100644 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -2625,5 +2625,35 @@ SET SESSION join_cache_level = DEFAULT; DROP TABLE t1,t2,t3,t4; +--echo # +--echo # Bug #660963: nested outer join with join_cache_level set to 5 +--echo # + +CREATE TABLE t1 (a1 int) ; +INSERT INTO t1 VALUES (0),(0); + +CREATE TABLE t2 (a2 int, b2 int, PRIMARY KEY (a2)) ; +INSERT INTO t2 VALUES (2,1); + +CREATE TABLE t3 (a3 int, b3 int, PRIMARY KEY (a3)) ; +INSERT INTO t3 VALUES (2,1); + +SET SESSION optimizer_switch = 'outer_join_with_cache=on'; + +SET SESSION join_cache_level = 6; +EXPLAIN +SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0 ; +SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0 ; + +SET SESSION join_cache_level = 5; +EXPLAIN +SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0 ; +SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0 ; + +SET SESSION optimizer_switch = 'outer_join_with_cache=off'; +SET SESSION join_cache_level = DEFAULT; + +DROP TABLE t1,t2,t3; + # 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 4b59b8086c7..c6c97764078 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7635,8 +7635,7 @@ uint check_join_cache_usage(JOIN_TAB *tab, if (cache_level == 0 || i == join->const_tables || !prev_tab) return 0; - if (force_unlinked_cache && - (cache_level & JOIN_CACHE_INCREMENTAL_BIT)) + if (force_unlinked_cache && (cache_level%2 == 0)) cache_level--; if (options & SELECT_NO_JOIN_CACHE) @@ -7658,13 +7657,14 @@ uint check_join_cache_usage(JOIN_TAB *tab, /* Non-linked join buffers can't guarantee one match */ - if ((force_unlinked_cache || cache_level == 1) && - ((tab->is_inner_table_of_semi_join_with_first_match() && - !tab->is_single_inner_of_semi_join_with_first_match()) || - (tab->is_inner_table_of_outer_join() && - !tab->is_single_inner_of_outer_join()))) - goto no_join_cache; - + if (tab->is_nested_inner()) + { + if (force_unlinked_cache || cache_level == 1) + goto no_join_cache; + if (cache_level & 1) + cache_level--; + } + /* Don't use join buffering if we're dictated not to by no_jbuf_after (this ...) @@ -7757,9 +7757,6 @@ uint check_join_cache_usage(JOIN_TAB *tab, (cache_level <= 6 || no_hashed_cache)) goto no_join_cache; - if (prev_tab->cache && cache_level==7) - cache_level= 6; - if ((rows != HA_POS_ERROR) && !(flags & HA_MRR_USE_DEFAULT_IMPL)) { if (cache_level <= 6 || no_hashed_cache) diff --git a/sql/sql_select.h b/sql/sql_select.h index 1e57f487f69..ee6a58ed97e 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -359,6 +359,19 @@ typedef struct st_join_table { return (first_inner && first_inner->last_inner == this) || last_sj_inner_tab == this; } + /* + Check whether the table belongs to a nest of inner tables of an + outer join or to a nest of inner tables of a semi-join + */ + bool is_nested_inner() + { + if (first_inner && + (first_inner != first_inner->last_inner || first_inner->first_upper)) + return TRUE; + if (first_sj_inner_tab && first_sj_inner_tab != last_sj_inner_tab) + return TRUE; + return FALSE; + } struct st_join_table *get_first_inner_table() { if (first_inner) |