Merge

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;