# The results where created without innodb persistent stats.
SET @old_innodb_stats_persistent= @@global.innodb_stats_persistent;
SET @@global.innodb_stats_persistent= 0;
# Original tests for WL#4443
create table thread_to_monitor(thread_id int);
insert into thread_to_monitor(thread_id)
SELECT THREAD_ID FROM performance_schema.threads
WHERE PROCESSLIST_ID=CONNECTION_ID();
CREATE TABLE t1 (a int PRIMARY KEY, b varchar(128), KEY (b))
ENGINE = InnoDB
PARTITION BY HASH (a) PARTITIONS 13;
CREATE TABLE t2 (a int PRIMARY KEY AUTO_INCREMENT, b varchar(128))
ENGINE = InnoDB
PARTITION BY HASH (a) PARTITIONS 13;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`a`)
PARTITIONS 13 */
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`a`)
PARTITIONS 13 */
#
#
# Test how INSERT prune locks
# First test, no defaults
#
FLUSH STATUS;
INSERT INTO t1 VALUES (1, 'First row, p1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
# 1 commit
FLUSH STATUS;
INSERT INTO t1 VALUES (1, 'First row, duplicate');
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
VARIABLE_NAME	VARIABLE_VALUE
Handler_external_lock	2
Handler_rollback	1
Handler_write	1
# 1 rollback
FLUSH STATUS;
INSERT INTO t1 VALUES (0, 'First row, p0'), (2, 'First row, p2'),
(3, 'First row, p3'), (4, 'First row, p4');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	4
# 1 commit
FLUSH STATUS;
INSERT INTO t1 VALUES (1 * 13, 'Second row, p0'), (2 * 13, 'Third row, p0'),
(3 * 13, 'Fourth row, p0'), (4 * 13, 'Fifth row, p0');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	4
# 1 commit
#
# INSERT with auto increment, lock pruning
#
FLUSH STATUS;
INSERT INTO t2 VALUES (NULL, 'First auto-inc row');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
# Auto increment value is not known until write.
# 1 commit
FLUSH STATUS;
INSERT INTO t2 (b) VALUES ('Second auto-inc row');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
# Auto increment value is not known until write.
# 1 commit
FLUSH STATUS;
INSERT INTO t2 VALUES (10, "First row, p10");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
# Insert pruning on tables with auto increment is not yet supported
# 1 commit
#
# UPDATE with auto increment, lock pruning
#
FLUSH STATUS;
UPDATE t2 SET b = CONCAT(b, ", UPDATED") WHERE a = 10;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
# 1 read_key + 1 update + 1 commit
#
# Test handling of INSERT INTO <table> VALUES (<all fields specified>)
#
CREATE TABLE t3 (a INT, b CHAR(10)) PARTITION BY HASH (a) PARTITIONS 2;
SHOW CREATE TABLE t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `a` int(11) DEFAULT NULL,
  `b` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`a`)
PARTITIONS 2 */
FLUSH STATUS;
INSERT INTO t3 VALUES (1, "Test 1");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t3 VALUES (2, "Test 2"), (3, "Test 3"), (4, "Test 4");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	3
FLUSH STATUS;
INSERT INTO t3 VALUES (6, "Test 6"), (8, "Test 8"), (10, "Test 10");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	3
FLUSH STATUS;
INSERT INTO t3 VALUES (5, "Test 5"), (7, "Test 7"), (9, "Test 9");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	3
FLUSH STATUS;
INSERT INTO t3 VALUES (0, "Test 0");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t3 (a, b) VALUES (1, "Test 1");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t3 (a, b) VALUES (2, "Test 2"), (3, "Test 3"), (4, "Test 4");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	3
FLUSH STATUS;
INSERT INTO t3 (a, b) VALUES (6, "Test 6"), (8, "Test 8"), (10, "Test 10");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	3
FLUSH STATUS;
INSERT INTO t3 (a, b) VALUES (5, "Test 5"), (7, "Test 7"), (9, "Test 9");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	3
FLUSH STATUS;
INSERT INTO t3 (a, b) VALUES (0, "Test 0");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
#
# Test handling of
# INSERT INTO <table> VALUES (<not all fields specified>)
#
FLUSH STATUS;
INSERT INTO t3 (a) VALUES (1);
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t3 (a) VALUES (2), (3), (4);
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	3
FLUSH STATUS;
INSERT INTO t3 (a) VALUES (6), (8), (10);
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	3
FLUSH STATUS;
INSERT INTO t3 (a) VALUES (5), (7), (9);
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	3
FLUSH STATUS;
INSERT INTO t3 (b) VALUES ("Only b 1");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t3 (b) VALUES ("Only b 2"), ("Only b 3");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	2
SELECT * FROM t3 ORDER BY a, b;
a	b
NULL	Only b 1
NULL	Only b 2
NULL	Only b 3
0	Test 0
0	Test 0
1	NULL
1	Test 1
1	Test 1
2	NULL
2	Test 2
2	Test 2
3	NULL
3	Test 3
3	Test 3
4	NULL
4	Test 4
4	Test 4
5	NULL
5	Test 5
5	Test 5
6	NULL
6	Test 6
6	Test 6
7	NULL
7	Test 7
7	Test 7
8	NULL
8	Test 8
8	Test 8
9	NULL
9	Test 9
9	Test 9
10	NULL
10	Test 10
10	Test 10
DROP TABLE t3;
#
# Test of insert pruning with subpartitions
#
# I've placed the varchar column before the int column for better
# distribution by LINEAR KEY.
CREATE TABLE t3
(a int DEFAULT 10,
b varchar(64) DEFAULT "Default",
c varchar(64) DEFAULT "Default",
d int unsigned DEFAULT 9,
e varchar(255) DEFAULT "Default-filler.filler.filler.",
PRIMARY KEY (a,b,c,d))
charset latin1
PARTITION BY RANGE COLUMNS (a, b)
SUBPARTITION BY LINEAR KEY (d, c)
SUBPARTITIONS 4
(PARTITION pNeg VALUES LESS THAN (0, ""),
PARTITION `p0-9` VALUES LESS THAN (9, MAXVALUE),
PARTITION p10 VALUES LESS THAN (10, MAXVALUE),
PARTITION `p11-100` VALUES LESS THAN (99, MAXVALUE));
SHOW CREATE TABLE t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `a` int(11) NOT NULL DEFAULT '10',
  `b` varchar(64) NOT NULL DEFAULT 'Default',
  `c` varchar(64) NOT NULL DEFAULT 'Default',
  `d` int(10) unsigned NOT NULL DEFAULT '9',
  `e` varchar(255) DEFAULT 'Default-filler.filler.filler.',
  PRIMARY KEY (`a`,`b`,`c`,`d`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE  COLUMNS(a,b)
SUBPARTITION BY LINEAR KEY (d,c)
SUBPARTITIONS 4
(PARTITION pNeg VALUES LESS THAN (0,'') ENGINE = InnoDB,
 PARTITION `p0-9` VALUES LESS THAN (9,MAXVALUE) ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN (10,MAXVALUE) ENGINE = InnoDB,
 PARTITION `p11-100` VALUES LESS THAN (99,MAXVALUE) ENGINE = InnoDB) */
#
# Test INSERT with
# empty field specifier list and empty value list
#
FLUSH STATUS;
INSERT INTO t3 () VALUES ();
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
#
# Test INSERT with
# no field specifier list and full value list, including DEFAULT
# specifier
#
FLUSH STATUS;
INSERT IGNORE INTO t3 VALUES (-1, "ZZZzzzz", "yyyYYY", -1, DEFAULT);
Warnings:
Warning	1264	Out of range value for column 'd' at row 1
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
#
# Test INSERT with
# empty field specifier list and full value list, including NULL
#
FLUSH STATUS;
INSERT INTO t3 () VALUES (0, "", "", 0, NULL);
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
#
# Test INSERT with field specifier list for only some fields
#
FLUSH STATUS;
INSERT INTO t3 (a) VALUES (1);
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t3 (a, b) VALUES (1, "Part expr fulfilled"),
(10, "Part expr fulfilled");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	2
FLUSH STATUS;
INSERT INTO t3 (d) VALUES (1), (2);
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	2
FLUSH STATUS;
INSERT INTO t3 (c, d) VALUES ("Subpart expr fulfilled", 1);
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t3 (a, b, d) VALUES (10, "Full part, half subpart", 1),
(12, "Full part, half subpart", 1),
(12, "Full part, half subpart", 2),
(12, "Full part, half subpart", 3),
(12, "Full part, half subpart", 4),
(12, "Full part, half subpart", 0);
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	6
# d = 0 and d = 4 goes to the same subpart!
FLUSH STATUS;
INSERT INTO t3 (a, b, c) VALUES (1, "Full part", "Half subpart");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
# Adding 'Default' as padding to see if LINEAR KEY uses different parts.
FLUSH STATUS;
INSERT INTO t3 (a, c, d) VALUES (12, "Half part, full subpart", 1),
(12, "Half part, full subpartDefault", 1),
(12, "Half part, full subpart Default", 1);
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	3
# First and last row goes to the same subpartition.
FLUSH STATUS;
INSERT INTO t3 (b, c, d) VALUES ("Half part", "Full subpart", 1);
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
#
# Test INSERT with full field specifier list and full value list
#
INSERT INTO t3 (a, b, c, d) VALUES (1, "Full part", "Full subpart", 1);
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
#
# Test INSERT with no field specifier list and empty value list
# (need to delete previous inserted default row first...)
#
DELETE FROM t3 WHERE a = 10 AND b = 'Default' AND c = 'Default' AND D = 9;
FLUSH STATUS;
INSERT INTO t3 VALUES ();
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
#
# Verifing result
#
SELECT * FROM t3;
a	b	c	d	e
-1	ZZZzzzz	yyyYYY	0	Default-filler.filler.filler.
0			0	NULL
1	Default	Default	9	Default-filler.filler.filler.
1	Full part	Full subpart	1	Default-filler.filler.filler.
1	Full part	Half subpart	9	Default-filler.filler.filler.
1	Part expr fulfilled	Default	9	Default-filler.filler.filler.
10	Default	Default	1	Default-filler.filler.filler.
10	Default	Default	2	Default-filler.filler.filler.
10	Default	Default	9	Default-filler.filler.filler.
10	Default	Subpart expr fulfilled	1	Default-filler.filler.filler.
10	Full part, half subpart	Default	1	Default-filler.filler.filler.
10	Half part	Full subpart	1	Default-filler.filler.filler.
10	Part expr fulfilled	Default	9	Default-filler.filler.filler.
12	Default	Half part, full subpart	1	Default-filler.filler.filler.
12	Default	Half part, full subpart Default	1	Default-filler.filler.filler.
12	Default	Half part, full subpartDefault	1	Default-filler.filler.filler.
12	Full part, half subpart	Default	0	Default-filler.filler.filler.
12	Full part, half subpart	Default	1	Default-filler.filler.filler.
12	Full part, half subpart	Default	2	Default-filler.filler.filler.
12	Full part, half subpart	Default	3	Default-filler.filler.filler.
12	Full part, half subpart	Default	4	Default-filler.filler.filler.
SELECT d, c FROM t3 PARTITION(`p11-100sp0`);
d	c
0	Default
4	Default
SELECT d, c FROM t3 PARTITION(`p11-100sp1`);
d	c
1	Default
1	Half part, full subpart
1	Half part, full subpart Default
SELECT d, c FROM t3 PARTITION(`p11-100sp2`);
d	c
1	Half part, full subpartDefault
2	Default
SELECT d, c FROM t3 PARTITION(`p11-100sp3`);
d	c
3	Default
#
# Test with LOCK TABLES
#
LOCK TABLES t3 PARTITION (`p11-100sp0`) WRITE;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PARTITION (`p11-100sp0`) WRITE' at line 1
FLUSH STATUS;
LOCK TABLES t3 WRITE;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	17
Handler_external_lock	65
Handler_read_key	32
Handler_read_next	16
# No further locks/unlocks until UNLOCK TABLES.
#
# Test INSERT with no field specifier list and empty value list
# (need to delete previous inserted default row first...)
#
DELETE FROM t3 WHERE a = 10 AND b = 'Default' AND c = 'Default' AND D = 9;
FLUSH STATUS;
INSERT INTO t3 VALUES ();
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
#
# Test INSERT with field specifier list for only some fields
# (need to delete previous inserted default row first...)
#
DELETE FROM t3
WHERE a = 10 AND b = "Default" AND c = "Default" AND d = 9;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_prepare	2
Handler_read_key	1
FLUSH STATUS;
INSERT INTO t3 (b, d, e) VALUES (DEFAULT, DEFAULT, "All default!");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
#
# Test UPDATE of non PK field in default row
#
UPDATE t3
SET e = CONCAT(e, ", updated")
WHERE a = 10 AND b = "Default" AND c = "Default" AND d = 9;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
FLUSH STATUS;
#
# Test UPDATE of PK field + non PK field in default row
#
UPDATE t3
SET a = DEFAULT, b = "Not DEFAULT!", e = CONCAT(e, ", updated2")
WHERE a = 10 AND b = "Default" AND c = "Default" AND d = 9;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_prepare	2
Handler_read_key	2
Handler_read_rnd	1
Handler_update	1
FLUSH STATUS;
#
# Test REPLACE of default row (INSERT, since not duplicate)
#
REPLACE INTO t3 (e) VALUES ("New default row");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
SELECT * FROM t3
WHERE a = 10 AND b = "Default" AND c = "Default" AND d = 9;
a	b	c	d	e
10	Default	Default	9	New default row
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_read_key	1
FLUSH STATUS;
EXPLAIN SELECT * FROM t3
WHERE a = 10 AND b = "Default" AND c = "Default" AND d = 9;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t3	p10_p10sp1	const	PRIMARY	PRIMARY	140	const,const,const,const	#	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select '10' AS `a`,'Default' AS `b`,'Default' AS `c`,'9' AS `d`,'New default row' AS `e` from `test`.`t3` where 1
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_read_key	1
FLUSH STATUS;
#
# Test REPLACE of default row (REPLACE, since duplicate exists)
#
REPLACE INTO t3 (e) VALUES ("Newest default row");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
Handler_write	1
#
# Test SELECT with explicit partition selection
#
FLUSH STATUS;
SELECT * FROM t3 PARTITION (p10);
a	b	c	d	e
10	Default	Default	1	Default-filler.filler.filler.
10	Default	Default	2	Default-filler.filler.filler.
10	Default	Default	9	Newest default row
10	Default	Subpart expr fulfilled	1	Default-filler.filler.filler.
10	Full part, half subpart	Default	1	Default-filler.filler.filler.
10	Half part	Full subpart	1	Default-filler.filler.filler.
10	Not DEFAULT!	Default	9	All default!, updated, updated2
10	Part expr fulfilled	Default	9	Default-filler.filler.filler.
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_read_first	4
Handler_read_key	4
Handler_read_rnd_next	8
FLUSH STATUS;
EXPLAIN SELECT * FROM t3 PARTITION (p10);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t3	p10_p10sp0,p10_p10sp1,p10_p10sp2,p10_p10sp3	ALL	NULL	NULL	NULL	NULL	#	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t3`.`c` AS `c`,`test`.`t3`.`d` AS `d`,`test`.`t3`.`e` AS `e` from `test`.`t3` PARTITION (`p10`)
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
FLUSH STATUS;
UNLOCK TABLES;
VARIABLE_NAME	VARIABLE_VALUE
Handler_external_lock	1
DROP TABLE t3;
#
# End of LOCK TABLE test.
#
#
# Test INSERT with timestamp column NO default function
#
SELECT UNIX_TIMESTAMP('2011-01-01 00:00:00') as time_t,
UNIX_TIMESTAMP('2011-01-01 00:00:00') % 3 as part,
1234567890 % 3 as part2;
time_t	part	part2
1293829200	0	0
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
CREATE TABLE t3
(a timestamp DEFAULT 0,
b char(10),
PRIMARY KEY (a))
PARTITION BY HASH (UNIX_TIMESTAMP(a)) PARTITIONS 3;
SHOW CREATE TABLE t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `b` char(10) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (unix_timestamp(`a`))
PARTITIONS 3 */
FLUSH STATUS;
SET TIMESTAMP = 1234567890;
INSERT INTO t3 (a) VALUES (NOW());
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
SET TIMESTAMP = 1234567891;
INSERT INTO t3 VALUES ();
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:00');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:01');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:02'), ('2011-01-01 00:00:03');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	2
# 2 writes
FLUSH STATUS;
SET TIMESTAMP = 1234567892;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:00')
ON DUPLICATE KEY UPDATE b = "DUP_KEY";
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
Handler_write	1
# 1 read_key + 1 update (same partition)
# 1 (failed) write
FLUSH STATUS;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:01')
ON DUPLICATE KEY UPDATE a = '2011-01-01 00:00:05', b = "DUP_KEY2";
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_write	2
# No pruning due to updating partitioning field.
# 1 read_key + 1 delete + 2 write (1 failed + 1 ok)
# 1 delete + 1 write due to moved to different partition
FLUSH STATUS;
SET TIMESTAMP = 1234567893;
UPDATE t3 SET b = 'Updated' WHERE a = '2011-01-01 00:00:02';
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
# 1 read_key + 1 update (same partition)
# 1 (failed) write
FLUSH STATUS;
REPLACE INTO t3 VALUES ('2011-01-01 00:00:04', 'Replace1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
REPLACE INTO t3 VALUES ('2011-01-01 00:00:04', 'Replace2');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
Handler_write	1
# 1 read_key + 1 update + 1 failed write
#
# Test of replace of default PK (delete might be needed first)
#
DELETE FROM t3 WHERE a = 0;
FLUSH STATUS;
SET TIMESTAMP = 1234567894;
REPLACE INTO t3 (b) VALUES ('Replace3');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
SET TIMESTAMP = 1234567894;
REPLACE INTO t3 (b) VALUES ('Replace4');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
Handler_write	1
# 1 read_key + 1 update + 1 failed write
SELECT * FROM t3;
a	b
0000-00-00 00:00:00	Replace4
2009-02-14 02:31:30	NULL
2011-01-01 00:00:00	DUP_KEY
2011-01-01 00:00:02	Updated
2011-01-01 00:00:03	NULL
2011-01-01 00:00:04	Replace2
2011-01-01 00:00:05	DUP_KEY2
DROP TABLE t3;
#
# Test INSERT with timestamp column DEFAULT INSERT + UPDATE
#
CREATE TABLE t3
(a timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
b char(10),
PRIMARY KEY (a))
PARTITION BY HASH (UNIX_TIMESTAMP(a)) PARTITIONS 3;
SHOW CREATE TABLE t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `b` char(10) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (unix_timestamp(`a`))
PARTITIONS 3 */
FLUSH STATUS;
SET TIMESTAMP = 1234567890;
INSERT INTO t3 (a) VALUES (NOW());
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
SET TIMESTAMP = 1234567891;
INSERT INTO t3 VALUES ();
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:00');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:01');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:02'), ('2011-01-01 00:00:03');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	2
# 2 writes
FLUSH STATUS;
SET TIMESTAMP = 1234567892;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:00')
ON DUPLICATE KEY UPDATE b = "DUP_KEY";
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_write	2
# No pruning due to DEFAULT function on partitioning column
# 1 read_key + 1 delete + 2 write (1 failed + 1 ok)
# 1 delete + 1 write due to moved to different partition
FLUSH STATUS;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:01')
ON DUPLICATE KEY UPDATE a = '2011-01-01 00:00:05', b = "DUP_KEY2";
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_write	2
# No pruning due to updating partitioning field.
# 1 read_key + 1 delete + 2 write (1 failed + 1 ok)
# 1 delete + 1 write due to moved to different partition
FLUSH STATUS;
SET TIMESTAMP = 1234567893;
UPDATE t3 SET b = 'Updated' WHERE a = '2011-01-01 00:00:02';
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	2
Handler_read_rnd	1
Handler_write	1
# No pruning due to DEFAULT function on partitioning column
# 2 read_key + 1 read_rnd (1 read_key due to index lookup,
# 1 read_rnd + 1 read_key due to positioning before update)
# 1 delete + 1 write due to moved to different partition
# + 1 (failed) write
FLUSH STATUS;
REPLACE INTO t3 VALUES ('2011-01-01 00:00:04', 'Replace1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
REPLACE INTO t3 VALUES ('2011-01-01 00:00:04', 'Replace2');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
Handler_write	1
# 1 read_key + 1 update + 1 failed write
#
# Test of replace of default PK (delete might be needed first)
#
DELETE FROM t3 WHERE a = 0;
FLUSH STATUS;
SET TIMESTAMP = 1234567894;
REPLACE INTO t3 (b) VALUES ('Replace3');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
SET TIMESTAMP = 1234567894;
REPLACE INTO t3 (b) VALUES ('Replace4');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
Handler_write	1
# 1 read_key + 1 update + 1 failed write
SELECT * FROM t3;
a	b
2009-02-14 02:31:30	NULL
2009-02-14 02:31:31	NULL
2009-02-14 02:31:32	DUP_KEY
2009-02-14 02:31:33	Updated
2009-02-14 02:31:34	Replace4
2011-01-01 00:00:03	NULL
2011-01-01 00:00:04	Replace2
2011-01-01 00:00:05	DUP_KEY2
DROP TABLE t3;
#
# Test INSERT with timestamp column DEFAULT UPDATE
#
CREATE TABLE t3
(a timestamp DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP,
b char(10),
PRIMARY KEY (a))
PARTITION BY HASH (UNIX_TIMESTAMP(a)) PARTITIONS 3;
SHOW CREATE TABLE t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `b` char(10) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (unix_timestamp(`a`))
PARTITIONS 3 */
FLUSH STATUS;
SET TIMESTAMP = 1234567890;
INSERT INTO t3 (a) VALUES (NOW());
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
SET TIMESTAMP = 1234567891;
INSERT INTO t3 VALUES ();
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:00');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:01');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:02'), ('2011-01-01 00:00:03');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	2
# 2 writes
FLUSH STATUS;
SET TIMESTAMP = 1234567892;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:00')
ON DUPLICATE KEY UPDATE b = "DUP_KEY";
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_write	2
# No pruning due to DEFAULT function on partitioning column
# 1 read_key + 1 delete + 2 write (1 failed + 1 ok)
# 1 delete + 1 write due to moved to different partition
FLUSH STATUS;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:01')
ON DUPLICATE KEY UPDATE a = '2011-01-01 00:00:05', b = "DUP_KEY2";
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_write	2
# No pruning due to updating partitioning field.
# 1 read_key + 1 delete + 2 write (1 failed + 1 ok)
# 1 delete + 1 write due to moved to different partition
FLUSH STATUS;
SET TIMESTAMP = 1234567893;
UPDATE t3 SET b = 'Updated' WHERE a = '2011-01-01 00:00:02';
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	2
Handler_read_rnd	1
Handler_write	1
# No pruning due to DEFAULT function on partitioning column
# 2 read_key + 1 read_rnd (1 read_key due to index lookup,
# 1 read_rnd + 1 read_key due to positioning before update)
# 1 delete + 1 write due to moved to different partition
# + 1 (failed) write
FLUSH STATUS;
REPLACE INTO t3 VALUES ('2011-01-01 00:00:04', 'Replace1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
REPLACE INTO t3 VALUES ('2011-01-01 00:00:04', 'Replace2');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
Handler_write	1
# 1 read_key + 1 update + 1 failed write
#
# Test of replace of default PK (delete might be needed first)
#
DELETE FROM t3 WHERE a = 0;
FLUSH STATUS;
SET TIMESTAMP = 1234567894;
REPLACE INTO t3 (b) VALUES ('Replace3');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
SET TIMESTAMP = 1234567894;
REPLACE INTO t3 (b) VALUES ('Replace4');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
Handler_write	1
# 1 read_key + 1 update + 1 failed write
SELECT * FROM t3;
a	b
0000-00-00 00:00:00	Replace4
2009-02-14 02:31:30	NULL
2009-02-14 02:31:32	DUP_KEY
2009-02-14 02:31:33	Updated
2011-01-01 00:00:03	NULL
2011-01-01 00:00:04	Replace2
2011-01-01 00:00:05	DUP_KEY2
DROP TABLE t3;
#
# Test INSERT with timestamp column DEFAULT INSERT
#
CREATE TABLE t3
(a timestamp DEFAULT CURRENT_TIMESTAMP,
b char(10),
PRIMARY KEY (a))
PARTITION BY HASH (UNIX_TIMESTAMP(a)) PARTITIONS 3;
SHOW CREATE TABLE t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `b` char(10) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (unix_timestamp(`a`))
PARTITIONS 3 */
FLUSH STATUS;
SET TIMESTAMP = 1234567890;
INSERT INTO t3 (a) VALUES (NOW());
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
SET TIMESTAMP = 1234567891;
INSERT INTO t3 VALUES ();
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:00');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:01');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:02'), ('2011-01-01 00:00:03');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	2
# 2 writes
FLUSH STATUS;
SET TIMESTAMP = 1234567892;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:00')
ON DUPLICATE KEY UPDATE b = "DUP_KEY";
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
Handler_write	1
# 1 read_key + 1 update (same partition)
# 1 (failed) write
FLUSH STATUS;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:01')
ON DUPLICATE KEY UPDATE a = '2011-01-01 00:00:05', b = "DUP_KEY2";
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_write	2
# No pruning due to updating partitioning field.
# 1 read_key + 1 delete + 2 write (1 failed + 1 ok)
# 1 delete + 1 write due to moved to different partition
FLUSH STATUS;
SET TIMESTAMP = 1234567893;
UPDATE t3 SET b = 'Updated' WHERE a = '2011-01-01 00:00:02';
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
# 1 read_key + 1 update (same partition)
# 1 (failed) write
FLUSH STATUS;
REPLACE INTO t3 VALUES ('2011-01-01 00:00:04', 'Replace1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
REPLACE INTO t3 VALUES ('2011-01-01 00:00:04', 'Replace2');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
Handler_write	1
# 1 read_key + 1 update + 1 failed write
#
# Test of replace of default PK (delete might be needed first)
#
DELETE FROM t3 WHERE a = 0;
FLUSH STATUS;
SET TIMESTAMP = 1234567894;
REPLACE INTO t3 (b) VALUES ('Replace3');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
SET TIMESTAMP = 1234567894;
REPLACE INTO t3 (b) VALUES ('Replace4');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
Handler_write	1
# 1 read_key + 1 update + 1 failed write
SELECT * FROM t3;
a	b
2009-02-14 02:31:30	NULL
2009-02-14 02:31:31	NULL
2009-02-14 02:31:34	Replace4
2011-01-01 00:00:00	DUP_KEY
2011-01-01 00:00:02	Updated
2011-01-01 00:00:03	NULL
2011-01-01 00:00:04	Replace2
2011-01-01 00:00:05	DUP_KEY2
DROP TABLE t3;
#
# Test INSERT with DATETIME column NO default function
#
CREATE TABLE t3
(a DATETIME DEFAULT 0,
b char(10),
PRIMARY KEY (a))
PARTITION BY KEY (a) PARTITIONS 3;
SHOW CREATE TABLE t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `a` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `b` char(10) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY KEY (a)
PARTITIONS 3 */
FLUSH STATUS;
SET TIMESTAMP = 1234567890;
INSERT INTO t3 (a) VALUES (NOW());
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
SET TIMESTAMP = 1234567891;
INSERT INTO t3 VALUES ();
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:00');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:01');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:02'), ('2011-01-01 00:00:03');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	2
# 2 writes
FLUSH STATUS;
SET TIMESTAMP = 1234567892;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:00')
ON DUPLICATE KEY UPDATE b = "DUP_KEY";
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
Handler_write	1
# 1 read_key + 1 update (same partition)
# 1 (failed) write
FLUSH STATUS;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:01')
ON DUPLICATE KEY UPDATE a = '2011-01-01 00:00:05', b = "DUP_KEY2";
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_write	2
# No pruning due to updating partitioning field.
# 1 read_key + 1 delete + 2 write (1 failed + 1 ok)
# 1 delete + 1 write due to moved to different partition
FLUSH STATUS;
SET TIMESTAMP = 1234567893;
UPDATE t3 SET b = 'Updated' WHERE a = '2011-01-01 00:00:02';
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
# 1 read_key + 1 update (same partition)
# 1 (failed) write
FLUSH STATUS;
REPLACE INTO t3 VALUES ('2011-01-01 00:00:04', 'Replace1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
REPLACE INTO t3 VALUES ('2011-01-01 00:00:04', 'Replace2');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
Handler_write	1
# 1 read_key + 1 update + 1 failed write
#
# Test of replace of default PK (delete might be needed first)
#
DELETE FROM t3 WHERE a = 0;
FLUSH STATUS;
SET TIMESTAMP = 1234567894;
REPLACE INTO t3 (b) VALUES ('Replace3');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
SET TIMESTAMP = 1234567894;
REPLACE INTO t3 (b) VALUES ('Replace4');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
Handler_write	1
# 1 read_key + 1 update + 1 failed write
SELECT * FROM t3;
a	b
0000-00-00 00:00:00	Replace4
2009-02-14 02:31:30	NULL
2011-01-01 00:00:00	DUP_KEY
2011-01-01 00:00:02	Updated
2011-01-01 00:00:03	NULL
2011-01-01 00:00:04	Replace2
2011-01-01 00:00:05	DUP_KEY2
DROP TABLE t3;
#
# Test INSERT with DATETIME column DEFAULT INSERT + UPDATE
#
CREATE TABLE t3
(a DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
b char(10),
PRIMARY KEY (a))
PARTITION BY KEY (a) PARTITIONS 3;
SHOW CREATE TABLE t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `a` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `b` char(10) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY KEY (a)
PARTITIONS 3 */
FLUSH STATUS;
SET TIMESTAMP = 1234567890;
INSERT INTO t3 (a) VALUES (NOW());
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
SET TIMESTAMP = 1234567891;
INSERT INTO t3 VALUES ();
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:00');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:01');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:02'), ('2011-01-01 00:00:03');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	2
# 2 writes
FLUSH STATUS;
SET TIMESTAMP = 1234567892;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:00')
ON DUPLICATE KEY UPDATE b = "DUP_KEY";
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_write	2
# No pruning due to DEFAULT function on partitioning column
# 1 read_key + 1 delete + 2 write (1 failed + 1 ok)
# 1 delete + 1 write due to moved to different partition
FLUSH STATUS;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:01')
ON DUPLICATE KEY UPDATE a = '2011-01-01 00:00:05', b = "DUP_KEY2";
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_write	2
# No pruning due to updating partitioning field.
# 1 read_key + 1 delete + 2 write (1 failed + 1 ok)
# 1 delete + 1 write due to moved to different partition
FLUSH STATUS;
SET TIMESTAMP = 1234567893;
UPDATE t3 SET b = 'Updated' WHERE a = '2011-01-01 00:00:02';
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	2
Handler_read_rnd	1
Handler_update	1
# No pruning due to DEFAULT function on partitioning column
# 2 read_key + 1 read_rnd (1 read_key due to index lookup,
# 1 read_rnd + 1 read_key due to positioning before update)
# 1 delete + 1 write due to moved to different partition
# + 1 (failed) write
FLUSH STATUS;
REPLACE INTO t3 VALUES ('2011-01-01 00:00:04', 'Replace1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
REPLACE INTO t3 VALUES ('2011-01-01 00:00:04', 'Replace2');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
Handler_write	1
# 1 read_key + 1 update + 1 failed write
#
# Test of replace of default PK (delete might be needed first)
#
DELETE FROM t3 WHERE a = 0;
FLUSH STATUS;
SET TIMESTAMP = 1234567894;
REPLACE INTO t3 (b) VALUES ('Replace3');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
SET TIMESTAMP = 1234567894;
REPLACE INTO t3 (b) VALUES ('Replace4');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
Handler_write	1
# 1 read_key + 1 update + 1 failed write
SELECT * FROM t3;
a	b
2009-02-14 02:31:30	NULL
2009-02-14 02:31:31	NULL
2009-02-14 02:31:32	DUP_KEY
2009-02-14 02:31:33	Updated
2009-02-14 02:31:34	Replace4
2011-01-01 00:00:03	NULL
2011-01-01 00:00:04	Replace2
2011-01-01 00:00:05	DUP_KEY2
DROP TABLE t3;
#
# Test INSERT with DATETIME column DEFAULT UPDATE
#
CREATE TABLE t3
(a DATETIME DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP,
b char(10),
PRIMARY KEY (a))
PARTITION BY KEY (a) PARTITIONS 3;
SHOW CREATE TABLE t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `a` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `b` char(10) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY KEY (a)
PARTITIONS 3 */
FLUSH STATUS;
SET TIMESTAMP = 1234567890;
INSERT INTO t3 (a) VALUES (NOW());
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
SET TIMESTAMP = 1234567891;
INSERT INTO t3 VALUES ();
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:00');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:01');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:02'), ('2011-01-01 00:00:03');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	2
# 2 writes
FLUSH STATUS;
SET TIMESTAMP = 1234567892;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:00')
ON DUPLICATE KEY UPDATE b = "DUP_KEY";
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_write	2
# No pruning due to DEFAULT function on partitioning column
# 1 read_key + 1 delete + 2 write (1 failed + 1 ok)
# 1 delete + 1 write due to moved to different partition
FLUSH STATUS;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:01')
ON DUPLICATE KEY UPDATE a = '2011-01-01 00:00:05', b = "DUP_KEY2";
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_write	2
# No pruning due to updating partitioning field.
# 1 read_key + 1 delete + 2 write (1 failed + 1 ok)
# 1 delete + 1 write due to moved to different partition
FLUSH STATUS;
SET TIMESTAMP = 1234567893;
UPDATE t3 SET b = 'Updated' WHERE a = '2011-01-01 00:00:02';
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	2
Handler_read_rnd	1
Handler_update	1
# No pruning due to DEFAULT function on partitioning column
# 2 read_key + 1 read_rnd (1 read_key due to index lookup,
# 1 read_rnd + 1 read_key due to positioning before update)
# 1 delete + 1 write due to moved to different partition
# + 1 (failed) write
FLUSH STATUS;
REPLACE INTO t3 VALUES ('2011-01-01 00:00:04', 'Replace1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
REPLACE INTO t3 VALUES ('2011-01-01 00:00:04', 'Replace2');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
Handler_write	1
# 1 read_key + 1 update + 1 failed write
#
# Test of replace of default PK (delete might be needed first)
#
DELETE FROM t3 WHERE a = 0;
FLUSH STATUS;
SET TIMESTAMP = 1234567894;
REPLACE INTO t3 (b) VALUES ('Replace3');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
SET TIMESTAMP = 1234567894;
REPLACE INTO t3 (b) VALUES ('Replace4');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
Handler_write	1
# 1 read_key + 1 update + 1 failed write
SELECT * FROM t3;
a	b
0000-00-00 00:00:00	Replace4
2009-02-14 02:31:30	NULL
2009-02-14 02:31:32	DUP_KEY
2009-02-14 02:31:33	Updated
2011-01-01 00:00:03	NULL
2011-01-01 00:00:04	Replace2
2011-01-01 00:00:05	DUP_KEY2
DROP TABLE t3;
#
# Test INSERT with DATETIME column DEFAULT INSERT
#
CREATE TABLE t3
(a DATETIME DEFAULT CURRENT_TIMESTAMP,
b char(10),
PRIMARY KEY (a))
PARTITION BY KEY (a) PARTITIONS 3;
SHOW CREATE TABLE t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `a` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `b` char(10) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY KEY (a)
PARTITIONS 3 */
FLUSH STATUS;
SET TIMESTAMP = 1234567890;
INSERT INTO t3 (a) VALUES (NOW());
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
SET TIMESTAMP = 1234567891;
INSERT INTO t3 VALUES ();
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:00');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:01');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:02'), ('2011-01-01 00:00:03');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	2
# 2 writes
FLUSH STATUS;
SET TIMESTAMP = 1234567892;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:00')
ON DUPLICATE KEY UPDATE b = "DUP_KEY";
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
Handler_write	1
# 1 read_key + 1 update (same partition)
# 1 (failed) write
FLUSH STATUS;
INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:01')
ON DUPLICATE KEY UPDATE a = '2011-01-01 00:00:05', b = "DUP_KEY2";
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_write	2
# No pruning due to updating partitioning field.
# 1 read_key + 1 delete + 2 write (1 failed + 1 ok)
# 1 delete + 1 write due to moved to different partition
FLUSH STATUS;
SET TIMESTAMP = 1234567893;
UPDATE t3 SET b = 'Updated' WHERE a = '2011-01-01 00:00:02';
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
# 1 read_key + 1 update (same partition)
# 1 (failed) write
FLUSH STATUS;
REPLACE INTO t3 VALUES ('2011-01-01 00:00:04', 'Replace1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
REPLACE INTO t3 VALUES ('2011-01-01 00:00:04', 'Replace2');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
Handler_write	1
# 1 read_key + 1 update + 1 failed write
#
# Test of replace of default PK (delete might be needed first)
#
DELETE FROM t3 WHERE a = 0;
FLUSH STATUS;
SET TIMESTAMP = 1234567894;
REPLACE INTO t3 (b) VALUES ('Replace3');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
SET TIMESTAMP = 1234567894;
REPLACE INTO t3 (b) VALUES ('Replace4');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
Handler_write	1
# 1 read_key + 1 update + 1 failed write
SELECT * FROM t3;
a	b
2009-02-14 02:31:30	NULL
2009-02-14 02:31:31	NULL
2009-02-14 02:31:34	Replace4
2011-01-01 00:00:00	DUP_KEY
2011-01-01 00:00:02	Updated
2011-01-01 00:00:03	NULL
2011-01-01 00:00:04	Replace2
2011-01-01 00:00:05	DUP_KEY2
DROP TABLE t3;
SET sql_mode = default;
#
# Test INSERT SELECT
#
FLUSH STATUS;
TRUNCATE TABLE t2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	
Handler_delete	
Handler_external_lock	
Handler_prepare	
Handler_read_key	
Handler_read_next	
Handler_update	
Handler_write	
# All partitions needs to be locked
# 1 commit
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`a`)
PARTITIONS 13 */
FLUSH STATUS;
INSERT INTO t2 SELECT a, b FROM t1 WHERE a IN (1,4);
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	4
Handler_prepare	2
Handler_read_first	2
Handler_read_key	2
Handler_read_next	2
Handler_write	2
# All partitions in t2 needs to be locked (no propagation from t1 yet).
# 2 partitions in t1 needs to be locked (for 1 and 4)
# 2 read_first, read_key and read_next.
# 1 commit
#
# Test TRUNCATE PARTITION
#
FLUSH STATUS;
ALTER TABLE t2 TRUNCATE PARTITION p1;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	
Handler_delete	
Handler_external_lock	
Handler_prepare	
Handler_read_key	
Handler_read_next	
Handler_update	
Handler_write	
# Lots of lock acquisitions, reads and updated due to data-dictionary
# being updated.
# Warm-up data-dictionary cache.
FLUSH STATUS;
INSERT INTO t2 SELECT a, b FROM t1 WHERE a = 1;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	4
Handler_prepare	2
Handler_read_key	1
Handler_write	1
#
# Test insert on duplicated key update
#
FLUSH STATUS;
INSERT INTO t1 VALUES (65, "No duplicate")
ON DUPLICATE KEY UPDATE b = CONCAT(b, ", INSERT_DUP_KEY_UPDATE");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
# 1 write (insert)
# 1 commit
FLUSH STATUS;
INSERT INTO t1 VALUES (65, "No duplicate")
ON DUPLICATE KEY UPDATE b = CONCAT(b, ", INSERT_DUP_KEY_UPDATE");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
Handler_write	1
# 1 read_key
# 1 update
# 1 commit
FLUSH STATUS;
INSERT INTO t1 VALUES (78, "No duplicate")
ON DUPLICATE KEY UPDATE a = a + 13, b = CONCAT(b, ", INSERT_DUP_KEY_UPDATE");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
# If a partitioning column is updated, no pruning
# 1 write (insert)
# 1 commit
FLUSH STATUS;
INSERT INTO t1 VALUES (78, "No duplicate")
ON DUPLICATE KEY UPDATE a = a + 13, b = CONCAT(b, ", INSERT_DUP_KEY_UPDATE");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
Handler_write	1
# If partitioning column is updated, no pruning
# 1 read_key
# 1 update
# 1 commit
#
# Test of insert on duplicate key with failed update
#
FLUSH STATUS;
INSERT INTO t1 VALUES (78, "No duplicate")
ON DUPLICATE KEY UPDATE a = a + 13,
b = CONCAT(b, ", INSERT_DUP_KEY_UPDATE third");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
# If partitioning column is updated, no pruning
# 1 commit
FLUSH STATUS;
INSERT INTO t1 VALUES (78, "No duplicate")
ON DUPLICATE KEY UPDATE a = a + 13,
b = CONCAT(b, ", INSERT_DUP_KEY_UPDATE fail?");
ERROR 23000: Duplicate entry '91' for key 'PRIMARY'
VARIABLE_NAME	VARIABLE_VALUE
Handler_external_lock	2
Handler_read_key	1
Handler_rollback	1
Handler_update	1
Handler_write	1
# If partitioning column is updated, no pruning
# 1 read_key
# 1 update
# 1 rollback
#
# Test of insert on duplicate key with update to different partition
#
FLUSH STATUS;
INSERT INTO t1 VALUES (104, "No duplicate")
ON DUPLICATE KEY UPDATE a = a + 1;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
# If partitioning column is updated, no pruning
# 1 write
# 1 commit
FLUSH STATUS;
INSERT INTO t1 VALUES (104, "No duplicate")
ON DUPLICATE KEY UPDATE a = a + 1;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_write	2
# If partitioning column is updated, no pruning
# 1 delete
# 1 write
# 1 read_key
# 1 commit
FLUSH STATUS;
INSERT INTO t1 VALUES (104, "No duplicate 104")
ON DUPLICATE KEY UPDATE a = a + 1;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
# If partitioning column is updated, no pruning
# 1 write
# 1 commit
#
# Test of insert on duplicate key with failed update to different
# partition
#
FLUSH STATUS;
INSERT INTO t1 VALUES (104, "No duplicate 104 + 1")
ON DUPLICATE KEY UPDATE a = a + 1;
ERROR 23000: Duplicate entry '105' for key 'PRIMARY'
VARIABLE_NAME	VARIABLE_VALUE
Handler_external_lock	2
Handler_read_key	1
Handler_rollback	1
Handler_write	2
# If partitioning column is updated, no pruning
# 1 write
# 1 read_key
# 1 rollback
#
# Test replace
#
FLUSH STATUS;
REPLACE INTO t1 VALUES (5, "REPLACE first");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
# 1 write
# 1 commit
FLUSH STATUS;
REPLACE INTO t1 VALUES (5, "REPLACE second");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
Handler_write	1
# 1 write
# 1 read_key
# 1 update (NOTE: write_record() may cheat instead of delete/insert!)
# 1 rollback
#
# Test SELECT
#
FLUSH STATUS;
SELECT * FROM t1 ORDER BY a;
a	b
0	First row, p0
1	First row, p1
2	First row, p2
3	First row, p3
4	First row, p4
5	REPLACE second
13	Second row, p0
26	Third row, p0
39	Fourth row, p0
52	Fifth row, p0
65	No duplicate, INSERT_DUP_KEY_UPDATE
78	No duplicate
91	No duplicate, INSERT_DUP_KEY_UPDATE
104	No duplicate 104
105	No duplicate
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
Handler_read_first	13
Handler_read_key	13
Handler_read_next	15
# 13 read_first
# 13 read_key
# 15 read_next
FLUSH STATUS;
SELECT * FROM t1 WHERE a IN (0, 1, 4, 13, 26) ORDER BY a;
a	b
0	First row, p0
1	First row, p1
4	First row, p4
13	Second row, p0
26	Third row, p0
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
Handler_read_first	3
Handler_read_key	3
Handler_read_next	12
# 3 read_first, read_key
# 12 read_next
FLUSH STATUS;
SELECT * FROM t1 WHERE a IN (13, 26, 39, 52);
a	b
13	Second row, p0
26	Third row, p0
39	Fourth row, p0
52	Fifth row, p0
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
Handler_read_first	1
Handler_read_key	1
Handler_read_next	9
# 1 read_first, read_key
# 9 read_next
FLUSH STATUS;
SELECT * FROM t1 WHERE a = 3;
a	b
3	First row, p3
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
Handler_read_key	1
# 1 read_key
FLUSH STATUS;
SELECT * FROM t1 WHERE b LIKE 'First%' ORDER BY a;
a	b
0	First row, p0
1	First row, p1
2	First row, p2
3	First row, p3
4	First row, p4
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
Handler_read_key	13
Handler_read_next	5
# 13 read_key
# 5 read_next
#
# Test EXPLAIN SELECT
#
FLUSH STATUS;
EXPLAIN SELECT * FROM t1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12	index	NULL	b	515	NULL	#	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
FLUSH STATUS;
EXPLAIN SELECT * FROM t1 WHERE a IN (0, 1, 4, 13, 26) ORDER BY a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	p0,p1,p4	index	PRIMARY	PRIMARY	4	NULL	#	41.67	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` in (0,1,4,13,26)) order by `test`.`t1`.`a`
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
FLUSH STATUS;
EXPLAIN SELECT * FROM t1 WHERE a IN (13, 26, 39, 52);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	p0	index	PRIMARY	b	515	NULL	#	44.44	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` in (13,26,39,52))
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
FLUSH STATUS;
EXPLAIN SELECT * FROM t1 WHERE a = 3;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	p3	const	PRIMARY	PRIMARY	4	const	#	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select '3' AS `a`,'First row, p3' AS `b` from `test`.`t1` where 1
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
Handler_read_key	1
FLUSH STATUS;
EXPLAIN SELECT * FROM t1 WHERE b LIKE 'First%' ORDER BY a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12	range	b	b	515	NULL	#	100.00	Using where; Using index; Using filesort
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`b` like 'First%') order by `test`.`t1`.`a`
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
#
# Test pruning of non static values
# They will need to lock all partitions, but will allow scan pruning
# due to a second pruning call in optimize.
#
CREATE TABLE t3 (a INT);
INSERT INTO t3 VALUES (1);
FLUSH STATUS;
SELECT * FROM t1 WHERE a = (SELECT a FROM t3);
a	b
1	First row, p1
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_first	1
Handler_read_key	2
Handler_read_rnd_next	2
# 1 read_first (NOTE only reads from one partition!)
# 2 read_key
# 2 read_rnd_next
FLUSH STATUS;
SELECT t1.a FROM t1 INNER JOIN t3 ON t1.a = t3.a;
a
1
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_first	1
Handler_read_key	2
Handler_read_rnd_next	2
# 1 read_first (NOTE only reads from one partition!)
# 2 read_key
FLUSH STATUS;
EXPLAIN SELECT t1.a, t1.b FROM t1 INNER JOIN t3 ON t1.a = t3.a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t3	NULL	ALL	NULL	NULL	NULL	NULL	#	100.00	Using where
1	SIMPLE	t1	p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	#	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t3` where (`test`.`t1`.`a` = `test`.`t3`.`a`)
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
FLUSH STATUS;
EXPLAIN SELECT * FROM t1 WHERE a = (SELECT a FROM t3);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	p1	const	PRIMARY	PRIMARY	4	const	#	100.00	NULL
2	SUBQUERY	t3	NULL	ALL	NULL	NULL	NULL	NULL	#	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select '1' AS `a`,'First row, p1' AS `b` from `test`.`t1` where 1
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_first	1
Handler_read_key	2
Handler_read_rnd_next	2
FLUSH STATUS;
SELECT * FROM t1 WHERE a = 1;
a	b
1	First row, p1
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
Handler_read_key	1
# 1 read_key
FLUSH STATUS;
SELECT * FROM t1 WHERE a = (SELECT COUNT(*) FROM t3);
a	b
1	First row, p1
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	1
# 1 read_first
# 2 read_key, read_rnd_next
#
# Test of non indexed partition column
#
CREATE TABLE t4 SELECT a, b FROM t1;
ALTER TABLE t4 PARTITION BY HASH (a) PARTITIONS 5;
SHOW CREATE TABLE t4;
Table	Create Table
t4	CREATE TABLE `t4` (
  `a` int(11) NOT NULL,
  `b` varchar(128) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`a`)
PARTITIONS 5 */
FLUSH STATUS;
SELECT * FROM t4 WHERE a = (SELECT a FROM t3);
a	b
1	First row, p1
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_first	2
Handler_read_key	2
Handler_read_rnd_next	5
# 2 read_first, read_key
FLUSH STATUS;
EXPLAIN SELECT * FROM t4 WHERE a = (SELECT a FROM t3);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t4	p1	ALL	NULL	NULL	NULL	NULL	#	33.33	Using where
2	SUBQUERY	t3	NULL	ALL	NULL	NULL	NULL	NULL	#	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t4` where (`test`.`t4`.`a` = (/* select#2 */ select `test`.`t3`.`a` from `test`.`t3`))
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_first	1
Handler_read_key	1
Handler_read_rnd_next	2
INSERT INTO t3 VALUES (3);
SELECT * FROM t4 WHERE a = (SELECT a FROM t3);
ERROR 21000: Subquery returns more than 1 row
EXPLAIN SELECT * FROM t4 WHERE a = (SELECT a FROM t3);
ERROR 21000: Subquery returns more than 1 row
EXPLAIN SELECT * FROM t4 WHERE a = (SELECT a FROM t3 LIMIT 1);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t4	p1	ALL	NULL	NULL	NULL	NULL	#	33.33	Using where
2	SUBQUERY	t3	NULL	ALL	NULL	NULL	NULL	NULL	#	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t4` where (`test`.`t4`.`a` = (/* select#2 */ select `test`.`t3`.`a` from `test`.`t3` limit 1))
EXPLAIN SELECT * FROM t4 WHERE a = (SELECT MAX(a) FROM t3);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t4	p3	ALL	NULL	NULL	NULL	NULL	#	33.33	Using where
2	SUBQUERY	t3	NULL	ALL	NULL	NULL	NULL	NULL	#	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t4` where (`test`.`t4`.`a` = (/* select#2 */ select max(`test`.`t3`.`a`) from `test`.`t3`))
DROP TABLE t3;
DROP TABLE t4;
#
# Test derived tables like SELECT * FROM (SELECT * FROM ...)
#
set @optimizer_switch_saved=@@optimizer_switch;
set optimizer_switch='derived_merge=off';
FLUSH STATUS;
SELECT * FROM (SELECT * FROM t1 WHERE a IN (0,2,3,13,26)) t3;
a	b
0	First row, p0
13	Second row, p0
26	Third row, p0
2	First row, p2
3	First row, p3
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
Handler_read_first	3
Handler_read_key	3
Handler_read_next	11
Handler_read_rnd_next	6
Handler_write	5
# 3 read_first, read_key
# 11 read_next
# 6 read_rnd_next (tmp table)
FLUSH STATUS;
SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0,2,3,13,26)) t3) t4;
a	b
0	First row, p0
13	Second row, p0
26	Third row, p0
2	First row, p2
3	First row, p3
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
Handler_read_first	3
Handler_read_key	3
Handler_read_next	11
Handler_read_rnd_next	12
Handler_write	10
# 3 read_first, read_key
# 11 read_next
# 12 read_rnd_next (tmp table)
#
# Test EXPLAIN SELECT * FROM (SELECT * FROM ...)
#
FLUSH STATUS;
EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a IN (0,2,3,13,26)) t3;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	NULL	ALL	NULL	NULL	NULL	NULL	#	100.00	NULL
2	DERIVED	t1	p0,p2,p3	index	PRIMARY	b	515	NULL	#	45.45	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select `t3`.`a` AS `a`,`t3`.`b` AS `b` from (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` in (0,2,3,13,26))) `t3`
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
FLUSH STATUS;
EXPLAIN SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0,2,3,13,26)) t3) t4;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	NULL	ALL	NULL	NULL	NULL	NULL	#	100.00	NULL
2	DERIVED	<derived3>	NULL	ALL	NULL	NULL	NULL	NULL	#	100.00	NULL
3	DERIVED	t1	p0,p2,p3	index	PRIMARY	b	515	NULL	#	45.45	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select `t4`.`a` AS `a`,`t4`.`b` AS `b` from (/* select#2 */ select `t3`.`a` AS `a`,`t3`.`b` AS `b` from (/* select#3 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` in (0,2,3,13,26))) `t3`) `t4`
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
#
# Test SELECT ... UNION SELECT ...
#
FLUSH STATUS;
SELECT * FROM t1 UNION SELECT * FROM t2;
a	b
0	First row, p0
1	First row, p1
104	No duplicate 104
105	No duplicate
13	Second row, p0
2	First row, p2
26	Third row, p0
3	First row, p3
39	Fourth row, p0
4	First row, p4
5	REPLACE second
52	Fifth row, p0
65	No duplicate, INSERT_DUP_KEY_UPDATE
78	No duplicate
91	No duplicate, INSERT_DUP_KEY_UPDATE
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_first	26
Handler_read_key	26
Handler_read_next	15
Handler_read_rnd_next	18
Handler_write	17
FLUSH STATUS;
SELECT * FROM t1 WHERE a IN (0, 1, 13, 4, 26) UNION SELECT * FROM t2;
a	b
0	First row, p0
13	Second row, p0
26	Third row, p0
1	First row, p1
4	First row, p4
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_first	16
Handler_read_key	16
Handler_read_next	12
Handler_read_rnd_next	8
Handler_write	7
FLUSH STATUS;
SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4, 26)) t3 UNION SELECT * FROM t2;
a	b
0	First row, p0
13	Second row, p0
26	Third row, p0
1	First row, p1
4	First row, p4
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_first	16
Handler_read_key	16
Handler_read_next	12
Handler_read_rnd_next	14
Handler_write	12
FLUSH STATUS;
SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4, 26) UNION SELECT * FROM t2) t3) t4;
a	b
0	First row, p0
13	Second row, p0
26	Third row, p0
1	First row, p1
4	First row, p4
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_first	16
Handler_read_key	16
Handler_read_next	12
Handler_read_rnd_next	20
Handler_write	17
FLUSH STATUS;
SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4, 26)) t3 UNION SELECT * FROM t2) t4;
a	b
0	First row, p0
13	Second row, p0
26	Third row, p0
1	First row, p1
4	First row, p4
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_first	16
Handler_read_key	16
Handler_read_next	12
Handler_read_rnd_next	20
Handler_write	17
FLUSH STATUS;
SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4, 26)) t3) t4 UNION SELECT * FROM t2;
a	b
0	First row, p0
13	Second row, p0
26	Third row, p0
1	First row, p1
4	First row, p4
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_first	16
Handler_read_key	16
Handler_read_next	12
Handler_read_rnd_next	20
Handler_write	17
FLUSH STATUS;
SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4, 26)) t3 UNION SELECT * FROM t2 WHERE a = 1) t4;
a	b
0	First row, p0
13	Second row, p0
26	Third row, p0
1	First row, p1
4	First row, p4
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_first	3
Handler_read_key	4
Handler_read_next	12
Handler_read_rnd_next	18
Handler_write	16
#
# Test EXPLAIN SELECT ... UNION SELECT ...
#
FLUSH STATUS;
EXPLAIN SELECT * FROM t1 UNION SELECT * FROM t2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12	index	NULL	b	515	NULL	#	100.00	Using index
2	UNION	t2	p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12	ALL	NULL	NULL	NULL	NULL	#	100.00	NULL
NULL	UNION RESULT	<union1,2>	NULL	ALL	NULL	NULL	NULL	NULL	#	NULL	Using temporary
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` union /* select#2 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2`
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
FLUSH STATUS;
EXPLAIN SELECT * FROM t1 WHERE a IN (0, 1, 13, 4, 26) UNION SELECT * FROM t2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	p0,p1,p4	index	PRIMARY	b	515	NULL	#	41.67	Using where; Using index
2	UNION	t2	p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12	ALL	NULL	NULL	NULL	NULL	#	100.00	NULL
NULL	UNION RESULT	<union1,2>	NULL	ALL	NULL	NULL	NULL	NULL	#	NULL	Using temporary
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` in (0,1,13,4,26)) union /* select#2 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2`
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
FLUSH STATUS;
EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4, 26)) t3 UNION SELECT * FROM t2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	NULL	ALL	NULL	NULL	NULL	NULL	#	100.00	NULL
2	DERIVED	t1	p0,p1,p4	index	PRIMARY	b	515	NULL	#	41.67	Using where; Using index
3	UNION	t2	p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12	ALL	NULL	NULL	NULL	NULL	#	100.00	NULL
NULL	UNION RESULT	<union1,3>	NULL	ALL	NULL	NULL	NULL	NULL	#	NULL	Using temporary
Warnings:
Note	1003	/* select#1 */ select `t3`.`a` AS `a`,`t3`.`b` AS `b` from (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` in (0,1,13,4,26))) `t3` union /* select#3 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2`
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
FLUSH STATUS;
EXPLAIN SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4, 26) UNION SELECT * FROM t2) t3) t4;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	NULL	ALL	NULL	NULL	NULL	NULL	#	100.00	NULL
2	DERIVED	<derived3>	NULL	ALL	NULL	NULL	NULL	NULL	#	100.00	NULL
3	DERIVED	t1	p0,p1,p4	index	PRIMARY	b	515	NULL	#	41.67	Using where; Using index
4	UNION	t2	p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12	ALL	NULL	NULL	NULL	NULL	#	100.00	NULL
NULL	UNION RESULT	<union3,4>	NULL	ALL	NULL	NULL	NULL	NULL	#	NULL	Using temporary
Warnings:
Note	1003	/* select#1 */ select `t4`.`a` AS `a`,`t4`.`b` AS `b` from (/* select#2 */ select `t3`.`a` AS `a`,`t3`.`b` AS `b` from (/* select#3 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` in (0,1,13,4,26)) union /* select#4 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2`) `t3`) `t4`
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
FLUSH STATUS;
EXPLAIN SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4, 26)) t3 UNION SELECT * FROM t2) t4;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	NULL	ALL	NULL	NULL	NULL	NULL	#	100.00	NULL
2	DERIVED	<derived3>	NULL	ALL	NULL	NULL	NULL	NULL	#	100.00	NULL
3	DERIVED	t1	p0,p1,p4	index	PRIMARY	b	515	NULL	#	41.67	Using where; Using index
4	UNION	t2	p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12	ALL	NULL	NULL	NULL	NULL	#	100.00	NULL
NULL	UNION RESULT	<union2,4>	NULL	ALL	NULL	NULL	NULL	NULL	#	NULL	Using temporary
Warnings:
Note	1003	/* select#1 */ select `t4`.`a` AS `a`,`t4`.`b` AS `b` from (/* select#2 */ select `t3`.`a` AS `a`,`t3`.`b` AS `b` from (/* select#3 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` in (0,1,13,4,26))) `t3` union /* select#4 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2`) `t4`
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
FLUSH STATUS;
EXPLAIN SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4, 26)) t3) t4 UNION SELECT * FROM t2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	NULL	ALL	NULL	NULL	NULL	NULL	#	100.00	NULL
2	DERIVED	<derived3>	NULL	ALL	NULL	NULL	NULL	NULL	#	100.00	NULL
3	DERIVED	t1	p0,p1,p4	index	PRIMARY	b	515	NULL	#	41.67	Using where; Using index
4	UNION	t2	p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12	ALL	NULL	NULL	NULL	NULL	#	100.00	NULL
NULL	UNION RESULT	<union1,4>	NULL	ALL	NULL	NULL	NULL	NULL	#	NULL	Using temporary
Warnings:
Note	1003	/* select#1 */ select `t4`.`a` AS `a`,`t4`.`b` AS `b` from (/* select#2 */ select `t3`.`a` AS `a`,`t3`.`b` AS `b` from (/* select#3 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` in (0,1,13,4,26))) `t3`) `t4` union /* select#4 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2`
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
FLUSH STATUS;
EXPLAIN SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE a IN (0, 1, 13, 4, 26)) t3 UNION SELECT * FROM t2 WHERE a = 1) t4;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	NULL	ALL	NULL	NULL	NULL	NULL	#	100.00	NULL
2	DERIVED	<derived3>	NULL	ALL	NULL	NULL	NULL	NULL	#	100.00	NULL
3	DERIVED	t1	p0,p1,p4	index	PRIMARY	b	515	NULL	#	41.67	Using where; Using index
4	UNION	t2	p1	const	PRIMARY	PRIMARY	4	const	#	100.00	NULL
NULL	UNION RESULT	<union2,4>	NULL	ALL	NULL	NULL	NULL	NULL	#	NULL	Using temporary
Warnings:
Note	1003	/* select#1 */ select `t4`.`a` AS `a`,`t4`.`b` AS `b` from (/* select#2 */ select `t3`.`a` AS `a`,`t3`.`b` AS `b` from (/* select#3 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` in (0,1,13,4,26))) `t3` union /* select#4 */ select '1' AS `a`,'First row, p1' AS `b` from `test`.`t2` where 1) `t4`
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	1
set @@optimizer_switch=@optimizer_switch_saved;
#
# Test UPDATE
#
SELECT * FROM t1 ORDER BY a;
a	b
0	First row, p0
1	First row, p1
2	First row, p2
3	First row, p3
4	First row, p4
5	REPLACE second
13	Second row, p0
26	Third row, p0
39	Fourth row, p0
52	Fifth row, p0
65	No duplicate, INSERT_DUP_KEY_UPDATE
78	No duplicate
91	No duplicate, INSERT_DUP_KEY_UPDATE
104	No duplicate 104
105	No duplicate
# This should be prunable (does not change the partitioning key)
FLUSH STATUS;
UPDATE t1 SET b = CONCAT(b, ", updated 1") WHERE a IN (13, 26, 39, 52);
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	4
Handler_update	4
# 4 read_key
# 4 update
#
# This should not be prunable (only after implementing 'update pruning')
# i.e if all changed partitioning field is set to constant values,
# set lock_partitions to be a union of read_partition and the matching
# partition for the constants. Easy if all partitioning fields are set,
# probably needs a second round of prune_partitions() with these fields
# set to see if possible to prune locks.
FLUSH STATUS;
UPDATE t1 SET a = 99, b = CONCAT(b, ", updated 2 -> p8") WHERE a = 13;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	2
Handler_read_rnd	1
Handler_write	1
# 2 read_key
# 1 read_rnd
# 1 delete (due to moved to another partition)
# 1 write
#
# This should use ha_update_row instead of ha_write_row + ha_delete_row
FLUSH STATUS;
UPDATE t1 SET a = 13 + 99, b = CONCAT(b, ", updated 3") WHERE a = 99;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	2
Handler_read_rnd	1
Handler_update	1
# 2 read_key
# 1 read_rnd
# 1 update
#
# This should not be prunable (only after implementing
# 'optimized update pruning', which will probably never happen, since
# it depends on which partitioning type is used (for this only hash is
# simple, but range and list is possible, key is very hard)
FLUSH STATUS;
UPDATE t1 SET a = a + 1, b = CONCAT(b, ", updated 4 -> p9") WHERE a = 112;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	2
Handler_read_rnd	1
Handler_write	1
# 2 read_key
# 1 read_rnd
# 1 delete (due to moved to another partition)
# 1 write
FLUSH STATUS;
UPDATE t1 SET b = CONCAT(b, ", same as min(a) + 2 in t2") WHERE a = (SELECT MIN(a) + 2 FROM t2);
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	4
Handler_prepare	2
Handler_read_first	13
Handler_read_key	14
Handler_update	1
FLUSH STATUS;
UPDATE t1 SET b = CONCAT(b, ", max(a) in t2: ", (SELECT MAX(a) FROM t2)) WHERE a = 5;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	4
Handler_prepare	2
Handler_read_key	14
Handler_read_last	13
Handler_update	1
#
# Test multi table UPDATE
#
SELECT * FROM t1 ORDER BY a;
a	b
0	First row, p0
1	First row, p1
2	First row, p2
3	First row, p3, same as min(a) + 2 in t2
4	First row, p4
5	REPLACE second, max(a) in t2: 4
26	Third row, p0, updated 1
39	Fourth row, p0, updated 1
52	Fifth row, p0, updated 1
65	No duplicate, INSERT_DUP_KEY_UPDATE
78	No duplicate
91	No duplicate, INSERT_DUP_KEY_UPDATE
104	No duplicate 104
105	No duplicate
113	Second row, p0, updated 1, updated 2 -> p8, updated 3, updated 4 -> p9
SELECT * FROM t2 ORDER BY a;
a	b
1	First row, p1
4	First row, p4
FLUSH STATUS;
EXPLAIN UPDATE t1, t2
SET t1.b = CONCAT(t1.b, ", t2.b:", t2.b),
t2.b = CONCAT(t2.b, ", t1.b:", t1.b)
WHERE t2.b = t1.b and t2.a = 4;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t2	p4	const	PRIMARY	PRIMARY	4	const	#	100.00	NULL
1	UPDATE	t1	p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12	ref	b	b	515	const	#	100.00	NULL
Warnings:
Note	1003	update `test`.`t1` join `test`.`t2` set `test`.`t1`.`b` = concat(`test`.`t1`.`b`,', t2.b:','First row, p4'),`test`.`t2`.`b` = concat('First row, p4',', t1.b:',`test`.`t1`.`b`) where ((`test`.`t1`.`b` = `test`.`t2`.`b`))
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	1
FLUSH STATUS;
EXPLAIN UPDATE t1, t2
SET t1.b = CONCAT(t1.b, ", t2.b:", t2.b),
t2.b = CONCAT(t2.b, ", t1.b:", t1.b)
WHERE t2.b = t1.b and t2.a = 4;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t2	p4	const	PRIMARY	PRIMARY	4	const	#	100.00	NULL
1	UPDATE	t1	p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12	ref	b	b	515	const	#	100.00	NULL
Warnings:
Note	1003	update `test`.`t1` join `test`.`t2` set `test`.`t1`.`b` = concat(`test`.`t1`.`b`,', t2.b:','First row, p4'),`test`.`t2`.`b` = concat('First row, p4',', t1.b:',`test`.`t1`.`b`) where ((`test`.`t1`.`b` = `test`.`t2`.`b`))
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	1
FLUSH STATUS;
UPDATE t1, t2
SET t1.b = CONCAT(t1.b, ", t2.b:", t2.b),
t2.b = CONCAT(t2.b, ", t1.b:", t1.b)
WHERE t2.b = t1.b and t2.a = 4;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	4
Handler_prepare	2
Handler_read_key	15
Handler_read_next	1
Handler_read_rnd	1
Handler_read_rnd_next	2
Handler_update	2
Handler_write	1
# 15 read_key
# 1 read_next, read_rnd
# 2 read_rnd_next
# 2 update
#
# Test of views
#
FLUSH STATUS;
CREATE VIEW v1_25 AS SELECT a, b FROM t1 PARTITION (p2, p5);
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	7
Handler_external_lock	72
Handler_prepare	4
Handler_read_key	3
Handler_write	4
# 34 locks (dictionary related)
FLUSH STATUS;
CREATE VIEW v1_25_check AS SELECT a, b FROM t1 PARTITION (p2, p5) t1_alias WITH CHECK OPTION;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	7
Handler_external_lock	72
Handler_prepare	4
Handler_read_key	3
Handler_write	4
# 34 locks (dictionary related)
FLUSH STATUS;
CREATE VIEW v1_9 AS SELECT a, b FROM t1 WHERE a = 9;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	7
Handler_external_lock	72
Handler_prepare	4
Handler_read_key	3
Handler_write	4
# 34 locks (dictionary related)
FLUSH STATUS;
CREATE VIEW v1_9_check AS SELECT a, b FROM t1 WHERE a = 9 WITH CHECK OPTION;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	7
Handler_external_lock	72
Handler_prepare	4
Handler_read_key	3
Handler_write	4
# 34 locks (dictionary related)
FLUSH STATUS;
CREATE VIEW v1_all AS SELECT a, b FROM t1;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	7
Handler_external_lock	72
Handler_prepare	4
Handler_read_key	3
Handler_write	4
# 34 locks (dictionary related)
SELECT TABLE_NAME, CHECK_OPTION, IS_UPDATABLE, VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME LIKE 'v1_%';
TABLE_NAME	CHECK_OPTION	IS_UPDATABLE	VIEW_DEFINITION
v1_25	NONE	YES	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` PARTITION (`p2`,`p5`)
v1_25_check	CASCADED	YES	select `t1_alias`.`a` AS `a`,`t1_alias`.`b` AS `b` from `test`.`t1` PARTITION (`p2`,`p5`) `t1_alias`
v1_9	NONE	YES	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` = 9)
v1_9_check	CASCADED	YES	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` = 9)
v1_all	NONE	YES	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`
SHOW CREATE VIEW v1_all;
View	Create View	character_set_client	collation_connection
v1_all	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1_all` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1`	utf8mb4	utf8mb4_0900_ai_ci
FLUSH STATUS;
INSERT INTO v1_all VALUES (23, "Insert in v1_all");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
SHOW CREATE VIEW v1_25;
View	Create View	character_set_client	collation_connection
v1_25	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1_25` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` PARTITION (`p2`,`p5`)	utf8mb4	utf8mb4_0900_ai_ci
FLUSH STATUS;
INSERT INTO v1_25 VALUES (18, "Insert in v1_25");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO v1_25 VALUES (17, "Insert in v1_25 fail");
ERROR HY000: Found a row not matching the given partition set
VARIABLE_NAME	VARIABLE_VALUE
Handler_external_lock	2
FLUSH STATUS;
INSERT IGNORE INTO v1_25 VALUES (17, "Insert ignore in v1_25");
Warnings:
Warning	1748	Found a row not matching the given partition set
VARIABLE_NAME	VARIABLE_VALUE
Handler_external_lock	2
SHOW CREATE VIEW v1_25_check;
View	Create View	character_set_client	collation_connection
v1_25_check	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1_25_check` AS select `t1_alias`.`a` AS `a`,`t1_alias`.`b` AS `b` from `t1` PARTITION (`p2`,`p5`) `t1_alias` WITH CASCADED CHECK OPTION	utf8mb4	utf8mb4_0900_ai_ci
FLUSH STATUS;
INSERT INTO v1_25_check VALUES (31, "Insert in v1_25_check");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO v1_25_check VALUES (30, "Insert in v1_25_check fail");
ERROR HY000: Found a row not matching the given partition set
VARIABLE_NAME	VARIABLE_VALUE
Handler_external_lock	2
FLUSH STATUS;
INSERT IGNORE INTO v1_25_check VALUES (30, "Insert ignore in v1_25_check");
Warnings:
Warning	1748	Found a row not matching the given partition set
VARIABLE_NAME	VARIABLE_VALUE
Handler_external_lock	2
SHOW CREATE VIEW v1_9;
View	Create View	character_set_client	collation_connection
v1_9	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1_9` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where (`t1`.`a` = 9)	utf8mb4	utf8mb4_0900_ai_ci
FLUSH STATUS;
INSERT INTO v1_9 VALUES (9, "Insert in v1_9");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO v1_9 VALUES (8, "Insert in v1_9 NO CHECK!");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
SELECT * FROM t1 WHERE a = 8;
a	b
8	Insert in v1_9 NO CHECK!
# DELETE will not find row not in view
SHOW CREATE VIEW v1_9_check;
View	Create View	character_set_client	collation_connection
v1_9_check	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1_9_check` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` where (`t1`.`a` = 9) WITH CASCADED CHECK OPTION	utf8mb4	utf8mb4_0900_ai_ci
FLUSH STATUS;
DELETE FROM v1_9_check WHERE a = 8;
VARIABLE_NAME	VARIABLE_VALUE
# 0 locks, impossible where!
EXPLAIN DELETE FROM v1_9_check WHERE a = 8;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	#	NULL	No matching rows after partition pruning
Warnings:
Note	1003	delete from (`test`.`t1`) where ((`test`.`t1`.`a` = 8) and (`test`.`t1`.`a` = 9))
EXPLAIN SELECT * FROM v1_9_check WHERE a = 8;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	#	NULL	Impossible WHERE
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where 0
SELECT * FROM t1 WHERE a = 8;
a	b
8	Insert in v1_9 NO CHECK!
FLUSH STATUS;
INSERT INTO v1_9_check VALUES (10, "Insert in v1_9_check fail");
ERROR HY000: CHECK OPTION failed 'test.v1_9_check'
VARIABLE_NAME	VARIABLE_VALUE
Handler_external_lock	2
Handler_rollback	1
SELECT * FROM t1 WHERE a = 9;
a	b
9	Insert in v1_9
FLUSH STATUS;
DELETE FROM v1_9_check WHERE a = 9;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
FLUSH STATUS;
INSERT INTO v1_9_check VALUES (9, "Insert in v1_9_check");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
SELECT * FROM v1_9;
a	b
9	Insert in v1_9_check
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
Handler_read_key	1
FLUSH STATUS;
SELECT * FROM v1_25;
a	b
18	Insert in v1_25
2	First row, p2
31	Insert in v1_25_check
5	REPLACE second, max(a) in t2: 4
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
Handler_read_first	2
Handler_read_key	2
Handler_read_next	4
FLUSH STATUS;
SELECT * FROM v1_all;
a	b
0	First row, p0
1	First row, p1
104	No duplicate 104
105	No duplicate
113	Second row, p0, updated 1, updated 2 -> p8, updated 3, updated 4 -> p9
18	Insert in v1_25
2	First row, p2
23	Insert in v1_all
26	Third row, p0, updated 1
3	First row, p3, same as min(a) + 2 in t2
31	Insert in v1_25_check
39	Fourth row, p0, updated 1
4	First row, p4, t2.b:First row, p4
5	REPLACE second, max(a) in t2: 4
52	Fifth row, p0, updated 1
65	No duplicate, INSERT_DUP_KEY_UPDATE
78	No duplicate
8	Insert in v1_9 NO CHECK!
9	Insert in v1_9_check
91	No duplicate, INSERT_DUP_KEY_UPDATE
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
Handler_read_first	13
Handler_read_key	13
Handler_read_next	20
DROP VIEW v1_all;
DROP VIEW v1_9, v1_9_check;
DROP VIEW v1_25, v1_25_check;
#
# Test CREATE SELECT
#
FLUSH STATUS;
CREATE TABLE t3 SELECT a, b FROM t1 WHERE a IN (0, 1, 13, 113, 26);
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	13
Handler_external_lock	164
Handler_prepare	4
Handler_read_first	3
Handler_read_key	25
Handler_read_next	12
Handler_update	12
Handler_write	18
SELECT * FROM t3 ORDER BY a;
a	b
0	First row, p0
1	First row, p1
26	Third row, p0, updated 1
113	Second row, p0, updated 1, updated 2 -> p8, updated 3, updated 4 -> p9
DROP TABLE t3;
FLUSH STATUS;
CREATE TABLE t3 SELECT a, b FROM t1 WHERE b LIKE 'First%';
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	13
Handler_external_lock	164
Handler_prepare	4
Handler_read_key	35
Handler_read_next	5
Handler_update	12
Handler_write	19
SELECT * FROM t3 ORDER BY a;
a	b
0	First row, p0
1	First row, p1
2	First row, p2
3	First row, p3, same as min(a) + 2 in t2
4	First row, p4, t2.b:First row, p4
DROP TABLE t3;
#
# Test Stored procedures
#
CREATE PROCEDURE sp_insert(a INT, b CHAR(16))
INSERT INTO test.t1 VALUES (a, b);
CREATE PROCEDURE sp_insert_partition(p CHAR(16), a INT, b CHAR(16))
BEGIN
SET @str = CONCAT("INSERT INTO test.t1 PARTITION(", p, ") VALUES (?, ?)");
SET @x = a, @y = b;
PREPARE stmt FROM @str;
EXECUTE stmt USING @x, @y;
DEALLOCATE PREPARE stmt;
END|
CREATE PROCEDURE sp_select_all()
SELECT * FROM test.t1;
CREATE PROCEDURE sp_select_exact(x INT)
SELECT * FROM test.t1 WHERE a = x;
CREATE PROCEDURE sp_select_partition(p CHAR(16))
BEGIN
SET @str = CONCAT("SELECT * FROM test.t1 PARTITION(", p, ")");
PREPARE stmt FROM @str;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END|
CREATE PROCEDURE sp_select_range(x INT, y INT)
SELECT * FROM test.t1 WHERE a between x and y;
# Warm-up data-dictionary cache.
FLUSH STATUS;
CALL sp_insert(313,"Test313");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
CALL sp_insert_partition("p7", 98, "Test98");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
CALL sp_insert_partition("p8", 111, "Test111");
ERROR HY000: Found a row not matching the given partition set
VARIABLE_NAME	VARIABLE_VALUE
Handler_external_lock	2
# no proc locking since already in proc cache.
FLUSH STATUS;
CALL sp_insert_partition("p7,p8", 111, "Test111");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
CALL sp_select_all();
a	b
0	First row, p0
1	First row, p1
104	No duplicate 104
105	No duplicate
111	Test111
113	Second row, p0, updated 1, updated 2 -> p8, updated 3, updated 4 -> p9
18	Insert in v1_25
2	First row, p2
23	Insert in v1_all
26	Third row, p0, updated 1
3	First row, p3, same as min(a) + 2 in t2
31	Insert in v1_25_check
313	Test313
39	Fourth row, p0, updated 1
4	First row, p4, t2.b:First row, p4
5	REPLACE second, max(a) in t2: 4
52	Fifth row, p0, updated 1
65	No duplicate, INSERT_DUP_KEY_UPDATE
78	No duplicate
8	Insert in v1_9 NO CHECK!
9	Insert in v1_9_check
91	No duplicate, INSERT_DUP_KEY_UPDATE
98	Test98
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
Handler_read_first	13
Handler_read_key	13
Handler_read_next	23
FLUSH STATUS;
CALL sp_select_exact(98);
a	b
98	Test98
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
Handler_read_key	1
FLUSH STATUS;
CALL sp_select_partition("p7");
a	b
111	Test111
98	Test98
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
Handler_read_first	1
Handler_read_key	1
Handler_read_next	2
FLUSH STATUS;
CALL sp_select_partition("p8");
a	b
8	Insert in v1_9 NO CHECK!
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
Handler_read_first	1
Handler_read_key	1
Handler_read_next	1
# no proc locking since already in proc cache.
FLUSH STATUS;
CALL sp_select_partition("p7,p8");
a	b
111	Test111
8	Insert in v1_9 NO CHECK!
98	Test98
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
Handler_read_first	2
Handler_read_key	2
Handler_read_next	3
FLUSH STATUS;
CALL sp_select_range(1,5);
a	b
1	First row, p1
2	First row, p2
3	First row, p3, same as min(a) + 2 in t2
4	First row, p4, t2.b:First row, p4
5	REPLACE second, max(a) in t2: 4
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
Handler_read_key	5
Handler_read_next	5
DROP PROCEDURE sp_insert;
DROP PROCEDURE sp_insert_partition;
DROP PROCEDURE sp_select_all;
DROP PROCEDURE sp_select_partition;
DROP PROCEDURE sp_select_range;
DROP PROCEDURE sp_select_exact;
#
# Test EXPLAIN DELETE
#
SELECT * FROM t1 ORDER BY a;
a	b
0	First row, p0
1	First row, p1
2	First row, p2
3	First row, p3, same as min(a) + 2 in t2
4	First row, p4, t2.b:First row, p4
5	REPLACE second, max(a) in t2: 4
8	Insert in v1_9 NO CHECK!
9	Insert in v1_9_check
18	Insert in v1_25
23	Insert in v1_all
26	Third row, p0, updated 1
31	Insert in v1_25_check
39	Fourth row, p0, updated 1
52	Fifth row, p0, updated 1
65	No duplicate, INSERT_DUP_KEY_UPDATE
78	No duplicate
91	No duplicate, INSERT_DUP_KEY_UPDATE
98	Test98
104	No duplicate 104
105	No duplicate
111	Test111
113	Second row, p0, updated 1, updated 2 -> p8, updated 3, updated 4 -> p9
313	Test313
FLUSH STATUS;
EXPLAIN DELETE FROM t1 WHERE a = 105;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	t1	p1	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where
Warnings:
Note	1003	delete from `test`.`t1` where (`test`.`t1`.`a` = 105)
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
FLUSH STATUS;
EXPLAIN DELETE FROM t1 WHERE b = "No duplicate";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	t1	p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12	range	b	b	515	const	#	100.00	Using where
Warnings:
Note	1003	delete from `test`.`t1` where (`test`.`t1`.`b` = 'No duplicate')
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
FLUSH STATUS;
EXPLAIN DELETE FROM t1 WHERE a = 105;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	t1	p1	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where
Warnings:
Note	1003	delete from `test`.`t1` where (`test`.`t1`.`a` = 105)
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
FLUSH STATUS;
EXPLAIN DELETE FROM t1 WHERE b = "No duplicate";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	t1	p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12	range	b	b	515	const	#	100.00	Using where
Warnings:
Note	1003	delete from `test`.`t1` where (`test`.`t1`.`b` = 'No duplicate')
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
#
# Test DELETE
#
FLUSH STATUS;
DELETE FROM t1 WHERE a = 105;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
# 1 read_key
# 1 delete
FLUSH STATUS;
DELETE FROM t1 WHERE b = "No duplicate";
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	13
Handler_read_next	1
# 13 read_key
# 1 read_next (if more matches after the first match)
# 1 delete
FLUSH STATUS;
DELETE FROM t1 WHERE a = (SELECT a + 90 FROM t2 WHERE a = 1);
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	4
Handler_prepare	2
Handler_read_key	2
# 2 read_key
# 2 read_next (if more matches after the first match)
# 1 delete
EXPLAIN DELETE FROM t1 WHERE a = (SELECT a + 90 FROM t2 WHERE a = 1);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	t1	p0	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where
2	SUBQUERY	t2	p1	const	PRIMARY	PRIMARY	4	const	#	100.00	Using index
Warnings:
Note	1003	delete from `test`.`t1` where (`test`.`t1`.`a` = (/* select#2 */ select (`test`.`t2`.`a` + 90) from `test`.`t2` where 1))
FLUSH STATUS;
DELETE FROM t1 PARTITION (p0)
WHERE a = (SELECT a + 2 FROM t2 WHERE a = 1);
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	1
# Impossible delete, all partitions pruned away after locking!
# 1 read_key
EXPLAIN DELETE FROM t1 PARTITION (p0)
WHERE a = (SELECT a + 2 FROM t2 WHERE a = 1);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	#	NULL	No matching rows after partition pruning
2	SUBQUERY	t2	p1	const	PRIMARY	PRIMARY	4	const	#	100.00	Using index
Warnings:
Note	1003	delete from `test`.`t1` PARTITION (`p0`) where (`test`.`t1`.`a` = (/* select#2 */ select (`test`.`t2`.`a` + 2) from `test`.`t2` where 1))
#
# Test multi table DELETE
#
SELECT * FROM t1 ORDER BY a;
a	b
0	First row, p0
1	First row, p1
2	First row, p2
3	First row, p3, same as min(a) + 2 in t2
4	First row, p4, t2.b:First row, p4
5	REPLACE second, max(a) in t2: 4
8	Insert in v1_9 NO CHECK!
9	Insert in v1_9_check
18	Insert in v1_25
23	Insert in v1_all
26	Third row, p0, updated 1
31	Insert in v1_25_check
39	Fourth row, p0, updated 1
52	Fifth row, p0, updated 1
65	No duplicate, INSERT_DUP_KEY_UPDATE
98	Test98
104	No duplicate 104
111	Test111
113	Second row, p0, updated 1, updated 2 -> p8, updated 3, updated 4 -> p9
313	Test313
SELECT * FROM t2 ORDER BY a;
a	b
1	First row, p1
4	First row, p4, t1.b:First row, p4
FLUSH STATUS;
EXPLAIN DELETE t1, t2 FROM t1, t2
WHERE t1.a = t2.a AND t1.b = 'First row, p1';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	t1	p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12	ref	PRIMARY,b	b	515	const	#	100.00	NULL
1	DELETE	t2	p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	#	100.00	NULL
Warnings:
Note	1003	delete `test`.`t1`, `test`.`t2` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`b` = 'First row, p1') and (`test`.`t2`.`a` = `test`.`t1`.`a`))
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
FLUSH STATUS;
EXPLAIN DELETE FROM t2, t1 USING t2, t1
WHERE t1.b = t2.b AND t2.a = 4;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	t2	p4	const	PRIMARY	PRIMARY	4	const	#	100.00	NULL
1	DELETE	t1	p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12	ref	b	b	515	const	#	100.00	NULL
Warnings:
Note	1003	delete `test`.`t2`, `test`.`t1` from `test`.`t2` join `test`.`t1` where ((`test`.`t1`.`b` = `test`.`t2`.`b`))
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	1
FLUSH STATUS;
DELETE t1, t2 FROM t1, t2
WHERE t1.a = t2.a AND t1.b = 'First row, p1';
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	2
Handler_external_lock	4
Handler_prepare	2
Handler_read_key	15
Handler_read_next	1
Handler_read_rnd	1
# 15 read_key
# 2 delete
FLUSH STATUS;
DELETE FROM t2, t1 USING t2, t1
WHERE t1.b = t2.b AND t2.a = 4;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	14
SELECT * FROM t1 ORDER BY a;
a	b
0	First row, p0
2	First row, p2
3	First row, p3, same as min(a) + 2 in t2
4	First row, p4, t2.b:First row, p4
5	REPLACE second, max(a) in t2: 4
8	Insert in v1_9 NO CHECK!
9	Insert in v1_9_check
18	Insert in v1_25
23	Insert in v1_all
26	Third row, p0, updated 1
31	Insert in v1_25_check
39	Fourth row, p0, updated 1
52	Fifth row, p0, updated 1
65	No duplicate, INSERT_DUP_KEY_UPDATE
98	Test98
104	No duplicate 104
111	Test111
113	Second row, p0, updated 1, updated 2 -> p8, updated 3, updated 4 -> p9
313	Test313
SELECT * FROM t2 ORDER BY a;
a	b
4	First row, p4, t1.b:First row, p4
#
# Test subquery IN expression
#
FLUSH STATUS;
EXPLAIN SELECT count(*) FROM t1 p
WHERE a IN (1, 2, 9);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	p	p1,p2,p9	index	PRIMARY	b	515	NULL	4	75.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `count(*)` from `test`.`t1` `p` where (`test`.`p`.`a` in (1,2,9))
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
FLUSH STATUS;
EXPLAIN SELECT count(*) FROM t1 p
WHERE a IN
(SELECT a + 1 FROM t2 WHERE a = 4);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	p	p5	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
1	SIMPLE	t2	p4	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `count(*)` from `test`.`t2` join `test`.`t1` `p` where 1
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	2
#
# Test triggers
# Tables used in triggers cannot be pruned for locks.
# Tables with triggers cannot be pruned for locks if
# BEFORE INSERT/UPDATE trigger uses any partitioning columns.
#
CREATE TABLE t3
(old_a int,
new_a int,
old_b varchar(255),
new_b varchar(255),
key (new_a, new_b),
key(new_b))
PARTITION BY HASH (new_a) PARTITIONS 5;
CREATE TRIGGER t1_after_insert AFTER INSERT
ON t1 FOR EACH ROW
INSERT INTO t3 VALUES (2, NEW.a, NULL, CONCAT("AI: ", NEW.b));
CREATE TRIGGER t1_after_update AFTER UPDATE
ON t1 FOR EACH ROW
INSERT INTO t3 VALUES (OLD.a, NEW.a, CONCAT("AU: ", OLD.b), CONCAT("AU: ", NEW.b));
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`a`)
PARTITIONS 13 */
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`a`)
PARTITIONS 13 */
SHOW CREATE TABLE t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `old_a` int(11) DEFAULT NULL,
  `new_a` int(11) DEFAULT NULL,
  `old_b` varchar(255) DEFAULT NULL,
  `new_b` varchar(255) DEFAULT NULL,
  KEY `new_a` (`new_a`,`new_b`),
  KEY `new_b` (`new_b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`new_a`)
PARTITIONS 5 */
FLUSH STATUS;
INSERT INTO t1 VALUES (2, "First row, p2")
ON DUPLICATE KEY UPDATE b = CONCAT(b, ", duplicate key 2");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	6
Handler_prepare	2
Handler_read_key	1
Handler_update	1
Handler_write	2
# (t1 to insert, t3 after insert trigger, t3 after update trigger)
SELECT * FROM t1 WHERE a = 2;
a	b
2	First row, p2, duplicate key 2
FLUSH STATUS;
REPLACE INTO t1 VALUES (0, "First row, p0 REPLACED");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	4
Handler_prepare	2
Handler_read_key	1
Handler_update	1
Handler_write	2
# (t1 to replace, t3 after insert trigger)
# Note that since there is no delete trigger, REPLACE cheats by
# doing update instead of delete+insert!
SELECT * FROM t1 WHERE a = 0;
a	b
0	First row, p0 REPLACED
FLUSH STATUS;
EXPLAIN UPDATE t1 SET b = CONCAT(b, ", UPDATED2") WHERE a = 3;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t1	p3	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where
Warnings:
Note	1003	update `test`.`t1` set `test`.`t1`.`b` = concat(`test`.`t1`.`b`,', UPDATED2') where (`test`.`t1`.`a` = 3)
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
# (t1 to insert, t3 after update trigger)
CREATE TRIGGER t1_after_delete AFTER DELETE
ON t1 FOR EACH ROW
INSERT INTO t3 VALUES (OLD.a, NULL, CONCAT("AD: ", OLD.b), NULL);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`a`)
PARTITIONS 13 */
FLUSH STATUS;
REPLACE INTO t1 VALUES (0, "First row, p0 REPLACED2");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	6
Handler_prepare	2
Handler_read_key	1
Handler_write	4
# (t1 to replace, t3 after insert trigger, t3 after delete trigger)
# Note that now it does delete+insert instead, due to delete trigger!
SELECT * FROM t1 WHERE a = 0;
a	b
0	First row, p0 REPLACED2
CREATE TRIGGER t1_before_delete BEFORE DELETE
ON t1 FOR EACH ROW
INSERT INTO t3 VALUES (OLD.a, NULL, CONCAT("BD: ", OLD.b), NULL);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`a`)
PARTITIONS 13 */
FLUSH STATUS;
REPLACE INTO t1 VALUES (0, "First row, p0 REPLACED3");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	8
Handler_prepare	2
Handler_read_key	1
Handler_write	5
# (t1 to replace, t3 after insert trigger, t3 before delete trigger,
#  t3 after delete trigger)
SELECT * FROM t1 WHERE a = 0;
a	b
0	First row, p0 REPLACED3
CREATE TRIGGER t1_before_update BEFORE UPDATE
ON t1 FOR EACH ROW
INSERT INTO t3 VALUES (OLD.a, NEW.a, CONCAT("BU: ", OLD.b), CONCAT("BU: ", NEW.b));
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`a`)
PARTITIONS 13 */
FLUSH STATUS;
INSERT INTO t1 VALUES (2, "First row, p2")
ON DUPLICATE KEY UPDATE b = CONCAT(b, ", duplicate key 2");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	8
Handler_prepare	2
Handler_read_key	1
Handler_update	1
Handler_write	3
# No pruning possible, due to BEFORE UPDATE trigger
# t1, t3 after insert, t3 before update, t3 after update
SELECT * FROM t1 WHERE a = 2;
a	b
2	First row, p2, duplicate key 2, duplicate key 2
FLUSH STATUS;
REPLACE INTO t1 VALUES (0, "First row, p0 REPLACED4");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	8
Handler_prepare	2
Handler_read_key	1
Handler_write	5
# t1, t3 after insert, t3 before delete, t3 after delete
SELECT * FROM t1 WHERE a = 0;
a	b
0	First row, p0 REPLACED4
FLUSH STATUS;
EXPLAIN UPDATE t1 SET b = CONCAT(b, ", UPDATED2") WHERE a = 3;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t1	p3	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where; Using temporary
Warnings:
Note	1003	update `test`.`t1` set `test`.`t1`.`b` = concat(`test`.`t1`.`b`,', UPDATED2') where (`test`.`t1`.`a` = 3)
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	6
# No pruning possible, due to BEFORE UPDATE trigger
# t1, before update, after update
SELECT * FROM t1 WHERE a = 3;
a	b
3	First row, p3, same as min(a) + 2 in t2
FLUSH STATUS;
UPDATE t1 SET b = CONCAT(b, ", UPDATED2") WHERE a = 3;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	6
Handler_prepare	2
Handler_read_key	2
Handler_read_rnd	1
Handler_update	1
Handler_write	2
# t1, before update, after update
SELECT * FROM t1 WHERE a = 3;
a	b
3	First row, p3, same as min(a) + 2 in t2, UPDATED2
EXPLAIN INSERT INTO t1 VALUES (12, "First row, p12");
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	INSERT	t1	p12	ALL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
Warnings:
Note	1003	insert into `test`.`t1` values (12,'First row, p12')
FLUSH STATUS;
INSERT INTO t1 VALUES (12, "First row, p12");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	4
Handler_prepare	2
Handler_write	2
# t1, t3 after insert trigger
CREATE TRIGGER t1_before_insert BEFORE INSERT
ON t1 FOR EACH ROW
INSERT INTO t3 VALUES (1, NEW.a, NULL, CONCAT("BI: ", NEW.b));
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`a`)
PARTITIONS 13 */
FLUSH STATUS;
INSERT INTO t1 VALUES (11, "First row, p11");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	6
Handler_prepare	2
Handler_write	3
# Nothing can be pruned, due to triggers.
# t1, t3 before insert, t3 after insert.
FLUSH STATUS;
EXPLAIN DELETE FROM t1 WHERE a = 98;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	t1	p7	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where
Warnings:
Note	1003	delete from `test`.`t1` where (`test`.`t1`.`a` = 98)
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	6
# t1, t3 before delete trigger, t3 after delete trigger
# part 7, part 0-4, part 0-4.
SELECT * FROM t1 ORDER BY a;
a	b
0	First row, p0 REPLACED4
2	First row, p2, duplicate key 2, duplicate key 2
3	First row, p3, same as min(a) + 2 in t2, UPDATED2
4	First row, p4, t2.b:First row, p4
5	REPLACE second, max(a) in t2: 4
8	Insert in v1_9 NO CHECK!
9	Insert in v1_9_check
11	First row, p11
12	First row, p12
18	Insert in v1_25
23	Insert in v1_all
26	Third row, p0, updated 1
31	Insert in v1_25_check
39	Fourth row, p0, updated 1
52	Fifth row, p0, updated 1
65	No duplicate, INSERT_DUP_KEY_UPDATE
98	Test98
104	No duplicate 104
111	Test111
113	Second row, p0, updated 1, updated 2 -> p8, updated 3, updated 4 -> p9
313	Test313
FLUSH STATUS;
DELETE FROM t1 WHERE a = 98;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	6
Handler_prepare	2
Handler_read_key	1
Handler_write	2
# t1, t3 before delete trigger, t3 after delete trigger
SELECT * FROM t1 ORDER BY a;
a	b
0	First row, p0 REPLACED4
2	First row, p2, duplicate key 2, duplicate key 2
3	First row, p3, same as min(a) + 2 in t2, UPDATED2
4	First row, p4, t2.b:First row, p4
5	REPLACE second, max(a) in t2: 4
8	Insert in v1_9 NO CHECK!
9	Insert in v1_9_check
11	First row, p11
12	First row, p12
18	Insert in v1_25
23	Insert in v1_all
26	Third row, p0, updated 1
31	Insert in v1_25_check
39	Fourth row, p0, updated 1
52	Fifth row, p0, updated 1
65	No duplicate, INSERT_DUP_KEY_UPDATE
104	No duplicate 104
111	Test111
113	Second row, p0, updated 1, updated 2 -> p8, updated 3, updated 4 -> p9
313	Test313
SELECT * FROM t2 ORDER BY a;
a	b
4	First row, p4, t1.b:First row, p4
SELECT * FROM t3 ORDER BY new_a;
old_a	new_a	old_b	new_b
0	NULL	AD: First row, p0 REPLACED	NULL
0	NULL	AD: First row, p0 REPLACED2	NULL
0	NULL	AD: First row, p0 REPLACED3	NULL
0	NULL	BD: First row, p0 REPLACED2	NULL
0	NULL	BD: First row, p0 REPLACED3	NULL
1	11	NULL	BI: First row, p11
2	0	NULL	AI: First row, p0 REPLACED
2	0	NULL	AI: First row, p0 REPLACED2
2	0	NULL	AI: First row, p0 REPLACED3
2	0	NULL	AI: First row, p0 REPLACED4
2	11	NULL	AI: First row, p11
2	12	NULL	AI: First row, p12
2	2	AU: First row, p2	AU: First row, p2, duplicate key 2
2	2	AU: First row, p2, duplicate key 2	AU: First row, p2, duplicate key 2, duplicate key 2
2	2	BU: First row, p2, duplicate key 2	BU: First row, p2, duplicate key 2, duplicate key 2
3	3	AU: First row, p3, same as min(a) + 2 in t2	AU: First row, p3, same as min(a) + 2 in t2, UPDATED2
3	3	BU: First row, p3, same as min(a) + 2 in t2	BU: First row, p3, same as min(a) + 2 in t2, UPDATED2
98	NULL	AD: Test98	NULL
98	NULL	BD: Test98	NULL
TRUNCATE TABLE t1;
DROP TRIGGER t1_before_insert;
DROP TRIGGER t1_before_update;
DROP TRIGGER t1_before_delete;
DROP TRIGGER t1_after_insert;
DROP TRIGGER t1_after_update;
DROP TRIGGER t1_after_delete;
#
# Test BEFORE INSERT TRIGGER depending on partitioning column
#
CREATE TRIGGER t1_before_insert BEFORE INSERT
ON t1 FOR EACH ROW
SET NEW.b = CONCAT("b: ", NEW.b, " a: ", NEW.a);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`a`)
PARTITIONS 13 */
FLUSH STATUS;
INSERT INTO t1 VALUES (0, "first row, p0");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t1 VALUES (0, "Second row, p0")
ON DUPLICATE KEY UPDATE b = CONCAT(b, ", duplicate key");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
Handler_write	1
FLUSH STATUS;
UPDATE t1 SET b = CONCAT(b, ", Updated") WHERE a = 0;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
FLUSH STATUS;
UPDATE t1 SET a = 1, b = CONCAT(b, ", a was 0") WHERE a = 0;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	2
Handler_read_rnd	1
Handler_write	1
# Updating partitioning column, no lock pruning
#
# Test BEFORE INSERT TRIGGER not depending on partitioning column
#
DROP TRIGGER t1_before_insert;
CREATE TRIGGER t1_before_insert BEFORE INSERT
ON t1 FOR EACH ROW
SET NEW.b = CONCAT("b: ", NEW.b);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`a`)
PARTITIONS 13 */
FLUSH STATUS;
INSERT INTO t1 VALUES (0, "first row, p0");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t1 VALUES (0, "Second row, p0")
ON DUPLICATE KEY UPDATE b = CONCAT(b, ", duplicate key");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
Handler_write	1
FLUSH STATUS;
UPDATE t1 SET b = CONCAT(b, ", Updated") WHERE a = 0;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
FLUSH STATUS;
UPDATE t1 SET a = 2, b = CONCAT(b, ", a was 0") WHERE a = 0;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	2
Handler_read_rnd	1
Handler_write	1
# Updating partitioning column, no lock pruning
#
# Test BEFORE UPDATE TRIGGER OLD depending on partitioning column.
# Note that it does not update any partitioning column.
#
CREATE TRIGGER t1_before_update BEFORE UPDATE
ON t1 FOR EACH ROW
SET NEW.b = CONCAT("old a: ", OLD.a, " new b: ", NEW.b);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`a`)
PARTITIONS 13 */
FLUSH STATUS;
INSERT INTO t1 VALUES (0, "1st p0");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t1 VALUES (0, "2nd p0")
ON DUPLICATE KEY UPDATE b = CONCAT(b, ", dup key");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
Handler_write	1
FLUSH STATUS;
UPDATE t1 SET b = CONCAT(b, ", Updated") WHERE a = 0;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
# Lock pruning possible!
FLUSH STATUS;
UPDATE t1 SET a = 3, b = CONCAT(b, ", a was 0") WHERE a = 0;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	2
Handler_read_rnd	1
Handler_write	1
# Updating partitioning column, no lock pruning
#
# Test BEFORE UPDATE TRIGGER NEW depending on partitioning column.
# Note that it does not update any partitioning column.
#
DROP TRIGGER t1_before_update;
CREATE TRIGGER t1_before_update BEFORE UPDATE
ON t1 FOR EACH ROW
SET NEW.b = CONCAT("new a: ", NEW.a, " new b: ", NEW.b);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`a`)
PARTITIONS 13 */
FLUSH STATUS;
INSERT INTO t1 VALUES (0, "1st p0");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t1 VALUES (0, "2nd p0")
ON DUPLICATE KEY UPDATE b = CONCAT(b, ", dup key");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
Handler_write	1
FLUSH STATUS;
UPDATE t1 SET b = CONCAT(b, ", Updated") WHERE a = 0;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	2
Handler_read_rnd	1
Handler_update	1
FLUSH STATUS;
UPDATE t1 SET a = 4, b = CONCAT(b, ", a was 0") WHERE a = 0;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	2
Handler_read_rnd	1
Handler_write	1
# Updating partitioning column, no lock pruning
#
# Test BEFORE UPDATE TRIGGER not depending on partitioning column
#
DROP TRIGGER t1_before_update;
CREATE TRIGGER t1_before_update BEFORE UPDATE
ON t1 FOR EACH ROW
SET NEW.b = CONCAT("new b: ", NEW.b);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`a`)
PARTITIONS 13 */
FLUSH STATUS;
INSERT INTO t1 VALUES (0, "1st p0");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	1
FLUSH STATUS;
INSERT INTO t1 VALUES (0, "2nd p0")
ON DUPLICATE KEY UPDATE b = CONCAT(b, ", dup key");
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
Handler_write	1
FLUSH STATUS;
UPDATE t1 SET b = CONCAT(b, ", Updated") WHERE a = 0;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	1
Handler_update	1
FLUSH STATUS;
UPDATE t1 SET a = 5, b = CONCAT(b, ", a was 0") WHERE a = 0;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	2
Handler_prepare	2
Handler_read_key	2
Handler_read_rnd	1
Handler_write	1
# Updating partitioning column, no lock pruning
SELECT * FROM t1 ORDER BY a;
a	b
1	b: first row, p0 a: 0, duplicate key, Updated, a was 0
2	b: first row, p0, duplicate key, Updated, a was 0
3	old a: 0 new b: old a: 0 new b: old a: 0 new b: b: 1st p0, dup key, Updated, a was 0
4	new a: 4 new b: new a: 0 new b: new a: 0 new b: b: 1st p0, dup key, Updated, a was 0
5	new b: new b: new b: b: 1st p0, dup key, Updated, a was 0
DROP TABLE t1, t2, t3;
#
# Test of BEFORE UPDATE triggers and multi UPDATE
#
CREATE TABLE t1 (a int, b varchar(128), KEY (b))
ENGINE = InnoDB
PARTITION BY HASH (a) PARTITIONS 13;
CREATE TABLE t2 (a int PRIMARY KEY, b varchar(128))
ENGINE = InnoDB
PARTITION BY HASH (a) PARTITIONS 13;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` varchar(128) DEFAULT NULL,
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`a`)
PARTITIONS 13 */
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) NOT NULL,
  `b` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`a`)
PARTITIONS 13 */
INSERT INTO t1 VALUES (1, "MultiUpdate1");
INSERT INTO t1 VALUES (2, "MultiUpdate2");
INSERT INTO t2 VALUES (1, "MultiUpdate1");
INSERT INTO t2 VALUES (2, "MultiUpdate2");
CREATE TRIGGER t1_before_update BEFORE UPDATE
ON t1 FOR EACH ROW
SET NEW.b = CONCAT("new1 b: ", NEW.b);
CREATE TRIGGER t2_before_update BEFORE UPDATE
ON t2 FOR EACH ROW
SET NEW.b = CONCAT("new2 a: ", NEW.a, " new2 b: ", NEW.b);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` varchar(128) DEFAULT NULL,
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`a`)
PARTITIONS 13 */
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) NOT NULL,
  `b` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`a`)
PARTITIONS 13 */
FLUSH STATUS;
EXPLAIN UPDATE t1, t2
SET t1.b = CONCAT(t1.b, ",(1) t2.b:", t2.b),
t2.b = CONCAT(t2.b, ",(1) t1.b:", t1.b)
WHERE t2.b = t1.b and t1.a = 1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t1	p1	ALL	b	NULL	NULL	NULL	#	100.00	Using where
1	UPDATE	t2	p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12	ALL	NULL	NULL	NULL	NULL	#	50.00	Using where
Warnings:
Note	1003	update `test`.`t1` join `test`.`t2` set `test`.`t1`.`b` = concat(`test`.`t1`.`b`,',(1) t2.b:',`test`.`t2`.`b`),`test`.`t2`.`b` = concat(`test`.`t2`.`b`,',(1) t1.b:',`test`.`t1`.`b`) where ((`test`.`t1`.`a` = 1) and (`test`.`t2`.`b` = `test`.`t1`.`b`))
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
FLUSH STATUS;
EXPLAIN UPDATE t1, t2
SET t1.b = CONCAT(t1.b, ",(1) t2.b:", t2.b),
t2.b = CONCAT(t2.b, ",(1) t1.b:", t1.b)
WHERE t2.b = t1.b and t1.a = 1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t1	p1	ALL	b	NULL	NULL	NULL	#	100.00	Using where
1	UPDATE	t2	p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12	ALL	NULL	NULL	NULL	NULL	#	50.00	Using where
Warnings:
Note	1003	update `test`.`t1` join `test`.`t2` set `test`.`t1`.`b` = concat(`test`.`t1`.`b`,',(1) t2.b:',`test`.`t2`.`b`),`test`.`t2`.`b` = concat(`test`.`t2`.`b`,',(1) t1.b:',`test`.`t1`.`b`) where ((`test`.`t1`.`a` = 1) and (`test`.`t2`.`b` = `test`.`t1`.`b`))
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
FLUSH STATUS;
UPDATE t1, t2
SET t1.b = CONCAT(t1.b, ",(1) t2.b:", t2.b),
t2.b = CONCAT(t2.b, ",(1) t1.b:", t1.b)
WHERE t2.b = t1.b and t1.a = 1;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	4
Handler_prepare	2
Handler_read_first	14
Handler_read_key	16
Handler_read_rnd	2
Handler_read_rnd_next	7
Handler_update	2
Handler_write	2
# 14 read_first
# 16 read_key
# 2 read_rnd
# 2 update
FLUSH STATUS;
EXPLAIN UPDATE t1, t2
SET t1.b = CONCAT(t1.b, ",(2) t2.b:", t2.b),
t2.b = CONCAT(t2.b, ",(2) t1.b:", t1.b)
WHERE t1.b = t2.b and t2.a = 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t2	p2	const	PRIMARY	PRIMARY	4	const	#	100.00	NULL
1	UPDATE	t1	p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12	ref	b	b	515	const	#	100.00	NULL
Warnings:
Note	1003	update `test`.`t1` join `test`.`t2` set `test`.`t1`.`b` = concat(`test`.`t1`.`b`,',(2) t2.b:','MultiUpdate2'),`test`.`t2`.`b` = concat('MultiUpdate2',',(2) t1.b:',`test`.`t1`.`b`) where ((`test`.`t1`.`b` = `test`.`t2`.`b`))
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	1
# Trigger touches partitioning column, unable to prune locks
FLUSH STATUS;
EXPLAIN UPDATE t1, t2
SET t1.b = CONCAT(t1.b, ",(2) t2.b:", t2.b),
t2.b = CONCAT(t2.b, ",(2) t1.b:", t1.b)
WHERE t1.b = t2.b and t2.a = 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t2	p2	const	PRIMARY	PRIMARY	4	const	#	100.00	NULL
1	UPDATE	t1	p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12	ref	b	b	515	const	#	100.00	NULL
Warnings:
Note	1003	update `test`.`t1` join `test`.`t2` set `test`.`t1`.`b` = concat(`test`.`t1`.`b`,',(2) t2.b:','MultiUpdate2'),`test`.`t2`.`b` = concat('MultiUpdate2',',(2) t1.b:',`test`.`t1`.`b`) where ((`test`.`t1`.`b` = `test`.`t2`.`b`))
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	1
# Trigger touches partitioning column, unable to prune locks
FLUSH STATUS;
UPDATE t1, t2
SET t1.b = CONCAT(t1.b, ",(2) t2.b:", t2.b),
t2.b = CONCAT(t2.b, ",(2) t1.b:", t1.b)
WHERE t1.b = t2.b and t2.a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	4
Handler_prepare	2
Handler_read_key	15
Handler_read_next	1
Handler_read_rnd	1
Handler_read_rnd_next	2
Handler_update	2
Handler_write	1
# Due to the BEFORE UPDATE trigger on t2 that looks at 'a',
# no locks can be pruned.
# 15 read_key
# 1 read_next, read_rnd
# 2 read_rnd_next
# 2 update
SELECT * FROM t1 ORDER BY a;
a	b
1	new1 b: MultiUpdate1,(1) t2.b:MultiUpdate1
2	new1 b: MultiUpdate2,(2) t2.b:MultiUpdate2
SELECT * FROM t2 ORDER BY a;
a	b
1	new2 a: 1 new2 b: MultiUpdate1,(1) t1.b:MultiUpdate1
2	new2 a: 2 new2 b: MultiUpdate2,(2) t1.b:MultiUpdate2
DROP TABLE t1, t2;
#
# Test constant propagation in WHERE clause
# (Currently no propagation is done before locking).
CREATE TABLE t1 (a int, b varchar(128), KEY (b))
ENGINE = InnoDB
PARTITION BY RANGE (a)
(PARTITION pNeg VALUES LESS THAN (0),
PARTITION p0 VALUES LESS THAN (1),
PARTITION p1 VALUES LESS THAN (2),
PARTITION p2 VALUES LESS THAN (3),
PARTITION p3 VALUES LESS THAN (4),
PARTITION pMax VALUES LESS THAN MAXVALUE);
CREATE TABLE t2 (a int PRIMARY KEY, b varchar(128))
ENGINE = InnoDB
PARTITION BY RANGE (a)
(PARTITION pNeg VALUES LESS THAN (0),
PARTITION p0 VALUES LESS THAN (1),
PARTITION p1 VALUES LESS THAN (2),
PARTITION p2 VALUES LESS THAN (3),
PARTITION p3 VALUES LESS THAN (4),
PARTITION pMax VALUES LESS THAN MAXVALUE);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` varchar(128) DEFAULT NULL,
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (`a`)
(PARTITION pNeg VALUES LESS THAN (0) ENGINE = InnoDB,
 PARTITION p0 VALUES LESS THAN (1) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (3) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (4) ENGINE = InnoDB,
 PARTITION pMax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) NOT NULL,
  `b` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (`a`)
(PARTITION pNeg VALUES LESS THAN (0) ENGINE = InnoDB,
 PARTITION p0 VALUES LESS THAN (1) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (3) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (4) ENGINE = InnoDB,
 PARTITION pMax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
INSERT INTO t1 VALUES (1, "Const1");
INSERT INTO t2 VALUES (1, "Const1");
INSERT INTO t1 VALUES (2, "Const2");
INSERT INTO t2 VALUES (2, "Const2");
INSERT INTO t1 VALUES (3, "Const3");
INSERT INTO t2 VALUES (3, "Const3");
# Test simple '=' propagation
FLUSH STATUS;
SELECT * FROM t1, t2
WHERE t1.a = t2.a AND t2.a = 1;
a	b	a	b
1	Const1	1	Const1
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_first	1
Handler_read_key	2
Handler_read_rnd_next	1
FLUSH STATUS;
SELECT * FROM t1, t2
WHERE t1.a = t2.a AND t1.a = 1;
a	b	a	b
1	Const1	1	Const1
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_first	1
Handler_read_key	2
Handler_read_rnd_next	1
# Test OR propagation
FLUSH STATUS;
SELECT * FROM t1, t2
WHERE t1.a = t2.a AND (t2.a = 1 OR t2.a = 2);
a	b	a	b
1	Const1	1	Const1
2	Const2	2	Const2
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_first	2
Handler_read_key	4
Handler_read_rnd_next	2
# But it will be scanned pruned!
EXPLAIN SELECT * FROM t1, t2
WHERE t1.a = t2.a AND (t1.a = 1 OR t1.a = 2);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	p1,p2	ALL	NULL	NULL	NULL	NULL	#	75.00	Using where
1	SIMPLE	t2	p1,p2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	#	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and ((`test`.`t1`.`a` = 1) or (`test`.`t1`.`a` = 2)))
# Test closed range propagation
FLUSH STATUS;
SELECT * FROM t1, t2
WHERE t1.a = t2.a AND t1.a >= 1 AND t1.a <=3;
a	b	a	b
1	Const1	1	Const1
2	Const2	2	Const2
3	Const3	3	Const3
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_first	3
Handler_read_key	6
Handler_read_rnd_next	3
# But it will be scanned pruned!
EXPLAIN SELECT * FROM t1, t2
WHERE t1.a = t2.a AND t1.a >= 1 AND t1.a <=3;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	p1,p2,p3	ALL	NULL	NULL	NULL	NULL	#	33.33	Using where
1	SIMPLE	t2	p1,p2,p3	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	#	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t1`.`a` >= 1) and (`test`.`t1`.`a` <= 3))
# Test open range propagation
FLUSH STATUS;
SELECT * FROM t1, t2
WHERE t1.a = t2.a AND t2.a >= 1;
a	b	a	b
1	Const1	1	Const1
2	Const2	2	Const2
3	Const3	3	Const3
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_first	4
Handler_read_key	7
Handler_read_rnd_next	3
# But is scanned pruned!
EXPLAIN SELECT * FROM t1, t2
WHERE t1.a = t2.a AND t2.a >= 1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	p1,p2,p3,pMax	ALL	NULL	NULL	NULL	NULL	#	100.00	Using where
1	SIMPLE	t2	p1,p2,p3,pMax	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	#	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t1`.`a` >= 1))
FLUSH STATUS;
SELECT * FROM t1, t2
WHERE t1.a = t2.a AND t2.a <= 1;
a	b	a	b
1	Const1	1	Const1
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_first	3
Handler_read_key	4
Handler_read_rnd_next	1
# But is scanned pruned!
EXPLAIN SELECT * FROM t1, t2
WHERE t1.a = t2.a AND t2.a <= 1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	pNeg,p0,p1	ALL	NULL	NULL	NULL	NULL	#	100.00	Using where
1	SIMPLE	t2	pNeg,p0,p1	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	#	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t1`.`a` <= 1))
# Test IN propagation
FLUSH STATUS;
SELECT * FROM t1, t2
WHERE t1.a = t2.a and t2.a IN (1, 3);
a	b	a	b
1	Const1	1	Const1
3	Const3	3	Const3
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_first	2
Handler_read_key	4
Handler_read_rnd_next	2
# But is scanned pruned!
EXPLAIN SELECT * FROM t1, t2
WHERE t1.a = t2.a AND t1.a IN (1, 3);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	p1,p3	ALL	NULL	NULL	NULL	NULL	#	50.00	Using where
1	SIMPLE	t2	p1,p3	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	#	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t1`.`a` in (1,3)))
# Same for UPDATE
FLUSH STATUS;
UPDATE t1, t2
SET t1.b = CONCAT(t1.b, ", t2.b:", t2.b)
WHERE t1.a = t2.a and t2.a IN (2, 3);
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	4
Handler_prepare	2
Handler_read_first	2
Handler_read_key	4
Handler_read_rnd_next	2
Handler_update	2
# But is scanned pruned!
EXPLAIN UPDATE t1, t2
SET t1.b = CONCAT(t1.b, ", t2.b:", t2.b)
WHERE t1.a = t2.a and t2.a IN (2, 3);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t1	p2,p3	ALL	NULL	NULL	NULL	NULL	#	100.00	Using where
1	SIMPLE	t2	p2,p3	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	#	100.00	NULL
Warnings:
Note	1003	update `test`.`t1` join `test`.`t2` set `test`.`t1`.`b` = concat(`test`.`t1`.`b`,', t2.b:',`test`.`t2`.`b`) where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t1`.`a` in (2,3)))
FLUSH STATUS;
UPDATE t1, t2
SET t1.b = CONCAT(t1.b, ", t2.b:", t2.b),
t2.b = CONCAT(t2.b, ", t1.b:", t1.b)
WHERE t1.a = t2.a and t2.a = 1;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	4
Handler_prepare	2
Handler_read_first	1
Handler_read_key	3
Handler_read_rnd	1
Handler_read_rnd_next	3
Handler_update	2
Handler_write	1
FLUSH STATUS;
UPDATE t1, t2
SET t1.b = CONCAT(t1.b, ", t2.b:", t2.b),
t2.b = CONCAT(t2.b, ", t1.b:", t1.b)
WHERE t1.a = t2.a and t1.a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	4
Handler_prepare	2
Handler_read_first	1
Handler_read_key	3
Handler_read_rnd	1
Handler_read_rnd_next	3
Handler_update	2
Handler_write	1
SELECT * FROM t1 ORDER BY a;
a	b
1	Const1, t2.b:Const1
2	Const2, t2.b:Const2, t2.b:Const2
3	Const3, t2.b:Const3
SELECT * FROM t2 ORDER BY a;
a	b
1	Const1, t1.b:Const1
2	Const2, t1.b:Const2, t2.b:Const2
3	Const3
# Same for DELETE
FLUSH STATUS;
DELETE t1 FROM t1, t2
WHERE t1.a = t2.a AND t2.a IN (1, 9);
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	4
Handler_prepare	2
Handler_read_first	2
Handler_read_key	3
Handler_read_rnd_next	1
# But is scanned pruned!
EXPLAIN DELETE t1 FROM t1, t2
WHERE t1.a = t2.a AND t2.a IN (1, 9);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	t1	p1,pMax	ALL	NULL	NULL	NULL	NULL	#	100.00	Using where
1	SIMPLE	t2	p1,pMax	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	#	100.00	Using index
Warnings:
Note	1003	delete `test`.`t1` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t1`.`a` in (1,9)))
SELECT * FROM t1 ORDER BY a;
a	b
2	Const2, t2.b:Const2, t2.b:Const2
3	Const3, t2.b:Const3
FLUSH STATUS;
DELETE t1 FROM t1, t2
WHERE t1.a = t2.a and t2.a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	4
Handler_prepare	2
Handler_read_first	1
Handler_read_key	2
Handler_read_rnd_next	1
FLUSH STATUS;
DELETE t1 FROM t1, t2
WHERE t1.a = t2.a and t1.a = 1;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_first	1
Handler_read_key	2
SELECT * FROM t1 ORDER BY a;
a	b
3	Const3, t2.b:Const3
SELECT * FROM t2 ORDER BY a;
a	b
1	Const1, t1.b:Const1
2	Const2, t1.b:Const2, t2.b:Const2
3	Const3
FLUSH STATUS;
DELETE t1, t2 FROM t1, t2
WHERE t1.a = t2.a and t2.a = 3;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	2
Handler_external_lock	4
Handler_prepare	2
Handler_read_first	1
Handler_read_key	2
Handler_read_rnd_next	1
SELECT * FROM t1 ORDER BY a;
a	b
SELECT * FROM t2 ORDER BY a;
a	b
1	Const1, t1.b:Const1
2	Const2, t1.b:Const2, t2.b:Const2
DROP TABLE t1, t2;
#
# DO is not supported by WL#4443 !!!
# Test of DO (eg. SELECT without returning values)
#
CREATE TABLE t1 (a INT, b VARCHAR(66))
PARTITION BY HASH (a) PARTITIONS 3;
INSERT INTO t1 VALUES (1, "One"), (2, "Two"), (3, "Three"), (4, "Four"), (5, "Five"), (6, "Six"), (0, "Zero");
DO (SELECT a FROM t1);
ERROR 21000: Subquery returns more than 1 row
FLUSH STATUS;
DO (SELECT @x:= b FROM t1 WHERE a = 5);
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
Handler_read_first	1
Handler_read_key	1
Handler_read_rnd_next	2
SELECT @x;
@x
Five
FLUSH STATUS;
DO (SELECT @x:= b FROM t1 WHERE a = 5 or a = 1 ORDER BY b LIMIT 1);
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
Handler_read_first	2
Handler_read_key	2
Handler_read_rnd_next	4
SELECT @x;
@x
Five
#
# SET is not supported by WL#4443 !!!
# Test of SET (eg. SELECT only setting an internal variable from
# the returning value)
#
FLUSH STATUS;
SET @x = (SELECT a FROM t1 WHERE a = 5);
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
Handler_read_first	1
Handler_read_key	1
Handler_read_rnd_next	2
SELECT @x;
@x
5
FLUSH STATUS;
SET @y = (SELECT @x:= b FROM t1 WHERE a = 5);
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
Handler_read_first	1
Handler_read_key	1
Handler_read_rnd_next	2
SELECT @x, @y;
@x	@y
Five	Five
FLUSH STATUS;
SET @y = (SELECT @x:= b FROM t1 WHERE a = 5 or a = 1 ORDER BY b LIMIT 1);
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
Handler_read_first	2
Handler_read_key	2
Handler_read_rnd_next	4
SELECT @x, @y;
@x	@y
Five	Five
#
# LOAD DATA is not supported by WL#4443 !!!
#
FLUSH STATUS;
SELECT * FROM t1 WHERE a IN (1, 4)
INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.part1';
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
Handler_read_first	1
Handler_read_key	1
Handler_read_rnd_next	2
DELETE FROM t1 WHERE a IN (1, 4);
SELECT * FROM t1 ORDER BY a, b;
a	b
0	Zero
2	Two
3	Three
5	Five
6	Six
FLUSH STATUS;
LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1.part1' INTO TABLE t1;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	2
SELECT * FROM t1 ORDER BY a, b;
a	b
0	Zero
1	One
2	Two
3	Three
4	Four
5	Five
6	Six
DELETE FROM t1 WHERE a IN (1, 4);
SELECT * FROM t1 ORDER BY a, b;
a	b
0	Zero
2	Two
3	Three
5	Five
6	Six
# It is possible to avoid locking with explicit partitioning selection!
FLUSH STATUS;
LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1.part1' INTO TABLE t1 PARTITION(p1);
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	2
Handler_prepare	2
Handler_write	2
SELECT * FROM t1 ORDER BY a, b;
a	b
0	Zero
1	One
2	Two
3	Three
4	Four
5	Five
6	Six
DROP TABLE t1;
#
# Test EXCHANGE PARTITION to only lock exchanged partition
#
CREATE TABLE t1 (a INT, b VARCHAR(44));
CREATE TABLE t2 (a INT, b VARCHAR(44))
PARTITION BY HASH (a) PARTITIONS 3;
INSERT INTO t1 VALUES (10, "Ten"), (13, "Thirteen"), (16, "Sixteen");
INSERT INTO t2 VALUES (0, "Zero"), (1, "One"), (2, "Two"),
(3, "Three"), (4, "Four"), (5, "Five"),
(6, "Six"), (7, "Seven"), (8, "Eight");
FLUSH STATUS;
ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	20
Handler_external_lock	132
Handler_prepare	4
Handler_read_first	1
Handler_read_key	66
Handler_read_next	13
Handler_read_rnd_next	4
Handler_update	36
SELECT * FROM t1 ORDER BY a;
a	b
1	One
4	Four
7	Seven
SELECT * FROM t2 ORDER BY a;
a	b
0	Zero
2	Two
3	Three
5	Five
6	Six
8	Eight
10	Ten
13	Thirteen
16	Sixteen
DROP TABLE t1, t2;
#
# Prepared statement
#
CREATE TABLE t1 (N int, M tinyint)
PARTITION BY HASH (N) PARTITIONS 3;
INSERT INTO t1 VALUES (1,0),(1,0),(2,0),(2,0),(3,0);
PREPARE stmt FROM 'UPDATE t1 AS P1 INNER JOIN (SELECT N FROM t1 GROUP BY N HAVING COUNT(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2';
FLUSH STATUS;
EXECUTE stmt;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	4
Handler_prepare	2
Handler_read_first	6
Handler_read_key	16
Handler_read_next	4
Handler_read_rnd_next	14
Handler_update	6
Handler_write	5
SELECT * FROM t1 ORDER BY N, M;
N	M
1	2
1	2
2	2
2	2
3	0
DEALLOCATE PREPARE stmt;
PREPARE stmt FROM 'SELECT * FROM t1 WHERE N = 2';
FLUSH STATUS;
EXECUTE stmt;
N	M
2	2
2	2
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	2
Handler_read_first	1
Handler_read_key	1
Handler_read_rnd_next	2
DROP TABLE t1;
# Check if we can infer from condition on partition fields that 
# no records will match.
CREATE TABLE t1 ( a int NOT NULL) PARTITION BY HASH(a) PARTITIONS 2;
INSERT INTO t1 VALUES (1),(2),(3);
FLUSH STATUS;
EXPLAIN SELECT * FROM t1 WHERE a=5 AND a=6;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	#	NULL	Impossible WHERE
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0
VARIABLE_NAME	VARIABLE_VALUE
Handler_external_lock	2
FLUSH STATUS;
SELECT * FROM t1 WHERE a=5 AND a=6;
a
VARIABLE_NAME	VARIABLE_VALUE
Handler_external_lock	2
DROP TABLE t1;
#
# Test of subqueries in INSERT
#
CREATE TABLE t1 (a INT, b VARCHAR(64));
CREATE TABLE t2 (a INT, b VARCHAR(64)) PARTITION BY HASH (a) PARTITIONS 3;
INSERT INTO t1 VALUES (1, "test 1");
INSERT INTO t2 VALUES (SELECT * FROM t1);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM t1)' at line 1
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL,
  `b` varchar(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`a`)
PARTITIONS 3 */
FLUSH STATUS;
INSERT INTO t2 VALUES ((SELECT a FROM t1), (SELECT b FROM t1));
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	6
Handler_prepare	2
Handler_read_first	2
Handler_read_key	2
Handler_read_rnd_next	4
Handler_write	1
# I.e. No lock pruning possible
FLUSH STATUS;
INSERT INTO t2 VALUES (1 + (SELECT a FROM t1),
CONCAT("subq: ", (SELECT b FROM t1)));
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	6
Handler_prepare	2
Handler_read_first	2
Handler_read_key	2
Handler_read_rnd_next	4
Handler_write	1
# I.e. No lock pruning possible
SELECT * FROM t2;
a	b
1	test 1
2	subq: test 1
DROP TABLE t1, t2;
CREATE TABLE t1 (a INT, b INT) PARTITION BY HASH (a) PARTITIONS 3;
CREATE TABLE t2 (a INT, b INT) PARTITION BY HASH (a) PARTITIONS 3;
INSERT INTO t1 VALUES (1, 1), (2, 0), (4, -1), (5, 2), (7, -3), (8, -9),
(10, 5), (11, 9);
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`a`)
PARTITIONS 3 */
FLUSH STATUS;
INSERT INTO t2 VALUES ((SELECT max(a) FROM t1), (SELECT min(a) FROM t1));
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	6
Handler_prepare	2
Handler_read_first	6
Handler_read_key	6
Handler_read_rnd_next	16
Handler_write	1
# I.e. No lock pruning possible
FLUSH STATUS;
EXPLAIN INSERT INTO t2 VALUES ((SELECT max(a) FROM t1),
(SELECT min(a) FROM t1));
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	INSERT	t2	p0,p1,p2	ALL	NULL	NULL	NULL	NULL	#	NULL	NULL
3	SUBQUERY	t1	p0,p1,p2	ALL	NULL	NULL	NULL	NULL	#	100.00	NULL
2	SUBQUERY	t1	p0,p1,p2	ALL	NULL	NULL	NULL	NULL	#	100.00	NULL
Warnings:
Note	1003	insert into `test`.`t2` values ((/* select#2 */ select max(`test`.`t1`.`a`) from `test`.`t1`),(/* select#3 */ select min(`test`.`t1`.`a`) from `test`.`t1`))
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	6
# I.e. No lock pruning possible
FLUSH STATUS;
INSERT INTO t2 VALUES ((SELECT a FROM t1 WHERE a = 1),
(SELECT b FROM t1 WHERE a = 2));
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	6
Handler_prepare	2
Handler_read_first	2
Handler_read_key	2
Handler_read_rnd_next	8
Handler_write	1
# I.e. No lock pruning possible on insert table
FLUSH STATUS;
EXPLAIN INSERT INTO t2 VALUES ((SELECT a FROM t1 WHERE a = 1),
(SELECT b FROM t1 WHERE a = 2));
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	INSERT	t2	p0,p1,p2	ALL	NULL	NULL	NULL	NULL	#	NULL	NULL
3	SUBQUERY	t1	p2	ALL	NULL	NULL	NULL	NULL	#	25.00	Using where
2	SUBQUERY	t1	p1	ALL	NULL	NULL	NULL	NULL	#	25.00	Using where
Warnings:
Note	1003	insert into `test`.`t2` values ((/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`a` = 1)),(/* select#3 */ select `test`.`t1`.`b` from `test`.`t1` where (`test`.`t1`.`a` = 2)))
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	6
# I.e. No lock pruning possible on insert table
SELECT * FROM t2 ORDER BY a, b;
a	b
1	0
11	1
DROP TABLE t1;
DROP TABLE t2;
#
# Test of InnoDB INSERT TABLE with non existing table in trigger
#
CREATE TABLE t1 (a INT)
ENGINE = InnoDB;
# Create a table to be used in a trigger on t1
CREATE TABLE t2 (a INT)
ENGINE = InnoDB;
# Create a trigger on t1 which uses t2
CREATE TRIGGER tr1_1_N  BEFORE INSERT ON t1
FOR EACH ROW BEGIN
UPDATE t2 SET a = 8 WHERE a > 3 LIMIT 0;
END//
# Drop t2 to cause a failure when inserting into t1
DROP TABLE t2;
INSERT INTO t1 VALUES (1);
ERROR 42S02: Table 'test.t2' doesn't exist
DROP TABLE t1;
CREATE TABLE t1 (a INT) PARTITION BY HASH (a) PARTITIONS 3;
INSERT INTO t1 VALUES (1), (3), (9), (2), (8), (7);
FLUSH STATUS;
CREATE TABLE t2 SELECT * FROM t1 PARTITION (p1, p2);
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	12
Handler_external_lock	162
Handler_prepare	4
Handler_read_first	2
Handler_read_key	21
Handler_read_rnd_next	4
Handler_update	10
Handler_write	16
SELECT * FROM t2;
a
1
2
7
8
DROP TABLE t2;
FLUSH STATUS;
CREATE TABLE t2 SELECT * FROM t1 WHERE a IN (1, 3, 9);
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	12
Handler_external_lock	162
Handler_prepare	4
Handler_read_first	2
Handler_read_key	21
Handler_read_rnd_next	4
Handler_update	10
Handler_write	15
SELECT * FROM t2;
a
1
3
9
DROP TABLE t1, t2;
#
# Test subqueries/stored functions with UPDATE/DELETE/SELECT
#
CREATE TABLE tq (id int PRIMARY KEY auto_increment, query varchar(255), not_select tinyint);
CREATE TABLE tsq (id int PRIMARY KEY auto_increment, subquery varchar(255), can_be_locked tinyint);
CREATE TABLE t1 (a int, b varchar(255), PRIMARY KEY (a), KEY (b))
ENGINE = InnoDB
PARTITION BY HASH (a) PARTITIONS 3;
CREATE TABLE t2 (a int, b varchar(255), PRIMARY KEY (a), KEY (b))
ENGINE = InnoDB
PARTITION BY HASH (a) PARTITIONS 3;
START TRANSACTION;
INSERT INTO t1 VALUES (1, "1");
INSERT INTO t1 VALUES (2, "2");
INSERT INTO t1 VALUES (8, "8");
INSERT INTO t2 VALUES (1, "1");
INSERT INTO t2 VALUES (2, "2");
INSERT INTO t2 VALUES (8, "8");
CREATE FUNCTION sf_add_hello(s VARCHAR(240))
RETURNS VARCHAR(246) DETERMINISTIC
RETURN CONCAT('hello ', s);
CREATE FUNCTION sf_add_1(i INT)
RETURNS INT DETERMINISTIC
RETURN i + 1;
CREATE FUNCTION sf_a_from_t1b_d(s varchar(128))
RETURNS INT DETERMINISTIC
BEGIN
DECLARE i INT;
SELECT a INTO i FROM t1 where b = s;
RETURN i;
END|
CREATE FUNCTION sf_a_from_t1b(s varchar(128))
RETURNS INT
BEGIN
DECLARE i INT;
SELECT a INTO i FROM t1 where b = s;
RETURN i;
END|
INSERT INTO tq (query, not_select) VALUES
("SELECT * FROM t2", 0),
("SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2", 0),
("UPDATE t2 SET b = CONCAT('+', b)", 1),
("UPDATE t2 SET b = sf_add_hello(b)", 1),
("UPDATE t2 SET a = sf_add_1(a) + 4", 1),
("DELETE FROM t2", 1);
INSERT INTO tsq (subquery, can_be_locked) VALUES
("(SELECT a FROM t1 WHERE b = '1')", 1),
("7 + (SELECT a FROM t1 WHERE b = '1')", 1),
("sf_a_from_t1b('1')", 1),
("sf_a_from_t1b_d('1')", 1),
("7 + sf_a_from_t1b('1')", 1),
("7 + sf_a_from_t1b_d('1')", 1),
("sf_a_from_t1b('1') AND a = 2", 1),
("sf_a_from_t1b_d('1') AND a = 2", 1),
("(SELECT a FROM t1 WHERE b = '1') AND a = 2", 1),
("(SELECT a FROM t1 WHERE b = '1') OR a = 2", 1),
("(SELECT a FROM t1 WHERE b = '1') AND a = 2 OR a = 8 AND sf_a_from_t1b('2')", 0);
set @old_autocommit= @@autocommit;
EXPLAIN SELECT * FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t2	p1	const	PRIMARY	PRIMARY	4	const	#	100.00	NULL
2	SUBQUERY	t1	p0,p1,p2	ref	b	b	1023	const	#	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select '1' AS `a`,'1' AS `b` from `test`.`t2` where 1
FLUSH STATUS;
START TRANSACTION;
SELECT * FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1');
a	b
1	1
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	4
Handler_read_next	1
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
SELECT * FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1');
a	b
1	1
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	8
Handler_external_lock	26
Handler_read_key	16
Handler_read_next	7
UNLOCK TABLES;
EXPLAIN SELECT * FROM t2 WHERE a = 7 + (SELECT a FROM t1 WHERE b = '1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t2	p2	const	PRIMARY	PRIMARY	4	const	#	100.00	NULL
2	SUBQUERY	t1	p0,p1,p2	ref	b	b	1023	const	#	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select '8' AS `a`,'8' AS `b` from `test`.`t2` where 1
FLUSH STATUS;
START TRANSACTION;
SELECT * FROM t2 WHERE a = 7 + (SELECT a FROM t1 WHERE b = '1');
a	b
8	8
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	4
Handler_read_next	1
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
SELECT * FROM t2 WHERE a = 7 + (SELECT a FROM t1 WHERE b = '1');
a	b
8	8
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	8
Handler_external_lock	26
Handler_read_key	16
Handler_read_next	7
UNLOCK TABLES;
EXPLAIN SELECT * FROM t2 WHERE a = sf_a_from_t1b('1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	p0,p1,p2	index	NULL	b	1023	NULL	#	33.33	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` = `sf_a_from_t1b`('1'))
FLUSH STATUS;
START TRANSACTION;
SELECT * FROM t2 WHERE a = sf_a_from_t1b('1');
a	b
1	1
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_first	3
Handler_read_key	12
Handler_read_next	6
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
SELECT * FROM t2 WHERE a = sf_a_from_t1b('1');
a	b
1	1
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	8
Handler_external_lock	26
Handler_read_first	3
Handler_read_key	24
Handler_read_next	12
UNLOCK TABLES;
EXPLAIN SELECT * FROM t2 WHERE a = sf_a_from_t1b_d('1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	p1	const	PRIMARY	PRIMARY	4	const	#	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select '1' AS `a`,'1' AS `b` from `test`.`t2` where 1
FLUSH STATUS;
START TRANSACTION;
SELECT * FROM t2 WHERE a = sf_a_from_t1b_d('1');
a	b
1	1
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	10
Handler_read_next	3
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
SELECT * FROM t2 WHERE a = sf_a_from_t1b_d('1');
a	b
1	1
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	8
Handler_external_lock	26
Handler_read_key	22
Handler_read_next	9
UNLOCK TABLES;
EXPLAIN SELECT * FROM t2 WHERE a = 7 + sf_a_from_t1b('1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	p0,p1,p2	index	NULL	b	1023	NULL	#	33.33	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` = (7 + `sf_a_from_t1b`('1')))
FLUSH STATUS;
START TRANSACTION;
SELECT * FROM t2 WHERE a = 7 + sf_a_from_t1b('1');
a	b
8	8
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_first	3
Handler_read_key	12
Handler_read_next	6
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
SELECT * FROM t2 WHERE a = 7 + sf_a_from_t1b('1');
a	b
8	8
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	8
Handler_external_lock	26
Handler_read_first	3
Handler_read_key	24
Handler_read_next	12
UNLOCK TABLES;
EXPLAIN SELECT * FROM t2 WHERE a = 7 + sf_a_from_t1b_d('1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	p2	const	PRIMARY	PRIMARY	4	const	#	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select '8' AS `a`,'8' AS `b` from `test`.`t2` where 1
FLUSH STATUS;
START TRANSACTION;
SELECT * FROM t2 WHERE a = 7 + sf_a_from_t1b_d('1');
a	b
8	8
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	10
Handler_read_next	3
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
SELECT * FROM t2 WHERE a = 7 + sf_a_from_t1b_d('1');
a	b
8	8
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	8
Handler_external_lock	26
Handler_read_key	22
Handler_read_next	9
UNLOCK TABLES;
EXPLAIN SELECT * FROM t2 WHERE a = sf_a_from_t1b('1') AND a = 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	p2	const	PRIMARY	PRIMARY	4	const	#	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select '2' AS `a`,'2' AS `b` from `test`.`t2` where ((2 = `sf_a_from_t1b`('1')))
FLUSH STATUS;
START TRANSACTION;
SELECT * FROM t2 WHERE a = sf_a_from_t1b('1') AND a = 2;
a	b
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	4
Handler_read_next	1
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
SELECT * FROM t2 WHERE a = sf_a_from_t1b('1') AND a = 2;
a	b
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	8
Handler_external_lock	26
Handler_read_key	16
Handler_read_next	7
UNLOCK TABLES;
EXPLAIN SELECT * FROM t2 WHERE a = sf_a_from_t1b_d('1') AND a = 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	#	NULL	Impossible WHERE
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where 0
FLUSH STATUS;
START TRANSACTION;
SELECT * FROM t2 WHERE a = sf_a_from_t1b_d('1') AND a = 2;
a	b
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	3
Handler_read_next	1
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
SELECT * FROM t2 WHERE a = sf_a_from_t1b_d('1') AND a = 2;
a	b
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	8
Handler_external_lock	26
Handler_read_key	15
Handler_read_next	7
UNLOCK TABLES;
EXPLAIN SELECT * FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	#	NULL	Impossible WHERE
2	SUBQUERY	t1	p0,p1,p2	ref	b	b	1023	const	#	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where 0
FLUSH STATUS;
START TRANSACTION;
SELECT * FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2;
a	b
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	3
Handler_read_next	1
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
SELECT * FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2;
a	b
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	8
Handler_external_lock	26
Handler_read_key	15
Handler_read_next	7
UNLOCK TABLES;
EXPLAIN SELECT * FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1') OR a = 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t2	p1,p2	index	PRIMARY	b	1023	NULL	#	66.67	Using where; Using index
2	SUBQUERY	t1	p0,p1,p2	ref	b	b	1023	const	#	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`a` = (/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`b` = '1'))) or (`test`.`t2`.`a` = 2))
FLUSH STATUS;
START TRANSACTION;
SELECT * FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1') OR a = 2;
a	b
1	1
2	2
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_first	2
Handler_read_key	5
Handler_read_next	4
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
SELECT * FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1') OR a = 2;
a	b
1	1
2	2
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	8
Handler_external_lock	26
Handler_read_first	2
Handler_read_key	17
Handler_read_next	10
UNLOCK TABLES;
EXPLAIN SELECT * FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2 OR a = 8 AND sf_a_from_t1b('2');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t2	p2	const	PRIMARY	PRIMARY	4	const	#	100.00	NULL
2	SUBQUERY	t1	p0,p1,p2	ref	b	b	1023	const	#	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select '8' AS `a`,'8' AS `b` from `test`.`t2` where (`sf_a_from_t1b`('2'))
FLUSH STATUS;
START TRANSACTION;
SELECT * FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2 OR a = 8 AND sf_a_from_t1b('2');
a	b
8	8
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	6
Handler_read_key	7
Handler_read_next	2
EXPLAIN SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t2	p1	const	PRIMARY	PRIMARY	4	const	#	100.00	NULL
2	SUBQUERY	t1	p0,p1,p2	ref	b	b	1023	const	#	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select (`sf_add_1`('1') - 1) AS `sf_add_1(a) - 1`,`sf_add_hello`('1') AS `sf_add_hello(b)` from `test`.`t2` where 1
FLUSH STATUS;
START TRANSACTION;
SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1');
sf_add_1(a) - 1	sf_add_hello(b)
1	hello 1
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	4
Handler_read_next	1
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1');
sf_add_1(a) - 1	sf_add_hello(b)
1	hello 1
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	8
Handler_external_lock	26
Handler_read_key	16
Handler_read_next	7
UNLOCK TABLES;
EXPLAIN SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2 WHERE a = 7 + (SELECT a FROM t1 WHERE b = '1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t2	p2	const	PRIMARY	PRIMARY	4	const	#	100.00	NULL
2	SUBQUERY	t1	p0,p1,p2	ref	b	b	1023	const	#	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select (`sf_add_1`('8') - 1) AS `sf_add_1(a) - 1`,`sf_add_hello`('8') AS `sf_add_hello(b)` from `test`.`t2` where 1
FLUSH STATUS;
START TRANSACTION;
SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2 WHERE a = 7 + (SELECT a FROM t1 WHERE b = '1');
sf_add_1(a) - 1	sf_add_hello(b)
8	hello 8
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	4
Handler_read_next	1
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2 WHERE a = 7 + (SELECT a FROM t1 WHERE b = '1');
sf_add_1(a) - 1	sf_add_hello(b)
8	hello 8
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	8
Handler_external_lock	26
Handler_read_key	16
Handler_read_next	7
UNLOCK TABLES;
EXPLAIN SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2 WHERE a = sf_a_from_t1b('1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	p0,p1,p2	index	NULL	b	1023	NULL	#	33.33	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select (`sf_add_1`(`test`.`t2`.`a`) - 1) AS `sf_add_1(a) - 1`,`sf_add_hello`(`test`.`t2`.`b`) AS `sf_add_hello(b)` from `test`.`t2` where (`test`.`t2`.`a` = `sf_a_from_t1b`('1'))
FLUSH STATUS;
START TRANSACTION;
SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2 WHERE a = sf_a_from_t1b('1');
sf_add_1(a) - 1	sf_add_hello(b)
1	hello 1
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_first	3
Handler_read_key	12
Handler_read_next	6
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2 WHERE a = sf_a_from_t1b('1');
sf_add_1(a) - 1	sf_add_hello(b)
1	hello 1
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	8
Handler_external_lock	26
Handler_read_first	3
Handler_read_key	24
Handler_read_next	12
UNLOCK TABLES;
EXPLAIN SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2 WHERE a = sf_a_from_t1b_d('1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	p1	const	PRIMARY	PRIMARY	4	const	#	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (`sf_add_1`('1') - 1) AS `sf_add_1(a) - 1`,`sf_add_hello`('1') AS `sf_add_hello(b)` from `test`.`t2` where 1
FLUSH STATUS;
START TRANSACTION;
SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2 WHERE a = sf_a_from_t1b_d('1');
sf_add_1(a) - 1	sf_add_hello(b)
1	hello 1
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	10
Handler_read_next	3
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2 WHERE a = sf_a_from_t1b_d('1');
sf_add_1(a) - 1	sf_add_hello(b)
1	hello 1
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	8
Handler_external_lock	26
Handler_read_key	22
Handler_read_next	9
UNLOCK TABLES;
EXPLAIN SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2 WHERE a = 7 + sf_a_from_t1b('1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	p0,p1,p2	index	NULL	b	1023	NULL	#	33.33	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select (`sf_add_1`(`test`.`t2`.`a`) - 1) AS `sf_add_1(a) - 1`,`sf_add_hello`(`test`.`t2`.`b`) AS `sf_add_hello(b)` from `test`.`t2` where (`test`.`t2`.`a` = (7 + `sf_a_from_t1b`('1')))
FLUSH STATUS;
START TRANSACTION;
SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2 WHERE a = 7 + sf_a_from_t1b('1');
sf_add_1(a) - 1	sf_add_hello(b)
8	hello 8
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_first	3
Handler_read_key	12
Handler_read_next	6
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2 WHERE a = 7 + sf_a_from_t1b('1');
sf_add_1(a) - 1	sf_add_hello(b)
8	hello 8
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	8
Handler_external_lock	26
Handler_read_first	3
Handler_read_key	24
Handler_read_next	12
UNLOCK TABLES;
EXPLAIN SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2 WHERE a = 7 + sf_a_from_t1b_d('1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	p2	const	PRIMARY	PRIMARY	4	const	#	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (`sf_add_1`('8') - 1) AS `sf_add_1(a) - 1`,`sf_add_hello`('8') AS `sf_add_hello(b)` from `test`.`t2` where 1
FLUSH STATUS;
START TRANSACTION;
SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2 WHERE a = 7 + sf_a_from_t1b_d('1');
sf_add_1(a) - 1	sf_add_hello(b)
8	hello 8
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	10
Handler_read_next	3
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2 WHERE a = 7 + sf_a_from_t1b_d('1');
sf_add_1(a) - 1	sf_add_hello(b)
8	hello 8
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	8
Handler_external_lock	26
Handler_read_key	22
Handler_read_next	9
UNLOCK TABLES;
EXPLAIN SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2 WHERE a = sf_a_from_t1b('1') AND a = 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	p2	const	PRIMARY	PRIMARY	4	const	#	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select (`sf_add_1`('2') - 1) AS `sf_add_1(a) - 1`,`sf_add_hello`('2') AS `sf_add_hello(b)` from `test`.`t2` where ((2 = `sf_a_from_t1b`('1')))
FLUSH STATUS;
START TRANSACTION;
SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2 WHERE a = sf_a_from_t1b('1') AND a = 2;
sf_add_1(a) - 1	sf_add_hello(b)
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	4
Handler_read_next	1
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2 WHERE a = sf_a_from_t1b('1') AND a = 2;
sf_add_1(a) - 1	sf_add_hello(b)
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	8
Handler_external_lock	26
Handler_read_key	16
Handler_read_next	7
UNLOCK TABLES;
EXPLAIN SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2 WHERE a = sf_a_from_t1b_d('1') AND a = 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	#	NULL	Impossible WHERE
Warnings:
Note	1003	/* select#1 */ select (`sf_add_1`(`test`.`t2`.`a`) - 1) AS `sf_add_1(a) - 1`,`sf_add_hello`(`test`.`t2`.`b`) AS `sf_add_hello(b)` from `test`.`t2` where 0
FLUSH STATUS;
START TRANSACTION;
SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2 WHERE a = sf_a_from_t1b_d('1') AND a = 2;
sf_add_1(a) - 1	sf_add_hello(b)
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	3
Handler_read_next	1
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2 WHERE a = sf_a_from_t1b_d('1') AND a = 2;
sf_add_1(a) - 1	sf_add_hello(b)
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	8
Handler_external_lock	26
Handler_read_key	15
Handler_read_next	7
UNLOCK TABLES;
EXPLAIN SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	#	NULL	Impossible WHERE
2	SUBQUERY	t1	p0,p1,p2	ref	b	b	1023	const	#	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select (`sf_add_1`(`test`.`t2`.`a`) - 1) AS `sf_add_1(a) - 1`,`sf_add_hello`(`test`.`t2`.`b`) AS `sf_add_hello(b)` from `test`.`t2` where 0
FLUSH STATUS;
START TRANSACTION;
SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2;
sf_add_1(a) - 1	sf_add_hello(b)
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	3
Handler_read_next	1
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2;
sf_add_1(a) - 1	sf_add_hello(b)
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	8
Handler_external_lock	26
Handler_read_key	15
Handler_read_next	7
UNLOCK TABLES;
EXPLAIN SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1') OR a = 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t2	p1,p2	index	PRIMARY	b	1023	NULL	#	66.67	Using where; Using index
2	SUBQUERY	t1	p0,p1,p2	ref	b	b	1023	const	#	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select (`sf_add_1`(`test`.`t2`.`a`) - 1) AS `sf_add_1(a) - 1`,`sf_add_hello`(`test`.`t2`.`b`) AS `sf_add_hello(b)` from `test`.`t2` where ((`test`.`t2`.`a` = (/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`b` = '1'))) or (`test`.`t2`.`a` = 2))
FLUSH STATUS;
START TRANSACTION;
SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1') OR a = 2;
sf_add_1(a) - 1	sf_add_hello(b)
1	hello 1
2	hello 2
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_first	2
Handler_read_key	5
Handler_read_next	4
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1') OR a = 2;
sf_add_1(a) - 1	sf_add_hello(b)
1	hello 1
2	hello 2
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	8
Handler_external_lock	26
Handler_read_first	2
Handler_read_key	17
Handler_read_next	10
UNLOCK TABLES;
EXPLAIN SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2 OR a = 8 AND sf_a_from_t1b('2');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t2	p2	const	PRIMARY	PRIMARY	4	const	#	100.00	NULL
2	SUBQUERY	t1	p0,p1,p2	ref	b	b	1023	const	#	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select (`sf_add_1`('8') - 1) AS `sf_add_1(a) - 1`,`sf_add_hello`('8') AS `sf_add_hello(b)` from `test`.`t2` where (`sf_a_from_t1b`('2'))
FLUSH STATUS;
START TRANSACTION;
SELECT sf_add_1(a) - 1, sf_add_hello(b) FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2 OR a = 8 AND sf_a_from_t1b('2');
sf_add_1(a) - 1	sf_add_hello(b)
8	hello 8
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	6
Handler_read_key	7
Handler_read_next	2
EXPLAIN UPDATE t2 SET b = CONCAT('+', b) WHERE a = (SELECT a FROM t1 WHERE b = '1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t2	p1	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where
2	SUBQUERY	t1	p0,p1,p2	ref	b	b	1023	const	#	100.00	Using index
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`b` = concat('+',`test`.`t2`.`b`) where (`test`.`t2`.`a` = (/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`b` = '1')))
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET b = CONCAT('+', b) WHERE a = (SELECT a FROM t1 WHERE b = '1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	4
Handler_prepare	2
Handler_read_key	4
Handler_read_next	1
Handler_update	1
SELECT * FROM t2;
a	b
1	+1
2	2
8	8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
UPDATE t2 SET b = CONCAT('+', b) WHERE a = (SELECT a FROM t1 WHERE b = '1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	9
Handler_external_lock	26
Handler_prepare	2
Handler_read_key	16
Handler_read_next	7
Handler_update	1
SELECT * FROM t2;
a	b
1	+1
2	2
8	8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN UPDATE t2 SET b = CONCAT('+', b) WHERE a = 7 + (SELECT a FROM t1 WHERE b = '1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t2	p2	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where
2	SUBQUERY	t1	p0,p1,p2	ref	b	b	1023	const	#	100.00	Using index
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`b` = concat('+',`test`.`t2`.`b`) where (`test`.`t2`.`a` = (7 + (/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`b` = '1'))))
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET b = CONCAT('+', b) WHERE a = 7 + (SELECT a FROM t1 WHERE b = '1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	4
Handler_prepare	2
Handler_read_key	4
Handler_read_next	1
Handler_update	1
SELECT * FROM t2;
a	b
1	1
2	2
8	+8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
UPDATE t2 SET b = CONCAT('+', b) WHERE a = 7 + (SELECT a FROM t1 WHERE b = '1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	9
Handler_external_lock	26
Handler_prepare	2
Handler_read_key	16
Handler_read_next	7
Handler_update	1
SELECT * FROM t2;
a	b
1	1
2	2
8	+8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN UPDATE t2 SET b = CONCAT('+', b) WHERE a = sf_a_from_t1b('1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t2	p0,p1,p2	index	NULL	PRIMARY	4	NULL	#	100.00	Using where
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`b` = concat('+',`test`.`t2`.`b`) where (`test`.`t2`.`a` = `sf_a_from_t1b`('1'))
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET b = CONCAT('+', b) WHERE a = sf_a_from_t1b('1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	4
Handler_prepare	2
Handler_read_first	3
Handler_read_key	12
Handler_read_next	3
Handler_read_rnd_next	3
Handler_update	1
SELECT * FROM t2;
a	b
1	+1
2	2
8	8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
UPDATE t2 SET b = CONCAT('+', b) WHERE a = sf_a_from_t1b('1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	9
Handler_external_lock	26
Handler_prepare	2
Handler_read_first	3
Handler_read_key	24
Handler_read_next	9
Handler_read_rnd_next	3
Handler_update	1
SELECT * FROM t2;
a	b
1	+1
2	2
8	8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN UPDATE t2 SET b = CONCAT('+', b) WHERE a = sf_a_from_t1b_d('1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t2	p1	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`b` = concat('+',`test`.`t2`.`b`) where (`test`.`t2`.`a` = `sf_a_from_t1b_d`('1'))
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET b = CONCAT('+', b) WHERE a = sf_a_from_t1b_d('1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	4
Handler_prepare	2
Handler_read_key	13
Handler_read_next	4
Handler_update	1
SELECT * FROM t2;
a	b
1	+1
2	2
8	8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
UPDATE t2 SET b = CONCAT('+', b) WHERE a = sf_a_from_t1b_d('1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	9
Handler_external_lock	26
Handler_prepare	2
Handler_read_key	25
Handler_read_next	10
Handler_update	1
SELECT * FROM t2;
a	b
1	+1
2	2
8	8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN UPDATE t2 SET b = CONCAT('+', b) WHERE a = 7 + sf_a_from_t1b('1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t2	p0,p1,p2	index	NULL	PRIMARY	4	NULL	#	100.00	Using where
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`b` = concat('+',`test`.`t2`.`b`) where (`test`.`t2`.`a` = (7 + `sf_a_from_t1b`('1')))
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET b = CONCAT('+', b) WHERE a = 7 + sf_a_from_t1b('1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	4
Handler_prepare	2
Handler_read_first	3
Handler_read_key	12
Handler_read_next	3
Handler_read_rnd_next	3
Handler_update	1
SELECT * FROM t2;
a	b
1	1
2	2
8	+8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
UPDATE t2 SET b = CONCAT('+', b) WHERE a = 7 + sf_a_from_t1b('1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	9
Handler_external_lock	26
Handler_prepare	2
Handler_read_first	3
Handler_read_key	24
Handler_read_next	9
Handler_read_rnd_next	3
Handler_update	1
SELECT * FROM t2;
a	b
1	1
2	2
8	+8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN UPDATE t2 SET b = CONCAT('+', b) WHERE a = 7 + sf_a_from_t1b_d('1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t2	p2	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`b` = concat('+',`test`.`t2`.`b`) where (`test`.`t2`.`a` = (7 + `sf_a_from_t1b_d`('1')))
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET b = CONCAT('+', b) WHERE a = 7 + sf_a_from_t1b_d('1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	4
Handler_prepare	2
Handler_read_key	13
Handler_read_next	4
Handler_update	1
SELECT * FROM t2;
a	b
1	1
2	2
8	+8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
UPDATE t2 SET b = CONCAT('+', b) WHERE a = 7 + sf_a_from_t1b_d('1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	9
Handler_external_lock	26
Handler_prepare	2
Handler_read_key	25
Handler_read_next	10
Handler_update	1
SELECT * FROM t2;
a	b
1	1
2	2
8	+8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN UPDATE t2 SET b = CONCAT('+', b) WHERE a = sf_a_from_t1b('1') AND a = 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t2	p2	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`b` = concat('+',`test`.`t2`.`b`) where ((`test`.`t2`.`a` = 2) and (2 = `sf_a_from_t1b`('1')))
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET b = CONCAT('+', b) WHERE a = sf_a_from_t1b('1') AND a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	4
Handler_read_next	1
SELECT * FROM t2;
a	b
1	1
2	2
8	8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
UPDATE t2 SET b = CONCAT('+', b) WHERE a = sf_a_from_t1b('1') AND a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	8
Handler_external_lock	26
Handler_read_key	16
Handler_read_next	7
SELECT * FROM t2;
a	b
1	1
2	2
8	8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN UPDATE t2 SET b = CONCAT('+', b) WHERE a = sf_a_from_t1b_d('1') AND a = 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	#	NULL	Impossible WHERE
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`b` = concat('+',`test`.`t2`.`b`) where ()
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET b = CONCAT('+', b) WHERE a = sf_a_from_t1b_d('1') AND a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	3
Handler_read_next	1
SELECT * FROM t2;
a	b
1	1
2	2
8	8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
UPDATE t2 SET b = CONCAT('+', b) WHERE a = sf_a_from_t1b_d('1') AND a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	8
Handler_external_lock	26
Handler_read_key	15
Handler_read_next	7
SELECT * FROM t2;
a	b
1	1
2	2
8	8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN UPDATE t2 SET b = CONCAT('+', b) WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	#	NULL	Impossible WHERE
2	SUBQUERY	t1	p0,p1,p2	ref	b	b	1023	const	#	100.00	Using index
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`b` = concat('+',`test`.`t2`.`b`) where ()
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET b = CONCAT('+', b) WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	3
Handler_read_next	1
SELECT * FROM t2;
a	b
1	1
2	2
8	8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
UPDATE t2 SET b = CONCAT('+', b) WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	8
Handler_external_lock	26
Handler_read_key	15
Handler_read_next	7
SELECT * FROM t2;
a	b
1	1
2	2
8	8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN UPDATE t2 SET b = CONCAT('+', b) WHERE a = (SELECT a FROM t1 WHERE b = '1') OR a = 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t2	p1,p2	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where
2	SUBQUERY	t1	p0,p1,p2	ref	b	b	1023	const	#	100.00	Using index
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`b` = concat('+',`test`.`t2`.`b`) where ((`test`.`t2`.`a` = (/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`b` = '1'))) or (`test`.`t2`.`a` = 2))
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET b = CONCAT('+', b) WHERE a = (SELECT a FROM t1 WHERE b = '1') OR a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	4
Handler_prepare	2
Handler_read_key	5
Handler_read_next	1
Handler_update	2
SELECT * FROM t2;
a	b
1	+1
2	+2
8	8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
UPDATE t2 SET b = CONCAT('+', b) WHERE a = (SELECT a FROM t1 WHERE b = '1') OR a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	9
Handler_external_lock	26
Handler_prepare	2
Handler_read_key	17
Handler_read_next	7
Handler_update	2
SELECT * FROM t2;
a	b
1	+1
2	+2
8	8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN UPDATE t2 SET b = CONCAT('+', b) WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2 OR a = 8 AND sf_a_from_t1b('2');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t2	p2	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where
2	SUBQUERY	t1	p0,p1,p2	ref	b	b	1023	const	#	100.00	Using index
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`b` = concat('+',`test`.`t2`.`b`) where (((`test`.`t2`.`a` = 8) and `sf_a_from_t1b`('2')))
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET b = CONCAT('+', b) WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2 OR a = 8 AND sf_a_from_t1b('2');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	6
Handler_prepare	2
Handler_read_key	7
Handler_read_next	2
Handler_update	1
SELECT * FROM t2;
a	b
1	1
2	2
8	+8
ROLLBACK;
EXPLAIN UPDATE t2 SET b = sf_add_hello(b) WHERE a = (SELECT a FROM t1 WHERE b = '1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t2	p1	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where
2	SUBQUERY	t1	p0,p1,p2	ref	b	b	1023	const	#	100.00	Using index
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`b` = `sf_add_hello`(`test`.`t2`.`b`) where (`test`.`t2`.`a` = (/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`b` = '1')))
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET b = sf_add_hello(b) WHERE a = (SELECT a FROM t1 WHERE b = '1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	4
Handler_prepare	2
Handler_read_key	4
Handler_read_next	1
Handler_update	1
SELECT * FROM t2;
a	b
1	hello 1
2	2
8	8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
UPDATE t2 SET b = sf_add_hello(b) WHERE a = (SELECT a FROM t1 WHERE b = '1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	9
Handler_external_lock	26
Handler_prepare	2
Handler_read_key	16
Handler_read_next	7
Handler_update	1
SELECT * FROM t2;
a	b
1	hello 1
2	2
8	8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN UPDATE t2 SET b = sf_add_hello(b) WHERE a = 7 + (SELECT a FROM t1 WHERE b = '1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t2	p2	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where
2	SUBQUERY	t1	p0,p1,p2	ref	b	b	1023	const	#	100.00	Using index
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`b` = `sf_add_hello`(`test`.`t2`.`b`) where (`test`.`t2`.`a` = (7 + (/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`b` = '1'))))
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET b = sf_add_hello(b) WHERE a = 7 + (SELECT a FROM t1 WHERE b = '1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	4
Handler_prepare	2
Handler_read_key	4
Handler_read_next	1
Handler_update	1
SELECT * FROM t2;
a	b
1	1
2	2
8	hello 8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
UPDATE t2 SET b = sf_add_hello(b) WHERE a = 7 + (SELECT a FROM t1 WHERE b = '1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	9
Handler_external_lock	26
Handler_prepare	2
Handler_read_key	16
Handler_read_next	7
Handler_update	1
SELECT * FROM t2;
a	b
1	1
2	2
8	hello 8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN UPDATE t2 SET b = sf_add_hello(b) WHERE a = sf_a_from_t1b('1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t2	p0,p1,p2	index	NULL	PRIMARY	4	NULL	#	100.00	Using where
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`b` = `sf_add_hello`(`test`.`t2`.`b`) where (`test`.`t2`.`a` = `sf_a_from_t1b`('1'))
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET b = sf_add_hello(b) WHERE a = sf_a_from_t1b('1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	4
Handler_prepare	2
Handler_read_first	3
Handler_read_key	12
Handler_read_next	3
Handler_read_rnd_next	3
Handler_update	1
SELECT * FROM t2;
a	b
1	hello 1
2	2
8	8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
UPDATE t2 SET b = sf_add_hello(b) WHERE a = sf_a_from_t1b('1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	9
Handler_external_lock	26
Handler_prepare	2
Handler_read_first	3
Handler_read_key	24
Handler_read_next	9
Handler_read_rnd_next	3
Handler_update	1
SELECT * FROM t2;
a	b
1	hello 1
2	2
8	8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN UPDATE t2 SET b = sf_add_hello(b) WHERE a = sf_a_from_t1b_d('1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t2	p1	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`b` = `sf_add_hello`(`test`.`t2`.`b`) where (`test`.`t2`.`a` = `sf_a_from_t1b_d`('1'))
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET b = sf_add_hello(b) WHERE a = sf_a_from_t1b_d('1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	4
Handler_prepare	2
Handler_read_key	13
Handler_read_next	4
Handler_update	1
SELECT * FROM t2;
a	b
1	hello 1
2	2
8	8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
UPDATE t2 SET b = sf_add_hello(b) WHERE a = sf_a_from_t1b_d('1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	9
Handler_external_lock	26
Handler_prepare	2
Handler_read_key	25
Handler_read_next	10
Handler_update	1
SELECT * FROM t2;
a	b
1	hello 1
2	2
8	8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN UPDATE t2 SET b = sf_add_hello(b) WHERE a = 7 + sf_a_from_t1b('1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t2	p0,p1,p2	index	NULL	PRIMARY	4	NULL	#	100.00	Using where
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`b` = `sf_add_hello`(`test`.`t2`.`b`) where (`test`.`t2`.`a` = (7 + `sf_a_from_t1b`('1')))
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET b = sf_add_hello(b) WHERE a = 7 + sf_a_from_t1b('1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	4
Handler_prepare	2
Handler_read_first	3
Handler_read_key	12
Handler_read_next	3
Handler_read_rnd_next	3
Handler_update	1
SELECT * FROM t2;
a	b
1	1
2	2
8	hello 8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
UPDATE t2 SET b = sf_add_hello(b) WHERE a = 7 + sf_a_from_t1b('1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	9
Handler_external_lock	26
Handler_prepare	2
Handler_read_first	3
Handler_read_key	24
Handler_read_next	9
Handler_read_rnd_next	3
Handler_update	1
SELECT * FROM t2;
a	b
1	1
2	2
8	hello 8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN UPDATE t2 SET b = sf_add_hello(b) WHERE a = 7 + sf_a_from_t1b_d('1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t2	p2	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`b` = `sf_add_hello`(`test`.`t2`.`b`) where (`test`.`t2`.`a` = (7 + `sf_a_from_t1b_d`('1')))
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET b = sf_add_hello(b) WHERE a = 7 + sf_a_from_t1b_d('1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	4
Handler_prepare	2
Handler_read_key	13
Handler_read_next	4
Handler_update	1
SELECT * FROM t2;
a	b
1	1
2	2
8	hello 8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
UPDATE t2 SET b = sf_add_hello(b) WHERE a = 7 + sf_a_from_t1b_d('1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	9
Handler_external_lock	26
Handler_prepare	2
Handler_read_key	25
Handler_read_next	10
Handler_update	1
SELECT * FROM t2;
a	b
1	1
2	2
8	hello 8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN UPDATE t2 SET b = sf_add_hello(b) WHERE a = sf_a_from_t1b('1') AND a = 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t2	p2	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`b` = `sf_add_hello`(`test`.`t2`.`b`) where ((`test`.`t2`.`a` = 2) and (2 = `sf_a_from_t1b`('1')))
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET b = sf_add_hello(b) WHERE a = sf_a_from_t1b('1') AND a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	4
Handler_read_next	1
SELECT * FROM t2;
a	b
1	1
2	2
8	8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
UPDATE t2 SET b = sf_add_hello(b) WHERE a = sf_a_from_t1b('1') AND a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	8
Handler_external_lock	26
Handler_read_key	16
Handler_read_next	7
SELECT * FROM t2;
a	b
1	1
2	2
8	8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN UPDATE t2 SET b = sf_add_hello(b) WHERE a = sf_a_from_t1b_d('1') AND a = 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	#	NULL	Impossible WHERE
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`b` = `sf_add_hello`(`test`.`t2`.`b`) where ()
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET b = sf_add_hello(b) WHERE a = sf_a_from_t1b_d('1') AND a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	3
Handler_read_next	1
SELECT * FROM t2;
a	b
1	1
2	2
8	8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
UPDATE t2 SET b = sf_add_hello(b) WHERE a = sf_a_from_t1b_d('1') AND a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	8
Handler_external_lock	26
Handler_read_key	15
Handler_read_next	7
SELECT * FROM t2;
a	b
1	1
2	2
8	8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN UPDATE t2 SET b = sf_add_hello(b) WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	#	NULL	Impossible WHERE
2	SUBQUERY	t1	p0,p1,p2	ref	b	b	1023	const	#	100.00	Using index
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`b` = `sf_add_hello`(`test`.`t2`.`b`) where ()
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET b = sf_add_hello(b) WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	3
Handler_read_next	1
SELECT * FROM t2;
a	b
1	1
2	2
8	8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
UPDATE t2 SET b = sf_add_hello(b) WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	8
Handler_external_lock	26
Handler_read_key	15
Handler_read_next	7
SELECT * FROM t2;
a	b
1	1
2	2
8	8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN UPDATE t2 SET b = sf_add_hello(b) WHERE a = (SELECT a FROM t1 WHERE b = '1') OR a = 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t2	p1,p2	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where
2	SUBQUERY	t1	p0,p1,p2	ref	b	b	1023	const	#	100.00	Using index
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`b` = `sf_add_hello`(`test`.`t2`.`b`) where ((`test`.`t2`.`a` = (/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`b` = '1'))) or (`test`.`t2`.`a` = 2))
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET b = sf_add_hello(b) WHERE a = (SELECT a FROM t1 WHERE b = '1') OR a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	4
Handler_prepare	2
Handler_read_key	5
Handler_read_next	1
Handler_update	2
SELECT * FROM t2;
a	b
1	hello 1
2	hello 2
8	8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
UPDATE t2 SET b = sf_add_hello(b) WHERE a = (SELECT a FROM t1 WHERE b = '1') OR a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	9
Handler_external_lock	26
Handler_prepare	2
Handler_read_key	17
Handler_read_next	7
Handler_update	2
SELECT * FROM t2;
a	b
1	hello 1
2	hello 2
8	8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN UPDATE t2 SET b = sf_add_hello(b) WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2 OR a = 8 AND sf_a_from_t1b('2');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t2	p2	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where
2	SUBQUERY	t1	p0,p1,p2	ref	b	b	1023	const	#	100.00	Using index
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`b` = `sf_add_hello`(`test`.`t2`.`b`) where (((`test`.`t2`.`a` = 8) and `sf_a_from_t1b`('2')))
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET b = sf_add_hello(b) WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2 OR a = 8 AND sf_a_from_t1b('2');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_external_lock	6
Handler_prepare	2
Handler_read_key	7
Handler_read_next	2
Handler_update	1
SELECT * FROM t2;
a	b
1	1
2	2
8	hello 8
ROLLBACK;
EXPLAIN UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = (SELECT a FROM t1 WHERE b = '1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t2	p1	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where; Using temporary
2	SUBQUERY	t1	p0,p1,p2	ref	b	b	1023	const	#	100.00	Using index
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`a` = (`sf_add_1`(`test`.`t2`.`a`) + 4) where (`test`.`t2`.`a` = (/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`b` = '1')))
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = (SELECT a FROM t1 WHERE b = '1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	4
Handler_prepare	2
Handler_read_key	5
Handler_read_next	1
Handler_read_rnd	1
Handler_write	1
SELECT * FROM t2;
a	b
2	2
6	1
8	8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = (SELECT a FROM t1 WHERE b = '1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	9
Handler_delete	1
Handler_external_lock	26
Handler_prepare	2
Handler_read_key	17
Handler_read_next	7
Handler_read_rnd	1
Handler_write	1
SELECT * FROM t2;
a	b
2	2
6	1
8	8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = 7 + (SELECT a FROM t1 WHERE b = '1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t2	p2	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where; Using temporary
2	SUBQUERY	t1	p0,p1,p2	ref	b	b	1023	const	#	100.00	Using index
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`a` = (`sf_add_1`(`test`.`t2`.`a`) + 4) where (`test`.`t2`.`a` = (7 + (/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`b` = '1'))))
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = 7 + (SELECT a FROM t1 WHERE b = '1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	4
Handler_prepare	2
Handler_read_key	5
Handler_read_next	1
Handler_read_rnd	1
Handler_write	1
SELECT * FROM t2;
a	b
1	1
13	8
2	2
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = 7 + (SELECT a FROM t1 WHERE b = '1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	9
Handler_delete	1
Handler_external_lock	26
Handler_prepare	2
Handler_read_key	17
Handler_read_next	7
Handler_read_rnd	1
Handler_write	1
SELECT * FROM t2;
a	b
1	1
13	8
2	2
ROLLBACK;
UNLOCK TABLES;
EXPLAIN UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = sf_a_from_t1b('1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t2	p0,p1,p2	index	NULL	PRIMARY	4	NULL	#	100.00	Using where; Using temporary
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`a` = (`sf_add_1`(`test`.`t2`.`a`) + 4) where (`test`.`t2`.`a` = `sf_a_from_t1b`('1'))
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = sf_a_from_t1b('1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	4
Handler_prepare	2
Handler_read_first	3
Handler_read_key	13
Handler_read_next	6
Handler_read_rnd	1
Handler_write	1
SELECT * FROM t2;
a	b
2	2
6	1
8	8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = sf_a_from_t1b('1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	9
Handler_delete	1
Handler_external_lock	26
Handler_prepare	2
Handler_read_first	3
Handler_read_key	25
Handler_read_next	12
Handler_read_rnd	1
Handler_write	1
SELECT * FROM t2;
a	b
2	2
6	1
8	8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = sf_a_from_t1b_d('1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t2	p1	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where; Using temporary
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`a` = (`sf_add_1`(`test`.`t2`.`a`) + 4) where (`test`.`t2`.`a` = `sf_a_from_t1b_d`('1'))
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = sf_a_from_t1b_d('1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	4
Handler_prepare	2
Handler_read_key	14
Handler_read_next	4
Handler_read_rnd	1
Handler_write	1
SELECT * FROM t2;
a	b
2	2
6	1
8	8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = sf_a_from_t1b_d('1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	9
Handler_delete	1
Handler_external_lock	26
Handler_prepare	2
Handler_read_key	26
Handler_read_next	10
Handler_read_rnd	1
Handler_write	1
SELECT * FROM t2;
a	b
2	2
6	1
8	8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = 7 + sf_a_from_t1b('1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t2	p0,p1,p2	index	NULL	PRIMARY	4	NULL	#	100.00	Using where; Using temporary
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`a` = (`sf_add_1`(`test`.`t2`.`a`) + 4) where (`test`.`t2`.`a` = (7 + `sf_a_from_t1b`('1')))
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = 7 + sf_a_from_t1b('1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	4
Handler_prepare	2
Handler_read_first	3
Handler_read_key	13
Handler_read_next	6
Handler_read_rnd	1
Handler_write	1
SELECT * FROM t2;
a	b
1	1
13	8
2	2
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = 7 + sf_a_from_t1b('1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	9
Handler_delete	1
Handler_external_lock	26
Handler_prepare	2
Handler_read_first	3
Handler_read_key	25
Handler_read_next	12
Handler_read_rnd	1
Handler_write	1
SELECT * FROM t2;
a	b
1	1
13	8
2	2
ROLLBACK;
UNLOCK TABLES;
EXPLAIN UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = 7 + sf_a_from_t1b_d('1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t2	p2	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where; Using temporary
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`a` = (`sf_add_1`(`test`.`t2`.`a`) + 4) where (`test`.`t2`.`a` = (7 + `sf_a_from_t1b_d`('1')))
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = 7 + sf_a_from_t1b_d('1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	4
Handler_prepare	2
Handler_read_key	14
Handler_read_next	4
Handler_read_rnd	1
Handler_write	1
SELECT * FROM t2;
a	b
1	1
13	8
2	2
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = 7 + sf_a_from_t1b_d('1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	9
Handler_delete	1
Handler_external_lock	26
Handler_prepare	2
Handler_read_key	26
Handler_read_next	10
Handler_read_rnd	1
Handler_write	1
SELECT * FROM t2;
a	b
1	1
13	8
2	2
ROLLBACK;
UNLOCK TABLES;
EXPLAIN UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = sf_a_from_t1b('1') AND a = 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t2	p2	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where; Using temporary
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`a` = (`sf_add_1`(`test`.`t2`.`a`) + 4) where ((`test`.`t2`.`a` = 2) and (2 = `sf_a_from_t1b`('1')))
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = sf_a_from_t1b('1') AND a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	4
Handler_read_next	1
SELECT * FROM t2;
a	b
1	1
2	2
8	8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = sf_a_from_t1b('1') AND a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	8
Handler_external_lock	26
Handler_read_key	16
Handler_read_next	7
SELECT * FROM t2;
a	b
1	1
2	2
8	8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = sf_a_from_t1b_d('1') AND a = 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	#	NULL	Impossible WHERE
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`a` = (`sf_add_1`(`test`.`t2`.`a`) + 4) where ()
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = sf_a_from_t1b_d('1') AND a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	3
Handler_read_next	1
SELECT * FROM t2;
a	b
1	1
2	2
8	8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = sf_a_from_t1b_d('1') AND a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	8
Handler_external_lock	26
Handler_read_key	15
Handler_read_next	7
SELECT * FROM t2;
a	b
1	1
2	2
8	8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	#	NULL	Impossible WHERE
2	SUBQUERY	t1	p0,p1,p2	ref	b	b	1023	const	#	100.00	Using index
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`a` = (`sf_add_1`(`test`.`t2`.`a`) + 4) where ()
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	3
Handler_read_next	1
SELECT * FROM t2;
a	b
1	1
2	2
8	8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	8
Handler_external_lock	26
Handler_read_key	15
Handler_read_next	7
SELECT * FROM t2;
a	b
1	1
2	2
8	8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = (SELECT a FROM t1 WHERE b = '1') OR a = 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t2	p1,p2	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where; Using temporary
2	SUBQUERY	t1	p0,p1,p2	ref	b	b	1023	const	#	100.00	Using index
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`a` = (`sf_add_1`(`test`.`t2`.`a`) + 4) where ((`test`.`t2`.`a` = (/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`b` = '1'))) or (`test`.`t2`.`a` = 2))
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = (SELECT a FROM t1 WHERE b = '1') OR a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	2
Handler_external_lock	4
Handler_prepare	2
Handler_read_key	7
Handler_read_next	1
Handler_read_rnd	2
Handler_write	2
SELECT * FROM t2;
a	b
6	1
7	2
8	8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = (SELECT a FROM t1 WHERE b = '1') OR a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	9
Handler_delete	2
Handler_external_lock	26
Handler_prepare	2
Handler_read_key	19
Handler_read_next	7
Handler_read_rnd	2
Handler_write	2
SELECT * FROM t2;
a	b
6	1
7	2
8	8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2 OR a = 8 AND sf_a_from_t1b('2');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t2	p2	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where; Using temporary
2	SUBQUERY	t1	p0,p1,p2	ref	b	b	1023	const	#	100.00	Using index
Warnings:
Note	1003	update `test`.`t2` set `test`.`t2`.`a` = (`sf_add_1`(`test`.`t2`.`a`) + 4) where (((`test`.`t2`.`a` = 8) and `sf_a_from_t1b`('2')))
FLUSH STATUS;
START TRANSACTION;
UPDATE t2 SET a = sf_add_1(a) + 4 WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2 OR a = 8 AND sf_a_from_t1b('2');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	6
Handler_prepare	2
Handler_read_key	8
Handler_read_next	2
Handler_read_rnd	1
Handler_write	1
SELECT * FROM t2;
a	b
1	1
13	8
2	2
ROLLBACK;
EXPLAIN DELETE FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	t2	p1	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where
2	SUBQUERY	t1	p0,p1,p2	ref	b	b	1023	const	#	100.00	Using index
Warnings:
Note	1003	delete from `test`.`t2` where (`test`.`t2`.`a` = (/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`b` = '1')))
FLUSH STATUS;
START TRANSACTION;
DELETE FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	4
Handler_prepare	2
Handler_read_key	4
Handler_read_next	1
SELECT * FROM t2;
a	b
2	2
8	8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
DELETE FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	9
Handler_delete	1
Handler_external_lock	26
Handler_prepare	2
Handler_read_key	16
Handler_read_next	7
SELECT * FROM t2;
a	b
2	2
8	8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN DELETE FROM t2 WHERE a = 7 + (SELECT a FROM t1 WHERE b = '1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	t2	p2	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where
2	SUBQUERY	t1	p0,p1,p2	ref	b	b	1023	const	#	100.00	Using index
Warnings:
Note	1003	delete from `test`.`t2` where (`test`.`t2`.`a` = (7 + (/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`b` = '1'))))
FLUSH STATUS;
START TRANSACTION;
DELETE FROM t2 WHERE a = 7 + (SELECT a FROM t1 WHERE b = '1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	4
Handler_prepare	2
Handler_read_key	4
Handler_read_next	1
SELECT * FROM t2;
a	b
1	1
2	2
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
DELETE FROM t2 WHERE a = 7 + (SELECT a FROM t1 WHERE b = '1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	9
Handler_delete	1
Handler_external_lock	26
Handler_prepare	2
Handler_read_key	16
Handler_read_next	7
SELECT * FROM t2;
a	b
1	1
2	2
ROLLBACK;
UNLOCK TABLES;
EXPLAIN DELETE FROM t2 WHERE a = sf_a_from_t1b('1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	t2	p0,p1,p2	ALL	NULL	NULL	NULL	NULL	#	100.00	Using where
Warnings:
Note	1003	delete from `test`.`t2` where (`test`.`t2`.`a` = `sf_a_from_t1b`('1'))
FLUSH STATUS;
START TRANSACTION;
DELETE FROM t2 WHERE a = sf_a_from_t1b('1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	4
Handler_prepare	2
Handler_read_first	3
Handler_read_key	12
Handler_read_next	3
Handler_read_rnd_next	3
SELECT * FROM t2;
a	b
2	2
8	8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
DELETE FROM t2 WHERE a = sf_a_from_t1b('1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	9
Handler_delete	1
Handler_external_lock	26
Handler_prepare	2
Handler_read_first	3
Handler_read_key	24
Handler_read_next	9
Handler_read_rnd_next	3
SELECT * FROM t2;
a	b
2	2
8	8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN DELETE FROM t2 WHERE a = sf_a_from_t1b_d('1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	t2	p1	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where
Warnings:
Note	1003	delete from `test`.`t2` where (`test`.`t2`.`a` = `sf_a_from_t1b_d`('1'))
FLUSH STATUS;
START TRANSACTION;
DELETE FROM t2 WHERE a = sf_a_from_t1b_d('1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	4
Handler_prepare	2
Handler_read_key	13
Handler_read_next	4
SELECT * FROM t2;
a	b
2	2
8	8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
DELETE FROM t2 WHERE a = sf_a_from_t1b_d('1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	9
Handler_delete	1
Handler_external_lock	26
Handler_prepare	2
Handler_read_key	25
Handler_read_next	10
SELECT * FROM t2;
a	b
2	2
8	8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN DELETE FROM t2 WHERE a = 7 + sf_a_from_t1b('1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	t2	p0,p1,p2	ALL	NULL	NULL	NULL	NULL	#	100.00	Using where
Warnings:
Note	1003	delete from `test`.`t2` where (`test`.`t2`.`a` = (7 + `sf_a_from_t1b`('1')))
FLUSH STATUS;
START TRANSACTION;
DELETE FROM t2 WHERE a = 7 + sf_a_from_t1b('1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	4
Handler_prepare	2
Handler_read_first	3
Handler_read_key	12
Handler_read_next	3
Handler_read_rnd_next	3
SELECT * FROM t2;
a	b
1	1
2	2
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
DELETE FROM t2 WHERE a = 7 + sf_a_from_t1b('1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	9
Handler_delete	1
Handler_external_lock	26
Handler_prepare	2
Handler_read_first	3
Handler_read_key	24
Handler_read_next	9
Handler_read_rnd_next	3
SELECT * FROM t2;
a	b
1	1
2	2
ROLLBACK;
UNLOCK TABLES;
EXPLAIN DELETE FROM t2 WHERE a = 7 + sf_a_from_t1b_d('1');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	t2	p2	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where
Warnings:
Note	1003	delete from `test`.`t2` where (`test`.`t2`.`a` = (7 + `sf_a_from_t1b_d`('1')))
FLUSH STATUS;
START TRANSACTION;
DELETE FROM t2 WHERE a = 7 + sf_a_from_t1b_d('1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	4
Handler_prepare	2
Handler_read_key	13
Handler_read_next	4
SELECT * FROM t2;
a	b
1	1
2	2
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
DELETE FROM t2 WHERE a = 7 + sf_a_from_t1b_d('1');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	9
Handler_delete	1
Handler_external_lock	26
Handler_prepare	2
Handler_read_key	25
Handler_read_next	10
SELECT * FROM t2;
a	b
1	1
2	2
ROLLBACK;
UNLOCK TABLES;
EXPLAIN DELETE FROM t2 WHERE a = sf_a_from_t1b('1') AND a = 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	t2	p2	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where
Warnings:
Note	1003	delete from `test`.`t2` where ((`test`.`t2`.`a` = 2) and (2 = `sf_a_from_t1b`('1')))
FLUSH STATUS;
START TRANSACTION;
DELETE FROM t2 WHERE a = sf_a_from_t1b('1') AND a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	4
Handler_read_next	1
SELECT * FROM t2;
a	b
1	1
2	2
8	8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
DELETE FROM t2 WHERE a = sf_a_from_t1b('1') AND a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	8
Handler_external_lock	26
Handler_read_key	16
Handler_read_next	7
SELECT * FROM t2;
a	b
1	1
2	2
8	8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN DELETE FROM t2 WHERE a = sf_a_from_t1b_d('1') AND a = 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	#	NULL	Impossible WHERE
Warnings:
Note	1003	delete from `test`.`t2` where ()
FLUSH STATUS;
START TRANSACTION;
DELETE FROM t2 WHERE a = sf_a_from_t1b_d('1') AND a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	3
Handler_read_next	1
SELECT * FROM t2;
a	b
1	1
2	2
8	8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
DELETE FROM t2 WHERE a = sf_a_from_t1b_d('1') AND a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	8
Handler_external_lock	26
Handler_read_key	15
Handler_read_next	7
SELECT * FROM t2;
a	b
1	1
2	2
8	8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN DELETE FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	#	NULL	Impossible WHERE
2	SUBQUERY	t1	p0,p1,p2	ref	b	b	1023	const	#	100.00	Using index
Warnings:
Note	1003	delete from `test`.`t2` where ()
FLUSH STATUS;
START TRANSACTION;
DELETE FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	1
Handler_external_lock	4
Handler_read_key	3
Handler_read_next	1
SELECT * FROM t2;
a	b
1	1
2	2
8	8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
DELETE FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	8
Handler_external_lock	26
Handler_read_key	15
Handler_read_next	7
SELECT * FROM t2;
a	b
1	1
2	2
8	8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN DELETE FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1') OR a = 2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	t2	p1,p2	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where
2	SUBQUERY	t1	p0,p1,p2	ref	b	b	1023	const	#	100.00	Using index
Warnings:
Note	1003	delete from `test`.`t2` where ((`test`.`t2`.`a` = (/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` where (`test`.`t1`.`b` = '1'))) or (`test`.`t2`.`a` = 2))
FLUSH STATUS;
START TRANSACTION;
DELETE FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1') OR a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	2
Handler_external_lock	4
Handler_prepare	2
Handler_read_key	5
Handler_read_next	1
SELECT * FROM t2;
a	b
8	8
ROLLBACK;
FLUSH STATUS;
SET autocommit = 0;
LOCK TABLES t1 read, t2 write;
DELETE FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1') OR a = 2;
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	9
Handler_delete	2
Handler_external_lock	26
Handler_prepare	2
Handler_read_key	17
Handler_read_next	7
SELECT * FROM t2;
a	b
8	8
ROLLBACK;
UNLOCK TABLES;
EXPLAIN DELETE FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2 OR a = 8 AND sf_a_from_t1b('2');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	DELETE	t2	p2	range	PRIMARY	PRIMARY	4	const	#	100.00	Using where
2	SUBQUERY	t1	p0,p1,p2	ref	b	b	1023	const	#	100.00	Using index
Warnings:
Note	1003	delete from `test`.`t2` where (((`test`.`t2`.`a` = 8) and `sf_a_from_t1b`('2')))
FLUSH STATUS;
START TRANSACTION;
DELETE FROM t2 WHERE a = (SELECT a FROM t1 WHERE b = '1') AND a = 2 OR a = 8 AND sf_a_from_t1b('2');
VARIABLE_NAME	VARIABLE_VALUE
Handler_commit	2
Handler_delete	1
Handler_external_lock	6
Handler_prepare	2
Handler_read_key	7
Handler_read_next	2
SELECT * FROM t2;
a	b
1	1
2	2
ROLLBACK;
set @@autocommit= @old_autocommit;
DROP FUNCTION sf_add_hello;
DROP FUNCTION sf_add_1;
DROP FUNCTION sf_a_from_t1b_d;
DROP FUNCTION sf_a_from_t1b;
DROP TABLE tq, tsq, t1, t2;
DROP TABLE test.thread_to_monitor;
SET @@global.innodb_stats_persistent= @old_innodb_stats_persistent;