diff options
| author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:00:34 +0000 |
|---|---|---|
| committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:00:34 +0000 |
| commit | 3f619478f796eddbba6e39502fe941b285dd97b1 (patch) | |
| tree | e2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/main/join_cache.test | |
| parent | Initial commit. (diff) | |
| download | mariadb-3f619478f796eddbba6e39502fe941b285dd97b1.tar.xz mariadb-3f619478f796eddbba6e39502fe941b285dd97b1.zip | |
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/main/join_cache.test')
| -rw-r--r-- | mysql-test/main/join_cache.test | 4309 |
1 files changed, 4309 insertions, 0 deletions
diff --git a/mysql-test/main/join_cache.test b/mysql-test/main/join_cache.test new file mode 100644 index 00000000..be33d116 --- /dev/null +++ b/mysql-test/main/join_cache.test @@ -0,0 +1,4309 @@ +--source include/no_valgrind_without_big.inc +--source include/default_optimizer_switch.inc +--source include/default_charset.inc + +--disable_warnings +DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11; +DROP DATABASE IF EXISTS world; +--enable_warnings + +set @org_optimizer_switch=@@optimizer_switch; +set @save_join_cache_level=@@join_cache_level; +set @save_join_buffer_space_limit=@@join_buffer_space_limit; +set @save_join_buffer_size=@@join_buffer_size; +set @save_expensive_subquery_limit=@@expensive_subquery_limit; + +set @@optimizer_switch='optimize_join_buffer_size=on'; +set @@optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; +set @@optimizer_switch='semijoin_with_cache=on'; +set @@optimizer_switch='outer_join_with_cache=on'; +set @@optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; +set @local_optimizer_switch=@@optimizer_switch; + +set @innodb_stats_persistent_save= @@innodb_stats_persistent; +set @innodb_stats_persistent_sample_pages_save= + @@innodb_stats_persistent_sample_pages; + +set global innodb_stats_persistent= 1; +set global innodb_stats_persistent_sample_pages=100; + +set names utf8; + +CREATE DATABASE world; + +use world; + +--source include/world_schema1.inc + +--disable_query_log +--disable_result_log +--disable_warnings +--source include/world.inc +--enable_warnings +--enable_result_log +--enable_query_log + +SELECT COUNT(*) FROM Country; +SELECT COUNT(*) FROM City; +SELECT COUNT(*) FROM CountryLanguage; + +show variables like 'join_buffer_size'; + +set join_cache_level=1; + +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; + +--sorted_result +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; + +--sorted_result +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=2; +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; + +--sorted_result +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; + +--sorted_result +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; + +--sorted_result +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; + + +--sorted_result +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; + +--sorted_result +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; + +--sorted_result +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 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; + +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; + +CREATE INDEX City_Population ON City(Population); +CREATE INDEX City_Name ON City(Name); + +--disable_result_log +ANALYZE TABLE City; +--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; + +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; + +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; + +DROP INDEX City_Population ON City; +DROP INDEX City_Name ON City; + +set join_cache_level=1; + +set join_buffer_size=256; +show variables like 'join_buffer_size'; + +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; + +--sorted_result +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; + +--sorted_result +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=2; +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; + +--sorted_result +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; + +--sorted_result +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; + +--sorted_result +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; + +--sorted_result +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; + +--sorted_result +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; + +--sorted_result +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=@save_join_cache_level; +set join_buffer_size=@save_join_buffer_size; + +DROP DATABASE world; + + +CREATE DATABASE world; + +use world; + +--source include/world_schema.inc + +--disable_query_log +--disable_result_log +--disable_warnings +--source include/world.inc +--enable_warnings +--enable_result_log +--enable_query_log + +show variables like 'join_buffer_size'; +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; + +--sorted_result +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; + +--sorted_result +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; + +EXPLAIN +SELECT Name FROM City + WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND + City.Population > 100000; + +SELECT Name FROM City + WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND + City.Population > 100000; + +#enable after fix MDEV-27871 +--disable_view_protocol + +EXPLAIN +SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) + FROM Country LEFT JOIN CountryLanguage ON + (CountryLanguage.Country=Country.Code AND Language='English') + WHERE + Country.Population > 10000000; + +SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) + FROM Country LEFT JOIN CountryLanguage ON + (CountryLanguage.Country=Country.Code AND Language='English') + WHERE + Country.Population > 10000000; + +--enable_view_protocol + +show variables like 'join_buffer_size'; +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; + +--sorted_result +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; + +--sorted_result +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; + +EXPLAIN +SELECT Name FROM City + WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND + City.Population > 100000; + +SELECT Name FROM City + WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND + City.Population > 100000; + +#enable after fix MDEV-27871 +--disable_view_protocol + +EXPLAIN +SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) + FROM Country LEFT JOIN CountryLanguage ON + (CountryLanguage.Country=Country.Code AND Language='English') + WHERE + Country.Population > 10000000; + +SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) + FROM Country LEFT JOIN CountryLanguage ON + (CountryLanguage.Country=Country.Code AND Language='English') + WHERE + Country.Population > 10000000; + +--enable_view_protocol + +--replace_column 9 # +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; + +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; + +CREATE INDEX City_Name ON City(Name); + +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; + +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; + +DROP INDEX City_Name ON City; + +show variables like 'join_buffer_size'; +set join_cache_level=5; +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; + +--sorted_result +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; + +--sorted_result +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; + +EXPLAIN +SELECT Name FROM City + WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND + City.Population > 100000; + +SELECT Name FROM City + WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND + City.Population > 100000; + +#enable after fix MDEV-27871 +--disable_view_protocol + +EXPLAIN +SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) + FROM Country LEFT JOIN CountryLanguage ON + (CountryLanguage.Country=Country.Code AND Language='English') + WHERE + Country.Population > 10000000; + +SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) + FROM Country LEFT JOIN CountryLanguage ON + (CountryLanguage.Country=Country.Code AND Language='English') + WHERE + Country.Population > 10000000; + +--enable_view_protocol + +set join_cache_level=6; +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; + +--sorted_result +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; + +--sorted_result +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; + +EXPLAIN +SELECT Name FROM City + WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND + City.Population > 100000; + +SELECT Name FROM City + WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND + City.Population > 100000; + +#enable after fix MDEV-27871 +--disable_view_protocol + +EXPLAIN +SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) + FROM Country LEFT JOIN CountryLanguage ON + (CountryLanguage.Country=Country.Code AND Language='English') + WHERE + Country.Population > 10000000; + +SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) + FROM Country LEFT JOIN CountryLanguage ON + (CountryLanguage.Country=Country.Code AND Language='English') + WHERE + Country.Population > 10000000; + +--enable_view_protocol + +set join_cache_level=7; +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; + +--sorted_result +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; + +--sorted_result +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; + +EXPLAIN +SELECT Name FROM City + WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND + City.Population > 100000; + +SELECT Name FROM City + WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND + City.Population > 100000; + +#enable after fix MDEV-27871 +--disable_view_protocol + +EXPLAIN +SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) + FROM Country LEFT JOIN CountryLanguage ON + (CountryLanguage.Country=Country.Code AND Language='English') + WHERE + Country.Population > 10000000; + +SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) + FROM Country LEFT JOIN CountryLanguage ON + (CountryLanguage.Country=Country.Code AND Language='English') + WHERE + Country.Population > 10000000; + +--enable_view_protocol + +set join_cache_level=8; +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; + +--sorted_result +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; + +--sorted_result +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; + +EXPLAIN +SELECT Name FROM City + WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND + City.Population > 100000; + +SELECT Name FROM City + WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND + City.Population > 100000; + +#enable after fix MDEV-27871 +--disable_view_protocol + +EXPLAIN +SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) + FROM Country LEFT JOIN CountryLanguage ON + (CountryLanguage.Country=Country.Code AND Language='English') + WHERE + Country.Population > 10000000; + +SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) + FROM Country LEFT JOIN CountryLanguage ON + (CountryLanguage.Country=Country.Code AND Language='English') + WHERE + Country.Population > 10000000; + +--enable_view_protocol + +set join_buffer_size=256; +show variables like 'join_buffer_size'; + +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; + +--sorted_result +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; + +--sorted_result +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; + +EXPLAIN +SELECT Name FROM City + WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND + City.Population > 100000; + +SELECT Name FROM City + WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND + City.Population > 100000; + +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; + +--sorted_result +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; + +--sorted_result +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; + +EXPLAIN +SELECT Name FROM City + WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND + City.Population > 100000; + +SELECT Name FROM City + WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND + City.Population > 100000; + +set join_cache_level=5; +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; + +--sorted_result +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; + +--sorted_result +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; + +EXPLAIN +SELECT Name FROM City + WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND + City.Population > 100000; + +SELECT Name FROM City + WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND + City.Population > 100000; + +set join_cache_level=6; +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; + +--sorted_result +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; + +--sorted_result +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; + +EXPLAIN +SELECT Name FROM City + WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND + City.Population > 100000; + +SELECT Name FROM City + WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND + City.Population > 100000; + +set join_cache_level=7; +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; + +--sorted_result +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; + +--sorted_result +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; + +EXPLAIN +SELECT Name FROM City + WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND + City.Population > 100000; + +SELECT Name FROM City + WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND + City.Population > 100000; + +set join_cache_level=8; +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; + +--sorted_result +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; + +--sorted_result +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; + +EXPLAIN +SELECT Name FROM City + WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND + City.Population > 100000; + +SELECT Name FROM City + WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND + City.Population > 100000; + +set join_cache_level=@save_join_cache_level; +set join_buffer_size=@save_join_buffer_size; + +set join_cache_level=1; + +--sorted_result +SELECT City.Name, Country.Name FROM City,Country + WHERE City.Country=Country.Code AND City.Population > 3000000; + +set join_cache_level=8; +set join_buffer_size=384; + +--replace_column 9 # +EXPLAIN +SELECT City.Name, Country.Name FROM City,Country + WHERE City.Country=Country.Code AND City.Population > 3000000; + +--sorted_result +SELECT City.Name, Country.Name FROM City,Country + WHERE City.Country=Country.Code AND City.Population > 3000000; + +set join_buffer_size=@save_join_buffer_size; + +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; + +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; + +set join_cache_level=@save_join_cache_level; +set join_buffer_size=@save_join_buffer_size; + + +--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=@save_join_cache_level; + +DROP DATABASE world; + +use test; + +# +# Bug #35685: assertion abort when initializing a BKA cache +# + +CREATE TABLE t1( + affiliatetometaid int NOT NULL default '0', + uniquekey int NOT NULL default '0', + metaid int NOT NULL default '0', + affiliateid int NOT NULL default '0', + xml text, + isactive char(1) NOT NULL default 'Y', + PRIMARY KEY (affiliatetometaid) +); +CREATE UNIQUE INDEX t1_uniquekey ON t1(uniquekey); +CREATE INDEX t1_affiliateid ON t1(affiliateid); +CREATE INDEX t1_metaid on t1 (metaid); +INSERT INTO t1 VALUES + (1616, 1571693233, 1391, 2, NULL, 'Y'), (1943, 1993216749, 1726, 2, NULL, 'Y'); + +CREATE TABLE t2( + metaid int NOT NULL default '0', + name varchar(80) NOT NULL default '', + dateadded timestamp NOT NULL , + xml text, + status int default NULL, + origin int default NULL, + gid int NOT NULL default '1', + formattypeid int default NULL, + PRIMARY KEY (metaid) +); +CREATE INDEX t2_status ON t2(status); +CREATE INDEX t2_gid ON t2(gid); +CREATE INDEX t2_formattypeid ON t2(formattypeid); +INSERT INTO t2 VALUES + (1391, "I Just Died", "2003-10-02 10:07:37", "", 1, NULL, 3, NULL), + (1726, "Me, Myself & I", "2003-12-05 11:24:36", " ", 1, NULL, 3, NULL); + +CREATE TABLE t3( + mediaid int NOT NULL , + metaid int NOT NULL default '0', + formatid int NOT NULL default '0', + status int default NULL, + path varchar(100) NOT NULL default '', + datemodified timestamp NOT NULL default now(), + resourcetype int NOT NULL default '1', + parameters text, + signature int default NULL, + quality int NOT NULL default '255', + PRIMARY KEY (mediaid) +); +CREATE INDEX t3_metaid ON t3(metaid); +CREATE INDEX t3_formatid ON t3(formatid); +CREATE INDEX t3_status ON t3(status); +CREATE INDEX t3_metaidformatid ON t3(metaid,formatid); +CREATE INDEX t3_signature ON t3(signature); +CREATE INDEX t3_quality ON t3(quality); +INSERT INTO t3 VALUES + (6, 4, 8, 0, "010101_anastacia_spmidi.mid", "2004-03-16 13:40:00", 1, NULL, NULL, 255), + (3343, 3, 8, 1, "010102_4VN4bsPwnxRQUJW5Zp1RhG2IL9vvl_8.mid", "2004-03-16 13:40:00", 1, NULL, NULL, 255); +insert into t3 (mediaid, formatid) values (1000,1),(1001,2),(1002,3),(1003,1),(1004,2),(1005,3); + +CREATE TABLE t4( + formatid int NOT NULL , + name varchar(60) NOT NULL default '', + formatclassid int NOT NULL default '0', + mime varchar(60) default NULL, + extension varchar(10) default NULL, + priority int NOT NULL default '0', + canaddtocapability char(1) NOT NULL default 'Y', + PRIMARY KEY (formatid) +); +CREATE INDEX t4_formatclassid ON t4(formatclassid); +CREATE INDEX t4_formats_idx ON t4(canaddtocapability); +INSERT INTO t4 VALUES + (19, "XHTML", 11, "text/html", "xhtml", 10, 'Y'), + (54, "AMR (wide band)", 13, "audio/amr-wb", "awb", 0, 'Y'); + +CREATE TABLE t5( + formatclassid int NOT NULL , + name varchar(60) NOT NULL default '', + priority int NOT NULL default '0', + formattypeid int NOT NULL default '0', + PRIMARY KEY (formatclassid) +); +CREATE INDEX t5_formattypeid on t5(formattypeid); +INSERT INTO t5 VALUES + (11, "Info", 0, 4), (13, "Digital Audio", 0, 2); + +CREATE TABLE t6( + formattypeid int NOT NULL , + name varchar(60) NOT NULL default '', + priority int default NULL, + PRIMARY KEY (formattypeid) +); +INSERT INTO t6 VALUES + (2, "Ringtones", 0); + +CREATE TABLE t7( + metaid int NOT NULL default '0', + artistid int NOT NULL default '0', + PRIMARY KEY (metaid,artistid) +); +INSERT INTO t7 VALUES + (4, 5), (3, 4); + +CREATE TABLE t8( + artistid int NOT NULL , + name varchar(80) NOT NULL default '', + PRIMARY KEY (artistid) +); +INSERT INTO t8 VALUES + (5, "Anastacia"), (4, "John Mayer"); + +CREATE TABLE t9( + subgenreid int NOT NULL default '0', + metaid int NOT NULL default '0', + PRIMARY KEY (subgenreid,metaid) +) ; +CREATE INDEX t9_subgenreid ON t9(subgenreid); +CREATE INDEX t9_metaid ON t9(metaid); +INSERT INTO t9 VALUES + (138, 4), (31, 3); + +CREATE TABLE t10( + subgenreid int NOT NULL , + genreid int NOT NULL default '0', + name varchar(80) NOT NULL default '', + PRIMARY KEY (subgenreid) +) ; +CREATE INDEX t10_genreid ON t10(genreid); +INSERT INTO t10 VALUES + (138, 19, ''), (31, 3, ''); + +CREATE TABLE t11( + genreid int NOT NULL default '0', + name char(80) NOT NULL default '', + priority int NOT NULL default '0', + masterclip char(1) default NULL, + PRIMARY KEY (genreid) +) ; +CREATE INDEX t11_masterclip ON t11( masterclip); +INSERT INTO t11 VALUES + (19, "Pop & Dance", 95, 'Y'), (3, "Rock & Alternative", 100, 'Y'); + +set join_cache_level=6; + +EXPLAIN +SELECT t1.uniquekey, t1.xml AS affiliateXml, + t8.name AS artistName, t8.artistid, + t11.name AS genreName, t11.genreid, t11.priority AS genrePriority, + t10.subgenreid, t10.name AS subgenreName, + t2.name AS metaName, t2.metaid, t2.xml AS metaXml, + t4.priority + t5.priority + t6.priority AS overallPriority, + t3.path AS path, t3.mediaid, + t4.formatid, t4.name AS formatName, + t5.formatclassid, t5.name AS formatclassName, + t6.formattypeid, t6.name AS formattypeName +FROM t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11 +WHERE t7.metaid = t2.metaid AND t7.artistid = t8.artistid AND + t9.metaid = t2.metaid AND t9.subgenreid = t10.subgenreid AND + t10.genreid = t11.genreid AND t3.metaid = t2.metaid AND + t3.formatid = t4.formatid AND t4.formatclassid = t5.formatclassid AND + t4.canaddtocapability = 'Y' AND t5.formattypeid = t6.formattypeid AND + t6.formattypeid IN (2) AND (t3.formatid IN (31, 8, 76)) AND + t1.metaid = t2.metaid AND t1.affiliateid = '2'; + +SELECT t1.uniquekey, t1.xml AS affiliateXml, + t8.name AS artistName, t8.artistid, + t11.name AS genreName, t11.genreid, t11.priority AS genrePriority, + t10.subgenreid, t10.name AS subgenreName, + t2.name AS metaName, t2.metaid, t2.xml AS metaXml, + t4.priority + t5.priority + t6.priority AS overallPriority, + t3.path AS path, t3.mediaid, + t4.formatid, t4.name AS formatName, + t5.formatclassid, t5.name AS formatclassName, + t6.formattypeid, t6.name AS formattypeName +FROM t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11 +WHERE t7.metaid = t2.metaid AND t7.artistid = t8.artistid AND + t9.metaid = t2.metaid AND t9.subgenreid = t10.subgenreid AND + t10.genreid = t11.genreid AND t3.metaid = t2.metaid AND + t3.formatid = t4.formatid AND t4.formatclassid = t5.formatclassid AND + t4.canaddtocapability = 'Y' AND t5.formattypeid = t6.formattypeid AND + t6.formattypeid IN (2) AND (t3.formatid IN (31, 8, 76)) AND + t1.metaid = t2.metaid AND t1.affiliateid = '2'; + +DROP TABLE t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11; + +# +# Bug #37131: 3-way join query with BKA used with a small buffer and +# only for the third table +# + +CREATE TABLE t1 (a1 int, filler1 char(64) default ' ' ); +CREATE TABLE t2 ( + a2 int, b2 int, filler2 char(64) default ' ', + PRIMARY KEY idx(a2,b2,filler2) +) ; +CREATE TABLE t3 (b3 int, c3 int, INDEX idx(b3)); + +INSERT INTO t1(a1) VALUES + (4), (7), (1), (9), (8), (5), (3), (6), (2); +INSERT INTO t2(a2,b2) VALUES + (1,30), (3,40), (2,61), (6,73), (8,92), (9,27), (4,18), (5,84), (7,56), + (4,14), (6,76), (8,98), (7,55), (1,39), (2,68), (3,45), (9,21), (5,81), + (5,88), (2,65), (6,74), (9,23), (1,37), (3,44), (4,17), (8,99), (7,51), + (9,28), (7,52), (1,33), (4,13), (5,87), (3,43), (8,91), (2,62), (6,79), + (3,49), (8,93), (7,34), (5,82), (6,78), (2,63), (1,32), (9,22), (4,11); +INSERT INTO t3 VALUES + (30,302), (92,923), (18,187), (45,459), (30,309), + (39,393), (68,685), (45,458), (21,210), (81,817), + (40,405), (61,618), (73,738), (92,929), (27,275), + (18,188), (84,846), (56,564), (14,144), (76,763), + (98,982), (55,551), (17,174), (99,998), (51,513), + (28,282), (52,527), (33,336), (13,138), (87,878), + (43,431), (91,916), (62,624), (79,797), (49,494), + (93,933), (34,347), (82,829), (78,780), (63,634), + (32,329), (22,228), (11,114), (74,749), (23,236); + +set join_cache_level=1; + +EXPLAIN +SELECT a1<>a2, a1, a2, b2, b3, c3, + SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2 +FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7; + +SELECT a1<>a2, a1, a2, b2, b3, c3, + SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2 +FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7; + +set join_cache_level=5; +set join_buffer_size=512; + +EXPLAIN +SELECT a1<>a2, a1, a2, b2, b3, c3, + SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2 +FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7; + +SELECT a1<>a2, a1, a2, b2, b3, c3, + SUBSTR(filler1,1,1) AS s1, SUBSTR(filler2,1,1) AS s2 +FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7; + +DROP TABLE t1,t2,t3; + +# +# Bug #37690: crash with a tiny buffer when using BKA_JOIN_CACHE_UNIQUE +# + +CREATE TABLE t1 (a int, b int, INDEX idx(b)); +CREATE TABLE t2 (a int, b int, INDEX idx(a)); +INSERT INTO t1 VALUES (5,30), (3,20), (7,40), (2,10), (8,30), (1,10), (4,20); +INSERT INTO t2 VALUES (7,10), (1,20), (2,20), (8,20), (8,10), (1,20); +INSERT INTO t2 VALUES (1,10), (4,20), (3,20), (7,20), (7,10), (1,20); +INSERT INTO t2 VALUES (17,10), (11,20), (12,20), (18,20), (18,10), (11,20); +INSERT INTO t2 VALUES (11,10), (14,20), (13,20), (17,20), (17,10), (11,20); + +set join_buffer_size=32; +set join_cache_level=8; + +EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b >= 30; +--sorted_result +SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b >= 30; + +DROP TABLE t1,t2; + +--echo # +--echo # Bug #40134: outer join with not exists optimization and join buffer +--echo # + +set join_cache_level=@save_join_cache_level; +set join_buffer_size=@save_join_buffer_size; + +CREATE TABLE t1 (a int NOT NULL); +INSERT INTO t1 VALUES (2), (4), (3), (5), (1); +CREATE TABLE t2 (a int NOT NULL, b int NOT NULL, INDEX i_a(a)); +INSERT INTO t2 VALUES (4,10), (2,10), (2,30), (2,20), (4,20); +INSERT INTO t2 VALUES (14,10), (12,10), (15,30), (12,20), (14,20); + +EXPLAIN +SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL; +SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL; + +SET join_cache_level=6; +EXPLAIN +SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL; +SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a WHERE t2.b IS NULL; + +DROP TABLE t1, t2; + +set join_cache_level=@save_join_cache_level; +set join_buffer_size=@save_join_buffer_size; + +--echo # +--echo # BUG#40136: Group by is ignored when join buffer is used for an outer join +--echo # +create table t1(a int PRIMARY KEY, b int); +insert into t1 values + (5, 10), (2, 70), (7, 80), (6, 20), (1, 50), (9, 40), (8, 30), (3, 60); +create table t2 (p int, a int, INDEX i_a(a)); +insert into t2 values + (103, 7), (109, 3), (102, 3), (108, 1), (106, 3), + (107, 7), (105, 1), (101, 3), (100, 7), (110, 1); +set @save_join_cache_level=@@join_cache_level; +set join_cache_level=6; +--echo The following must not show "using join cache": +explain +select t1.a, count(t2.p) as count + from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a; +select t1.a, count(t2.p) as count + from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a; +set join_cache_level=@save_join_cache_level; +drop table t1, t2; + +--echo # +--echo # BUG#40268: Nested outer join with not null-rejecting where condition +--echo # over an inner table which is not the last in the nest +--echo # + +CREATE TABLE t2 (a int, b int, c int); +CREATE TABLE t3 (a int, b int, c int); +CREATE TABLE t4 (a int, b int, c int); + +INSERT INTO t2 VALUES (3,3,0), (4,2,0), (5,3,0); +INSERT INTO t3 VALUES (1,2,0), (2,2,0); +INSERT INTO t4 VALUES (3,2,0), (4,2,0); + +set join_cache_level=6; + +SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b + FROM t2 LEFT JOIN (t3, t4) ON t2.b=t4.b + WHERE t3.a+2<t2.a OR t3.c IS NULL; + +set join_cache_level=@save_join_cache_level; +DROP TABLE t2, t3, t4; + +--echo # +--echo # Bug #40192: outer join with where clause when using BNL +--echo # + +create table t1 (a int, b int); +insert into t1 values (2, 20), (3, 30), (1, 10); +create table t2 (a int, c int); +insert into t2 values (1, 101), (3, 102), (1, 100); + +set join_cache_level=6; + +select * from t1 left join t2 on t1.a=t2.a; +explain select * from t1 left join t2 on t1.a=t2.a where t2.c=102 or t2.c is null; +select * from t1 left join t2 on t1.a=t2.a where t2.c=102 or t2.c is null; + +set join_cache_level=@save_join_cache_level; +drop table t1, t2; + +--echo # +--echo # Bug #40317: outer join with with constant on expression equal to FALSE +--echo # + +create table t1 (a int); +insert into t1 values (30), (40), (20); +create table t2 (b int); +insert into t2 values (200), (100); + +set join_cache_level=6; + +select * from t1 left join t2 on (1=0); +explain select * from t1 left join t2 on (1=0) where a=40; +select * from t1 left join t2 on (1=0) where a=40; + +set join_cache_level=0; +explain select * from t1 left join t2 on (1=0); + +set join_cache_level=@save_join_cache_level; +drop table t1, t2; + +--echo # +--echo # Bug #41204: small buffer with big rec_per_key for ref access +--echo # + +CREATE TABLE t1 (a int); + +INSERT INTO t1 VALUES (0); +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1 VALUES (20000), (10000); + +CREATE TABLE t2 (pk int AUTO_INCREMENT PRIMARY KEY, b int, c int, INDEX idx(b)); +INSERT INTO t2(b,c) VALUES (10000, 3), (20000, 7), (20000, 1), (10000, 9), (20000, 5); +INSERT INTO t2(b,c) SELECT b,c FROM t2; +INSERT INTO t2(b,c) SELECT b,c FROM t2; +INSERT INTO t2(b,c) SELECT b,c FROM t2; +INSERT INTO t2(b,c) SELECT b,c FROM t2; +INSERT INTO t2(b,c) SELECT b,c FROM t2; +INSERT INTO t2(b,c) SELECT b,c FROM t2; +INSERT INTO t2(b,c) SELECT b,c FROM t2; +INSERT INTO t2(b,c) SELECT b,c FROM t2; + +--disable_result_log +ANALYZE TABLE t1,t2; +--enable_result_log + +set join_cache_level=6; +set join_buffer_size=1024; + +EXPLAIN SELECT AVG(c) FROM t1,t2 WHERE t1.a=t2.b; +SELECT AVG(c) FROM t1,t2 WHERE t1.a=t2.b; + +set join_buffer_size=@save_join_buffer_size; +set join_cache_level=@save_join_cache_level; + +DROP TABLE t1, t2; + +--echo # +--echo # Bug #41894: big join buffer of level 7 used to join records +--echo # with null values in place of varchar strings +--echo # + +CREATE TABLE t1 (a int NOT NULL AUTO_INCREMENT PRIMARY KEY, + b varchar(127) DEFAULT NULL); + +INSERT INTO t1(a) VALUES (1); +INSERT INTO t1(b) SELECT b FROM t1; +INSERT INTO t1(b) SELECT b FROM t1; +INSERT INTO t1(b) SELECT b FROM t1; +INSERT INTO t1(b) SELECT b FROM t1; +INSERT INTO t1(b) SELECT b FROM t1; +INSERT INTO t1(b) SELECT b FROM t1; +INSERT INTO t1(b) SELECT b FROM t1; +INSERT INTO t1(b) SELECT b FROM t1; +INSERT INTO t1(b) SELECT b FROM t1; +INSERT INTO t1(b) SELECT b FROM t1; +INSERT INTO t1(b) SELECT b FROM t1; +INSERT INTO t1(b) SELECT b FROM t1; +INSERT INTO t1(b) SELECT b FROM t1; +INSERT INTO t1(b) SELECT b FROM t1; + +CREATE TABLE t2 (a int NOT NULL PRIMARY KEY, b varchar(127) DEFAULT NULL); +INSERT INTO t2 SELECT * FROM t1; + +CREATE TABLE t3 (a int NOT NULL PRIMARY KEY, b varchar(127) DEFAULT NULL); +INSERT INTO t3 SELECT * FROM t1; + +set join_cache_level=7; +set join_buffer_size=1024*1024; + +EXPLAIN +SELECT COUNT(*) FROM t1,t2,t3 + WHERE t1.a=t2.a AND t2.a=t3.a AND + t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL; + +SELECT COUNT(*) FROM t1,t2,t3 + WHERE t1.a=t2.a AND t2.a=t3.a AND + t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL; + +set join_buffer_size=@save_join_buffer_size; +set join_cache_level=@save_join_cache_level; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # Bug #42020: join buffer is used for outer join with fields of +--echo # several outer tables in join buffer +--echo # + +CREATE TABLE t1 ( + a bigint NOT NULL, + PRIMARY KEY (a) +); +INSERT INTO t1 VALUES + (2), (1); + +CREATE TABLE t2 ( + a bigint NOT NULL, + b bigint NOT NULL, + PRIMARY KEY (a,b) +); +INSERT INTO t2 VALUES + (2,30), (2,40), (2,50), (2,60), (2,70), (2,80), + (1,10), (1, 20), (1,30), (1,40), (1,50); + +CREATE TABLE t3 ( + pk bigint NOT NULL AUTO_INCREMENT, + a bigint NOT NULL, + b bigint NOT NULL, + val bigint DEFAULT '0', + PRIMARY KEY (pk), + KEY idx (a,b) +); +INSERT INTO t3(a,b) VALUES + (2,30), (2,40), (2,50), (2,60), (2,70), (2,80), + (4,30), (4,40), (4,50), (4,60), (4,70), (4,80), + (5,30), (5,40), (5,50), (5,60), (5,70), (5,80), + (7,30), (7,40), (7,50), (7,60), (7,70), (7,80); + +set join_cache_level=0; + +SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val + FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) + WHERE t1.a=t2.a; + +set join_cache_level=6; +set join_buffer_size=256; + +EXPLAIN +SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val + FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) + WHERE t1.a=t2.a; +--sorted_result +SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val + FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) + WHERE t1.a=t2.a; + +DROP INDEX idx ON t3; +set join_cache_level=2; + +EXPLAIN +SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val + FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) + WHERE t1.a=t2.a; + +--sorted_result +SELECT t1.a, t2.a, t3.a, t2.b, t3.b, t3.val + FROM (t1,t2) LEFT JOIN t3 ON (t1.a=t3.a AND t2.b=t3.b) + WHERE t1.a=t2.a; + +set join_buffer_size=@save_join_buffer_size; +set join_cache_level=@save_join_cache_level; + +DROP TABLE t1,t2,t3; + +# +# WL#4424 Full index condition pushdown with batched key access join +# +create table t1(f1 int, f2 int); +insert into t1 values (1,1),(2,2),(3,3); +create table t2(f1 int not null, f2 int not null, f3 char(200), key(f1,f2)); +insert into t2 values (1,1, 'qwerty'),(1,2, 'qwerty'),(1,3, 'qwerty'); +insert into t2 values (2,1, 'qwerty'),(2,2, 'qwerty'),(2,3, 'qwerty'), + (2,4, 'qwerty'),(2,5, 'qwerty'); +insert into t2 values (3,1, 'qwerty'),(3,4, 'qwerty'); +insert into t2 values (4,1, 'qwerty'),(4,2, 'qwerty'),(4,3, 'qwerty'), + (4,4, 'qwerty'); +insert into t2 values (1,1, 'qwerty'),(1,2, 'qwerty'),(1,3, 'qwerty'); +insert into t2 values (2,1, 'qwerty'),(2,2, 'qwerty'),(2,3, 'qwerty'), + (2,4, 'qwerty'),(2,5, 'qwerty'); +insert into t2 values (3,1, 'qwerty'),(3,4, 'qwerty'); +insert into t2 values (4,1, 'qwerty'),(4,2, 'qwerty'),(4,3, 'qwerty'), + (4,4, 'qwerty'); + +--disable_ps2_protocol +flush status; +set join_cache_level=5; +select t2.f1, t2.f2, t2.f3 from t1,t2 +where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1; + +explain select t2.f1, t2.f2, t2.f3 from t1,t2 +where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2; +show status like "Handler_icp%"; + +set join_cache_level=6; +select t2.f1, t2.f2, t2.f3 from t1,t2 +where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1; + +explain select t2.f1, t2.f2, t2.f3 from t1,t2 +where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2; +show status like "Handler_icp%"; + +set join_cache_level=7; +select t2.f1, t2.f2, t2.f3 from t1,t2 +where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1; + +explain select t2.f1, t2.f2, t2.f3 from t1,t2 +where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2; +show status like "Handler_icp%"; + +set join_cache_level=8; +select t2.f1, t2.f2, t2.f3 from t1,t2 +where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1; + +explain select t2.f1, t2.f2, t2.f3 from t1,t2 +where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2; +show status like "Handler_icp%"; + +--enable_ps2_protocol + +drop table t1,t2; +set join_cache_level=@save_join_cache_level; + +--echo # +--echo # Bug #42955: join with GROUP BY/ORDER BY and when BKA is enabled +--echo # + +create table t1 (d int, id1 int, index idx1 (d, id1)); +insert into t1 values + (3, 20), (2, 40), (3, 10), (1, 10), (3, 20), (1, 40), (2, 30), (3, 30); + +create table t2 (id1 int, id2 int, index idx2 (id1)); +insert into t2 values + (20, 100), (30, 400), (20, 400), (30, 200), (10, 300), (10, 200), (40, 100), + (40, 200), (30, 300), (10, 400), (20, 200), (20, 300); +insert into t2 values + (21, 10), (31, 400), (21, 400), (31, 200), (11, 300), (11, 200), (41, 100), + (41, 200), (31, 300), (11, 400), (21, 200), (21, 300); + +set join_cache_level=6; + +explain +select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1 + where t1.d=3 group by t1.id1; + +select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1 + where t1.d=3 group by t1.id1; + +explain +select t1.id1 from t1 join t2 on t1.id1=t2.id1 + where t1.d=3 and t2.id2 > 200 order by t1.id1; + +select t1.id1 from t1 join t2 on t1.id1=t2.id1 + where t1.d=3 and t2.id2 > 200 order by t1.id1; + +set join_cache_level=@save_join_cache_level; + +drop table t1,t2; + +--echo # +--echo # Bug #44019: star-like multi-join query executed join_cache_level=6 +--echo # + +create table t1 (a int, b int, c int, d int); +create table t2 (b int, e varchar(16), index idx(b)); +create table t3 (d int, f varchar(16), index idx(d)); +create table t4 (c int, g varchar(16), index idx(c)); + +insert into t1 values + (5, 50, 500, 5000), (3, 30, 300, 3000), (9, 90, 900, 9000), + (2, 20, 200, 2000), (4, 40, 400, 4000), (8, 80, 800, 800), + (7, 70, 700, 7000); +insert into t2 values + (30, 'bbb'), (10, 'b'), (70, 'bbbbbbb'), (60, 'bbbbbb'), + (31, 'bbb'), (11, 'b'), (71, 'bbbbbbb'), (61, 'bbbbbb'), + (32, 'bbb'), (12, 'b'), (72, 'bbbbbbb'), (62, 'bbbbbb'); +insert into t2 values + (130, 'bbb'), (110, 'b'), (170, 'bbbbbbb'), (160, 'bbbbbb'), + (131, 'bbb'), (111, 'b'), (171, 'bbbbbbb'), (161, 'bbbbbb'), + (132, 'bbb'), (112, 'b'), (172, 'bbbbbbb'), (162, 'bbbbbb'); +insert into t3 values + (4000, 'dddd'), (3000, 'ddd'), (1000, 'd'), (8000, 'dddddddd'), + (4001, 'dddd'), (3001, 'ddd'), (1001, 'd'), (8001, 'dddddddd'), + (4002, 'dddd'), (3002, 'ddd'), (1002, 'd'), (8002, 'dddddddd'); +insert into t3 values + (14000, 'dddd'), (13000, 'ddd'), (11000, 'd'), (18000, 'dddddddd'), + (14001, 'dddd'), (13001, 'ddd'), (11001, 'd'), (18001, 'dddddddd'), + (4002, 'dddd'), (3002, 'ddd'), (1002, 'd'), (8002, 'dddddddd'); +insert into t4 values + (200, 'cc'), (600, 'cccccc'), (300, 'ccc'), (500, 'ccccc'), + (201, 'cc'), (601, 'cccccc'), (301, 'ccc'), (501, 'ccccc'), + (202, 'cc'), (602, 'cccccc'), (302, 'ccc'), (502, 'ccccc'); +insert into t4 values + (1200, 'cc'), (1600, 'cccccc'), (1300, 'ccc'), (1500, 'ccccc'), + (1201, 'cc'), (1601, 'cccccc'), (1301, 'ccc'), (1501, 'ccccc'), + (1202, 'cc'), (1602, 'cccccc'), (1302, 'ccc'), (1502, 'ccccc'); + +--disable_result_log +--disable_warnings +analyze table t2,t3,t4; +--enable_warnings +--enable_result_log + +set join_cache_level=1; +explain +select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4 + where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c; + +select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4 + where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c; + +set join_cache_level=6; +explain +select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4 + where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c; + +select t1.a, t1.b, t1.c, t1.d, t2.e, t3.f, t4.g from t1,t2,t3,t4 + where t2.b=t1.b and t3.d=t1.d and t4.c=t1.c; + +set join_cache_level=@save_join_cache_level; + +drop table t1,t2,t3,t4; + +--echo # +--echo # Bug #44250: Corruption of linked join buffers when using BKA +--echo # + +CREATE TABLE t1 ( + id1 bigint(20) DEFAULT NULL, + id2 bigint(20) DEFAULT NULL, + id3 bigint(20) DEFAULT NULL, + num1 bigint(20) DEFAULT NULL, + num2 int(11) DEFAULT NULL, + num3 bigint(20) DEFAULT NULL +); + +CREATE TABLE t2 ( + id3 bigint(20) NOT NULL DEFAULT '0', + id4 bigint(20) DEFAULT NULL, + enum1 enum('Enabled','Disabled','Paused') DEFAULT NULL, + PRIMARY KEY (id3) +); + +CREATE TABLE t3 ( + id4 bigint(20) NOT NULL DEFAULT '0', + text1 text, + PRIMARY KEY (id4) +); + +CREATE TABLE t4 ( + id2 bigint(20) NOT NULL DEFAULT '0', + dummy int(11) DEFAULT '0', + PRIMARY KEY (id2) +); + +CREATE TABLE t5 ( + id1 bigint(20) NOT NULL DEFAULT '0', + id2 bigint(20) NOT NULL DEFAULT '0', + enum2 enum('Active','Deleted','Paused') DEFAULT NULL, + PRIMARY KEY (id1,id2) +); + +--disable_query_log +--disable_result_log +--disable_warnings + +INSERT INTO t1 VALUES +(228172702,72485641,2667134182,10,1,14),(228172702,94266195,2667134182,134,0,134), +(228172702,94266195,2667134182,15,0,15),(228172702,94266195,2667134182,2,0,3), +(228172702,818095880,2667134182,1,1,1),(228172702,1004959639,2667134182,3,0,3), +(228172702,1297484422,2667134182,1,2,1),(228172702,1730911800,2667134182,11,0,28), +(228172702,1730911800,2667134182,4,0,4),(228172702,2182755982,2667134182,5,0,15), +(228172702,2182755982,2667134182,1,0,1),(228172702,2968841184,2667134182,1,0,1), +(228172702,4765525626,2667134182,2,0,3),(228172702,4765525626,2667134182,29,0,38), +(228172702,4765525626,2667134182,7,0,7),(228172702,4765525626,2667134182,7,0,8), +(228172702,5330573302,2667134182,1,0,1),(228512602,191149872,935692942,3,0,17), +(228512602,259118753,935692942,13,7,13),(228512602,259118753,935692942,83,33,83), +(228512602,585705465,935692942,1,0,1),(228512602,585716775,935692942,1,0,1), +(228512602,585716775,935692942,6,6,6),(228512602,585716775,935692942,1,1,1), +(228512602,1105371172,935692942,2,0,3),(228512602,1105371172,935692942,7,2,7), +(228512602,1314223462,935692942,1,0,1),(228512602,1314223642,935692942,1,1,1), +(228512602,1411060522,935692942,1,0,1),(228512602,1467398182,935692942,1,0,1), +(228512602,1467398182,935692942,3,0,4),(228512602,1467398242,935692942,10,0,41), +(228512602,1467398242,935692942,28,0,40),(228512602,1467398242,935692942,0,0,0), +(228512602,1467398242,935692942,29,2,33),(228512602,1734178942,935692942,1,0,1), +(228512602,1734179122,935692942,1,0,4),(228512602,1734179122,935692942,3,0,6), +(228512602,1953612870,935692942,1,0,1),(228512602,2271510562,935692942,1,1,1), +(228512602,2271525022,935692942,0,0,0),(228512602,3058831402,935692942,1,1,1), +(228512602,3723638842,935692942,1,1,1),(228512602,3723638842,935692942,4,3,4), +(228512602,3723836602,935692942,1,1,1),(228512602,3723836842,935692942,1,1,1), +(228512602,3723836962,935692942,1,1,1),(228512602,3723988102,935692942,11,4,11), +(228512602,3723989182,935692942,8,3,8),(228512602,5920283002,935692942,1,0,1), +(228512602,5920314232,935692942,1,0,1),(228512602,191149872,1241589892,0,0,0), +(228512602,191149872,1241589892,2,0,4),(228512602,191149872,1241589892,0,0,0), +(228512602,259118753,1241589892,8,4,8),(228512602,259118753,1241589892,70,33,70), +(228512602,259118753,1241589892,1,1,1),(228512602,585716775,1241589892,8,7,8), +(228512602,1105371172,1241589892,1,0,1),(228512602,1105371172,1241589892,9,0,9), +(228512602,1314223462,1241589892,1,0,1),(228512602,1411060522,1241589892,1,1,1), +(228512602,1467398182,1241589892,1,0,1),(228512602,1467398182,1241589892,4,1,4), +(228512602,1467398182,1241589892,1,0,1),(228512602,1467398242,1241589892,10,0,28), +(228512602,1467398242,1241589892,37,1,78),(228512602,1467398242,1241589892,28,9,30), +(228512602,1467398242,1241589892,5,0,6),(228512602,1734179122,1241589892,3,1,18), +(228512602,1734179122,1241589892,1,1,1),(228512602,1734179122,1241589892,2,0,3), +(228512602,1953611430,1241589892,1,1,1),(228512602,1953611430,1241589892,1,1,1), +(228512602,1953612870,1241589892,1,0,1),(228512602,2026844250,1241589892,1,0,1), +(228512602,2271510562,1241589892,1,1,1),(228512602,2271525022,1241589892,1,0,1), +(228512602,2941612417,1241589892,1,0,1),(228512602,3723988102,1241589892,1,0,1); +INSERT INTO t1 VALUES +(228512602,3723988102,1241589892,11,4,11),(228512602,3723989002,1241589892,1,0,1), +(228512602,3752960902,1241589892,2,2,4),(228808822,17304242,935693782,6,0,17), +(228808822,17304242,935693782,28,1,50),(228808822,17304242,935693782,29,3,61), +(228808822,17304242,935693782,6,0,13),(228808822,30931012,935693782,21,0,60), +(228808822,30931012,935693782,5,0,13),(228808822,37254452,935693782,3,0,3), +(228808822,42726891,935693782,1,0,4),(228808822,42726891,935693782,3,0,6), +(228808822,76261151,935693782,8,0,18),(228808822,88240139,935693782,1,0,1), +(228808822,88240139,935693782,3,0,3),(228808822,94730895,935693782,2,0,4), +(228808822,179737402,935693782,10,0,13),(228808822,179737402,935693782,7,0,8), +(228808822,179737402,935693782,3,0,4),(228808822,271288782,935693782,1,0,6), +(228808822,304690943,935693782,5,2,10),(228808822,304691183,935693782,4,0,16), +(228808822,568994960,935693782,1,0,1),(228808822,631705925,935693782,1,0,1), +(228808822,631745165,935693782,1,0,1),(228808822,631749605,935693782,1,0,4), +(228808822,1057787002,935693782,1,0,1),(228808822,1057787002,935693782,2,1,4), +(228808822,1057787002,935693782,12,1,20),(228808822,1057788022,935693782,2,0,40), +(228808822,1057788022,935693782,2,1,3),(228808822,1057788022,935693782,9,2,16), +(228808822,1335646822,935693782,3,1,6),(228808822,1335646882,935693782,1,0,3), +(228808822,1335646882,935693782,1,0,3),(228808822,1335646942,935693782,7,2,15), +(228808822,5510586183,935693782,1,1,1),(228808822,17304242,2482416112,11,0,28), +(228808822,17304242,2482416112,34,0,62),(228808822,17304242,2482416112,43,2,89), +(228808822,17304242,2482416112,9,0,19),(228808822,30931012,2482416112,32,2,84), +(228808822,30931012,2482416112,6,0,14),(228808822,30931012,2482416112,2,0,9), +(228808822,37254452,2482416112,1,1,1),(228808822,42726891,2482416112,2,0,10), +(228808822,76261151,2482416112,11,0,26),(228808822,88240139,2482416112,3,0,3), +(228808822,88240139,2482416112,1,0,1),(228808822,88240139,2482416112,3,0,4), +(228808822,94730895,2482416112,1,0,3),(228808822,125469602,2482416112,0,0,0), +(228808822,179737402,2482416112,4,0,10),(228808822,179737402,2482416112,8,1,9), +(228808822,179737402,2482416112,7,1,9),(228808822,179737402,2482416112,1,0,1), +(228808822,271288782,2482416112,2,0,14),(228808822,304690943,2482416112,3,0,6), +(228808822,304691183,2482416112,1,0,4),(228808822,555689643,2482416112,2,1,8), +(228808822,555689643,2482416112,1,0,4),(228808822,631705925,2482416112,1,0,1), +(228808822,631712555,2482416112,1,0,1),(228808822,631745165,2482416112,1,0,1), +(228808822,710348755,2482416112,1,0,1),(228808822,753718113,2482416112,1,0,1), +(228808822,1057787002,2482416112,1,0,4),(228808822,1057787002,2482416112,1,0,1), +(228808822,1057787002,2482416112,4,1,7),(228808822,1057788022,2482416112,7,0,12), +(228808822,1057788022,2482416112,3,0,37),(228808822,1057788022,2482416112,0,0,0), +(228808822,1057788022,2482416112,12,0,15),(228808822,1335646822,2482416112,14,1,28), +(228808822,1335646882,2482416112,1,1,3),(228808822,1335646942,2482416112,5,1,9), +(228808822,1335646942,2482416112,1,0,1),(230941762,16069490,2691187582,0,0,0), +(230941762,16705991,2691187582,16,0,30),(230941762,16705991,2691187582,12,3,12); +INSERT INTO t1 VALUES +(230941762,16705991,2691187582,1,0,1),(230941762,27714032,2691187582,6,0,16), +(230941762,27714032,2691187582,1,0,1),(230941762,27714032,2691187582,9,0,14), +(230941762,28676710,2691187582,3,1,4),(230941762,370319272,2691187582,7,0,7), +(230941762,1409814802,2691187582,1,0,3),(230941762,1409814982,2691187582,1,0,1), +(230941762,1409814982,2691187582,1,1,1),(230941762,2069703256,2691187582,1,0,3), +(230941762,16705991,2691187672,8,1,20),(230941762,16705991,2691187672,11,6,11), +(230941762,16705991,2691187672,1,0,1),(230941762,27714032,2691187672,5,0,20), +(230941762,27714032,2691187672,1,0,10),(230941762,27714032,2691187672,12,2,17), +(230941762,28676710,2691187672,1,0,1),(230941762,142889951,2691187672,2,0,10), +(230941762,172526592,2691187672,1,1,1),(230941762,293109282,2691187672,1,0,1), +(230941762,370319272,2691187672,10,0,10),(230941762,1409814802,2691187672,1,0,3), +(230941762,1409814922,2691187672,1,0,1),(230941762,1409814982,2691187672,1,0,1), +(230941762,16069490,2694472582,1,1,1),(230941762,16069490,2694472582,1,1,1), +(230941762,16705991,2694472582,15,0,45),(230941762,16705991,2694472582,13,2,15), +(230941762,27714032,2694472582,9,0,34),(230941762,27714032,2694472582,2,0,4), +(230941762,27714032,2694472582,10,2,14),(230941762,28676710,2694472582,4,0,12), +(230941762,28676710,2694472582,1,0,1),(230941762,172526592,2694472582,1,0,4), +(230941762,293109282,2694472582,1,0,1),(230941762,370319272,2694472582,6,0,6), +(230941762,1409814802,2694472582,1,0,3),(230941762,1409814862,2694472582,1,0,4), +(230941762,1409814982,2694472582,1,0,1),(230941762,2680867980,2694472582,1,0,3), +(230942122,25451690,935695702,1,0,9),(230942122,31549341,935695702,2,0,18), +(230942122,31549341,935695702,2,0,4),(230942122,38900150,935695702,4,0,29), +(230942122,38900150,935695702,4,1,13),(230942122,906919252,935695702,39,0,271), +(230942122,906919252,935695702,20,0,83),(230942122,906919252,935695702,2,1,9), +(230942122,1409816782,935695702,3,0,18),(230942122,1409816842,935695702,1,0,7), +(230942122,1409816842,935695702,1,0,3),(230942122,1409816902,935695702,1,0,6), +(230942122,2145075862,935695702,4,1,4),(230942122,25451690,935695822,2,0,16), +(230942122,38900150,935695822,3,0,26),(230942122,38900150,935695822,1,0,3), +(230942122,906919252,935695822,24,0,176),(230942122,906919252,935695822,20,0,74), +(230942122,906919252,935695822,1,0,3),(230942122,1409816782,935695822,2,0,21), +(230942122,1409816782,935695822,2,0,21),(230942122,1409816842,935695822,1,0,3), +(230942122,1409816902,935695822,1,0,7),(231112162,1413675742,935696902,1,0,1), +(231112162,1413675742,935696962,0,0,0),(231112162,1413675742,935696962,4,2,4), +(231112162,1413675922,935696962,1,0,1),(231112162,1413675922,935696962,1,0,1), +(231112162,1413675742,1248588922,1,0,1),(231112162,1413675922,1248588922,3,0,3), +(233937022,12641121,935697562,2,0,13),(233937022,12653871,935697562,1,0,1), +(233937022,12693551,935697562,1,0,1),(233937022,12910461,935697562,2,0,6), +(233937022,12910461,935697562,26,0,65),(233937022,12910461,935697562,44,8,45), +(233937022,12910481,935697562,12,0,19),(233937022,12910481,935697562,7,2,9), +(233937022,12910481,935697562,1,0,1),(233937022,12910511,935697562,8,0,8); +INSERT INTO t1 VALUES +(233937022,12910511,935697562,20,6,22),(233937022,30879781,935697562,34,0,34), +(233937022,30879781,935697562,3,0,4),(233937022,30879781,935697562,1,0,1), +(233937022,45631730,935697562,8,0,39),(233937022,54079090,935697562,12,0,12), +(233937022,54079090,935697562,7,0,11),(233937022,54079090,935697562,14,0,16), +(233937022,94431735,935697562,6,0,31),(233937022,96876131,935697562,3,0,4), +(233937022,105436492,935697562,4,0,4),(233937022,128981555,935697562,3,0,3), +(233937022,145211004,935697562,1,0,1),(233937022,146382622,935697562,1,0,1), +(233937022,175678702,935697562,1,0,4),(233937022,298998998,935697562,1,0,1), +(233937022,335995773,935697562,3,0,3),(233937022,335995773,935697562,2,0,3), +(233937022,347447636,935697562,0,0,0),(233937022,459295955,935697562,3,0,3), +(233937022,459376625,935697562,1,0,1),(233937022,495877773,935697562,1,0,1), +(233937022,497008702,935697562,1,0,3),(233937022,561944105,935697562,1,0,1), +(233937022,561944105,935697562,1,0,1),(233937022,586535965,935697562,3,0,3), +(233937022,631549775,935697562,1,0,7),(233937022,647138479,935697562,1,0,1), +(233937022,655870453,935697562,4,0,7),(233937022,694832725,935697562,1,0,1), +(233937022,864475057,935697562,1,0,1),(233937022,1010757503,935697562,1,0,4), +(233937022,1010847736,935697562,2,0,9),(233937022,1287437116,935697562,2,0,4), +(233937022,1337693056,935697562,1,0,1),(233937022,1569279742,935697562,1,1,1), +(233937022,1569280102,935697562,2,0,7),(233937022,1569280882,935697562,2,1,3), +(233937022,1569281062,935697562,1,0,1),(233937022,1569281962,935697562,1,0,3), +(233937022,2823580588,935697562,2,0,8),(233937022,2823580588,935697562,3,1,10), +(233937022,2842066134,935697562,1,0,1),(233937022,2904542181,935697562,1,0,1), +(233937022,3058483627,935697562,1,0,1),(233937022,4507287318,935697562,1,0,1), +(233937022,5283489892,935697562,1,0,1),(233937022,11890554322,935697562,16,0,16), +(233937022,11890756102,935697562,3,1,3),(233937022,12641121,953996482,1,0,7), +(233937022,12641851,953996482,1,0,1),(233937022,12641851,953996482,1,0,1), +(233937022,12910461,953996482,4,0,14),(233937022,12910461,953996482,20,2,23), +(233937022,12910461,953996482,43,5,43),(233937022,12910461,953996482,1,0,1), +(233937022,12910481,953996482,17,2,30),(233937022,12910511,953996482,7,1,8), +(233937022,12910511,953996482,23,5,23),(233937022,14913951,953996482,2,0,3), +(233937022,21835210,953996482,1,1,1),(233937022,26481052,953996482,1,1,1), +(233937022,26481052,953996482,1,0,1),(233937022,30879781,953996482,2,0,3), +(233937022,30879781,953996482,22,0,22),(233937022,35617681,953996482,1,0,1), +(233937022,45631730,953996482,3,0,11),(233937022,54079090,953996482,13,0,13), +(233937022,54079090,953996482,11,0,16),(233937022,54079090,953996482,29,0,34), +(233937022,94431735,953996482,3,0,9),(233937022,96876131,953996482,3,0,4), +(233937022,105436492,953996482,1,0,1),(233937022,105437952,953996482,3,1,3), +(233937022,123639716,953996482,1,0,6),(233937022,145211004,953996482,2,0,3), +(233937022,145211004,953996482,2,1,3),(233937022,146382622,953996482,1,0,1), +(233937022,146382622,953996482,1,0,1),(233937022,155454324,953996482,1,0,1); +INSERT INTO t1 VALUES +(233937022,298998998,953996482,1,1,1),(233937022,335995773,953996482,1,0,1), +(233937022,335995773,953996482,7,2,9),(233937022,459295955,953996482,2,0,4), +(233937022,561944105,953996482,1,0,1),(233937022,655870453,953996482,5,0,9), +(233937022,694832725,953996482,1,0,1),(233937022,694832725,953996482,1,0,1), +(233937022,864475057,953996482,4,1,4),(233937022,897886118,953996482,1,0,1), +(233937022,897886118,953996482,1,0,3),(233937022,1005147016,953996482,1,0,1), +(233937022,1010757503,953996482,1,0,1),(233937022,1082217873,953996482,1,0,1), +(233937022,1286925326,953996482,1,0,1),(233937022,1337693056,953996482,4,0,4), +(233937022,1407236408,953996482,2,0,3),(233937022,1569280102,953996482,1,0,6), +(233937022,1569280222,953996482,1,0,1),(233937022,1569281062,953996482,1,0,1), +(233937022,1569284362,953996482,1,0,3),(233937022,2823580588,953996482,1,0,3), +(233937022,2904542181,953996482,3,0,7),(233937022,4371581485,953996482,1,0,1), +(233937022,5283491332,953996482,1,0,1),(233937022,7300486013,953996482,1,1,1), +(233937022,11890554322,953996482,16,0,16),(233937022,11890754392,953996482,1,0,1), +(233937022,11890754392,953996482,0,0,0); + +INSERT INTO t2 VALUES +(2667134182,2567095402,'Enabled'),(935692942,826927822,'Enabled'), +(1241589892,1130891152,'Enabled'),(935693782,826928662,'Enabled'), +(2482416112,2381969632,'Enabled'),(2691187582,2591198842,'Enabled'), +(2691187672,2591198932,'Enabled'),(2694472582,2594492212,'Paused'), +(935695702,826930582,'Enabled'),(935695822,826930702,'Enabled'), +(935696902,826931782,'Enabled'),(935696962,826931842,'Enabled'), +(1248588922,1137805582,'Enabled'),(935697562,826932442,'Paused'), +(953996482,845181202,'Enabled'),(2702549092,2602579882,'Enabled'), +(2702549182,2602579972,'Enabled'),(2702550712,2602581502,'Enabled'), +(1125312412,1015179502,'Enabled'),(2708245462,2608290202,'Enabled'), +(2708247262,2608292002,'Enabled'),(935699242,826934122,'Enabled'), +(1125312502,1015179592,'Enabled'),(1125312592,1015179682,'Enabled'), +(2711450452,2611502302,'Enabled'),(2711452252,2611504102,'Enabled'), +(935699902,826934782,'Enabled'),(935700262,826935142,'Enabled'), +(1215381442,1104677032,'Enabled'),(2503848082,2403457762,'Enabled'), +(935701762,826936642,'Enabled'),(935701822,826936702,'Enabled'), +(1468810282,1355227402,'Enabled'),(935702842,826937722,'Enabled'), +(1125312682,1015179772,'Enabled'),(2713816102,2613869392,'Enabled'), +(2688452032,2588455012,'Enabled'),(2688452212,2588455192,'Enabled'), +(2701527412,2601556942,'Enabled'),(1623918712,1510242412,'Enabled'), +(2701521922,2601551452,'Enabled'),(2701527772,2601557302,'Enabled'); + +INSERT INTO `t3` VALUES +(2567095402,'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'), +(826927822,'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB'),(1130891152,'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB'), +(826928662,'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC'), +(2381969632,'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC'), +(2591198842,'DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD'), +(2591198932,'EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE'), +(2594492212,'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF'), +(826930582,'GGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG'), +(826930702,'GGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG'), +(826931782,'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB'), +(826931842,'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB'), +(1137805582,'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB'); + +INSERT INTO t4 VALUES +(12618121,0),(12641121,0),(12641851,0),(12653871,0),(12665801,0),(12666811,0), +(12693551,0),(12910461,0),(12910481,0),(12910511,0),(14787251,0),(14913941,0), +(14913951,0),(16069490,0),(16705901,0),(16705991,0),(17291062,0),(17304242,0), +(20737411,0),(21524370,0),(21835210,0),(25300361,0),(25451690,0),(25728842,0), +(26481052,0),(27714032,0),(28676710,0),(30879781,0),(30931012,0),(31549341,0), +(35617681,0),(37254452,0),(38619430,0),(38895490,0),(38900150,0),(39798990,0), +(42726891,0),(42867050,0),(43439030,0),(45631730,0),(47171711,0),(49539832,0), +(54079090,0),(60442241,0),(65320501,0),(72485641,0),(76261151,0),(87949714,0), +(88240139,0),(94266195,0),(94431735,0),(94730895,0),(96876131,0); + +INSERT INTO t5 VALUES +(228172702,72485641,'Active'),(228172702,94266195,'Active'), +(228172702,818095880,'Active'),(228172702,1004959639,'Active'), +(228172702,1297484242,'Active'),(228172702,1297484422,'Active'), +(228172702,1730911800,'Active'),(228172702,1808277389,'Active'), +(228172702,2182755982,'Active'),(228172702,2968841184,'Active'), +(228172702,3015116542,'Active'),(228172702,3752383170,'Active'), +(228172702,4765525626,'Active'),(228172702,5330573302,'Active'), +(228512602,191149872,'Active'),(228512602,259118753,'Active'), +(228512602,585705465,'Active'),(228512602,585716775,'Active'), +(228512602,1105371172,'Active'),(228512602,1314223462,'Active'), +(228512602,1314223642,'Active'),(228512602,1411060522,'Active'), +(228512602,1467398182,'Active'),(228512602,1467398242,'Active'), +(228512602,1734178942,'Active'),(228512602,1734179122,'Active'), +(228512602,1953612870,'Active'),(228512602,2271510562,'Active'), +(228512602,2271525022,'Active'),(228512602,2941612417,'Active'), +(228512602,3058831402,'Active'),(228512602,3723638842,'Active'), +(228512602,3723836602,'Active'),(228512602,3723836842,'Active'), +(228512602,3723836962,'Active'),(228512602,3723988102,'Active'), +(228512602,3723989182,'Active'),(228512602,5920283002,'Active'), +(228512602,5920314232,'Active'),(228512602,585717615,'Active'), +(228512602,1953611430,'Active'),(228512602,2026844250,'Active'), +(228512602,3058831462,'Active'),(228512602,3723836902,'Active'), +(228512602,3723989002,'Active'),(228512602,3752960902,'Active'), +(228808822,17304242,'Active'),(228808822,30931012,'Active'), +(228808822,37254452,'Active'),(228808822,42726891,'Active'), +(228808822,76261151,'Active'),(228808822,88240139,'Active'), +(228808822,94730895,'Active'),(228808822,125469622,'Active'), +(228808822,179737402,'Active'),(228808822,271288782,'Active'), +(228808822,304690943,'Active'),(228808822,304691183,'Active'), +(228808822,496123368,'Active'),(228808822,555689643,'Active'), +(228808822,568994960,'Active'),(228808822,631705925,'Active'), +(228808822,631745165,'Active'),(228808822,631749605,'Active'), +(228808822,1057787002,'Active'),(228808822,1057788022,'Active'), +(228808822,1335646822,'Active'),(228808822,1335646882,'Active'), +(228808822,1335646942,'Active'),(228808822,1612792238,'Active'), +(228808822,5510586183,'Active'),(228808822,47171711,'Active'), +(228808822,125469602,'Active'),(228808822,631712555,'Active'), +(228808822,710348755,'Active'),(228808822,753718113,'Active'), +(230941762,16069490,'Active'),(230941762,16705991,'Active'), +(230941762,27714032,'Active'),(230941762,28676710,'Active'); +INSERT INTO t5 VALUES +(230941762,370319272,'Active'),(230941762,1409814802,'Active'), +(230941762,1409814982,'Active'),(230941762,2069703256,'Active'), +(230941762,142889951,'Active'),(230941762,172526592,'Active'), +(230941762,293109282,'Active'),(230941762,1409814922,'Active'), +(230941762,1409814862,'Active'),(230941762,2680867980,'Active'), +(230942122,25451690,'Active'),(230942122,31549341,'Active'), +(230942122,38900150,'Active'),(230942122,464554745,'Active'), +(230942122,906919252,'Active'),(230942122,1409816782,'Active'), +(230942122,1409816842,'Active'),(230942122,1409816902,'Active'), +(230942122,2145075862,'Active'),(231112162,1413675742,'Active'), +(231112162,1413675922,'Active'),(231112162,1413675562,'Active'), +(231112162,1413675802,'Active'),(233937022,12641121,'Active'), +(233937022,12653871,'Active'),(233937022,12693551,'Active'), +(233937022,12910461,'Active'),(233937022,12910481,'Active'), +(233937022,12910511,'Active'),(233937022,14913941,'Active'), +(233937022,30879781,'Active'),(233937022,45631730,'Active'), +(233937022,54079090,'Active'),(233937022,65320501,'Active'), +(233937022,94431735,'Active'),(233937022,96876131,'Active'), +(233937022,105436492,'Active'),(233937022,105437952,'Active'), +(233937022,128981555,'Active'),(233937022,145211004,'Active'), +(233937022,146382622,'Active'),(233937022,148832422,'Active'), +(233937022,175678702,'Active'),(233937022,260507673,'Active'), +(233937022,298998998,'Active'),(233937022,335995773,'Active'), +(233937022,347447636,'Active'),(233937022,459295955,'Active'), +(233937022,459376625,'Active'),(233937022,495877773,'Active'), +(233937022,497008702,'Active'),(233937022,561944105,'Active'), +(233937022,586535965,'Active'),(233937022,631549775,'Active'), +(233937022,647138479,'Active'),(233937022,655870453,'Active'), +(233937022,694832725,'Active'),(233937022,835712045,'Active'), +(233937022,864475057,'Active'),(233937022,864484777,'Active'), +(233937022,1010757503,'Active'),(233937022,1010847736,'Active'), +(233937022,1091554836,'Active'),(233937022,1287437116,'Active'), +(233937022,1337693056,'Active'),(233937022,1569279742,'Active'), +(233937022,1569280102,'Active'),(233937022,1569280222,'Active'), +(233937022,1569280582,'Active'),(233937022,1569280882,'Active'), +(233937022,1569281062,'Active'),(233937022,1569281962,'Active'), +(233937022,1569284362,'Active'),(233937022,1743317015,'Active'), +(233937022,2698799002,'Active'),(233937022,2698800742,'Active'), +(233937022,2823580588,'Active'),(233937022,2842066134,'Active'), +(233937022,2904542181,'Active'),(233937022,3058483627,'Active'); +INSERT INTO t5 VALUES +(233937022,4507287318,'Active'),(233937022,5283489892,'Active'), +(233937022,11890554322,'Active'),(233937022,11890756102,'Active'), +(233937022,12641851,'Active'),(233937022,14913951,'Active'), +(233937022,21835210,'Active'),(233937022,26481052,'Active'), +(233937022,35617681,'Active'),(233937022,123639716,'Active'), +(233937022,155454324,'Active'),(233937022,299001668,'Active'), +(233937022,897886118,'Active'),(233937022,1005147016,'Active'), +(233937022,1082217873,'Active'),(233937022,1286925326,'Active'), +(233937022,1407236408,'Active'),(233937022,4371581485,'Active'), +(233937022,5283491332,'Active'),(233937022,7300486013,'Active'), +(233937022,11890754392,'Active'); + +--enable_warnings +--enable_result_log +--enable_query_log + +set join_cache_level=8; +set join_buffer_size=2048; + +EXPLAIN +SELECT STRAIGHT_JOIN t1.id1, t1.num3, t3.text1, t3.id4, t2.id3, t4.dummy + FROM t1 JOIN t2 JOIN t3 JOIN t4 JOIN t5 + WHERE t1.id1=t5.id1 AND t1.id2=t5.id2 and t4.id2=t1.id2 AND + t5.enum2='Active' AND t3.id4=t2.id4 AND t2.id3=t1.id3 AND t3.text1<'D'; + +SELECT STRAIGHT_JOIN t1.id1, t1.num3, t3.text1, t3.id4, t2.id3, t4.dummy + FROM t1 JOIN t2 JOIN t3 JOIN t4 JOIN t5 + WHERE t1.id1=t5.id1 AND t1.id2=t5.id2 and t4.id2=t1.id2 AND + t5.enum2='Active' AND t3.id4=t2.id4 AND t2.id3=t1.id3 AND t3.text1<'D'; + +set join_buffer_size=@save_join_buffer_size; +set join_cache_level=@save_join_cache_level; + +DROP TABLE t1,t2,t3,t4,t5; + +--echo # +--echo # Bug#45267: Incomplete check caused wrong result. +--echo # +CREATE TABLE t1 ( + `pk` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY +); +CREATE TABLE t3 ( + `pk` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY +); +INSERT INTO t3 VALUES +(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15), +(16),(17),(18),(19),(20); +CREATE TABLE t2 ( + `pk` int(11) NOT NULL AUTO_INCREMENT, + `int_nokey` int(11) NOT NULL, + `time_key` time NOT NULL, + PRIMARY KEY (`pk`), + KEY `time_key` (`time_key`) +); +INSERT INTO t2 VALUES (10,9,'22:36:46'),(11,0,'08:46:46'); + +SELECT DISTINCT t1.`pk` +FROM t1 RIGHT JOIN t2 STRAIGHT_JOIN t3 ON t2.`int_nokey` ON t2.`time_key` +GROUP BY 1; + +DROP TABLE IF EXISTS t1, t2, t3; + +--echo # +--echo # Bug #46328: Use of aggregate function without GROUP BY clause +--echo # returns many rows (vs. one ) +--echo # + +CREATE TABLE t1 ( + int_key int(11) NOT NULL, + KEY int_key (int_key) +); + +INSERT INTO t1 VALUES +(0),(2),(2),(2),(3),(4),(5),(5),(6),(6),(8),(8),(9),(9); + +CREATE TABLE t2 ( + int_key int(11) NOT NULL, + KEY int_key (int_key) +); + +INSERT INTO t2 VALUES (2),(3); + +--echo + +--echo # The query shall return 1 record with a max value 9 and one of the +--echo # int_key values inserted above (undefined which one). A changed +--echo # execution plan may change the value in the second column +SELECT MAX(t1.int_key), t1.int_key +FROM t1 STRAIGHT_JOIN t2 +ORDER BY t1.int_key; + +--echo + +explain +SELECT MAX(t1.int_key), t1.int_key +FROM t1 STRAIGHT_JOIN t2 +ORDER BY t1.int_key; + +--echo + +DROP TABLE t1,t2; + +SET join_cache_level=@save_join_cache_level; + +--echo # +--echo # Regression test for +--echo # Bug#46733 - NULL value not returned for aggregate on empty result +--echo # set w/ semijoin on +--echo # + +CREATE TABLE t1 ( + i int(11) NOT NULL, + v varchar(1) DEFAULT NULL, + PRIMARY KEY (i) +); + +INSERT INTO t1 VALUES (10,'a'),(11,'b'),(12,'c'),(13,'d'); + +CREATE TABLE t2 ( + i int(11) NOT NULL, + v varchar(1) DEFAULT NULL, + PRIMARY KEY (i) +); + +INSERT INTO t2 VALUES (1,'x'),(2,'y'); + +--echo + +SELECT MAX(t1.i) +FROM t1 JOIN t2 ON t2.v +ORDER BY t2.v; + +--echo + +EXPLAIN +SELECT MAX(t1.i) +FROM t1 JOIN t2 ON t2.v +ORDER BY t2.v; + +--echo + +DROP TABLE t1,t2; + +--echo # +--echo # Bug #45092: join buffer contains two blob columns one of which is +--echo # used in the key employed to access the joined table +--echo # + +CREATE TABLE t1 (c1 int, c2 int, key (c2)); +INSERT INTO t1 VALUES (1,1); +INSERT INTO t1 VALUES (2,2); + +CREATE TABLE t2 (c1 text, c2 text); +INSERT INTO t2 VALUES('tt', 'uu'); +INSERT INTO t2 VALUES('zzzz', 'xxxxxxxxx'); + +--disable_result_log +ANALYZE TABLE t1,t2; +--enable_result_log + +set join_cache_level=6; + +SELECT t1.*, t2.*, LENGTH(t2.c1), LENGTH(t2.c2) FROM t1,t2 + WHERE t1.c2=LENGTH(t2.c2) and t1.c1=LENGTH(t2.c1); + +set join_cache_level=@save_join_cache_level; + +DROP TABLE t1,t2; + +--echo # +--echo # Bug #51092: linked join buffer is used for a 3-way cross join query +--echo # that selects only records of the first table +--echo # + +create table t1 (a int, b int); +insert into t1 values (1,1),(2,2); +create table t2 (a int, b int); +insert into t2 values (1,1),(2,2); +create table t3 (a int, b int); +insert into t3 values (1,1),(2,2); + +set join_cache_level=1; + +explain select t1.* from t1,t2,t3; +select t1.* from t1,t2,t3; + +set join_cache_level=2; + +explain select t1.* from t1,t2,t3; +select t1.* from t1,t2,t3; + +set join_cache_level=@save_join_cache_level; + +drop table t1,t2,t3; + +--echo # +--echo # Bug #52394: using join buffer for 3 table join with ref access +--echo # LP #623209: and no references to the columns of the middle table +--echo # + + +set join_cache_level=6; + +CREATE TABLE t1 (a int(11), b varchar(1)); +INSERT INTO t1 VALUES (6,'r'),(27,'o'); + +CREATE TABLE t2(a int); +INSERT INTO t2 VALUES(1),(2),(3),(4),(5); + +CREATE TABLE t3 (a int(11) primary key, b varchar(1)); +INSERT INTO t3 VALUES +(14,'d'),(15,'z'),(16,'e'),(17,'h'),(18,'b'),(19,'s'),(20,'e'), +(21,'j'),(22,'e'),(23,'f'),(24,'v'),(25,'x'),(26,'m'),(27,'o'); + +EXPLAIN +SELECT t3.a FROM t1,t2,t3 WHERE t1.a = t3.a AND t1.b = t3.b; +SELECT t3.a FROM t1,t2,t3 WHERE t1.a = t3.a AND t1.b = t3.b; + +DROP TABLE t1,t2,t3; + +set join_cache_level=@save_join_cache_level; + +--echo # +--echo # Bug #51084: Batched key access crashes for SELECT with +--echo # derived table and LEFT JOIN +--echo # + +CREATE TABLE t1 ( + carrier int, + id int PRIMARY KEY +); +INSERT INTO t1 VALUES (1,11),(1,12),(2,13); + +CREATE TABLE t2 ( + scan_date int, + package_id int +); +INSERT INTO t2 VALUES (2008,21),(2008,22); + +CREATE TABLE t3 ( + carrier int PRIMARY KEY, + id int +); +INSERT INTO t3 VALUES (1,31); + +CREATE TABLE t4 ( + carrier_id int, + INDEX carrier_id(carrier_id) +); +INSERT INTO t4 VALUES (31),(32); + +SET join_cache_level=8; + +SELECT COUNT(*) + FROM (t2 JOIN t1) LEFT JOIN (t3 JOIN t4 ON t3.id = t4.carrier_id) + ON t3.carrier = t1.carrier; + +EXPLAIN +SELECT COUNT(*) + FROM (t2 JOIN t1) LEFT JOIN (t3 JOIN t4 ON t3.id = t4.carrier_id) + ON t3.carrier = t1.carrier; + +SET join_cache_level=@save_join_cache_level; + +DROP TABLE t1,t2,t3,t4; + +--echo # +--echo # Bug #52636: allowing JOINs on NULL values w/ join_cache_level = 5-8 +--echo # + +CREATE TABLE t1 (b int); +INSERT INTO t1 VALUES (NULL),(3); + +CREATE TABLE t2 (a int, b int, KEY (b)); +INSERT INTO t2 VALUES + (100,NULL),(150,200),(50,150),(250,350),(180,210),(100,150), + (101,NULL),(151,200),(51,150),(251,350),(181,210),(101,150); + +set join_cache_level = 5; +explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; +--sorted_result +SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; + +set join_cache_level = 8; +explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; +--sorted_result +SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; + +# test crash when no key is worth collecting by BKA for t2's ref +delete from t1; +INSERT INTO t1 VALUES (NULL),(NULL); +set join_cache_level = 5; +explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; +--sorted_result +SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; + +DROP TABLE t1,t2; + +# test varchar keys +CREATE TABLE t1 (b varchar(100)); +INSERT INTO t1 VALUES (NULL),("some varchar"); + +CREATE TABLE t2 (a int, b varchar(100), KEY (b)); +INSERT INTO t2 VALUES (100,NULL),(150,"varchar"),(200,NULL),(250,"long long varchar"); +INSERT INTO t2 VALUES (100,NULL),(150,"long varchar"),(200,"varchar"),(250,"long long long varchar"); + +set join_cache_level = 5; +explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; +--sorted_result +SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; + +set join_cache_level = 8; +explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; +--sorted_result +SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b = t1.b; + +set join_cache_level=@save_join_cache_level; +DROP TABLE t1,t2; + +--echo # +--echo # Bug #54359: Extra rows with join_cache_level=7,8 and two joins +--echo # and multi-column index" +--echo # + +CREATE TABLE t1 ( + pk int NOT NULL, + a int DEFAULT NULL, + b varchar(16) DEFAULT NULL, + c varchar(16) DEFAULT NULL, + INDEX idx (b,a)) +; + +INSERT INTO t1 VALUES (4,9,'k','k'); +INSERT INTO t1 VALUES (12,5,'k','k'); + +set join_cache_level = 8; + +EXPLAIN +SELECT t.a FROM t1 t, t1 s FORCE INDEX(idx) + WHERE s.pk AND s.a >= t.pk AND s.b = t.c; + +SELECT t.a FROM t1 t, t1 s FORCE INDEX(idx) + WHERE s.pk AND s.a >= t.pk AND s.b = t.c; + +set join_cache_level=@save_join_cache_level; +DROP TABLE t1; + +--echo # +--echo # Bug #54235: Extra rows with join_cache_level=6,8 and two LEFT JOINs +--echo # + +CREATE TABLE t1 (a int); +CREATE TABLE t2 (a int); +CREATE TABLE t3 (a int); +CREATE TABLE t4 (a int); + +INSERT INTO t1 VALUES (null), (2), (null), (1); + +set join_cache_level = 6; +EXPLAIN +SELECT t1.a + FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a) ON 0 + WHERE t1.a OR t3.a; +SELECT t1.a + FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.a) ON 0 + WHERE t1.a OR t3.a; + +EXPLAIN +SELECT t1.a + FROM t1 LEFT JOIN (t2 LEFT JOIN (t3 LEFT JOIN t4 ON 1) ON t2.a) ON 0 + WHERE t1.a OR t4.a; +SELECT t1.a + FROM t1 LEFT JOIN (t2 LEFT JOIN (t3 LEFT JOIN t4 ON 1) ON t2.a) ON 0 + WHERE t1.a OR t4.a; + +set join_cache_level=@save_join_cache_level; +DROP TABLE t1,t2,t3,t4; + +--echo # +--echo # Bug #663840: Memory overwrite causing crash with hash join +--echo # + +SET SESSION join_cache_level=3; +SET SESSION join_buffer_size=100; + +CREATE TABLE t3 ( + i int NOT NULL, + j int NOT NULL, + d date NOT NULL, + t time NOT NULL, + v varchar(1) NOT NULL, + u varchar(1) NOT NULL, + INDEX idx (v) +) COLLATE=latin1_bin; + +INSERT INTO t3 VALUES + (3,8,'2008-12-04','00:00:00','v','v'), (3,8,'2009-03-28','00:00:00','f','f'), + (3,5,'1900-01-01','00:55:47','v','v'), (2,8,'2009-10-02','00:00:00','s','s'), + (1,8,'1900-01-01','20:51:59','a','a'), (0,6,'2008-06-04','09:47:27','p','p'), + (8,7,'2009-01-13','21:58:29','z','z'), (5,2,'1900-01-01','22:45:53','a','a'), + (9,5,'2008-01-28','14:06:48','h','h'), (5,7,'2004-09-18','22:17:16','h','h'), + (4,2,'2006-10-14','14:59:37','v','v'), (2,9,'1900-01-01','23:37:40','v','v'), + (33,142,'2000-11-28','14:14:01','b','b'), (5,3,'2008-04-04','02:54:19','y','y'), + (1,0,'2002-07-13','06:34:26','v','v'), (9,3,'2003-01-03','18:07:38','m','m'), + (1,5,'2006-04-02','13:55:23','z','z'), (3,9,'2006-10-19','20:32:28','n','n'), + (8,1,'2005-06-08','11:57:44','d','d'), (231,107,'2006-12-26','03:10:35','a','a'); +INSERT INTO t3 VALUES + (103,108,'2008-12-04','00:00:00','a','v'), (103,108,'2009-03-28','00:00:00','b','f'), + (103,105,'1900-01-01','00:55:47','c','v'), (102,108,'2009-10-02','00:00:00','d','s'), + (100,108,'1900-01-01','20:51:59','e','a'), (100,106,'2008-06-04','09:47:27','f','p'), + (108,107,'2009-01-13','21:58:29','g','z'), (105,102,'1900-01-01','22:45:53','h','a'), + (109,105,'2008-01-28','14:06:48','i','h'), (105,107,'2004-09-18','22:17:16','j','h'), + (104,102,'2006-10-14','14:59:37','k','v'), (102,109,'1900-01-01','23:37:40','l','v'), + (1033,1142,'2000-11-28','14:14:01','m','b'), (105,103,'2008-04-04','02:54:19','n','y'), + (100,100,'2002-07-13','06:34:26','o','v'), (109,103,'2003-01-03','18:07:38','p','m'), + (100,105,'2006-04-02','13:55:23','q','z'), (103,109,'2006-10-19','20:32:28','s','n'), + (108,100,'2005-06-08','11:57:44','t','d'), (1231,1107,'2006-12-26','03:10:35','v','a'); + +CREATE TABLE t1 SELECT * FROM t3; +DELETE FROM t1 WHERE i > 8; +CREATE TABLE t2 SELECT * FROM t3; +DELETE FROM t2 WHERE j > 10; + +EXPLAIN +SELECT t1.i, t1.d, t1.v, t2.i, t2.d, t2.t, t2.v FROM t1,t2,t3 + WHERE t3.u <='a' AND t2.j < 5 AND t3.v = t2.u; + +--sorted_result +SELECT t1.i, t1.d, t1.v, t2.i, t2.d, t2.t, t2.v FROM t1,t2,t3 + WHERE t3.u <='a' AND t2.j < 5 AND t3.v = t2.u; + +DROP TABLE t1,t2,t3; + +SET SESSION join_cache_level=@save_join_cache_level; +SET SESSION join_buffer_size=@save_join_buffer_size; + + +--echo # +--echo # Bug #664508: 'Simple' GROUP BY + ORDER BY +--echo # when join buffers are used +--echo # + +CREATE TABLE t1 ( + pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL, + PRIMARY KEY (pk), INDEX idx1(i), INDEX idx2 (v,i) +) COLLATE latin1_bin; +INSERT INTO t1 VALUES + (10,8,'v'), (11,8,'f'), (13,8,'s'), (14,8,'a'), + (15,6,'p'), (16,7,'z'), (17,2,'a'), (18,5,'h'), (19,7,'h'), + (25,3,'m'), (26,5,'a'), (27,9,'n'), (28,1,'d'), (29,107,'a'); +INSERT INTO t1 VALUES + (110,8,'x'), (111,8,'y'), (112,5,'v'), (113,8,'z'), (114,8,'i'), + (115,6,'j'), (116,7,'t'), (117,2,'b'), (118,5,'j'), (119,7,'w'), + (125,3,'q'), (126,5,'o'), (127,9,'n'), (128,1,'e'), (129,107,'c'); +INSERT INTO t1 VALUES + (210,8,'b'), (211,8,'c'), (212,5,'d'), (213,8,'e'), (214,8,'g'), + (215,6,'f'), (216,7,'h'), (217,2,'i'), (218,5,'j'), (219,7,'k'), + (225,3,'l'), (226,5,'m'), (227,9,'n'), (228,1,'o'), (229,107,'p'); + +CREATE TABLE t2 ( + pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL, + PRIMARY KEY (pk), INDEX idx1(i), INDEX idx2(v,i) +) COLLATE latin1_bin; +INSERT INTO t2 VALUES + (10,8,'v'), (11,8,'f'), (12,5,'v'), (13,8,'s'), (14,8,'a'), + (15,6,'p'), (16,7,'z'), (17,2,'a'), (18,5,'h'), (19,7,'h'), + (20,2,'v'), (21,9,'v'), (22,142,'b'), (23,3,'y'), (24,0,'v'), + (25,3,'m'), (26,5,'b'), (27,9,'n'), (28,1,'d'), (29,107,'a'); + +CREATE TABLE t3 ( + pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL, + PRIMARY KEY (pk), INDEX idx1(i), INDEX idx2(v,i) +) COLLATE latin1_bin; +INSERT INTO t3 VALUES + (1,9,'x'), (2,5,'g'), (3,1,'o'), (4,0,'g'), (5,1,'v'), + (6,190,'m'), (7,6,'x'), (8,3,'c'), (9,4,'z'), (10,3,'i'), + (11,186,'x'), (12,1,'g'), (13,8,'q'), (14,226,'m'), (15,133,'p'), + (16,6,'e'), (17,3,'t'), (18,8,'j'), (19,5,'h'), (20,7,'w'); + +SET SESSION join_cache_level=1; +EXPLAIN +SELECT t2.v FROM t1, t2, t3 +WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 + GROUP BY t2.v ORDER BY t1.pk,t2.v; +SELECT t2.v FROM t1, t2, t3 +WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 + GROUP BY t2.v ORDER BY t1.pk,t2.v; + +# MDEV-8189 field<>const and const<>field are not symmetric +# Do the same EXPLAIN and SELECT +# for "t2.v <> t3.v" instead of "t3.v <> t2.v" + +EXPLAIN +SELECT t2.v FROM t1, t2, t3 +WHERE t2.v <> t3.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 + GROUP BY t2.v ORDER BY t1.pk,t2.v; +SELECT t2.v FROM t1, t2, t3 +WHERE t2.v <> t3.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 + GROUP BY t2.v ORDER BY t1.pk,t2.v; + +SET SESSION join_cache_level=6; +EXPLAIN +SELECT t2.v FROM t1, t2, t3 +WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 + GROUP BY t2.v ORDER BY t1.pk,t2.v; +SELECT t2.v FROM t1, t2, t3 +WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 + GROUP BY t2.v ORDER BY t1.pk,t2.v; + +# MDEV-8189 field<>const and const<>field are not symmetric +# Do the same EXPLAIN and SELECT +# for "t2.v <> t3.v" instead of "t3.v <> t2.v" +EXPLAIN +SELECT t2.v FROM t1, t2, t3 +WHERE t2.v <> t3.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 + GROUP BY t2.v ORDER BY t1.pk,t2.v; +SELECT t2.v FROM t1, t2, t3 +WHERE t2.v <> t3.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 + GROUP BY t2.v ORDER BY t1.pk,t2.v; + +SET SESSION join_cache_level=4; +EXPLAIN +SELECT t2.v FROM t1, t2, t3 +WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 + GROUP BY t2.v ORDER BY t1.pk,t2.v; +SELECT t2.v FROM t1, t2, t3 +WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 + GROUP BY t2.v ORDER BY t1.pk,t2.v; + +# MDEV-8189 field<>const and const<>field are not symmetric +# Do the same EXPLAIN and SELECT +# for "t2.v <> t3.v" instead of "t3.v <> t2.v" +EXPLAIN +SELECT t2.v FROM t1, t2, t3 +WHERE t2.v <> t3.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 + GROUP BY t2.v ORDER BY t1.pk,t2.v; +SELECT t2.v FROM t1, t2, t3 +WHERE t2.v <> t3.v AND t3.pk = t2.i AND t1.v = t3.v AND t1.pk*2<100 + GROUP BY t2.v ORDER BY t1.pk,t2.v; + +DROP TABLE t1,t2,t3; + +SET SESSION join_cache_level=@save_join_cache_level; + +--echo # +--echo # Bug #668290: hash join with non-binary collations +--echo # + +CREATE TABLE t1 ( + i int DEFAULT NULL, + cl varchar(10) CHARACTER SET latin1 DEFAULT NULL, + cu varchar(10) CHARACTER SET utf8 DEFAULT NULL, + INDEX cl (cl), + INDEX cu (cu) +); +INSERT INTO t1 VALUES + (650903552,'cmxffkpsel','z'), (535298048,'tvtjrcmxff','y'), + (1626865664,'when','for'), (39649280,'rcvljitvtj','ercvljitvt'), + (792068096,'ttercvljit','jttercvlji'); +INSERT INTO t1 SELECT * FROM t1; + +CREATE TABLE t2 ( + cu varchar(10) CHARACTER SET utf8 DEFAULT NULL, + i int DEFAULT NULL, + cl varchar(10) CHARACTER SET latin1 DEFAULT NULL, + INDEX cu (cu), + INDEX cl (cl) +); +INSERT INTO t2 VALUES + ('g',7,'like'), ('fujttercvl',6,'y'), + ('s',2,'e'), ('didn\'t',0,'v'), + ('gvdrodpedk',8,'chogvdrodp'), ('jichogvdro',7,'will'); + +EXPLAIN +SELECT t2.i FROM t1,t2 WHERE t1.cu = t2.cl ; +SELECT t2.i FROM t1,t2 WHERE t1.cu = t2.cl ; + +SET SESSION join_cache_level = 4; + +EXPLAIN +SELECT t2.i FROM t1,t2 WHERE t1.cu = t2.cl ; +SELECT t2.i FROM t1,t2 WHERE t1.cu = t2.cl ; + +SET SESSION join_cache_level=@save_join_cache_level; + +DROP TABLE t1,t2; + +--echo # +--echo # Bug #669382: hash join using a ref with constant key parts +--echo # + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES + (9), (11), (7), (8), (4), (1), (12), (3), (5); +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; + +CREATE TABLE t2 (a int, b int, c int, INDEX idx (a,b)); +INSERT INTO t2 VALUES + (8, 80, 800), (1, 10, 100), (1, 11, 101), (3, 30, 300), + (1, 12, 102), (8, 81, 801), (7, 70, 700), (12, 120, 1200), + (8, 82, 802), (1, 13, 103), (1, 14, 104), (3, 31, 301), + (1, 15, 105), (8, 83, 803), (7, 71, 701); + +SET SESSION join_cache_level = 4; +SET SESSION join_buffer_size = 256; + +EXPLAIN +SELECT t1.a, t2.c FROM t1,t2 WHERE t1.a=t2.a AND t2.b=99; +SELECT t1.a, t2.c FROM t1,t2 WHERE t1.a=t2.a AND t2.b=99; + +SET SESSION join_cache_level=@save_join_cache_level; +SET SESSION join_buffer_size=@save_join_buffer_size; + +DROP TABLE t1,t2; + +--echo # +--echo # Bug #671901: hash join using a ref to a varchar field +--echo # + +CREATE TABLE t1 ( + v varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + i int DEFAULT NULL +); +INSERT INTO t1 VALUES + ('k',8), ('abcdefjh',-575340544), ('f',77), ('because', 2), ('f',-517472256), + ('abcdefjhj',5), ('z',7); + +CREATE TABLE t2 ( + v varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + i int DEFAULT NULL, + INDEX idx (v) +); +INSERT INTO t2 VALUES + ('did',5), ('was',-1631322112), ('are',3), ('abcdefjhjk',3), + ('abcdefjhjk',4), ('tell',-824573952), ('t',0),('v',-1711013888), + ('abcdefjhjk',1015414784), ('or',4), ('now',0), ('abcdefjhjk',-32702464), + ('abcdefjhjk',4), ('time',1078394880), ('f',4), ('m',-1845559296), + ('ff', 5), ('abcdefjhjk',-1074397184); +INSERT INTO t2 VALUES + ('dig',5), ('were',-1631322112), ('is',3), ('abcdefjhjl',3), + ('abcdefjh',4), ('told',-824573952), ('tt',0),('vv',-1711013888), + ('abcdefjhjj',1015414784), ('and',4), ('here',0), ('abcdefjhjm',-32702464), + ('abcdefjhji',4), ('space',1078394880), ('fs',4), ('mn',-1845559296), + ('fq', 5), ('abcdefjhjp',-1074397184); + +EXPLAIN +SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = t1.v; +SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = t1.v; +EXPLAIN +SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = concat(t1.v, t1.v); +SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = concat(t1.v, t1.v); + +SET SESSION join_cache_level = 4; +EXPLAIN +SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = t1.v; +SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = t1.v; +EXPLAIN +SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = concat(t1.v, t1.v); +SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = concat(t1.v, t1.v); + +SET SESSION join_cache_level=@save_join_cache_level; + +DROP TABLE t1,t2; + +#--echo # +--echo # Bug #672497: 3 way join with tiny incremental join buffer with +--echo # and a ref access from the first table +--echo # + +CREATE TABLE t1 ( + pk int PRIMARY KEY, + v varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + INDEX idx (v) +); +INSERT INTO t1 VALUES + (1,'abcdefjhjk'), (2,'i'),(3,'abcdefjhjk'), (4,'well'), (5,'abcdefjhjk'), + (6,'abcdefjhjk'), (7,'that'); + +CREATE TABLE t2 ( + pk int PRIMARY KEY, + i int DEFAULT NULL, + v varchar(1000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + INDEX idx (v) +); +INSERT INTO t2 VALUES + (1,6,'yes'), (2,NULL,'will'), (3,NULL,'o'), (4,NULL,'k'), (5,NULL,'she'), + (6,-1450835968,'abcdefjhjkl'), (7,-975831040,'abcdefjhjkl'), (8,NULL,'z'), + (10,-343932928,'t'), + (11,6,'yes'), (12,NULL,'will'), (13,NULL,'o'), (14,NULL,'k'), (15,NULL,'she'), + (16,-1450835968,'abcdefjhjkl'), (17,-975831040,'abcdefjhjkl'), (18,NULL,'z'), + (19,-343932928,'t'); +INSERT INTO t2 VALUES + (101,6,'yes'), (102,NULL,'will'), (103,NULL,'o'), (104,NULL,'k'), (105,NULL,'she'), + (106,-1450835968,'abcdefjhjkl'), (107,-975831040,'abcdefjhjkl'), (108,NULL,'z'), + (100,-343932928,'t'), + (111,6,'yes'), (112,NULL,'will'), (113,NULL,'o'), (114,NULL,'k'), (115,NULL,'she'), + (116,-1450835968,'abcdefjhjkl'), (117,-975831040,'abcdefjhjkl'), (118,NULL,'z'), + (119,-343932928,'t'); + +CREATE TABLE t3 ( + pk int NOT NULL PRIMARY KEY, + i int, + v varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, + INDEX idx (v(333)) +); +INSERT INTO t3 VALUES +(1,7,'abcdefjhjkl'),(2,6,'y'), (3,NULL,'to'),(4,7,'n'),(5,7,'look'), (6,NULL,'all'), +(7,1443168256,'c'), (8,1427046400,'right'), +(11,7,'abcdefjhjkl'), (12,6,'y'), (13,NULL,'to'), (14,7,'n'), (15,7,'look'), +(16,NULL,'all'), (17,1443168256,'c'), (18,1427046400,'right'), +(21,7,'abcdefjhjkl'), (22,6,'y'), (23,NULL,'to'), (24,7,'n'), (25,7,'look'), +(26,NULL,'all'), (27,1443168256,'c'), (28,1427046400,'right'), +(31,7,'abcdefjhjkl'), (32,6,'y'), (33,NULL,'to'), (34,7,'n'), (35,7,'look'), +(36,NULL,'all'), (37,1443168256,'c'), (38,1427046400,'right'); +INSERT INTO t3 VALUES +(101,7,'abcdefjhjkl'),(102,6,'y'), (103,NULL,'to'),(104,7,'n'),(105,7,'look'), +(106,NULL,'all'), (107,1443168256,'c'), (108,1427046400,'right'), +(111,7,'abcdefjhjkl'), (112,6,'y'), (113,NULL,'to'), (114,7,'n'), (115,7,'look'), +(116,NULL,'all'), (117,1443168256,'c'), (118,1427046400,'right'), +(121,7,'abcdefjhjkl'), (122,6,'y'), (123,NULL,'to'), (124,7,'n'), (125,7,'look'), +(126,NULL,'all'), (127,1443168256,'c'), (128,1427046400,'right'), +(131,7,'abcdefjhjkl'), (132,6,'y'), (133,NULL,'to'), (134,7,'n'), (135,7,'look'), +(136,NULL,'all'), (137,1443168256,'c'), (138,1427046400,'right'); + +SET SESSION join_buffer_size = 256; + +SET SESSION join_cache_level = 4; +EXPLAIN +SELECT t3.i FROM t1,t2,t3 + WHERE t1.v = t2.v AND t3.v = t1.v AND t2.i <> 0; +SELECT t3.i FROM t1,t2,t3 + WHERE t1.v = t2.v AND t3.v = t1.v AND t2.i <> 0; + +SET SESSION join_cache_level=@save_join_cache_level; +SET SESSION join_buffer_size=@save_join_buffer_size; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # Bug #672551: hash join over a long varchar field +--echo # + +CREATE TABLE t1 ( + pk int PRIMARY KEY, + a varchar(512) CHARSET latin1 COLLATE latin1_bin DEFAULT NULL, + INDEX idx (a) +); +INSERT INTO t1 VALUES (2, 'aa'), (5, 'ccccccc'), (3, 'bb'); + +CREATE TABLE t2( + pk int PRIMARY KEY, + a varchar(512) CHARSET latin1 COLLATE latin1_bin DEFAULT NULL, + INDEX idx (a) +); +INSERT INTO t2 VALUES + (10, 'a'), (20, 'c'), (30, 'aa'), (4, 'bb'), + (11, 'a'), (21, 'c'), (31, 'aa'), (41, 'cc'), + (12, 'a'), (22, 'c'), (32, 'bb'), (42, 'aa'); +INSERT INTO t2 VALUES + (110, 'a'), (120, 'c'), (130, 'aa'), (14, 'bb'), + (111, 'a'), (121, 'c'), (131, 'aa'), (141, 'cc'), + (112, 'a'), (122, 'c'), (132, 'bb'), (142, 'aa'); + +SELECT * FROM t1,t2 WHERE t2.a=t1.a; + +SET SESSION join_cache_level = 4; +EXPLAIN +SELECT * FROM t1,t2 WHERE t2.a=t1.a; +SELECT * FROM t1,t2 WHERE t2.a=t1.a; + +SET SESSION join_cache_level=@save_join_cache_level; + +DROP TABLE t1,t2; + +--echo # +--echo # Bug #674431: nested outer join when join_cache_level is set to 7 +--echo # + +CREATE TABLE t1 (a int, b varchar(32)) ; +INSERT INTO t1 VALUES (5,'h'), (NULL,'j'); +CREATE TABLE t2 (a int, b varchar(32), c int) ; +INSERT INTO t2 VALUES (5,'h',100), (NULL,'j',200); +CREATE TABLE t3 (a int, b varchar(32), INDEX idx(b)); +INSERT INTO t3 VALUES (77,'h'), (88,'g'); + +SET SESSION optimizer_switch = 'outer_join_with_cache=on'; +SET SESSION join_cache_level = 7; +SELECT t3.a + FROM t1 LEFT JOIN + (t2 LEFT OUTER JOIN t3 ON t2.b = t3.b) ON t2.a = t1.b + WHERE t3.a BETWEEN 3 AND 11 OR t1.a <= t2.c; + +SET SESSION optimizer_switch=@local_optimizer_switch; +SET SESSION join_cache_level=@save_join_cache_level; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # Bug #52540: nested outer join when join_cache_level is set to 3 +--echo # + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (2); +CREATE TABLE t2 (a varchar(10)); +INSERT INTO t2 VALUES ('f'),('x'); +CREATE TABLE t3 (pk int(11) PRIMARY KEY); +INSERT INTO t3 VALUES (2); +CREATE TABLE t4 (a varchar(10)); + +SET SESSION optimizer_switch = 'outer_join_with_cache=on'; +SET SESSION join_cache_level = 3; + +SELECT * + FROM t2 LEFT JOIN + ((t1 JOIN t3 ON t1.a = t3.pk) LEFT JOIN t4 ON 1) ON 1; + +SET SESSION optimizer_switch=@local_optimizer_switch; +SET SESSION join_cache_level=@save_join_cache_level; + +DROP TABLE t1,t2,t3,t4; + +--echo # +--echo # Bug #674423: outer join with ON expression over only outer tables +--echo # + +CREATE TABLE t1 (a int) ; +INSERT INTO t1 VALUES ('9'); + +CREATE TABLE t2 (pk int, a int) ; +INSERT INTO t2 VALUES ('9',NULL), ('1',NULL); + +SET SESSION optimizer_switch = 'outer_join_with_cache=on'; + +SET SESSION join_cache_level = 0; +EXPLAIN +SELECT * FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t1.a <> 0 OR t2.pk < 9; +SELECT * FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t1.a <>0 OR t2.pk < 9; + +SET SESSION join_cache_level = 1; +EXPLAIN +SELECT * FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t1.a <> 0 OR t2.pk < 9; +SELECT * FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t1.a <> 0 OR t2.pk < 9; + +SET SESSION optimizer_switch=@local_optimizer_switch; +SET SESSION join_cache_level=@save_join_cache_level; + +DROP TABLE t1,t2; + +--echo # +--echo # Bug #675095: nested outer join using join buffer +--echo # + +CREATE TABLE t1 (pk int, a1 int) ; +INSERT IGNORE INTO t1 VALUES (2,NULL), (8,0); + +CREATE TABLE t2 (pk int, a2 int, c2 int, d2 int) ; +INSERT IGNORE INTO t2 VALUES (9,0,0,2), (1,0,0,7); + +CREATE TABLE t3 (pk int, a3 int, c3 int, d3 int) ; +INSERT IGNORE INTO t3 VALUES (9,0,0,2), (1,0,0,7); + +CREATE TABLE t4 (pk int, a4 int, INDEX idx(a4)) ; +INSERT IGNORE INTO t4 VALUES (2,NULL), (8,0); +INSERT IGNORE INTO t4 VALUES (12,10), (18,20); +INSERT IGNORE INTO t4 VALUES (22,11), (28,21); +INSERT IGNORE INTO t4 VALUES (32,12), (38,22); + +CREATE TABLE t5 (pk int, a5 int) ; +INSERT IGNORE INTO t5 VALUES (2,0), (8,0); + + +SET SESSION optimizer_switch = 'outer_join_with_cache=on'; + +SET SESSION join_cache_level = 0; + +EXPLAIN EXTENDED +SELECT * + FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.c2 = t3.a3) ON t1.pk = t2.d2) + LEFT JOIN t4 ON t1.a1 = t4.a4) LEFT JOIN t5 ON t3.a3 = t5.a5; +SELECT * + FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.c2 = t3.a3) ON t1.pk = t2.d2) + LEFT JOIN t4 ON t1.a1 = t4.a4) LEFT JOIN t5 ON t3.a3 = t5.a5; + +SET SESSION join_cache_level = 2; + +EXPLAIN EXTENDED +SELECT * + FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.c2 = t3.a3) ON t1.pk = t2.d2) + LEFT JOIN t4 ON t1.a1 = t4.a4) LEFT JOIN t5 ON t3.a3 = t5.a5; +SELECT * + FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.c2 = t3.a3) ON t1.pk = t2.d2) + LEFT JOIN t4 ON t1.a1 = t4.a4) LEFT JOIN t5 ON t3.a3 = t5.a5; + +SET SESSION join_cache_level = 1; + +EXPLAIN EXTENDED +SELECT * + FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.c2 = t3.a3) ON t1.pk = t2.d2) + LEFT JOIN t4 ON t1.a1 = t4.a4) LEFT JOIN t5 ON t3.a3 = t5.a5; +SELECT * + FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.c2 = t3.a3) ON t1.pk = t2.d2) + LEFT JOIN t4 ON t1.a1 = t4.a4) LEFT JOIN t5 ON t3.a3 = t5.a5; + +SET SESSION optimizer_switch=@local_optimizer_switch; +SET SESSION join_cache_level=@save_join_cache_level; + +DROP TABLE t1,t2,t3,t4,t5; + +--echo # +--echo # Bug #675516: nested outer join with 3 tables in the nest +--echo # using BNL + BNLH +--echo # + +CREATE TABLE t1 (a1 int, b1 int, c1 int) ; +INSERT INTO t1 VALUES (7,8,0), (6,4,0); + +CREATE TABLE t2 (a2 int) ; +INSERT INTO t2 VALUES (5); + +CREATE TABLE t3 (a3 int, b3 int, c3 int, PRIMARY KEY (b3)) ; +INSERT INTO t3 VALUES (2,5,0); + +CREATE TABLE t4 (a4 int, b4 int, c4 int) ; +INSERT INTO t4 VALUES (7,8,0); + +SET SESSION optimizer_switch = 'outer_join_with_cache=on'; + +SET SESSION join_cache_level = 4; +EXPLAIN +SELECT * FROM + t1 LEFT JOIN + ((t2 JOIN t3 ON t2.a2 = t3.b3) JOIN t4 ON t4.b4 <> 0) ON t1.c1 = t3.c3 + WHERE t3.a3 IS NULL; +SELECT * FROM + t1 LEFT JOIN + ((t2 JOIN t3 ON t2.a2 = t3.b3) JOIN t4 ON t4.b4 <> 0) ON t1.c1 = t3.c3 + WHERE t3.a3 IS NULL; + +SET SESSION join_cache_level = 0; +EXPLAIN +SELECT * FROM + t1 LEFT JOIN + ((t2 JOIN t3 ON t2.a2 = t3.b3) JOIN t4 ON t4.b4 <> 0) ON t1.c1 = t3.c3 + WHERE t3.a3 IS NULL; +SELECT * FROM + t1 LEFT JOIN + ((t2 JOIN t3 ON t2.a2 = t3.b3) JOIN t4 ON t4.b4 <> 0) ON t1.c1 = t3.c3 + WHERE t3.a3 IS NULL; + +SET SESSION optimizer_switch=@local_optimizer_switch; +SET SESSION join_cache_level=@save_join_cache_level; + +DROP TABLE t1,t2,t3,t4; + +--echo # +--echo # Bug #660963: nested outer join with join_cache_level set to 5 +--echo # + +CREATE TABLE t1 (a1 int) ; +INSERT INTO t1 VALUES (0),(0); + +CREATE TABLE t2 (a2 int, b2 int, PRIMARY KEY (a2)) ; +INSERT INTO t2 VALUES (2,1); + +CREATE TABLE t3 (a3 int, b3 int, PRIMARY KEY (a3)) ; +INSERT INTO t3 VALUES (2,1); + +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; + +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; + +SET SESSION optimizer_switch=@local_optimizer_switch; +SET SESSION join_cache_level=@save_join_cache_level; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # Bug #675922: incremental buffer for BKA with access from previous +--echo # buffers from non-nullable columns whose values may be null +--echo # + +CREATE TABLE t1 (a1 varchar(32)) ; +INSERT INTO t1 VALUES ('s'),('k'); + +CREATE TABLE t2 (a2 int PRIMARY KEY, b2 varchar(32)) ; +INSERT INTO t2 VALUES (7,'s'); + +CREATE TABLE t3 (a3 int PRIMARY KEY, b3 varchar(32)) ; +INSERT INTO t3 VALUES (7,'s'); + +CREATE TABLE t4 (a4 int) ; +INSERT INTO t4 VALUES (9); + +CREATE TABLE t5(a5 int PRIMARY KEY, b5 int) ; +INSERT INTO t5 VALUES (7,0); + +SET SESSION optimizer_switch = 'outer_join_with_cache=on'; + +SET SESSION join_cache_level = 0; +EXPLAIN +SELECT t4.a4, t5.b5 + FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1) + LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2; +SELECT t4.a4, t5.b5 + FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1) + LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2; + +SET SESSION join_cache_level = 6; +EXPLAIN +SELECT t4.a4, t5.b5 + FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1) + LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2; +SELECT t4.a4, t5.b5 + FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.a2 = t3.a3) ON t2.b2 = t1.a1) + LEFT JOIN t4 ON t4.a4 <> 0) LEFT JOIN t5 ON t5.a5 = t2.a2; + +SET SESSION optimizer_switch=@local_optimizer_switch; +SET SESSION join_cache_level=@save_join_cache_level; + +DROP TABLE t1,t2,t3,t4,t5; + +--echo # +--echo # Bug #670380: hash join for non-binary collation +--echo # + + +CREATE TABLE t1 (pk int PRIMARY KEY, a varchar(32)); +CREATE TABLE t2 (pk int PRIMARY KEY, a varchar(32), INDEX idx(a)); +INSERT INTO t1 VALUES + (10,'AAA'), (20,'BBBB'), (30,'Cc'), (40,'DD'), (50,'ee'); +INSERT INTO t2 VALUES + (1,'Bbbb'), (2,'BBB'), (3,'bbbb'), (4,'AaA'), (5,'CC'), + (6,'cC'), (7,'CCC'), (8,'AAA'), (9,'bBbB'), (10,'aaaa'), + (11,'a'), (12,'dd'), (13,'EE'), (14,'ee'), (15,'D'), + (101,'Bbbb'), (102,'BBB'), (103,'bbbb'), (104,'AaA'), (105,'CC'), + (106,'cC'), (107,'CCC'), (108,'AAA'), (109,'bBbB'), (110,'aaaa'), + (111,'a'), (112,'dd'), (113,'EE'), (114,'ee'), (115,'D'); + +SET SESSION join_cache_level = 4; + +EXPLAIN +SELECT * FROM t1,t2 WHERE t1.a=t2.a; +SELECT * FROM t1,t2 WHERE t1.a=t2.a; + +SET SESSION join_cache_level=@save_join_cache_level; + +DROP TABLE t1,t2; + +--echo # +--echo # Bug #694092: incorrect detection of index only pushdown conditions +--echo # + +CREATE TABLE t1 ( + f1 varchar(10), f3 int(11), PRIMARY KEY (f3) +); +INSERT INTO t1 VALUES ('y',1),('or',5); + +CREATE TABLE t2 ( + f3 int(11), f2 varchar(1024), f4 varchar(10), PRIMARY KEY (f3) +); +INSERT INTO t2 VALUES (6,'RPOYT','y'),(10,'JINQE','m'); + +SET SESSION join_cache_level = 1; + +SET SESSION optimizer_switch = 'index_condition_pushdown=off'; +EXPLAIN +SELECT * FROM t1,t2 + WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6); +SELECT * FROM t1,t2 + WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6); + +SET SESSION optimizer_switch = 'index_condition_pushdown=on'; +EXPLAIN +SELECT * FROM t1,t2 + WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6); +SELECT * FROM t1,t2 + WHERE t1.f1 = t2.f4 AND (t1.f3 = 1 AND t2.f3 = 4 OR t1.f3 = 2 AND t2.f3 = 6); + +SET SESSION join_cache_level=@save_join_cache_level; +SET SESSION optimizer_switch=@local_optimizer_switch; + +DROP TABLE t1,t2; + +# The same cause of the problem but no join buffer is used (see bug #695442) + +CREATE TABLE t1 (f1 int, f2 varchar(10), KEY (f1), KEY (f2)) ; +INSERT INTO t1 VALUES + (4,'e'), (891879424,'l'), (-243400704,'ectlyqupbk'), (1851981824,'of'), + (-1495203840,'you'), (4,'no'), (-1436942336,'c'), (891420672,'DQQYO'), + (608698368,'qergldqmec'), (1,'x'); + +CREATE TABLE t2 (f3 varchar(64), KEY (f3)); +INSERT INTO t2 VALUES + ('d'), ('UALLN'), ('d'), ('z'), ('r'), ('YVAKV'), ('d'), ('TNGZK'), ('e'), + ('xucupaxdyythsgiw'), ('why'), ('ttugkxucupaxdyyt'), ('l'), ('LHTKN'), + ('d'), ('o'), ('v'), ('KGLCJ'), ('your'); + + +SET SESSION optimizer_switch='index_merge_sort_intersection=off'; + +SET SESSION optimizer_switch = 'index_condition_pushdown=off'; +EXPLAIN SELECT * FROM t1,t2 + WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0, 100) ORDER BY t1.f2 LIMIT 1; +SELECT * FROM t1,t2 + WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0 ,100) ORDER BY t1.f2 LIMIT 1; +SET SESSION optimizer_switch=@local_optimizer_switch; + +SET SESSION optimizer_switch = 'index_condition_pushdown=on'; +EXPLAIN SELECT * FROM t1,t2 + WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0 ,100) ORDER BY t1.f2 LIMIT 1; +SELECT * FROM t1,t2 + WHERE t2.f3 = t1.f2 AND t1.f1 IN (9, 0 ,100) ORDER BY t1.f2 LIMIT 1; + +SET SESSION optimizer_switch=@local_optimizer_switch; + +DROP TABLE t1,t2; + +--echo # +--echo # Bug #694443: hash join using IS NULL the an equi-join condition +--echo # + +CREATE TABLE t1 (a int PRIMARY KEY); +INSERT INTO t1 VALUES + (7), (4), (9), (1), (3), (8), (2); + +CREATE TABLE t2 (a int, b int, INDEX idx (a)); +INSERT INTO t2 VALUES + (NULL,10), (4,80), (7,70), (6,11), (7,90), (NULL,40), + (4,77), (4,50), (NULL,41), (7,99), (7,88), (8,12), + (1,21), (4,90), (7,91), (8,22), (6,92), (NULL,42), + (2,78), (2,51), (1,43), (5,97), (5,89); + +SET SESSION join_cache_level = 1; +EXPLAIN +SELECT * FROM t1,t2 WHERE t1.a < 3 and t2.a IS NULL; +SELECT * FROM t1,t2 WHERE t1.a < 3 and t2.a IS NULL; + +SET SESSION join_cache_level = 4; +EXPLAIN +SELECT * FROM t1,t2 WHERE t1.a < 3 and t2.a IS NULL; +SELECT * FROM t1,t2 WHERE t1.a < 3 and t2.a IS NULL; + +SET SESSION join_cache_level=@save_join_cache_level; + +DROP TABLE t1,t2; + +--echo # +--echo # Bug #697557: hash join on a varchar field +--echo # + +CREATE TABLE t1 ( f1 varchar(10) , f2 int(11) , KEY (f1)); +INSERT INTO t1 VALUES ('r',1), ('m',2); + +CREATE TABLE t2 ( f1 varchar(10) , f2 int(11) , KEY (f1)); +INSERT INTO t2 VALUES + ('hgtofubn',1), ('GDOXZ',91), ('n',2), ('fggxgalh',88), + ('hgtofu',1), ('GDO',101), ('n',3), ('fggxga',55), + ('hgtofu',3), ('GDO',33), ('nn',3), ('fggxgarrr',77), + ('jgtofu',3), ('JDO',33), ('mn',3), ('jggxgarrr',77), + ('igtofu',3), ('IDO',33), ('ln',3), ('iggxgarrr',77); + + +SET SESSION join_cache_level=3; + +EXPLAIN +SELECT * FROM t1,t2 WHERE t2.f1 = t1.f1; +SELECT * FROM t1,t2 WHERE t2.f1 = t1.f1; + +SET SESSION join_cache_level=@save_join_cache_level; + +DROP TABLE t1,t2; + +--echo # +--echo # Bug #707827: hash join on varchar column with NULLs +--echo # + +CREATE TABLE t1 (v varchar(1)); +INSERT INTO t1 VALUES ('o'), ('u'); + +CREATE TABLE t2 (a int, v varchar(1), INDEX idx (v)) ; +INSERT INTO t2 VALUES + (8,NULL), (10,'b'), (5,'k'), (4,NULL), + (1,NULL), (11,'u'), (7,NULL), (2,'d'), + (18,'u'), (11,'b'), (15,'k'), (12,'d'), + (18,'x'), (11,'y'), (15,'l'), (12,'e'); + +SET SESSION join_buffer_size = 256; + +SET SESSION join_cache_level = 4; +EXPLAIN +SELECT a FROM t1,t2 WHERE t2.v = t1.v ; +SELECT a FROM t1,t2 WHERE t2.v = t1.v ; + +SET SESSION join_cache_level = 1; +EXPLAIN +SELECT a FROM t1,t2 WHERE t2.v = t1.v ; +SELECT a FROM t1,t2 WHERE t2.v = t1.v ; + +SET SESSION join_cache_level=@save_join_cache_level; +SET SESSION join_buffer_size=@save_join_buffer_size; + +DROP TABLE t1,t2; + +--echo # +--echo # Bug #802860: crash on join cache + derived + duplicate_weedout +--echo # + +SET SESSION optimizer_switch= + 'semijoin=on,materialization=off,firstmatch=off,loosescan=off,derived_with_keys=on'; + +CREATE TABLE t1 (a int) ; +INSERT IGNORE INTO t1 VALUES (0), (1), (0); + +CREATE TABLE t2 (a int) ; +INSERT IGNORE INTO t2 VALUES (0), (3), (0), (2); + +SET SESSION join_cache_level = 0; + +EXPLAIN +SELECT * FROM (SELECT DISTINCT * FROM t1) t + WHERE t.a IN (SELECT t2.a FROM t2); +SELECT * FROM (SELECT DISTINCT * FROM t1) t + WHERE t.a IN (SELECT t2.a FROM t2); + +SET SESSION join_cache_level = 1; + +EXPLAIN +SELECT * FROM (SELECT DISTINCT * FROM t1) t + WHERE t.a IN (SELECT t2.a FROM t2); +SELECT * FROM (SELECT DISTINCT * FROM t1) t + WHERE t.a IN (SELECT t2.a FROM t2); + +DROP TABLE t1, t2; + +SET SESSION join_cache_level=@save_join_cache_level; + +# Note that next tests are run with same optimizer_switch as previous one! + +--echo # +--echo # Bug #887479: join_cache_level=3 + semijoin=on +--echo # + +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (3914,17), (3710,5), (3888,20); + +CREATE TABLE t2 (c int, KEY (c)); +INSERT INTO t2 VALUES (27), (17), (33), (20), (3), (7), (18), (2); + +SET @tmp_optimizer_switch=@@optimizer_switch; +SET SESSION optimizer_switch='semijoin=on'; +SET SESSION optimizer_switch='semijoin_with_cache=on'; + +SET SESSION join_cache_level=1; +EXPLAIN +SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2); +SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2); + +SET SESSION join_cache_level=3; +EXPLAIN +SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2); +SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2); + +SET SESSION join_cache_level=@save_join_cache_level; +set @@optimizer_switch=@tmp_optimizer_switch; + +DROP TABLE t1,t2; + +--echo # +--echo # Bug #899777: join_cache_level=4 + semijoin=on +--echo # + +CREATE TABLE t1 (a int, b int, c int, UNIQUE INDEX idx (a)); +INSERT INTO t1 VALUES (1,8,6), (2,2,8); +CREATE TABLE t2 (a int, b int, c int, UNIQUE INDEX idx (a)); +INSERT INTO t2 VALUES (1,8,6), (2,2,8); +CREATE TABLE t3 (a int, b int, c int, UNIQUE INDEX idx (a)); +INSERT INTO t3 VALUES (1,8,6), (2,2,8); +CREATE TABLE t4 (a int, b int, c int, UNIQUE INDEX idx (a)); +INSERT INTO t4 VALUES (1,8,6), (2,2,8); + +SET @tmp_optimizer_switch=@@optimizer_switch; +SET SESSION optimizer_switch='semijoin=on'; +SET SESSION optimizer_switch='semijoin_with_cache=on'; + +SET SESSION join_cache_level=1; +EXPLAIN +SELECT t1.* FROM t1,t2 + WHERE (t1.b,t2.b) IN (SELECT t3.b,t4.b FROM t3,t4 WHERE t4.c=t3.b) + AND t1.a = 1; +SELECT t1.* FROM t1,t2 + WHERE (t1.b,t2.b) IN (SELECT t3.b,t4.b FROM t3,t4 WHERE t4.c=t3.b) + AND t1.a = 1; + +SET SESSION join_cache_level=4; +EXPLAIN +SELECT t1.* FROM t1,t2 + WHERE (t1.b,t2.b) IN (SELECT t3.b,t4.b FROM t3,t4 WHERE t4.c=t3.b) + AND t1.a = 1; +SELECT t1.* FROM t1,t2 + WHERE (t1.b,t2.b) IN (SELECT t3.b,t4.b FROM t3,t4 WHERE t4.c=t3.b) + AND t1.a = 1; + +SET SESSION join_cache_level=@save_join_cache_level; +set @@optimizer_switch=@local_optimizer_switch; + +DROP TABLE t1,t2,t3,t4; + +--echo # +--echo # Bug #899509: an attempt to use hash join with join_cache_level=0 +--echo # + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (8), (7); +CREATE TABLE t2 (a int); +INSERT INTO t2 VALUES (8), (7); +CREATE TABLE t3 (a int); +INSERT INTO t3 VALUES (8), (7); + +set @@optimizer_switch='semijoin_with_cache=off'; +set @@optimizer_switch='outer_join_with_cache=off'; +set @@optimizer_switch='derived_merge=off,derived_with_keys=off'; +SET join_cache_level=0; + +EXPLAIN +SELECT * FROM (SELECT t1.* FROM t1, t2) t WHERE t.a IN (SELECT * FROM t3); +SELECT * FROM (SELECT t1.* FROM t1, t2) t WHERE t.a IN (SELECT * FROM t3); + +SELECT * FROM ( SELECT ta.* FROM t1 AS ta, t1 ) tb WHERE a IN ( SELECT * FROM t1 ); + +SET SESSION join_cache_level=@save_join_cache_level; +set @@optimizer_switch=@local_optimizer_switch; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # Bug #900469: semijoin + BNLH + ORDER BY +--echo # + +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (8,10); + +CREATE TABLE t2 (c int, d int); +INSERT INTO t2 VALUES (8,10); +INSERT INTO t2 VALUES (9,11); + +CREATE TABLE t3 (c int, d int); +INSERT INTO t3 VALUES (8,10); +INSERT INTO t3 VALUES (9,11); + +set @@optimizer_switch='semijoin_with_cache=on'; +set @@optimizer_switch='firstmatch=off'; + +SET join_cache_level=1; +EXPLAIN +SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c) ORDER BY a,d; +SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c) ORDER BY a,d; + +SET join_cache_level=3; +EXPLAIN +SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c); +SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c); + +SET join_cache_level=3; +EXPLAIN +SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c) ORDER BY a,d; +SELECT * FROM t1,t2 WHERE b IN (SELECT d FROM t3 WHERE c <= t2.c) ORDER BY a,d; + +SET SESSION join_cache_level=@save_join_cache_level; +set @@optimizer_switch=@local_optimizer_switch; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # Bug #901478: semijoin + ORDER BY + join_cache_level=4|6 +--echo # + +CREATE TABLE t1 (a char(1)); +INSERT INTO t1 VALUES ('x'); +CREATE TABLE t2 (a int, b int, c int, KEY(a), KEY(b), KEY(c)); +INSERT INTO t2 VALUES + (9,1,0), (7,2,8), (2,3,5), (4,2,9), (8,3,8), (3,4,1), (5,5,4); +CREATE TABLE t3 (a CHAR(1)); +INSERT INTO t3 VALUES ('x'); +CREATE TABLE t4 (a int, b int, c int, KEY(b), KEY(c)); +INSERT INTO t4 VALUES + (9,1,0), (7,2,8), (2,3,5), (4,2,9), (8,3,8), (3,4,1), (5,5,4); +INSERT INTO t4 VALUES + (19,11,10), (17,12,18), (12,13,15), (14,12,19), + (18,13,18), (13,14,11), (15,15,14); + +SET @@optimizer_switch='semijoin=on'; +SET @@optimizer_switch='firstmatch=off'; +SET @@optimizer_switch='mrr=off'; +SET @@optimizer_switch='semijoin_with_cache=off'; + +set join_cache_level=1; +EXPLAIN +SELECT * FROM t1,t2 + WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND + t2.a BETWEEN 4 and 5 + ORDER BY t2.b; +SELECT * FROM t1,t2 + WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND + t2.a BETWEEN 4 and 5 + ORDER BY t2.b; + +set join_cache_level=4; +EXPLAIN +SELECT * FROM t1,t2 + WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND + t2.a BETWEEN 4 and 5 + ORDER BY t2.b; +SELECT * FROM t1,t2 + WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND + t2.a BETWEEN 4 and 5 + ORDER BY t2.b; + +SET @@optimizer_switch='semijoin_with_cache=on'; +set join_cache_level=6; +EXPLAIN +SELECT * FROM t1,t2 + WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND + t2.a BETWEEN 4 and 5 + ORDER BY t2.b; +SELECT * FROM t1,t2 + WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND + t2.a BETWEEN 4 and 5 + ORDER BY t2.b; + +set join_cache_level=@save_join_cache_level; +set @@optimizer_switch=@local_optimizer_switch; + +DROP TABLE t1,t2,t3,t4; + +--echo # +--echo # Bug#53305 Duplicate weedout + join buffer (join cache --level=7,8) +--echo # + +create table t1 (uid int, fid int, index(uid)); +insert into t1 values + (1,1), (1,2), (1,3), (1,4), + (2,5), (2,6), (2,7), (2,8), + (3,1), (3,2), (3,9); + +create table t2 (uid int primary key, name varchar(128), index(name)); +insert into t2 values + (1, "A"), (2, "B"), (3, "C"), (4, "D"), (5, "E"), + (6, "F"), (7, "G"), (8, "H"), (9, "I"); + +create table t3 (uid int, fid int, index(uid)); +insert into t3 values + (1,1), (1,2), (1,3),(1,4), + (2,5), (2,6), (2,7), (2,8), + (3,1), (3,2), (3,9); + +set @@optimizer_switch='semijoin=on'; +set @@optimizer_switch='materialization=off'; +set @@optimizer_switch='loosescan=off,firstmatch=off'; +set @@optimizer_switch='mrr_sort_keys=off'; +set join_cache_level=7; + +create table t4 (uid int primary key, name varchar(128), index(name)); +insert into t4 values + (1, "A"), (2, "B"), (3, "C"), (4, "D"), (5, "E"), + (6, "F"), (7, "G"), (8, "H"), (9, "I"); + +explain select name from t2, t1 + where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid) + and t2.uid=t1.fid; + +--sorted_result +select name from t2, t1 + where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid) + and t2.uid=t1.fid; + +set join_cache_level=@save_join_cache_level; +set @@optimizer_switch=@local_optimizer_switch; + +drop table t1,t2,t3,t4; + +--echo # +--echo # Bug#50358 - semijoin execution of subquery with outerjoin +--echo # emplying join buffer +--echo # + +CREATE TABLE t1 (i int); +CREATE TABLE t2 (i int); +CREATE TABLE t3 (i int); +INSERT INTO t1 VALUES (1), (2); +INSERT INTO t2 VALUES (6); +INSERT INTO t3 VALUES (1), (2); + +set @@optimizer_switch='semijoin=on'; +set @@optimizer_switch='materialization=on'; + +set join_cache_level=0; +EXPLAIN +SELECT * FROM t1 WHERE t1.i IN + (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i); +SELECT * FROM t1 WHERE t1.i IN + (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i); + +set join_cache_level=2; +EXPLAIN +SELECT * FROM t1 WHERE t1.i IN + (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i); +SELECT * FROM t1 WHERE t1.i IN + (SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i); + +set join_cache_level=@save_join_cache_level; +set @@optimizer_switch=@local_optimizer_switch; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # Bug #12546542: missing row with semijoin=off + join cache +--echo # (LP bug #922971) +--echo # + +CREATE TABLE t1 (a varchar(1024)); +INSERT INTO t1 VALUES ('v'), ('we'); +CREATE TABLE t2 ( + a varchar(1024) CHARACTER SET utf8 DEFAULT NULL, b int, c int +); +INSERT INTO t2 VALUES ('we',4,NULL), ('v',1305673728,6); +CREATE TABLE t3 (b int, c int); +INSERT INTO t3 VALUES (4,4); + +set @@optimizer_switch='semijoin=off'; +set @@optimizer_switch='materialization=off'; + +set join_cache_level=0; +EXPLAIN +SELECT * FROM t1 + WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b + WHERE t2.c < 10 OR t3.c > 1); + +SELECT * FROM t1 + WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b + WHERE t2.c < 10 OR t3.c > 1); + +set join_cache_level=2; +EXPLAIN +SELECT * FROM t1 + WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b + WHERE t2.c < 10 OR t3.c > 1); +SELECT * FROM t1 + WHERE a IN (SELECT t2.a FROM t2 LEFT JOIN t3 ON t2.b = t3.b + WHERE t2.c < 10 OR t3.c > 1); + +set join_cache_level=@save_join_cache_level; +set @@optimizer_switch=@local_optimizer_switch; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # lp:925985 LEFT JOIN with optimize_join_buffer_size=off + +--echo # join_buffer_size > join_buffer_space_limit +--echo # + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (5), (3); + +CREATE TABLE t2 (a int, b int); +INSERT INTO t2 VALUES + (3,30), (1,10), (7,70), (2,20), + (3,31), (1,11), (7,71), (2,21), + (3,32), (1,12), (7,72), (2,22); + +CREATE TABLE t3 (b int, c int); +INSERT INTO t3 VALUES (32, 302), (42,400), (30,300); + +set @@optimizer_switch='optimize_join_buffer_size=off'; +set @@optimizer_switch='outer_join_with_cache=on'; +set join_buffer_space_limit=4096; +set join_buffer_size=4096*2; +set join_cache_level=2; + +EXPLAIN +SELECT * FROM t1, t2 LEFT JOIN t3 ON t2.b=t3.b WHERE t1.a=t2.a; +SELECT * FROM t1, t2 LEFT JOIN t3 ON t2.b=t3.b WHERE t1.a=t2.a; + +set join_buffer_space_limit=@save_join_buffer_space_limit; +set join_buffer_size=@save_join_buffer_size; +set join_cache_level=@save_join_cache_level; +set @@optimizer_switch=@local_optimizer_switch; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # Bug #1058071: LEFT JOIN using blobs +--echo # (MDEV-564) when join buffer size is small +--echo # + +CREATE TABLE t1 ( + col269 decimal(31,10) unsigned DEFAULT NULL, + col280 geometry DEFAULT NULL, + col281 tinyint(1) DEFAULT NULL, + col282 time NOT NULL, + col284 datetime DEFAULT NULL, + col286 date DEFAULT NULL, + col287 datetime DEFAULT NULL, + col288 decimal(30,29) DEFAULT NULL, + col291 time DEFAULT NULL, + col292 time DEFAULT NULL +) ENGINE=Aria; + +INSERT INTO t1 VALUES +(0.0,PointFromText('POINT(9 0)'),0,'11:24:05','2013-04-14 21:30:28',NULL,'2011-12-20 06:00:34',9.9,'13:04:39',NULL), +(0.0,NULL,127,'05:43:12','2012-09-05 06:15:27','2027-01-01','2011-10-29 10:48:29',0.0,'06:24:05','11:33:37'), +(0.0,NULL,127,'12:54:41','2013-01-12 11:32:58','2011-11-03','2013-01-03 02:00:34',00,'11:54:15','20:19:15'), +(0.0,PointFromText('POINT(9 0)'),0,'19:48:07','2012-07-16 15:45:25','2012-03-25','2013-09-07 17:21:52',0.5,'17:36:54','21:24:19'), +(0.0,PointFromText('POINT(9 0)'),0,'03:43:48','2012-09-28 00:00:00','2012-06-26','2011-11-16 05:01:09',00,'01:25:42','19:30:06'), +(0.0,LineStringFromText('LINESTRING(0 0,9 9,0 0,9 0,0 0)'),127,'11:33:21','2012-03-31 10:29:22','2012-10-10','2012-04-21 19:21:06',NULL,'05:13:22','09:48:34'), +(NULL,PointFromText('POINT(9 0)'),127,'00:00:00','0000-00-00','2012-04-04 21:26:12','2013-03-04',0.0,'12:54:30',NULL), +(NULL,PointFromText('POINT(9 0)'),1,'00:00:00','2013-05-01 22:37:49','2013-06-26','2012-09-22 17:31:03',0.0,'08:09:57','11:15:36'); + +CREATE TABLE t2 (b int) ENGINE=Aria; +INSERT INTO t2 VALUES (NULL); +CREATE TABLE t3 (c int) ENGINE=Aria; +INSERT INTO t3 VALUES (NULL); + +set @@optimizer_switch = 'outer_join_with_cache=on,join_cache_incremental=on'; +set join_buffer_size=128; + +EXPLAIN +SELECT 1 AS c FROM t1 NATURAL LEFT JOIN t2 LEFT OUTER JOIN t3 ON 1 + GROUP BY elt(t1.col282,1,t1.col280); + +SELECT 1 AS c FROM t1 NATURAL LEFT JOIN t2 LEFT OUTER JOIN t3 ON 1 + GROUP BY elt(t1.col282,1,t1.col280); + +DROP table t1,t2,t3; +set join_buffer_size=@save_join_buffer_size; + +# +# --echo switch to use orginal test suite optimizer switch +# + +set @@optimizer_switch=@org_optimizer_switch,@local_optimizer_switch= @org_optimizer_switch; + + +--echo # +--echo # MDEV-5293: outer join, join buffering, and order by - invalid query plan +--echo # +create table t0 (a int primary key) engine=myisam; +insert into t0 values (1); + +create table t1(a int) engine=myisam; +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +alter table t1 add b int; + +create table t2 like t1; +insert into t2 select * from t1; +--echo #The following must use "Using temporary; Using filesort" and not just "Using filesort": +explain select * from t0,t1 left join t2 on t1.b=t2.b order by t0.a, t1.a; + +drop table t0,t1,t2; + +--echo # MDEV-6292: huge performance degradation for a sequence +--echo # of LEFT JOIN operations when using join buffer +--echo # + +--source include/have_innodb.inc + +CREATE TABLE t1 ( + id int(11) NOT NULL AUTO_INCREMENT, + col1 varchar(255) NOT NULL DEFAULT '', + PRIMARY KEY (id) +) ENGINE=INNODB; + +CREATE TABLE t2 ( + id int(11) NOT NULL AUTO_INCREMENT, + parent_id smallint(3) NOT NULL DEFAULT '0', + col2 varchar(25) NOT NULL DEFAULT '', + PRIMARY KEY (id) +) ENGINE=INNODB; + +set join_buffer_size=8192; + +set join_cache_level=0; + +set @init_time:=now(); +SELECT t.* +FROM + t1 t + LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val" + LEFT JOIN t2 c2 ON c2.parent_id = t.id AND c2.col2 = "val" + LEFT JOIN t2 c3 ON c3.parent_id = t.id AND c3.col2 = "val" + LEFT JOIN t2 c4 ON c4.parent_id = t.id AND c4.col2 = "val" + LEFT JOIN t2 c5 ON c5.parent_id = t.id AND c5.col2 = "val" + LEFT JOIN t2 c6 ON c6.parent_id = t.id AND c6.col2 = "val" + LEFT JOIN t2 c7 ON c7.parent_id = t.id AND c7.col2 = "val" + LEFT JOIN t2 c8 ON c8.parent_id = t.id AND c8.col2 = "val" + LEFT JOIN t2 c9 ON c9.parent_id = t.id AND c9.col2 = "val" + LEFT JOIN t2 c10 ON c10.parent_id = t.id AND c10.col2 = "val" + LEFT JOIN t2 c11 ON c11.parent_id = t.id AND c11.col2 = "val" + LEFT JOIN t2 c12 ON c12.parent_id = t.id AND c12.col2 = "val" + LEFT JOIN t2 c13 ON c13.parent_id = t.id AND c13.col2 = "val" + LEFT JOIN t2 c14 ON c14.parent_id = t.id AND c14.col2 = "val" + LEFT JOIN t2 c15 ON c15.parent_id = t.id AND c15.col2 = "val" + LEFT JOIN t2 c16 ON c16.parent_id = t.id AND c16.col2 = "val" + LEFT JOIN t2 c17 ON c17.parent_id = t.id AND c17.col2 = "val" + LEFT JOIN t2 c18 ON c18.parent_id = t.id AND c18.col2 = "val" + LEFT JOIN t2 c19 ON c19.parent_id = t.id AND c19.col2 = "val" + LEFT JOIN t2 c20 ON c20.parent_id = t.id AND c20.col2 = "val" + LEFT JOIN t2 c21 ON c21.parent_id = t.id AND c21.col2 = "val" + LEFT JOIN t2 c22 ON c22.parent_id = t.id AND c22.col2 = "val" + LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val" + LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val" + LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val" + LEFT JOIN t2 c26 ON c26.parent_id = t.id AND c26.col2 = "val" + LEFT JOIN t2 c27 ON c27.parent_id = t.id AND c27.col2 = "val" +ORDER BY + col1; +select timestampdiff(second, @init_time, now()) <= 5; + +set join_cache_level=2; + +set @init_time:=now(); +SELECT t.* +FROM + t1 t + LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val" + LEFT JOIN t2 c2 ON c2.parent_id = t.id AND c2.col2 = "val" + LEFT JOIN t2 c3 ON c3.parent_id = t.id AND c3.col2 = "val" + LEFT JOIN t2 c4 ON c4.parent_id = t.id AND c4.col2 = "val" + LEFT JOIN t2 c5 ON c5.parent_id = t.id AND c5.col2 = "val" + LEFT JOIN t2 c6 ON c6.parent_id = t.id AND c6.col2 = "val" + LEFT JOIN t2 c7 ON c7.parent_id = t.id AND c7.col2 = "val" + LEFT JOIN t2 c8 ON c8.parent_id = t.id AND c8.col2 = "val" + LEFT JOIN t2 c9 ON c9.parent_id = t.id AND c9.col2 = "val" + LEFT JOIN t2 c10 ON c10.parent_id = t.id AND c10.col2 = "val" + LEFT JOIN t2 c11 ON c11.parent_id = t.id AND c11.col2 = "val" + LEFT JOIN t2 c12 ON c12.parent_id = t.id AND c12.col2 = "val" + LEFT JOIN t2 c13 ON c13.parent_id = t.id AND c13.col2 = "val" + LEFT JOIN t2 c14 ON c14.parent_id = t.id AND c14.col2 = "val" + LEFT JOIN t2 c15 ON c15.parent_id = t.id AND c15.col2 = "val" + LEFT JOIN t2 c16 ON c16.parent_id = t.id AND c16.col2 = "val" + LEFT JOIN t2 c17 ON c17.parent_id = t.id AND c17.col2 = "val" + LEFT JOIN t2 c18 ON c18.parent_id = t.id AND c18.col2 = "val" + LEFT JOIN t2 c19 ON c19.parent_id = t.id AND c19.col2 = "val" + LEFT JOIN t2 c20 ON c20.parent_id = t.id AND c20.col2 = "val" + LEFT JOIN t2 c21 ON c21.parent_id = t.id AND c21.col2 = "val" + LEFT JOIN t2 c22 ON c22.parent_id = t.id AND c22.col2 = "val" + LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val" + LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val" + LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val" + LEFT JOIN t2 c26 ON c26.parent_id = t.id AND c26.col2 = "val" + LEFT JOIN t2 c27 ON c27.parent_id = t.id AND c27.col2 = "val" +ORDER BY + col1; +select timestampdiff(second, @init_time, now()) <= 5; + +EXPLAIN +SELECT t.* +FROM + t1 t + LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val" + LEFT JOIN t2 c2 ON c2.parent_id = t.id AND c2.col2 = "val" + LEFT JOIN t2 c3 ON c3.parent_id = t.id AND c3.col2 = "val" + LEFT JOIN t2 c4 ON c4.parent_id = t.id AND c4.col2 = "val" + LEFT JOIN t2 c5 ON c5.parent_id = t.id AND c5.col2 = "val" + LEFT JOIN t2 c6 ON c6.parent_id = t.id AND c6.col2 = "val" + LEFT JOIN t2 c7 ON c7.parent_id = t.id AND c7.col2 = "val" + LEFT JOIN t2 c8 ON c8.parent_id = t.id AND c8.col2 = "val" + LEFT JOIN t2 c9 ON c9.parent_id = t.id AND c9.col2 = "val" + LEFT JOIN t2 c10 ON c10.parent_id = t.id AND c10.col2 = "val" + LEFT JOIN t2 c11 ON c11.parent_id = t.id AND c11.col2 = "val" + LEFT JOIN t2 c12 ON c12.parent_id = t.id AND c12.col2 = "val" + LEFT JOIN t2 c13 ON c13.parent_id = t.id AND c13.col2 = "val" + LEFT JOIN t2 c14 ON c14.parent_id = t.id AND c14.col2 = "val" + LEFT JOIN t2 c15 ON c15.parent_id = t.id AND c15.col2 = "val" + LEFT JOIN t2 c16 ON c16.parent_id = t.id AND c16.col2 = "val" + LEFT JOIN t2 c17 ON c17.parent_id = t.id AND c17.col2 = "val" + LEFT JOIN t2 c18 ON c18.parent_id = t.id AND c18.col2 = "val" + LEFT JOIN t2 c19 ON c19.parent_id = t.id AND c19.col2 = "val" + LEFT JOIN t2 c20 ON c20.parent_id = t.id AND c20.col2 = "val" + LEFT JOIN t2 c21 ON c21.parent_id = t.id AND c21.col2 = "val" + LEFT JOIN t2 c22 ON c22.parent_id = t.id AND c22.col2 = "val" + LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val" + LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val" + LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val" + LEFT JOIN t2 c26 ON c26.parent_id = t.id AND c26.col2 = "val" + LEFT JOIN t2 c27 ON c27.parent_id = t.id AND c27.col2 = "val" +ORDER BY + col1; + +set join_buffer_size=@save_join_buffer_size; +set join_cache_level=@save_join_cache_level; + +DROP TABLE t1,t2; + +--echo # +--echo # MDEV-14960: BNLH used for materialized semi-join +--echo # + +CREATE TABLE t1 (i1 int); +CREATE TABLE t2 (e1 int); +CREATE TABLE t4 (e1 int); +CREATE TABLE t5 (e1 int); + +INSERT INTO t1 VALUES + (1),(2),(3),(4),(5),(6),(7),(8); +INSERT INTO t1 SELECT i1+8 FROM t1; +INSERT INTO t1 SELECT i1+16 FROM t1; +INSERT INTO t1 SELECT i1+32 FROM t1; +INSERT INTO t1 SELECT i1+64 FROM t1; +INSERT INTO t2 SELECT * FROM t1; +INSERT INTO t4 SELECT * FROM t1; +INSERT INTO t5 SELECT * FROM t1; + +SET join_cache_level = 6; +SET join_buffer_size=4096; +SET join_buffer_space_limit=4096; +set @@optimizer_switch = 'join_cache_hashed=on,optimize_join_buffer_size=on'; + +let $q= +SELECT * FROM t1 +WHERE + i1 < 10 AND + i1 IN + (SELECT i1 FROM + (SELECT (t4.e1) i1 FROM t4 + LEFT JOIN t5 ON t4.e1 = t5.e1 + LEFT JOIN (SELECT e1 FROM t2 ) AS d ON t4.e1 = d.e1) a); + +eval EXPLAIN $q; +eval $q; + +set join_cache_level=@save_join_cache_level; +SET join_buffer_size=@save_join_buffer_size; +SET join_buffer_space_limit=@save_join_buffer_space_limit; +set @@optimizer_switch=@local_optimizer_switch; + +DROP TABLE t1,t4,t5,t2; + +--echo # +--echo # MDEV-16603: BNLH for query with materialized semi-join +--echo # + +--source include/have_innodb.inc + +set join_cache_level=4; + +CREATE TABLE t1 ( i1 int, v1 varchar(1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (7,'x'); + +CREATE TABLE t2 (i1 int, v1 varchar(1), KEY v1 (v1,i1)) ENGINE=InnoDB; + +INSERT INTO t2 VALUES + (NULL,'x'),(1,'x'),(3,'x'),(5,'x'),(8,'x'),(48,'x'), + (228,'x'),(3,'y'),(1,'z'),(9,'z'); + +ANALYZE TABLE t1,t2; + +CREATE TABLE temp +SELECT t1.i1 AS f1, t1.v1 AS f2 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1)); + +let $q = +SELECT * FROM temp +WHERE (f1,f2) IN (SELECT t1.i1, t1.v1 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1))); + +eval $q; +eval EXPLAIN EXTENDED $q; + +DROP TABLE t1,t2,temp; + +set join_cache_level=@save_join_cache_level; + +--echo # +--echo # MDEV-5123 Remove duplicated conditions pushed both to join_tab->select_cond and join_tab->cache_select->cond for blocked joins. +--echo # + +set expensive_subquery_limit=0; + +create table t1 (c1 int); +create table t2 (c2 int); +create table t3 (c3 int); + +insert into t1 values (1), (2); +insert into t2 values (1), (2); +insert into t3 values (2); + +set @counter=0; + +--disable_ps2_protocol +explain +select count(*) from t1 straight_join t2 +where c1 = c2-0 and c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1); + +select count(*) from t1 straight_join t2 +where c1 = c2-0 and c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1); + +select @counter; + +explain +select count(*) from t1 straight_join t2 +where c1 = c2-0 and + c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1) and + c2 / 2 = 1; + +set @counter=0; + +select count(*) from t1 straight_join t2 +where c1 = c2-0 and + c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1) and + c2 / 2 = 1; + +select @counter; + +--enable_ps2_protocol + +drop table t1,t2,t3; +set expensive_subquery_limit=@save_expensive_subquery_limit; + +--echo # +--echo # MDEV-6071: EXPLAIN chooses to use join buffer while execution turns it down +--echo # + +create table t1 (a int); +insert into t1 values +(7), (9), (1), (4), (2), (3), (5), (8), (11), (6), (10); + +explain select count(*) from t1, t1 t2 where t1.a=t2.a; + +set join_buffer_space_limit=1024*8; + +explain select count(*) from t1, t1 t2 where t1.a=t2.a; + +set join_buffer_space_limit=@save_join_buffer_space_limit; + +drop table t1; + +--echo # +--echo # MDEV-6687: Assertion `0' failed in Protocol::end_statement on query +--echo # +SET join_cache_level = 3; +--echo # The following should have +--echo # - table order PROFILING,user, +--echo # - table db accessed with hash_ALL: +explain +SELECT * FROM INFORMATION_SCHEMA.PROFILING, mysql.db WHERE Select_priv = PAGE_FAULTS_MINOR; + +set join_cache_level=@save_join_cache_level; + +# +# MDEV-12580 Wrong query result in join when using an index (Version > "10.2.3") +# +create table t1 (c1 date not null, key (c1)) engine=innodb; +insert t1 values ('2017-12-27'); +create table t2 (pk int, f1 int, f2 int); +insert t2 values (4,1,1), (6,1,1); +set join_buffer_size = 222222208; +select f2 from t2,t1 where f2 = 0; +drop table t1, t2; +set join_buffer_size=@save_join_buffer_size; + + +--echo # +--echo # MDEV-21104: BNLH used for multi-join query with embedded outer join +--echo # and possible 'not exists' optimization +--echo # + +set join_cache_level=4; + +CREATE TABLE t1 (a int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b int, c int) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,2),(2,4); +CREATE TABLE t3 (d int, KEY(d)) ENGINE=MyISAM; +INSERT INTO t3 VALUES (1),(2); +CREATE TABLE t4 (e int primary key) ENGINE=MyISAM; +INSERT INTO t4 VALUES (1),(2); +ANALYZE TABLE t1,t2,t3,t4; + +SELECT * FROM t2 LEFT JOIN t3 ON c = d; +SELECT * FROM (t2 LEFT JOIN t3 ON c = d ) JOIN t4; + +let $q1= +SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e; +eval EXPLAIN $q1; +eval $q1; + +let $q2= +SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e + WHERE e IS NULL; +eval EXPLAIN $q2; +eval $q2; + +DROP TABLE t1,t2,t3,t4; + +set join_cache_level=@save_join_cache_level; + +--echo # +--echo # MDEV-24767: forced BNLH used for equi-join supported by compound index +--echo # + +create table t1 (a int, b int, c int ) engine=myisam ; +create table t2 (a int, b int, c int, primary key (c,a,b)) engine=myisam ; +insert into t1 values (3,4,2), (5,6,4); +insert into t2 values (3,4,2), (5,6,4); + +let $q= +select t1.a, t1.b, t1.c from t1,t2 + where t2.a = t1.a and t2.b = t1.b and t2.c=t1.c; + +eval $q; +eval explain $q; + +set join_cache_level=3; +eval $q; +eval explain $q; + +drop table t1,t2; + +set join_cache_level=@save_join_cache_level; + +--echo # +--echo # MDEV-21243: Join buffer: condition is checked in wrong place for range access +--echo # +create table t1(a int primary key); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2 (a int); +insert into t2 select A.a + 10*B.a from t1 A, t1 B; + +create table t3 ( + kp1 int, + kp2 int, + col1 int, + col2 int, + key (kp1, kp2) +); + +insert into t3 +select + A.a, + B.a, + A.a + 100*B.a, + A.a + 100*B.a +from + t2 A, t2 B; +analyze table t3; + +--echo # The following must have "B.col1 + 1 < 33333" attached to table B +--echo # and not to the block-nl-join node: +explain format=json +select * +from t1 a, t3 b +where + b.kp1=a.a and + b.kp1 <= 10 and + b.kp2 <= 10 and + b.col1 +1 < 33333; + +drop table t1,t2,t3; + +--echo # End of 10.3 tests + +# The following command must be the last one in the file +set @@optimizer_switch=@save_optimizer_switch; + +set global innodb_stats_persistent= @innodb_stats_persistent_save; +set global innodb_stats_persistent_sample_pages= + @innodb_stats_persistent_sample_pages_save; + +--echo # +--echo # MDEV-31226 Server crash or assertion failure with row size close to +--echo # join_buffer_size +--echo # + +set @org_optimizer_switch=@@optimizer_switch; +set @org_join_buffer_size=@@join_buffer_size; +CREATE TABLE t (f VARCHAR(16384)) ENGINE=MyISAM CHARACTER SET utf8; +INSERT INTO t VALUES (REPEAT('a',16384)),(REPEAT('b',16384)); +SET OPTIMIZER_SWITCH = 'optimize_join_buffer_size=off'; +SET JOIN_BUFFER_SIZE = 16384; +explain SELECT length(concat(t1.f,t2.f)) FROM t t1, t t2; +SELECT length(concat(t1.f,t2.f)) FROM t t1, t t2; +DROP TABLE t; +set @@optimizer_switch=@org_optimizer_switch; +set @@join_buffer_size=@org_join_buffer_size; + +--echo # +--echo # MDEV-31348 Assertion `last_key_entry >= end_pos' failed in +--echo # virtual bool JOIN_CACHE_HASHED::put_record() +--echo # + +SET JOIN_buffer_size=1; +SET SESSION JOIN_cache_level=4; +SET SESSION optimizer_switch='optimize_JOIN_buffer_size=OFF'; +--error ER_OUTOFMEMORY +SELECT * FROM information_schema.statistics JOIN information_schema.COLUMNS USING (table_name,column_name); +SET JOIN_buffer_size=16384; +--disable_result_log +SELECT * FROM information_schema.statistics JOIN information_schema.COLUMNS USING (table_name,column_name); +--enable_result_log + +--echo # +--echo # MDEV-32351: Join buffer used for outer join with ON condition +--echo # depending only on outer tables +--echo # + +--source include/have_sequence.inc + +CREATE TABLE t1 (b int NOT NULL, PRIMARY KEY (b)) ENGINE=MYISAM; +INSERT INTO t1 select seq from seq_1_to_10000; +CREATE TABLE t2 (b int NOT NULL, d varchar(255), KEY (b)) ENGINE=MYISAM ; +INSERT INTO t2 VALUES (1,1),(2,2),(3,3); +CREATE TABLE t3 (c int NOT NULL, PRIMARY KEY (c)) ENGINE=MYISAM ; +INSERT INTO t3 select seq from seq_1_to_3000; +CREATE TABLE t4 (c int NOT NULL, PRIMARY KEY (c)) ENGINE=MYISAM; +INSERT INTO t4 select seq from seq_1_to_3000; +ANALYZE TABLE t1,t2,t3,t4; + +let $q1= +SELECT COUNT(*) +FROM t1 + LEFT JOIN t2 ON t1.b = t2.b + LEFT JOIN t3 ON t2.d = t3.c + LEFT JOIN t4 ON t3.c=1; + +set join_cache_level=0; +eval EXPLAIN $q1; +eval $q1; +set join_cache_level=default; +eval EXPLAIN $q1; +eval $q1; + +DROP TABLE t1,t2,t3,t4; + +CREATE TABLE t1 (b int NOT NULL, PRIMARY KEY (b)); +INSERT INTO t1 select seq from seq_1_to_10; +CREATE TABLE t2 (b int NOT NULL, d varchar(255), KEY (b)) ; +INSERT INTO t2 VALUES (1,1),(2,2),(3,3); +CREATE TABLE t3 (c int NOT NULL, PRIMARY KEY (c)) ; +INSERT INTO t3 select seq from seq_1_to_3; +CREATE TABLE t4 (c int NOT NULL, PRIMARY KEY (c)) ; +INSERT INTO t4 select seq from seq_1_to_3; + +let $q2= +SELECT * +FROM t1 + LEFT JOIN t2 ON t1.b = t2.b + LEFT JOIN t3 ON t2.d = t3.c + LEFT JOIN t4 ON t3.c=1; + +set join_cache_level=0; +eval EXPLAIN $q2; +eval $q2; +set join_cache_level=default; +eval EXPLAIN $q2; +eval $q2; + +DROP TABLE t1,t2,t3,t4; + +--echo # +--echo # End of 10.4 tests +--echo # |