diff options
| -rw-r--r-- | mysql-test/r/derived_opt.result | 125 | ||||
| -rw-r--r-- | mysql-test/r/join_cache.result | 12 | ||||
| -rw-r--r-- | mysql-test/r/join_outer.result | 23 | ||||
| -rw-r--r-- | mysql-test/r/join_outer_jcl6.result | 23 | ||||
| -rw-r--r-- | mysql-test/t/derived_opt.test | 64 | ||||
| -rw-r--r-- | mysql-test/t/join_outer.test | 24 | ||||
| -rw-r--r-- | sql/sql_select.cc | 31 | ||||
| -rw-r--r-- | sql/table.cc | 2 |
8 files changed, 291 insertions, 13 deletions
diff --git a/mysql-test/r/derived_opt.result b/mysql-test/r/derived_opt.result index b75e735446d..49d383e7e5e 100644 --- a/mysql-test/r/derived_opt.result +++ b/mysql-test/r/derived_opt.result @@ -1,4 +1,5 @@ -drop table if exists t1,t2,t3; +drop table if exists t0,t1,t2,t3; +drop database if exists test1; set @exit_optimizer_switch=@@optimizer_switch; set optimizer_switch='derived_merge=on,derived_with_keys=on'; set @save_optimizer_switch=@@optimizer_switch; @@ -352,4 +353,126 @@ pk pk 72 72 80 80 drop table t1, t2, t3, t4; +# +# MDEV-6888: Query spends a long time in best_extension_by_limited_search with mrr enabled +# +create database test1; +use test1; +set @tmp_jcl= @@join_cache_level; +set @tmp_os= @@optimizer_switch; +set join_cache_level=8; +set optimizer_switch='mrr=on,mrr_sort_keys=on'; +CREATE TABLE t0 ( +f1 bigint(20) DEFAULT NULL, +f2 char(50) DEFAULT NULL +) ENGINE=MEMORY DEFAULT CHARSET=latin1; +INSERT INTO t0 VALUES (NULL,'numeric column is NULL'),(0,NULL),(5,'five'),(1,'one'),(2,'two'); +CREATE TABLE t1 ( +f1 decimal(64,30) DEFAULT NULL, +f2 varchar(50) DEFAULT NULL +) ENGINE=MEMORY DEFAULT CHARSET=latin1; +INSERT INTO t1 VALUES +(NULL,'numeric column is NULL'), +(0.000000000000000000000000000000,NULL), +(5.000000000000000000000000000000,'five'), +(1.000000000000000000000000000000,'one'), +(3.000000000000000000000000000000,'three'); +CREATE TABLE t2 ( +f1 double DEFAULT NULL, +f2 varbinary(50) DEFAULT NULL +) ENGINE=MEMORY DEFAULT CHARSET=latin1; +INSERT INTO t2 VALUES (NULL,'numeric column is NULL'),(0,NULL),(5,'five'),(2,'two'),(3,'three'); +create VIEW v0 AS select f1,f2 from t1 ; +create VIEW v1 AS select tab1_v1.f1,tab1_v1.f2 from t1 tab1_v1 join v0 tab2 on tab1_v1.f1 = tab2.f1 and tab1_v1.f2 = tab2.f2; +create VIEW v2 AS select tab1_v2.f1,tab1_v2.f2 from t2 tab1_v2 join v1 tab2 on tab1_v2.f1 = tab2.f1 and tab1_v2.f2 = tab2.f2; +create VIEW v3 AS select tab1_v3.f1,tab1_v3.f2 from t0 tab1_v3 join v2 tab2 on tab1_v3.f1 = tab2.f1 and tab1_v3.f2 = tab2.f2; +create VIEW v4 AS select tab1_v4.f1,tab1_v4.f2 from t1 tab1_v4 join v3 tab2 on tab1_v4.f1 = tab2.f1 and tab1_v4.f2 = tab2.f2; +create VIEW v5 AS select tab1_v5.f1,tab1_v5.f2 from t2 tab1_v5 join v4 tab2 on tab1_v5.f1 = tab2.f1 and tab1_v5.f2 = tab2.f2; +create VIEW v6 AS select tab1_v6.f1,tab1_v6.f2 from t0 tab1_v6 join v5 tab2 on tab1_v6.f1 = tab2.f1 and tab1_v6.f2 = tab2.f2; +create VIEW v7 AS select tab1_v7.f1,tab1_v7.f2 from t1 tab1_v7 join v6 tab2 on tab1_v7.f1 = tab2.f1 and tab1_v7.f2 = tab2.f2; +create VIEW v8 AS select tab1_v8.f1,tab1_v8.f2 from t2 tab1_v8 join v7 tab2 on tab1_v8.f1 = tab2.f1 and tab1_v8.f2 = tab2.f2; +create VIEW v9 AS select tab1_v9.f1,tab1_v9.f2 from t0 tab1_v9 join v8 tab2 on tab1_v9.f1 = tab2.f1 and tab1_v9.f2 = tab2.f2; +create VIEW v10 AS select tab1_v10.f1,tab1_v10.f2 from t1 tab1_v10 join v9 tab2 on tab1_v10.f1 = tab2.f1 and tab1_v10.f2 = tab2.f2; +create VIEW v11 AS select tab1_v11.f1,tab1_v11.f2 from t2 tab1_v11 join v10 tab2 on tab1_v11.f1 = tab2.f1 and tab1_v11.f2 = tab2.f2; +create VIEW v12 AS select tab1_v12.f1,tab1_v12.f2 from t0 tab1_v12 join v11 tab2 on tab1_v12.f1 = tab2.f1 and tab1_v12.f2 = tab2.f2; +create VIEW v13 AS select tab1_v13.f1,tab1_v13.f2 from t1 tab1_v13 join v12 tab2 on tab1_v13.f1 = tab2.f1 and tab1_v13.f2 = tab2.f2; +create VIEW v14 AS select tab1_v14.f1,tab1_v14.f2 from t2 tab1_v14 join v13 tab2 on tab1_v14.f1 = tab2.f1 and tab1_v14.f2 = tab2.f2; +create VIEW v15 AS select tab1_v15.f1,tab1_v15.f2 from t0 tab1_v15 join v14 tab2 on tab1_v15.f1 = tab2.f1 and tab1_v15.f2 = tab2.f2; +create VIEW v16 AS select tab1_v16.f1,tab1_v16.f2 from t1 tab1_v16 join v15 tab2 on tab1_v16.f1 = tab2.f1 and tab1_v16.f2 = tab2.f2; +create VIEW v17 AS select tab1_v17.f1,tab1_v17.f2 from t2 tab1_v17 join v16 tab2 on tab1_v17.f1 = tab2.f1 and tab1_v17.f2 = tab2.f2; +create VIEW v18 AS select tab1_v18.f1,tab1_v18.f2 from t0 tab1_v18 join v17 tab2 on tab1_v18.f1 = tab2.f1 and tab1_v18.f2 = tab2.f2; +create VIEW v19 AS select tab1_v19.f1,tab1_v19.f2 from t1 tab1_v19 join v18 tab2 on tab1_v19.f1 = tab2.f1 and tab1_v19.f2 = tab2.f2; +create VIEW v20 AS select tab1_v20.f1,tab1_v20.f2 from t2 tab1_v20 join v19 tab2 on tab1_v20.f1 = tab2.f1 and tab1_v20.f2 = tab2.f2; +create VIEW v21 AS select tab1_v21.f1,tab1_v21.f2 from t0 tab1_v21 join v20 tab2 on tab1_v21.f1 = tab2.f1 and tab1_v21.f2 = tab2.f2; +create VIEW v22 AS select tab1_v22.f1,tab1_v22.f2 from t1 tab1_v22 join v21 tab2 on tab1_v22.f1 = tab2.f1 and tab1_v22.f2 = tab2.f2; +create VIEW v23 AS select tab1_v23.f1,tab1_v23.f2 from t2 tab1_v23 join v22 tab2 on tab1_v23.f1 = tab2.f1 and tab1_v23.f2 = tab2.f2; +create VIEW v24 AS select tab1_v24.f1,tab1_v24.f2 from t0 tab1_v24 join v23 tab2 on tab1_v24.f1 = tab2.f1 and tab1_v24.f2 = tab2.f2; +create VIEW v25 AS select tab1_v25.f1,tab1_v25.f2 from t1 tab1_v25 join v24 tab2 on tab1_v25.f1 = tab2.f1 and tab1_v25.f2 = tab2.f2; +create VIEW v26 AS select tab1_v26.f1,tab1_v26.f2 from t2 tab1_v26 join v25 tab2 on tab1_v26.f1 = tab2.f1 and tab1_v26.f2 = tab2.f2; +create VIEW v27 AS select tab1_v27.f1,tab1_v27.f2 from t0 tab1_v27 join v26 tab2 on tab1_v27.f1 = tab2.f1 and tab1_v27.f2 = tab2.f2; +EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1, CAST(f2 AS CHAR) AS f2 FROM v27; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE tab1_v27 ALL NULL NULL NULL NULL 5 Using where +1 SIMPLE tab1_v26 hash_ALL NULL #hash#$hj 63 test1.tab1_v27.f1,test1.tab1_v27.f2 5 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE tab1_v25 hash_ALL NULL #hash#$hj 31 test1.tab1_v26.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v24 hash_ALL NULL #hash#$hj 60 test1.tab1_v25.f1,test1.tab1_v25.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v23 hash_ALL NULL #hash#$hj 63 test1.tab1_v24.f1,test1.tab1_v24.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v22 hash_ALL NULL #hash#$hj 31 test1.tab1_v23.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v21 hash_ALL NULL #hash#$hj 60 test1.tab1_v22.f1,test1.tab1_v22.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v20 hash_ALL NULL #hash#$hj 63 test1.tab1_v21.f1,test1.tab1_v21.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v19 hash_ALL NULL #hash#$hj 31 test1.tab1_v20.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v18 hash_ALL NULL #hash#$hj 60 test1.tab1_v19.f1,test1.tab1_v19.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v17 hash_ALL NULL #hash#$hj 63 test1.tab1_v18.f1,test1.tab1_v18.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v16 hash_ALL NULL #hash#$hj 31 test1.tab1_v17.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v15 hash_ALL NULL #hash#$hj 60 test1.tab1_v16.f1,test1.tab1_v16.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v14 hash_ALL NULL #hash#$hj 63 test1.tab1_v15.f1,test1.tab1_v15.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v13 hash_ALL NULL #hash#$hj 31 test1.tab1_v14.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v12 hash_ALL NULL #hash#$hj 60 test1.tab1_v13.f1,test1.tab1_v13.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v11 hash_ALL NULL #hash#$hj 63 test1.tab1_v12.f1,test1.tab1_v12.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v10 hash_ALL NULL #hash#$hj 31 test1.tab1_v11.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v9 hash_ALL NULL #hash#$hj 60 test1.tab1_v10.f1,test1.tab1_v10.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v8 hash_ALL NULL #hash#$hj 63 test1.tab1_v9.f1,test1.tab1_v9.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v7 hash_ALL NULL #hash#$hj 31 test1.tab1_v8.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v6 hash_ALL NULL #hash#$hj 60 test1.tab1_v7.f1,test1.tab1_v7.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v5 hash_ALL NULL #hash#$hj 63 test1.tab1_v6.f1,test1.tab1_v6.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v4 hash_ALL NULL #hash#$hj 31 test1.tab1_v5.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v3 hash_ALL NULL #hash#$hj 60 test1.tab1_v4.f1,test1.tab1_v4.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v2 hash_ALL NULL #hash#$hj 63 test1.tab1_v3.f1,test1.tab1_v3.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v1 hash_ALL NULL #hash#$hj 31 test1.tab1_v2.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t1 hash_ALL NULL #hash#$hj 85 test1.tab1_v1.f1,test1.tab1_v1.f2 5 Using where; Using join buffer (incremental, BNLH join) +# This used to hang forever: +EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1, CAST(f2 AS CHAR) AS f2 FROM v27; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE tab1_v27 ALL NULL NULL NULL NULL 5 Using where +1 SIMPLE tab1_v26 hash_ALL NULL #hash#$hj 63 test1.tab1_v27.f1,test1.tab1_v27.f2 5 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE tab1_v25 hash_ALL NULL #hash#$hj 31 test1.tab1_v26.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v24 hash_ALL NULL #hash#$hj 60 test1.tab1_v25.f1,test1.tab1_v25.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v23 hash_ALL NULL #hash#$hj 63 test1.tab1_v24.f1,test1.tab1_v24.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v22 hash_ALL NULL #hash#$hj 31 test1.tab1_v23.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v21 hash_ALL NULL #hash#$hj 60 test1.tab1_v22.f1,test1.tab1_v22.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v20 hash_ALL NULL #hash#$hj 63 test1.tab1_v21.f1,test1.tab1_v21.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v19 hash_ALL NULL #hash#$hj 31 test1.tab1_v20.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v18 hash_ALL NULL #hash#$hj 60 test1.tab1_v19.f1,test1.tab1_v19.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v17 hash_ALL NULL #hash#$hj 63 test1.tab1_v18.f1,test1.tab1_v18.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v16 hash_ALL NULL #hash#$hj 31 test1.tab1_v17.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v15 hash_ALL NULL #hash#$hj 60 test1.tab1_v16.f1,test1.tab1_v16.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v14 hash_ALL NULL #hash#$hj 63 test1.tab1_v15.f1,test1.tab1_v15.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v13 hash_ALL NULL #hash#$hj 31 test1.tab1_v14.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v12 hash_ALL NULL #hash#$hj 60 test1.tab1_v13.f1,test1.tab1_v13.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v11 hash_ALL NULL #hash#$hj 63 test1.tab1_v12.f1,test1.tab1_v12.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v10 hash_ALL NULL #hash#$hj 31 test1.tab1_v11.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v9 hash_ALL NULL #hash#$hj 60 test1.tab1_v10.f1,test1.tab1_v10.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v8 hash_ALL NULL #hash#$hj 63 test1.tab1_v9.f1,test1.tab1_v9.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v7 hash_ALL NULL #hash#$hj 31 test1.tab1_v8.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v6 hash_ALL NULL #hash#$hj 60 test1.tab1_v7.f1,test1.tab1_v7.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v5 hash_ALL NULL #hash#$hj 63 test1.tab1_v6.f1,test1.tab1_v6.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v4 hash_ALL NULL #hash#$hj 31 test1.tab1_v5.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v3 hash_ALL NULL #hash#$hj 60 test1.tab1_v4.f1,test1.tab1_v4.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v2 hash_ALL NULL #hash#$hj 63 test1.tab1_v3.f1,test1.tab1_v3.f2 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE tab1_v1 hash_ALL NULL #hash#$hj 31 test1.tab1_v2.f1 5 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t1 hash_ALL NULL #hash#$hj 85 test1.tab1_v1.f1,test1.tab1_v1.f2 5 Using where; Using join buffer (incremental, BNLH join) +use test; +drop database test1; +set join_cache_level=@tmp_jcl; +set optimizer_switch=@tmp_os; set optimizer_switch=@exit_optimizer_switch; diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index be1086b1afc..1f2ae4a9e5a 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -498,8 +498,8 @@ CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE Country ALL NULL NULL NULL NULL 239 Using where -1 SIMPLE City ALL NULL NULL NULL NULL 4079 Using where; Using join buffer (flat, BNL join) 1 SIMPLE CountryLanguage ALL NULL NULL NULL NULL 984 Using where; Using join buffer (flat, BNL join) +1 SIMPLE City ALL NULL NULL NULL NULL 4079 Using where; Using join buffer (flat, BNL join) SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -576,8 +576,8 @@ CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE Country ALL NULL NULL NULL NULL 239 Using where -1 SIMPLE City ALL NULL NULL NULL NULL 4079 Using where; Using join buffer (flat, BNL join) -1 SIMPLE CountryLanguage ALL NULL NULL NULL NULL 984 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE CountryLanguage ALL NULL NULL NULL NULL 984 Using where; Using join buffer (flat, BNL join) +1 SIMPLE City ALL NULL NULL NULL NULL 4079 Using where; Using join buffer (incremental, BNL join) SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -654,8 +654,8 @@ CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE Country ALL NULL NULL NULL NULL 239 Using where -1 SIMPLE City hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) 1 SIMPLE CountryLanguage hash_ALL NULL #hash#$hj 3 world.Country.Code 984 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE City hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -732,8 +732,8 @@ CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE Country ALL NULL NULL NULL NULL 239 Using where -1 SIMPLE City hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE CountryLanguage hash_ALL NULL #hash#$hj 3 world.Country.Code 984 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE CountryLanguage hash_ALL NULL #hash#$hj 3 world.Country.Code 984 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE City hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 Using where; Using join buffer (incremental, BNLH join) SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 670b9efde00..894d7bd57c6 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1920,4 +1920,27 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`i1` AS `i1`,`test`.`t2`.`i2` AS `i2`,`test`.`t3`.`i3` AS `i3`,`test`.`t3`.`d3` AS `d3` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`i2` = `test`.`t1`.`i1`) and (`test`.`t3`.`i3` = `test`.`t1`.`i1`))) where ((`test`.`t3`.`d3` = 0) or isnull(`test`.`t3`.`d3`)) DROP TABLE t1,t2,t3; +# +# Bug mdev-6705: wrong on expression after constant row substitution +# that triggers a simplification of WHERE condition +# +CREATE TABLE t1 (a int, b int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (10,8); +CREATE TABLE t2 (c int) ENGINE=MyISAM; +INSERT INTO t2 VALUES (8),(9); +CREATE TABLE t3 (d int) ENGINE=MyISAM; +INSERT INTO t3 VALUES (3),(8); +EXPLAIN EXTENDED +SELECT * FROM t1 INNER JOIN t2 ON c = b LEFT JOIN t3 ON d = a +WHERE b IN (1,2,3) OR b = d; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select 10 AS `a`,8 AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t3`.`d` AS `d` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on((`test`.`t3`.`d` = 10)) where ((`test`.`t2`.`c` = 8) and (`test`.`t3`.`d` = 8)) +SELECT * FROM t1 INNER JOIN t2 ON c = b LEFT JOIN t3 ON d = a +WHERE b IN (1,2,3) OR b = d; +a b c d +DROP TABLE t1,t2,t3; SET optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result index ae81535a171..280aa5837a9 100644 --- a/mysql-test/r/join_outer_jcl6.result +++ b/mysql-test/r/join_outer_jcl6.result @@ -1931,6 +1931,29 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`i1` AS `i1`,`test`.`t2`.`i2` AS `i2`,`test`.`t3`.`i3` AS `i3`,`test`.`t3`.`d3` AS `d3` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(((`test`.`t2`.`i2` = `test`.`t1`.`i1`) and (`test`.`t3`.`i3` = `test`.`t1`.`i1`))) where ((`test`.`t3`.`d3` = 0) or isnull(`test`.`t3`.`d3`)) DROP TABLE t1,t2,t3; +# +# Bug mdev-6705: wrong on expression after constant row substitution +# that triggers a simplification of WHERE condition +# +CREATE TABLE t1 (a int, b int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (10,8); +CREATE TABLE t2 (c int) ENGINE=MyISAM; +INSERT INTO t2 VALUES (8),(9); +CREATE TABLE t3 (d int) ENGINE=MyISAM; +INSERT INTO t3 VALUES (3),(8); +EXPLAIN EXTENDED +SELECT * FROM t1 INNER JOIN t2 ON c = b LEFT JOIN t3 ON d = a +WHERE b IN (1,2,3) OR b = d; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select 10 AS `a`,8 AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t3`.`d` AS `d` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on((`test`.`t3`.`d` = 10)) where ((`test`.`t2`.`c` = 8) and (`test`.`t3`.`d` = 8)) +SELECT * FROM t1 INNER JOIN t2 ON c = b LEFT JOIN t3 ON d = a +WHERE b IN (1,2,3) OR b = d; +a b c d +DROP TABLE t1,t2,t3; SET optimizer_switch=@save_optimizer_switch; set join_cache_level=default; show variables like 'join_cache_level'; diff --git a/mysql-test/t/derived_opt.test b/mysql-test/t/derived_opt.test index b01c479111b..4bb1db0b4f6 100644 --- a/mysql-test/t/derived_opt.test +++ b/mysql-test/t/derived_opt.test @@ -1,6 +1,7 @@ # Initialize --disable_warnings -drop table if exists t1,t2,t3; +drop table if exists t0,t1,t2,t3; +drop database if exists test1; --enable_warnings set @exit_optimizer_switch=@@optimizer_switch; @@ -272,5 +273,66 @@ limit 10; drop table t1, t2, t3, t4; +--echo # +--echo # MDEV-6888: Query spends a long time in best_extension_by_limited_search with mrr enabled +--echo # +create database test1; +use test1; + +set @tmp_jcl= @@join_cache_level; +set @tmp_os= @@optimizer_switch; +set join_cache_level=8; +set optimizer_switch='mrr=on,mrr_sort_keys=on'; + +CREATE TABLE t0 ( + f1 bigint(20) DEFAULT NULL, + f2 char(50) DEFAULT NULL +) ENGINE=MEMORY DEFAULT CHARSET=latin1; +INSERT INTO t0 VALUES (NULL,'numeric column is NULL'),(0,NULL),(5,'five'),(1,'one'),(2,'two'); + +CREATE TABLE t1 ( + f1 decimal(64,30) DEFAULT NULL, + f2 varchar(50) DEFAULT NULL +) ENGINE=MEMORY DEFAULT CHARSET=latin1; + +INSERT INTO t1 VALUES +(NULL,'numeric column is NULL'), +(0.000000000000000000000000000000,NULL), +(5.000000000000000000000000000000,'five'), +(1.000000000000000000000000000000,'one'), +(3.000000000000000000000000000000,'three'); + +CREATE TABLE t2 ( + f1 double DEFAULT NULL, + f2 varbinary(50) DEFAULT NULL +) ENGINE=MEMORY DEFAULT CHARSET=latin1; +INSERT INTO t2 VALUES (NULL,'numeric column is NULL'),(0,NULL),(5,'five'),(2,'two'),(3,'three'); + +create VIEW v0 AS select f1,f2 from t1 ; + +let $cnt= 27; +while ($cnt) +{ +# i runs from 1 to 27 + let $i= `select 28 - $cnt`; + let $prev=`select $i - 1`; + +# rem = i mod 3 + let $rem= `select MOD($i, 3)`; +# view uses $i, $prev and $rem: + eval create VIEW v$i AS select tab1_v$i.f1,tab1_v$i.f2 from t$rem tab1_v$i join v$prev tab2 on tab1_v$i.f1 = tab2.f1 and tab1_v$i.f2 = tab2.f2; + dec $cnt; +} + +EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1, CAST(f2 AS CHAR) AS f2 FROM v27; +--echo # This used to hang forever: +EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1, CAST(f2 AS CHAR) AS f2 FROM v27; + +use test; +drop database test1; +set join_cache_level=@tmp_jcl; +set optimizer_switch=@tmp_os; + + # The following command must be the last one the file set optimizer_switch=@exit_optimizer_switch; diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index 006df9f0359..184142361a2 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -1463,4 +1463,28 @@ SELECT * FROM t1 LEFT JOIN t2 LEFT JOIN t3 ON i2 = i3 ON i1 = i3 DROP TABLE t1,t2,t3; +--echo # +--echo # Bug mdev-6705: wrong on expression after constant row substitution +--echo # that triggers a simplification of WHERE condition +--echo # + +CREATE TABLE t1 (a int, b int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (10,8); + +CREATE TABLE t2 (c int) ENGINE=MyISAM; +INSERT INTO t2 VALUES (8),(9); + +CREATE TABLE t3 (d int) ENGINE=MyISAM; +INSERT INTO t3 VALUES (3),(8); + +EXPLAIN EXTENDED +SELECT * FROM t1 INNER JOIN t2 ON c = b LEFT JOIN t3 ON d = a + WHERE b IN (1,2,3) OR b = d; + +SELECT * FROM t1 INNER JOIN t2 ON c = b LEFT JOIN t3 ON d = a + WHERE b IN (1,2,3) OR b = d; + +DROP TABLE t1,t2,t3; + + SET optimizer_switch=@save_optimizer_switch; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 161c00405b0..5263a0c2e30 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -3530,6 +3530,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, join->impossible_where= false; if (conds && const_count) { + COND_EQUAL *orig_cond_equal = join->cond_equal; conds->update_used_tables(); conds= remove_eq_conds(join->thd, conds, &join->cond_value); if (conds && conds->type() == Item::COND_ITEM && @@ -3556,7 +3557,21 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, join->cond_equal->current_level.empty(); join->cond_equal->current_level.push_back((Item_equal*) conds); } - } + } + + if (orig_cond_equal != join->cond_equal) + { + /* + If join->cond_equal has changed all references to it from COND_EQUAL + objects associated with ON expressions must be updated. + */ + for (JOIN_TAB **pos=stat_vector+const_count ; (s= *pos) ; pos++) + { + if (*s->on_expr_ref && s->cond_equal && + s->cond_equal->upper_levels == orig_cond_equal) + s->cond_equal->upper_levels= join->cond_equal; + } + } } /* Calc how many (possible) matched records in each table */ @@ -11999,10 +12014,18 @@ Item *eliminate_item_equal(COND *cond, COND_EQUAL *upper_levels, if (upper) { TABLE_LIST *native_sjm= embedding_sjm(item_equal->context_field); - if (item_const && upper->get_const()) + Item *upper_const= upper->get_const(); + if (item_const && upper_const) { - /* Upper item also has "field_item=const". Don't produce equality here */ - item= 0; + /* + Upper item also has "field_item=const". + Don't produce equality if const is equal to item_const. + */ + Item_func_eq *func= new Item_func_eq(item_const, upper_const); + func->set_cmp_func(); + func->quick_fix_field(); + if (func->val_int()) + item= 0; } else { diff --git a/sql/table.cc b/sql/table.cc index eae98e0ea50..47f70613ed4 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -5401,7 +5401,6 @@ void TABLE::create_key_part_by_field(KEY *keyinfo, KEY_PART_INFO *key_part_info, Field *field, uint fieldnr) { - field->flags|= PART_KEY_FLAG; key_part_info->null_bit= field->null_bit; key_part_info->null_offset= (uint) (field->null_ptr - (uchar*) record[0]); @@ -5552,6 +5551,7 @@ bool TABLE::add_tmp_key(uint key, uint key_parts, (*reg_field)->key_start.set_bit(key); (*reg_field)->part_of_key.set_bit(key); create_key_part_by_field(keyinfo, key_part_info, *reg_field, fld_idx+1); + (*reg_field)->flags|= PART_KEY_FLAG; key_start= FALSE; key_part_info++; } |