diff options
author | Oleksandr Byelkin <sanja@mariadb.com> | 2019-06-12 22:54:46 +0200 |
---|---|---|
committer | Oleksandr Byelkin <sanja@mariadb.com> | 2019-06-12 22:54:46 +0200 |
commit | 5b65d61d9384a45ea1b8df79694493fbb1a14e4a (patch) | |
tree | 451255aa141e115baf97b867fb0752b8ce4dd9a9 | |
parent | 56c60b2fc5d006206f54b93be401570837f2f312 (diff) | |
parent | 7a7d9904e12335ee8b1eea9671138b3c469a3829 (diff) | |
download | mariadb-git-5b65d61d9384a45ea1b8df79694493fbb1a14e4a.tar.gz |
Merge branch '5.5' into 10.1
36 files changed, 775 insertions, 204 deletions
diff --git a/.clang-format b/.clang-format new file mode 100644 index 00000000000..3b735b16d74 --- /dev/null +++ b/.clang-format @@ -0,0 +1,18 @@ +SpaceBeforeAssignmentOperators: false +SpaceAfterCStyleCast: true + +BreakBeforeBraces: Custom +BraceWrapping: + AfterClass: true + AfterControlStatement: true + AfterEnum: true + AfterFunction: true + AfterNamespace: true + AfterStruct: true + AfterUnion: true + AfterExternBlock: true + BeforeCatch: true + BeforeElse: true + SplitEmptyFunction: true + SplitEmptyRecord: true + SplitEmptyNamespace: true diff --git a/include/my_base.h b/include/my_base.h index 5c785adce14..edd14e6a2f2 100644 --- a/include/my_base.h +++ b/include/my_base.h @@ -622,6 +622,7 @@ typedef ulong ha_rows; #define HA_POS_ERROR (~ (ha_rows) 0) #define HA_OFFSET_ERROR (~ (my_off_t) 0) +#define HA_ROWS_MAX HA_POS_ERROR #if SYSTEM_SIZEOF_OFF_T == 4 #define MAX_FILE_SIZE INT_MAX32 diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 6f786e34a9a..7c340925450 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -1,4 +1,3 @@ -drop table if exists t1,t2,t3; set @save_derived_optimizer_switch=@@optimizer_switch; set optimizer_switch='derived_merge=off,derived_with_keys=off'; select * from (select 2 from DUAL) b; diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index b1996262d49..f23f706be5d 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -2652,7 +2652,7 @@ DROP TABLE t1, t2; set optimizer_switch=@exit_optimizer_switch; set join_cache_level=@exit_join_cache_level; # -# Bug mdev-12812: EXPLAIN for query with many expensive derived +# Bug mdev-18479: EXPLAIN for query with many expensive derived # CREATE TABLE t1 (id int auto_increment primary key, @@ -2953,15 +2953,15 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY p10 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (incremental, BNL join) 1 PRIMARY <derived17> ALL NULL NULL NULL NULL 50328437500000 Using where; Using join buffer (incremental, BNL join) 1 PRIMARY <derived14> ALL NULL NULL NULL NULL 27680640625000000 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived7> ALL NULL NULL NULL NULL 7798774269472204800 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived8> ALL NULL NULL NULL NULL 7798774269472204800 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived9> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived10> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived11> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived12> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived13> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived15> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived16> ALL NULL NULL NULL NULL -3222391729959550976 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived9> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived10> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived11> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived12> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived13> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived15> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived16> ALL NULL NULL NULL NULL 15224352343750000640 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived7> ALL NULL NULL NULL NULL 18446744073709551615 Using where; Using join buffer (incremental, BNL join) +1 PRIMARY <derived8> ALL NULL NULL NULL NULL 18446744073709551615 Using where; Using join buffer (incremental, BNL join) 17 DERIVED t2 system NULL NULL NULL NULL 1 17 DERIVED p4 ALL NULL NULL NULL NULL 550 Using where 17 DERIVED p5 ALL NULL NULL NULL NULL 550 Using where; Using join buffer (flat, BNL join) diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index 8c529b27e92..4bf8d5b6ce4 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -1137,7 +1137,7 @@ SELECT 1 FROM v1 right join v1 AS v2 ON RAND(); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 select 1 AS `1` from `test`.`t1` left join `test`.`t1` `t2` on((1 = 1)) left join (`test`.`t1` left join `test`.`t1` `t2` on((1 = 1))) on(rand()) where 1 diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result index 6ddd39cbfec..d618f7c3ac8 100644 --- a/mysql-test/r/join_nested.result +++ b/mysql-test/r/join_nested.result @@ -1966,3 +1966,36 @@ Note 1003 select 1 AS `K1`,'T1Row1' AS `Name`,`t2a`.`K2` AS `K2`,`t2a`.`K1r` AS DROP VIEW v1; DROP TABLE t1,t2; set optimizer_search_depth= @tmp_mdev621; +# +# MDEV-19588: Nested left joins using optimized join cache +# +set optimizer_switch='optimize_join_buffer_size=on'; +set @save_join_cache_level= @@join_cache_level; +set join_cache_level=2; +CREATE TABLE t1 (i1 int, c1 varchar(20), pk int) engine=myisam; +CREATE TABLE t2 (pk int, c1 varchar(20), i1 int) engine=myisam; +INSERT INTO t2 VALUES (7,'a',-912),(8,'a',5); +CREATE TABLE t3 (pk int, c1 varchar(20), i1 int) engine=myisam; +INSERT INTO t3 VALUES +(1,'a',-145),(2,'a',6),(3,'a',1),(7,'a',NULL),(8,'a',889),(9,'a',146), +(10,'a',177),(16,'a',-433),(17,'a',NULL),(18,'a',2),(19,'a',3),(20,'a',5), +(21,'a',-484),(22,'a',369),(23,'a',-192),(24,'a',-163),(25,'a',5),(26,'a',NULL); +SELECT t3.* +FROM t3 LEFT JOIN t1 LEFT JOIN t2 ON t1.i1 = t2.i1 ON t3.i1 = t1.i1 +WHERE t2.pk < 13 OR t3.i1 IS NULL; +pk c1 i1 +7 a NULL +17 a NULL +26 a NULL +explain extended SELECT t3.* +FROM t3 LEFT JOIN t1 LEFT JOIN t2 ON t1.i1 = t2.i1 ON t3.i1 = t1.i1 +WHERE t2.pk < 13 OR t3.i1 IS NULL; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t3 ALL NULL NULL NULL NULL 18 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 0 0.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t3`.`pk` AS `pk`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`i1` AS `i1` from `test`.`t3` left join (`test`.`t1` left join `test`.`t2` on((`test`.`t2`.`i1` = `test`.`t3`.`i1`))) on((`test`.`t1`.`i1` = `test`.`t3`.`i1`)) where ((`test`.`t2`.`pk` < 13) or isnull(`test`.`t3`.`i1`)) +DROP TABLE t1,t2,t3; +set join_cache_level= @save_join_cache_level; +set optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/r/join_nested_jcl6.result b/mysql-test/r/join_nested_jcl6.result index 51073bf0f1d..dff9feda635 100644 --- a/mysql-test/r/join_nested_jcl6.result +++ b/mysql-test/r/join_nested_jcl6.result @@ -1977,6 +1977,39 @@ Note 1003 select 1 AS `K1`,'T1Row1' AS `Name`,`t2a`.`K2` AS `K2`,`t2a`.`K1r` AS DROP VIEW v1; DROP TABLE t1,t2; set optimizer_search_depth= @tmp_mdev621; +# +# MDEV-19588: Nested left joins using optimized join cache +# +set optimizer_switch='optimize_join_buffer_size=on'; +set @save_join_cache_level= @@join_cache_level; +set join_cache_level=2; +CREATE TABLE t1 (i1 int, c1 varchar(20), pk int) engine=myisam; +CREATE TABLE t2 (pk int, c1 varchar(20), i1 int) engine=myisam; +INSERT INTO t2 VALUES (7,'a',-912),(8,'a',5); +CREATE TABLE t3 (pk int, c1 varchar(20), i1 int) engine=myisam; +INSERT INTO t3 VALUES +(1,'a',-145),(2,'a',6),(3,'a',1),(7,'a',NULL),(8,'a',889),(9,'a',146), +(10,'a',177),(16,'a',-433),(17,'a',NULL),(18,'a',2),(19,'a',3),(20,'a',5), +(21,'a',-484),(22,'a',369),(23,'a',-192),(24,'a',-163),(25,'a',5),(26,'a',NULL); +SELECT t3.* +FROM t3 LEFT JOIN t1 LEFT JOIN t2 ON t1.i1 = t2.i1 ON t3.i1 = t1.i1 +WHERE t2.pk < 13 OR t3.i1 IS NULL; +pk c1 i1 +7 a NULL +17 a NULL +26 a NULL +explain extended SELECT t3.* +FROM t3 LEFT JOIN t1 LEFT JOIN t2 ON t1.i1 = t2.i1 ON t3.i1 = t1.i1 +WHERE t2.pk < 13 OR t3.i1 IS NULL; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t3 ALL NULL NULL NULL NULL 18 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 0 0.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t3`.`pk` AS `pk`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`i1` AS `i1` from `test`.`t3` left join (`test`.`t1` left join `test`.`t2` on((`test`.`t2`.`i1` = `test`.`t3`.`i1`))) on((`test`.`t1`.`i1` = `test`.`t3`.`i1`)) where ((`test`.`t2`.`pk` < 13) or isnull(`test`.`t3`.`i1`)) +DROP TABLE t1,t2,t3; +set join_cache_level= @save_join_cache_level; +set optimizer_switch=@save_optimizer_switch; CREATE TABLE t5 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b)); CREATE TABLE t6 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b)); CREATE TABLE t7 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b)); diff --git a/mysql-test/r/mdev13607.result b/mysql-test/r/mdev13607.result index 08848bc645b..f5edfa6a8d6 100644 --- a/mysql-test/r/mdev13607.result +++ b/mysql-test/r/mdev13607.result @@ -76,21 +76,21 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY p7 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) 1 PRIMARY p8 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) 1 PRIMARY p9 ALL NULL NULL NULL NULL 50 Using where; Using join buffer (incremental, BNL join) -1 PRIMARY <derived3> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived4> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived5> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived6> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived7> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived8> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived9> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived10> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived11> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived12> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived13> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived14> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived15> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived16> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) -1 PRIMARY <derived17> ALL NULL NULL NULL NULL -1127208515966861312 Using join buffer (incremental, BNL join) +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 18446744073709551615 Using join buffer (incremental, BNL join) +1 PRIMARY <derived4> ALL NULL NULL NULL NULL 18446744073709551615 Using join buffer (incremental, BNL join) +1 PRIMARY <derived5> ALL NULL NULL NULL NULL 18446744073709551615 Using join buffer (incremental, BNL join) +1 PRIMARY <derived6> ALL NULL NULL NULL NULL 18446744073709551615 Using join buffer (incremental, BNL join) +1 PRIMARY <derived7> ALL NULL NULL NULL NULL 18446744073709551615 Using join buffer (incremental, BNL join) +1 PRIMARY <derived8> ALL NULL NULL NULL NULL 18446744073709551615 Using join buffer (incremental, BNL join) +1 PRIMARY <derived9> ALL NULL NULL NULL NULL 18446744073709551615 Using join buffer (incremental, BNL join) +1 PRIMARY <derived10> ALL NULL NULL NULL NULL 18446744073709551615 Using join buffer (incremental, BNL join) +1 PRIMARY <derived11> ALL NULL NULL NULL NULL 18446744073709551615 Using join buffer (incremental, BNL join) +1 PRIMARY <derived12> ALL NULL NULL NULL NULL 18446744073709551615 Using join buffer (incremental, BNL join) +1 PRIMARY <derived13> ALL NULL NULL NULL NULL 18446744073709551615 Using join buffer (incremental, BNL join) +1 PRIMARY <derived14> ALL NULL NULL NULL NULL 18446744073709551615 Using join buffer (incremental, BNL join) +1 PRIMARY <derived15> ALL NULL NULL NULL NULL 18446744073709551615 Using join buffer (incremental, BNL join) +1 PRIMARY <derived16> ALL NULL NULL NULL NULL 18446744073709551615 Using join buffer (incremental, BNL join) +1 PRIMARY <derived17> ALL NULL NULL NULL NULL 18446744073709551615 Using join buffer (incremental, BNL join) 17 DERIVED r1 ALL NULL NULL NULL NULL 2 17 DERIVED d1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) 17 DERIVED r2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (incremental, BNL join) diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result index f614ea1e497..ec8ba1c53d3 100644 --- a/mysql-test/r/multi_update.result +++ b/mysql-test/r/multi_update.result @@ -1039,7 +1039,27 @@ triggered triggered drop table t1,t2, t3; drop user foo; -end of 5.5 tests +create table t1 (a int, b int); +create table t2 (c int, d int); +insert t1 values (1,2),(3,4); +insert t2 values (5,6),(7,8); +create table t0 (x int); +insert t0 values (11), (22); +create trigger tr1 before update on t2 for each row insert t0 values (new.c); +lock table t0 write; +update t1 join t2 on (a=c+4) set b=d; +drop table t1, t2, t0; +create table t1 (a int, b varchar(50), c varchar(50)); +insert t1 (a,b) values (1,'1'), (2,'2'), (3,'3'); +create function f1() returns varchar(50) return 'result'; +create trigger tr before update on t1 for each row set new.c = (select f1()); +create table t2 select a, b from t1; +update t1 join t2 using (a) set t1.b = t2.b; +drop table t1, t2; +drop function f1; +# +# end of 5.5 tests +# # Bug mdev-5970 # Bug#13256831 - ERROR 1032 (HY000): CAN'T FIND RECORD diff --git a/mysql-test/r/multi_update_debug.result b/mysql-test/r/multi_update_debug.result new file mode 100644 index 00000000000..8bcd813e5b7 --- /dev/null +++ b/mysql-test/r/multi_update_debug.result @@ -0,0 +1,13 @@ +create table t1 (a int, b int); +create table t2 (c int, d int); +insert t1 values (1,2),(3,4); +insert t2 values (5,6),(7,8); +create table t0 (x int); +insert t0 values (11), (22); +create trigger tr1 before update on t1 for each row insert t0 values (new.b); +set debug_sync='open_tables_after_open_and_process_table WAIT_FOR cont'; +update t1 join t2 on (a=c+4) set b=d; +set debug_sync='mdl_acquire_lock_wait SIGNAL cont'; +lock table t1 write, t0 write; +drop table t1, t2, t0; +set debug_sync='reset'; diff --git a/mysql-test/r/multi_update_innodb.result b/mysql-test/r/multi_update_innodb.result index 643287c3a93..5da8cf46f5a 100644 --- a/mysql-test/r/multi_update_innodb.result +++ b/mysql-test/r/multi_update_innodb.result @@ -67,3 +67,20 @@ SELECT * FROM t2; col_int_key pk_1 pk_2 col_int 1 2 3 4 DROP TABLE t1,t2; +create table t1 (id serial, size int(11)) engine=innodb; +create table t2 (id serial, size int, account_id int) engine=innodb; +create table t3 (id serial, size int, article_id int) engine=innodb; +create table t4 (id serial, file_id int, article_id int) engine=innodb; +insert t1 values(null, 400); +insert t2 values(null, 0, 1), (null, 1, 1); +insert t3 values(null, 100, 1); +insert t4 values(null, 1, 2); +create trigger file_update_article before update on t3 for each row +update t2 set t2.size = new.size where t2.id = new.article_id; +create trigger article_update_account before update on t2 for each row +update t1 set t1.size = t1.size + new.size where t1.id = new.account_id; +update t3 join t4 on t4.file_id =t3.id and t4.article_id=2 set t3.size=t3.size + 2; +drop table t1, t2, t3, t4; +# +# end of 5.5 tests +# diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 69db0959373..06dddcc2ac9 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -6969,7 +6969,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index 6fae057dd17..3a844fcfc9b 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -2484,6 +2484,95 @@ SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); 1 drop table t1,t2; drop view v1; +# +# MDEV-19580: function invocation in the left part of IN subquery +# +create table t1 (id int, a varchar(50), b int); +insert into t1 values +(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1); +create table t2 (id int, a varchar(50), x int); +insert into t2 values +(1,'grand',1),(2,'average',1),(3,'serf',0); +create table t3 (d1 date, d2 date, t1_id int, t2_id int ); +insert into t3 values +('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1), +('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3); +create table t4 ( id int, a varchar(50) ); +insert into t4 values +(1,'songwriter'),(2,'song character'); +create function f1(who int, dt date) returns int +deterministic +begin +declare result int; +select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; +return result; +end$$ +create function f2(who int, dt date) returns int +begin +declare result int; +select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; +return result; +end$$ +# Deterministic function in left part of IN subquery: semi-join is OK +select * from t1 +left join t4 on t1.b = t4.id +where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a +3 paul 1 1 songwriter +4 art 1 1 songwriter +1 mrs 2 2 song character +explain extended select * from t1 +left join t4 on t1.b = t4.id +where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f1`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`)) +# Non-deterministic function in left part of IN subq: semi-join is OK +select * from t1 +left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a +3 paul 1 1 songwriter +4 art 1 1 songwriter +1 mrs 2 2 song character +explain extended select * from t1 +left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`)) +select t1.*, t4.*, +(select max(t4.id) from t4 where t4.id=t1.b and sleep(0) = 0) as s +from t1 left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a s +3 paul 1 1 songwriter 1 +4 art 1 1 songwriter 1 +1 mrs 2 2 song character 2 +explain extended select t1.*, t4.*, +(select max(t4.id) from t4 where t4.id=t1.b and sleep(0) = 0) as s +from t1 left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1276 Field or reference 'test.t1.b' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a`,(select max(`test`.`t4`.`id`) from `test`.`t4` where ((`test`.`t4`.`id` = `test`.`t1`.`b`) and (sleep(0) = 0))) AS `s` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`)) +drop function f1; +drop function f2; +drop table t1,t2,t3,t4; # End of 5.5 tests # # MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result index ef3bf7ef692..75759c0b9ca 100644 --- a/mysql-test/r/subselect_no_exists_to_in.result +++ b/mysql-test/r/subselect_no_exists_to_in.result @@ -6969,7 +6969,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 703572e0ef8..b1855825de3 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -6963,7 +6963,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 39a098b754b..bbbb082c596 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -6960,7 +6960,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index b9a59bf3952..f2b2f771569 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -6975,7 +6975,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index f1df85fed6e..012d7605cdc 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -6960,7 +6960,7 @@ WHERE SLEEP(0.1) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where -1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index +1 PRIMARY t3 ref d d 5 test.t2.b 2 Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @tmp_mdev410=@@global.userstat; set global userstat=on; diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index 3cb3750cbd6..4038256ad49 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -2524,6 +2524,95 @@ SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); 1 drop table t1,t2; drop view v1; +# +# MDEV-19580: function invocation in the left part of IN subquery +# +create table t1 (id int, a varchar(50), b int); +insert into t1 values +(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1); +create table t2 (id int, a varchar(50), x int); +insert into t2 values +(1,'grand',1),(2,'average',1),(3,'serf',0); +create table t3 (d1 date, d2 date, t1_id int, t2_id int ); +insert into t3 values +('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1), +('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3); +create table t4 ( id int, a varchar(50) ); +insert into t4 values +(1,'songwriter'),(2,'song character'); +create function f1(who int, dt date) returns int +deterministic +begin +declare result int; +select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; +return result; +end$$ +create function f2(who int, dt date) returns int +begin +declare result int; +select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; +return result; +end$$ +# Deterministic function in left part of IN subquery: semi-join is OK +select * from t1 +left join t4 on t1.b = t4.id +where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a +3 paul 1 1 songwriter +4 art 1 1 songwriter +1 mrs 2 2 song character +explain extended select * from t1 +left join t4 on t1.b = t4.id +where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f1`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`)) +# Non-deterministic function in left part of IN subq: semi-join is OK +select * from t1 +left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a +3 paul 1 1 songwriter +4 art 1 1 songwriter +1 mrs 2 2 song character +explain extended select * from t1 +left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`)) +select t1.*, t4.*, +(select max(t4.id) from t4 where t4.id=t1.b and sleep(0) = 0) as s +from t1 left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id a b id a s +3 paul 1 1 songwriter 1 +4 art 1 1 songwriter 1 +1 mrs 2 2 song character 2 +explain extended select t1.*, t4.*, +(select max(t4.id) from t4 where t4.id=t1.b and sleep(0) = 0) as s +from t1 left join t4 on t1.b = t4.id +where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1276 Field or reference 'test.t1.b' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t4`.`id` AS `id`,`test`.`t4`.`a` AS `a`,(select max(`test`.`t4`.`id`) from `test`.`t4` where ((`test`.`t4`.`id` = `test`.`t1`.`b`) and (sleep(0) = 0))) AS `s` from `test`.`t1` semi join (`test`.`t2`) left join `test`.`t4` on((`test`.`t4`.`id` = `test`.`t1`.`b`)) where ((`test`.`t2`.`x` = 1) and (`f2`(`test`.`t1`.`id`,'1980-01-01') = `test`.`t2`.`id`)) +drop function f1; +drop function f2; +drop table t1,t2,t3,t4; # End of 5.5 tests # # MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index 778d141b80f..c63eee546dc 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -1,7 +1,4 @@ # Initialize ---disable_warnings -drop table if exists t1,t2,t3; ---enable_warnings set @save_derived_optimizer_switch=@@optimizer_switch; set optimizer_switch='derived_merge=off,derived_with_keys=off'; diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test index 2c81ba2c49f..ed9fb824940 100644 --- a/mysql-test/t/derived_view.test +++ b/mysql-test/t/derived_view.test @@ -1936,7 +1936,7 @@ set optimizer_switch=@exit_optimizer_switch; set join_cache_level=@exit_join_cache_level; --echo # ---echo # Bug mdev-12812: EXPLAIN for query with many expensive derived +--echo # Bug mdev-18479: EXPLAIN for query with many expensive derived --echo # CREATE TABLE t1 diff --git a/mysql-test/t/join_nested.test b/mysql-test/t/join_nested.test index e60b7827f75..cfb24a63304 100644 --- a/mysql-test/t/join_nested.test +++ b/mysql-test/t/join_nested.test @@ -1380,3 +1380,37 @@ DROP VIEW v1; DROP TABLE t1,t2; set optimizer_search_depth= @tmp_mdev621; + +--echo # +--echo # MDEV-19588: Nested left joins using optimized join cache +--echo # + +set optimizer_switch='optimize_join_buffer_size=on'; + +set @save_join_cache_level= @@join_cache_level; +set join_cache_level=2; + +CREATE TABLE t1 (i1 int, c1 varchar(20), pk int) engine=myisam; + +CREATE TABLE t2 (pk int, c1 varchar(20), i1 int) engine=myisam; +INSERT INTO t2 VALUES (7,'a',-912),(8,'a',5); + +CREATE TABLE t3 (pk int, c1 varchar(20), i1 int) engine=myisam; +INSERT INTO t3 VALUES +(1,'a',-145),(2,'a',6),(3,'a',1),(7,'a',NULL),(8,'a',889),(9,'a',146), +(10,'a',177),(16,'a',-433),(17,'a',NULL),(18,'a',2),(19,'a',3),(20,'a',5), +(21,'a',-484),(22,'a',369),(23,'a',-192),(24,'a',-163),(25,'a',5),(26,'a',NULL); + +let $q= +SELECT t3.* +FROM t3 LEFT JOIN t1 LEFT JOIN t2 ON t1.i1 = t2.i1 ON t3.i1 = t1.i1 +WHERE t2.pk < 13 OR t3.i1 IS NULL; + +eval $q; +eval explain extended $q; + +DROP TABLE t1,t2,t3; + +set join_cache_level= @save_join_cache_level; + +set optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index 8399c465562..146277b2985 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -371,7 +371,6 @@ drop table t1, t2; # connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); -connection root; --disable_warnings create database mysqltest; --enable_warnings @@ -382,7 +381,6 @@ create user mysqltest_1@localhost; grant select on mysqltest.* to mysqltest_1@localhost; grant update on mysqltest.t1 to mysqltest_1@localhost; connect (user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK); -connection user1; update t1, t2 set t1.b=1 where t1.a=t2.a; update t1, t2 set t1.b=(select t3.b from t3 where t1.a=t3.a) where t1.a=t2.a; connection root; @@ -495,15 +493,12 @@ insert into t2 values (10), (20), (30); create view v1 as select a as b, a/10 as a from t2; connect (locker,localhost,root,,test); -connection locker; lock table t1 write; connect (changer,localhost,root,,test); -connection changer; send alter table t1 add column c int default 100 after a; connect (updater,localhost,root,,test); -connection updater; # Wait till "alter table t1 ..." of session changer is in work. # = There is one session waiting. let $wait_condition= select count(*)= 1 from information_schema.processlist @@ -1087,7 +1082,38 @@ select * from t2; drop table t1,t2, t3; drop user foo; ---echo end of 5.5 tests +# +# Another test on not-opening tables unnecessary +# +create table t1 (a int, b int); +create table t2 (c int, d int); +insert t1 values (1,2),(3,4); +insert t2 values (5,6),(7,8); +create table t0 (x int); +insert t0 values (11), (22); +create trigger tr1 before update on t2 for each row insert t0 values (new.c); +connect con1, localhost, root; +lock table t0 write; +connection default; +update t1 join t2 on (a=c+4) set b=d; +disconnect con1; +drop table t1, t2, t0; + +# +# MDEV-19521 Update Table Fails with Trigger and Stored Function +# +create table t1 (a int, b varchar(50), c varchar(50)); +insert t1 (a,b) values (1,'1'), (2,'2'), (3,'3'); +create function f1() returns varchar(50) return 'result'; +create trigger tr before update on t1 for each row set new.c = (select f1()); +create table t2 select a, b from t1; +update t1 join t2 using (a) set t1.b = t2.b; +drop table t1, t2; +drop function f1; + +--echo # +--echo # end of 5.5 tests +--echo # --source include/have_xtradb.inc diff --git a/mysql-test/t/multi_update_debug.test b/mysql-test/t/multi_update_debug.test new file mode 100644 index 00000000000..2da376e1b87 --- /dev/null +++ b/mysql-test/t/multi_update_debug.test @@ -0,0 +1,27 @@ +# +# test MDL backoff-and-retry during multi-update +# +source include/have_debug_sync.inc; +create table t1 (a int, b int); +create table t2 (c int, d int); +insert t1 values (1,2),(3,4); +insert t2 values (5,6),(7,8); +create table t0 (x int); +insert t0 values (11), (22); +create trigger tr1 before update on t1 for each row insert t0 values (new.b); + +set debug_sync='open_tables_after_open_and_process_table WAIT_FOR cont'; +send update t1 join t2 on (a=c+4) set b=d; + +connect con1, localhost, root; +let $wait_condition= select count(*) from information_schema.processlist where state = ' debug sync point: open_tables_after_open_and_process_table' +source include/wait_condition.inc; +set debug_sync='mdl_acquire_lock_wait SIGNAL cont'; +lock table t1 write, t0 write; +let $wait_condition= select count(*) from information_schema.processlist where state = 'Waiting for table metadata lock' +source include/wait_condition.inc; +disconnect con1; +connection default; +reap; +drop table t1, t2, t0; +set debug_sync='reset'; diff --git a/mysql-test/t/multi_update_innodb.test b/mysql-test/t/multi_update_innodb.test index 51757c29553..9042f267c66 100644 --- a/mysql-test/t/multi_update_innodb.test +++ b/mysql-test/t/multi_update_innodb.test @@ -75,3 +75,25 @@ UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_2=10,B.pk_2=11; SELECT * FROM t2; DROP TABLE t1,t2; + +# +# MDEV-19491 update query stopped working after mariadb upgrade 10.2.23 -> 10.2.24 +# +create table t1 (id serial, size int(11)) engine=innodb; +create table t2 (id serial, size int, account_id int) engine=innodb; +create table t3 (id serial, size int, article_id int) engine=innodb; +create table t4 (id serial, file_id int, article_id int) engine=innodb; +insert t1 values(null, 400); +insert t2 values(null, 0, 1), (null, 1, 1); +insert t3 values(null, 100, 1); +insert t4 values(null, 1, 2); +create trigger file_update_article before update on t3 for each row + update t2 set t2.size = new.size where t2.id = new.article_id; +create trigger article_update_account before update on t2 for each row + update t1 set t1.size = t1.size + new.size where t1.id = new.account_id; +update t3 join t4 on t4.file_id =t3.id and t4.article_id=2 set t3.size=t3.size + 2; +drop table t1, t2, t3, t4; + +--echo # +--echo # end of 5.5 tests +--echo # diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index 1f514be23d2..f6862057769 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -2238,6 +2238,81 @@ explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); drop table t1,t2; drop view v1; + + +--echo # +--echo # MDEV-19580: function invocation in the left part of IN subquery +--echo # + +create table t1 (id int, a varchar(50), b int); +insert into t1 values +(1,'mrs',2), (2,'joe',2), (3,'paul',1), (4,'art',1); + +create table t2 (id int, a varchar(50), x int); +insert into t2 values +(1,'grand',1),(2,'average',1),(3,'serf',0); + +create table t3 (d1 date, d2 date, t1_id int, t2_id int ); +insert into t3 values +('1972-01-01','1988-12-31',3,1), ('1972-01-01','1988-12-31',4,1), +('1972-01-01','1988-12-31',1,2), ('1972-01-01','1988-12-31',2,3); + +create table t4 ( id int, a varchar(50) ); +insert into t4 values +(1,'songwriter'),(2,'song character'); + +delimiter $$; + +create function f1(who int, dt date) returns int +deterministic +begin + declare result int; + select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; + return result; +end$$ + +create function f2(who int, dt date) returns int +begin + declare result int; + select t2_id into result from t3 where dt>=d1 and dt<=d2 and t1_id=who; + return result; +end$$ + +delimiter ;$$ + +--echo # Deterministic function in left part of IN subquery: semi-join is OK + +let $q1= +select * from t1 + left join t4 on t1.b = t4.id + where f1(t1.id, '1980-01-01') in (select id from t2 where x=1); + +eval $q1; +eval explain extended $q1; + +--echo # Non-deterministic function in left part of IN subq: semi-join is OK + +let $q2= +select * from t1 + left join t4 on t1.b = t4.id + where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); + +eval $q2; +eval explain extended $q2; + +let $q3= +select t1.*, t4.*, + (select max(t4.id) from t4 where t4.id=t1.b and sleep(0) = 0) as s + from t1 left join t4 on t1.b = t4.id + where f2(t1.id, '1980-01-01') in (select id from t2 where x=1); + +eval $q3; +eval explain extended $q3; + +drop function f1; +drop function f2; +drop table t1,t2,t3,t4; + --echo # End of 5.5 tests --echo # --echo # MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT diff --git a/sql/my_json_writer.cc b/sql/my_json_writer.cc index 72b000df639..22c66ba7a34 100644 --- a/sql/my_json_writer.cc +++ b/sql/my_json_writer.cc @@ -129,6 +129,13 @@ void Json_writer::add_ll(longlong val) add_unquoted_str(buf); } +void Json_writer::add_ull(ulonglong val) +{ + char buf[64]; + my_snprintf(buf, sizeof(buf), "%llu", val); + add_unquoted_str(buf); +} + /* Add a memory size, printing in Kb, Kb, Gb if necessary */ void Json_writer::add_size(longlong val) diff --git a/sql/my_json_writer.h b/sql/my_json_writer.h index 349a1f380da..ffee6db4c03 100644 --- a/sql/my_json_writer.h +++ b/sql/my_json_writer.h @@ -108,6 +108,7 @@ public: void add_str(const String &str); void add_ll(longlong val); + void add_ull(ulonglong val); void add_size(longlong val); void add_double(double val); void add_bool(bool val); diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 043be9b9a96..d49fbcddbf9 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -4531,8 +4531,7 @@ open_tables_check_upgradable_mdl(THD *thd, TABLE_LIST *tables_start, */ bool open_tables(THD *thd, const DDL_options_st &options, - TABLE_LIST **start, uint *counter, - Sroutine_hash_entry **sroutine_to_open_list, uint flags, + TABLE_LIST **start, uint *counter, uint flags, Prelocking_strategy *prelocking_strategy) { /* @@ -4575,9 +4574,10 @@ restart: has_prelocking_list= thd->lex->requires_prelocking(); table_to_open= start; - sroutine_to_open= sroutine_to_open_list; + sroutine_to_open= &thd->lex->sroutines_list.first; *counter= 0; THD_STAGE_INFO(thd, stage_opening_tables); + prelocking_strategy->reset(thd); /* If we are executing LOCK TABLES statement or a DDL statement @@ -4635,8 +4635,7 @@ restart: elements in prelocking list/set. */ while (*table_to_open || - (thd->locked_tables_mode <= LTM_LOCK_TABLES && - *sroutine_to_open)) + (thd->locked_tables_mode <= LTM_LOCK_TABLES && *sroutine_to_open)) { /* For every table in the list of tables to open, try to find or open @@ -4756,6 +4755,8 @@ restart: } } } + if ((error= prelocking_strategy->handle_end(thd))) + goto error; } /* diff --git a/sql/sql_base.h b/sql/sql_base.h index 445d537ba36..91eef7369ac 100644 --- a/sql/sql_base.h +++ b/sql/sql_base.h @@ -252,17 +252,7 @@ lock_table_names(THD *thd, TABLE_LIST *table_list, } bool open_tables(THD *thd, const DDL_options_st &options, TABLE_LIST **tables, uint *counter, - Sroutine_hash_entry **sroutine_to_open, uint flags, - Prelocking_strategy *prelocking_strategy); - -static inline bool -open_tables(THD *thd, const DDL_options_st &options, TABLE_LIST **tables, - uint *counter, uint flags, Prelocking_strategy *prelocking_strategy) -{ - return open_tables(thd, options, tables, counter, - &thd->lex->sroutines_list.first, flags, - prelocking_strategy); -} + uint flags, Prelocking_strategy *prelocking_strategy); static inline bool open_tables(THD *thd, TABLE_LIST **tables, uint *counter, uint flags, @@ -442,6 +432,7 @@ class Prelocking_strategy public: virtual ~Prelocking_strategy() { } + virtual void reset(THD *thd) { }; virtual bool handle_routine(THD *thd, Query_tables_list *prelocking_ctx, Sroutine_hash_entry *rt, sp_head *sp, bool *need_prelocking) = 0; @@ -449,6 +440,7 @@ public: TABLE_LIST *table_list, bool *need_prelocking) = 0; virtual bool handle_view(THD *thd, Query_tables_list *prelocking_ctx, TABLE_LIST *table_list, bool *need_prelocking)= 0; + virtual bool handle_end(THD *thd) { return 0; }; }; diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc index c8576136069..2fa4b2cb3ae 100644 --- a/sql/sql_explain.cc +++ b/sql/sql_explain.cc @@ -1223,7 +1223,7 @@ int Explain_table_access::print_explain(select_result_sink *output, uint8 explai if (rows_set) { item_list.push_back(new (mem_root) - Item_int(thd, (longlong) (ulonglong) rows, + Item_int(thd, (ulonglong) rows, MY_INT64_NUM_DECIMAL_DIGITS), mem_root); } @@ -1601,7 +1601,7 @@ void Explain_table_access::print_explain_json(Explain_query *query, /* `rows` */ if (rows_set) - writer->add_member("rows").add_ll(rows); + writer->add_member("rows").add_ull(rows); /* `r_rows` */ if (is_analyze) @@ -2239,7 +2239,7 @@ void Explain_update::print_explain_json(Explain_query *query, } /* `rows` */ - writer->add_member("rows").add_ll(rows); + writer->add_member("rows").add_ull(rows); if (mrr_type.length() != 0) @@ -2268,7 +2268,7 @@ void Explain_update::print_explain_json(Explain_query *query, r_rows= 0; r_filtered= buf_tracker.get_filtered_after_where() * 100.0; } - writer->add_member("r_rows").add_ll(r_rows); + writer->add_member("r_rows").add_ull(r_rows); writer->add_member("r_filtered").add_double(r_filtered); } else /* Not doing buffering */ diff --git a/sql/sql_join_cache.cc b/sql/sql_join_cache.cc index 336b1800042..f7366a64e60 100644 --- a/sql/sql_join_cache.cc +++ b/sql/sql_join_cache.cc @@ -2156,7 +2156,8 @@ enum_nested_loop_state JOIN_CACHE::join_records(bool skip_last) } finish: - if (outer_join_first_inner) + if (outer_join_first_inner && + join_tab->first_inner == join_tab->first_unmatched) { /* All null complemented rows have been already generated for all diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 45e62733698..64406f54005 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -4372,7 +4372,10 @@ void SELECT_LEX::increase_derived_records(ha_rows records) DBUG_ASSERT(unit->derived); select_union *result= (select_union*)unit->result; - result->records+= records; + if (HA_ROWS_MAX - records > result->records) + result->records+= records; + else + result->records= HA_ROWS_MAX; } diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 22ea300a79a..954080cbc11 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -218,7 +218,8 @@ static COND *make_cond_for_table_from_pred(THD *thd, Item *root_cond, table_map used_table, int join_tab_idx_arg, bool exclude_expensive_cond, - bool retain_ref_cond); + bool retain_ref_cond, + bool is_top_and_level); static Item* part_of_refkey(TABLE *form,Field *field); uint find_shortest_key(TABLE *table, const key_map *usable_keys); @@ -4238,7 +4239,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, DBUG_RETURN(TRUE); /* purecov: inspected */ { - ha_rows records= 1; + double records= 1; SELECT_LEX_UNIT *unit= join->select_lex->master_unit(); /* Find an optimal join order of the non-constant tables. */ @@ -4263,10 +4264,11 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, table/view. */ for (i= 0; i < join->table_count ; i++) - records*= join->best_positions[i].records_read ? - (ha_rows)join->best_positions[i].records_read : 1; - set_if_smaller(records, unit->select_limit_cnt); - join->select_lex->increase_derived_records(records); + if (double rr= join->best_positions[i].records_read) + records= COST_MULT(records, rr); + ha_rows rows= records > HA_ROWS_MAX ? HA_ROWS_MAX : (ha_rows) records; + set_if_smaller(rows, unit->select_limit_cnt); + join->select_lex->increase_derived_records(rows); } } @@ -7392,18 +7394,23 @@ double JOIN::get_examined_rows() { double examined_rows; double prev_fanout= 1; + double records; JOIN_TAB *tab= first_breadth_first_optimization_tab(); JOIN_TAB *prev_tab= tab; - examined_rows= tab->get_examined_rows(); + records= tab->get_examined_rows(); while ((tab= next_breadth_first_tab(first_breadth_first_optimization_tab(), top_table_access_tabs_count, tab))) { - prev_fanout *= prev_tab->records_read; - examined_rows+= tab->get_examined_rows() * prev_fanout; + prev_fanout= COST_MULT(prev_fanout, prev_tab->records_read); + records= + COST_ADD(records, + COST_MULT((double) (tab->get_examined_rows()), prev_fanout)); prev_tab= tab; } + examined_rows= + records > (double) HA_ROWS_MAX ? HA_ROWS_MAX : (ha_rows) records; return examined_rows; } @@ -9853,12 +9860,6 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) RAND_TABLE_BIT; } - /* - Following force including random expression in last table condition. - It solve problem with select like SELECT * FROM t1 WHERE rand() > 0.5 - */ - if (tab == join->join_tab + last_top_base_tab_idx) - current_map|= RAND_TABLE_BIT; used_tables|=current_map; if (tab->type == JT_REF && tab->quick && @@ -9900,6 +9901,20 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) { tmp= make_cond_for_table(thd, cond, used_tables, current_map, i, FALSE, FALSE); + if (tab == join->join_tab + last_top_base_tab_idx) + { + /* + This pushes conjunctive conditions of WHERE condition such that: + - their used_tables() contain RAND_TABLE_BIT + - the conditions does not refer to any fields + (such like rand() > 0.5) + */ + table_map rand_table_bit= (table_map) RAND_TABLE_BIT; + COND *rand_cond= make_cond_for_table(thd, cond, used_tables, + rand_table_bit, -1, + FALSE, FALSE); + add_cond_and_fix(thd, &tmp, rand_cond); + } } /* Add conditions added by add_not_null_conds(). */ if (tab->select_cond) @@ -10235,6 +10250,21 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) current_map, /*(inner_tab - first_tab)*/ -1, FALSE, FALSE); + if (tab == last_tab) + { + /* + This pushes conjunctive conditions of ON expression of an outer + join such that: + - their used_tables() contain RAND_TABLE_BIT + - the conditions does not refer to any fields + (such like rand() > 0.5) + */ + table_map rand_table_bit= (table_map) RAND_TABLE_BIT; + COND *rand_cond= make_cond_for_table(thd, on_expr, used_tables2, + rand_table_bit, -1, + FALSE, FALSE); + add_cond_and_fix(thd, &tmp_cond, rand_cond); + } bool is_sjm_lookup_tab= FALSE; if (inner_tab->bush_children) { @@ -11848,6 +11878,8 @@ ha_rows JOIN_TAB::get_examined_rows() else examined_rows= records_read; + if (examined_rows >= (double) HA_ROWS_MAX) + return HA_ROWS_MAX; return (ha_rows) examined_rows; } @@ -20275,7 +20307,7 @@ make_cond_for_table(THD *thd, Item *cond, table_map tables, return make_cond_for_table_from_pred(thd, cond, cond, tables, used_table, join_tab_idx_arg, exclude_expensive_cond, - retain_ref_cond); + retain_ref_cond, true); } @@ -20285,9 +20317,12 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, int join_tab_idx_arg, bool exclude_expensive_cond __attribute__ ((unused)), - bool retain_ref_cond) + bool retain_ref_cond, + bool is_top_and_level) { + table_map rand_table_bit= (table_map) RAND_TABLE_BIT; + if (used_table && !(cond->used_tables() & used_table)) return (COND*) 0; // Already checked @@ -20303,11 +20338,28 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, Item *item; while ((item=li++)) { + /* + Special handling of top level conjuncts with RAND_TABLE_BIT: + if such a conjunct contains a reference to a field that is not + an outer field then it is pushed to the corresponding table by + the same rule as all other conjuncts. Otherwise, if the conjunct + is used in WHERE is is pushed to the last joined table, if is it + is used in ON condition of an outer join it is pushed into the + last inner table of the outer join. Such conjuncts are pushed in + a call of make_cond_for_table_from_pred() with the + parameter 'used_table' equal to PSEUDO_TABLE_BITS. + */ + if (is_top_and_level && used_table == rand_table_bit && + (item->used_tables() & ~OUTER_REF_TABLE_BIT) != rand_table_bit) + { + /* The conjunct with RAND_TABLE_BIT has been allready pushed */ + continue; + } Item *fix=make_cond_for_table_from_pred(thd, root_cond, item, tables, used_table, - join_tab_idx_arg, + join_tab_idx_arg, exclude_expensive_cond, - retain_ref_cond); + retain_ref_cond, false); if (fix) new_cond->argument_list()->push_back(fix, thd->mem_root); } @@ -20331,6 +20383,13 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, } else { // Or list + if (is_top_and_level && used_table == rand_table_bit && + (cond->used_tables() & ~OUTER_REF_TABLE_BIT) != rand_table_bit) + { + /* This top level formula with RAND_TABLE_BIT has been already pushed */ + return (COND*) 0; + } + Item_cond_or *new_cond=new (thd->mem_root) Item_cond_or(thd); if (!new_cond) return (COND*) 0; // OOM /* purecov: inspected */ @@ -20342,7 +20401,7 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, tables, 0L, join_tab_idx_arg, exclude_expensive_cond, - retain_ref_cond); + retain_ref_cond, false); if (!fix) return (COND*) 0; // Always true new_cond->argument_list()->push_back(fix, thd->mem_root); @@ -20359,6 +20418,13 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, } } + if (is_top_and_level && used_table == rand_table_bit && + (cond->used_tables() & ~OUTER_REF_TABLE_BIT) != rand_table_bit) + { + /* This top level formula with RAND_TABLE_BIT has been already pushed */ + return (COND*) 0; + } + /* Because the following test takes a while and it can be done table_count times, we mark each item that we have examined with the result @@ -24485,10 +24551,10 @@ void JOIN_TAB::save_explain_data(Explain_table_access *eta, } else { - double examined_rows= get_examined_rows(); + ha_rows examined_rows= get_examined_rows(); eta->rows_set= true; - eta->rows= (ha_rows) examined_rows; + eta->rows= examined_rows; /* "filtered" */ float f= 0.0; diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 009d8ae2658..1360bf9fb74 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -1364,106 +1364,81 @@ static bool multi_update_check_table_access(THD *thd, TABLE_LIST *table, } -/* - make update specific preparation and checks after opening tables +class Multiupdate_prelocking_strategy : public DML_prelocking_strategy +{ + bool done; + bool has_prelocking_list; +public: + void reset(THD *thd); + bool handle_end(THD *thd); +}; + +void Multiupdate_prelocking_strategy::reset(THD *thd) +{ + done= false; + has_prelocking_list= thd->lex->requires_prelocking(); +} - SYNOPSIS - mysql_multi_update_prepare() - thd thread handler +/** + Determine what tables could be updated in the multi-update - RETURN - FALSE OK - TRUE Error + For these tables we'll need to open triggers and continue prelocking + until all is open. */ - -int mysql_multi_update_prepare(THD *thd) +bool Multiupdate_prelocking_strategy::handle_end(THD *thd) { - LEX *lex= thd->lex; - TABLE_LIST *table_list= lex->query_tables; - TABLE_LIST *tl; - List<Item> *fields= &lex->select_lex.item_list; - table_map tables_for_update; - bool update_view= 0; - DML_prelocking_strategy prelocking_strategy; - bool has_prelocking_list= thd->lex->requires_prelocking(); + DBUG_ENTER("Multiupdate_prelocking_strategy::handle_end"); + if (done) + DBUG_RETURN(0); - /* - if this multi-update was converted from usual update, here is table - counter else junk will be assigned here, but then replaced with real - count in open_tables() - */ - uint table_count= lex->table_count; - const bool using_lock_tables= thd->locked_tables_mode != LTM_NONE; - bool original_multiupdate= (thd->lex->sql_command == SQLCOM_UPDATE_MULTI); - DBUG_ENTER("mysql_multi_update_prepare"); + LEX *lex= thd->lex; + SELECT_LEX *select_lex= &lex->select_lex; + TABLE_LIST *table_list= lex->query_tables, *tl; - /* following need for prepared statements, to run next time multi-update */ - thd->lex->sql_command= SQLCOM_UPDATE_MULTI; + done= true; - /* - Open tables and create derived ones, but do not lock and fill them yet. + if (mysql_handle_derived(lex, DT_INIT) || + mysql_handle_derived(lex, DT_MERGE_FOR_INSERT) || + mysql_handle_derived(lex, DT_PREPARE)) + DBUG_RETURN(1); - During prepare phase acquire only S metadata locks instead of SW locks to - keep prepare of multi-UPDATE compatible with concurrent LOCK TABLES WRITE - and global read lock. - */ - if ((original_multiupdate && open_tables(thd, &table_list, &table_count, - thd->stmt_arena->is_stmt_prepare() - ? MYSQL_OPEN_FORCE_SHARED_MDL : 0, - &prelocking_strategy)) || - mysql_handle_derived(lex, DT_INIT)) - DBUG_RETURN(TRUE); /* setup_tables() need for VIEWs. JOIN::prepare() will call setup_tables() second time, but this call will do nothing (there are check for second call in setup_tables()). */ - //We need to merge for insert prior to prepare. - if (mysql_handle_derived(lex, DT_MERGE_FOR_INSERT)) - DBUG_RETURN(TRUE); - if (mysql_handle_derived(lex, DT_PREPARE)) - DBUG_RETURN(TRUE); - - if (setup_tables_and_check_access(thd, &lex->select_lex.context, - &lex->select_lex.top_join_list, - table_list, - lex->select_lex.leaf_tables, FALSE, - UPDATE_ACL, SELECT_ACL, FALSE)) - DBUG_RETURN(TRUE); + if (setup_tables_and_check_access(thd, &select_lex->context, + &select_lex->top_join_list, table_list, select_lex->leaf_tables, + FALSE, UPDATE_ACL, SELECT_ACL, FALSE)) + DBUG_RETURN(1); - if (lex->select_lex.handle_derived(thd->lex, DT_MERGE)) - DBUG_RETURN(TRUE); + if (select_lex->handle_derived(thd->lex, DT_MERGE)) + DBUG_RETURN(1); + List<Item> *fields= &lex->select_lex.item_list; if (setup_fields_with_no_wrap(thd, 0, *fields, MARK_COLUMNS_WRITE, 0, 0)) - DBUG_RETURN(TRUE); + DBUG_RETURN(1); for (tl= table_list; tl ; tl= tl->next_local) - { if (tl->view) { - update_view= 1; - break; + if (check_fields(thd, *fields)) + DBUG_RETURN(1); + else + break; } - } - - if (update_view && check_fields(thd, *fields)) - { - DBUG_RETURN(TRUE); - } - - thd->table_map_for_update= tables_for_update= get_table_map(fields); - if (unsafe_key_update(lex->select_lex.leaf_tables, tables_for_update)) - DBUG_RETURN(true); + table_map tables_for_update= thd->table_map_for_update= get_table_map(fields); - TABLE_LIST **new_tables= lex->query_tables_last; - DBUG_ASSERT(*new_tables== NULL); + if (unsafe_key_update(select_lex->leaf_tables, tables_for_update)) + DBUG_RETURN(1); /* Setup timestamp handling and locking mode */ - List_iterator<TABLE_LIST> ti(lex->select_lex.leaf_tables); + List_iterator<TABLE_LIST> ti(select_lex->leaf_tables); + const bool using_lock_tables= thd->locked_tables_mode != LTM_NONE; while ((tl= ti++)) { TABLE *table= tl->table; @@ -1477,7 +1452,7 @@ int mysql_multi_update_prepare(THD *thd) if (!tl->single_table_updatable() || check_key_in_view(thd, tl)) { my_error(ER_NON_UPDATABLE_TABLE, MYF(0), tl->alias, "UPDATE"); - DBUG_RETURN(TRUE); + DBUG_RETURN(1); } DBUG_PRINT("info",("setting table `%s` for update", tl->alias)); @@ -1488,8 +1463,8 @@ int mysql_multi_update_prepare(THD *thd) tl->updating= 1; if (tl->belong_to_view) tl->belong_to_view->updating= 1; - if (extend_table_list(thd, tl, &prelocking_strategy, has_prelocking_list)) - DBUG_RETURN(TRUE); + if (extend_table_list(thd, tl, this, has_prelocking_list)) + DBUG_RETURN(1); } else { @@ -1521,19 +1496,6 @@ int mysql_multi_update_prepare(THD *thd) through all leaf tables but also through all view hierarchy. */ - uint addon_table_count= 0; - if (*new_tables) - { - Sroutine_hash_entry **new_routines= thd->lex->sroutines_list.next; - DBUG_ASSERT(*new_routines == NULL); - if (open_tables(thd, thd->lex->create_info, new_tables, - &addon_table_count, new_routines, - thd->stmt_arena->is_stmt_prepare() - ? MYSQL_OPEN_FORCE_SHARED_MDL : 0, - &prelocking_strategy)) - DBUG_RETURN(TRUE); - } - for (tl= table_list; tl; tl= tl->next_local) { bool not_used= false; @@ -1546,23 +1508,67 @@ int mysql_multi_update_prepare(THD *thd) /* check single table update for view compound from several tables */ for (tl= table_list; tl; tl= tl->next_local) { + TABLE_LIST *for_update= 0; if (tl->is_jtbm()) continue; - if (tl->is_merged_derived()) + if (tl->is_merged_derived() && + tl->check_single_table(&for_update, tables_for_update, tl)) { - TABLE_LIST *for_update= 0; - if (tl->check_single_table(&for_update, tables_for_update, tl)) - { - my_error(ER_VIEW_MULTIUPDATE, MYF(0), - tl->view_db.str, tl->view_name.str); - DBUG_RETURN(-1); - } + my_error(ER_VIEW_MULTIUPDATE, MYF(0), tl->view_db.str, tl->view_name.str); + DBUG_RETURN(-1); } } + DBUG_RETURN(0); +} + +/* + make update specific preparation and checks after opening tables + + SYNOPSIS + mysql_multi_update_prepare() + thd thread handler + + RETURN + FALSE OK + TRUE Error +*/ + +int mysql_multi_update_prepare(THD *thd) +{ + LEX *lex= thd->lex; + TABLE_LIST *table_list= lex->query_tables; + TABLE_LIST *tl; + Multiupdate_prelocking_strategy prelocking_strategy; + uint table_count= lex->table_count; + DBUG_ENTER("mysql_multi_update_prepare"); + + /* + Open tables and create derived ones, but do not lock and fill them yet. + + During prepare phase acquire only S metadata locks instead of SW locks to + keep prepare of multi-UPDATE compatible with concurrent LOCK TABLES WRITE + and global read lock. + */ + if (thd->lex->sql_command == SQLCOM_UPDATE_MULTI) + { + if (open_tables(thd, &table_list, &table_count, + thd->stmt_arena->is_stmt_prepare() ? MYSQL_OPEN_FORCE_SHARED_MDL : 0, + &prelocking_strategy)) + DBUG_RETURN(TRUE); + } + else + { + /* following need for prepared statements, to run next time multi-update */ + thd->lex->sql_command= SQLCOM_UPDATE_MULTI; + prelocking_strategy.reset(thd); + if (prelocking_strategy.handle_end(thd)) + DBUG_RETURN(TRUE); + } + /* now lock and fill tables */ if (!thd->stmt_arena->is_stmt_prepare() && - lock_tables(thd, table_list, table_count + addon_table_count, 0)) + lock_tables(thd, table_list, table_count, 0)) { DBUG_RETURN(TRUE); } @@ -1575,7 +1581,7 @@ int mysql_multi_update_prepare(THD *thd) */ lex->select_lex.exclude_from_table_unique_test= TRUE; /* We only need SELECT privilege for columns in the values list */ - ti.rewind(); + List_iterator<TABLE_LIST> ti(lex->select_lex.leaf_tables); while ((tl= ti++)) { if (tl->is_jtbm()) @@ -1618,25 +1624,18 @@ int mysql_multi_update_prepare(THD *thd) Setup multi-update handling and call SELECT to do the join */ -bool mysql_multi_update(THD *thd, - TABLE_LIST *table_list, - List<Item> *fields, - List<Item> *values, - COND *conds, - ulonglong options, +bool mysql_multi_update(THD *thd, TABLE_LIST *table_list, List<Item> *fields, + List<Item> *values, COND *conds, ulonglong options, enum enum_duplicates handle_duplicates, - bool ignore, - SELECT_LEX_UNIT *unit, - SELECT_LEX *select_lex, - multi_update **result) + bool ignore, SELECT_LEX_UNIT *unit, + SELECT_LEX *select_lex, multi_update **result) { bool res; DBUG_ENTER("mysql_multi_update"); - + if (!(*result= new (thd->mem_root) multi_update(thd, table_list, &thd->lex->select_lex.leaf_tables, - fields, values, - handle_duplicates, ignore))) + fields, values, handle_duplicates, ignore))) { DBUG_RETURN(TRUE); } @@ -1644,11 +1643,9 @@ bool mysql_multi_update(THD *thd, thd->abort_on_warning= !ignore && thd->is_strict_mode(); List<Item> total_list; - res= mysql_select(thd, &select_lex->ref_pointer_array, - table_list, select_lex->with_wild, - total_list, - conds, 0, (ORDER *) NULL, (ORDER *)NULL, (Item *) NULL, - (ORDER *)NULL, + res= mysql_select(thd, &select_lex->ref_pointer_array, table_list, + select_lex->with_wild, total_list, conds, 0, NULL, NULL, + NULL, NULL, options | SELECT_NO_JOIN_CACHE | SELECT_NO_UNLOCK | OPTION_SETUP_TABLES_DONE, *result, unit, select_lex); diff --git a/storage/innobase/.clang-format b/storage/innobase/.clang-format new file mode 100644 index 00000000000..f7a72f3cf24 --- /dev/null +++ b/storage/innobase/.clang-format @@ -0,0 +1,10 @@ +UseTab: Always +TabWidth: 8 +IndentWidth: 8 +BreakBeforeBinaryOperators: All +PointerAlignment: Left +AlwaysBreakAfterReturnType: TopLevel +BreakBeforeBraces: Custom +BraceWrapping: + AfterFunction: true +AccessModifierOffset: -8 |