diff options
Diffstat (limited to 'mysql-test/main')
65 files changed, 3104 insertions, 552 deletions
diff --git a/mysql-test/main/alter_user.result b/mysql-test/main/alter_user.result index 0dcec5835b7..bdc96a64fee 100644 --- a/mysql-test/main/alter_user.result +++ b/mysql-test/main/alter_user.result @@ -102,4 +102,14 @@ select * from mysql.user where user = 'foo'; Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv Delete_history_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired is_role default_role max_statement_time % foo *88C89BE093D4ECF72D039F62EBB7477EA1FD4D63 N N N N N N N N N N N N N N N Y N N N N N N N N N Y N N N N SPECIFIED text foo_issuer foo_subject 10 20 30 40 mysql_native_password *88C89BE093D4ECF72D039F62EBB7477EA1FD4D63 N N 0.000000 drop user foo; +# +# Bug #29882299: ALTER USER ... IDENTIFIED WITH ... BY ... SHOULD BE A PRIVILEGED OPERATION +# +create user foo@localhost; +connect x,localhost,foo; +alter user current_user identified with 'something'; +ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation +connection default; +disconnect x; +drop user foo@localhost; update mysql.global_priv set priv=@root_priv where user='root' and host='localhost'; diff --git a/mysql-test/main/alter_user.test b/mysql-test/main/alter_user.test index 5a05a671277..83a06a78728 100644 --- a/mysql-test/main/alter_user.test +++ b/mysql-test/main/alter_user.test @@ -87,4 +87,15 @@ alter user foo with MAX_QUERIES_PER_HOUR 10 select * from mysql.user where user = 'foo'; drop user foo; +--echo # +--echo # Bug #29882299: ALTER USER ... IDENTIFIED WITH ... BY ... SHOULD BE A PRIVILEGED OPERATION +--echo # +create user foo@localhost; +--connect x,localhost,foo +--error ER_SPECIFIC_ACCESS_DENIED_ERROR +alter user current_user identified with 'something'; +--connection default +--disconnect x +drop user foo@localhost; + update mysql.global_priv set priv=@root_priv where user='root' and host='localhost'; diff --git a/mysql-test/main/create.result b/mysql-test/main/create.result index 39ede1f90b9..d249e4b453c 100644 --- a/mysql-test/main/create.result +++ b/mysql-test/main/create.result @@ -1072,58 +1072,58 @@ create table t1 like information_schema.processlist; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `ID` bigint(4) NOT NULL DEFAULT 0, - `USER` varchar(128) NOT NULL DEFAULT '', - `HOST` varchar(64) NOT NULL DEFAULT '', - `DB` varchar(64) DEFAULT NULL, - `COMMAND` varchar(16) NOT NULL DEFAULT '', - `TIME` int(7) NOT NULL DEFAULT 0, - `STATE` varchar(64) DEFAULT NULL, - `INFO` longtext DEFAULT NULL, - `TIME_MS` decimal(22,3) NOT NULL DEFAULT 0.000, - `STAGE` tinyint(2) NOT NULL DEFAULT 0, - `MAX_STAGE` tinyint(2) NOT NULL DEFAULT 0, - `PROGRESS` decimal(7,3) NOT NULL DEFAULT 0.000, - `MEMORY_USED` bigint(7) NOT NULL DEFAULT 0, - `MAX_MEMORY_USED` bigint(7) NOT NULL DEFAULT 0, - `EXAMINED_ROWS` int(7) NOT NULL DEFAULT 0, - `QUERY_ID` bigint(4) NOT NULL DEFAULT 0, - `INFO_BINARY` blob DEFAULT NULL, - `TID` bigint(4) NOT NULL DEFAULT 0 + `ID` bigint(4) NOT NULL, + `USER` varchar(128) NOT NULL, + `HOST` varchar(64) NOT NULL, + `DB` varchar(64), + `COMMAND` varchar(16) NOT NULL, + `TIME` int(7) NOT NULL, + `STATE` varchar(64), + `INFO` longtext, + `TIME_MS` decimal(22,3) NOT NULL, + `STAGE` tinyint(2) NOT NULL, + `MAX_STAGE` tinyint(2) NOT NULL, + `PROGRESS` decimal(7,3) NOT NULL, + `MEMORY_USED` bigint(7) NOT NULL, + `MAX_MEMORY_USED` bigint(7) NOT NULL, + `EXAMINED_ROWS` int(7) NOT NULL, + `QUERY_ID` bigint(4) NOT NULL, + `INFO_BINARY` blob, + `TID` bigint(4) NOT NULL ) DEFAULT CHARSET=utf8 drop table t1; create temporary table t1 like information_schema.processlist; show create table t1; Table Create Table t1 CREATE TEMPORARY TABLE `t1` ( - `ID` bigint(4) NOT NULL DEFAULT 0, - `USER` varchar(128) NOT NULL DEFAULT '', - `HOST` varchar(64) NOT NULL DEFAULT '', - `DB` varchar(64) DEFAULT NULL, - `COMMAND` varchar(16) NOT NULL DEFAULT '', - `TIME` int(7) NOT NULL DEFAULT 0, - `STATE` varchar(64) DEFAULT NULL, - `INFO` longtext DEFAULT NULL, - `TIME_MS` decimal(22,3) NOT NULL DEFAULT 0.000, - `STAGE` tinyint(2) NOT NULL DEFAULT 0, - `MAX_STAGE` tinyint(2) NOT NULL DEFAULT 0, - `PROGRESS` decimal(7,3) NOT NULL DEFAULT 0.000, - `MEMORY_USED` bigint(7) NOT NULL DEFAULT 0, - `MAX_MEMORY_USED` bigint(7) NOT NULL DEFAULT 0, - `EXAMINED_ROWS` int(7) NOT NULL DEFAULT 0, - `QUERY_ID` bigint(4) NOT NULL DEFAULT 0, - `INFO_BINARY` blob DEFAULT NULL, - `TID` bigint(4) NOT NULL DEFAULT 0 + `ID` bigint(4) NOT NULL, + `USER` varchar(128) NOT NULL, + `HOST` varchar(64) NOT NULL, + `DB` varchar(64), + `COMMAND` varchar(16) NOT NULL, + `TIME` int(7) NOT NULL, + `STATE` varchar(64), + `INFO` longtext, + `TIME_MS` decimal(22,3) NOT NULL, + `STAGE` tinyint(2) NOT NULL, + `MAX_STAGE` tinyint(2) NOT NULL, + `PROGRESS` decimal(7,3) NOT NULL, + `MEMORY_USED` bigint(7) NOT NULL, + `MAX_MEMORY_USED` bigint(7) NOT NULL, + `EXAMINED_ROWS` int(7) NOT NULL, + `QUERY_ID` bigint(4) NOT NULL, + `INFO_BINARY` blob, + `TID` bigint(4) NOT NULL ) DEFAULT CHARSET=utf8 drop table t1; create table t1 like information_schema.character_sets; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `CHARACTER_SET_NAME` varchar(32) NOT NULL DEFAULT '', - `DEFAULT_COLLATE_NAME` varchar(32) NOT NULL DEFAULT '', - `DESCRIPTION` varchar(60) NOT NULL DEFAULT '', - `MAXLEN` bigint(3) NOT NULL DEFAULT 0 + `CHARACTER_SET_NAME` varchar(32) NOT NULL, + `DEFAULT_COLLATE_NAME` varchar(32) NOT NULL, + `DESCRIPTION` varchar(60) NOT NULL, + `MAXLEN` bigint(3) NOT NULL ) ENGINE=MEMORY DEFAULT CHARSET=utf8 drop table t1; diff --git a/mysql-test/main/derived.result b/mysql-test/main/derived.result index ac7d3b2d879..3c995d95a01 100644 --- a/mysql-test/main/derived.result +++ b/mysql-test/main/derived.result @@ -1204,6 +1204,59 @@ REPLACE INTO v2 ( SELECT * FROM v4 ) UNION ( SELECT f FROM v2 ); drop view v1,v2,v3,v4; drop table t1,t2,t3; # +# MDEV-20325: Assertion `outer_context || !*from_field || *from_field == not_found_field' failed in Item_field::fix_outer_field | `!derived->is_excluded()' failed in TABLE_LIST::set_check_materialized | SIGEGV in st_select_lex::mark_as_dependent (optimized builds) +# +CREATE TABLE t1 (a INT); +# Check that re-execution of a stored routine containing +# a query with subquery in the FROM clause doesn't result in +# assert failure in case the 'derived_merge' optimizer option +# has been turned on/off +CREATE PROCEDURE sp() SELECT * FROM (SELECT a FROM t1) tb; +CALL sp(); +a +SET optimizer_switch='derived_merge=off'; +# Without the patch the following statement would result in assert +# failure +CALL sp(); +a +# Check the same test case for Prepared Statement +SET optimizer_switch='derived_merge=on'; +PREPARE stmt FROM "SELECT * FROM (SELECT a FROM t1) tb"; +EXECUTE stmt; +a +SET optimizer_switch='derived_merge=off'; +# Without the patch the following statement would result in assert +# failure +EXECUTE stmt; +a +DEALLOCATE PREPARE stmt; +# Here check the reverse test case - first turn off the 'derived_merge' +# optimizer option, run the stored routine containing a query with +# subquery in the FROM clause, then turn on the 'derived_merge' +# optimizer option and re-execute the same stored routine to check that +# the routine is finished successfully. +CREATE PROCEDURE sp1() SELECT * FROM (SELECT a FROM t1) tb; +SET optimizer_switch='derived_merge=off'; +CALL sp1(); +a +SET optimizer_switch='derived_merge=on'; +CALL sp1(); +a +# Check the same test case for Prepared Statement +SET optimizer_switch='derived_merge=off'; +PREPARE stmt FROM "SELECT * FROM (SELECT a FROM t1) tb"; +EXECUTE stmt; +a +SET optimizer_switch='derived_merge=on'; +# Without the patch the following statement would result in assert +# failure +EXECUTE stmt; +a +DEALLOCATE PREPARE stmt; +DROP PROCEDURE sp; +DROP PROCEDURE sp1; +DROP TABLE t1; +# # End of 10.2 tests # # diff --git a/mysql-test/main/derived.test b/mysql-test/main/derived.test index f4477ce8550..4ff0cf4165c 100644 --- a/mysql-test/main/derived.test +++ b/mysql-test/main/derived.test @@ -1037,6 +1037,57 @@ drop view v1,v2,v3,v4; drop table t1,t2,t3; --echo # +--echo # MDEV-20325: Assertion `outer_context || !*from_field || *from_field == not_found_field' failed in Item_field::fix_outer_field | `!derived->is_excluded()' failed in TABLE_LIST::set_check_materialized | SIGEGV in st_select_lex::mark_as_dependent (optimized builds) +--echo # +CREATE TABLE t1 (a INT); + +--echo # Check that re-execution of a stored routine containing +--echo # a query with subquery in the FROM clause doesn't result in +--echo # assert failure in case the 'derived_merge' optimizer option +--echo # has been turned on/off +CREATE PROCEDURE sp() SELECT * FROM (SELECT a FROM t1) tb; +CALL sp(); +SET optimizer_switch='derived_merge=off'; +--echo # Without the patch the following statement would result in assert +--echo # failure +CALL sp(); + +--echo # Check the same test case for Prepared Statement +SET optimizer_switch='derived_merge=on'; +PREPARE stmt FROM "SELECT * FROM (SELECT a FROM t1) tb"; +EXECUTE stmt; +SET optimizer_switch='derived_merge=off'; +--echo # Without the patch the following statement would result in assert +--echo # failure +EXECUTE stmt; +DEALLOCATE PREPARE stmt; + +--echo # Here check the reverse test case - first turn off the 'derived_merge' +--echo # optimizer option, run the stored routine containing a query with +--echo # subquery in the FROM clause, then turn on the 'derived_merge' +--echo # optimizer option and re-execute the same stored routine to check that +--echo # the routine is finished successfully. +CREATE PROCEDURE sp1() SELECT * FROM (SELECT a FROM t1) tb; +SET optimizer_switch='derived_merge=off'; +CALL sp1(); +SET optimizer_switch='derived_merge=on'; +CALL sp1(); + +--echo # Check the same test case for Prepared Statement +SET optimizer_switch='derived_merge=off'; +PREPARE stmt FROM "SELECT * FROM (SELECT a FROM t1) tb"; +EXECUTE stmt; +SET optimizer_switch='derived_merge=on'; +--echo # Without the patch the following statement would result in assert +--echo # failure +EXECUTE stmt; +DEALLOCATE PREPARE stmt; + +DROP PROCEDURE sp; +DROP PROCEDURE sp1; +DROP TABLE t1; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 01a863ccb8b..bb6d8ee5a87 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -17491,7 +17491,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 1 PRIMARY <derived2> ref key0 key0 5 test.t3.id 2 3 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 -2 LATERAL DERIVED cp2 ref a a 5 test.t1.a 1 Using index +2 LATERAL DERIVED cp2 ref a a 5 test.t1.a 1 Using where; Using index explain format=json select * from t1, (select a from t1 cp2 group by a) dt, t3 where dt.a = t1.a and t1.a = t3.id and t1.a in (select a from t2); EXPLAIN @@ -17564,6 +17564,7 @@ EXPLAIN "ref": ["test.t1.a"], "rows": 1, "filtered": 100, + "attached_condition": "cp2.a = t3.`id`", "using_index": true } } @@ -17583,6 +17584,618 @@ id a a id 21 2 2 2 deallocate prepare stmt; drop table t1,t2,t3; +# +# MDEV-MDEV-27132: Splittable derived with equality in WHERE +# +CREATE TABLE t1 ( +id int PRIMARY KEY +) ENGINE=MyISAM; +INSERT INTO t1 VALUES +(-1),(2070),(4826),(4827),(4828),(4829),(4830),(4831),(4832),(4833),(4834), +(4835),(4836),(4837),(4838),(4839),(4840),(4841),(4842),(4843),(4844), +(4845),(4846),(4847),(4848),(4849),(4850),(4851),(4852),(4853),(4854), +(4855),(4856),(4857),(4858),(4859),(4860),(4861),(4862),(4863),(4864), +(4865),(4866),(4867),(4868),(4869),(4870),(4871),(4872),(4873),(4874), +(4875),(4876); +CREATE TABLE t2 ( +id int PRIMARY KEY AUTO_INCREMENT, +deleted int(1), +t1_id int, +email varchar(255), +reporting_person int(1), +KEY t1_id (t1_id) +) ENGINE=MyISAM; +INSERT INTO t2 VALUES +(1,0,2064,'1test@test.ee',1),(2,1626095588,2066,'2test@test.ee',1), +(3,0,2066,'3test@test.ee',1),(4,0,2068,'4test@test.ee',1), +(5,0,2068,'5test@test.ee',1),(6,0,2069,'6test@test.ee',1),(7,0,2070,'',0), +(8,0,2070,'',0),(9,0,2071,'',0),(10,0,2071,'',0),(11,0,2072,'',0), +(12,0,2072,'',0),(13,0,2072,'13test@test.ee',1),(14,0,2073,'14test@test.ee',1), +(15,0,2074,'15test@test.ee',1),(16,0,2075,'16test@test.ee',1),(17,0,2075,'',0), +(18,0,2075,'',0),(19,0,2076,'19test@test.ee',1),(20,0,2077,'',0), +(21,0,2078,'21test@test.ee',1),(22,0,2078,'22test@test.ee',1); +INSERT INTO t2(deleted, t1_id, email, reporting_person) +SELECT deleted, t1_id, email, reporting_person FROM t2; +INSERT INTO t2(deleted, t1_id, email, reporting_person) +SELECT deleted, t1_id+10000, email, reporting_person FROM t2; +INSERT INTO t2(deleted, t1_id, email, reporting_person) +SELECT deleted, t1_id+20000, email, reporting_person FROM t2; +INSERT INTO t2(deleted, t1_id, email, reporting_person) +SELECT deleted, t1_id+40000, email, reporting_person FROM t2; +INSERT INTO t2(deleted, t1_id, email, reporting_person) +SELECT deleted, t1_id+80000, email, reporting_person FROM t2; +INSERT INTO t2(deleted, t1_id, email, reporting_person) +SELECT deleted, t1_id+160000, email, reporting_person FROM t2; +CREATE TABLE t3 ( +id int PRIMARY KEY, +deleted int, +t1_id int, +YEAR int(4), +quarter int(1), +KEY t1_id (t1_id,year,quarter) +) ENGINE=MyISAM; +INSERT INTO t3 VALUES +(1,0,3885,2020,1),(2,0,2064,2020,1),(3,1611670734,2225,2020,1), +(4,0,2070,2020,1),(5,1611055981,2095,2020,1),(6,1610970096,2102,2020,1), +(7,0,3974,2020,1),(153,1609851928,3892,2020,2),(154,0,3885,2020,2), +(155,0,2064,2020,2),(156,1611670717,2225,2020,2),(157,0,2070,2020,2), +(317,0,2257,2020,2),(318,0,3885,2020,3),(319,0,2064,2020,3), +(320,1611670709,2225,2020,3),(321,0,2070,2020,3),(322,0,2095,2020,3), +(323,0,2102,2020,3),(324,0,3974,2020,3),(325,0,3886,2020,3), +(326,1609939963,2104,2020,3),(327,0,3887,2020,3),(328,0,3888,2020,3), +(329,0,2148,2020,3),(330,0,3889,2020,3),(331,0,3890,2020,3), +(332,0,2179,2020,3),(333,0,2115,2020,3),(334,0,2193,2020,3), +(335,0,2213,2020,3),(336,0,3891,2020,3),(337,1609851955,3892,2020,3), +(338,1610447706,2232,2020,3),(339,0,2235,2020,3),(340,0,2237,2020,3), +(341,0,3972,2020,3),(342,1610449357,2242,2020,3),(343,0,3893,2020,3), +(344,0,2257,2020,3),(345,0,3951,2020,3),(346,0,3894,2020,3), +(347,0,3912,2020,3),(348,0,3895,2020,3),(349,0,2301,2020,3), +(350,0,2304,2020,3),(351,0,3896,2020,3); +ANALYZE TABLE t1,t2,t3; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status OK +set optimizer_switch='split_materialized=on'; +SELECT t1.id +FROM t1 +JOIN t3 +ON t3.t1_id = t1.id +JOIN (SELECT t1_id FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx +ON tx.t1_id = t1.id +WHERE t1.id BETWEEN 200 AND 100000; +id +EXPLAIN SELECT t1.id +FROM t1 +JOIN t3 +ON t3.t1_id = t1.id +JOIN (SELECT t1_id FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx +ON tx.t1_id = t1.id +WHERE t1.id BETWEEN 200 AND 100000; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 index t1_id t1_id 15 NULL 47 Using where; Using index +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t3.t1_id 1 Using index +1 PRIMARY <derived2> ref key0 key0 5 test.t3.t1_id 2 +2 LATERAL DERIVED t2 ref t1_id t1_id 5 test.t1.id 3 Using index condition; Using where +EXPLAIN FORMAT=JSON SELECT t1.id +FROM t1 +JOIN t3 +ON t3.t1_id = t1.id +JOIN (SELECT t1_id FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx +ON tx.t1_id = t1.id +WHERE t1.id BETWEEN 200 AND 100000; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t3", + "access_type": "index", + "possible_keys": ["t1_id"], + "key": "t1_id", + "key_length": "15", + "used_key_parts": ["t1_id", "YEAR", "quarter"], + "rows": 47, + "filtered": 100, + "attached_condition": "t3.t1_id between 200 and 100000 and t3.t1_id is not null", + "using_index": true + }, + "table": { + "table_name": "t1", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["id"], + "ref": ["test.t3.t1_id"], + "rows": 1, + "filtered": 100, + "using_index": true + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "5", + "used_key_parts": ["t1_id"], + "ref": ["test.t3.t1_id"], + "rows": 2, + "filtered": 100, + "materialized": { + "lateral": 1, + "query_block": { + "select_id": 2, + "table": { + "table_name": "t2", + "access_type": "ref", + "possible_keys": ["t1_id"], + "key": "t1_id", + "key_length": "5", + "used_key_parts": ["t1_id"], + "ref": ["test.t1.id"], + "rows": 3, + "filtered": 58.594, + "index_condition": "t2.t1_id between 200 and 100000 and t2.t1_id = t3.t1_id", + "attached_condition": "t2.reporting_person = 1" + } + } + } + } + } +} +set optimizer_switch='split_materialized=off'; +SELECT t1.id +FROM t1 +JOIN t3 +ON t3.t1_id = t1.id +JOIN (SELECT t1_id FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx +ON tx.t1_id = t1.id +WHERE t1.id BETWEEN 200 AND 100000; +id +set optimizer_switch='split_materialized=default'; +DROP TABLE t1,t2,t3; +# +# MDEV-27510: Splittable derived with grouping over two tables +# +CREATE TABLE ledgers ( +id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, +name VARCHAR(32) +) ENGINE=MyISAM; +CREATE TABLE charges ( +id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, +from_ledger_id BIGINT UNSIGNED NOT NULL, +to_ledger_id BIGINT UNSIGNED NOT NULL, +amount INT NOT NULL, +KEY fk_charge_from_ledger (from_ledger_id), +KEY fk_charge_to_ledger (to_ledger_id) +) ENGINE=MyISAM; +CREATE TABLE transactions ( +id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, +ledger_id BIGINT UNSIGNED NOT NULL, +KEY fk_transactions_ledger (ledger_id) +) ENGINE=MyISAM; +CREATE TABLE transaction_items ( +id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, +transaction_id BIGINT UNSIGNED NOT NULL, +charge_id BIGINT UNSIGNED, +amount INT NOT NULL, +KEY fk_items_transaction (transaction_id), +KEY fk_items_charge (charge_id) +) ENGINE=MyISAM; +INSERT INTO ledgers (id, name) VALUES +(1, 'Anna'), (2, 'John'), (3, 'Fred'); +INSERT INTO charges (id, from_ledger_id, to_ledger_id, amount) VALUES +(1, 2, 1, 200), (2, 1, 2, 330), (3, 1, 2, 640), (4, 3, 1, 640), (5, 3, 2, 1000), +(6, 3, 1, 660), (7, 2, 3, 650), (8, 3, 2, 160), (9, 2, 1, 740), (10, 3, 2, 310), +(11, 2, 1, 640), (12, 3, 2, 240), (13, 3, 2, 340), (14, 2, 1, 720), +(15, 2, 3, 100), +(16, 2, 3, 980), (17, 2, 1, 80), (18, 1, 2, 760), (19, 2, 3, 740), +(20, 2, 1, 990); +INSERT INTO transactions (id, ledger_id) VALUES +(2, 1), (3, 1), (5, 1), (8, 1), (12, 1), (18, 1), (22, 1), (28, 1), +(34, 1), (35, 1), +(40, 1), (1, 2), (4, 2), (6, 2), (10, 2), (13, 2), (16, 2), (17, 2), +(20, 2), (21, 2), +(24, 2), (26, 2), (27, 2), (29, 2), (31, 2), (33, 2), (36, 2), (37, 2), +(39, 2), (7, 3), +(9, 3), (11, 3), (14, 3), (15, 3), (19, 3), (23, 3), (25, 3), (30, 3), +(32, 3), (38, 3); +INSERT INTO transaction_items (id, transaction_id, charge_id, amount) VALUES +(1, 1, 1, -200), (2, 2, 1, 200), (3, 3, 2, -330), (4, 4, 2, 330), +(5, 5, 3, -640), +(6, 6, 3, 640), (7, 7, 4, -640), (8, 8, 4, 640), (9, 9, 5, -1000), +(10, 10, 5, 1000), +(11, 11, 6, -660), (12, 12, 6, 660), (13, 13, 7, -650), (14, 14, 7, 650), +(15, 15, 8, -160), +(16, 16, 8, 160), (17, 17, 9, -740), (18, 18, 9, 740), (19, 19, 10, -310), +(20, 20, 10, 310), +(21, 21, 11, -640), (22, 22, 11, 640), (23, 23, 12, -240), (24, 24, 12, 240), +(25, 25, 13, -340), +(26, 26, 13, 340), (27, 27, 14, -720), (28, 28, 14, 720), (29, 29, 15, -100), +(30, 30, 15, 100), +(31, 31, 16, -980), (32, 32, 16, 980), (33, 33, 17, -80), (34, 34, 17, 80), +(35, 35, 18, -760), +(36, 36, 18, 760), (37, 37, 19, -740), (38, 38, 19, 740), (39, 39, 20, -990), +(40, 40, 20, 990); +ANALYZE TABLE ledgers, charges, transactions, transaction_items; +Table Op Msg_type Msg_text +test.ledgers analyze status Engine-independent statistics collected +test.ledgers analyze status OK +test.charges analyze status Engine-independent statistics collected +test.charges analyze status OK +test.transactions analyze status Engine-independent statistics collected +test.transactions analyze status OK +test.transaction_items analyze status Engine-independent statistics collected +test.transaction_items analyze status OK +set optimizer_switch='split_materialized=on'; +SELECT +charges.id, +charges.from_ledger_id, +charges.to_ledger_id, +from_agg_items.num_rows AS from_num_rows +FROM charges +INNER JOIN ( +SELECT +transactions.ledger_id, +transaction_items.charge_id, +count(*) as num_rows +FROM transaction_items +INNER JOIN transactions ON transaction_items.transaction_id = transactions.id +GROUP BY transactions.ledger_id, transaction_items.charge_id +) AS from_agg_items +ON from_agg_items.charge_id = charges.id AND +from_agg_items.ledger_id = charges.from_ledger_id +WHERE charges.to_ledger_id = 2; +id from_ledger_id to_ledger_id from_num_rows +2 1 2 1 +3 1 2 1 +5 3 2 1 +8 3 2 1 +10 3 2 1 +12 3 2 1 +13 3 2 1 +18 1 2 1 +EXPLAIN SELECT +charges.id, +charges.from_ledger_id, +charges.to_ledger_id, +from_agg_items.num_rows AS from_num_rows +FROM charges +INNER JOIN ( +SELECT +transactions.ledger_id, +transaction_items.charge_id, +count(*) as num_rows +FROM transaction_items +INNER JOIN transactions ON transaction_items.transaction_id = transactions.id +GROUP BY transactions.ledger_id, transaction_items.charge_id +) AS from_agg_items +ON from_agg_items.charge_id = charges.id AND +from_agg_items.ledger_id = charges.from_ledger_id +WHERE charges.to_ledger_id = 2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY charges ref PRIMARY,fk_charge_from_ledger,fk_charge_to_ledger fk_charge_to_ledger 8 const 8 +1 PRIMARY <derived2> ref key0 key0 17 test.charges.from_ledger_id,test.charges.id 2 +2 LATERAL DERIVED transaction_items ref fk_items_transaction,fk_items_charge fk_items_charge 9 test.charges.id 2 +2 LATERAL DERIVED transactions eq_ref PRIMARY,fk_transactions_ledger PRIMARY 8 test.transaction_items.transaction_id 1 Using where +EXPLAIN FORMAT=JSON SELECT +charges.id, +charges.from_ledger_id, +charges.to_ledger_id, +from_agg_items.num_rows AS from_num_rows +FROM charges +INNER JOIN ( +SELECT +transactions.ledger_id, +transaction_items.charge_id, +count(*) as num_rows +FROM transaction_items +INNER JOIN transactions ON transaction_items.transaction_id = transactions.id +GROUP BY transactions.ledger_id, transaction_items.charge_id +) AS from_agg_items +ON from_agg_items.charge_id = charges.id AND +from_agg_items.ledger_id = charges.from_ledger_id +WHERE charges.to_ledger_id = 2; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "charges", + "access_type": "ref", + "possible_keys": ["PRIMARY", "fk_charge_from_ledger", "fk_charge_to_ledger"], + "key": "fk_charge_to_ledger", + "key_length": "8", + "used_key_parts": ["to_ledger_id"], + "ref": ["const"], + "rows": 8, + "filtered": 100 + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "17", + "used_key_parts": ["ledger_id", "charge_id"], + "ref": ["test.charges.from_ledger_id", "test.charges.id"], + "rows": 2, + "filtered": 100, + "materialized": { + "lateral": 1, + "query_block": { + "select_id": 2, + "table": { + "table_name": "transaction_items", + "access_type": "ref", + "possible_keys": ["fk_items_transaction", "fk_items_charge"], + "key": "fk_items_charge", + "key_length": "9", + "used_key_parts": ["charge_id"], + "ref": ["test.charges.id"], + "rows": 2, + "filtered": 100 + }, + "table": { + "table_name": "transactions", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY", "fk_transactions_ledger"], + "key": "PRIMARY", + "key_length": "8", + "used_key_parts": ["id"], + "ref": ["test.transaction_items.transaction_id"], + "rows": 1, + "filtered": 100, + "attached_condition": "transactions.ledger_id = charges.from_ledger_id" + } + } + } + } + } +} +set optimizer_switch='split_materialized=off'; +SELECT +charges.id, +charges.from_ledger_id, +charges.to_ledger_id, +from_agg_items.num_rows AS from_num_rows +FROM charges +INNER JOIN ( +SELECT +transactions.ledger_id, +transaction_items.charge_id, +count(*) as num_rows +FROM transaction_items +INNER JOIN transactions ON transaction_items.transaction_id = transactions.id +GROUP BY transactions.ledger_id, transaction_items.charge_id +) AS from_agg_items +ON from_agg_items.charge_id = charges.id AND +from_agg_items.ledger_id = charges.from_ledger_id +WHERE charges.to_ledger_id = 2; +id from_ledger_id to_ledger_id from_num_rows +2 1 2 1 +3 1 2 1 +5 3 2 1 +8 3 2 1 +10 3 2 1 +12 3 2 1 +13 3 2 1 +18 1 2 1 +EXPLAIN SELECT +charges.id, +charges.from_ledger_id, +charges.to_ledger_id, +from_agg_items.num_rows AS from_num_rows +FROM charges +INNER JOIN ( +SELECT +transactions.ledger_id, +transaction_items.charge_id, +count(*) as num_rows +FROM transaction_items +INNER JOIN transactions ON transaction_items.transaction_id = transactions.id +GROUP BY transactions.ledger_id, transaction_items.charge_id +) AS from_agg_items +ON from_agg_items.charge_id = charges.id AND +from_agg_items.ledger_id = charges.from_ledger_id +WHERE charges.to_ledger_id = 2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY charges ref PRIMARY,fk_charge_from_ledger,fk_charge_to_ledger fk_charge_to_ledger 8 const 8 +1 PRIMARY <derived2> ref key0 key0 17 test.charges.from_ledger_id,test.charges.id 4 +2 DERIVED transaction_items ALL fk_items_transaction NULL NULL NULL 40 Using temporary; Using filesort +2 DERIVED transactions eq_ref PRIMARY PRIMARY 8 test.transaction_items.transaction_id 1 +INSERT INTO charges (id, from_ledger_id, to_ledger_id, amount) VALUES +(101, 4, 2, 100), (102, 7, 2, 200); +set optimizer_switch='split_materialized=on'; +SELECT +charges.id, +charges.from_ledger_id, +charges.to_ledger_id, +from_agg_items.num_rows AS from_num_rows +FROM charges +LEFT JOIN ( +SELECT +transactions.ledger_id, +transaction_items.charge_id, +count(*) as num_rows +FROM transaction_items +INNER JOIN transactions ON transaction_items.transaction_id = transactions.id +GROUP BY transactions.ledger_id, transaction_items.charge_id +) AS from_agg_items +ON from_agg_items.charge_id = charges.id AND +from_agg_items.ledger_id = charges.from_ledger_id +WHERE charges.to_ledger_id = 2; +id from_ledger_id to_ledger_id from_num_rows +2 1 2 1 +3 1 2 1 +5 3 2 1 +8 3 2 1 +10 3 2 1 +12 3 2 1 +13 3 2 1 +18 1 2 1 +101 4 2 NULL +102 7 2 NULL +EXPLAIN SELECT +charges.id, +charges.from_ledger_id, +charges.to_ledger_id, +from_agg_items.num_rows AS from_num_rows +FROM charges +LEFT JOIN ( +SELECT +transactions.ledger_id, +transaction_items.charge_id, +count(*) as num_rows +FROM transaction_items +INNER JOIN transactions ON transaction_items.transaction_id = transactions.id +GROUP BY transactions.ledger_id, transaction_items.charge_id +) AS from_agg_items +ON from_agg_items.charge_id = charges.id AND +from_agg_items.ledger_id = charges.from_ledger_id +WHERE charges.to_ledger_id = 2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY charges ref fk_charge_to_ledger fk_charge_to_ledger 8 const 10 +1 PRIMARY <derived2> ref key0 key0 18 test.charges.from_ledger_id,test.charges.id 2 +2 LATERAL DERIVED transaction_items ref fk_items_transaction,fk_items_charge fk_items_charge 9 test.charges.id 2 +2 LATERAL DERIVED transactions eq_ref PRIMARY,fk_transactions_ledger PRIMARY 8 test.transaction_items.transaction_id 1 Using where +EXPLAIN FORMAT=JSON SELECT +charges.id, +charges.from_ledger_id, +charges.to_ledger_id, +from_agg_items.num_rows AS from_num_rows +FROM charges +LEFT JOIN ( +SELECT +transactions.ledger_id, +transaction_items.charge_id, +count(*) as num_rows +FROM transaction_items +INNER JOIN transactions ON transaction_items.transaction_id = transactions.id +GROUP BY transactions.ledger_id, transaction_items.charge_id +) AS from_agg_items +ON from_agg_items.charge_id = charges.id AND +from_agg_items.ledger_id = charges.from_ledger_id +WHERE charges.to_ledger_id = 2; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "charges", + "access_type": "ref", + "possible_keys": ["fk_charge_to_ledger"], + "key": "fk_charge_to_ledger", + "key_length": "8", + "used_key_parts": ["to_ledger_id"], + "ref": ["const"], + "rows": 10, + "filtered": 100 + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "18", + "used_key_parts": ["ledger_id", "charge_id"], + "ref": ["test.charges.from_ledger_id", "test.charges.id"], + "rows": 2, + "filtered": 100, + "materialized": { + "lateral": 1, + "query_block": { + "select_id": 2, + "table": { + "table_name": "transaction_items", + "access_type": "ref", + "possible_keys": ["fk_items_transaction", "fk_items_charge"], + "key": "fk_items_charge", + "key_length": "9", + "used_key_parts": ["charge_id"], + "ref": ["test.charges.id"], + "rows": 2, + "filtered": 100 + }, + "table": { + "table_name": "transactions", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY", "fk_transactions_ledger"], + "key": "PRIMARY", + "key_length": "8", + "used_key_parts": ["id"], + "ref": ["test.transaction_items.transaction_id"], + "rows": 1, + "filtered": 100, + "attached_condition": "transactions.ledger_id = charges.from_ledger_id" + } + } + } + } + } +} +set optimizer_switch='split_materialized=off'; +SELECT +charges.id, +charges.from_ledger_id, +charges.to_ledger_id, +from_agg_items.num_rows AS from_num_rows +FROM charges +LEFT JOIN ( +SELECT +transactions.ledger_id, +transaction_items.charge_id, +count(*) as num_rows +FROM transaction_items +INNER JOIN transactions ON transaction_items.transaction_id = transactions.id +GROUP BY transactions.ledger_id, transaction_items.charge_id +) AS from_agg_items +ON from_agg_items.charge_id = charges.id AND +from_agg_items.ledger_id = charges.from_ledger_id +WHERE charges.to_ledger_id = 2; +id from_ledger_id to_ledger_id from_num_rows +2 1 2 1 +3 1 2 1 +5 3 2 1 +8 3 2 1 +10 3 2 1 +12 3 2 1 +13 3 2 1 +18 1 2 1 +101 4 2 NULL +102 7 2 NULL +EXPLAIN SELECT +charges.id, +charges.from_ledger_id, +charges.to_ledger_id, +from_agg_items.num_rows AS from_num_rows +FROM charges +LEFT JOIN ( +SELECT +transactions.ledger_id, +transaction_items.charge_id, +count(*) as num_rows +FROM transaction_items +INNER JOIN transactions ON transaction_items.transaction_id = transactions.id +GROUP BY transactions.ledger_id, transaction_items.charge_id +) AS from_agg_items +ON from_agg_items.charge_id = charges.id AND +from_agg_items.ledger_id = charges.from_ledger_id +WHERE charges.to_ledger_id = 2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY charges ref fk_charge_to_ledger fk_charge_to_ledger 8 const 10 +1 PRIMARY <derived2> ref key0 key0 18 test.charges.from_ledger_id,test.charges.id 4 +2 DERIVED transaction_items ALL fk_items_transaction NULL NULL NULL 40 Using temporary; Using filesort +2 DERIVED transactions eq_ref PRIMARY PRIMARY 8 test.transaction_items.transaction_id 1 +set optimizer_switch='split_materialized=default'; +DROP TABLE transaction_items; +DROP TABLE transactions; +DROP TABLE charges; +DROP TABLE ledgers; # End of 10.3 tests # # MDEV-18679: materialized view with SELECT S containing materialized diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test index 9544ad34572..bd38716f2a6 100644 --- a/mysql-test/main/derived_cond_pushdown.test +++ b/mysql-test/main/derived_cond_pushdown.test @@ -3628,6 +3628,248 @@ deallocate prepare stmt; drop table t1,t2,t3; +--echo # +--echo # MDEV-MDEV-27132: Splittable derived with equality in WHERE +--echo # + +CREATE TABLE t1 ( + id int PRIMARY KEY +) ENGINE=MyISAM; + +INSERT INTO t1 VALUES +(-1),(2070),(4826),(4827),(4828),(4829),(4830),(4831),(4832),(4833),(4834), +(4835),(4836),(4837),(4838),(4839),(4840),(4841),(4842),(4843),(4844), +(4845),(4846),(4847),(4848),(4849),(4850),(4851),(4852),(4853),(4854), +(4855),(4856),(4857),(4858),(4859),(4860),(4861),(4862),(4863),(4864), +(4865),(4866),(4867),(4868),(4869),(4870),(4871),(4872),(4873),(4874), +(4875),(4876); + +CREATE TABLE t2 ( + id int PRIMARY KEY AUTO_INCREMENT, + deleted int(1), + t1_id int, + email varchar(255), + reporting_person int(1), + KEY t1_id (t1_id) +) ENGINE=MyISAM; + +INSERT INTO t2 VALUES +(1,0,2064,'1test@test.ee',1),(2,1626095588,2066,'2test@test.ee',1), +(3,0,2066,'3test@test.ee',1),(4,0,2068,'4test@test.ee',1), +(5,0,2068,'5test@test.ee',1),(6,0,2069,'6test@test.ee',1),(7,0,2070,'',0), +(8,0,2070,'',0),(9,0,2071,'',0),(10,0,2071,'',0),(11,0,2072,'',0), +(12,0,2072,'',0),(13,0,2072,'13test@test.ee',1),(14,0,2073,'14test@test.ee',1), +(15,0,2074,'15test@test.ee',1),(16,0,2075,'16test@test.ee',1),(17,0,2075,'',0), +(18,0,2075,'',0),(19,0,2076,'19test@test.ee',1),(20,0,2077,'',0), +(21,0,2078,'21test@test.ee',1),(22,0,2078,'22test@test.ee',1); + +INSERT INTO t2(deleted, t1_id, email, reporting_person) + SELECT deleted, t1_id, email, reporting_person FROM t2; +INSERT INTO t2(deleted, t1_id, email, reporting_person) + SELECT deleted, t1_id+10000, email, reporting_person FROM t2; +INSERT INTO t2(deleted, t1_id, email, reporting_person) + SELECT deleted, t1_id+20000, email, reporting_person FROM t2; +INSERT INTO t2(deleted, t1_id, email, reporting_person) + SELECT deleted, t1_id+40000, email, reporting_person FROM t2; +INSERT INTO t2(deleted, t1_id, email, reporting_person) + SELECT deleted, t1_id+80000, email, reporting_person FROM t2; +INSERT INTO t2(deleted, t1_id, email, reporting_person) + SELECT deleted, t1_id+160000, email, reporting_person FROM t2; + +CREATE TABLE t3 ( + id int PRIMARY KEY, + deleted int, + t1_id int, + YEAR int(4), + quarter int(1), + KEY t1_id (t1_id,year,quarter) +) ENGINE=MyISAM; + +INSERT INTO t3 VALUES +(1,0,3885,2020,1),(2,0,2064,2020,1),(3,1611670734,2225,2020,1), +(4,0,2070,2020,1),(5,1611055981,2095,2020,1),(6,1610970096,2102,2020,1), +(7,0,3974,2020,1),(153,1609851928,3892,2020,2),(154,0,3885,2020,2), +(155,0,2064,2020,2),(156,1611670717,2225,2020,2),(157,0,2070,2020,2), +(317,0,2257,2020,2),(318,0,3885,2020,3),(319,0,2064,2020,3), +(320,1611670709,2225,2020,3),(321,0,2070,2020,3),(322,0,2095,2020,3), +(323,0,2102,2020,3),(324,0,3974,2020,3),(325,0,3886,2020,3), +(326,1609939963,2104,2020,3),(327,0,3887,2020,3),(328,0,3888,2020,3), +(329,0,2148,2020,3),(330,0,3889,2020,3),(331,0,3890,2020,3), +(332,0,2179,2020,3),(333,0,2115,2020,3),(334,0,2193,2020,3), +(335,0,2213,2020,3),(336,0,3891,2020,3),(337,1609851955,3892,2020,3), +(338,1610447706,2232,2020,3),(339,0,2235,2020,3),(340,0,2237,2020,3), +(341,0,3972,2020,3),(342,1610449357,2242,2020,3),(343,0,3893,2020,3), +(344,0,2257,2020,3),(345,0,3951,2020,3),(346,0,3894,2020,3), +(347,0,3912,2020,3),(348,0,3895,2020,3),(349,0,2301,2020,3), +(350,0,2304,2020,3),(351,0,3896,2020,3); + +ANALYZE TABLE t1,t2,t3; + +let $q= +SELECT t1.id +FROM t1 + JOIN t3 + ON t3.t1_id = t1.id + JOIN (SELECT t1_id FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx + ON tx.t1_id = t1.id +WHERE t1.id BETWEEN 200 AND 100000; + +set optimizer_switch='split_materialized=on'; + +eval $q; +eval EXPLAIN $q; +eval EXPLAIN FORMAT=JSON $q; + +set optimizer_switch='split_materialized=off'; + +eval $q; + +set optimizer_switch='split_materialized=default'; + +DROP TABLE t1,t2,t3; + +--echo # +--echo # MDEV-27510: Splittable derived with grouping over two tables +--echo # + +CREATE TABLE ledgers ( + id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, + name VARCHAR(32) +) ENGINE=MyISAM; + +CREATE TABLE charges ( + id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, + from_ledger_id BIGINT UNSIGNED NOT NULL, + to_ledger_id BIGINT UNSIGNED NOT NULL, + amount INT NOT NULL, + KEY fk_charge_from_ledger (from_ledger_id), + KEY fk_charge_to_ledger (to_ledger_id) +) ENGINE=MyISAM; + +CREATE TABLE transactions ( + id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, + ledger_id BIGINT UNSIGNED NOT NULL, + KEY fk_transactions_ledger (ledger_id) +) ENGINE=MyISAM; + +CREATE TABLE transaction_items ( + id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, + transaction_id BIGINT UNSIGNED NOT NULL, + charge_id BIGINT UNSIGNED, + amount INT NOT NULL, + KEY fk_items_transaction (transaction_id), + KEY fk_items_charge (charge_id) +) ENGINE=MyISAM; + +INSERT INTO ledgers (id, name) VALUES +(1, 'Anna'), (2, 'John'), (3, 'Fred'); + +INSERT INTO charges (id, from_ledger_id, to_ledger_id, amount) VALUES +(1, 2, 1, 200), (2, 1, 2, 330), (3, 1, 2, 640), (4, 3, 1, 640), (5, 3, 2, 1000), +(6, 3, 1, 660), (7, 2, 3, 650), (8, 3, 2, 160), (9, 2, 1, 740), (10, 3, 2, 310), +(11, 2, 1, 640), (12, 3, 2, 240), (13, 3, 2, 340), (14, 2, 1, 720), +(15, 2, 3, 100), +(16, 2, 3, 980), (17, 2, 1, 80), (18, 1, 2, 760), (19, 2, 3, 740), +(20, 2, 1, 990); + +INSERT INTO transactions (id, ledger_id) VALUES +(2, 1), (3, 1), (5, 1), (8, 1), (12, 1), (18, 1), (22, 1), (28, 1), +(34, 1), (35, 1), +(40, 1), (1, 2), (4, 2), (6, 2), (10, 2), (13, 2), (16, 2), (17, 2), +(20, 2), (21, 2), +(24, 2), (26, 2), (27, 2), (29, 2), (31, 2), (33, 2), (36, 2), (37, 2), +(39, 2), (7, 3), +(9, 3), (11, 3), (14, 3), (15, 3), (19, 3), (23, 3), (25, 3), (30, 3), +(32, 3), (38, 3); + +INSERT INTO transaction_items (id, transaction_id, charge_id, amount) VALUES +(1, 1, 1, -200), (2, 2, 1, 200), (3, 3, 2, -330), (4, 4, 2, 330), +(5, 5, 3, -640), +(6, 6, 3, 640), (7, 7, 4, -640), (8, 8, 4, 640), (9, 9, 5, -1000), +(10, 10, 5, 1000), +(11, 11, 6, -660), (12, 12, 6, 660), (13, 13, 7, -650), (14, 14, 7, 650), +(15, 15, 8, -160), +(16, 16, 8, 160), (17, 17, 9, -740), (18, 18, 9, 740), (19, 19, 10, -310), +(20, 20, 10, 310), +(21, 21, 11, -640), (22, 22, 11, 640), (23, 23, 12, -240), (24, 24, 12, 240), +(25, 25, 13, -340), +(26, 26, 13, 340), (27, 27, 14, -720), (28, 28, 14, 720), (29, 29, 15, -100), +(30, 30, 15, 100), +(31, 31, 16, -980), (32, 32, 16, 980), (33, 33, 17, -80), (34, 34, 17, 80), +(35, 35, 18, -760), +(36, 36, 18, 760), (37, 37, 19, -740), (38, 38, 19, 740), (39, 39, 20, -990), +(40, 40, 20, 990); + +ANALYZE TABLE ledgers, charges, transactions, transaction_items; + +let $q= +SELECT + charges.id, + charges.from_ledger_id, + charges.to_ledger_id, + from_agg_items.num_rows AS from_num_rows +FROM charges +INNER JOIN ( + SELECT + transactions.ledger_id, + transaction_items.charge_id, + count(*) as num_rows + FROM transaction_items + INNER JOIN transactions ON transaction_items.transaction_id = transactions.id + GROUP BY transactions.ledger_id, transaction_items.charge_id +) AS from_agg_items +ON from_agg_items.charge_id = charges.id AND + from_agg_items.ledger_id = charges.from_ledger_id +WHERE charges.to_ledger_id = 2; + +set optimizer_switch='split_materialized=on'; +eval $q; +eval EXPLAIN $q; +eval EXPLAIN FORMAT=JSON $q; + +set optimizer_switch='split_materialized=off'; +eval $q; +eval EXPLAIN $q; + +INSERT INTO charges (id, from_ledger_id, to_ledger_id, amount) VALUES +(101, 4, 2, 100), (102, 7, 2, 200); + +let $q1= +SELECT + charges.id, + charges.from_ledger_id, + charges.to_ledger_id, + from_agg_items.num_rows AS from_num_rows +FROM charges +LEFT JOIN ( + SELECT + transactions.ledger_id, + transaction_items.charge_id, + count(*) as num_rows + FROM transaction_items + INNER JOIN transactions ON transaction_items.transaction_id = transactions.id + GROUP BY transactions.ledger_id, transaction_items.charge_id +) AS from_agg_items +ON from_agg_items.charge_id = charges.id AND + from_agg_items.ledger_id = charges.from_ledger_id +WHERE charges.to_ledger_id = 2; + +set optimizer_switch='split_materialized=on'; +eval $q1; +eval EXPLAIN $q1; +eval EXPLAIN FORMAT=JSON $q1; + +set optimizer_switch='split_materialized=off'; +eval $q1; +eval EXPLAIN $q1; + +set optimizer_switch='split_materialized=default'; + +DROP TABLE transaction_items; +DROP TABLE transactions; +DROP TABLE charges; +DROP TABLE ledgers; + --echo # End of 10.3 tests --echo # diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result index 63db8c94dde..04f79d3f018 100644 --- a/mysql-test/main/derived_split_innodb.result +++ b/mysql-test/main/derived_split_innodb.result @@ -239,4 +239,48 @@ drop table t1,t2,t3; set optimizer_switch='split_materialized=default'; set use_stat_tables=default; set optimizer_use_condition_selectivity=default; +# +# MDEV-26337: subquery with groupby and ROLLUP returns incorrect results +# (The testcase is taken from testcase for MDEV-13389 due to it being +# much smaller) +# +create table t3 (a int, b int, c char(127), index idx_b(b)) engine=myisam; +insert into t3 values +(8,11,'aa'), (5,15,'cc'), (1,14,'bb'), (2,12,'aa'), (7,17,'cc'), +(7,18,'aa'), (2,11,'aa'), (7,10,'bb'), (3,11,'dd'), (4,12,'ee'), +(5,14,'dd'), (9,12,'ee'); +create table t4 (a int, b int, c char(127), index idx(a,c)) engine=myisam; +insert into t4 values +(7,10,'cc'), (1,20,'aa'), (2,23,'bb'), (7,18,'cc'), (1,30,'bb'), +(4,71,'xx'), (3,15,'aa'), (7,82,'aa'), (8,12,'dd'), (4,15,'aa'), +(11,33,'yy'), (10,42,'zz'), (4,53,'xx'), (10,17,'yy'), (7,12,'cc'), +(8,20,'dd'), (7,32,'bb'), (1,50,'aa'), (3,40,'bb'), (3,77,'aa'); +insert into t4 select a+10, b+10, concat(c,'f') from t4; +analyze table t3,t4; +Table Op Msg_type Msg_text +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status OK +test.t4 analyze status Engine-independent statistics collected +test.t4 analyze status OK +# This should use a plan with LATERAL DERIVED: +explain select t3.a,t3.c,t.max,t.min +from t3 join +(select a, c, max(b) max, min(b) min from t4 group by a,c) t +on t3.a=t.a and t3.c=t.c +where t3.b > 15; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 range idx_b idx_b 5 NULL 2 Using index condition; Using where +1 PRIMARY <derived2> ref key0 key0 133 test.t3.a,test.t3.c 2 +2 LATERAL DERIVED t4 ref idx idx 133 test.t3.a,test.t3.c 1 +# ... and if one adds WITH ROLLUP, then LATERAL DERIVED is no longer used: +explain select t3.a,t3.c,t.max,t.min +from t3 join +(select a, c, max(b) max, min(b) min from t4 group by a,c with rollup) t +on t3.a=t.a and t3.c=t.c +where t3.b > 15; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 range idx_b idx_b 5 NULL 2 Using index condition; Using where +1 PRIMARY <derived2> ref key0 key0 133 test.t3.a,test.t3.c 4 +2 DERIVED t4 ALL NULL NULL NULL NULL 40 Using filesort +drop table t3, t4; # End of 10.3 tests diff --git a/mysql-test/main/derived_split_innodb.test b/mysql-test/main/derived_split_innodb.test index 22793b448da..2f74f5fe747 100644 --- a/mysql-test/main/derived_split_innodb.test +++ b/mysql-test/main/derived_split_innodb.test @@ -190,4 +190,40 @@ set optimizer_switch='split_materialized=default'; set use_stat_tables=default; set optimizer_use_condition_selectivity=default; +--echo # +--echo # MDEV-26337: subquery with groupby and ROLLUP returns incorrect results +--echo # (The testcase is taken from testcase for MDEV-13389 due to it being +--echo # much smaller) +--echo # + +create table t3 (a int, b int, c char(127), index idx_b(b)) engine=myisam; +insert into t3 values +(8,11,'aa'), (5,15,'cc'), (1,14,'bb'), (2,12,'aa'), (7,17,'cc'), +(7,18,'aa'), (2,11,'aa'), (7,10,'bb'), (3,11,'dd'), (4,12,'ee'), +(5,14,'dd'), (9,12,'ee'); +create table t4 (a int, b int, c char(127), index idx(a,c)) engine=myisam; +insert into t4 values +(7,10,'cc'), (1,20,'aa'), (2,23,'bb'), (7,18,'cc'), (1,30,'bb'), +(4,71,'xx'), (3,15,'aa'), (7,82,'aa'), (8,12,'dd'), (4,15,'aa'), +(11,33,'yy'), (10,42,'zz'), (4,53,'xx'), (10,17,'yy'), (7,12,'cc'), +(8,20,'dd'), (7,32,'bb'), (1,50,'aa'), (3,40,'bb'), (3,77,'aa'); +insert into t4 select a+10, b+10, concat(c,'f') from t4; +analyze table t3,t4; + +--echo # This should use a plan with LATERAL DERIVED: +explain select t3.a,t3.c,t.max,t.min +from t3 join +(select a, c, max(b) max, min(b) min from t4 group by a,c) t +on t3.a=t.a and t3.c=t.c +where t3.b > 15; + +--echo # ... and if one adds WITH ROLLUP, then LATERAL DERIVED is no longer used: +explain select t3.a,t3.c,t.max,t.min +from t3 join +(select a, c, max(b) max, min(b) min from t4 group by a,c with rollup) t +on t3.a=t.a and t3.c=t.c +where t3.b > 15; + +drop table t3, t4; + --echo # End of 10.3 tests diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result index f761a9f92de..268c0ae13b9 100644 --- a/mysql-test/main/derived_view.result +++ b/mysql-test/main/derived_view.result @@ -3500,4 +3500,134 @@ a 7 drop view v1; drop table t1; +# +# MDEV-24454 Second execution of SELECT containing set function +# MDEV-25086: whose only argument is an outer reference to a column +# of mergeable view/derived/table/CTE +# +create table t1 (a int); +create table t2 (b int); +insert into t1 values (3), (1), (3); +insert into t2 values (70), (30), (70); +create view v1 as select * from t2; +prepare stmt from " +select (select sum(b) from t1 where a=1) as r from v1; +"; +execute stmt; +r +170 +execute stmt; +r +170 +deallocate prepare stmt; +prepare stmt from " +select (select sum(b) from t1 where a=1) as r from (select * from t2) dt; +"; +execute stmt; +r +170 +execute stmt; +r +170 +deallocate prepare stmt; +prepare stmt from " +with cte as (select * from t2) +select (select sum(b) from t1 where a=1) as r from cte; +"; +execute stmt; +r +170 +execute stmt; +r +170 +deallocate prepare stmt; +prepare stmt from " +select (select sum(b) from t1 where a=1) as r +from (select * from v1 where b > 50) dt; +"; +execute stmt; +r +140 +execute stmt; +r +140 +deallocate prepare stmt; +prepare stmt from " +select (select sum(b) from t1 where a=1) as r +from (select * from (select * from t2) dt1 where b > 50) dt; +"; +execute stmt; +r +140 +execute stmt; +r +140 +deallocate prepare stmt; +prepare stmt from " +with cte as (select * from (select * from t2) dt1 where b > 50) +select (select sum(b) from t1 where a=1) as r from cte; +"; +execute stmt; +r +140 +execute stmt; +r +140 +deallocate prepare stmt; +create procedure sp1() +begin +select (select sum(b) from t1 where a=1) as r from v1; +end | +call sp1(); +r +170 +call sp1(); +r +170 +drop procedure sp1; +create procedure sp1() +begin +select (select sum(b) from t1 where a=1) as r from (select * from t2) dt; +end | +call sp1(); +r +170 +call sp1(); +r +170 +drop procedure sp1; +create procedure sp1() +begin +with cte as (select * from t2) +select (select sum(b) from t1 where a=1) as r from cte; +end | +call sp1(); +r +170 +call sp1(); +r +170 +drop procedure sp1; +drop view v1; +drop table t1,t2; +CREATE TABLE t1(f0 INT); +INSERT INTO t1 VALUES (3); +CREATE VIEW v1 AS SELECT f0 AS f1 FROM t1; +CREATE VIEW v2 AS +SELECT +(SELECT GROUP_CONCAT(v1.f1 SEPARATOR ', ') FROM v1 n) AS f2, +GROUP_CONCAT('aa' SEPARATOR ', ') AS f3 +FROM v1; +CREATE VIEW v3 AS SELECT * FROM v2; +CREATE PROCEDURE p1() +SELECT * FROM v3; +CALL p1(); +f2 f3 +3 aa +CALL p1(); +f2 f3 +3 aa +DROP PROCEDURE p1; +DROP VIEW v1,v2,v3; +DROP TABLE t1; # End of 10.2 tests diff --git a/mysql-test/main/derived_view.test b/mysql-test/main/derived_view.test index 9ab1ddd3a76..584bde2d75d 100644 --- a/mysql-test/main/derived_view.test +++ b/mysql-test/main/derived_view.test @@ -2289,4 +2289,115 @@ select * from ((select a from t1 limit 2) order by a desc) dt; drop view v1; drop table t1; +--echo # +--echo # MDEV-24454 Second execution of SELECT containing set function +--echo # MDEV-25086: whose only argument is an outer reference to a column +--echo # of mergeable view/derived/table/CTE +--echo # + +create table t1 (a int); +create table t2 (b int); +insert into t1 values (3), (1), (3); +insert into t2 values (70), (30), (70); +create view v1 as select * from t2; + +prepare stmt from " +select (select sum(b) from t1 where a=1) as r from v1; +"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +prepare stmt from " +select (select sum(b) from t1 where a=1) as r from (select * from t2) dt; +"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +prepare stmt from " +with cte as (select * from t2) +select (select sum(b) from t1 where a=1) as r from cte; +"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +prepare stmt from " +select (select sum(b) from t1 where a=1) as r +from (select * from v1 where b > 50) dt; +"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +prepare stmt from " +select (select sum(b) from t1 where a=1) as r +from (select * from (select * from t2) dt1 where b > 50) dt; +"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +prepare stmt from " +with cte as (select * from (select * from t2) dt1 where b > 50) +select (select sum(b) from t1 where a=1) as r from cte; +"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +--delimiter | +create procedure sp1() +begin +select (select sum(b) from t1 where a=1) as r from v1; +end | +--delimiter ; +call sp1(); +call sp1(); +drop procedure sp1; + +--delimiter | +create procedure sp1() +begin +select (select sum(b) from t1 where a=1) as r from (select * from t2) dt; +end | +--delimiter ; +call sp1(); +call sp1(); +drop procedure sp1; + +--delimiter | +create procedure sp1() +begin +with cte as (select * from t2) +select (select sum(b) from t1 where a=1) as r from cte; +end | +--delimiter ; +call sp1(); +call sp1(); +drop procedure sp1; + +drop view v1; +drop table t1,t2; + +CREATE TABLE t1(f0 INT); +INSERT INTO t1 VALUES (3); +CREATE VIEW v1 AS SELECT f0 AS f1 FROM t1; +CREATE VIEW v2 AS +SELECT + (SELECT GROUP_CONCAT(v1.f1 SEPARATOR ', ') FROM v1 n) AS f2, + GROUP_CONCAT('aa' SEPARATOR ', ') AS f3 +FROM v1; +CREATE VIEW v3 AS SELECT * FROM v2; + +CREATE PROCEDURE p1() + SELECT * FROM v3; +CALL p1(); +CALL p1(); + +DROP PROCEDURE p1; +DROP VIEW v1,v2,v3; +DROP TABLE t1; + --echo # End of 10.2 tests diff --git a/mysql-test/main/events_embedded.test b/mysql-test/main/events_embedded.test index 9922ea6dfee..f6921f302bf 100644 --- a/mysql-test/main/events_embedded.test +++ b/mysql-test/main/events_embedded.test @@ -2,4 +2,3 @@ --error 1193 set global event_scheduler=ON; - diff --git a/mysql-test/main/explain_innodb.result b/mysql-test/main/explain_innodb.result new file mode 100644 index 00000000000..fe51e45e35d --- /dev/null +++ b/mysql-test/main/explain_innodb.result @@ -0,0 +1,20 @@ +# +# MDEV-26249: Crash in in Explain_node::print_explain_for_children while writing to the slow query log +# +set @sql_tmp=@@slow_query_log; +SET GLOBAL slow_query_log = 1; +SET long_query_time = 0.000000; +SET log_slow_verbosity = 'explain'; +CREATE TABLE t1 ( id varchar(50), KEY (id)) engine=innodb; +SELECT * FROM (SELECT id FROM t1 GROUP BY id) dt WHERE 1=0; +id +select 1; +1 +1 +explain +SELECT * FROM (SELECT id FROM t1 GROUP BY id) dt WHERE 1=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 +2 DERIVED t1 index NULL id 53 NULL 1 Using index +SET GLOBAL slow_query_log = @sql_tmp; +drop table t1; diff --git a/mysql-test/main/explain_innodb.test b/mysql-test/main/explain_innodb.test new file mode 100644 index 00000000000..2c29a6e26da --- /dev/null +++ b/mysql-test/main/explain_innodb.test @@ -0,0 +1,20 @@ +--echo # +--echo # MDEV-26249: Crash in in Explain_node::print_explain_for_children while writing to the slow query log +--echo # + +--source include/have_innodb.inc + +set @sql_tmp=@@slow_query_log; +SET GLOBAL slow_query_log = 1; +SET long_query_time = 0.000000; +SET log_slow_verbosity = 'explain'; + +CREATE TABLE t1 ( id varchar(50), KEY (id)) engine=innodb; +SELECT * FROM (SELECT id FROM t1 GROUP BY id) dt WHERE 1=0; +select 1; + +explain +SELECT * FROM (SELECT id FROM t1 GROUP BY id) dt WHERE 1=0; + +SET GLOBAL slow_query_log = @sql_tmp; +drop table t1; diff --git a/mysql-test/main/func_default.result b/mysql-test/main/func_default.result index 9699f0795e3..8721270ca1c 100644 --- a/mysql-test/main/func_default.result +++ b/mysql-test/main/func_default.result @@ -29,6 +29,9 @@ INSERT INTO t1 VALUES (1, 'one'), (2, 'two'), (3, 'three'); SELECT s, 32 AS mi FROM t1 GROUP BY s HAVING DEFAULT(mi) IS NULL; ERROR HY000: Field 'mi' doesn't have a default value DROP TABLE t1; +# +# Start of 10.2 tests +# set timestamp=unix_timestamp('2001-01-01 10:20:30.123456'); create table t1 (a int default 1, b int default (a+1), c varchar(100) default 'foo', d text default 'bar', @@ -40,3 +43,121 @@ default(a) default(b) default(c) default(d) default(e) default(f) 1 2 foo bar 2001-01-01 10:20:30 2001-01-01 10:20:30.120000 1 11 foo bar 2001-01-01 10:20:30 2001-01-01 10:20:30.120000 drop table t1; +# +# MDEV-21639 DEFAULT(col) evaluates to a bad value in WHERE clause +# +CREATE TABLE t1 (a BIGINT NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),10))); +INSERT INTO t1 VALUES (10000); +SELECT +a, +DEFAULT(a), +CASE WHEN a THEN DEFAULT(a) END AS c, +CASE WHEN a THEN DEFAULT(a) END = 10 AS ce +FROM t1; +a DEFAULT(a) c ce +10000 10 10 1 +SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END=10; +a +10000 +DROP TABLE t1; +CREATE TABLE t1 (a DOUBLE NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),10))); +INSERT INTO t1 VALUES (10000); +SELECT +a, +DEFAULT(a), +CASE WHEN a THEN DEFAULT(a) END AS c, +CASE WHEN a THEN DEFAULT(a) END = 10 AS ce +FROM t1; +a DEFAULT(a) c ce +10000 10 10 1 +SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END=10; +a +10000 +DROP TABLE t1; +CREATE TABLE t1 (a DECIMAL(10,0) NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),10))); +INSERT INTO t1 VALUES (10000); +SELECT +a, +DEFAULT(a), +CASE WHEN a THEN DEFAULT(a) END AS c, +CASE WHEN a THEN DEFAULT(a) END = 10 AS ce +FROM t1; +a DEFAULT(a) c ce +10000 10 10 1 +SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END=10; +a +10000 +DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(32) NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),10))); +INSERT INTO t1 VALUES (10000); +SELECT +a, +DEFAULT(a), +CASE WHEN a THEN DEFAULT(a) END AS c, +CASE WHEN a THEN DEFAULT(a) END = '10' AS ce +FROM t1; +a DEFAULT(a) c ce +10000 10 10 1 +SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END='10'; +a +10000 +DROP TABLE t1; +CREATE TABLE t1 (a DATE NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP()%10,DATE'2001-01-01'))); +INSERT INTO t1 VALUES ('2000-01-01'); +SELECT +a, +DEFAULT(a), +CASE WHEN a THEN DEFAULT(a) END AS c, +CASE WHEN a THEN DEFAULT(a) END = '2001-01-01' AS ce +FROM t1; +a DEFAULT(a) c ce +2000-01-01 2001-01-01 2001-01-01 1 +SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END='2001-01-01'; +a +2000-01-01 +DROP TABLE t1; +CREATE TABLE t1 (a TIME NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP()%10,TIME'10:20:30'))); +INSERT INTO t1 VALUES ('10:00:00'); +SELECT +a, +DEFAULT(a), +CASE WHEN a THEN DEFAULT(a) END AS c, +CASE WHEN a THEN DEFAULT(a) END = '10:20:30' AS ce +FROM t1; +a DEFAULT(a) c ce +10:00:00 10:20:30 10:20:30 1 +SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END='10:20:30'; +a +10:00:00 +DROP TABLE t1; +CREATE TABLE t1 (a DATETIME NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),TIMESTAMP'2001-01-01 10:20:30'))); +INSERT INTO t1 VALUES ('2000-01-01 10:00:00'); +SELECT +a, +DEFAULT(a), +CASE WHEN a THEN DEFAULT(a) END AS c, +CASE WHEN a THEN DEFAULT(a) END = '2001-01-01 10:20:30' AS ce +FROM t1; +a DEFAULT(a) c ce +2000-01-01 10:00:00 2001-01-01 10:20:30 2001-01-01 10:20:30 1 +SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END='2001-01-01 10:20:30'; +a +2000-01-01 10:00:00 +DROP TABLE t1; +CREATE TABLE t1 (a INT NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),FALSE))); +INSERT INTO t1 VALUES (10); +SELECT +a, +DEFAULT(a), +CASE WHEN a THEN DEFAULT(a) END AS c, +CASE WHEN a THEN DEFAULT(a) END IS FALSE AS ce +FROM t1; +a DEFAULT(a) c ce +10 0 0 1 +SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END IS FALSE; +a +10 +DROP TABLE t1; +# +# End of 10.2 tests +# diff --git a/mysql-test/main/func_default.test b/mysql-test/main/func_default.test index 332bfca021f..cba7842c68f 100644 --- a/mysql-test/main/func_default.test +++ b/mysql-test/main/func_default.test @@ -34,9 +34,9 @@ INSERT INTO t1 VALUES (1, 'one'), (2, 'two'), (3, 'three'); SELECT s, 32 AS mi FROM t1 GROUP BY s HAVING DEFAULT(mi) IS NULL; DROP TABLE t1; -# -# 10.2 tests -# +--echo # +--echo # Start of 10.2 tests +--echo # set timestamp=unix_timestamp('2001-01-01 10:20:30.123456'); create table t1 (a int default 1, b int default (a+1), @@ -46,3 +46,99 @@ insert t1 () values (); insert t1 (a) values (10); select default(a),default(b),default(c),default(d),default(e),default(f) from t1; drop table t1; + +--echo # +--echo # MDEV-21639 DEFAULT(col) evaluates to a bad value in WHERE clause +--echo # + +CREATE TABLE t1 (a BIGINT NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),10))); +INSERT INTO t1 VALUES (10000); +SELECT + a, + DEFAULT(a), + CASE WHEN a THEN DEFAULT(a) END AS c, + CASE WHEN a THEN DEFAULT(a) END = 10 AS ce +FROM t1; +SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END=10; +DROP TABLE t1; + +CREATE TABLE t1 (a DOUBLE NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),10))); +INSERT INTO t1 VALUES (10000); +SELECT + a, + DEFAULT(a), + CASE WHEN a THEN DEFAULT(a) END AS c, + CASE WHEN a THEN DEFAULT(a) END = 10 AS ce +FROM t1; +SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END=10; +DROP TABLE t1; + +CREATE TABLE t1 (a DECIMAL(10,0) NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),10))); +INSERT INTO t1 VALUES (10000); +SELECT + a, + DEFAULT(a), + CASE WHEN a THEN DEFAULT(a) END AS c, + CASE WHEN a THEN DEFAULT(a) END = 10 AS ce +FROM t1; +SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END=10; +DROP TABLE t1; + +CREATE TABLE t1 (a VARCHAR(32) NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),10))); +INSERT INTO t1 VALUES (10000); +SELECT + a, + DEFAULT(a), + CASE WHEN a THEN DEFAULT(a) END AS c, + CASE WHEN a THEN DEFAULT(a) END = '10' AS ce +FROM t1; +SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END='10'; +DROP TABLE t1; + +CREATE TABLE t1 (a DATE NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP()%10,DATE'2001-01-01'))); +INSERT INTO t1 VALUES ('2000-01-01'); +SELECT + a, + DEFAULT(a), + CASE WHEN a THEN DEFAULT(a) END AS c, + CASE WHEN a THEN DEFAULT(a) END = '2001-01-01' AS ce +FROM t1; +SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END='2001-01-01'; +DROP TABLE t1; + +CREATE TABLE t1 (a TIME NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP()%10,TIME'10:20:30'))); +INSERT INTO t1 VALUES ('10:00:00'); +SELECT + a, + DEFAULT(a), + CASE WHEN a THEN DEFAULT(a) END AS c, + CASE WHEN a THEN DEFAULT(a) END = '10:20:30' AS ce +FROM t1; +SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END='10:20:30'; +DROP TABLE t1; + +CREATE TABLE t1 (a DATETIME NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),TIMESTAMP'2001-01-01 10:20:30'))); +INSERT INTO t1 VALUES ('2000-01-01 10:00:00'); +SELECT + a, + DEFAULT(a), + CASE WHEN a THEN DEFAULT(a) END AS c, + CASE WHEN a THEN DEFAULT(a) END = '2001-01-01 10:20:30' AS ce +FROM t1; +SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END='2001-01-01 10:20:30'; +DROP TABLE t1; + +CREATE TABLE t1 (a INT NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),FALSE))); +INSERT INTO t1 VALUES (10); +SELECT + a, + DEFAULT(a), + CASE WHEN a THEN DEFAULT(a) END AS c, + CASE WHEN a THEN DEFAULT(a) END IS FALSE AS ce +FROM t1; +SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END IS FALSE; +DROP TABLE t1; + +--echo # +--echo # End of 10.2 tests +--echo # diff --git a/mysql-test/main/func_json.result b/mysql-test/main/func_json.result index 1e0c542d02f..2c051d9bce5 100644 --- a/mysql-test/main/func_json.result +++ b/mysql-test/main/func_json.result @@ -940,6 +940,24 @@ SELECT JSON_REPLACE( JSON_DETAILED('["x"]'), '$.a', 'xx' ); JSON_REPLACE( JSON_DETAILED('["x"]'), '$.a', 'xx' ) ["x"] # +# MDEV-18284 JSON casting using JSON_COMPACT doesn't always work +# with values from subqueries +# +CREATE TABLE json_test(a JSON, b JSON); +INSERT INTO json_test VALUES ("[1,2,3]", '{"a":"foo"}'); +SELECT * FROM json_test; +a b +[1,2,3] {"a":"foo"} +SELECT json_object("a", json_compact(a), "b", b) +FROM (SELECT * FROM json_test) AS json_test_values; +json_object("a", json_compact(a), "b", b) +{"a": [1,2,3], "b": "{\"a\":\"foo\"}"} +SELECT json_object("a", json_compact(a), "b", json_compact(b)) +FROM (SELECT * FROM json_test) AS json_test_values; +json_object("a", json_compact(a), "b", json_compact(b)) +{"a": [1,2,3], "b": {"a":"foo"}} +DROP TABLE json_test; +# # End of 10.2 tests # # diff --git a/mysql-test/main/func_json.test b/mysql-test/main/func_json.test index bbee8c28a8b..321a9f1082f 100644 --- a/mysql-test/main/func_json.test +++ b/mysql-test/main/func_json.test @@ -558,6 +558,21 @@ SELECT SELECT JSON_REPLACE( JSON_DETAILED('["x"]'), '$.a', 'xx' ); --echo # +--echo # MDEV-18284 JSON casting using JSON_COMPACT doesn't always work +--echo # with values from subqueries +--echo # + +CREATE TABLE json_test(a JSON, b JSON); +INSERT INTO json_test VALUES ("[1,2,3]", '{"a":"foo"}'); +SELECT * FROM json_test; + +SELECT json_object("a", json_compact(a), "b", b) + FROM (SELECT * FROM json_test) AS json_test_values; +SELECT json_object("a", json_compact(a), "b", json_compact(b)) + FROM (SELECT * FROM json_test) AS json_test_values; +DROP TABLE json_test; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/func_str.result b/mysql-test/main/func_str.result index d50a77544f3..cb9393da144 100644 --- a/mysql-test/main/func_str.result +++ b/mysql-test/main/func_str.result @@ -5023,6 +5023,59 @@ SELECT NULL IN (RIGHT(AES_ENCRYPT('foo','bar'), LAST_INSERT_ID()), 'qux'); NULL IN (RIGHT(AES_ENCRYPT('foo','bar'), LAST_INSERT_ID()), 'qux') NULL # +# Bug#31374305 - FORMAT() NOT DISPLAYING WHOLE NUMBER SIDE CORRECTLY +# FOR ES_MX AND ES_ES LOCALES +# +CREATE PROCEDURE load_locale_format_table() +BEGIN +DECLARE locale_list VARCHAR(1000) DEFAULT ' + es_AR,es_BO,es_CL,es_CO,es_CR,es_DO,es_EC,es_ES,es_GT,es_HN, + es_MX,es_NI,es_PA,es_PE,es_PR,es_PY,es_SV,es_US,es_UY,es_VE'; +SET @fmt_stmt = 'INSERT INTO locale_format VALUES + (?, FORMAT(12131254123412541,2,?));'; +PREPARE stmt FROM @fmt_stmt; +WHILE locale_list != '' DO +/* get the first locale from the list */ +SET @locale = +TRIM(REPLACE((SUBSTRING_INDEX(locale_list, ',', 1)), '\n','')); +EXECUTE stmt USING @locale, @locale; +/* remove the first locale from the list */ +IF LOCATE(',', locale_list) > 0 THEN +SET locale_list = +SUBSTRING(locale_list, LOCATE(',', locale_list) + 1); +ELSE +SET locale_list = ''; +END IF; +END WHILE; +DEALLOCATE PREPARE stmt; +END| +CREATE TABLE locale_format(locale VARCHAR(10), formatted_string VARCHAR(100)); +CALL load_locale_format_table(); +SELECT * FROM locale_format; +locale formatted_string +es_AR 12.131.254.123.412.541,00 +es_BO 12.131.254.123.412.541,00 +es_CL 12.131.254.123.412.541,00 +es_CO 12.131.254.123.412.541,00 +es_CR 12 131 254 123 412 541,00 +es_DO 12,131,254,123,412,541.00 +es_EC 12.131.254.123.412.541,00 +es_ES 12.131.254.123.412.541,00 +es_GT 12,131,254,123,412,541.00 +es_HN 12,131,254,123,412,541.00 +es_MX 12,131,254,123,412,541.00 +es_NI 12,131,254,123,412,541.00 +es_PA 12,131,254,123,412,541.00 +es_PE 12,131,254,123,412,541.00 +es_PR 12,131,254,123,412,541.00 +es_PY 12.131.254.123.412.541,00 +es_SV 12,131,254,123,412,541.00 +es_US 12,131,254,123,412,541.00 +es_UY 12.131.254.123.412.541,00 +es_VE 12.131.254.123.412.541,00 +DROP PROCEDURE load_locale_format_table; +DROP TABLE locale_format; +# # End of 10.2 tests # # diff --git a/mysql-test/main/func_str.test b/mysql-test/main/func_str.test index 6a9fbc71371..f3fcd527f76 100644 --- a/mysql-test/main/func_str.test +++ b/mysql-test/main/func_str.test @@ -1997,6 +1997,42 @@ DROP TABLE t1; SELECT NULL IN (RIGHT(AES_ENCRYPT('foo','bar'), LAST_INSERT_ID()), 'qux'); +--echo # +--echo # Bug#31374305 - FORMAT() NOT DISPLAYING WHOLE NUMBER SIDE CORRECTLY +--echo # FOR ES_MX AND ES_ES LOCALES +--echo # + +DELIMITER |; +CREATE PROCEDURE load_locale_format_table() +BEGIN + DECLARE locale_list VARCHAR(1000) DEFAULT ' + es_AR,es_BO,es_CL,es_CO,es_CR,es_DO,es_EC,es_ES,es_GT,es_HN, + es_MX,es_NI,es_PA,es_PE,es_PR,es_PY,es_SV,es_US,es_UY,es_VE'; + SET @fmt_stmt = 'INSERT INTO locale_format VALUES + (?, FORMAT(12131254123412541,2,?));'; + PREPARE stmt FROM @fmt_stmt; + WHILE locale_list != '' DO + /* get the first locale from the list */ + SET @locale = + TRIM(REPLACE((SUBSTRING_INDEX(locale_list, ',', 1)), '\n','')); + EXECUTE stmt USING @locale, @locale; + /* remove the first locale from the list */ + IF LOCATE(',', locale_list) > 0 THEN + SET locale_list = + SUBSTRING(locale_list, LOCATE(',', locale_list) + 1); + ELSE + SET locale_list = ''; + END IF; + END WHILE; + DEALLOCATE PREPARE stmt; +END| +DELIMITER ;| + +CREATE TABLE locale_format(locale VARCHAR(10), formatted_string VARCHAR(100)); +CALL load_locale_format_table(); +SELECT * FROM locale_format; +DROP PROCEDURE load_locale_format_table; +DROP TABLE locale_format; --echo # --echo # End of 10.2 tests diff --git a/mysql-test/main/func_system.result b/mysql-test/main/func_system.result index 06fb7e44cf0..688f4a30b50 100644 --- a/mysql-test/main/func_system.result +++ b/mysql-test/main/func_system.result @@ -46,7 +46,7 @@ create table t1 (version char(60)) select database(), user(), version() as 'vers show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `database()` varchar(34) CHARACTER SET utf8 DEFAULT NULL, + `database()` varchar(64) CHARACTER SET utf8 DEFAULT NULL, `user()` varchar(141) CHARACTER SET utf8 DEFAULT NULL, `version` char(60) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 @@ -95,3 +95,21 @@ select left(concat(a,version()),1) from t1; left(concat(a,version()),1) a drop table t1; +# +# Start of 10.2 tests +# + +MDEV-27544 database() function under UNION ALL truncates results to 34 characters + + +SET NAMES utf8; +create database betäubungsmittelverschreibungsverordnung; +use betäubungsmittelverschreibungsverordnung; +select database() as "database" union all select database(); +database +betäubungsmittelverschreibungsverordnung +betäubungsmittelverschreibungsverordnung +drop database betäubungsmittelverschreibungsverordnung; +# +# End of 10.2 tests +# diff --git a/mysql-test/main/func_system.test b/mysql-test/main/func_system.test index fa09e81a300..d9f2bda750a 100644 --- a/mysql-test/main/func_system.test +++ b/mysql-test/main/func_system.test @@ -55,3 +55,23 @@ select left(concat(a,version()),1) from t1; drop table t1; # End of 4.1 tests + +--echo # +--echo # Start of 10.2 tests +--echo # + +--echo +--echo MDEV-27544 database() function under UNION ALL truncates results to 34 characters +--echo +--echo + +SET NAMES utf8; +create database betäubungsmittelverschreibungsverordnung; +use betäubungsmittelverschreibungsverordnung; +select database() as "database" union all select database(); +drop database betäubungsmittelverschreibungsverordnung; + + +--echo # +--echo # End of 10.2 tests +--echo # diff --git a/mysql-test/main/gis.result b/mysql-test/main/gis.result index 655649b85ff..73427052b57 100644 --- a/mysql-test/main/gis.result +++ b/mysql-test/main/gis.result @@ -1746,27 +1746,27 @@ drop table t1; SHOW CREATE TABLE information_schema.geometry_columns; Table Create Table GEOMETRY_COLUMNS CREATE TEMPORARY TABLE `GEOMETRY_COLUMNS` ( - `F_TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '', - `F_TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '', - `F_TABLE_NAME` varchar(64) NOT NULL DEFAULT '', - `F_GEOMETRY_COLUMN` varchar(64) NOT NULL DEFAULT '', - `G_TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '', - `G_TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '', - `G_TABLE_NAME` varchar(64) NOT NULL DEFAULT '', - `G_GEOMETRY_COLUMN` varchar(64) NOT NULL DEFAULT '', - `STORAGE_TYPE` tinyint(2) NOT NULL DEFAULT 0, - `GEOMETRY_TYPE` int(7) NOT NULL DEFAULT 0, - `COORD_DIMENSION` tinyint(2) NOT NULL DEFAULT 0, - `MAX_PPR` tinyint(2) NOT NULL DEFAULT 0, - `SRID` smallint(5) NOT NULL DEFAULT 0 + `F_TABLE_CATALOG` varchar(512) NOT NULL, + `F_TABLE_SCHEMA` varchar(64) NOT NULL, + `F_TABLE_NAME` varchar(64) NOT NULL, + `F_GEOMETRY_COLUMN` varchar(64) NOT NULL, + `G_TABLE_CATALOG` varchar(512) NOT NULL, + `G_TABLE_SCHEMA` varchar(64) NOT NULL, + `G_TABLE_NAME` varchar(64) NOT NULL, + `G_GEOMETRY_COLUMN` varchar(64) NOT NULL, + `STORAGE_TYPE` tinyint(2) NOT NULL, + `GEOMETRY_TYPE` int(7) NOT NULL, + `COORD_DIMENSION` tinyint(2) NOT NULL, + `MAX_PPR` tinyint(2) NOT NULL, + `SRID` smallint(5) NOT NULL ) ENGINE=MEMORY DEFAULT CHARSET=utf8 SHOW CREATE TABLE information_schema.spatial_ref_sys; Table Create Table SPATIAL_REF_SYS CREATE TEMPORARY TABLE `SPATIAL_REF_SYS` ( - `SRID` smallint(5) NOT NULL DEFAULT 0, - `AUTH_NAME` varchar(512) NOT NULL DEFAULT '', - `AUTH_SRID` int(5) NOT NULL DEFAULT 0, - `SRTEXT` varchar(2048) NOT NULL DEFAULT '' + `SRID` smallint(5) NOT NULL, + `AUTH_NAME` varchar(512) NOT NULL, + `AUTH_SRID` int(5) NOT NULL, + `SRTEXT` varchar(2048) NOT NULL ) ENGINE=MEMORY DEFAULT CHARSET=utf8 create table t1(g GEOMETRY, pt POINT); create table t2(g LINESTRING, pl POLYGON); diff --git a/mysql-test/main/index_merge_myisam.result b/mysql-test/main/index_merge_myisam.result index 55caf051720..92c25259be5 100644 --- a/mysql-test/main/index_merge_myisam.result +++ b/mysql-test/main/index_merge_myisam.result @@ -180,17 +180,29 @@ or id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7 i3,i5 4,4 NULL 9 Using sort_union(i3,i5); Using where explain select * from t0 where -((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4)) +((key3 < 4 or key5 < 4) and (key1 < 4 or key2 < 4)) or ((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6)); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 ALL i1,i2,i3,i5,i6 NULL NULL NULL 1024 Using where explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where -((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4)) +((key3 < 4 or key5 < 4) and (key1 < 4 or key2 < 4)) +or +((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 4,4 NULL 1024 Using sort_union(i3,i5); Using where +explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where +((key3 < 5 or key5 < 4) and (key1 < 4 or key2 < 4)) or ((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6)); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 0,4 NULL 1024 Using sort_union(i3,i5); Using where +1 SIMPLE t0 ALL i1,i2,i3,i5,i6 NULL NULL NULL 1024 Using where +explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where +((key3 < 10 or key5 < 4) and (key1 < 4 or key2 < 4)) +or +((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL i1,i2,i3,i5,i6 NULL NULL NULL 1024 Using where select * from t0 where key1 < 5 or key8 < 4 order by key1; key1 key2 key3 key4 key5 key6 key7 key8 1 1 1 1 1 1 1 1023 @@ -1690,7 +1702,7 @@ SELECT * FROM t1 FORCE KEY (PRIMARY , i , c1 , c2) WHERE pk = 255 OR i = 22 OR (pk IN (1 , 136) AND c2 IN ('c' , 'w') AND (c1 NOT BETWEEN 'e' AND 'i' OR c2 > 'g')) OR (pk is not null and (pk <1 or pk>1)) ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index_merge PRIMARY,c1,i,c2 PRIMARY,i 0,5 NULL 69 Using sort_union(PRIMARY,i); Using where +1 SIMPLE t1 ALL PRIMARY,c1,i,c2 NULL NULL NULL 69 Using where DROP TABLE t1; set optimizer_switch= @optimizer_switch_save; # diff --git a/mysql-test/main/information_schema.result b/mysql-test/main/information_schema.result index cc7f10a32fd..cd726f7298f 100644 --- a/mysql-test/main/information_schema.result +++ b/mysql-test/main/information_schema.result @@ -227,7 +227,7 @@ Field Type Collation Null Key Default Extra Privileges Comment Insert_priv enum('N','Y') utf8_general_ci NO N select,insert,update,references show full columns from v1; Field Type Collation Null Key Default Extra Privileges Comment -c varchar(64) utf8_general_ci NO select,insert,update,references +c varchar(64) utf8_general_ci NO NULL select,insert,update,references select * from information_schema.COLUMNS where table_name="t1" and column_name= "a"; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT IS_GENERATED GENERATION_EXPRESSION @@ -605,19 +605,19 @@ drop table t1; SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets; Table Create Table CHARACTER_SETS CREATE TEMPORARY TABLE `CHARACTER_SETS` ( - `CHARACTER_SET_NAME` varchar(32) NOT NULL DEFAULT '', - `DEFAULT_COLLATE_NAME` varchar(32) NOT NULL DEFAULT '', - `DESCRIPTION` varchar(60) NOT NULL DEFAULT '', - `MAXLEN` bigint(3) NOT NULL DEFAULT 0 + `CHARACTER_SET_NAME` varchar(32) NOT NULL, + `DEFAULT_COLLATE_NAME` varchar(32) NOT NULL, + `DESCRIPTION` varchar(60) NOT NULL, + `MAXLEN` bigint(3) NOT NULL ) ENGINE=MEMORY DEFAULT CHARSET=utf8 set names latin2; SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets; Table Create Table CHARACTER_SETS CREATE TEMPORARY TABLE `CHARACTER_SETS` ( - `CHARACTER_SET_NAME` varchar(32) NOT NULL DEFAULT '', - `DEFAULT_COLLATE_NAME` varchar(32) NOT NULL DEFAULT '', - `DESCRIPTION` varchar(60) NOT NULL DEFAULT '', - `MAXLEN` bigint(3) NOT NULL DEFAULT 0 + `CHARACTER_SET_NAME` varchar(32) NOT NULL, + `DEFAULT_COLLATE_NAME` varchar(32) NOT NULL, + `DESCRIPTION` varchar(60) NOT NULL, + `MAXLEN` bigint(3) NOT NULL ) ENGINE=MEMORY DEFAULT CHARSET=utf8 set names latin1; create table t1 select * from information_schema.CHARACTER_SETS @@ -629,10 +629,10 @@ alter table t1 default character set utf8; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `CHARACTER_SET_NAME` varchar(32) NOT NULL DEFAULT '', - `DEFAULT_COLLATE_NAME` varchar(32) NOT NULL DEFAULT '', - `DESCRIPTION` varchar(60) NOT NULL DEFAULT '', - `MAXLEN` bigint(3) NOT NULL DEFAULT 0 + `CHARACTER_SET_NAME` varchar(32) NOT NULL, + `DEFAULT_COLLATE_NAME` varchar(32) NOT NULL, + `DESCRIPTION` varchar(60) NOT NULL, + `MAXLEN` bigint(3) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 drop table t1; create view v1 as select * from information_schema.TABLES; @@ -2367,5 +2367,30 @@ select * from t1 where (name, len) in (select name, len from information_schema name len drop table t1; # +# MDEV-20254 Problems with EMPTY_STRING_IS_NULL and I_S tables +# +SET SQL_MODE= 'EMPTY_STRING_IS_NULL'; +CREATE OR REPLACE TABLE t1 AS SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE 1 = 0; +SHOW returned: CREATE TABLE `t1` ( + `TABLE_NAME` varchar(64) CHARACTER SET utf8 NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE `t1` ( + `TABLE_NAME` varchar(64) CHARACTER SET utf8 NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `TABLE_NAME` varchar(64) CHARACTER SET utf8 NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +SET SQL_MODE=DEFAULT; +SET SQL_MODE= 'EMPTY_STRING_IS_NULL'; +CREATE OR REPLACE TABLE t1 AS SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE 1 = 0; +DROP TABLE t1; +# Executing the statement returned from SHOW CREATE TABLE +DROP TABLE t1; +SET SQL_MODE=DEFAULT; +# # End of 10.3 tests # diff --git a/mysql-test/main/information_schema.test b/mysql-test/main/information_schema.test index 360717a2685..c7087958f79 100644 --- a/mysql-test/main/information_schema.test +++ b/mysql-test/main/information_schema.test @@ -2075,5 +2075,38 @@ select * from t1 where (name, len) in (select name, len from information_schema drop table t1; --echo # +--echo # MDEV-20254 Problems with EMPTY_STRING_IS_NULL and I_S tables +--echo # + +# Test one column with detailed output + +SET SQL_MODE= 'EMPTY_STRING_IS_NULL'; +CREATE OR REPLACE TABLE t1 AS SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE 1 = 0; +--let $myvar= query_get_value(SHOW CREATE TABLE test.t1, Create Table, 1) +--echo SHOW returned: $myvar +DROP TABLE t1; +--eval $myvar +SHOW CREATE TABLE t1; +DROP TABLE t1; +SET SQL_MODE=DEFAULT; + +# Test all columns without detailed output. +# Just make sure the SHOW CREATE TABLE result +# can be passed back to the server without errors. + +SET SQL_MODE= 'EMPTY_STRING_IS_NULL'; +CREATE OR REPLACE TABLE t1 AS SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE 1 = 0; +--let $myvar= query_get_value(SHOW CREATE TABLE test.t1, Create Table, 1) +DROP TABLE t1; +--disable_query_log +--echo # Executing the statement returned from SHOW CREATE TABLE +--eval $myvar +--enable_query_log +DROP TABLE t1; +SET SQL_MODE=DEFAULT; + + + +--echo # --echo # End of 10.3 tests --echo # diff --git a/mysql-test/main/information_schema_inno.result b/mysql-test/main/information_schema_inno.result index d952e4372ca..c81631728f0 100644 --- a/mysql-test/main/information_schema_inno.result +++ b/mysql-test/main/information_schema_inno.result @@ -1,4 +1,3 @@ -DROP TABLE IF EXISTS t1,t2,t3; CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB; CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id, id), FOREIGN KEY (t1_id) REFERENCES t1(id) ON DELETE CASCADE, diff --git a/mysql-test/main/information_schema_inno.test b/mysql-test/main/information_schema_inno.test index 3cdbb8111d9..6c50ff3ece0 100644 --- a/mysql-test/main/information_schema_inno.test +++ b/mysql-test/main/information_schema_inno.test @@ -1,8 +1,6 @@ -- source include/testdb_only.inc -- source include/have_innodb.inc ---disable_warnings -DROP TABLE IF EXISTS t1,t2,t3; ---enable_warnings +-- source include/have_symlink.inc # # Test for KEY_COLUMN_USAGE & TABLE_CONSTRAINTS tables diff --git a/mysql-test/main/information_schema_parameters.result b/mysql-test/main/information_schema_parameters.result index 02f97159f28..81fed4c1ae4 100644 --- a/mysql-test/main/information_schema_parameters.result +++ b/mysql-test/main/information_schema_parameters.result @@ -3,22 +3,22 @@ USE INFORMATION_SCHEMA; SHOW CREATE TABLE INFORMATION_SCHEMA.PARAMETERS; Table Create Table PARAMETERS CREATE TEMPORARY TABLE `PARAMETERS` ( - `SPECIFIC_CATALOG` varchar(512) NOT NULL DEFAULT '', - `SPECIFIC_SCHEMA` varchar(64) NOT NULL DEFAULT '', - `SPECIFIC_NAME` varchar(64) NOT NULL DEFAULT '', - `ORDINAL_POSITION` int(21) NOT NULL DEFAULT 0, - `PARAMETER_MODE` varchar(5) DEFAULT NULL, - `PARAMETER_NAME` varchar(64) DEFAULT NULL, - `DATA_TYPE` varchar(64) NOT NULL DEFAULT '', - `CHARACTER_MAXIMUM_LENGTH` int(21) DEFAULT NULL, - `CHARACTER_OCTET_LENGTH` int(21) DEFAULT NULL, - `NUMERIC_PRECISION` int(21) DEFAULT NULL, - `NUMERIC_SCALE` int(21) DEFAULT NULL, - `DATETIME_PRECISION` bigint(21) unsigned DEFAULT NULL, - `CHARACTER_SET_NAME` varchar(64) DEFAULT NULL, - `COLLATION_NAME` varchar(64) DEFAULT NULL, - `DTD_IDENTIFIER` longtext NOT NULL DEFAULT '', - `ROUTINE_TYPE` varchar(9) NOT NULL DEFAULT '' + `SPECIFIC_CATALOG` varchar(512) NOT NULL, + `SPECIFIC_SCHEMA` varchar(64) NOT NULL, + `SPECIFIC_NAME` varchar(64) NOT NULL, + `ORDINAL_POSITION` int(21) NOT NULL, + `PARAMETER_MODE` varchar(5), + `PARAMETER_NAME` varchar(64), + `DATA_TYPE` varchar(64) NOT NULL, + `CHARACTER_MAXIMUM_LENGTH` int(21), + `CHARACTER_OCTET_LENGTH` int(21), + `NUMERIC_PRECISION` int(21), + `NUMERIC_SCALE` int(21), + `DATETIME_PRECISION` bigint(21) unsigned, + `CHARACTER_SET_NAME` varchar(64), + `COLLATION_NAME` varchar(64), + `DTD_IDENTIFIER` longtext NOT NULL, + `ROUTINE_TYPE` varchar(9) NOT NULL ) DEFAULT CHARSET=utf8 SELECT * FROM information_schema.columns WHERE table_schema = 'information_schema' @@ -29,7 +29,7 @@ TABLE_SCHEMA information_schema TABLE_NAME PARAMETERS COLUMN_NAME SPECIFIC_CATALOG ORDINAL_POSITION 1 -COLUMN_DEFAULT '' +COLUMN_DEFAULT NULL IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 512 @@ -51,7 +51,7 @@ TABLE_SCHEMA information_schema TABLE_NAME PARAMETERS COLUMN_NAME SPECIFIC_SCHEMA ORDINAL_POSITION 2 -COLUMN_DEFAULT '' +COLUMN_DEFAULT NULL IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 64 @@ -73,7 +73,7 @@ TABLE_SCHEMA information_schema TABLE_NAME PARAMETERS COLUMN_NAME SPECIFIC_NAME ORDINAL_POSITION 3 -COLUMN_DEFAULT '' +COLUMN_DEFAULT NULL IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 64 @@ -95,7 +95,7 @@ TABLE_SCHEMA information_schema TABLE_NAME PARAMETERS COLUMN_NAME ORDINAL_POSITION ORDINAL_POSITION 4 -COLUMN_DEFAULT 0 +COLUMN_DEFAULT NULL IS_NULLABLE NO DATA_TYPE int CHARACTER_MAXIMUM_LENGTH NULL @@ -161,7 +161,7 @@ TABLE_SCHEMA information_schema TABLE_NAME PARAMETERS COLUMN_NAME DATA_TYPE ORDINAL_POSITION 7 -COLUMN_DEFAULT '' +COLUMN_DEFAULT NULL IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 64 @@ -337,7 +337,7 @@ TABLE_SCHEMA information_schema TABLE_NAME PARAMETERS COLUMN_NAME DTD_IDENTIFIER ORDINAL_POSITION 15 -COLUMN_DEFAULT '' +COLUMN_DEFAULT NULL IS_NULLABLE NO DATA_TYPE longtext CHARACTER_MAXIMUM_LENGTH 4294967295 @@ -359,7 +359,7 @@ TABLE_SCHEMA information_schema TABLE_NAME PARAMETERS COLUMN_NAME ROUTINE_TYPE ORDINAL_POSITION 16 -COLUMN_DEFAULT '' +COLUMN_DEFAULT NULL IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 9 @@ -378,13 +378,13 @@ IS_GENERATED NEVER GENERATION_EXPRESSION NULL DESCRIBE INFORMATION_SCHEMA.PARAMETERS; Field Type Null Key Default Extra -SPECIFIC_CATALOG varchar(512) NO -SPECIFIC_SCHEMA varchar(64) NO -SPECIFIC_NAME varchar(64) NO -ORDINAL_POSITION int(21) NO 0 +SPECIFIC_CATALOG varchar(512) NO NULL +SPECIFIC_SCHEMA varchar(64) NO NULL +SPECIFIC_NAME varchar(64) NO NULL +ORDINAL_POSITION int(21) NO NULL PARAMETER_MODE varchar(5) YES NULL PARAMETER_NAME varchar(64) YES NULL -DATA_TYPE varchar(64) NO +DATA_TYPE varchar(64) NO NULL CHARACTER_MAXIMUM_LENGTH int(21) YES NULL CHARACTER_OCTET_LENGTH int(21) YES NULL NUMERIC_PRECISION int(21) YES NULL @@ -392,8 +392,8 @@ NUMERIC_SCALE int(21) YES NULL DATETIME_PRECISION bigint(21) unsigned YES NULL CHARACTER_SET_NAME varchar(64) YES NULL COLLATION_NAME varchar(64) YES NULL -DTD_IDENTIFIER longtext NO -ROUTINE_TYPE varchar(9) NO +DTD_IDENTIFIER longtext NO NULL +ROUTINE_TYPE varchar(9) NO NULL # ========== parameters.2 ========== DROP DATABASE IF EXISTS i_s_parameters_test; CREATE DATABASE i_s_parameters_test; diff --git a/mysql-test/main/information_schema_routines.result b/mysql-test/main/information_schema_routines.result index eaa69adf740..65779351289 100644 --- a/mysql-test/main/information_schema_routines.result +++ b/mysql-test/main/information_schema_routines.result @@ -5,37 +5,37 @@ USE INFORMATION_SCHEMA; SHOW CREATE TABLE INFORMATION_SCHEMA.ROUTINES; Table Create Table ROUTINES CREATE TEMPORARY TABLE `ROUTINES` ( - `SPECIFIC_NAME` varchar(64) NOT NULL DEFAULT '', - `ROUTINE_CATALOG` varchar(512) NOT NULL DEFAULT '', - `ROUTINE_SCHEMA` varchar(64) NOT NULL DEFAULT '', - `ROUTINE_NAME` varchar(64) NOT NULL DEFAULT '', - `ROUTINE_TYPE` varchar(13) NOT NULL DEFAULT '', - `DATA_TYPE` varchar(64) NOT NULL DEFAULT '', - `CHARACTER_MAXIMUM_LENGTH` int(21) DEFAULT NULL, - `CHARACTER_OCTET_LENGTH` int(21) DEFAULT NULL, - `NUMERIC_PRECISION` int(21) DEFAULT NULL, - `NUMERIC_SCALE` int(21) DEFAULT NULL, - `DATETIME_PRECISION` bigint(21) unsigned DEFAULT NULL, - `CHARACTER_SET_NAME` varchar(64) DEFAULT NULL, - `COLLATION_NAME` varchar(64) DEFAULT NULL, - `DTD_IDENTIFIER` longtext DEFAULT NULL, - `ROUTINE_BODY` varchar(8) NOT NULL DEFAULT '', - `ROUTINE_DEFINITION` longtext DEFAULT NULL, - `EXTERNAL_NAME` varchar(64) DEFAULT NULL, - `EXTERNAL_LANGUAGE` varchar(64) DEFAULT NULL, - `PARAMETER_STYLE` varchar(8) NOT NULL DEFAULT '', - `IS_DETERMINISTIC` varchar(3) NOT NULL DEFAULT '', - `SQL_DATA_ACCESS` varchar(64) NOT NULL DEFAULT '', - `SQL_PATH` varchar(64) DEFAULT NULL, - `SECURITY_TYPE` varchar(7) NOT NULL DEFAULT '', - `CREATED` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `LAST_ALTERED` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', - `SQL_MODE` varchar(8192) NOT NULL DEFAULT '', - `ROUTINE_COMMENT` longtext NOT NULL DEFAULT '', - `DEFINER` varchar(189) NOT NULL DEFAULT '', - `CHARACTER_SET_CLIENT` varchar(32) NOT NULL DEFAULT '', - `COLLATION_CONNECTION` varchar(32) NOT NULL DEFAULT '', - `DATABASE_COLLATION` varchar(32) NOT NULL DEFAULT '' + `SPECIFIC_NAME` varchar(64) NOT NULL, + `ROUTINE_CATALOG` varchar(512) NOT NULL, + `ROUTINE_SCHEMA` varchar(64) NOT NULL, + `ROUTINE_NAME` varchar(64) NOT NULL, + `ROUTINE_TYPE` varchar(13) NOT NULL, + `DATA_TYPE` varchar(64) NOT NULL, + `CHARACTER_MAXIMUM_LENGTH` int(21), + `CHARACTER_OCTET_LENGTH` int(21), + `NUMERIC_PRECISION` int(21), + `NUMERIC_SCALE` int(21), + `DATETIME_PRECISION` bigint(21) unsigned, + `CHARACTER_SET_NAME` varchar(64), + `COLLATION_NAME` varchar(64), + `DTD_IDENTIFIER` longtext, + `ROUTINE_BODY` varchar(8) NOT NULL, + `ROUTINE_DEFINITION` longtext, + `EXTERNAL_NAME` varchar(64), + `EXTERNAL_LANGUAGE` varchar(64), + `PARAMETER_STYLE` varchar(8) NOT NULL, + `IS_DETERMINISTIC` varchar(3) NOT NULL, + `SQL_DATA_ACCESS` varchar(64) NOT NULL, + `SQL_PATH` varchar(64), + `SECURITY_TYPE` varchar(7) NOT NULL, + `CREATED` datetime NOT NULL, + `LAST_ALTERED` datetime NOT NULL, + `SQL_MODE` varchar(8192) NOT NULL, + `ROUTINE_COMMENT` longtext NOT NULL, + `DEFINER` varchar(189) NOT NULL, + `CHARACTER_SET_CLIENT` varchar(32) NOT NULL, + `COLLATION_CONNECTION` varchar(32) NOT NULL, + `DATABASE_COLLATION` varchar(32) NOT NULL ) DEFAULT CHARSET=utf8 SELECT * FROM information_schema.columns WHERE table_schema = 'information_schema' @@ -46,7 +46,7 @@ TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME SPECIFIC_NAME ORDINAL_POSITION 1 -COLUMN_DEFAULT '' +COLUMN_DEFAULT NULL IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 64 @@ -68,7 +68,7 @@ TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME ROUTINE_CATALOG ORDINAL_POSITION 2 -COLUMN_DEFAULT '' +COLUMN_DEFAULT NULL IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 512 @@ -90,7 +90,7 @@ TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME ROUTINE_SCHEMA ORDINAL_POSITION 3 -COLUMN_DEFAULT '' +COLUMN_DEFAULT NULL IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 64 @@ -112,7 +112,7 @@ TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME ROUTINE_NAME ORDINAL_POSITION 4 -COLUMN_DEFAULT '' +COLUMN_DEFAULT NULL IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 64 @@ -134,7 +134,7 @@ TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME ROUTINE_TYPE ORDINAL_POSITION 5 -COLUMN_DEFAULT '' +COLUMN_DEFAULT NULL IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 13 @@ -156,7 +156,7 @@ TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME DATA_TYPE ORDINAL_POSITION 6 -COLUMN_DEFAULT '' +COLUMN_DEFAULT NULL IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 64 @@ -354,7 +354,7 @@ TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME ROUTINE_BODY ORDINAL_POSITION 15 -COLUMN_DEFAULT '' +COLUMN_DEFAULT NULL IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 8 @@ -442,7 +442,7 @@ TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME PARAMETER_STYLE ORDINAL_POSITION 19 -COLUMN_DEFAULT '' +COLUMN_DEFAULT NULL IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 8 @@ -464,7 +464,7 @@ TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME IS_DETERMINISTIC ORDINAL_POSITION 20 -COLUMN_DEFAULT '' +COLUMN_DEFAULT NULL IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 3 @@ -486,7 +486,7 @@ TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME SQL_DATA_ACCESS ORDINAL_POSITION 21 -COLUMN_DEFAULT '' +COLUMN_DEFAULT NULL IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 64 @@ -530,7 +530,7 @@ TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME SECURITY_TYPE ORDINAL_POSITION 23 -COLUMN_DEFAULT '' +COLUMN_DEFAULT NULL IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 7 @@ -552,7 +552,7 @@ TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME CREATED ORDINAL_POSITION 24 -COLUMN_DEFAULT '0000-00-00 00:00:00' +COLUMN_DEFAULT NULL IS_NULLABLE NO DATA_TYPE datetime CHARACTER_MAXIMUM_LENGTH NULL @@ -574,7 +574,7 @@ TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME LAST_ALTERED ORDINAL_POSITION 25 -COLUMN_DEFAULT '0000-00-00 00:00:00' +COLUMN_DEFAULT NULL IS_NULLABLE NO DATA_TYPE datetime CHARACTER_MAXIMUM_LENGTH NULL @@ -596,7 +596,7 @@ TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME SQL_MODE ORDINAL_POSITION 26 -COLUMN_DEFAULT '' +COLUMN_DEFAULT NULL IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 8192 @@ -618,7 +618,7 @@ TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME ROUTINE_COMMENT ORDINAL_POSITION 27 -COLUMN_DEFAULT '' +COLUMN_DEFAULT NULL IS_NULLABLE NO DATA_TYPE longtext CHARACTER_MAXIMUM_LENGTH 4294967295 @@ -640,7 +640,7 @@ TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME DEFINER ORDINAL_POSITION 28 -COLUMN_DEFAULT '' +COLUMN_DEFAULT NULL IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 189 @@ -662,7 +662,7 @@ TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME CHARACTER_SET_CLIENT ORDINAL_POSITION 29 -COLUMN_DEFAULT '' +COLUMN_DEFAULT NULL IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 32 @@ -684,7 +684,7 @@ TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME COLLATION_CONNECTION ORDINAL_POSITION 30 -COLUMN_DEFAULT '' +COLUMN_DEFAULT NULL IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 32 @@ -706,7 +706,7 @@ TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME DATABASE_COLLATION ORDINAL_POSITION 31 -COLUMN_DEFAULT '' +COLUMN_DEFAULT NULL IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 32 @@ -725,12 +725,12 @@ IS_GENERATED NEVER GENERATION_EXPRESSION NULL DESCRIBE INFORMATION_SCHEMA.ROUTINES; Field Type Null Key Default Extra -SPECIFIC_NAME varchar(64) NO -ROUTINE_CATALOG varchar(512) NO -ROUTINE_SCHEMA varchar(64) NO -ROUTINE_NAME varchar(64) NO -ROUTINE_TYPE varchar(13) NO -DATA_TYPE varchar(64) NO +SPECIFIC_NAME varchar(64) NO NULL +ROUTINE_CATALOG varchar(512) NO NULL +ROUTINE_SCHEMA varchar(64) NO NULL +ROUTINE_NAME varchar(64) NO NULL +ROUTINE_TYPE varchar(13) NO NULL +DATA_TYPE varchar(64) NO NULL CHARACTER_MAXIMUM_LENGTH int(21) YES NULL CHARACTER_OCTET_LENGTH int(21) YES NULL NUMERIC_PRECISION int(21) YES NULL @@ -739,23 +739,23 @@ DATETIME_PRECISION bigint(21) unsigned YES NULL CHARACTER_SET_NAME varchar(64) YES NULL COLLATION_NAME varchar(64) YES NULL DTD_IDENTIFIER longtext YES NULL -ROUTINE_BODY varchar(8) NO +ROUTINE_BODY varchar(8) NO NULL ROUTINE_DEFINITION longtext YES NULL EXTERNAL_NAME varchar(64) YES NULL EXTERNAL_LANGUAGE varchar(64) YES NULL -PARAMETER_STYLE varchar(8) NO -IS_DETERMINISTIC varchar(3) NO -SQL_DATA_ACCESS varchar(64) NO +PARAMETER_STYLE varchar(8) NO NULL +IS_DETERMINISTIC varchar(3) NO NULL +SQL_DATA_ACCESS varchar(64) NO NULL SQL_PATH varchar(64) YES NULL -SECURITY_TYPE varchar(7) NO -CREATED datetime NO 0000-00-00 00:00:00 -LAST_ALTERED datetime NO 0000-00-00 00:00:00 -SQL_MODE varchar(8192) NO -ROUTINE_COMMENT longtext NO -DEFINER varchar(189) NO -CHARACTER_SET_CLIENT varchar(32) NO -COLLATION_CONNECTION varchar(32) NO -DATABASE_COLLATION varchar(32) NO +SECURITY_TYPE varchar(7) NO NULL +CREATED datetime NO NULL +LAST_ALTERED datetime NO NULL +SQL_MODE varchar(8192) NO NULL +ROUTINE_COMMENT longtext NO NULL +DEFINER varchar(189) NO NULL +CHARACTER_SET_CLIENT varchar(32) NO NULL +COLLATION_CONNECTION varchar(32) NO NULL +DATABASE_COLLATION varchar(32) NO NULL # ========== routines.2 ========== DROP DATABASE IF EXISTS i_s_routines_test; CREATE DATABASE i_s_routines_test; diff --git a/mysql-test/main/insert_select.result b/mysql-test/main/insert_select.result index 5094638c92b..e85c4982137 100644 --- a/mysql-test/main/insert_select.result +++ b/mysql-test/main/insert_select.result @@ -865,3 +865,22 @@ t2 CREATE TABLE `t2` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1, t2; End of 5.5 tests +# +# Beginning of 10.2 test +# +# MDEV-26698: Incorrect row number upon INSERT .. SELECT from the same +# table: rows are counted twice +# +CREATE TABLE t1(a TINYINT); +INSERT INTO t1 VALUES (1), (100); +INSERT INTO t1 SELECT a*2 FROM t1; +Warnings: +Warning 1264 Out of range value for column 'a' at row 2 +TRUNCATE TABLE t1; +# using ORDER BY +INSERT INTO t1 VALUES(1), (2), (100), (3); +INSERT INTO t1 SELECT a*2 FROM t1 ORDER BY a; +Warnings: +Warning 1264 Out of range value for column 'a' at row 4 +DROP TABLE t1; +# End of 10.2 test diff --git a/mysql-test/main/insert_select.test b/mysql-test/main/insert_select.test index 0b5cdf95daf..91b2cc6f981 100644 --- a/mysql-test/main/insert_select.test +++ b/mysql-test/main/insert_select.test @@ -435,3 +435,28 @@ show create table t2; drop table t1, t2; --echo End of 5.5 tests + +--echo # +--echo # Beginning of 10.2 test +--echo # +--echo # MDEV-26698: Incorrect row number upon INSERT .. SELECT from the same +--echo # table: rows are counted twice +--echo # + +CREATE TABLE t1(a TINYINT); + +INSERT INTO t1 VALUES (1), (100); + +INSERT INTO t1 SELECT a*2 FROM t1; + +TRUNCATE TABLE t1; + +--echo # using ORDER BY + +INSERT INTO t1 VALUES(1), (2), (100), (3); + +INSERT INTO t1 SELECT a*2 FROM t1 ORDER BY a; + +DROP TABLE t1; + +--echo # End of 10.2 test diff --git a/mysql-test/main/mysql_client_test-master.opt b/mysql-test/main/mysql_client_test-master.opt index 8d49abf6a10..e3d42121b45 100644 --- a/mysql-test/main/mysql_client_test-master.opt +++ b/mysql-test/main/mysql_client_test-master.opt @@ -3,3 +3,4 @@ --log-output=FILE,TABLE --max-allowed-packet=32000000 --proxy-protocol-networks=* +--sequence=on diff --git a/mysql-test/main/mysql_client_test_comp-master.opt b/mysql-test/main/mysql_client_test_comp-master.opt index 6c4a5e4c782..ccaa4a8ee6e 100644 --- a/mysql-test/main/mysql_client_test_comp-master.opt +++ b/mysql-test/main/mysql_client_test_comp-master.opt @@ -1,3 +1,4 @@ --loose-enable-performance-schema --max-allowed-packet=32000000 --proxy-protocol-networks=::1/32,127.0.0.0/8,localhost +--sequence=on diff --git a/mysql-test/main/mysql_client_test_nonblock-master.opt b/mysql-test/main/mysql_client_test_nonblock-master.opt index a39d0089562..f306d27feac 100644 --- a/mysql-test/main/mysql_client_test_nonblock-master.opt +++ b/mysql-test/main/mysql_client_test_nonblock-master.opt @@ -1,3 +1,4 @@ --general-log --general-log-file=$MYSQLTEST_VARDIR/log/master.log --log-output=FILE,TABLE --max-allowed-packet=32000000 --proxy-protocol-networks=::1,::ffff:127.0.0.1/97,localhost +--sequence=on diff --git a/mysql-test/main/mysql_upgrade-6984.test b/mysql-test/main/mysql_upgrade-6984.test index 01bdc15c64b..d6d06a1e05e 100644 --- a/mysql-test/main/mysql_upgrade-6984.test +++ b/mysql-test/main/mysql_upgrade-6984.test @@ -13,6 +13,7 @@ update mysql.global_priv set priv=json_set(priv, '$.plugin', 'mysql_native_password', '$.authentication_string', password('foo')) where user='root'; +--replace_regex /[^ ]*mysql_upgrade_info/...mysql_upgrade_info/ --exec $MYSQL_UPGRADE connect(con1,localhost,root,foo,,,); @@ -21,3 +22,6 @@ update mysql.global_priv set priv=json_compact(json_remove(priv, '$.plugin', '$. flush privileges; # Load event table set global event_scheduler=OFF; + +let MYSQLD_DATADIR= `select @@datadir`; +--remove_file $MYSQLD_DATADIR/mysql_upgrade_info diff --git a/mysql-test/main/mysql_upgrade.result b/mysql-test/main/mysql_upgrade.result index c4c0af1601c..75566d06a0c 100644 --- a/mysql-test/main/mysql_upgrade.result +++ b/mysql-test/main/mysql_upgrade.result @@ -47,7 +47,8 @@ test Phase 7/7: Running 'FLUSH PRIVILEGES' OK Run it again - should say already completed -This installation of MariaDB is already upgraded to VERSION, use --force if you still need to run mysql_upgrade +This installation of MariaDB is already upgraded to VERSION.There is no need to run mysql_upgrade again for VERSION. +You can use --force if you still want to run mysql_upgrade Force should run it regardless of whether it has been run before Phase 1/7: Checking and upgrading mysql database Processing databases @@ -145,11 +146,12 @@ test Phase 7/7: Running 'FLUSH PRIVILEGES' OK DROP USER mysqltest1@'%'; -Version check failed. Got the following error when calling the 'mysql' command line client +Reading datadir from the MariaDB server failed. Got the following error when executing the 'mysql' command line client ERROR 1045 (28000): Access denied for user 'mysqltest1'@'localhost' (using password: YES) FATAL ERROR: Upgrade failed 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 +Reading datadir from the MariaDB server failed. Got the following error when executing the 'mysql' command line client +ERROR 2005 (HY000): Unknown MySQL server host 'not_existing_host' (errno) FATAL ERROR: Upgrade failed set GLOBAL sql_mode='STRICT_ALL_TABLES,ANSI_QUOTES,NO_ZERO_DATE'; Phase 1/7: Checking and upgrading mysql database @@ -412,9 +414,12 @@ OK # Bug #21489398: MYSQL_UPGRADE: FATAL ERROR: UPGRADE FAILED - IMPROVE ERROR # Run mysql_upgrade with unauthorized access -Version check failed. Got the following error when calling the 'mysql' command line client +Reading datadir from the MariaDB server failed. Got the following error when executing the 'mysql' command line client ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) FATAL ERROR: Upgrade failed +Reading datadir from the MariaDB server failed. Got the following error when executing the 'mysql' command line client +ERROR 1045 (errno): Access denied for user 'root'@'localhost' (using password: YES) +FATAL ERROR: Upgrade failed # # MDEV-4332 Increase username length from 16 characters # MDEV-6068, MDEV-6178 mysql_upgrade breaks databases with long user names @@ -768,6 +773,34 @@ GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION GRANT USAGE ON *.* TO `aRole` DROP ROLE `aRole`; FLUSH PRIVILEGES; +# +# MDEV-27279: mariadb_upgrade add --check-if-upgrade-is-needed +# +This installation of MariaDB is already upgraded to MariaDB . +There is no need to run mysql_upgrade again for MariaDB . +Looking for 'mysql' as: mysql +This installation of MariaDB is already upgraded to MariaDB . +There is no need to run mysql_upgrade again for MariaDB . +# +# MDEV-27279: mariadb_upgrade check-if-upgrade absence is do it +# +Looking for 'mysql' as: mysql +Empty or non existent ...mysql_upgrade_info. Assuming mysql_upgrade has to be run! +# +# MDEV-27279: mariadb_upgrade check-if-upgrade with minor version change +# +Looking for 'mysql' as: mysql +This installation of MariaDB is already upgraded to MariaDB . +There is no need to run mysql_upgrade again for MariaDB . +This installation of MariaDB is already upgraded to MariaDB . +There is no need to run mysql_upgrade again for MariaDB . +You can use --force if you still want to run mysql_upgrade +# +# MDEV-27279: mariadb_upgrade check-if-upgrade with major version change +# +Major version upgrade detected from MariaDB to MariaDB . Check required! +Looking for 'mysql' as: mysql +Major version upgrade detected from MariaDB to MariaDB . Check required! drop table mysql.global_priv; rename table mysql.global_priv_bak to mysql.global_priv; # End of 10.2 tests diff --git a/mysql-test/main/mysql_upgrade.test b/mysql-test/main/mysql_upgrade.test index afc06dfc5ee..0440cd75d77 100644 --- a/mysql-test/main/mysql_upgrade.test +++ b/mysql-test/main/mysql_upgrade.test @@ -17,7 +17,7 @@ let $MYSQLD_DATADIR= `select @@datadir`; file_exists $MYSQLD_DATADIR/mysql_upgrade_info; --echo Run it again - should say already completed ---replace_regex /upgraded to .*, use/upgraded to VERSION, use/ +--replace_regex /upgraded to [^\n].*/upgraded to VERSION./ /again for [^\n]*/again for VERSION./ --exec $MYSQL_UPGRADE 2>&1 # It should have created a file in the MySQL Servers datadir @@ -125,7 +125,7 @@ let $MYSQLD_DATADIR= `select @@datadir`; --remove_file $MYSQLD_DATADIR/mysql_upgrade_info --echo # Running mysql_upgrade with --skip-write-binlog.. ---replace_result $MYSQLTEST_VARDIR var +--replace_regex /[^ ]*mysql_upgrade_info/...mysql_upgrade_info/ --exec $MYSQL_UPGRADE --skip-write-binlog # mysql_upgrade must have created mysql_upgrade_info file, @@ -139,6 +139,9 @@ let $MYSQLD_DATADIR= `select @@datadir`; --echo Run mysql_upgrade with unauthorized access --error 1 --exec $MYSQL_UPGRADE --skip-verbose --user=root --password=wrong_password 2>&1 +--replace_regex /.*mysqlcheck.*: Got/mysqlcheck: Got/ /\([0-9|-]*\)/(errno)/ +--error 1 +--exec $MYSQL_UPGRADE --skip-verbose --skip-version-check --user=root --password=wrong_password 2>&1 --echo # --echo # MDEV-4332 Increase username length from 16 characters @@ -232,6 +235,7 @@ FLUSH TABLES mysql.user; FLUSH PRIVILEGES; SHOW CREATE TABLE mysql.user; +--replace_result $MYSQLTEST_VARDIR var --exec $MYSQL_UPGRADE --force 2>&1 SHOW CREATE TABLE mysql.user; @@ -276,6 +280,79 @@ SHOW GRANTS; DROP ROLE `aRole`; --exec $MYSQL mysql < $MYSQLTEST_VARDIR/tmp/user.sql FLUSH PRIVILEGES; + +--echo # +--echo # MDEV-27279: mariadb_upgrade add --check-if-upgrade-is-needed +--echo # + +--error 1 +--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed --silent +--replace_regex /\d\d\.\d*\.\d*[^ .\n]*/MariaDB / +--error 1 +--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed +--replace_regex /\d\d\.\d*\.\d*[^ .\n]*/MariaDB / /'mysql.* as:[^\n]*/'mysql' as: mysql/ +--error 1 +--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed --verbose + +--echo # +--echo # MDEV-27279: mariadb_upgrade check-if-upgrade absence is do it +--echo # + +--remove_file $MYSQLD_DATADIR/mysql_upgrade_info +--replace_regex /[^ ]*mysql_upgrade_info/...mysql_upgrade_info/ +--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed + +--replace_regex /'mysql.* as:[^\n]*/'mysql' as: mysql/ /open .* Assuming/open XXX. Assuming/ /[^ ]*mysql_upgrade_info/...mysql_upgrade_info/ +--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed --verbose + +--echo # +--echo # MDEV-27279: mariadb_upgrade check-if-upgrade with minor version change +--echo # + +# take 3rd number of version and change to 0 + +let DATADIR= $MYSQLD_DATADIR; + +perl; + my $ver= $ENV{'MYSQL_SERVER_VERSION'} or die "MYSQL_SERVER_VERSION not set"; + my $file= $ENV{'DATADIR'} or die "MYSQLD_DATADIR not set"; + $ver =~ s/^(\d*)\.(\d*).(\d*)(.*)/$1.$2.0$4/; + open(FILE, ">$file/mysql_upgrade_info") or die "Failed to open $file"; + print FILE "$ver\n"; + close(FILE); +EOF + +--error 1 +--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed --silent +--replace_regex /\d\d\.\d*\.\d*[^ .\n]*/MariaDB / /'mysql.* as:[^\n]*/'mysql' as: mysql/ +--error 1 +--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed --verbose +--replace_regex /\d\d\.\d*\.\d*[^ .\n]*/MariaDB / +--exec $MYSQL_UPGRADE +--remove_file $MYSQLD_DATADIR/mysql_upgrade_info + +--echo # +--echo # MDEV-27279: mariadb_upgrade check-if-upgrade with major version change +--echo # + +# take 2rd number of version and change to 0 + +let DATADIR= $MYSQLD_DATADIR; + +perl; + my $ver= $ENV{'MYSQL_SERVER_VERSION'} or die "MYSQL_SERVER_VERSION not set"; + my $file= $ENV{'DATADIR'} or die "MYSQLD_DATADIR not set"; + $ver =~ s/^(\d*)\.(\d*).(\d*)(.*)/$1.0.$3$4/; + open(FILE, ">$file/mysql_upgrade_info") or die "Failed to open $file"; + print FILE "$ver\n"; + close(FILE); +EOF + +--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed --silent +--replace_regex /\d\d\.\d*\.\d*[^ .\n]*/MariaDB / +--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed +--replace_regex /\d\d\.\d*\.\d*[^ .\n]*/MariaDB / /'mysql.* as:[^\n]*/'mysql' as: mysql/ +--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed --verbose --remove_file $MYSQLD_DATADIR/mysql_upgrade_info drop table mysql.global_priv; rename table mysql.global_priv_bak to mysql.global_priv; diff --git a/mysql-test/main/mysql_upgrade_noengine.result b/mysql-test/main/mysql_upgrade_noengine.result index 7b7082ab007..2e62d72538f 100644 --- a/mysql-test/main/mysql_upgrade_noengine.result +++ b/mysql-test/main/mysql_upgrade_noengine.result @@ -55,6 +55,7 @@ Warnings: Level Warning Code 1286 Message Unknown storage engine 'ARCHIVE' +# upgrade from 10.1 - engines aren't enabled Phase 1/7: Checking and upgrading mysql database Processing databases mysql @@ -148,6 +149,8 @@ rename table mysql.global_priv_bak to mysql.global_priv; # switching from mysql.global_priv to mysql.user drop view mysql.user_bak; alter table mysql.user drop column default_role, drop column max_statement_time; +# still upgrade from 10.1 +Major version upgrade detected from MariaDB to MariaDB . Check required! Phase 1/7: Checking and upgrading mysql database Processing databases mysql @@ -241,6 +244,7 @@ rename table mysql.global_priv_bak to mysql.global_priv; # switching from mysql.global_priv to mysql.user drop view mysql.user_bak; alter table mysql.user drop column default_role, drop column max_statement_time; +# upgrade from 10.0 - engines are enabled Phase 1/7: Checking and upgrading mysql database Processing databases mysql diff --git a/mysql-test/main/mysql_upgrade_noengine.test b/mysql-test/main/mysql_upgrade_noengine.test index e3c3e718591..48a007c4557 100644 --- a/mysql-test/main/mysql_upgrade_noengine.test +++ b/mysql-test/main/mysql_upgrade_noengine.test @@ -31,7 +31,8 @@ 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' and table_name='t1'; 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' and table_name='t2'; -# upgrade from 10.1 - engines aren't enabled +--echo # upgrade from 10.1 - engines aren't enabled +--replace_regex /\d\d\.\d*\.\d*[^ .\n]*/MariaDB / exec $MYSQL_UPGRADE 2>&1; 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' and table_name='t1'; 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' and table_name='t2'; @@ -50,7 +51,8 @@ write_file $datadir/mysql_upgrade_info; 10.1.10-MariaDB EOF -# still upgrade from 10.1 +--echo # still upgrade from 10.1 +--replace_regex /\d\d\.\d*\.\d*[^ .\n]*/MariaDB / exec $MYSQL_UPGRADE 2>&1; 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' and table_name='t1'; 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' and table_name='t2'; @@ -63,7 +65,8 @@ drop view mysql.user_bak; alter table mysql.user drop column default_role, drop column max_statement_time; remove_file $datadir/mysql_upgrade_info; -# upgrade from 10.0 - engines are enabled +--echo # upgrade from 10.0 - engines are enabled +--replace_regex /\d\d\.\d*\.\d*[^ .\n]*/MariaDB / exec $MYSQL_UPGRADE 2>&1; 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' and table_name='t1'; 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' and table_name='t2'; diff --git a/mysql-test/main/mysqldump.result b/mysql-test/main/mysqldump.result index d0d054b5473..81a8d0b8162 100644 --- a/mysql-test/main/mysqldump.result +++ b/mysql-test/main/mysqldump.result @@ -3704,29 +3704,29 @@ DROP TABLE IF EXISTS `TABLES`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TEMPORARY TABLE `TABLES` ( - `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '', - `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '', - `TABLE_NAME` varchar(64) NOT NULL DEFAULT '', - `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '', - `ENGINE` varchar(64) DEFAULT NULL, - `VERSION` bigint(21) unsigned DEFAULT NULL, - `ROW_FORMAT` varchar(10) DEFAULT NULL, - `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL, - `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL, - `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL, - `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL, - `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL, - `DATA_FREE` bigint(21) unsigned DEFAULT NULL, - `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL, - `CREATE_TIME` datetime DEFAULT NULL, - `UPDATE_TIME` datetime DEFAULT NULL, - `CHECK_TIME` datetime DEFAULT NULL, - `TABLE_COLLATION` varchar(32) DEFAULT NULL, - `CHECKSUM` bigint(21) unsigned DEFAULT NULL, - `CREATE_OPTIONS` varchar(2048) DEFAULT NULL, - `TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT '', - `MAX_INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL, - `TEMPORARY` varchar(1) DEFAULT NULL + `TABLE_CATALOG` varchar(512) NOT NULL, + `TABLE_SCHEMA` varchar(64) NOT NULL, + `TABLE_NAME` varchar(64) NOT NULL, + `TABLE_TYPE` varchar(64) NOT NULL, + `ENGINE` varchar(64), + `VERSION` bigint(21) unsigned, + `ROW_FORMAT` varchar(10), + `TABLE_ROWS` bigint(21) unsigned, + `AVG_ROW_LENGTH` bigint(21) unsigned, + `DATA_LENGTH` bigint(21) unsigned, + `MAX_DATA_LENGTH` bigint(21) unsigned, + `INDEX_LENGTH` bigint(21) unsigned, + `DATA_FREE` bigint(21) unsigned, + `AUTO_INCREMENT` bigint(21) unsigned, + `CREATE_TIME` datetime, + `UPDATE_TIME` datetime, + `CHECK_TIME` datetime, + `TABLE_COLLATION` varchar(32), + `CHECKSUM` bigint(21) unsigned, + `CREATE_OPTIONS` varchar(2048), + `TABLE_COMMENT` varchar(2048) NOT NULL, + `MAX_INDEX_LENGTH` bigint(21) unsigned, + `TEMPORARY` varchar(1) ) ENGINE=MEMORY DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; # diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result index 129bd8928f2..46d7544eb4e 100644 --- a/mysql-test/main/order_by.result +++ b/mysql-test/main/order_by.result @@ -3561,6 +3561,29 @@ b a 40 1 30 4 drop table t1,t2; +# +# MDEV-22256 Assertion `length == pack_length()' failed in Field_timestamp_with_dec::sort_string +# +SET sql_mode=''; +SET @@SESSION.max_sort_length=4; +Warnings: +Warning 1292 Truncated incorrect max_sort_length value: '4' +CREATE TABLE t1 (c TIMESTAMP(1)); +INSERT INTO t1 VALUES(0); +DELETE FROM t1 ORDER BY c; +DROP TABLE t1; +SET @@SESSION.max_sort_length=DEFAULT; +SET sql_mode=DEFAULT; +SET sql_mode=''; +SET @@SESSION.max_sort_length=1; +Warnings: +Warning 1292 Truncated incorrect max_sort_length value: '1' +CREATE TEMPORARY TABLE t1 (c DATETIME); +INSERT INTO t1 VALUES(0); +DELETE FROM t1 ORDER BY c; +DROP TABLE t1; +SET @@SESSION.max_sort_length=DEFAULT; +SET sql_mode=DEFAULT; # End of 10.2 tests # # MDEV-16214: Incorrect plan taken by the optimizer , uses INDEX instead of ref access with ORDER BY diff --git a/mysql-test/main/order_by.test b/mysql-test/main/order_by.test index 0bf0311a642..191e6d02673 100644 --- a/mysql-test/main/order_by.test +++ b/mysql-test/main/order_by.test @@ -2310,6 +2310,29 @@ explain (select b,a from t2 order by a limit 3) order by b desc; drop table t1,t2; +--echo # +--echo # MDEV-22256 Assertion `length == pack_length()' failed in Field_timestamp_with_dec::sort_string +--echo # + +SET sql_mode=''; +SET @@SESSION.max_sort_length=4; +CREATE TABLE t1 (c TIMESTAMP(1)); +INSERT INTO t1 VALUES(0); +DELETE FROM t1 ORDER BY c; +DROP TABLE t1; +SET @@SESSION.max_sort_length=DEFAULT; +SET sql_mode=DEFAULT; + +SET sql_mode=''; +SET @@SESSION.max_sort_length=1; +CREATE TEMPORARY TABLE t1 (c DATETIME); +INSERT INTO t1 VALUES(0); +DELETE FROM t1 ORDER BY c; +DROP TABLE t1; +SET @@SESSION.max_sort_length=DEFAULT; +SET sql_mode=DEFAULT; + + --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/partition_sp.result b/mysql-test/main/partition_sp.result new file mode 100644 index 00000000000..585e2c7ab9d --- /dev/null +++ b/mysql-test/main/partition_sp.result @@ -0,0 +1,22 @@ +# +# MDEV-8652: Partitioned table creation problem when +# creating from procedure context twice in same session +# +CREATE PROCEDURE p1() +BEGIN +DROP TABLE IF EXISTS t1 ; +CREATE TABLE t1 ( +id INT PRIMARY KEY +) +PARTITION BY RANGE (id) ( +PARTITION P1 VALUES LESS THAN (2), +PARTITION P2 VALUES LESS THAN (3) +); +END | +call p1(); +call p1(); +drop procedure p1; +drop table t1; +# +# End of 10.2 tests +# diff --git a/mysql-test/main/partition_sp.test b/mysql-test/main/partition_sp.test new file mode 100644 index 00000000000..1d3d1d707c7 --- /dev/null +++ b/mysql-test/main/partition_sp.test @@ -0,0 +1,35 @@ +--source include/have_partition.inc + +--echo # +--echo # MDEV-8652: Partitioned table creation problem when +--echo # creating from procedure context twice in same session +--echo # + + +DELIMITER |; + +CREATE PROCEDURE p1() +BEGIN + DROP TABLE IF EXISTS t1 ; + + CREATE TABLE t1 ( + id INT PRIMARY KEY + ) + PARTITION BY RANGE (id) ( + PARTITION P1 VALUES LESS THAN (2), + PARTITION P2 VALUES LESS THAN (3) + ); +END | + +DELIMITER ;| + +call p1(); +call p1(); + +drop procedure p1; +drop table t1; + +--echo # +--echo # End of 10.2 tests +--echo # + diff --git a/mysql-test/main/partition_symlink.result b/mysql-test/main/partition_symlink.result index 90048eb3438..b5a976e3a9e 100644 --- a/mysql-test/main/partition_symlink.result +++ b/mysql-test/main/partition_symlink.result @@ -177,3 +177,97 @@ partition by key (a) (partition p0, partition p1 DATA DIRECTORY 'part-data' INDEX DIRECTORY 'part-data'); Got one of the listed errors +# +# MDEV-25917 create table like fails if source table is partitioned and engine is myisam or aria with data directory. +# +CREATE TABLE t1 (a INT) +ENGINE = MyISAM +PARTITION BY LIST (a) +(PARTITION p0 VALUES IN (0) +DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp', +PARTITION p1 VALUES IN (1) +DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp', +PARTITION p2 VALUES IN (2)); +CREATE TABLE t2 LIKE t1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + PARTITION BY LIST (`a`) +(PARTITION `p0` VALUES IN (0) DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp' ENGINE = MyISAM, + PARTITION `p1` VALUES IN (1) DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp' ENGINE = MyISAM, + PARTITION `p2` VALUES IN (2) ENGINE = MyISAM) +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + PARTITION BY LIST (`a`) +(PARTITION `p0` VALUES IN (0) ENGINE = MyISAM, + PARTITION `p1` VALUES IN (1) ENGINE = MyISAM, + PARTITION `p2` VALUES IN (2) ENGINE = MyISAM) +DROP TABLE t1, t2; +CREATE TABLE t1 ( +ID int(11) NOT NULL, +type int(11)) Engine=MyISAM +PARTITION BY RANGE(ID) +SUBPARTITION BY HASH(type) +( +PARTITION p01 VALUES LESS THAN(100) +(SUBPARTITION s11 +DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp', +SUBPARTITION s12 +DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp' + ), +PARTITION p11 VALUES LESS THAN(200) +(SUBPARTITION s21, SUBPARTITION s22), +PARTITION p21 VALUES LESS THAN MAXVALUE +(SUBPARTITION s31 +DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp', +SUBPARTITION s32 +DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY 'MYSQLTEST_VARDIR/tmp' + ) +); +CREATE TABLE t2 LIKE t1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `ID` int(11) NOT NULL, + `type` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + PARTITION BY RANGE (`ID`) +SUBPARTITION BY HASH (`type`) +(PARTITION `p01` VALUES LESS THAN (100) + (SUBPARTITION `s11` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp' ENGINE = MyISAM, + SUBPARTITION `s12` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp' ENGINE = MyISAM), + PARTITION `p11` VALUES LESS THAN (200) + (SUBPARTITION `s21` ENGINE = MyISAM, + SUBPARTITION `s22` ENGINE = MyISAM), + PARTITION `p21` VALUES LESS THAN MAXVALUE + (SUBPARTITION `s31` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp' ENGINE = MyISAM, + SUBPARTITION `s32` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp' ENGINE = MyISAM)) +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `ID` int(11) NOT NULL, + `type` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + PARTITION BY RANGE (`ID`) +SUBPARTITION BY HASH (`type`) +(PARTITION `p01` VALUES LESS THAN (100) + (SUBPARTITION `s11` ENGINE = MyISAM, + SUBPARTITION `s12` ENGINE = MyISAM), + PARTITION `p11` VALUES LESS THAN (200) + (SUBPARTITION `s21` ENGINE = MyISAM, + SUBPARTITION `s22` ENGINE = MyISAM), + PARTITION `p21` VALUES LESS THAN MAXVALUE + (SUBPARTITION `s31` ENGINE = MyISAM, + SUBPARTITION `s32` ENGINE = MyISAM)) +DROP TABLE t1, t2; diff --git a/mysql-test/main/partition_symlink.test b/mysql-test/main/partition_symlink.test index 8f6e837299a..7e09c7d0642 100644 --- a/mysql-test/main/partition_symlink.test +++ b/mysql-test/main/partition_symlink.test @@ -220,3 +220,58 @@ ENGINE = MyISAM partition by key (a) (partition p0, partition p1 DATA DIRECTORY 'part-data' INDEX DIRECTORY 'part-data'); + +--echo # +--echo # MDEV-25917 create table like fails if source table is partitioned and engine is myisam or aria with data directory. +--echo # +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval CREATE TABLE t1 (a INT) +ENGINE = MyISAM +PARTITION BY LIST (a) +(PARTITION p0 VALUES IN (0) + DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp', + PARTITION p1 VALUES IN (1) + DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp', + PARTITION p2 VALUES IN (2)); + +CREATE TABLE t2 LIKE t1; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t1; +SHOW CREATE TABLE t2; +DROP TABLE t1, t2; + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +eval CREATE TABLE t1 ( + ID int(11) NOT NULL, + type int(11)) Engine=MyISAM +PARTITION BY RANGE(ID) +SUBPARTITION BY HASH(type) +( + PARTITION p01 VALUES LESS THAN(100) + (SUBPARTITION s11 + DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp', + SUBPARTITION s12 + DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp' + ), + PARTITION p11 VALUES LESS THAN(200) + (SUBPARTITION s21, SUBPARTITION s22), + PARTITION p21 VALUES LESS THAN MAXVALUE + (SUBPARTITION s31 + DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp', + SUBPARTITION s32 + DATA DIRECTORY '$MYSQLTEST_VARDIR/tmp' + INDEX DIRECTORY '$MYSQLTEST_VARDIR/tmp' + ) +); + +CREATE TABLE t2 LIKE t1; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +SHOW CREATE TABLE t1; +SHOW CREATE TABLE t2; + +DROP TABLE t1, t2; diff --git a/mysql-test/main/range_innodb.result b/mysql-test/main/range_innodb.result index 66a1e6e6c87..0caf16cbab8 100644 --- a/mysql-test/main/range_innodb.result +++ b/mysql-test/main/range_innodb.result @@ -110,6 +110,112 @@ SET @@GLOBAL.debug_dbug = @saved_dbug; set @@optimizer_switch= @optimizer_switch_save; # End of 10.1 tests # +# MDEV-27262: Index intersection with full scan over an index +# +CREATE TABLE t1 ( +id int(10) unsigned NOT NULL AUTO_INCREMENT, +p char(32) DEFAULT NULL, +es tinyint(3) unsigned NOT NULL DEFAULT 0, +er tinyint(3) unsigned NOT NULL DEFAULT 0, +x mediumint(8) unsigned NOT NULL DEFAULT 0, +PRIMARY KEY (id), +INDEX es (es), +INDEX x (x), +INDEX er (er,x), +INDEX p (p) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +insert into t1(es,er) select 0, 1 from seq_1_to_45; +insert into t1(es,er) select 0, 2 from seq_1_to_49; +insert into t1(es,er) select 0, 3 from seq_1_to_951; +insert into t1(es,er) select 0, 3 from seq_1_to_1054; +insert into t1(es,er) select 0, 6 from seq_1_to_25; +insert into t1(es,er) select 0, 11 from seq_1_to_1; +insert into t1(es,er) select 1, 1 from seq_1_to_45; +insert into t1(es,er) select 1, 2 from seq_1_to_16; +insert into t1(es,er) select 1, 3 from seq_1_to_511; +insert into t1(es,er) select 1, 4 from seq_1_to_687; +insert into t1(es,er) select 1, 6 from seq_1_to_50; +insert into t1(es,er) select 1, 7 from seq_1_to_4; +insert into t1(es,er) select 1, 11 from seq_1_to_1; +insert into t1(es,er) select 2, 1 from seq_1_to_82; +insert into t1(es,er) select 2, 2 from seq_1_to_82; +insert into t1(es,er) select 2, 3 from seq_1_to_1626; +insert into t1(es,er) select 2, 4 from seq_1_to_977; +insert into t1(es,er) select 2, 6 from seq_1_to_33; +insert into t1(es,er) select 2, 11 from seq_1_to_1; +insert into t1(es,er) select 3, 1 from seq_1_to_245; +insert into t1(es,er) select 3, 2 from seq_1_to_81; +insert into t1(es,er) select 3, 3 from seq_1_to_852; +insert into t1(es,er) select 3, 4 from seq_1_to_2243; +insert into t1(es,er) select 3, 6 from seq_1_to_44; +insert into t1(es,er) select 3, 11 from seq_1_to_1; +insert into t1(es,er) select 4, 1 from seq_1_to_91; +insert into t1(es,er) select 4, 2 from seq_1_to_83; +insert into t1(es,er) select 4, 3 from seq_1_to_297; +insert into t1(es,er) select 4, 4 from seq_1_to_2456; +insert into t1(es,er) select 4, 6 from seq_1_to_19; +insert into t1(es,er) select 4, 11 from seq_1_to_1; +update t1 set p='foobar'; +update t1 set x=0; +set @save_isp=@@innodb_stats_persistent; +set global innodb_stats_persistent= 1; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +set optimizer_switch='index_merge_sort_intersection=on'; +SELECT * FROM t1 +WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2; +id p es er x +14645 foobar 4 4 0 +14646 foobar 4 4 0 +EXPLAIN EXTENDED SELECT * FROM t1 +WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range es,er,p es 1 NULL # 100.00 Using index condition; Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`p` AS `p`,`test`.`t1`.`es` AS `es`,`test`.`t1`.`er` AS `er`,`test`.`t1`.`x` AS `x` from `test`.`t1` where (`test`.`t1`.`p` = 'foo' and `test`.`t1`.`er` <> 4 or `test`.`t1`.`er` = 4) and `test`.`t1`.`es` >= 4 limit 2 +set optimizer_switch='index_merge_sort_intersection=off'; +SELECT * FROM t1 +WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2; +id p es er x +14645 foobar 4 4 0 +14646 foobar 4 4 0 +EXPLAIN EXTENDED SELECT * FROM t1 +WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range es,er,p es 1 NULL # 100.00 Using index condition; Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`p` AS `p`,`test`.`t1`.`es` AS `es`,`test`.`t1`.`er` AS `er`,`test`.`t1`.`x` AS `x` from `test`.`t1` where (`test`.`t1`.`p` = 'foo' and `test`.`t1`.`er` <> 4 or `test`.`t1`.`er` = 4) and `test`.`t1`.`es` >= 4 limit 2 +set optimizer_switch='index_merge_sort_intersection=on'; +SELECT * FROM t1 +WHERE ((p = 'foo' AND er < 6) OR er >=2 ) AND (es >= 4) LIMIT 2; +id p es er x +14007 foobar 4 2 0 +14008 foobar 4 2 0 +EXPLAIN EXTENDED SELECT * FROM t1 +WHERE ((p = 'foo' AND er < 6) OR er >=2 ) AND (es >= 4) LIMIT 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range es,er,p es 1 NULL # 100.00 Using index condition; Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`p` AS `p`,`test`.`t1`.`es` AS `es`,`test`.`t1`.`er` AS `er`,`test`.`t1`.`x` AS `x` from `test`.`t1` where (`test`.`t1`.`p` = 'foo' and `test`.`t1`.`er` < 6 or `test`.`t1`.`er` >= 2) and `test`.`t1`.`es` >= 4 limit 2 +set optimizer_switch='index_merge_sort_intersection=off'; +SELECT * FROM t1 +WHERE ((p = 'foo' AND er < 6) OR er >=2 ) AND (es >= 4) LIMIT 2; +id p es er x +14007 foobar 4 2 0 +14008 foobar 4 2 0 +EXPLAIN EXTENDED SELECT * FROM t1 +WHERE ((p = 'foo' AND er < 6) OR er >=2 ) AND (es >= 4) LIMIT 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range es,er,p es 1 NULL # 100.00 Using index condition; Using where +Warnings: +Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`p` AS `p`,`test`.`t1`.`es` AS `es`,`test`.`t1`.`er` AS `er`,`test`.`t1`.`x` AS `x` from `test`.`t1` where (`test`.`t1`.`p` = 'foo' and `test`.`t1`.`er` < 6 or `test`.`t1`.`er` >= 2) and `test`.`t1`.`es` >= 4 limit 2 +set optimizer_switch='index_merge_sort_intersection=default'; +set global innodb_stats_persistent= @save_isp; +DROP TABLE t1; +# End of 10.2 tests +# # MDEV-19634: Assertion `0' failed in row_sel_convert_mysql_key_to_innobase, # [Warning] InnoDB: Using a partial-field key prefix in search # diff --git a/mysql-test/main/range_innodb.test b/mysql-test/main/range_innodb.test index f79104fde85..8b9771d1d1e 100644 --- a/mysql-test/main/range_innodb.test +++ b/mysql-test/main/range_innodb.test @@ -118,6 +118,99 @@ set @@optimizer_switch= @optimizer_switch_save; --echo # End of 10.1 tests --echo # +--echo # MDEV-27262: Index intersection with full scan over an index +--echo # + +--source include/have_sequence.inc + +CREATE TABLE t1 ( + id int(10) unsigned NOT NULL AUTO_INCREMENT, + p char(32) DEFAULT NULL, + es tinyint(3) unsigned NOT NULL DEFAULT 0, + er tinyint(3) unsigned NOT NULL DEFAULT 0, + x mediumint(8) unsigned NOT NULL DEFAULT 0, + PRIMARY KEY (id), + INDEX es (es), + INDEX x (x), + INDEX er (er,x), + INDEX p (p) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +insert into t1(es,er) select 0, 1 from seq_1_to_45; +insert into t1(es,er) select 0, 2 from seq_1_to_49; +insert into t1(es,er) select 0, 3 from seq_1_to_951; +insert into t1(es,er) select 0, 3 from seq_1_to_1054; +insert into t1(es,er) select 0, 6 from seq_1_to_25; +insert into t1(es,er) select 0, 11 from seq_1_to_1; +insert into t1(es,er) select 1, 1 from seq_1_to_45; +insert into t1(es,er) select 1, 2 from seq_1_to_16; +insert into t1(es,er) select 1, 3 from seq_1_to_511; +insert into t1(es,er) select 1, 4 from seq_1_to_687; +insert into t1(es,er) select 1, 6 from seq_1_to_50; +insert into t1(es,er) select 1, 7 from seq_1_to_4; +insert into t1(es,er) select 1, 11 from seq_1_to_1; +insert into t1(es,er) select 2, 1 from seq_1_to_82; +insert into t1(es,er) select 2, 2 from seq_1_to_82; +insert into t1(es,er) select 2, 3 from seq_1_to_1626; +insert into t1(es,er) select 2, 4 from seq_1_to_977; +insert into t1(es,er) select 2, 6 from seq_1_to_33; +insert into t1(es,er) select 2, 11 from seq_1_to_1; +insert into t1(es,er) select 3, 1 from seq_1_to_245; +insert into t1(es,er) select 3, 2 from seq_1_to_81; +insert into t1(es,er) select 3, 3 from seq_1_to_852; +insert into t1(es,er) select 3, 4 from seq_1_to_2243; +insert into t1(es,er) select 3, 6 from seq_1_to_44; +insert into t1(es,er) select 3, 11 from seq_1_to_1; +insert into t1(es,er) select 4, 1 from seq_1_to_91; +insert into t1(es,er) select 4, 2 from seq_1_to_83; +insert into t1(es,er) select 4, 3 from seq_1_to_297; +insert into t1(es,er) select 4, 4 from seq_1_to_2456; +insert into t1(es,er) select 4, 6 from seq_1_to_19; +insert into t1(es,er) select 4, 11 from seq_1_to_1; +update t1 set p='foobar'; +update t1 set x=0; +set @save_isp=@@innodb_stats_persistent; +set global innodb_stats_persistent= 1; +analyze table t1; + +let $q= +SELECT * FROM t1 + WHERE ((p = 'foo' AND er != 4) OR er = 4 ) AND (es >= 4) LIMIT 2; + +set optimizer_switch='index_merge_sort_intersection=on'; +eval $q; +--replace_column 9 # +eval EXPLAIN EXTENDED $q; + +set optimizer_switch='index_merge_sort_intersection=off'; +# execution of $q and explain for it led to an assertion failure in 10.4 +# (with the optimizer switch rowid_filter set to 'on') +eval $q; +--replace_column 9 # +eval EXPLAIN EXTENDED $q; + +let $q= +SELECT * FROM t1 + WHERE ((p = 'foo' AND er < 6) OR er >=2 ) AND (es >= 4) LIMIT 2; + +set optimizer_switch='index_merge_sort_intersection=on'; +eval $q; +--replace_column 9 # +eval EXPLAIN EXTENDED $q; + +set optimizer_switch='index_merge_sort_intersection=off'; +eval $q; +--replace_column 9 # +eval EXPLAIN EXTENDED $q; + +set optimizer_switch='index_merge_sort_intersection=default'; + +set global innodb_stats_persistent= @save_isp; +DROP TABLE t1; + +--echo # End of 10.2 tests + +--echo # --echo # MDEV-19634: Assertion `0' failed in row_sel_convert_mysql_key_to_innobase, --echo # [Warning] InnoDB: Using a partial-field key prefix in search --echo # diff --git a/mysql-test/main/range_vs_index_merge.result b/mysql-test/main/range_vs_index_merge.result index 286338d0433..b4e59f474d4 100644 --- a/mysql-test/main/range_vs_index_merge.result +++ b/mysql-test/main/range_vs_index_merge.result @@ -1812,7 +1812,7 @@ EXPLAIN SELECT * FROM t1 WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR (a<2 or a>2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,idx PRIMARY 0 NULL 2 Using index condition; Using where +1 SIMPLE t1 ALL PRIMARY,idx NULL NULL NULL 2 Using where SELECT * FROM t1 WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR (a<2 or a>2); a b diff --git a/mysql-test/main/show.result b/mysql-test/main/show.result index d1b373d8969..4a9e2738346 100644 --- a/mysql-test/main/show.result +++ b/mysql-test/main/show.result @@ -40,5 +40,16 @@ nm varchar(32) YES NULL a int(11) YES NULL drop table t1; # +# MDEV-4621 select returns null for information_schema.statistics.collation field +# +create table t1 (f varchar(64), key(f)); +select index_name, column_name, collation, cardinality from information_schema.STATISTICS where table_schema='test' and table_name='t1'; +index_name column_name collation cardinality +f f A NULL +select index_name, column_name, collation from information_schema.STATISTICS where table_schema='test' and table_name='t1'; +index_name column_name collation +f f A +drop table t1; +# # End of 10.2 tests # diff --git a/mysql-test/main/show.test b/mysql-test/main/show.test index f2f6efc4e45..9b0b58349d5 100644 --- a/mysql-test/main/show.test +++ b/mysql-test/main/show.test @@ -35,5 +35,13 @@ show fields from test.t1 where field in drop table t1; --echo # +--echo # MDEV-4621 select returns null for information_schema.statistics.collation field +--echo # +create table t1 (f varchar(64), key(f)); +select index_name, column_name, collation, cardinality from information_schema.STATISTICS where table_schema='test' and table_name='t1'; +select index_name, column_name, collation from information_schema.STATISTICS where table_schema='test' and table_name='t1'; +drop table t1; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/show_check.result b/mysql-test/main/show_check.result index d031c792922..06277f5fe93 100644 --- a/mysql-test/main/show_check.result +++ b/mysql-test/main/show_check.result @@ -63,19 +63,19 @@ Table Op Msg_type Msg_text test.t1 check status OK show index from t1; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def information_schema STATISTICS STATISTICS TABLE_NAME Table 253 64 2 N 1 0 8 -def information_schema STATISTICS STATISTICS NON_UNIQUE Non_unique 8 1 1 N 32769 0 63 -def information_schema STATISTICS STATISTICS INDEX_NAME Key_name 253 64 7 N 1 0 8 -def information_schema STATISTICS STATISTICS SEQ_IN_INDEX Seq_in_index 8 2 1 N 32769 0 63 -def information_schema STATISTICS STATISTICS COLUMN_NAME Column_name 253 64 1 N 1 0 8 -def information_schema STATISTICS STATISTICS COLLATION Collation 253 1 1 Y 0 0 8 -def information_schema STATISTICS STATISTICS CARDINALITY Cardinality 8 21 1 Y 32768 0 63 -def information_schema STATISTICS STATISTICS SUB_PART Sub_part 8 3 0 Y 32768 0 63 -def information_schema STATISTICS STATISTICS PACKED Packed 253 10 0 Y 0 0 8 -def information_schema STATISTICS STATISTICS NULLABLE Null 253 3 0 N 1 0 8 -def information_schema STATISTICS STATISTICS INDEX_TYPE Index_type 253 16 5 N 1 0 8 -def information_schema STATISTICS STATISTICS COMMENT Comment 253 16 0 Y 0 0 8 -def information_schema STATISTICS STATISTICS INDEX_COMMENT Index_comment 253 1024 0 N 1 0 8 +def information_schema STATISTICS STATISTICS TABLE_NAME Table 253 64 2 N 4097 0 8 +def information_schema STATISTICS STATISTICS NON_UNIQUE Non_unique 8 1 1 N 36865 0 63 +def information_schema STATISTICS STATISTICS INDEX_NAME Key_name 253 64 7 N 4097 0 8 +def information_schema STATISTICS STATISTICS SEQ_IN_INDEX Seq_in_index 8 2 1 N 36865 0 63 +def information_schema STATISTICS STATISTICS COLUMN_NAME Column_name 253 64 1 N 4097 0 8 +def information_schema STATISTICS STATISTICS COLLATION Collation 253 1 1 Y 4096 0 8 +def information_schema STATISTICS STATISTICS CARDINALITY Cardinality 8 21 1 Y 36864 0 63 +def information_schema STATISTICS STATISTICS SUB_PART Sub_part 8 3 0 Y 36864 0 63 +def information_schema STATISTICS STATISTICS PACKED Packed 253 10 0 Y 4096 0 8 +def information_schema STATISTICS STATISTICS NULLABLE Null 253 3 0 N 4097 0 8 +def information_schema STATISTICS STATISTICS INDEX_TYPE Index_type 253 16 5 N 4097 0 8 +def information_schema STATISTICS STATISTICS COMMENT Comment 253 16 0 Y 4096 0 8 +def information_schema STATISTICS STATISTICS INDEX_COMMENT Index_comment 253 1024 0 N 4097 0 8 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment t1 0 PRIMARY 1 a A 5 NULL NULL BTREE t1 1 b 1 b A 1 NULL NULL BTREE @@ -102,47 +102,47 @@ drop table t1; -- after Bug#29394 is implemented. show variables like "wait_timeout%"; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def information_schema SESSION_VARIABLES SESSION_VARIABLES VARIABLE_NAME Variable_name 253 64 12 N 1 0 8 -def information_schema SESSION_VARIABLES SESSION_VARIABLES VARIABLE_VALUE Value 253 2048 5 N 1 0 8 +def information_schema SESSION_VARIABLES SESSION_VARIABLES VARIABLE_NAME Variable_name 253 64 12 N 4097 0 8 +def information_schema SESSION_VARIABLES SESSION_VARIABLES VARIABLE_VALUE Value 253 2048 5 N 4097 0 8 Variable_name Value wait_timeout 28800 show variables like "WAIT_timeout%"; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def information_schema SESSION_VARIABLES SESSION_VARIABLES VARIABLE_NAME Variable_name 253 64 12 N 1 0 8 -def information_schema SESSION_VARIABLES SESSION_VARIABLES VARIABLE_VALUE Value 253 2048 5 N 1 0 8 +def information_schema SESSION_VARIABLES SESSION_VARIABLES VARIABLE_NAME Variable_name 253 64 12 N 4097 0 8 +def information_schema SESSION_VARIABLES SESSION_VARIABLES VARIABLE_VALUE Value 253 2048 5 N 4097 0 8 Variable_name Value wait_timeout 28800 show variables like "this_doesn't_exists%"; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def information_schema SESSION_VARIABLES SESSION_VARIABLES VARIABLE_NAME Variable_name 253 64 0 N 1 0 8 -def information_schema SESSION_VARIABLES SESSION_VARIABLES VARIABLE_VALUE Value 253 2048 0 N 1 0 8 +def information_schema SESSION_VARIABLES SESSION_VARIABLES VARIABLE_NAME Variable_name 253 64 0 N 4097 0 8 +def information_schema SESSION_VARIABLES SESSION_VARIABLES VARIABLE_VALUE Value 253 2048 0 N 4097 0 8 Variable_name Value show table status from test like "this_doesn't_exists%"; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def information_schema TABLES TABLES TABLE_NAME Name 253 64 0 N 1 0 8 -def information_schema TABLES TABLES ENGINE Engine 253 64 0 Y 0 0 8 -def information_schema TABLES TABLES VERSION Version 8 21 0 Y 32800 0 63 -def information_schema TABLES TABLES ROW_FORMAT Row_format 253 10 0 Y 0 0 8 -def information_schema TABLES TABLES TABLE_ROWS Rows 8 21 0 Y 32800 0 63 -def information_schema TABLES TABLES AVG_ROW_LENGTH Avg_row_length 8 21 0 Y 32800 0 63 -def information_schema TABLES TABLES DATA_LENGTH Data_length 8 21 0 Y 32800 0 63 -def information_schema TABLES TABLES MAX_DATA_LENGTH Max_data_length 8 21 0 Y 32800 0 63 -def information_schema TABLES TABLES INDEX_LENGTH Index_length 8 21 0 Y 32800 0 63 -def information_schema TABLES TABLES DATA_FREE Data_free 8 21 0 Y 32800 0 63 -def information_schema TABLES TABLES AUTO_INCREMENT Auto_increment 8 21 0 Y 32800 0 63 -def information_schema TABLES TABLES CREATE_TIME Create_time 12 19 0 Y 128 0 63 -def information_schema TABLES TABLES UPDATE_TIME Update_time 12 19 0 Y 128 0 63 -def information_schema TABLES TABLES CHECK_TIME Check_time 12 19 0 Y 128 0 63 -def information_schema TABLES TABLES TABLE_COLLATION Collation 253 32 0 Y 0 0 8 -def information_schema TABLES TABLES CHECKSUM Checksum 8 21 0 Y 32800 0 63 -def information_schema TABLES TABLES CREATE_OPTIONS Create_options 253 2048 0 Y 0 0 8 -def information_schema TABLES TABLES TABLE_COMMENT Comment 253 2048 0 N 1 0 8 -def information_schema TABLES TABLES MAX_INDEX_LENGTH Max_index_length 8 21 0 Y 32800 0 63 -def information_schema TABLES TABLES TEMPORARY Temporary 253 1 0 Y 0 0 8 +def information_schema TABLES TABLES TABLE_NAME Name 253 64 0 N 4097 0 8 +def information_schema TABLES TABLES ENGINE Engine 253 64 0 Y 4096 0 8 +def information_schema TABLES TABLES VERSION Version 8 21 0 Y 36896 0 63 +def information_schema TABLES TABLES ROW_FORMAT Row_format 253 10 0 Y 4096 0 8 +def information_schema TABLES TABLES TABLE_ROWS Rows 8 21 0 Y 36896 0 63 +def information_schema TABLES TABLES AVG_ROW_LENGTH Avg_row_length 8 21 0 Y 36896 0 63 +def information_schema TABLES TABLES DATA_LENGTH Data_length 8 21 0 Y 36896 0 63 +def information_schema TABLES TABLES MAX_DATA_LENGTH Max_data_length 8 21 0 Y 36896 0 63 +def information_schema TABLES TABLES INDEX_LENGTH Index_length 8 21 0 Y 36896 0 63 +def information_schema TABLES TABLES DATA_FREE Data_free 8 21 0 Y 36896 0 63 +def information_schema TABLES TABLES AUTO_INCREMENT Auto_increment 8 21 0 Y 36896 0 63 +def information_schema TABLES TABLES CREATE_TIME Create_time 12 19 0 Y 4224 0 63 +def information_schema TABLES TABLES UPDATE_TIME Update_time 12 19 0 Y 4224 0 63 +def information_schema TABLES TABLES CHECK_TIME Check_time 12 19 0 Y 4224 0 63 +def information_schema TABLES TABLES TABLE_COLLATION Collation 253 32 0 Y 4096 0 8 +def information_schema TABLES TABLES CHECKSUM Checksum 8 21 0 Y 36896 0 63 +def information_schema TABLES TABLES CREATE_OPTIONS Create_options 253 2048 0 Y 4096 0 8 +def information_schema TABLES TABLES TABLE_COMMENT Comment 253 2048 0 N 4097 0 8 +def information_schema TABLES TABLES MAX_INDEX_LENGTH Max_index_length 8 21 0 Y 36896 0 63 +def information_schema TABLES TABLES TEMPORARY Temporary 253 1 0 Y 4096 0 8 Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary show databases; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def information_schema SCHEMATA SCHEMATA SCHEMA_NAME Database 253 64 18 N 1 0 8 +def information_schema SCHEMATA SCHEMATA SCHEMA_NAME Database 253 64 18 N 4097 0 8 Database information_schema mtr @@ -151,7 +151,7 @@ performance_schema test show databases like "test%"; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def information_schema SCHEMATA SCHEMATA SCHEMA_NAME Database (test%) 253 64 4 N 1 0 8 +def information_schema SCHEMATA SCHEMATA SCHEMA_NAME Database (test%) 253 64 4 N 4097 0 8 Database (test%) test create table t1 (f1 int not null, f2 int not null, f3 int not null, f4 int not null, primary key(f1,f2,f3,f4)); @@ -647,19 +647,19 @@ PRIMARY KEY(field1(1000)) ); show index from t1; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def information_schema STATISTICS STATISTICS TABLE_NAME Table 253 64 2 N 1 0 63 -def information_schema STATISTICS STATISTICS NON_UNIQUE Non_unique 8 1 1 N 32769 0 63 -def information_schema STATISTICS STATISTICS INDEX_NAME Key_name 253 64 7 N 1 0 63 -def information_schema STATISTICS STATISTICS SEQ_IN_INDEX Seq_in_index 8 2 1 N 32769 0 63 -def information_schema STATISTICS STATISTICS COLUMN_NAME Column_name 253 64 6 N 1 0 63 -def information_schema STATISTICS STATISTICS COLLATION Collation 253 1 1 Y 0 0 63 -def information_schema STATISTICS STATISTICS CARDINALITY Cardinality 8 21 1 Y 32768 0 63 -def information_schema STATISTICS STATISTICS SUB_PART Sub_part 8 3 4 Y 32768 0 63 -def information_schema STATISTICS STATISTICS PACKED Packed 253 10 0 Y 0 0 63 -def information_schema STATISTICS STATISTICS NULLABLE Null 253 3 0 N 1 0 63 -def information_schema STATISTICS STATISTICS INDEX_TYPE Index_type 253 16 5 N 1 0 63 -def information_schema STATISTICS STATISTICS COMMENT Comment 253 16 0 Y 0 0 63 -def information_schema STATISTICS STATISTICS INDEX_COMMENT Index_comment 253 1024 0 N 1 0 63 +def information_schema STATISTICS STATISTICS TABLE_NAME Table 253 64 2 N 4097 0 63 +def information_schema STATISTICS STATISTICS NON_UNIQUE Non_unique 8 1 1 N 36865 0 63 +def information_schema STATISTICS STATISTICS INDEX_NAME Key_name 253 64 7 N 4097 0 63 +def information_schema STATISTICS STATISTICS SEQ_IN_INDEX Seq_in_index 8 2 1 N 36865 0 63 +def information_schema STATISTICS STATISTICS COLUMN_NAME Column_name 253 64 6 N 4097 0 63 +def information_schema STATISTICS STATISTICS COLLATION Collation 253 1 1 Y 4096 0 63 +def information_schema STATISTICS STATISTICS CARDINALITY Cardinality 8 21 1 Y 36864 0 63 +def information_schema STATISTICS STATISTICS SUB_PART Sub_part 8 3 4 Y 36864 0 63 +def information_schema STATISTICS STATISTICS PACKED Packed 253 10 0 Y 4096 0 63 +def information_schema STATISTICS STATISTICS NULLABLE Null 253 3 0 N 4097 0 63 +def information_schema STATISTICS STATISTICS INDEX_TYPE Index_type 253 16 5 N 4097 0 63 +def information_schema STATISTICS STATISTICS COMMENT Comment 253 16 0 Y 4096 0 63 +def information_schema STATISTICS STATISTICS INDEX_COMMENT Index_comment 253 1024 0 N 4097 0 63 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment t1 0 PRIMARY 1 field1 A 0 1000 NULL BTREE drop table t1; @@ -874,21 +874,21 @@ set names utf8; ---------------------------------------------------------------- SHOW CHARACTER SET LIKE 'utf8'; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def information_schema CHARACTER_SETS CHARACTER_SETS CHARACTER_SET_NAME Charset 253 96 4 N 1 0 33 -def information_schema CHARACTER_SETS CHARACTER_SETS DESCRIPTION Description 253 180 13 N 1 0 33 -def information_schema CHARACTER_SETS CHARACTER_SETS DEFAULT_COLLATE_NAME Default collation 253 96 15 N 1 0 33 -def information_schema CHARACTER_SETS CHARACTER_SETS MAXLEN Maxlen 8 3 1 N 32769 0 63 +def information_schema CHARACTER_SETS CHARACTER_SETS CHARACTER_SET_NAME Charset 253 96 4 N 4097 0 33 +def information_schema CHARACTER_SETS CHARACTER_SETS DESCRIPTION Description 253 180 13 N 4097 0 33 +def information_schema CHARACTER_SETS CHARACTER_SETS DEFAULT_COLLATE_NAME Default collation 253 96 15 N 4097 0 33 +def information_schema CHARACTER_SETS CHARACTER_SETS MAXLEN Maxlen 8 3 1 N 36865 0 63 Charset Description Default collation Maxlen utf8 UTF-8 Unicode utf8_general_ci 3 ---------------------------------------------------------------- SHOW COLLATION LIKE 'latin1_bin'; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def information_schema COLLATIONS COLLATIONS COLLATION_NAME Collation 253 96 10 N 1 0 33 -def information_schema COLLATIONS COLLATIONS CHARACTER_SET_NAME Charset 253 96 6 N 1 0 33 -def information_schema COLLATIONS COLLATIONS ID Id 8 11 2 N 32769 0 63 -def information_schema COLLATIONS COLLATIONS IS_DEFAULT Default 253 9 0 N 1 0 33 -def information_schema COLLATIONS COLLATIONS IS_COMPILED Compiled 253 9 3 N 1 0 33 -def information_schema COLLATIONS COLLATIONS SORTLEN Sortlen 8 3 1 N 32769 0 63 +def information_schema COLLATIONS COLLATIONS COLLATION_NAME Collation 253 96 10 N 4097 0 33 +def information_schema COLLATIONS COLLATIONS CHARACTER_SET_NAME Charset 253 96 6 N 4097 0 33 +def information_schema COLLATIONS COLLATIONS ID Id 8 11 2 N 36865 0 63 +def information_schema COLLATIONS COLLATIONS IS_DEFAULT Default 253 9 0 N 4097 0 33 +def information_schema COLLATIONS COLLATIONS IS_COMPILED Compiled 253 9 3 N 4097 0 33 +def information_schema COLLATIONS COLLATIONS SORTLEN Sortlen 8 3 1 N 36865 0 63 Collation Charset Id Default Compiled Sortlen latin1_bin latin1 47 Yes 1 ---------------------------------------------------------------- @@ -901,7 +901,7 @@ mysqltest1 CREATE DATABASE `mysqltest1` /*!40100 DEFAULT CHARACTER SET latin1 */ ---------------------------------------------------------------- SHOW DATABASES LIKE 'mysqltest1'; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def information_schema SCHEMATA SCHEMATA SCHEMA_NAME Database (mysqltest1) 253 192 10 N 1 0 33 +def information_schema SCHEMATA SCHEMATA SCHEMA_NAME Database (mysqltest1) 253 192 10 N 4097 0 33 Database (mysqltest1) mysqltest1 ---------------------------------------------------------------- @@ -917,19 +917,19 @@ t1 CREATE TABLE `t1` ( ---------------------------------------------------------------- SHOW INDEX FROM t1; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def information_schema STATISTICS STATISTICS TABLE_NAME Table 253 192 2 N 1 0 33 -def information_schema STATISTICS STATISTICS NON_UNIQUE Non_unique 8 1 1 N 32769 0 63 -def information_schema STATISTICS STATISTICS INDEX_NAME Key_name 253 192 7 N 1 0 33 -def information_schema STATISTICS STATISTICS SEQ_IN_INDEX Seq_in_index 8 2 1 N 32769 0 63 -def information_schema STATISTICS STATISTICS COLUMN_NAME Column_name 253 192 1 N 1 0 33 -def information_schema STATISTICS STATISTICS COLLATION Collation 253 3 1 Y 0 0 33 -def information_schema STATISTICS STATISTICS CARDINALITY Cardinality 8 21 1 Y 32768 0 63 -def information_schema STATISTICS STATISTICS SUB_PART Sub_part 8 3 0 Y 32768 0 63 -def information_schema STATISTICS STATISTICS PACKED Packed 253 30 0 Y 0 0 33 -def information_schema STATISTICS STATISTICS NULLABLE Null 253 9 0 N 1 0 33 -def information_schema STATISTICS STATISTICS INDEX_TYPE Index_type 253 48 5 N 1 0 33 -def information_schema STATISTICS STATISTICS COMMENT Comment 253 48 0 Y 0 0 33 -def information_schema STATISTICS STATISTICS INDEX_COMMENT Index_comment 253 3072 0 N 1 0 33 +def information_schema STATISTICS STATISTICS TABLE_NAME Table 253 192 2 N 4097 0 33 +def information_schema STATISTICS STATISTICS NON_UNIQUE Non_unique 8 1 1 N 36865 0 63 +def information_schema STATISTICS STATISTICS INDEX_NAME Key_name 253 192 7 N 4097 0 33 +def information_schema STATISTICS STATISTICS SEQ_IN_INDEX Seq_in_index 8 2 1 N 36865 0 63 +def information_schema STATISTICS STATISTICS COLUMN_NAME Column_name 253 192 1 N 4097 0 33 +def information_schema STATISTICS STATISTICS COLLATION Collation 253 3 1 Y 4096 0 33 +def information_schema STATISTICS STATISTICS CARDINALITY Cardinality 8 21 1 Y 36864 0 63 +def information_schema STATISTICS STATISTICS SUB_PART Sub_part 8 3 0 Y 36864 0 63 +def information_schema STATISTICS STATISTICS PACKED Packed 253 30 0 Y 4096 0 33 +def information_schema STATISTICS STATISTICS NULLABLE Null 253 9 0 N 4097 0 33 +def information_schema STATISTICS STATISTICS INDEX_TYPE Index_type 253 48 5 N 4097 0 33 +def information_schema STATISTICS STATISTICS COMMENT Comment 253 48 0 Y 4096 0 33 +def information_schema STATISTICS STATISTICS INDEX_COMMENT Index_comment 253 3072 0 N 4097 0 33 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment t1 0 PRIMARY 1 c A 0 NULL NULL BTREE ---------------------------------------------------------------- @@ -946,15 +946,15 @@ TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't1'; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def information_schema TABLES TABLES TABLE_CATALOG TABLE_CATALOG 253 1536 3 N 1 0 33 -def information_schema TABLES TABLES TABLE_SCHEMA TABLE_SCHEMA 253 192 4 N 1 0 33 -def information_schema TABLES TABLES TABLE_NAME TABLE_NAME 253 192 2 N 1 0 33 -def information_schema TABLES TABLES TABLE_TYPE TABLE_TYPE 253 192 10 N 1 0 33 -def information_schema TABLES TABLES ENGINE ENGINE 253 192 6 Y 0 0 33 -def information_schema TABLES TABLES ROW_FORMAT ROW_FORMAT 253 30 5 Y 0 0 33 -def information_schema TABLES TABLES TABLE_COLLATION TABLE_COLLATION 253 96 17 Y 0 0 33 -def information_schema TABLES TABLES CREATE_OPTIONS CREATE_OPTIONS 253 6144 0 Y 0 0 33 -def information_schema TABLES TABLES TABLE_COMMENT TABLE_COMMENT 253 6144 0 N 1 0 33 +def information_schema TABLES TABLES TABLE_CATALOG TABLE_CATALOG 253 1536 3 N 4097 0 33 +def information_schema TABLES TABLES TABLE_SCHEMA TABLE_SCHEMA 253 192 4 N 4097 0 33 +def information_schema TABLES TABLES TABLE_NAME TABLE_NAME 253 192 2 N 4097 0 33 +def information_schema TABLES TABLES TABLE_TYPE TABLE_TYPE 253 192 10 N 4097 0 33 +def information_schema TABLES TABLES ENGINE ENGINE 253 192 6 Y 4096 0 33 +def information_schema TABLES TABLES ROW_FORMAT ROW_FORMAT 253 30 5 Y 4096 0 33 +def information_schema TABLES TABLES TABLE_COLLATION TABLE_COLLATION 253 96 17 Y 4096 0 33 +def information_schema TABLES TABLES CREATE_OPTIONS CREATE_OPTIONS 253 6144 0 Y 4096 0 33 +def information_schema TABLES TABLES TABLE_COMMENT TABLE_COMMENT 253 6144 0 N 4097 0 33 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE ROW_FORMAT TABLE_COLLATION CREATE_OPTIONS TABLE_COMMENT def test t1 BASE TABLE MyISAM Fixed latin1_swedish_ci ---------------------------------------------------------------- @@ -976,53 +976,53 @@ COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 't1'; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def information_schema COLUMNS COLUMNS TABLE_CATALOG TABLE_CATALOG 253 1536 3 N 1 0 33 -def information_schema COLUMNS COLUMNS TABLE_SCHEMA TABLE_SCHEMA 253 192 4 N 1 0 33 -def information_schema COLUMNS COLUMNS TABLE_NAME TABLE_NAME 253 192 2 N 1 0 33 -def information_schema COLUMNS COLUMNS COLUMN_NAME COLUMN_NAME 253 192 1 N 1 0 33 -def information_schema COLUMNS COLUMNS COLUMN_DEFAULT COLUMN_DEFAULT 252 589788 0 Y 16 0 33 -def information_schema COLUMNS COLUMNS IS_NULLABLE IS_NULLABLE 253 9 2 N 1 0 33 -def information_schema COLUMNS COLUMNS DATA_TYPE DATA_TYPE 253 192 3 N 1 0 33 -def information_schema COLUMNS COLUMNS CHARACTER_SET_NAME CHARACTER_SET_NAME 253 96 0 Y 0 0 33 -def information_schema COLUMNS COLUMNS COLLATION_NAME COLLATION_NAME 253 96 0 Y 0 0 33 -def information_schema COLUMNS COLUMNS COLUMN_TYPE COLUMN_TYPE 252 589815 7 N 17 0 33 -def information_schema COLUMNS COLUMNS COLUMN_KEY COLUMN_KEY 253 9 3 N 1 0 33 -def information_schema COLUMNS COLUMNS EXTRA EXTRA 253 90 0 N 1 0 33 -def information_schema COLUMNS COLUMNS PRIVILEGES PRIVILEGES 253 240 31 N 1 0 33 -def information_schema COLUMNS COLUMNS COLUMN_COMMENT COLUMN_COMMENT 253 3072 0 N 1 0 33 +def information_schema COLUMNS COLUMNS TABLE_CATALOG TABLE_CATALOG 253 1536 3 N 4097 0 33 +def information_schema COLUMNS COLUMNS TABLE_SCHEMA TABLE_SCHEMA 253 192 4 N 4097 0 33 +def information_schema COLUMNS COLUMNS TABLE_NAME TABLE_NAME 253 192 2 N 4097 0 33 +def information_schema COLUMNS COLUMNS COLUMN_NAME COLUMN_NAME 253 192 1 N 4097 0 33 +def information_schema COLUMNS COLUMNS COLUMN_DEFAULT COLUMN_DEFAULT 252 589788 0 Y 4112 0 33 +def information_schema COLUMNS COLUMNS IS_NULLABLE IS_NULLABLE 253 9 2 N 4097 0 33 +def information_schema COLUMNS COLUMNS DATA_TYPE DATA_TYPE 253 192 3 N 4097 0 33 +def information_schema COLUMNS COLUMNS CHARACTER_SET_NAME CHARACTER_SET_NAME 253 96 0 Y 4096 0 33 +def information_schema COLUMNS COLUMNS COLLATION_NAME COLLATION_NAME 253 96 0 Y 4096 0 33 +def information_schema COLUMNS COLUMNS COLUMN_TYPE COLUMN_TYPE 252 589815 7 N 4113 0 33 +def information_schema COLUMNS COLUMNS COLUMN_KEY COLUMN_KEY 253 9 3 N 4097 0 33 +def information_schema COLUMNS COLUMNS EXTRA EXTRA 253 90 0 N 4097 0 33 +def information_schema COLUMNS COLUMNS PRIVILEGES PRIVILEGES 253 240 31 N 4097 0 33 +def information_schema COLUMNS COLUMNS COLUMN_COMMENT COLUMN_COMMENT 253 3072 0 N 4097 0 33 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT def test t1 c NULL NO int NULL NULL int(11) PRI select,insert,update,references ---------------------------------------------------------------- SHOW TABLES LIKE 't1'; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def information_schema TABLE_NAMES TABLE_NAMES TABLE_NAME Tables_in_test (t1) 253 219 2 N 1 0 33 +def information_schema TABLE_NAMES TABLE_NAMES TABLE_NAME Tables_in_test (t1) 253 219 2 N 4097 0 33 Tables_in_test (t1) t1 ---------------------------------------------------------------- SHOW COLUMNS FROM t1; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def information_schema COLUMNS COLUMNS COLUMN_NAME Field 253 192 1 N 1 0 33 -def information_schema COLUMNS COLUMNS COLUMN_TYPE Type 252 589815 7 N 17 0 33 -def information_schema COLUMNS COLUMNS IS_NULLABLE Null 253 9 2 N 1 0 33 -def information_schema COLUMNS COLUMNS COLUMN_KEY Key 253 9 3 N 1 0 33 -def information_schema COLUMNS COLUMNS COLUMN_DEFAULT Default 252 589788 0 Y 16 0 33 -def information_schema COLUMNS COLUMNS EXTRA Extra 253 90 0 N 1 0 33 +def information_schema COLUMNS COLUMNS COLUMN_NAME Field 253 192 1 N 4097 0 33 +def information_schema COLUMNS COLUMNS COLUMN_TYPE Type 252 589815 7 N 4113 0 33 +def information_schema COLUMNS COLUMNS IS_NULLABLE Null 253 9 2 N 4097 0 33 +def information_schema COLUMNS COLUMNS COLUMN_KEY Key 253 9 3 N 4097 0 33 +def information_schema COLUMNS COLUMNS COLUMN_DEFAULT Default 252 589788 0 Y 4112 0 33 +def information_schema COLUMNS COLUMNS EXTRA Extra 253 90 0 N 4097 0 33 Field Type Null Key Default Extra c int(11) NO PRI NULL ---------------------------------------------------------------- SHOW TRIGGERS LIKE 't1'; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def information_schema TRIGGERS TRIGGERS TRIGGER_NAME Trigger 253 192 5 N 1 0 33 -def information_schema TRIGGERS TRIGGERS EVENT_MANIPULATION Event 253 18 6 N 1 0 33 -def information_schema TRIGGERS TRIGGERS EVENT_OBJECT_TABLE Table 253 192 2 N 1 0 33 -def information_schema TRIGGERS TRIGGERS ACTION_STATEMENT Statement 252 589815 10 N 17 0 33 -def information_schema TRIGGERS TRIGGERS ACTION_TIMING Timing 253 18 6 N 1 0 33 -def information_schema TRIGGERS TRIGGERS CREATED Created 12 22 22 Y 128 2 63 -def information_schema TRIGGERS TRIGGERS SQL_MODE sql_mode 253 24576 89 N 1 0 33 -def information_schema TRIGGERS TRIGGERS DEFINER Definer 253 567 14 N 1 0 33 -def information_schema TRIGGERS TRIGGERS CHARACTER_SET_CLIENT character_set_client 253 96 6 N 1 0 33 -def information_schema TRIGGERS TRIGGERS COLLATION_CONNECTION collation_connection 253 96 6 N 1 0 33 -def information_schema TRIGGERS TRIGGERS DATABASE_COLLATION Database Collation 253 96 17 N 1 0 33 +def information_schema TRIGGERS TRIGGERS TRIGGER_NAME Trigger 253 192 5 N 4097 0 33 +def information_schema TRIGGERS TRIGGERS EVENT_MANIPULATION Event 253 18 6 N 4097 0 33 +def information_schema TRIGGERS TRIGGERS EVENT_OBJECT_TABLE Table 253 192 2 N 4097 0 33 +def information_schema TRIGGERS TRIGGERS ACTION_STATEMENT Statement 252 589815 10 N 4113 0 33 +def information_schema TRIGGERS TRIGGERS ACTION_TIMING Timing 253 18 6 N 4097 0 33 +def information_schema TRIGGERS TRIGGERS CREATED Created 12 22 22 Y 4224 2 63 +def information_schema TRIGGERS TRIGGERS SQL_MODE sql_mode 253 24576 89 N 4097 0 33 +def information_schema TRIGGERS TRIGGERS DEFINER Definer 253 567 14 N 4097 0 33 +def information_schema TRIGGERS TRIGGERS CHARACTER_SET_CLIENT character_set_client 253 96 6 N 4097 0 33 +def information_schema TRIGGERS TRIGGERS COLLATION_CONNECTION collation_connection 253 96 6 N 4097 0 33 +def information_schema TRIGGERS TRIGGERS DATABASE_COLLATION Database Collation 253 96 17 N 4097 0 33 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation t1_bi INSERT t1 SET @a = 1 BEFORE # STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost binary binary latin1_swedish_ci ---------------------------------------------------------------- @@ -1047,28 +1047,28 @@ DEFINER FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name = 't1_bi'; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def information_schema TRIGGERS TRIGGERS TRIGGER_CATALOG TRIGGER_CATALOG 253 1536 3 N 1 0 33 -def information_schema TRIGGERS TRIGGERS TRIGGER_SCHEMA TRIGGER_SCHEMA 253 192 4 N 1 0 33 -def information_schema TRIGGERS TRIGGERS TRIGGER_NAME TRIGGER_NAME 253 192 5 N 1 0 33 -def information_schema TRIGGERS TRIGGERS EVENT_MANIPULATION EVENT_MANIPULATION 253 18 6 N 1 0 33 -def information_schema TRIGGERS TRIGGERS EVENT_OBJECT_CATALOG EVENT_OBJECT_CATALOG 253 1536 3 N 1 0 33 -def information_schema TRIGGERS TRIGGERS EVENT_OBJECT_SCHEMA EVENT_OBJECT_SCHEMA 253 192 4 N 1 0 33 -def information_schema TRIGGERS TRIGGERS EVENT_OBJECT_TABLE EVENT_OBJECT_TABLE 253 192 2 N 1 0 33 -def information_schema TRIGGERS TRIGGERS ACTION_CONDITION ACTION_CONDITION 252 589815 0 Y 16 0 33 -def information_schema TRIGGERS TRIGGERS ACTION_STATEMENT ACTION_STATEMENT 252 589815 10 N 17 0 33 -def information_schema TRIGGERS TRIGGERS ACTION_ORIENTATION ACTION_ORIENTATION 253 27 3 N 1 0 33 -def information_schema TRIGGERS TRIGGERS ACTION_TIMING ACTION_TIMING 253 18 6 N 1 0 33 -def information_schema TRIGGERS TRIGGERS ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_OLD_TABLE 253 192 0 Y 0 0 33 -def information_schema TRIGGERS TRIGGERS ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_NEW_TABLE 253 192 0 Y 0 0 33 -def information_schema TRIGGERS TRIGGERS ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_OLD_ROW 253 9 3 N 1 0 33 -def information_schema TRIGGERS TRIGGERS ACTION_REFERENCE_NEW_ROW ACTION_REFERENCE_NEW_ROW 253 9 3 N 1 0 33 -def information_schema TRIGGERS TRIGGERS SQL_MODE SQL_MODE 253 24576 89 N 1 0 33 -def information_schema TRIGGERS TRIGGERS DEFINER DEFINER 253 567 14 N 1 0 33 +def information_schema TRIGGERS TRIGGERS TRIGGER_CATALOG TRIGGER_CATALOG 253 1536 3 N 4097 0 33 +def information_schema TRIGGERS TRIGGERS TRIGGER_SCHEMA TRIGGER_SCHEMA 253 192 4 N 4097 0 33 +def information_schema TRIGGERS TRIGGERS TRIGGER_NAME TRIGGER_NAME 253 192 5 N 4097 0 33 +def information_schema TRIGGERS TRIGGERS EVENT_MANIPULATION EVENT_MANIPULATION 253 18 6 N 4097 0 33 +def information_schema TRIGGERS TRIGGERS EVENT_OBJECT_CATALOG EVENT_OBJECT_CATALOG 253 1536 3 N 4097 0 33 +def information_schema TRIGGERS TRIGGERS EVENT_OBJECT_SCHEMA EVENT_OBJECT_SCHEMA 253 192 4 N 4097 0 33 +def information_schema TRIGGERS TRIGGERS EVENT_OBJECT_TABLE EVENT_OBJECT_TABLE 253 192 2 N 4097 0 33 +def information_schema TRIGGERS TRIGGERS ACTION_CONDITION ACTION_CONDITION 252 589815 0 Y 4112 0 33 +def information_schema TRIGGERS TRIGGERS ACTION_STATEMENT ACTION_STATEMENT 252 589815 10 N 4113 0 33 +def information_schema TRIGGERS TRIGGERS ACTION_ORIENTATION ACTION_ORIENTATION 253 27 3 N 4097 0 33 +def information_schema TRIGGERS TRIGGERS ACTION_TIMING ACTION_TIMING 253 18 6 N 4097 0 33 +def information_schema TRIGGERS TRIGGERS ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_OLD_TABLE 253 192 0 Y 4096 0 33 +def information_schema TRIGGERS TRIGGERS ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_NEW_TABLE 253 192 0 Y 4096 0 33 +def information_schema TRIGGERS TRIGGERS ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_OLD_ROW 253 9 3 N 4097 0 33 +def information_schema TRIGGERS TRIGGERS ACTION_REFERENCE_NEW_ROW ACTION_REFERENCE_NEW_ROW 253 9 3 N 4097 0 33 +def information_schema TRIGGERS TRIGGERS SQL_MODE SQL_MODE 253 24576 89 N 4097 0 33 +def information_schema TRIGGERS TRIGGERS DEFINER DEFINER 253 567 14 N 4097 0 33 TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW SQL_MODE DEFINER def test t1_bi INSERT def test t1 NULL SET @a = 1 ROW BEFORE NULL NULL OLD NEW STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost SELECT CREATED FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name='t1_bi'; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def information_schema TRIGGERS TRIGGERS CREATED CREATED 12 22 22 Y 128 2 63 +def information_schema TRIGGERS TRIGGERS CREATED CREATED 12 22 22 Y 4224 2 63 CREATED # ---------------------------------------------------------------- @@ -1085,17 +1085,17 @@ SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE table_name = 'v1'; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def information_schema VIEWS VIEWS TABLE_CATALOG TABLE_CATALOG 253 1536 3 N 1 0 33 -def information_schema VIEWS VIEWS TABLE_SCHEMA TABLE_SCHEMA 253 192 4 N 1 0 33 -def information_schema VIEWS VIEWS TABLE_NAME TABLE_NAME 253 192 2 N 1 0 33 -def information_schema VIEWS VIEWS VIEW_DEFINITION VIEW_DEFINITION 252 589815 15 N 17 0 33 -def information_schema VIEWS VIEWS CHECK_OPTION CHECK_OPTION 253 24 4 N 1 0 33 -def information_schema VIEWS VIEWS IS_UPDATABLE IS_UPDATABLE 253 9 2 N 1 0 33 -def information_schema VIEWS VIEWS DEFINER DEFINER 253 567 14 N 1 0 33 -def information_schema VIEWS VIEWS SECURITY_TYPE SECURITY_TYPE 253 21 7 N 1 0 33 -def information_schema VIEWS VIEWS CHARACTER_SET_CLIENT CHARACTER_SET_CLIENT 253 96 6 N 1 0 33 -def information_schema VIEWS VIEWS COLLATION_CONNECTION COLLATION_CONNECTION 253 96 6 N 1 0 33 -def information_schema VIEWS VIEWS ALGORITHM ALGORITHM 253 30 9 N 1 0 33 +def information_schema VIEWS VIEWS TABLE_CATALOG TABLE_CATALOG 253 1536 3 N 4097 0 33 +def information_schema VIEWS VIEWS TABLE_SCHEMA TABLE_SCHEMA 253 192 4 N 4097 0 33 +def information_schema VIEWS VIEWS TABLE_NAME TABLE_NAME 253 192 2 N 4097 0 33 +def information_schema VIEWS VIEWS VIEW_DEFINITION VIEW_DEFINITION 252 589815 15 N 4113 0 33 +def information_schema VIEWS VIEWS CHECK_OPTION CHECK_OPTION 253 24 4 N 4097 0 33 +def information_schema VIEWS VIEWS IS_UPDATABLE IS_UPDATABLE 253 9 2 N 4097 0 33 +def information_schema VIEWS VIEWS DEFINER DEFINER 253 567 14 N 4097 0 33 +def information_schema VIEWS VIEWS SECURITY_TYPE SECURITY_TYPE 253 21 7 N 4097 0 33 +def information_schema VIEWS VIEWS CHARACTER_SET_CLIENT CHARACTER_SET_CLIENT 253 96 6 N 4097 0 33 +def information_schema VIEWS VIEWS COLLATION_CONNECTION COLLATION_CONNECTION 253 96 6 N 4097 0 33 +def information_schema VIEWS VIEWS ALGORITHM ALGORITHM 253 30 9 N 4097 0 33 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION ALGORITHM def test v1 select 1 AS `1` NONE NO root@localhost DEFINER binary binary UNDEFINED ---------------------------------------------------------------- @@ -1133,24 +1133,24 @@ DEFINER FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_name = 'p1'; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def information_schema ROUTINES ROUTINES SPECIFIC_NAME SPECIFIC_NAME 253 192 2 N 1 0 33 -def information_schema ROUTINES ROUTINES ROUTINE_CATALOG ROUTINE_CATALOG 253 1536 3 N 1 0 33 -def information_schema ROUTINES ROUTINES ROUTINE_SCHEMA ROUTINE_SCHEMA 253 192 4 N 1 0 33 -def information_schema ROUTINES ROUTINES ROUTINE_NAME ROUTINE_NAME 253 192 2 N 1 0 33 -def information_schema ROUTINES ROUTINES ROUTINE_TYPE ROUTINE_TYPE 253 39 9 N 1 0 33 -def information_schema ROUTINES ROUTINES DTD_IDENTIFIER DTD_IDENTIFIER 252 589815 0 Y 16 0 33 -def information_schema ROUTINES ROUTINES ROUTINE_BODY ROUTINE_BODY 253 24 3 N 1 0 33 -def information_schema ROUTINES ROUTINES ROUTINE_DEFINITION ROUTINE_DEFINITION 252 589815 8 Y 16 0 33 -def information_schema ROUTINES ROUTINES EXTERNAL_NAME EXTERNAL_NAME 253 192 0 Y 0 0 33 -def information_schema ROUTINES ROUTINES EXTERNAL_LANGUAGE EXTERNAL_LANGUAGE 253 192 0 Y 0 0 33 -def information_schema ROUTINES ROUTINES PARAMETER_STYLE PARAMETER_STYLE 253 24 3 N 1 0 33 -def information_schema ROUTINES ROUTINES IS_DETERMINISTIC IS_DETERMINISTIC 253 9 2 N 1 0 33 -def information_schema ROUTINES ROUTINES SQL_DATA_ACCESS SQL_DATA_ACCESS 253 192 12 N 1 0 33 -def information_schema ROUTINES ROUTINES SQL_PATH SQL_PATH 253 192 0 Y 0 0 33 -def information_schema ROUTINES ROUTINES SECURITY_TYPE SECURITY_TYPE 253 21 7 N 1 0 33 -def information_schema ROUTINES ROUTINES SQL_MODE SQL_MODE 253 24576 89 N 1 0 33 -def information_schema ROUTINES ROUTINES ROUTINE_COMMENT ROUTINE_COMMENT 252 589815 0 N 17 0 33 -def information_schema ROUTINES ROUTINES DEFINER DEFINER 253 567 14 N 1 0 33 +def information_schema ROUTINES ROUTINES SPECIFIC_NAME SPECIFIC_NAME 253 192 2 N 4097 0 33 +def information_schema ROUTINES ROUTINES ROUTINE_CATALOG ROUTINE_CATALOG 253 1536 3 N 4097 0 33 +def information_schema ROUTINES ROUTINES ROUTINE_SCHEMA ROUTINE_SCHEMA 253 192 4 N 4097 0 33 +def information_schema ROUTINES ROUTINES ROUTINE_NAME ROUTINE_NAME 253 192 2 N 4097 0 33 +def information_schema ROUTINES ROUTINES ROUTINE_TYPE ROUTINE_TYPE 253 39 9 N 4097 0 33 +def information_schema ROUTINES ROUTINES DTD_IDENTIFIER DTD_IDENTIFIER 252 589815 0 Y 4112 0 33 +def information_schema ROUTINES ROUTINES ROUTINE_BODY ROUTINE_BODY 253 24 3 N 4097 0 33 +def information_schema ROUTINES ROUTINES ROUTINE_DEFINITION ROUTINE_DEFINITION 252 589815 8 Y 4112 0 33 +def information_schema ROUTINES ROUTINES EXTERNAL_NAME EXTERNAL_NAME 253 192 0 Y 4096 0 33 +def information_schema ROUTINES ROUTINES EXTERNAL_LANGUAGE EXTERNAL_LANGUAGE 253 192 0 Y 4096 0 33 +def information_schema ROUTINES ROUTINES PARAMETER_STYLE PARAMETER_STYLE 253 24 3 N 4097 0 33 +def information_schema ROUTINES ROUTINES IS_DETERMINISTIC IS_DETERMINISTIC 253 9 2 N 4097 0 33 +def information_schema ROUTINES ROUTINES SQL_DATA_ACCESS SQL_DATA_ACCESS 253 192 12 N 4097 0 33 +def information_schema ROUTINES ROUTINES SQL_PATH SQL_PATH 253 192 0 Y 4096 0 33 +def information_schema ROUTINES ROUTINES SECURITY_TYPE SECURITY_TYPE 253 21 7 N 4097 0 33 +def information_schema ROUTINES ROUTINES SQL_MODE SQL_MODE 253 24576 89 N 4097 0 33 +def information_schema ROUTINES ROUTINES ROUTINE_COMMENT ROUTINE_COMMENT 252 589815 0 N 4113 0 33 +def information_schema ROUTINES ROUTINES DEFINER DEFINER 253 567 14 N 4097 0 33 SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE SQL_MODE ROUTINE_COMMENT DEFINER p1 def test p1 PROCEDURE NULL SQL SELECT 1 NULL NULL SQL NO CONTAINS SQL NULL DEFINER STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost ---------------------------------------------------------------- @@ -1188,24 +1188,24 @@ DEFINER FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_name = 'f1'; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def information_schema ROUTINES ROUTINES SPECIFIC_NAME SPECIFIC_NAME 253 192 2 N 1 0 33 -def information_schema ROUTINES ROUTINES ROUTINE_CATALOG ROUTINE_CATALOG 253 1536 3 N 1 0 33 -def information_schema ROUTINES ROUTINES ROUTINE_SCHEMA ROUTINE_SCHEMA 253 192 4 N 1 0 33 -def information_schema ROUTINES ROUTINES ROUTINE_NAME ROUTINE_NAME 253 192 2 N 1 0 33 -def information_schema ROUTINES ROUTINES ROUTINE_TYPE ROUTINE_TYPE 253 39 8 N 1 0 33 -def information_schema ROUTINES ROUTINES DTD_IDENTIFIER DTD_IDENTIFIER 252 589815 7 Y 16 0 33 -def information_schema ROUTINES ROUTINES ROUTINE_BODY ROUTINE_BODY 253 24 3 N 1 0 33 -def information_schema ROUTINES ROUTINES ROUTINE_DEFINITION ROUTINE_DEFINITION 252 589815 8 Y 16 0 33 -def information_schema ROUTINES ROUTINES EXTERNAL_NAME EXTERNAL_NAME 253 192 0 Y 0 0 33 -def information_schema ROUTINES ROUTINES EXTERNAL_LANGUAGE EXTERNAL_LANGUAGE 253 192 0 Y 0 0 33 -def information_schema ROUTINES ROUTINES PARAMETER_STYLE PARAMETER_STYLE 253 24 3 N 1 0 33 -def information_schema ROUTINES ROUTINES IS_DETERMINISTIC IS_DETERMINISTIC 253 9 2 N 1 0 33 -def information_schema ROUTINES ROUTINES SQL_DATA_ACCESS SQL_DATA_ACCESS 253 192 12 N 1 0 33 -def information_schema ROUTINES ROUTINES SQL_PATH SQL_PATH 253 192 0 Y 0 0 33 -def information_schema ROUTINES ROUTINES SECURITY_TYPE SECURITY_TYPE 253 21 7 N 1 0 33 -def information_schema ROUTINES ROUTINES SQL_MODE SQL_MODE 253 24576 89 N 1 0 33 -def information_schema ROUTINES ROUTINES ROUTINE_COMMENT ROUTINE_COMMENT 252 589815 0 N 17 0 33 -def information_schema ROUTINES ROUTINES DEFINER DEFINER 253 567 14 N 1 0 33 +def information_schema ROUTINES ROUTINES SPECIFIC_NAME SPECIFIC_NAME 253 192 2 N 4097 0 33 +def information_schema ROUTINES ROUTINES ROUTINE_CATALOG ROUTINE_CATALOG 253 1536 3 N 4097 0 33 +def information_schema ROUTINES ROUTINES ROUTINE_SCHEMA ROUTINE_SCHEMA 253 192 4 N 4097 0 33 +def information_schema ROUTINES ROUTINES ROUTINE_NAME ROUTINE_NAME 253 192 2 N 4097 0 33 +def information_schema ROUTINES ROUTINES ROUTINE_TYPE ROUTINE_TYPE 253 39 8 N 4097 0 33 +def information_schema ROUTINES ROUTINES DTD_IDENTIFIER DTD_IDENTIFIER 252 589815 7 Y 4112 0 33 +def information_schema ROUTINES ROUTINES ROUTINE_BODY ROUTINE_BODY 253 24 3 N 4097 0 33 +def information_schema ROUTINES ROUTINES ROUTINE_DEFINITION ROUTINE_DEFINITION 252 589815 8 Y 4112 0 33 +def information_schema ROUTINES ROUTINES EXTERNAL_NAME EXTERNAL_NAME 253 192 0 Y 4096 0 33 +def information_schema ROUTINES ROUTINES EXTERNAL_LANGUAGE EXTERNAL_LANGUAGE 253 192 0 Y 4096 0 33 +def information_schema ROUTINES ROUTINES PARAMETER_STYLE PARAMETER_STYLE 253 24 3 N 4097 0 33 +def information_schema ROUTINES ROUTINES IS_DETERMINISTIC IS_DETERMINISTIC 253 9 2 N 4097 0 33 +def information_schema ROUTINES ROUTINES SQL_DATA_ACCESS SQL_DATA_ACCESS 253 192 12 N 4097 0 33 +def information_schema ROUTINES ROUTINES SQL_PATH SQL_PATH 253 192 0 Y 4096 0 33 +def information_schema ROUTINES ROUTINES SECURITY_TYPE SECURITY_TYPE 253 21 7 N 4097 0 33 +def information_schema ROUTINES ROUTINES SQL_MODE SQL_MODE 253 24576 89 N 4097 0 33 +def information_schema ROUTINES ROUTINES ROUTINE_COMMENT ROUTINE_COMMENT 252 589815 0 N 4113 0 33 +def information_schema ROUTINES ROUTINES DEFINER DEFINER 253 567 14 N 4097 0 33 SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE SQL_MODE ROUTINE_COMMENT DEFINER f1 def test f1 FUNCTION int(11) SQL RETURN 1 NULL NULL SQL NO CONTAINS SQL NULL DEFINER STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost ---------------------------------------------------------------- diff --git a/mysql-test/main/sp.result b/mysql-test/main/sp.result index 35ec5fc628f..7dfaa6176a3 100644 --- a/mysql-test/main/sp.result +++ b/mysql-test/main/sp.result @@ -8483,6 +8483,34 @@ b-c 0 drop procedure p1| drop function f1| +# +# MDEV-24827: MariaDB 10.5.5 crash (sig 11) during a SELECT +# +CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT); +CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 INT, KEY idx_c2(c2)); +INSERT INTO t1 (c1, c2) SELECT seq, seq FROM seq_1_to_10000; +INSERT INTO t2 (c1, c2) SELECT seq, seq FROM seq_1_to_20000; +CREATE OR REPLACE PROCEDURE p1() +begin +DECLARE done INT DEFAULT FALSE; +DECLARE a INT; +DECLARE cur1 CURSOR FOR +SELECT t2.c1 AS c1 FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1 +WHERE EXISTS (SELECT 1 FROM t1 WHERE c2 = -1) ORDER BY c1; +DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; +OPEN cur1; +read_loop: LOOP +FETCH cur1 INTO a; +IF done THEN +LEAVE read_loop; +END IF; +END LOOP; +CLOSE cur1; +END $ +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; +DROP TABLE t2; #End of 10.2 tests # # MDEV-12007 Allow ROW variables as a cursor FETCH target diff --git a/mysql-test/main/sp.test b/mysql-test/main/sp.test index 681ffa3fb33..bd614072e1a 100644 --- a/mysql-test/main/sp.test +++ b/mysql-test/main/sp.test @@ -1,4 +1,6 @@ --source include/have_partition.inc +--source include/have_sequence.inc + # # Basic stored PROCEDURE tests # @@ -10032,6 +10034,46 @@ drop procedure p1| drop function f1| delimiter ;| +--echo # +--echo # MDEV-24827: MariaDB 10.5.5 crash (sig 11) during a SELECT +--echo # + +CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT); +CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 INT, KEY idx_c2(c2)); + +INSERT INTO t1 (c1, c2) SELECT seq, seq FROM seq_1_to_10000; +INSERT INTO t2 (c1, c2) SELECT seq, seq FROM seq_1_to_20000; + +--delimiter $ + +CREATE OR REPLACE PROCEDURE p1() +begin + DECLARE done INT DEFAULT FALSE; + DECLARE a INT; + + DECLARE cur1 CURSOR FOR + SELECT t2.c1 AS c1 FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1 + WHERE EXISTS (SELECT 1 FROM t1 WHERE c2 = -1) ORDER BY c1; + + DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; + + OPEN cur1; + read_loop: LOOP + FETCH cur1 INTO a; + IF done THEN + LEAVE read_loop; + END IF; + END LOOP; + CLOSE cur1; +END $ + +--delimiter ; + +CALL p1(); + +DROP PROCEDURE p1; +DROP TABLE t1; +DROP TABLE t2; --echo #End of 10.2 tests diff --git a/mysql-test/main/subselect_sj.result b/mysql-test/main/subselect_sj.result index f3519e1d846..3af248e7ab2 100644 --- a/mysql-test/main/subselect_sj.result +++ b/mysql-test/main/subselect_sj.result @@ -3318,4 +3318,22 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 500 drop table t1, t2, t3, t4; +# +# MDEV-20770: Server crashes in JOIN::transform_in_predicates_into_in_subq +# upon 2nd execution of PS/SP comparing GEOMETRY with other types +# +CREATE TABLE t1 (a GEOMETRY); +CREATE TABLE t2 (b INT); +INSERT INTO t1 VALUES (GeomFromText('POINT(0 0)')),(GeomFromText('POINT(1 1)')); +INSERT INTO t2 VALUES (1),(2); +PREPARE stmt FROM "SELECT * from t1 WHERE a IN (SELECT b FROM t2)"; +EXECUTE stmt; +ERROR HY000: Illegal parameter data types geometry and int for operation '=' +EXECUTE stmt; +ERROR HY000: Illegal parameter data types geometry and int for operation '=' +EXECUTE stmt; +ERROR HY000: Illegal parameter data types geometry and int for operation '=' +EXECUTE stmt; +ERROR HY000: Illegal parameter data types geometry and int for operation '=' +DROP TABLE t1, t2; set optimizer_switch=@subselect_sj_tmp; diff --git a/mysql-test/main/subselect_sj.test b/mysql-test/main/subselect_sj.test index c5c3354bc32..324f2828de2 100644 --- a/mysql-test/main/subselect_sj.test +++ b/mysql-test/main/subselect_sj.test @@ -2995,5 +2995,28 @@ explain select * from t3 where a in (select a from t4); drop table t1, t2, t3, t4; +--echo # +--echo # MDEV-20770: Server crashes in JOIN::transform_in_predicates_into_in_subq +--echo # upon 2nd execution of PS/SP comparing GEOMETRY with other types +--echo # + +CREATE TABLE t1 (a GEOMETRY); +CREATE TABLE t2 (b INT); + +INSERT INTO t1 VALUES (GeomFromText('POINT(0 0)')),(GeomFromText('POINT(1 1)')); +INSERT INTO t2 VALUES (1),(2); + +PREPARE stmt FROM "SELECT * from t1 WHERE a IN (SELECT b FROM t2)"; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +EXECUTE stmt; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +EXECUTE stmt; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +EXECUTE stmt; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +EXECUTE stmt; + +DROP TABLE t1, t2; + # The following command must be the last one the file set optimizer_switch=@subselect_sj_tmp; diff --git a/mysql-test/main/subselect_sj_jcl6.result b/mysql-test/main/subselect_sj_jcl6.result index 02e9a833db6..645c123cfcd 100644 --- a/mysql-test/main/subselect_sj_jcl6.result +++ b/mysql-test/main/subselect_sj_jcl6.result @@ -3329,6 +3329,24 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 500 drop table t1, t2, t3, t4; +# +# MDEV-20770: Server crashes in JOIN::transform_in_predicates_into_in_subq +# upon 2nd execution of PS/SP comparing GEOMETRY with other types +# +CREATE TABLE t1 (a GEOMETRY); +CREATE TABLE t2 (b INT); +INSERT INTO t1 VALUES (GeomFromText('POINT(0 0)')),(GeomFromText('POINT(1 1)')); +INSERT INTO t2 VALUES (1),(2); +PREPARE stmt FROM "SELECT * from t1 WHERE a IN (SELECT b FROM t2)"; +EXECUTE stmt; +ERROR HY000: Illegal parameter data types geometry and int for operation '=' +EXECUTE stmt; +ERROR HY000: Illegal parameter data types geometry and int for operation '=' +EXECUTE stmt; +ERROR HY000: Illegal parameter data types geometry and int for operation '=' +EXECUTE stmt; +ERROR HY000: Illegal parameter data types geometry and int for operation '=' +DROP TABLE t1, t2; set optimizer_switch=@subselect_sj_tmp; # # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off diff --git a/mysql-test/main/trigger.result b/mysql-test/main/trigger.result index 339394a29c2..756fd8591dd 100644 --- a/mysql-test/main/trigger.result +++ b/mysql-test/main/trigger.result @@ -2428,6 +2428,13 @@ CREATE TRIGGER t1_trigger BEFORE INSERT ON t1 FOR EACH ROW BEGIN END; INSERT INTO t1 () VALUES (); DROP TABLE t1; # +# Bug#33141958 - THE FIRST ASAN UAF ISSUE OF MYSQL SERVER +# +create table t1 (a int); +create trigger tr1 after insert on t1 for each row alter table t1 tablespace s2; +ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger +drop table t1; +# # End of 10.2 tests # # diff --git a/mysql-test/main/trigger.test b/mysql-test/main/trigger.test index 581015a45e6..56e9246b95c 100644 --- a/mysql-test/main/trigger.test +++ b/mysql-test/main/trigger.test @@ -2757,6 +2757,14 @@ INSERT INTO t1 () VALUES (); DROP TABLE t1; --echo # +--echo # Bug#33141958 - THE FIRST ASAN UAF ISSUE OF MYSQL SERVER +--echo # +create table t1 (a int); +--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +create trigger tr1 after insert on t1 for each row alter table t1 tablespace s2; +drop table t1; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/type_enum.result b/mysql-test/main/type_enum.result index c8277ab832f..d17b94bbb4f 100644 --- a/mysql-test/main/type_enum.result +++ b/mysql-test/main/type_enum.result @@ -2225,6 +2225,27 @@ a DROP TABLE t1; # +# MDEV-26129 Bad results with join comparing case insensitive VARCHAR/ENUM/SET expression to a _bin ENUM column +# +CREATE TABLE t1 (a ENUM('a') CHARACTER SET latin1 PRIMARY KEY); +INSERT INTO t1 VALUES ('a'); +CREATE TABLE t2 (a ENUM('a','A','b','B','c','C','d','D','e','E') CHARACTER SET latin1 COLLATE latin1_bin); +INSERT INTO t2 VALUES ('a'),('A'); +INSERT INTO t2 VALUES ('b'),('B'),('c'),('C'),('d'),('D'),('e'),('E'); +ALTER TABLE t2 ADD PRIMARY KEY(a); +SELECT t1.a res FROM t1 JOIN t2 ON t1.a COLLATE latin1_swedish_ci=t2.a; +res +a +a +SELECT t1.a res FROM t1 LEFT JOIN t2 ON t1.a COLLATE latin1_swedish_ci=t2.a; +res +a +a +DROP TABLE IF EXISTS t1,t2; +# +# End of 10.2. tests +# +# # Start of 10.3 tests # # diff --git a/mysql-test/main/type_enum.test b/mysql-test/main/type_enum.test index a36d63f8725..6ff3e257feb 100644 --- a/mysql-test/main/type_enum.test +++ b/mysql-test/main/type_enum.test @@ -458,6 +458,26 @@ SELECT * FROM t1; DROP TABLE t1; --echo # +--echo # MDEV-26129 Bad results with join comparing case insensitive VARCHAR/ENUM/SET expression to a _bin ENUM column +--echo # + +CREATE TABLE t1 (a ENUM('a') CHARACTER SET latin1 PRIMARY KEY); +INSERT INTO t1 VALUES ('a'); +CREATE TABLE t2 (a ENUM('a','A','b','B','c','C','d','D','e','E') CHARACTER SET latin1 COLLATE latin1_bin); +INSERT INTO t2 VALUES ('a'),('A'); +# without the following insert the bug doesn't show, was fixed in MDEV-6978 +INSERT INTO t2 VALUES ('b'),('B'),('c'),('C'),('d'),('D'),('e'),('E'); +ALTER TABLE t2 ADD PRIMARY KEY(a); +SELECT t1.a res FROM t1 JOIN t2 ON t1.a COLLATE latin1_swedish_ci=t2.a; +SELECT t1.a res FROM t1 LEFT JOIN t2 ON t1.a COLLATE latin1_swedish_ci=t2.a; +DROP TABLE IF EXISTS t1,t2; + + +--echo # +--echo # End of 10.2. tests +--echo # + +--echo # --echo # Start of 10.3 tests --echo # diff --git a/mysql-test/main/userstat.result b/mysql-test/main/userstat.result index 9152f602304..5315317e33a 100644 --- a/mysql-test/main/userstat.result +++ b/mysql-test/main/userstat.result @@ -3,71 +3,71 @@ Warnings: Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead show columns from information_schema.client_statistics; Field Type Null Key Default Extra -CLIENT varchar(64) NO -TOTAL_CONNECTIONS bigint(21) NO 0 -CONCURRENT_CONNECTIONS bigint(21) NO 0 -CONNECTED_TIME bigint(21) NO 0 -BUSY_TIME double NO 0 -CPU_TIME double NO 0 -BYTES_RECEIVED bigint(21) NO 0 -BYTES_SENT bigint(21) NO 0 -BINLOG_BYTES_WRITTEN bigint(21) NO 0 -ROWS_READ bigint(21) NO 0 -ROWS_SENT bigint(21) NO 0 -ROWS_DELETED bigint(21) NO 0 -ROWS_INSERTED bigint(21) NO 0 -ROWS_UPDATED bigint(21) NO 0 -SELECT_COMMANDS bigint(21) NO 0 -UPDATE_COMMANDS bigint(21) NO 0 -OTHER_COMMANDS bigint(21) NO 0 -COMMIT_TRANSACTIONS bigint(21) NO 0 -ROLLBACK_TRANSACTIONS bigint(21) NO 0 -DENIED_CONNECTIONS bigint(21) NO 0 -LOST_CONNECTIONS bigint(21) NO 0 -ACCESS_DENIED bigint(21) NO 0 -EMPTY_QUERIES bigint(21) NO 0 -TOTAL_SSL_CONNECTIONS bigint(21) unsigned NO 0 -MAX_STATEMENT_TIME_EXCEEDED bigint(21) NO 0 +CLIENT varchar(64) NO NULL +TOTAL_CONNECTIONS bigint(21) NO NULL +CONCURRENT_CONNECTIONS bigint(21) NO NULL +CONNECTED_TIME bigint(21) NO NULL +BUSY_TIME double NO NULL +CPU_TIME double NO NULL +BYTES_RECEIVED bigint(21) NO NULL +BYTES_SENT bigint(21) NO NULL +BINLOG_BYTES_WRITTEN bigint(21) NO NULL +ROWS_READ bigint(21) NO NULL +ROWS_SENT bigint(21) NO NULL +ROWS_DELETED bigint(21) NO NULL +ROWS_INSERTED bigint(21) NO NULL +ROWS_UPDATED bigint(21) NO NULL +SELECT_COMMANDS bigint(21) NO NULL +UPDATE_COMMANDS bigint(21) NO NULL +OTHER_COMMANDS bigint(21) NO NULL +COMMIT_TRANSACTIONS bigint(21) NO NULL +ROLLBACK_TRANSACTIONS bigint(21) NO NULL +DENIED_CONNECTIONS bigint(21) NO NULL +LOST_CONNECTIONS bigint(21) NO NULL +ACCESS_DENIED bigint(21) NO NULL +EMPTY_QUERIES bigint(21) NO NULL +TOTAL_SSL_CONNECTIONS bigint(21) unsigned NO NULL +MAX_STATEMENT_TIME_EXCEEDED bigint(21) NO NULL show columns from information_schema.user_statistics; Field Type Null Key Default Extra -USER varchar(128) NO -TOTAL_CONNECTIONS int(11) NO 0 -CONCURRENT_CONNECTIONS int(11) NO 0 -CONNECTED_TIME int(11) NO 0 -BUSY_TIME double NO 0 -CPU_TIME double NO 0 -BYTES_RECEIVED bigint(21) NO 0 -BYTES_SENT bigint(21) NO 0 -BINLOG_BYTES_WRITTEN bigint(21) NO 0 -ROWS_READ bigint(21) NO 0 -ROWS_SENT bigint(21) NO 0 -ROWS_DELETED bigint(21) NO 0 -ROWS_INSERTED bigint(21) NO 0 -ROWS_UPDATED bigint(21) NO 0 -SELECT_COMMANDS bigint(21) NO 0 -UPDATE_COMMANDS bigint(21) NO 0 -OTHER_COMMANDS bigint(21) NO 0 -COMMIT_TRANSACTIONS bigint(21) NO 0 -ROLLBACK_TRANSACTIONS bigint(21) NO 0 -DENIED_CONNECTIONS bigint(21) NO 0 -LOST_CONNECTIONS bigint(21) NO 0 -ACCESS_DENIED bigint(21) NO 0 -EMPTY_QUERIES bigint(21) NO 0 -TOTAL_SSL_CONNECTIONS bigint(21) unsigned NO 0 -MAX_STATEMENT_TIME_EXCEEDED bigint(21) NO 0 +USER varchar(128) NO NULL +TOTAL_CONNECTIONS int(11) NO NULL +CONCURRENT_CONNECTIONS int(11) NO NULL +CONNECTED_TIME int(11) NO NULL +BUSY_TIME double NO NULL +CPU_TIME double NO NULL +BYTES_RECEIVED bigint(21) NO NULL +BYTES_SENT bigint(21) NO NULL +BINLOG_BYTES_WRITTEN bigint(21) NO NULL +ROWS_READ bigint(21) NO NULL +ROWS_SENT bigint(21) NO NULL +ROWS_DELETED bigint(21) NO NULL +ROWS_INSERTED bigint(21) NO NULL +ROWS_UPDATED bigint(21) NO NULL +SELECT_COMMANDS bigint(21) NO NULL +UPDATE_COMMANDS bigint(21) NO NULL +OTHER_COMMANDS bigint(21) NO NULL +COMMIT_TRANSACTIONS bigint(21) NO NULL +ROLLBACK_TRANSACTIONS bigint(21) NO NULL +DENIED_CONNECTIONS bigint(21) NO NULL +LOST_CONNECTIONS bigint(21) NO NULL +ACCESS_DENIED bigint(21) NO NULL +EMPTY_QUERIES bigint(21) NO NULL +TOTAL_SSL_CONNECTIONS bigint(21) unsigned NO NULL +MAX_STATEMENT_TIME_EXCEEDED bigint(21) NO NULL show columns from information_schema.index_statistics; Field Type Null Key Default Extra -TABLE_SCHEMA varchar(192) NO -TABLE_NAME varchar(192) NO -INDEX_NAME varchar(192) NO -ROWS_READ bigint(21) NO 0 +TABLE_SCHEMA varchar(192) NO NULL +TABLE_NAME varchar(192) NO NULL +INDEX_NAME varchar(192) NO NULL +ROWS_READ bigint(21) NO NULL show columns from information_schema.table_statistics; Field Type Null Key Default Extra -TABLE_SCHEMA varchar(192) NO -TABLE_NAME varchar(192) NO -ROWS_READ bigint(21) NO 0 -ROWS_CHANGED bigint(21) NO 0 -ROWS_CHANGED_X_INDEXES bigint(21) NO 0 +TABLE_SCHEMA varchar(192) NO NULL +TABLE_NAME varchar(192) NO NULL +ROWS_READ bigint(21) NO NULL +ROWS_CHANGED bigint(21) NO NULL +ROWS_CHANGED_X_INDEXES bigint(21) NO NULL set @save_general_log=@@global.general_log; set @@global.general_log=0; set @@global.userstat=1; diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result index b096397f8b2..795231fab97 100644 --- a/mysql-test/main/view.result +++ b/mysql-test/main/view.result @@ -6789,49 +6789,6 @@ sum(z) DROP TABLE t1; DROP VIEW v1; # -# MDEV-24454: Crash at change_item_tree -# -CREATE TABLE t1(f0 INT); -CREATE VIEW v1 AS -SELECT -f0 AS f1 -FROM t1; -CREATE VIEW v2 AS -SELECT -(SELECT GROUP_CONCAT(v1.f1 SEPARATOR ', ') -FROM v1 n) AS f2, -GROUP_CONCAT('' SEPARATOR ', ') AS f3 -FROM v1; -CREATE VIEW v3 AS -SELECT 1 as f4 FROM v2; -CREATE PROCEDURE p1() -SELECT * FROM v3; -CALL p1(); -f4 -1 -CALL p1(); -f4 -1 -drop procedure p1; -drop view v1,v2,v3; -drop table t1; -# -# MDEV-25631: Crash in st_select_lex::mark_as_dependent with -# VIEW, aggregate and subquery -# -CREATE TABLE t1 (i1 int); -insert into t1 values (1),(2),(3); -CREATE VIEW v1 AS -SELECT t1.i1 FROM (t1 a JOIN t1 ON (t1.i1 = (SELECT t1.i1 FROM t1 b))); -SELECT 1 FROM (SELECT count(((SELECT i1 FROM v1))) FROM v1) dt ; -ERROR 21000: Subquery returns more than 1 row -delete from t1 where i1 > 1; -SELECT 1 FROM (SELECT count(((SELECT i1 FROM v1))) FROM v1) dt ; -1 -1 -drop view v1; -drop table t1; -# # MDEV-26299: Some views force server (and mysqldump) to generate # invalid SQL for their definitions # @@ -6843,6 +6800,24 @@ drop view v1; CREATE VIEW v1 AS select `t1`.`12345678901234567890123456789012345678901234567890123456789012345` AS `Name_exp_1` from (select '12345678901234567890123456789012345678901234567890123456789012345') `t1`; drop view v1; # +# MDEV-25631: view with outer reference in select used +# as argument of set function +# +create table t1 (c int); +insert into t1 values (1); +create view v1 as select c from t1 where (select t1.c from t1 t) = 1; +select * from (select sum((select * from v1)) as r) dt; +r +1 +with cte as (select c from t1 where (select t1.c from t1 t) = 1) +select * from (select sum((select * from cte)) as r) dt1 +union +select * from (select sum((select * from cte)) as r) dt2; +r +1 +drop view v1; +drop table t1; +# # End of 10.2 tests # # diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test index 64f65c04dff..6c079c48b00 100644 --- a/mysql-test/main/view.test +++ b/mysql-test/main/view.test @@ -6501,56 +6501,6 @@ DROP TABLE t1; DROP VIEW v1; --echo # ---echo # MDEV-24454: Crash at change_item_tree ---echo # - -CREATE TABLE t1(f0 INT); - -CREATE VIEW v1 AS -SELECT - f0 AS f1 -FROM t1; - -CREATE VIEW v2 AS -SELECT - (SELECT GROUP_CONCAT(v1.f1 SEPARATOR ', ') - FROM v1 n) AS f2, - GROUP_CONCAT('' SEPARATOR ', ') AS f3 -FROM v1; - -CREATE VIEW v3 AS -SELECT 1 as f4 FROM v2; - -CREATE PROCEDURE p1() - SELECT * FROM v3; - -CALL p1(); -CALL p1(); - -drop procedure p1; -drop view v1,v2,v3; -drop table t1; - ---echo # ---echo # MDEV-25631: Crash in st_select_lex::mark_as_dependent with ---echo # VIEW, aggregate and subquery ---echo # - -CREATE TABLE t1 (i1 int); -insert into t1 values (1),(2),(3); #not important -CREATE VIEW v1 AS - SELECT t1.i1 FROM (t1 a JOIN t1 ON (t1.i1 = (SELECT t1.i1 FROM t1 b))); - ---error ER_SUBQUERY_NO_1_ROW -SELECT 1 FROM (SELECT count(((SELECT i1 FROM v1))) FROM v1) dt ; -delete from t1 where i1 > 1; -SELECT 1 FROM (SELECT count(((SELECT i1 FROM v1))) FROM v1) dt ; - -drop view v1; -drop table t1; - - ---echo # --echo # MDEV-26299: Some views force server (and mysqldump) to generate --echo # invalid SQL for their definitions --echo # @@ -6566,9 +6516,29 @@ drop view v1; eval CREATE VIEW v1 AS $definition; + drop view v1; --echo # +--echo # MDEV-25631: view with outer reference in select used +--echo # as argument of set function +--echo # + +create table t1 (c int); +insert into t1 values (1); +create view v1 as select c from t1 where (select t1.c from t1 t) = 1; + +select * from (select sum((select * from v1)) as r) dt; + +with cte as (select c from t1 where (select t1.c from t1 t) = 1) +select * from (select sum((select * from cte)) as r) dt1 +union +select * from (select sum((select * from cte)) as r) dt2; + +drop view v1; +drop table t1; + +--echo # --echo # End of 10.2 tests --echo # |