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

1 files changed, 54 insertions, 74 deletions

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