path: root/mysql-test/main/join_cache.test

--disable_warnings
DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11;
DROP DATABASE IF EXISTS world;
--enable_warnings
--source include/default_optimizer_switch.inc
--source include/default_charset.inc

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;

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;

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;

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;


--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;

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;

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;

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;

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;

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;

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;

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;

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 ,
  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');

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%";

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);

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);

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);

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;

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;