diff options
| author | Sergei Golubchik <serg@mariadb.org> | 2016-08-25 12:40:09 +0200 |
|---|---|---|
| committer | Sergei Golubchik <serg@mariadb.org> | 2016-08-25 12:40:09 +0200 |
| commit | 6b1863b8304662189a3b9a4aef1e1bebef035b86 (patch) | |
| tree | 48ba2cbdda863b644108e93ace3668333ae0d193 /mysql-test/r | |
| parent | ea91bb6801b1b619d64fa137ea351eca9de683ec (diff) | |
| parent | 5bbe929d706e26cb3f9b291da6009526a17b1545 (diff) | |
| download | mariadb-git-6b1863b8304662189a3b9a4aef1e1bebef035b86.tar.gz | |
Merge branch '10.0' into 10.1
Diffstat (limited to 'mysql-test/r')
52 files changed, 675 insertions, 114 deletions
diff --git a/mysql-test/r/contributors.result b/mysql-test/r/contributors.result index 1e01ca81990..918ceaa496f 100644 --- a/mysql-test/r/contributors.result +++ b/mysql-test/r/contributors.result @@ -1,13 +1,14 @@ SHOW CONTRIBUTORS; Name Location Comment -Booking.com http://www.booking.com Founding member of the MariaDB Foundation -MariaDB Corporation https://mariadb.com Founding member of the MariaDB Foundation -Auttomattic http://automattic.com Member of the MariaDB Foundation -Visma http://visma.com Member of the MariaDB Foundation -Nexedi http://www.nexedi.com Member of the MariaDB Foundation -Acronis http://www.acronis.com Member of the MariaDB Foundation -Verkkokauppa.com Finland Sponsor of the MariaDB Foundation -Virtuozzo https://virtuozzo.com/ Sponsor of the MariaDB Foundation +Booking.com http://www.booking.com Founding member, Platinum Sponsor of the MariaDB Foundation +MariaDB Corporation https://mariadb.com Founding member, Gold Sponsor of the MariaDB Foundation +Visma http://visma.com Gold Sponsor of the MariaDB Foundation +DBS http://dbs.com Gold Sponsor of the MariaDB Foundation +Nexedi https://www.nexedi.com Silver Sponsor of the MariaDB Foundation +Acronis http://www.acronis.com Silver Sponsor of the MariaDB Foundation +Auttomattic https://automattic.com Bronze Sponsor of the MariaDB Foundation +Verkkokauppa.com https://virtuozzo.com Bronze Sponsor of the MariaDB Foundation +Virtuozzo https://virtuozzo.com/ Bronze Sponsor of the MariaDB Foundation Google USA Sponsoring encryption, parallel replication and GTID Facebook USA Sponsoring non-blocking API, LIMIT ROWS EXAMINED etc Ronald Bradford Brisbane, Australia EFF contribution for UC2006 Auction diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index cad46f34c75..3987ff7c16f 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -939,6 +939,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 7 diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index 5783247b673..9849068c894 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -79,6 +79,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -93,6 +94,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 12 @@ -166,6 +168,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -180,6 +183,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 12 @@ -232,6 +236,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -248,6 +253,7 @@ Handler_read_key 11 Handler_read_last 0 Handler_read_next 3 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 11 Handler_read_rnd_deleted 0 Handler_read_rnd_next 36 @@ -323,6 +329,7 @@ Handler_read_key 22 Handler_read_last 0 Handler_read_next 22 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 60 @@ -340,6 +347,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -356,6 +364,7 @@ Handler_read_key 11 Handler_read_last 0 Handler_read_next 3 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 11 Handler_read_rnd_deleted 0 Handler_read_rnd_next 36 @@ -451,6 +460,7 @@ Handler_read_key 2 Handler_read_last 0 Handler_read_next 2 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 8 Handler_read_rnd_deleted 0 Handler_read_rnd_next 39 diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 0d9d4ffff9f..318248459a5 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -2274,6 +2274,45 @@ EXECUTE stmt; t2_id GROUP_CONCAT(IF (t6.b, t6.f, t5.f) ORDER BY 1) DROP TABLE t1,t2,t3,t4,t5,t6; # +# MDEV-10500 CASE/IF Statement returns multiple values and shifts further result values to the next column +# +CREATE TABLE t1 ( +id int not null AUTO_INCREMENT, +active bool not null, +data1 bigint, +data2 bigint, +data3 bigint, +primary key (id) +); +INSERT INTO t1 (active,data1,data2,data3) VALUES (1,null,100,200); +SELECT +CASE WHEN active THEN SUM(data1) END AS C_1, +SUM(data2) AS C_2, +SUM(data3) AS C_3 +FROM t1; +C_1 C_2 C_3 +NULL 100 200 +SELECT +IF(active, SUM(data1), 5) AS C_1, +SUM(data2) AS C_2, +SUM(data3) AS C_3 +FROM t1; +C_1 C_2 C_3 +NULL 100 200 +DROP TABLE t1; +# +# MDEV-10468 Assertion `nr >= 0.0' failed in Item_sum_std::val_real() +# +SELECT STDDEV_POP(f) FROM (SELECT "1e+309" AS f UNION SELECT "-1e+309" AS f) tbl; +STDDEV_POP(f) +1.7976931348623157e308 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '1e+309' +Warning 1292 Truncated incorrect DOUBLE value: '-1e+309' +SELECT STDDEV(f) FROM (SELECT 1.7976931348623157e+308 AS f UNION SELECT -1.7976931348623157e+308 AS f) tbl; +STDDEV(f) +1.7976931348623157e308 +# # MDEV-8852 Implicit or explicit CAST from MAX(string) to INT,DOUBLE,DECIMAL does not produce warnings # SELECT MAX('x') << 1, CAST(MAX('x') AS DOUBLE), CAST(MAX('x') AS DECIMAL); diff --git a/mysql-test/r/func_math.result b/mysql-test/r/func_math.result index f8e89dbc1c2..f694d25cb04 100644 --- a/mysql-test/r/func_math.result +++ b/mysql-test/r/func_math.result @@ -787,10 +787,16 @@ select 5.9 div 2, 1.23456789e3 DIV 2, 1.23456789e9 DIV 2, 1.23456789e19 DIV 2; 5.9 div 2 1.23456789e3 DIV 2 1.23456789e9 DIV 2 1.23456789e19 DIV 2 2 617 617283945 6172839450000000000 # -# End of 5.5 tests +# MDEV-10467 Assertion `nr >= 0.0' failed in Item_sum_std::val_real() # +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (1),(2); +SELECT STDDEV_SAMP(ROUND('0', 309)) FROM t1; +STDDEV_SAMP(ROUND('0', 309)) +0 +DROP TABLE t1; # -# Start of 10.0 tests +# End of 5.5 tests # # # MDEV-5781 Item_sum_std::val_real(): Assertion `nr >= 0.0' fails on query with STDDEV_POP, ROUND and variable diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result index 700fda2e182..06ea89d9ab8 100644 --- a/mysql-test/r/func_misc.result +++ b/mysql-test/r/func_misc.result @@ -570,6 +570,13 @@ AND 57813X540X1723 = 'Test'; N AVG 0 NULL drop table t1; +SELECT NAME_CONST('a', -(1 OR 2)) OR 1; +ERROR HY000: Incorrect arguments to NAME_CONST +SELECT NAME_CONST('a', -(1 AND 2)) OR 1; +ERROR HY000: Incorrect arguments to NAME_CONST +SELECT NAME_CONST('a', -(1)) OR 1; +NAME_CONST('a', -(1)) OR 1 +1 # # End of 5.5 tests # diff --git a/mysql-test/r/grant2.result b/mysql-test/r/grant2.result index bd14f21010b..cf58a2dc6f5 100644 --- a/mysql-test/r/grant2.result +++ b/mysql-test/r/grant2.result @@ -710,7 +710,7 @@ mysqltest_db1.t3 preload_keys status OK # RENAME (doesn't work for temporary tables, thus should fail). # RENAME TABLE t3 TO t3_1; -ERROR 42000: DROP, ALTER command denied to user 'mysqltest_u1'@'localhost' for table 't3' +ERROR 42000: INSERT, CREATE command denied to user 'mysqltest_u1'@'localhost' for table 't3_1' # # HANDLER OPEN/READ/CLOSE. # diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index cf867dd1e18..7d57222aafd 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -2378,6 +2378,7 @@ SHOW STATUS LIKE 'handler_read__e%'; Variable_name Value Handler_read_key 8 Handler_read_next 0 +Handler_read_retry 0 EXPLAIN SELECT max(b), a FROM t1 GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range NULL a 5 NULL 8 Using index for group-by @@ -2387,6 +2388,7 @@ SHOW STATUS LIKE 'handler_read__e%'; Variable_name Value Handler_read_key 8 Handler_read_next 0 +Handler_read_retry 0 FLUSH STATUS; SELECT * FROM (SELECT max(b), a FROM t1 GROUP BY a) b; max(b) a @@ -2398,6 +2400,7 @@ SHOW STATUS LIKE 'handler_read__e%'; Variable_name Value Handler_read_key 8 Handler_read_next 0 +Handler_read_retry 0 FLUSH STATUS; (SELECT max(b), a FROM t1 GROUP BY a) UNION (SELECT max(b), a FROM t1 GROUP BY a); @@ -2410,6 +2413,7 @@ SHOW STATUS LIKE 'handler_read__e%'; Variable_name Value Handler_read_key 16 Handler_read_next 0 +Handler_read_retry 0 EXPLAIN (SELECT max(b), a FROM t1 GROUP BY a) UNION (SELECT max(b), a FROM t1 GROUP BY a); id select_type table type possible_keys key key_len ref rows Extra @@ -2462,6 +2466,7 @@ SHOW STATUS LIKE 'handler_read__e%'; Variable_name Value Handler_read_key 8 Handler_read_next 0 +Handler_read_retry 0 DELETE FROM t3; FLUSH STATUS; INSERT INTO t3 SELECT 1, (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) @@ -2470,12 +2475,14 @@ SHOW STATUS LIKE 'handler_read__e%'; Variable_name Value Handler_read_key 8 Handler_read_next 0 +Handler_read_retry 0 FLUSH STATUS; DELETE FROM t3 WHERE (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) > 10000; SHOW STATUS LIKE 'handler_read__e%'; Variable_name Value Handler_read_key 8 Handler_read_next 0 +Handler_read_retry 0 FLUSH STATUS; DELETE FROM t3 WHERE (SELECT (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) x FROM t1) > 10000; @@ -2484,6 +2491,7 @@ SHOW STATUS LIKE 'handler_read__e%'; Variable_name Value Handler_read_key 8 Handler_read_next 1 +Handler_read_retry 0 DROP TABLE t1,t2,t3; CREATE TABLE t1 (a int, INDEX idx(a)); INSERT INTO t1 VALUES diff --git a/mysql-test/r/handler_read_last.result b/mysql-test/r/handler_read_last.result index 574c3c25ab1..9dd3784673a 100644 --- a/mysql-test/r/handler_read_last.result +++ b/mysql-test/r/handler_read_last.result @@ -12,6 +12,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -26,6 +27,7 @@ Handler_read_key 0 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -42,6 +44,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 2 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -58,6 +61,7 @@ Handler_read_key 0 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 2 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 diff --git a/mysql-test/r/information_schema_stats.result b/mysql-test/r/information_schema_stats.result index cd73636879c..bc2ce9f0294 100644 --- a/mysql-test/r/information_schema_stats.result +++ b/mysql-test/r/information_schema_stats.result @@ -16,8 +16,8 @@ count(*) 2 select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test'; TABLE_SCHEMA TABLE_NAME INDEX_NAME ROWS_READ -test just_a_test IND_just_a_test_state 2 test just_a_test IND_just_a_test_first_name_last_name 1 +test just_a_test IND_just_a_test_state 2 select * from information_schema.table_statistics where table_schema='test' and table_name='just_a_test'; TABLE_SCHEMA TABLE_NAME ROWS_READ ROWS_CHANGED ROWS_CHANGED_X_INDEXES test just_a_test 18 5 5 @@ -56,9 +56,9 @@ count(*) 3 select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test'; TABLE_SCHEMA TABLE_NAME INDEX_NAME ROWS_READ +test just_a_test PRIMARY 5 test just_a_test first_name 1 test just_a_test state 2 -test just_a_test PRIMARY 5 select * from information_schema.table_statistics where table_schema='test' and table_name='just_a_test'; TABLE_SCHEMA TABLE_NAME ROWS_READ ROWS_CHANGED ROWS_CHANGED_X_INDEXES test just_a_test 8 5 15 diff --git a/mysql-test/r/innodb_ext_key.result b/mysql-test/r/innodb_ext_key.result index f800676c863..1305be86e5a 100644 --- a/mysql-test/r/innodb_ext_key.result +++ b/mysql-test/r/innodb_ext_key.result @@ -20,6 +20,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 5 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -39,6 +40,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 1 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -60,6 +62,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -81,6 +84,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -102,6 +106,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 6 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -123,6 +128,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 1 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -146,6 +152,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 6 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -169,6 +176,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 3 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -188,6 +196,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 6 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -207,6 +216,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -228,6 +238,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 6 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -249,6 +260,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -270,6 +282,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 5 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -291,6 +304,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -317,6 +331,7 @@ Handler_read_key 2 Handler_read_last 0 Handler_read_next 9 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 9 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -343,6 +358,7 @@ Handler_read_key 2 Handler_read_last 0 Handler_read_next 2 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 2 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -370,6 +386,7 @@ Handler_read_key 2 Handler_read_last 0 Handler_read_next 9 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 9 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -397,6 +414,7 @@ Handler_read_key 2 Handler_read_last 0 Handler_read_next 3 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 3 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -422,6 +440,7 @@ Handler_read_key 2 Handler_read_last 0 Handler_read_next 9 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 3 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -447,6 +466,7 @@ Handler_read_key 2 Handler_read_last 0 Handler_read_next 3 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 3 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -477,6 +497,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 294 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -507,6 +528,7 @@ Handler_read_key 21 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -529,6 +551,7 @@ Handler_read_key 2 Handler_read_last 0 Handler_read_next 1230 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -551,6 +574,7 @@ Handler_read_key 6 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -581,6 +605,7 @@ Handler_read_key 3 Handler_read_last 0 Handler_read_next 26 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -610,6 +635,7 @@ Handler_read_key 3 Handler_read_last 0 Handler_read_next 3 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -807,6 +833,7 @@ Handler_read_key 10 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 11 @@ -828,6 +855,7 @@ Handler_read_key 10 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 11 diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result index 8bfc4e9215e..3f4a26a728e 100644 --- a/mysql-test/r/insert_select.result +++ b/mysql-test/r/insert_select.result @@ -701,6 +701,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 1 diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index e7292e8ddce..d500b38a8dc 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -860,6 +860,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 5 @@ -1271,6 +1272,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 1 diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 266c8332b36..b3dfc0835f0 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1244,6 +1244,7 @@ Handler_read_key 5 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 6 @@ -1805,6 +1806,7 @@ Handler_read_key 4 Handler_read_last 0 Handler_read_next 5 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 1048581 @@ -1819,6 +1821,7 @@ Handler_read_key 4 Handler_read_last 0 Handler_read_next 5 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 1048581 diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result index 5710dac519d..158e845d730 100644 --- a/mysql-test/r/join_outer_jcl6.result +++ b/mysql-test/r/join_outer_jcl6.result @@ -1255,6 +1255,7 @@ Handler_read_key 5 Handler_read_last 0 Handler_read_next 9 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 3 Handler_read_rnd_deleted 0 Handler_read_rnd_next 6 @@ -1816,6 +1817,7 @@ Handler_read_key 4 Handler_read_last 0 Handler_read_next 5 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 5 Handler_read_rnd_deleted 0 Handler_read_rnd_next 1048581 @@ -1830,6 +1832,7 @@ Handler_read_key 4 Handler_read_last 0 Handler_read_next 5 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 5 Handler_read_rnd_deleted 0 Handler_read_rnd_next 1048581 diff --git a/mysql-test/r/limit_rows_examined.result b/mysql-test/r/limit_rows_examined.result index 130d17ae270..1f829d545f6 100644 --- a/mysql-test/r/limit_rows_examined.result +++ b/mysql-test/r/limit_rows_examined.result @@ -751,6 +751,7 @@ Handler_read_key 5 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 46 @@ -775,6 +776,7 @@ Handler_read_key 5 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 2 Handler_read_rnd_deleted 1 Handler_read_rnd_next 47 diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result index 8cd3b0a7ac8..c54d9a8277f 100644 --- a/mysql-test/r/loaddata.result +++ b/mysql-test/r/loaddata.result @@ -531,7 +531,7 @@ DROP TABLE t1; # Bug#11765139 58069: LOAD DATA INFILE: VALGRIND REPORTS INVALID MEMORY READS AND WRITES WITH U # CREATE TABLE t1(f1 INT); -SELECT 0xE1BB30 INTO OUTFILE 't1.dat'; +SELECT 0xE1C330 INTO OUTFILE 't1.dat'; LOAD DATA INFILE 't1.dat' IGNORE INTO TABLE t1 CHARACTER SET utf8; DROP TABLE t1; # @@ -556,3 +556,27 @@ FIELDS TERMINATED BY 't' LINES TERMINATED BY ''; Got one of the listed errors SET @@sql_mode= @old_mode; DROP TABLE t1; + +# +# Bug#23080148 - Backport of Bug#20683959. +# Bug#20683959 LOAD DATA INFILE IGNORES A SPECIFIC ROW SILENTLY +# UNDER DB CHARSET IS UTF8. +# +CREATE DATABASE d1 CHARSET latin1; +USE d1; +CREATE TABLE t1 (val TEXT); +LOAD DATA INFILE '../../std_data/bug20683959loaddata.txt' INTO TABLE t1; +SELECT COUNT(*) FROM t1; +COUNT(*) +1 +SELECT HEX(val) FROM t1; +HEX(val) +C38322525420406E696F757A656368756E3A20E98198E2889AF58081AEE7B99DE4B88AE383A3E7B99DE69690F58087B3E7B9A7EFBDA8E7B99DEFBDB3E7B99DE78999E880B3E7B8BAEFBDAAE7B9A7E89699E296A1E7B8BAE4BBA3EFBD8CE7B8BAEFBDA9E7B8B2E2889AE38184E7B99DEFBDB3E7B99DE4B88AE383A3E7B99DE69690F58087B3E7B9A7EFBDA8E7B99DEFBDB3E7B99DE5B3A8EFBD84E8ABA0EFBDA8E89C89F580948EE599AAE7B8BAEFBDAAE7B8BAE9A198EFBDA9EFBDB1E7B9A7E581B5E289A0E7B8BAEFBDBEE7B9A7E9A194EFBDA9E882B4EFBDA5EFBDB5E980A7F5808B96E28693E99EABE38287E58F99E7B8BAE58AB1E28691E7B8BAF5808B9AE7828AE98095EFBDB1E7B8BAEFBDAFE7B8B2E288ABE6A89FE89EB3E6BA98F58081ADE88EA0EFBDBAE98095E6BA98F58081AEE89D93EFBDBAE8AD9BEFBDACE980A7F5808B96E28693E7B8BAF580918EE288AAE7B8BAE4B88AEFBC9EE7B8BAE4B99DE28691E7B8BAF5808B96EFBCA0E88DB3E6A68AEFBDB9EFBDB3E981B2E5B3A8E296A1E7B8BAE7A4BCE7828AE88DB3E6A68AEFBDB0EFBDBDE7B8BAA0E7B8BAE88B93EFBDBEE5B899EFBC9E +CREATE DATABASE d2 CHARSET utf8; +USE d2; +CREATE TABLE t1 (val TEXT); +LOAD DATA INFILE '../../std_data/bug20683959loaddata.txt' INTO TABLE t1; +ERROR HY000: Invalid utf8 character string: 'Ã"RT @niouzechun: \9058\221A' +DROP TABLE d1.t1, d2.t1; +DROP DATABASE d1; +DROP DATABASE d2; diff --git a/mysql-test/r/myisam_enable_keys-10506.result b/mysql-test/r/myisam_enable_keys-10506.result new file mode 100644 index 00000000000..e7ffba787f0 --- /dev/null +++ b/mysql-test/r/myisam_enable_keys-10506.result @@ -0,0 +1,114 @@ +CREATE TABLE t1 ( +pk INT AUTO_INCREMENT, +i INT, +d DATE, +dt DATETIME, +v VARCHAR(1), +PRIMARY KEY (pk), +KEY (dt) +) ENGINE=MyISAM; +INSERT INTO t1 (i, d, dt, v) VALUES +(9, '2005-07-23', '2004-05-13 01:01:39', 't'), +(2, '2009-11-01', '2003-12-24 07:39:29', 'h'), +(6, NULL, '2008-07-03 05:32:22', 'l'), +(6, '2007-07-16', '2008-08-28 18:46:11', 'j'), +(5, NULL, '2001-07-12 21:27:00', 'h'), +(3, '2007-07-22', '1900-01-01 00:00:00', 'p'), +(2, '2000-11-21', '2007-05-25 11:58:54', 'g'), +(6, '1900-01-01', '2009-06-03 17:11:10', 'i'), +(2, '2008-02-10', '2001-06-15 16:20:07', 'p'), +(3, '2009-06-04', '1900-01-01 00:00:00', 'h'), +(9, '2007-04-25', '1900-01-01 00:00:00', 'e'), +(9, '2006-03-02', '1900-01-01 00:00:00', 'e'), +(1, '1900-01-01', '2002-11-08 09:33:27', 'u'), +(7, '2008-07-13', '2007-08-07 17:35:52', 'j'), +(0, '2004-11-12', '2006-05-01 00:00:00', 'e'), +(0, '1900-01-01', '2003-05-01 00:00:00', 'z'), +(1, '2009-09-02', '2007-02-12 09:30:49', 'w'), +(0, '2004-11-06', '1900-01-01 00:00:00', 't'), +(4, '2003-01-06', '2002-07-03 02:51:11', 'i'), +(6, '2006-01-14', '2008-02-26 04:57:32', 'i'), +(0, '2002-01-19', '2009-02-12 00:00:00', 'i'), +(8, '2007-02-12', '1900-01-01 00:00:00', 'b'), +(4, '1900-01-01', '2001-05-16 05:28:40', 'm'), +(2, '2005-07-16', NULL, 'j'), +(1, '2004-09-04', '2001-01-24 21:45:18', 'v'), +(3, '2009-07-01', NULL, NULL), +(2, '2009-07-21', '2002-07-24 00:00:00', 'h'), +(4, NULL, '2001-11-03 12:22:30', 'q'), +(1, '2002-06-22', '2008-06-17 03:17:59', 'f'), +(7, '2005-06-23', '2005-12-24 00:00:00', 'p'), +(6, '2001-05-20', '2008-10-23 00:00:00', NULL), +(3, '2001-10-01', '2000-10-12 16:32:35', 'o'), +(3, '2001-01-07', '2005-09-11 10:09:54', 'w'), +(6, '2007-11-02', '2009-09-10 01:44:18', 'l'), +(6, NULL, NULL, 'i'), +(9, NULL, '2002-05-18 15:21:55', 'd'), +(4, '2008-12-21', '2004-10-15 10:09:54', 'j'), +(6, '2003-10-05', '2009-07-13 03:51:02', 'e'), +(2, '2001-03-03', '1900-01-01 00:00:00', 'e'), +(2, '2007-04-04', '2001-11-08 21:14:52', 'q'), +(5, NULL, '2006-12-02 00:00:00', 'm'), +(0, '2009-01-04', '1900-01-01 00:00:00', NULL), +(8, '2008-04-03', '2005-01-01 11:55:18', 'q'), +(8, NULL, '2005-02-28 03:44:02', 'w'), +(0, '2003-08-22', NULL, 'c'), +(9, '1900-01-01', NULL, 'y'), +(NULL, NULL, '2006-08-25 16:28:09', 'g'), +(5, '2004-07-04', '2002-08-11 00:00:00', 'z'), +(1, '1900-01-01', '2007-07-22 21:19:18', 'm'), +(2, '2007-02-04', '2006-02-10 18:41:38', 't'), +(2, '1900-01-01', '2009-02-16 14:58:58', 'd'), +(7, '2001-03-14', '2007-08-14 00:00:00', 'h'), +(0, NULL, '1900-01-01 00:00:00', NULL), +(1, '2008-10-05', NULL, 'f'), +(6, '2001-11-25', '2008-12-03 06:59:23', 'l'), +(NULL, '2003-01-27', '2008-10-04 00:00:00', 'g'), +(8, '2008-08-08', '2009-07-07 07:00:21', 'v'), +(8, '2006-07-03', '2001-04-15 00:00:00', NULL), +(5, '2002-11-21', '2007-07-08 04:01:58', 'm'), +(5, '2006-04-08', '2007-09-23 00:01:35', 'i'), +(5, '2001-05-06', '2008-05-15 00:00:00', 'h'), +(7, '1900-01-01', '1900-01-01 00:00:00', 'u'), +(30, '2007-04-16', '2004-03-05 23:35:38', 'o'), +(NULL, '1900-01-01', '2007-08-25 01:32:47', 'z'), +(6, '2004-12-03', '1900-01-01 00:00:00', 'o'), +(8, '2001-06-23', '1900-01-01 00:00:00', 'f'), +(NULL, '2008-12-15', '2001-05-19 08:28:28', 'a'), +(9, '2000-02-15', '2009-09-03 06:07:22', 'd'), +(2, '2001-08-05', '2006-10-08 07:17:27', 'k'), +(5, '2004-01-17', '2003-09-06 20:36:01', 'd'), +(4, '2003-10-01', '2001-02-05 18:10:49', 'u'), +(4, '2003-07-28', '2001-01-07 16:11:37', 'h'), +(0, '1900-01-01', '2008-08-01 05:26:38', 'w'), +(9, '1900-01-01', '2001-05-08 00:00:00', 't'), +(1, '2000-04-17', '2008-07-10 21:26:28', 'i'), +(8, '2002-01-05', '2006-08-06 20:56:35', 'k'), +(9, '2001-04-10', '2003-02-17 00:00:00', 'z'), +(0, '2009-12-04', NULL, 'h'), +(7, NULL, '2004-10-27 00:29:57', 'h'), +(2, '2006-03-07', '2008-03-04 06:14:13', 'b'), +(0, '2001-10-15', '2001-03-17 00:00:00', 'm'), +(5, '1900-01-01', '2009-02-21 11:35:50', 'i'), +(4, NULL, '1900-01-01 00:00:00', 'w'), +(5, '2009-04-05', '1900-01-01 00:00:00', 'm'), +(6, '2001-03-19', '2001-04-12 00:00:00', 'q'), +(NULL, '2009-12-08', '2001-12-04 20:21:01', 'k'), +(2, '2005-02-09', '2001-05-27 08:41:01', 'l'), +(9, '2004-05-25', '2004-09-18 00:00:00', 'c'), +(3, '2005-01-17', '2002-09-12 11:18:48', 'd'), +(0, '2003-08-28', '1900-01-01 00:00:00', 'k'), +(6, '2006-10-11', '2003-10-28 03:31:02', 'a'), +(5, '1900-01-01', '2001-08-22 10:20:09', 'p'), +(8, '1900-01-01', '2008-04-24 00:00:00', 'o'), +(4, '2005-08-18', '2006-11-10 10:08:49', 'e'), +(NULL, '2007-03-12', '2007-10-16 00:00:00', 'n'), +(1, '2000-11-18', '2009-05-27 12:25:07', 't'), +(4, '2001-03-03', NULL, 'u'), +(3, '2003-09-11', '2001-09-10 18:10:10', 'f'), +(4, '2007-06-17', '1900-01-01 00:00:00', 't'), +(NULL, '2008-09-11', '2004-06-07 23:17:09', 'k'); +ALTER TABLE t1 ADD UNIQUE KEY ind1 (pk, d, i, v); +ALTER TABLE t1 ADD UNIQUE KEY ind2 (d, v); +ERROR 23000: Duplicate entry '2008-09-11-k' for key 'ind2' +DROP TABLE t1; diff --git a/mysql-test/r/mysqlcheck.result b/mysql-test/r/mysqlcheck.result index e0d93b02ecd..8894616fbb3 100644 --- a/mysql-test/r/mysqlcheck.result +++ b/mysql-test/r/mysqlcheck.result @@ -343,10 +343,37 @@ DROP TABLE bug47205; # #MDEV-6128:[PATCH] mysqlcheck wrongly escapes '.' in table names # -CREATE TABLE test.`t.1` (id int); +create table `t.1` (id int); +create view `v.1` as select 1; mysqlcheck test t.1 test.t.1 OK -drop table test.`t.1`; +mysqlcheck --all-in-1 test t.1 +test.t.1 OK +mysqlcheck --all-in-1 --databases --process-views test +test.t.1 OK +test.v.1 OK +create table `t.2`(a varchar(20) primary key) default character set utf8 collate utf8_general_ci engine=innodb; +flush table `t.2`; +mysqlcheck --check-upgrade --auto-repair test +test.t.1 OK +test.t.2 +error : Table rebuild required. Please do "ALTER TABLE `t.2` FORCE" or dump/reload to fix it! +test.t.3 Needs upgrade + +Repairing tables +test.t.3 OK +check table `t.1`, `t.2`, `t.3`; +Table Op Msg_type Msg_text +test.t.1 check status OK +test.t.2 check status OK +test.t.3 check status OK +check table `t.1`, `t.2`, `t.3` for upgrade; +Table Op Msg_type Msg_text +test.t.1 check status OK +test.t.2 check status OK +test.t.3 check status OK +drop view `v.1`; +drop table test.`t.1`, `t.2`, `t.3`; # # MDEV-8123 mysqlcheck: new --process-views option conflicts with --quick, --extended and such # @@ -381,6 +408,57 @@ show tables; Tables_in_test t1`1 drop table `t1``1`; +call mtr.add_suppression("ha_myisam"); +call mtr.add_suppression("Checking table"); +create database mysqltest1; +create table mysqltest1.t1 (a int) engine=myisam; +create table t2 (a int); +check table mysqltest1.t1; +Table Op Msg_type Msg_text +mysqltest1.t1 check warning Size of datafile is: 4 Should be: 0 +mysqltest1.t1 check error got error: 0 when reading datafile at record: 0 +mysqltest1.t1 check error Corrupt +mtr.global_suppressions Table is already up to date +mtr.test_suppressions Table is already up to date +mysql.column_stats Table is already up to date +mysql.columns_priv Table is already up to date +mysql.db Table is already up to date +mysql.event Table is already up to date +mysql.func Table is already up to date +mysql.gtid_slave_pos Table is already up to date +mysql.help_category Table is already up to date +mysql.help_keyword Table is already up to date +mysql.help_relation Table is already up to date +mysql.help_topic Table is already up to date +mysql.host Table is already up to date +mysql.index_stats Table is already up to date +mysql.innodb_index_stats OK +mysql.innodb_table_stats OK +mysql.plugin Table is already up to date +mysql.proc Table is already up to date +mysql.procs_priv Table is already up to date +mysql.proxies_priv Table is already up to date +mysql.roles_mapping Table is already up to date +mysql.servers Table is already up to date +mysql.table_stats Table is already up to date +mysql.tables_priv Table is already up to date +mysql.time_zone Table is already up to date +mysql.time_zone_leap_second Table is already up to date +mysql.time_zone_name Table is already up to date +mysql.time_zone_transition Table is already up to date +mysql.time_zone_transition_type Table is already up to date +mysql.user Table is already up to date +mysqltest1.t1 +warning : Table is marked as crashed +warning : Size of datafile is: 4 Should be: 0 +error : got error: 0 when reading datafile at record: 0 +error : Corrupt +test.t2 Table is already up to date + +Repairing tables +mysqltest1.t1 OK +drop table t2; +drop database mysqltest1; # #MDEV-7384 [PATCH] add PERSISENT FOR ALL option to mysqlanalyze/mysqlcheck # diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result index e80851329a4..81bfa1ac994 100644 --- a/mysql-test/r/null_key.result +++ b/mysql-test/r/null_key.result @@ -429,6 +429,7 @@ Handler_read_key 6 Handler_read_last 0 Handler_read_next 2 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 5 diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index d108bce5eb1..50ac3435596 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -2864,6 +2864,7 @@ Handler_read_key 250 Handler_read_last 0 Handler_read_next 249 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 249 Handler_read_rnd_deleted 0 Handler_read_rnd_next 250 @@ -2882,6 +2883,7 @@ Handler_read_key 250 Handler_read_last 0 Handler_read_next 249 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 1 Handler_read_rnd_deleted 0 Handler_read_rnd_next 250 diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index 71adc194693..6cb5845de40 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -562,6 +562,7 @@ Handler_read_key 2 Handler_read_last 0 Handler_read_next 4 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -582,6 +583,7 @@ Handler_read_key 2 Handler_read_last 0 Handler_read_next 4 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -614,6 +616,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 2 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -632,6 +635,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 2 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -650,6 +654,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 2 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -668,6 +673,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 2 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 diff --git a/mysql-test/r/partition_explicit_prune.result b/mysql-test/r/partition_explicit_prune.result index 765803d6332..3ca1e688e8f 100644 --- a/mysql-test/r/partition_explicit_prune.result +++ b/mysql-test/r/partition_explicit_prune.result @@ -19,7 +19,7 @@ WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 HANDLER_READ_RND_NEXT 3 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 # 4 locks (1 table, 1 partition lock/unlock) FLUSH STATUS; SELECT a FROM t1 PARTITION (p0); @@ -31,7 +31,7 @@ WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 HANDLER_READ_RND_NEXT 3 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 # 4 locks (1 table, 1 partition lock/unlock) FLUSH STATUS; INSERT INTO v1 VALUES (10); @@ -39,14 +39,14 @@ SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 HANDLER_WRITE 2 # 4 locks (1 table, 1 partition lock/unlock) FLUSH STATUS; SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 # 2 locks (1 table, all partitions pruned) FLUSH STATUS; SELECT * FROM v1; @@ -59,7 +59,7 @@ WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 HANDLER_READ_RND_NEXT 4 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 # 4 locks (1 table, 1 partition lock/unlock) FLUSH STATUS; SELECT a FROM t1 PARTITION (p0); @@ -72,7 +72,7 @@ WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 HANDLER_READ_RND_NEXT 4 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 # 4 locks (1 table, 1 partition lock/unlock) SELECT * FROM t1; a @@ -89,14 +89,14 @@ SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 HANDLER_WRITE 2 # 4 locks (1 table, 1 partition lock/unlock) FLUSH STATUS; SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 # 2 locks (1 table, all partitions pruned) SELECT * FROM v1; a @@ -121,7 +121,7 @@ SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 HANDLER_WRITE 2 # 4 locks (1 table, 1 partition lock/unlock) FLUSH STATUS; @@ -131,7 +131,7 @@ SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_ROLLBACK 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 # 2 locks (1 table, all partitions pruned) FLUSH STATUS; INSERT INTO v1 VALUES (32); @@ -140,7 +140,7 @@ SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_ROLLBACK 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 # 4 locks (1 table, 1 partition lock/unlock) SELECT * FROM v1; a @@ -166,12 +166,12 @@ FLUSH STATUS; SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE -HANDLER_READ_RND_NEXT 26 -HANDLER_TMP_WRITE 47 +HANDLER_READ_RND_NEXT 27 +HANDLER_TMP_WRITE 49 # OK, seems to add number of variables processed before HANDLER_WRITE # and number of variables + 1 evaluated in the previous call in RND_NEXT CREATE TABLE t1 @@ -233,7 +233,7 @@ SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 HANDLER_WRITE 2 # Should be 1 commit # 4 external locks (due to pruning of locks) @@ -250,7 +250,7 @@ SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 HANDLER_WRITE 2 # Should be 1 commit # 4 external locks @@ -262,7 +262,7 @@ SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 # should be 1 commit # 9 locks (1 ha_partition + 8 ha_innobase) # 17 writes (internal I_S) @@ -271,8 +271,8 @@ SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 2 -HANDLER_READ_RND_NEXT 26 -HANDLER_TMP_WRITE 47 +HANDLER_READ_RND_NEXT 27 +HANDLER_TMP_WRITE 49 HANDLER_WRITE 2 # + 1 commit # + 19 rnd next (internal I_S) @@ -282,8 +282,8 @@ SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 2 -HANDLER_READ_RND_NEXT 52 -HANDLER_TMP_WRITE 72 +HANDLER_READ_RND_NEXT 54 +HANDLER_TMP_WRITE 75 HANDLER_WRITE 2 # + 9 locks (unlocks) # + 19 rnd next (internal I_S) @@ -338,7 +338,7 @@ ERROR HY000: Unknown partition 'pNonexistent' in table 't1' SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 # should have failed before locking (only 17 internal I_S writes) FLUSH STATUS; SELECT * FROM t1 PARTITION (subp2); @@ -348,7 +348,7 @@ WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 HANDLER_READ_FIRST 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 # Should be 1 commit # 4 locks (1 ha_partition + 1 ha_innobase) x 2 (lock/unlock) # 1 read first (also calls index_read) @@ -366,7 +366,7 @@ VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 HANDLER_READ_FIRST 3 HANDLER_READ_NEXT 3 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 # Should be 1 commit # 8 locks (1 ha_partition + 2 + 1 ha_innobase) x 2 # 3 read first (one for each partition) @@ -379,7 +379,7 @@ SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 # 1 commit # 18 locks # 18 READ KEY from opening a new partition table instance, @@ -398,8 +398,8 @@ VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 2 HANDLER_READ_FIRST 1 HANDLER_READ_NEXT 3 -HANDLER_READ_RND_NEXT 26 -HANDLER_TMP_WRITE 47 +HANDLER_READ_RND_NEXT 27 +HANDLER_TMP_WRITE 49 # + 1 commit # + 1 read first (read first key from index in one partition) # + 2 read key (innobase_get_index from index_init + from index_first) @@ -415,8 +415,8 @@ VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 3 HANDLER_READ_FIRST 3 HANDLER_READ_NEXT 4 -HANDLER_READ_RND_NEXT 52 -HANDLER_TMP_WRITE 72 +HANDLER_READ_RND_NEXT 54 +HANDLER_TMP_WRITE 75 # + 1 commit # + 2 read first (one for each subpart) # + 4 read key (innobase_get_index from index_init + from index_first) @@ -431,8 +431,8 @@ VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 4 HANDLER_READ_FIRST 3 HANDLER_READ_NEXT 4 -HANDLER_READ_RND_NEXT 78 -HANDLER_TMP_WRITE 97 +HANDLER_READ_RND_NEXT 81 +HANDLER_TMP_WRITE 101 # No matching partition, only internal I_S. SELECT * FROM t1 PARTITION (pNeg) WHERE a = 100; a b @@ -443,8 +443,8 @@ VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 5 HANDLER_READ_FIRST 3 HANDLER_READ_NEXT 4 -HANDLER_READ_RND_NEXT 104 -HANDLER_TMP_WRITE 122 +HANDLER_READ_RND_NEXT 108 +HANDLER_TMP_WRITE 127 # + 18 for unlock (same as lock above) (100 is not in pNeg, no match) # Test that EXPLAIN PARTITION works EXPLAIN PARTITIONS SELECT * FROM t1 PARTITION (pNonexistent); @@ -460,7 +460,7 @@ SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 # 8 locks (1 ha_partition + 3 ha_innobase) x 2 (lock/unlock) EXPLAIN PARTITIONS SELECT * FROM t1 PARTITION (subp3) AS TableAlias; id select_type table partitions type possible_keys key key_len ref rows Extra @@ -513,7 +513,7 @@ SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_ROLLBACK 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 HANDLER_WRITE 1 # 2 locks (1 ha_partition) x 2 (lock/unlock), Was 4 locks before WL4443 # explicit pruning says part_id 0 and implicit pruning says part_id 1 @@ -526,7 +526,7 @@ SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 HANDLER_WRITE 2 # 1 commit # 4 locks (1 ha_partition + 1 ha_innobase) x 2 (lock/unlock) @@ -544,7 +544,7 @@ WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 HANDLER_READ_KEY 2 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 HANDLER_UPDATE 2 HANDLER_WRITE 2 # 1 commit @@ -566,7 +566,7 @@ SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 # 1 commit # 9 locks # 17 write (internal I_S) @@ -578,8 +578,8 @@ HANDLER_COMMIT 2 HANDLER_DELETE 2 HANDLER_READ_KEY 1 HANDLER_READ_NEXT 1 -HANDLER_READ_RND_NEXT 26 -HANDLER_TMP_WRITE 47 +HANDLER_READ_RND_NEXT 27 +HANDLER_TMP_WRITE 49 # + 1 commit # + 1 delete (one row deleted) # + 3 read key (1 innodb_get_index in records_in_range, @@ -596,9 +596,9 @@ HANDLER_COMMIT 2 HANDLER_DELETE 2 HANDLER_READ_KEY 1 HANDLER_READ_NEXT 1 -HANDLER_READ_RND_NEXT 52 +HANDLER_READ_RND_NEXT 54 HANDLER_ROLLBACK 1 -HANDLER_TMP_WRITE 72 +HANDLER_TMP_WRITE 75 HANDLER_WRITE 1 # Failed before start_stmt/execution. # + 19 rnd next (internal I_S) @@ -612,9 +612,9 @@ HANDLER_COMMIT 3 HANDLER_DELETE 2 HANDLER_READ_KEY 1 HANDLER_READ_NEXT 1 -HANDLER_READ_RND_NEXT 78 +HANDLER_READ_RND_NEXT 81 HANDLER_ROLLBACK 1 -HANDLER_TMP_WRITE 97 +HANDLER_TMP_WRITE 101 HANDLER_WRITE 3 # + 1 commit # + 19 rnd next (internal I_S) @@ -627,9 +627,9 @@ HANDLER_COMMIT 4 HANDLER_DELETE 2 HANDLER_READ_KEY 3 HANDLER_READ_NEXT 1 -HANDLER_READ_RND_NEXT 104 +HANDLER_READ_RND_NEXT 108 HANDLER_ROLLBACK 1 -HANDLER_TMP_WRITE 122 +HANDLER_TMP_WRITE 127 HANDLER_UPDATE 2 HANDLER_WRITE 5 # + 1 commit @@ -650,9 +650,9 @@ HANDLER_DELETE 2 HANDLER_READ_FIRST 1 HANDLER_READ_KEY 3 HANDLER_READ_NEXT 4 -HANDLER_READ_RND_NEXT 130 +HANDLER_READ_RND_NEXT 135 HANDLER_ROLLBACK 1 -HANDLER_TMP_WRITE 147 +HANDLER_TMP_WRITE 153 HANDLER_UPDATE 2 HANDLER_WRITE 5 # + 1 commit @@ -670,9 +670,9 @@ HANDLER_DELETE 2 HANDLER_READ_FIRST 1 HANDLER_READ_KEY 3 HANDLER_READ_NEXT 4 -HANDLER_READ_RND_NEXT 156 +HANDLER_READ_RND_NEXT 162 HANDLER_ROLLBACK 1 -HANDLER_TMP_WRITE 172 +HANDLER_TMP_WRITE 179 HANDLER_UPDATE 2 HANDLER_WRITE 5 # + 9 locks @@ -696,7 +696,7 @@ VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 HANDLER_READ_FIRST 4 HANDLER_READ_NEXT 5 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 # 1 commit # 10 locks (1 ha_partition + 4 ha_innobase) x 2 (lock/unlock) # 4 read first (for reading the first row in 4 partitions) @@ -709,7 +709,7 @@ SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 # 10 locks (table + 4 partition) x (lock + unlock) SELECT * FROM t1 PARTITION (pNeg, `p10-99`); a b @@ -720,7 +720,7 @@ SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_ROLLBACK 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 HANDLER_WRITE 3 # 6 locks (1 ha_partition + 2 ha_innobase) x 2 (lock+unlock) # 1 rollback @@ -732,7 +732,7 @@ SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 HANDLER_WRITE 10 # 10 lock (1 ha_partition + 4 ha_innobase) x 2 (lock + unlock) ALTER TABLE t1 TRUNCATE PARTITION pNeg, `p10-99`; @@ -742,7 +742,7 @@ SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 # 9 locks # 18 read key (ALTER forces table to be closed, see above for open) LOAD DATA INFILE 'loadtest.txt' INTO TABLE t1 PARTITION (pNeg, `p10-99`); @@ -750,8 +750,8 @@ SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 2 -HANDLER_READ_RND_NEXT 26 -HANDLER_TMP_WRITE 47 +HANDLER_READ_RND_NEXT 27 +HANDLER_TMP_WRITE 49 HANDLER_WRITE 10 # + 23 write (18 internal I_S + 5 rows) UNLOCK TABLES; @@ -759,8 +759,8 @@ SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 2 -HANDLER_READ_RND_NEXT 52 -HANDLER_TMP_WRITE 72 +HANDLER_READ_RND_NEXT 54 +HANDLER_TMP_WRITE 75 HANDLER_WRITE 10 # + 9 locks # @@ -773,7 +773,7 @@ WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 HANDLER_READ_RND_NEXT 2 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 HANDLER_UPDATE 2 # 1 commit # 4 lock (1 ha_partition + 1 ha_innobase) x 2 (lock + unlock) @@ -791,7 +791,7 @@ WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 HANDLER_READ_KEY 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 HANDLER_UPDATE 2 # 1 commit # 4 lock @@ -806,7 +806,7 @@ VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 HANDLER_READ_KEY 1 HANDLER_READ_RND 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 HANDLER_UPDATE 2 # 1 commit # 4 lock @@ -819,7 +819,7 @@ SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 # Nothing, since impossible PARTITION+WHERE clause. FLUSH STATUS; UPDATE t1 PARTITION(subp0) SET a = -2, b = concat(b, ', Updated from a = 100') @@ -828,7 +828,7 @@ SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 # Nothing, since impossible PARTITION+WHERE clause. FLUSH STATUS; UPDATE t1 PARTITION(`p100-99999`) SET a = -2, b = concat(b, ', Updated from a = 100') @@ -840,7 +840,7 @@ VARIABLE_NAME VARIABLE_VALUE HANDLER_READ_KEY 1 HANDLER_READ_RND 1 HANDLER_ROLLBACK 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 HANDLER_UPDATE 1 # 6 lock # 4 read key (1 index init + 1 index read + 1 rnd init + 1 rnd pos) @@ -856,7 +856,7 @@ VARIABLE_NAME VARIABLE_VALUE HANDLER_READ_KEY 1 HANDLER_READ_RND 1 HANDLER_ROLLBACK 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 HANDLER_UPDATE 1 HANDLER_WRITE 1 # 10 locks @@ -874,7 +874,7 @@ HANDLER_COMMIT 1 HANDLER_DELETE 1 HANDLER_READ_KEY 1 HANDLER_READ_RND 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 HANDLER_UPDATE 1 HANDLER_WRITE 1 # 1 commit @@ -902,7 +902,7 @@ SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 # Nothing (no matching partition found) FLUSH STATUS; UPDATE t1 PARTITION (pNeg) SET b = concat(b, ', Updated2') WHERE a = 1000000; @@ -910,7 +910,7 @@ SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 # Nothing (no matching partition found) FLUSH STATUS; LOCK TABLE t1 WRITE; @@ -918,7 +918,7 @@ SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 # 9 locks UPDATE t1 PARTITION (subp7) SET b = concat(b, ', Updated to 103'), a = 103 WHERE a = 101; SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS @@ -927,8 +927,8 @@ VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 2 HANDLER_READ_KEY 1 HANDLER_READ_RND 1 -HANDLER_READ_RND_NEXT 26 -HANDLER_TMP_WRITE 47 +HANDLER_READ_RND_NEXT 27 +HANDLER_TMP_WRITE 49 HANDLER_UPDATE 2 # + 4 read key # + 1 read rnd @@ -941,8 +941,8 @@ HANDLER_COMMIT 3 HANDLER_DELETE 1 HANDLER_READ_KEY 2 HANDLER_READ_RND 2 -HANDLER_READ_RND_NEXT 52 -HANDLER_TMP_WRITE 72 +HANDLER_READ_RND_NEXT 54 +HANDLER_TMP_WRITE 75 HANDLER_UPDATE 3 HANDLER_WRITE 1 # + 1 delete @@ -957,8 +957,8 @@ HANDLER_COMMIT 3 HANDLER_DELETE 1 HANDLER_READ_KEY 2 HANDLER_READ_RND 2 -HANDLER_READ_RND_NEXT 78 -HANDLER_TMP_WRITE 97 +HANDLER_READ_RND_NEXT 81 +HANDLER_TMP_WRITE 101 HANDLER_UPDATE 3 HANDLER_WRITE 1 + 9 locks @@ -986,7 +986,7 @@ VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 HANDLER_DELETE 2 HANDLER_READ_KEY 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 # 1 delete # 4 locks (pruning works!). # 1 read key (index read) @@ -998,7 +998,7 @@ VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 HANDLER_DELETE 2 HANDLER_READ_RND_NEXT 3 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 # 1 delete # 4 locks # 1 read first @@ -1010,7 +1010,7 @@ SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 # 9 locks DELETE FROM t1 PARTITION (subp1) WHERE b = 'p0-9:subp3'; SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS @@ -1018,8 +1018,8 @@ WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 2 HANDLER_READ_KEY 1 -HANDLER_READ_RND_NEXT 26 -HANDLER_TMP_WRITE 47 +HANDLER_READ_RND_NEXT 27 +HANDLER_TMP_WRITE 49 # + 3 read key (1 innodb_get_index in records_in_range # + 1 innobase_get_index in index_init + 1 index read) DELETE FROM t1 PARTITION (`p0-9`) WHERE b = 'p0-9:subp3'; @@ -1030,8 +1030,8 @@ HANDLER_COMMIT 3 HANDLER_DELETE 2 HANDLER_READ_KEY 3 HANDLER_READ_NEXT 1 -HANDLER_READ_RND_NEXT 52 -HANDLER_TMP_WRITE 72 +HANDLER_READ_RND_NEXT 54 +HANDLER_TMP_WRITE 75 # + 1 delete # + 6 read key (same as above, but for two subpartitions) # + 1 read next (read next after found row) @@ -1043,8 +1043,8 @@ HANDLER_COMMIT 3 HANDLER_DELETE 2 HANDLER_READ_KEY 3 HANDLER_READ_NEXT 1 -HANDLER_READ_RND_NEXT 78 -HANDLER_TMP_WRITE 97 +HANDLER_READ_RND_NEXT 81 +HANDLER_TMP_WRITE 101 # + 9 locks # Test multi-table DELETE # Can be expressed in two different ways. @@ -1057,7 +1057,7 @@ VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 HANDLER_READ_FIRST 5 HANDLER_READ_NEXT 5 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 HANDLER_WRITE 10 # 24 locks (2 table, 5 + 5 subpartitions lock/unlock) FLUSH STATUS; @@ -1066,7 +1066,7 @@ SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 # 14 locks (1 table, 6 subpartitions lock/unlock) FLUSH STATUS; INSERT INTO t2 PARTITION (subp3) SELECT * FROM t1 PARTITION (subp3, `p10-99`, `p100-99999`); @@ -1076,7 +1076,7 @@ WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_READ_FIRST 5 HANDLER_ROLLBACK 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 HANDLER_WRITE 1 # 16 locks (2 tables, 1 + 5 subpartitions lock/unlock) FLUSH STATUS; @@ -1091,7 +1091,7 @@ VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 HANDLER_READ_FIRST 5 HANDLER_READ_NEXT 5 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 HANDLER_WRITE 7 # 16 locks (2 tables, 1 + 5 subpartitions lock/unlock) TRUNCATE TABLE t2; @@ -1103,7 +1103,7 @@ VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 HANDLER_READ_FIRST 5 HANDLER_READ_NEXT 5 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 HANDLER_WRITE 10 # 30 locks (2 table, 8 + 5 subpartitions lock/unlock) FLUSH STATUS; @@ -1114,7 +1114,7 @@ VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 HANDLER_READ_FIRST 5 HANDLER_READ_NEXT 7 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 HANDLER_WRITE 7 # 14 locks (2 table, 5 subpartitions lock/unlock) SHOW CREATE TABLE t1; @@ -1201,7 +1201,7 @@ WHERE t1.a = t3.a AND t3.b = 'subp3'' at line 1 SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 # Multi table delete without any matching rows FLUSH STATUS; DELETE t1, t2 FROM t1 PARTITION (pNeg), t3, t2 PARTITION (subp3) @@ -1211,7 +1211,7 @@ WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 HANDLER_READ_RND_NEXT 3 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 # 12 locks (3 in t1, 1 in t3, 2 in t2) x 2 (lock + unlock) # 1 read first (first rnd_next in t2) # 4 read key (1 innodb_get_index in rnd_init in t2 + index read in t2 @@ -1231,7 +1231,7 @@ HANDLER_READ_KEY 2 HANDLER_READ_NEXT 2 HANDLER_READ_RND 4 HANDLER_READ_RND_NEXT 16 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 # 4 delete (2 in t2 + 2 in t3) # 12 locks (3 in t2, 1 in t3, 2 in t1) x 2 (lock + unlock) # 3 read first (1 in t1 + 1 in t3 + 1 in t3, for second row in t1) @@ -1279,7 +1279,7 @@ SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 -HANDLER_TMP_WRITE 22 +HANDLER_TMP_WRITE 23 # 6 locks (lock/unlock two subpartitions + table) # Test on non partitioned table SELECT * FROM t3 PARTITION (pNeg); diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index e8a7eb133fb..ca6a5cf876a 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -3950,6 +3950,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -3964,6 +3965,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -3978,6 +3980,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 8 @@ -3992,6 +3995,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 268bd184c58..5adb8225b38 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -2114,6 +2114,37 @@ a b 1 1 drop table t2; # +# MDEV-10228: Delete missing rows with OR conditions +# (The example uses UPDATE, because UPDATE allows to use index hints +# and so it's possible to make an example that works with any storage +# engine) +# +CREATE TABLE t1 ( +key1varchar varchar(14) NOT NULL, +key2int int(11) NOT NULL DEFAULT '0', +col1 int, +PRIMARY KEY (key1varchar,key2int), +KEY key1varchar (key1varchar), +KEY key2int (key2int) +) DEFAULT CHARSET=utf8; +insert into t1 values +('value1',0, 0), +('value1',1, 0), +('value1',1000685, 0), +('value1',1003560, 0), +('value1',1004807, 0); +update t1 force index (PRIMARY) set col1=12345 +where (key1varchar='value1' AND (key2int <=1 OR key2int > 1)); +# The following must show col1=12345 for all rows: +select * from t1; +key1varchar key2int col1 +value1 0 12345 +value1 1 12345 +value1 1000685 12345 +value1 1003560 12345 +value1 1004807 12345 +drop table t1; +# # BUG#13731380: RANGE OPTIMIZER CALLS RECORDS_IN_RANGE() FOR OPEN RANGE # CREATE TABLE t1 (pk INT PRIMARY KEY); diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result index 5c6930f4323..4ef7c0b658c 100644 --- a/mysql-test/r/range_mrr_icp.result +++ b/mysql-test/r/range_mrr_icp.result @@ -2116,6 +2116,37 @@ a b 1 1 drop table t2; # +# MDEV-10228: Delete missing rows with OR conditions +# (The example uses UPDATE, because UPDATE allows to use index hints +# and so it's possible to make an example that works with any storage +# engine) +# +CREATE TABLE t1 ( +key1varchar varchar(14) NOT NULL, +key2int int(11) NOT NULL DEFAULT '0', +col1 int, +PRIMARY KEY (key1varchar,key2int), +KEY key1varchar (key1varchar), +KEY key2int (key2int) +) DEFAULT CHARSET=utf8; +insert into t1 values +('value1',0, 0), +('value1',1, 0), +('value1',1000685, 0), +('value1',1003560, 0), +('value1',1004807, 0); +update t1 force index (PRIMARY) set col1=12345 +where (key1varchar='value1' AND (key2int <=1 OR key2int > 1)); +# The following must show col1=12345 for all rows: +select * from t1; +key1varchar key2int col1 +value1 0 12345 +value1 1 12345 +value1 1000685 12345 +value1 1003560 12345 +value1 1004807 12345 +drop table t1; +# # BUG#13731380: RANGE OPTIMIZER CALLS RECORDS_IN_RANGE() FOR OPEN RANGE # CREATE TABLE t1 (pk INT PRIMARY KEY); diff --git a/mysql-test/r/range_vs_index_merge.result b/mysql-test/r/range_vs_index_merge.result index 1b12a9f5512..9af359a55f3 100644 --- a/mysql-test/r/range_vs_index_merge.result +++ b/mysql-test/r/range_vs_index_merge.result @@ -1310,6 +1310,7 @@ Handler_read_key 2 Handler_read_last 0 Handler_read_next 385 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 377 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -1338,6 +1339,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 59 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -1367,6 +1369,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 59 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 diff --git a/mysql-test/r/range_vs_index_merge_innodb.result b/mysql-test/r/range_vs_index_merge_innodb.result index 8428936d25f..601ae9b7613 100644 --- a/mysql-test/r/range_vs_index_merge_innodb.result +++ b/mysql-test/r/range_vs_index_merge_innodb.result @@ -1311,6 +1311,7 @@ Handler_read_key 2 Handler_read_last 0 Handler_read_next 385 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 377 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -1339,6 +1340,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 59 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -1368,6 +1370,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 59 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 9dbf6e0c46b..ca8b235ba31 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -4351,6 +4351,7 @@ Handler_read_key 2 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 6 diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result index 81e52929881..4659b0753ed 100644 --- a/mysql-test/r/select_jcl6.result +++ b/mysql-test/r/select_jcl6.result @@ -4362,6 +4362,7 @@ Handler_read_key 2 Handler_read_last 0 Handler_read_next 10 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 10 Handler_read_rnd_deleted 1 Handler_read_rnd_next 6 diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result index 9dbf6e0c46b..ca8b235ba31 100644 --- a/mysql-test/r/select_pkeycache.result +++ b/mysql-test/r/select_pkeycache.result @@ -4351,6 +4351,7 @@ Handler_read_key 2 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 6 diff --git a/mysql-test/r/single_delete_update.result b/mysql-test/r/single_delete_update.result index cdf97e20238..85e79f53c89 100644 --- a/mysql-test/r/single_delete_update.result +++ b/mysql-test/r/single_delete_update.result @@ -29,6 +29,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 4 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -48,6 +49,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 4 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -84,6 +86,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 17 @@ -103,6 +106,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 5 Handler_read_rnd_deleted 0 Handler_read_rnd_next 17 @@ -136,6 +140,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 17 @@ -155,6 +160,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 1 Handler_read_rnd_deleted 0 Handler_read_rnd_next 17 @@ -192,6 +198,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 4 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -211,6 +218,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 4 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -242,6 +250,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 17 @@ -261,6 +270,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 1 Handler_read_rnd_deleted 0 Handler_read_rnd_next 17 @@ -290,6 +300,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 1 Handler_read_rnd_deleted 0 Handler_read_rnd_next 17 @@ -309,6 +320,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 1 Handler_read_rnd_deleted 0 Handler_read_rnd_next 17 @@ -341,6 +353,7 @@ Handler_read_key 2 Handler_read_last 0 Handler_read_next 7 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 4 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -365,6 +378,7 @@ Handler_read_key 2 Handler_read_last 0 Handler_read_next 7 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 8 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -403,6 +417,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 4 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -422,6 +437,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 4 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -458,6 +474,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 17 @@ -477,6 +494,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 5 Handler_read_rnd_deleted 0 Handler_read_rnd_next 17 @@ -522,6 +540,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 4 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -547,6 +566,7 @@ Handler_read_key 0 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 4 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -566,6 +586,7 @@ Handler_read_key 0 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 4 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -603,6 +624,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 4 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -622,6 +644,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 4 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 5 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -663,6 +686,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 17 @@ -682,6 +706,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 5 Handler_read_rnd_deleted 0 Handler_read_rnd_next 17 @@ -720,6 +745,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 17 @@ -739,6 +765,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 1 Handler_read_rnd_deleted 0 Handler_read_rnd_next 17 @@ -776,6 +803,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 4 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -795,6 +823,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 4 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 5 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -826,6 +855,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 17 @@ -845,6 +875,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 1 Handler_read_rnd_deleted 0 Handler_read_rnd_next 17 @@ -875,6 +906,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 1 Handler_read_rnd_deleted 0 Handler_read_rnd_next 17 @@ -894,6 +926,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 1 Handler_read_rnd_deleted 0 Handler_read_rnd_next 17 @@ -927,6 +960,7 @@ Handler_read_key 2 Handler_read_last 0 Handler_read_next 7 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 4 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -951,6 +985,7 @@ Handler_read_key 2 Handler_read_last 0 Handler_read_next 7 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 8 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -993,6 +1028,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 4 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -1012,6 +1048,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 4 +Handler_read_retry 0 Handler_read_rnd 5 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -1053,6 +1090,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 17 @@ -1072,6 +1110,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 5 Handler_read_rnd_deleted 0 Handler_read_rnd_next 17 @@ -1111,6 +1150,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 4 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -1136,6 +1176,7 @@ Handler_read_key 0 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 4 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -1155,6 +1196,7 @@ Handler_read_key 0 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 4 +Handler_read_retry 0 Handler_read_rnd 5 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 diff --git a/mysql-test/r/sp-prelocking.result b/mysql-test/r/sp-prelocking.result index 9e82a966268..5b594e9ea55 100644 --- a/mysql-test/r/sp-prelocking.result +++ b/mysql-test/r/sp-prelocking.result @@ -320,3 +320,23 @@ c2 DROP TRIGGER t1_ai; DROP TABLE t1, t2; End of 5.0 tests +# +# Bug#21142859: FUNCTION UPDATING A VIEW FAILS TO FIND TABLE THAT ACTUALLY EXISTS +# +CREATE TABLE t1 SELECT 1 AS fld1, 'A' AS fld2; +CREATE TABLE t2 (fld3 INT, fld4 CHAR(1)); +CREATE VIEW v1 AS SELECT * FROM t1; +CREATE TRIGGER t1_au AFTER UPDATE ON t1 +FOR EACH ROW INSERT INTO t2 VALUES (new.fld1, new.fld2); +CREATE FUNCTION f1() RETURNS INT +BEGIN +UPDATE v1 SET fld2='B' WHERE fld1=1; +RETURN row_count(); +END ! +# Without the patch, an error was getting reported. +SELECT f1(); +f1() +1 +DROP FUNCTION f1; +DROP VIEW v1; +DROP TABLE t1,t2; diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 9453a1da3ca..c69ec67ae9b 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -7716,6 +7716,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -7727,6 +7728,7 @@ Handler_read_key 2 Handler_read_last 0 Handler_read_next 4097 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 diff --git a/mysql-test/r/status.result b/mysql-test/r/status.result index 55afd159053..7c82936d30a 100644 --- a/mysql-test/r/status.result +++ b/mysql-test/r/status.result @@ -288,6 +288,7 @@ Handler_read_key 4 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 7 Handler_read_rnd_deleted 0 Handler_read_rnd_next 23 @@ -305,7 +306,7 @@ Created_tmp_files 0 Created_tmp_tables 2 Handler_tmp_update 2 Handler_tmp_write 7 -Rows_tmp_read 42 +Rows_tmp_read 43 drop table t1; CREATE TABLE t1 (i int(11) DEFAULT NULL, KEY i (i) ) ENGINE=MyISAM; insert into t1 values (1),(2),(3),(4),(5); @@ -330,6 +331,7 @@ Handler_read_key 2 Handler_read_last 0 Handler_read_next 2 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 1 Handler_read_rnd_next 2 diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 428cf89c36c..042bf68cda0 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -6892,6 +6892,7 @@ Handler_read_key 8 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 22 @@ -6929,6 +6930,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 16 diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index 24d9f0de35a..0c03959a96a 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -124,6 +124,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 41 diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result index 19d3d25148f..415963af882 100644 --- a/mysql-test/r/subselect3_jcl6.result +++ b/mysql-test/r/subselect3_jcl6.result @@ -134,6 +134,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 41 diff --git a/mysql-test/r/subselect_cache.result b/mysql-test/r/subselect_cache.result index 8ca6bf52e0e..97a5f5a77f6 100644 --- a/mysql-test/r/subselect_cache.result +++ b/mysql-test/r/subselect_cache.result @@ -36,6 +36,7 @@ Handler_read_key 7 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 31 @@ -289,6 +290,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 61 @@ -316,6 +318,7 @@ Handler_read_key 7 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 31 @@ -342,6 +345,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 61 @@ -369,6 +373,7 @@ Handler_read_key 7 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 31 @@ -395,6 +400,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 61 @@ -494,6 +500,7 @@ Handler_read_key 7 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 442 @@ -592,6 +599,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 472 @@ -615,6 +623,7 @@ Handler_read_key 17 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 4 Handler_read_rnd_deleted 0 Handler_read_rnd_next 36 @@ -637,6 +646,7 @@ Handler_read_key 10 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 4 Handler_read_rnd_deleted 0 Handler_read_rnd_next 86 @@ -660,6 +670,7 @@ Handler_read_key 17 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 4 Handler_read_rnd_deleted 0 Handler_read_rnd_next 41 @@ -682,6 +693,7 @@ Handler_read_key 10 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 4 Handler_read_rnd_deleted 0 Handler_read_rnd_next 91 @@ -711,6 +723,7 @@ Handler_read_key 7 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 31 @@ -739,6 +752,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 61 @@ -762,6 +776,7 @@ Handler_read_key 7 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 4 Handler_read_rnd_deleted 1 Handler_read_rnd_next 36 @@ -784,6 +799,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 4 Handler_read_rnd_deleted 1 Handler_read_rnd_next 66 @@ -883,6 +899,7 @@ Handler_read_key 70 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 141 @@ -981,6 +998,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 671 @@ -1606,6 +1624,7 @@ Handler_read_key 11 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 145 @@ -1636,6 +1655,7 @@ Handler_read_key 32 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 84 @@ -1669,6 +1689,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 127 @@ -1699,6 +1720,7 @@ Handler_read_key 13 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 69 @@ -1890,6 +1912,7 @@ Handler_read_key 15 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 8 Handler_read_rnd_deleted 0 Handler_read_rnd_next 57 @@ -1916,6 +1939,7 @@ Handler_read_key 21 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 8 Handler_read_rnd_deleted 0 Handler_read_rnd_next 37 @@ -1943,6 +1967,7 @@ Handler_read_key 15 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 8 Handler_read_rnd_deleted 0 Handler_read_rnd_next 57 @@ -1969,6 +1994,7 @@ Handler_read_key 22 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 8 Handler_read_rnd_deleted 0 Handler_read_rnd_next 41 @@ -2011,6 +2037,7 @@ Handler_read_key 15 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 16 @@ -2049,6 +2076,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 151 @@ -2086,6 +2114,7 @@ Handler_read_key 15 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 43 @@ -2123,6 +2152,7 @@ Handler_read_key 18 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 27 diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index d0c5c2eda26..a34b7d28e2e 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -2237,6 +2237,7 @@ Handler_read_key 5000 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 6003 diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result index b24edd438b2..f5191487ddf 100644 --- a/mysql-test/r/subselect_no_exists_to_in.result +++ b/mysql-test/r/subselect_no_exists_to_in.result @@ -6892,6 +6892,7 @@ Handler_read_key 8 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 22 @@ -6929,6 +6930,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 16 diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 23b8ade7ef5..14bd38175ce 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -6886,6 +6886,7 @@ Handler_read_key 8 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 22 @@ -6923,6 +6924,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 16 diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 2907fd3f4b3..7964df42f88 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -6883,6 +6883,7 @@ Handler_read_key 8 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 22 @@ -6920,6 +6921,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 16 diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 08394bc6332..650e7e1e2a6 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -6898,6 +6898,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 58 @@ -6935,6 +6936,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 16 diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 9b1d4d24031..2350100314b 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -6883,6 +6883,7 @@ Handler_read_key 8 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 22 @@ -6920,6 +6921,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 16 diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index e846549f406..93fdcf1ccca 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -2277,6 +2277,7 @@ Handler_read_key 5000 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 6003 diff --git a/mysql-test/r/temp_table.result b/mysql-test/r/temp_table.result index ee0b3ab6d48..0ecdcf0058a 100644 --- a/mysql-test/r/temp_table.result +++ b/mysql-test/r/temp_table.result @@ -291,6 +291,9 @@ test.t1 repair status OK test.t2 repair status OK test.t3 repair status OK DROP TABLES t1, t2, t3; +CREATE TEMPORARY TABLE t1 (a int); +RENAME TABLE t1 TO t2; +DROP TABLE t2; create temporary temporary table t1 (a int); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'temporary table t1 (a int)' at line 1 flush status; diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result index a1eeda063e4..d2587d7199e 100644 --- a/mysql-test/r/type_date.result +++ b/mysql-test/r/type_date.result @@ -165,6 +165,7 @@ str_to_date( '', a ) NULL DROP TABLE t1; CREATE TABLE t1 (a DATE, b INT, PRIMARY KEY (a,b)); +SET timestamp=UNIX_TIMESTAMP('2016-07-21 14:48:18'); INSERT INTO t1 VALUES (DATE(NOW()), 1); SELECT COUNT(*) FROM t1 WHERE a = NOW(); COUNT(*) @@ -192,6 +193,7 @@ COUNT(*) EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW(); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +SET timestamp=DEFAULT; DROP TABLE t1; CREATE TABLE t1 (a DATE); CREATE TABLE t2 (a DATE); diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result index a0c35c6e0ca..49141e33709 100644 --- a/mysql-test/r/update.result +++ b/mysql-test/r/update.result @@ -276,6 +276,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -289,6 +290,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 2 Handler_read_rnd_deleted 0 Handler_read_rnd_next 9 @@ -301,6 +303,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -313,6 +316,7 @@ Handler_read_key 0 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -326,6 +330,7 @@ Handler_read_key 0 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 1 Handler_read_rnd_deleted 2 Handler_read_rnd_next 7 @@ -381,6 +386,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 1 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 1 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -420,6 +426,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -432,6 +439,7 @@ Handler_read_key 2 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -443,6 +451,7 @@ Handler_read_key 3 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -454,6 +463,7 @@ Handler_read_key 3 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 @@ -699,6 +709,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 1 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 2 Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 diff --git a/mysql-test/r/userstat.result b/mysql-test/r/userstat.result index 0c5baba20b9..f818ab9c0a7 100644 --- a/mysql-test/r/userstat.result +++ b/mysql-test/r/userstat.result @@ -119,6 +119,7 @@ Handler_read_key 9 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 5 diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index d4a2a9b1b79..f695419903e 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -4265,6 +4265,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 17 @@ -4288,6 +4289,7 @@ Handler_read_key 1 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 +Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 17 @@ -5551,6 +5553,21 @@ test.v1 check Error 'test.v1' is not BASE TABLE test.v1 check status Operation failed drop view v1; drop table t1; +# +# MDEV-10419: crash in mariadb 10.1.16-MariaDB-1~trusty +# +CREATE TABLE t1 (c1 CHAR(13)); +CREATE TABLE t2 (c2 CHAR(13)); +CREATE FUNCTION f() RETURNS INT RETURN 0; +CREATE OR REPLACE VIEW v1 AS select f() from t1 where c1 in (select c2 from t2); +DROP FUNCTION f; +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `f`() AS `f()` from `t1` where `test`.`t1`.`c1` in (select `test`.`t2`.`c2` from `t2`) latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +drop view v1; +drop table t1,t2; # ----------------------------------------------------------------- # -- End of 5.5 tests. # ----------------------------------------------------------------- diff --git a/mysql-test/r/xtradb_mrr.result b/mysql-test/r/xtradb_mrr.result index 15b750d2fd3..c238d0530af 100644 --- a/mysql-test/r/xtradb_mrr.result +++ b/mysql-test/r/xtradb_mrr.result @@ -311,10 +311,10 @@ concat('c-', 1000 + C.a, '-c'), 'filler' from t1 A, t1 B, t1 C; explain -select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a'; +select count(length(a) + length(filler)) from t2 force index (a) where a>='a-1000-a' and a <'a-1001-a'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range a a 9 NULL 99 Using index condition; Rowid-ordered scan -select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a'; +select count(length(a) + length(filler)) from t2 force index (a) where a>='a-1000-a' and a <'a-1001-a'; count(length(a) + length(filler)) 100 drop table t2; |