revision-id: fe0809f6f3e8f78bc4f823195fd8fbd5b66fa324 (mariadb-10.3.10-39-gfe0809f6f3e) parent(s): 63bcbd9ef7d15bbe1422f82b65e29e57308f6f57 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2018-11-01 11:35:28 +0300 message: MDEV-17518: Range optimization doesn't use ON expressions from nested outer joins Part#2: take into account that join nest that we are marking as constant might already have constant tables in it. Don't count these tables twice. --- mysql-test/main/join_outer.result | 15 +++++++++++++++ mysql-test/main/join_outer.test | 17 +++++++++++++++++ mysql-test/main/join_outer_jcl6.result | 15 +++++++++++++++ sql/sql_select.cc | 19 +++++++++++-------- 4 files changed, 58 insertions(+), 8 deletions(-) diff --git a/mysql-test/main/join_outer.result b/mysql-test/main/join_outer.result index aa9f30885a9..6b671a0759f 100644 --- a/mysql-test/main/join_outer.result +++ b/mysql-test/main/join_outer.result @@ -2564,4 +2564,19 @@ drop table t1,t2,t3; set optimizer_use_condition_selectivity= @tmp1; set use_stat_tables= @tmp2; set histogram_size= @tmp3; +# Another test +CREATE TABLE t1 (i1 int) ; +CREATE TABLE t2 (pk int NOT NULL PRIMARY KEY) ; +CREATE TABLE t3 (pk int NOT NULL, i1 int, PRIMARY KEY (pk)) ; +INSERT INTO t3 VALUES (2, NULL); +CREATE TABLE t4 (pk int NOT NULL, i1 int, PRIMARY KEY (pk), KEY i1 (i1)) ; +CREATE VIEW v4 AS SELECT * FROM t4; +SELECT 1 +FROM t3 RIGHT JOIN t1 ON t3.i1 = t1.i1 +LEFT JOIN v4 +RIGHT JOIN t2 ON v4.i1 = t2.pk ON t1.i1 = t2.pk +WHERE t3.pk IN (2); +1 +drop view v4; +drop table t1,t2,t3,t4; SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/main/join_outer.test b/mysql-test/main/join_outer.test index 9118febc800..82127397bee 100644 --- a/mysql-test/main/join_outer.test +++ b/mysql-test/main/join_outer.test @@ -2084,5 +2084,22 @@ set optimizer_use_condition_selectivity= @tmp1; set use_stat_tables= @tmp2; set histogram_size= @tmp3; +--echo # Another test +CREATE TABLE t1 (i1 int) ; +CREATE TABLE t2 (pk int NOT NULL PRIMARY KEY) ; +CREATE TABLE t3 (pk int NOT NULL, i1 int, PRIMARY KEY (pk)) ; +INSERT INTO t3 VALUES (2, NULL); + +CREATE TABLE t4 (pk int NOT NULL, i1 int, PRIMARY KEY (pk), KEY i1 (i1)) ; +CREATE VIEW v4 AS SELECT * FROM t4; + +SELECT 1 +FROM t3 RIGHT JOIN t1 ON t3.i1 = t1.i1 + LEFT JOIN v4 + RIGHT JOIN t2 ON v4.i1 = t2.pk ON t1.i1 = t2.pk +WHERE t3.pk IN (2); + +drop view v4; +drop table t1,t2,t3,t4; SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/main/join_outer_jcl6.result b/mysql-test/main/join_outer_jcl6.result index 6f5e4b3e85e..f46f3e2df21 100644 --- a/mysql-test/main/join_outer_jcl6.result +++ b/mysql-test/main/join_outer_jcl6.result @@ -2575,6 +2575,21 @@ drop table t1,t2,t3; set optimizer_use_condition_selectivity= @tmp1; set use_stat_tables= @tmp2; set histogram_size= @tmp3; +# Another test +CREATE TABLE t1 (i1 int) ; +CREATE TABLE t2 (pk int NOT NULL PRIMARY KEY) ; +CREATE TABLE t3 (pk int NOT NULL, i1 int, PRIMARY KEY (pk)) ; +INSERT INTO t3 VALUES (2, NULL); +CREATE TABLE t4 (pk int NOT NULL, i1 int, PRIMARY KEY (pk), KEY i1 (i1)) ; +CREATE VIEW v4 AS SELECT * FROM t4; +SELECT 1 +FROM t3 RIGHT JOIN t1 ON t3.i1 = t1.i1 +LEFT JOIN v4 +RIGHT JOIN t2 ON v4.i1 = t2.pk ON t1.i1 = t2.pk +WHERE t3.pk IN (2); +1 +drop view v4; +drop table t1,t2,t3,t4; SET optimizer_switch=@save_optimizer_switch; set join_cache_level=default; show variables like 'join_cache_level'; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 39c77dc4dc2..a99b991af96 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -4316,14 +4316,17 @@ void mark_join_nest_as_const(JOIN *join, } JOIN_TAB *tab= tbl->table->reginfo.join_tab; - tab->type= JT_CONST; - tab->info= ET_IMPOSSIBLE_ON_CONDITION; - tab->table->const_table= 1; - - join->const_table_map|= tab->table->map; - *found_const_table_map|= tab->table->map; - set_position(join,(*const_count)++,tab,(KEYUSE*) 0); - mark_as_null_row(tab->table); // All fields are NULL + if (!(join->const_table_map & tab->table->map)) + { + tab->type= JT_CONST; + tab->info= ET_IMPOSSIBLE_ON_CONDITION; + tab->table->const_table= 1; + + join->const_table_map|= tab->table->map; + *found_const_table_map|= tab->table->map; + set_position(join,(*const_count)++,tab,(KEYUSE*) 0); + mark_as_null_row(tab->table); // All fields are NULL + } } }