From 10d2a6f9464b9076db30f96375fd9ca70c0f52eb Mon Sep 17 00:00:00 2001 From: Nikita Malyavin Date: Tue, 3 Sep 2019 21:29:03 +0300 Subject: MDEV-16039 Crash when selecting virtual columns generated using functions with DAYNAME() Due to collation specifics an Item tree could change while fixing. Virtual fields. 1. This should be done on the correct query arena, which is table->expr_arena. 2. Doing it once per table open is enough. Furthermore, fixing virtual exprs after open sets the collation to the one definde by `SET NAMES`, which is incorrect: only the native table collation should be used, and it shouldn't be changed after the table is created. --- mysql-test/suite/gcol/r/gcol_bugfixes.result | 80 +++++++++++++++++++++++++ mysql-test/suite/gcol/t/gcol_bugfixes.test | 87 ++++++++++++++++++++++++++++ sql/sql_base.cc | 43 +------------- sql/table.cc | 13 +++++ 4 files changed, 181 insertions(+), 42 deletions(-) diff --git a/mysql-test/suite/gcol/r/gcol_bugfixes.result b/mysql-test/suite/gcol/r/gcol_bugfixes.result index 3a859116cab..12acb0a9926 100644 --- a/mysql-test/suite/gcol/r/gcol_bugfixes.result +++ b/mysql-test/suite/gcol/r/gcol_bugfixes.result @@ -604,3 +604,83 @@ test gcol_t1 sidea NEVER NULL test gcol_t1 sideb NEVER NULL test gcol_t1 sidec VIRTUAL GENERATED ALWAYS sqrt(`sidea` * `sidea` + `sideb` * `sideb`) DROP TABLE gcol_t1; +# +# MDEV-16039 Crash when selecting virtual columns +# generated using functions with DAYNAME() +# +CREATE TABLE t1 ( +suppliersenttoday INT NOT NULL, +suppliercaptoday CHAR(10) AS (CONCAT('',DAYNAME('2018-04-26'))) +) COLLATE utf8_bin; +INSERT INTO t1 (suppliersenttoday) VALUES (0); +INSERT INTO t1 (suppliersenttoday) VALUES (0); +SELECT * FROM t1; +suppliersenttoday suppliercaptoday +0 Thursday +0 Thursday +PREPARE STMT FROM 'INSERT INTO t1 (suppliersenttoday) VALUES (1)'; +CREATE OR REPLACE TABLE t1 ( +suppliersenttoday INT NOT NULL, +suppliercaptoday CHAR(10) AS (CONCAT('',DAYNAME('2018-04-26'))) +) COLLATE utf8_bin; +EXECUTE STMT; +EXECUTE STMT; +SELECT * FROM t1; +suppliersenttoday suppliercaptoday +1 Thursday +1 Thursday +DROP TABLE t1; +# (duplicate) MDEV-20380 Server crash during update +CREATE TABLE gafld ( +nuigafld INTEGER NOT NULL, +ucrgafld VARCHAR(30) COLLATE UTF8_BIN NOT NULL +DEFAULT SUBSTRING_INDEX(USER(),'@',1) +); +EXPLAIN UPDATE gafld SET nuigafld = 0 WHERE nuigafld = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE gafld ALL NULL NULL NULL NULL 1 Using where +EXPLAIN UPDATE gafld SET nuigafld = 0 WHERE nuigafld = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE gafld ALL NULL NULL NULL NULL 1 Using where +DROP TABLE gafld; +# (duplicate) MDEV-17653 replace into generated columns is unstable +# Some columns are snipped from the MDEV test and variable is changed +CREATE TABLE t ( +c0 TIMESTAMP NOT NULL DEFAULT current_timestamp() +ON UPDATE current_timestamp(), +c1 DECIMAL(27,25) GENERATED ALWAYS AS (DAYOFMONTH(@timestamp)), +c4 TIME NOT NULL, +c8 SMALLINT(6) GENERATED ALWAYS AS +(CONCAT_WS(CONVERT(C1 USING CP932), +GET_FORMAT(DATETIME, 'ISO'), +@@GLOBAL.FLUSH_TIME) <> (c4 = 1)), +PRIMARY KEY (c4) +) DEFAULT CHARSET=latin1; +REPLACE INTO t SET c0 = '2018-06-03 10:31:43', c4 = '02:58:55'; +REPLACE INTO t SET c0 = '2018-06-03 10:31:44', c4 = '02:58:55'; +REPLACE INTO t SET c0 = '2018-06-03 10:31:45', c4 = '02:58:55'; +DROP TABLE t; +# (duplicate) MDEV-17986 crash when I insert on a table +CREATE OR REPLACE TABLE t2 ( +number BIGINT(20) NOT NULL, +lrn BIGINT(20) NOT NULL DEFAULT 0, +source VARCHAR(15) NOT NULL +DEFAULT (REVERSE(SUBSTRING_INDEX(REVERSE(user()), '@', 1))), +PRIMARY KEY (number) +); +REPLACE t2(number) VALUES('1'); +REPLACE t2(number) VALUES('1'); +DROP TABLE t2; +# (duplicate) MDEV-16048 crashing with virtual columns +SET @save_old_sql_mode= @@sql_mode; +SET sql_mode=''; +DROP TABLE IF EXISTS t; +Warnings: +Note 1051 Unknown table 'test.t' +CREATE TABLE t ( +A INT AS (UPDATEXML((@@global.date_format), 1, SUBSTRING_INDEX(1, 2, 3))) +) ENGINE=INNODB; +INSERT INTO t VALUES(); +INSERT IGNORE INTO t VALUES(); +DROP TABLE t; +SET sql_mode= @save_old_sql_mode; diff --git a/mysql-test/suite/gcol/t/gcol_bugfixes.test b/mysql-test/suite/gcol/t/gcol_bugfixes.test index 5563347a02a..8df41b4fa2f 100644 --- a/mysql-test/suite/gcol/t/gcol_bugfixes.test +++ b/mysql-test/suite/gcol/t/gcol_bugfixes.test @@ -564,3 +564,90 @@ SELECT table_schema,table_name,column_name,extra,is_generated,generation_express FROM information_schema.columns WHERE table_name='gcol_t1'; DROP TABLE gcol_t1; + +--echo # +--echo # MDEV-16039 Crash when selecting virtual columns +--echo # generated using functions with DAYNAME() +--echo # + +CREATE TABLE t1 ( + suppliersenttoday INT NOT NULL, + suppliercaptoday CHAR(10) AS (CONCAT('',DAYNAME('2018-04-26'))) +) COLLATE utf8_bin; + +INSERT INTO t1 (suppliersenttoday) VALUES (0); +INSERT INTO t1 (suppliersenttoday) VALUES (0); +SELECT * FROM t1; + +PREPARE STMT FROM 'INSERT INTO t1 (suppliersenttoday) VALUES (1)'; + +CREATE OR REPLACE TABLE t1 ( + suppliersenttoday INT NOT NULL, + suppliercaptoday CHAR(10) AS (CONCAT('',DAYNAME('2018-04-26'))) +) COLLATE utf8_bin; + +EXECUTE STMT; +EXECUTE STMT; +SELECT * FROM t1; + +DROP TABLE t1; + +--echo # (duplicate) MDEV-20380 Server crash during update +CREATE TABLE gafld ( + nuigafld INTEGER NOT NULL, + ucrgafld VARCHAR(30) COLLATE UTF8_BIN NOT NULL + DEFAULT SUBSTRING_INDEX(USER(),'@',1) +); +EXPLAIN UPDATE gafld SET nuigafld = 0 WHERE nuigafld = 10; +EXPLAIN UPDATE gafld SET nuigafld = 0 WHERE nuigafld = 10; +DROP TABLE gafld; + +--echo # (duplicate) MDEV-17653 replace into generated columns is unstable +--echo # Some columns are snipped from the MDEV test and variable is changed +CREATE TABLE t ( + c0 TIMESTAMP NOT NULL DEFAULT current_timestamp() + ON UPDATE current_timestamp(), + c1 DECIMAL(27,25) GENERATED ALWAYS AS (DAYOFMONTH(@timestamp)), + c4 TIME NOT NULL, + c8 SMALLINT(6) GENERATED ALWAYS AS + (CONCAT_WS(CONVERT(C1 USING CP932), + GET_FORMAT(DATETIME, 'ISO'), + @@GLOBAL.FLUSH_TIME) <> (c4 = 1)), + PRIMARY KEY (c4) +) DEFAULT CHARSET=latin1; + +REPLACE INTO t SET c0 = '2018-06-03 10:31:43', c4 = '02:58:55'; +REPLACE INTO t SET c0 = '2018-06-03 10:31:44', c4 = '02:58:55'; +REPLACE INTO t SET c0 = '2018-06-03 10:31:45', c4 = '02:58:55'; + +DROP TABLE t; + +--echo # (duplicate) MDEV-17986 crash when I insert on a table +CREATE OR REPLACE TABLE t2 ( + number BIGINT(20) NOT NULL, + lrn BIGINT(20) NOT NULL DEFAULT 0, + source VARCHAR(15) NOT NULL + DEFAULT (REVERSE(SUBSTRING_INDEX(REVERSE(user()), '@', 1))), + PRIMARY KEY (number) +); + +REPLACE t2(number) VALUES('1'); +REPLACE t2(number) VALUES('1'); + +DROP TABLE t2; + + +--echo # (duplicate) MDEV-16048 crashing with virtual columns +SET @save_old_sql_mode= @@sql_mode; +SET sql_mode=''; + +DROP TABLE IF EXISTS t; +CREATE TABLE t ( + A INT AS (UPDATEXML((@@global.date_format), 1, SUBSTRING_INDEX(1, 2, 3))) +) ENGINE=INNODB; +INSERT INTO t VALUES(); +INSERT IGNORE INTO t VALUES(); + +DROP TABLE t; +SET sql_mode= @save_old_sql_mode; + diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 7ac0dcad596..ff9575bb478 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -5388,45 +5388,6 @@ static void mark_real_tables_as_free_for_reuse(TABLE_LIST *table_list) } -static bool fix_all_session_vcol_exprs(THD *thd, TABLE_LIST *tables) -{ - Security_context *save_security_ctx= thd->security_ctx; - TABLE_LIST *first_not_own= thd->lex->first_not_own_table(); - DBUG_ENTER("fix_session_vcol_expr"); - - for (TABLE_LIST *table= tables; table && table != first_not_own; - table= table->next_global) - { - TABLE *t= table->table; - if (!table->placeholder() && t->s->vcols_need_refixing && - table->lock_type >= TL_WRITE_ALLOW_WRITE) - { - if (table->security_ctx) - thd->security_ctx= table->security_ctx; - - for (Field **vf= t->vfield; vf && *vf; vf++) - if (fix_session_vcol_expr(thd, (*vf)->vcol_info)) - goto err; - - for (Field **df= t->default_field; df && *df; df++) - if ((*df)->default_value && - fix_session_vcol_expr(thd, (*df)->default_value)) - goto err; - - for (Virtual_column_info **cc= t->check_constraints; cc && *cc; cc++) - if (fix_session_vcol_expr(thd, (*cc))) - goto err; - - thd->security_ctx= save_security_ctx; - } - } - DBUG_RETURN(0); -err: - thd->security_ctx= save_security_ctx; - DBUG_RETURN(1); -} - - /** Lock all tables in a list. @@ -5587,9 +5548,7 @@ bool lock_tables(THD *thd, TABLE_LIST *tables, uint count, uint flags) } } - bool res= fix_all_session_vcol_exprs(thd, tables); - if (!res) - res= thd->decide_logging_format(tables); + bool res= thd->decide_logging_format(tables); DBUG_RETURN(res); } diff --git a/sql/table.cc b/sql/table.cc index 3b3a1fa6fb0..7a546412abc 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -1263,6 +1263,19 @@ bool parse_vcol_defs(THD *thd, MEM_ROOT *mem_root, TABLE *table, goto end; } + for (Field **vf= table->vfield; vf && *vf; vf++) + if (fix_session_vcol_expr(thd, (*vf)->vcol_info)) + goto end; + + for (Field **df= table->default_field; df && *df; df++) + if ((*df)->default_value && + fix_session_vcol_expr(thd, (*df)->default_value)) + goto end; + + for (Virtual_column_info **cc= table->check_constraints; cc && *cc; cc++) + if (fix_session_vcol_expr(thd, (*cc))) + goto end; + table->find_constraint_correlated_indexes(); res=0; -- cgit v1.2.1