MDEV-7563 Support CHECK constraint as in (or close to) SQL Standard

MDEV-10134 Add full support for DEFAULT - Added support for using tables with MySQL 5.7 virtual fields, including MySQL 5.7 syntax - Better error messages also for old cases - CREATE ... SELECT now also updates timestamp columns - Blob can now have default values - Added new system variable "check_constraint_checks", to turn of CHECK constraint checking if needed. - Removed some engine independent tests in suite vcol to only test myisam - Moved some tests from 'include' to 't'. Should some day be done for all tests. - FRM version increased to 11 if one uses virtual fields or constraints - Changed to use a bitmap to check if a field has got a value, instead of setting HAS_EXPLICIT_VALUE bit in field flags - Expressions can now be up to 65K in total - Ensure we are not refering to uninitialized fields when handling virtual fields or defaults - Changed check_vcol_func_processor() to return a bitmap of used types - Had to change some functions that calculated cached value in fix_fields to do this in val() or getdate() instead. - store_now_in_TIME() now takes a THD argument - fill_record() now updates default values - Add a lookahead for NOT NULL, to be able to handle DEFAULT 1+1 NOT NULL - Automatically generate a name for constraints that doesn't have a name - Added support for ALTER TABLE DROP CONSTRAINT - Ensure that partition functions register virtual fields used. This fixes some bugs when using virtual fields in a partitioning function
author: Michael Widenius <monty@mariadb.org> 2016-06-29 09:14:22 +0200
committer: Sergei Golubchik <serg@mariadb.org> 2016-06-30 11:43:02 +0200
commit: db7edfed17efe6bc3684b0fbacc0b0249e4f0fa2 (patch)
tree: f1f484057487a73d32f379a5fdaacd53bfa27b5e /mysql-test/r
parent: 23d03a1b1e486da353f20964a1b91068bec209c0 (diff)
download: mariadb-git-db7edfed17efe6bc3684b0fbacc0b0249e4f0fa2.tar.gz
35 files changed, 1299 insertions, 278 deletions
diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result
index 9e3fc14d631..7c1686379fe 100644
--- a/mysql-test/r/alter_table.result
+++ b/mysql-test/r/alter_table.result
@@ -414,12 +414,12 @@ t1	CREATE TABLE `t1` (
   UNIQUE KEY `b` (`b`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 ALTER TABLE t1 DROP PRIMARY KEY;
-ERROR 42000: Can't DROP 'PRIMARY'; check that column/key exists
+ERROR 42000: Can't DROP 'PRIMARY'; check that constraint/column/key exists
 DROP TABLE t1;
 create table t1 (a int, b int, key(a));
 insert into t1 values (1,1), (2,2);
 alter table t1 drop key no_such_key;
-ERROR 42000: Can't DROP 'no_such_key'; check that column/key exists
+ERROR 42000: Can't DROP 'no_such_key'; check that constraint/column/key exists
 alter table t1 drop key a;
 drop table t1;
 CREATE TABLE T12207(a int) ENGINE=MYISAM;
@@ -1374,7 +1374,7 @@ Note	1060	Duplicate column name 'lol'
 ALTER TABLE t1 DROP COLUMN IF EXISTS lol;
 ALTER TABLE t1 DROP COLUMN IF EXISTS lol;
 Warnings:
-Note	1091	Can't DROP 'lol'; check that column/key exists
+Note	1091	Can't DROP 'lol'; check that constraint/column/key exists
 ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param);
 ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param);
 Warnings:
@@ -1385,7 +1385,7 @@ Note	1054	Unknown column 'lol' in 't1'
 DROP INDEX IF EXISTS x_param ON t1;
 DROP INDEX IF EXISTS x_param ON t1;
 Warnings:
-Note	1091	Can't DROP 'x_param'; check that column/key exists
+Note	1091	Can't DROP 'x_param'; check that constraint/column/key exists
 CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param);
 CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param);
 Warnings:
@@ -1416,7 +1416,7 @@ Note	1060	Duplicate column name 'lol'
 ALTER TABLE t1 DROP COLUMN IF EXISTS lol;
 ALTER TABLE t1 DROP COLUMN IF EXISTS lol;
 Warnings:
-Note	1091	Can't DROP 'lol'; check that column/key exists
+Note	1091	Can't DROP 'lol'; check that constraint/column/key exists
 ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param);
 ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param);
 Warnings:
@@ -1427,7 +1427,7 @@ Note	1054	Unknown column 'lol' in 't1'
 DROP INDEX IF EXISTS x_param ON t1;
 DROP INDEX IF EXISTS x_param ON t1;
 Warnings:
-Note	1091	Can't DROP 'x_param'; check that column/key exists
+Note	1091	Can't DROP 'x_param'; check that constraint/column/key exists
 CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param);
 CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param);
 Warnings:
@@ -1447,7 +1447,7 @@ Note	1061	Duplicate key name 'fk'
 ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS fk;
 ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS fk;
 Warnings:
-Note	1091	Can't DROP 'fk'; check that column/key exists
+Note	1091	Can't DROP 'fk'; check that constraint/column/key exists
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
@@ -1461,7 +1461,7 @@ Note	1061	Duplicate key name 't2_ibfk_1'
 ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS t2_ibfk_1;
 ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS t2_ibfk_1;
 Warnings:
-Note	1091	Can't DROP 't2_ibfk_1'; check that column/key exists
+Note	1091	Can't DROP 't2_ibfk_1'; check that constraint/column/key exists
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
@@ -1486,10 +1486,10 @@ t2	CREATE TABLE `t2` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 ALTER TABLE t2 DROP KEY k_id, DROP KEY IF EXISTS k_id;
 Warnings:
-Note	1091	Can't DROP 'k_id'; check that column/key exists
+Note	1091	Can't DROP 'k_id'; check that constraint/column/key exists
 ALTER TABLE t2 DROP COLUMN a, DROP COLUMN IF EXISTS a;
 Warnings:
-Note	1091	Can't DROP 'a'; check that column/key exists
+Note	1091	Can't DROP 'a'; check that constraint/column/key exists
 SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
@@ -1667,6 +1667,8 @@ ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= EXCLUSIVE;
 affected rows: 0
 ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= NONE;
 ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.
+ALTER ONLINE TABLE m1 ADD COLUMN c int;
+ERROR 0A000: LOCK=NONE is not supported for this operation. Try LOCK=SHARED.
 ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= SHARED;
 affected rows: 2
 info: Records: 2  Duplicates: 0  Warnings: 0
diff --git a/mysql-test/r/check_constraint.result b/mysql-test/r/check_constraint.result
new file mode 100644
index 00000000000..657c9326c38
--- /dev/null
+++ b/mysql-test/r/check_constraint.result
@@ -0,0 +1,97 @@
+set @save_check_constraint=@@check_constraint_checks;
+create table t1 (a int check(a>10), b int check (b > 20), constraint `min` check (a+b > 100), constraint `max` check (a+b <500)) engine=myisam;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL CHECK (a>10),
+  `b` int(11) DEFAULT NULL CHECK (b > 20),
+  CONSTRAINT `min` CHECK (a+b > 100),
+  CONSTRAINT `max` CHECK (a+b <500)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert into t1 values (100,100);
+insert into t1 values (1,1);
+ERROR HY000: CONSTRAINT 'a' failed for 'test.t1'
+insert into t1 values (20,1);
+ERROR HY000: CONSTRAINT 'b' failed for 'test.t1'
+insert into t1 values (20,30);
+ERROR HY000: CONSTRAINT 'min' failed for 'test.t1'
+insert into t1 values (500,500);
+ERROR HY000: CONSTRAINT 'max' failed for 'test.t1'
+insert into t1 values (101,101),(102,102),(600,600),(103,103);
+ERROR HY000: CONSTRAINT 'max' failed for 'test.t1'
+select * from t1;
+a	b
+100	100
+101	101
+102	102
+truncate table t1;
+insert ignore into t1 values (101,101),(102,102),(600,600),(103,103);
+Warnings:
+Warning	1369	CONSTRAINT 'max' failed for 'test.t1'
+select * from t1;
+a	b
+101	101
+102	102
+103	103
+set check_constraint_checks=0;
+truncate table t1;
+insert into t1 values (101,101),(102,102),(600,600),(103,103);
+select * from t1;
+a	b
+101	101
+102	102
+600	600
+103	103
+set check_constraint_checks=@save_check_constraint;
+alter table t1 add c int default 0 check (c < 10);
+ERROR HY000: CONSTRAINT 'max' failed for table
+set check_constraint_checks=0;
+alter table t1 add c int default 0 check (c < 10);
+alter table t1 add check (a+b+c < 500);
+set check_constraint_checks=@save_check_constraint;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL CHECK (a>10),
+  `b` int(11) DEFAULT NULL CHECK (b > 20),
+  `c` int(11) DEFAULT '0' CHECK (c < 10),
+  CONSTRAINT `min` CHECK (a+b > 100),
+  CONSTRAINT `max` CHECK (a+b <500),
+  CONSTRAINT `CONSTRAINT_1` CHECK (a+b+c < 500)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert into t1 values(105,105,105);
+ERROR HY000: CONSTRAINT 'c' failed for 'test.t1'
+insert into t1 values(249,249,9);
+ERROR HY000: CONSTRAINT 'CONSTRAINT_1' failed for 'test.t1'
+insert into t1 values(105,105,9);
+select * from t1;
+a	b	c
+101	101	0
+102	102	0
+600	600	0
+103	103	0
+105	105	9
+create table t2 like t1;
+show create table t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `a` int(11) DEFAULT NULL CHECK (a>10),
+  `b` int(11) DEFAULT NULL CHECK (b > 20),
+  `c` int(11) DEFAULT '0' CHECK (c < 10),
+  CONSTRAINT `min` CHECK (a+b > 100),
+  CONSTRAINT `max` CHECK (a+b <500),
+  CONSTRAINT `CONSTRAINT_1` CHECK (a+b+c < 500)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+alter table t2 drop constraint c;
+ERROR 42000: Can't DROP 'c'; check that constraint/column/key exists
+alter table t2 drop constraint min;
+show create table t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `a` int(11) DEFAULT NULL CHECK (a>10),
+  `b` int(11) DEFAULT NULL CHECK (b > 20),
+  `c` int(11) DEFAULT '0' CHECK (c < 10),
+  CONSTRAINT `max` CHECK (a+b <500),
+  CONSTRAINT `CONSTRAINT_1` CHECK (a+b+c < 500)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1,t2;
diff --git a/mysql-test/r/constraints.result b/mysql-test/r/constraints.result
index 3bec2c3e16d..2c101a209ef 100644
--- a/mysql-test/r/constraints.result
+++ b/mysql-test/r/constraints.result
@@ -1,17 +1,44 @@
 drop table if exists t1;
 create table t1 (a int check (a>0));
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL CHECK (a>0)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
 insert into t1 values (1);
 insert into t1 values (0);
+ERROR HY000: CONSTRAINT 'a' failed for 'test.t1'
 drop table t1;
 create table t1 (a int, b int, check (a>b));
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL,
+  CONSTRAINT `CONSTRAINT_1` CHECK (a>b)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
 insert into t1 values (1,0);
 insert into t1 values (0,1);
+ERROR HY000: CONSTRAINT 'CONSTRAINT_1' failed for 'test.t1'
 drop table t1;
 create table t1 (a int ,b int, constraint abc check (a>b));
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL,
+  CONSTRAINT `abc` CHECK (a>b)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
 insert into t1 values (1,0);
 insert into t1 values (0,1);
+ERROR HY000: CONSTRAINT 'abc' failed for 'test.t1'
 drop table t1;
 create table t1 (a int null);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
 insert into t1 values (1),(NULL);
 drop table t1;
 create table t1 (a int null);
diff --git a/mysql-test/r/create_drop_binlog.result b/mysql-test/r/create_drop_binlog.result
index 537a1bd4605..bc08ea82809 100644
--- a/mysql-test/r/create_drop_binlog.result
+++ b/mysql-test/r/create_drop_binlog.result
@@ -363,7 +363,7 @@ t1	CREATE TABLE `t1` (
 DROP INDEX IF EXISTS i1 ON t1;
 DROP INDEX IF EXISTS i1 ON t1;
 Warnings:
-Note	1091	Can't DROP 'i1'; check that column/key exists
+Note	1091	Can't DROP 'i1'; check that constraint/column/key exists
 DROP TABLE t1;
 DROP TABLE IF EXISTS t1;
 Warnings:
diff --git a/mysql-test/r/create_drop_index.result b/mysql-test/r/create_drop_index.result
index 113c32aca4d..f7b9ac02da6 100644
--- a/mysql-test/r/create_drop_index.result
+++ b/mysql-test/r/create_drop_index.result
@@ -16,7 +16,7 @@ t1	CREATE TABLE `t1` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP INDEX IF EXISTS i1 ON t1;
 Warnings:
-Note	1091	Can't DROP 'i1'; check that column/key exists
+Note	1091	Can't DROP 'i1'; check that constraint/column/key exists
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
diff --git a/mysql-test/r/default.result b/mysql-test/r/default.result
index 5531a99ec28..53a0e7b243c 100644
--- a/mysql-test/r/default.result
+++ b/mysql-test/r/default.result
@@ -236,3 +236,903 @@ DROP TABLE IF EXISTS t1;
 #
 # End of 10.1 tests
 #
+#
+# Start of 10.2 tests
+#
+
+Check that CURRENT_TIMESTAMP works as before
+
+CREATE or replace TABLE t1 (event_time TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+CREATE or replace TABLE t1 (event_time TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(2) ON UPDATE CURRENT_TIMESTAMP);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+CREATE or replace TABLE t1 (event_time TIMESTAMP(6) NOT NULL DEFAULT SYSDATE(2) ON UPDATE CURRENT_TIMESTAMP);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `event_time` timestamp(6) NOT NULL DEFAULT SYSDATE(2) ON UPDATE CURRENT_TIMESTAMP(6)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
+
+Check default expressions
+
+create or replace table t1 (a int default 1, b int default a+1, c int default a+b) engine myisam;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT '1',
+  `b` int(11) DEFAULT a+1,
+  `c` int(11) DEFAULT a+b
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert into t1 values ();
+insert into t1 (a) values (2);
+insert into t1 (a,b) values (10,20);
+insert into t1 (a,b,c) values (100,200,300);
+select * from t1;
+a	b	c
+1	2	3
+2	3	5
+10	20	30
+100	200	300
+truncate table t1;
+insert delayed into t1 values ();
+insert delayed into t1 (a) values (2);
+insert delayed into t1 (a,b) values (10,20);
+insert delayed into t1 (a,b,c) values (100,200,300);
+flush tables t1;
+select * from t1;
+a	b	c
+1	2	3
+2	3	5
+10	20	30
+100	200	300
+create or replace table t1 (a int, b blob default (1), c blob default ("hello"), t text default (concat(a,b,c))) engine=myisam;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` blob DEFAULT (1),
+  `c` blob DEFAULT ("hello"),
+  `t` text DEFAULT (concat(a,b,c))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert into t1 (a) values (2);
+insert into t1 (a,b) values (10,"test1");
+insert into t1 (a,b,c) values (10,"test2","test3");
+insert delayed into t1 (a,b) values (10,"test4");
+flush tables t1;
+select * from t1;
+a	b	c	t
+2	1	hello	21hello
+10	test1	hello	10test1hello
+10	test2	test3	10test2test3
+10	test4	hello	10test4hello
+drop table t1;
+create or replace table t1 (a bigint default uuid_short());
+insert into t1 values();
+select a > 0 from t1;
+a > 0
+1
+drop table t1;
+create or replace table t1 (param_list int DEFAULT (1+1) NOT NULL);
+create or replace table t1 (param_list int DEFAULT 1+1 NOT NULL);
+create or replace table t1 (param_list blob DEFAULT "" NOT NULL);
+drop table t1;
+create table t1 (a int);
+insert into t1 values(-1);
+alter table t1 add b int default 1, add c int default -1, add d int default 1+1, add e timestamp;
+select a,b,c,d,e > 0 from t1;
+a	b	c	d	e > 0
+-1	1	-1	2	1
+insert into t1 values(10,10,10,10,0);
+alter table t1 add f int default 1+1+1 null, add g int default 1+1+1+1 not null,add h int default (2+2+2+2);
+select a,b,c,d,e > 0,f,g,h from t1;
+a	b	c	d	e > 0	f	g	h
+-1	1	-1	2	1	3	4	8
+10	10	10	10	0	3	4	8
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT '1',
+  `c` int(11) DEFAULT '-1',
+  `d` int(11) DEFAULT 1+1,
+  `e` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+  `f` int(11) DEFAULT 1+1+1,
+  `g` int(11) NOT NULL DEFAULT 1+1+1+1,
+  `h` int(11) DEFAULT (2+2+2+2)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+create table t2 like t1;
+show create table t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT '1',
+  `c` int(11) DEFAULT '-1',
+  `d` int(11) DEFAULT 1+1,
+  `e` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+  `f` int(11) DEFAULT 1+1+1,
+  `g` int(11) NOT NULL DEFAULT 1+1+1+1,
+  `h` int(11) DEFAULT (2+2+2+2)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert into t2 (a) values (100);
+select a,b,c,d,e > 0,f,g,h from t2;
+a	b	c	d	e > 0	f	g	h
+100	1	-1	2	1	3	4	8
+drop table t1,t2;
+create table t1 (a int default 1----1);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT 1----1
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert into t1 values();
+insert into t1 values();
+select * from t1;
+a
+2
+2
+drop table t1;
+
+create or replace can delete a table on error
+
+create table t1 (a int);
+create or replace table t1 (a int default b, b int default a);
+ERROR 01000: Expression for field `a` is refering to uninitialized field `b`
+show create table t1;
+ERROR 42S02: Table 'test.t1' doesn't exist
+
+Refering to other columns
+
+create or replace table t1 (a int default 1, b int default a);
+create or replace table t1 (a int default 1, b int as (a));
+create or replace table t1 (a int default b, b int default 1);
+create or replace table t1 (a int as (b), b int default 1);
+create or replace table t1 (a int as (b), b int default 1+1);
+create or replace table t1 (a int default 1, b int as (c), c int default (a+1));
+create or replace table t1 (a int default 1+1, b int as (c), c int default (a+1));
+create or replace table t1 (a VARCHAR(128) DEFAULT @@version);
+create or replace table t1 (a int not null, b int as (a));
+create or replace table t1 (a int not null, b int default a+1);
+create or replace table t1 (a int default a);
+ERROR 01000: Expression for field `a` is refering to uninitialized field `a`
+create or replace table t1 (a int default b, b int default 1+1);
+ERROR 01000: Expression for field `a` is refering to uninitialized field `b`
+create or replace table t1 (a int default 1, b int as (c), c int as (a+1));
+ERROR 01000: Expression for field `b` is refering to uninitialized field `c`
+CREATE TABLE t1 (a INT DEFAULT a);
+ERROR 01000: Expression for field `a` is refering to uninitialized field `a`
+CREATE TABLE t1 (a INT DEFAULT (DEFAULT(a)));
+ERROR HY000: Field 'a' doesn't have a default value
+CREATE TABLE t1 (a INT DEFAULT(DEFAULT(b)), b INT DEFAULT(DEFAULT(a)));
+ERROR HY000: Field 'b' doesn't have a default value
+CREATE TABLE t1 (a INT DEFAULT(DEFAULT(b)) NOT NULL, b INT DEFAULT(DEFAULT(a)) NOT NULL);
+ERROR HY000: Field 'b' doesn't have a default value
+drop table if exists t1;
+Warnings:
+Note	1051	Unknown table 'test.t1'
+
+Allow defaults to refer to not default fields
+
+create or replace table t1 (a int as (b), b int not null);
+insert into t1 values();
+Warnings:
+Warning	1364	Field 'b' doesn't have a default value
+insert into t1 (a) values(1);
+Warnings:
+Warning	1364	Field 'b' doesn't have a default value
+Warning	1906	The value specified for computed column 'a' in table 't1' ignored
+insert into t1 (b) values(2);
+insert into t1 (a,b) values(3,4);
+Warnings:
+Warning	1906	The value specified for computed column 'a' in table 't1' ignored
+select * from t1;
+a	b
+0	0
+0	0
+2	2
+4	4
+drop table t1;
+
+Error handling
+
+create or replace table t1 (a bigint default xxx());
+ERROR HY000: Function or expression '`xxx`' is not allowed for 'DEFAULT' of column/constraint 'a'
+create or replace table t1 (a bigint default (select (1)));
+ERROR HY000: Function or expression 'subselect' is not allowed for 'DEFAULT' of column/constraint 'a'
+create or replace table t1 (a bigint default (1,2,3)));
+ERROR 21000: Operand should contain 1 column(s)
+drop table if exists t1;
+Warnings:
+Note	1051	Unknown table 'test.t1'
+#
+# Invalid DEFAULT expressions
+#
+CREATE TABLE t1 (a INT DEFAULT (SELECT 1));
+ERROR HY000: Function or expression 'subselect' is not allowed for 'DEFAULT' of column/constraint 'a'
+CREATE TABLE t1 (a INT DEFAULT (EXISTS (SELECT 1)));
+ERROR HY000: Function or expression 'subselect' is not allowed for 'DEFAULT' of column/constraint 'a'
+CREATE TABLE t1 (a INT DEFAULT (1=ANY (SELECT 1)));
+ERROR HY000: Function or expression 'subselect' is not allowed for 'DEFAULT' of column/constraint 'a'
+CREATE TABLE t1 (a INT DEFAULT ROW(1,1));
+ERROR 21000: Operand should contain 1 column(s)
+CREATE TABLE t1 (a INT DEFAULT (1,1));
+ERROR 21000: Operand should contain 1 column(s)
+CREATE TABLE t1 (a INT DEFAULT ((1,1)));
+ERROR 21000: Operand should contain 1 column(s)
+CREATE TABLE t1 (a INT DEFAULT ?);
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?)' at line 1
+CREATE TABLE t1 (a INT DEFAULT(?));
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?))' at line 1
+CREATE TABLE t1 (a INT DEFAULT (b), b INT DEFAULT(a));
+ERROR 01000: Expression for field `a` is refering to uninitialized field `b`
+CREATE TABLE t1 (a INT DEFAULT @v);
+ERROR HY000: Function or expression 'user_var' is not allowed for 'DEFAULT' of column/constraint 'a'
+CREATE TABLE t1 (a INT DEFAULT @v:=1);
+ERROR HY000: Function or expression 'user_var' is not allowed for 'DEFAULT' of column/constraint 'a'
+CREATE TABLE t1 (a INT DEFAULT(NAME_CONST('xxx', 'yyy'));
+ERROR HY000: Function or expression 'name_const' is not allowed for 'DEFAULT' of column/constraint 'a'
+CREATE TABLE t1 (a INT DEFAULT COUNT(*));
+ERROR HY000: Function or expression 'count(' is not allowed for 'DEFAULT' of column/constraint 'a'
+CREATE TABLE t1 (a INT DEFAULT COUNT(1));
+ERROR HY000: Function or expression 'count(' is not allowed for 'DEFAULT' of column/constraint 'a'
+CREATE TABLE t1 (a INT DEFAULT AVG(1));
+ERROR HY000: Function or expression 'avg(' is not allowed for 'DEFAULT' of column/constraint 'a'
+CREATE TABLE t1 (a INT DEFAULT MIN(1));
+ERROR HY000: Function or expression 'min(' is not allowed for 'DEFAULT' of column/constraint 'a'
+CREATE TABLE t1 (a INT DEFAULT GROUP_CONCAT(1));
+ERROR HY000: Function or expression 'group_concat' is not allowed for 'DEFAULT' of column/constraint 'a'
+CREATE TABLE t1 (a INT DEFAULT ROW_NUMBER() OVER ());
+ERROR HY000: Function or expression 'row_number' is not allowed for 'DEFAULT' of column/constraint 'a'
+CREATE FUNCTION f1() RETURNS INT RETURN 1;
+CREATE TABLE t1 (a INT DEFAULT f1());
+ERROR HY000: Function or expression '`f1`' is not allowed for 'DEFAULT' of column/constraint 'a'
+DROP FUNCTION f1;
+CREATE PROCEDURE p1(par INT) CREATE TABLE t1 (a INT DEFAULT par);
+ERROR HY000: Function or expression '???' is not allowed for 'DEFAULT' of column/constraint 'a'
+CREATE TABLE t1 (a INT DEFAULT par);
+ERROR 42S22: Unknown column 'par' in 'virtual column function'
+CREATE PROCEDURE p1() CREATE TABLE t1 (a INT DEFAULT par);
+CALL p1;
+ERROR 42S22: Unknown column 'par' in 'virtual column function'
+DROP PROCEDURE p1;
+CREATE TABLE t1 (a INT DEFAULT VALUES(a));
+ERROR HY000: Function or expression 'values' is not allowed for 'DEFAULT' of column/constraint 'a'
+CREATE TABLE t1 (a INT);
+CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW CREATE TABLE t2 (a INT DEFAULT NEW.a);
+ERROR HY000: Function or expression 'trigger' is not allowed for 'DEFAULT' of column/constraint 'a'
+CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW CREATE TEMPORARY TABLE t2 (a INT DEFAULT NEW.a);
+ERROR HY000: Function or expression 'trigger' is not allowed for 'DEFAULT' of column/constraint 'a'
+DROP TABLE t1;
+#
+# Prepared statements
+#
+PREPARE stmt FROM 'CREATE TABLE t1 (a INT DEFAULT(?))';
+set @a=1;
+execute stmt using @a;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT '1'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
+set @a=-1;
+execute stmt using @a;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT '-1'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
+DEALLOCATE PREPARE stmt;
+PREPARE stmt FROM 'CREATE TABLE t1 (a INT DEFAULT(?), b INT DEFAULT(?))';
+set @a=1, @b=2;
+execute stmt using @a,@b;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT '1',
+  `b` int(11) DEFAULT '2'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
+DEALLOCATE PREPARE stmt;
+PREPARE stmt FROM 'CREATE TABLE t1 (a INT DEFAULT(?+?))';
+set @a=1;
+execute stmt using @a,@a;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?+?))' at line 1
+DEALLOCATE PREPARE stmt;
+#
+# Parenthesized Item_basic_constant
+#
+CREATE TABLE t1 (
+i01 INT DEFAULT (((1))),
+i02 INT DEFAULT (((0x3939))),
+i03 INT DEFAULT (((1.0))),
+i04 INT DEFAULT (((1e0))),
+i05 INT DEFAULT (((NULL))),
+f01 DOUBLE DEFAULT (((PI()))),
+s01 VARCHAR(10) DEFAULT (((_latin1'test'))),
+s02 VARCHAR(10) DEFAULT ((('test'))),
+s03 VARCHAR(10) DEFAULT (((0x40))),
+s04 VARCHAR(10) DEFAULT (((X'40'))),
+s05 VARCHAR(10) DEFAULT (((B'1000000'))),
+d01 TIME DEFAULT (((TIME'10:20:30'))),
+d02 DATE DEFAULT (((DATE'2001-01-01'))),
+d03 DATETIME DEFAULT (((TIMESTAMP'2001-01-01 10:20:30')))
+);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `i01` int(11) DEFAULT '1',
+  `i02` int(11) DEFAULT '14649',
+  `i03` int(11) DEFAULT '1',
+  `i04` int(11) DEFAULT '1',
+  `i05` int(11) DEFAULT NULL,
+  `f01` double DEFAULT '3.141592653589793',
+  `s01` varchar(10) DEFAULT 'test',
+  `s02` varchar(10) DEFAULT 'test',
+  `s03` varchar(10) DEFAULT '@',
+  `s04` varchar(10) DEFAULT '@',
+  `s05` varchar(10) DEFAULT '@',
+  `d01` time DEFAULT '10:20:30',
+  `d02` date DEFAULT '2001-01-01',
+  `d03` datetime DEFAULT '2001-01-01 10:20:30'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES ();
+SELECT * FROM t1;
+i01	1
+i02	14649
+i03	1
+i04	1
+i05	NULL
+f01	3.141592653589793
+s01	test
+s02	test
+s03	@
+s04	@
+s05	@
+d01	10:20:30
+d02	2001-01-01
+d03	2001-01-01 10:20:30
+DROP TABLE t1;
+#
+# COALESCE(Item_basic_constant)
+#
+CREATE TABLE t1 (
+i01 INT DEFAULT COALESCE(1),
+i02 INT DEFAULT COALESCE(0x3939),
+i03 INT DEFAULT COALESCE(1.0),
+i04 INT DEFAULT COALESCE(1e0),
+i05 INT DEFAULT COALESCE(NULL),
+f01 DOUBLE DEFAULT COALESCE(PI()),
+s01 VARCHAR(10) DEFAULT COALESCE(_latin1'test'),
+s02 VARCHAR(10) DEFAULT COALESCE('test'),
+s03 VARCHAR(10) DEFAULT COALESCE(0x40),
+s04 VARCHAR(10) DEFAULT COALESCE(X'40'),
+s05 VARCHAR(10) DEFAULT COALESCE(B'1000000'),
+d01 TIME DEFAULT COALESCE(TIME'10:20:30'),
+d02 DATE DEFAULT COALESCE(DATE'2001-01-01'),
+d03 DATETIME DEFAULT COALESCE(TIMESTAMP'2001-01-01 10:20:30')
+);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `i01` int(11) DEFAULT COALESCE(1),
+  `i02` int(11) DEFAULT COALESCE(0x3939),
+  `i03` int(11) DEFAULT COALESCE(1.0),
+  `i04` int(11) DEFAULT COALESCE(1e0),
+  `i05` int(11) DEFAULT COALESCE(NULL),
+  `f01` double DEFAULT COALESCE(PI()),
+  `s01` varchar(10) DEFAULT COALESCE(_latin1'test'),
+  `s02` varchar(10) DEFAULT COALESCE('test'),
+  `s03` varchar(10) DEFAULT COALESCE(0x40),
+  `s04` varchar(10) DEFAULT COALESCE(X'40'),
+  `s05` varchar(10) DEFAULT COALESCE(B'1000000'),
+  `d01` time DEFAULT COALESCE(TIME'10:20:30'),
+  `d02` date DEFAULT COALESCE(DATE'2001-01-01'),
+  `d03` datetime DEFAULT COALESCE(TIMESTAMP'2001-01-01 10:20:30')
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES ();
+SELECT * FROM t1;
+i01	1
+i02	99
+i03	1
+i04	1
+i05	NULL
+f01	3.141592653589793
+s01	test
+s02	test
+s03	@
+s04	@
+s05	@
+d01	10:20:30
+d02	2001-01-01
+d03	2001-01-01 10:20:30
+DROP TABLE t1;
+#
+# TINYINT: out of range
+#
+CREATE TABLE t1 (a TINYINT DEFAULT 300 NOT NULL);
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1 (a TINYINT DEFAULT 128 NOT NULL);
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1 (a TINYINT DEFAULT -500 NOT NULL);
+ERROR 42000: Invalid default value for 'a'
+#
+# INT: simple numeric expressions
+#
+CREATE TABLE t1 (a INT DEFAULT 1 NOT NULL);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL DEFAULT '1'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (DEFAULT);
+SELECT * FROM t1;
+a
+1
+DROP TABLE t1;
+CREATE TABLE t1 (a INT DEFAULT COALESCE(1) NOT NULL);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL DEFAULT COALESCE(1)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (DEFAULT);
+SELECT * FROM t1;
+a
+1
+DROP TABLE t1;
+#
+# INT: simple string expressions 
+#
+CREATE TABLE t1 (a INT DEFAULT '1' NOT NULL);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL DEFAULT '1'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (DEFAULT);
+SELECT * FROM t1;
+a
+1
+DROP TABLE t1;
+CREATE TABLE t1 (a INT DEFAULT CONCAT('1') NOT NULL);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL DEFAULT CONCAT('1')
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (DEFAULT);
+SELECT * FROM t1;
+a
+1
+DROP TABLE t1;
+CREATE TABLE t1 (a INT DEFAULT COALESCE('1') NOT NULL);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL DEFAULT COALESCE('1')
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (DEFAULT);
+SELECT * FROM t1;
+a
+1
+DROP TABLE t1;
+#
+# INT: string expressions with garbage
+#
+CREATE TABLE t1 (a INT DEFAULT 'x');
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1 (a INT DEFAULT CONCAT('x'));
+insert into t1 values();
+Warnings:
+Warning	1366	Incorrect integer value: 'x' for column 'a' at row 1
+DROP TABLE t1;
+CREATE TABLE t1 (a INT DEFAULT COALESCE('x'));
+insert into t1 values();
+Warnings:
+Warning	1366	Incorrect integer value: 'x' for column 'a' at row 1
+DROP TABLE t1;
+#
+# INT: string expressions with numbers + garbage
+#
+CREATE TABLE t1 (a INT DEFAULT '1x');
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1 (a INT DEFAULT COALESCE('1x'));
+insert into t1 values();
+Warnings:
+Warning	1265	Data truncated for column 'a' at row 1
+DROP TABLE t1;
+CREATE TABLE t1 (a INT DEFAULT CONCAT('1x'));
+insert into t1 values();
+Warnings:
+Warning	1265	Data truncated for column 'a' at row 1
+DROP TABLE t1;
+#
+# INT: string expressions with numbers + trailing space
+#
+CREATE TABLE t1 (a INT DEFAULT '1 ');
+Warnings:
+Note	1265	Data truncated for column 'a' at row 1
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT '1'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (DEFAULT);
+SELECT * FROM t1;
+a
+1
+DROP TABLE t1;
+CREATE TABLE t1 (a INT DEFAULT CONCAT('1 '));
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT CONCAT('1 ')
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (DEFAULT);
+Warnings:
+Note	1265	Data truncated for column 'a' at row 1
+SELECT * FROM t1;
+a
+1
+DROP TABLE t1;
+CREATE TABLE t1 (a INT DEFAULT COALESCE('1 '));
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT COALESCE('1 ')
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (DEFAULT);
+Warnings:
+Note	1265	Data truncated for column 'a' at row 1
+SELECT * FROM t1;
+a
+1
+DROP TABLE t1;
+#
+# INT: a HEX value
+#
+CREATE TABLE t1 (a INT DEFAULT 0x61 NOT NULL);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL DEFAULT '97'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (DEFAULT);
+SELECT * FROM t1;
+a
+97
+DROP TABLE t1;
+#
+# VARCHAR: good defaults
+#
+CREATE TABLE t1 (a VARCHAR(30) DEFAULT 'xxx' NOT NULL);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(30) NOT NULL DEFAULT 'xxx'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (DEFAULT);
+SELECT * FROM t1;
+a
+xxx
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(2) DEFAULT 0x41 NOT NULL);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(2) NOT NULL DEFAULT 'A'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(2) DEFAULT CONCAT(0x41) NOT NULL);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(2) NOT NULL DEFAULT CONCAT(0x41)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (DEFAULT);
+SELECT * FROM t1;
+a
+A
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(2) DEFAULT COALESCE(0x41) NOT NULL);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(2) NOT NULL DEFAULT COALESCE(0x41)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (DEFAULT);
+SELECT * FROM t1;
+a
+A
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(2) DEFAULT CONCAT(_utf8 0x41) NOT NULL);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(2) NOT NULL DEFAULT CONCAT(_utf8 0x41)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (DEFAULT);
+SELECT * FROM t1;
+a
+A
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(2) DEFAULT CONCAT(_utf8 X'41') NOT NULL);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(2) NOT NULL DEFAULT CONCAT(_utf8 X'41')
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (DEFAULT);
+SELECT * FROM t1;
+a
+A
+DROP TABLE t1;
+#
+# VARCHAR: Too long default
+#
+CREATE TABLE t1 (a VARCHAR(2) DEFAULT 'xxx' NOT NULL);
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1 (a VARCHAR(2) DEFAULT CONCAT('xxx') NOT NULL);
+insert into t1 values();
+Warnings:
+Warning	1265	Data truncated for column 'a' at row 1
+DROP TABLE t1;
+#
+# VARCHAR: Too long default with non-important data
+#
+CREATE TABLE t1 (a VARCHAR(2) DEFAULT 'xx ' NOT NULL);
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1 (a VARCHAR(2) DEFAULT CONCAT('xx ') NOT NULL);
+insert into t1 values();
+Warnings:
+Note	1265	Data truncated for column 'a' at row 1
+DROP TABLE t1;
+#
+# VARCHAR: conversion failures
+#
+CREATE TABLE t1 (a VARCHAR(2) CHARACTER SET latin1 DEFAULT _utf8 X'D18F' NOT NULL);
+ERROR 42000: Invalid default value for 'a'
+CREATE TABLE t1 (a VARCHAR(2) CHARACTER SET latin1 DEFAULT CONCAT(_utf8 X'D18F') NOT NULL);
+insert into t1 values();
+Warnings:
+Warning	1366	Incorrect string value: '\xD1\x8F' for column 'a' at row 1
+DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(2) CHARACTER SET latin1 DEFAULT CONCAT(_utf8 0xD18F) NOT NULL);
+insert into t1 values();
+Warnings:
+Warning	1366	Incorrect string value: '\xD1\x8F' for column 'a' at row 1
+DROP TABLE t1;
+#
+# Field as a default value
+#
+CREATE TABLE t1 (a INT, b INT DEFAULT (a));
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT (a)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (1, 1);
+INSERT INTO t1 VALUES (DEFAULT, DEFAULT);
+INSERT INTO t1 VALUES (1, DEFAULT);
+INSERT INTO t1 VALUES (DEFAULT, 1);
+SELECT * FROM t1;
+a	b
+1	1
+NULL	NULL
+1	1
+NULL	1
+DROP TABLE t1;
+#
+# Function DEFAULT(field)
+#
+CREATE TABLE t1 (a INT DEFAULT(DEFAULT(b)), b INT DEFAULT 1);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT (DEFAULT(b)),
+  `b` int(11) DEFAULT '1'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (DEFAULT, DEFAULT);
+SELECT * FROM t1;
+a	b
+1	1
+DROP TABLE t1;
+CREATE TABLE t1 (a INT DEFAULT 1, b INT DEFAULT(DEFAULT(a)));
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT '1',
+  `b` int(11) DEFAULT (DEFAULT(a))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (DEFAULT, DEFAULT);
+SELECT * FROM t1;
+a	b
+1	1
+DROP TABLE t1;
+#
+# SQL Standard <datetime value function> as a <default option>
+#
+CREATE TABLE t1 (a DATETIME DEFAULT CURRENT_TIMESTAMP);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` datetime DEFAULT CURRENT_TIMESTAMP
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 (a TIME DEFAULT CURRENT_TIME);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` time DEFAULT CURRENT_TIME
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE DEFAULT CURRENT_DATE);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` date DEFAULT CURRENT_DATE
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+#
+# DECIMAL + CURRENT_TIMESTAMP, no truncation
+#
+SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30.123456');
+CREATE TABLE t1 (a DECIMAL(30,6) DEFAULT CURRENT_TIMESTAMP(6));
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` decimal(30,6) DEFAULT CURRENT_TIMESTAMP(6)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES ();
+SELECT * FROM t1;
+a
+20010101102030.123456
+DROP TABLE t1;
+CREATE TABLE t1 (a DECIMAL(30,6) DEFAULT COALESCE(CURRENT_TIMESTAMP(6)));
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` decimal(30,6) DEFAULT COALESCE(CURRENT_TIMESTAMP(6))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES();
+Warnings:
+Warning	1265	Data truncated for column 'a' at row 1
+INSERT IGNORE INTO t1 VALUES();
+Warnings:
+Warning	1265	Data truncated for column 'a' at row 1
+SET sql_mode = 'STRICT_ALL_TABLES';
+INSERT INTO t1 VALUES();
+ERROR 01000: Data truncated for column 'a' at row 1
+SET sql_mode = DEFAULT;
+DROP TABLE t1;
+SET timestamp=DEFAULT;
+#
+# DECIMAL + CURRENT_TIME, no truncation
+#
+SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30.123456');
+CREATE TABLE t1 (a DECIMAL(30,6) DEFAULT COALESCE(CURRENT_TIME(6)));
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` decimal(30,6) DEFAULT COALESCE(CURRENT_TIME(6))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES();
+Warnings:
+Warning	1265	Data truncated for column 'a' at row 1
+DROP TABLE t1;
+SET timestamp=DEFAULT;
+#
+# DECIMAL + CURRENT_DATE, no truncation
+#
+SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30.123456');
+CREATE TABLE t1 (a DECIMAL(30,6) DEFAULT COALESCE(CURRENT_DATE));
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` decimal(30,6) DEFAULT COALESCE(CURRENT_DATE)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES();
+Warnings:
+Warning	1265	Data truncated for column 'a' at row 1
+DROP TABLE t1;
+SET timestamp=DEFAULT;
+#
+# COALESCE for SQL Standard <datetime value function>
+#
+CREATE TABLE t1 (a TIMESTAMP DEFAULT COALESCE(CURRENT_TIMESTAMP));
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` timestamp NOT NULL DEFAULT COALESCE(CURRENT_TIMESTAMP)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE DEFAULT COALESCE(CURRENT_DATE));
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` date DEFAULT COALESCE(CURRENT_DATE)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 (a TIME DEFAULT COALESCE(CURRENT_TIME));
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` time DEFAULT COALESCE(CURRENT_TIME)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30.123456');
+CREATE TABLE t1 (
+a TIMESTAMP DEFAULT CURRENT_TIMESTAMP(6),
+b TIMESTAMP DEFAULT COALESCE(CURRENT_TIMESTAMP(6))
+);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  `b` timestamp NOT NULL DEFAULT COALESCE(CURRENT_TIMESTAMP(6))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES ();
+SELECT CURRENT_TIMESTAMP(6);
+CURRENT_TIMESTAMP(6)
+2001-01-01 10:20:30.123456
+SELECT * FROM t1;
+a	b
+2001-01-01 10:20:30	2001-01-01 10:20:30
+DROP TABLE t1;
+SET timestamp=DEFAULT;
+SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30.123456');
+CREATE TABLE t1 (
+a DECIMAL(30,0) DEFAULT CURRENT_TIMESTAMP(6),
+b DECIMAL(30,0) DEFAULT COALESCE(CURRENT_TIMESTAMP(6))
+);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` decimal(30,0) DEFAULT CURRENT_TIMESTAMP(6),
+  `b` decimal(30,0) DEFAULT COALESCE(CURRENT_TIMESTAMP(6))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES ();
+Warnings:
+Note	1265	Data truncated for column 'a' at row 1
+Warning	1265	Data truncated for column 'b' at row 1
+SELECT * FROM t1;
+a	b
+20010101102030	2001
+DROP TABLE t1;
+#
+# Miscelaneous SQL standard <default option> variants
+#
+CREATE TABLE t1 (a VARCHAR(30) DEFAULT CURRENT_USER);
+ERROR HY000: Function or expression 'current_user()' is not allowed for 'DEFAULT' of column/constraint 'a'
+CREATE TABLE t1 (a VARCHAR(30) DEFAULT CURRENT_ROLE);
+ERROR HY000: Function or expression 'current_role()' is not allowed for 'DEFAULT' of column/constraint 'a'
+#
+# Check DEFAULT() function
+#
+CREATE TABLE `t1` (`a` int(11) DEFAULT 3+3,`b` int(11) DEFAULT '1000');
+insert into t1 values (1,1),(2,2);
+insert into t1 values (default,default);
+select * from t1;
+a	b
+1	1
+2	2
+6	1000
+select default(a),b from t1;
+ERROR HY000: Field 'a' doesn't have a default value
+select a,default(b) from t1;
+a	default(b)
+1	1000
+2	1000
+6	1000
+drop table t1;
diff --git a/mysql-test/r/fulltext_distinct.result b/mysql-test/r/fulltext_distinct.result
index 7fd42fab646..de0668ff28c 100644
--- a/mysql-test/r/fulltext_distinct.result
+++ b/mysql-test/r/fulltext_distinct.result
@@ -8,8 +8,6 @@ KEY kt(tag),
 KEY kv(value(15)),
 FULLTEXT KEY kvf(value)
 ) ENGINE=MyISAM;
-Warnings:
-Warning	1101	BLOB/TEXT column 'value' can't have a default value
 CREATE TABLE t2 (
 id_t2 mediumint unsigned NOT NULL default '0',
 id_t1 mediumint unsigned NOT NULL default '0',
diff --git a/mysql-test/r/fulltext_update.result b/mysql-test/r/fulltext_update.result
index 4a615c88fdd..5d3f95b318c 100644
--- a/mysql-test/r/fulltext_update.result
+++ b/mysql-test/r/fulltext_update.result
@@ -9,8 +9,6 @@ name VARCHAR(80) DEFAULT '' NOT NULL,
 FULLTEXT(url,description,shortdesc,longdesc),
 PRIMARY KEY(gnr)
 );
-Warnings:
-Warning	1101	BLOB/TEXT column 'longdesc' can't have a default value
 insert into test (url,shortdesc,longdesc,description,name) VALUES 
 ("http:/test.at", "kurz", "lang","desc", "name");
 insert into test (url,shortdesc,longdesc,description,name) VALUES 
diff --git a/mysql-test/r/func_test.result b/mysql-test/r/func_test.result
index 387b8545f59..8c4c78460d5 100644
--- a/mysql-test/r/func_test.result
+++ b/mysql-test/r/func_test.result
@@ -319,6 +319,9 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (not((`test`.`t1`.`a` + 0)))
+select not 1, not null, not not null, 1 is not null;
+not 1	NULL	not not null	1 is not null
+0	NULL	NULL	1
 drop table t1;
 #
 # Start of 10.0 tests
diff --git a/mysql-test/r/function_defaults.result b/mysql-test/r/function_defaults.result
index b8f42b86c1f..a5367c4c591 100644
--- a/mysql-test/r/function_defaults.result
+++ b/mysql-test/r/function_defaults.result
@@ -9,50 +9,40 @@ SET TIME_ZONE = "+00:00";
 # Test of errors for column data types that dont support function
 # defaults.
 #
-CREATE TABLE t1( a BIT DEFAULT CURRENT_TIMESTAMP );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a TINYINT DEFAULT CURRENT_TIMESTAMP );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a SMALLINT DEFAULT CURRENT_TIMESTAMP );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a MEDIUMINT DEFAULT CURRENT_TIMESTAMP );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a INT DEFAULT CURRENT_TIMESTAMP );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a BIGINT DEFAULT CURRENT_TIMESTAMP );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a FLOAT DEFAULT CURRENT_TIMESTAMP );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a DECIMAL DEFAULT CURRENT_TIMESTAMP );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a DATE DEFAULT CURRENT_TIMESTAMP );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a TIME DEFAULT CURRENT_TIMESTAMP );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a YEAR DEFAULT CURRENT_TIMESTAMP );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a BIT ON UPDATE CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a BIT DEFAULT CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a TINYINT DEFAULT CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a SMALLINT DEFAULT CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a MEDIUMINT DEFAULT CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a INT DEFAULT CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a BIGINT DEFAULT CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a FLOAT DEFAULT CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a DECIMAL DEFAULT CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a DATE DEFAULT CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a TIME DEFAULT CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a YEAR DEFAULT CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a BIT ON UPDATE CURRENT_TIMESTAMP );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a TINYINT ON UPDATE CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a TINYINT ON UPDATE CURRENT_TIMESTAMP );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a SMALLINT ON UPDATE CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a SMALLINT ON UPDATE CURRENT_TIMESTAMP );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a MEDIUMINT ON UPDATE CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a MEDIUMINT ON UPDATE CURRENT_TIMESTAMP );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a INT ON UPDATE CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a INT ON UPDATE CURRENT_TIMESTAMP );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a BIGINT ON UPDATE CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a BIGINT ON UPDATE CURRENT_TIMESTAMP );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a FLOAT ON UPDATE CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a FLOAT ON UPDATE CURRENT_TIMESTAMP );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a DECIMAL ON UPDATE CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a DECIMAL ON UPDATE CURRENT_TIMESTAMP );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a DATE ON UPDATE CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a DATE ON UPDATE CURRENT_TIMESTAMP );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a TIME ON UPDATE CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a TIME ON UPDATE CURRENT_TIMESTAMP );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a YEAR ON UPDATE CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a YEAR ON UPDATE CURRENT_TIMESTAMP );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
+drop table if exists t1;
 #
 # Test that the default clause behaves like NOW() regarding time zones.
 #
@@ -864,8 +854,8 @@ SET TIMESTAMP = 2000.876543;
 INSERT INTO t2( a ) VALUES ( 3 );
 SELECT * FROM t2;
 b	a
-0000-00-00 00:00:00	1
-0000-00-00 00:00:00	2
+1970-01-01 00:16:40	1
+1970-01-01 00:16:40	2
 1970-01-01 00:33:20	3
 DROP TABLE t1, t2;
 #
@@ -1453,7 +1443,7 @@ a	b
 # 1970-01-01 03:33:20
 SET TIMESTAMP = 2000.000234;
 UPDATE v1 SET a = 2;
-ERROR HY000: CHECK OPTION failed 'test.v1'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 SELECT * FROM t1;
 a	b
 1	1970-01-01 03:16:40
@@ -1476,7 +1466,7 @@ a	c
 1973-08-14 09:11:22	1
 SET TIMESTAMP = 1.126789;
 INSERT INTO v1 ( c ) VALUES ( 1 ) ON DUPLICATE KEY UPDATE c = 2;
-ERROR HY000: CHECK OPTION failed 'test.v1'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 SELECT * FROM v1;
 a	c
 1973-08-14 09:11:22	1
@@ -1565,50 +1555,40 @@ SET TIME_ZONE = "+00:00";
 # Test of errors for column data types that dont support function
 # defaults.
 #
-CREATE TABLE t1( a BIT DEFAULT CURRENT_TIMESTAMP(6) );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a TINYINT DEFAULT CURRENT_TIMESTAMP(6) );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a SMALLINT DEFAULT CURRENT_TIMESTAMP(6) );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a MEDIUMINT DEFAULT CURRENT_TIMESTAMP(6) );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a INT DEFAULT CURRENT_TIMESTAMP(6) );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a BIGINT DEFAULT CURRENT_TIMESTAMP(6) );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a FLOAT DEFAULT CURRENT_TIMESTAMP(6) );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a DECIMAL DEFAULT CURRENT_TIMESTAMP(6) );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a DATE DEFAULT CURRENT_TIMESTAMP(6) );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a TIME DEFAULT CURRENT_TIMESTAMP(6) );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a YEAR DEFAULT CURRENT_TIMESTAMP(6) );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a BIT ON UPDATE CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a BIT DEFAULT CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a TINYINT DEFAULT CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a SMALLINT DEFAULT CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a MEDIUMINT DEFAULT CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a INT DEFAULT CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a BIGINT DEFAULT CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a FLOAT DEFAULT CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a DECIMAL DEFAULT CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a DATE DEFAULT CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a TIME DEFAULT CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a YEAR DEFAULT CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a BIT ON UPDATE CURRENT_TIMESTAMP(6) );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a TINYINT ON UPDATE CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a TINYINT ON UPDATE CURRENT_TIMESTAMP(6) );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a SMALLINT ON UPDATE CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a SMALLINT ON UPDATE CURRENT_TIMESTAMP(6) );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a MEDIUMINT ON UPDATE CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a MEDIUMINT ON UPDATE CURRENT_TIMESTAMP(6) );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a INT ON UPDATE CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a INT ON UPDATE CURRENT_TIMESTAMP(6) );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a BIGINT ON UPDATE CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a BIGINT ON UPDATE CURRENT_TIMESTAMP(6) );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a FLOAT ON UPDATE CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a FLOAT ON UPDATE CURRENT_TIMESTAMP(6) );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a DECIMAL ON UPDATE CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a DECIMAL ON UPDATE CURRENT_TIMESTAMP(6) );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a DATE ON UPDATE CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a DATE ON UPDATE CURRENT_TIMESTAMP(6) );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a TIME ON UPDATE CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a TIME ON UPDATE CURRENT_TIMESTAMP(6) );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a YEAR ON UPDATE CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a YEAR ON UPDATE CURRENT_TIMESTAMP(6) );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
+drop table if exists t1;
 #
 # Test that the default clause behaves like NOW() regarding time zones.
 #
@@ -2420,8 +2400,8 @@ SET TIMESTAMP = 2000.876543;
 INSERT INTO t2( a ) VALUES ( 3 );
 SELECT * FROM t2;
 b	a
-0000-00-00 00:00:00.000000	1
-0000-00-00 00:00:00.000000	2
+1970-01-01 00:16:40.987654	1
+1970-01-01 00:16:40.987654	2
 1970-01-01 00:33:20.876543	3
 DROP TABLE t1, t2;
 #
@@ -3009,7 +2989,7 @@ a	b
 # 1970-01-01 03:33:20
 SET TIMESTAMP = 2000.000234;
 UPDATE v1 SET a = 2;
-ERROR HY000: CHECK OPTION failed 'test.v1'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 SELECT * FROM t1;
 a	b
 1	1970-01-01 03:16:40.123456
@@ -3032,7 +3012,7 @@ a	c
 1973-08-14 09:11:22.089786	1
 SET TIMESTAMP = 1.126789;
 INSERT INTO v1 ( c ) VALUES ( 1 ) ON DUPLICATE KEY UPDATE c = 2;
-ERROR HY000: CHECK OPTION failed 'test.v1'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 SELECT * FROM v1;
 a	c
 1973-08-14 09:11:22.089786	1
diff --git a/mysql-test/r/function_defaults_innodb.result b/mysql-test/r/function_defaults_innodb.result
index 2525d4e948a..ee89d847a5c 100644
--- a/mysql-test/r/function_defaults_innodb.result
+++ b/mysql-test/r/function_defaults_innodb.result
@@ -10,50 +10,40 @@ SET TIME_ZONE = "+00:00";
 # Test of errors for column data types that dont support function
 # defaults.
 #
-CREATE TABLE t1( a BIT DEFAULT CURRENT_TIMESTAMP );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a TINYINT DEFAULT CURRENT_TIMESTAMP );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a SMALLINT DEFAULT CURRENT_TIMESTAMP );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a MEDIUMINT DEFAULT CURRENT_TIMESTAMP );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a INT DEFAULT CURRENT_TIMESTAMP );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a BIGINT DEFAULT CURRENT_TIMESTAMP );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a FLOAT DEFAULT CURRENT_TIMESTAMP );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a DECIMAL DEFAULT CURRENT_TIMESTAMP );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a DATE DEFAULT CURRENT_TIMESTAMP );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a TIME DEFAULT CURRENT_TIMESTAMP );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a YEAR DEFAULT CURRENT_TIMESTAMP );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a BIT ON UPDATE CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a BIT DEFAULT CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a TINYINT DEFAULT CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a SMALLINT DEFAULT CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a MEDIUMINT DEFAULT CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a INT DEFAULT CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a BIGINT DEFAULT CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a FLOAT DEFAULT CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a DECIMAL DEFAULT CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a DATE DEFAULT CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a TIME DEFAULT CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a YEAR DEFAULT CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a BIT ON UPDATE CURRENT_TIMESTAMP );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a TINYINT ON UPDATE CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a TINYINT ON UPDATE CURRENT_TIMESTAMP );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a SMALLINT ON UPDATE CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a SMALLINT ON UPDATE CURRENT_TIMESTAMP );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a MEDIUMINT ON UPDATE CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a MEDIUMINT ON UPDATE CURRENT_TIMESTAMP );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a INT ON UPDATE CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a INT ON UPDATE CURRENT_TIMESTAMP );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a BIGINT ON UPDATE CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a BIGINT ON UPDATE CURRENT_TIMESTAMP );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a FLOAT ON UPDATE CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a FLOAT ON UPDATE CURRENT_TIMESTAMP );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a DECIMAL ON UPDATE CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a DECIMAL ON UPDATE CURRENT_TIMESTAMP );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a DATE ON UPDATE CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a DATE ON UPDATE CURRENT_TIMESTAMP );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a TIME ON UPDATE CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a TIME ON UPDATE CURRENT_TIMESTAMP );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a YEAR ON UPDATE CURRENT_TIMESTAMP );
+CREATE OR REPLACE TABLE t1( a YEAR ON UPDATE CURRENT_TIMESTAMP );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
+drop table if exists t1;
 #
 # Test that the default clause behaves like NOW() regarding time zones.
 #
@@ -865,8 +855,8 @@ SET TIMESTAMP = 2000.876543;
 INSERT INTO t2( a ) VALUES ( 3 );
 SELECT * FROM t2;
 b	a
-0000-00-00 00:00:00	1
-0000-00-00 00:00:00	2
+1970-01-01 00:16:40	1
+1970-01-01 00:16:40	2
 1970-01-01 00:33:20	3
 DROP TABLE t1, t2;
 #
@@ -1454,7 +1444,7 @@ a	b
 # 1970-01-01 03:33:20
 SET TIMESTAMP = 2000.000234;
 UPDATE v1 SET a = 2;
-ERROR HY000: CHECK OPTION failed 'test.v1'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 SELECT * FROM t1;
 a	b
 1	1970-01-01 03:16:40
@@ -1477,7 +1467,7 @@ a	c
 1973-08-14 09:11:22	1
 SET TIMESTAMP = 1.126789;
 INSERT INTO v1 ( c ) VALUES ( 1 ) ON DUPLICATE KEY UPDATE c = 2;
-ERROR HY000: CHECK OPTION failed 'test.v1'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 SELECT * FROM v1;
 a	c
 1973-08-14 09:11:22	1
@@ -1566,50 +1556,40 @@ SET TIME_ZONE = "+00:00";
 # Test of errors for column data types that dont support function
 # defaults.
 #
-CREATE TABLE t1( a BIT DEFAULT CURRENT_TIMESTAMP(6) );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a TINYINT DEFAULT CURRENT_TIMESTAMP(6) );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a SMALLINT DEFAULT CURRENT_TIMESTAMP(6) );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a MEDIUMINT DEFAULT CURRENT_TIMESTAMP(6) );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a INT DEFAULT CURRENT_TIMESTAMP(6) );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a BIGINT DEFAULT CURRENT_TIMESTAMP(6) );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a FLOAT DEFAULT CURRENT_TIMESTAMP(6) );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a DECIMAL DEFAULT CURRENT_TIMESTAMP(6) );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a DATE DEFAULT CURRENT_TIMESTAMP(6) );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a TIME DEFAULT CURRENT_TIMESTAMP(6) );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a YEAR DEFAULT CURRENT_TIMESTAMP(6) );
-ERROR 42000: Invalid default value for 'a'
-CREATE TABLE t1( a BIT ON UPDATE CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a BIT DEFAULT CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a TINYINT DEFAULT CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a SMALLINT DEFAULT CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a MEDIUMINT DEFAULT CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a INT DEFAULT CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a BIGINT DEFAULT CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a FLOAT DEFAULT CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a DECIMAL DEFAULT CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a DATE DEFAULT CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a TIME DEFAULT CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a YEAR DEFAULT CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a BIT ON UPDATE CURRENT_TIMESTAMP(6) );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a TINYINT ON UPDATE CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a TINYINT ON UPDATE CURRENT_TIMESTAMP(6) );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a SMALLINT ON UPDATE CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a SMALLINT ON UPDATE CURRENT_TIMESTAMP(6) );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a MEDIUMINT ON UPDATE CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a MEDIUMINT ON UPDATE CURRENT_TIMESTAMP(6) );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a INT ON UPDATE CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a INT ON UPDATE CURRENT_TIMESTAMP(6) );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a BIGINT ON UPDATE CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a BIGINT ON UPDATE CURRENT_TIMESTAMP(6) );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a FLOAT ON UPDATE CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a FLOAT ON UPDATE CURRENT_TIMESTAMP(6) );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a DECIMAL ON UPDATE CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a DECIMAL ON UPDATE CURRENT_TIMESTAMP(6) );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a DATE ON UPDATE CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a DATE ON UPDATE CURRENT_TIMESTAMP(6) );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a TIME ON UPDATE CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a TIME ON UPDATE CURRENT_TIMESTAMP(6) );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
-CREATE TABLE t1( a YEAR ON UPDATE CURRENT_TIMESTAMP(6) );
+CREATE OR REPLACE TABLE t1( a YEAR ON UPDATE CURRENT_TIMESTAMP(6) );
 ERROR HY000: Invalid ON UPDATE clause for 'a' column
+drop table if exists t1;
 #
 # Test that the default clause behaves like NOW() regarding time zones.
 #
@@ -2421,8 +2401,8 @@ SET TIMESTAMP = 2000.876543;
 INSERT INTO t2( a ) VALUES ( 3 );
 SELECT * FROM t2;
 b	a
-0000-00-00 00:00:00.000000	1
-0000-00-00 00:00:00.000000	2
+1970-01-01 00:16:40.987654	1
+1970-01-01 00:16:40.987654	2
 1970-01-01 00:33:20.876543	3
 DROP TABLE t1, t2;
 #
@@ -3010,7 +2990,7 @@ a	b
 # 1970-01-01 03:33:20
 SET TIMESTAMP = 2000.000234;
 UPDATE v1 SET a = 2;
-ERROR HY000: CHECK OPTION failed 'test.v1'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 SELECT * FROM t1;
 a	b
 1	1970-01-01 03:16:40.123456
@@ -3033,7 +3013,7 @@ a	c
 1973-08-14 09:11:22.089786	1
 SET TIMESTAMP = 1.126789;
 INSERT INTO v1 ( c ) VALUES ( 1 ) ON DUPLICATE KEY UPDATE c = 2;
-ERROR HY000: CHECK OPTION failed 'test.v1'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 SELECT * FROM v1;
 a	c
 1973-08-14 09:11:22.089786	1
diff --git a/mysql-test/r/gis-rtree.result b/mysql-test/r/gis-rtree.result
index 0506a0b2a0a..43c51b838e2 100644
--- a/mysql-test/r/gis-rtree.result
+++ b/mysql-test/r/gis-rtree.result
@@ -804,8 +804,6 @@ INSERT INTO t2 SELECT GeomFromText(st) FROM t1;
 ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
 drop table t1, t2;
 CREATE TABLE t1 (`geometry` geometry NOT NULL default '',SPATIAL KEY `gndx` (`geometry`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-Warnings:
-Warning	1101	BLOB/TEXT column 'geometry' can't have a default value
 INSERT INTO t1 (geometry) VALUES
 (PolygonFromText('POLYGON((-18.6086111000 -66.9327777000, -18.6055555000
 -66.8158332999, -18.7186111000 -66.8102777000, -18.7211111000 -66.9269443999,
@@ -822,8 +820,6 @@ CREATE TABLE t1 (
 c1 geometry NOT NULL default '',
 SPATIAL KEY i1 (c1)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-Warnings:
-Warning	1101	BLOB/TEXT column 'c1' can't have a default value
 INSERT INTO t1 (c1) VALUES (
 PolygonFromText('POLYGON((-18.6086111000 -66.9327777000,
                             -18.6055555000 -66.8158332999,
@@ -838,8 +834,6 @@ CREATE TABLE t1 (
 c1 geometry NOT NULL default '',
 SPATIAL KEY i1 (c1)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-Warnings:
-Warning	1101	BLOB/TEXT column 'c1' can't have a default value
 INSERT INTO t1 (c1) VALUES (
 PolygonFromText('POLYGON((-18.6086111000 -66.9327777000,
                             -18.6055555000 -66.8158332999,
diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result
index 33080509114..6731b019ad0 100644
--- a/mysql-test/r/gis.result
+++ b/mysql-test/r/gis.result
@@ -614,8 +614,6 @@ t1	CREATE TABLE `t1` (
 drop table t1;
 CREATE TABLE `t1` (`object_id` bigint(20) unsigned NOT NULL default '0', `geo`
 geometry NOT NULL default '') ENGINE=MyISAM ;
-Warnings:
-Warning	1101	BLOB/TEXT column 'geo' can't have a default value
 insert into t1 values ('85984',GeomFromText('MULTIPOLYGON(((-115.006363
 36.305435,-114.992394 36.305202,-114.991219 36.305975,-114.991163
 36.306845,-114.989432 36.309452,-114.978275 36.312642,-114.977363
diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result
index fba04812a5b..098332a7de6 100644
--- a/mysql-test/r/information_schema.result
+++ b/mysql-test/r/information_schema.result
@@ -576,6 +576,7 @@ select s1 from t1 where s1 in (select version from
 information_schema.tables) union select version from
 information_schema.tables;
 s1
+11
 10
 drop table t1;
 SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets;
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index ca544f40aa8..24dcdeacf60 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -601,8 +601,6 @@ id int(11) DEFAULT '0' NOT NULL,
 name tinytext DEFAULT '' NOT NULL,
 UNIQUE id (id)
 );
-Warnings:
-Warning	1101	BLOB/TEXT column 'name' can't have a default value
 INSERT INTO t1 VALUES (1,'yes'),(2,'no');
 CREATE TABLE t2 (
 id int(11) DEFAULT '0' NOT NULL,
diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result
index 3616deaee9c..e20914c6e13 100644
--- a/mysql-test/r/join_outer_jcl6.result
+++ b/mysql-test/r/join_outer_jcl6.result
@@ -612,8 +612,6 @@ id int(11) DEFAULT '0' NOT NULL,
 name tinytext DEFAULT '' NOT NULL,
 UNIQUE id (id)
 );
-Warnings:
-Warning	1101	BLOB/TEXT column 'name' can't have a default value
 INSERT INTO t1 VALUES (1,'yes'),(2,'no');
 CREATE TABLE t2 (
 id int(11) DEFAULT '0' NOT NULL,
diff --git a/mysql-test/r/key.result b/mysql-test/r/key.result
index c44eb852fe2..806cc7a7b1a 100644
--- a/mysql-test/r/key.result
+++ b/mysql-test/r/key.result
@@ -467,7 +467,7 @@ alter table t1 drop index c2, add index (c2(4),c3(7));
 alter table t1 add primary key (c1, c2), drop primary key;
 alter table t1 drop primary key;
 alter table t1 add primary key (c1, c2), drop primary key;
-ERROR 42000: Can't DROP 'PRIMARY'; check that column/key exists
+ERROR 42000: Can't DROP 'PRIMARY'; check that constraint/column/key exists
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
diff --git a/mysql-test/r/mysql57_virtual.result b/mysql-test/r/mysql57_virtual.result
new file mode 100644
index 00000000000..a473a85cb3b
--- /dev/null
+++ b/mysql-test/r/mysql57_virtual.result
@@ -0,0 +1,38 @@
+#
+# Test that we can use tables created in MySQL 5.7
+#
+SHOW CREATE TABLE mysql57_virtual;
+Table	Create Table
+mysql57_virtual	CREATE TABLE `mysql57_virtual` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) AS ((`a` + 1)) VIRTUAL,
+  `c` int(11) AS ((`a` + 3)) PERSISTENT
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert into mysql57_virtual (a) values (1),(2);
+select * from mysql57_virtual;
+a	b	c
+1	2	4
+2	3	5
+alter online table mysql57_virtual comment "I am now a MariaDB table";
+ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.
+alter table mysql57_virtual comment "I am now a MariaDB table";
+SHOW CREATE TABLE mysql57_virtual;
+Table	Create Table
+mysql57_virtual	CREATE TABLE `mysql57_virtual` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) AS ((`a` + 1)) VIRTUAL,
+  `c` int(11) AS ((`a` + 3)) PERSISTENT
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='I am now a MariaDB table'
+DROP TABLE mysql57_virtual;
+#
+# Check MySQL 5.7 syntax
+#
+create table t1 (a int, b int generated always as (a+1) STORED, c int generated always as (a+2) VIRTUAL);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) AS (a+1) PERSISTENT,
+  `c` int(11) AS (a+2) VIRTUAL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
diff --git a/mysql-test/r/mysqlbinlog-innodb.result b/mysql-test/r/mysqlbinlog-innodb.result
index 2704be9e3ed..9508f725ec4 100644
--- a/mysql-test/r/mysqlbinlog-innodb.result
+++ b/mysql-test/r/mysqlbinlog-innodb.result
@@ -29,7 +29,7 @@ BEGIN
 use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/;
 SET @@session.sql_mode=1342177280/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -60,7 +60,7 @@ BEGIN
 use `foo`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/;
 SET @@session.sql_mode=1342177280/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
diff --git a/mysql-test/r/mysqlbinlog.result b/mysql-test/r/mysqlbinlog.result
index c7da070a622..108e03e5a47 100644
--- a/mysql-test/r/mysqlbinlog.result
+++ b/mysql-test/r/mysqlbinlog.result
@@ -22,7 +22,7 @@ ROLLBACK/*!*/;
 use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/;
 SET @@session.sql_mode=1342177280/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -102,7 +102,7 @@ BEGIN
 use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/;
 SET @@session.sql_mode=1342177280/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -138,7 +138,7 @@ BEGIN
 /*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/;
 SET @@session.sql_mode=1342177280/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -189,7 +189,7 @@ BEGIN
 use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/;
 SET @@session.sql_mode=1342177280/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -216,7 +216,7 @@ ROLLBACK/*!*/;
 use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/;
 SET @@session.sql_mode=1342177280/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -296,7 +296,7 @@ BEGIN
 use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/;
 SET @@session.sql_mode=1342177280/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -332,7 +332,7 @@ BEGIN
 /*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/;
 SET @@session.sql_mode=1342177280/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -383,7 +383,7 @@ BEGIN
 use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/;
 SET @@session.sql_mode=1342177280/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -492,7 +492,7 @@ DELIMITER /*!*/;
 use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/;
 SET @@session.sql_mode=1342177280/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -544,7 +544,7 @@ DELIMITER /*!*/;
 use `test`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/;
 SET @@session.sql_mode=1342177280/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -724,7 +724,7 @@ ROLLBACK/*!*/;
 use `test`/*!*/;
 SET TIMESTAMP=1253783037/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=0/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -778,7 +778,7 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 SET TIMESTAMP=1253783037/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=0/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -813,7 +813,7 @@ ROLLBACK /* added by mysqlbinlog */;
 DELIMITER /*!*/;
 SET TIMESTAMP=1266652094/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=0/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -855,7 +855,7 @@ ROLLBACK /* added by mysqlbinlog */;
 DELIMITER /*!*/;
 SET TIMESTAMP=1266652094/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=0/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -964,7 +964,7 @@ AAAAAAAAAAAAAAAAAAAgrgJSFzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
 # Event: 	Query	thread_id=1	exec_time=0	error_code=0
 SET TIMESTAMP=1375907364/*!*/;
 SET @@session.pseudo_thread_id=1/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=0/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -1047,7 +1047,7 @@ AAAAAAAAAAAAAAAAAAA/rQJSGzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
 # Event: 	Query	thread_id=1	exec_time=1	error_code=0
 SET TIMESTAMP=1375907141/*!*/;
 SET @@session.pseudo_thread_id=1/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=0/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -1130,7 +1130,7 @@ AAAAAAAAAAAAAAAAAAAnrAJSHzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
 # Event: 	Query	thread_id=1	exec_time=0	error_code=0
 SET TIMESTAMP=1375906879/*!*/;
 SET @@session.pseudo_thread_id=1/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=0/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
@@ -1213,7 +1213,7 @@ AAAAAAAAAAAAAAAAAABbsAJSEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
 # Event: 	Query	thread_id=1	exec_time=0	error_code=0
 SET TIMESTAMP=1375907933/*!*/;
 SET @@session.pseudo_thread_id=1/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=0/*!*/;
 SET @@session.sql_mode=0/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
diff --git a/mysql-test/r/mysqlbinlog_row_minimal.result b/mysql-test/r/mysqlbinlog_row_minimal.result
index 20837aeec24..84c0e668981 100644
--- a/mysql-test/r/mysqlbinlog_row_minimal.result
+++ b/mysql-test/r/mysqlbinlog_row_minimal.result
@@ -31,7 +31,7 @@ ROLLBACK/*!*/;
 use `test`/*!*/;
 SET TIMESTAMP=X/*!*/;
 SET @@session.pseudo_thread_id=4/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/;
 SET @@session.sql_mode=1342177280/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index a015819e480..697ebe26ad2 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -284,11 +284,7 @@ info text NOT NULL default '',
 ipnr varchar(30) NOT NULL default '',
 PRIMARY KEY  (member_id)
 ) ENGINE=MyISAM PACK_KEYS=1;
-Warnings:
-Warning	1101	BLOB/TEXT column 'info' can't have a default value
 insert into t1 (member_id) values (1),(2),(3);
-Warnings:
-Warning	1364	Field 'info' doesn't have a default value
 select member_id, nickname, voornaam FROM t1
 ORDER by lastchange_datum DESC LIMIT 2;
 member_id	nickname	voornaam
diff --git a/mysql-test/r/partition_explicit_prune.result b/mysql-test/r/partition_explicit_prune.result
index 765803d6332..1719556d9c8 100644
--- a/mysql-test/r/partition_explicit_prune.result
+++ b/mysql-test/r/partition_explicit_prune.result
@@ -126,7 +126,7 @@ HANDLER_WRITE	2
 # 4 locks (1 table, 1 partition lock/unlock)
 FLUSH STATUS;
 INSERT INTO v1 VALUES (31);
-ERROR HY000: CHECK OPTION failed 'test.v1'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
 WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
 VARIABLE_NAME	VARIABLE_VALUE
@@ -135,7 +135,7 @@ HANDLER_TMP_WRITE	22
 # 2 locks (1 table, all partitions pruned)
 FLUSH STATUS;
 INSERT INTO v1 VALUES (32);
-ERROR HY000: CHECK OPTION failed 'test.v1'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
 WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
 VARIABLE_NAME	VARIABLE_VALUE
diff --git a/mysql-test/r/replace.result b/mysql-test/r/replace.result
index 59b8565f671..2c1fc263b2d 100644
--- a/mysql-test/r/replace.result
+++ b/mysql-test/r/replace.result
@@ -27,6 +27,6 @@ drop table t1;
 CREATE TABLE t1 (f1 INT);
 CREATE VIEW v1 AS SELECT f1 FROM t1 WHERE f1 = 0 WITH CHECK OPTION;
 REPLACE INTO v1 (f1) VALUES (1);
-ERROR HY000: CHECK OPTION failed 'test.v1'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 DROP TABLE t1;
 DROP VIEW v1;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index c973bbe11d5..45daac4fa10 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -1932,7 +1932,7 @@ create table t1(id int);
 create table t2(id int);
 create table t3(flag int);
 select (select * from t3 where id not null) from t1, t2;
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'null) from t1, t2' at line 1
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'not null) from t1, t2' at line 1
 drop table t1,t2,t3;
 CREATE TABLE t1 (id INT);
 CREATE TABLE t2 (id INT);
@@ -5001,7 +5001,7 @@ CREATE VIEW v2 (a,b) AS
 SELECT t2.id, t2.c AS c FROM t1, t2
 WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION;
 INSERT INTO v2(a,b) VALUES (2,2);
-ERROR HY000: CHECK OPTION failed 'test.v2'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v2'
 SELECT * FROM v1;
 c
 1
diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result
index 8ad01c0816c..f95704a28b1 100644
--- a/mysql-test/r/subselect_no_exists_to_in.result
+++ b/mysql-test/r/subselect_no_exists_to_in.result
@@ -1936,7 +1936,7 @@ create table t1(id int);
 create table t2(id int);
 create table t3(flag int);
 select (select * from t3 where id not null) from t1, t2;
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'null) from t1, t2' at line 1
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'not null) from t1, t2' at line 1
 drop table t1,t2,t3;
 CREATE TABLE t1 (id INT);
 CREATE TABLE t2 (id INT);
@@ -5003,7 +5003,7 @@ CREATE VIEW v2 (a,b) AS
 SELECT t2.id, t2.c AS c FROM t1, t2
 WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION;
 INSERT INTO v2(a,b) VALUES (2,2);
-ERROR HY000: CHECK OPTION failed 'test.v2'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v2'
 SELECT * FROM v1;
 c
 1
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 6dc3cba5a77..d2a412caa1d 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -1939,7 +1939,7 @@ create table t1(id int);
 create table t2(id int);
 create table t3(flag int);
 select (select * from t3 where id not null) from t1, t2;
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'null) from t1, t2' at line 1
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'not null) from t1, t2' at line 1
 drop table t1,t2,t3;
 CREATE TABLE t1 (id INT);
 CREATE TABLE t2 (id INT);
@@ -5001,7 +5001,7 @@ CREATE VIEW v2 (a,b) AS
 SELECT t2.id, t2.c AS c FROM t1, t2
 WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION;
 INSERT INTO v2(a,b) VALUES (2,2);
-ERROR HY000: CHECK OPTION failed 'test.v2'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v2'
 SELECT * FROM v1;
 c
 1
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 843adafe2b9..f06e2d8e554 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -1935,7 +1935,7 @@ create table t1(id int);
 create table t2(id int);
 create table t3(flag int);
 select (select * from t3 where id not null) from t1, t2;
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'null) from t1, t2' at line 1
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'not null) from t1, t2' at line 1
 drop table t1,t2,t3;
 CREATE TABLE t1 (id INT);
 CREATE TABLE t2 (id INT);
@@ -4997,7 +4997,7 @@ CREATE VIEW v2 (a,b) AS
 SELECT t2.id, t2.c AS c FROM t1, t2
 WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION;
 INSERT INTO v2(a,b) VALUES (2,2);
-ERROR HY000: CHECK OPTION failed 'test.v2'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v2'
 SELECT * FROM v1;
 c
 1
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 348258f5ee1..ab27af586aa 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -1938,7 +1938,7 @@ create table t1(id int);
 create table t2(id int);
 create table t3(flag int);
 select (select * from t3 where id not null) from t1, t2;
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'null) from t1, t2' at line 1
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'not null) from t1, t2' at line 1
 drop table t1,t2,t3;
 CREATE TABLE t1 (id INT);
 CREATE TABLE t2 (id INT);
@@ -5007,7 +5007,7 @@ CREATE VIEW v2 (a,b) AS
 SELECT t2.id, t2.c AS c FROM t1, t2
 WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION;
 INSERT INTO v2(a,b) VALUES (2,2);
-ERROR HY000: CHECK OPTION failed 'test.v2'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v2'
 SELECT * FROM v1;
 c
 1
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index cd28339e4c9..d3be251a9e5 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -1935,7 +1935,7 @@ create table t1(id int);
 create table t2(id int);
 create table t3(flag int);
 select (select * from t3 where id not null) from t1, t2;
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'null) from t1, t2' at line 1
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'not null) from t1, t2' at line 1
 drop table t1,t2,t3;
 CREATE TABLE t1 (id INT);
 CREATE TABLE t2 (id INT);
@@ -4997,7 +4997,7 @@ CREATE VIEW v2 (a,b) AS
 SELECT t2.id, t2.c AS c FROM t1, t2
 WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION;
 INSERT INTO v2(a,b) VALUES (2,2);
-ERROR HY000: CHECK OPTION failed 'test.v2'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v2'
 SELECT * FROM v1;
 c
 1
diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result
index ec33f8ba203..5249c89e4a4 100644
--- a/mysql-test/r/type_blob.result
+++ b/mysql-test/r/type_blob.result
@@ -37,10 +37,20 @@ ERROR 42000: Column length too big for column 'a' (max = 255); use BLOB or TEXT
 CREATE TABLE t2 (a char(256));
 ERROR 42000: Column length too big for column 'a' (max = 255); use BLOB or TEXT instead
 CREATE TABLE t1 (a varchar(70000) default "hello");
-ERROR 42000: Column length too big for column 'a' (max = 65535); use BLOB or TEXT instead
+Warnings:
+Note	1246	Converting column 'a' from VARCHAR to TEXT
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` mediumtext DEFAULT "hello"
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
 CREATE TABLE t2 (a blob default "hello");
-ERROR 42000: BLOB/TEXT column 'a' can't have a default value
-drop table if exists t1,t2;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `a` blob DEFAULT "hello"
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1,t2;
 create table t1 (nr int(5) not null auto_increment,b blob,str char(10), primary key (nr));
 insert into t1 values (null,"a","A");
 insert into t1 values (null,"bbb","BBB");
@@ -503,11 +513,7 @@ foobar	boggle
 fish	10
 drop table t1;
 create table t1 (id integer auto_increment unique,imagem LONGBLOB not null default '');
-Warnings:
-Warning	1101	BLOB/TEXT column 'imagem' can't have a default value
 insert into t1 (id) values (1);
-Warnings:
-Warning	1364	Field 'imagem' doesn't have a default value
 select
 charset(load_file('../../std_data/words.dat')),
 collation(load_file('../../std_data/words.dat')),
@@ -793,21 +799,24 @@ NULL
 620000000000
 drop table t1;
 create table t1 (a text default '');
-Warnings:
-Warning	1101	BLOB/TEXT column 'a' can't have a default value
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `a` text DEFAULT NULL
+  `a` text DEFAULT ''
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 insert into t1 values (default);
 select * from t1;
 a
-NULL
+
 drop table t1;
 set @@sql_mode='TRADITIONAL';
 create table t1 (a text default '');
-ERROR 42000: BLOB/TEXT column 'a' can't have a default value
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` text DEFAULT ''
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
 set @@sql_mode='';
 CREATE TABLE t (c TEXT CHARSET ASCII);
 INSERT INTO t (c) VALUES (REPEAT('1',65537));
@@ -879,9 +888,9 @@ CREATE TABLE b15776 (a char(4294967295));
 ERROR 42000: Column length too big for column 'a' (max = 255); use BLOB or TEXT instead
 CREATE TABLE b15776 (a char(4294967296));
 ERROR 42000: Column length too big for column 'a' (max = 255); use BLOB or TEXT instead
-CREATE TABLE b15776 (a year(4294967295));
+CREATE TABLE b15776 (a year(?));
 Warnings:
-Note	1287	'YEAR(4294967295)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
+Note	1287	'YEAR(?)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
 INSERT INTO b15776 VALUES (42);
 SELECT * FROM b15776;
 a
diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result
index c998c28ebd9..9984d6dddff 100644
--- a/mysql-test/r/type_ranges.result
+++ b/mysql-test/r/type_ranges.result
@@ -38,9 +38,6 @@ KEY (ulong),
 KEY (ulonglong,ulong),
 KEY (options,flags)
 );
-Warnings:
-Warning	1101	BLOB/TEXT column 'mediumblob_col' can't have a default value
-Warning	1101	BLOB/TEXT column 'longblob_col' can't have a default value
 show full fields from t1;
 Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
 auto	int(5) unsigned	NULL	NO	PRI	NULL	auto_increment	#	
@@ -63,8 +60,8 @@ time_field	time	NULL	YES		NULL		#
 date_time	datetime	NULL	YES		NULL		#	
 blob_col	blob	NULL	YES		NULL		#	
 tinyblob_col	tinyblob	NULL	YES		NULL		#	
-mediumblob_col	mediumblob	NULL	NO		NULL		#	
-longblob_col	longblob	NULL	NO		NULL		#	
+mediumblob_col	mediumblob	NULL	NO		''		#	
+longblob_col	longblob	NULL	NO		''		#	
 options	enum('one','two','tree')	latin1_swedish_ci	NO	MUL	NULL		#	
 flags	set('one','two','tree')	latin1_swedish_ci	NO				#	
 show keys from t1;
@@ -129,9 +126,6 @@ Warning	1264	Out of range value for column 'ushort' at row 1
 Warning	1264	Out of range value for column 'umedium' at row 1
 Warning	1265	Data truncated for column 'options' at row 1
 insert into t1 (tiny) values (1);
-Warnings:
-Warning	1364	Field 'mediumblob_col' doesn't have a default value
-Warning	1364	Field 'longblob_col' doesn't have a default value
 select auto,string,tiny,short,medium,long_int,longlong,real_float,real_double,utiny,ushort,umedium,ulong,ulonglong,mod(floor(time_stamp/1000000),1000000)-mod(curdate(),1000000),date_field,time_field,date_time,blob_col,tinyblob_col,mediumblob_col,longblob_col from t1;
 auto	string	tiny	short	medium	long_int	longlong	real_float	real_double	utiny	ushort	umedium	ulong	ulonglong	mod(floor(time_stamp/1000000),1000000)-mod(curdate(),1000000)	date_field	time_field	date_time	blob_col	tinyblob_col	mediumblob_col	longblob_col
 10	1	1	1	1	1	1	1.0	1.0000	1	00001	1	1	1	0	0000-00-00	00:00:00	0000-00-00 00:00:00	1	1	1	1
@@ -238,7 +232,7 @@ time_field	time	NULL	YES		NULL		#
 date_time	datetime	NULL	YES		NULL		#	
 new_blob_col	varchar(20)	latin1_swedish_ci	YES		NULL		#	
 tinyblob_col	tinyblob	NULL	YES		NULL		#	
-mediumblob_col	mediumblob	NULL	NO		NULL		#	
+mediumblob_col	mediumblob	NULL	NO		''		#	
 options	enum('one','two','tree')	latin1_swedish_ci	NO	MUL	NULL		#	
 flags	set('one','two','tree')	latin1_swedish_ci	NO				#	
 new_field	char(10)	latin1_swedish_ci	NO		new		#	
@@ -264,7 +258,7 @@ time_field	time	NULL	YES		NULL		#
 date_time	datetime	NULL	YES		NULL		#	
 new_blob_col	varchar(20)	latin1_swedish_ci	YES		NULL		#	
 tinyblob_col	tinyblob	NULL	YES		NULL		#	
-mediumblob_col	mediumblob	NULL	NO		NULL		#	
+mediumblob_col	mediumblob	NULL	NO		''		#	
 options	enum('one','two','tree')	latin1_swedish_ci	NO		NULL		#	
 flags	set('one','two','tree')	latin1_swedish_ci	NO				#	
 new_field	char(10)	latin1_swedish_ci	NO		new		#	
diff --git a/mysql-test/r/type_timestamp_hires.result b/mysql-test/r/type_timestamp_hires.result
index 573ebbeb709..89f6754ec9a 100644
--- a/mysql-test/r/type_timestamp_hires.result
+++ b/mysql-test/r/type_timestamp_hires.result
@@ -273,11 +273,21 @@ a
 2011-01-01 01:01:01.12345
 drop table t1;
 create table t1 (a timestamp(5) default current_timestamp);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` timestamp(5) NOT NULL DEFAULT CURRENT_TIMESTAMP(5)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 create table t1 (a timestamp(5) default current_timestamp());
 drop table t1;
 create table t1 (a timestamp(5) default current_timestamp(2));
-ERROR 42000: Invalid default value for 'a'
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` timestamp(5) NOT NULL DEFAULT CURRENT_TIMESTAMP(5)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
 create table t1 (a timestamp(5) default current_timestamp(5));
 drop table t1;
 create table t1 (a timestamp(5) default current_timestamp(6));
diff --git a/mysql-test/r/user_var-binlog.result b/mysql-test/r/user_var-binlog.result
index 2e1eb15cad4..9f4762776a6 100644
--- a/mysql-test/r/user_var-binlog.result
+++ b/mysql-test/r/user_var-binlog.result
@@ -29,7 +29,7 @@ SET @`a b`:=_latin1 X'68656C6C6F' COLLATE `latin1_swedish_ci`/*!*/;
 use `test`/*!*/;
 SET TIMESTAMP=10000/*!*/;
 SET @@session.pseudo_thread_id=999999999/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
+SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/;
 SET @@session.sql_mode=1342177280/*!*/;
 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 5b21ba57619..1bf740698fe 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -1134,11 +1134,11 @@ create table t1 (a int);
 create view v1 as select * from t1 where a < 2 with check option;
 insert into v1 values(1);
 insert into v1 values(3);
-ERROR HY000: CHECK OPTION failed 'test.v1'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 insert ignore into v1 values (2),(3),(0);
 Warnings:
-Warning	1369	CHECK OPTION failed 'test.v1'
-Warning	1369	CHECK OPTION failed 'test.v1'
+Warning	1369	CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
+Warning	1369	CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 select * from t1;
 a
 1
@@ -1146,20 +1146,20 @@ a
 delete from t1;
 insert into v1 SELECT 1;
 insert into v1 SELECT 3;
-ERROR HY000: CHECK OPTION failed 'test.v1'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 create table t2 (a int);
 insert into t2 values (2),(3),(0);
 insert ignore into v1 SELECT a from t2;
 Warnings:
-Warning	1369	CHECK OPTION failed 'test.v1'
-Warning	1369	CHECK OPTION failed 'test.v1'
+Warning	1369	CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
+Warning	1369	CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 select * from t1 order by a desc;
 a
 1
 0
 update v1 set a=-1 where a=0;
 update v1 set a=2 where a=1;
-ERROR HY000: CHECK OPTION failed 'test.v1'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 select * from t1 order by a desc;
 a
 1
@@ -1174,7 +1174,7 @@ a
 update v1 set a=a+1;
 update ignore v1,t2 set v1.a=v1.a+1 where v1.a=t2.a;
 Warnings:
-Warning	1369	CHECK OPTION failed 'test.v1'
+Warning	1369	CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 select * from t1;
 a
 1
@@ -1188,12 +1188,12 @@ create view v3 as select * from v1 where a > 0 with cascaded check option;
 insert into v2 values (1);
 insert into v3 values (1);
 insert into v2 values (0);
-ERROR HY000: CHECK OPTION failed 'test.v2'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v2'
 insert into v3 values (0);
-ERROR HY000: CHECK OPTION failed 'test.v3'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v3'
 insert into v2 values (2);
 insert into v3 values (2);
-ERROR HY000: CHECK OPTION failed 'test.v3'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v3'
 select * from t1;
 a
 1
@@ -1205,10 +1205,10 @@ create table t1 (a int, primary key (a));
 create view v1 as select * from t1 where a < 2 with check option;
 insert into v1 values (1) on duplicate key update a=2;
 insert into v1 values (1) on duplicate key update a=2;
-ERROR HY000: CHECK OPTION failed 'test.v1'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 insert ignore into v1 values (1) on duplicate key update a=2;
 Warnings:
-Warning	1369	CHECK OPTION failed 'test.v1'
+Warning	1369	CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 select * from t1;
 a
 1
@@ -1263,7 +1263,7 @@ s1
 alter view v2 as select * from t2 where s1 in (select s1 from t1) with check option;
 insert into v2 values (5);
 update v2 set s1 = 1;
-ERROR HY000: CHECK OPTION failed 'test.v2'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v2'
 insert into t1 values (1);
 update v2 set s1 = 1;
 select * from v2;
@@ -1300,16 +1300,16 @@ create table t1 (s1 tinyint);
 create view v1 as select * from t1 where s1 <> 0 with local check option;
 create view v2 as select * from v1 with cascaded check option;
 insert into v2 values (0);
-ERROR HY000: CHECK OPTION failed 'test.v2'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v2'
 drop view v2, v1;
 drop table t1;
 create table t1 (s1 int);
 create view v1 as select * from t1 where s1 < 5 with check option;
 insert ignore into v1 values (6);
-ERROR HY000: CHECK OPTION failed 'test.v1'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 insert ignore into v1 values (6),(3);
 Warnings:
-Warning	1369	CHECK OPTION failed 'test.v1'
+Warning	1369	CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 select * from t1;
 s1
 3
@@ -1319,7 +1319,7 @@ create table t1 (s1 tinyint);
 create trigger t1_bi before insert on t1 for each row set new.s1 = 500;
 create view v1 as select * from t1 where s1 <> 127 with check option;
 insert into v1 values (0);
-ERROR HY000: CHECK OPTION failed 'test.v1'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 select * from v1;
 s1
 select * from t1;
@@ -1331,7 +1331,7 @@ create table t1 (s1 tinyint);
 create view v1 as select * from t1 where s1 <> 0;
 create view v2 as select * from v1 where s1 <> 1 with cascaded check option;
 insert into v2 values (0);
-ERROR HY000: CHECK OPTION failed 'test.v2'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v2'
 select * from v2;
 s1
 select * from t1;
@@ -1341,7 +1341,7 @@ drop table t1;
 create table t1 (a int, b char(10));
 create view v1 as select * from t1 where a != 0 with check option;
 load data infile '../../std_data/loaddata3.dat' into table v1 fields terminated by '' enclosed by '' ignore 1 lines;
-ERROR HY000: CHECK OPTION failed 'test.v1'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 select * from t1;
 a	b
 1	row 1
@@ -1356,10 +1356,10 @@ Warnings:
 Note	1265	Data truncated for column 'a' at row 1
 Note	1265	Data truncated for column 'a' at row 2
 Warning	1366	Incorrect integer value: 'error      ' for column 'a' at row 3
-Warning	1369	CHECK OPTION failed 'test.v1'
+Warning	1369	CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 Note	1265	Data truncated for column 'a' at row 3
 Warning	1366	Incorrect integer value: 'wrong end  ' for column 'a' at row 4
-Warning	1369	CHECK OPTION failed 'test.v1'
+Warning	1369	CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 select * from t1 order by a,b;
 a	b
 1	row 1
@@ -1375,7 +1375,7 @@ drop table t1;
 create table t1 (a text, b text);
 create view v1 as select * from t1 where a <> 'Field A' with check option;
 load data infile '../../std_data/loaddata2.dat' into table v1 fields terminated by ',' enclosed by '''';
-ERROR HY000: CHECK OPTION failed 'test.v1'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 select concat('|',a,'|'), concat('|',b,'|') from t1;
 concat('|',a,'|')	concat('|',b,'|')
 select concat('|',a,'|'), concat('|',b,'|') from v1;
@@ -1383,7 +1383,7 @@ concat('|',a,'|')	concat('|',b,'|')
 delete from t1;
 load data infile '../../std_data/loaddata2.dat' ignore into table v1 fields terminated by ',' enclosed by '''';
 Warnings:
-Warning	1369	CHECK OPTION failed 'test.v1'
+Warning	1369	CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 Warning	1261	Row 2 doesn't contain data for all columns
 select concat('|',a,'|'), concat('|',b,'|') from t1;
 concat('|',a,'|')	concat('|',b,'|')
@@ -3047,9 +3047,9 @@ CREATE VIEW v1 AS SELECT id, val FROM t1 WHERE val >= 1 AND val <= 5 WITH CHECK
 INSERT INTO v1 (val) VALUES (2);
 INSERT INTO v1 (val) VALUES (4);
 INSERT INTO v1 (val) VALUES (6);
-ERROR HY000: CHECK OPTION failed 'test.v1'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 UPDATE v1 SET val=6 WHERE id=2;
-ERROR HY000: CHECK OPTION failed 'test.v1'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 DROP VIEW v1;
 DROP TABLE t1;
 DROP VIEW IF EXISTS v1, v2;
@@ -3135,7 +3135,7 @@ b
 1
 2
 UPDATE v1 SET b=3;
-ERROR HY000: CHECK OPTION failed 'test.v1'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 SELECT * FROM v1;
 b
 1
@@ -3467,14 +3467,14 @@ a1	c
 1	0
 2	0
 UPDATE v1 SET c=3;
-ERROR HY000: CHECK OPTION failed 'test.v1'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 PREPARE t FROM 'UPDATE v1 SET c=3';
 EXECUTE t;
-ERROR HY000: CHECK OPTION failed 'test.v1'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 EXECUTE t;
-ERROR HY000: CHECK OPTION failed 'test.v1'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 INSERT INTO v1(a1, c) VALUES (3, 3);
-ERROR HY000: CHECK OPTION failed 'test.v1'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v1'
 UPDATE v1 SET c=1 WHERE a1=1;
 SELECT * FROM v1;
 a1	c
@@ -3493,14 +3493,14 @@ a1	c
 1	1
 2	0
 UPDATE v2 SET c=3;
-ERROR HY000: CHECK OPTION failed 'test.v2'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v2'
 PREPARE t FROM 'UPDATE v2 SET c=3';
 EXECUTE t;
-ERROR HY000: CHECK OPTION failed 'test.v2'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v2'
 EXECUTE t;
-ERROR HY000: CHECK OPTION failed 'test.v2'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v2'
 INSERT INTO v2(a1, c) VALUES (3, 3);
-ERROR HY000: CHECK OPTION failed 'test.v2'
+ERROR HY000: CONSTRAINT 'WITH CHECK OPTION' failed for 'test.v2'
 UPDATE v2 SET c=2 WHERE a1=1;
 SELECT * FROM v2;
 a1	c
author	Michael Widenius <monty@mariadb.org>	2016-06-29 09:14:22 +0200
committer	Sergei Golubchik <serg@mariadb.org>	2016-06-30 11:43:02 +0200
commit	db7edfed17efe6bc3684b0fbacc0b0249e4f0fa2 (patch)
tree	f1f484057487a73d32f379a5fdaacd53bfa27b5e /mysql-test/r
parent	23d03a1b1e486da353f20964a1b91068bec209c0 (diff)
download	mariadb-git-db7edfed17efe6bc3684b0fbacc0b0249e4f0fa2.tar.gz