diff options
45 files changed, 1066 insertions, 414 deletions
diff --git a/BUILD/compile-dist b/BUILD/compile-dist index d8b939dc0fc..3b02629b8c1 100755 --- a/BUILD/compile-dist +++ b/BUILD/compile-dist @@ -28,7 +28,7 @@ fi # Default to gcc for CC and CXX if test -z "$CXX" ; then export CXX - CXX=gcc + CXX=g++ # Set some required compile options if test -z "$CXXFLAGS" ; then export CXXFLAGS diff --git a/client/readline.cc b/client/readline.cc index b32cb71b0de..2a66fc6144b 100644 --- a/client/readline.cc +++ b/client/readline.cc @@ -47,6 +47,7 @@ char *batch_readline(LINE_BUFFER *line_buff, bool *truncated) char *pos; ulong out_length; DBUG_ASSERT(truncated != NULL); + LINT_INIT(out_length); if (!(pos=intern_read_line(line_buff,&out_length, truncated))) return 0; diff --git a/cmd-line-utils/libedit/vi.c b/cmd-line-utils/libedit/vi.c index d628f076a1d..d18a518a10d 100644 --- a/cmd-line-utils/libedit/vi.c +++ b/cmd-line-utils/libedit/vi.c @@ -1012,8 +1012,13 @@ vi_histedit(EditLine *el, int c __attribute__((__unused__))) if (fd < 0) return CC_ERROR; cp = el->el_line.buffer; - write(fd, cp, el->el_line.lastchar - cp +0u); - write(fd, "\n", 1); + if (write(fd, cp, el->el_line.lastchar - cp +0u) == -1 || + write(fd, "\n", 1) == -1) + { + close(fd); + unlink(tempfile); + return CC_ERROR; + } pid = fork(); switch (pid) { case -1: diff --git a/mysql-test/r/index_intersect.result b/mysql-test/r/index_intersect.result index c1b4464c3dd..1bbe7d09c34 100644 --- a/mysql-test/r/index_intersect.result +++ b/mysql-test/r/index_intersect.result @@ -64,22 +64,22 @@ EXPLAIN SELECT * FROM City WHERE Name LIKE 'C%' AND Population > 1000000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge Population,Name Name,Population 35,4 NULL 9 Using sort_intersect(Name,Population); Using where +1 SIMPLE City index_merge Population,Name Name,Population 35,4 NULL # Using sort_intersect(Name,Population); Using where EXPLAIN SELECT * FROM City WHERE Name LIKE 'M%' AND Population > 1500000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge Population,Name Population,Name 4,35 NULL 5 Using sort_intersect(Population,Name); Using where +1 SIMPLE City index_merge Population,Name Population,Name 4,35 NULL # Using sort_intersect(Population,Name); Using where EXPLAIN SELECT * FROM City WHERE Name LIKE 'M%' AND Population > 300000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population,Name Name 35 NULL 164 Using index condition; Using where; Using MRR +1 SIMPLE City range Population,Name Name 35 NULL # Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM City WHERE Name LIKE 'M%' AND Population > 7000000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population,Name Population 4 NULL 15 Using index condition; Using where; Using MRR +1 SIMPLE City range Population,Name Population 4 NULL # Using index condition; Using where; Using MRR SELECT * FROM City USE INDEX () WHERE Name LIKE 'C%' AND Population > 1000000; ID Name Country Population @@ -361,17 +361,17 @@ EXPLAIN SELECT * FROM City WHERE Name BETWEEN 'M' AND 'N' AND Population > 1000000 AND Country LIKE 'C%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge Population,Country,Name Name,Population 35,4 NULL 9 Using sort_intersect(Name,Population); Using where +1 SIMPLE City index_merge Population,Country,Name Name,Population 35,4 NULL # Using sort_intersect(Name,Population); Using where EXPLAIN SELECT * FROM City WHERE Name BETWEEN 'G' AND 'J' AND Population > 1000000 AND Country LIKE 'B%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge Population,Country,Name Population,Country 4,3 NULL 19 Using sort_intersect(Population,Country); Using where +1 SIMPLE City index_merge Population,Country,Name Population,Country 4,3 NULL # Using sort_intersect(Population,Country); Using where EXPLAIN SELECT * FROM City WHERE Name BETWEEN 'G' AND 'K' AND Population > 500000 AND Country LIKE 'C%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population,Country,Name Name 35 NULL 283 Using index condition; Using where; Using MRR +1 SIMPLE City range Population,Country,Name Name 35 NULL # Using index condition; Using where; Using MRR SELECT * FROM City USE INDEX () WHERE Name BETWEEN 'M' AND 'N' AND Population > 1000000 AND Country LIKE 'C%'; ID Name Country Population @@ -462,29 +462,29 @@ EXPLAIN SELECT * FROM City WHERE ID BETWEEN 501 AND 1000 AND Population > 700000 AND Country LIKE 'C%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY,Population,Country Population 4 NULL 359 Using index condition; Using where; Using MRR +1 SIMPLE City range PRIMARY,Population,Country Population 4 NULL # Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM City WHERE ID BETWEEN 1 AND 500 AND Population > 1000000 AND Country LIKE 'A%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge PRIMARY,Population,Country Country,Population 3,4 NULL 6 Using sort_intersect(Country,Population); Using where +1 SIMPLE City index_merge PRIMARY,Population,Country Country,Population 3,4 NULL # Using sort_intersect(Country,Population); Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 2001 AND 2500 AND Population > 300000 AND Country LIKE 'H%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY,Population,Country Country 3 NULL 21 Using index condition; Using where; Using MRR +1 SIMPLE City range PRIMARY,Population,Country Country 3 NULL # Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM City WHERE ID BETWEEN 3701 AND 4000 AND Population > 1000000 AND Country BETWEEN 'S' AND 'Z'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge PRIMARY,Population,Country Population,PRIMARY 4,4 NULL 17 Using sort_intersect(Population,PRIMARY); Using where +1 SIMPLE City index_merge PRIMARY,Population,Country Population,PRIMARY 4,4 NULL # Using sort_intersect(Population,PRIMARY); Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000 AND Country BETWEEN 'S' AND 'Z' ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY,Population,Country Population 4 NULL 429 Using index condition; Using where; Using MRR +1 SIMPLE City range PRIMARY,Population,Country Population 4 NULL # Using index condition; Using where; Using MRR SELECT * FROM City USE INDEX () WHERE ID BETWEEN 501 AND 1000 AND Population > 700000 AND Country LIKE 'C%'; ID Name Country Population @@ -718,33 +718,33 @@ EXPLAIN SELECT * FROM City WHERE Name LIKE 'C%' AND Population > 1000000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge Population,Name Name,Population 35,4 NULL 9 Using sort_intersect(Name,Population); Using where +1 SIMPLE City index_merge Population,Name Name,Population 35,4 NULL # Using sort_intersect(Name,Population); Using where EXPLAIN SELECT * FROM City WHERE Name LIKE 'M%' AND Population > 1500000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge Population,Name Population,Name 4,35 NULL 5 Using sort_intersect(Population,Name); Using where +1 SIMPLE City index_merge Population,Name Population,Name 4,35 NULL # Using sort_intersect(Population,Name); Using where EXPLAIN SELECT * FROM City WHERE Name BETWEEN 'G' AND 'J' AND Population > 1000000 AND Country LIKE 'B%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge Population,Country,Name Population,Country 4,3 NULL 19 Using sort_intersect(Population,Country); Using where +1 SIMPLE City index_merge Population,Country,Name Population,Country 4,3 NULL # Using sort_intersect(Population,Country); Using where EXPLAIN SELECT * FROM City WHERE Name BETWEEN 'G' AND 'J' AND Population > 500000 AND Country LIKE 'C%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Population,Country,Name Name 35 NULL 225 Using index condition; Using where; Using MRR +1 SIMPLE City range Population,Country,Name Name 35 NULL # Using index condition; Using where; Using MRR EXPLAIN SELECT * FROM City WHERE ID BETWEEN 1 AND 500 AND Population > 1000000 AND Country LIKE 'A%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge PRIMARY,Population,Country Country,Population 3,4 NULL 6 Using sort_intersect(Country,Population); Using where +1 SIMPLE City index_merge PRIMARY,Population,Country Country,Population 3,4 NULL # Using sort_intersect(Country,Population); Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 3001 AND 4000 AND Population > 600000 AND Country BETWEEN 'S' AND 'Z'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY,Population,Country Population 4 NULL 429 Using index condition; Using where; Using MRR +1 SIMPLE City range PRIMARY,Population,Country Population 4 NULL # Using index condition; Using where; Using MRR SELECT * FROM City WHERE Name LIKE 'C%' AND Population > 1000000; ID Name Country Population @@ -885,17 +885,17 @@ EXPLAIN SELECT * FROM City WHERE Country LIKE 'M%' AND Population > 1000000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge Population,CountryID,CountryName Population,CountryID 4,3 NULL 15 Using sort_intersect(Population,CountryID); Using where +1 SIMPLE City index_merge Population,CountryID,CountryName Population,CountryID 4,3 NULL # Using sort_intersect(Population,CountryID); Using where EXPLAIN SELECT * FROM City WHERE Country='CHN' AND Population > 1500000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge Population,CountryID,CountryName Population,CountryID 4,3 NULL 11 Using sort_intersect(Population,CountryID); Using where +1 SIMPLE City index_merge Population,CountryID,CountryName Population,CountryID 4,3 NULL # Using sort_intersect(Population,CountryID); Using where EXPLAIN SELECT * FROM City WHERE Country='CHN' AND Population > 1500000 AND Name LIKE 'C%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge Population,Name,CountryID,CountryName CountryName,Population 38,4 NULL 1 Using sort_intersect(CountryName,Population); Using where +1 SIMPLE City index_merge Population,Name,CountryID,CountryName CountryName,Population 38,4 NULL # Using sort_intersect(CountryName,Population); Using where SELECT * FROM City USE INDEX () WHERE Country LIKE 'M%' AND Population > 1000000; ID Name Country Population @@ -1028,7 +1028,7 @@ EXPLAIN SELECT * FROM t1 WHERE (f1 < 535 OR f1 > 985) AND ( f4='r' OR f4 LIKE 'a%' ) ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,f4 f4 35 NULL 5 Using index condition; Using where; Using MRR +1 SIMPLE t1 range PRIMARY,f4 f4 35 NULL # Using index condition; Using where; Using MRR SELECT * FROM t1 WHERE (f1 < 535 OR f1 > 985) AND ( f4='r' OR f4 LIKE 'a%' ) ; f1 f4 f5 diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index e8df0cfec2f..749e58df26f 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -1251,3 +1251,37 @@ Handler_read_rnd 0 Handler_read_rnd_next 1 DROP TABLE t1, t2; End of 5.1 tests +# +# BUG#724275: Crash in JOIN::optimize in maria-5.3 +# +create table t1 (a int); +insert into t1 values (1),(2); +insert into t1 select * from t1; +create table t2 (a int, b int, key(a,b)); +insert into t2 values (1,1),(1,2),(1,3),(1,4),(2,5),(2,6),(2,7),(2,8),(2,9); +insert into t2 select * from t2; +insert into t2 select * from t2; +insert into t2 select * from t2; +create table t3 (a int, b int, key(a)); +insert into t3 values (1,1),(2,2); +select * from +t3 straight_join t1 straight_join t2 force index(a) +where t2.a=1 and t2.b=t1.a and t1.a=t3.b and t3.a=1; +a b a a b +1 1 1 1 1 +1 1 1 1 1 +1 1 1 1 1 +1 1 1 1 1 +1 1 1 1 1 +1 1 1 1 1 +1 1 1 1 1 +1 1 1 1 1 +1 1 1 1 1 +1 1 1 1 1 +1 1 1 1 1 +1 1 1 1 1 +1 1 1 1 1 +1 1 1 1 1 +1 1 1 1 1 +1 1 1 1 1 +drop table t1,t2,t3; diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index ca1558610b6..99e76952cfe 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -201,7 +201,7 @@ WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE Country ALL NULL NULL NULL NULL 239 Using where -1 SIMPLE City hash NULL hj_key 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE City hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -231,8 +231,8 @@ CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE Country ALL NULL NULL NULL NULL 239 Using where -1 SIMPLE CountryLanguage hash NULL hj_key 3 world.Country.Code 984 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE City hash NULL hj_key 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE CountryLanguage hash_ALL NULL #hash#$hj 3 world.Country.Code 984 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE City hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -279,7 +279,7 @@ WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE Country ALL NULL NULL NULL NULL 239 Using where -1 SIMPLE City hash NULL hj_key 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE City hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -309,8 +309,8 @@ CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE Country ALL NULL NULL NULL NULL 239 Using where -1 SIMPLE City hash NULL hj_key 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE CountryLanguage hash NULL hj_key 3 world.Country.Code 984 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE City hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE CountryLanguage hash_ALL NULL #hash#$hj 3 world.Country.Code 984 Using where; Using join buffer (incremental, BNLH join) SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -347,6 +347,110 @@ Long Beach United States English Lexington-Fayette United States English Louisville United States English Little Rock United States English +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; +Name Population Name Population +China 1277558000 Shanghai 9696300 +China 1277558000 Peking 7472000 +China 1277558000 Chongqing 6351600 +China 1277558000 Tianjin 5286800 +Colombia 42321000 Santafé de Bogotá 6260862 +Congo, The Democratic Republic of the 51654000 Kinshasa 5064000 +Chile 15211000 NULL NULL +Cambodia 11168000 NULL NULL +Cameroon 15085000 NULL NULL +Canada 31147000 NULL NULL +Cuba 11201000 NULL NULL +Côte d?Ivoire 14786000 NULL NULL +Czech Republic 10278100 NULL NULL +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; +Name Population Name Population +China 1277558000 Shanghai 9696300 +China 1277558000 Peking 7472000 +China 1277558000 Chongqing 6351600 +China 1277558000 Tianjin 5286800 +China 1277558000 Zaozhuang 380846 +China 1277558000 Zaoyang 162198 +China 1277558000 Zalantun 130031 +Colombia 42321000 Santafé de Bogotá 6260862 +Congo, The Democratic Republic of the 51654000 Kinshasa 5064000 +Chile 15211000 NULL NULL +Cambodia 11168000 NULL NULL +Cameroon 15085000 NULL NULL +Canada 31147000 NULL NULL +Cuba 11201000 NULL NULL +Côte d?Ivoire 14786000 NULL NULL +Czech Republic 10278100 NULL NULL +CREATE INDEX City_Population ON City(Population); +CREATE INDEX City_Name ON City(Name); +ANALYZE TABLE City; +Table Op Msg_type Msg_text +world.City analyze status Table is already up to date +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; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE Country ALL NULL NULL NULL NULL 239 Using where +1 SIMPLE City hash_range City_Population #hash#$hj:City_Population 3:4 world.Country.Code 25 Using where; Using MRR; Using join buffer (flat, BNLH join) +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; +Name Population Name Population +China 1277558000 Shanghai 9696300 +China 1277558000 Peking 7472000 +China 1277558000 Chongqing 6351600 +China 1277558000 Tianjin 5286800 +Colombia 42321000 Santafé de Bogotá 6260862 +Congo, The Democratic Republic of the 51654000 Kinshasa 5064000 +Chile 15211000 NULL NULL +Cambodia 11168000 NULL NULL +Cameroon 15085000 NULL NULL +Canada 31147000 NULL NULL +Cuba 11201000 NULL NULL +Côte d?Ivoire 14786000 NULL NULL +Czech Republic 10278100 NULL NULL +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; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE Country ALL NULL NULL NULL NULL 239 Using where +1 SIMPLE City hash_index_merge City_Population,City_Name #hash#$hj:City_Population,City_Name 3:4,35 world.Country.Code 96 Using sort_union(City_Population,City_Name); Using where; Using join buffer (flat, BNLH join) +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; +Name Population Name Population +China 1277558000 Shanghai 9696300 +China 1277558000 Peking 7472000 +China 1277558000 Chongqing 6351600 +China 1277558000 Tianjin 5286800 +China 1277558000 Zaozhuang 380846 +China 1277558000 Zaoyang 162198 +China 1277558000 Zalantun 130031 +Colombia 42321000 Santafé de Bogotá 6260862 +Congo, The Democratic Republic of the 51654000 Kinshasa 5064000 +Chile 15211000 NULL NULL +Cambodia 11168000 NULL NULL +Cameroon 15085000 NULL NULL +Canada 31147000 NULL NULL +Cuba 11201000 NULL NULL +Côte d?Ivoire 14786000 NULL NULL +Czech Republic 10278100 NULL NULL +DROP INDEX City_Population ON City; +DROP INDEX City_Name ON City; set join_cache_level=default; set join_buffer_size=256; show variables like 'join_buffer_size'; @@ -517,7 +621,7 @@ WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE Country ALL NULL NULL NULL NULL 239 Using where -1 SIMPLE City hash NULL hj_key 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE City hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -547,8 +651,8 @@ CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE Country ALL NULL NULL NULL NULL 239 Using where -1 SIMPLE City hash NULL hj_key 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE CountryLanguage hash NULL hj_key 3 world.Country.Code 984 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE City hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE CountryLanguage hash_ALL NULL #hash#$hj 3 world.Country.Code 984 Using where; Using join buffer (flat, BNLH join) SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -595,7 +699,7 @@ WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE Country ALL NULL NULL NULL NULL 239 Using where -1 SIMPLE City hash NULL hj_key 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE City hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -625,8 +729,8 @@ CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE Country ALL NULL NULL NULL NULL 239 Using where -1 SIMPLE City hash NULL hj_key 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE CountryLanguage hash NULL hj_key 3 world.Country.Code 984 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE City hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE CountryLanguage hash_ALL NULL #hash#$hj 3 world.Country.Code 984 Using where; Using join buffer (incremental, BNLH join) SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -712,7 +816,7 @@ WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR -1 SIMPLE City hash Population,Country Country 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE City hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -742,8 +846,8 @@ CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE CountryLanguage ALL PRIMARY,Percentage NULL NULL NULL 984 Using where -1 SIMPLE Country hash PRIMARY PRIMARY 3 world.CountryLanguage.Country 239 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE City hash Country Country 3 world.CountryLanguage.Country 4079 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE Country hash_ALL PRIMARY #hash#PRIMARY 3 world.CountryLanguage.Country 239 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE City hash_ALL Country #hash#Country 3 world.CountryLanguage.Country 4079 Using where; Using join buffer (flat, BNLH join) SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -786,7 +890,7 @@ WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AN City.Population > 100000; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR -1 PRIMARY City hash Population,Country Country 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY City hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -814,7 +918,7 @@ WHERE Country.Population > 10000000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE Country ALL NULL NULL NULL NULL 239 Using where -1 SIMPLE CountryLanguage hash PRIMARY PRIMARY 33 world.Country.Code,const 984 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE CountryLanguage hash_ALL PRIMARY #hash#PRIMARY 33 world.Country.Code,const 984 Using where; Using join buffer (flat, BNLH join) SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) FROM Country LEFT JOIN CountryLanguage ON (CountryLanguage.Country=Country.Code AND Language='English') @@ -912,7 +1016,7 @@ WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR -1 SIMPLE City hash Population,Country Country 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE City hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -942,8 +1046,8 @@ CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE CountryLanguage ALL PRIMARY,Percentage NULL NULL NULL 984 Using where -1 SIMPLE Country hash PRIMARY PRIMARY 3 world.CountryLanguage.Country 239 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE City hash Country Country 3 world.CountryLanguage.Country 4079 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE Country hash_ALL PRIMARY #hash#PRIMARY 3 world.CountryLanguage.Country 239 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE City hash_ALL Country #hash#Country 3 world.CountryLanguage.Country 4079 Using where; Using join buffer (incremental, BNLH join) SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -986,7 +1090,7 @@ WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AN City.Population > 100000; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR -1 PRIMARY City hash Population,Country Country 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY City hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -1014,7 +1118,7 @@ WHERE Country.Population > 10000000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE Country ALL NULL NULL NULL NULL 239 Using where -1 SIMPLE CountryLanguage hash PRIMARY PRIMARY 33 world.Country.Code,const 984 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE CountryLanguage hash_ALL PRIMARY #hash#PRIMARY 33 world.Country.Code,const 984 Using where; Using join buffer (flat, BNLH join) SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) FROM Country LEFT JOIN CountryLanguage ON (CountryLanguage.Country=Country.Code AND Language='English') @@ -1099,6 +1203,65 @@ Belarus NULL Venezuela NULL Russian Federation NULL Vietnam NULL +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; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE Country range Name Name 52 NULL # Using index condition; Using where; Using MRR +1 SIMPLE City hash_range Population,Country #hash#Country:Population 3:4 world.Country.Code # Using where; Using MRR; Using join buffer (flat, BNLH join) +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; +Name Population Name Population +China 1277558000 Shanghai 9696300 +China 1277558000 Peking 7472000 +China 1277558000 Chongqing 6351600 +China 1277558000 Tianjin 5286800 +Colombia 42321000 Santafé de Bogotá 6260862 +Congo, The Democratic Republic of the 51654000 Kinshasa 5064000 +Chile 15211000 NULL NULL +Cambodia 11168000 NULL NULL +Cameroon 15085000 NULL NULL +Canada 31147000 NULL NULL +Cuba 11201000 NULL NULL +Côte d?Ivoire 14786000 NULL NULL +Czech Republic 10278100 NULL NULL +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; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE Country range Name Name 52 NULL 17 Using index condition; Using where; Using MRR +1 SIMPLE City hash_index_merge Population,Country,City_Name #hash#Country:Population,City_Name 3:4,35 world.Country.Code 96 Using sort_union(Population,City_Name); Using where; Using join buffer (flat, BNLH join) +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; +Name Population Name Population +China 1277558000 Shanghai 9696300 +China 1277558000 Peking 7472000 +China 1277558000 Chongqing 6351600 +China 1277558000 Tianjin 5286800 +China 1277558000 Zaozhuang 380846 +China 1277558000 Zaoyang 162198 +China 1277558000 Zalantun 130031 +Colombia 42321000 Santafé de Bogotá 6260862 +Congo, The Democratic Republic of the 51654000 Kinshasa 5064000 +Chile 15211000 NULL NULL +Cambodia 11168000 NULL NULL +Cameroon 15085000 NULL NULL +Canada 31147000 NULL NULL +Cuba 11201000 NULL NULL +Côte d?Ivoire 14786000 NULL NULL +Czech Republic 10278100 NULL NULL +DROP INDEX City_Name ON City; show variables like 'join_buffer_size'; Variable_name Value join_buffer_size 131072 @@ -1904,7 +2067,7 @@ WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR -1 SIMPLE City hash Population,Country Country 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE City hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -1934,8 +2097,8 @@ CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE CountryLanguage ALL PRIMARY,Percentage NULL NULL NULL 984 Using where -1 SIMPLE Country hash PRIMARY PRIMARY 3 world.CountryLanguage.Country 239 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE City hash Country Country 3 world.CountryLanguage.Country 4079 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE Country hash_ALL PRIMARY #hash#PRIMARY 3 world.CountryLanguage.Country 239 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE City hash_ALL Country #hash#Country 3 world.CountryLanguage.Country 4079 Using where; Using join buffer (flat, BNLH join) SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -1978,7 +2141,7 @@ WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AN City.Population > 100000; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR -1 PRIMARY City hash Population,Country Country 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY City hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -2008,7 +2171,7 @@ WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR -1 SIMPLE City hash Population,Country Country 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE City hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -2038,8 +2201,8 @@ CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE CountryLanguage ALL PRIMARY,Percentage NULL NULL NULL 984 Using where -1 SIMPLE Country hash PRIMARY PRIMARY 3 world.CountryLanguage.Country 239 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE City hash Country Country 3 world.CountryLanguage.Country 4079 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE Country hash_ALL PRIMARY #hash#PRIMARY 3 world.CountryLanguage.Country 239 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE City hash_ALL Country #hash#Country 3 world.CountryLanguage.Country 4079 Using where; Using join buffer (incremental, BNLH join) SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -2082,7 +2245,7 @@ WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AN City.Population > 100000; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY Country range PRIMARY,Name Name 52 NULL 10 Using index condition; Using MRR -1 PRIMARY City hash Population,Country Country 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY City hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 Using where; Using join buffer (flat, BNLH join) SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -3105,7 +3268,7 @@ a b a c explain select * from t1 left join t2 on t1.a=t2.a where t2.c=102 or t2.c is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 -1 SIMPLE t2 hash NULL hj_key 5 test.t1.a 3 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL NULL #hash#$hj 5 test.t1.a 3 Using where; Using join buffer (flat, BNLH join) select * from t1 left join t2 on t1.a=t2.a where t2.c=102 or t2.c is null; a b a c 3 30 3 102 @@ -3959,7 +4122,7 @@ WHERE t3.u <='a' AND t2.j < 5 AND t3.v = t2.u; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 16 1 SIMPLE t2 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t3 hash idx idx 3 test.t2.u 20 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t3 hash_ALL idx #hash#idx 3 test.t2.u 20 Using where; Using join buffer (flat, BNLH join) 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; i d v i d t v @@ -4082,8 +4245,8 @@ SELECT t2.v FROM t1, t2, t3 WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v GROUP BY t2.v ORDER BY t1.pk,t2.v; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL idx1 NULL NULL NULL 20 Using temporary; Using filesort -1 SIMPLE t3 hash PRIMARY,idx2 PRIMARY 4 test.t2.i 20 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE t1 hash idx2 idx2 3 test.t3.v 15 Using join buffer (incremental, BNLH join) +1 SIMPLE t3 hash_ALL PRIMARY,idx2 #hash#PRIMARY 4 test.t2.i 20 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t1 hash_ALL idx2 #hash#idx2 3 test.t3.v 15 Using join buffer (incremental, BNLH join) SELECT t2.v FROM t1, t2, t3 WHERE t3.v <> t2.v AND t3.pk = t2.i AND t1.v = t3.v GROUP BY t2.v ORDER BY t1.pk,t2.v; v @@ -4134,7 +4297,7 @@ EXPLAIN SELECT t2.i FROM t1,t2 WHERE t1.cu = t2.cl ; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 6 -1 SIMPLE t1 hash cu cu 33 func 10 Using where; Using index; Using join buffer (flat, BNLH join) +1 SIMPLE t1 hash_index cu #hash#cu:cu 33:33 func 10 Using where; Using join buffer (flat, BNLH join) SELECT t2.i FROM t1,t2 WHERE t1.cu = t2.cl ; i 6 @@ -4161,7 +4324,7 @@ EXPLAIN SELECT t1.a, t2.c FROM t1,t2 WHERE t1.a=t2.a AND t2.b=99; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 36 Using where -1 SIMPLE t2 hash idx idx 10 test.t1.a,const 15 Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL idx #hash#idx 10 test.t1.a,const 15 Using join buffer (flat, BNLH join) SELECT t1.a, t2.c FROM t1,t2 WHERE t1.a=t2.a AND t2.b=99; a c SET SESSION join_cache_level = DEFAULT; @@ -4211,7 +4374,7 @@ EXPLAIN SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = t1.v; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where -1 SIMPLE t2 hash idx idx 13 test.t1.v 18 Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL idx #hash#idx 13 test.t1.v 18 Using join buffer (flat, BNLH join) SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = t1.v; v i f 4 @@ -4220,7 +4383,7 @@ EXPLAIN SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = concat(t1.v, t1.v); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 7 -1 SIMPLE t2 hash idx idx 13 func 18 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL idx #hash#idx 13 func 18 Using where; Using join buffer (flat, BNLH join) SELECT t1.v,t2.i FROM t1,t2 WHERE t2.v = concat(t1.v, t1.v); v i f 5 @@ -4273,8 +4436,8 @@ SELECT t3.i FROM t1,t2,t3 WHERE t1.v = t2.v AND t3.v = t1.v AND t2.i <> 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index idx idx 13 NULL 7 Using where; Using index -1 SIMPLE t2 hash idx idx 1003 test.t1.v 18 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE t3 hash idx idx 1002 func 32 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t2 hash_ALL idx #hash#idx 1003 test.t1.v 18 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t3 hash_ALL idx #hash#idx 1002 func 32 Using where; Using join buffer (incremental, BNLH join) SELECT t3.i FROM t1,t2,t3 WHERE t1.v = t2.v AND t3.v = t1.v AND t2.i <> 0; i @@ -4311,7 +4474,7 @@ EXPLAIN SELECT * FROM t1,t2 WHERE t2.a=t1.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL idx NULL NULL NULL 3 Using where -1 SIMPLE t2 hash idx idx 515 test.t1.a 12 Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL idx #hash#idx 515 test.t1.a 12 Using join buffer (flat, BNLH join) SELECT * FROM t1,t2 WHERE t2.a=t1.a; pk a pk a 2 aa 30 aa @@ -4494,8 +4657,8 @@ t1 LEFT JOIN WHERE t3.a3 IS NULL; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 -1 SIMPLE t3 hash PRIMARY hj_key 5 test.t1.c1 1 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE t2 hash NULL hj_key 5 test.t3.b3 1 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t3 hash_ALL PRIMARY #hash#$hj 5 test.t1.c1 1 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL NULL #hash#$hj 5 test.t3.b3 1 Using where; Using join buffer (incremental, BNLH join) 1 SIMPLE t4 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) SELECT * FROM t1 LEFT JOIN @@ -4548,7 +4711,7 @@ SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 1 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t3 hash PRIMARY PRIMARY 4 test.t2.a2 1 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t3 hash_ALL PRIMARY #hash#PRIMARY 4 test.t2.a2 1 Using where; Using join buffer (incremental, BNLH join) SELECT * FROM t1 LEFT JOIN t2 JOIN t3 ON t3.a3 = t2.a2 ON t3.b3 <> 0; a1 a2 b2 a3 b3 0 2 1 2 1 @@ -4624,7 +4787,7 @@ EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where -1 SIMPLE t2 hash idx idx 35 test.t1.a 15 Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL idx #hash#idx 35 test.t1.a 15 Using join buffer (flat, BNLH join) SELECT * FROM t1,t2 WHERE t1.a=t2.a; pk a pk a 20 BBBB 1 Bbbb @@ -4739,7 +4902,7 @@ EXPLAIN SELECT * FROM t1,t2 WHERE t1.a < 3 and t2.a IS NULL; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 Using where; Using index -1 SIMPLE t2 hash idx idx 5 const 23 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_range idx #hash#idx:idx 5:5 const 4 Using index condition; Using MRR; Using join buffer (flat, BNLH join) SELECT * FROM t1,t2 WHERE t1.a < 3 and t2.a IS NULL; a a b 1 NULL 10 @@ -4767,7 +4930,7 @@ EXPLAIN SELECT * FROM t1,t2 WHERE t2.f1 = t1.f1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL f1 NULL NULL NULL 2 Using where -1 SIMPLE t2 hash f1 f1 13 test.t1.f1 12 Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL f1 #hash#f1 13 test.t1.f1 12 Using join buffer (flat, BNLH join) SELECT * FROM t1,t2 WHERE t2.f1 = t1.f1; f1 f2 f1 f2 SET SESSION join_cache_level = DEFAULT; @@ -4787,7 +4950,7 @@ EXPLAIN SELECT a FROM t1,t2 WHERE t2.v = t1.v ; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where -1 SIMPLE t2 hash idx idx 4 test.t1.v 8 Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL idx #hash#idx 4 test.t1.v 8 Using join buffer (flat, BNLH join) SELECT a FROM t1,t2 WHERE t2.v = t1.v ; a 11 diff --git a/mysql-test/r/join_nested_jcl6.result b/mysql-test/r/join_nested_jcl6.result index 3b97a679a3f..78108ccd008 100644 --- a/mysql-test/r/join_nested_jcl6.result +++ b/mysql-test/r/join_nested_jcl6.result @@ -81,7 +81,7 @@ WHERE t3.a=1 OR t3.c IS NULL; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t4 hash NULL hj_key 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) where ((`test`.`t3`.`a` = 1) or isnull(`test`.`t3`.`c`)) SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b @@ -157,7 +157,7 @@ WHERE t3.a>1 OR t3.c IS NULL; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t4 hash NULL hj_key 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b` from `test`.`t2` left join (`test`.`t3` join `test`.`t4` join `test`.`t5`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) where ((`test`.`t3`.`a` > 1) or isnull(`test`.`t3`.`c`)) @@ -187,7 +187,7 @@ WHERE (t3.a>1 OR t3.c IS NULL) AND id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) -1 SIMPLE t4 hash NULL hj_key 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b` from `test`.`t2` left join (`test`.`t3` join `test`.`t4` join `test`.`t5`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) where (((`test`.`t3`.`a` > 1) or isnull(`test`.`t3`.`c`)) and ((`test`.`t5`.`a` < 3) or isnull(`test`.`t5`.`c`))) @@ -237,7 +237,7 @@ ON t7.b=t8.b AND t6.b < 10; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) -1 SIMPLE t8 hash NULL hj_key 5 test.t7.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t8 hash_ALL NULL #hash#$hj 5 test.t7.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) Warnings: Note 1003 select `test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b` from `test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and (`test`.`t8`.`b` = `test`.`t7`.`b`) and (`test`.`t7`.`b` is not null))) where 1 SELECT t6.a,t6.b,t7.a,t7.b,t8.a,t8.b @@ -551,14 +551,14 @@ t0.b=t1.b AND (t2.a >= 4 OR t2.c IS NULL); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t1 hash NULL hj_key 5 test.t0.b 3 100.00 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t1 hash_ALL NULL #hash#$hj 5 test.t0.b 3 100.00 Using where; Using join buffer (flat, BNLH join) 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE t4 hash NULL hj_key 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (incremental, BNL join) -1 SIMPLE t7 hash NULL hj_key 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE t8 hash NULL hj_key 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t8 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) Warnings: Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) where ((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`))) SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, @@ -646,14 +646,14 @@ t0.b=t1.b AND (t9.a=1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t1 hash NULL hj_key 5 test.t0.b 3 100.00 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t1 hash_ALL NULL #hash#$hj 5 test.t0.b 3 100.00 Using where; Using join buffer (flat, BNLH join) 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE t4 hash NULL hj_key 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE t7 hash NULL hj_key 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE t8 hash NULL hj_key 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t8 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) Warnings: Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) @@ -845,7 +845,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE t4 hash NULL hj_key 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t1` join `test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) where (`test`.`t1`.`a` <= 2) CREATE INDEX idx_b ON t2(b); @@ -913,14 +913,14 @@ t0.b=t1.b AND (t9.a=1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t1 hash NULL hj_key 5 test.t0.b 3 100.00 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t1 hash_ALL NULL #hash#$hj 5 test.t0.b 3 100.00 Using where; Using join buffer (flat, BNLH join) 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE t4 hash NULL hj_key 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 1 SIMPLE t5 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE t7 hash NULL hj_key 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE t8 hash NULL hj_key 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t8 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) Warnings: Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) @@ -963,14 +963,14 @@ t0.b=t1.b AND (t9.a=1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t1 hash NULL hj_key 5 test.t0.b 3 100.00 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t1 hash_ALL NULL #hash#$hj 5 test.t0.b 3 100.00 Using where; Using join buffer (flat, BNLH join) 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BKA join) 1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE t7 hash NULL hj_key 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE t8 hash NULL hj_key 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t8 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) Warnings: Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) @@ -1012,12 +1012,12 @@ t0.b=t1.b AND (t9.a=1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t1 hash NULL hj_key 5 test.t0.b 3 100.00 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t1 hash_ALL NULL #hash#$hj 5 test.t0.b 3 100.00 Using where; Using join buffer (flat, BNLH join) 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BKA join) 1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE t7 hash NULL hj_key 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BKA join) 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) @@ -1067,7 +1067,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 2 100.00 Using where; Using join buffer (incremental, BKA join) 1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) -1 SIMPLE t7 hash NULL hj_key 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t7 hash_ALL NULL #hash#$hj 5 test.t5.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE t8 ref idx_b idx_b 5 test.t7.b 2 100.00 Using where; Using join buffer (incremental, BKA join) 1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (incremental, BNL join) @@ -1324,7 +1324,7 @@ c11 c21 c31 EXPLAIN SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 -1 SIMPLE t2 hash NULL hj_key 5 test.t1.c11 0 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL NULL #hash#$hj 5 test.t1.c11 0 Using where; Using join buffer (flat, BNLH join) 1 SIMPLE t3 ALL NULL NULL NULL NULL 0 Using where; Using join buffer (incremental, BNL join) DROP TABLE t1,t2,t3; CREATE TABLE t1 (goods int(12) NOT NULL, price varchar(128) NOT NULL); diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result index 405c059b2af..69de8baa2cf 100644 --- a/mysql-test/r/join_outer_jcl6.result +++ b/mysql-test/r/join_outer_jcl6.result @@ -319,11 +319,11 @@ Lilliana Angelovska NULL NULL explain select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.id is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 -1 SIMPLE t2 hash NULL hj_key 2 test.t1.id 3 Using where; Not exists; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL NULL #hash#$hj 2 test.t1.id 3 Using where; Not exists; Using join buffer (flat, BNLH join) explain select t1.name, t2.name, t2.id from t1 left join t2 on (t1.id = t2.owner) where t2.name is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 -1 SIMPLE t2 hash NULL hj_key 2 test.t1.id 3 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL NULL #hash#$hj 2 test.t1.id 3 Using where; Using join buffer (flat, BNLH join) select count(*) from t1 left join t2 on (t1.id = t2.owner); count(*) 4 @@ -339,11 +339,11 @@ Lilliana Angelovska NULL NULL explain select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.id is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 -1 SIMPLE t2 hash NULL hj_key 2 test.t1.id 3 Using where; Not exists; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL NULL #hash#$hj 2 test.t1.id 3 Using where; Not exists; Using join buffer (flat, BNLH join) explain select t1.name, t2.name, t2.id from t2 right join t1 on (t1.id = t2.owner) where t2.name is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 -1 SIMPLE t2 hash NULL hj_key 2 test.t1.id 3 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL NULL #hash#$hj 2 test.t1.id 3 Using where; Using join buffer (flat, BNLH join) select count(*) from t2 right join t1 on (t1.id = t2.owner); count(*) 4 @@ -695,8 +695,8 @@ a1 a2 b1 b2 c1 c2 explain select * from t1 left join t2 on b1 = a1 left join t3 on c1 = a1 and b1 is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 -1 SIMPLE t2 hash NULL hj_key 4 test.t1.a1 2 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE t3 hash NULL hj_key 5 test.t1.a1 2 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE t2 hash_ALL NULL #hash#$hj 4 test.t1.a1 2 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t3 hash_ALL NULL #hash#$hj 5 test.t1.a1 2 Using where; Using join buffer (incremental, BNLH join) drop table t1, t2, t3; create table t1 ( a int(11), diff --git a/mysql-test/r/select_debug.result b/mysql-test/r/select_debug.result new file mode 100644 index 00000000000..1eb8a0754fa --- /dev/null +++ b/mysql-test/r/select_debug.result @@ -0,0 +1,18 @@ +# +# Bug #725050: print keyuse info when hash join is used +# +create table t1 (a int, b int); +insert into t1 values (2,2), (1,1); +create table t2 (a int); +insert into t2 values (2), (3); +set session join_cache_level=3; +set @@debug = 'd:t:O,/tmp/trace.out'; +explain select t1.b from t1,t2 where t1.b=t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t2 hash_ALL NULL #hash#$hj 5 test.t1.b 2 Using where; Using join buffer (flat, BNLH join) +select t1.b from t1,t2 where t1.b=t2.a; +b +2 +set session join_cache_level=default; +drop table t1,t2; diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result index 3f65ece0536..55da7572a77 100644 --- a/mysql-test/r/select_jcl6.result +++ b/mysql-test/r/select_jcl6.result @@ -1370,7 +1370,7 @@ id select_type table type possible_keys key key_len ref rows Extra explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 -1 SIMPLE t2 hash NULL hj_key 1 test.t4.companynr 1200 Using where; Not exists; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL NULL #hash#$hj 1 test.t4.companynr 1200 Using where; Not exists; Using join buffer (flat, BNLH join) select companynr,companyname from t2 left join t4 using (companynr) where companynr is null; companynr companyname select count(*) from t2 left join t4 using (companynr) where companynr is not null; @@ -1398,39 +1398,39 @@ id select_type table type possible_keys key key_len ref rows Extra explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 hash NULL hj_key 1 test.t4.companynr 1199 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL NULL #hash#$hj 1 test.t4.companynr 1199 Using where; Using join buffer (flat, BNLH join) explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 hash NULL hj_key 1 test.t4.companynr 1199 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL NULL #hash#$hj 1 test.t4.companynr 1199 Using where; Using join buffer (flat, BNLH join) explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 hash NULL hj_key 1 test.t4.companynr 1199 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL NULL #hash#$hj 1 test.t4.companynr 1199 Using where; Using join buffer (flat, BNLH join) explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 -1 SIMPLE t2 hash NULL hj_key 1 test.t4.companynr 1199 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL NULL #hash#$hj 1 test.t4.companynr 1199 Using where; Using join buffer (flat, BNLH join) explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 -1 SIMPLE t2 hash NULL hj_key 1 test.t4.companynr 1199 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL NULL #hash#$hj 1 test.t4.companynr 1199 Using where; Using join buffer (flat, BNLH join) explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 -1 SIMPLE t2 hash NULL hj_key 1 test.t4.companynr 1199 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL NULL #hash#$hj 1 test.t4.companynr 1199 Using where; Using join buffer (flat, BNLH join) explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 hash NULL hj_key 1 test.t4.companynr 1199 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL NULL #hash#$hj 1 test.t4.companynr 1199 Using where; Using join buffer (flat, BNLH join) explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 hash NULL hj_key 1 test.t4.companynr 1199 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL NULL #hash#$hj 1 test.t4.companynr 1199 Using where; Using join buffer (flat, BNLH join) explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t2 hash NULL hj_key 1 test.t4.companynr 1199 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL NULL #hash#$hj 1 test.t4.companynr 1199 Using where; Using join buffer (flat, BNLH join) select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; companynr companynr 37 36 @@ -1438,7 +1438,7 @@ companynr companynr explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t4 index NULL PRIMARY 1 NULL 12 Using index; Using temporary -1 SIMPLE t2 hash NULL hj_key 1 func 1199 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL NULL #hash#$hj 1 func 1199 Using where; Using join buffer (flat, BNLH join) select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008; fld1 companynr fld3 period 038008 37 reporters 1008 @@ -2337,7 +2337,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 system NULL NULL NULL NULL 0 const row not found 1 SIMPLE t4 const id4 NULL NULL NULL 1 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 -1 SIMPLE t2 hash NULL hj_key 4 test.t1.id1 1 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL NULL #hash#$hj 4 test.t1.id1 1 Using where; Using join buffer (flat, BNLH join) select * from t1 left join t2 on id1 = id2 left join t3 on id1 = id3 left join t4 on id3 = id4 where id2 = 1 or id4 = 1; id1 id2 id3 id4 id44 @@ -2917,11 +2917,11 @@ a EXPLAIN SELECT t1.a FROM t1 STRAIGHT_JOIN t2 ON t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where -1 SIMPLE t2 hash NULL hj_key 5 test.t1.a 3 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_ALL NULL #hash#$hj 5 test.t1.a 3 Using where; Using join buffer (flat, BNLH join) EXPLAIN SELECT t1.a FROM t1 INNER JOIN t2 ON t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where -1 SIMPLE t1 hash NULL hj_key 5 test.t2.a 5 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE t1 hash_ALL NULL #hash#$hj 5 test.t2.a 5 Using where; Using join buffer (flat, BNLH join) DROP TABLE t1,t2; select x'10' + 0, X'10' + 0, b'10' + 0, B'10' + 0; x'10' + 0 X'10' + 0 b'10' + 0 B'10' + 0 diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result index 5ae8776a231..b5a78da238d 100644 --- a/mysql-test/r/subselect3_jcl6.result +++ b/mysql-test/r/subselect3_jcl6.result @@ -1036,9 +1036,9 @@ explain select t21.* from t21,t22 where t21.a = t22.a and t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort; Start temporary -1 PRIMARY t12 hash NULL hj_key 4 test.t11.a 8 Using where; Using join buffer (flat, BNLH join) -1 PRIMARY t21 hash NULL hj_key 4 test.t11.a 26 Using where; Using join buffer (incremental, BNLH join) -1 PRIMARY t22 hash NULL hj_key 4 test.t11.a 26 Using where; End temporary; Using join buffer (incremental, BNLH join) +1 PRIMARY t12 hash_ALL NULL #hash#$hj 4 test.t11.a 8 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY t21 hash_ALL NULL #hash#$hj 4 test.t11.a 26 Using where; Using join buffer (incremental, BNLH join) +1 PRIMARY t22 hash_ALL NULL #hash#$hj 4 test.t11.a 26 Using where; End temporary; Using join buffer (incremental, BNLH join) select t21.* from t21,t22 where t21.a = t22.a and t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a; a b c @@ -1052,7 +1052,7 @@ select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) a id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY X ALL NULL NULL NULL NULL 2 2 DEPENDENT SUBQUERY Y ALL NULL NULL NULL NULL 2 Using where; Start temporary -2 DEPENDENT SUBQUERY Z hash NULL hj_key 5 test.Y.a 2 Using where; End temporary; Using join buffer (flat, BNLH join) +2 DEPENDENT SUBQUERY Z hash_ALL NULL #hash#$hj 5 test.Y.a 2 Using where; End temporary; Using join buffer (flat, BNLH join) select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) as subq from t1 X; subq NULL @@ -1155,7 +1155,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY A ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) 1 PRIMARY B ALL NULL NULL NULL NULL 10 Using join buffer (incremental, BNL join) 1 PRIMARY C ALL NULL NULL NULL NULL 10 Using join buffer (incremental, BNL join) -1 PRIMARY D hash NULL hj_key 5 test.E.a 10 Using where; End temporary; Using join buffer (incremental, BNLH join) +1 PRIMARY D hash_ALL NULL #hash#$hj 5 test.E.a 10 Using where; End temporary; Using join buffer (incremental, BNLH join) flush status; select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E); count(*) @@ -1192,11 +1192,11 @@ insert into t3 values (1),(2); explain select * from t2 where a in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Start temporary -1 PRIMARY t1 hash NULL hj_key 4 test.t2.a 4 Using where; End temporary; Using join buffer (flat, BNLH join) +1 PRIMARY t1 hash_ALL NULL #hash#$hj 4 test.t2.a 4 Using where; End temporary; Using join buffer (flat, BNLH join) explain select * from t2 where a in (select a from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Start temporary -1 PRIMARY t2 hash NULL hj_key 5 test.t2.a 2 Using where; End temporary; Using join buffer (flat, BNLH join) +1 PRIMARY t2 hash_ALL NULL #hash#$hj 5 test.t2.a 2 Using where; End temporary; Using join buffer (flat, BNLH join) explain select * from t2 where a in (select a from t3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start temporary @@ -1211,7 +1211,7 @@ insert into t1 values (1),(2); explain select * from t1 where a in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Start temporary -1 PRIMARY t1 hash NULL hj_key 6 test.t1.a 2 Using where; End temporary; Using join buffer (flat, BNLH join) +1 PRIMARY t1 hash_ALL NULL #hash#$hj 6 test.t1.a 2 Using where; End temporary; Using join buffer (flat, BNLH join) drop table t1; set @@optimizer_switch=@save_optimizer_switch; create table t1 (a int); @@ -1282,13 +1282,13 @@ set @@optimizer_switch='firstmatch=off'; explain select * from t1 where (a,b) in (select a,b from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where; Start temporary -1 PRIMARY t2 hash NULL hj_key 10 test.t1.a,test.t1.b 100 Using where; End temporary; Using join buffer (flat, BNLH join) +1 PRIMARY t2 hash_ALL NULL #hash#$hj 10 test.t1.a,test.t1.b 100 Using where; End temporary; Using join buffer (flat, BNLH join) set @save_optimizer_search_depth=@@optimizer_search_depth; set @@optimizer_search_depth=63; explain select * from t1 where (a,b) in (select a,b from t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where; Start temporary -1 PRIMARY t2 hash NULL hj_key 10 test.t1.a,test.t1.b 100 Using where; End temporary; Using join buffer (flat, BNLH join) +1 PRIMARY t2 hash_ALL NULL #hash#$hj 10 test.t1.a,test.t1.b 100 Using where; End temporary; Using join buffer (flat, BNLH join) set @@optimizer_search_depth=@save_optimizer_search_depth; set @@optimizer_switch=@save_optimizer_switch; drop table t0, t1, t2; @@ -1299,7 +1299,7 @@ insert into t1 select * from t0; explain select * from t0 where a in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 ALL NULL NULL NULL NULL 2 Using where; Start temporary -1 PRIMARY t1 hash NULL hj_key 3 test.t0.a 4 Using where; End temporary; Using join buffer (flat, BNLH join) +1 PRIMARY t1 hash_ALL NULL #hash#$hj 3 test.t0.a 4 Using where; End temporary; Using join buffer (flat, BNLH join) select * from t0 where a in (select a from t1); a 10.24 @@ -1312,7 +1312,7 @@ insert into t1 select * from t0; explain select * from t0 where a in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t0 ALL NULL NULL NULL NULL 2 Using where; Start temporary -1 PRIMARY t1 hash NULL hj_key 4 test.t0.a 4 Using where; End temporary; Using join buffer (flat, BNLH join) +1 PRIMARY t1 hash_ALL NULL #hash#$hj 4 test.t0.a 4 Using where; End temporary; Using join buffer (flat, BNLH join) select * from t0 where a in (select a from t1); a 2008-01-01 @@ -1326,9 +1326,9 @@ insert into t2 select * from t2; explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Start temporary -1 PRIMARY X hash NULL hj_key 5 test.t1.a 6 Using where; Using join buffer (flat, BNLH join) -1 PRIMARY Y hash NULL hj_key 5 test.t1.b 6 Using where; Using join buffer (incremental, BNLH join) -1 PRIMARY Z hash NULL hj_key 5 test.t1.c 6 Using where; End temporary; Using join buffer (incremental, BNLH join) +1 PRIMARY X hash_ALL NULL #hash#$hj 5 test.t1.a 6 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY Y hash_ALL NULL #hash#$hj 5 test.t1.b 6 Using where; Using join buffer (incremental, BNLH join) +1 PRIMARY Z hash_ALL NULL #hash#$hj 5 test.t1.c 6 Using where; End temporary; Using join buffer (incremental, BNLH join) drop table t0,t1,t2; BUG#37842: Assertion in DsMrr_impl::dsmrr_init, at handler.cc:4307 diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index fbdfa61af68..672576e643b 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -690,6 +690,24 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY B ALL a,b NULL NULL NULL 1000 Range checked for each record (index map: 0x3) drop table t1, t2; # +# BUG#723822: Crash in get_constant_key_infix with EXISTS ( SELECT .. DISTINCT ) +# +CREATE TABLE t1 ( f1 int(11), f3 varchar(1)) ; +INSERT INTO t1 VALUES ('8','c'),('5','f'); +ALTER TABLE t1 ADD KEY (f3,f1); +CREATE TABLE t2 ( f4 varchar(1)) ; +INSERT INTO t2 VALUES ('f'),('d'); +SELECT * FROM t2 +WHERE EXISTS ( +SELECT DISTINCT f3 +FROM t1 +WHERE f3 <= t2.f4 +); +f4 +f +d +drop table t1,t2; +# # LP BUG#641203 Query returns rows where no result is expected (impossible WHERE) # CREATE TABLE t1 (c1 varchar(1) DEFAULT NULL); diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result index fd765432314..e05d6ebacef 100644 --- a/mysql-test/r/subselect_sj2_jcl6.result +++ b/mysql-test/r/subselect_sj2_jcl6.result @@ -134,7 +134,7 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY it ALL NULL NULL NULL NULL 22 Using where; Start temporary -1 PRIMARY ot hash NULL hj_key 5 test.it.a 32 Using where; End temporary; Using join buffer (flat, BNLH join) +1 PRIMARY ot hash_ALL NULL #hash#$hj 5 test.it.a 32 Using where; End temporary; Using join buffer (flat, BNLH join) select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); @@ -200,7 +200,7 @@ a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY it ALL NULL NULL NULL NULL 22 Using where; Start temporary -1 PRIMARY ot hash NULL hj_key 5 test.it.a 52 Using where; End temporary; Using join buffer (flat, BNLH join) +1 PRIMARY ot hash_ALL NULL #hash#$hj 5 test.it.a 52 Using where; End temporary; Using join buffer (flat, BNLH join) select a, mid(filler1, 1,10), length(filler1)=length(filler2) as Z from t1 ot where a in (select a from t2 it); @@ -579,7 +579,7 @@ explain select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t3)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 -1 PRIMARY t2 hash NULL hj_key 5 test.t1.a 3 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY t2 hash_ALL NULL #hash#$hj 5 test.t1.a 3 Using where; Using join buffer (flat, BNLH join) 2 DEPENDENT SUBQUERY t3 unique_subquery PRIMARY PRIMARY 4 func 1 Using index drop table t0, t1, t2, t3; create table t1 (a int); diff --git a/mysql-test/suite/handler/aria.result b/mysql-test/suite/handler/aria.result index 58ecdaa1f09..efddd7784fd 100644 --- a/mysql-test/suite/handler/aria.result +++ b/mysql-test/suite/handler/aria.result @@ -4,7 +4,7 @@ create table t1 (a int, b char(10), key a (a), key b (a,b)); insert into t1 values (17,"ddd"),(18,"eee"),(19,"fff"),(19,"yyy"), (14,"aaa"),(16,"ccc"),(16,"xxx"), -(20,"ggg"),(21,"hhh"),(22,"iii"); +(20,"ggg"),(21,"hhh"),(22,"iii"),(23,"xxx"),(24,"xxx"),(25,"xxx"); handler t1 open as t2; handler t2 read b first; a b @@ -20,13 +20,13 @@ a b 16 ccc handler t2 read b last; a b -22 iii +25 xxx handler t2 read b prev; a b -21 hhh +24 xxx handler t2 read b prev; a b -20 ggg +23 xxx handler t2 read b first; a b 14 aaa @@ -34,13 +34,13 @@ handler t2 read b prev; a b handler t2 read b last; a b -22 iii +25 xxx handler t2 read b prev; a b -21 hhh +24 xxx handler t2 read b next; a b -22 iii +25 xxx handler t2 read b next; a b handler t2 read a=(15); @@ -105,10 +105,10 @@ handler t2 read a>(54); a b handler t2 read a<=(54); a b -22 iii +25 xxx handler t2 read a<(54); a b -22 iii +25 xxx handler t2 read a=(1); a b handler t2 read a>=(1); @@ -158,9 +158,9 @@ a b 18 eee handler t2 read a last limit 3; a b -22 iii -21 hhh -20 ggg +25 xxx +24 xxx +23 xxx handler t2 read b=(16) limit 1,3; a b 16 xxx @@ -191,10 +191,10 @@ handler t1 close; handler t1 open; handler t1 read a prev; a b -22 iii +25 xxx handler t1 read a prev; a b -21 hhh +24 xxx handler t1 close; handler t1 open as t2; handler t2 read first; @@ -204,53 +204,97 @@ alter table t1 engine = Aria; handler t2 read first; ERROR 42S02: Unknown table 't2' in HANDLER handler t1 open; -handler t1 read a=(16) limit 1,3; +handler t1 read a=(20) limit 1,3; a b -16 xxx flush tables; -handler t1 read a=(16) limit 1,3; +handler t1 read a=(20) limit 1,3; a b -16 xxx +handler t1 close; +handler t1 open; +handler t1 read a=(25); +a b +25 xxx +handler t1 read a next; +a b +handler t1 read a next; +a b +handler t1 read a next; +a b +handler t1 read a prev; +a b +25 xxx +handler t1 read a=(1000); +a b +handler t1 read a next; +a b +handler t1 read a prev; +a b +25 xxx +handler t1 read a=(1000); +a b +handler t1 read a prev; +a b +25 xxx +handler t1 read a=(14); +a b +14 aaa +handler t1 read a prev; +a b +handler t1 read a prev; +a b +handler t1 read a next; +a b +14 aaa +handler t1 read a=(1); +a b +handler t1 read a prev; +a b +handler t1 read a next; +a b +14 aaa +handler t1 read a=(1); +a b +handler t1 read a next; +a b +14 aaa handler t1 close; handler t1 open; prepare stmt from 'handler t1 read a=(?) limit ?,?'; -set @a=16,@b=1,@c=100; +set @a=20,@b=1,@c=100; execute stmt using @a,@b,@c; a b -16 xxx -set @a=16,@b=2,@c=1; +set @a=20,@b=2,@c=1; execute stmt using @a,@b,@c; a b -set @a=16,@b=0,@c=2; +set @a=20,@b=0,@c=2; execute stmt using @a,@b,@c; a b -16 ccc -16 xxx +20 ggg deallocate prepare stmt; prepare stmt from 'handler t1 read a next limit ?'; -handler t1 read a>=(11); +handler t1 read a>=(21); a b -14 aaa +21 hhh set @a=3; execute stmt using @a; a b -16 ccc -16 xxx -17 ddd +22 iii +23 xxx +24 xxx execute stmt using @a; a b -18 eee -19 fff -19 yyy +25 xxx execute stmt using @a; a b -20 ggg -21 hhh -22 iii deallocate prepare stmt; prepare stmt from 'handler t1 read b prev limit ?'; execute stmt using @a; a b +25 xxx +24 xxx +23 xxx +execute stmt using @a; +a b 22 iii 21 hhh 20 ggg @@ -264,9 +308,6 @@ a b 17 ddd 16 xxx 16 ccc -execute stmt using @a; -a b -14 aaa deallocate prepare stmt; prepare stmt from 'handler t1 read b=(?,?)'; set @a=14, @b='aaa'; @@ -288,28 +329,28 @@ a b 16 ccc 16 xxx deallocate prepare stmt; -prepare stmt from 'handler t1 read a>=(?) where a < ? limit 5'; -set @a=15, @b=20; +prepare stmt from 'handler t1 read b>=(?) where a < ? limit 5'; +set @a=17, @b=24; execute stmt using @a,@b; a b -16 ccc -16 xxx 17 ddd 18 eee 19 fff +19 yyy +20 ggg execute stmt using @a,@b; a b -16 ccc -16 xxx 17 ddd 18 eee 19 fff +19 yyy +20 ggg deallocate prepare stmt; prepare stmt from 'handler t1 read a=(?)'; -set @a=16; +set @a=17; execute stmt using @a; a b -16 ccc +17 ddd alter table t1 add c int; execute stmt using @a; ERROR 42S02: Unknown table 't1' in HANDLER @@ -319,7 +360,7 @@ ERROR 42S02: Unknown table 't1' in HANDLER handler t1 open; prepare stmt from 'handler t1 read a=(?)'; flush tables; -set @a=16; +set @a=17; execute stmt using @a; ERROR HY000: Prepared statement needs to be re-prepared deallocate prepare stmt; diff --git a/mysql-test/suite/handler/handler.inc b/mysql-test/suite/handler/handler.inc index d0ae6164079..5c87652f2b5 100644 --- a/mysql-test/suite/handler/handler.inc +++ b/mysql-test/suite/handler/handler.inc @@ -120,9 +120,37 @@ eval alter table t1 engine = $engine_type; handler t2 read first; handler t1 open; -handler t1 read a=(16) limit 1,3; +handler t1 read a=(20) limit 1,3; flush tables; -handler t1 read a=(16) limit 1,3; +handler t1 read a=(20) limit 1,3; +handler t1 close; + +# +# Search after end and before start of index +# + +handler t1 open; +handler t1 read a=(25); +handler t1 read a next; +handler t1 read a next; +handler t1 read a next; +handler t1 read a prev; +handler t1 read a=(1000); +handler t1 read a next; +handler t1 read a prev; +handler t1 read a=(1000); +handler t1 read a prev; + +handler t1 read a=(14); +handler t1 read a prev; +handler t1 read a prev; +handler t1 read a next; +handler t1 read a=(1); +handler t1 read a prev; +handler t1 read a next; +handler t1 read a=(1); +handler t1 read a next; + handler t1 close; # @@ -131,16 +159,16 @@ handler t1 close; handler t1 open; prepare stmt from 'handler t1 read a=(?) limit ?,?'; -set @a=16,@b=1,@c=100; +set @a=20,@b=1,@c=100; execute stmt using @a,@b,@c; -set @a=16,@b=2,@c=1; +set @a=20,@b=2,@c=1; execute stmt using @a,@b,@c; -set @a=16,@b=0,@c=2; +set @a=20,@b=0,@c=2; execute stmt using @a,@b,@c; deallocate prepare stmt; prepare stmt from 'handler t1 read a next limit ?'; -handler t1 read a>=(11); +handler t1 read a>=(21); set @a=3; execute stmt using @a; execute stmt using @a; @@ -167,14 +195,14 @@ execute stmt using @a; execute stmt using @a; deallocate prepare stmt; -prepare stmt from 'handler t1 read a>=(?) where a < ? limit 5'; -set @a=15, @b=20; +prepare stmt from 'handler t1 read b>=(?) where a < ? limit 5'; +set @a=17, @b=24; execute stmt using @a,@b; execute stmt using @a,@b; deallocate prepare stmt; prepare stmt from 'handler t1 read a=(?)'; -set @a=16; +set @a=17; execute stmt using @a; alter table t1 add c int; --error 1109 @@ -186,7 +214,7 @@ handler t1 close; handler t1 open; prepare stmt from 'handler t1 read a=(?)'; flush tables; -set @a=16; +set @a=17; --error ER_NEED_REPREPARE execute stmt using @a; deallocate prepare stmt; diff --git a/mysql-test/suite/handler/heap.result b/mysql-test/suite/handler/heap.result index 07050042447..04bc9c73159 100644 --- a/mysql-test/suite/handler/heap.result +++ b/mysql-test/suite/handler/heap.result @@ -4,7 +4,7 @@ create table t1 (a int, b char(10), key a using btree (a), key b using btree (a, insert into t1 values (17,"ddd"),(18,"eee"),(19,"fff"),(19,"yyy"), (14,"aaa"),(16,"ccc"),(16,"xxx"), -(20,"ggg"),(21,"hhh"),(22,"iii"); +(20,"ggg"),(21,"hhh"),(22,"iii"),(23,"xxx"),(24,"xxx"),(25,"xxx"); handler t1 open as t2; handler t2 read b first; a b @@ -20,13 +20,13 @@ a b 16 ccc handler t2 read b last; a b -22 iii +25 xxx handler t2 read b prev; a b -21 hhh +24 xxx handler t2 read b prev; a b -20 ggg +23 xxx handler t2 read b first; a b 14 aaa @@ -34,13 +34,13 @@ handler t2 read b prev; a b handler t2 read b last; a b -22 iii +25 xxx handler t2 read b prev; a b -21 hhh +24 xxx handler t2 read b next; a b -22 iii +25 xxx handler t2 read b next; a b handler t2 read a=(15); @@ -105,10 +105,10 @@ handler t2 read a>(54); a b handler t2 read a<=(54); a b -22 iii +25 xxx handler t2 read a<(54); a b -22 iii +25 xxx handler t2 read a=(1); a b handler t2 read a>=(1); @@ -158,9 +158,9 @@ a b 18 eee handler t2 read a last limit 3; a b -22 iii -21 hhh -20 ggg +25 xxx +24 xxx +23 xxx handler t2 read b=(16) limit 1,3; a b 16 xxx @@ -191,10 +191,10 @@ handler t1 close; handler t1 open; handler t1 read a prev; a b -22 iii +25 xxx handler t1 read a prev; a b -21 hhh +24 xxx handler t1 close; handler t1 open as t2; handler t2 read first; @@ -204,53 +204,97 @@ alter table t1 engine = MEMORY; handler t2 read first; ERROR 42S02: Unknown table 't2' in HANDLER handler t1 open; -handler t1 read a=(16) limit 1,3; +handler t1 read a=(20) limit 1,3; a b -16 ccc flush tables; -handler t1 read a=(16) limit 1,3; +handler t1 read a=(20) limit 1,3; a b -16 ccc +handler t1 close; +handler t1 open; +handler t1 read a=(25); +a b +25 xxx +handler t1 read a next; +a b +handler t1 read a next; +a b +handler t1 read a next; +a b +handler t1 read a prev; +a b +25 xxx +handler t1 read a=(1000); +a b +handler t1 read a next; +a b +handler t1 read a prev; +a b +25 xxx +handler t1 read a=(1000); +a b +handler t1 read a prev; +a b +25 xxx +handler t1 read a=(14); +a b +14 aaa +handler t1 read a prev; +a b +handler t1 read a prev; +a b +handler t1 read a next; +a b +14 aaa +handler t1 read a=(1); +a b +handler t1 read a prev; +a b +handler t1 read a next; +a b +14 aaa +handler t1 read a=(1); +a b +handler t1 read a next; +a b +14 aaa handler t1 close; handler t1 open; prepare stmt from 'handler t1 read a=(?) limit ?,?'; -set @a=16,@b=1,@c=100; +set @a=20,@b=1,@c=100; execute stmt using @a,@b,@c; a b -16 ccc -set @a=16,@b=2,@c=1; +set @a=20,@b=2,@c=1; execute stmt using @a,@b,@c; a b -set @a=16,@b=0,@c=2; +set @a=20,@b=0,@c=2; execute stmt using @a,@b,@c; a b -16 xxx -16 ccc +20 ggg deallocate prepare stmt; prepare stmt from 'handler t1 read a next limit ?'; -handler t1 read a>=(11); +handler t1 read a>=(21); a b -14 aaa +21 hhh set @a=3; execute stmt using @a; a b -16 xxx -16 ccc -17 ddd +22 iii +23 xxx +24 xxx execute stmt using @a; a b -18 eee -19 fff -19 yyy +25 xxx execute stmt using @a; a b -20 ggg -21 hhh -22 iii deallocate prepare stmt; prepare stmt from 'handler t1 read b prev limit ?'; execute stmt using @a; a b +25 xxx +24 xxx +23 xxx +execute stmt using @a; +a b 22 iii 21 hhh 20 ggg @@ -264,9 +308,6 @@ a b 17 ddd 16 xxx 16 ccc -execute stmt using @a; -a b -14 aaa deallocate prepare stmt; prepare stmt from 'handler t1 read b=(?,?)'; set @a=14, @b='aaa'; @@ -288,28 +329,28 @@ a b 16 ccc 16 xxx deallocate prepare stmt; -prepare stmt from 'handler t1 read a>=(?) where a < ? limit 5'; -set @a=15, @b=20; +prepare stmt from 'handler t1 read b>=(?) where a < ? limit 5'; +set @a=17, @b=24; execute stmt using @a,@b; a b -16 xxx -16 ccc 17 ddd 18 eee 19 fff +19 yyy +20 ggg execute stmt using @a,@b; a b -16 xxx -16 ccc 17 ddd 18 eee 19 fff +19 yyy +20 ggg deallocate prepare stmt; prepare stmt from 'handler t1 read a=(?)'; -set @a=16; +set @a=17; execute stmt using @a; a b -16 xxx +17 ddd alter table t1 add c int; execute stmt using @a; ERROR 42S02: Unknown table 't1' in HANDLER @@ -319,7 +360,7 @@ ERROR 42S02: Unknown table 't1' in HANDLER handler t1 open; prepare stmt from 'handler t1 read a=(?)'; flush tables; -set @a=16; +set @a=17; execute stmt using @a; ERROR HY000: Prepared statement needs to be re-prepared deallocate prepare stmt; diff --git a/mysql-test/suite/handler/init.inc b/mysql-test/suite/handler/init.inc index 32c6010f95b..3e63f14a103 100644 --- a/mysql-test/suite/handler/init.inc +++ b/mysql-test/suite/handler/init.inc @@ -30,4 +30,4 @@ eval create table t1 (a int, b char(10), key a $key_type (a), key b $key_type (a insert into t1 values (17,"ddd"),(18,"eee"),(19,"fff"),(19,"yyy"), (14,"aaa"),(16,"ccc"),(16,"xxx"), -(20,"ggg"),(21,"hhh"),(22,"iii"); +(20,"ggg"),(21,"hhh"),(22,"iii"),(23,"xxx"),(24,"xxx"),(25,"xxx"); diff --git a/mysql-test/suite/handler/innodb.result b/mysql-test/suite/handler/innodb.result index 113d5d7e61f..3bf3a1f2c70 100644 --- a/mysql-test/suite/handler/innodb.result +++ b/mysql-test/suite/handler/innodb.result @@ -4,7 +4,7 @@ create table t1 (a int, b char(10), key a (a), key b (a,b)); insert into t1 values (17,"ddd"),(18,"eee"),(19,"fff"),(19,"yyy"), (14,"aaa"),(16,"ccc"),(16,"xxx"), -(20,"ggg"),(21,"hhh"),(22,"iii"); +(20,"ggg"),(21,"hhh"),(22,"iii"),(23,"xxx"),(24,"xxx"),(25,"xxx"); handler t1 open as t2; handler t2 read b first; a b @@ -20,13 +20,13 @@ a b 16 ccc handler t2 read b last; a b -22 iii +25 xxx handler t2 read b prev; a b -21 hhh +24 xxx handler t2 read b prev; a b -20 ggg +23 xxx handler t2 read b first; a b 14 aaa @@ -34,13 +34,13 @@ handler t2 read b prev; a b handler t2 read b last; a b -22 iii +25 xxx handler t2 read b prev; a b -21 hhh +24 xxx handler t2 read b next; a b -22 iii +25 xxx handler t2 read b next; a b handler t2 read a=(15); @@ -105,10 +105,10 @@ handler t2 read a>(54); a b handler t2 read a<=(54); a b -22 iii +25 xxx handler t2 read a<(54); a b -22 iii +25 xxx handler t2 read a=(1); a b handler t2 read a>=(1); @@ -158,9 +158,9 @@ a b 18 eee handler t2 read a last limit 3; a b -22 iii -21 hhh -20 ggg +25 xxx +24 xxx +23 xxx handler t2 read b=(16) limit 1,3; a b 16 xxx @@ -191,10 +191,10 @@ handler t1 close; handler t1 open; handler t1 read a prev; a b -22 iii +25 xxx handler t1 read a prev; a b -21 hhh +24 xxx handler t1 close; handler t1 open as t2; handler t2 read first; @@ -204,53 +204,97 @@ alter table t1 engine = InnoDB; handler t2 read first; ERROR 42S02: Unknown table 't2' in HANDLER handler t1 open; -handler t1 read a=(16) limit 1,3; +handler t1 read a=(20) limit 1,3; a b -16 xxx flush tables; -handler t1 read a=(16) limit 1,3; +handler t1 read a=(20) limit 1,3; a b -16 xxx +handler t1 close; +handler t1 open; +handler t1 read a=(25); +a b +25 xxx +handler t1 read a next; +a b +handler t1 read a next; +a b +handler t1 read a next; +a b +handler t1 read a prev; +a b +25 xxx +handler t1 read a=(1000); +a b +handler t1 read a next; +a b +handler t1 read a prev; +a b +25 xxx +handler t1 read a=(1000); +a b +handler t1 read a prev; +a b +25 xxx +handler t1 read a=(14); +a b +14 aaa +handler t1 read a prev; +a b +handler t1 read a prev; +a b +handler t1 read a next; +a b +14 aaa +handler t1 read a=(1); +a b +handler t1 read a prev; +a b +handler t1 read a next; +a b +14 aaa +handler t1 read a=(1); +a b +handler t1 read a next; +a b +16 ccc handler t1 close; handler t1 open; prepare stmt from 'handler t1 read a=(?) limit ?,?'; -set @a=16,@b=1,@c=100; +set @a=20,@b=1,@c=100; execute stmt using @a,@b,@c; a b -16 xxx -set @a=16,@b=2,@c=1; +set @a=20,@b=2,@c=1; execute stmt using @a,@b,@c; a b -set @a=16,@b=0,@c=2; +set @a=20,@b=0,@c=2; execute stmt using @a,@b,@c; a b -16 ccc -16 xxx +20 ggg deallocate prepare stmt; prepare stmt from 'handler t1 read a next limit ?'; -handler t1 read a>=(11); +handler t1 read a>=(21); a b -14 aaa +21 hhh set @a=3; execute stmt using @a; a b -16 ccc -16 xxx -17 ddd +22 iii +23 xxx +24 xxx execute stmt using @a; a b -18 eee -19 fff -19 yyy +25 xxx execute stmt using @a; a b -20 ggg -21 hhh -22 iii deallocate prepare stmt; prepare stmt from 'handler t1 read b prev limit ?'; execute stmt using @a; a b +25 xxx +24 xxx +23 xxx +execute stmt using @a; +a b 22 iii 21 hhh 20 ggg @@ -264,9 +308,6 @@ a b 17 ddd 16 xxx 16 ccc -execute stmt using @a; -a b -14 aaa deallocate prepare stmt; prepare stmt from 'handler t1 read b=(?,?)'; set @a=14, @b='aaa'; @@ -288,28 +329,28 @@ a b 16 ccc 16 xxx deallocate prepare stmt; -prepare stmt from 'handler t1 read a>=(?) where a < ? limit 5'; -set @a=15, @b=20; +prepare stmt from 'handler t1 read b>=(?) where a < ? limit 5'; +set @a=17, @b=24; execute stmt using @a,@b; a b -16 ccc -16 xxx 17 ddd 18 eee 19 fff +19 yyy +20 ggg execute stmt using @a,@b; a b -16 ccc -16 xxx 17 ddd 18 eee 19 fff +19 yyy +20 ggg deallocate prepare stmt; prepare stmt from 'handler t1 read a=(?)'; -set @a=16; +set @a=17; execute stmt using @a; a b -16 ccc +17 ddd alter table t1 add c int; execute stmt using @a; ERROR 42S02: Unknown table 't1' in HANDLER @@ -319,7 +360,7 @@ ERROR 42S02: Unknown table 't1' in HANDLER handler t1 open; prepare stmt from 'handler t1 read a=(?)'; flush tables; -set @a=16; +set @a=17; execute stmt using @a; ERROR HY000: Prepared statement needs to be re-prepared deallocate prepare stmt; diff --git a/mysql-test/suite/handler/interface.result b/mysql-test/suite/handler/interface.result index 9949097c65a..04fcb1b9002 100644 --- a/mysql-test/suite/handler/interface.result +++ b/mysql-test/suite/handler/interface.result @@ -6,7 +6,7 @@ create table t1 (a int, b char(10), key a (a), key b (a,b)); insert into t1 values (17,"ddd"),(18,"eee"),(19,"fff"),(19,"yyy"), (14,"aaa"),(16,"ccc"),(16,"xxx"), -(20,"ggg"),(21,"hhh"),(22,"iii"); +(20,"ggg"),(21,"hhh"),(22,"iii"),(23,"xxx"),(24,"xxx"),(25,"xxx"); handler t1 open; handler t1 read a=(SELECT 1); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT 1)' at line 1 diff --git a/mysql-test/suite/handler/myisam.result b/mysql-test/suite/handler/myisam.result index fd333015bbe..321811d7339 100644 --- a/mysql-test/suite/handler/myisam.result +++ b/mysql-test/suite/handler/myisam.result @@ -4,7 +4,7 @@ create table t1 (a int, b char(10), key a (a), key b (a,b)); insert into t1 values (17,"ddd"),(18,"eee"),(19,"fff"),(19,"yyy"), (14,"aaa"),(16,"ccc"),(16,"xxx"), -(20,"ggg"),(21,"hhh"),(22,"iii"); +(20,"ggg"),(21,"hhh"),(22,"iii"),(23,"xxx"),(24,"xxx"),(25,"xxx"); handler t1 open as t2; handler t2 read b first; a b @@ -20,13 +20,13 @@ a b 16 ccc handler t2 read b last; a b -22 iii +25 xxx handler t2 read b prev; a b -21 hhh +24 xxx handler t2 read b prev; a b -20 ggg +23 xxx handler t2 read b first; a b 14 aaa @@ -34,13 +34,13 @@ handler t2 read b prev; a b handler t2 read b last; a b -22 iii +25 xxx handler t2 read b prev; a b -21 hhh +24 xxx handler t2 read b next; a b -22 iii +25 xxx handler t2 read b next; a b handler t2 read a=(15); @@ -105,10 +105,10 @@ handler t2 read a>(54); a b handler t2 read a<=(54); a b -22 iii +25 xxx handler t2 read a<(54); a b -22 iii +25 xxx handler t2 read a=(1); a b handler t2 read a>=(1); @@ -158,9 +158,9 @@ a b 18 eee handler t2 read a last limit 3; a b -22 iii -21 hhh -20 ggg +25 xxx +24 xxx +23 xxx handler t2 read b=(16) limit 1,3; a b 16 xxx @@ -191,10 +191,10 @@ handler t1 close; handler t1 open; handler t1 read a prev; a b -22 iii +25 xxx handler t1 read a prev; a b -21 hhh +24 xxx handler t1 close; handler t1 open as t2; handler t2 read first; @@ -204,53 +204,97 @@ alter table t1 engine = MyISAM; handler t2 read first; ERROR 42S02: Unknown table 't2' in HANDLER handler t1 open; -handler t1 read a=(16) limit 1,3; +handler t1 read a=(20) limit 1,3; a b -16 xxx flush tables; -handler t1 read a=(16) limit 1,3; +handler t1 read a=(20) limit 1,3; a b -16 xxx +handler t1 close; +handler t1 open; +handler t1 read a=(25); +a b +25 xxx +handler t1 read a next; +a b +handler t1 read a next; +a b +handler t1 read a next; +a b +handler t1 read a prev; +a b +25 xxx +handler t1 read a=(1000); +a b +handler t1 read a next; +a b +handler t1 read a prev; +a b +25 xxx +handler t1 read a=(1000); +a b +handler t1 read a prev; +a b +25 xxx +handler t1 read a=(14); +a b +14 aaa +handler t1 read a prev; +a b +handler t1 read a prev; +a b +handler t1 read a next; +a b +14 aaa +handler t1 read a=(1); +a b +handler t1 read a prev; +a b +handler t1 read a next; +a b +14 aaa +handler t1 read a=(1); +a b +handler t1 read a next; +a b +14 aaa handler t1 close; handler t1 open; prepare stmt from 'handler t1 read a=(?) limit ?,?'; -set @a=16,@b=1,@c=100; +set @a=20,@b=1,@c=100; execute stmt using @a,@b,@c; a b -16 xxx -set @a=16,@b=2,@c=1; +set @a=20,@b=2,@c=1; execute stmt using @a,@b,@c; a b -set @a=16,@b=0,@c=2; +set @a=20,@b=0,@c=2; execute stmt using @a,@b,@c; a b -16 ccc -16 xxx +20 ggg deallocate prepare stmt; prepare stmt from 'handler t1 read a next limit ?'; -handler t1 read a>=(11); +handler t1 read a>=(21); a b -14 aaa +21 hhh set @a=3; execute stmt using @a; a b -16 ccc -16 xxx -17 ddd +22 iii +23 xxx +24 xxx execute stmt using @a; a b -18 eee -19 fff -19 yyy +25 xxx execute stmt using @a; a b -20 ggg -21 hhh -22 iii deallocate prepare stmt; prepare stmt from 'handler t1 read b prev limit ?'; execute stmt using @a; a b +25 xxx +24 xxx +23 xxx +execute stmt using @a; +a b 22 iii 21 hhh 20 ggg @@ -264,9 +308,6 @@ a b 17 ddd 16 xxx 16 ccc -execute stmt using @a; -a b -14 aaa deallocate prepare stmt; prepare stmt from 'handler t1 read b=(?,?)'; set @a=14, @b='aaa'; @@ -288,28 +329,28 @@ a b 16 ccc 16 xxx deallocate prepare stmt; -prepare stmt from 'handler t1 read a>=(?) where a < ? limit 5'; -set @a=15, @b=20; +prepare stmt from 'handler t1 read b>=(?) where a < ? limit 5'; +set @a=17, @b=24; execute stmt using @a,@b; a b -16 ccc -16 xxx 17 ddd 18 eee 19 fff +19 yyy +20 ggg execute stmt using @a,@b; a b -16 ccc -16 xxx 17 ddd 18 eee 19 fff +19 yyy +20 ggg deallocate prepare stmt; prepare stmt from 'handler t1 read a=(?)'; -set @a=16; +set @a=17; execute stmt using @a; a b -16 ccc +17 ddd alter table t1 add c int; execute stmt using @a; ERROR 42S02: Unknown table 't1' in HANDLER @@ -319,7 +360,7 @@ ERROR 42S02: Unknown table 't1' in HANDLER handler t1 open; prepare stmt from 'handler t1 read a=(?)'; flush tables; -set @a=16; +set @a=17; execute stmt using @a; ERROR HY000: Prepared statement needs to be re-prepared deallocate prepare stmt; diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result index fd9f794a8c0..193dff3c742 100644 --- a/mysql-test/suite/innodb/r/innodb_mysql.result +++ b/mysql-test/suite/innodb/r/innodb_mysql.result @@ -2664,7 +2664,7 @@ SELECT t1.pk FROM t1,t2 WHERE t1.pk = t2.pk AND t2.pk <> 8; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 11 Using where; Using index -1 SIMPLE t2 hash PRIMARY PRIMARY 4 test.t1.pk 13 Using index; Using join buffer (flat, BNLH join) +1 SIMPLE t2 hash_range PRIMARY #hash#PRIMARY:PRIMARY 4:4 test.t1.pk 7 Using where; Using index; Using join buffer (flat, BNLH join) SELECT t1.pk FROM t1,t2 WHERE t1.pk = t2.pk AND t2.pk <> 8; pk diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index cf431ace24c..29b9dcf4734 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -941,3 +941,26 @@ SHOW STATUS LIKE 'Handler_read_%'; DROP TABLE t1, t2; --echo End of 5.1 tests + +--echo # +--echo # BUG#724275: Crash in JOIN::optimize in maria-5.3 +--echo # + +create table t1 (a int); +insert into t1 values (1),(2); +insert into t1 select * from t1; + +create table t2 (a int, b int, key(a,b)); +insert into t2 values (1,1),(1,2),(1,3),(1,4),(2,5),(2,6),(2,7),(2,8),(2,9); +insert into t2 select * from t2; +insert into t2 select * from t2; +insert into t2 select * from t2; + +create table t3 (a int, b int, key(a)); +insert into t3 values (1,1),(2,2); +select * from + t3 straight_join t1 straight_join t2 force index(a) +where t2.a=1 and t2.b=t1.a and t1.a=t3.b and t3.a=1; + +drop table t1,t2,t3; + diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test index 5c6f9950c0b..4bde588cb7d 100644 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -146,6 +146,50 @@ SELECT City.Name, Country.Name, CountryLanguage.Language 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); + +ANALYZE TABLE City; + +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=default; set join_buffer_size=256; @@ -394,6 +438,35 @@ SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.P 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'; diff --git a/mysql-test/t/select_debug.test b/mysql-test/t/select_debug.test new file mode 100644 index 00000000000..16e8425efc4 --- /dev/null +++ b/mysql-test/t/select_debug.test @@ -0,0 +1,19 @@ +--source include/have_debug.inc + +--echo # +--echo # Bug #725050: print keyuse info when hash join is used +--echo # + +create table t1 (a int, b int); +insert into t1 values (2,2), (1,1); +create table t2 (a int); +insert into t2 values (2), (3); + +set session join_cache_level=3; +set @@debug = 'd:t:O,/tmp/trace.out'; + +explain select t1.b from t1,t2 where t1.b=t2.a; +select t1.b from t1,t2 where t1.b=t2.a; + +set session join_cache_level=default; +drop table t1,t2; diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index 77f7a1c2b9b..8801e6a2fbc 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -619,6 +619,27 @@ select a, from t1 A; drop table t1, t2; + +--echo # +--echo # BUG#723822: Crash in get_constant_key_infix with EXISTS ( SELECT .. DISTINCT ) +--echo # +CREATE TABLE t1 ( f1 int(11), f3 varchar(1)) ; +INSERT INTO t1 VALUES ('8','c'),('5','f'); + +ALTER TABLE t1 ADD KEY (f3,f1); + +CREATE TABLE t2 ( f4 varchar(1)) ; +INSERT INTO t2 VALUES ('f'),('d'); + +SELECT * FROM t2 +WHERE EXISTS ( + SELECT DISTINCT f3 + FROM t1 + WHERE f3 <= t2.f4 +); + +drop table t1,t2; + --echo # --echo # LP BUG#641203 Query returns rows where no result is expected (impossible WHERE) --echo # diff --git a/mysql-test/t/variables-big.test b/mysql-test/t/variables-big.test index 67a8d9fe86d..2df0222c9d5 100644 --- a/mysql-test/t/variables-big.test +++ b/mysql-test/t/variables-big.test @@ -40,18 +40,22 @@ SET SESSION transaction_prealloc_size=1024*1024*1024*1; --replace_column 1 <Id> 6 <Time> # Embedded server is hardcoded to show "Writing to net" as STATE. --replace_result "Writing to net" "NULL" +--replace_regex /localhost[:0-9]*/localhost/ SHOW PROCESSLIST; SET SESSION transaction_prealloc_size=1024*1024*1024*2; --replace_column 1 <Id> 6 <Time> --replace_result "Writing to net" "NULL" +--replace_regex /localhost[:0-9]*/localhost/ SHOW PROCESSLIST; SET SESSION transaction_prealloc_size=1024*1024*1024*3; --replace_column 1 <Id> 6 <Time> --replace_result "Writing to net" "NULL" +--replace_regex /localhost[:0-9]*/localhost/ SHOW PROCESSLIST; SET SESSION transaction_prealloc_size=1024*1024*1024*4; --replace_column 1 <Id> 6 <Time> --replace_result "Writing to net" "NULL" +--replace_regex /localhost[:0-9]*/localhost/ SHOW PROCESSLIST; SET SESSION transaction_prealloc_size=1024*1024*1024*5; --replace_column 1 <Id> 6 <Time> diff --git a/mysql-test/valgrind.supp b/mysql-test/valgrind.supp index 05be9a489a1..5f37fc1dc9e 100644 --- a/mysql-test/valgrind.supp +++ b/mysql-test/valgrind.supp @@ -558,7 +558,6 @@ obj:/lib*/ld-*.so) obj:/lib*/libdl-*.so) fun:dlsym - fun:plugin_dl_add } # diff --git a/plugin/handler_socket/handlersocket/Makefile.am b/plugin/handler_socket/handlersocket/Makefile.am index f8e695b67f7..e00b7bc4ea4 100644 --- a/plugin/handler_socket/handlersocket/Makefile.am +++ b/plugin/handler_socket/handlersocket/Makefile.am @@ -1,11 +1,7 @@ -CXXFLAGS += -fimplicit-templates pkgplugindir = $(PLUGIN_DIR) noinst_HEADERS = database.hpp hstcpsvr.hpp hstcpsvr_worker.hpp mysql_incl.hpp pkgplugin_LTLIBRARIES = handlersocket.la handlersocket_la_LDFLAGS = -module ../libhsclient/libhsclient.la -handlersocket_la_CFLAGS = $(MYSQL_INC) $(MYSQL_CFLAGS) $(AM_CFLAGS) \ - -I$(srcdir)/../libhsclient -handlersocket_la_CXXFLAGS = $(MYSQL_INC) $(MYSQL_CFLAGS) $(AM_CFLAGS) \ - -I$(srcdir)/../libhsclient +handlersocket_la_CXXFLAGS = $(MYSQL_INC) $(MYSQL_CFLAGS) $(AM_CXXFLAGS) -fimplicit-templates -I$(srcdir)/../libhsclient handlersocket_la_SOURCES = database.cpp handlersocket.cpp \ hstcpsvr_worker.cpp hstcpsvr.cpp diff --git a/sql/filesort.cc b/sql/filesort.cc index 65e4116479c..2b39e74b8cb 100644 --- a/sql/filesort.cc +++ b/sql/filesort.cc @@ -1239,7 +1239,7 @@ int merge_buffers(SORTPARAM *param, IO_CACHE *from_file, QUEUE queue; qsort2_cmp cmp; void *first_cmp_arg; - element_count dupl_count; + element_count dupl_count= 0; uchar *src; THD::killed_state not_killable; uchar *unique_buff= param->unique_buff; diff --git a/sql/log.cc b/sql/log.cc index 7d837938ab4..e8858f0e3c4 100644 --- a/sql/log.cc +++ b/sql/log.cc @@ -1899,6 +1899,7 @@ static int find_uniq_filename(char *name) size_t buf_length, length; char *start, *end; DBUG_ENTER("find_uniq_filename"); + LINT_INIT(number); length= dirname_part(buff, name, &buf_length); start= name + length; diff --git a/sql/mysqld.cc b/sql/mysqld.cc index 7580f64417a..a73bae5c35e 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -7415,7 +7415,8 @@ thread is in the relay logs.", "index_condition_pushdown, firstmatch, loosescan, materialization, in_to_exists, " "semijoin, partial_match_rowid_merge, partial_match_table_scan, " "subquery_cache, outer_join_with_cache, semijoin_with_cache, " - "join_cache_incremental, join_cache_hashed, join_cache_bka" + "join_cache_incremental, join_cache_hashed, join_cache_bka, " + "optimize_join_buffer_size" #ifndef DBUG_OFF ", table_elimination" #endif diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 74ff9846383..d0973270da0 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -11867,13 +11867,14 @@ get_constant_key_infix(KEY *index_info, SEL_ARG *index_range_tree, Find the range tree for the current keypart. We assume that index_range_tree points to the leftmost keypart in the index. */ - for (cur_range= index_range_tree; cur_range; + for (cur_range= index_range_tree; + cur_range && cur_range->type == SEL_ARG::KEY_RANGE; cur_range= cur_range->next_key_part) { if (cur_range->field->eq(cur_part->field)) break; } - if (!cur_range) + if (!cur_range || cur_range->type != SEL_ARG::KEY_RANGE) { if (min_max_arg_part) return FALSE; /* The current keypart has no range predicates at all. */ diff --git a/sql/opt_range_mrr.cc b/sql/opt_range_mrr.cc index 2047f6f250c..da6086d6cdc 100644 --- a/sql/opt_range_mrr.cc +++ b/sql/opt_range_mrr.cc @@ -225,7 +225,7 @@ walk_up_n_right: RANGE_SEQ_ENTRY *cur= &seq->stack[seq->i]; uint min_key_length= cur->min_key - seq->param->min_key; - range->ptr= (char*)(int)(key_tree->part); + range->ptr= (char*)(intptr)(key_tree->part); if (cur->min_key_flag & GEOM_FLAG) { range->range_flag= cur->min_key_flag; diff --git a/sql/sql_join_cache.cc b/sql/sql_join_cache.cc index be3def865a8..d04f5eddfa8 100644 --- a/sql/sql_join_cache.cc +++ b/sql/sql_join_cache.cc @@ -3871,7 +3871,8 @@ bool bka_skip_index_tuple(range_seq_t rseq, char *range_info) { DBUG_ENTER("bka_skip_index_tuple"); JOIN_CACHE_BKA *cache= (JOIN_CACHE_BKA *) rseq; - DBUG_RETURN(cache->skip_index_tuple(range_info)); + bool res= cache->skip_index_tuple(range_info); + DBUG_RETURN(res); } diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 91b760f6ad2..56950ea16af 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -42,8 +42,8 @@ const char *join_type_str[]={ "UNKNOWN","system","const","eq_ref","ref", "MAYBE_REF","ALL","range","index","fulltext", "ref_or_null","unique_subquery","index_subquery", - "index_merge","hash" -}; + "index_merge", "hash_ALL", "hash_range", + "hash_index", "hash_index_merge" }; const char *copy_to_tmp_table= "Copying to tmp table"; @@ -1054,6 +1054,7 @@ JOIN::optimize() */ for (JOIN_TAB *tab= join_tab + const_tables; tab < join_tab + tables; tab++) { + uint key_copy_index=0; for (uint i=0; i < tab->ref.key_parts; i++) { @@ -1069,13 +1070,14 @@ JOIN::optimize() { *ref_item_ptr= ref_item; Item *item= ref_item->real_item(); - store_key *key_copy= tab->ref.key_copy[i]; + store_key *key_copy= tab->ref.key_copy[key_copy_index]; if (key_copy->type() == store_key::FIELD_STORE_KEY) { store_key_field *field_copy= ((store_key_field *)key_copy); field_copy->change_source_field((Item_field *) item); } } + key_copy_index++; } } @@ -5074,19 +5076,20 @@ best_access_path(JOIN *join, /* Estimate the cost of the hash join access to the table */ ha_rows rnd_records= matching_candidates_in_table(s, found_constraint); - tmp= s->table->file->scan_time(); + tmp= s->quick ? s->quick->read_time : s->table->file->scan_time(); + tmp+= (s->records - rnd_records)/(double) TIME_FOR_COMPARE; + /* We read the table as many times as join buffer becomes full. */ tmp*= (1.0 + floor((double) cache_record_length(join,idx) * record_count / (double) thd->variables.join_buff_size)); - tmp+= (s->records - rnd_records)/(double) TIME_FOR_COMPARE; best_time= tmp + (record_count*join_sel) / TIME_FOR_COMPARE * rnd_records; best= tmp; records= rows2double(rnd_records); best_key= hj_start_key; best_ref_depends_map= 0; - best_uses_jbuf= test(!disable_jbuf); + best_uses_jbuf= TRUE; } /* @@ -6483,7 +6486,7 @@ static bool create_hj_key_for_table(JOIN *join, JOIN_TAB *join_tab, keyinfo->key_length=0; keyinfo->algorithm= HA_KEY_ALG_UNDEF; keyinfo->flags= HA_GENERATED_KEY; - keyinfo->name= (char *) "hj_key"; + keyinfo->name= (char *) "$hj"; keyinfo->rec_per_key= (ulong*) thd->calloc(sizeof(ulong)*key_parts); if (!keyinfo->rec_per_key) DBUG_RETURN(TRUE); @@ -7167,6 +7170,7 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) table_map current_map; for (uint i=join->const_tables ; i < join->tables ; i++) { + bool is_hj; tab= join->join_tab+i; /* first_inner is the X in queries like: @@ -7227,9 +7231,20 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) /* Add conditions added by add_not_null_conds(). */ if (tab->select_cond) add_cond_and_fix(thd, &tmp, tab->select_cond); + + is_hj= (tab->type == JT_REF || tab->type == JT_EQ_REF) && + (join->allowed_join_cache_types & JOIN_CACHE_HASHED_BIT) && + ((join->max_allowed_join_cache_level+1)/2 == 2 || + ((join->max_allowed_join_cache_level+1)/2 > 2 && + is_hash_join_key_no(tab->ref.key))) && + (!tab->emb_sj_nest || + join->allowed_semijoin_with_cache) && + (!(tab->table->map & join->outer_join) || + join->allowed_outer_join_with_cache); + if (cond && !tmp && tab->quick) { // Outer join - if (tab->type != JT_ALL) + if (tab->type != JT_ALL && !is_hj) { /* Don't use the quick method @@ -7308,9 +7323,10 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) { /* Use quick key read if it's a constant and it's not used with key reading */ - if (tab->needed_reg.is_clear_all() && tab->type != JT_EQ_REF - && tab->type != JT_FT && (tab->type != JT_REF || - (uint) tab->ref.key == tab->quick->index)) + if ((tab->needed_reg.is_clear_all() && tab->type != JT_EQ_REF + && tab->type != JT_FT && + (tab->type != JT_REF || + (uint) tab->ref.key == tab->quick->index)) || is_hj) { sel->quick=tab->quick; // Use value from get_quick_... sel->quick_keys.clear_all(); @@ -8295,6 +8311,9 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) if (jcl) tab[-1].next_select=sub_select_cache; + + if (tab->cache && tab->cache->get_join_alg() == JOIN_CACHE::BNLH_JOIN_ALG) + tab->type= JT_HASH; switch (tab->type) { case JT_SYSTEM: // Only happens with left join @@ -8308,7 +8327,7 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) table->key_read=1; table->file->extra(HA_EXTRA_KEYREAD); } - else if ((!jcl || jcl > 4) && !tab->is_ref_for_hash_join()) + else if (!jcl || jcl > 4) push_index_cond(tab, tab->ref.key); break; case JT_EQ_REF: @@ -8320,7 +8339,7 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) table->key_read=1; table->file->extra(HA_EXTRA_KEYREAD); } - else if ((!jcl || jcl > 4) && !tab->is_ref_for_hash_join()) + else if (!jcl || jcl > 4) push_index_cond(tab, tab->ref.key); break; case JT_REF_OR_NULL: @@ -8335,10 +8354,11 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) if (table->covering_keys.is_set(tab->ref.key) && !table->no_keyread) table->enable_keyread(); - else if ((!jcl || jcl > 4) &&!tab->is_ref_for_hash_join()) + else if (!jcl || jcl > 4) push_index_cond(tab, tab->ref.key); break; case JT_ALL: + case JT_HASH: /* If previous table use cache If the incoming data set is already sorted don't use cache. @@ -8412,7 +8432,8 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) */ tab->index=find_shortest_key(table, & table->covering_keys); tab->read_first_record= join_read_first; - tab->type=JT_NEXT; // Read with index_first / index_next + /* Read with index_first / index_next */ + tab->type= tab->type == JT_ALL ? JT_NEXT : JT_HASH_NEXT; } } if (tab->select && tab->select->quick && @@ -8454,6 +8475,11 @@ make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after) sort_by_tab->type= JT_ALL; sort_by_tab->read_first_record= join_init_read_record; } + else if (sort_by_tab->type == JT_HASH_NEXT) + { + sort_by_tab->type= JT_HASH; + sort_by_tab->read_first_record= join_init_read_record; + } } break; } @@ -19109,7 +19135,7 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, TABLE *table=tab->table; TABLE_LIST *table_list= tab->table->pos_in_table_list; char buff[512]; - char buff1[512], buff2[512], buff3[512]; + char buff1[512], buff2[512], buff3[512], buff4[512]; char keylen_str_buf[64]; my_bool key_read; String extra(buff, sizeof(buff),cs); @@ -19117,10 +19143,17 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, String tmp1(buff1,sizeof(buff1),cs); String tmp2(buff2,sizeof(buff2),cs); String tmp3(buff3,sizeof(buff3),cs); + String tmp4(buff4,sizeof(buff4),cs); + char hash_key_prefix[]= "#hash#"; + KEY *key_info= 0; + uint key_len= 0; + bool is_hj= tab->type == JT_HASH || tab->type ==JT_HASH_NEXT; + extra.length(0); tmp1.length(0); tmp2.length(0); tmp3.length(0); + tmp4.length(0); quick_type= -1; /* Don't show eliminated tables */ @@ -19216,21 +19249,19 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, goto loop_end; } - if (tab->type == JT_ALL && tab->select && tab->select->quick) + if ((tab->type == JT_ALL || tab->type == JT_HASH) && + tab->select && tab->select->quick) { quick_type= tab->select->quick->get_type(); if ((quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE) || (quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_INTERSECT) || (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT) || (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION)) - tab->type = JT_INDEX_MERGE; + tab->type= tab->type == JT_ALL ? JT_INDEX_MERGE : JT_HASH_INDEX_MERGE; else - tab->type = JT_RANGE; + tab->type= tab->type == JT_ALL ? JT_RANGE : JT_HASH_RANGE; } - if (tab->cache && tab->cache->get_join_alg() == JOIN_CACHE::BNLH_JOIN_ALG) - tab->type= JT_HASH; - /* table */ if (table->derived_select_number) { @@ -19292,45 +19323,66 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, item_list.push_back(item_null); /* Build "key", "key_len", and "ref" values and add them to item_list */ - if (tab->ref.key_parts) + if (tab->type == JT_NEXT) + { + key_info= table->key_info+tab->index; + key_len= key_info->key_length; + } + else if (tab->ref.key_parts) + { + key_info= tab->get_keyinfo_by_key_no(tab->ref.key); + key_len= tab->ref.key_length; + } + if (key_info) { - KEY *key_info= tab->get_keyinfo_by_key_no(tab->ref.key); register uint length; - item_list.push_back(new Item_string(key_info->name, - strlen(key_info->name), - system_charset_info)); - length= (longlong10_to_str(tab->ref.key_length, keylen_str_buf, 10) - + if (is_hj) + tmp2.append(hash_key_prefix, strlen(hash_key_prefix), cs); + tmp2.append(key_info->name, strlen(key_info->name), cs); + length= (longlong10_to_str(key_len, keylen_str_buf, 10) - keylen_str_buf); - item_list.push_back(new Item_string(keylen_str_buf, length, - system_charset_info)); - for (store_key **ref=tab->ref.key_copy ; *ref ; ref++) + tmp3.append(keylen_str_buf, length, cs); + if (tab->ref.key_parts) { - if (tmp2.length()) - tmp2.append(','); - tmp2.append((*ref)->name(), strlen((*ref)->name()), - system_charset_info); - } - item_list.push_back(new Item_string(tmp2.ptr(),tmp2.length(),cs)); + for (store_key **ref=tab->ref.key_copy ; *ref ; ref++) + { + if (tmp4.length()) + tmp4.append(','); + tmp4.append((*ref)->name(), strlen((*ref)->name()), cs); + } + } } - else if (tab->type == JT_NEXT) + if (is_hj && tab->type != JT_HASH) { - KEY *key_info=table->key_info+ tab->index; - register uint length; - item_list.push_back(new Item_string(key_info->name, - strlen(key_info->name),cs)); - length= (longlong10_to_str(key_info->key_length, keylen_str_buf, 10) - - keylen_str_buf); - item_list.push_back(new Item_string(keylen_str_buf, - length, - system_charset_info)); - item_list.push_back(item_null); + tmp2.append(':'); + tmp3.append(':'); } - else if (tab->select && tab->select->quick) + if (tab->type == JT_HASH_NEXT) { + register uint length; + key_info= table->key_info+tab->index; + key_len= key_info->key_length; + tmp2.append(key_info->name, strlen(key_info->name), cs); + length= (longlong10_to_str(key_len, keylen_str_buf, 10) - + keylen_str_buf); + tmp3.append(keylen_str_buf, length, cs); + } + if (tab->select && tab->select->quick) tab->select->quick->add_keys_and_lengths(&tmp2, &tmp3); - item_list.push_back(new Item_string(tmp2.ptr(),tmp2.length(),cs)); - item_list.push_back(new Item_string(tmp3.ptr(),tmp3.length(),cs)); - item_list.push_back(item_null); + if (key_info || (tab->select && tab->select->quick)) + { + if (tmp2.length()) + item_list.push_back(new Item_string(tmp2.ptr(),tmp2.length(),cs)); + else + item_list.push_back(item_null); + if (tmp3.length()) + item_list.push_back(new Item_string(tmp3.ptr(),tmp3.length(),cs)); + else + item_list.push_back(item_null); + if (key_info && tab->type != JT_NEXT) + item_list.push_back(new Item_string(tmp4.ptr(),tmp4.length(),cs)); + else + item_list.push_back(item_null); } else { @@ -19378,8 +19430,7 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, ha_rows examined_rows; if (tab->select && tab->select->quick) examined_rows= tab->select->quick->records; - else if (tab->type == JT_NEXT || tab->type == JT_ALL || - tab->type == JT_HASH) + else if (tab->type == JT_NEXT || tab->type == JT_ALL || is_hj) { if (tab->limit) examined_rows= tab->limit; diff --git a/sql/sql_select.h b/sql/sql_select.h index 67c901ebd66..03d135b7c4b 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -135,7 +135,7 @@ typedef struct st_table_ref enum join_type { JT_UNKNOWN,JT_SYSTEM,JT_CONST,JT_EQ_REF,JT_REF,JT_MAYBE_REF, JT_ALL, JT_RANGE, JT_NEXT, JT_FT, JT_REF_OR_NULL, JT_UNIQUE_SUBQUERY, JT_INDEX_SUBQUERY, JT_INDEX_MERGE, - JT_HASH}; + JT_HASH, JT_HASH_RANGE, JT_HASH_NEXT, JT_HASH_INDEX_MERGE}; class JOIN; diff --git a/sql/sql_test.cc b/sql/sql_test.cc index 5bf9cd1f77e..af89b3cd525 100644 --- a/sql/sql_test.cc +++ b/sql/sql_test.cc @@ -231,14 +231,18 @@ void print_keyuse(KEYUSE *keyuse) char buff[256]; char buf2[64]; const char *fieldname; + JOIN_TAB *join_tab= keyuse->table->reginfo.join_tab; + KEY *key_info= join_tab->get_keyinfo_by_key_no(keyuse->key); String str(buff,(uint32) sizeof(buff), system_charset_info); str.length(0); keyuse->val->print(&str, QT_ORDINARY); str.append('\0'); - if (keyuse->keypart == FT_KEYPART) + if (keyuse->is_for_hash_join()) + fieldname= keyuse->table->field[keyuse->keypart]->field_name; + else if (keyuse->keypart == FT_KEYPART) fieldname= "FT_KEYPART"; else - fieldname= keyuse->table->key_info[keyuse->key].key_part[keyuse->keypart].field->field_name; + fieldname= key_info->key_part[keyuse->keypart].field->field_name; longlong2str(keyuse->used_tables, buf2, 16, 0); DBUG_LOCK_FILE; fprintf(DBUG_FILE, "KEYUSE: %s.%s=%s optimize: %u used_tables: %s " diff --git a/storage/heap/hp_rfirst.c b/storage/heap/hp_rfirst.c index 8e562983b02..e45af4a219f 100644 --- a/storage/heap/hp_rfirst.c +++ b/storage/heap/hp_rfirst.c @@ -52,6 +52,7 @@ int heap_rfirst(HP_INFO *info, uchar *record, int inx) } else { + info->update= HA_STATE_NO_KEY; my_errno = HA_ERR_END_OF_FILE; DBUG_RETURN(my_errno); } diff --git a/storage/heap/hp_rkey.c b/storage/heap/hp_rkey.c index c2edf63f6f2..166ed28aed0 100644 --- a/storage/heap/hp_rkey.c +++ b/storage/heap/hp_rkey.c @@ -51,7 +51,7 @@ int heap_rkey(HP_INFO *info, uchar *record, int inx, const uchar *key, if (!(pos= tree_search_key(&keyinfo->rb_tree, info->lastkey, info->parents, &info->last_pos, find_flag, &custom_arg))) { - info->update= 0; + info->update= HA_STATE_NO_KEY; DBUG_RETURN(my_errno= HA_ERR_KEY_NOT_FOUND); } memcpy(&pos, pos + (*keyinfo->get_key_length)(keyinfo, pos), sizeof(uchar*)); @@ -61,7 +61,7 @@ int heap_rkey(HP_INFO *info, uchar *record, int inx, const uchar *key, { if (!(pos= hp_search(info, share->keydef + inx, key, 0))) { - info->update= 0; + info->update= HA_STATE_NO_KEY; DBUG_RETURN(my_errno); } if (!(keyinfo->flag & HA_NOSAME)) diff --git a/storage/heap/hp_rnext.c b/storage/heap/hp_rnext.c index 7a654850e0e..7a759e70972 100644 --- a/storage/heap/hp_rnext.c +++ b/storage/heap/hp_rnext.c @@ -32,7 +32,20 @@ int heap_rnext(HP_INFO *info, uchar *record) { heap_rb_param custom_arg; - if (info->last_pos) + /* If no active record and last was not deleted */ + if (!(info->update & (HA_STATE_AKTIV | HA_STATE_NO_KEY | + HA_STATE_DELETED))) + { + if (info->update & HA_STATE_NEXT_FOUND) + pos= 0; /* Can't search after last row */ + else + { + /* Last was 'prev' before first record; search after first record */ + pos= tree_search_edge(&keyinfo->rb_tree, info->parents, + &info->last_pos, offsetof(TREE_ELEMENT, left)); + } + } + else if (info->last_pos) { /* We enter this branch for non-DELETE queries after heap_rkey() diff --git a/storage/heap/hp_rprev.c b/storage/heap/hp_rprev.c index 1d71c20eef4..8a50444bb5f 100644 --- a/storage/heap/hp_rprev.c +++ b/storage/heap/hp_rprev.c @@ -32,7 +32,20 @@ int heap_rprev(HP_INFO *info, uchar *record) { heap_rb_param custom_arg; - if (info->last_pos) + /* If no active record and last was not deleted */ + if (!(info->update & (HA_STATE_AKTIV | HA_STATE_NO_KEY | + HA_STATE_DELETED))) + { + if (info->update & HA_STATE_PREV_FOUND) + pos= 0; /* Can't search before first row */ + else + { + /* Last was 'next' after last record; search after last record */ + pos= tree_search_edge(&keyinfo->rb_tree, info->parents, + &info->last_pos, offsetof(TREE_ELEMENT, right)); + } + } + else if (info->last_pos) pos = tree_search_next(&keyinfo->rb_tree, &info->last_pos, offsetof(TREE_ELEMENT, right), offsetof(TREE_ELEMENT, left)); diff --git a/storage/heap/hp_rsame.c b/storage/heap/hp_rsame.c index 1a3724672b6..f93a443aa48 100644 --- a/storage/heap/hp_rsame.c +++ b/storage/heap/hp_rsame.c @@ -43,7 +43,7 @@ int heap_rsame(register HP_INFO *info, uchar *record, int inx) hp_make_key(share->keydef + inx, info->lastkey, record); if (!hp_search(info, share->keydef + inx, info->lastkey, 3)) { - info->update=0; + info->update= 0; DBUG_RETURN(my_errno); } } diff --git a/storage/xtradb/buf/buf0buf.c b/storage/xtradb/buf/buf0buf.c index 1e9624fce50..9e09075ae9f 100644 --- a/storage/xtradb/buf/buf0buf.c +++ b/storage/xtradb/buf/buf0buf.c @@ -2196,7 +2196,7 @@ buf_page_get_gen( ulint fix_type; ibool must_read; ulint retries = 0; - mutex_t* block_mutex; + mutex_t* block_mutex= NULL; trx_t* trx = NULL; ulint sec; ulint ms; diff --git a/storage/xtradb/fil/fil0fil.c b/storage/xtradb/fil/fil0fil.c index a8520187013..926b1de3051 100644 --- a/storage/xtradb/fil/fil0fil.c +++ b/storage/xtradb/fil/fil0fil.c @@ -3313,7 +3313,7 @@ skip_info: } if (page_is_corrupt) { - fprintf(stderr, " [errp:%lld]", offset / UNIV_PAGE_SIZE); + fprintf(stderr, " [errp:%lld]", (longlong) (offset / UNIV_PAGE_SIZE)); /* cannot treat corrupt page */ goto skip_write; diff --git a/storage/xtradb/handler/ha_innodb.cc b/storage/xtradb/handler/ha_innodb.cc index f0a62d10617..e2ea4331d38 100644 --- a/storage/xtradb/handler/ha_innodb.cc +++ b/storage/xtradb/handler/ha_innodb.cc @@ -12091,7 +12091,7 @@ ha_rows ha_innobase::multi_range_read_info(uint keyno, uint n_ranges, uint keys, bool ha_innobase::is_thd_killed() { - return test(user_thd->killed); + return thd_killed(user_thd); } /** @@ -12109,7 +12109,7 @@ static xtradb_icp_result_t index_cond_func_innodb(void *arg) { ha_innobase *h= (ha_innobase*)arg; if (h->is_thd_killed()) - return XTRADB_ICP_ERROR; + return XTRADB_ICP_ABORTED_BY_USER; if (h->end_range) { |