diff options
Diffstat (limited to 'mysql-test/main/join_cache.test')
| -rw-r--r-- | mysql-test/main/join_cache.test | 79 |
1 files changed, 79 insertions, 0 deletions
diff --git a/mysql-test/main/join_cache.test b/mysql-test/main/join_cache.test index b69be56a435..97f660e7e0d 100644 --- a/mysql-test/main/join_cache.test +++ b/mysql-test/main/join_cache.test @@ -49,6 +49,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--sorted_result SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -62,6 +63,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--sorted_result SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -78,6 +80,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--sorted_result SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -91,6 +94,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--sorted_result SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -107,6 +111,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--sorted_result SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -121,6 +126,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language LENGTH(Language) < LENGTH(City.Name) - 2; +--sorted_result SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -138,6 +144,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--sorted_result SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -151,6 +158,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--sorted_result SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -217,6 +225,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--sorted_result SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -230,6 +239,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--sorted_result SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -246,6 +256,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--sorted_result SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -259,6 +270,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--sorted_result SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -275,6 +287,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--sorted_result SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -288,6 +301,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--sorted_result SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -304,6 +318,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--sorted_result SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -317,6 +332,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--sorted_result SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -357,6 +373,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--sorted_result SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -370,6 +387,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--sorted_result SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -409,6 +427,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--sorted_result SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -422,6 +441,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--sorted_result SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -491,6 +511,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--sorted_result SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -504,6 +525,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--sorted_result SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -542,6 +564,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--sorted_result SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -555,6 +578,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--sorted_result SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -593,6 +617,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--sorted_result SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -606,6 +631,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--sorted_result SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -644,6 +670,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--sorted_result SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -657,6 +684,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--sorted_result SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -698,6 +726,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--sorted_result SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -711,6 +740,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--sorted_result SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -736,6 +766,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--sorted_result SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -749,6 +780,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--sorted_result SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -774,6 +806,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--sorted_result SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -787,6 +820,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--sorted_result SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -812,6 +846,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--sorted_result SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -825,6 +860,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--sorted_result SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -850,6 +886,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--sorted_result SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -863,6 +900,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--sorted_result SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -888,6 +926,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--sorted_result SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -901,6 +940,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--sorted_result SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -926,6 +966,7 @@ show variables like 'join_cache_level'; set join_cache_level=1; +--sorted_result SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND City.Population > 3000000; @@ -947,12 +988,14 @@ set join_cache_level=6; ALTER TABLE Country MODIFY Name varchar(52) NOT NULL default ''; +--sorted_result SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; ALTER TABLE Country MODIFY Name varchar(300) NOT NULL default ''; +--sorted_result SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -961,12 +1004,14 @@ ALTER TABLE Country ADD COLUMN PopulationBar text; UPDATE Country SET PopulationBar=REPEAT('x', CAST(Population/100000 AS unsigned int)); +--sorted_result SELECT City.Name, Country.Name, Country.PopulationBar FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; set join_buffer_size=256; +--sorted_result SELECT City.Name, Country.Name, Country.PopulationBar FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -974,6 +1019,40 @@ SELECT City.Name, Country.Name, Country.PopulationBar FROM City,Country set join_cache_level=default; set join_buffer_size=default; + +--echo # +--echo # MDEV-17752: Plan changes from hash_index_merge to index_merge with new optimizer defaults +--echo # + +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_use_stat_tables=@@use_stat_tables; +set optimizer_use_condition_selectivity=4; +set use_stat_tables='preferably'; + +use world; +set join_cache_level=4; +CREATE INDEX City_Name ON City(Name); + +--disable_result_log +ANALYZE TABLE City, Country; +--enable_result_log + +EXPLAIN +SELECT Country.Name, Country.Population, City.Name, City.Population + FROM Country LEFT JOIN City + ON City.Country=Country.Code AND City.Population > 5000000 + WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; + +EXPLAIN +SELECT Country.Name, Country.Population, City.Name, City.Population + FROM Country LEFT JOIN City + ON City.Country=Country.Code AND + (City.Population > 5000000 OR City.Name LIKE 'Za%') + WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +set @@use_stat_tables=@save_use_stat_tables; +set join_cache_level=default; + DROP DATABASE world; use test; |