diff options
Diffstat (limited to '')
| -rw-r--r-- | mysql-test/main/default.test | 2188 |
1 files changed, 2188 insertions, 0 deletions
diff --git a/mysql-test/main/default.test b/mysql-test/main/default.test new file mode 100644 index 00000000..2ebe9ee9 --- /dev/null +++ b/mysql-test/main/default.test @@ -0,0 +1,2188 @@ +--source include/have_innodb.inc + +# +# Bug 10838 +# Insert causes warnings for no default values and corrupts tables +# +--disable_warnings +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 not null default now(); +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 # +--echo # MDEV-11359: Implement IGNORE for bulk operation +--echo # +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; + +--echo # +--echo # MDEV-10201 Bad results for CREATE TABLE t1 (a INT DEFAULT b, b INT DEFAULT 4) +--echo # +create table t1 (a int default b, b int default 4, t text); +insert t1 (b, t) values (5, '1 column is omitted'); +insert t1 values (default, 5, '2 column gets DEFAULT, keyword'); +insert t1 values (default(a), 5, '3 column gets DEFAULT(a), expression'); +insert t1 values (default(a)+0, 5, '4 also expression DEFAULT(0)+0'); +insert t1 values (b, 5, '5 the value of the DEFAULT(a), that is b'); +# and the same in a different order +insert t1 (t,b,a) values ('6 reversed, column gets DEFAULT, keyword', 5, default); +insert t1 (t,b,a) values ('7 reversed, column gets DEFAULT(a), expression', 5, default(a)); +insert t1 (t,b,a) values ('8 reversed, also expression DEFAULT(0)+0', 5, default(a)+0); +insert t1 (t,b,a) values ('9 reversed, the value of the DEFAULT(a), that is b', 5, b); +select * from t1 order by t; +drop table t1; + +--echo # +--echo # MDEV-10352 Server crashes in Field::set_default on CREATE TABLE +--echo # +--error ER_EXPRESSION_REFERS_TO_UNINIT_FIELD +create table t1 (col1 int default(-(default(col1)))); + +--echo # +--echo # MDEV-10354 Assertion `! is_set()' failed in Diagnostics_area::set_ok_status on CREATE TABLE with invalid default +--echo # +--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 # +--echo # MDEV-18278 Misleading error message in error log upon failed table creation +--echo # +--error ER_EXPRESSION_REFERS_TO_UNINIT_FIELD +create table t1 (a int as (a)); +show warnings; + + +--echo # +--echo # MDEV-26423: MariaDB server crash in Create_tmp_table::finalize +--echo # + +CREATE TABLE t1 (pk varchar(36) DEFAULT uuid()); +INSERT INTO t1 VALUES (),(); +SELECT 1 FROM t1 GROUP BY DEFAULT(pk); +DROP TABLE t1; + +--echo # +--echo # MDEV-28402: ASAN heap-use-after-free in create_tmp_table, +--echo # Assertion `l_offset >= 0 && table->s->rec_buff_length - l_offset > 0' +--echo # +CREATE TABLE t (a INT, KEY (a)); +INSERT INTO t VALUES (1),(2); +SELECT DISTINCT DEFAULT(a), CASE a WHEN 0 THEN 1 ELSE 2 END FROM t GROUP BY a WITH ROLLUP; +DROP TABLE t; + +CREATE TABLE t (a INT, KEY (a)); +INSERT INTO t VALUES (1),(2); +CREATE ALGORITHM=TEMPTABLE VIEW v AS SELECT * FROM t; +SELECT DISTINCT DEFAULT(a), CASE a WHEN 0 THEN 1 ELSE 2 END FROM v GROUP BY a WITH ROLLUP; +DROP TABLE t; +DROP VIEW v; +--echo # +--echo # End of 10.2 test +--echo # + +--echo # +--echo # MDEV-22703 DEFAULT() on a BLOB column can overwrite the default +--echo # record, which can cause crashes when accessing already released +--echo # memory. +--echo # + +CREATE TEMPORARY TABLE t1 (h POINT DEFAULT ST_GEOMFROMTEXT('Point(1 1)')) ENGINE=InnoDB; +INSERT INTO t1 () VALUES (),(); +ALTER TABLE t1 FORCE; +--disable_result_log +SELECT DEFAULT(h) FROM t1; +--enable_result_log +SELECT length(DEFAULT(h)) FROM t1; +INSERT INTO t1 () VALUES (); +drop table t1; + +--echo # +--echo # End of 10.3 test +--echo # + +--echo # +--echo # MDEV-26423: MariaDB server crash in Create_tmp_table::finalize +--echo # +CREATE TABLE t1 (pk text DEFAULT length(uuid())); +INSERT INTO t1 VALUES (),(); +SELECT 1 FROM t1 GROUP BY DEFAULT(pk); +DROP TABLE t1; + +--echo # +--echo # MDEV-29890 Update with inner join false row count result +--echo # +create table t1 (a int not null); +select column_name, column_default, column_default is not null as 'has_default', is_nullable from information_schema.columns where table_schema='test' and table_name='t1'; +create or replace view v1 as select * from t1; +select column_name, column_default, column_default is not null as 'has_default', is_nullable from information_schema.columns where table_schema='test' and table_name='v1'; +create or replace view v1 as select * from t1 group by a; +select column_name, column_default, column_default is not null as 'has_default', is_nullable from information_schema.columns where table_schema='test' and table_name='v1'; +create or replace view v1 as select * from t1 group by a with rollup; +select column_name, column_default, column_default is not null as 'has_default', is_nullable from information_schema.columns where table_schema='test' and table_name='v1'; +drop view v1; +drop table t1; + +--echo # +--echo # End of 10.4 test +--echo # |