bugfix: non-deterministic vcols in indexes

4 files changed, 73 insertions, 9 deletions

diff --git a/mysql-test/suite/vcol/inc/vcol_keys.inc b/mysql-test/suite/vcol/inc/vcol_keys.inc
index 95fd8a6488d..42510588f34 100644
--- a/mysql-test/suite/vcol/inc/vcol_keys.inc
+++ b/mysql-test/suite/vcol/inc/vcol_keys.inc
@@ -157,6 +157,27 @@ drop table t1;
 --echo #  - vcol_ins_upd.inc
 --echo #  - vcol_select.inc
 
---echo #
---echo # TODO: CHECK
+#
+# Restrictions when indexed:
+#
+
+--error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create table t1 (a int, b timestamp as (now()), key (b));
+create table t1 (a int, b timestamp as (now()));
+--error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+alter table t1 add index (b);
+drop table t1;
+
+--error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create table t1 (a int, b varchar(100) as (user()), key (b));
+create table t1 (a int, b varchar(100) as (user()));
+--error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+alter table t1 add index (b);
+drop table t1;
 
+--error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+create table t1 (a int, b double as (rand()), key (b));
+create table t1 (a int, b double as (rand()));
+--error ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED
+alter table t1 add index (b);
+drop table t1;
diff --git a/mysql-test/suite/vcol/r/vcol_keys_innodb.result b/mysql-test/suite/vcol/r/vcol_keys_innodb.result
index 843bd6efc8e..c9c95e912df 100644
--- a/mysql-test/suite/vcol/r/vcol_keys_innodb.result
+++ b/mysql-test/suite/vcol/r/vcol_keys_innodb.result
@@ -150,5 +150,21 @@ drop table t1;
 # on virtual columns can be found in:
 #  - vcol_ins_upd.inc
 #  - vcol_select.inc
-#
-# TODO: CHECK
+create table t1 (a int, b timestamp as (now()), key (b));
+ERROR HY000: Function or expression 'current_timestamp()' cannot be used in the GENERATED ALWAYS AS clause of `b`
+create table t1 (a int, b timestamp as (now()));
+alter table t1 add index (b);
+ERROR HY000: Function or expression 'current_timestamp()' cannot be used in the GENERATED ALWAYS AS clause of `b`
+drop table t1;
+create table t1 (a int, b varchar(100) as (user()), key (b));
+ERROR HY000: Function or expression 'user()' cannot be used in the GENERATED ALWAYS AS clause of `b`
+create table t1 (a int, b varchar(100) as (user()));
+alter table t1 add index (b);
+ERROR HY000: Function or expression 'user()' cannot be used in the GENERATED ALWAYS AS clause of `b`
+drop table t1;
+create table t1 (a int, b double as (rand()), key (b));
+ERROR HY000: Function or expression 'rand()' cannot be used in the GENERATED ALWAYS AS clause of `b`
+create table t1 (a int, b double as (rand()));
+alter table t1 add index (b);
+ERROR HY000: Function or expression 'rand()' cannot be used in the GENERATED ALWAYS AS clause of `b`
+drop table t1;
diff --git a/mysql-test/suite/vcol/r/vcol_keys_myisam.result b/mysql-test/suite/vcol/r/vcol_keys_myisam.result
index 2a551956173..17f6fba7e89 100644
--- a/mysql-test/suite/vcol/r/vcol_keys_myisam.result
+++ b/mysql-test/suite/vcol/r/vcol_keys_myisam.result
@@ -260,5 +260,21 @@ drop table t1;
 # on virtual columns can be found in:
 #  - vcol_ins_upd.inc
 #  - vcol_select.inc
-#
-# TODO: CHECK
+create table t1 (a int, b timestamp as (now()), key (b));
+ERROR HY000: Function or expression 'current_timestamp()' cannot be used in the GENERATED ALWAYS AS clause of `b`
+create table t1 (a int, b timestamp as (now()));
+alter table t1 add index (b);
+ERROR HY000: Function or expression 'current_timestamp()' cannot be used in the GENERATED ALWAYS AS clause of `b`
+drop table t1;
+create table t1 (a int, b varchar(100) as (user()), key (b));
+ERROR HY000: Function or expression 'user()' cannot be used in the GENERATED ALWAYS AS clause of `b`
+create table t1 (a int, b varchar(100) as (user()));
+alter table t1 add index (b);
+ERROR HY000: Function or expression 'user()' cannot be used in the GENERATED ALWAYS AS clause of `b`
+drop table t1;
+create table t1 (a int, b double as (rand()), key (b));
+ERROR HY000: Function or expression 'rand()' cannot be used in the GENERATED ALWAYS AS clause of `b`
+create table t1 (a int, b double as (rand()));
+alter table t1 add index (b);
+ERROR HY000: Function or expression 'rand()' cannot be used in the GENERATED ALWAYS AS clause of `b`
+drop table t1;
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 5ae8fd8270b..11abeac90d0 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -3877,10 +3877,21 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
 	  }
 	}
 #endif
-        if (key->type == Key::PRIMARY && sql_field->vcol_info)
+        if (sql_field->vcol_info)
         {
-          my_error(ER_PRIMARY_KEY_BASED_ON_VIRTUAL_COLUMN, MYF(0));
-          DBUG_RETURN(TRUE);
+          if (key->type == Key::PRIMARY)
+          {
+            my_error(ER_PRIMARY_KEY_BASED_ON_VIRTUAL_COLUMN, MYF(0));
+            DBUG_RETURN(TRUE);
+          }
+          if (sql_field->vcol_info->flags & VCOL_NOT_STRICTLY_DETERMINISTIC)
+          {
+            /* use check_expression() to report an error */
+            check_expression(sql_field->vcol_info, sql_field->field_name,
+                             VCOL_GENERATED_STORED);
+            DBUG_ASSERT(thd->is_error());
+            DBUG_RETURN(TRUE);
+          }
         }
 	if (!(sql_field->flags & NOT_NULL_FLAG))
 	{