revision-id: e5c26fdfab3d43ad2b0524a3bf29f59f6fab37d8 (mariadb-10.0.35-58-ge5c26fdfab3) parent(s): 1fd84f9129f2ed98706f6e225b06b16a13d0ebd0 2fbf2277ffec86d69f793534da7043b6dd540780 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-07-17 16:56:21 +0200 message: Merge branch '5.5' into bb-10.0-merge mysql-test/r/derived.result | 33 +++++++++++++++++++++ mysql-test/r/join.result | 40 +++++++++++++++++++++++++ mysql-test/r/join_cache.result | 33 +++++++++++++++++++++ mysql-test/r/subselect_sj2_mat.result | 51 ++++++++++++++++++++++++++++++++ mysql-test/suite/vcol/t/vcol_misc.test | 2 ++ mysql-test/t/derived.test | 21 ++++++++++++++ mysql-test/t/join.test | 53 ++++++++++++++++++++++++++++++++++ mysql-test/t/join_cache.test | 32 +++++++++++++++++++- mysql-test/t/subselect_sj2_mat.test | 49 +++++++++++++++++++++++++++++++ mysql-test/valgrind.supp | 13 +++++++++ scripts/mysql_install_db.sh | 5 ++++ sql/log.cc | 10 +++---- sql/opt_subselect.cc | 3 +- sql/sql_base.cc | 12 ++++---- sql/sql_select.cc | 4 +-- sql/table.cc | 19 ++++++++---- 16 files changed, 361 insertions(+), 19 deletions(-) diff --cc mysql-test/r/join.result index d500b38a8dc,7b0e7807e39..d065403371d --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@@ -1500,54 -1498,42 +1500,94 @@@ DROP VIEW v2 DROP TABLE t1,t2; SET optimizer_switch=@save_optimizer_switch; # + # MDEV-16512 + # Server crashes in find_field_in_table_ref on 2nd execution of SP referring to + # non-existing field + # + CREATE TABLE t (i INT); + CREATE PROCEDURE p() SELECT t1.f FROM t AS t1 JOIN t AS t2 USING (f); + CALL p; + ERROR 42S22: Unknown column 'f' in 'from clause' + CALL p; + ERROR 42S22: Unknown column 'f' in 'from clause' + FLUSH TABLES; + CALL p; + ERROR 42S22: Unknown column 'f' in 'from clause' + DROP TABLE t; + CREATE TABLE t (f INT); + CALL p; + ERROR 42S22: Unknown column 'f' in 'from clause' + DROP TABLE t; + CREATE TABLE t (i INT); + CALL p; + ERROR 42S22: Unknown column 'f' in 'from clause' + DROP PROCEDURE p; + DROP TABLE t; + CREATE TABLE t1 (a INT, b INT); + CREATE TABLE t2 (a INT); + CREATE TABLE t3 (a INT, c INT); + CREATE TABLE t4 (a INT, c INT); + CREATE TABLE t5 (a INT, c INT); + CREATE PROCEDURE p1() SELECT c FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a) + LEFT JOIN t5 USING (a)) USING (a); + CALL p1; + ERROR 23000: Column 'c' in field list is ambiguous + CALL p1; + ERROR 23000: Column 'c' in field list is ambiguous + DROP PROCEDURE p1; + DROP TABLE t1,t2,t3,t4,t5; + # + # End of MariaDB 5.5 tests + # ++# +# Bug #35268: Parser can't handle STRAIGHT_JOIN with USING +# +CREATE TABLE t1 (a int); +INSERT INTO t1 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8); +CREATE TABLE t2 (a int); +INSERT INTO t2 (a) VALUES (1),(2),(3),(4); +EXPLAIN +SELECT t1.a FROM t1 NATURAL INNER JOIN t2 ORDER BY t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) +SELECT t1.a FROM t1 NATURAL INNER JOIN t2 ORDER BY t1.a; +a +1 +2 +3 +4 +EXPLAIN +SELECT t1.a FROM t1 STRAIGHT_JOIN t2 USING(a) ORDER BY t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +SELECT t1.a FROM t1 STRAIGHT_JOIN t2 USING(a) ORDER BY t1.a; +a +1 +2 +3 +4 +EXPLAIN +SELECT t1.a FROM t1 NATURAL STRAIGHT_JOIN t2 ORDER BY t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +SELECT t1.a FROM t1 NATURAL STRAIGHT_JOIN t2 ORDER BY t1.a; +a +1 +2 +3 +4 +DROP TABLE t1,t2; +# +# MDEV-5635: join of a const table with non-const tables +# +CREATE TABLE t1 (a varchar(3) NOT NULL) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('foo'); +CREATE TABLE t2 (b varchar(3), c varchar(3), INDEX(b)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('bar', 'bar'),( 'qux', 'qux'); +SELECT STRAIGHT_JOIN * FROM t1, t2 AS t2_1, t2 AS t2_2 +WHERE t2_2.c = t2_1.c AND t2_2.b = t2_1.b AND ( a IS NULL OR t2_1.c = a ); +a b c b c +DROP TABLE t1,t2; diff --cc mysql-test/r/join_cache.result index f2383ce2681,f1e6fb577c8..18c3a057398 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@@ -5871,86 -5872,36 +5871,119 @@@ SET join_buffer_space_limit= default set optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t4,t5,t2; # + # MDEV-16603: BNLH for query with materialized semi-join + # + set join_cache_level=4; + CREATE TABLE t1 ( i1 int, v1 varchar(1)) ENGINE=InnoDB; + INSERT INTO t1 VALUES (7,'x'); + CREATE TABLE t2 (i1 int, v1 varchar(1), KEY v1 (v1,i1)) ENGINE=InnoDB; + INSERT INTO t2 VALUES + (NULL,'x'),(1,'x'),(3,'x'),(5,'x'),(8,'x'),(48,'x'), + (228,'x'),(3,'y'),(1,'z'),(9,'z'); + CREATE TABLE temp + SELECT t1.i1 AS f1, t1.v1 AS f2 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1)); + SELECT * FROM temp + WHERE (f1,f2) IN (SELECT t1.i1, t1.v1 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1))); + f1 f2 + 7 x + 7 x + 7 x + 7 x + 7 x + 7 x + 7 x + EXPLAIN EXTENDED SELECT * FROM temp + WHERE (f1,f2) IN (SELECT t1.i1, t1.v1 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1))); + id select_type table type possible_keys key key_len ref rows filtered Extra + 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1 100.00 + 1 PRIMARY temp hash_ALL NULL #hash#$hj 9 test.t1.i1,test.t1.v1 7 100.00 Using where; Using join buffer (flat, BNLH join) + 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 1 100.00 Using where + 2 MATERIALIZED t2 hash_index v1 #hash#v1:v1 4:9 test.t1.v1 10 10.00 Using join buffer (flat, BNLH join) + Warnings: + Note 1003 select `test`.`temp`.`f1` AS `f1`,`test`.`temp`.`f2` AS `f2` from `test`.`temp` semi join (`test`.`t2` join `test`.`t1`) where ((`test`.`temp`.`f1` = `test`.`t1`.`i1`) and (`test`.`t2`.`v1` = `test`.`t1`.`v1`) and (`test`.`temp`.`f2` = `test`.`t1`.`v1`)) + DROP TABLE t1,t2,temp; + SET join_cache_level = default; ++# +# MDEV-5123 Remove duplicated conditions pushed both to join_tab->select_cond and join_tab->cache_select->cond for blocked joins. +# +set join_cache_level=default; +set expensive_subquery_limit=0; +create table t1 (c1 int); +create table t2 (c2 int); +create table t3 (c3 int); +insert into t1 values (1), (2); +insert into t2 values (1), (2); +insert into t3 values (2); +explain +select count(*) from t1 straight_join t2 +where c1 = c2-0 and c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +2 UNCACHEABLE SUBQUERY t3 system NULL NULL NULL NULL 1 +set @counter=0; +select count(*) from t1 straight_join t2 +where c1 = c2-0 and c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1); +count(*) +2 +select @counter; +@counter +2 +explain +select count(*) from t1 straight_join t2 +where c1 = c2-0 and +c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1) and +c2 / 2 = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +2 UNCACHEABLE SUBQUERY t3 system NULL NULL NULL NULL 1 +set @counter=0; +select count(*) from t1 straight_join t2 +where c1 = c2-0 and +c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1) and +c2 / 2 = 1; +count(*) +1 +select @counter; +@counter +2 +drop table t1,t2,t3; +set expensive_subquery_limit=default; +# +# MDEV-6071: EXPLAIN chooses to use join buffer while execution turns it down +# +create table t1 (a int); +insert into t1 values +(7), (9), (1), (4), (2), (3), (5), (8), (11), (6), (10); +explain select count(*) from t1, t1 t2 where t1.a=t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 11 +1 SIMPLE t2 ALL NULL NULL NULL NULL 11 Using where; Using join buffer (flat, BNL join) +set join_buffer_space_limit=1024*8; +select @@join_buffer_space_limit; +@@join_buffer_space_limit +8192 +select @@join_buffer_size; +@@join_buffer_size +131072 +explain select count(*) from t1, 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 11 +1 SIMPLE t2 ALL NULL NULL NULL NULL 11 Using where +set join_buffer_space_limit=default; +drop table t1; +# +# MDEV-6687: Assertion `0' failed in Protocol::end_statement on query +# +SET join_cache_level = 3; +# The following should have +# - table order PROFILING,user, +# - table user accessed with hash_ALL: +explain +SELECT * FROM INFORMATION_SCHEMA.PROFILING, mysql.user WHERE password_expired = PAGE_FAULTS_MINOR; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE PROFILING ALL NULL NULL NULL NULL NULL Using where +1 SIMPLE user hash_ALL NULL #hash#$hj 1 information_schema.PROFILING.PAGE_FAULTS_MINOR 4 Using where; Using join buffer (flat, BNLH join) +set join_cache_level=default; set @@optimizer_switch=@save_optimizer_switch; diff --cc mysql-test/r/subselect_sj2_mat.result index c27beb295b8,7f97e1aabee..140739e5195 --- a/mysql-test/r/subselect_sj2_mat.result +++ b/mysql-test/r/subselect_sj2_mat.result @@@ -1692,92 -1659,53 +1692,143 @@@ i 13 drop table t1; # + # MDEV-15982: Incorrect results when subquery is materialized + # + CREATE TABLE `t1` (`id` int(32) NOT NULL primary key); + INSERT INTO `t1` VALUES + (45), (46), (47), (48), (49), (50), (51), (52), (53), (54), (55), (56), (57), (58), (59), (60), (61), (62), + (63), (64), (65), (66), (67), (68), (69), (70), (71), (72), (73), (74), (75), (76), (77), (78), (79), (80), + (81), (82), (83), (84), (85), (86), (87), (88), (89), (90), (91), (92),(93),(94),(95),(96), (97), (98), + (99), (100), (101), (102), (103), (104), (105), (106), (107), (108), (109), (110), (111), (112), (113), + (114), (115), (116), (117), (118), (119), (120), (121), (122), (123), (124), (125), (126), (127), (128), + (129), (130), (131), (132), (133), (134), (135), (136), (137), (138), (139), (140), (141), (142), (143), (144), (145), (146), + (147), (148), (149), (150), (151), (152), (153), (154), (155), (156), (157), (158), (159), (160), (161), + (162), (163), (164), (165), (166), (167), (168), (169), (170), (171), (172), (173), + (174), (175), (176), (177), (178), (179), (180), (181), (182), (183), (2), (3), (4), (5), (6), (19), (35), + (7), (20), (8), (36), (219), (22), (10), (23), (37), (11), (24); + CREATE TABLE `t2` (`type` int , `id` int(32) NOT NULL primary key); + INSERT INTO `t2` VALUES + (2,2),(2,3),(1,4),(2,5),(1,6),(1,19),(5,7),(1,20),(1,8),(1,21),(1,9), + (1,22),(2,10),(1,23),(2,11),(1,24),(1,12),(1,25),(2,13),(2,26),(2,14), + (2,27),(1,15),(1,28),(3,16),(1,29),(2,17),(1,30),(5,18),(2,1); + CREATE TABLE `t3` (`ref_id` int(32) unsigned ,`type` varchar(80),`id` int(32) NOT NULL ); + INSERT INTO `t3` VALUES + (1,'incident',31),(2,'faux pas',32), + (5,'oopsies',33),(3,'deniable',34), + (11,'wasntme',35),(10,'wasntme',36), + (17,'faux pas',37),(13,'unlikely',38), + (13,'improbable',39),(14,'incident',40), + (26,'problem',41),(14,'problem',42), + (26,'incident',43),(27,'incident',44); + explain + SELECT t2.id FROM t2,t1 + WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id; + id select_type table type possible_keys key key_len ref rows Extra + 1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 30 Using index + 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 Using where + 1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t2.id 1 Using index + 2 MATERIALIZED t3 ALL NULL NULL NULL NULL 14 + 2 MATERIALIZED t1 eq_ref PRIMARY PRIMARY 4 test.t3.id 1 Using index + SELECT t2.id FROM t2,t1 + WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id; + id + 10 + 11 + set optimizer_switch='materialization=off'; + SELECT t2.id FROM t2,t1 + WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id; + id + 11 + 10 + set optimizer_switch='materialization=on'; + DROP TABLE t1,t2,t3; ++# +# MDEV-15247: Crash when SET NAMES 'utf8' is set +# +CREATE TABLE t1 ( +id_category int unsigned, +id_product int unsigned, +PRIMARY KEY (id_category,id_product) +) ENGINE=MyISAM; +INSERT INTO `t1` VALUES (31,216), (31,215), (31,214), (31,213), (31,212), (32,211), (32,210), (32,209), (32,208), (29,207), (30,315372), (2,161), (2,132), (33,315380), (31,315371), (29,315370), (29,315373), (29,315369), (29,315374), (29,315368), (29,315375), (29,315367), (29,183), (29,182), (30,177), (29,315376), (13,315365), (2,167), (2,315357), (2,164), (2,159), (2,131), (2,127), (14,315364), (27,315363), (29,205), (29,204), (29,203), (29,202), (29,201), (29,200), (29,199), (29,198), (29,197), (29,196), (29,195), (29,194), (29,193), (29,192), (29,191), (29,190), (29,189), (14,188), (29,187), (29,186), (29,185), (29,184), (29,315377), (29,315378), (29,181), (33,315379), (29,179), (30,178), (29,180), (30,176), (30,175), (30,174), (30,173), (30,172), (11,171), (27,315357), (23,108), (23,102); +CREATE TABLE t2 ( +id_product int, +id_t2 int, +KEY id_t2 (id_t2), +KEY id_product (id_product) +) ENGINE=MyISAM; +INSERT INTO `t2` VALUES (11,31), (11,31), (11,31), (11,32), (11,32), +(11,32), (10,26), (11,32), (10,28), (11,32), (10,29), (11,33), (10,26), +(11,33), (10,27), (9,23), (11,32), (10,26), (8,18), (7,15), (11,32), +(10,28), (11,32), (10,28), (11,32), (10,29), (11,32), (10,29), (8,19), +(7,16), (8,18), (7,16), (8,20), (7,16), (11,32), (10,28), (8,19), +(7,16), (8,20), (7,16), (11,32), (10,29), (8,19), (7,16), (8,20), +(7,16), (10,27), (9,23), (10,27), (9,23), (10,27), (9,23), (11,32), +(10,27), (11,32), (10,27), (8,18), (7,15), (10,26), (9,24), (8,19), +(7,16), (10,26), (9,23), (8,19), (7,16), (8,18), (7,16), (8,18), (7,16), +(9,23), (8,18), (9,23), (8,19), (7,16), (7,16), (8,19), (7,16), (11,31), +(10,27), (9,24), (11,31), (10,27), (9,23), (8,19), (11,31), (10,26), (9,24), +(8,19), (11,31), (10,26), (9,25), (8,18), (11,31), (10,26), (9,23), (8,19), +(11,31), (10,26), (9,23), (8,18), (11,31), (10,30), (9,23), (8,18), (11,31), +(10,30), (9,23), (8,19), (11,31), (10,26), (9,25), (8,19), (8,21), (11,32), +(10,26), (9,22), (8,19), (11,32), (10,26), (9,22), (8,18), (11,32), (10,26), +(9,22), (8,20), (11,33), (10,26), (9,22), (8,19), (11,33), (10,26), (9,22), +(8,18), (11,33), (10,26), (9,22), (8,20), (11,32), (10,26), (9,24), (8,19), +(11,32), (10,26), (9,25), (8,19), (11,32), (10,26), (9,25), (8,18), (11,32), +(10,26), (9,23), (8,18), (11,32), (10,30), (9,23), (8,18), (11,32), (10,30), +(9,23), (8,19), (11,32), (10,26), (9,23), (8,19), (11,32), (10,27), (9,23), +(11,32), (10,27), (9,23), (11,32), (10,27), (9,23), (10,26), (9,22), (8,19), +(7,15), (10,26), (9,22), (8,20), (7,15), (10,26), (9,22), (8,18), (7,15), +(8,19), (10,26), (10,26), (11,33), (10,26), (11,33), (10,26), (11,33), +(10,27), (11,33), (10,27), (11,31), (10,26), (11,31), (10,26), (8,18), +(7,15), (9,23), (9,23), (9,24), (8,21), (7,15), (7,15), (7,15), (7,15), +(7,15), (7,15), (7,15), (7,15), (7,15), (8,18), (7,17), (8,18), (7,17), (8,19), (8,19); +CREATE TABLE t3 ( +id_product int unsigned, +PRIMARY KEY (id_product) +) ENGINE=MyISAM; +INSERT INTO t3 VALUES +(102),(103),(104),(105),(106),(107),(108),(109),(110), +(315371),(315373),(315374),(315375),(315376),(315377), +(315378),(315379),(315380); +CREATE TABLE t4 ( +id_product int not null, +id_shop int, +PRIMARY KEY (id_product,id_shop) +) ENGINE=MyISAM ; +INSERT INTO t4 VALUES +(202,1),(201,1),(200,1),(199,1),(198,1),(197,1),(196,1),(195,1), +(194,1),(193,1),(192,1),(191,1),(190,1),(189,1),(188,1),(187,1), +(186,1),(185,1),(184,1),(183,1),(182,1),(181,1),(179,1),(178,1), +(177,1),(176,1),(126,1),(315380,1); +CREATE TABLE t5 (id_product int) ENGINE=MyISAM; +INSERT INTO `t5` VALUES +(652),(668),(669),(670),(671),(673),(674),(675),(676), +(677),(679),(680),(681),(682),(683),(684),(685),(686); +explain +SELECT * FROM t3 +JOIN t4 ON (t4.id_product = t3.id_product AND t4.id_shop = 1) +JOIN t1 ON (t1.id_product = t3.id_product) +LEFT JOIN t5 ON (t5.id_product = t3.id_product) +WHERE 1=1 +AND t3.id_product IN (SELECT id_product FROM t2 t2_1 WHERE t2_1.id_t2 = 32) +AND t3.id_product IN (SELECT id_product FROM t2 t2_2 WHERE t2_2.id_t2 = 15) +AND t3.id_product IN (SELECT id_product FROM t2 t2_3 WHERE t2_3.id_t2 = 18 OR t2_3.id_t2 = 19) +AND t3.id_product IN (SELECT id_product FROM t2 t2_4 WHERE t2_4.id_t2 = 34 OR t2_4.id_t2 = 23) +AND t3.id_product IN (SELECT id_product FROM t2 t2_5 WHERE t2_5.id_t2 = 29 OR t2_5.id_t2 = 28 OR t2_5.id_t2 = 26); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL PRIMARY 8 NULL 73 Using index +1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.id_product 1 Using index +1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY t4 eq_ref PRIMARY PRIMARY 8 test.t1.id_product,const 1 Using where; Using index +1 PRIMARY <subquery6> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY t5 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) +5 MATERIALIZED t2_4 range id_t2,id_product id_t2 5 NULL 18 Using index condition; Using where +4 MATERIALIZED t2_3 range id_t2,id_product id_t2 5 NULL 32 Using index condition; Using where +3 MATERIALIZED t2_2 ref id_t2,id_product id_t2 5 const 12 +2 MATERIALIZED t2_1 ref id_t2,id_product id_t2 5 const 50 +6 MATERIALIZED t2_5 range id_t2,id_product id_t2 5 NULL 30 Using index condition; Using where +drop table t1,t2,t3,t4,t5; diff --cc mysql-test/t/join.test index e07a3665920,feafac57a7e..8a088de91cc --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@@ -1160,43 -1160,54 +1160,96 @@@ DROP TABLE t1,t2 SET optimizer_switch=@save_optimizer_switch; + --echo # + --echo # MDEV-16512 + --echo # Server crashes in find_field_in_table_ref on 2nd execution of SP referring to + --echo # non-existing field + --echo # + + CREATE TABLE t (i INT); + CREATE PROCEDURE p() SELECT t1.f FROM t AS t1 JOIN t AS t2 USING (f); + --error ER_BAD_FIELD_ERROR + CALL p; + --error ER_BAD_FIELD_ERROR + CALL p; + FLUSH TABLES; + --error ER_BAD_FIELD_ERROR + CALL p; + DROP TABLE t; + + # + # Fix the table definition to match the using + # + + CREATE TABLE t (f INT); + # + # The following shouldn't fail as the table is now matching the using + # + --error ER_BAD_FIELD_ERROR + CALL p; + DROP TABLE t; + CREATE TABLE t (i INT); + --error ER_BAD_FIELD_ERROR + CALL p; + DROP PROCEDURE p; + DROP TABLE t; + + CREATE TABLE t1 (a INT, b INT); + CREATE TABLE t2 (a INT); + CREATE TABLE t3 (a INT, c INT); + CREATE TABLE t4 (a INT, c INT); + CREATE TABLE t5 (a INT, c INT); + CREATE PROCEDURE p1() SELECT c FROM t1 JOIN (t2 LEFT JOIN t3 USING (a) LEFT JOIN t4 USING (a) + LEFT JOIN t5 USING (a)) USING (a); + --error ER_NON_UNIQ_ERROR + CALL p1; + --error ER_NON_UNIQ_ERROR + CALL p1; + DROP PROCEDURE p1; + DROP TABLE t1,t2,t3,t4,t5; + + --echo # + --echo # End of MariaDB 5.5 tests + --echo # ++ ++ +--echo # +--echo # Bug #35268: Parser can't handle STRAIGHT_JOIN with USING +--echo # + +CREATE TABLE t1 (a int); + +INSERT INTO t1 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8); + +CREATE TABLE t2 (a int); + +INSERT INTO t2 (a) VALUES (1),(2),(3),(4); + +EXPLAIN +SELECT t1.a FROM t1 NATURAL INNER JOIN t2 ORDER BY t1.a; +SELECT t1.a FROM t1 NATURAL INNER JOIN t2 ORDER BY t1.a; + +EXPLAIN +SELECT t1.a FROM t1 STRAIGHT_JOIN t2 USING(a) ORDER BY t1.a; +SELECT t1.a FROM t1 STRAIGHT_JOIN t2 USING(a) ORDER BY t1.a; + +EXPLAIN +SELECT t1.a FROM t1 NATURAL STRAIGHT_JOIN t2 ORDER BY t1.a; +SELECT t1.a FROM t1 NATURAL STRAIGHT_JOIN t2 ORDER BY t1.a; + +DROP TABLE t1,t2; + +--echo # +--echo # MDEV-5635: join of a const table with non-const tables +--echo # + +CREATE TABLE t1 (a varchar(3) NOT NULL) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('foo'); + +CREATE TABLE t2 (b varchar(3), c varchar(3), INDEX(b)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('bar', 'bar'),( 'qux', 'qux'); + +SELECT STRAIGHT_JOIN * FROM t1, t2 AS t2_1, t2 AS t2_2 + WHERE t2_2.c = t2_1.c AND t2_2.b = t2_1.b AND ( a IS NULL OR t2_1.c = a ); + +DROP TABLE t1,t2; diff --cc mysql-test/t/join_cache.test index 8507d58eecd,d82b4fa6030..e095419e88d --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@@ -3835,82 -3836,36 +3835,112 @@@ set optimizer_switch=@save_optimizer_sw DROP TABLE t1,t4,t5,t2; + --echo # + --echo # MDEV-16603: BNLH for query with materialized semi-join + --echo # + + --source include/have_innodb.inc + + set join_cache_level=4; + + CREATE TABLE t1 ( i1 int, v1 varchar(1)) ENGINE=InnoDB; + INSERT INTO t1 VALUES (7,'x'); + + CREATE TABLE t2 (i1 int, v1 varchar(1), KEY v1 (v1,i1)) ENGINE=InnoDB; + + INSERT INTO t2 VALUES + (NULL,'x'),(1,'x'),(3,'x'),(5,'x'),(8,'x'),(48,'x'), + (228,'x'),(3,'y'),(1,'z'),(9,'z'); + + CREATE TABLE temp + SELECT t1.i1 AS f1, t1.v1 AS f2 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1)); + + let $q = + SELECT * FROM temp + WHERE (f1,f2) IN (SELECT t1.i1, t1.v1 FROM (t2 JOIN t1 ON (t1.v1 = t2.v1))); + + eval $q; + eval EXPLAIN EXTENDED $q; + + DROP TABLE t1,t2,temp; + + SET join_cache_level = default; + -# this must be the last command in the file +--echo # +--echo # MDEV-5123 Remove duplicated conditions pushed both to join_tab->select_cond and join_tab->cache_select->cond for blocked joins. +--echo # + +set join_cache_level=default; +set expensive_subquery_limit=0; + +create table t1 (c1 int); +create table t2 (c2 int); +create table t3 (c3 int); + +insert into t1 values (1), (2); +insert into t2 values (1), (2); +insert into t3 values (2); + +explain +select count(*) from t1 straight_join t2 +where c1 = c2-0 and c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1); + +set @counter=0; + +select count(*) from t1 straight_join t2 +where c1 = c2-0 and c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1); + +select @counter; + +explain +select count(*) from t1 straight_join t2 +where c1 = c2-0 and + c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1) and + c2 / 2 = 1; + +set @counter=0; + +select count(*) from t1 straight_join t2 +where c1 = c2-0 and + c2 <= (select max(c3) from t3 where c3 = 2 and @counter:=@counter+1) and + c2 / 2 = 1; + +select @counter; + +drop table t1,t2,t3; +set expensive_subquery_limit=default; + +--echo # +--echo # MDEV-6071: EXPLAIN chooses to use join buffer while execution turns it down +--echo # + +create table t1 (a int); +insert into t1 values +(7), (9), (1), (4), (2), (3), (5), (8), (11), (6), (10); + +explain select count(*) from t1, t1 t2 where t1.a=t2.a; + +set join_buffer_space_limit=1024*8; +select @@join_buffer_space_limit; +select @@join_buffer_size; + +explain select count(*) from t1, t1 t2 where t1.a=t2.a; + +set join_buffer_space_limit=default; + +drop table t1; + +--echo # +--echo # MDEV-6687: Assertion `0' failed in Protocol::end_statement on query +--echo # +SET join_cache_level = 3; +--echo # The following should have +--echo # - table order PROFILING,user, +--echo # - table user accessed with hash_ALL: +explain +SELECT * FROM INFORMATION_SCHEMA.PROFILING, mysql.user WHERE password_expired = PAGE_FAULTS_MINOR; + +set join_cache_level=default; + +# The following command must be the last one the file - # this must be the last command in the file set @@optimizer_switch=@save_optimizer_switch; diff --cc mysql-test/t/subselect_sj2_mat.test index 68a888012f2,43b63459928..f54771856e7 --- a/mysql-test/t/subselect_sj2_mat.test +++ b/mysql-test/t/subselect_sj2_mat.test @@@ -345,90 -345,52 +345,139 @@@ WHERE (t.id IN (0,4,12,13,1,10,3,11)) ); drop table t1; + + --echo # + --echo # MDEV-15982: Incorrect results when subquery is materialized + --echo # + + CREATE TABLE `t1` (`id` int(32) NOT NULL primary key); + INSERT INTO `t1` VALUES + (45), (46), (47), (48), (49), (50), (51), (52), (53), (54), (55), (56), (57), (58), (59), (60), (61), (62), + (63), (64), (65), (66), (67), (68), (69), (70), (71), (72), (73), (74), (75), (76), (77), (78), (79), (80), + (81), (82), (83), (84), (85), (86), (87), (88), (89), (90), (91), (92),(93),(94),(95),(96), (97), (98), + (99), (100), (101), (102), (103), (104), (105), (106), (107), (108), (109), (110), (111), (112), (113), + (114), (115), (116), (117), (118), (119), (120), (121), (122), (123), (124), (125), (126), (127), (128), + (129), (130), (131), (132), (133), (134), (135), (136), (137), (138), (139), (140), (141), (142), (143), (144), (145), (146), + (147), (148), (149), (150), (151), (152), (153), (154), (155), (156), (157), (158), (159), (160), (161), + (162), (163), (164), (165), (166), (167), (168), (169), (170), (171), (172), (173), + (174), (175), (176), (177), (178), (179), (180), (181), (182), (183), (2), (3), (4), (5), (6), (19), (35), + (7), (20), (8), (36), (219), (22), (10), (23), (37), (11), (24); + + CREATE TABLE `t2` (`type` int , `id` int(32) NOT NULL primary key); + INSERT INTO `t2` VALUES + (2,2),(2,3),(1,4),(2,5),(1,6),(1,19),(5,7),(1,20),(1,8),(1,21),(1,9), + (1,22),(2,10),(1,23),(2,11),(1,24),(1,12),(1,25),(2,13),(2,26),(2,14), + (2,27),(1,15),(1,28),(3,16),(1,29),(2,17),(1,30),(5,18),(2,1); + + CREATE TABLE `t3` (`ref_id` int(32) unsigned ,`type` varchar(80),`id` int(32) NOT NULL ); + INSERT INTO `t3` VALUES + (1,'incident',31),(2,'faux pas',32), + (5,'oopsies',33),(3,'deniable',34), + (11,'wasntme',35),(10,'wasntme',36), + (17,'faux pas',37),(13,'unlikely',38), + (13,'improbable',39),(14,'incident',40), + (26,'problem',41),(14,'problem',42), + (26,'incident',43),(27,'incident',44); + + explain + SELECT t2.id FROM t2,t1 + WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id; + + SELECT t2.id FROM t2,t1 + WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id; + + set optimizer_switch='materialization=off'; + + SELECT t2.id FROM t2,t1 + WHERE t2.id IN (SELECT t3.ref_id FROM t3,t1 where t3.id = t1.id) and t2.id = t1.id; + set optimizer_switch='materialization=on'; + + DROP TABLE t1,t2,t3; + + +--echo # +--echo # MDEV-15247: Crash when SET NAMES 'utf8' is set +--echo # + +CREATE TABLE t1 ( + id_category int unsigned, + id_product int unsigned, + PRIMARY KEY (id_category,id_product) +) ENGINE=MyISAM; + +INSERT INTO `t1` VALUES (31,216), (31,215), (31,214), (31,213), (31,212), (32,211), (32,210), (32,209), (32,208), (29,207), (30,315372), (2,161), (2,132), (33,315380), (31,315371), (29,315370), (29,315373), (29,315369), (29,315374), (29,315368), (29,315375), (29,315367), (29,183), (29,182), (30,177), (29,315376), (13,315365), (2,167), (2,315357), (2,164), (2,159), (2,131), (2,127), (14,315364), (27,315363), (29,205), (29,204), (29,203), (29,202), (29,201), (29,200), (29,199), (29,198), (29,197), (29,196), (29,195), (29,194), (29,193), (29,192), (29,191), (29,190), (29,189), (14,188), (29,187), (29,186), (29,185), (29,184), (29,315377), (29,315378), (29,181), (33,315379), (29,179), (30,178), (29,180), (30,176), (30,175), (30,174), (30,173), (30,172), (11,171), (27,315357), (23,108), (23,102); + +CREATE TABLE t2 ( + id_product int, + id_t2 int, + KEY id_t2 (id_t2), + KEY id_product (id_product) +) ENGINE=MyISAM; + +INSERT INTO `t2` VALUES (11,31), (11,31), (11,31), (11,32), (11,32), +(11,32), (10,26), (11,32), (10,28), (11,32), (10,29), (11,33), (10,26), +(11,33), (10,27), (9,23), (11,32), (10,26), (8,18), (7,15), (11,32), +(10,28), (11,32), (10,28), (11,32), (10,29), (11,32), (10,29), (8,19), +(7,16), (8,18), (7,16), (8,20), (7,16), (11,32), (10,28), (8,19), +(7,16), (8,20), (7,16), (11,32), (10,29), (8,19), (7,16), (8,20), +(7,16), (10,27), (9,23), (10,27), (9,23), (10,27), (9,23), (11,32), +(10,27), (11,32), (10,27), (8,18), (7,15), (10,26), (9,24), (8,19), +(7,16), (10,26), (9,23), (8,19), (7,16), (8,18), (7,16), (8,18), (7,16), +(9,23), (8,18), (9,23), (8,19), (7,16), (7,16), (8,19), (7,16), (11,31), +(10,27), (9,24), (11,31), (10,27), (9,23), (8,19), (11,31), (10,26), (9,24), +(8,19), (11,31), (10,26), (9,25), (8,18), (11,31), (10,26), (9,23), (8,19), +(11,31), (10,26), (9,23), (8,18), (11,31), (10,30), (9,23), (8,18), (11,31), +(10,30), (9,23), (8,19), (11,31), (10,26), (9,25), (8,19), (8,21), (11,32), +(10,26), (9,22), (8,19), (11,32), (10,26), (9,22), (8,18), (11,32), (10,26), +(9,22), (8,20), (11,33), (10,26), (9,22), (8,19), (11,33), (10,26), (9,22), +(8,18), (11,33), (10,26), (9,22), (8,20), (11,32), (10,26), (9,24), (8,19), +(11,32), (10,26), (9,25), (8,19), (11,32), (10,26), (9,25), (8,18), (11,32), +(10,26), (9,23), (8,18), (11,32), (10,30), (9,23), (8,18), (11,32), (10,30), +(9,23), (8,19), (11,32), (10,26), (9,23), (8,19), (11,32), (10,27), (9,23), +(11,32), (10,27), (9,23), (11,32), (10,27), (9,23), (10,26), (9,22), (8,19), +(7,15), (10,26), (9,22), (8,20), (7,15), (10,26), (9,22), (8,18), (7,15), +(8,19), (10,26), (10,26), (11,33), (10,26), (11,33), (10,26), (11,33), +(10,27), (11,33), (10,27), (11,31), (10,26), (11,31), (10,26), (8,18), +(7,15), (9,23), (9,23), (9,24), (8,21), (7,15), (7,15), (7,15), (7,15), +(7,15), (7,15), (7,15), (7,15), (7,15), (8,18), (7,17), (8,18), (7,17), (8,19), (8,19); + +CREATE TABLE t3 ( + id_product int unsigned, + PRIMARY KEY (id_product) +) ENGINE=MyISAM; + +INSERT INTO t3 VALUES +(102),(103),(104),(105),(106),(107),(108),(109),(110), +(315371),(315373),(315374),(315375),(315376),(315377), +(315378),(315379),(315380); + +CREATE TABLE t4 ( + id_product int not null, + id_shop int, + PRIMARY KEY (id_product,id_shop) +) ENGINE=MyISAM ; + +INSERT INTO t4 VALUES +(202,1),(201,1),(200,1),(199,1),(198,1),(197,1),(196,1),(195,1), +(194,1),(193,1),(192,1),(191,1),(190,1),(189,1),(188,1),(187,1), +(186,1),(185,1),(184,1),(183,1),(182,1),(181,1),(179,1),(178,1), +(177,1),(176,1),(126,1),(315380,1); + +CREATE TABLE t5 (id_product int) ENGINE=MyISAM; +INSERT INTO `t5` VALUES +(652),(668),(669),(670),(671),(673),(674),(675),(676), +(677),(679),(680),(681),(682),(683),(684),(685),(686); + +explain +SELECT * FROM t3 + JOIN t4 ON (t4.id_product = t3.id_product AND t4.id_shop = 1) + JOIN t1 ON (t1.id_product = t3.id_product) +LEFT JOIN t5 ON (t5.id_product = t3.id_product) +WHERE 1=1 +AND t3.id_product IN (SELECT id_product FROM t2 t2_1 WHERE t2_1.id_t2 = 32) +AND t3.id_product IN (SELECT id_product FROM t2 t2_2 WHERE t2_2.id_t2 = 15) +AND t3.id_product IN (SELECT id_product FROM t2 t2_3 WHERE t2_3.id_t2 = 18 OR t2_3.id_t2 = 19) +AND t3.id_product IN (SELECT id_product FROM t2 t2_4 WHERE t2_4.id_t2 = 34 OR t2_4.id_t2 = 23) +AND t3.id_product IN (SELECT id_product FROM t2 t2_5 WHERE t2_5.id_t2 = 29 OR t2_5.id_t2 = 28 OR t2_5.id_t2 = 26); + +drop table t1,t2,t3,t4,t5; diff --cc scripts/mysql_install_db.sh index d04a6f497da,a8f32526a7e..5300036def5 --- a/scripts/mysql_install_db.sh +++ b/scripts/mysql_install_db.sh @@@ -208,11 -208,14 +208,16 @@@ cannot_find_file( fi echo - echo "If you compiled from source, you need to run 'make install' to" + echo "If you compiled from source, you need to either run 'make install' to" echo "copy the software into the correct location ready for operation." + echo "If you don't want to do a full install, you can use the --srcddir" + echo "option to only install the mysql database and privilege tables" echo + echo "If you compiled from source, you need to either run 'make install' to" + echo "copy the software into the correct location ready for operation." + echo "If you don't want to do a full install, you can use the --srcdir" + echo "option to only install the mysql database and privilege tables" + echo echo "If you are using a binary release, you must either be at the top" echo "level of the extracted archive, or pass the --basedir option" echo "pointing to that location."