summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorOleksandr Byelkin <sanja@mariadb.com>2019-06-12 22:54:46 +0200
committerOleksandr Byelkin <sanja@mariadb.com>2019-06-12 22:54:46 +0200
commit5b65d61d9384a45ea1b8df79694493fbb1a14e4a (patch)
tree451255aa141e115baf97b867fb0752b8ce4dd9a9
parent56c60b2fc5d006206f54b93be401570837f2f312 (diff)
parent7a7d9904e12335ee8b1eea9671138b3c469a3829 (diff)
downloadmariadb-git-5b65d61d9384a45ea1b8df79694493fbb1a14e4a.tar.gz
Merge branch '5.5' into 10.1
-rw-r--r--.clang-format18
-rw-r--r--include/my_base.h1
-rw-r--r--mysql-test/r/derived.result1
-rw-r--r--mysql-test/r/derived_view.result20
-rw-r--r--mysql-test/r/join.result2
-rw-r--r--mysql-test/r/join_nested.result33
-rw-r--r--mysql-test/r/join_nested_jcl6.result33
-rw-r--r--mysql-test/r/mdev13607.result30
-rw-r--r--mysql-test/r/multi_update.result22
-rw-r--r--mysql-test/r/multi_update_debug.result13
-rw-r--r--mysql-test/r/multi_update_innodb.result17
-rw-r--r--mysql-test/r/subselect.result2
-rw-r--r--mysql-test/r/subselect_mat.result89
-rw-r--r--mysql-test/r/subselect_no_exists_to_in.result2
-rw-r--r--mysql-test/r/subselect_no_mat.result2
-rw-r--r--mysql-test/r/subselect_no_opts.result2
-rw-r--r--mysql-test/r/subselect_no_scache.result2
-rw-r--r--mysql-test/r/subselect_no_semijoin.result2
-rw-r--r--mysql-test/r/subselect_sj_mat.result89
-rw-r--r--mysql-test/t/derived.test3
-rw-r--r--mysql-test/t/derived_view.test2
-rw-r--r--mysql-test/t/join_nested.test34
-rw-r--r--mysql-test/t/multi_update.test38
-rw-r--r--mysql-test/t/multi_update_debug.test27
-rw-r--r--mysql-test/t/multi_update_innodb.test22
-rw-r--r--mysql-test/t/subselect_sj_mat.test75
-rw-r--r--sql/my_json_writer.cc7
-rw-r--r--sql/my_json_writer.h1
-rw-r--r--sql/sql_base.cc11
-rw-r--r--sql/sql_base.h14
-rw-r--r--sql/sql_explain.cc8
-rw-r--r--sql/sql_join_cache.cc3
-rw-r--r--sql/sql_lex.cc5
-rw-r--r--sql/sql_select.cc110
-rw-r--r--sql/sql_update.cc229
-rw-r--r--storage/innobase/.clang-format10
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