diff options
Diffstat (limited to 'mysql-test/main')
59 files changed, 1422 insertions, 95 deletions
diff --git a/mysql-test/main/alter_table.result b/mysql-test/main/alter_table.result index ac6f9aba17b..cb3783eca5a 100644 --- a/mysql-test/main/alter_table.result +++ b/mysql-test/main/alter_table.result @@ -2235,6 +2235,29 @@ t1 CREATE TABLE `t1` ( ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DROP TABLE t1; # +# MDEV-14668 ADD PRIMARY KEY IF NOT EXISTS on composite key +# +CREATE TABLE t1 ( +`ID` BIGINT(20) NOT NULL, +`RANK` MEDIUMINT(4) NOT NULL, +`CHECK_POINT` BIGINT(20) NOT NULL, +UNIQUE INDEX `HORIZON_UIDX01` (`ID`, `RANK`) +) ENGINE=InnoDB; +ALTER TABLE t1 ADD PRIMARY KEY IF NOT EXISTS (`ID`, `CHECK_POINT`); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `ID` bigint(20) NOT NULL, + `RANK` mediumint(4) NOT NULL, + `CHECK_POINT` bigint(20) NOT NULL, + PRIMARY KEY (`ID`,`CHECK_POINT`), + UNIQUE KEY `HORIZON_UIDX01` (`ID`,`RANK`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +ALTER TABLE t1 ADD PRIMARY KEY IF NOT EXISTS (`ID`, `CHECK_POINT`); +Warnings: +Note 1061 Multiple primary key defined +DROP TABLE t1; +# # End of 10.0 tests # # diff --git a/mysql-test/main/alter_table.test b/mysql-test/main/alter_table.test index c9302d588da..5c218c26923 100644 --- a/mysql-test/main/alter_table.test +++ b/mysql-test/main/alter_table.test @@ -1835,6 +1835,21 @@ SHOW CREATE TABLE t1; DROP TABLE t1; --echo # +--echo # MDEV-14668 ADD PRIMARY KEY IF NOT EXISTS on composite key +--echo # +CREATE TABLE t1 ( + `ID` BIGINT(20) NOT NULL, + `RANK` MEDIUMINT(4) NOT NULL, + `CHECK_POINT` BIGINT(20) NOT NULL, + UNIQUE INDEX `HORIZON_UIDX01` (`ID`, `RANK`) + ) ENGINE=InnoDB; + +ALTER TABLE t1 ADD PRIMARY KEY IF NOT EXISTS (`ID`, `CHECK_POINT`); +SHOW CREATE TABLE t1; +ALTER TABLE t1 ADD PRIMARY KEY IF NOT EXISTS (`ID`, `CHECK_POINT`); +DROP TABLE t1; + +--echo # --echo # End of 10.0 tests --echo # diff --git a/mysql-test/main/analyze_stmt_orderby.result b/mysql-test/main/analyze_stmt_orderby.result index dd7bd6b53f7..deb19d4d93a 100644 --- a/mysql-test/main/analyze_stmt_orderby.result +++ b/mysql-test/main/analyze_stmt_orderby.result @@ -306,7 +306,7 @@ ANALYZE "r_rows": 10, "r_total_time_ms": "REPLACED", "filtered": 100, - "r_filtered": 1, + "r_filtered": 100, "attached_condition": "t0.a is not null" } } diff --git a/mysql-test/main/auto_increment.result b/mysql-test/main/auto_increment.result index 660a93b1b30..bd61d73f08c 100644 --- a/mysql-test/main/auto_increment.result +++ b/mysql-test/main/auto_increment.result @@ -538,6 +538,35 @@ pk 1 drop table t1; # +# End of 5.3 tests +# +# +# MDEV-16534 PPC64: Unexpected error with a negative values into auto-increment columns in HEAP, MyISAM, ARIA +# +CREATE TABLE t1 ( +id TINYINT NOT NULL AUTO_INCREMENT, +name CHAR(30) NOT NULL, +PRIMARY KEY (id) +) ENGINE=MyISAM; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` tinyint(4) NOT NULL AUTO_INCREMENT, + `name` char(30) NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 (name) VALUES ('dog'); +UPDATE t1 SET id=-1 WHERE id=1; +INSERT INTO t1 (name) VALUES ('cat'); +SELECT * FROM t1; +id name +-1 dog +2 cat +DROP TABLE t1; +# +# End of 5.5 tests +# +# # MDEV-15352 AUTO_INCREMENT breaks after updating a column value to a negative number # SET @engine='MyISAM'; @@ -657,3 +686,6 @@ id name -1 dog 2 cat DROP PROCEDURE autoinc_mdev15353_one; +# +# End of 10.2 tests +# diff --git a/mysql-test/main/auto_increment.test b/mysql-test/main/auto_increment.test index 6f678ed309f..1c359ec5268 100644 --- a/mysql-test/main/auto_increment.test +++ b/mysql-test/main/auto_increment.test @@ -398,6 +398,29 @@ select last_insert_id(); select * from t1; drop table t1; +--echo # +--echo # End of 5.3 tests +--echo # + +--echo # +--echo # MDEV-16534 PPC64: Unexpected error with a negative values into auto-increment columns in HEAP, MyISAM, ARIA +--echo # + +CREATE TABLE t1 ( + id TINYINT NOT NULL AUTO_INCREMENT, + name CHAR(30) NOT NULL, + PRIMARY KEY (id) +) ENGINE=MyISAM; +SHOW CREATE TABLE t1; +INSERT INTO t1 (name) VALUES ('dog'); +UPDATE t1 SET id=-1 WHERE id=1; +INSERT INTO t1 (name) VALUES ('cat'); +SELECT * FROM t1; +DROP TABLE t1; + +--echo # +--echo # End of 5.5 tests +--echo # --echo # --echo # MDEV-15352 AUTO_INCREMENT breaks after updating a column value to a negative number @@ -405,3 +428,8 @@ drop table t1; SET @engine='MyISAM'; --source include/autoinc_mdev15353.inc + +--echo # +--echo # End of 10.2 tests +--echo # + diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result index 150fe13f7fb..879d837f9f1 100644 --- a/mysql-test/main/cte_nonrecursive.result +++ b/mysql-test/main/cte_nonrecursive.result @@ -1478,3 +1478,37 @@ select 2 as f; f 2 drop table t1; +# +# MDEV-16473: query with CTE when no database is set +# +create database db_mdev_16473; +use db_mdev_16473; +drop database db_mdev_16473; +# Now no default database is set +select database(); +database() +NULL +with cte as (select 1 as a) select * from cte; +a +1 +create database db_mdev_16473; +create table db_mdev_16473.t1 (a int); +insert into db_mdev_16473.t1 values (2), (7), (3), (1); +with cte as (select * from db_mdev_16473.t1) select * from cte; +a +2 +7 +3 +1 +with cte as (select * from db_mdev_16473.t1) +select * from cte, t1 as t where cte.a=t.a; +ERROR 3D000: No database selected +with cte as (select * from db_mdev_16473.t1) +select * from cte, db_mdev_16473.t1 as t where cte.a=t.a; +a a +2 2 +7 7 +3 3 +1 1 +drop database db_mdev_16473; +use test; diff --git a/mysql-test/main/cte_nonrecursive.test b/mysql-test/main/cte_nonrecursive.test index 98a77940c99..11c864bcac1 100644 --- a/mysql-test/main/cte_nonrecursive.test +++ b/mysql-test/main/cte_nonrecursive.test @@ -1029,4 +1029,31 @@ with cte as select 2 as f; drop table t1; -
\ No newline at end of file + +--echo # +--echo # MDEV-16473: query with CTE when no database is set +--echo # + +create database db_mdev_16473; +use db_mdev_16473; +drop database db_mdev_16473; + +--echo # Now no default database is set +select database(); + +with cte as (select 1 as a) select * from cte; + +create database db_mdev_16473; +create table db_mdev_16473.t1 (a int); +insert into db_mdev_16473.t1 values (2), (7), (3), (1); +with cte as (select * from db_mdev_16473.t1) select * from cte; + +--error ER_NO_DB_ERROR +with cte as (select * from db_mdev_16473.t1) +select * from cte, t1 as t where cte.a=t.a; +with cte as (select * from db_mdev_16473.t1) +select * from cte, db_mdev_16473.t1 as t where cte.a=t.a; + +drop database db_mdev_16473; + +use test; diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 487cb9add1f..0cd8b4cb844 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -13402,6 +13402,145 @@ EXPLAIN } DROP TABLE t1; # +# MDEV-16517: pushdown condition with the IN predicate defined +# with non-constant values +# +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,2),(1,3); +SELECT * FROM +( +SELECT t1.a +FROM t1 +WHERE 1 IN (0,t1.a) +GROUP BY t1.a +) AS dt1 +JOIN +( +SELECT t1.a +FROM t1 +WHERE 1 IN (0,t1.a) +) AS dt2 +ON dt1.a = dt2.a; +a a +1 1 +1 1 +EXPLAIN FORMAT=JSON SELECT * FROM +( +SELECT t1.a +FROM t1 +WHERE 1 IN (0,t1.a) +GROUP BY t1.a +) AS dt1 +JOIN +( +SELECT t1.a +FROM t1 +WHERE 1 IN (0,t1.a) +) AS dt2 +ON dt1.a = dt2.a; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "attached_condition": "1 in (0,dt1.a)", + "materialized": { + "query_block": { + "select_id": 2, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "attached_condition": "1 in (0,t1.a) and 1 in (0,t1.a)" + } + } + } + } + } + }, + "block-nl-join": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 2, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "256Kb", + "join_type": "BNL", + "attached_condition": "t1.a = dt1.a" + } + } +} +SELECT * FROM +( +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +) AS dt, t1 +WHERE dt.a=t1.a AND dt.a IN (1,t1.a); +a MAX(t1.b) a b +1 3 1 2 +1 3 1 3 +EXPLAIN FORMAT=JSON SELECT * FROM +( +SELECT t1.a,MAX(t1.b) +FROM t1 +GROUP BY t1.a +) AS dt, t1 +WHERE dt.a=t1.a AND dt.a IN (1,t1.a); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "attached_condition": "dt.a in (1,dt.a)", + "materialized": { + "query_block": { + "select_id": 2, + "filesort": { + "sort_key": "t1.a", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "attached_condition": "t1.a in (1,t1.a)" + } + } + } + } + } + }, + "block-nl-join": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 2, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "256Kb", + "join_type": "BNL", + "attached_condition": "t1.a = dt.a" + } + } +} +DROP TABLE t1; +# # MDEV-10855: Pushdown into derived with window functions # set @save_optimizer_switch= @@optimizer_switch; diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test index 4a908a49add..e51646a9764 100644 --- a/mysql-test/main/derived_cond_pushdown.test +++ b/mysql-test/main/derived_cond_pushdown.test @@ -2176,7 +2176,7 @@ CREATE TABLE t2 (x INT, y INT, z INT); INSERT INTO t1 VALUES (1,1,66,1), (1,1,56,2), (3,2,42,3); INSERT INTO t2 VALUES (1,1,66), (1,12,32); -LET $query= +let $query= SELECT * FROM t2, ( @@ -2187,11 +2187,11 @@ FROM t2, ) AS v1 WHERE (v1.a=1) AND (v1.b=v1.a) AND (v1.a=t2.x) AND (v1.max_c>30); -EVAL $query; -EVAL EXPLAIN $query; -EVAL EXPLAIN FORMAT=JSON $query; +eval $query; +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; -LET $query= +let $query= SELECT * FROM t2, ( @@ -2202,9 +2202,9 @@ FROM t2, ) AS v1 WHERE (v1.a=1) AND (v1.b=v1.a) AND (v1.b=v1.d) AND (v1.a=t2.x) AND (v1.max_c>30); -EVAL $query; -EVAL EXPLAIN $query; -EVAL EXPLAIN FORMAT=JSON $query; +eval $query; +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; DROP TABLE t1,t2; @@ -2230,7 +2230,7 @@ CREATE TABLE t2 (e INT, f INT, g INT); INSERT INTO t1 VALUES (1,14),(2,13),(1,19),(2,32),(3,24); INSERT INTO t2 VALUES (1,19,2),(3,24,1),(1,12,2),(3,11,3),(2,32,1); -LET $query= +let $query= SELECT * FROM t1 WHERE (t1.a,t1.b) IN ( @@ -2244,11 +2244,11 @@ WHERE (t1.a,t1.b) IN WHERE d_tab.e>1 ) ; -EVAL $query; -EVAL EXPLAIN $query; -EVAL EXPLAIN FORMAT=JSON $query; +eval $query; +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; -LET $query= +let $query= SELECT * FROM t1 WHERE (t1.a,t1.b) IN ( @@ -2262,11 +2262,11 @@ WHERE (t1.a,t1.b) IN WHERE d_tab.max_f<25 ) ; -EVAL $query; -EVAL EXPLAIN $query; -EVAL EXPLAIN FORMAT=JSON $query; +eval $query; +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; -LET $query= +let $query= SELECT * FROM t1 WHERE (t1.a,t1.b) IN ( @@ -2280,11 +2280,11 @@ WHERE (t1.a,t1.b) IN GROUP BY d_tab.g ) ; -EVAL $query; -EVAL EXPLAIN $query; -EVAL EXPLAIN FORMAT=JSON $query; +eval $query; +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; -LET $query= +let $query= SELECT * FROM t1 WHERE (t1.a,t1.b) IN ( @@ -2298,9 +2298,9 @@ WHERE (t1.a,t1.b) IN GROUP BY d_tab.g ) ; -EVAL $query; -EVAL EXPLAIN $query; -EVAL EXPLAIN FORMAT=JSON $query; +eval $query; +eval EXPLAIN $query; +eval EXPLAIN FORMAT=JSON $query; DROP TABLE t1,t2; @@ -2329,7 +2329,7 @@ CREATE TABLE t1 (a INT, b INT); INSERT INTO t1 VALUES (1,2),(2,3),(3,4); -LET $query= +let $query= SELECT * FROM ( SELECT t1.b AS a @@ -2337,10 +2337,10 @@ SELECT * FROM GROUP BY t1.a ) dt WHERE (dt.a=2); -EVAL $query; -EVAL EXPLAIN FORMAT=JSON $query; +eval $query; +eval EXPLAIN FORMAT=JSON $query; -LET $query= +let $query= SELECT * FROM ( SELECT t1.b AS a @@ -2349,10 +2349,10 @@ SELECT * FROM HAVING (t1.a<3) ) dt WHERE (dt.a>1); -EVAL $query; -EVAL EXPLAIN FORMAT=JSON $query; +eval $query; +eval EXPLAIN FORMAT=JSON $query; -LET $query= +let $query= SELECT * FROM ( SELECT 'ab' AS a @@ -2360,10 +2360,10 @@ SELECT * FROM GROUP BY t1.a ) dt WHERE (dt.a='ab'); -EVAL $query; -EVAL EXPLAIN FORMAT=JSON $query; +eval $query; +eval EXPLAIN FORMAT=JSON $query; -LET $query= +let $query= SELECT * FROM ( SELECT 1 AS a @@ -2371,9 +2371,47 @@ SELECT * FROM GROUP BY t1.a ) dt WHERE (dt.a=1); -EVAL $query; -EVAL EXPLAIN FORMAT=JSON $query; +eval $query; +eval EXPLAIN FORMAT=JSON $query; + +DROP TABLE t1; + +--echo # +--echo # MDEV-16517: pushdown condition with the IN predicate defined +--echo # with non-constant values +--echo # + +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,2),(1,3); +let $query= +SELECT * FROM +( + SELECT t1.a + FROM t1 + WHERE 1 IN (0,t1.a) + GROUP BY t1.a +) AS dt1 +JOIN +( + SELECT t1.a + FROM t1 + WHERE 1 IN (0,t1.a) +) AS dt2 +ON dt1.a = dt2.a; +eval $query; +eval EXPLAIN FORMAT=JSON $query; + +let $query= +SELECT * FROM +( + SELECT t1.a,MAX(t1.b) + FROM t1 + GROUP BY t1.a +) AS dt, t1 +WHERE dt.a=t1.a AND dt.a IN (1,t1.a); +eval $query; +eval EXPLAIN FORMAT=JSON $query; DROP TABLE t1; # Start of 10.3 tests diff --git a/mysql-test/main/func_json.result b/mysql-test/main/func_json.result index 9b41c1f9d61..5fa5d7c628c 100644 --- a/mysql-test/main/func_json.result +++ b/mysql-test/main/func_json.result @@ -739,6 +739,14 @@ drop table t1; select json_extract('{"test":8.437e-5}','$.test'); json_extract('{"test":8.437e-5}','$.test') 8.437e-5 +select json_value('{"b":true}','$.b')=1; +json_value('{"b":true}','$.b')=1 +1 +CREATE TABLE t1 (c VARCHAR(8)); +INSERT INTO t1 VALUES ('foo'),('bar'); +SELECT * FROM t1 WHERE c IN (JSON_EXTRACT('{"a":"b"}', '$.*')); +c +DROP TABLE t1; # # End of 10.2 tests # diff --git a/mysql-test/main/func_json.test b/mysql-test/main/func_json.test index 2669408fdce..4f05d4f23c4 100644 --- a/mysql-test/main/func_json.test +++ b/mysql-test/main/func_json.test @@ -397,6 +397,21 @@ drop table t1; select json_extract('{"test":8.437e-5}','$.test'); +# +# MDEV-15905 select json_value('{"b":true}','$.b')=1 --> false with +# "Truncated incorrect DOUBLE value: 'true'" +# +select json_value('{"b":true}','$.b')=1; + +# +# MDEV-16209 JSON_EXTRACT in query crashes server. +# + +CREATE TABLE t1 (c VARCHAR(8)); +INSERT INTO t1 VALUES ('foo'),('bar'); +SELECT * FROM t1 WHERE c IN (JSON_EXTRACT('{"a":"b"}', '$.*')); +DROP TABLE t1; + --echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/grant.result b/mysql-test/main/grant.result index 1d4402185a5..1279bcdd41e 100644 --- a/mysql-test/main/grant.result +++ b/mysql-test/main/grant.result @@ -1451,6 +1451,7 @@ CURRENT_USER() root@localhost SET PASSWORD FOR CURRENT_USER() = PASSWORD("admin"); SET PASSWORD FOR CURRENT_USER() = PASSWORD(""); +update mysql.user set plugin=''; # Bug#57952 diff --git a/mysql-test/main/grant.test b/mysql-test/main/grant.test index 72e427493da..cb4254fe8c6 100644 --- a/mysql-test/main/grant.test +++ b/mysql-test/main/grant.test @@ -1265,6 +1265,9 @@ SELECT CURRENT_USER(); SET PASSWORD FOR CURRENT_USER() = PASSWORD("admin"); SET PASSWORD FOR CURRENT_USER() = PASSWORD(""); +#cleanup after MDEV-16238 +update mysql.user set plugin=''; + # # Bug#57952: privilege change is not taken into account by EXECUTE. # diff --git a/mysql-test/main/grant2.result b/mysql-test/main/grant2.result index d7e42f9b7aa..ffb41c1b5f8 100644 --- a/mysql-test/main/grant2.result +++ b/mysql-test/main/grant2.result @@ -372,8 +372,8 @@ mysqltest_1@127.0.0.1 set password = password('changed'); disconnect b12302; connection default; -select host, length(password) from mysql.user where user like 'mysqltest\_1'; -host length(password) +select host, length(authentication_string) from mysql.user where user like 'mysqltest\_1'; +host length(authentication_string) 127.0.0.1 41 revoke all on mysqltest_1.* from mysqltest_1@'127.0.0.1'; delete from mysql.user where user like 'mysqltest\_1'; @@ -387,8 +387,8 @@ mysqltest_1@127.0.0.0/255.0.0.0 set password = password('changed'); disconnect b12302_2; connection default; -select host, length(password) from mysql.user where user like 'mysqltest\_1'; -host length(password) +select host, length(authentication_string) from mysql.user where user like 'mysqltest\_1'; +host length(authentication_string) 127.0.0.0/255.0.0.0 41 revoke all on mysqltest_1.* from mysqltest_1@'127.0.0.0/255.0.0.0'; delete from mysql.user where user like 'mysqltest\_1'; diff --git a/mysql-test/main/grant2.test b/mysql-test/main/grant2.test index cee5df089df..1f7450df6c1 100644 --- a/mysql-test/main/grant2.test +++ b/mysql-test/main/grant2.test @@ -385,7 +385,7 @@ select current_user(); set password = password('changed'); disconnect b12302; connection default; -select host, length(password) from mysql.user where user like 'mysqltest\_1'; +select host, length(authentication_string) from mysql.user where user like 'mysqltest\_1'; revoke all on mysqltest_1.* from mysqltest_1@'127.0.0.1'; delete from mysql.user where user like 'mysqltest\_1'; flush privileges; @@ -396,7 +396,7 @@ select current_user(); set password = password('changed'); disconnect b12302_2; connection default; -select host, length(password) from mysql.user where user like 'mysqltest\_1'; +select host, length(authentication_string) from mysql.user where user like 'mysqltest\_1'; revoke all on mysqltest_1.* from mysqltest_1@'127.0.0.0/255.0.0.0'; delete from mysql.user where user like 'mysqltest\_1'; flush privileges; diff --git a/mysql-test/main/having.result b/mysql-test/main/having.result index 6c206a1699d..f37cc48772e 100644 --- a/mysql-test/main/having.result +++ b/mysql-test/main/having.result @@ -711,6 +711,23 @@ a ct set sql_mode=@save_sql_mode; drop table t1; # +# mdev-16235: impossible HAVING in query without aggregation +# +explain extended +select * from mysql.help_topic where example = 'foo' having description is null; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING +Warnings: +Note 1003 select `mysql`.`help_topic`.`help_topic_id` AS `help_topic_id`,`mysql`.`help_topic`.`name` AS `name`,`mysql`.`help_topic`.`help_category_id` AS `help_category_id`,`mysql`.`help_topic`.`description` AS `description`,`mysql`.`help_topic`.`example` AS `example`,`mysql`.`help_topic`.`url` AS `url` from `mysql`.`help_topic` where `mysql`.`help_topic`.`example` = 'foo' having 0 +select * from mysql.help_topic where example = 'foo' having description is null; +help_topic_id name help_category_id description example url +# +# End of 5. tests +# +# +# Start of 10.0 tests +# +# # Bug mdev-5160: two-way join with HAVING over the second table # CREATE TABLE t1 (c1 varchar(6)) ENGINE=MyISAM; diff --git a/mysql-test/main/having.test b/mysql-test/main/having.test index c9231fef3be..179af14559f 100644 --- a/mysql-test/main/having.test +++ b/mysql-test/main/having.test @@ -746,6 +746,23 @@ set sql_mode=@save_sql_mode; drop table t1; --echo # +--echo # mdev-16235: impossible HAVING in query without aggregation +--echo # + +explain extended +select * from mysql.help_topic where example = 'foo' having description is null; + +select * from mysql.help_topic where example = 'foo' having description is null; + +--echo # +--echo # End of 5. tests +--echo # + +--echo # +--echo # Start of 10.0 tests +--echo # + +--echo # --echo # Bug mdev-5160: two-way join with HAVING over the second table --echo # diff --git a/mysql-test/main/information_schema.result b/mysql-test/main/information_schema.result index 5fcda217d01..0e6b4168ea5 100644 --- a/mysql-test/main/information_schema.result +++ b/mysql-test/main/information_schema.result @@ -1489,7 +1489,7 @@ USE test; End of 5.0 tests. select * from information_schema.engines WHERE ENGINE="MyISAM"; ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS -MyISAM DEFAULT MyISAM storage engine NO NO NO +MyISAM DEFAULT Non-transactional engine with good performance and small data footprint NO NO NO grant select on *.* to user3148@localhost; connect con3148,localhost,user3148,,test; connection con3148; diff --git a/mysql-test/main/limit.result b/mysql-test/main/limit.result index 064fa5a18a7..b47644eb40d 100644 --- a/mysql-test/main/limit.result +++ b/mysql-test/main/limit.result @@ -146,3 +146,19 @@ a 16 DROP TABLE t1; End of 5.1 tests +# +# mdev-16235: SELECT over a table with LIMIT 0 +# +EXPLAIN +SELECT * FROM mysql.slow_log WHERE sql_text != 'foo' LIMIT 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Zero limit +SELECT * FROM mysql.slow_log WHERE sql_text != 'foo' LIMIT 0; +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 +EXPLAIN +SELECT * FROM mysql.help_topic WHERE help_category_id != example LIMIT 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Zero limit +SELECT * FROM mysql.help_topic WHERE help_category_id != example LIMIT 0; +help_topic_id name help_category_id description example url +End of 5.5 tests diff --git a/mysql-test/main/limit.test b/mysql-test/main/limit.test index 4dbe13096d4..668d3b74518 100644 --- a/mysql-test/main/limit.test +++ b/mysql-test/main/limit.test @@ -115,3 +115,17 @@ SELECT a FROM t1 ORDER BY a LIMIT 2 OFFSET 14; DROP TABLE t1; --echo End of 5.1 tests + +--echo # +--echo # mdev-16235: SELECT over a table with LIMIT 0 +--echo # + +EXPLAIN +SELECT * FROM mysql.slow_log WHERE sql_text != 'foo' LIMIT 0; +SELECT * FROM mysql.slow_log WHERE sql_text != 'foo' LIMIT 0; + +EXPLAIN +SELECT * FROM mysql.help_topic WHERE help_category_id != example LIMIT 0; +SELECT * FROM mysql.help_topic WHERE help_category_id != example LIMIT 0; + +--echo End of 5.5 tests diff --git a/mysql-test/main/lock.result b/mysql-test/main/lock.result index e3fd16ee59a..0805fd45864 100644 --- a/mysql-test/main/lock.result +++ b/mysql-test/main/lock.result @@ -406,7 +406,7 @@ LOCK TABLE t1 WRITE; HANDLER t1 OPEN; ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction HANDLER t1 READ FIRST; -ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +Got one of the listed errors HANDLER t1 CLOSE; ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction UNLOCK TABLES; diff --git a/mysql-test/main/lock.test b/mysql-test/main/lock.test index 2c2c5d42783..92ab8294273 100644 --- a/mysql-test/main/lock.test +++ b/mysql-test/main/lock.test @@ -474,7 +474,7 @@ LOCK TABLE t1 WRITE; --echo # HANDLER commands are not allowed in LOCK TABLES mode --error ER_LOCK_OR_ACTIVE_TRANSACTION HANDLER t1 OPEN; ---error ER_LOCK_OR_ACTIVE_TRANSACTION +--error ER_LOCK_OR_ACTIVE_TRANSACTION,ER_UNKNOWN_TABLE HANDLER t1 READ FIRST; --error ER_LOCK_OR_ACTIVE_TRANSACTION HANDLER t1 CLOSE; diff --git a/mysql-test/main/max_statement_time.result b/mysql-test/main/max_statement_time.result index 44ee03b813a..a87a899b575 100644 --- a/mysql-test/main/max_statement_time.result +++ b/mysql-test/main/max_statement_time.result @@ -181,3 +181,6 @@ ERROR 70100: Query execution was interrupted (max_statement_time exceeded) set max_statement_time = 0; drop procedure pr; drop table t1; +SET max_statement_time= 1; +CREATE TABLE t ENGINE=InnoDB SELECT * FROM seq_1_to_50000; +ERROR 70100: Query execution was interrupted (max_statement_time exceeded) diff --git a/mysql-test/main/max_statement_time.test b/mysql-test/main/max_statement_time.test index 0882daff139..24b6d9311f2 100644 --- a/mysql-test/main/max_statement_time.test +++ b/mysql-test/main/max_statement_time.test @@ -5,6 +5,7 @@ --source include/not_embedded.inc --source include/have_innodb.inc +--source include/have_sequence.inc --source include/not_valgrind.inc --echo @@ -226,3 +227,10 @@ call pr(); set max_statement_time = 0; drop procedure pr; drop table t1; + +# +# MDEV-16615 ASAN SEGV in handler::print_error or server crash after error upon CREATE TABLE +# +SET max_statement_time= 1; +--error ER_STATEMENT_TIMEOUT +CREATE TABLE t ENGINE=InnoDB SELECT * FROM seq_1_to_50000; diff --git a/mysql-test/main/mysqldump.result b/mysql-test/main/mysqldump.result index 620afb32666..11e46657888 100644 --- a/mysql-test/main/mysqldump.result +++ b/mysql-test/main/mysqldump.result @@ -5608,6 +5608,21 @@ DROP DATABASE db1; DROP DATABASE db2; FOUND 1 /Database: mysql/ in bug11505.sql # +# MDEV-15021: Fix the order in which routines are called +# +use test; +CREATE FUNCTION f() RETURNS INT RETURN 1; +CREATE VIEW v1 AS SELECT f(); +# Running mysqldump -uroot test --routines --tables v1 > **vardir**/test.dmp +DROP VIEW v1; +DROP FUNCTION f; +# Running mysql -uroot test < **vardir**/test.dmp +# +# Cleanup after succesful import. +# +DROP VIEW v1; +DROP FUNCTION f; +# # Test for --add-drop-trigger # use test; diff --git a/mysql-test/main/mysqldump.test b/mysql-test/main/mysqldump.test index ebe54bac44a..61287b3170d 100644 --- a/mysql-test/main/mysqldump.test +++ b/mysql-test/main/mysqldump.test @@ -2645,6 +2645,28 @@ exec $MYSQL_DUMP mysql func > $SEARCH_FILE; source include/search_pattern_in_file.inc; --echo # +--echo # MDEV-15021: Fix the order in which routines are called +--echo # +use test; +CREATE FUNCTION f() RETURNS INT RETURN 1; +CREATE VIEW v1 AS SELECT f(); + +--echo # Running mysqldump -uroot test --routines --tables v1 > **vardir**/test.dmp +--exec $MYSQL_DUMP -uroot test --routines --tables v1 > $MYSQLTEST_VARDIR/test.dmp + +DROP VIEW v1; +DROP FUNCTION f; + +--echo # Running mysql -uroot test < **vardir**/test.dmp +--exec $MYSQL -uroot test < $MYSQLTEST_VARDIR/test.dmp + +--echo # +--echo # Cleanup after succesful import. +--echo # +DROP VIEW v1; +DROP FUNCTION f; + +--echo # --echo # Test for --add-drop-trigger --echo # use test; diff --git a/mysql-test/main/mysqlslap.result b/mysql-test/main/mysqlslap.result index d3c5107dee3..791cb5ac6b3 100644 --- a/mysql-test/main/mysqlslap.result +++ b/mysql-test/main/mysqlslap.result @@ -255,3 +255,6 @@ Benchmark # MDEV-4684 - Enhancement request: --init-command support for mysqlslap # DROP TABLE t1; +# +# Bug MDEV-15789 (Upstream: #80329): MYSQLSLAP OPTIONS --AUTO-GENERATE-SQL-GUID-PRIMARY and --AUTO-GENERATE-SQL-SECONDARY-INDEXES DONT WORK +# diff --git a/mysql-test/main/mysqlslap.test b/mysql-test/main/mysqlslap.test index c49c4ab3d7d..81115d59d09 100644 --- a/mysql-test/main/mysqlslap.test +++ b/mysql-test/main/mysqlslap.test @@ -80,3 +80,11 @@ DROP DATABASE bug58090; --exec $MYSQL_SLAP --create-schema=test --init-command="CREATE TABLE t1(a INT)" --silent --concurrency=1 --iterations=1 DROP TABLE t1; + +--echo # +--echo # Bug MDEV-15789 (Upstream: #80329): MYSQLSLAP OPTIONS --AUTO-GENERATE-SQL-GUID-PRIMARY and --AUTO-GENERATE-SQL-SECONDARY-INDEXES DONT WORK +--echo # + +--exec $MYSQL_SLAP --concurrency=1 --silent --iterations=1 --number-int-cols=2 --number-char-cols=3 --auto-generate-sql --auto-generate-sql-guid-primary --create-schema=slap + +--exec $MYSQL_SLAP --concurrency=1 --silent --iterations=1 --number-int-cols=2 --number-char-cols=3 --auto-generate-sql --auto-generate-sql-secondary-indexes=1 --create-schema=slap diff --git a/mysql-test/main/mysqltest_tracking_info.result b/mysql-test/main/mysqltest_tracking_info.result new file mode 100644 index 00000000000..df966ae1d39 --- /dev/null +++ b/mysql-test/main/mysqltest_tracking_info.result @@ -0,0 +1,31 @@ +SELECT @@session.character_set_connection; +@@session.character_set_connection +latin1 +SET @@session.session_track_system_variables='character_set_connection'; +# tracking info on +SET NAMES 'utf8'; +-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES +-- character_set_connection +-- utf8 + +SET NAMES 'big5'; +-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES +-- character_set_connection +-- big5 + +# tracking info on once +SET NAMES 'utf8'; +-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES +-- character_set_connection +-- utf8 + +SET NAMES 'big5'; +# tracking info on +SET NAMES 'utf8'; +-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES +-- character_set_connection +-- utf8 + +# tracking info off once +SET NAMES 'big5'; +SET @@session.session_track_system_variables= default; diff --git a/mysql-test/main/mysqltest_tracking_info.test b/mysql-test/main/mysqltest_tracking_info.test new file mode 100644 index 00000000000..d31f68b3cbd --- /dev/null +++ b/mysql-test/main/mysqltest_tracking_info.test @@ -0,0 +1,25 @@ + +--source include/no_protocol.inc +--source include/not_embedded.inc + +SELECT @@session.character_set_connection; +SET @@session.session_track_system_variables='character_set_connection'; + +--echo # tracking info on +--enable_session_track_info +SET NAMES 'utf8'; +SET NAMES 'big5'; +--disable_session_track_info +--echo # tracking info on once +--enable_session_track_info ONCE +SET NAMES 'utf8'; +SET NAMES 'big5'; +--echo # tracking info on +--enable_session_track_info +SET NAMES 'utf8'; +--echo # tracking info off once +--disable_session_track_info ONCE +SET NAMES 'big5'; +--disable_session_track_info + +SET @@session.session_track_system_variables= default; diff --git a/mysql-test/main/olap.result b/mysql-test/main/olap.result index 6fdbe008016..1931fac6029 100644 --- a/mysql-test/main/olap.result +++ b/mysql-test/main/olap.result @@ -767,7 +767,29 @@ NULL DROP TABLE t1, t2; End of 5.0 tests # -# Start of 10.3 tests +# End of 10.0 tests +# +# +# MDEV-16190 Server crashes in Item_null_result::field_type on SELECT with time field, ROLLUP and HAVING +# +CREATE TABLE t1 (t TIME) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('12:12:12'); +SELECT t, COUNT(*) FROM t1 GROUP BY t WITH ROLLUP HAVING t > '00:00:00'; +t COUNT(*) +12:12:12 1 +DROP TABLE t1; +CREATE TABLE t1 (t TIME) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('12:12:12'),('12:12:13'); +SELECT t, COUNT(*) FROM t1 GROUP BY t WITH ROLLUP HAVING t > '00:00:00'; +t COUNT(*) +12:12:12 1 +12:12:13 1 +DROP TABLE t1; +# +# End of 10.1 tests +# +# +# End of 10.2 tests # # # MDEV-12886 Different default for INT and BIGINT column in a VIEW for a SELECT with ROLLUP diff --git a/mysql-test/main/olap.test b/mysql-test/main/olap.test index 74dbe8ba10b..3756712b49d 100644 --- a/mysql-test/main/olap.test +++ b/mysql-test/main/olap.test @@ -406,7 +406,29 @@ DROP TABLE t1, t2; --echo End of 5.0 tests --echo # ---echo # Start of 10.3 tests +--echo # End of 10.0 tests +--echo # + +--echo # +--echo # MDEV-16190 Server crashes in Item_null_result::field_type on SELECT with time field, ROLLUP and HAVING +--echo # +CREATE TABLE t1 (t TIME) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('12:12:12'); +SELECT t, COUNT(*) FROM t1 GROUP BY t WITH ROLLUP HAVING t > '00:00:00'; +DROP TABLE t1; + +CREATE TABLE t1 (t TIME) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('12:12:12'),('12:12:13'); +SELECT t, COUNT(*) FROM t1 GROUP BY t WITH ROLLUP HAVING t > '00:00:00'; +DROP TABLE t1; + + +--echo # +--echo # End of 10.1 tests +--echo # + +--echo # +--echo # End of 10.2 tests --echo # --echo # @@ -462,5 +484,4 @@ SELECT NULLIF( CAST( 'foo' AS DATE ), NULL & 'bar' ) AS f FROM t1 GROUP BY f WIT --enable_warnings DROP TABLE t1; - --echo # End of 10.3 Tests diff --git a/mysql-test/main/ps.result b/mysql-test/main/ps.result index 518ae252d94..682c3e31be4 100644 --- a/mysql-test/main/ps.result +++ b/mysql-test/main/ps.result @@ -4368,7 +4368,46 @@ LINE3 3 drop table t1; # End of 5.5 tests # -# Start of 10.2 tests +# End of 10.0 tests +# +# +# MDEV-12060 Crash in EXECUTE IMMEDIATE with an expression returning a GRANT command +# (the 10.1 part) +# +CREATE PROCEDURE p2 () +BEGIN +SET STATEMENT join_cache_level=CAST(CONCAT(_utf8'6',_latin1'') AS INT) FOR PREPARE stmt FROM 'SELECT 1'; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; +END; +/ +CALL p2(); +1 +1 +DROP PROCEDURE p2; +BEGIN NOT ATOMIC +SET STATEMENT join_cache_level=CAST(CONCAT(_utf8'6',_latin1'') AS INT) FOR PREPARE stmt FROM 'SELECT 1'; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; +END; +/ +1 +1 +BEGIN NOT ATOMIC +SET STATEMENT join_cache_level=CAST(CONCAT(_utf8'6',_latin1'') AS INT) FOR PREPARE stmt FROM 'SELECT 1'; +DEALLOCATE PREPARE stmt; +END; +/ +BEGIN NOT ATOMIC +PREPARE stmt FROM 'SELECT 1'; +SET STATEMENT join_cache_level=CAST(CONCAT(_utf8'6',_latin1'') AS INT) FOR EXECUTE stmt; +DEALLOCATE PREPARE stmt; +END; +/ +1 +1 +# +# End of 10.1 tests # # # MDEV-10709 Expressions as parameters to Dynamic SQL diff --git a/mysql-test/main/ps.test b/mysql-test/main/ps.test index 1cf5124eec1..79c08d62290 100644 --- a/mysql-test/main/ps.test +++ b/mysql-test/main/ps.test @@ -3875,7 +3875,58 @@ drop table t1; --echo # End of 5.5 tests --echo # ---echo # Start of 10.2 tests +--echo # End of 10.0 tests +--echo # + +--echo # +--echo # MDEV-12060 Crash in EXECUTE IMMEDIATE with an expression returning a GRANT command +--echo # (the 10.1 part) +--echo # + +DELIMITER /; +CREATE PROCEDURE p2 () +BEGIN + SET STATEMENT join_cache_level=CAST(CONCAT(_utf8'6',_latin1'') AS INT) FOR PREPARE stmt FROM 'SELECT 1'; + EXECUTE stmt; + DEALLOCATE PREPARE stmt; +END; +/ +DELIMITER ;/ +CALL p2(); +DROP PROCEDURE p2; + + +DELIMITER /; +BEGIN NOT ATOMIC + SET STATEMENT join_cache_level=CAST(CONCAT(_utf8'6',_latin1'') AS INT) FOR PREPARE stmt FROM 'SELECT 1'; + EXECUTE stmt; + DEALLOCATE PREPARE stmt; +END; +/ +DELIMITER ;/ + + +DELIMITER /; +BEGIN NOT ATOMIC + SET STATEMENT join_cache_level=CAST(CONCAT(_utf8'6',_latin1'') AS INT) FOR PREPARE stmt FROM 'SELECT 1'; + DEALLOCATE PREPARE stmt; +END; +/ +DELIMITER ;/ + + +DELIMITER /; +BEGIN NOT ATOMIC + PREPARE stmt FROM 'SELECT 1'; + SET STATEMENT join_cache_level=CAST(CONCAT(_utf8'6',_latin1'') AS INT) FOR EXECUTE stmt; + DEALLOCATE PREPARE stmt; +END; +/ +DELIMITER ;/ + + +--echo # +--echo # End of 10.1 tests --echo # --echo # @@ -4733,7 +4784,6 @@ DROP PROCEDURE p2; DROP PROCEDURE p1; DROP ROLE testrole; - --echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/read_only_innodb.result b/mysql-test/main/read_only_innodb.result index b6e294b633c..abfc5322ed0 100644 --- a/mysql-test/main/read_only_innodb.result +++ b/mysql-test/main/read_only_innodb.result @@ -237,14 +237,6 @@ a a 5 10 DROP TABLE temp1, temp2; -# MDEV-14185 CREATE TEMPORARY TABLE AS SELECT causes error 1290 with read_only and InnoDB. - -CREATE TEMPORARY TABLE temp1 ENGINE=INNODB AS SELECT a FROM t1; -SELECT * FROM temp1; -a -1 -DROP TABLE temp1; - # Disconnect and cleanup disconnect con1; diff --git a/mysql-test/main/read_only_innodb.test b/mysql-test/main/read_only_innodb.test index a9310a1a78e..9ba3ccaca07 100644 --- a/mysql-test/main/read_only_innodb.test +++ b/mysql-test/main/read_only_innodb.test @@ -241,15 +241,6 @@ SELECT * FROM temp1, temp2; DROP TABLE temp1, temp2; --echo ---echo # MDEV-14185 CREATE TEMPORARY TABLE AS SELECT causes error 1290 with read_only and InnoDB. ---echo - -CREATE TEMPORARY TABLE temp1 ENGINE=INNODB AS SELECT a FROM t1; -SELECT * FROM temp1; -DROP TABLE temp1; - - ---echo --echo # Disconnect and cleanup --echo disconnect con1; diff --git a/mysql-test/main/reset_connection.result b/mysql-test/main/reset_connection.result new file mode 100644 index 00000000000..925195f704e --- /dev/null +++ b/mysql-test/main/reset_connection.result @@ -0,0 +1,7 @@ +FLUSH STATUS; +SHOW local STATUS LIKE 'com_select'; +Variable_name Value +Com_select 10 +SHOW local STATUS LIKE 'com_select'; +Variable_name Value +Com_select 0 diff --git a/mysql-test/main/reset_connection.test b/mysql-test/main/reset_connection.test new file mode 100644 index 00000000000..49f41c32fc3 --- /dev/null +++ b/mysql-test/main/reset_connection.test @@ -0,0 +1,25 @@ +--source include/not_embedded.inc + +FLUSH STATUS; + +--disable_result_log +--disable_query_log + +let $i = 10; +begin; +while ($i) +{ + dec $i; + SELECT 1; +} +commit; + +--enable_query_log +--enable_result_log + +SHOW local STATUS LIKE 'com_select'; + +--reset_connection + +SHOW local STATUS LIKE 'com_select'; + diff --git a/mysql-test/main/selectivity.result b/mysql-test/main/selectivity.result index 9cb6ee3e9bf..aff14b23ccb 100644 --- a/mysql-test/main/selectivity.result +++ b/mysql-test/main/selectivity.result @@ -356,13 +356,13 @@ and o_orderkey = l_orderkey group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice order by o_totalprice desc, o_orderdate; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 6005 0.00 Using temporary; Using filesort -1 PRIMARY orders eq_ref PRIMARY,i_o_custkey PRIMARY 4 <subquery2>.l_orderkey 1 100.00 Using where +1 PRIMARY orders ALL PRIMARY,i_o_custkey NULL NULL NULL 1500 100.00 Using where; Using temporary; Using filesort +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 dbt3_s001.orders.o_orderkey 1 100.00 1 PRIMARY customer eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey 1 100.00 -1 PRIMARY lineitem ref PRIMARY,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 <subquery2>.l_orderkey 4 100.00 +1 PRIMARY lineitem ref PRIMARY,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey_quantity 4 dbt3_s001.orders.o_orderkey 4 100.00 Using index 2 MATERIALIZED lineitem index NULL i_l_orderkey_quantity 13 NULL 6005 100.00 Using index Warnings: -Note 1003 /* select#1 */ select `dbt3_s001`.`customer`.`c_name` AS `c_name`,`dbt3_s001`.`customer`.`c_custkey` AS `c_custkey`,`dbt3_s001`.`orders`.`o_orderkey` AS `o_orderkey`,`dbt3_s001`.`orders`.`o_orderDATE` AS `o_orderdate`,`dbt3_s001`.`orders`.`o_totalprice` AS `o_totalprice`,sum(`dbt3_s001`.`lineitem`.`l_quantity`) AS `sum(l_quantity)` from <materialize> (/* select#2 */ select `dbt3_s001`.`lineitem`.`l_orderkey` from `dbt3_s001`.`lineitem` group by `dbt3_s001`.`lineitem`.`l_orderkey` having sum(`dbt3_s001`.`lineitem`.`l_quantity`) > 250) join `dbt3_s001`.`customer` join `dbt3_s001`.`orders` join `dbt3_s001`.`lineitem` where `dbt3_s001`.`customer`.`c_custkey` = `dbt3_s001`.`orders`.`o_custkey` and `dbt3_s001`.`orders`.`o_orderkey` = `<subquery2>`.`l_orderkey` and `dbt3_s001`.`lineitem`.`l_orderkey` = `<subquery2>`.`l_orderkey` group by `dbt3_s001`.`customer`.`c_name`,`dbt3_s001`.`customer`.`c_custkey`,`dbt3_s001`.`orders`.`o_orderkey`,`dbt3_s001`.`orders`.`o_orderDATE`,`dbt3_s001`.`orders`.`o_totalprice` order by `dbt3_s001`.`orders`.`o_totalprice` desc,`dbt3_s001`.`orders`.`o_orderDATE` +Note 1003 /* select#1 */ select `dbt3_s001`.`customer`.`c_name` AS `c_name`,`dbt3_s001`.`customer`.`c_custkey` AS `c_custkey`,`dbt3_s001`.`orders`.`o_orderkey` AS `o_orderkey`,`dbt3_s001`.`orders`.`o_orderDATE` AS `o_orderdate`,`dbt3_s001`.`orders`.`o_totalprice` AS `o_totalprice`,sum(`dbt3_s001`.`lineitem`.`l_quantity`) AS `sum(l_quantity)` from <materialize> (/* select#2 */ select `dbt3_s001`.`lineitem`.`l_orderkey` from `dbt3_s001`.`lineitem` group by `dbt3_s001`.`lineitem`.`l_orderkey` having sum(`dbt3_s001`.`lineitem`.`l_quantity`) > 250) join `dbt3_s001`.`customer` join `dbt3_s001`.`orders` join `dbt3_s001`.`lineitem` where `dbt3_s001`.`customer`.`c_custkey` = `dbt3_s001`.`orders`.`o_custkey` and `<subquery2>`.`l_orderkey` = `dbt3_s001`.`orders`.`o_orderkey` and `dbt3_s001`.`lineitem`.`l_orderkey` = `dbt3_s001`.`orders`.`o_orderkey` group by `dbt3_s001`.`customer`.`c_name`,`dbt3_s001`.`customer`.`c_custkey`,`dbt3_s001`.`orders`.`o_orderkey`,`dbt3_s001`.`orders`.`o_orderDATE`,`dbt3_s001`.`orders`.`o_totalprice` order by `dbt3_s001`.`orders`.`o_totalprice` desc,`dbt3_s001`.`orders`.`o_orderDATE` select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity) from customer, orders, lineitem @@ -1530,6 +1530,68 @@ t 10:00:00 11:00:00 DROP TABLE t1; +# +# MDEV-16374: filtered shows 0 for materilization scan for a semi join, which makes optimizer +# always pick materialization scan over materialization lookup +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int, b int); +insert into t1 values (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10), +(11,11),(12,12),(13,13),(14,14),(15,15); +set @@optimizer_use_condition_selectivity=2; +explain extended select * from t1 where a in (select max(a) from t1 group by b); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 100.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 16 100.00 Using temporary +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (/* select#2 */ select max(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`b`) join `test`.`t1` where `<subquery2>`.`max(a)` = `test`.`t1`.`a` +select * from t1 where a in (select max(a) from t1 group by b); +a b +0 0 +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 +7 7 +8 8 +9 9 +10 10 +11 11 +12 12 +13 13 +14 14 +15 15 +set @@optimizer_use_condition_selectivity=1; +explain extended select * from t1 where a in (select max(a) from t1 group by b); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 100.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 16 100.00 Using temporary +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (/* select#2 */ select max(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`b`) join `test`.`t1` where `<subquery2>`.`max(a)` = `test`.`t1`.`a` +select * from t1 where a in (select max(a) from t1 group by b); +a b +0 0 +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 +7 7 +8 8 +9 9 +10 10 +11 11 +12 12 +13 13 +14 14 +15 15 +drop table t1,t0; set histogram_size=@save_histogram_size; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/main/selectivity.test b/mysql-test/main/selectivity.test index 3e60f242083..cf12bdaea21 100644 --- a/mysql-test/main/selectivity.test +++ b/mysql-test/main/selectivity.test @@ -1045,6 +1045,24 @@ SELECT * FROM (SELECT t FROM t1 WHERE d IS NULL) sq; DROP TABLE t1; +--echo # +--echo # MDEV-16374: filtered shows 0 for materilization scan for a semi join, which makes optimizer +--echo # always pick materialization scan over materialization lookup +--echo # + +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int, b int); +insert into t1 values (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10), +(11,11),(12,12),(13,13),(14,14),(15,15); +set @@optimizer_use_condition_selectivity=2; +explain extended select * from t1 where a in (select max(a) from t1 group by b); +select * from t1 where a in (select max(a) from t1 group by b); +set @@optimizer_use_condition_selectivity=1; +explain extended select * from t1 where a in (select max(a) from t1 group by b); +select * from t1 where a in (select max(a) from t1 group by b); +drop table t1,t0; + set histogram_size=@save_histogram_size; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result index 332cc947a6b..ab698760c55 100644 --- a/mysql-test/main/selectivity_innodb.result +++ b/mysql-test/main/selectivity_innodb.result @@ -359,13 +359,13 @@ and o_orderkey = l_orderkey group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice order by o_totalprice desc, o_orderdate; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 6005 0.00 Using temporary; Using filesort -1 PRIMARY orders eq_ref PRIMARY,i_o_custkey PRIMARY 4 <subquery2>.l_orderkey 1 100.00 Using where +1 PRIMARY orders ALL PRIMARY,i_o_custkey NULL NULL NULL 1500 100.00 Using where; Using temporary; Using filesort +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 dbt3_s001.orders.o_orderkey 1 100.00 1 PRIMARY customer eq_ref PRIMARY PRIMARY 4 dbt3_s001.orders.o_custkey 1 100.00 -1 PRIMARY lineitem ref PRIMARY,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 <subquery2>.l_orderkey 4 100.00 +1 PRIMARY lineitem ref PRIMARY,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey_quantity 4 dbt3_s001.orders.o_orderkey 4 100.00 Using index 2 MATERIALIZED lineitem index NULL PRIMARY 8 NULL 6005 100.00 Warnings: -Note 1003 /* select#1 */ select `dbt3_s001`.`customer`.`c_name` AS `c_name`,`dbt3_s001`.`customer`.`c_custkey` AS `c_custkey`,`dbt3_s001`.`orders`.`o_orderkey` AS `o_orderkey`,`dbt3_s001`.`orders`.`o_orderDATE` AS `o_orderdate`,`dbt3_s001`.`orders`.`o_totalprice` AS `o_totalprice`,sum(`dbt3_s001`.`lineitem`.`l_quantity`) AS `sum(l_quantity)` from <materialize> (/* select#2 */ select `dbt3_s001`.`lineitem`.`l_orderkey` from `dbt3_s001`.`lineitem` group by `dbt3_s001`.`lineitem`.`l_orderkey` having sum(`dbt3_s001`.`lineitem`.`l_quantity`) > 250) join `dbt3_s001`.`customer` join `dbt3_s001`.`orders` join `dbt3_s001`.`lineitem` where `dbt3_s001`.`customer`.`c_custkey` = `dbt3_s001`.`orders`.`o_custkey` and `dbt3_s001`.`orders`.`o_orderkey` = `<subquery2>`.`l_orderkey` and `dbt3_s001`.`lineitem`.`l_orderkey` = `<subquery2>`.`l_orderkey` group by `dbt3_s001`.`customer`.`c_name`,`dbt3_s001`.`customer`.`c_custkey`,`dbt3_s001`.`orders`.`o_orderkey`,`dbt3_s001`.`orders`.`o_orderDATE`,`dbt3_s001`.`orders`.`o_totalprice` order by `dbt3_s001`.`orders`.`o_totalprice` desc,`dbt3_s001`.`orders`.`o_orderDATE` +Note 1003 /* select#1 */ select `dbt3_s001`.`customer`.`c_name` AS `c_name`,`dbt3_s001`.`customer`.`c_custkey` AS `c_custkey`,`dbt3_s001`.`orders`.`o_orderkey` AS `o_orderkey`,`dbt3_s001`.`orders`.`o_orderDATE` AS `o_orderdate`,`dbt3_s001`.`orders`.`o_totalprice` AS `o_totalprice`,sum(`dbt3_s001`.`lineitem`.`l_quantity`) AS `sum(l_quantity)` from <materialize> (/* select#2 */ select `dbt3_s001`.`lineitem`.`l_orderkey` from `dbt3_s001`.`lineitem` group by `dbt3_s001`.`lineitem`.`l_orderkey` having sum(`dbt3_s001`.`lineitem`.`l_quantity`) > 250) join `dbt3_s001`.`customer` join `dbt3_s001`.`orders` join `dbt3_s001`.`lineitem` where `dbt3_s001`.`customer`.`c_custkey` = `dbt3_s001`.`orders`.`o_custkey` and `<subquery2>`.`l_orderkey` = `dbt3_s001`.`orders`.`o_orderkey` and `dbt3_s001`.`lineitem`.`l_orderkey` = `dbt3_s001`.`orders`.`o_orderkey` group by `dbt3_s001`.`customer`.`c_name`,`dbt3_s001`.`customer`.`c_custkey`,`dbt3_s001`.`orders`.`o_orderkey`,`dbt3_s001`.`orders`.`o_orderDATE`,`dbt3_s001`.`orders`.`o_totalprice` order by `dbt3_s001`.`orders`.`o_totalprice` desc,`dbt3_s001`.`orders`.`o_orderDATE` select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity) from customer, orders, lineitem @@ -1541,6 +1541,68 @@ t 10:00:00 11:00:00 DROP TABLE t1; +# +# MDEV-16374: filtered shows 0 for materilization scan for a semi join, which makes optimizer +# always pick materialization scan over materialization lookup +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int, b int); +insert into t1 values (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10), +(11,11),(12,12),(13,13),(14,14),(15,15); +set @@optimizer_use_condition_selectivity=2; +explain extended select * from t1 where a in (select max(a) from t1 group by b); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 100.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 16 100.00 Using temporary +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (/* select#2 */ select max(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`b`) join `test`.`t1` where `<subquery2>`.`max(a)` = `test`.`t1`.`a` +select * from t1 where a in (select max(a) from t1 group by b); +a b +0 0 +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 +7 7 +8 8 +9 9 +10 10 +11 11 +12 12 +13 13 +14 14 +15 15 +set @@optimizer_use_condition_selectivity=1; +explain extended select * from t1 where a in (select max(a) from t1 group by b); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 16 100.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 16 100.00 Using temporary +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (/* select#2 */ select max(`test`.`t1`.`a`) from `test`.`t1` group by `test`.`t1`.`b`) join `test`.`t1` where `<subquery2>`.`max(a)` = `test`.`t1`.`a` +select * from t1 where a in (select max(a) from t1 group by b); +a b +0 0 +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 +7 7 +8 8 +9 9 +10 10 +11 11 +12 12 +13 13 +14 14 +15 15 +drop table t1,t0; set histogram_size=@save_histogram_size; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/main/session_tracker_last_gtid.result b/mysql-test/main/session_tracker_last_gtid.result new file mode 100644 index 00000000000..795d0aaa2a2 --- /dev/null +++ b/mysql-test/main/session_tracker_last_gtid.result @@ -0,0 +1,34 @@ +# +# MDEV-15477: SESSION_SYSVARS_TRACKER does not track last_gtid +# +SET gtid_seq_no=1000; +SET @@session.session_track_system_variables='last_gtid'; +create table t1 (a int) engine=innodb; +-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES +-- last_gtid +-- 0-1-1000 + +select @@last_gtid; +@@last_gtid +0-1-1000 +-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES +-- last_gtid +-- 0-1-1000 + +insert into t1 values (1); +-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES +-- last_gtid +-- 0-1-1001 + +select @@last_gtid; +@@last_gtid +0-1-1001 +-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES +-- last_gtid +-- 0-1-1001 + +drop table t1; +-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES +-- last_gtid +-- 0-1-1002 + diff --git a/mysql-test/main/session_tracker_last_gtid.test b/mysql-test/main/session_tracker_last_gtid.test new file mode 100644 index 00000000000..24cf2104a0c --- /dev/null +++ b/mysql-test/main/session_tracker_last_gtid.test @@ -0,0 +1,19 @@ +--source include/not_embedded.inc +--source include/have_innodb.inc +--source include/have_binlog_format_statement.inc + +--enable_session_track_info + +--echo # +--echo # MDEV-15477: SESSION_SYSVARS_TRACKER does not track last_gtid +--echo # + +SET gtid_seq_no=1000; +SET @@session.session_track_system_variables='last_gtid'; +create table t1 (a int) engine=innodb; +select @@last_gtid; +insert into t1 values (1); +select @@last_gtid; +drop table t1; + +--disable_session_track_info diff --git a/mysql-test/main/set_password_plugin-9835.result b/mysql-test/main/set_password.result index 3cc723957d8..315d0bef9fb 100644 --- a/mysql-test/main/set_password_plugin-9835.result +++ b/mysql-test/main/set_password.result @@ -11,10 +11,10 @@ select user, host, password, plugin, authentication_string from mysql.user where user host password plugin authentication_string natauth localhost *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 newpass localhost *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 -newpassnat localhost *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 +newpassnat localhost mysql_native_password *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 oldauth localhost 378b243e220ca493 oldpass localhost 378b243e220ca493 -oldpassold localhost 378b243e220ca493 +oldpassold localhost mysql_old_password 378b243e220ca493 connect con,localhost,natauth,test,; select current_user(); current_user() @@ -86,12 +86,12 @@ set password for oldpass@localhost = PASSWORD('test2'); set password for oldpassold@localhost = PASSWORD('test2'); select user, host, password, plugin, authentication_string from mysql.user where user != 'root'; user host password plugin authentication_string -natauth localhost *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E -newpass localhost *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E -newpassnat localhost *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E -oldauth localhost *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E -oldpass localhost *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E -oldpassold localhost *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E +natauth localhost mysql_native_password *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E +newpass localhost mysql_native_password *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E +newpassnat localhost mysql_native_password *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E +oldauth localhost mysql_native_password *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E +oldpass localhost mysql_native_password *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E +oldpassold localhost mysql_native_password *7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E connect con,localhost,natauth,test2,; select current_user(); current_user() @@ -158,3 +158,24 @@ connection default; drop user natauth@localhost, newpass@localhost, newpassnat@localhost; drop user oldauth@localhost, oldpass@localhost, oldpassold@localhost; set global secure_auth=default; +create user foo@localhost identified with mysql_native_password; +update mysql.user set authentication_string=password('foo'), plugin='mysql_native_password' where user='foo' and host='localhost'; +set password for 'foo'@'localhost' = password('bar'); +flush privileges; +connect foo, localhost, foo, bar; +select user(), current_user(); +user() current_user() +foo@localhost foo@localhost +show grants; +Grants for foo@localhost +GRANT USAGE ON *.* TO 'foo'@'localhost' IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB' +disconnect foo; +connection default; +select user,host,password,plugin,authentication_string from mysql.user where user='foo'; +user host password plugin authentication_string +foo localhost mysql_native_password *E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB +set password for 'foo'@'localhost' = ''; +select user,host,password,plugin,authentication_string from mysql.user where user='foo'; +user host password plugin authentication_string +foo localhost mysql_native_password +drop user foo@localhost; diff --git a/mysql-test/main/set_password_plugin-9835.test b/mysql-test/main/set_password.test index 6afccd74f9d..fc1ecb5ef5c 100644 --- a/mysql-test/main/set_password_plugin-9835.test +++ b/mysql-test/main/set_password.test @@ -129,3 +129,19 @@ drop user natauth@localhost, newpass@localhost, newpassnat@localhost; drop user oldauth@localhost, oldpass@localhost, oldpassold@localhost; set global secure_auth=default; +# +# MDEV-16238 root/localhost authn prioritizes authentication_string over Password +# +create user foo@localhost identified with mysql_native_password; +update mysql.user set authentication_string=password('foo'), plugin='mysql_native_password' where user='foo' and host='localhost'; +set password for 'foo'@'localhost' = password('bar'); +flush privileges; +--connect foo, localhost, foo, bar +select user(), current_user(); +show grants; +--disconnect foo +--connection default +select user,host,password,plugin,authentication_string from mysql.user where user='foo'; +set password for 'foo'@'localhost' = ''; +select user,host,password,plugin,authentication_string from mysql.user where user='foo'; +drop user foo@localhost; diff --git a/mysql-test/main/show_grants_with_plugin-7985.result b/mysql-test/main/show_grants_with_plugin-7985.result index 81880e5cc40..0f8e1e39969 100644 --- a/mysql-test/main/show_grants_with_plugin-7985.result +++ b/mysql-test/main/show_grants_with_plugin-7985.result @@ -71,7 +71,7 @@ connection default; set password for u1 = PASSWORD('SOMETHINGELSE'); select user, host, password, plugin, authentication_string from mysql.user where user = 'u1'; user host password plugin authentication_string -u1 % *054B7BBD2B9A553DA560520DCD3F76DA2D81B7C6 +u1 % mysql_native_password *054B7BBD2B9A553DA560520DCD3F76DA2D81B7C6 # # Here we should use the password field, as that primes over # the authentication_string field. @@ -112,7 +112,7 @@ connection default; # Now we remove the authentication plugin password, flush privileges and # try again. # -update mysql.user set authentication_string = '' where user='u1'; +update mysql.user set password=authentication_string, plugin='', authentication_string='' where user='u1'; select user, host, password, plugin, authentication_string from mysql.user where user = 'u1'; user host password plugin authentication_string u1 % *054B7BBD2B9A553DA560520DCD3F76DA2D81B7C6 @@ -172,7 +172,7 @@ connection default; set password for u1 = ''; select user, host, password, plugin, authentication_string from mysql.user where user = 'u1'; user host password plugin authentication_string -u1 % +u1 % mysql_native_password # # Test no password connect. # diff --git a/mysql-test/main/show_grants_with_plugin-7985.test b/mysql-test/main/show_grants_with_plugin-7985.test index 84f71c72667..85952870254 100644 --- a/mysql-test/main/show_grants_with_plugin-7985.test +++ b/mysql-test/main/show_grants_with_plugin-7985.test @@ -91,7 +91,7 @@ show grants; --echo # Now we remove the authentication plugin password, flush privileges and --echo # try again. --echo # -update mysql.user set authentication_string = '' where user='u1'; +update mysql.user set password=authentication_string, plugin='', authentication_string='' where user='u1'; select user, host, password, plugin, authentication_string from mysql.user where user = 'u1'; flush privileges; show grants for u1; diff --git a/mysql-test/main/sp-security.result b/mysql-test/main/sp-security.result index 882072ff7c7..7813ab6a192 100644 --- a/mysql-test/main/sp-security.result +++ b/mysql-test/main/sp-security.result @@ -755,6 +755,7 @@ GRANT EXECUTE ON PROCEDURE `test`.`sp1` TO 'root'@'localhost' GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION drop procedure sp1; set password=''; +update mysql.user set plugin=''; # # MDEV-13396 Unexpected "alter routine comand defined" during CREATE OR REPLACE PROCEDURE # diff --git a/mysql-test/main/sp-security.test b/mysql-test/main/sp-security.test index 059a5dd0fa8..73d0263dd69 100644 --- a/mysql-test/main/sp-security.test +++ b/mysql-test/main/sp-security.test @@ -1023,6 +1023,8 @@ grant execute on procedure sp1 to current_user() identified by 'barfoo'; show grants; drop procedure sp1; set password=''; +#cleanup after MDEV-16238 +update mysql.user set plugin=''; --echo # --echo # MDEV-13396 Unexpected "alter routine comand defined" during CREATE OR REPLACE PROCEDURE diff --git a/mysql-test/main/statistics.result b/mysql-test/main/statistics.result index b710bc0e51a..574eb5f4727 100644 --- a/mysql-test/main/statistics.result +++ b/mysql-test/main/statistics.result @@ -1692,6 +1692,27 @@ set use_stat_tables=@save_use_stat_tables; set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; drop table t1,t2; # +# MDEV-16507: statistics for temporary tables should not be used +# +SET +@save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +SET @@use_stat_tables = preferably ; +SET @@optimizer_use_condition_selectivity = 4; +CREATE TABLE t1 ( +TIMESTAMP TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP +ON UPDATE CURRENT_TIMESTAMP +); +SET @had_t1_table= @@warning_count != 0; +CREATE TEMPORARY TABLE tmp_t1 LIKE t1; +INSERT INTO tmp_t1 VALUES (now()); +INSERT INTO t1 SELECT * FROM tmp_t1 WHERE @had_t1_table=0; +DROP TABLE t1; +SET +use_stat_tables=@save_use_stat_tables; +SET +optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +# End of 10.0 tests +# # MDEV-9590: Always print "Engine-independent statistic" warnings and # might be filtering columns unintentionally from engines # diff --git a/mysql-test/main/statistics.test b/mysql-test/main/statistics.test index 368cee0f8c8..b2e544064b0 100644 --- a/mysql-test/main/statistics.test +++ b/mysql-test/main/statistics.test @@ -819,6 +819,32 @@ set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivi drop table t1,t2; --echo # +--echo # MDEV-16507: statistics for temporary tables should not be used +--echo # + +SET +@save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +SET @@use_stat_tables = preferably ; +SET @@optimizer_use_condition_selectivity = 4; + +CREATE TABLE t1 ( + TIMESTAMP TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP + ON UPDATE CURRENT_TIMESTAMP +); + +SET @had_t1_table= @@warning_count != 0; +CREATE TEMPORARY TABLE tmp_t1 LIKE t1; +INSERT INTO tmp_t1 VALUES (now()); +INSERT INTO t1 SELECT * FROM tmp_t1 WHERE @had_t1_table=0; +DROP TABLE t1; + +SET +use_stat_tables=@save_use_stat_tables; +SET +optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +--echo # End of 10.0 tests + +--echo # --echo # MDEV-9590: Always print "Engine-independent statistic" warnings and --echo # might be filtering columns unintentionally from engines --echo # diff --git a/mysql-test/main/subselect_mat_cost_bugs.result b/mysql-test/main/subselect_mat_cost_bugs.result index 125da8da517..658722112d2 100644 --- a/mysql-test/main/subselect_mat_cost_bugs.result +++ b/mysql-test/main/subselect_mat_cost_bugs.result @@ -334,7 +334,7 @@ SELECT * FROM t1 WHERE (f1) IN (SELECT f1 FROM t2) LIMIT 0; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Zero limit 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where SELECT * FROM t1 WHERE (f1) IN (SELECT f1 FROM t2) diff --git a/mysql-test/main/subselect_sj2_mat.result b/mysql-test/main/subselect_sj2_mat.result index 6a1f310511f..62248536e34 100644 --- a/mysql-test/main/subselect_sj2_mat.result +++ b/mysql-test/main/subselect_sj2_mat.result @@ -1670,3 +1670,150 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 11 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where DROP TABLE t1,t2; +# +# MDEV-16225: wrong resultset from query with semijoin=on +# +CREATE TABLE t1 ( +`id` int(10) NOT NULL AUTO_INCREMENT, +`local_name` varchar(64) NOT NULL, +PRIMARY KEY (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1; +insert into t1(`id`,`local_name`) values +(1,'Cash Advance'), +(2,'Cash Advance'), +(3,'Rollover'), +(4,'AL Installment'), +(5,'AL Installment'), +(6,'AL Installment'), +(7,'AL Installment'), +(8,'AL Installment'), +(9,'AL Installment'), +(10,'Internet Payday'), +(11,'Rollover - Internet Payday'), +(12,'AL Monthly Installment'), +(13,'AL Semi-Monthly Installment'); +explain +SELECT SQL_NO_CACHE t.id +FROM t1 t +WHERE ( +t.id IN (SELECT A.id FROM t1 AS A WHERE A.local_name IN (SELECT B.local_name FROM t1 AS B WHERE B.id IN (0,4,12,13,1,10,3,11))) +OR +(t.id IN (0,4,12,13,1,10,3,11)) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t index PRIMARY PRIMARY 4 NULL 13 Using where; Using index +2 MATERIALIZED <subquery3> ALL distinct_key NULL NULL NULL 8 +2 MATERIALIZED A ALL PRIMARY NULL NULL NULL 13 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED B ALL PRIMARY NULL NULL NULL 13 Using where +SELECT SQL_NO_CACHE t.id +FROM t1 t +WHERE ( +t.id IN (SELECT A.id FROM t1 AS A WHERE A.local_name IN (SELECT B.local_name FROM t1 AS B WHERE B.id IN (0,4,12,13,1,10,3,11))) +OR +(t.id IN (0,4,12,13,1,10,3,11)) +); +id +1 +2 +3 +4 +5 +6 +7 +8 +9 +10 +11 +12 +13 +drop table t1; +# +# MDEV-15247: Crash when SET NAMES 'utf8' is set +# +CREATE TABLE t1 ( +id_category int unsigned, +id_product int unsigned, +PRIMARY KEY (id_category,id_product) +) ENGINE=MyISAM; +INSERT INTO `t1` VALUES (31,216), (31,215), (31,214), (31,213), (31,212), (32,211), (32,210), (32,209), (32,208), (29,207), (30,315372), (2,161), (2,132), (33,315380), (31,315371), (29,315370), (29,315373), (29,315369), (29,315374), (29,315368), (29,315375), (29,315367), (29,183), (29,182), (30,177), (29,315376), (13,315365), (2,167), (2,315357), (2,164), (2,159), (2,131), (2,127), (14,315364), (27,315363), (29,205), (29,204), (29,203), (29,202), (29,201), (29,200), (29,199), (29,198), (29,197), (29,196), (29,195), (29,194), (29,193), (29,192), (29,191), (29,190), (29,189), (14,188), (29,187), (29,186), (29,185), (29,184), (29,315377), (29,315378), (29,181), (33,315379), (29,179), (30,178), (29,180), (30,176), (30,175), (30,174), (30,173), (30,172), (11,171), (27,315357), (23,108), (23,102); +CREATE TABLE t2 ( +id_product int, +id_t2 int, +KEY id_t2 (id_t2), +KEY id_product (id_product) +) ENGINE=MyISAM; +INSERT INTO `t2` VALUES (11,31), (11,31), (11,31), (11,32), (11,32), +(11,32), (10,26), (11,32), (10,28), (11,32), (10,29), (11,33), (10,26), +(11,33), (10,27), (9,23), (11,32), (10,26), (8,18), (7,15), (11,32), +(10,28), (11,32), (10,28), (11,32), (10,29), (11,32), (10,29), (8,19), +(7,16), (8,18), (7,16), (8,20), (7,16), (11,32), (10,28), (8,19), +(7,16), (8,20), (7,16), (11,32), (10,29), (8,19), (7,16), (8,20), +(7,16), (10,27), (9,23), (10,27), (9,23), (10,27), (9,23), (11,32), +(10,27), (11,32), (10,27), (8,18), (7,15), (10,26), (9,24), (8,19), +(7,16), (10,26), (9,23), (8,19), (7,16), (8,18), (7,16), (8,18), (7,16), +(9,23), (8,18), (9,23), (8,19), (7,16), (7,16), (8,19), (7,16), (11,31), +(10,27), (9,24), (11,31), (10,27), (9,23), (8,19), (11,31), (10,26), (9,24), +(8,19), (11,31), (10,26), (9,25), (8,18), (11,31), (10,26), (9,23), (8,19), +(11,31), (10,26), (9,23), (8,18), (11,31), (10,30), (9,23), (8,18), (11,31), +(10,30), (9,23), (8,19), (11,31), (10,26), (9,25), (8,19), (8,21), (11,32), +(10,26), (9,22), (8,19), (11,32), (10,26), (9,22), (8,18), (11,32), (10,26), +(9,22), (8,20), (11,33), (10,26), (9,22), (8,19), (11,33), (10,26), (9,22), +(8,18), (11,33), (10,26), (9,22), (8,20), (11,32), (10,26), (9,24), (8,19), +(11,32), (10,26), (9,25), (8,19), (11,32), (10,26), (9,25), (8,18), (11,32), +(10,26), (9,23), (8,18), (11,32), (10,30), (9,23), (8,18), (11,32), (10,30), +(9,23), (8,19), (11,32), (10,26), (9,23), (8,19), (11,32), (10,27), (9,23), +(11,32), (10,27), (9,23), (11,32), (10,27), (9,23), (10,26), (9,22), (8,19), +(7,15), (10,26), (9,22), (8,20), (7,15), (10,26), (9,22), (8,18), (7,15), +(8,19), (10,26), (10,26), (11,33), (10,26), (11,33), (10,26), (11,33), +(10,27), (11,33), (10,27), (11,31), (10,26), (11,31), (10,26), (8,18), +(7,15), (9,23), (9,23), (9,24), (8,21), (7,15), (7,15), (7,15), (7,15), +(7,15), (7,15), (7,15), (7,15), (7,15), (8,18), (7,17), (8,18), (7,17), (8,19), (8,19); +CREATE TABLE t3 ( +id_product int unsigned, +PRIMARY KEY (id_product) +) ENGINE=MyISAM; +INSERT INTO t3 VALUES +(102),(103),(104),(105),(106),(107),(108),(109),(110), +(315371),(315373),(315374),(315375),(315376),(315377), +(315378),(315379),(315380); +CREATE TABLE t4 ( +id_product int not null, +id_shop int, +PRIMARY KEY (id_product,id_shop) +) ENGINE=MyISAM ; +INSERT INTO t4 VALUES +(202,1),(201,1),(200,1),(199,1),(198,1),(197,1),(196,1),(195,1), +(194,1),(193,1),(192,1),(191,1),(190,1),(189,1),(188,1),(187,1), +(186,1),(185,1),(184,1),(183,1),(182,1),(181,1),(179,1),(178,1), +(177,1),(176,1),(126,1),(315380,1); +CREATE TABLE t5 (id_product int) ENGINE=MyISAM; +INSERT INTO `t5` VALUES +(652),(668),(669),(670),(671),(673),(674),(675),(676), +(677),(679),(680),(681),(682),(683),(684),(685),(686); +explain +SELECT * FROM t3 +JOIN t4 ON (t4.id_product = t3.id_product AND t4.id_shop = 1) +JOIN t1 ON (t1.id_product = t3.id_product) +LEFT JOIN t5 ON (t5.id_product = t3.id_product) +WHERE 1=1 +AND t3.id_product IN (SELECT id_product FROM t2 t2_1 WHERE t2_1.id_t2 = 32) +AND t3.id_product IN (SELECT id_product FROM t2 t2_2 WHERE t2_2.id_t2 = 15) +AND t3.id_product IN (SELECT id_product FROM t2 t2_3 WHERE t2_3.id_t2 = 18 OR t2_3.id_t2 = 19) +AND t3.id_product IN (SELECT id_product FROM t2 t2_4 WHERE t2_4.id_t2 = 34 OR t2_4.id_t2 = 23) +AND t3.id_product IN (SELECT id_product FROM t2 t2_5 WHERE t2_5.id_t2 = 29 OR t2_5.id_t2 = 28 OR t2_5.id_t2 = 26); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL PRIMARY 8 NULL 73 Using index +1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.id_product 1 Using index +1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY t4 eq_ref PRIMARY PRIMARY 8 test.t1.id_product,const 1 Using where; Using index +1 PRIMARY <subquery6> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY t5 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) +5 MATERIALIZED t2_4 range id_t2,id_product id_t2 5 NULL 18 Using index condition; Using where +4 MATERIALIZED t2_3 range id_t2,id_product id_t2 5 NULL 32 Using index condition; Using where +3 MATERIALIZED t2_2 ref id_t2,id_product id_t2 5 const 12 +2 MATERIALIZED t2_1 ref id_t2,id_product id_t2 5 const 50 +6 MATERIALIZED t2_5 range id_t2,id_product id_t2 5 NULL 30 Using index condition; Using where +drop table t1,t2,t3,t4,t5; diff --git a/mysql-test/main/subselect_sj2_mat.test b/mysql-test/main/subselect_sj2_mat.test index 0234f0cb7b6..bba436078e3 100644 --- a/mysql-test/main/subselect_sj2_mat.test +++ b/mysql-test/main/subselect_sj2_mat.test @@ -303,3 +303,132 @@ eval $q; eval explain $q; DROP TABLE t1,t2; + +--echo # +--echo # MDEV-16225: wrong resultset from query with semijoin=on +--echo # + +CREATE TABLE t1 ( + `id` int(10) NOT NULL AUTO_INCREMENT, + `local_name` varchar(64) NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1; + +insert into t1(`id`,`local_name`) values +(1,'Cash Advance'), +(2,'Cash Advance'), +(3,'Rollover'), +(4,'AL Installment'), +(5,'AL Installment'), +(6,'AL Installment'), +(7,'AL Installment'), +(8,'AL Installment'), +(9,'AL Installment'), +(10,'Internet Payday'), +(11,'Rollover - Internet Payday'), +(12,'AL Monthly Installment'), +(13,'AL Semi-Monthly Installment'); + +explain +SELECT SQL_NO_CACHE t.id +FROM t1 t +WHERE ( + t.id IN (SELECT A.id FROM t1 AS A WHERE A.local_name IN (SELECT B.local_name FROM t1 AS B WHERE B.id IN (0,4,12,13,1,10,3,11))) + OR + (t.id IN (0,4,12,13,1,10,3,11)) +); +SELECT SQL_NO_CACHE t.id +FROM t1 t +WHERE ( + t.id IN (SELECT A.id FROM t1 AS A WHERE A.local_name IN (SELECT B.local_name FROM t1 AS B WHERE B.id IN (0,4,12,13,1,10,3,11))) + OR + (t.id IN (0,4,12,13,1,10,3,11)) +); +drop table t1; + +--echo # +--echo # MDEV-15247: Crash when SET NAMES 'utf8' is set +--echo # + +CREATE TABLE t1 ( + id_category int unsigned, + id_product int unsigned, + PRIMARY KEY (id_category,id_product) +) ENGINE=MyISAM; + +INSERT INTO `t1` VALUES (31,216), (31,215), (31,214), (31,213), (31,212), (32,211), (32,210), (32,209), (32,208), (29,207), (30,315372), (2,161), (2,132), (33,315380), (31,315371), (29,315370), (29,315373), (29,315369), (29,315374), (29,315368), (29,315375), (29,315367), (29,183), (29,182), (30,177), (29,315376), (13,315365), (2,167), (2,315357), (2,164), (2,159), (2,131), (2,127), (14,315364), (27,315363), (29,205), (29,204), (29,203), (29,202), (29,201), (29,200), (29,199), (29,198), (29,197), (29,196), (29,195), (29,194), (29,193), (29,192), (29,191), (29,190), (29,189), (14,188), (29,187), (29,186), (29,185), (29,184), (29,315377), (29,315378), (29,181), (33,315379), (29,179), (30,178), (29,180), (30,176), (30,175), (30,174), (30,173), (30,172), (11,171), (27,315357), (23,108), (23,102); + +CREATE TABLE t2 ( + id_product int, + id_t2 int, + KEY id_t2 (id_t2), + KEY id_product (id_product) +) ENGINE=MyISAM; + +INSERT INTO `t2` VALUES (11,31), (11,31), (11,31), (11,32), (11,32), +(11,32), (10,26), (11,32), (10,28), (11,32), (10,29), (11,33), (10,26), +(11,33), (10,27), (9,23), (11,32), (10,26), (8,18), (7,15), (11,32), +(10,28), (11,32), (10,28), (11,32), (10,29), (11,32), (10,29), (8,19), +(7,16), (8,18), (7,16), (8,20), (7,16), (11,32), (10,28), (8,19), +(7,16), (8,20), (7,16), (11,32), (10,29), (8,19), (7,16), (8,20), +(7,16), (10,27), (9,23), (10,27), (9,23), (10,27), (9,23), (11,32), +(10,27), (11,32), (10,27), (8,18), (7,15), (10,26), (9,24), (8,19), +(7,16), (10,26), (9,23), (8,19), (7,16), (8,18), (7,16), (8,18), (7,16), +(9,23), (8,18), (9,23), (8,19), (7,16), (7,16), (8,19), (7,16), (11,31), +(10,27), (9,24), (11,31), (10,27), (9,23), (8,19), (11,31), (10,26), (9,24), +(8,19), (11,31), (10,26), (9,25), (8,18), (11,31), (10,26), (9,23), (8,19), +(11,31), (10,26), (9,23), (8,18), (11,31), (10,30), (9,23), (8,18), (11,31), +(10,30), (9,23), (8,19), (11,31), (10,26), (9,25), (8,19), (8,21), (11,32), +(10,26), (9,22), (8,19), (11,32), (10,26), (9,22), (8,18), (11,32), (10,26), +(9,22), (8,20), (11,33), (10,26), (9,22), (8,19), (11,33), (10,26), (9,22), +(8,18), (11,33), (10,26), (9,22), (8,20), (11,32), (10,26), (9,24), (8,19), +(11,32), (10,26), (9,25), (8,19), (11,32), (10,26), (9,25), (8,18), (11,32), +(10,26), (9,23), (8,18), (11,32), (10,30), (9,23), (8,18), (11,32), (10,30), +(9,23), (8,19), (11,32), (10,26), (9,23), (8,19), (11,32), (10,27), (9,23), +(11,32), (10,27), (9,23), (11,32), (10,27), (9,23), (10,26), (9,22), (8,19), +(7,15), (10,26), (9,22), (8,20), (7,15), (10,26), (9,22), (8,18), (7,15), +(8,19), (10,26), (10,26), (11,33), (10,26), (11,33), (10,26), (11,33), +(10,27), (11,33), (10,27), (11,31), (10,26), (11,31), (10,26), (8,18), +(7,15), (9,23), (9,23), (9,24), (8,21), (7,15), (7,15), (7,15), (7,15), +(7,15), (7,15), (7,15), (7,15), (7,15), (8,18), (7,17), (8,18), (7,17), (8,19), (8,19); + +CREATE TABLE t3 ( + id_product int unsigned, + PRIMARY KEY (id_product) +) ENGINE=MyISAM; + +INSERT INTO t3 VALUES +(102),(103),(104),(105),(106),(107),(108),(109),(110), +(315371),(315373),(315374),(315375),(315376),(315377), +(315378),(315379),(315380); + +CREATE TABLE t4 ( + id_product int not null, + id_shop int, + PRIMARY KEY (id_product,id_shop) +) ENGINE=MyISAM ; + +INSERT INTO t4 VALUES +(202,1),(201,1),(200,1),(199,1),(198,1),(197,1),(196,1),(195,1), +(194,1),(193,1),(192,1),(191,1),(190,1),(189,1),(188,1),(187,1), +(186,1),(185,1),(184,1),(183,1),(182,1),(181,1),(179,1),(178,1), +(177,1),(176,1),(126,1),(315380,1); + +CREATE TABLE t5 (id_product int) ENGINE=MyISAM; +INSERT INTO `t5` VALUES +(652),(668),(669),(670),(671),(673),(674),(675),(676), +(677),(679),(680),(681),(682),(683),(684),(685),(686); + +explain +SELECT * FROM t3 + JOIN t4 ON (t4.id_product = t3.id_product AND t4.id_shop = 1) + JOIN t1 ON (t1.id_product = t3.id_product) +LEFT JOIN t5 ON (t5.id_product = t3.id_product) +WHERE 1=1 +AND t3.id_product IN (SELECT id_product FROM t2 t2_1 WHERE t2_1.id_t2 = 32) +AND t3.id_product IN (SELECT id_product FROM t2 t2_2 WHERE t2_2.id_t2 = 15) +AND t3.id_product IN (SELECT id_product FROM t2 t2_3 WHERE t2_3.id_t2 = 18 OR t2_3.id_t2 = 19) +AND t3.id_product IN (SELECT id_product FROM t2 t2_4 WHERE t2_4.id_t2 = 34 OR t2_4.id_t2 = 23) +AND t3.id_product IN (SELECT id_product FROM t2 t2_5 WHERE t2_5.id_t2 = 29 OR t2_5.id_t2 = 28 OR t2_5.id_t2 = 26); + +drop table t1,t2,t3,t4,t5; diff --git a/mysql-test/main/subselect_sj_nonmerged.result b/mysql-test/main/subselect_sj_nonmerged.result index c7e04225ffe..47970668ae5 100644 --- a/mysql-test/main/subselect_sj_nonmerged.result +++ b/mysql-test/main/subselect_sj_nonmerged.result @@ -77,9 +77,9 @@ explain select * from t4 where t4.a in (select max(t2.a) from t1, t2 group by t2.b) and t4.b in (select max(t2.a) from t1, t2 group by t2.b); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 5 -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 Using join buffer (flat, BNL join) -1 PRIMARY t4 ref a a 10 <subquery2>.max(t2.a),<subquery3>.max(t2.a) 12 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 +1 PRIMARY t4 ref a a 5 <subquery2>.max(t2.a) 12 Using index condition +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 test.t4.b 1 3 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary 3 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary diff --git a/mysql-test/main/type_blob.result b/mysql-test/main/type_blob.result index 569ba65df3f..3c99366168c 100644 --- a/mysql-test/main/type_blob.result +++ b/mysql-test/main/type_blob.result @@ -1063,3 +1063,19 @@ DROP TABLE t1; # # End of 5.5 tests # +# +# Start of 10.2 test +# +# +# MDEV-12809 Bad column type created for TEXT(1431655798) CHARACTER SET utf8 +# +CREATE TABLE t1 (a TEXT(1431655798) CHARACTER SET utf8); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` longtext CHARACTER SET utf8 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +# +# End of 10.2 test +# diff --git a/mysql-test/main/type_blob.test b/mysql-test/main/type_blob.test index 8db6ac6da2a..2c74d4ea241 100644 --- a/mysql-test/main/type_blob.test +++ b/mysql-test/main/type_blob.test @@ -680,3 +680,21 @@ DROP TABLE t1; --echo # --echo # End of 5.5 tests --echo # + + +--echo # +--echo # Start of 10.2 test +--echo # + +--echo # +--echo # MDEV-12809 Bad column type created for TEXT(1431655798) CHARACTER SET utf8 +--echo # + +CREATE TABLE t1 (a TEXT(1431655798) CHARACTER SET utf8); +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +--echo # +--echo # End of 10.2 test +--echo # diff --git a/mysql-test/main/type_decimal.result b/mysql-test/main/type_decimal.result index f96f98e7a9b..521dc887ff6 100644 --- a/mysql-test/main/type_decimal.result +++ b/mysql-test/main/type_decimal.result @@ -1033,6 +1033,31 @@ c1 c2 0.123456 0.123456 SET sql_mode=DEFAULT; # +# MDEV-12574 MAX(old_decimal) produces a column of the old DECIMAL type +# +SHOW CREATE TABLE t1dec102; +Table Create Table +t1dec102 CREATE TABLE `t1dec102` ( + `a` decimal(10,2)/*old*/ DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +CREATE TABLE t1 AS SELECT a, MAX(a), COALESCE(a) FROM t1dec102; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` decimal(10,2) DEFAULT NULL, + `MAX(a)` decimal(10,2) DEFAULT NULL, + `COALESCE(a)` decimal(10,2) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 AS SELECT a FROM t1dec102 UNION SELECT a FROM t1dec102; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` decimal(10,2) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +DROP TABLE t1dec102; +# # End of 10.2 tests # # @@ -1046,7 +1071,7 @@ CREATE TABLE t1 AS SELECT MAX(a) FROM t1dec102; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `MAX(a)` decimal(10,2)/*old*/ DEFAULT NULL + `MAX(a)` decimal(10,2) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1 AS SELECT COALESCE(a) FROM t1dec102; diff --git a/mysql-test/main/type_decimal.test b/mysql-test/main/type_decimal.test index 903f375d1ae..319f85edf9a 100644 --- a/mysql-test/main/type_decimal.test +++ b/mysql-test/main/type_decimal.test @@ -628,6 +628,27 @@ SELECT SET sql_mode=DEFAULT; --echo # +--echo # MDEV-12574 MAX(old_decimal) produces a column of the old DECIMAL type +--echo # + +let $MYSQLD_DATADIR= `select @@datadir`; +--copy_file std_data/old_decimal/t1dec102.frm $MYSQLD_DATADIR/test/t1dec102.frm +--copy_file std_data/old_decimal/t1dec102.MYD $MYSQLD_DATADIR/test/t1dec102.MYD +--copy_file std_data/old_decimal/t1dec102.MYI $MYSQLD_DATADIR/test/t1dec102.MYI + +SHOW CREATE TABLE t1dec102; + +CREATE TABLE t1 AS SELECT a, MAX(a), COALESCE(a) FROM t1dec102; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 AS SELECT a FROM t1dec102 UNION SELECT a FROM t1dec102; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +DROP TABLE t1dec102; + +--echo # --echo # End of 10.2 tests --echo # |