From 3df297271a02ef13babae6ff6a7e47a6bdb7d538 Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Fri, 15 May 2020 20:16:58 +0400 Subject: MDEV-22579 No error when inserting DEFAULT(non_virtual_column) into a virtual column The code erroneously allowed both: INSERT INTO t1 (vcol) VALUES (DEFAULT); INSERT INTO t1 (vcol) VALUES (DEFAULT(non_virtual_column)); The former is OK, but the latter is not. Adding a new virtual method in Item: virtual bool vcol_assignment_allowed_value() const { return false; } Item_null, Item_param and Item_default_value override it. Item_default_value overrides it in the way to: - allow DEFAULT - disallow DEFAULT(col) --- mysql-test/suite/vcol/r/vcol_misc.result | 20 ++++++++++++++++++++ mysql-test/suite/vcol/t/vcol_misc.test | 30 ++++++++++++++++++++++++++++++ sql/item.h | 11 +++++++++++ sql/sql_base.cc | 6 ++---- 4 files changed, 63 insertions(+), 4 deletions(-) diff --git a/mysql-test/suite/vcol/r/vcol_misc.result b/mysql-test/suite/vcol/r/vcol_misc.result index 5e84a314b38..abbd73cead6 100644 --- a/mysql-test/suite/vcol/r/vcol_misc.result +++ b/mysql-test/suite/vcol/r/vcol_misc.result @@ -409,5 +409,25 @@ aaa Warnings: Warning 1918 Encountered illegal value '\xF0\xF1\xF2\xF3\xF4\xF5\xF6\xF7' when converting to DOUBLE # +# MDEV-22579 No error when inserting DEFAULT(non_virtual_column) into a virtual column +# +SET sql_mode=STRICT_ALL_TABLES; +CREATE OR REPLACE TABLE t1 ( +a INT NOT NULL DEFAULT 10, +b INT AS (a+1) VIRTUAL +) ENGINE=MyISAM; +INSERT INTO t1 (b) VALUES (10); +ERROR HY000: The value specified for computed column 'b' in table 't1' has been ignored +INSERT INTO t1 (b) VALUES (DEFAULT(a)); +ERROR HY000: The value specified for computed column 'b' in table 't1' has been ignored +INSERT INTO t1 (b) VALUES (DEFAULT); +INSERT INTO t1 VALUES (10,10); +ERROR HY000: The value specified for computed column 'b' in table 't1' has been ignored +INSERT INTO t1 VALUES (10,DEFAULT(a)); +ERROR HY000: The value specified for computed column 'b' in table 't1' has been ignored +INSERT INTO t1 VALUES (10, DEFAULT); +DROP TABLE t1; +SET sql_mode=DEFAULT; +# # End of 10.1 tests # diff --git a/mysql-test/suite/vcol/t/vcol_misc.test b/mysql-test/suite/vcol/t/vcol_misc.test index d753f4c09cf..9440cc533f8 100644 --- a/mysql-test/suite/vcol/t/vcol_misc.test +++ b/mysql-test/suite/vcol/t/vcol_misc.test @@ -365,6 +365,36 @@ SELECT COLUMN_GET(@aaa, 'price' AS DECIMAL) aaa; SELECT COLUMN_GET(@aaa, 'price' AS INT) aaa; SELECT COLUMN_GET(@aaa, 'price' AS DOUBLE) aaa; + +--echo # +--echo # MDEV-22579 No error when inserting DEFAULT(non_virtual_column) into a virtual column +--echo # + +SET sql_mode=STRICT_ALL_TABLES; +CREATE OR REPLACE TABLE t1 ( + a INT NOT NULL DEFAULT 10, + b INT AS (a+1) VIRTUAL +) ENGINE=MyISAM; + +# Testing with a column list + +--error ER_WARNING_NON_DEFAULT_VALUE_FOR_VIRTUAL_COLUMN +INSERT INTO t1 (b) VALUES (10); +--error ER_WARNING_NON_DEFAULT_VALUE_FOR_VIRTUAL_COLUMN +INSERT INTO t1 (b) VALUES (DEFAULT(a)); +INSERT INTO t1 (b) VALUES (DEFAULT); + +# Testing without a column list +--error ER_WARNING_NON_DEFAULT_VALUE_FOR_VIRTUAL_COLUMN +INSERT INTO t1 VALUES (10,10); +--error ER_WARNING_NON_DEFAULT_VALUE_FOR_VIRTUAL_COLUMN +INSERT INTO t1 VALUES (10,DEFAULT(a)); +INSERT INTO t1 VALUES (10, DEFAULT); + +DROP TABLE t1; +SET sql_mode=DEFAULT; + + --echo # --echo # End of 10.1 tests --echo # diff --git a/sql/item.h b/sql/item.h index 8a90d99db9d..bf1087e7e6e 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1128,6 +1128,13 @@ public: a constant expression. Used in the optimizer to propagate basic constants. */ virtual bool basic_const_item() const { return 0; } + /* + Determines if the expression is allowed as + a virtual column assignment source: + INSERT INTO t1 (vcol) VALUES (10) -> error + INSERT INTO t1 (vcol) VALUES (NULL) -> ok + */ + virtual bool vcol_assignment_allowed_value() const { return false; } /* cloning of constant items (0 if it is not const) */ virtual Item *clone_item(THD *thd) { return 0; } virtual cond_result eq_cmp_result() const { return COND_OK; } @@ -2626,6 +2633,7 @@ public: collation.set(cs, DERIVATION_IGNORABLE, MY_REPERTOIRE_ASCII); } enum Type type() const { return NULL_ITEM; } + bool vcol_assignment_allowed_value() const { return true; } bool eq(const Item *item, bool binary_cmp) const { return null_eq(item); } double val_real(); longlong val_int(); @@ -2699,6 +2707,8 @@ public: DECIMAL_VALUE } state; + bool vcol_assignment_allowed_value() const { return state == NULL_VALUE; } + /* A buffer for string and long data values. Historically all allocated values returned from val_str() were treated as eligible to @@ -4837,6 +4847,7 @@ public: (const char *)NULL), arg(a) {} enum Type type() const { return DEFAULT_VALUE_ITEM; } + bool vcol_assignment_allowed_value() const { return arg == NULL; } bool eq(const Item *item, bool binary_cmp) const; bool fix_fields(THD *, Item **); virtual void print(String *str, enum_query_type query_type); diff --git a/sql/sql_base.cc b/sql/sql_base.cc index c092faa986b..3533c241fbc 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -8889,8 +8889,7 @@ fill_record(THD *thd, TABLE *table_arg, List &fields, List &values, rfield->field_index == table->next_number_field->field_index) table->auto_increment_field_not_null= TRUE; if (rfield->vcol_info && - value->type() != Item::DEFAULT_VALUE_ITEM && - value->type() != Item::NULL_ITEM && + !value->vcol_assignment_allowed_value() && table->s->table_category != TABLE_CATEGORY_TEMPORARY) { push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, @@ -9098,8 +9097,7 @@ fill_record(THD *thd, TABLE *table, Field **ptr, List &values, if (field->field_index == autoinc_index) table->auto_increment_field_not_null= TRUE; if (field->vcol_info && - value->type() != Item::DEFAULT_VALUE_ITEM && - value->type() != Item::NULL_ITEM && + !value->vcol_assignment_allowed_value() && table->s->table_category != TABLE_CATEGORY_TEMPORARY) { push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, -- cgit v1.2.1