diff options
| author | Sergei Golubchik <serg@mariadb.org> | 2017-03-30 12:48:42 +0200 |
|---|---|---|
| committer | Sergei Golubchik <serg@mariadb.org> | 2017-03-30 12:48:42 +0200 |
| commit | da4d71d10d23c1ac2d10b72baee14991ccb7a146 (patch) | |
| tree | 7cdf3a8c8e72ca7c1c8105427c04123f025bd870 /mysql-test/r | |
| parent | 9ec85009985d644ce7ae797bc3572d0ad0f69bb0 (diff) | |
| parent | a00517ac9707ffd51c092f5af5d198c5ee789bb4 (diff) | |
| download | mariadb-git-da4d71d10d23c1ac2d10b72baee14991ccb7a146.tar.gz | |
Merge branch '10.1' into 10.2
Diffstat (limited to 'mysql-test/r')
49 files changed, 1479 insertions, 242 deletions
diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index 7cdd042e0a6..c88abd9867e 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -1925,8 +1925,8 @@ ALTER TABLE ti1 FORCE; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tm1 FORCE; -affected rows: 2 -info: Records: 2 Duplicates: 0 Warnings: 0 +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE ti1 AUTO_INCREMENT 3; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 @@ -2104,6 +2104,27 @@ Note 1061 Duplicate key name 'id1' DROP TABLE t2; DROP TABLE t1; # +# MDEV-6390 CONVERT TO CHARACTER SET utf8 doesn't change DEFAULT CHARSET. +# +CREATE TABLE t1 (id int(11) NOT NULL, a int(11) NOT NULL, b int(11)) +ENGINE=InnoDB DEFAULT CHARSET=latin1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) NOT NULL, + `a` int(11) NOT NULL, + `b` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +ALTER TABLE t1 CONVERT TO CHARACTER SET utf8; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) NOT NULL, + `a` int(11) NOT NULL, + `b` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8 +DROP TABLE t1; +# # Start of 10.1 tests # # diff --git a/mysql-test/r/contributors.result b/mysql-test/r/contributors.result index f3f5e227d3a..4a26d0f19dd 100644 --- a/mysql-test/r/contributors.result +++ b/mysql-test/r/contributors.result @@ -1,15 +1,17 @@ SHOW CONTRIBUTORS; Name Location Comment -Booking.com http://www.booking.com Founding member, Platinum Sponsor of the MariaDB Foundation +Booking.com https://www.booking.com Founding member, Platinum Sponsor of the MariaDB Foundation +Alibaba Cloud https://intl.aliyun.com 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 +Visma https://visma.com Gold Sponsor of the MariaDB Foundation +DBS https://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 -Tencent Game DBA http://tencentdba.com/about/ Bronze Sponsor of the MariaDB Foundation +Verkkokauppa.com https://www.verkkokauppa.com Bronze Sponsor of the MariaDB Foundation +Virtuozzo https://virtuozzo.com Bronze Sponsor of the MariaDB Foundation +Tencent Game DBA http://tencentdba.com/about Bronze Sponsor of the MariaDB Foundation +Tencent TDSQL http://tdsql.org 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/ctype_upgrade.result b/mysql-test/r/ctype_upgrade.result index b317be42d5c..53cb858035b 100644 --- a/mysql-test/r/ctype_upgrade.result +++ b/mysql-test/r/ctype_upgrade.result @@ -227,7 +227,7 @@ DROP TABLE mysql050614_xxx_croatian_ci; # Checking mysql_upgrade # # Running mysql_upgrade -Phase 1/6: Checking and upgrading mysql database +Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -258,10 +258,11 @@ mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK -Phase 2/6: Fixing views -Phase 3/6: Running 'mysql_fix_privilege_tables' -Phase 4/6: Fixing table and database names -Phase 5/6: Checking and upgrading tables +Phase 2/7: Installing used storage engines... Skipped +Phase 3/7: Fixing views +Phase 4/7: Running 'mysql_fix_privilege_tables' +Phase 5/7: Fixing table and database names +Phase 6/7: Checking and upgrading tables Processing databases information_schema mtr @@ -280,11 +281,11 @@ test.maria050313_ucs2_croatian_ci_def OK test.maria050313_utf8_croatian_ci OK test.maria050533_xxx_croatian_ci OK test.maria100004_xxx_croatian_ci OK -Phase 6/6: Running 'FLUSH PRIVILEGES' +Phase 7/7: Running 'FLUSH PRIVILEGES' OK # Running mysql_upgrade for the second time # This should report OK for all tables -Phase 1/6: Checking and upgrading mysql database +Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -315,10 +316,11 @@ mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK -Phase 2/6: Fixing views -Phase 3/6: Running 'mysql_fix_privilege_tables' -Phase 4/6: Fixing table and database names -Phase 5/6: Checking and upgrading tables +Phase 2/7: Installing used storage engines... Skipped +Phase 3/7: Fixing views +Phase 4/7: Running 'mysql_fix_privilege_tables' +Phase 5/7: Fixing table and database names +Phase 6/7: Checking and upgrading tables Processing databases information_schema mtr @@ -331,7 +333,7 @@ test.maria050313_utf8_croatian_ci OK test.maria050533_xxx_croatian_ci OK test.maria100004_xxx_croatian_ci OK test.mysql050614_xxx_croatian_ci OK -Phase 6/6: Running 'FLUSH PRIVILEGES' +Phase 7/7: Running 'FLUSH PRIVILEGES' OK SHOW CREATE TABLE maria050313_ucs2_croatian_ci_def; Table Create Table diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index d0ac61493c2..8a7422ba5ec 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -1018,6 +1018,29 @@ David Yes 210 Edward Yes 150 DROP TABLE example1463; set sql_mode= @save_sql_mode; +# +# MDEV-9028: SELECT DISTINCT constant column of derived table +# used as the second operand of LEFT JOIN +# +create table t1 (id int, data varchar(255)); +insert into t1 values (1,'yes'),(2,'yes'); +select distinct t1.id, tt.id, tt.data +from t1 +left join +(select t1.id, 'yes' as data from t1) as tt +on t1.id = tt.id; +id id data +1 1 yes +2 2 yes +select distinct t1.id, tt.id, tt.data +from t1 +left join +(select t1.id, 'yes' as data from t1 where id > 1) as tt +on t1.id = tt.id; +id id data +2 2 yes +1 NULL NULL +drop table t1; # end of 5.5 # # Start of 10.1 tests diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result index fd58ee038c7..021633200e1 100644 --- a/mysql-test/r/derived_cond_pushdown.result +++ b/mysql-test/r/derived_cond_pushdown.result @@ -8317,7 +8317,7 @@ EXPLAIN "access_type": "ALL", "rows": 2, "filtered": 100, - "attached_condition": "<in_optimizer>(1,<exists>(subquery#2)) or v1.c = 'foo'", + "attached_condition": "<cache>(<in_optimizer>(1,<exists>(subquery#2))) or v1.c = 'foo'", "materialized": { "query_block": { "select_id": 3, diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index 316d7bdc4bc..7a451a312ca 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -2305,6 +2305,11 @@ GROUP BY TABLE_SCHEMA) AS UNIQUES ON ( COLUMNS.TABLE_SCHEMA = UNIQUES.TABLE_SCHEMA); COUNT(*) > 0 1 +Warnings: +Warning 1286 Unknown storage engine 'InnoDB' +Warning 1286 Unknown storage engine 'InnoDB' +Warning 1286 Unknown storage engine 'InnoDB' +Warning 1286 Unknown storage engine 'InnoDB' DROP TABLE t1; SET SESSION optimizer_switch= @save_optimizer_switch; # diff --git a/mysql-test/r/drop_bad_db_type.result b/mysql-test/r/drop_bad_db_type.result index 6a125cdccf5..de22373e0fd 100644 --- a/mysql-test/r/drop_bad_db_type.result +++ b/mysql-test/r/drop_bad_db_type.result @@ -4,6 +4,27 @@ create table t1 (a int) engine=archive; insert t1 values (1),(2),(3); flush tables; uninstall soname 'ha_archive'; +select table_schema, table_name from information_schema.tables where table_name like 't1'; +table_schema test +table_name t1 +select table_schema, table_name, engine, version from information_schema.tables where table_name like 't1'; +table_schema test +table_name t1 +engine ARCHIVE +version NULL +Warnings: +Level Warning +Code 1286 +Message Unknown storage engine 'ARCHIVE' +select table_schema, table_name, engine, row_format from information_schema.tables where table_name like 't1'; +table_schema test +table_name t1 +engine ARCHIVE +row_format NULL +Warnings: +Level Warning +Code 1286 +Message Unknown storage engine 'ARCHIVE' install soname 'ha_archive'; t1.ARZ t1.frm diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index 005b4239e9c..b9ee2a9136f 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -1730,7 +1730,7 @@ SPATIAL_REF_SYS CREATE TEMPORARY TABLE `SPATIAL_REF_SYS` ( ) ENGINE=MEMORY DEFAULT CHARSET=utf8 create table t1(g GEOMETRY, pt POINT); create table t2(g LINESTRING, pl POLYGON); -select * from information_schema.geometry_columns; +select * from information_schema.geometry_columns where f_table_schema='test'; F_TABLE_CATALOG F_TABLE_SCHEMA F_TABLE_NAME F_GEOMETRY_COLUMN G_TABLE_CATALOG G_TABLE_SCHEMA G_TABLE_NAME G_GEOMETRY_COLUMN STORAGE_TYPE GEOMETRY_TYPE COORD_DIMENSION MAX_PPR SRID def test t1 def test t1 g 1 0 2 0 0 def test t1 def test t1 pt 1 1 2 0 0 @@ -1739,7 +1739,7 @@ def test t2 def test t2 pl 1 3 2 0 0 drop table t1, t2; 10.1 tests create table t1(g GEOMETRY(9,4) REF_SYSTEM_ID=101, pt POINT(8,2), pg GEOMETRY REF_SYSTEM_ID=102); -SELECT SRID from information_schema.geometry_columns WHERE G_TABLE_NAME='t1'; +SELECT SRID from information_schema.geometry_columns WHERE f_table_schema='test' and G_TABLE_NAME='t1'; SRID 101 0 diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result index 2d6d9f3b632..b250137ebf8 100644 --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@ -2616,6 +2616,61 @@ DROP USER mysqltest_u1@localhost; # End of Bug#38347. # +# BUG#11759114 - '51401: GRANT TREATS NONEXISTENT FUNCTIONS/PRIVILEGES +# DIFFERENTLY'. +# +drop database if exists mysqltest_db1; +create database mysqltest_db1; +create user mysqltest_u1; +# Both GRANT statements below should fail with the same error. +grant execute on function mysqltest_db1.f1 to mysqltest_u1; +ERROR 42000: FUNCTION or PROCEDURE f1 does not exist +grant execute on procedure mysqltest_db1.p1 to mysqltest_u1; +ERROR 42000: FUNCTION or PROCEDURE p1 does not exist +# Let us show that GRANT behaviour for routines is consistent +# with GRANT behaviour for tables. Attempt to grant privilege +# on non-existent table also results in an error. +grant select on mysqltest_db1.t1 to mysqltest_u1; +ERROR 42S02: Table 'mysqltest_db1.t1' doesn't exist +show grants for mysqltest_u1; +Grants for mysqltest_u1@% +GRANT USAGE ON *.* TO 'mysqltest_u1'@'%' +drop database mysqltest_db1; +drop user mysqltest_u1; +# +# Bug#12766319 - 61865: RENAME USER DOES NOT WORK CORRECTLY - +# REQUIRES FLUSH PRIVILEGES +# +CREATE USER foo@'127.0.0.1'; +GRANT ALL ON *.* TO foo@'127.0.0.1'; +# First attempt, should connect successfully +connect conn1, '127.0.0.1', foo,,test; +SELECT user(), current_user(); +user() current_user() +foo@localhost foo@127.0.0.1 +# Rename the user +RENAME USER foo@'127.0.0.1' to foo@'127.0.0.0/255.0.0.0'; +# Second attempt, should connect successfully as its valid mask +# This was failing without fix +connect conn2, '127.0.0.1', foo,,test; +SELECT user(), current_user(); +user() current_user() +foo@localhost foo@127.0.0.0/255.0.0.0 +# Rename the user back to original +RENAME USER foo@'127.0.0.0/255.0.0.0' to foo@'127.0.0.1'; +# Third attempt, should connect successfully +connect conn3, '127.0.0.1', foo,,test; +SELECT user(), current_user(); +user() current_user() +foo@localhost foo@127.0.0.1 +# Clean-up +connection default; +disconnect conn1; +disconnect conn2; +disconnect conn3; +DROP USER foo@'127.0.0.1'; +# End of Bug#12766319 +# # Bug#11756966 - 48958: STORED PROCEDURES CAN BE LEVERAGED TO BYPASS # DATABASE SECURITY # @@ -2642,28 +2697,6 @@ connection default; disconnect con1; DROP USER untrusted@localhost; DROP DATABASE secret; -# -# BUG#11759114 - '51401: GRANT TREATS NONEXISTENT FUNCTIONS/PRIVILEGES -# DIFFERENTLY'. -# -drop database if exists mysqltest_db1; -create database mysqltest_db1; -create user mysqltest_u1; -# Both GRANT statements below should fail with the same error. -grant execute on function mysqltest_db1.f1 to mysqltest_u1; -ERROR 42000: FUNCTION or PROCEDURE f1 does not exist -grant execute on procedure mysqltest_db1.p1 to mysqltest_u1; -ERROR 42000: FUNCTION or PROCEDURE p1 does not exist -# Let us show that GRANT behaviour for routines is consistent -# with GRANT behaviour for tables. Attempt to grant privilege -# on non-existent table also results in an error. -grant select on mysqltest_db1.t1 to mysqltest_u1; -ERROR 42S02: Table 'mysqltest_db1.t1' doesn't exist -show grants for mysqltest_u1; -Grants for mysqltest_u1@% -GRANT USAGE ON *.* TO 'mysqltest_u1'@'%' -drop database mysqltest_db1; -drop user mysqltest_u1; set GLOBAL sql_mode=default; # # Start of 10.2 tests diff --git a/mysql-test/r/handlersocket.result b/mysql-test/r/handlersocket.result index e38de6bf5c2..26c77813b26 100644 --- a/mysql-test/r/handlersocket.result +++ b/mysql-test/r/handlersocket.result @@ -5,7 +5,7 @@ plugin_version 1.0 plugin_status ACTIVE plugin_type DAEMON plugin_library handlersocket.so -plugin_library_version 1.11 +plugin_library_version 1.12 plugin_author higuchi dot akira at dena dot jp plugin_description Direct access into InnoDB plugin_license BSD diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index 17e48ae329c..aea2d604b03 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -759,6 +759,9 @@ from information_schema.statistics join information_schema.columns using(table_n TABLE_NAME COLUMN_NAME TABLE_CATALOG TABLE_SCHEMA NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLLATION SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT TABLE_CATALOG TABLE_SCHEMA COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA COLUMN_COMMENT user Host def mysql 0 mysql PRIMARY 1 A NULL NULL BTREE def mysql NO char 60 180 NULL NULL utf8 utf8_bin char(60) PRI user User def mysql 0 mysql PRIMARY 2 A NULL NULL BTREE def mysql NO char 80 240 NULL NULL utf8 utf8_bin char(80) PRI +Warnings: +Warning 1286 Unknown storage engine 'InnoDB' +Warning 1286 Unknown storage engine 'InnoDB' drop table t1; drop table t2; drop table t3; diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result index 28f8a1e5990..917a31e2a79 100644 --- a/mysql-test/r/join_nested.result +++ b/mysql-test/r/join_nested.result @@ -1870,4 +1870,99 @@ f4 NULL NULL DROP TABLE t1,t2,t3,t4,t5; +# +# MDEV-7992: Nested left joins + 'not exists' optimization +# +CREATE TABLE t1( +K1 INT PRIMARY KEY, +Name VARCHAR(15) +); +INSERT INTO t1 VALUES +(1,'T1Row1'), (2,'T1Row2'); +CREATE TABLE t2( +K2 INT PRIMARY KEY, +K1r INT, +rowTimestamp DATETIME, +Event VARCHAR(15) +); +INSERT INTO t2 VALUES +(1, 1, '2015-04-13 10:42:11' ,'T1Row1Event1'), +(2, 1, '2015-04-13 10:42:12' ,'T1Row1Event2'), +(3, 1, '2015-04-13 10:42:12' ,'T1Row1Event3'); +SELECT t1a.*, t2a.*, +t2i.K2 AS K2B, t2i.K1r AS K1rB, +t2i.rowTimestamp AS rowTimestampB, t2i.Event AS EventB +FROM +t1 t1a JOIN t2 t2a ON t2a.K1r = t1a.K1 +LEFT JOIN +( t1 t1i LEFT JOIN t2 t2i ON t2i.K1r = t1i.K1) +ON (t1i.K1 = 1) AND +(((t2i.K1r = t1a.K1 AND t2i.rowTimestamp > t2a.rowTimestamp ) OR +(t2i.rowTimestamp = t2a.rowTimestamp AND t2i.K2 > t2a.K2)) +OR (t2i.K2 IS NULL)) +WHERE +t2a.K1r = 1 AND t2i.K2 IS NULL; +K1 Name K2 K1r rowTimestamp Event K2B K1rB rowTimestampB EventB +1 T1Row1 3 1 2015-04-13 10:42:12 T1Row1Event3 NULL NULL NULL NULL +EXPLAIN EXTENDED SELECT t1a.*, t2a.*, +t2i.K2 AS K2B, t2i.K1r AS K1rB, +t2i.rowTimestamp AS rowTimestampB, t2i.Event AS EventB +FROM +t1 t1a JOIN t2 t2a ON t2a.K1r = t1a.K1 +LEFT JOIN +( t1 t1i LEFT JOIN t2 t2i ON t2i.K1r = t1i.K1) +ON (t1i.K1 = 1) AND +(((t2i.K1r = t1a.K1 AND t2i.rowTimestamp > t2a.rowTimestamp ) OR +(t2i.rowTimestamp = t2a.rowTimestamp AND t2i.K2 > t2a.K2)) +OR (t2i.K2 IS NULL)) +WHERE +t2a.K1r = 1 AND t2i.K2 IS NULL; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1a const PRIMARY PRIMARY 4 const 1 100.00 +1 SIMPLE t2a ALL NULL NULL NULL NULL 3 100.00 Using where +1 SIMPLE t1i const PRIMARY PRIMARY 4 const 1 100.00 Using index +1 SIMPLE t2i ALL NULL NULL NULL NULL 3 100.00 Using where; Not exists +Warnings: +Note 1003 select 1 AS `K1`,'T1Row1' AS `Name`,`test`.`t2a`.`K2` AS `K2`,`test`.`t2a`.`K1r` AS `K1r`,`test`.`t2a`.`rowTimestamp` AS `rowTimestamp`,`test`.`t2a`.`Event` AS `Event`,`test`.`t2i`.`K2` AS `K2B`,`test`.`t2i`.`K1r` AS `K1rB`,`test`.`t2i`.`rowTimestamp` AS `rowTimestampB`,`test`.`t2i`.`Event` AS `EventB` from `test`.`t1` `t1a` join `test`.`t2` `t2a` left join (`test`.`t1` `t1i` left join `test`.`t2` `t2i` on(`test`.`t2i`.`K1r` = 1)) on(`test`.`t1i`.`K1` = 1 and (`test`.`t2i`.`K1r` = 1 and `test`.`t2i`.`rowTimestamp` > `test`.`t2a`.`rowTimestamp` or `test`.`t2i`.`rowTimestamp` = `test`.`t2a`.`rowTimestamp` and `test`.`t2i`.`K2` > `test`.`t2a`.`K2` or `test`.`t2i`.`K2` is null)) where `test`.`t2a`.`K1r` = 1 and `test`.`t2i`.`K2` is null +CREATE VIEW v1 AS +SELECT t2i.* +FROM t1 as t1i LEFT JOIN t2 as t2i ON t2i.K1r = t1i.K1 +WHERE t1i.K1 = 1 ; +SELECT +t1a.*, t2a.*, t2b.K2 as K2B, t2b.K1r as K1rB, +t2b.rowTimestamp as rowTimestampB, t2b.Event as EventB +FROM +t1 as t1a JOIN t2 as t2a ON t2a.K1r = t1a.K1 +LEFT JOIN +v1 as t2b +ON ((t2b.K1r = t1a.K1 AND t2b.rowTimestamp > t2a.rowTimestamp) OR +(t2b.rowTimestamp = t2a.rowTimestamp AND t2b.K2 > t2a.K2)) +OR (t2b.K2 IS NULL) +WHERE +t1a.K1 = 1 AND +t2b.K2 IS NULL; +K1 Name K2 K1r rowTimestamp Event K2B K1rB rowTimestampB EventB +1 T1Row1 3 1 2015-04-13 10:42:12 T1Row1Event3 NULL NULL NULL NULL +EXPLAIN EXTENDED SELECT +t1a.*, t2a.*, t2b.K2 as K2B, t2b.K1r as K1rB, +t2b.rowTimestamp as rowTimestampB, t2b.Event as EventB +FROM +t1 as t1a JOIN t2 as t2a ON t2a.K1r = t1a.K1 +LEFT JOIN +v1 as t2b +ON ((t2b.K1r = t1a.K1 AND t2b.rowTimestamp > t2a.rowTimestamp) OR +(t2b.rowTimestamp = t2a.rowTimestamp AND t2b.K2 > t2a.K2)) +OR (t2b.K2 IS NULL) +WHERE +t1a.K1 = 1 AND +t2b.K2 IS NULL; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1a const PRIMARY PRIMARY 4 const 1 100.00 +1 SIMPLE t2a ALL NULL NULL NULL NULL 3 100.00 Using where +1 SIMPLE t1i const PRIMARY PRIMARY 4 const 1 100.00 Using index +1 SIMPLE t2i ALL NULL NULL NULL NULL 3 100.00 Using where; Not exists +Warnings: +Note 1003 select 1 AS `K1`,'T1Row1' AS `Name`,`t2a`.`K2` AS `K2`,`t2a`.`K1r` AS `K1r`,`t2a`.`rowTimestamp` AS `rowTimestamp`,`t2a`.`Event` AS `Event`,`test`.`t2i`.`K2` AS `K2B`,`test`.`t2i`.`K1r` AS `K1rB`,`test`.`t2i`.`rowTimestamp` AS `rowTimestampB`,`test`.`t2i`.`Event` AS `EventB` from `test`.`t1` `t1a` join `test`.`t2` `t2a` left join (`test`.`t1` `t1i` left join `test`.`t2` `t2i` on(`test`.`t2i`.`K1r` = 1)) on(`test`.`t1i`.`K1` = 1 and (`test`.`t2i`.`K1r` = 1 and `test`.`t2i`.`rowTimestamp` > `t2a`.`rowTimestamp` or `test`.`t2i`.`rowTimestamp` = `t2a`.`rowTimestamp` and `test`.`t2i`.`K2` > `t2a`.`K2` or `test`.`t2i`.`K2` is null)) where `t2a`.`K1r` = 1 and `test`.`t2i`.`K2` is null +DROP VIEW v1; +DROP TABLE t1,t2; set optimizer_search_depth= @tmp_mdev621; diff --git a/mysql-test/r/join_nested_jcl6.result b/mysql-test/r/join_nested_jcl6.result index d1f402054cf..1ffd94547cc 100644 --- a/mysql-test/r/join_nested_jcl6.result +++ b/mysql-test/r/join_nested_jcl6.result @@ -1881,6 +1881,101 @@ f4 NULL NULL DROP TABLE t1,t2,t3,t4,t5; +# +# MDEV-7992: Nested left joins + 'not exists' optimization +# +CREATE TABLE t1( +K1 INT PRIMARY KEY, +Name VARCHAR(15) +); +INSERT INTO t1 VALUES +(1,'T1Row1'), (2,'T1Row2'); +CREATE TABLE t2( +K2 INT PRIMARY KEY, +K1r INT, +rowTimestamp DATETIME, +Event VARCHAR(15) +); +INSERT INTO t2 VALUES +(1, 1, '2015-04-13 10:42:11' ,'T1Row1Event1'), +(2, 1, '2015-04-13 10:42:12' ,'T1Row1Event2'), +(3, 1, '2015-04-13 10:42:12' ,'T1Row1Event3'); +SELECT t1a.*, t2a.*, +t2i.K2 AS K2B, t2i.K1r AS K1rB, +t2i.rowTimestamp AS rowTimestampB, t2i.Event AS EventB +FROM +t1 t1a JOIN t2 t2a ON t2a.K1r = t1a.K1 +LEFT JOIN +( t1 t1i LEFT JOIN t2 t2i ON t2i.K1r = t1i.K1) +ON (t1i.K1 = 1) AND +(((t2i.K1r = t1a.K1 AND t2i.rowTimestamp > t2a.rowTimestamp ) OR +(t2i.rowTimestamp = t2a.rowTimestamp AND t2i.K2 > t2a.K2)) +OR (t2i.K2 IS NULL)) +WHERE +t2a.K1r = 1 AND t2i.K2 IS NULL; +K1 Name K2 K1r rowTimestamp Event K2B K1rB rowTimestampB EventB +1 T1Row1 3 1 2015-04-13 10:42:12 T1Row1Event3 NULL NULL NULL NULL +EXPLAIN EXTENDED SELECT t1a.*, t2a.*, +t2i.K2 AS K2B, t2i.K1r AS K1rB, +t2i.rowTimestamp AS rowTimestampB, t2i.Event AS EventB +FROM +t1 t1a JOIN t2 t2a ON t2a.K1r = t1a.K1 +LEFT JOIN +( t1 t1i LEFT JOIN t2 t2i ON t2i.K1r = t1i.K1) +ON (t1i.K1 = 1) AND +(((t2i.K1r = t1a.K1 AND t2i.rowTimestamp > t2a.rowTimestamp ) OR +(t2i.rowTimestamp = t2a.rowTimestamp AND t2i.K2 > t2a.K2)) +OR (t2i.K2 IS NULL)) +WHERE +t2a.K1r = 1 AND t2i.K2 IS NULL; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1a const PRIMARY PRIMARY 4 const 1 100.00 +1 SIMPLE t2a ALL NULL NULL NULL NULL 3 100.00 Using where +1 SIMPLE t1i const PRIMARY PRIMARY 4 const 1 100.00 Using index +1 SIMPLE t2i ALL NULL NULL NULL NULL 3 100.00 Using where; Not exists +Warnings: +Note 1003 select 1 AS `K1`,'T1Row1' AS `Name`,`test`.`t2a`.`K2` AS `K2`,`test`.`t2a`.`K1r` AS `K1r`,`test`.`t2a`.`rowTimestamp` AS `rowTimestamp`,`test`.`t2a`.`Event` AS `Event`,`test`.`t2i`.`K2` AS `K2B`,`test`.`t2i`.`K1r` AS `K1rB`,`test`.`t2i`.`rowTimestamp` AS `rowTimestampB`,`test`.`t2i`.`Event` AS `EventB` from `test`.`t1` `t1a` join `test`.`t2` `t2a` left join (`test`.`t1` `t1i` left join `test`.`t2` `t2i` on(`test`.`t2i`.`K1r` = 1)) on(`test`.`t1i`.`K1` = 1 and (`test`.`t2i`.`K1r` = 1 and `test`.`t2i`.`rowTimestamp` > `test`.`t2a`.`rowTimestamp` or `test`.`t2i`.`rowTimestamp` = `test`.`t2a`.`rowTimestamp` and `test`.`t2i`.`K2` > `test`.`t2a`.`K2` or `test`.`t2i`.`K2` is null)) where `test`.`t2a`.`K1r` = 1 and `test`.`t2i`.`K2` is null +CREATE VIEW v1 AS +SELECT t2i.* +FROM t1 as t1i LEFT JOIN t2 as t2i ON t2i.K1r = t1i.K1 +WHERE t1i.K1 = 1 ; +SELECT +t1a.*, t2a.*, t2b.K2 as K2B, t2b.K1r as K1rB, +t2b.rowTimestamp as rowTimestampB, t2b.Event as EventB +FROM +t1 as t1a JOIN t2 as t2a ON t2a.K1r = t1a.K1 +LEFT JOIN +v1 as t2b +ON ((t2b.K1r = t1a.K1 AND t2b.rowTimestamp > t2a.rowTimestamp) OR +(t2b.rowTimestamp = t2a.rowTimestamp AND t2b.K2 > t2a.K2)) +OR (t2b.K2 IS NULL) +WHERE +t1a.K1 = 1 AND +t2b.K2 IS NULL; +K1 Name K2 K1r rowTimestamp Event K2B K1rB rowTimestampB EventB +1 T1Row1 3 1 2015-04-13 10:42:12 T1Row1Event3 NULL NULL NULL NULL +EXPLAIN EXTENDED SELECT +t1a.*, t2a.*, t2b.K2 as K2B, t2b.K1r as K1rB, +t2b.rowTimestamp as rowTimestampB, t2b.Event as EventB +FROM +t1 as t1a JOIN t2 as t2a ON t2a.K1r = t1a.K1 +LEFT JOIN +v1 as t2b +ON ((t2b.K1r = t1a.K1 AND t2b.rowTimestamp > t2a.rowTimestamp) OR +(t2b.rowTimestamp = t2a.rowTimestamp AND t2b.K2 > t2a.K2)) +OR (t2b.K2 IS NULL) +WHERE +t1a.K1 = 1 AND +t2b.K2 IS NULL; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1a const PRIMARY PRIMARY 4 const 1 100.00 +1 SIMPLE t2a ALL NULL NULL NULL NULL 3 100.00 Using where +1 SIMPLE t1i const PRIMARY PRIMARY 4 const 1 100.00 Using index +1 SIMPLE t2i ALL NULL NULL NULL NULL 3 100.00 Using where; Not exists +Warnings: +Note 1003 select 1 AS `K1`,'T1Row1' AS `Name`,`t2a`.`K2` AS `K2`,`t2a`.`K1r` AS `K1r`,`t2a`.`rowTimestamp` AS `rowTimestamp`,`t2a`.`Event` AS `Event`,`test`.`t2i`.`K2` AS `K2B`,`test`.`t2i`.`K1r` AS `K1rB`,`test`.`t2i`.`rowTimestamp` AS `rowTimestampB`,`test`.`t2i`.`Event` AS `EventB` from `test`.`t1` `t1a` join `test`.`t2` `t2a` left join (`test`.`t1` `t1i` left join `test`.`t2` `t2i` on(`test`.`t2i`.`K1r` = 1)) on(`test`.`t1i`.`K1` = 1 and (`test`.`t2i`.`K1r` = 1 and `test`.`t2i`.`rowTimestamp` > `t2a`.`rowTimestamp` or `test`.`t2i`.`rowTimestamp` = `t2a`.`rowTimestamp` and `test`.`t2i`.`K2` > `t2a`.`K2` or `test`.`t2i`.`K2` is null)) where `t2a`.`K1r` = 1 and `test`.`t2i`.`K2` is null +DROP VIEW v1; +DROP TABLE t1,t2; set optimizer_search_depth= @tmp_mdev621; CREATE TABLE t5 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b)); CREATE TABLE t6 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b)); diff --git a/mysql-test/r/log_tables.result b/mysql-test/r/log_tables.result index 2ec12bfe1c4..126230a8765 100644 --- a/mysql-test/r/log_tables.result +++ b/mysql-test/r/log_tables.result @@ -436,9 +436,9 @@ My own slow query sleep(2) My own slow query 0 SELECT * FROM mysql.slow_log WHERE seq >= 2 LIMIT 3; start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text thread_id rows_affected seq -START_TIME USER_HOST QUERY_TIME 00:00:00.000000 1 0 test 0 0 1 SELECT "My own slow query", sleep(2) 3 0 2 -START_TIME USER_HOST QUERY_TIME 00:00:00.000000 1 0 test 0 0 1 SELECT "My own slow query", sleep(2) 3 0 3 -START_TIME USER_HOST QUERY_TIME 00:00:00.000000 1 0 test 0 0 1 SELECT "My own slow query", sleep(2) 3 0 4 +START_TIME USER_HOST QUERY_TIME 00:00:00.000000 1 0 test 0 0 1 SELECT "My own slow query", sleep(2) 4 0 2 +START_TIME USER_HOST QUERY_TIME 00:00:00.000000 1 0 test 0 0 1 SELECT "My own slow query", sleep(2) 4 0 3 +START_TIME USER_HOST QUERY_TIME 00:00:00.000000 1 0 test 0 0 1 SELECT "My own slow query", sleep(2) 4 0 4 SET GLOBAL slow_query_log = 0; SET SESSION long_query_time =@saved_long_query_time; FLUSH LOGS; diff --git a/mysql-test/r/log_tables_upgrade.result b/mysql-test/r/log_tables_upgrade.result index 6cbb25bd1d4..a56d067c2cd 100644 --- a/mysql-test/r/log_tables_upgrade.result +++ b/mysql-test/r/log_tables_upgrade.result @@ -11,7 +11,7 @@ Table Op Msg_type Msg_text test.bug49823 repair status OK RENAME TABLE general_log TO renamed_general_log; RENAME TABLE test.bug49823 TO general_log; -Phase 1/6: Checking and upgrading mysql database +Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -43,10 +43,11 @@ mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK -Phase 2/6: Fixing views -Phase 3/6: Running 'mysql_fix_privilege_tables' -Phase 4/6: Fixing table and database names -Phase 5/6: Checking and upgrading tables +Phase 2/7: Installing used storage engines... Skipped +Phase 3/7: Fixing views +Phase 4/7: Running 'mysql_fix_privilege_tables' +Phase 5/7: Fixing table and database names +Phase 6/7: Checking and upgrading tables Processing databases information_schema mtr @@ -54,7 +55,7 @@ mtr.global_suppressions OK mtr.test_suppressions OK performance_schema test -Phase 6/6: Running 'FLUSH PRIVILEGES' +Phase 7/7: Running 'FLUSH PRIVILEGES' OK DROP TABLE general_log; RENAME TABLE renamed_general_log TO general_log; diff --git a/mysql-test/r/mysql_upgrade-6984.result b/mysql-test/r/mysql_upgrade-6984.result index fec0fcacb5f..59c9a865b7c 100644 --- a/mysql-test/r/mysql_upgrade-6984.result +++ b/mysql-test/r/mysql_upgrade-6984.result @@ -1,5 +1,5 @@ update mysql.user set password=password("foo") where user='root'; -Phase 1/6: Checking and upgrading mysql database +Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -42,10 +42,11 @@ error : Corrupt mysql.innodb_table_stats Error : Unknown storage engine 'InnoDB' error : Corrupt -Phase 2/6: Fixing views -Phase 3/6: Running 'mysql_fix_privilege_tables' -Phase 4/6: Fixing table and database names -Phase 5/6: Checking and upgrading tables +Phase 2/7: Installing used storage engines... Skipped +Phase 3/7: Fixing views +Phase 4/7: Running 'mysql_fix_privilege_tables' +Phase 5/7: Fixing table and database names +Phase 6/7: Checking and upgrading tables Processing databases information_schema mtr @@ -53,7 +54,7 @@ mtr.global_suppressions OK mtr.test_suppressions OK performance_schema test -Phase 6/6: Running 'FLUSH PRIVILEGES' +Phase 7/7: Running 'FLUSH PRIVILEGES' OK connect con1,localhost,root,foo,,,; update mysql.user set password='' where user='root'; diff --git a/mysql-test/r/mysql_upgrade.result b/mysql-test/r/mysql_upgrade.result index 99446cd9d6f..a337a939acc 100644 --- a/mysql-test/r/mysql_upgrade.result +++ b/mysql-test/r/mysql_upgrade.result @@ -1,6 +1,6 @@ set sql_mode=""; Run mysql_upgrade once -Phase 1/6: Checking and upgrading mysql database +Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -31,10 +31,11 @@ mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK -Phase 2/6: Fixing views -Phase 3/6: Running 'mysql_fix_privilege_tables' -Phase 4/6: Fixing table and database names -Phase 5/6: Checking and upgrading tables +Phase 2/7: Installing used storage engines... Skipped +Phase 3/7: Fixing views +Phase 4/7: Running 'mysql_fix_privilege_tables' +Phase 5/7: Fixing table and database names +Phase 6/7: Checking and upgrading tables Processing databases information_schema mtr @@ -42,12 +43,12 @@ mtr.global_suppressions OK mtr.test_suppressions OK performance_schema test -Phase 6/6: Running 'FLUSH PRIVILEGES' +Phase 7/7: Running 'FLUSH PRIVILEGES' OK Run it again - should say already completed This installation of MySQL is already upgraded to VERSION, use --force if you still need to run mysql_upgrade Force should run it regardless of whether it has been run before -Phase 1/6: Checking and upgrading mysql database +Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -78,10 +79,11 @@ mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK -Phase 2/6: Fixing views -Phase 3/6: Running 'mysql_fix_privilege_tables' -Phase 4/6: Fixing table and database names -Phase 5/6: Checking and upgrading tables +Phase 2/7: Installing used storage engines... Skipped +Phase 3/7: Fixing views +Phase 4/7: Running 'mysql_fix_privilege_tables' +Phase 5/7: Fixing table and database names +Phase 6/7: Checking and upgrading tables Processing databases information_schema mtr @@ -89,12 +91,12 @@ mtr.global_suppressions OK mtr.test_suppressions OK performance_schema test -Phase 6/6: Running 'FLUSH PRIVILEGES' +Phase 7/7: Running 'FLUSH PRIVILEGES' OK CREATE USER mysqltest1@'%' IDENTIFIED by 'sakila'; GRANT ALL ON *.* TO mysqltest1@'%'; Run mysql_upgrade with password protected account -Phase 1/6: Checking and upgrading mysql database +Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -125,10 +127,11 @@ mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK -Phase 2/6: Fixing views -Phase 3/6: Running 'mysql_fix_privilege_tables' -Phase 4/6: Fixing table and database names -Phase 5/6: Checking and upgrading tables +Phase 2/7: Installing used storage engines... Skipped +Phase 3/7: Fixing views +Phase 4/7: Running 'mysql_fix_privilege_tables' +Phase 5/7: Fixing table and database names +Phase 6/7: Checking and upgrading tables Processing databases information_schema mtr @@ -136,7 +139,7 @@ mtr.global_suppressions OK mtr.test_suppressions OK performance_schema test -Phase 6/6: Running 'FLUSH PRIVILEGES' +Phase 7/7: Running 'FLUSH PRIVILEGES' OK DROP USER mysqltest1@'%'; Version check failed. Got the following error when calling the 'mysql' command line client @@ -146,7 +149,7 @@ Run mysql_upgrade with a non existing server socket mysqlcheck: Got error: 2005: Unknown MySQL server host 'not_existing_host' (errno) when trying to connect FATAL ERROR: Upgrade failed set GLOBAL sql_mode='STRICT_ALL_TABLES,ANSI_QUOTES,NO_ZERO_DATE'; -Phase 1/6: Checking and upgrading mysql database +Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -177,10 +180,11 @@ mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK -Phase 2/6: Fixing views -Phase 3/6: Running 'mysql_fix_privilege_tables' -Phase 4/6: Fixing table and database names -Phase 5/6: Checking and upgrading tables +Phase 2/7: Installing used storage engines... Skipped +Phase 3/7: Fixing views +Phase 4/7: Running 'mysql_fix_privilege_tables' +Phase 5/7: Fixing table and database names +Phase 6/7: Checking and upgrading tables Processing databases information_schema mtr @@ -188,7 +192,7 @@ mtr.global_suppressions OK mtr.test_suppressions OK performance_schema test -Phase 6/6: Running 'FLUSH PRIVILEGES' +Phase 7/7: Running 'FLUSH PRIVILEGES' OK set GLOBAL sql_mode=default; # @@ -199,7 +203,7 @@ CREATE PROCEDURE testproc() BEGIN END; UPDATE mysql.proc SET character_set_client = NULL WHERE name LIKE 'testproc'; UPDATE mysql.proc SET collation_connection = NULL WHERE name LIKE 'testproc'; UPDATE mysql.proc SET db_collation = NULL WHERE name LIKE 'testproc'; -Phase 1/6: Checking and upgrading mysql database +Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -230,10 +234,11 @@ mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK -Phase 2/6: Fixing views -Phase 3/6: Running 'mysql_fix_privilege_tables' -Phase 4/6: Fixing table and database names -Phase 5/6: Checking and upgrading tables +Phase 2/7: Installing used storage engines... Skipped +Phase 3/7: Fixing views +Phase 4/7: Running 'mysql_fix_privilege_tables' +Phase 5/7: Fixing table and database names +Phase 6/7: Checking and upgrading tables Processing databases information_schema mtr @@ -241,7 +246,7 @@ mtr.global_suppressions OK mtr.test_suppressions OK performance_schema test -Phase 6/6: Running 'FLUSH PRIVILEGES' +Phase 7/7: Running 'FLUSH PRIVILEGES' OK CALL testproc(); DROP PROCEDURE testproc; @@ -255,7 +260,7 @@ WARNING: NULL values of the 'db_collation' column ('mysql.proc' table) have been GRANT USAGE ON *.* TO 'user3'@'%'; GRANT ALL PRIVILEGES ON `roelt`.`test2` TO 'user3'@'%'; Run mysql_upgrade with all privileges on a user -Phase 1/6: Checking and upgrading mysql database +Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -286,10 +291,11 @@ mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK -Phase 2/6: Fixing views -Phase 3/6: Running 'mysql_fix_privilege_tables' -Phase 4/6: Fixing table and database names -Phase 5/6: Checking and upgrading tables +Phase 2/7: Installing used storage engines... Skipped +Phase 3/7: Fixing views +Phase 4/7: Running 'mysql_fix_privilege_tables' +Phase 5/7: Fixing table and database names +Phase 6/7: Checking and upgrading tables Processing databases information_schema mtr @@ -297,7 +303,7 @@ mtr.global_suppressions OK mtr.test_suppressions OK performance_schema test -Phase 6/6: Running 'FLUSH PRIVILEGES' +Phase 7/7: Running 'FLUSH PRIVILEGES' OK SHOW GRANTS FOR 'user3'@'%'; Grants for user3@% @@ -306,7 +312,7 @@ GRANT ALL PRIVILEGES ON `roelt`.`test2` TO 'user3'@'%' DROP USER 'user3'@'%'; End of 5.1 tests The --upgrade-system-tables option was used, user tables won't be touched. -Phase 1/6: Checking and upgrading mysql database +Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -337,11 +343,12 @@ mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK -Phase 2/6: Fixing views... Skipped -Phase 3/6: Running 'mysql_fix_privilege_tables' -Phase 4/6: Fixing table and database names ... Skipped -Phase 5/6: Checking and upgrading tables... Skipped -Phase 6/6: Running 'FLUSH PRIVILEGES' +Phase 2/7: Installing used storage engines... Skipped +Phase 3/7: Fixing views... Skipped +Phase 4/7: Running 'mysql_fix_privilege_tables' +Phase 5/7: Fixing table and database names ... Skipped +Phase 6/7: Checking and upgrading tables... Skipped +Phase 7/7: Running 'FLUSH PRIVILEGES' OK # # Bug#11827359 60223: MYSQL_UPGRADE PROBLEM WITH OPTION @@ -349,7 +356,7 @@ OK # # Droping the previously created mysql_upgrade_info file.. # Running mysql_upgrade with --skip-write-binlog.. -Phase 1/6: Checking and upgrading mysql database +Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -380,10 +387,11 @@ mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK -Phase 2/6: Fixing views -Phase 3/6: Running 'mysql_fix_privilege_tables' -Phase 4/6: Fixing table and database names -Phase 5/6: Checking and upgrading tables +Phase 2/7: Installing used storage engines... Skipped +Phase 3/7: Fixing views +Phase 4/7: Running 'mysql_fix_privilege_tables' +Phase 5/7: Fixing table and database names +Phase 6/7: Checking and upgrading tables Processing databases information_schema mtr @@ -391,7 +399,7 @@ mtr.global_suppressions OK mtr.test_suppressions OK performance_schema test -Phase 6/6: Running 'FLUSH PRIVILEGES' +Phase 7/7: Running 'FLUSH PRIVILEGES' OK # # Bug #21489398: MYSQL_UPGRADE: FATAL ERROR: UPGRADE FAILED - IMPROVE ERROR @@ -413,7 +421,7 @@ GRANT INSERT ON mysql.user TO very_long_user_name_number_2; GRANT UPDATE (User) ON mysql.db TO very_long_user_name_number_1; GRANT UPDATE (User) ON mysql.db TO very_long_user_name_number_2; CREATE PROCEDURE test.pr() BEGIN END; -Phase 1/6: Checking and upgrading mysql database +Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -444,10 +452,11 @@ mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK -Phase 2/6: Fixing views -Phase 3/6: Running 'mysql_fix_privilege_tables' -Phase 4/6: Fixing table and database names -Phase 5/6: Checking and upgrading tables +Phase 2/7: Installing used storage engines... Skipped +Phase 3/7: Fixing views +Phase 4/7: Running 'mysql_fix_privilege_tables' +Phase 5/7: Fixing table and database names +Phase 6/7: Checking and upgrading tables Processing databases information_schema mtr @@ -455,7 +464,7 @@ mtr.global_suppressions OK mtr.test_suppressions OK performance_schema test -Phase 6/6: Running 'FLUSH PRIVILEGES' +Phase 7/7: Running 'FLUSH PRIVILEGES' OK SELECT definer FROM mysql.proc WHERE db = 'test' AND name = 'pr'; definer @@ -471,7 +480,7 @@ set sql_mode=default; create table test.t1(a int) engine=MyISAM; # Trying to enforce InnoDB for all tables SET GLOBAL enforce_storage_engine=InnoDB; -Phase 1/6: Checking and upgrading mysql database +Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -502,10 +511,11 @@ mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK -Phase 2/6: Fixing views -Phase 3/6: Running 'mysql_fix_privilege_tables' -Phase 4/6: Fixing table and database names -Phase 5/6: Checking and upgrading tables +Phase 2/7: Installing used storage engines... Skipped +Phase 3/7: Fixing views +Phase 4/7: Running 'mysql_fix_privilege_tables' +Phase 5/7: Fixing table and database names +Phase 6/7: Checking and upgrading tables Processing databases information_schema mtr @@ -514,7 +524,7 @@ mtr.test_suppressions OK performance_schema test test.t1 OK -Phase 6/6: Running 'FLUSH PRIVILEGES' +Phase 7/7: Running 'FLUSH PRIVILEGES' OK # Should return 2 SELECT count(*) FROM information_schema.tables where ENGINE="InnoDB"; diff --git a/mysql-test/r/mysql_upgrade_no_innodb.result b/mysql-test/r/mysql_upgrade_no_innodb.result index acbca131587..6ad818278f8 100644 --- a/mysql-test/r/mysql_upgrade_no_innodb.result +++ b/mysql-test/r/mysql_upgrade_no_innodb.result @@ -1,5 +1,5 @@ The --upgrade-system-tables option was used, user tables won't be touched. -Phase 1/6: Checking and upgrading mysql database +Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -42,9 +42,10 @@ error : Corrupt mysql.innodb_table_stats Error : Unknown storage engine 'InnoDB' error : Corrupt -Phase 2/6: Fixing views... Skipped -Phase 3/6: Running 'mysql_fix_privilege_tables' -Phase 4/6: Fixing table and database names ... Skipped -Phase 5/6: Checking and upgrading tables... Skipped -Phase 6/6: Running 'FLUSH PRIVILEGES' +Phase 2/7: Installing used storage engines... Skipped +Phase 3/7: Fixing views... Skipped +Phase 4/7: Running 'mysql_fix_privilege_tables' +Phase 5/7: Fixing table and database names ... Skipped +Phase 6/7: Checking and upgrading tables... Skipped +Phase 7/7: Running 'FLUSH PRIVILEGES' OK diff --git a/mysql-test/r/mysql_upgrade_noengine.result b/mysql-test/r/mysql_upgrade_noengine.result new file mode 100644 index 00000000000..09e705abb69 --- /dev/null +++ b/mysql-test/r/mysql_upgrade_noengine.result @@ -0,0 +1,297 @@ +install soname 'ha_blackhole'; +install soname 'ha_archive'; +create table t1 (a int) engine=blackhole; +create table t2 (a int) engine=archive; +select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test'; +table_catalog def +table_schema test +table_name t1 +table_type BASE TABLE +engine BLACKHOLE +row_format Fixed +table_rows 0 +data_length 0 +table_comment +table_catalog def +table_schema test +table_name t2 +table_type BASE TABLE +engine ARCHIVE +row_format Compressed +table_rows 0 +data_length 521 +table_comment +flush tables; +uninstall plugin blackhole; +uninstall plugin archive; +select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test'; +table_catalog def +table_schema test +table_name t1 +table_type BASE TABLE +engine BLACKHOLE +row_format NULL +table_rows NULL +data_length NULL +table_comment Unknown storage engine 'BLACKHOLE' +table_catalog def +table_schema test +table_name t2 +table_type BASE TABLE +engine ARCHIVE +row_format NULL +table_rows NULL +data_length NULL +table_comment Unknown storage engine 'ARCHIVE' +Warnings: +Level Warning +Code 1286 +Message Unknown storage engine 'BLACKHOLE' +Level Warning +Code 1286 +Message Unknown storage engine 'ARCHIVE' +Phase 1/7: Checking and upgrading mysql database +Processing databases +mysql +mysql.column_stats OK +mysql.columns_priv OK +mysql.db OK +mysql.event OK +mysql.func OK +mysql.gtid_slave_pos OK +mysql.help_category OK +mysql.help_keyword OK +mysql.help_relation OK +mysql.help_topic OK +mysql.host OK +mysql.index_stats OK +mysql.innodb_index_stats OK +mysql.innodb_table_stats OK +mysql.plugin OK +mysql.proc OK +mysql.procs_priv OK +mysql.proxies_priv OK +mysql.roles_mapping OK +mysql.servers OK +mysql.table_stats OK +mysql.tables_priv OK +mysql.time_zone OK +mysql.time_zone_leap_second OK +mysql.time_zone_name OK +mysql.time_zone_transition OK +mysql.time_zone_transition_type OK +mysql.user OK +Phase 2/7: Installing used storage engines... Skipped +Phase 3/7: Fixing views +Phase 4/7: Running 'mysql_fix_privilege_tables' +Phase 5/7: Fixing table and database names +Phase 6/7: Checking and upgrading tables +Processing databases +information_schema +mtr +mtr.global_suppressions OK +mtr.test_suppressions OK +performance_schema +test +test.t1 +Error : Unknown storage engine 'BLACKHOLE' +error : Corrupt +test.t2 +Error : Unknown storage engine 'ARCHIVE' +error : Corrupt + +Repairing tables +test.t1 +Error : Unknown storage engine 'BLACKHOLE' +error : Corrupt +test.t2 +Error : Unknown storage engine 'ARCHIVE' +error : Corrupt +Phase 7/7: Running 'FLUSH PRIVILEGES' +OK +select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test'; +table_catalog def +table_schema test +table_name t1 +table_type BASE TABLE +engine BLACKHOLE +row_format NULL +table_rows NULL +data_length NULL +table_comment Unknown storage engine 'BLACKHOLE' +table_catalog def +table_schema test +table_name t2 +table_type BASE TABLE +engine ARCHIVE +row_format NULL +table_rows NULL +data_length NULL +table_comment Unknown storage engine 'ARCHIVE' +Warnings: +Level Warning +Code 1286 +Message Unknown storage engine 'BLACKHOLE' +Level Warning +Code 1286 +Message Unknown storage engine 'ARCHIVE' +alter table mysql.user drop column default_role, drop column max_statement_time; +Phase 1/7: Checking and upgrading mysql database +Processing databases +mysql +mysql.column_stats OK +mysql.columns_priv OK +mysql.db OK +mysql.event OK +mysql.func OK +mysql.gtid_slave_pos OK +mysql.help_category OK +mysql.help_keyword OK +mysql.help_relation OK +mysql.help_topic OK +mysql.host OK +mysql.index_stats OK +mysql.innodb_index_stats OK +mysql.innodb_table_stats OK +mysql.plugin OK +mysql.proc OK +mysql.procs_priv OK +mysql.proxies_priv OK +mysql.roles_mapping OK +mysql.servers OK +mysql.table_stats OK +mysql.tables_priv OK +mysql.time_zone OK +mysql.time_zone_leap_second OK +mysql.time_zone_name OK +mysql.time_zone_transition OK +mysql.time_zone_transition_type OK +mysql.user OK +Phase 2/7: Installing used storage engines... Skipped +Phase 3/7: Fixing views +Phase 4/7: Running 'mysql_fix_privilege_tables' +Phase 5/7: Fixing table and database names +Phase 6/7: Checking and upgrading tables +Processing databases +information_schema +mtr +mtr.global_suppressions OK +mtr.test_suppressions OK +performance_schema +test +test.t1 +Error : Unknown storage engine 'BLACKHOLE' +error : Corrupt +test.t2 +Error : Unknown storage engine 'ARCHIVE' +error : Corrupt + +Repairing tables +test.t1 +Error : Unknown storage engine 'BLACKHOLE' +error : Corrupt +test.t2 +Error : Unknown storage engine 'ARCHIVE' +error : Corrupt +Phase 7/7: Running 'FLUSH PRIVILEGES' +OK +select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test'; +table_catalog def +table_schema test +table_name t1 +table_type BASE TABLE +engine BLACKHOLE +row_format NULL +table_rows NULL +data_length NULL +table_comment Unknown storage engine 'BLACKHOLE' +table_catalog def +table_schema test +table_name t2 +table_type BASE TABLE +engine ARCHIVE +row_format NULL +table_rows NULL +data_length NULL +table_comment Unknown storage engine 'ARCHIVE' +Warnings: +Level Warning +Code 1286 +Message Unknown storage engine 'BLACKHOLE' +Level Warning +Code 1286 +Message Unknown storage engine 'ARCHIVE' +alter table mysql.user drop column default_role, drop column max_statement_time; +Phase 1/7: Checking and upgrading mysql database +Processing databases +mysql +mysql.column_stats OK +mysql.columns_priv OK +mysql.db OK +mysql.event OK +mysql.func OK +mysql.gtid_slave_pos OK +mysql.help_category OK +mysql.help_keyword OK +mysql.help_relation OK +mysql.help_topic OK +mysql.host OK +mysql.index_stats OK +mysql.innodb_index_stats OK +mysql.innodb_table_stats OK +mysql.plugin OK +mysql.proc OK +mysql.procs_priv OK +mysql.proxies_priv OK +mysql.roles_mapping OK +mysql.servers OK +mysql.table_stats OK +mysql.tables_priv OK +mysql.time_zone OK +mysql.time_zone_leap_second OK +mysql.time_zone_name OK +mysql.time_zone_transition OK +mysql.time_zone_transition_type OK +mysql.user OK +Upgrading from a version before MariaDB-10.1 +Phase 2/7: Installing used storage engines +Checking for tables with unknown storage engine +installing plugin for 'blackhole' storage engine +installing plugin for 'archive' storage engine +Phase 3/7: Fixing views +Phase 4/7: Running 'mysql_fix_privilege_tables' +Phase 5/7: Fixing table and database names +Phase 6/7: Checking and upgrading tables +Processing databases +information_schema +mtr +mtr.global_suppressions OK +mtr.test_suppressions OK +performance_schema +test +test.t1 OK +test.t2 OK +Phase 7/7: Running 'FLUSH PRIVILEGES' +OK +select table_catalog, table_schema, table_name, table_type, engine, row_format, table_rows, data_length, table_comment from information_schema.tables where table_schema='test'; +table_catalog def +table_schema test +table_name t1 +table_type BASE TABLE +engine BLACKHOLE +row_format Fixed +table_rows 0 +data_length 0 +table_comment +table_catalog def +table_schema test +table_name t2 +table_type BASE TABLE +engine ARCHIVE +row_format Compressed +table_rows 0 +data_length 521 +table_comment +drop table t1, t2; +uninstall plugin blackhole; +uninstall plugin archive; diff --git a/mysql-test/r/mysql_upgrade_ssl.result b/mysql-test/r/mysql_upgrade_ssl.result index e06d1bb1671..918a24ffc71 100644 --- a/mysql-test/r/mysql_upgrade_ssl.result +++ b/mysql-test/r/mysql_upgrade_ssl.result @@ -1,7 +1,7 @@ # # Bug#55672 mysql_upgrade dies with internal error # -Phase 1/6: Checking and upgrading mysql database +Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -32,10 +32,11 @@ mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK -Phase 2/6: Fixing views -Phase 3/6: Running 'mysql_fix_privilege_tables' -Phase 4/6: Fixing table and database names -Phase 5/6: Checking and upgrading tables +Phase 2/7: Installing used storage engines... Skipped +Phase 3/7: Fixing views +Phase 4/7: Running 'mysql_fix_privilege_tables' +Phase 5/7: Fixing table and database names +Phase 6/7: Checking and upgrading tables Processing databases information_schema mtr @@ -43,5 +44,5 @@ mtr.global_suppressions OK mtr.test_suppressions OK performance_schema test -Phase 6/6: Running 'FLUSH PRIVILEGES' +Phase 7/7: Running 'FLUSH PRIVILEGES' OK diff --git a/mysql-test/r/mysql_upgrade_view.result b/mysql-test/r/mysql_upgrade_view.result index f43f42f97fd..dc31592566a 100644 --- a/mysql-test/r/mysql_upgrade_view.result +++ b/mysql-test/r/mysql_upgrade_view.result @@ -63,7 +63,7 @@ test.v2 check error Upgrade required. Please do "REPAIR VIEW `v2`" or dump/reloa check view v3 for upgrade; Table Op Msg_type Msg_text test.v3 check error Upgrade required. Please do "REPAIR VIEW `v3`" or dump/reload to fix it! -Phase 1/6: Checking and upgrading mysql database +Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -106,14 +106,15 @@ error : Corrupt mysql.innodb_table_stats Error : Unknown storage engine 'InnoDB' error : Corrupt -Phase 2/6: Fixing views +Phase 2/7: Installing used storage engines... Skipped +Phase 3/7: Fixing views test.v1 OK test.v1badcheck OK test.v2 OK test.v3 OK -Phase 3/6: Running 'mysql_fix_privilege_tables' -Phase 4/6: Fixing table and database names -Phase 5/6: Checking and upgrading tables +Phase 4/7: Running 'mysql_fix_privilege_tables' +Phase 5/7: Fixing table and database names +Phase 6/7: Checking and upgrading tables Processing databases information_schema mtr @@ -123,7 +124,7 @@ performance_schema test test.kv OK test.t1 OK -Phase 6/6: Running 'FLUSH PRIVILEGES' +Phase 7/7: Running 'FLUSH PRIVILEGES' OK show create view v1; View Create View character_set_client collation_connection @@ -205,7 +206,7 @@ show create view v4; View Create View character_set_client collation_connection v4 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS select `t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci MySQL upgrade detected -Phase 1/6: Checking and upgrading mysql database +Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -249,14 +250,15 @@ error : Corrupt mysql.innodb_table_stats Error : Unknown storage engine 'InnoDB' error : Corrupt -Phase 2/6: Fixing views from mysql +Phase 2/7: Installing used storage engines... Skipped +Phase 3/7: Fixing views from mysql test.v1 OK test.v2 OK test.v3 OK test.v4 OK -Phase 3/6: Running 'mysql_fix_privilege_tables' -Phase 4/6: Fixing table and database names -Phase 5/6: Checking and upgrading tables +Phase 4/7: Running 'mysql_fix_privilege_tables' +Phase 5/7: Fixing table and database names +Phase 6/7: Checking and upgrading tables Processing databases information_schema mtr @@ -266,7 +268,7 @@ performance_schema test test.kv OK test.t1 OK -Phase 6/6: Running 'FLUSH PRIVILEGES' +Phase 7/7: Running 'FLUSH PRIVILEGES' OK flush tables; show create view v1; @@ -323,7 +325,7 @@ rename table mysql.event to mysql.ev_bk; flush tables; The --upgrade-system-tables option was used, user tables won't be touched. MySQL upgrade detected -Phase 1/6: Checking and upgrading mysql database +Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK @@ -367,14 +369,15 @@ error : Corrupt mysql.innodb_table_stats Error : Unknown storage engine 'InnoDB' error : Corrupt -Phase 2/6: Fixing views from mysql +Phase 2/7: Installing used storage engines... Skipped +Phase 3/7: Fixing views from mysql test.v1 OK test.v2 OK test.v3 OK -Phase 3/6: Running 'mysql_fix_privilege_tables' -Phase 4/6: Fixing table and database names ... Skipped -Phase 5/6: Checking and upgrading tables... Skipped -Phase 6/6: Running 'FLUSH PRIVILEGES' +Phase 4/7: Running 'mysql_fix_privilege_tables' +Phase 5/7: Fixing table and database names ... Skipped +Phase 6/7: Checking and upgrading tables... Skipped +Phase 7/7: Running 'FLUSH PRIVILEGES' OK drop table mysql.event; rename table mysql.ev_bk to mysql.event; diff --git a/mysql-test/r/mysqlbinlog_row_compressed.result b/mysql-test/r/mysqlbinlog_row_compressed.result index 0583ee354df..24fff723ec8 100644 --- a/mysql-test/r/mysqlbinlog_row_compressed.result +++ b/mysql-test/r/mysqlbinlog_row_compressed.result @@ -29,10 +29,10 @@ ROLLBACK/*!*/; /*!100001 SET @@session.server_id=1*//*!*/; /*!100001 SET @@session.gtid_seq_no=1*//*!*/; # at 371 -#<date> server id 1 end_log_pos 533 CRC32 XXX Query_compressed thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 533 CRC32 XXX Query_compressed thread_id=5 exec_time=x error_code=0 use `test`/*!*/; SET TIMESTAMP=X/*!*/; -SET @@session.pseudo_thread_id=4/*!*/; +SET @@session.pseudo_thread_id=5/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; SET @@session.sql_mode=1411383296/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; @@ -46,7 +46,7 @@ CREATE TABLE t1 (pk INT PRIMARY KEY, f1 INT, f2 INT, f3 TINYINT, f4 MEDIUMINT, f #<date> server id 1 end_log_pos 575 CRC32 XXX GTID 0-1-2 ddl /*!100001 SET @@session.gtid_seq_no=2*//*!*/; # at 575 -#<date> server id 1 end_log_pos 727 CRC32 XXX Query_compressed thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 727 CRC32 XXX Query_compressed thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; CREATE TABLE t2 (pk INT PRIMARY KEY, f1 INT, f2 INT, f3 INT, f4 INT, f5 MEDIUMINT, f6 INT, f7 INT, f8 char(1)) /*!*/; @@ -74,7 +74,7 @@ BEGIN ### @8=7 /* INT meta=0 nullable=1 is_null=0 */ ### @9='' /* STRING(1) meta=65025 nullable=1 is_null=0 */ # at 967 -#<date> server id 1 end_log_pos 1040 CRC32 XXX Query thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 1040 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; COMMIT /*!*/; @@ -102,7 +102,7 @@ BEGIN ### @8=7 /* INT meta=0 nullable=1 is_null=0 */ ### @9=NULL /* STRING(1) meta=65025 nullable=1 is_null=1 */ # at 1281 -#<date> server id 1 end_log_pos 1354 CRC32 XXX Query thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 1354 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; COMMIT /*!*/; @@ -130,7 +130,7 @@ BEGIN ### @8=7 /* INT meta=0 nullable=1 is_null=0 */ ### @9='A' /* STRING(1) meta=65025 nullable=1 is_null=0 */ # at 1596 -#<date> server id 1 end_log_pos 1669 CRC32 XXX Query thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 1669 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; COMMIT /*!*/; @@ -158,7 +158,7 @@ BEGIN ### @8=7 /* INT meta=0 nullable=1 is_null=0 */ ### @9='A' /* STRING(1) meta=65025 nullable=1 is_null=0 */ # at 1909 -#<date> server id 1 end_log_pos 1982 CRC32 XXX Query thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 1982 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; COMMIT /*!*/; @@ -219,7 +219,7 @@ BEGIN ### @8=7 /* INT meta=0 nullable=1 is_null=0 */ ### @9='A' /* STRING(1) meta=65025 nullable=1 is_null=0 */ # at 2225 -#<date> server id 1 end_log_pos 2298 CRC32 XXX Query thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 2298 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; COMMIT /*!*/; @@ -299,7 +299,7 @@ BEGIN ### @8=7 /* INT meta=0 nullable=1 is_null=0 */ ### @9='A' /* STRING(1) meta=65025 nullable=1 is_null=0 */ # at 2561 -#<date> server id 1 end_log_pos 2634 CRC32 XXX Query thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 2634 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; COMMIT /*!*/; @@ -360,7 +360,7 @@ BEGIN ### @8=7 /* INT meta=0 nullable=1 is_null=0 */ ### @9='A' /* STRING(1) meta=65025 nullable=1 is_null=0 */ # at 2861 -#<date> server id 1 end_log_pos 2934 CRC32 XXX Query thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 2934 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; COMMIT /*!*/; @@ -421,7 +421,7 @@ BEGIN ### @8=7 /* INT meta=0 nullable=1 is_null=0 */ ### @9='A' /* STRING(1) meta=65025 nullable=1 is_null=0 */ # at 3154 -#<date> server id 1 end_log_pos 3227 CRC32 XXX Query thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 3227 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; COMMIT /*!*/; diff --git a/mysql-test/r/mysqlbinlog_row_minimal.result b/mysql-test/r/mysqlbinlog_row_minimal.result index 6d36ec0e82f..ca8e43bfb33 100644 --- a/mysql-test/r/mysqlbinlog_row_minimal.result +++ b/mysql-test/r/mysqlbinlog_row_minimal.result @@ -27,10 +27,10 @@ ROLLBACK/*!*/; /*!100001 SET @@session.server_id=1*//*!*/; /*!100001 SET @@session.gtid_seq_no=1*//*!*/; # at 371 -#<date> server id 1 end_log_pos 555 CRC32 XXX Query thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 555 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 use `test`/*!*/; SET TIMESTAMP=X/*!*/; -SET @@session.pseudo_thread_id=4/*!*/; +SET @@session.pseudo_thread_id=5/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; SET @@session.sql_mode=1411383296/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; @@ -44,7 +44,7 @@ CREATE TABLE t1 (pk INT PRIMARY KEY, f1 INT, f2 INT, f3 TINYINT, f4 MEDIUMINT, f #<date> server id 1 end_log_pos 597 CRC32 XXX GTID 0-1-2 ddl /*!100001 SET @@session.gtid_seq_no=2*//*!*/; # at 597 -#<date> server id 1 end_log_pos 774 CRC32 XXX Query thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 774 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; CREATE TABLE t2 (pk INT PRIMARY KEY, f1 INT, f2 INT, f3 INT, f4 INT, f5 MEDIUMINT, f6 INT, f7 INT, f8 char(1)) /*!*/; @@ -72,7 +72,7 @@ BEGIN ### @8=7 /* INT meta=0 nullable=1 is_null=0 */ ### @9='' /* STRING(1) meta=65025 nullable=1 is_null=0 */ # at 1015 -#<date> server id 1 end_log_pos 1088 CRC32 XXX Query thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 1088 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; COMMIT /*!*/; @@ -100,7 +100,7 @@ BEGIN ### @8=7 /* INT meta=0 nullable=1 is_null=0 */ ### @9=NULL /* STRING(1) meta=65025 nullable=1 is_null=1 */ # at 1330 -#<date> server id 1 end_log_pos 1403 CRC32 XXX Query thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 1403 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; COMMIT /*!*/; @@ -128,7 +128,7 @@ BEGIN ### @8=7 /* INT meta=0 nullable=1 is_null=0 */ ### @9='A' /* STRING(1) meta=65025 nullable=1 is_null=0 */ # at 1646 -#<date> server id 1 end_log_pos 1719 CRC32 XXX Query thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 1719 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; COMMIT /*!*/; @@ -156,7 +156,7 @@ BEGIN ### @8=7 /* INT meta=0 nullable=1 is_null=0 */ ### @9='A' /* STRING(1) meta=65025 nullable=1 is_null=0 */ # at 1962 -#<date> server id 1 end_log_pos 2035 CRC32 XXX Query thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 2035 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; COMMIT /*!*/; @@ -217,7 +217,7 @@ BEGIN ### @8=7 /* INT meta=0 nullable=1 is_null=0 */ ### @9='A' /* STRING(1) meta=65025 nullable=1 is_null=0 */ # at 2354 -#<date> server id 1 end_log_pos 2427 CRC32 XXX Query thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 2427 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; COMMIT /*!*/; @@ -252,7 +252,7 @@ BEGIN ### SET ### @5=5 /* INT meta=0 nullable=1 is_null=0 */ # at 2665 -#<date> server id 1 end_log_pos 2738 CRC32 XXX Query thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 2738 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; COMMIT /*!*/; @@ -281,7 +281,7 @@ BEGIN ### WHERE ### @1=13 /* INT meta=0 nullable=0 is_null=0 */ # at 2927 -#<date> server id 1 end_log_pos 3000 CRC32 XXX Query thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 3000 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; COMMIT /*!*/; @@ -310,7 +310,7 @@ BEGIN ### WHERE ### @1=13 /* INT meta=0 nullable=0 is_null=0 */ # at 3189 -#<date> server id 1 end_log_pos 3262 CRC32 XXX Query thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 3262 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; COMMIT /*!*/; diff --git a/mysql-test/r/mysqlbinlog_stmt_compressed.result b/mysql-test/r/mysqlbinlog_stmt_compressed.result index cb268cac0d4..cd8e98c1ee3 100644 --- a/mysql-test/r/mysqlbinlog_stmt_compressed.result +++ b/mysql-test/r/mysqlbinlog_stmt_compressed.result @@ -29,10 +29,10 @@ ROLLBACK/*!*/; /*!100001 SET @@session.server_id=1*//*!*/; /*!100001 SET @@session.gtid_seq_no=1*//*!*/; # at 371 -#<date> server id 1 end_log_pos 533 CRC32 XXX Query_compressed thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 533 CRC32 XXX Query_compressed thread_id=5 exec_time=x error_code=0 use `test`/*!*/; SET TIMESTAMP=X/*!*/; -SET @@session.pseudo_thread_id=4/*!*/; +SET @@session.pseudo_thread_id=5/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; SET @@session.sql_mode=1411383296/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; @@ -46,7 +46,7 @@ CREATE TABLE t1 (pk INT PRIMARY KEY, f1 INT, f2 INT, f3 TINYINT, f4 MEDIUMINT, f #<date> server id 1 end_log_pos 575 CRC32 XXX GTID 0-1-2 ddl /*!100001 SET @@session.gtid_seq_no=2*//*!*/; # at 575 -#<date> server id 1 end_log_pos 727 CRC32 XXX Query_compressed thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 727 CRC32 XXX Query_compressed thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; CREATE TABLE t2 (pk INT PRIMARY KEY, f1 INT, f2 INT, f3 INT, f4 INT, f5 MEDIUMINT, f6 INT, f7 INT, f8 char(1)) /*!*/; @@ -56,12 +56,12 @@ CREATE TABLE t2 (pk INT PRIMARY KEY, f1 INT, f2 INT, f3 INT, f4 INT, f5 MEDIUMIN BEGIN /*!*/; # at 769 -#<date> server id 1 end_log_pos 897 CRC32 XXX Query_compressed thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 897 CRC32 XXX Query_compressed thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; INSERT INTO t1 VALUES (10, 1, 2, 3, 4, 5, 6, 7, "") /*!*/; # at 897 -#<date> server id 1 end_log_pos 970 CRC32 XXX Query thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 970 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; COMMIT /*!*/; @@ -71,12 +71,12 @@ COMMIT BEGIN /*!*/; # at 1012 -#<date> server id 1 end_log_pos 1140 CRC32 XXX Query_compressed thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 1140 CRC32 XXX Query_compressed thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; INSERT INTO t1 VALUES (11, 1, 2, 3, 4, 5, 6, 7, NULL) /*!*/; # at 1140 -#<date> server id 1 end_log_pos 1213 CRC32 XXX Query thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 1213 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; COMMIT /*!*/; @@ -86,12 +86,12 @@ COMMIT BEGIN /*!*/; # at 1255 -#<date> server id 1 end_log_pos 1385 CRC32 XXX Query_compressed thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 1385 CRC32 XXX Query_compressed thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; INSERT INTO t1 VALUES (12, 1, 2, 3, NULL, 5, 6, 7, "A") /*!*/; # at 1385 -#<date> server id 1 end_log_pos 1458 CRC32 XXX Query thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 1458 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; COMMIT /*!*/; @@ -101,12 +101,12 @@ COMMIT BEGIN /*!*/; # at 1500 -#<date> server id 1 end_log_pos 1627 CRC32 XXX Query_compressed thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 1627 CRC32 XXX Query_compressed thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; INSERT INTO t1 VALUES (13, 1, 2, 3, 0, 5, 6, 7, "A") /*!*/; # at 1627 -#<date> server id 1 end_log_pos 1700 CRC32 XXX Query thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 1700 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; COMMIT /*!*/; @@ -116,12 +116,12 @@ COMMIT BEGIN /*!*/; # at 1742 -#<date> server id 1 end_log_pos 1850 CRC32 XXX Query_compressed thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 1850 CRC32 XXX Query_compressed thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; INSERT INTO t2 SELECT * FROM t1 /*!*/; # at 1850 -#<date> server id 1 end_log_pos 1923 CRC32 XXX Query thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 1923 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; COMMIT /*!*/; @@ -131,12 +131,12 @@ COMMIT BEGIN /*!*/; # at 1965 -#<date> server id 1 end_log_pos 2082 CRC32 XXX Query_compressed thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 2082 CRC32 XXX Query_compressed thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; UPDATE t2 SET f4=5 WHERE f4>0 or f4 is NULL /*!*/; # at 2082 -#<date> server id 1 end_log_pos 2155 CRC32 XXX Query thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 2155 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; COMMIT /*!*/; @@ -146,12 +146,12 @@ COMMIT BEGIN /*!*/; # at 2197 -#<date> server id 1 end_log_pos 2288 CRC32 XXX Query_compressed thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 2288 CRC32 XXX Query_compressed thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; DELETE FROM t1 /*!*/; # at 2288 -#<date> server id 1 end_log_pos 2361 CRC32 XXX Query thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 2361 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; COMMIT /*!*/; @@ -161,12 +161,12 @@ COMMIT BEGIN /*!*/; # at 2403 -#<date> server id 1 end_log_pos 2494 CRC32 XXX Query_compressed thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 2494 CRC32 XXX Query_compressed thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; DELETE FROM t2 /*!*/; # at 2494 -#<date> server id 1 end_log_pos 2567 CRC32 XXX Query thread_id=4 exec_time=x error_code=0 +#<date> server id 1 end_log_pos 2567 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 SET TIMESTAMP=X/*!*/; COMMIT /*!*/; diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index 277c7ab64dc..b46115c26f9 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -5533,3 +5533,4 @@ USE `db1`; DROP DATABASE db1; DROP DATABASE db2; +FOUND /Database: mysql/ in bug11505.sql diff --git a/mysql-test/r/partition_column.result b/mysql-test/r/partition_column.result index 06d39771466..3df31078a50 100644 --- a/mysql-test/r/partition_column.result +++ b/mysql-test/r/partition_column.result @@ -540,7 +540,7 @@ a b drop table t1; create table t1 as select to_seconds(null) as to_seconds; select data_type from information_schema.columns -where column_name='to_seconds'; +where table_schema='test' and column_name='to_seconds'; data_type int drop table t1; diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result index c1eff91de53..047b6dac2b6 100644 --- a/mysql-test/r/partition_innodb.result +++ b/mysql-test/r/partition_innodb.result @@ -740,6 +740,97 @@ SELECT * FROM t1 WHERE d = '1991-01-01'; d 1991-01-01 DROP TABLE t1; +set global default_storage_engine=default; +# +# MDEV-9455: [ERROR] mysqld got signal 11 +# +CREATE TABLE `t1` ( +`DIARY_TOTAL_DAY_SEQ` bigint(20) unsigned NOT NULL AUTO_INCREMENT, +`IMORY_ID` bigint(20) NOT NULL, +`NAME` varchar(75) DEFAULT NULL, +`DATETIME` varchar(10) NOT NULL DEFAULT '', +`DAILY_CALL_CNT` int(11) DEFAULT NULL, +`DAILY_SMS_CNT` int(11) DEFAULT NULL, +`NUMBER` varchar(64) DEFAULT NULL, +`DURATION` varchar(16) DEFAULT NULL, +PRIMARY KEY (`DIARY_TOTAL_DAY_SEQ`,`DATETIME`), +KEY `IDX_t1_01` (`IMORY_ID`,`DATETIME`) +) AUTO_INCREMENT=328702514 DEFAULT CHARSET=utf8mb4 +PARTITION BY RANGE COLUMNS(`DATETIME`) +(PARTITION p0 VALUES LESS THAN ('2015-10-01') ENGINE = InnoDB, +PARTITION p1 VALUES LESS THAN ('2015-11-01') ENGINE = InnoDB, +PARTITION p2 VALUES LESS THAN ('2015-12-01') ENGINE = InnoDB, +PARTITION p3 VALUES LESS THAN ('2016-01-01') ENGINE = InnoDB, +PARTITION p4 VALUES LESS THAN ('2016-02-01') ENGINE = InnoDB, +PARTITION p5 VALUES LESS THAN ('2016-03-01') ENGINE = InnoDB, +PARTITION p6 VALUES LESS THAN ('2016-04-01') ENGINE = InnoDB, +PARTITION p7 VALUES LESS THAN ('2016-05-01') ENGINE = InnoDB, +PARTITION p8 VALUES LESS THAN ('2016-06-01') ENGINE = InnoDB, +PARTITION p9 VALUES LESS THAN ('2016-07-01') ENGINE = InnoDB, +PARTITION p10 VALUES LESS THAN ('2016-08-01') ENGINE = InnoDB) +; +CREATE TABLE `t2` ( +`DIARY_SEQ` bigint(20) unsigned NOT NULL AUTO_INCREMENT, +`IMORY_ID` bigint(20) NOT NULL, +`CALL_TYPE` varchar(1) DEFAULT NULL, +`DATA_TYPE` varchar(1) DEFAULT NULL, +`FEATURES` varchar(1) DEFAULT NULL, +`NAME` varchar(75) DEFAULT NULL, +`NUMBER` varchar(64) DEFAULT NULL, +`DATETIME` datetime NOT NULL, +`REG_DATE` datetime NOT NULL, +`TITLE` varchar(50) DEFAULT NULL, +`BODY` varchar(4200) DEFAULT NULL, +`MIME_TYPE` varchar(32) DEFAULT NULL, +`DURATION` varchar(16) DEFAULT NULL, +`DEVICE_ID` varchar(64) DEFAULT NULL, +`DEVICE_NAME` varchar(32) DEFAULT NULL, +PRIMARY KEY (`DIARY_SEQ`,`DATETIME`,`REG_DATE`), +KEY `IDX_TB_DIARY_01` (`IMORY_ID`,`DATETIME`,`CALL_TYPE`,`NUMBER`), +KEY `IDX_TB_DIARY_02` (`REG_DATE`) +) AUTO_INCREMENT=688799006 DEFAULT CHARSET=utf8mb4 +PARTITION BY RANGE COLUMNS(REG_DATE) +(PARTITION p0 VALUES LESS THAN ('2015-10-01') ENGINE = InnoDB, +PARTITION p1 VALUES LESS THAN ('2015-11-01') ENGINE = InnoDB, +PARTITION p2 VALUES LESS THAN ('2015-12-01') ENGINE = InnoDB, +PARTITION p3 VALUES LESS THAN ('2016-01-01') ENGINE = InnoDB, +PARTITION p4 VALUES LESS THAN ('2016-02-01') ENGINE = InnoDB, +PARTITION p5 VALUES LESS THAN ('2016-03-01') ENGINE = InnoDB, +PARTITION p6 VALUES LESS THAN ('2016-04-01') ENGINE = InnoDB, +PARTITION p7 VALUES LESS THAN ('2016-05-01') ENGINE = InnoDB, +PARTITION p8 VALUES LESS THAN ('2016-06-01') ENGINE = InnoDB, +PARTITION p9 VALUES LESS THAN ('2016-07-01') ENGINE = InnoDB, +PARTITION p10 VALUES LESS THAN ('2016-08-01') ENGINE = InnoDB) +; +SELECT +A.IMORY_ID, +A.NUMBER, +A.NAME, +DATE_FORMAT(A.DATETIME, '%Y-%m-%d') AS TARGET_DATE, +SUM( CASE WHEN A.DATA_TYPE='1' THEN 1 ELSE 0 END) AS CALL_CNT, +SUM( CASE WHEN A.DATA_TYPE IN ('2', '3') THEN 1 ELSE 0 END) AS SMS_CNT, +SUM(CAST(A.DURATION AS INT)) AS DURATION, +( SELECT COUNT(*) +FROM t1 +WHERE IMORY_ID=A.IMORY_ID +AND NUMBER=A.NUMBER +AND NAME=A.NAME +AND DATETIME = DATE_FORMAT(A.DATETIME, '%Y-%m-%d') +) STATS_COUNT +FROM t2 A +WHERE A.IMORY_ID = 55094102 +AND A.DATETIME LIKE ( +SELECT CONCAT (DATE_FORMAT(DATETIME, '%Y-%m-%d') ,'%') +FROM t2 +WHERE IMORY_ID=55094102 +AND DIARY_SEQ IN ( 608351221, 608351225, 608351229 ) +group by DATE_FORMAT(DATETIME, '%Y-%m-%d') +) +GROUP BY A.IMORY_ID, A.NUMBER, A.NAME, DATE_FORMAT(A.DATETIME, '%Y-%m-%d') +; +IMORY_ID NUMBER NAME TARGET_DATE CALL_CNT SMS_CNT DURATION STATS_COUNT +drop table t2, t1; +set global default_storage_engine='innodb'; # # MDEV-5963: InnoDB: Assertion failure in file row0sel.cc line 2503, # Failing assertion: 0 with "key ptr now exceeds key end by 762 bytes" diff --git a/mysql-test/r/partition_myisam.result b/mysql-test/r/partition_myisam.result index f9f917a2803..664eb60c2c5 100644 --- a/mysql-test/r/partition_myisam.result +++ b/mysql-test/r/partition_myisam.result @@ -230,6 +230,22 @@ PARTITION pMax VALUES LESS THAN MAXVALUE); INSERT INTO t1 VALUES (1, "Partition p1, first row"); DROP TABLE t1; # +# MDEV-10418 Assertion `m_extra_cache' failed +# in ha_partition::late_extra_cache(uint) +# +CREATE TABLE t1 (f1 INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (f2 INT) ENGINE=MyISAM PARTITION BY RANGE(f2) (PARTITION pmax VALUES LESS THAN MAXVALUE); +INSERT INTO t2 VALUES (8); +CREATE ALGORITHM = MERGE VIEW v AS SELECT f2 FROM t2, t1; +UPDATE v SET f2 = 1; +SELECT * FROM t2; +f2 +1 +DROP VIEW v; +DROP TABLE t2; +DROP TABLE t1; +# # bug#11760213-52599: ALTER TABLE REMOVE PARTITIONING ON NON-PARTITIONED # TABLE CORRUPTS MYISAM DROP TABLE if exists `t1`; diff --git a/mysql-test/r/plugin.result b/mysql-test/r/plugin.result index c23c4f2d8a2..f278724cc9a 100644 --- a/mysql-test/r/plugin.result +++ b/mysql-test/r/plugin.result @@ -12,7 +12,7 @@ PLUGIN_STATUS ACTIVE PLUGIN_TYPE STORAGE ENGINE PLUGIN_TYPE_VERSION # PLUGIN_LIBRARY ha_example.so -PLUGIN_LIBRARY_VERSION 1.11 +PLUGIN_LIBRARY_VERSION 1.12 PLUGIN_AUTHOR Brian Aker, MySQL AB PLUGIN_DESCRIPTION Example storage engine PLUGIN_LICENSE GPL @@ -25,7 +25,7 @@ PLUGIN_STATUS ACTIVE PLUGIN_TYPE DAEMON PLUGIN_TYPE_VERSION # PLUGIN_LIBRARY ha_example.so -PLUGIN_LIBRARY_VERSION 1.11 +PLUGIN_LIBRARY_VERSION 1.12 PLUGIN_AUTHOR Sergei Golubchik PLUGIN_DESCRIPTION Unusable Daemon PLUGIN_LICENSE GPL @@ -64,7 +64,7 @@ PLUGIN_STATUS DELETED PLUGIN_TYPE STORAGE ENGINE PLUGIN_TYPE_VERSION # PLUGIN_LIBRARY ha_example.so -PLUGIN_LIBRARY_VERSION 1.11 +PLUGIN_LIBRARY_VERSION 1.12 PLUGIN_AUTHOR Brian Aker, MySQL AB PLUGIN_DESCRIPTION Example storage engine PLUGIN_LICENSE GPL diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index eb21f8ed7ea..40746d2e6c6 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -1208,12 +1208,21 @@ prepare my_stmt from @aux; execute my_stmt; COUNT(*) 46 +Warnings: +Warning 1286 Unknown storage engine 'InnoDB' +Warning 1286 Unknown storage engine 'InnoDB' execute my_stmt; COUNT(*) 46 +Warnings: +Warning 1286 Unknown storage engine 'InnoDB' +Warning 1286 Unknown storage engine 'InnoDB' execute my_stmt; COUNT(*) 46 +Warnings: +Warning 1286 Unknown storage engine 'InnoDB' +Warning 1286 Unknown storage engine 'InnoDB' deallocate prepare my_stmt; drop procedure if exists p1| drop table if exists t1| @@ -3930,8 +3939,6 @@ c1 c2 count(c3) 2012-03-01 01:00:00 3 1 2012-03-01 02:00:00 3 1 DEALLOCATE PREPARE s1; -# -# End of 5.5 tests. prepare stmt from "select date('2010-10-10') between '2010-09-09' and ?"; set @a='2010-11-11'; execute stmt using @a; @@ -4123,6 +4130,78 @@ NULL NULL deallocate prepare stmt; drop table t1,t2,t3,t4; +# +# MDEV-11859: the plans for the first and the second executions +# of PS are not the same +# +create table t1 (id int, c varchar(3), key idx(c))engine=myisam; +insert into t1 values (3,'bar'), (1,'xxx'), (2,'foo'), (5,'yyy'); +prepare stmt1 from +"explain extended + select * from t1 where (1, 2) in ( select 3, 4 ) or c = 'foo'"; +execute stmt1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ref idx idx 6 const 1 100.00 Using index condition +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`c` = 'foo' +execute stmt1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ref idx idx 6 const 1 100.00 Using index condition +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`c` AS `c` from `test`.`t1` where `test`.`t1`.`c` = 'foo' +deallocate prepare stmt1; +prepare stmt1 from +"select * from t1 where (1, 2) in ( select 3, 4 ) or c = 'foo'"; +flush status; +execute stmt1; +id c +2 foo +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +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 +flush status; +execute stmt1; +id c +2 foo +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +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 +deallocate prepare stmt1; +prepare stmt2 from +"explain extended + select * from t1 where (1, 2) in ( select 3, 4 )"; +execute stmt2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`c` AS `c` from `test`.`t1` where 0 +execute stmt2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`c` AS `c` from `test`.`t1` where 0 +deallocate prepare stmt2; +drop table t1; # End of 5.5 tests # # Start of 10.2 tests diff --git a/mysql-test/r/range_vs_index_merge.result b/mysql-test/r/range_vs_index_merge.result index 9af359a55f3..6813c40a5cf 100644 --- a/mysql-test/r/range_vs_index_merge.result +++ b/mysql-test/r/range_vs_index_merge.result @@ -60,11 +60,11 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM City WHERE Population > 100000 AND Name LIKE 'Aba%' OR -Country IN ('CAN', 'ARG') AND ID < 3800 OR -Country < 'U' AND Name LIKE 'Zhu%' OR -ID BETWEEN 3800 AND 3810; +Country IN ('CAN', 'ARG') AND ID BETWEEN 120 AND 130 OR +Country <= 'ALB' AND Name LIKE 'L%' OR +ID BETWEEN 3807 AND 3810; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge PRIMARY,Population,Country,Name Name,Country,PRIMARY 35,3,4 NULL 132 Using sort_union(Name,Country,PRIMARY); Using where +1 SIMPLE City index_merge PRIMARY,Population,Country,Name Name,PRIMARY,Country 35,4,3 NULL 31 Using sort_union(Name,PRIMARY,Country); Using where EXPLAIN SELECT * FROM City WHERE (Population > 101000 AND Population < 115000); @@ -1769,4 +1769,42 @@ a b 167 9999 168 10000 DROP TABLE t1; +# +# MDEV-8603: Wrong result OR/AND condition over index fields +# +CREATE TABLE t1 ( +id INT NOT NULL, +state VARCHAR(64), +capital VARCHAR(64), +UNIQUE KEY (id), +KEY state (state,id), +KEY capital (capital, id) +); +INSERT INTO t1 VALUES +(1,'Arizona','Phoenix'), +(2,'Hawaii','Honolulu'), +(3,'Georgia','Atlanta'), +(4,'Florida','Tallahassee'), +(5,'Alaska','Juneau'), +(6,'Michigan','Lansing'), +(7,'Pennsylvania','Harrisburg'), +(8,'Virginia','Richmond') +; +EXPLAIN +SELECT * FROM t1 FORCE KEY (state,capital) +WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 +OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range state,capital state 71 NULL 12 Using index condition; Using where +SELECT * FROM t1 FORCE KEY (state,capital) +WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 +OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; +id state capital +4 Florida Tallahassee +3 Georgia Atlanta +2 Hawaii Honolulu +6 Michigan Lansing +7 Pennsylvania Harrisburg +8 Virginia Richmond +DROP TABLE t1; set session optimizer_switch='index_merge_sort_intersection=default'; diff --git a/mysql-test/r/range_vs_index_merge_innodb.result b/mysql-test/r/range_vs_index_merge_innodb.result index 225d341f1de..0c3b682b197 100644 --- a/mysql-test/r/range_vs_index_merge_innodb.result +++ b/mysql-test/r/range_vs_index_merge_innodb.result @@ -61,11 +61,11 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM City WHERE Population > 100000 AND Name LIKE 'Aba%' OR -Country IN ('CAN', 'ARG') AND ID < 3800 OR -Country < 'U' AND Name LIKE 'Zhu%' OR -ID BETWEEN 3800 AND 3810; +Country IN ('CAN', 'ARG') AND ID BETWEEN 120 AND 130 OR +Country <= 'ALB' AND Name LIKE 'L%' OR +ID BETWEEN 3807 AND 3810; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City index_merge PRIMARY,Population,Country,Name Name,Country,PRIMARY 35,7,4 NULL 125 Using sort_union(Name,Country,PRIMARY); Using where +1 SIMPLE City index_merge PRIMARY,Population,Country,Name Name,Country,PRIMARY 35,3,4 NULL 32 Using sort_union(Name,Country,PRIMARY); Using where EXPLAIN SELECT * FROM City WHERE (Population > 101000 AND Population < 115000); @@ -369,7 +369,7 @@ WHERE ((ID < 200) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG'))) OR ((ID BETWEEN 100 AND 200) AND (Name LIKE 'Pa%' OR (Population > 103000 AND Population < 104000))); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY,Population,Country,Name PRIMARY 4 NULL 200 Using where +1 SIMPLE City index_merge PRIMARY,Population,Country,Name Name,Population,PRIMARY 39,4,4 NULL 307 Using sort_union(Name,Population,PRIMARY); Using where SELECT * FROM City USE INDEX () WHERE ((ID < 10) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG'))) OR ((ID BETWEEN 100 AND 110) AND @@ -1770,5 +1770,43 @@ a b 167 9999 168 10000 DROP TABLE t1; +# +# MDEV-8603: Wrong result OR/AND condition over index fields +# +CREATE TABLE t1 ( +id INT NOT NULL, +state VARCHAR(64), +capital VARCHAR(64), +UNIQUE KEY (id), +KEY state (state,id), +KEY capital (capital, id) +); +INSERT INTO t1 VALUES +(1,'Arizona','Phoenix'), +(2,'Hawaii','Honolulu'), +(3,'Georgia','Atlanta'), +(4,'Florida','Tallahassee'), +(5,'Alaska','Juneau'), +(6,'Michigan','Lansing'), +(7,'Pennsylvania','Harrisburg'), +(8,'Virginia','Richmond') +; +EXPLAIN +SELECT * FROM t1 FORCE KEY (state,capital) +WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 +OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range state,capital state 71 NULL 12 Using index condition; Using where +SELECT * FROM t1 FORCE KEY (state,capital) +WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 +OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; +id state capital +4 Florida Tallahassee +3 Georgia Atlanta +2 Hawaii Honolulu +6 Michigan Lansing +7 Pennsylvania Harrisburg +8 Virginia Richmond +DROP TABLE t1; set session optimizer_switch='index_merge_sort_intersection=default'; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/r/repair_symlink-5543.result b/mysql-test/r/repair_symlink-5543.result index 98ded32686e..c77e7162a51 100644 --- a/mysql-test/r/repair_symlink-5543.result +++ b/mysql-test/r/repair_symlink-5543.result @@ -1,13 +1,18 @@ create table t1 (a int) engine=myisam data directory='MYSQL_TMP_DIR'; insert t1 values (1); +# Some systems fail with errcode 40, when doing openat, while others +# don't have openat and fail with errcode 20. repair table t1; Table Op Msg_type Msg_text -test.t1 repair status OK +test.t1 repair error 20 for record at pos 0 +test.t1 repair Error File 'MYSQL_TMP_DIR/t1.MYD' not found (Errcode: 20 "<errmsg>") +test.t1 repair status Operation failed drop table t1; create table t2 (a int) engine=aria data directory='MYSQL_TMP_DIR'; insert t2 values (1); repair table t2; Table Op Msg_type Msg_text -test.t2 repair status OK +test.t2 repair error 20 for record at pos 256 +test.t2 repair Error File 'MYSQL_TMP_DIR/t2.MAD' not found (Errcode: 20 "<errmsg>") +test.t2 repair status Operation failed drop table t2; -foobar5543 diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index a39ca3379c6..90216c6cedc 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -2956,7 +2956,7 @@ insert into t1 values (1,'x',5); select * from t1 natural join v1; s1 s2 s3 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'x' +Warning 1292 Truncated incorrect DECIMAL value: 'x' drop table t1; drop view v1; create table t1(a1 int); diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result index 482fca0d530..054aa94763a 100644 --- a/mysql-test/r/select_jcl6.result +++ b/mysql-test/r/select_jcl6.result @@ -2967,7 +2967,7 @@ insert into t1 values (1,'x',5); select * from t1 natural join v1; s1 s2 s3 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'x' +Warning 1292 Truncated incorrect DECIMAL value: 'x' drop table t1; drop view v1; create table t1(a1 int); diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result index a39ca3379c6..90216c6cedc 100644 --- a/mysql-test/r/select_pkeycache.result +++ b/mysql-test/r/select_pkeycache.result @@ -2956,7 +2956,7 @@ insert into t1 values (1,'x',5); select * from t1 natural join v1; s1 s2 s3 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'x' +Warning 1292 Truncated incorrect DECIMAL value: 'x' drop table t1; drop view v1; create table t1(a1 int); diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index a843b9a5f95..47adafa93ba 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -7963,6 +7963,44 @@ set global table_open_cache= @tmp_toc; set global table_definition_cache= @tmp_tdc; drop procedure p1; drop table t1,t2,t3,t4,t5,t6; +# +# MDEV-11935: Queries in stored procedures with and +# EXISTS(SELECT * FROM VIEW) crashes and closes hte conneciton. +# +CREATE TABLE ANY_TABLE ( +ENTITY_UID BIGINT NOT NULL +); +CREATE TABLE SECURITY_PATH( +origid BIGINT UNSIGNED NOT NULL, +destid BIGINT UNSIGNED NOT NULL, +KEY (destid) +); +CREATE VIEW ENTITY_ACCESS ( +ENTITY_UID, +OWNER_UID +) AS +SELECT SP1.origid, +SP2.destid +FROM SECURITY_PATH SP1 +JOIN SECURITY_PATH SP2 ON SP1.destid = SP2.origid +; +CREATE PROCEDURE SP_EXAMPLE_SELECT () +BEGIN +SELECT * +FROM ANY_TABLE AT1 +WHERE EXISTS ( SELECT * +FROM ENTITY_ACCESS EA +WHERE AT1.ENTITY_UID = EA.ENTITY_UID +AND EA.OWNER_UID IS NULL ); +END +// +CALL SP_EXAMPLE_SELECT (); +ENTITY_UID +CALL SP_EXAMPLE_SELECT (); +ENTITY_UID +drop procedure SP_EXAMPLE_SELECT; +drop view ENTITY_ACCESS; +drop table ANY_TABLE, SECURITY_PATH; # End of 10.0 test CREATE FUNCTION f(f1 VARCHAR(64) COLLATE latin1_german2_ci) RETURNS VARCHAR(64) diff --git a/mysql-test/r/stat_tables_par.result b/mysql-test/r/stat_tables_par.result index b965e289e1d..006df89f358 100644 --- a/mysql-test/r/stat_tables_par.result +++ b/mysql-test/r/stat_tables_par.result @@ -46,14 +46,14 @@ dbt3_s001 supplier PRIMARY 1 1.0000 dbt3_s001 supplier i_s_nationkey 1 1.1111 flush table lineitem; set use_stat_tables='never'; -select sum(l_extendedprice*l_discount) as revenue +select round(sum(l_extendedprice*l_discount),4) as revenue from lineitem where l_shipdate >= date '1994-01-01' and l_shipdate < date '1994-01-01' + interval '1' year and l_discount between 0.06 - 0.01 and 0.06 + 0.01 and l_quantity < 24; revenue -77949.91860000002 +77949.9186 connect con1, localhost, root,,; connect con2, localhost, root,,; connection con1; @@ -61,7 +61,7 @@ set debug_sync='statistics_mem_alloc_start1 WAIT_FOR second_thread_started_too' set debug_sync='statistics_mem_alloc_start2 SIGNAL first_thread_working'; use dbt3_s001; set use_stat_tables='preferably'; -select sum(l_extendedprice*l_discount) as revenue +select round(sum(l_extendedprice*l_discount),4) as revenue from lineitem where l_shipdate >= date '1994-01-01' and l_shipdate < date '1994-01-01' + interval '1' year @@ -72,7 +72,7 @@ set debug_sync='statistics_mem_alloc_start1 SIGNAL second_thread_started_too'; set debug_sync='statistics_mem_alloc_start2 WAIT_FOR first_thread_working'; use dbt3_s001; set use_stat_tables='preferably'; -select sum(l_extendedprice*l_discount) as revenue +select round(sum(l_extendedprice*l_discount),4) as revenue from lineitem where l_shipdate >= date '1994-01-01' and l_shipdate < date '1994-01-01' + interval '1' year @@ -80,10 +80,10 @@ and l_discount between 0.06 - 0.01 and 0.06 + 0.01 and l_quantity < 24; connection con1; revenue -77949.91860000002 +77949.9186 connection con2; revenue -77949.91860000002 +77949.9186 connection default; set use_stat_tables='preferably'; disconnect con1; diff --git a/mysql-test/r/stat_tables_par_innodb.result b/mysql-test/r/stat_tables_par_innodb.result index 155582c9192..8fc0483d9aa 100644 --- a/mysql-test/r/stat_tables_par_innodb.result +++ b/mysql-test/r/stat_tables_par_innodb.result @@ -49,14 +49,14 @@ dbt3_s001 supplier PRIMARY 1 1.0000 dbt3_s001 supplier i_s_nationkey 1 1.1111 flush table lineitem; set use_stat_tables='never'; -select sum(l_extendedprice*l_discount) as revenue +select round(sum(l_extendedprice*l_discount),4) as revenue from lineitem where l_shipdate >= date '1994-01-01' and l_shipdate < date '1994-01-01' + interval '1' year and l_discount between 0.06 - 0.01 and 0.06 + 0.01 and l_quantity < 24; revenue -77949.91860000002 +77949.9186 connect con1, localhost, root,,; connect con2, localhost, root,,; connection con1; @@ -64,7 +64,7 @@ set debug_sync='statistics_mem_alloc_start1 WAIT_FOR second_thread_started_too' set debug_sync='statistics_mem_alloc_start2 SIGNAL first_thread_working'; use dbt3_s001; set use_stat_tables='preferably'; -select sum(l_extendedprice*l_discount) as revenue +select round(sum(l_extendedprice*l_discount),4) as revenue from lineitem where l_shipdate >= date '1994-01-01' and l_shipdate < date '1994-01-01' + interval '1' year @@ -75,7 +75,7 @@ set debug_sync='statistics_mem_alloc_start1 SIGNAL second_thread_started_too'; set debug_sync='statistics_mem_alloc_start2 WAIT_FOR first_thread_working'; use dbt3_s001; set use_stat_tables='preferably'; -select sum(l_extendedprice*l_discount) as revenue +select round(sum(l_extendedprice*l_discount),4) as revenue from lineitem where l_shipdate >= date '1994-01-01' and l_shipdate < date '1994-01-01' + interval '1' year @@ -83,10 +83,10 @@ and l_discount between 0.06 - 0.01 and 0.06 + 0.01 and l_quantity < 24; connection con1; revenue -77949.91860000002 +77949.9186 connection con2; revenue -77949.91860000002 +77949.9186 connection default; set use_stat_tables='preferably'; disconnect con1; diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 6bbb80c0662..3d0dfd189a8 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -2440,6 +2440,53 @@ EXECUTE stmt; i 6 drop table t1, t2, t3; +# +# MDEV-11078: NULL NOT IN (non-empty subquery) should never return results +# +create table t1(a int,b int); +create table t2(a int,b int); +insert into t1 value (1,2); +select (NULL) in (select 1 from t1); +(NULL) in (select 1 from t1) +NULL +select (null) in (select 1 from t2); +(null) in (select 1 from t2) +0 +select 1 in (select 1 from t1); +1 in (select 1 from t1) +1 +select 1 in (select 1 from t2); +1 in (select 1 from t2) +0 +select 1 from dual where null in (select 1 from t1); +1 +select 1 from dual where null in (select 1 from t2); +1 +select (null,null) in (select * from t1); +(null,null) in (select * from t1) +NULL +select (null,null) in (select * from t2); +(null,null) in (select * from t2) +0 +select 1 from dual where null not in (select 1 from t1); +1 +select 1 from dual where null not in (select 1 from t2); +1 +1 +drop table t1,t2; +# +# MDEV-6486: Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' +# failed with SELECT SQ, TEXT field +# +CREATE TABLE t1 (a VARCHAR(8), KEY(a)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('foo'),( 'bar'); +CREATE TABLE t2 (b VARCHAR(8), c TINYTEXT, KEY(b)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('baz','baz'),('qux', 'qux'); +SELECT ( SELECT COUNT(*) FROM t1 WHERE a = c ) AS field, COUNT(DISTINCT c) +FROM t2 WHERE b <= 'quux' GROUP BY field; +field COUNT(DISTINCT c) +0 1 +drop table t1,t2; SET optimizer_switch= @@global.optimizer_switch; set @@tmp_table_size= @@global.tmp_table_size; # diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result index cfbe5d41418..11221c797ff 100644 --- a/mysql-test/r/subselect_innodb.result +++ b/mysql-test/r/subselect_innodb.result @@ -494,6 +494,21 @@ HAVING SQ2_alias1 . col_int_key >= 7 drop table t1; set optimizer_switch=@subselect_innodb_tmp; # +# MDEV-9635:Server crashes in part_of_refkey or assertion +# `!created && key_to_save < (int)s->keys' failed in +# TABLE::use_index(int) or with join_cache_level>2 +# +SET join_cache_level=3; +CREATE TABLE t1 (f1 VARCHAR(1024)) ENGINE=InnoDB; +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; +CREATE TABLE t2 (f2 VARCHAR(4)) ENGINE=InnoDB; +INSERT INTO t2 VALUES ('foo'),('bar'); +SELECT * FROM v1, t2 WHERE ( f1, f2 ) IN ( SELECT f1, f1 FROM t1 ); +f1 f2 +set join_cache_level = default; +drop view v1; +drop table t1,t2; +# # MDEV-6041: ORDER BY+subqueries: subquery_table.key=outer_table.col is not recongized as binding # create table t1(a int) engine=innodb; diff --git a/mysql-test/r/symlink-aria-11902.result b/mysql-test/r/symlink-aria-11902.result new file mode 100644 index 00000000000..4d8f179dac9 --- /dev/null +++ b/mysql-test/r/symlink-aria-11902.result @@ -0,0 +1,43 @@ +set default_storage_engine=Aria; +call mtr.add_suppression("File.*t1.* not found"); +create table mysql.t1 (a int, b char(16), index(a)); +insert mysql.t1 values (100, 'test'),(101,'test'); +create table t1 (a int, b char(16), index(a)) +data directory="MYSQLTEST_VARDIR/tmp/foo"; +insert t1 values (200, 'some'),(201,'some'); +select * from t1; +a b +200 some +201 some +flush tables; +set debug_sync='mi_open_datafile SIGNAL ok WAIT_FOR go'; +select * from t1; +connect con1, localhost, root; +set debug_sync='now WAIT_FOR ok'; +set debug_sync='now SIGNAL go'; +connection default; +ERROR HY000: File 'MYSQLTEST_VARDIR/tmp/foo/t1.MAD' not found (Errcode: 20 <errmsg>) +flush tables; +drop table if exists t1; +create table t1 (a int, b char(16), index (a)) +index directory="MYSQLTEST_VARDIR/tmp/foo"; +insert t1 values (200, 'some'),(201,'some'); +explain select a from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL a 5 NULL 2 Using index +select a from t1; +a +200 +201 +flush tables; +set debug_sync='mi_open_kfile SIGNAL waiting WAIT_FOR run'; +select a from t1; +connection con1; +set debug_sync='now WAIT_FOR waiting'; +set debug_sync='now SIGNAL run'; +connection default; +ERROR HY000: Can't find file: './test/t1.MAI' (errno: 20 <errmsg>) +flush tables; +drop table if exists t1; +drop table mysql.t1; +set debug_sync='RESET'; diff --git a/mysql-test/r/symlink-myisam-11902.result b/mysql-test/r/symlink-myisam-11902.result new file mode 100644 index 00000000000..bc9a0316bab --- /dev/null +++ b/mysql-test/r/symlink-myisam-11902.result @@ -0,0 +1,42 @@ +call mtr.add_suppression("File.*t1.* not found"); +create table mysql.t1 (a int, b char(16), index(a)); +insert mysql.t1 values (100, 'test'),(101,'test'); +create table t1 (a int, b char(16), index(a)) +data directory="MYSQLTEST_VARDIR/tmp/foo"; +insert t1 values (200, 'some'),(201,'some'); +select * from t1; +a b +200 some +201 some +flush tables; +set debug_sync='mi_open_datafile SIGNAL ok WAIT_FOR go'; +select * from t1; +connect con1, localhost, root; +set debug_sync='now WAIT_FOR ok'; +set debug_sync='now SIGNAL go'; +connection default; +ERROR HY000: File 'MYSQLTEST_VARDIR/tmp/foo/t1.MYD' not found (Errcode: 20 <errmsg>) +flush tables; +drop table if exists t1; +create table t1 (a int, b char(16), index (a)) +index directory="MYSQLTEST_VARDIR/tmp/foo"; +insert t1 values (200, 'some'),(201,'some'); +explain select a from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL a 5 NULL 2 Using index +select a from t1; +a +200 +201 +flush tables; +set debug_sync='mi_open_kfile SIGNAL waiting WAIT_FOR run'; +select a from t1; +connection con1; +set debug_sync='now WAIT_FOR waiting'; +set debug_sync='now SIGNAL run'; +connection default; +ERROR HY000: Can't find file: './test/t1.MYI' (errno: 20 <errmsg>) +flush tables; +drop table if exists t1; +drop table mysql.t1; +set debug_sync='RESET'; diff --git a/mysql-test/r/table_elim.result b/mysql-test/r/table_elim.result index a7b0842d14f..764a1b2780e 100644 --- a/mysql-test/r/table_elim.result +++ b/mysql-test/r/table_elim.result @@ -597,7 +597,8 @@ CREATE TABLE t1 (a int(11), b varchar(1)) ; INSERT IGNORE INTO t1 VALUES (0,'g'); CREATE TABLE t3 ( a varchar(1)) ; INSERT IGNORE INTO t3 VALUES ('g'); -CREATE TABLE t2 ( a int(11) NOT NULL, PRIMARY KEY (a)) ; +CREATE TABLE t2 ( a int(11) NOT NULL, PRIMARY KEY (a)); +INSERT INTO t2 VALUES (9), (10); create view v1 as SELECT t1.* FROM t1 LEFT JOIN t2 ON ( t1.a = t2.a ) WHERE t2.a <> 0; SELECT alias1.* FROM t3 LEFT JOIN v1 as alias1 ON ( t3.a = alias1.b ); a b @@ -606,7 +607,7 @@ EXPLAIN SELECT alias1.* FROM t3 LEFT JOIN v1 as alias1 ON ( t3.a = alias1.b ); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 system NULL NULL NULL NULL 1 1 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using where -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where; Using index +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index drop view v1; DROP TABLE t1,t2,t3; # diff --git a/mysql-test/r/trigger_no_defaults-11698.result b/mysql-test/r/trigger_no_defaults-11698.result index 40546cee41d..8ce495eaf3a 100644 --- a/mysql-test/r/trigger_no_defaults-11698.result +++ b/mysql-test/r/trigger_no_defaults-11698.result @@ -20,3 +20,23 @@ a b 10 10 0 30 drop table t1; +set sql_mode=default; +set sql_mode=''; +create table t1 ( +id int(11) not null auto_increment primary key, +data1 varchar(10) not null, +data2 varchar(10) not null +); +insert into t1 (data2) values ('x'); +Warnings: +Warning 1364 Field 'data1' doesn't have a default value +create trigger test_trigger before insert on t1 for each row begin end; +insert into t1 (data2) values ('y'); +Warnings: +Warning 1364 Field 'data1' doesn't have a default value +select * from t1; +id data1 data2 +1 x +2 y +drop table t1; +set sql_mode=default; diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result index 313894b4b77..ab0e2dbc71d 100644 --- a/mysql-test/r/type_newdecimal.result +++ b/mysql-test/r/type_newdecimal.result @@ -2239,11 +2239,50 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; # +# Bug#18408499 UNSIGNED BIGINT HIGH VALUES +# WRONG NUMERICAL COMPARISON RESULTS +# +CREATE TABLE t1(value DECIMAL(24,0) NOT NULL); +INSERT INTO t1(value) +VALUES('100000000000000000000001'), +('100000000000000000000002'), +('100000000000000000000003'); +SELECT * FROM t1 WHERE value = '100000000000000000000002'; +value +100000000000000000000002 +SELECT * FROM t1 WHERE '100000000000000000000002' = value; +value +100000000000000000000002 +SELECT * FROM t1 WHERE value + 0 = '100000000000000000000002'; +value +100000000000000000000002 +SELECT * FROM t1 WHERE value = 100000000000000000000002; +value +100000000000000000000002 +SELECT * FROM t1 WHERE value + 0 = 100000000000000000000002; +value +100000000000000000000002 +PREPARE stmt FROM 'SELECT * FROM t1 WHERE value = ?'; +set @a="100000000000000000000002"; +EXECUTE stmt using @a; +value +100000000000000000000002 +set @a=100000000000000000000002; +EXECUTE stmt using @a; +value +100000000000000000000002 +DEALLOCATE PREPARE stmt; +ALTER TABLE t1 ADD INDEX value (value); +SELECT * FROM t1 WHERE value = '100000000000000000000002'; +value +100000000000000000000002 +DROP TABLE t1; +# # End of 10.1 tests # # # Test CREATE .. SELECT - +# create or replace table t1 as select 1.000000000000000000000000000000000 as a; show create table t1; Table Create Table diff --git a/mysql-test/r/type_num.result b/mysql-test/r/type_num.result index 9573852ce72..193a59741f5 100644 --- a/mysql-test/r/type_num.result +++ b/mysql-test/r/type_num.result @@ -570,7 +570,7 @@ SELECT COUNT(*) FROM t1 WHERE d='1 '; COUNT(*) 2 Warnings: -Note 1292 Truncated incorrect DOUBLE value: '1 ' +Note 1292 Truncated incorrect DECIMAL value: '1 ' SELECT COUNT(*) FROM t1 WHERE f4=''; COUNT(*) 2 @@ -605,7 +605,7 @@ SELECT COUNT(*) FROM t1 WHERE d=''; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '' +Warning 1292 Truncated incorrect DECIMAL value: '' SELECT COUNT(*) FROM t1 WHERE f4='x'; COUNT(*) 2 @@ -640,7 +640,7 @@ SELECT COUNT(*) FROM t1 WHERE d='x'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'x' +Warning 1292 Truncated incorrect DECIMAL value: 'x' SELECT COUNT(*) FROM t1 WHERE f4=' x'; COUNT(*) 2 @@ -675,7 +675,7 @@ SELECT COUNT(*) FROM t1 WHERE d=' x'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: ' x' +Warning 1292 Truncated incorrect DECIMAL value: ' x' SELECT COUNT(*) FROM t1 WHERE f4='.'; COUNT(*) 2 @@ -710,7 +710,7 @@ SELECT COUNT(*) FROM t1 WHERE d='.'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '.' +Warning 1292 Truncated incorrect DECIMAL value: '.' SELECT COUNT(*) FROM t1 WHERE f4='-'; COUNT(*) 2 @@ -745,7 +745,7 @@ SELECT COUNT(*) FROM t1 WHERE d='-'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '-' +Warning 1292 Truncated incorrect DECIMAL value: '-' SELECT COUNT(*) FROM t1 WHERE f4='+'; COUNT(*) 2 @@ -780,7 +780,7 @@ SELECT COUNT(*) FROM t1 WHERE d='+'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '+' +Warning 1292 Truncated incorrect DECIMAL value: '+' SELECT COUNT(*) FROM t1 WHERE f4='1x'; COUNT(*) 2 @@ -815,7 +815,7 @@ SELECT COUNT(*) FROM t1 WHERE d='1x'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1x' +Warning 1292 Truncated incorrect DECIMAL value: '1x' SELECT COUNT(*) FROM t1 WHERE f4='1e'; COUNT(*) 2 @@ -850,7 +850,7 @@ SELECT COUNT(*) FROM t1 WHERE d='1e'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1e' +Warning 1292 Truncated incorrect DECIMAL value: '1e' SELECT COUNT(*) FROM t1 WHERE f4='1e+'; COUNT(*) 2 @@ -885,7 +885,7 @@ SELECT COUNT(*) FROM t1 WHERE d='1e+'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1e+' +Warning 1292 Truncated incorrect DECIMAL value: '1e+' SELECT COUNT(*) FROM t1 WHERE f4='1E-'; COUNT(*) 2 @@ -920,7 +920,7 @@ SELECT COUNT(*) FROM t1 WHERE d='1E-'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1E-' +Warning 1292 Truncated incorrect DECIMAL value: '1E-' SELECT COUNT(*) FROM t1 WHERE f4='1Ex'; COUNT(*) 2 @@ -955,7 +955,7 @@ SELECT COUNT(*) FROM t1 WHERE d='1Ex'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1Ex' +Warning 1292 Truncated incorrect DECIMAL value: '1Ex' SELECT COUNT(*) FROM t1 WHERE f4='1e+x'; COUNT(*) 2 @@ -990,7 +990,7 @@ SELECT COUNT(*) FROM t1 WHERE d='1e+x'; COUNT(*) 2 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1e+x' +Warning 1292 Truncated incorrect DECIMAL value: '1e+x' SELECT COUNT(*) FROM t1 WHERE f4='1e1000'; COUNT(*) 0 @@ -1025,7 +1025,8 @@ SELECT COUNT(*) FROM t1 WHERE d='1e1000'; COUNT(*) 0 Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '1e1000' +Warning 1916 Got overflow when converting '' to DECIMAL. Value truncated +Warning 1292 Truncated incorrect DECIMAL value: '1e1000' ALTER TABLE t1 ADD KEY f4(f4), ADD KEY f8(f8), diff --git a/mysql-test/r/update_innodb.result b/mysql-test/r/update_innodb.result index 88c86c50625..5fcc584035a 100644 --- a/mysql-test/r/update_innodb.result +++ b/mysql-test/r/update_innodb.result @@ -29,3 +29,29 @@ CREATE ALGORITHM=UNDEFINED VIEW `v1` AS select `t4`.`c1` AS `c1`,`t4`.`c2` AS `c UPDATE t1 a JOIN t2 b ON a.c1 = b.c1 JOIN v1 vw ON b.c2 = vw.c1 JOIN t3 del ON vw.c2 = del.c2 SET a.c2 = ( SELECT max(t.c1) FROM t3 t, v1 i WHERE del.c2 = t.c2 AND vw.c3 = i.c3 AND t.c3 = 4 ) WHERE a.c2 IS NULL OR a.c2 < '2011-05-01'; drop view v1; drop table t1,t2,t3,t4; +# +# MDEV-10232 Scalar result of subquery changes after adding an outer select stmt +# +CREATE TABLE t1 ( +a_id INT(20) UNSIGNED NOT NULL AUTO_INCREMENT, +b_id INT(20) UNSIGNED NULL DEFAULT NULL, +c_id VARCHAR(255) NULL DEFAULT NULL, +PRIMARY KEY (a_id))COLLATE = 'utf8_general_ci' ENGINE = InnoDB; +CREATE TABLE t2 ( +b_id INT(20) UNSIGNED NOT NULL AUTO_INCREMENT, +c_id VARCHAR(255) NULL DEFAULT NULL, +PRIMARY KEY (b_id), +INDEX idx_c_id (c_id))COLLATE = 'utf8_general_ci' ENGINE = InnoDB; +INSERT INTO t1 (b_id, c_id) VALUES (NULL, NULL); +INSERT INTO t2 (c_id) VALUES (NULL); +INSERT INTO t2 (c_id) VALUES (NULL); +SELECT * FROM t1; +a_id b_id c_id +1 NULL NULL +SELECT t2.b_id FROM t1,t2 WHERE t2.c_id = t1.c_id; +b_id +UPDATE t1 SET b_id = (SELECT t2.b_id FROM t2 t2 WHERE t2.c_id = t1.c_id); +SELECT * FROM t1; +a_id b_id c_id +1 NULL NULL +drop table t1,t2; diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 5d8f71ef708..b899695f11f 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -5583,6 +5583,89 @@ 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; +# +# MDEV-12099: usage of mergeable view with LEFT JOIN +# that can be converted to INNER JOIN +# +create table t1 (a int, b int, key(a)) engine=myisam; +insert into t1 values +(3,20), (7,10), (2,10), (4,30), (8,70), +(7,70), (9,100), (9,60), (8,80), (7,60); +create table t2 (c int, d int, key (c)) engine=myisam; +insert into t2 values +(50,100), (20, 200), (10,300), +(150,100), (120, 200), (110,300), +(250,100), (220, 200), (210,300); +create table t3(e int, f int not null, key(e), unique (f)) engine=myisam; +insert into t3 values +(100, 3), (300, 5), (400, 4), (300,7), +(300,2), (600, 13), (800, 15), (700, 14), +(600, 23), (800, 25), (700, 24); +create view v1 as +select * from t2 left join t3 on t3.e=t2.d where t3.f is not null; +select * +from t1 left join v1 on v1.c=t1.b +where t1.a < 5; +a b c d e f +2 10 10 300 300 5 +2 10 10 300 300 7 +2 10 10 300 300 2 +3 20 NULL NULL NULL NULL +4 30 NULL NULL NULL NULL +select * +from t1 left join ( t2 left join t3 on t3.e=t2.d ) +on t2.c=t1.b and t3.f is not null +where t1.a < 5; +a b c d e f +2 10 10 300 300 5 +2 10 10 300 300 7 +2 10 10 300 300 2 +3 20 NULL NULL NULL NULL +4 30 NULL NULL NULL NULL +explain extended +select * +from t1 left join v1 on v1.c=t1.b +where t1.a < 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range a a 5 NULL 3 100.00 Using index condition +1 SIMPLE t2 ref c c 5 test.t1.b 2 100.00 Using where +1 SIMPLE t3 ref f,e e 5 test.t2.d 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t2`.`c` = `test`.`t1`.`b` and `test`.`t3`.`e` = `test`.`t2`.`d` and `test`.`t3`.`f` is not null and `test`.`t1`.`b` is not null and `test`.`t2`.`d` is not null) where `test`.`t1`.`a` < 5 +explain extended +select * +from t1 left join ( t2 left join t3 on t3.e=t2.d ) +on t2.c=t1.b and t3.f is not null +where t1.a < 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range a a 5 NULL 3 100.00 Using index condition +1 SIMPLE t2 ref c c 5 test.t1.b 2 100.00 Using where +1 SIMPLE t3 ref f,e e 5 test.t2.d 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t2`.`c` = `test`.`t1`.`b` and `test`.`t3`.`e` = `test`.`t2`.`d` and `test`.`t3`.`f` is not null and `test`.`t1`.`b` is not null and `test`.`t2`.`d` is not null) where `test`.`t1`.`a` < 5 +explain extended +select * +from t1 left join v1 on v1.c=t1.b and v1.f=t1.a +where t1.a < 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range a a 5 NULL 3 100.00 Using index condition +1 SIMPLE t3 eq_ref f,e f 4 test.t1.a 1 100.00 Using where +1 SIMPLE t2 ref c c 5 test.t1.b 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t2`.`c` = `test`.`t1`.`b` and `test`.`t3`.`f` = `test`.`t1`.`a` and `test`.`t2`.`d` = `test`.`t3`.`e` and `test`.`t1`.`a` is not null and `test`.`t1`.`a` is not null and `test`.`t1`.`b` is not null) where `test`.`t1`.`a` < 5 +explain extended +select * +from t1 left join ( t2 left join t3 on t3.e=t2.d ) +on t2.c=t1.b and t3.f=t1.a and t3.f is not null +where t1.a < 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range a a 5 NULL 3 100.00 Using index condition +1 SIMPLE t3 eq_ref f,e f 4 test.t1.a 1 100.00 Using where +1 SIMPLE t2 ref c c 5 test.t1.b 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d`,`test`.`t3`.`e` AS `e`,`test`.`t3`.`f` AS `f` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t2`.`c` = `test`.`t1`.`b` and `test`.`t3`.`f` = `test`.`t1`.`a` and `test`.`t2`.`d` = `test`.`t3`.`e` and `test`.`t1`.`a` is not null and `test`.`t1`.`a` is not null and `test`.`t1`.`b` is not null) where `test`.`t1`.`a` < 5 +drop view v1; +drop table t1,t2,t3; # ----------------------------------------------------------------- # -- End of 5.5 tests. # ----------------------------------------------------------------- |