MDEV 13679 Enabled sequences to be used in DEFAULT

Other changes done to get this to work:
- Added 'internal_tables' to TABLE object to list which sequence tables
  is needed to use the table.
- Mark any expression using DEFAULT() with LEX->default_used.
  This is needed when deciding if we should open internal sequence
  tables when a table is opened (we don't need to open sequence tables
  if the main table is only used with SELECT).
- Create_and_open_temporary_table() can now also open all internal
  sequence tables.
- Added option MYSQL_LOCK_USE_MALLOC to mysql_lock_tables()
  to force memory allocation to be used with malloc instead of
  memroot.
- Added flag to MYSQL_LOCK to remember if allocation was done with
  malloc or memroot (makes code simpler and safer).
- init_one_table_for_prelocking() now takes argument for what lock to
  use instead of it's a routine or something else.
- Renamed prelocking placeholders to make them more understandable as
  they are now used in more code.
- Changed test in check_lock_and_start_stmt() if found table has correct
  locks. The old test didn't work for tables that has lock
  TL_WRITE_ALLOW_WRITE, which is what sequence tables are using.
- Added VCOL_NOT_VIRTUAL option to ensure that sequence functions can't
  be used with virtual columns
- More sequence tests

22 files changed, 591 insertions, 51 deletions

diff --git a/mysql-test/suite/sql_sequence/default.result b/mysql-test/suite/sql_sequence/default.result
new file mode 100644
index 00000000000..3c22ea296ca
--- /dev/null
+++ b/mysql-test/suite/sql_sequence/default.result
@@ -0,0 +1,171 @@
+drop table if exists t1,s1,s2;
+Warnings:
+Note	1051	Unknown table 'test.t1'
+Note	1051	Unknown table 'test.s1'
+Note	1051	Unknown table 'test.s2'
+drop view if exists v1;
+Warnings:
+Note	4092	Unknown VIEW: 'test.v1'
+#
+# Test DEFAULT
+#
+CREATE SEQUENCE s1 nocache engine=myisam;
+CREATE table t1 (a int default next value for s1, b int);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT nextval(`test`.`s1`),
+  `b` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert into t1 SET b=1;
+insert into t1 SET b=2;
+insert into t1 (b) values (3),(4);
+select * from t1;
+a	b
+1	1
+2	2
+3	3
+4	4
+update t1 set b=5 where a=1;
+delete from t1 where b=1;
+select * from t1;
+a	b
+1	5
+2	2
+3	3
+4	4
+#
+# Executing DEFAULT function
+#
+INSERT into t1 values(default(a),10);
+INSERT into t1 values(default(a),default(a));
+update t1 set a=default(a), b=12 where b=2;
+select * from t1;
+a	b
+1	5
+8	12
+3	3
+4	4
+5	10
+6	7
+select default(a), a, b from t1;
+default(a)	a	b
+9	1	5
+10	8	12
+11	3	3
+12	4	4
+13	5	10
+14	6	7
+select * from s1;
+next_not_cached_value	minimum_value	maximum_value	start_value	increment	cache_size	cycle_option	cycle_count
+15	1	9223372036854775806	1	1	0	0	0
+select * from t1 where default(a) > 0;
+a	b
+1	5
+8	12
+3	3
+4	4
+5	10
+6	7
+select * from s1;
+next_not_cached_value	minimum_value	maximum_value	start_value	increment	cache_size	cycle_option	cycle_count
+16	1	9223372036854775806	1	1	0	0	0
+#
+# View
+#
+create view v1 as select * from t1;
+insert into v1 set b=20;
+select * from v1;
+a	b
+1	5
+8	12
+3	3
+4	4
+5	10
+6	7
+16	20
+drop view v1;
+#
+# Alter table
+#
+CREATE SEQUENCE s2 nocache engine=myisam;
+alter table t1 add column c int default next value for s2, add column d int default previous value for s2;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT nextval(`test`.`s1`),
+  `b` int(11) DEFAULT NULL,
+  `c` int(11) DEFAULT nextval(`test`.`s2`),
+  `d` int(11) DEFAULT lastval(`test`.`s2`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+select * from t1;
+a	b	c	d
+1	5	1	1
+8	12	2	2
+3	3	3	3
+4	4	4	4
+5	10	5	5
+6	7	6	6
+16	20	7	7
+drop sequence s2;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT nextval(`test`.`s1`),
+  `b` int(11) DEFAULT NULL,
+  `c` int(11) DEFAULT nextval(`test`.`s2`),
+  `d` int(11) DEFAULT lastval(`test`.`s2`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
+drop sequence s1;
+#
+# LOCK TABLES
+#
+CREATE SEQUENCE s1 nocache engine=myisam;
+CREATE table t1 (a int default next value for s1, b int);
+insert into t1 (b) values (3),(4);
+LOCK TABLE t1 WRITE;
+insert into t1 (b) values (5),(6);
+ERROR HY000: Table 's1' was not locked with LOCK TABLES
+UNLOCK TABLES;
+LOCK TABLE t1 WRITE, s1 WRITE;
+insert into t1 (b) values (5),(6);
+select default(a) from t1;
+default(a)
+5
+6
+7
+8
+UNLOCK TABLES;
+LOCK TABLE t1 READ;
+insert into t1 (b) values (5),(6);
+ERROR HY000: Table 's1' was not locked with LOCK TABLES
+select default(a) from t1;
+ERROR HY000: Table 's1' was not locked with LOCK TABLES
+UNLOCK TABLES;
+LOCK TABLE t1 READ, s1 read;
+insert into t1 (b) values (5),(6);
+ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
+select default(a) from t1;
+ERROR HY000: Table 's1' was locked with a READ lock and can't be updated
+UNLOCK TABLES;
+drop table t1;
+drop sequence s1;
+#
+# Wrong usage of default
+#
+CREATE table t1 (a int default next value for s1, b int);
+ERROR 42S02: Table 'test.s1' doesn't exist
+CREATE SEQUENCE s1 nocache engine=myisam;
+CREATE table t1 (a int default next value for s1, b int);
+DROP SEQUENCE s1;
+insert into t1 (b) values (5),(6);
+ERROR 42S02: Table 'test.s1' doesn't exist
+ALTER TABLE t1 add column c int;
+ERROR 42S02: Table 'test.s1' doesn't exist
+CREATE SEQUENCE s1 nocache engine=myisam;
+ALTER TABLE t1 add column c int;
+ALTER TABLE t1 add column d int default next value for s_not_exits;
+ERROR 42S02: Table 'test.s_not_exits' doesn't exist
+drop table t1;
+drop sequence s1;
diff --git a/mysql-test/suite/sql_sequence/default.test b/mysql-test/suite/sql_sequence/default.test
new file mode 100644
index 00000000000..2c527246eff
--- /dev/null
+++ b/mysql-test/suite/sql_sequence/default.test
@@ -0,0 +1,111 @@
+#
+# Testing sequence in DEFAULT clause
+#
+--source include/have_sequence.inc
+
+drop table if exists t1,s1,s2;
+drop view if exists v1;
+
+--echo #
+--echo # Test DEFAULT
+--echo #
+
+CREATE SEQUENCE s1 nocache engine=myisam;
+CREATE table t1 (a int default next value for s1, b int);
+show create table t1;
+insert into t1 SET b=1;
+insert into t1 SET b=2;
+insert into t1 (b) values (3),(4);
+select * from t1;
+update t1 set b=5 where a=1;
+delete from t1 where b=1;
+select * from t1;
+
+--echo #
+--echo # Executing DEFAULT function
+--echo #
+
+INSERT into t1 values(default(a),10);
+INSERT into t1 values(default(a),default(a));
+update t1 set a=default(a), b=12 where b=2;
+select * from t1;
+select default(a), a, b from t1;
+select * from s1;
+select * from t1 where default(a) > 0;
+select * from s1;
+
+--echo #
+--echo # View
+--echo #
+
+create view v1 as select * from t1;
+insert into v1 set b=20;
+select * from v1;
+drop view v1;
+
+--echo #
+--echo # Alter table
+--echo #
+
+CREATE SEQUENCE s2 nocache engine=myisam;
+alter table t1 add column c int default next value for s2, add column d int default previous value for s2;
+show create table t1;
+select * from t1;
+drop sequence s2;
+show create table t1;
+drop table t1;
+drop sequence s1;
+
+--echo #
+--echo # LOCK TABLES
+--echo #
+
+CREATE SEQUENCE s1 nocache engine=myisam;
+CREATE table t1 (a int default next value for s1, b int);
+insert into t1 (b) values (3),(4);
+LOCK TABLE t1 WRITE;
+--error ER_TABLE_NOT_LOCKED
+insert into t1 (b) values (5),(6);
+UNLOCK TABLES;
+
+LOCK TABLE t1 WRITE, s1 WRITE;
+insert into t1 (b) values (5),(6);
+select default(a) from t1;
+UNLOCK TABLES;
+
+LOCK TABLE t1 READ;
+--error ER_TABLE_NOT_LOCKED
+insert into t1 (b) values (5),(6);
+--error ER_TABLE_NOT_LOCKED
+select default(a) from t1;
+UNLOCK TABLES;
+
+LOCK TABLE t1 READ, s1 read;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+insert into t1 (b) values (5),(6);
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+select default(a) from t1;
+UNLOCK TABLES;
+
+drop table t1;
+drop sequence s1;
+
+--echo #
+--echo # Wrong usage of default
+--echo #
+
+--error ER_NO_SUCH_TABLE
+CREATE table t1 (a int default next value for s1, b int);
+CREATE SEQUENCE s1 nocache engine=myisam;
+CREATE table t1 (a int default next value for s1, b int);
+DROP SEQUENCE s1;
+--error ER_NO_SUCH_TABLE
+insert into t1 (b) values (5),(6);
+--error ER_NO_SUCH_TABLE
+ALTER TABLE t1 add column c int;
+CREATE SEQUENCE s1 nocache engine=myisam;
+ALTER TABLE t1 add column c int;
+--error ER_NO_SUCH_TABLE
+ALTER TABLE t1 add column d int default next value for s_not_exits;
+drop table t1;
+drop sequence s1;
diff --git a/mysql-test/suite/sql_sequence/other.result b/mysql-test/suite/sql_sequence/other.result
index 7a81b7f4729..a36a2585b8a 100644
--- a/mysql-test/suite/sql_sequence/other.result
+++ b/mysql-test/suite/sql_sequence/other.result
@@ -171,3 +171,16 @@ CREATE SEQUENCE s1;
 ALTER SEQUENCE s1 MAXVALUE 100 NO MAXVALUE;
 ERROR HY000: Option 'MAXVALUE' used twice in statement
 DROP SEQUENCE s1;
+#
+# Don't allow SEQUENCE to be used with CHECK or virtual fields
+#
+CREATE SEQUENCE s1 nocache engine=myisam;
+CREATE table t1 (a int check (next value for s1 > 0));
+ERROR HY000: Function or expression 'nextval()' cannot be used in the CHECK clause of `a`
+CREATE table t1 (a int check (previous value for s1 > 0));
+ERROR HY000: Function or expression 'lastval()' cannot be used in the CHECK clause of `a`
+CREATE table t1 (a int check (setval(s1,10)));
+ERROR HY000: Function or expression 'setval()' cannot be used in the CHECK clause of `a`
+CREATE TABLE t1 (a int, b int as (next value for s1 > 0));
+ERROR HY000: Function or expression 'nextval()' cannot be used in the GENERATED ALWAYS AS clause of `b`
+drop sequence s1;
diff --git a/mysql-test/suite/sql_sequence/other.test b/mysql-test/suite/sql_sequence/other.test
index 8e62479d86a..7d91bd45027 100644
--- a/mysql-test/suite/sql_sequence/other.test
+++ b/mysql-test/suite/sql_sequence/other.test
@@ -142,3 +142,18 @@ CREATE SEQUENCE s1;
 --error ER_DUP_ARGUMENT
 ALTER SEQUENCE s1 MAXVALUE 100 NO MAXVALUE;
 DROP SEQUENCE s1;
+
+--echo #
+--echo # Don't allow SEQUENCE to be used with CHECK or virtual fields
+--echo #
+
+CREATE SEQUENCE s1 nocache engine=myisam;
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+CREATE table t1 (a int check (next value for s1 > 0));
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+CREATE table t1 (a int check (previous value for s1 > 0));
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+CREATE table t1 (a int check (setval(s1,10)));
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+CREATE TABLE t1 (a int, b int as (next value for s1 > 0));
+drop sequence s1;
diff --git a/mysql-test/suite/sql_sequence/replication.result b/mysql-test/suite/sql_sequence/replication.result
index 5c7d45614e9..83946b07419 100644
--- a/mysql-test/suite/sql_sequence/replication.result
+++ b/mysql-test/suite/sql_sequence/replication.result
@@ -1058,6 +1058,35 @@ select next value for s1, cycle_count from s1;
 next value for s1	cycle_count
 9223372036854775805	3
 drop sequence s1;
+###########################################
+test default()
+###########################################
+connection master;
+CREATE SEQUENCE s1 nocache engine=myisam;
+CREATE table t1 (a int default next value for s1, b int);
+insert into t1 (b) values (1),(2);
+select default(a) from t1;
+default(a)
+3
+4
+select * from t1;
+a	b
+1	1
+2	2
+select * from s1;
+next_not_cached_value	minimum_value	maximum_value	start_value	increment	cache_size	cycle_option	cycle_count
+5	1	9223372036854775806	1	1	0	0	0
+connection slave;
+connection s_normal_3;
+select * from t1;
+a	b
+1	1
+2	2
+select * from s1;
+next_not_cached_value	minimum_value	maximum_value	start_value	increment	cache_size	cycle_option	cycle_count
+5	1	9223372036854775806	1	1	0	0	0
+connection master;
+drop table t1,s1;
 connection master;
 drop database s_db;
 drop user normal_1@'%';
diff --git a/mysql-test/suite/sql_sequence/replication.test b/mysql-test/suite/sql_sequence/replication.test
index 7bd6f00e2ed..0c9422ab626 100644
--- a/mysql-test/suite/sql_sequence/replication.test
+++ b/mysql-test/suite/sql_sequence/replication.test
@@ -851,6 +851,28 @@ select next value for s1, cycle_count from s1;
 select next value for s1, cycle_count from s1;
 drop sequence s1;
 
+--echo ###########################################
+--echo test default()
+--echo ###########################################
+connection master;
+
+CREATE SEQUENCE s1 nocache engine=myisam;
+CREATE table t1 (a int default next value for s1, b int);
+insert into t1 (b) values (1),(2);
+select default(a) from t1;
+select * from t1;
+select * from s1;
+--sync_slave_with_master
+connection s_normal_3;
+select * from t1;
+select * from s1;
+connection master;
+drop table t1,s1;
+
+#
+# Cleanup
+#
+
 connection master;
 drop database s_db;
 drop user normal_1@'%';
diff --git a/sql/item_func.cc b/sql/item_func.cc
index ad04c767fa5..9cd537418b3 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -6894,14 +6894,22 @@ longlong Item_func_nextval::val_int()
   longlong value;
   int error;
   const char *key;
-  TABLE *table= table_list->table;
   uint length= get_table_def_key(table_list, &key);
-  THD *thd= table->in_use;
+  THD *thd;
   SEQUENCE_LAST_VALUE *entry;
   char buff[80];
   String key_buff(buff,sizeof(buff), &my_charset_bin);
-  DBUG_ASSERT(table && table->s->sequence);
   DBUG_ENTER("Item_func_nextval::val_int");
+  update_table();
+  DBUG_ASSERT(table && table->s->sequence);
+  thd= table->in_use;
+
+  if (thd->count_cuted_fields == CHECK_FIELD_EXPRESSION)
+  {
+    /* Alter table checking if function works */
+    null_value= 0;
+    DBUG_RETURN(0);
+  }
 
   if (table->s->tmp_table != NO_TMP_TABLE)
   {
@@ -6953,7 +6961,7 @@ void Item_func_nextval::print(String *str, enum_query_type query_type)
   char d_name_buff[MAX_ALIAS_NAME], t_name_buff[MAX_ALIAS_NAME];
   const char *d_name= table_list->db, *t_name= table_list->table_name;
   bool use_db_name= d_name && d_name[0];
-  THD *thd= current_thd;
+  THD *thd= current_thd;                        // Don't trust 'table'
 
   str->append(func_name());
   str->append('(');
@@ -6993,12 +7001,14 @@ longlong Item_func_lastval::val_int()
   const char *key;
   SEQUENCE_LAST_VALUE *entry;
   uint length= get_table_def_key(table_list, &key);
-  THD *thd= table_list->table->in_use;
+  THD *thd;
   char buff[80];
   String key_buff(buff,sizeof(buff), &my_charset_bin);
   DBUG_ENTER("Item_func_lastval::val_int");
+  update_table();
+  thd= table->in_use;
 
-  if (table_list->table->s->tmp_table != NO_TMP_TABLE)
+  if (table->s->tmp_table != NO_TMP_TABLE)
   {
     /*
       Temporary tables has an extra \0 at end to distinguish it from
@@ -7017,7 +7027,7 @@ longlong Item_func_lastval::val_int()
     null_value= 1;
     DBUG_RETURN(0);
   }
-  if (entry->check_version(table_list->table))
+  if (entry->check_version(table))
   {
     /* Table droped and re-created, remove current version */
     my_hash_delete(&thd->sequences, (uchar*) entry);
@@ -7042,10 +7052,20 @@ longlong Item_func_setval::val_int()
 {
   longlong value;
   int error;
-  TABLE *table= table_list->table;
-  DBUG_ASSERT(table && table->s->sequence);
+  THD *thd;
   DBUG_ENTER("Item_func_setval::val_int");
 
+  update_table();
+  DBUG_ASSERT(table && table->s->sequence);
+  thd= table->in_use;
+
+  if (thd->count_cuted_fields == CHECK_FIELD_EXPRESSION)
+  {
+    /* Alter table checking if function works */
+    null_value= 0;
+    DBUG_RETURN(0);
+  }
+
   value= nextval;
   error= table->s->sequence->set_value(table, nextval, round, is_used);
   if (error)
@@ -7064,7 +7084,7 @@ void Item_func_setval::print(String *str, enum_query_type query_type)
   char d_name_buff[MAX_ALIAS_NAME], t_name_buff[MAX_ALIAS_NAME];
   const char *d_name= table_list->db, *t_name= table_list->table_name;
   bool use_db_name= d_name && d_name[0];
-  THD *thd= table_list->table->in_use;
+  THD *thd= current_thd;                        // Don't trust 'table'
 
   str->append(func_name());
   str->append('(');
diff --git a/sql/item_func.h b/sql/item_func.h
index cf7302c5b35..2a1dcef70da 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -3032,9 +3032,10 @@ class Item_func_nextval :public Item_longlong_func
 {
 protected:
   TABLE_LIST *table_list;
+  TABLE *table;
 public:
-  Item_func_nextval(THD *thd, TABLE_LIST *table):
-  Item_longlong_func(thd), table_list(table) {}
+  Item_func_nextval(THD *thd, TABLE_LIST *table_list_arg):
+  Item_longlong_func(thd), table_list(table_list_arg) {}
   longlong val_int();
   const char *func_name() const { return "nextval"; }
   void fix_length_and_dec()
@@ -3043,6 +3044,22 @@ public:
     max_length= MAX_BIGINT_WIDTH;
     maybe_null= 1;                              /* In case of errors */
   }
+  /*
+    update_table() function must be called during the value function
+    as in case of DEFAULT the sequence table may not yet be open
+    while fix_fields() are called
+  */
+  void update_table()
+  {
+    if (!(table= table_list->table))
+    {
+      /*
+        If nextval was used in DEFAULT then next_local points to
+        the table_list used by to open the sequence table
+      */
+      table= table_list->next_local->table;
+    }
+  }
   bool const_item() const { return 0; }
   Item *get_copy(THD *thd, MEM_ROOT *mem_root)
   { return get_item_copy<Item_func_nextval>(thd, mem_root, this); }
@@ -3050,7 +3067,8 @@ public:
   bool check_vcol_func_processor(void *arg)
   {
     return mark_unsupported_function(func_name(), "()", arg,
-                                     VCOL_NON_DETERMINISTIC);
+                                     (VCOL_NON_DETERMINISTIC |
+                                      VCOL_NOT_VIRTUAL));
   }
 };
 
@@ -3060,8 +3078,8 @@ public:
 class Item_func_lastval :public Item_func_nextval
 {
 public:
-  Item_func_lastval(THD *thd, TABLE_LIST *table):
-  Item_func_nextval(thd, table) {}
+  Item_func_lastval(THD *thd, TABLE_LIST *table_list_arg):
+  Item_func_nextval(thd, table_list_arg) {}
   longlong val_int();
   const char *func_name() const { return "lastval"; }
   Item *get_copy(THD *thd, MEM_ROOT *mem_root)
@@ -3077,9 +3095,9 @@ class Item_func_setval :public Item_func_nextval
   ulonglong round;
   bool is_used;
 public:
-  Item_func_setval(THD *thd, TABLE_LIST *table, longlong nextval_arg,
+  Item_func_setval(THD *thd, TABLE_LIST *table_list_arg, longlong nextval_arg,
                    ulonglong round_arg, bool is_used_arg)
-    : Item_func_nextval(thd, table),
+    : Item_func_nextval(thd, table_list_arg),
     nextval(nextval_arg), round(round_arg), is_used(is_used_arg)
   {}
   longlong val_int();
diff --git a/sql/lock.cc b/sql/lock.cc
index c6dd3724eba..b3ae189625b 100644
--- a/sql/lock.cc
+++ b/sql/lock.cc
@@ -294,7 +294,8 @@ MYSQL_LOCK *mysql_lock_tables(THD *thd, TABLE **tables, uint count, uint flags)
   if (lock_tables_check(thd, tables, count, flags))
     DBUG_RETURN(NULL);
 
-  if (!(thd->variables.option_bits & OPTION_TABLE_LOCK))
+  if (!(thd->variables.option_bits & OPTION_TABLE_LOCK) &&
+      !(flags & MYSQL_LOCK_USE_MALLOC))
     gld_flags|= GET_LOCK_ON_THD;
 
   if (! (sql_lock= get_lock_data(thd, tables, count, gld_flags)))
@@ -415,7 +416,8 @@ static int lock_external(THD *thd, TABLE **tables, uint count)
 void mysql_unlock_tables(THD *thd, MYSQL_LOCK *sql_lock)
 {
   mysql_unlock_tables(thd, sql_lock,
-                      thd->variables.option_bits & OPTION_TABLE_LOCK);
+                      (thd->variables.option_bits & OPTION_TABLE_LOCK) ||
+                      !(sql_lock->flags & GET_LOCK_ON_THD));
 }
 
 
@@ -433,7 +435,10 @@ void mysql_unlock_tables(THD *thd, MYSQL_LOCK *sql_lock, bool free_lock)
   if (sql_lock->lock_count)
     thr_multi_unlock(sql_lock->locks, sql_lock->lock_count, 0);
   if (free_lock)
+  {
+    DBUG_ASSERT(!(sql_lock->flags & GET_LOCK_ON_THD));
     my_free(sql_lock);
+  }
   if (!errors)
     thd->clear_error();
   THD_STAGE_INFO(thd, org_stage);
@@ -782,6 +787,7 @@ MYSQL_LOCK *get_lock_data(THD *thd, TABLE **table_ptr, uint count, uint flags)
   locks= locks_buf= sql_lock->locks= (THR_LOCK_DATA**) (sql_lock + 1);
   to= table_buf= sql_lock->table= (TABLE**) (locks + lock_count * 2);
   sql_lock->table_count= table_count;
+  sql_lock->flags= flags;
 
   for (i=0 ; i < count ; i++)
   {
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index 39708b141f1..9469bce9964 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -4739,7 +4739,8 @@ sp_head::add_used_tables_to_table_list(THD *thd,
       table->init_one_table_for_prelocking(key_buff, stab->db_length,
            key_buff + stab->db_length + 1, stab->table_name_length,
            key_buff + stab->db_length + stab->table_name_length + 2,
-           stab->lock_type, true, belong_to_view, stab->trg_event_map,
+           stab->lock_type, TABLE_LIST::PRELOCK_ROUTINE, belong_to_view,
+           stab->trg_event_map,
            query_tables_last_ptr);
 
       tab_buff+= ALIGN_SIZE(sizeof(TABLE_LIST));
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 0d3608292fd..e657861a495 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -3531,7 +3531,7 @@ open_and_process_table(THD *thd, LEX *lex, TABLE_LIST *tables,
   */
   if (thd->locked_tables_mode <= LTM_LOCK_TABLES &&
       ! has_prelocking_list &&
-      tables->lock_type >= TL_WRITE_ALLOW_WRITE)
+      (tables->lock_type >= TL_WRITE_ALLOW_WRITE || thd->lex->default_used))
   {
     bool need_prelocking= FALSE;
     TABLE_LIST **save_query_tables_last= lex->query_tables_last;
@@ -4237,7 +4237,7 @@ static bool table_already_fk_prelocked(TABLE_LIST *tl, LEX_CSTRING *db,
   for (; tl; tl= tl->next_global )
   {
     if (tl->lock_type >= lock_type &&
-        tl->prelocking_placeholder == TABLE_LIST::FK &&
+        tl->prelocking_placeholder == TABLE_LIST::PRELOCK_FK &&
         strcmp(tl->db, db->str) == 0 &&
         strcmp(tl->table_name, table->str) == 0)
       return true;
@@ -4246,6 +4246,34 @@ static bool table_already_fk_prelocked(TABLE_LIST *tl, LEX_CSTRING *db,
 }
 
 
+static bool
+add_internal_tables(THD *thd, Query_tables_list *prelocking_ctx,
+                    TABLE_LIST *global_table_list, TABLE_LIST *tables)
+{
+  do
+  {
+    TABLE_LIST *tl= (TABLE_LIST *) thd->alloc(sizeof(TABLE_LIST));
+    if (!tl)
+      return TRUE;
+    tl->init_one_table_for_prelocking(tables->db,
+                                      strlen(tables->db),
+                                      tables->table_name,
+                                      strlen(tables->table_name),
+                                      NULL, tables->lock_type,
+                                      TABLE_LIST::PRELOCK_NONE,
+                                      0, 0,
+                                      &prelocking_ctx->query_tables_last);
+    /*
+      Store link to the new table_list that will be used by open so that
+      Item_func_nextval() can find it
+    */
+    tables->next_local= tl;
+  } while ((tables= tables->next_global));
+  return FALSE;
+}
+
+
+
 /**
   Defines how prelocking algorithm for DML statements should handle table list
   elements:
@@ -4272,21 +4300,23 @@ bool DML_prelocking_strategy::
 handle_table(THD *thd, Query_tables_list *prelocking_ctx,
              TABLE_LIST *table_list, bool *need_prelocking)
 {
+  TABLE *table= table_list->table;
   /* We rely on a caller to check that table is going to be changed. */
-  DBUG_ASSERT(table_list->lock_type >= TL_WRITE_ALLOW_WRITE);
+  DBUG_ASSERT(table_list->lock_type >= TL_WRITE_ALLOW_WRITE ||
+              thd->lex->default_used);
 
   if (table_list->trg_event_map)
   {
-    if (table_list->table->triggers)
+    if (table->triggers)
     {
       *need_prelocking= TRUE;
 
-      if (table_list->table->triggers->
+      if (table->triggers->
           add_tables_and_routines_for_triggers(thd, prelocking_ctx, table_list))
         return TRUE;
     }
 
-    if (table_list->table->file->referenced_by_foreign_key())
+    if (table->file->referenced_by_foreign_key())
     {
       List <FOREIGN_KEY_INFO> fk_list;
       List_iterator<FOREIGN_KEY_INFO> fk_list_it(fk_list);
@@ -4295,7 +4325,7 @@ handle_table(THD *thd, Query_tables_list *prelocking_ctx,
 
       arena= thd->activate_stmt_arena_if_needed(&backup);
 
-      table_list->table->file->get_parent_foreign_key_list(thd, &fk_list);
+      table->file->get_parent_foreign_key_list(thd, &fk_list);
       if (thd->is_error())
       {
         if (arena)
@@ -4324,21 +4354,88 @@ handle_table(THD *thd, Query_tables_list *prelocking_ctx,
           continue;
 
         TABLE_LIST *tl= (TABLE_LIST *) thd->alloc(sizeof(TABLE_LIST));
-        tl->init_one_table_for_prelocking(fk->foreign_db->str, fk->foreign_db->length,
-                           fk->foreign_table->str, fk->foreign_table->length,
-                           NULL, lock_type, false, table_list->belong_to_view,
-                           op, &prelocking_ctx->query_tables_last);
+        tl->init_one_table_for_prelocking(fk->foreign_db->str,
+                                          fk->foreign_db->length,
+                                          fk->foreign_table->str,
+                                          fk->foreign_table->length,
+                                          NULL, lock_type,
+                                          TABLE_LIST::PRELOCK_FK,
+                                          table_list->belong_to_view, op,
+                                          &prelocking_ctx->query_tables_last);
       }
       if (arena)
         thd->restore_active_arena(arena, &backup);
     }
   }
 
+  /* Open any tables used by DEFAULT (like sequence tables) */
+  if (table->internal_tables &&
+      ((sql_command_flags[thd->lex->sql_command] & CF_INSERTS_DATA) ||
+       thd->lex->default_used) &&
+      add_internal_tables(thd, prelocking_ctx, table_list,
+                          table->internal_tables))
+  {
+    *need_prelocking= TRUE;
+    return TRUE;
+  }
   return FALSE;
 }
 
 
 /**
+  Open all tables used by DEFAULT functions.
+
+  This is different from normal open_and_lock_tables() as we may
+  already have other tables opened and locked and we have to merge the
+  new table with the old ones.
+*/
+
+bool open_and_lock_internal_tables(TABLE *table, bool lock_table)
+{
+  THD *thd= table->in_use;
+  TABLE_LIST *tl;
+  MYSQL_LOCK *save_lock,*new_lock;
+  DBUG_ENTER("open_internal_tables");
+
+  /* remove pointer to old select_lex which is already destroyed */
+  for (tl= table->internal_tables ; tl ; tl= tl->next_global)
+    tl->select_lex= 0;
+
+  uint counter;
+  MDL_savepoint mdl_savepoint= thd->mdl_context.mdl_savepoint();
+  TABLE_LIST *tmp= table->internal_tables;
+  DML_prelocking_strategy prelocking_strategy;
+
+  if (open_tables(thd, thd->lex->create_info, &tmp, &counter, 0,
+                  &prelocking_strategy))
+    goto err;
+
+  if (lock_table)
+  {
+    save_lock= thd->lock;
+    thd->lock= 0;
+    if (lock_tables(thd, table->internal_tables, counter,
+                    MYSQL_LOCK_USE_MALLOC))
+      goto err;
+
+    if (!(new_lock= mysql_lock_merge(save_lock, thd->lock)))
+    {
+      thd->lock= save_lock;
+      mysql_unlock_tables(thd, save_lock, 1);
+      /* We don't have to close tables as caller will do that */
+      goto err;
+    }
+    thd->lock= new_lock;
+  }
+  DBUG_RETURN(0);
+
+err:
+  thd->mdl_context.rollback_to_savepoint(mdl_savepoint);
+  DBUG_RETURN(1);
+}
+
+
+/**
   Defines how prelocking algorithm for DML statements should handle view -
   all view routines should be added to the prelocking set.
 
@@ -4489,7 +4586,7 @@ static bool check_lock_and_start_stmt(THD *thd,
     Prelocking placeholder is not set for TABLE_LIST that
     are directly used by TOP level statement.
   */
-  DBUG_ASSERT(table_list->prelocking_placeholder == false);
+  DBUG_ASSERT(table_list->prelocking_placeholder == TABLE_LIST::PRELOCK_NONE);
 
   /*
     TL_WRITE_DEFAULT and TL_READ_DEFAULT are supposed to be parser only
@@ -4502,7 +4599,6 @@ static bool check_lock_and_start_stmt(THD *thd,
     Last argument routine_modifies_data for read_lock_type_for_table()
     is ignored, as prelocking placeholder will never be set here.
   */
-  DBUG_ASSERT(table_list->prelocking_placeholder == false);
   if (table_list->lock_type == TL_WRITE_DEFAULT)
     lock_type= thd->update_lock_default;
   else if (table_list->lock_type == TL_READ_DEFAULT)
@@ -4510,8 +4606,8 @@ static bool check_lock_and_start_stmt(THD *thd,
   else
     lock_type= table_list->lock_type;
 
-  if ((int) lock_type > (int) TL_WRITE_ALLOW_WRITE &&
-      (int) table_list->table->reginfo.lock_type <= (int) TL_WRITE_ALLOW_WRITE)
+  if ((int) lock_type >= (int) TL_WRITE_ALLOW_WRITE &&
+      (int) table_list->table->reginfo.lock_type < (int) TL_WRITE_ALLOW_WRITE)
   {
     my_error(ER_TABLE_NOT_LOCKED_FOR_WRITE, MYF(0),
              table_list->table->alias.c_ptr());
diff --git a/sql/sql_base.h b/sql/sql_base.h
index bb33af66590..bf140cb5d17 100644
--- a/sql/sql_base.h
+++ b/sql/sql_base.h
@@ -100,6 +100,7 @@ TABLE *open_ltable(THD *thd, TABLE_LIST *table_list, thr_lock_type update,
 */
 #define MYSQL_OPEN_SKIP_SCOPED_MDL_LOCK         0x1000
 #define MYSQL_LOCK_NOT_TEMPORARY		0x2000
+#define MYSQL_LOCK_USE_MALLOC                   0x4000
 /**
   Only check THD::killed if waits happen (e.g. wait on MDL, wait on
   table flush, wait on thr_lock.c locks) while opening and locking table.
@@ -257,6 +258,7 @@ bool open_normal_and_derived_tables(THD *thd, TABLE_LIST *tables, uint flags,
                                     uint dt_phases);
 bool open_tables_only_view_structure(THD *thd, TABLE_LIST *tables,
                                      bool can_deadlock);
+bool open_and_lock_internal_tables(TABLE *table, bool lock);
 bool lock_tables(THD *thd, TABLE_LIST *tables, uint counter, uint flags);
 int decide_logging_format(THD *thd, TABLE_LIST *tables);
 void close_thread_table(THD *thd, TABLE **table_ptr);
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 91fa1c2cbc2..3b4c17ffe67 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -4688,7 +4688,7 @@ TABLE *find_fk_open_table(THD *thd, const char *db, size_t db_len,
   {
     if (t->s->db.length == db_len && t->s->table_name.length == table_len &&
         !strcmp(t->s->db.str, db) && !strcmp(t->s->table_name.str, table) &&
-        t->pos_in_table_list->prelocking_placeholder == TABLE_LIST::FK)
+        t->pos_in_table_list->prelocking_placeholder == TABLE_LIST::PRELOCK_FK)
       return t;
   }
   return NULL;
@@ -6036,7 +6036,7 @@ int THD::decide_logging_format(TABLE_LIST *tables)
       replicated_tables_count++;
 
       if (table->lock_type <= TL_READ_NO_INSERT &&
-          table->prelocking_placeholder != TABLE_LIST::FK)
+          table->prelocking_placeholder != TABLE_LIST::PRELOCK_FK)
         has_read_tables= true;
       else if (table->table->found_next_number_field &&
                 (table->lock_type >= TL_WRITE_ALLOW_WRITE))
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 125abce4bf2..37a111ade5e 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -370,8 +370,9 @@ public:
 typedef struct st_mysql_lock
 {
   TABLE **table;
-  uint table_count,lock_count;
   THR_LOCK_DATA **locks;
+  uint table_count,lock_count;
+  uint flags;
 } MYSQL_LOCK;
 
 
@@ -4408,7 +4409,8 @@ public:
                                    const char *path,
                                    const char *db,
                                    const char *table_name,
-                                   bool open_in_engine);
+                                   bool open_in_engine,
+                                   bool open_internal_tables);
 
   TABLE *find_temporary_table(const char *db, const char *table_name);
   TABLE *find_temporary_table(const TABLE_LIST *tl);
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index e870d8ec852..866a6837b56 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -196,8 +196,9 @@ init_lex_with_single_table(THD *thd, TABLE *table, LEX *lex)
   lex_start(thd);
   context->init();
   if ((!(table_ident= new Table_ident(thd,
+                                      &table->s->db,
                                       &table->s->table_name,
-                                      &table->s->db, TRUE))) ||
+                                      TRUE))) ||
       (!(table_list= select_lex->add_table_to_list(thd,
                                                    table_ident,
                                                    NULL,
@@ -741,6 +742,7 @@ void LEX::start(THD *thd_arg)
   spcont= NULL;
   proc_list.first= 0;
   escape_used= FALSE;
+  default_used= FALSE;
   query_tables= 0;
   reset_query_tables_list(FALSE);
   expr_allows_subselect= TRUE;
@@ -3047,7 +3049,7 @@ LEX::LEX()
   : explain(NULL),
     result(0), arena_for_set_stmt(0), mem_root_for_set_stmt(0),
     option_type(OPT_DEFAULT), context_analysis_only(0), sphead(0),
-    is_lex_started(0), limit_rows_examined_cnt(ULONGLONG_MAX)
+    default_used(0), is_lex_started(0), limit_rows_examined_cnt(ULONGLONG_MAX)
 {
 
   init_dynamic_array2(&plugins, sizeof(plugin_ref), plugins_static_buffer,
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 53fa3286c86..b5c9424eecc 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -2950,6 +2950,7 @@ public:
   st_alter_tablespace *alter_tablespace_info;
   
   bool escape_used;
+  bool default_used;    /* using default() function */
   bool is_lex_started; /* If lex_start() did run. For debugging. */
 
   /*
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index ea36a90f391..28d80ae727a 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -4772,7 +4772,8 @@ int create_table_impl(THD *thd,
   if (!frm_only && create_info->tmp_table())
   {
     TABLE *table= thd->create_and_open_tmp_table(create_info->db_type, frm,
-                                                 path, db, table_name, true);
+                                                 path, db, table_name, true,
+                                                 false);
 
     if (!table)
     {
@@ -9145,7 +9146,7 @@ bool mysql_alter_table(THD *thd, const char *new_db, const char *new_name,
           thd->create_and_open_tmp_table(new_db_type, &frm,
                                          alter_ctx.get_tmp_path(),
                                          alter_ctx.new_db, alter_ctx.tmp_name,
-                                         false)))
+                                         false, true)))
       goto err_new_table_cleanup;
 
     /* Set markers for fields in TABLE object for altered table. */
@@ -9290,7 +9291,8 @@ bool mysql_alter_table(THD *thd, const char *new_db, const char *new_name,
   }
 
   // It's now safe to take the table level lock.
-  if (lock_tables(thd, table_list, alter_ctx.tables_opened, 0))
+  if (lock_tables(thd, table_list, alter_ctx.tables_opened,
+                  MYSQL_LOCK_USE_MALLOC))
     goto err_new_table_cleanup;
 
   if (ha_create_table(thd, alter_ctx.get_tmp_path(),
@@ -9307,7 +9309,7 @@ bool mysql_alter_table(THD *thd, const char *new_db, const char *new_name,
       thd->create_and_open_tmp_table(new_db_type, &frm,
                                      alter_ctx.get_tmp_path(),
                                      alter_ctx.new_db, alter_ctx.tmp_name,
-                                     true);
+                                     true, true);
     if (!tmp_table)
     {
       goto err_new_table_cleanup;
@@ -9341,7 +9343,7 @@ bool mysql_alter_table(THD *thd, const char *new_db, const char *new_name,
       thd->create_and_open_tmp_table(new_db_type, &frm,
                                      alter_ctx.get_tmp_path(),
                                      alter_ctx.new_db, alter_ctx.tmp_name,
-                                     true);
+                                     true, true);
   }
   if (!new_table)
     goto err_new_table_cleanup;
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 15413d43eee..2bc9d918fd3 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -9591,6 +9591,7 @@ column_default_non_parenthesized_expr:
                                                          $3);
             if ($$ == NULL)
               MYSQL_YYABORT;
+            Lex->default_used= TRUE;
           }
         | VALUES '(' simple_ident_nospvar ')'
           {
diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy
index 46cfdb5ae99..a3a6796de7d 100644
--- a/sql/sql_yacc_ora.yy
+++ b/sql/sql_yacc_ora.yy
@@ -9792,6 +9792,7 @@ column_default_non_parenthesized_expr:
                                                          $3);
             if ($$ == NULL)
               MYSQL_YYABORT;
+            Lex->default_used= TRUE;
           }
         | VALUES '(' simple_ident_nospvar ')'
           {
diff --git a/sql/table.cc b/sql/table.cc
index 7502a13d5ce..95ded6b70a9 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -2960,6 +2960,14 @@ unpack_vcol_info_from_frm(THD *thd, MEM_ROOT *mem_root, TABLE *table,
   if (error)
     goto end;
 
+  if (lex.current_select->table_list.first[0].next_global)
+  {
+    /* We are using NEXT VALUE FOR sequence. Remember table name for open */
+    TABLE_LIST *sequence= lex.current_select->table_list.first[0].next_global;
+    sequence->next_global= table->internal_tables;
+    table->internal_tables= sequence;
+  }
+
   vcol_storage.vcol_info->set_vcol_type(vcol->get_vcol_type());
   vcol_storage.vcol_info->stored_in_db=      vcol->stored_in_db;
   vcol_storage.vcol_info->name=              vcol->name;
diff --git a/sql/table.h b/sql/table.h
index 83a1e8702b7..92cd21ad762 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -1093,6 +1093,8 @@ public:
   TABLE_LIST *pos_in_table_list;/* Element referring to this table */
   /* Position in thd->locked_table_list under LOCK TABLES */
   TABLE_LIST *pos_in_locked_tables;
+  /* Tables used in DEFAULT and CHECK CONSTRAINT (normally sequence tables) */
+  TABLE_LIST *internal_tables;
 
   /*
     Not-null for temporary tables only. Non-null values means this table is
@@ -1743,6 +1745,11 @@ struct TABLE_LIST
 {
   TABLE_LIST() {}                          /* Remove gcc warning */
 
+  enum prelocking_types
+  {
+    PRELOCK_NONE, PRELOCK_ROUTINE, PRELOCK_FK
+  };
+
   /**
     Prepare TABLE_LIST that consists of one table instance to use in
     open_and_lock_tables
@@ -1773,7 +1780,7 @@ struct TABLE_LIST
                              size_t table_name_length_arg,
                              const char *alias_arg,
                              enum thr_lock_type lock_type_arg,
-                             bool routine,
+                             prelocking_types prelocking_type,
                              TABLE_LIST *belong_to_view_arg,
                              uint8 trg_event_map_arg,
                              TABLE_LIST ***last_ptr)
@@ -1781,8 +1788,10 @@ struct TABLE_LIST
     init_one_table(db_name_arg, db_length_arg, table_name_arg,
                    table_name_length_arg, alias_arg, lock_type_arg);
     cacheable_table= 1;
-    prelocking_placeholder= routine ? ROUTINE : FK;
-    open_type= routine ? OT_TEMPORARY_OR_BASE : OT_BASE_ONLY;
+    prelocking_placeholder= prelocking_type;
+    open_type= (prelocking_type == PRELOCK_ROUTINE ?
+                OT_TEMPORARY_OR_BASE :
+                OT_BASE_ONLY);
     belong_to_view= belong_to_view_arg;
     trg_event_map= trg_event_map_arg;
 
@@ -2071,7 +2080,7 @@ struct TABLE_LIST
     This TABLE_LIST object is just placeholder for prelocking, it will be
     used for implicit LOCK TABLES only and won't be used in real statement.
   */
-  enum { USER, ROUTINE, FK } prelocking_placeholder;
+  prelocking_types prelocking_placeholder;
   /**
      Indicates that if TABLE_LIST object corresponds to the table/view
      which requires special handling.
diff --git a/sql/temporary_tables.cc b/sql/temporary_tables.cc
index 5e5a05084b3..f95640871b5 100644
--- a/sql/temporary_tables.cc
+++ b/sql/temporary_tables.cc
@@ -64,7 +64,8 @@ TABLE *THD::create_and_open_tmp_table(handlerton *hton,
                                       const char *path,
                                       const char *db,
                                       const char *table_name,
-                                      bool open_in_engine)
+                                      bool open_in_engine,
+                                      bool open_internal_tables)
 {
   DBUG_ENTER("THD::create_and_open_tmp_table");
 
@@ -89,6 +90,15 @@ TABLE *THD::create_and_open_tmp_table(handlerton *hton,
 
       /* Free the TMP_TABLE_SHARE. */
       free_tmp_table_share(share, false);
+      DBUG_RETURN(0);
+    }
+
+    /* Open any related tables */
+    if (open_internal_tables && table->internal_tables &&
+        open_and_lock_internal_tables(table, open_in_engine))
+    {
+      drop_temporary_table(table, NULL, false);
+      DBUG_RETURN(0);
     }
   }