diff options
Diffstat (limited to 'mysql-test/t/join_cache.test')
| -rw-r--r-- | mysql-test/t/join_cache.test | 222 |
1 files changed, 186 insertions, 36 deletions
diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test index e6669833824..5c6f9950c0b 100644 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -46,14 +46,16 @@ SELECT City.Name, Country.Name, CountryLanguage.Language WHERE City.Country=Country.Code AND CountryLanguage.Country=Country.Code AND City.Name LIKE 'L%' AND Country.Population > 3000000 AND - CountryLanguage.Percentage > 50; + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND CountryLanguage.Country=Country.Code AND City.Name LIKE 'L%' AND Country.Population > 3000000 AND - CountryLanguage.Percentage > 50; + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; set join_cache_level=2; show variables like 'join_cache_level'; @@ -73,14 +75,76 @@ SELECT City.Name, Country.Name, CountryLanguage.Language WHERE City.Country=Country.Code AND CountryLanguage.Country=Country.Code AND City.Name LIKE 'L%' AND Country.Population > 3000000 AND - CountryLanguage.Percentage > 50; + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND CountryLanguage.Country=Country.Code AND City.Name LIKE 'L%' AND Country.Population > 3000000 AND - CountryLanguage.Percentage > 50; + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; + +set join_cache_level=3; +show variables like 'join_cache_level'; + +EXPLAIN +SELECT City.Name, Country.Name FROM City,Country + WHERE City.Country=Country.Code AND + Country.Name LIKE 'L%' AND City.Population > 100000; + +SELECT City.Name, Country.Name FROM City,Country + WHERE City.Country=Country.Code AND + Country.Name LIKE 'L%' AND City.Population > 100000; + +EXPLAIN +SELECT City.Name, Country.Name, CountryLanguage.Language + FROM City,Country,CountryLanguage + WHERE City.Country=Country.Code AND + CountryLanguage.Country=Country.Code AND + City.Name LIKE 'L%' AND Country.Population > 3000000 AND + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; + + +SELECT City.Name, Country.Name, CountryLanguage.Language + FROM City,Country,CountryLanguage + WHERE City.Country=Country.Code AND + CountryLanguage.Country=Country.Code AND + City.Name LIKE 'L%' AND Country.Population > 3000000 AND + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; + + +set join_cache_level=4; +show variables like 'join_cache_level'; + +EXPLAIN +SELECT City.Name, Country.Name FROM City,Country + WHERE City.Country=Country.Code AND + Country.Name LIKE 'L%' AND City.Population > 100000; + +SELECT City.Name, Country.Name FROM City,Country + WHERE City.Country=Country.Code AND + Country.Name LIKE 'L%' AND City.Population > 100000; + +EXPLAIN +SELECT City.Name, Country.Name, CountryLanguage.Language + FROM City,Country,CountryLanguage + WHERE City.Country=Country.Code AND + CountryLanguage.Country=Country.Code AND + City.Name LIKE 'L%' AND Country.Population > 3000000 AND + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; + +SELECT City.Name, Country.Name, CountryLanguage.Language + FROM City,Country,CountryLanguage + WHERE City.Country=Country.Code AND + CountryLanguage.Country=Country.Code AND + City.Name LIKE 'L%' AND Country.Population > 3000000 AND + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; set join_cache_level=default; @@ -104,14 +168,16 @@ SELECT City.Name, Country.Name, CountryLanguage.Language WHERE City.Country=Country.Code AND CountryLanguage.Country=Country.Code AND City.Name LIKE 'L%' AND Country.Population > 3000000 AND - CountryLanguage.Percentage > 50; + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND CountryLanguage.Country=Country.Code AND City.Name LIKE 'L%' AND Country.Population > 3000000 AND - CountryLanguage.Percentage > 50; + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; set join_cache_level=2; show variables like 'join_cache_level'; @@ -131,14 +197,74 @@ SELECT City.Name, Country.Name, CountryLanguage.Language WHERE City.Country=Country.Code AND CountryLanguage.Country=Country.Code AND City.Name LIKE 'L%' AND Country.Population > 3000000 AND - CountryLanguage.Percentage > 50; + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; + +SELECT City.Name, Country.Name, CountryLanguage.Language + FROM City,Country,CountryLanguage + WHERE City.Country=Country.Code AND + CountryLanguage.Country=Country.Code AND + City.Name LIKE 'L%' AND Country.Population > 3000000 AND + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; + +set join_cache_level=3; +show variables like 'join_cache_level'; + +EXPLAIN +SELECT City.Name, Country.Name FROM City,Country + WHERE City.Country=Country.Code AND + Country.Name LIKE 'L%' AND City.Population > 100000; + +SELECT City.Name, Country.Name FROM City,Country + WHERE City.Country=Country.Code AND + Country.Name LIKE 'L%' AND City.Population > 100000; + +EXPLAIN +SELECT City.Name, Country.Name, CountryLanguage.Language + FROM City,Country,CountryLanguage + WHERE City.Country=Country.Code AND + CountryLanguage.Country=Country.Code AND + City.Name LIKE 'L%' AND Country.Population > 3000000 AND + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; + +SELECT City.Name, Country.Name, CountryLanguage.Language + FROM City,Country,CountryLanguage + WHERE City.Country=Country.Code AND + CountryLanguage.Country=Country.Code AND + City.Name LIKE 'L%' AND Country.Population > 3000000 AND + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; + +set join_cache_level=4; +show variables like 'join_cache_level'; + +EXPLAIN +SELECT City.Name, Country.Name FROM City,Country + WHERE City.Country=Country.Code AND + Country.Name LIKE 'L%' AND City.Population > 100000; + +SELECT City.Name, Country.Name FROM City,Country + WHERE City.Country=Country.Code AND + Country.Name LIKE 'L%' AND City.Population > 100000; + +EXPLAIN +SELECT City.Name, Country.Name, CountryLanguage.Language + FROM City,Country,CountryLanguage + WHERE City.Country=Country.Code AND + CountryLanguage.Country=Country.Code AND + City.Name LIKE 'L%' AND Country.Population > 3000000 AND + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND CountryLanguage.Country=Country.Code AND City.Name LIKE 'L%' AND Country.Population > 3000000 AND - CountryLanguage.Percentage > 50; + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; set join_cache_level=default; set join_buffer_size=default; @@ -182,14 +308,16 @@ SELECT City.Name, Country.Name, CountryLanguage.Language WHERE City.Country=Country.Code AND CountryLanguage.Country=Country.Code AND City.Name LIKE 'L%' AND Country.Population > 3000000 AND - CountryLanguage.Percentage > 50; + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND CountryLanguage.Country=Country.Code AND City.Name LIKE 'L%' AND Country.Population > 3000000 AND - CountryLanguage.Percentage > 50; + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; EXPLAIN SELECT Name FROM City @@ -232,14 +360,16 @@ SELECT City.Name, Country.Name, CountryLanguage.Language WHERE City.Country=Country.Code AND CountryLanguage.Country=Country.Code AND City.Name LIKE 'L%' AND Country.Population > 3000000 AND - CountryLanguage.Percentage > 50; + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND CountryLanguage.Country=Country.Code AND City.Name LIKE 'L%' AND Country.Population > 3000000 AND - CountryLanguage.Percentage > 50; + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; EXPLAIN SELECT Name FROM City @@ -283,14 +413,16 @@ SELECT City.Name, Country.Name, CountryLanguage.Language WHERE City.Country=Country.Code AND CountryLanguage.Country=Country.Code AND City.Name LIKE 'L%' AND Country.Population > 3000000 AND - CountryLanguage.Percentage > 50; + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND CountryLanguage.Country=Country.Code AND City.Name LIKE 'L%' AND Country.Population > 3000000 AND - CountryLanguage.Percentage > 50; + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; EXPLAIN SELECT Name FROM City @@ -332,14 +464,16 @@ SELECT City.Name, Country.Name, CountryLanguage.Language WHERE City.Country=Country.Code AND CountryLanguage.Country=Country.Code AND City.Name LIKE 'L%' AND Country.Population > 3000000 AND - CountryLanguage.Percentage > 50; + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND CountryLanguage.Country=Country.Code AND City.Name LIKE 'L%' AND Country.Population > 3000000 AND - CountryLanguage.Percentage > 50; + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; EXPLAIN SELECT Name FROM City @@ -381,14 +515,16 @@ SELECT City.Name, Country.Name, CountryLanguage.Language WHERE City.Country=Country.Code AND CountryLanguage.Country=Country.Code AND City.Name LIKE 'L%' AND Country.Population > 3000000 AND - CountryLanguage.Percentage > 50; + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND CountryLanguage.Country=Country.Code AND City.Name LIKE 'L%' AND Country.Population > 3000000 AND - CountryLanguage.Percentage > 50; + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; EXPLAIN SELECT Name FROM City @@ -430,14 +566,16 @@ SELECT City.Name, Country.Name, CountryLanguage.Language WHERE City.Country=Country.Code AND CountryLanguage.Country=Country.Code AND City.Name LIKE 'L%' AND Country.Population > 3000000 AND - CountryLanguage.Percentage > 50; + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND CountryLanguage.Country=Country.Code AND City.Name LIKE 'L%' AND Country.Population > 3000000 AND - CountryLanguage.Percentage > 50; + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; EXPLAIN SELECT Name FROM City @@ -482,14 +620,16 @@ SELECT City.Name, Country.Name, CountryLanguage.Language WHERE City.Country=Country.Code AND CountryLanguage.Country=Country.Code AND City.Name LIKE 'L%' AND Country.Population > 3000000 AND - CountryLanguage.Percentage > 50; + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND CountryLanguage.Country=Country.Code AND City.Name LIKE 'L%' AND Country.Population > 3000000 AND - CountryLanguage.Percentage > 50; + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; EXPLAIN SELECT Name FROM City @@ -518,14 +658,16 @@ SELECT City.Name, Country.Name, CountryLanguage.Language WHERE City.Country=Country.Code AND CountryLanguage.Country=Country.Code AND City.Name LIKE 'L%' AND Country.Population > 3000000 AND - CountryLanguage.Percentage > 50; + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND CountryLanguage.Country=Country.Code AND City.Name LIKE 'L%' AND Country.Population > 3000000 AND - CountryLanguage.Percentage > 50; + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; EXPLAIN SELECT Name FROM City @@ -554,14 +696,16 @@ SELECT City.Name, Country.Name, CountryLanguage.Language WHERE City.Country=Country.Code AND CountryLanguage.Country=Country.Code AND City.Name LIKE 'L%' AND Country.Population > 3000000 AND - CountryLanguage.Percentage > 50; + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND CountryLanguage.Country=Country.Code AND City.Name LIKE 'L%' AND Country.Population > 3000000 AND - CountryLanguage.Percentage > 50; + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; EXPLAIN SELECT Name FROM City @@ -590,14 +734,16 @@ SELECT City.Name, Country.Name, CountryLanguage.Language WHERE City.Country=Country.Code AND CountryLanguage.Country=Country.Code AND City.Name LIKE 'L%' AND Country.Population > 3000000 AND - CountryLanguage.Percentage > 50; + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND CountryLanguage.Country=Country.Code AND City.Name LIKE 'L%' AND Country.Population > 3000000 AND - CountryLanguage.Percentage > 50; + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; EXPLAIN SELECT Name FROM City @@ -626,14 +772,16 @@ SELECT City.Name, Country.Name, CountryLanguage.Language WHERE City.Country=Country.Code AND CountryLanguage.Country=Country.Code AND City.Name LIKE 'L%' AND Country.Population > 3000000 AND - CountryLanguage.Percentage > 50; + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND CountryLanguage.Country=Country.Code AND City.Name LIKE 'L%' AND Country.Population > 3000000 AND - CountryLanguage.Percentage > 50; + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; EXPLAIN SELECT Name FROM City @@ -662,14 +810,16 @@ SELECT City.Name, Country.Name, CountryLanguage.Language WHERE City.Country=Country.Code AND CountryLanguage.Country=Country.Code AND City.Name LIKE 'L%' AND Country.Population > 3000000 AND - CountryLanguage.Percentage > 50; + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND CountryLanguage.Country=Country.Code AND City.Name LIKE 'L%' AND Country.Population > 3000000 AND - CountryLanguage.Percentage > 50; + CountryLanguage.Percentage > 50 AND + LENGTH(Language) < LENGTH(City.Name) - 2; EXPLAIN SELECT Name FROM City @@ -2642,13 +2792,13 @@ 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 ; +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 ; +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; |