Create 'main' test directory and move 't' and 'r' there

1 files changed, 0 insertions, 2106 deletions

diff --git a/mysql-test/t/default.test b/mysql-test/t/default.test
deleted file mode 100644
index 44778bc568a..00000000000
--- a/mysql-test/t/default.test
+++ /dev/null
@@ -1,2106 +0,0 @@
-#
-# test of already fixed bugs
-#
---disable_warnings
-drop table if exists t1,t2,t3,t4,t5,t6;
-drop database if exists mysqltest;
-
-#
-# Bug 10838
-# Insert causes warnings for no default values and corrupts tables
-#
-set sql_mode="";
-CREATE TABLE t1 (a varchar(30) binary NOT NULL DEFAULT ' ',
-                 b varchar(1) binary NOT NULL DEFAULT ' ',
-		 c varchar(4) binary NOT NULL DEFAULT '0000',
-		 d tinyblob NULL,
-		 e tinyblob NULL,
-		 f tinyblob NULL,
-		 g tinyblob NULL,
-		 h tinyblob NULL,
-		 i tinyblob NULL,
-		 j tinyblob NULL,
-		 k tinyblob NULL,
-		 l tinyblob NULL,
-		 m tinyblob NULL,
-		 n tinyblob NULL,
-		 o tinyblob NULL,
-		 p tinyblob NULL,
-                 q varchar(30) binary NOT NULL DEFAULT ' ',
-                 r varchar(30) binary NOT NULL DEFAULT ' ',
-		 s tinyblob NULL,
-                 t varchar(4) binary NOT NULL DEFAULT ' ',
-                 u varchar(1) binary NOT NULL DEFAULT ' ',
-                 v varchar(30) binary NOT NULL DEFAULT ' ',
-                 w varchar(30) binary NOT NULL DEFAULT ' ',
-		 x tinyblob NULL,
-                 y varchar(5) binary NOT NULL DEFAULT ' ',
-                 z varchar(20) binary NOT NULL DEFAULT ' ',
-                 a1 varchar(30) binary NOT NULL DEFAULT ' ',
-		 b1 tinyblob NULL)
-ENGINE=InnoDB DEFAULT CHARACTER SET = latin1 COLLATE latin1_bin;
---enable_warnings
-set sql_mode=default;
-
-INSERT into t1 (b) values ('1');
-SHOW WARNINGS;
-SELECT * from t1;
-
-CREATE TABLE t2 (a varchar(30) binary NOT NULL DEFAULT ' ',
-                 b varchar(1) binary NOT NULL DEFAULT ' ',
-		 c varchar(4) binary NOT NULL DEFAULT '0000',
-		 d tinyblob NULL,
-		 e tinyblob NULL,
-		 f tinyblob NULL,
-		 g tinyblob NULL,
-		 h tinyblob NULL,
-		 i tinyblob NULL,
-		 j tinyblob NULL,
-		 k tinyblob NULL,
-		 l tinyblob NULL,
-		 m tinyblob NULL,
-		 n tinyblob NULL,
-		 o tinyblob NULL,
-		 p tinyblob NULL,
-                 q varchar(30) binary NOT NULL DEFAULT ' ',
-                 r varchar(30) binary NOT NULL DEFAULT ' ',
-		 s tinyblob NULL,
-                 t varchar(4) binary NOT NULL DEFAULT ' ',
-                 u varchar(1) binary NOT NULL DEFAULT ' ',
-                 v varchar(30) binary NOT NULL DEFAULT ' ',
-                 w varchar(30) binary NOT NULL DEFAULT ' ',
-		 x tinyblob NULL,
-                 y varchar(5) binary NOT NULL DEFAULT ' ',
-                 z varchar(20) binary NOT NULL DEFAULT ' ',
-                 a1 varchar(30) binary NOT NULL DEFAULT ' ',
-		 b1 tinyblob NULL)
-ENGINE=MyISAM DEFAULT CHARACTER SET = latin1 COLLATE latin1_bin;
-
-SHOW CREATE TABLE t2;
-INSERT into t2 (b) values ('1');
-SHOW WARNINGS;
-SELECT * from t2;
-
-drop table t1;
-drop table t2;
-
-
-#
-# Bug#20691: DATETIME col (NOT NULL, NO DEFAULT) may insert garbage when specifying DEFAULT
-#
-# From the docs:
-#  If the column can take NULL as a value, the column is defined with an
-#  explicit DEFAULT NULL clause. This is the same as before 5.0.2.
-#
-#  If the column cannot take NULL as the value, MySQL defines the column with
-#  no explicit DEFAULT clause. For data entry, if an INSERT or REPLACE
-#  statement includes no value for the column, MySQL handles the column
-#  according to the SQL mode in effect at the time:
-#
-#    * If strict SQL mode is not enabled, MySQL sets the column to the
-#      implicit default value for the column data type.
-#
-#    * If strict mode is enabled, an error occurs for transactional tables and
-#      the statement is rolled back. For non-transactional tables, an error
-#      occurs, but if this happens for the second or subsequent row of a
-#      multiple-row statement, the preceding rows will have been inserted.
-#
-create table bug20691 (i int, d datetime NOT NULL, dn datetime not null default '0000-00-00 00:00:00');
-insert ignore into bug20691 values (1, DEFAULT, DEFAULT), (1, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (1, DEFAULT, DEFAULT);
-insert ignore into bug20691 (i) values (2);
-desc bug20691;
-insert ignore into bug20691 values (3, DEFAULT, DEFAULT), (3, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (3, DEFAULT, DEFAULT);
-insert ignore into bug20691 (i) values (4);
-insert ignore into bug20691 values (5, DEFAULT, DEFAULT), (5, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (5, DEFAULT, DEFAULT);
-SET sql_mode = 'ALLOW_INVALID_DATES';
-insert into bug20691 values (6, DEFAULT, DEFAULT), (6, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (6, DEFAULT, DEFAULT);
-SET sql_mode = 'STRICT_ALL_TABLES';
---error 1364
-insert into bug20691 values (7, DEFAULT, DEFAULT), (7, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (7, DEFAULT, DEFAULT);
-select * from bug20691 order by i asc;
-drop table bug20691;
-
-SET sql_mode = '';
-create table bug20691 (
-  a set('one', 'two', 'three') not null,
-  b enum('small', 'medium', 'large', 'enormous', 'ellisonego') not null,
-  c time not null,
-  d date not null,
-  e int not null,
-  f long not null,
-  g blob not null,
-  h datetime not null,
-  i decimal not null,
-  x int);
-insert into bug20691 values (2, 3, 5, '0007-01-01', 11, 13, 17, '0019-01-01 00:00:00', 23, 1);
-insert into bug20691 (x) values (2);
-insert into bug20691 values (2, 3, 5, '0007-01-01', 11, 13, 17, '0019-01-01 00:00:00', 23, 3);
-insert into bug20691 values (DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, 4);
-select * from bug20691 order by x asc;
-drop table bug20691;
-
-create table t1 (id int not null);
-insert into t1 values(default);
-
-create view v1 (c) as select id from t1;
-insert into t1 values(default);
-drop view v1;
-drop table t1;
-
-#
-# Bug #39002: crash with
-#             INSERT ... SELECT ... ON DUPLICATE KEY UPDATE col=DEFAULT
-#
-
-create table t1 (a int unique);
-create table t2 (b int default 10);
-insert into t1 (a) values (1);
-insert into t2 (b) values (1);
-
-insert into t1 (a) select b from t2 on duplicate key update a=default;
-select * from t1;
-
-insert into t1 (a) values (1);
-insert into t1 (a) select b from t2 on duplicate key update a=default(b);
-select * from t1;
-
-drop table t1, t2;
-
---echo # End of 5.0 tests
-
---echo #
---echo # Start of 10.0 tests
---echo #
-
---echo #
---echo # MDEV-11265 Access defied when CREATE VIIEW v1 AS SELECT DEFAULT(column) FROM t1
---echo #
-
-CREATE TABLE t1 (a INT DEFAULT 10);
-INSERT INTO t1 VALUES (11);
-CREATE VIEW v1 AS SELECT a AS a FROM t1;
-CREATE VIEW v2 AS SELECT DEFAULT(a) AS a FROM t1;
-CREATE VIEW v3 AS SELECT VALUE(a) AS a FROM t1;
-SELECT * FROM v1;
-SELECT * FROM v2;
-SELECT * FROM v3;
---error ER_NONUPDATEABLE_COLUMN
-UPDATE v2 SET a=123;
---error ER_NONUPDATEABLE_COLUMN
-UPDATE v3 SET a=123;
-DROP VIEW v3;
-DROP VIEW v2;
-DROP VIEW v1;
-DROP TABLE t1;
-
---echo #
---echo # MDEV-10780 Server crashes in in create_tmp_table
---echo #
-
-# Note, the problem was not repeatable with a non-fresh connection.
---connect (con1,localhost,root,,test)
-CREATE TABLE t1 (pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=MyISAM;
-INSERT INTO t1 VALUES ();
-INSERT INTO t1 VALUES ();
-SELECT DISTINCT DEFAULT (pk) FROM t1 GROUP BY RAND() WITH ROLLUP;
---disconnect con1
---connection default
-DROP TABLE t1;
-
---echo #
---echo # End of 10.0 tests
---echo #
-
---echo #
---echo # Start of 10.1 tests
---echo #
-
-# Using DEFAULT(col) in WHERE condition
-CREATE TABLE t1 (a INT DEFAULT 100, b INT DEFAULT NULL);
-INSERT INTO t1 VALUES ();
-SELECT * FROM t1 WHERE DEFAULT(a);
-SELECT * FROM t1 WHERE DEFAULT(b);
-DROP TABLE IF EXISTS t1;
-
-
---echo #
---echo # End of 10.1 tests
---echo #
-
---echo #
---echo # Start of 10.2 tests
---echo #
-
-SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30.123456');
-
---echo #
---echo # Check that CURRENT_TIMESTAMP works as before
---echo #
-
-CREATE or replace TABLE t1 (event_time TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
-SHOW CREATE TABLE t1;
-
-CREATE or replace TABLE t1 (event_time TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(2) ON UPDATE CURRENT_TIMESTAMP);
-SHOW CREATE TABLE t1;
-
-CREATE or replace TABLE t1 (event_time TIMESTAMP(6) NOT NULL DEFAULT SYSDATE(2) ON UPDATE CURRENT_TIMESTAMP);
-SHOW CREATE TABLE t1;
-
-drop table t1;
-
---echo #
---echo # Check default expressions
---echo #
-
-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;
-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,400);
-select * from t1;
-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,400);
-flush tables t1;
-select * from t1;
-
-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;
-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;
-drop table t1;
-
-create or replace table t1 (a bigint default uuid_short());
-insert into t1 values();
-select a > 0 from t1;
-drop table t1;
-
-create or replace table t1 (param_list int DEFAULT (1+1) NOT NULL);
---error ER_PARSE_ERROR
-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 from t1;
-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,f,g,h from t1;
-show create table t1;
-
-create table t2 like t1;
-show create table t2;
-insert into t2 (a) values (100);
-select a,b,c,d,e,f,g,h from t2;
-drop table t1,t2;
-
-create table t1 (a int default (1----1), b int default - 1, c int default +1, e int default (--1));
-show create table t1;
-insert into t1 values();
-insert into t1 values();
-select * from t1;
-drop table t1;
-
---echo #
---echo # Create or replace can delete a table on error
---echo #
-create table t1 (a int);
---error ER_EXPRESSION_REFERS_TO_UNINIT_FIELD
-create or replace table t1 (a int default b, b int default a);
---error ER_NO_SUCH_TABLE
-show create table t1;
-
---echo #
---echo # Refering to other columns
---echo #
-
-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));
-
-
---error ER_EXPRESSION_REFERS_TO_UNINIT_FIELD
-create or replace table t1 (a int default a);
-create or replace table t1 (a int default b, b int default (1+1));
---error ER_EXPRESSION_REFERS_TO_UNINIT_FIELD
-create or replace table t1 (a int default 1, b int as (c), c int as (a+1));
---error ER_EXPRESSION_REFERS_TO_UNINIT_FIELD
-CREATE TABLE t1 (a INT DEFAULT (DEFAULT(a)));
---error ER_EXPRESSION_REFERS_TO_UNINIT_FIELD
-CREATE TABLE t1 (a INT DEFAULT(DEFAULT(b)), b INT DEFAULT(DEFAULT(a)));
---error ER_EXPRESSION_REFERS_TO_UNINIT_FIELD
-CREATE TABLE t1 (a INT DEFAULT(DEFAULT(b)) NOT NULL, b INT DEFAULT(DEFAULT(a)) NOT NULL);
-
---echo #
---echo # Allow defaults to refer to not default fields
---echo #
-
-create or replace table t1 (a int default b, b int not null);
-insert into t1 values();
-insert into t1 (a) values(1);
-insert into t1 (b) values(2);
-insert into t1 (a,b) values(3,4);
-select * from t1;
-drop table t1;
-CREATE OR REPLACE TABLE t1 (a INT DEFAULT @v); drop table t1;
-CREATE TABLE t1 (a INT DEFAULT @v:=1); drop table t1;
-
---echo #
---echo # Error handling
---echo #
-
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create or replace table t1 (a bigint default xxx());
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-create or replace table t1 (a bigint default (select (1)));
---error ER_OPERAND_COLUMNS
-create or replace table t1 (a bigint default (1,2,3));
---error ER_OPERAND_COLUMNS
-create or replace table t1 (a bigint default ((1,2,3)));
---error ER_PARSE_ERROR
-CREATE TABLE t1 (a INT, b INT, c INT DEFAULT a DIV b);
---error ER_PARSE_ERROR
-CREATE TABLE t1 (a INT, b INT DEFAULT -a);
-
---echo #
---echo # Invalid DEFAULT expressions
---echo #
-
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-CREATE TABLE t1 (a INT DEFAULT ((SELECT 1)));
-
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-CREATE TABLE t1 (a INT DEFAULT (EXISTS (SELECT 1)));
-
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-CREATE TABLE t1 (a INT DEFAULT (1=ANY (SELECT 1)));
-
---error ER_OPERAND_COLUMNS
-CREATE TABLE t1 (a INT DEFAULT ROW(1,1));
-
---error ER_OPERAND_COLUMNS
-CREATE TABLE t1 (a INT DEFAULT (1,1));
-
---error ER_OPERAND_COLUMNS
-CREATE TABLE t1 (a INT DEFAULT ((1,1)));
-
---error ER_PARSE_ERROR,2031
-CREATE TABLE t1 (a INT DEFAULT ?);
---error ER_PARSE_ERROR,2031
-CREATE TABLE t1 (a INT DEFAULT(?));
-
---error ER_EXPRESSION_REFERS_TO_UNINIT_FIELD
-CREATE TABLE t1 (a INT DEFAULT (b), b INT DEFAULT(a));
-
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-CREATE TABLE t1 (a INT DEFAULT(NAME_CONST('xxx', 'yyy'));
-
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-CREATE TABLE t1 (a INT DEFAULT COUNT(*));
-
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-CREATE TABLE t1 (a INT DEFAULT COUNT(1));
-
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-CREATE TABLE t1 (a INT DEFAULT AVG(1));
-
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-CREATE TABLE t1 (a INT DEFAULT MIN(1));
-
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-CREATE TABLE t1 (a INT DEFAULT GROUP_CONCAT(1));
-
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-CREATE TABLE t1 (a INT DEFAULT ROW_NUMBER() OVER ());
-
-CREATE FUNCTION f1() RETURNS INT RETURN 1;
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-CREATE TABLE t1 (a INT DEFAULT f1());
-DROP FUNCTION f1;
-
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-CREATE PROCEDURE p1(par INT) CREATE TABLE t1 (a INT DEFAULT par);
-
---error ER_BAD_FIELD_ERROR
-CREATE TABLE t1 (a INT DEFAULT par);
-
-CREATE PROCEDURE p1() CREATE TABLE t1 (a INT DEFAULT par);
---error ER_BAD_FIELD_ERROR
-CALL p1;
-DROP PROCEDURE p1;
-
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-CREATE TABLE t1 (a INT DEFAULT VALUE(a));
-
-
-CREATE TABLE t1 (a INT);
-# "Explicit or implicit commit is not allowed in stored function or trigger
-# because the entire CREATE TABLE is actually not allowed in triggers!
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW CREATE TABLE t2 (a INT DEFAULT NEW.a);
-# This is OK to return Function or expression is not allowed for 'DEFAULT'
-# because CREATE TEMPORARY TABLE is allowed in triggers
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW CREATE TEMPORARY TABLE t2 (a INT DEFAULT NEW.a);
-DROP TABLE t1;
-
---echo #
---echo # Prepared statements
---echo #
-
-PREPARE stmt FROM 'CREATE TABLE t1 (a INT DEFAULT(?))';
-set @a=1;
-execute stmt using @a;
-show create table t1;
-drop table t1;
-set @a=-1;
-execute stmt using @a;
-show create table t1;
-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;
-drop table t1;
-DEALLOCATE PREPARE stmt;
-
-#
-# We can't have an expression for prepared statements
-#
-
-prepare stmt from 'create table t1 (a int default(?+?))';
-set @a=1;
-execute stmt using @a,@a;
-deallocate prepare stmt;
-show create table t1;
-drop table t1;
-
---echo #
---echo # Parenthesized Item_basic_constant
---echo #
-
-
-# It would be better if SHOW would display PI() rather than '3.141592653589793'
-# The problem is that PI() is declared as a basic constant item and it
-# could cause some problems changing it.
-
-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;
-INSERT INTO t1 VALUES ();
---vertical_results
-SELECT * FROM t1;
---horizontal_results
-DROP TABLE t1;
-
-
---echo #
---echo # COALESCE(Item_basic_constant)
---echo #
-
-# i02 INT DEFAULT 0x3939           -- gives 14649 (see the previous query),
-#                                     because it treats as a number
-# i02 INT DEFAULT COALESCE(0x3939) -- gives 99, because it converts to string
-#
-# should be at least documented
-
-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;
-INSERT INTO t1 VALUES ();
---vertical_results
-SELECT * FROM t1;
---horizontal_results
-DROP TABLE t1;
-
-
---echo #
---echo # TINYINT: out of range
---echo #
---error ER_INVALID_DEFAULT
-CREATE TABLE t1 (a TINYINT DEFAULT 300 NOT NULL);
---error ER_INVALID_DEFAULT
-CREATE TABLE t1 (a TINYINT DEFAULT 128 NOT NULL);
---error ER_INVALID_DEFAULT
-CREATE TABLE t1 (a TINYINT DEFAULT -500 NOT NULL);
-
---echo #
---echo # INT: simple numeric expressions
---echo #
-CREATE TABLE t1 (a INT DEFAULT 1 NOT NULL);
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT DEFAULT COALESCE(1) NOT NULL);
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
---echo #
---echo # INT: simple string expressions 
---echo #
-
-CREATE TABLE t1 (a INT DEFAULT '1' NOT NULL);
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT DEFAULT CONCAT('1') NOT NULL);
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT DEFAULT COALESCE('1') NOT NULL);
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
---echo #
---echo # INT: string expressions with garbage
---echo #
---error ER_INVALID_DEFAULT
-CREATE TABLE t1 (a INT DEFAULT 'x');
---error ER_INVALID_DEFAULT
-CREATE TABLE t1 (a INT DEFAULT CONCAT('x'));
---error ER_INVALID_DEFAULT
-CREATE TABLE t1 (a INT DEFAULT COALESCE('x'));
---error ER_INVALID_DEFAULT
-CREATE TABLE t1 (a INT DEFAULT (((((COALESCE('x')))))));
-
---echo #
---echo # INT: string expressions with numbers + garbage
---echo #
-
---error ER_INVALID_DEFAULT
-CREATE TABLE t1 (a INT DEFAULT '1x');
---error ER_INVALID_DEFAULT
-CREATE TABLE t1 (a INT DEFAULT COALESCE('1x'));
---error ER_INVALID_DEFAULT
-CREATE TABLE t1 (a INT DEFAULT CONCAT('1x'));
-
---echo #
---echo # INT: string expressions with numbers + trailing space
---echo #
-
-CREATE TABLE t1 (a INT DEFAULT '1 ');
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-# unlike constant, this preserve trailing spaces
-# and sends a note on INSERT. Perhaps CREATE should be rejected
-CREATE TABLE t1 (a INT DEFAULT CONCAT('1 '));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-# unlike constant, this preserve trailing spaces
-# and sends a note on INSERT
-CREATE TABLE t1 (a INT DEFAULT COALESCE('1 '));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
---echo #
---echo # INT: a HEX value
---echo #
-CREATE TABLE t1 (a INT DEFAULT 0x61 NOT NULL);
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-
---echo #
---echo # VARCHAR: good defaults
---echo #
-CREATE TABLE t1 (a VARCHAR(30) DEFAULT 'xxx' NOT NULL);
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(2) DEFAULT 0x41 NOT NULL);
-SHOW CREATE TABLE t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(2) DEFAULT CONCAT(0x41) NOT NULL);
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(2) DEFAULT COALESCE(0x41) NOT NULL);
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(2) DEFAULT CONCAT(_utf8 0x41) NOT NULL);
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(2) DEFAULT CONCAT(_utf8 X'41') NOT NULL);
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-
---echo #
---echo # VARCHAR: Too long default
---echo #
---error ER_INVALID_DEFAULT
-CREATE TABLE t1 (a VARCHAR(2) DEFAULT 'xxx' NOT NULL);
---error ER_INVALID_DEFAULT
-CREATE TABLE t1 (a VARCHAR(2) DEFAULT CONCAT('xxx') NOT NULL);
-
---echo #
---echo # VARCHAR: Too long default with non-important data
---echo #
---error ER_INVALID_DEFAULT
-CREATE TABLE t1 (a VARCHAR(2) DEFAULT 'xx ' NOT NULL);
---error ER_INVALID_DEFAULT
-CREATE TABLE t1 (a VARCHAR(2) DEFAULT CONCAT('xx ') NOT NULL);
-
---echo #
---echo # VARCHAR: conversion failures
---echo #
-
-# DEFAULT with a Cyrillic letter for a Latin1 column
---error ER_INVALID_DEFAULT
-CREATE TABLE t1 (a VARCHAR(2) CHARACTER SET latin1 DEFAULT _utf8 X'D18F' NOT NULL);
---error ER_INVALID_DEFAULT
-CREATE TABLE t1 (a VARCHAR(2) CHARACTER SET latin1 DEFAULT CONCAT(_utf8 X'D18F') NOT NULL);
---error ER_INVALID_DEFAULT
-CREATE TABLE t1 (a VARCHAR(2) CHARACTER SET latin1 DEFAULT CONCAT(_utf8 0xD18F) NOT NULL);
-
---echo #
---echo # Field as a default value
---echo #
-
-CREATE TABLE t1 (a INT, b INT DEFAULT (a));
-SHOW CREATE TABLE t1;
-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;
-DROP TABLE t1;
-
---echo #
---echo # Function DEFAULT(field)
---echo #
-
-CREATE TABLE t1 (a INT DEFAULT(DEFAULT(b)), b INT DEFAULT 1);
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (DEFAULT, DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT DEFAULT 1, b INT DEFAULT(DEFAULT(a)));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (DEFAULT, DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
---echo #
---echo # SQL Standard <datetime value function> as a <default option>
---echo #
-
-CREATE TABLE t1 (a DATETIME DEFAULT CURRENT_TIMESTAMP);
-SHOW CREATE TABLE t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a TIME DEFAULT CURRENT_TIME);
-SHOW CREATE TABLE t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a DATE DEFAULT CURRENT_DATE);
-SHOW CREATE TABLE t1;
-DROP TABLE t1;
-
---echo #
---echo # DECIMAL + CURRENT_TIMESTAMP, no truncation
---echo #
-CREATE TABLE t1 (a DECIMAL(30,6) DEFAULT CURRENT_TIMESTAMP(6));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES ();
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a DECIMAL(30,6) DEFAULT COALESCE(CURRENT_TIMESTAMP(6)));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES();
-INSERT IGNORE INTO t1 VALUES();
-SET sql_mode = 'STRICT_ALL_TABLES';
-INSERT INTO t1 VALUES();
-SET sql_mode = DEFAULT;
-DROP TABLE t1;
-
---echo #
---echo # DECIMAL + CURRENT_TIME, no truncation
---echo #
-CREATE TABLE t1 (a DECIMAL(30,6) DEFAULT COALESCE(CURRENT_TIME(6)));
-SHOW CREATE TABLE t1;
-INSERT IGNORE INTO t1 VALUES();
-DROP TABLE t1;
-
---echo #
---echo # DECIMAL + CURRENT_DATE, no truncation
---echo #
-CREATE TABLE t1 (a DECIMAL(30,6) DEFAULT COALESCE(CURRENT_DATE));
-SHOW CREATE TABLE t1;
-INSERT IGNORE INTO t1 VALUES();
-DROP TABLE t1;
-
-
---echo #
---echo # COALESCE for SQL Standard <datetime value function>
---echo #
-
-CREATE TABLE t1 (a TIMESTAMP DEFAULT COALESCE(CURRENT_TIMESTAMP));
-SHOW CREATE TABLE t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a DATE DEFAULT COALESCE(CURRENT_DATE));
-SHOW CREATE TABLE t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a TIME DEFAULT COALESCE(CURRENT_TIME));
-SHOW CREATE TABLE t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (
-  a TIMESTAMP DEFAULT CURRENT_TIMESTAMP(6),
-  b TIMESTAMP DEFAULT COALESCE(CURRENT_TIMESTAMP(6))
-);
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES ();
-SELECT CURRENT_TIMESTAMP(6);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (
-  a DECIMAL(30,0) DEFAULT CURRENT_TIMESTAMP(6),
-  b DECIMAL(30,0) DEFAULT COALESCE(CURRENT_TIMESTAMP(6))
-);
-SHOW CREATE TABLE t1;
-INSERT IGNORE INTO t1 VALUES ();
-SELECT * FROM t1;
-DROP TABLE t1;
-
---echo #
---echo # Check DEFAULT() function
---echo #
-
-CREATE TABLE `t1` (`a` int(11) DEFAULT (3+3),`b` int(11) DEFAULT '1000');
-SHOW CREATE TABLE t1;
-insert into t1 values (1,1),(2,2);
-insert into t1 values (default,default);
-select * from t1;
-select default(a),b from t1;
-select a,default(b) from t1;
-drop table t1;
-
-
---echo #
---echo # Real functions
---echo #
-
-CREATE TABLE t1 (a DECIMAL(10,1), b DOUBLE DEFAULT CAST(a AS DOUBLE));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (10.1, DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a DOUBLE, b DOUBLE DEFAULT EXP(a), c DOUBLE DEFAULT LOG(b), d DOUBLE DEFAULT LOG(4, b));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (2, DEFAULT, DEFAULT, DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b DOUBLE DEFAULT LOG2(a), c DOUBLE DEFAULT LOG10(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (4, DEFAULT, DEFAULT);
-INSERT INTO t1 VALUES (100, DEFAULT, DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-
-CREATE TABLE t1 (a DOUBLE, b DOUBLE DEFAULT SQRT(a), c DOUBLE DEFAULT POW(a,3));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (4, DEFAULT, DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a DOUBLE, b DOUBLE DEFAULT ACOS(a), c DOUBLE DEFAULT ASIN(a), d DOUBLE DEFAULT ATAN(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (1, DEFAULT, DEFAULT, DEFAULT);
-SELECT a, b/PI(), c/PI(), d/PI() FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a DOUBLE, b DOUBLE DEFAULT COS(a), c DOUBLE DEFAULT SIN(a), d DOUBLE DEFAULT TAN(a), e DOUBLE DEFAULT COT(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES (PI()/3);
-SELECT ROUND(a,3), ROUND(b,3), ROUND(c,3), ROUND(d,3), ROUND(e,3) FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a DOUBLE DEFAULT RAND());
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (DEFAULT);
-DROP TABLE t1;
-
-CREATE TABLE t1 (a DOUBLE, b DOUBLE DEFAULT DEGREES(a), c DOUBLE DEFAULT RADIANS(b));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (PI(), DEFAULT, DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
---echo #
---echo # INT result functions
---echo #
-
-CREATE TABLE t1 (a INT, b INT DEFAULT INTERVAL(a, 10, 20, 30, 40));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES (34);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a DIV b));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a, b) VALUES (13, 3);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b INT DEFAULT SIGN(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES (-10),(0), (10);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(30), b INT DEFAULT FIELD(a, 'Hej', 'ej', 'Heja', 'hej', 'foo'));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('ej');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(30), b INT DEFAULT FIND_IN_SET(a, 'Hej,ej,Heja,hej,foo'));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('ej');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(30), b INT DEFAULT ASCII(a), c INT DEFAULT ORD(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('a');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a TEXT DEFAULT UUID_SHORT());
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES ();
-SELECT a>0 FROM t1;
-DROP TABLE t1;
-
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-CREATE TABLE t1 (a INT DEFAULT BENCHMARK(1,1));
-
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-CREATE TABLE t1 (a INT DEFAULT GET_LOCK('a',1));
-
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-CREATE TABLE t1 (a INT DEFAULT RELEASE_LOCK('a'));
-
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-CREATE TABLE t1 (a INT DEFAULT IS_USED_LOCK('a'));
-
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-CREATE TABLE t1 (a INT DEFAULT IS_FREE_LOCK('a'));
-
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-CREATE TABLE t1 (a INT DEFAULT SLEEP(1));
-
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-CREATE TABLE t1 (a INT DEFAULT ROW_COUNT());
-
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-CREATE TABLE t1 (a INT DEFAULT FOUND_ROWS());
-
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-CREATE TABLE t1 (a INT DEFAULT MASTER_POS_WAIT('test',100));
-
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-CREATE TABLE t1 (a INT DEFAULT MASTER_GTID_WAIT('test'));
-
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-CREATE TABLE t1 (a VARCHAR(30), b DOUBLE DEFAULT MATCH (a) AGAINST('bbbb' IN BOOLEAN MODE));
-
---echo #
---echo # Temporal functions
---echo #
-
---echo # Item_temporal_hybrid_func
-
-CREATE TABLE t1 (a DATE, b INT, c DATE DEFAULT DATE_ADD(a, INTERVAL b DAY));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES ('2001-01-01', 30, DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a DATE, b TIME, c DATETIME DEFAULT ADDTIME(a, b));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES ('2001-01-01', '10:20:30', DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(32), b VARCHAR(32), c DATE DEFAULT STR_TO_DATE(a,b));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES ('01,5,2013','%d,%m,%Y', DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
---echo # Item_datefunc
-
-SET time_zone='-10:00';
-SET timestamp=UNIX_TIMESTAMP('2001-01-01 23:59:59');
-CREATE TABLE t1 (a DATE DEFAULT CURDATE(), b DATE DEFAULT UTC_DATE());
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES ();
-SELECT * FROM t1;
-DROP TABLE t1;
-SET time_zone=DEFAULT, timestamp= DEFAULT;
-
-CREATE TABLE t1 (a INT, b DATE DEFAULT FROM_DAYS(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (730669, DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a DATE, b DATE DEFAULT LAST_DAY(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES ('2003-02-05', DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (yy INT, yd INT, d DATE DEFAULT MAKEDATE(yy, yd));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (2011,32,DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
---echo # Item_timefunc
-
-SET time_zone='-10:00';
-SET timestamp=UNIX_TIMESTAMP('2001-01-01 23:59:59');
-CREATE TABLE t1 (a TIME DEFAULT CURTIME(), b TIME DEFAULT UTC_TIME());
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES ();
-SELECT * FROM t1;
-DROP TABLE t1;
-SET time_zone=DEFAULT, timestamp= DEFAULT;
-
-CREATE TABLE t1 (a INT, b TIME DEFAULT SEC_TO_TIME(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (2378, DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a DATETIME, b DATETIME, c TIME DEFAULT TIMEDIFF(a,b));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES ('2000:01:01 00:00:00', '2000:01:02 10:20:30', DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (hh INT, mm INT, ss INT, t TIME DEFAULT MAKETIME(hh,mm,ss));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (10,20,30,DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-
---echo # Item_datetimefunc
-
-SET time_zone='-10:00';
-SET timestamp=UNIX_TIMESTAMP('2001-01-01 23:59:59');
-CREATE TABLE t1 (a TIMESTAMP DEFAULT NOW(), b TIMESTAMP DEFAULT UTC_TIMESTAMP());
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES ();
-SELECT * FROM t1;
-DROP TABLE t1;
-SET time_zone=DEFAULT, timestamp= DEFAULT;
-
-# SYSDATE is evaluated during get_date() rather than fix_fields.
-CREATE TABLE t1 (a TIMESTAMP(6) DEFAULT SYSDATE(6), s INT, b TIMESTAMP(6) DEFAULT SYSDATE(6));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (DEFAULT(a), SLEEP(0.1), DEFAULT(b));
-SELECT b>a FROM t1;
-DROP TABLE t1;
-
-SET time_zone='+00:00';
-CREATE TABLE t1 (a INT, b TIMESTAMP DEFAULT FROM_UNIXTIME(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (1447430881, DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-SET time_zone=DEFAULT;
-
-CREATE TABLE t1 (a TIMESTAMP, b TIMESTAMP DEFAULT CONVERT_TZ(a, '-10:00', '+10:00'));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES ('2001-01-01 10:20:30', DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
---echo # Item_temporal_typecast
-CREATE TABLE t1 (a INT, b DATE DEFAULT CAST(a AS DATE));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (20010203, DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b TIME DEFAULT CAST(a AS TIME));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (102030, DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a BIGINT, b DATETIME DEFAULT CAST(a AS DATETIME));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (20010203102030, DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-
---echo #
---echo # Functions with temporal input
---echo #
-
-CREATE TABLE t1 (a INT, b INT, c INT DEFAULT PERIOD_ADD(a,b));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a,b) VALUES (200801, 2);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b INT, c INT DEFAULT PERIOD_DIFF(a,b));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a,b) VALUES (200802, 200703);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b INT DEFAULT TO_DAYS(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES (950501);
-SELECT * FROM t1;
-DROP TABLE t1;
-CREATE TABLE t1 (a DATE, b INT DEFAULT TO_DAYS(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('2007-10-07');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b BIGINT DEFAULT TO_SECONDS(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES (950501);
-SELECT * FROM t1;
-DROP TABLE t1;
-CREATE TABLE t1 (a DATE, b BIGINT DEFAULT TO_SECONDS(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('2009-11-29');
-SELECT * FROM t1;
-DROP TABLE t1;
-CREATE TABLE t1 (a DATETIME, b BIGINT DEFAULT TO_SECONDS(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('2009-11-29 13:43:32');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a DATE, b BIGINT DEFAULT DAYOFMONTH(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('2007-02-03');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a DATE, b BIGINT DEFAULT DAYOFWEEK(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('2007-02-03');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a DATE, b BIGINT DEFAULT DAYOFYEAR(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('2007-02-03');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a TIME, b INT DEFAULT HOUR(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('10:05:03');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a TIME, b INT DEFAULT MINUTE(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('10:05:03');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a TIME, b INT DEFAULT SECOND(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('10:05:03');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a DATETIME(6), b INT DEFAULT MICROSECOND(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('2009-12-31 23:59:59.000010');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a DATE, b INT DEFAULT YEAR(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('1987-01-01');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a DATE, b INT DEFAULT MONTH(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('1987-01-01');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a DATE, b INT DEFAULT WEEK(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('1987-02-01');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a DATE, b INT DEFAULT YEARWEEK(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('2000-01-01');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a DATE, b INT DEFAULT QUARTER(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('2008-04-01');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a DATE, b INT DEFAULT EXTRACT(YEAR FROM a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('2009-07-02');
-SELECT * FROM t1;
-DROP TABLE t1;
-CREATE TABLE t1 (a DATETIME, b INT DEFAULT EXTRACT(YEAR_MONTH FROM a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('2009-07-02 01:02:03');
-SELECT * FROM t1;
-DROP TABLE t1;
-CREATE TABLE t1 (a DATETIME, b INT DEFAULT EXTRACT(DAY_MINUTE FROM a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('2009-07-02 01:02:03');
-SELECT * FROM t1;
-DROP TABLE t1;
-CREATE TABLE t1 (a DATETIME(6), b INT DEFAULT EXTRACT(MICROSECOND FROM a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('2009-07-02 01:02:03.000123');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a DATE, b DATE, c INT DEFAULT TIMESTAMPDIFF(MONTH,a,b));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a,b) VALUES ('2003-02-01','2003-05-01');
-SELECT * FROM t1;
-DROP TABLE t1;
-CREATE TABLE t1 (a DATE, b DATE, c INT DEFAULT TIMESTAMPDIFF(YEAR,a,b));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a,b) VALUES ('2002-05-01','2001-01-01');
-SELECT * FROM t1;
-DROP TABLE t1;
-CREATE TABLE t1 (a DATE, b DATETIME, c INT DEFAULT TIMESTAMPDIFF(MINUTE,a,b));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a,b) VALUES ('2003-02-01','2003-05-01 12:05:55');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-#
-# MDEV-10355 Weird error message upon CREATE TABLE with DEFAULT
-#
-# Column default value expression that includes a temporal function
-#
-CREATE OR REPLACE TABLE t1 ( col INT DEFAULT ( 1 LIKE ( NOW() BETWEEN '2000-01-01' AND '2012-12-12' ) ) );
-SHOW CREATE TABLE t1;
-SET timestamp = UNIX_TIMESTAMP( '2004-04-04' );
-INSERT INTO t1 VALUES( DEFAULT );
-SET timestamp = DEFAULT;
-INSERT INTO t1 VALUES( DEFAULT );
-SELECT * FROM t1;
-DROP TABLE t1;
-
-
---echo #
---echo # Hybrid type functions
---echo #
-
-CREATE TABLE t1 (a INT, b INT, c INT DEFAULT COALESCE(a,b));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (NULL, 1, DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-
-CREATE TABLE t1 (a INT, b INT, c INT DEFAULT IFNULL(a,b));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (NULL, 2, DEFAULT);
-INSERT INTO t1 VALUES (1, 2, DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-
-CREATE TABLE t1 (a INT, b INT, c INT DEFAULT NULLIF(a,b));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (1, 1, DEFAULT);
-INSERT INTO t1 VALUES (1, 2, DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b INT, c INT DEFAULT IF(a,b,2));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (0, 1, DEFAULT);
-INSERT INTO t1 VALUES (1, 1, DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b INT, c INT DEFAULT CASE WHEN a THEN b ELSE 2 END);
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (0, 1, DEFAULT);
-INSERT INTO t1 VALUES (1, 1, DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b INT DEFAULT (-a));
-SHOW CREATE TABLE t1;
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (10, DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b INT DEFAULT ABS(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (-10, DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a DOUBLE, b INT DEFAULT CEILING(a), c INT DEFAULT FLOOR(a), d INT DEFAULT ROUND(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (1.5, DEFAULT, DEFAULT, DEFAULT);
-INSERT INTO t1 VALUES (-1.5, DEFAULT, DEFAULT, DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a+b), d INT DEFAULT (a-b));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (2, 1, DEFAULT, DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a*b), d INT DEFAULT (a/b), e INT DEFAULT (a MOD b));
-SHOW CREATE TABLE t1;
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (7, 3, DEFAULT, DEFAULT, DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-SET time_zone='+00:00';
-CREATE TABLE t1 (a DATETIME, b INT DEFAULT UNIX_TIMESTAMP(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES ('2001-01-01 10:20:30', DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-SET time_zone=DEFAULT;
-
-CREATE TABLE t1 (a TIME, b INT DEFAULT TIME_TO_SEC(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES ('22:23:00', DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b INT, c INT DEFAULT LEAST(a,b), d INT DEFAULT GREATEST(a,b));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (0, 1, DEFAULT, DEFAULT);
-INSERT INTO t1 VALUES (1, 1, DEFAULT, DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b INT, c INT DEFAULT LAST_VALUE(a,b));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (1, 2, DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
---echo #
---echo # CAST
---echo #
-CREATE TABLE t1 (a VARCHAR(30), b DECIMAL(10,6) DEFAULT CAST(a AS DECIMAL(10,1)));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('123.456');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a DECIMAL(10,3),
-                 b VARCHAR(10) DEFAULT CAST(a AS CHAR(10)),
-                 c VARCHAR(10) DEFAULT CAST(a AS CHAR(4)));
-SHOW CREATE TABLE t1;
-INSERT IGNORE INTO t1 (a) VALUES (123.456);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b INT UNSIGNED DEFAULT CAST(a AS UNSIGNED));
-SHOW CREATE TABLE t1;
-INSERT IGNORE INTO t1 (a) VALUES (-1);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a BIGINT UNSIGNED, b BIGINT SIGNED DEFAULT CAST(a AS SIGNED));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES (0xFFFFFFFFFFFFFFFF);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (
-  a VARCHAR(10) CHARACTER SET latin1,
-  b VARCHAR(10) CHARACTER SET latin1 DEFAULT a COLLATE latin1_bin,
-  c VARCHAR(10) CHARACTER SET utf8 DEFAULT CONVERT(a USING utf8),
-  d VARBINARY(10) DEFAULT (BINARY(a))
-);
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('a');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-
---echo #
---echo # Bit functions
---echo #
-
-CREATE TABLE t1 (a INT, b INT DEFAULT BIT_COUNT(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES (7);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a|b));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a,b) VALUES (1,2);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a&b));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a,b) VALUES (5,4);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a^b));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a,b) VALUES (11,3);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a&~b));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a,b) VALUES (5,1);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a<<b), d INT DEFAULT (a>>b));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a,b) VALUES (5,1);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-
---echo #
---echo # String functions
---echo #
-
-CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(20) DEFAULT REVERSE(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('abcd');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT UPPER(a), c VARCHAR(10) DEFAULT LOWER(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('ABcd');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT LEFT(a,1), c VARCHAR(10) DEFAULT RIGHT(a,1), d VARCHAR(10) DEFAULT SUBSTR(a,2,2));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('abcd');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20) DEFAULT SUBSTRING_INDEX(a,'.',2));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('www.mariadb.org');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10), c VARCHAR(20) DEFAULT CONCAT(a,b));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a,b) VALUES ('a','b');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10), c VARCHAR(20) DEFAULT CONCAT_WS(',',a,b));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a,b) VALUES ('a','b');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT REPLACE(a,'a','A'));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('abc');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT REGEXP_REPLACE(a,'[0-9]','.'));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('a1b2c');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT REGEXP_SUBSTR(a,'[0-9]+'));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('ab12cd');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20) DEFAULT SOUNDEX(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('tester');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20) DEFAULT QUOTE(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('a\'b');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT LPAD(a,10,'.'), c VARCHAR(10) DEFAULT RPAD(a,10,'.'));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('ab');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT LTRIM(a), c VARCHAR(10) DEFAULT RTRIM(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES (' ab ');
-SELECT a, HEX(b), HEX(c) FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT TRIM(BOTH 'a' FROM a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('abba');
-SELECT a, b FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b VARCHAR(10) DEFAULT SPACE(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES (3);
-SELECT a, HEX(b) FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b VARCHAR(10), c VARCHAR(10) DEFAULT REPEAT(b,a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a,b) VALUES (3,'x');
-SELECT a, b, c FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (str VARCHAR(10), pos INT, len INT, newstr VARCHAR(10), result VARCHAR(10) DEFAULT INSERT(str,pos,len,newstr));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (str,pos,len,newstr) VALUES ('Quadratic', 3, 4, 'What');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (n INT, res VARCHAR(10) DEFAULT ELT(n,'ej', 'Heja', 'hej', 'foo'));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (n) VALUES (1);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (bits INT, res VARCHAR(10) DEFAULT MAKE_SET(bits,'a','b','c','d'));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (bits) VALUES (1|4);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b VARCHAR(10) DEFAULT CHAR(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES (77);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b VARCHAR(10) DEFAULT CONV(a,10,16));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES (64);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b INT, c VARCHAR(30) DEFAULT FORMAT(a,b));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a,b) VALUES (10000,3);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b INT, l VARCHAR(10), c VARCHAR(30) DEFAULT FORMAT(a,b,l));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a,b,l) VALUES (10000,2,'no_NO'),(10000,2,'ru_RU'),(10000,2,'ar_BH');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(20) DEFAULT GET_FORMAT(DATE,a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('EUR'),('USA'),('JIS'),('ISO'),('INTERNAL');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (
-  bits INT,
-  v_on VARCHAR(10),
-  v_off VARCHAR(10),
-  v_separator VARCHAR(10),
-  number_of_bits INT,
-  x VARCHAR(30) DEFAULT EXPORT_SET(bits, v_on, v_off, v_separator, number_of_bits)
-);
-SHOW CREATE TABLE t1;
-INSERT IGNORE INTO t1 VALUES (0x50006,'Y','N','',64,DEFAULT);
-SELECT * FROM t1;
-DROP TABLE t1;
-
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-CREATE TABLE t1 (a VARCHAR(30), b BLOB DEFAULT LOAD_FILE(a));
-
---echo #
---echo # Predicates
---echo #
-
-CREATE TABLE t1 (a INT, b INT DEFAULT (NOT a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES (NULL),(0),(1);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b INT, x INT DEFAULT (a XOR b));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a,b) VALUES (0,0),(0,1),(1,0),(1,1);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b INT DEFAULT (a IS TRUE), c INT DEFAULT (a IS NOT TRUE));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES (NULL),(0),(1);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b INT DEFAULT (a IS FALSE), c INT DEFAULT (a IS NOT FALSE));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES (NULL),(0),(1);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b INT DEFAULT (a IS NULL), c INT DEFAULT (a IS NOT NULL));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES (NULL),(0),(1);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT, b INT DEFAULT (a IS UNKNOWN), c INT DEFAULT (a IS NOT UNKNOWN));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES (NULL),(0),(1);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a INT,
-                 eq INT DEFAULT (a=0), equal INT DEFAULT (a<=>0),
-                 ne INT DEFAULT (a<>0),
-                 lt INT DEFAULT (a<0), le INT DEFAULT (a<=0),
-                 gt INT DEFAULT (a>0), ge INT DEFAULT (a>=0));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES (NULL),(-1),(0),(1);
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a LIKE 'a%'));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a RLIKE 'a$'));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a IN ('aaa','bbb')));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'),('ccc');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a NOT IN ('aaa','bbb')));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'),('ccc');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a BETWEEN 'aaa' AND 'bbb'));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'),('ccc');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a NOT BETWEEN 'aaa' AND 'bbb'));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'),('ccc');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a TEXT DEFAULT UUID());
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES ();
-SELECT LENGTH(a)>0 FROM t1;
-DROP TABLE t1;
-
---echo #
---echo # Numeric result functions with string input
---echo #
-
-CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT STRCMP(a,'b'));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('A'),('a'),('B'),('b'),('C'),('c');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT LENGTH(a), c INT DEFAULT CHAR_LENGTH(a), d INT DEFAULT BIT_LENGTH(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('a'),('aa'),('aaa');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT LOCATE('a',a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('xa'),('xxa'),('xxxa');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT REGEXP_INSTR(a, 'a'));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('xa'),('xxa'),('xxxa');
-SELECT * FROM t1;
-DROP TABLE t1;
-
---echo #
---echo # INT result metadata functions
---echo #
-
-# QQ: LAST_INSERT_ID() should probably be allowed
---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-CREATE TABLE t1
-(
-  id SERIAL PRIMARY KEY,
-  b INT DEFAULT LAST_INSERT_ID()
-);
-
-CREATE TABLE t1 (a INT DEFAULT CONNECTION_ID());
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES();
-SELECT a>0 FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT COERCIBILITY(a), c INT DEFAULT COERCIBILITY(b));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('test');
-SELECT * FROM t1;
-DROP TABLE t1;
-
---echo #
---echo # String result metadata functions
---echo #
-CREATE TABLE t1 (
-   a VARCHAR(10) CHARACTER SET latin1,
-   b VARCHAR(20) DEFAULT CHARSET(a),
-   c VARCHAR(20) DEFAULT COLLATION(a)
-);
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('test');
-SELECT * FROM t1;
-DROP TABLE t1;
-
---echo #
---echo # Hash, compression, encode/decode
---echo #
-CREATE TABLE t1 (a VARCHAR(10), b BIGINT DEFAULT CRC32(a), c TEXT DEFAULT MD5(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('a');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(10), b TEXT DEFAULT TO_BASE64(a), c TEXT DEFAULT FROM_BASE64(b));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('aaaabbbb');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(10), b TEXT DEFAULT HEX(a), c TEXT DEFAULT UNHEX(b));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('aaaabbbb');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(10), b TEXT DEFAULT ENCODE(a,'test'), c TEXT DEFAULT DECODE(b,'test'));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('aaaabbbb');
-SELECT a, HEX(b), c FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (a VARCHAR(30), b TEXT DEFAULT PASSWORD(a));
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('notagoodpwd');
-SELECT * FROM t1;
-DROP TABLE t1;
-
-CREATE TABLE t1 (
-  a VARCHAR(30),
-  b BLOB DEFAULT AES_ENCRYPT(a, 'passwd'),
-  c TEXT DEFAULT AES_DECRYPT(b, 'passwd')
-);
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (a) VALUES ('test');
-SELECT c FROM t1;
-DROP TABLE t1;
-
---echo #
---echo # Collations
---echo #
-
---error ER_BAD_DATA
-CREATE TABLE t1 (a VARCHAR(20) CHARACTER SET latin1 DEFAULT CONCAT('ö')) CHARACTER SET koi8r COLLATE koi8r_bin;
-CREATE OR REPLACE TABLE t1 (a char(2) default concat('A') COLLATE utf8mb4_unicode_ci);
-SHOW CREATE TABLE t1;
-DROP TABLE t1;
-
-#
-# Order of evaluation:
-#
-create table t1 (a int default 1, b int default (rand()*0+2), c int);
-insert t1 (c) values (a);
-insert t1 (c) values (b);
-select * from t1;
-drop table t1;
-
-#
-# ALTER ... SET DEFAULT
-#
-create table t1 (a int default 1, b int default (1+1), c int);
-show create table t1;
-alter table t1 alter a set default (2+3), alter b set default 4,
-               alter c set default (-a);
---error ER_PARSE_ERROR
-alter table t1 alter a set default 1+2;
-show create table t1;
-drop table t1;
-
-#
-# CREATE ... SELECT
-#
-create table t1 (a int default 5 check (a>10), b int default (5+5), c int as (a+b));
-create table t2 as select a, b, c from t1;
-create table t3 as select max(a), max(b), max(c) from t1;
-show create table t2;
-show create table t3;
-drop table t1, t2, t3;
-
---echo # MDEV-11359: Implement IGNORE for bulk operation
-create table t1 (a int primary key default 0, b int default 3);
-insert into t1 values (1, ignore);
-insert into t1 values (2, ignore);
-replace into t1 values (2, ignore);
-replace into t1 values (3, ignore);
-replace into t1 values (4, 6);
-replace into t1 values (5, 7);
-update t1 set a=6,b=ignore where a=5;
-insert into t1 values (ignore, ignore);
---error ER_DUP_ENTRY
-insert into t1 values (ignore, ignore);
-select * from t1 order by a;
-delete from t1 where a < 4;
---echo # actually insert default instead of ignoring
---echo # (but REPLACE is non standard operator)
-replace into t1 values (4, ignore);
-select * from t1 order by a;
-drop table t1;
-
-#using in load
-create table t1 (a int default 100, b int, c varchar(60) default 'x');
-load data infile '../../std_data/rpl_loaddata.dat' into table t1 (a, @b) set b=@b+10, c=ignore;
-select * from t1;
-drop table t1;
-
-#using in duplicate
-CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT);
-CREATE TABLE t2 (a INT);
-INSERT INTO t2 VALUES (1),(2),(3),(2);
-INSERT INTO t1 SELECT a FROM t2 ON DUPLICATE KEY UPDATE a=DEFAULT;
-SELECT * FROM t1 order by a;
-truncate table t1;
-# efectively it is DEFALT
-INSERT INTO t1 SELECT a FROM t2 ON DUPLICATE KEY UPDATE a=IGNORE;
-SELECT * FROM t1 order by a;
-DROP TABLE t1,t2;
-
-create table t1 (a int primary key default 0, b int default 3);
-prepare insstmt from "insert into t1 values (?, ?)";
-prepare repstmt from "replace into t1 values (?, ?)";
-prepare updstmt from "update t1 set a=6,b=? where a=5";
-execute insstmt using 1, ignore;
-execute insstmt using 2, ignore;
-execute repstmt using 2, ignore;
-execute repstmt using 3, ignore;
-execute repstmt using 4, 6;
-execute repstmt using 5, 7;
-execute updstmt using ignore;
-execute insstmt using ignore, ignore;
---error ER_DUP_ENTRY
-execute insstmt using ignore, ignore;
-select * from t1 order by a;
-delete from t1 where a < 4;
-execute repstmt using 4, ignore;
-select * from t1 order by a;
-drop table t1;
-
---echo #
---echo # DEVAULT & PS adoption
---echo #
-
-
-# Correct usage
-CREATE TABLE t1 (a INT DEFAULT 10, b INT DEFAULT NULL);
-EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?,?)' USING IGNORE, IGNORE;
-SELECT * FROM t1;
-UPDATE t1 SET a=20, b=30;
-SELECT * FROM t1;
-EXECUTE IMMEDIATE 'UPDATE t1 SET a=?,b=?' USING IGNORE, IGNORE;
-SELECT * FROM t1;
-DROP TABLE t1;
-
-# Incorrect usage in a expression in INSERT..VALUES
-CREATE TABLE t1 (a INT DEFAULT 10);
---error ER_INVALID_DEFAULT_PARAM
-EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?+1)' USING IGNORE;
---error ER_INVALID_DEFAULT_PARAM
-EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (CONCAT(?,?))' USING IGNORE, 'test';
-DROP TABLE t1;
-
-# Incorrect usage in UPDATE..SET
-CREATE TABLE t1 (a INT DEFAULT 10);
-INSERT INTO t1 VALUES (20);
---error ER_INVALID_DEFAULT_PARAM
-EXECUTE IMMEDIATE 'UPDATE t1 SET a=?+1' USING IGNORE;
---error ER_INVALID_DEFAULT_PARAM
-EXECUTE IMMEDIATE 'UPDATE t1 SET a=CONCAT(?,?)' USING IGNORE, 'test';
-DROP TABLE t1;
-
-
-# Incorrect usage in not an UPDATE/INSERT query at all
---error ER_INVALID_DEFAULT_PARAM
-EXECUTE IMMEDIATE 'SELECT CAST(? AS SIGNED)' USING IGNORE;
---error ER_INVALID_DEFAULT_PARAM
-EXECUTE IMMEDIATE 'SELECT CAST(? AS DOUBLE)' USING IGNORE;
---error ER_INVALID_DEFAULT_PARAM
-EXECUTE IMMEDIATE 'SELECT CAST(? AS CHAR)' USING IGNORE;
---error ER_INVALID_DEFAULT_PARAM
-EXECUTE IMMEDIATE 'SELECT CAST(? AS DECIMAL(10,1))' USING IGNORE;
---error ER_INVALID_DEFAULT_PARAM
-EXECUTE IMMEDIATE 'SELECT CAST(? AS TIME)' USING IGNORE;
---error ER_INVALID_DEFAULT_PARAM
-EXECUTE IMMEDIATE 'SELECT CAST(? AS DATE)' USING IGNORE;
---error ER_INVALID_DEFAULT_PARAM
-EXECUTE IMMEDIATE 'SELECT CAST(? AS DATETIME)' USING IGNORE;
-
---error ER_INVALID_DEFAULT_PARAM
-EXECUTE IMMEDIATE 'SELECT ?+1' USING IGNORE;
---error ER_INVALID_DEFAULT_PARAM
-EXECUTE IMMEDIATE 'SELECT CONCAT(?,?)' USING IGNORE,'test';
-
-
-# Incorrect usage in the LIMIT clause
---error ER_INVALID_DEFAULT_PARAM
-EXECUTE IMMEDIATE 'SELECT 1 LIMIT ?' USING IGNORE;
-CREATE TABLE t1 (a INT DEFAULT 10);
-INSERT INTO t1 VALUES (1),(2),(3);
---error ER_INVALID_DEFAULT_PARAM
-EXECUTE IMMEDIATE 'SELECT * FROM t1 LIMIT ?' USING IGNORE;
-DROP TABLE t1;
-
-
---echo # The output of this query in 'Note' is a syntactically incorrect query.
---echo # But as it's never logged, it's ok. It should be human readable only.
-EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT ?' USING IGNORE;
-
-
-# This tests Item_param::eq() for IGNORE as a bound value
-CREATE TABLE t1 (a INT);
-INSERT INTO t1 VALUES (1),(2),(3);
---error ER_INVALID_DEFAULT_PARAM
-EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>?+a' USING DEFAULT,DEFAULT;
-DROP TABLE t1;
-
---echo #
---echo # MDEV-11134 Assertion `fixed' failed in Item::const_charset_converter(THD*, CHARSET_INFO*, bool, const char*)
---echo #
-
-SET NAMES utf8;
-PREPARE stmt FROM "CREATE OR REPLACE TABLE t1 (c CHAR(8) DEFAULT ?)";
-SET @a='';
-EXECUTE stmt USING @a;
-EXECUTE stmt USING @a;
-SHOW CREATE TABLE t1;
-DROP TABLE t1;
-SET @a='A';
-EXECUTE stmt USING @a;
-EXECUTE stmt USING @a;
-SHOW CREATE TABLE t1;
-DROP TABLE t1;
-SET @a=_utf8 0xC380; # LATIN CAPITAL LETTER A WITH GRAVE
-EXECUTE stmt USING @a;
-EXECUTE stmt USING @a;
-SHOW CREATE TABLE t1;
-DROP TABLE t1;
-SET @a=_utf8 0xD18F; # Cyrillic letter into a latin1 column
---error ER_INVALID_DEFAULT
-EXECUTE stmt USING @a;
---error ER_INVALID_DEFAULT
-EXECUTE stmt USING @a;
-DEALLOCATE PREPARE stmt;
-
-#
-# ANSI_QUOTES
-#
-set sql_mode=ansi_quotes;
-create table t1 (a int, b int default (a+1));
-show create table t1;
-insert t1 (a) values (10);
-set sql_mode='';
-show create table t1;
-insert t1 (a) values (20);
-flush tables;
-show create table t1;
-insert t1 (a) values (30);
-select * from t1;
-drop table t1;
-set sql_mode=default;
-
-#
-# MDEV-10201 Bad results for CREATE TABLE t1 (a INT DEFAULT b, b INT DEFAULT 4)
-#
-create table t1 (a int default b, b int default 4, t text);
-insert into t1 (b, t) values (5, '1 column is omitted');
-insert into t1 values (default, 5, '2 column gets DEFAULT, keyword');
-insert into t1 values (default(a), 5, '3 column gets DEFAULT(a), expression');
-insert into t1 values (default(a)+0, 5, '4 also expression DEFAULT(0)+0');
-insert into t1 values (b, 5, '5 the value of the DEFAULT(a), that is b');
-select * from t1 order by t;
-drop table t1;
-
-#
-# MDEV-10352 Server crashes in Field::set_default on CREATE TABLE
-#
---error ER_EXPRESSION_REFERS_TO_UNINIT_FIELD
-create table t1 (col1 int default(-(default(col1))));
-
-#
-# MDEV-10354 Assertion `! is_set()' failed in Diagnostics_area::set_ok_status on CREATE TABLE with invalid default
-#
---error ER_DATA_OUT_OF_RANGE
-create table t1 (col int default (yearweek((exp(710)))));
-
---echo #
---echo # MDEV-13707 Server in ORACLE mode crashes on ALTER with wrong DEFAULT clause
---echo #
-
-CREATE OR REPLACE TABLE t1(i int);
---error ER_BAD_FIELD_ERROR
-ALTER TABLE t1 ADD b CHAR(255) DEFAULT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa`;
-DROP TABLE t1;
-
-
---echo # end of 10.2 test