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; set sql_mode=default; INSERT into t1 (b) values ('1'); SHOW WARNINGS; Level Code Message SELECT * from t1; a b c d e f g h i j k l m n o p q r s t u v w x y z a1 b1 1 0000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 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; Table Create Table t2 CREATE TABLE `t2` ( `a` varchar(30) NOT NULL DEFAULT ' ', `b` varchar(1) NOT NULL DEFAULT ' ', `c` varchar(4) NOT NULL DEFAULT '0000', `d` tinyblob DEFAULT NULL, `e` tinyblob DEFAULT NULL, `f` tinyblob DEFAULT NULL, `g` tinyblob DEFAULT NULL, `h` tinyblob DEFAULT NULL, `i` tinyblob DEFAULT NULL, `j` tinyblob DEFAULT NULL, `k` tinyblob DEFAULT NULL, `l` tinyblob DEFAULT NULL, `m` tinyblob DEFAULT NULL, `n` tinyblob DEFAULT NULL, `o` tinyblob DEFAULT NULL, `p` tinyblob DEFAULT NULL, `q` varchar(30) NOT NULL DEFAULT ' ', `r` varchar(30) NOT NULL DEFAULT ' ', `s` tinyblob DEFAULT NULL, `t` varchar(4) NOT NULL DEFAULT ' ', `u` varchar(1) NOT NULL DEFAULT ' ', `v` varchar(30) NOT NULL DEFAULT ' ', `w` varchar(30) NOT NULL DEFAULT ' ', `x` tinyblob DEFAULT NULL, `y` varchar(5) NOT NULL DEFAULT ' ', `z` varchar(20) NOT NULL DEFAULT ' ', `a1` varchar(30) NOT NULL DEFAULT ' ', `b1` tinyblob DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin INSERT into t2 (b) values ('1'); SHOW WARNINGS; Level Code Message SELECT * from t2; a b c d e f g h i j k l m n o p q r s t u v w x y z a1 b1 1 0000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL drop table t1; drop table t2; 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); Warnings: Warning 1364 Field 'd' doesn't have a default value Warning 1364 Field 'd' doesn't have a default value insert ignore into bug20691 (i) values (2); Warnings: Warning 1364 Field 'd' doesn't have a default value desc bug20691; Field Type Null Key Default Extra i int(11) YES NULL d datetime NO NULL dn datetime NO 0000-00-00 00:00:00 insert ignore into bug20691 values (3, DEFAULT, DEFAULT), (3, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (3, DEFAULT, DEFAULT); Warnings: Warning 1364 Field 'd' doesn't have a default value Warning 1364 Field 'd' doesn't have a default value insert ignore into bug20691 (i) values (4); Warnings: Warning 1364 Field 'd' doesn't have a default value insert ignore into bug20691 values (5, DEFAULT, DEFAULT), (5, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (5, DEFAULT, DEFAULT); Warnings: Warning 1364 Field 'd' doesn't have a default value Warning 1364 Field 'd' doesn't have a default value 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); Warnings: Warning 1364 Field 'd' doesn't have a default value Warning 1364 Field 'd' doesn't have a default value SET sql_mode = 'STRICT_ALL_TABLES'; insert into bug20691 values (7, DEFAULT, DEFAULT), (7, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (7, DEFAULT, DEFAULT); ERROR HY000: Field 'd' doesn't have a default value select * from bug20691 order by i asc; i d dn 1 0000-00-00 00:00:00 0000-00-00 00:00:00 1 1975-07-10 07:10:03 1978-01-13 14:08:51 1 0000-00-00 00:00:00 0000-00-00 00:00:00 2 0000-00-00 00:00:00 0000-00-00 00:00:00 3 0000-00-00 00:00:00 0000-00-00 00:00:00 3 1975-07-10 07:10:03 1978-01-13 14:08:51 3 0000-00-00 00:00:00 0000-00-00 00:00:00 4 0000-00-00 00:00:00 0000-00-00 00:00:00 5 0000-00-00 00:00:00 0000-00-00 00:00:00 5 1975-07-10 07:10:03 1978-01-13 14:08:51 5 0000-00-00 00:00:00 0000-00-00 00:00:00 6 0000-00-00 00:00:00 0000-00-00 00:00:00 6 1975-07-10 07:10:03 1978-01-13 14:08:51 6 0000-00-00 00:00:00 0000-00-00 00:00:00 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); Warnings: Warning 1364 Field 'a' doesn't have a default value Warning 1364 Field 'c' doesn't have a default value Warning 1364 Field 'd' doesn't have a default value Warning 1364 Field 'e' doesn't have a default value Warning 1364 Field 'f' doesn't have a default value Warning 1364 Field 'g' doesn't have a default value Warning 1364 Field 'h' doesn't have a default value Warning 1364 Field 'i' doesn't have a default value 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); Warnings: Warning 1364 Field 'a' doesn't have a default value Warning 1364 Field 'c' doesn't have a default value Warning 1364 Field 'd' doesn't have a default value Warning 1364 Field 'e' doesn't have a default value Warning 1364 Field 'f' doesn't have a default value Warning 1364 Field 'g' doesn't have a default value Warning 1364 Field 'h' doesn't have a default value Warning 1364 Field 'i' doesn't have a default value select * from bug20691 order by x asc; a b c d e f g h i x two large 00:00:05 0007-01-01 11 13 17 0019-01-01 00:00:00 23 1 small 00:00:00 0000-00-00 0 0000-00-00 00:00:00 0 2 two large 00:00:05 0007-01-01 11 13 17 0019-01-01 00:00:00 23 3 small 00:00:00 0000-00-00 0 0000-00-00 00:00:00 0 4 drop table bug20691; create table t1 (id int not null); insert into t1 values(default); Warnings: Warning 1364 Field 'id' doesn't have a default value create view v1 (c) as select id from t1; insert into t1 values(default); Warnings: Warning 1364 Field 'id' doesn't have a default value drop view v1; drop table t1; 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; a NULL insert into t1 (a) values (1); insert into t1 (a) select b from t2 on duplicate key update a=default(b); select * from t1; a NULL 10 drop table t1, t2; # End of 5.0 tests # # Start of 10.0 tests # # # MDEV-11265 Access defied when CREATE VIIEW v1 AS SELECT DEFAULT(column) FROM t1 # 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; a 11 SELECT * FROM v2; a 10 SELECT * FROM v3; a NULL UPDATE v2 SET a=123; ERROR HY000: Column 'a' is not updatable UPDATE v3 SET a=123; ERROR HY000: Column 'a' is not updatable DROP VIEW v3; DROP VIEW v2; DROP VIEW v1; DROP TABLE t1; # # MDEV-10780 Server crashes in in create_tmp_table # 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; DEFAULT (pk) 0 disconnect con1; connection default; DROP TABLE t1; # # End of 10.0 tests # # # Start of 10.1 tests # CREATE TABLE t1 (a INT DEFAULT 100, b INT DEFAULT NULL); INSERT INTO t1 VALUES (); SELECT * FROM t1 WHERE DEFAULT(a); a b 100 NULL SELECT * FROM t1 WHERE DEFAULT(b); a b DROP TABLE IF EXISTS t1; # # End of 10.1 tests # # # Start of 10.2 tests # SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30.123456'); # # Check that CURRENT_TIMESTAMP works as before # CREATE or replace TABLE t1 (event_time TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `event_time` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CREATE or replace TABLE t1 (event_time TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(2) ON UPDATE CURRENT_TIMESTAMP); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `event_time` timestamp(6) NOT NULL DEFAULT current_timestamp(2) ON UPDATE current_timestamp(6) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CREATE or replace TABLE t1 (event_time TIMESTAMP(6) NOT NULL DEFAULT SYSDATE(2) ON UPDATE CURRENT_TIMESTAMP); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `event_time` timestamp(6) NOT NULL DEFAULT sysdate(2) ON UPDATE current_timestamp(6) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci drop table t1; # # Check default expressions # create or replace table t1 (a int default 1, b int default (a+1), c int default (a+b)) engine myisam; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT 1, `b` int(11) DEFAULT (`a` + 1), `c` int(11) DEFAULT (`a` + `b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci 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; a b c 1 2 3 2 3 5 10 20 30 100 200 400 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; a b c 1 2 3 2 3 5 10 20 30 100 200 400 create or replace table t1 (a int, b blob default (1), c blob default "hello", t text default concat(a,b,c)) engine=myisam; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` blob DEFAULT 1, `c` blob DEFAULT 'hello', `t` text DEFAULT concat(`a`,`b`,`c`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci insert into t1 (a) values (2); insert into t1 (a,b) values (10,"test1"); insert into t1 (a,b,c) values (10,"test2","test3"); insert delayed into t1 (a,b) values (10,"test4"); flush tables t1; select * from t1; a b c t 2 1 hello 21hello 10 test1 hello 10test1hello 10 test2 test3 10test2test3 10 test4 hello 10test4hello drop table t1; create or replace table t1 (a bigint default uuid_short()); insert into t1 values(); select a > 0 from t1; a > 0 1 drop table t1; create or replace table t1 (param_list int DEFAULT (1+1) NOT NULL); create or replace table t1 (param_list int DEFAULT 1+1 NOT NULL); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '+1 NOT NULL)' at line 1 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; a b c d e -1 1 -1 2 2001-01-01 10:20:30 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; a b c d e f g h -1 1 -1 2 2001-01-01 10:20:30 3 4 8 10 10 10 10 0000-00-00 00:00:00 3 4 8 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT 1, `c` int(11) DEFAULT -1, `d` int(11) DEFAULT (1 + 1), `e` timestamp NOT NULL DEFAULT current_timestamp(), `f` int(11) DEFAULT (1 + 1 + 1), `g` int(11) NOT NULL DEFAULT (1 + 1 + 1 + 1), `h` int(11) DEFAULT (2 + 2 + 2 + 2) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci create table t2 like t1; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT 1, `c` int(11) DEFAULT -1, `d` int(11) DEFAULT (1 + 1), `e` timestamp NOT NULL DEFAULT current_timestamp(), `f` int(11) DEFAULT (1 + 1 + 1), `g` int(11) NOT NULL DEFAULT (1 + 1 + 1 + 1), `h` int(11) DEFAULT (2 + 2 + 2 + 2) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci insert into t2 (a) values (100); select a,b,c,d,e,f,g,h from t2; a b c d e f g h 100 1 -1 2 2001-01-01 10:20:30 3 4 8 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; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT (1 - -1), `b` int(11) DEFAULT -1, `c` int(11) DEFAULT 1, `e` int(11) DEFAULT 1 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci insert into t1 values(); insert into t1 values(); select * from t1; a b c e 2 -1 1 1 2 -1 1 1 drop table t1; # # Create or replace can delete a table on error # create table t1 (a int); create or replace table t1 (a int default b, b int default a); ERROR 01000: Expression for field `a` is referring to uninitialized field `b` show create table t1; ERROR 42S02: Table 'test.t1' doesn't exist # # Refering to other columns # create or replace table t1 (a int default 1, b int default a); create or replace table t1 (a int default 1, b int as (a)); create or replace table t1 (a int default b, b int default 1); create or replace table t1 (a int as (b), b int default 1); create or replace table t1 (a int as (b), b int default (1+1)); create or replace table t1 (a int default 1, b int as (c), c int default (a+1)); create or replace table t1 (a int default (1+1), b int as (c), c int default (a+1)); create or replace table t1 (a varchar(128) default @@version); create or replace table t1 (a int not null, b int as (a)); create or replace table t1 (a int not null, b int default (a+1)); create or replace table t1 (a int default a); ERROR 01000: Expression for field `a` is referring to uninitialized field `a` create or replace table t1 (a int default b, b int default (1+1)); create or replace table t1 (a int default 1, b int as (c), c int as (a+1)); ERROR 01000: Expression for field `b` is referring to uninitialized field `c` CREATE TABLE t1 (a INT DEFAULT (DEFAULT(a))); ERROR 01000: Expression for field `a` is referring to uninitialized field `a` CREATE TABLE t1 (a INT DEFAULT(DEFAULT(b)), b INT DEFAULT(DEFAULT(a))); ERROR 01000: Expression for field `a` is referring to uninitialized field `b` CREATE TABLE t1 (a INT DEFAULT(DEFAULT(b)) NOT NULL, b INT DEFAULT(DEFAULT(a)) NOT NULL); ERROR 01000: Expression for field `a` is referring to uninitialized field `b` # # Allow defaults to refer to not default fields # create or replace table t1 (a int default b, b int not null); insert into t1 values(); Warnings: Warning 1364 Field 'b' doesn't have a default value insert into t1 (a) values(1); Warnings: Warning 1364 Field 'b' doesn't have a default value insert into t1 (b) values(2); insert into t1 (a,b) values(3,4); select * from t1; a b 0 0 1 0 2 2 3 4 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; # # Error handling # create or replace table t1 (a bigint default xxx()); ERROR HY000: Function or expression '`xxx`()' cannot be used in the DEFAULT clause of `a` create or replace table t1 (a bigint default (select (1))); ERROR HY000: Function or expression 'select ...' cannot be used in the DEFAULT clause of `a` create or replace table t1 (a bigint default (1,2,3)); ERROR 21000: Operand should contain 1 column(s) create or replace table t1 (a bigint default ((1,2,3))); ERROR 21000: Operand should contain 1 column(s) CREATE TABLE t1 (a INT, b INT, c INT DEFAULT a DIV b); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DIV b)' at line 1 CREATE TABLE t1 (a INT, b INT DEFAULT -a); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'a)' at line 1 # # Invalid DEFAULT expressions # CREATE TABLE t1 (a INT DEFAULT ((SELECT 1))); ERROR HY000: Function or expression 'select ...' cannot be used in the DEFAULT clause of `a` CREATE TABLE t1 (a INT DEFAULT (EXISTS (SELECT 1))); ERROR HY000: Function or expression 'select ...' cannot be used in the DEFAULT clause of `a` CREATE TABLE t1 (a INT DEFAULT (1=ANY (SELECT 1))); ERROR HY000: Function or expression 'select ...' cannot be used in the DEFAULT clause of `a` CREATE TABLE t1 (a INT DEFAULT ROW(1,1)); ERROR 21000: Operand should contain 1 column(s) CREATE TABLE t1 (a INT DEFAULT (1,1)); ERROR 21000: Operand should contain 1 column(s) CREATE TABLE t1 (a INT DEFAULT ((1,1))); ERROR 21000: Operand should contain 1 column(s) CREATE TABLE t1 (a INT DEFAULT ?); Got one of the listed errors CREATE TABLE t1 (a INT DEFAULT(?)); Got one of the listed errors CREATE TABLE t1 (a INT DEFAULT (b), b INT DEFAULT(a)); ERROR 01000: Expression for field `a` is referring to uninitialized field `b` CREATE TABLE t1 (a INT DEFAULT(NAME_CONST('xxx', 'yyy')); ERROR HY000: Function or expression 'name_const()' cannot be used in the DEFAULT clause of `a` CREATE TABLE t1 (a INT DEFAULT COUNT(*)); ERROR HY000: Function or expression 'count()' cannot be used in the DEFAULT clause of `a` CREATE TABLE t1 (a INT DEFAULT COUNT(1)); ERROR HY000: Function or expression 'count()' cannot be used in the DEFAULT clause of `a` CREATE TABLE t1 (a INT DEFAULT AVG(1)); ERROR HY000: Function or expression 'avg()' cannot be used in the DEFAULT clause of `a` CREATE TABLE t1 (a INT DEFAULT MIN(1)); ERROR HY000: Function or expression 'min()' cannot be used in the DEFAULT clause of `a` CREATE TABLE t1 (a INT DEFAULT GROUP_CONCAT(1)); ERROR HY000: Function or expression 'group_concat()' cannot be used in the DEFAULT clause of `a` CREATE TABLE t1 (a INT DEFAULT ROW_NUMBER() OVER ()); ERROR HY000: Function or expression 'row_number()' cannot be used in the DEFAULT clause of `a` CREATE FUNCTION f1() RETURNS INT RETURN 1; CREATE TABLE t1 (a INT DEFAULT f1()); ERROR HY000: Function or expression '`f1`()' cannot be used in the DEFAULT clause of `a` DROP FUNCTION f1; CREATE PROCEDURE p1(par INT) CREATE TABLE t1 (a INT DEFAULT par); ERROR HY000: Function or expression 'par' cannot be used in the DEFAULT clause of `a` CREATE TABLE t1 (a INT DEFAULT par); ERROR 42S22: Unknown column 'par' in 'DEFAULT' CREATE PROCEDURE p1() CREATE TABLE t1 (a INT DEFAULT par); CALL p1; ERROR 42S22: Unknown column 'par' in 'DEFAULT' DROP PROCEDURE p1; CREATE TABLE t1 (a INT DEFAULT VALUE(a)); ERROR HY000: Function or expression 'value()' cannot be used in the DEFAULT clause of `a` CREATE TABLE t1 (a INT); CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW CREATE TABLE t2 (a INT DEFAULT NEW.a); ERROR HY000: Function or expression 'NEW.a' cannot be used in the DEFAULT clause of `a` CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW CREATE TEMPORARY TABLE t2 (a INT DEFAULT NEW.a); ERROR HY000: Function or expression 'NEW.a' cannot be used in the DEFAULT clause of `a` DROP TABLE t1; # # Prepared statements # PREPARE stmt FROM 'CREATE TABLE t1 (a INT DEFAULT(?))'; set @a=1; execute stmt using @a; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT 1 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci drop table t1; set @a=-1; execute stmt using @a; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT -1 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci drop table t1; DEALLOCATE PREPARE stmt; PREPARE stmt FROM 'CREATE TABLE t1 (a INT DEFAULT(?), b INT DEFAULT(?))'; set @a=1, @b=2; execute stmt using @a,@b; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT 1, `b` int(11) DEFAULT 2 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci drop table t1; DEALLOCATE PREPARE stmt; prepare stmt from 'create table t1 (a int default(?+?))'; set @a=1; execute stmt using @a,@a; deallocate prepare stmt; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT (1 + 1) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci drop table t1; # # Parenthesized Item_basic_constant # CREATE TABLE t1 ( i01 INT DEFAULT (((1))), i02 INT DEFAULT (((0x3939))), i03 INT DEFAULT (((1.0))), i04 INT DEFAULT (((1e0))), i05 INT DEFAULT (((NULL))), f01 DOUBLE DEFAULT (((PI()))), s01 VARCHAR(10) DEFAULT (((_latin1'test'))), s02 VARCHAR(10) DEFAULT ((('test'))), s03 VARCHAR(10) DEFAULT (((0x40))), s04 VARCHAR(10) DEFAULT (((X'40'))), s05 VARCHAR(10) DEFAULT (((B'1000000'))), d01 TIME DEFAULT (((TIME'10:20:30'))), d02 DATE DEFAULT (((DATE'2001-01-01'))), d03 DATETIME DEFAULT (((TIMESTAMP'2001-01-01 10:20:30'))) ); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `i01` int(11) DEFAULT 1, `i02` int(11) DEFAULT 14649, `i03` int(11) DEFAULT 1, `i04` int(11) DEFAULT 1, `i05` int(11) DEFAULT NULL, `f01` double DEFAULT 3.141592653589793, `s01` varchar(10) DEFAULT 'test', `s02` varchar(10) DEFAULT 'test', `s03` varchar(10) DEFAULT '@', `s04` varchar(10) DEFAULT '@', `s05` varchar(10) DEFAULT '@', `d01` time DEFAULT '10:20:30', `d02` date DEFAULT '2001-01-01', `d03` datetime DEFAULT '2001-01-01 10:20:30' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (); SELECT * FROM t1; i01 1 i02 14649 i03 1 i04 1 i05 NULL f01 3.141592653589793 s01 test s02 test s03 @ s04 @ s05 @ d01 10:20:30 d02 2001-01-01 d03 2001-01-01 10:20:30 DROP TABLE t1; # # COALESCE(Item_basic_constant) # CREATE TABLE t1 ( i01 INT DEFAULT COALESCE(1), i02 INT DEFAULT COALESCE(0x3939), i03 INT DEFAULT COALESCE(1.0), i04 INT DEFAULT COALESCE(1e0), i05 INT DEFAULT COALESCE(NULL), f01 DOUBLE DEFAULT COALESCE(PI()), s01 VARCHAR(10) DEFAULT COALESCE(_latin1'test'), s02 VARCHAR(10) DEFAULT COALESCE('test'), s03 VARCHAR(10) DEFAULT COALESCE(0x40), s04 VARCHAR(10) DEFAULT COALESCE(X'40'), s05 VARCHAR(10) DEFAULT COALESCE(B'1000000'), d01 TIME DEFAULT COALESCE(TIME'10:20:30'), d02 DATE DEFAULT COALESCE(DATE'2001-01-01'), d03 DATETIME DEFAULT COALESCE(TIMESTAMP'2001-01-01 10:20:30') ); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `i01` int(11) DEFAULT coalesce(1), `i02` int(11) DEFAULT coalesce(0x3939), `i03` int(11) DEFAULT coalesce(1.0), `i04` int(11) DEFAULT coalesce(1e0), `i05` int(11) DEFAULT coalesce(NULL), `f01` double DEFAULT coalesce(pi()), `s01` varchar(10) DEFAULT coalesce(_latin1'test'), `s02` varchar(10) DEFAULT coalesce('test'), `s03` varchar(10) DEFAULT coalesce(0x40), `s04` varchar(10) DEFAULT coalesce(X'40'), `s05` varchar(10) DEFAULT coalesce(0x40), `d01` time DEFAULT coalesce(TIME'10:20:30'), `d02` date DEFAULT coalesce(DATE'2001-01-01'), `d03` datetime DEFAULT coalesce(TIMESTAMP'2001-01-01 10:20:30') ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (); SELECT * FROM t1; i01 1 i02 99 i03 1 i04 1 i05 NULL f01 3.141592653589793 s01 test s02 test s03 @ s04 @ s05 @ d01 10:20:30 d02 2001-01-01 d03 2001-01-01 10:20:30 DROP TABLE t1; # # TINYINT: out of range # CREATE TABLE t1 (a TINYINT DEFAULT 300 NOT NULL); ERROR 42000: Invalid default value for 'a' CREATE TABLE t1 (a TINYINT DEFAULT 128 NOT NULL); ERROR 42000: Invalid default value for 'a' CREATE TABLE t1 (a TINYINT DEFAULT -500 NOT NULL); ERROR 42000: Invalid default value for 'a' # # INT: simple numeric expressions # CREATE TABLE t1 (a INT DEFAULT 1 NOT NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL DEFAULT 1 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (DEFAULT); SELECT * FROM t1; a 1 DROP TABLE t1; CREATE TABLE t1 (a INT DEFAULT COALESCE(1) NOT NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL DEFAULT coalesce(1) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (DEFAULT); SELECT * FROM t1; a 1 DROP TABLE t1; # # INT: simple string expressions # CREATE TABLE t1 (a INT DEFAULT '1' NOT NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL DEFAULT 1 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (DEFAULT); SELECT * FROM t1; a 1 DROP TABLE t1; CREATE TABLE t1 (a INT DEFAULT CONCAT('1') NOT NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL DEFAULT concat('1') ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (DEFAULT); SELECT * FROM t1; a 1 DROP TABLE t1; CREATE TABLE t1 (a INT DEFAULT COALESCE('1') NOT NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL DEFAULT coalesce('1') ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (DEFAULT); SELECT * FROM t1; a 1 DROP TABLE t1; # # INT: string expressions with garbage # CREATE TABLE t1 (a INT DEFAULT 'x'); ERROR 42000: Invalid default value for 'a' CREATE TABLE t1 (a INT DEFAULT CONCAT('x')); ERROR 42000: Invalid default value for 'a' CREATE TABLE t1 (a INT DEFAULT COALESCE('x')); ERROR 42000: Invalid default value for 'a' CREATE TABLE t1 (a INT DEFAULT (((((COALESCE('x'))))))); ERROR 42000: Invalid default value for 'a' # # INT: string expressions with numbers + garbage # CREATE TABLE t1 (a INT DEFAULT '1x'); ERROR 42000: Invalid default value for 'a' CREATE TABLE t1 (a INT DEFAULT COALESCE('1x')); ERROR 42000: Invalid default value for 'a' CREATE TABLE t1 (a INT DEFAULT CONCAT('1x')); ERROR 42000: Invalid default value for 'a' # # INT: string expressions with numbers + trailing space # CREATE TABLE t1 (a INT DEFAULT '1 '); Warnings: Note 1265 Data truncated for column 'a' at row 0 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT 1 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (DEFAULT); SELECT * FROM t1; a 1 DROP TABLE t1; CREATE TABLE t1 (a INT DEFAULT CONCAT('1 ')); Warnings: Note 1265 Data truncated for column 'a' at row 0 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT concat('1 ') ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (DEFAULT); Warnings: Note 1265 Data truncated for column 'a' at row 1 SELECT * FROM t1; a 1 DROP TABLE t1; CREATE TABLE t1 (a INT DEFAULT COALESCE('1 ')); Warnings: Note 1265 Data truncated for column 'a' at row 0 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT coalesce('1 ') ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (DEFAULT); Warnings: Note 1265 Data truncated for column 'a' at row 1 SELECT * FROM t1; a 1 DROP TABLE t1; # # INT: a HEX value # CREATE TABLE t1 (a INT DEFAULT 0x61 NOT NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL DEFAULT 97 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (DEFAULT); SELECT * FROM t1; a 97 DROP TABLE t1; # # VARCHAR: good defaults # CREATE TABLE t1 (a VARCHAR(30) DEFAULT 'xxx' NOT NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(30) NOT NULL DEFAULT 'xxx' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (DEFAULT); SELECT * FROM t1; a xxx DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(2) DEFAULT 0x41 NOT NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(2) NOT NULL DEFAULT 'A' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(2) DEFAULT CONCAT(0x41) NOT NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(2) NOT NULL DEFAULT concat(0x41) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (DEFAULT); SELECT * FROM t1; a A DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(2) DEFAULT COALESCE(0x41) NOT NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(2) NOT NULL DEFAULT coalesce(0x41) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (DEFAULT); SELECT * FROM t1; a A DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(2) DEFAULT CONCAT(_utf8 0x41) NOT NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(2) NOT NULL DEFAULT concat(_utf8mb3'A') ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (DEFAULT); SELECT * FROM t1; a A DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(2) DEFAULT CONCAT(_utf8 X'41') NOT NULL); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(2) NOT NULL DEFAULT concat(_utf8mb3'A') ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (DEFAULT); SELECT * FROM t1; a A DROP TABLE t1; # # VARCHAR: Too long default # CREATE TABLE t1 (a VARCHAR(2) DEFAULT 'xxx' NOT NULL); ERROR 42000: Invalid default value for 'a' CREATE TABLE t1 (a VARCHAR(2) DEFAULT CONCAT('xxx') NOT NULL); ERROR 42000: Invalid default value for 'a' # # VARCHAR: Too long default with non-important data # CREATE TABLE t1 (a VARCHAR(2) DEFAULT 'xx ' NOT NULL); ERROR 42000: Invalid default value for 'a' CREATE TABLE t1 (a VARCHAR(2) DEFAULT CONCAT('xx ') NOT NULL); ERROR 42000: Invalid default value for 'a' # # VARCHAR: conversion failures # CREATE TABLE t1 (a VARCHAR(2) CHARACTER SET latin1 DEFAULT _utf8 X'D18F' NOT NULL); ERROR 42000: Invalid default value for 'a' CREATE TABLE t1 (a VARCHAR(2) CHARACTER SET latin1 DEFAULT CONCAT(_utf8 X'D18F') NOT NULL); ERROR 42000: Invalid default value for 'a' CREATE TABLE t1 (a VARCHAR(2) CHARACTER SET latin1 DEFAULT CONCAT(_utf8 0xD18F) NOT NULL); ERROR 42000: Invalid default value for 'a' # # Field as a default value # CREATE TABLE t1 (a INT, b INT DEFAULT (a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT `a` ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (1, 1); INSERT INTO t1 VALUES (DEFAULT, DEFAULT); INSERT INTO t1 VALUES (1, DEFAULT); INSERT INTO t1 VALUES (DEFAULT, 1); SELECT * FROM t1; a b 1 1 NULL NULL 1 1 NULL 1 DROP TABLE t1; # # Function DEFAULT(field) # CREATE TABLE t1 (a INT DEFAULT(DEFAULT(b)), b INT DEFAULT 1); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT default(`b`), `b` int(11) DEFAULT 1 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (DEFAULT, DEFAULT); SELECT * FROM t1; a b 1 1 DROP TABLE t1; CREATE TABLE t1 (a INT DEFAULT 1, b INT DEFAULT(DEFAULT(a))); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT 1, `b` int(11) DEFAULT default(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (DEFAULT, DEFAULT); SELECT * FROM t1; a b 1 1 DROP TABLE t1; # # SQL Standard as a # CREATE TABLE t1 (a DATETIME DEFAULT CURRENT_TIMESTAMP); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` datetime DEFAULT current_timestamp() ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1; CREATE TABLE t1 (a TIME DEFAULT CURRENT_TIME); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` time DEFAULT curtime() ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1; CREATE TABLE t1 (a DATE DEFAULT CURRENT_DATE); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` date DEFAULT curdate() ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1; # # DECIMAL + CURRENT_TIMESTAMP, no truncation # CREATE TABLE t1 (a DECIMAL(30,6) DEFAULT CURRENT_TIMESTAMP(6)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` decimal(30,6) DEFAULT current_timestamp(6) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (); SELECT * FROM t1; a 20010101102030.123456 DROP TABLE t1; CREATE TABLE t1 (a DECIMAL(30,6) DEFAULT COALESCE(CURRENT_TIMESTAMP(6))); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` decimal(30,6) DEFAULT coalesce(current_timestamp(6)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci 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; # # DECIMAL + CURRENT_TIME, no truncation # CREATE TABLE t1 (a DECIMAL(30,6) DEFAULT COALESCE(CURRENT_TIME(6))); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` decimal(30,6) DEFAULT coalesce(curtime(6)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT IGNORE INTO t1 VALUES(); DROP TABLE t1; # # DECIMAL + CURRENT_DATE, no truncation # CREATE TABLE t1 (a DECIMAL(30,6) DEFAULT COALESCE(CURRENT_DATE)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` decimal(30,6) DEFAULT coalesce(curdate()) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT IGNORE INTO t1 VALUES(); DROP TABLE t1; # # COALESCE for SQL Standard # CREATE TABLE t1 (a TIMESTAMP DEFAULT COALESCE(CURRENT_TIMESTAMP)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` timestamp NULL DEFAULT coalesce(current_timestamp()) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1; CREATE TABLE t1 (a DATE DEFAULT COALESCE(CURRENT_DATE)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` date DEFAULT coalesce(curdate()) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1; CREATE TABLE t1 (a TIME DEFAULT COALESCE(CURRENT_TIME)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` time DEFAULT coalesce(curtime()) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1; CREATE TABLE t1 ( a TIMESTAMP DEFAULT CURRENT_TIMESTAMP(6), b TIMESTAMP DEFAULT COALESCE(CURRENT_TIMESTAMP(6)) ); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` timestamp NULL DEFAULT current_timestamp(), `b` timestamp NULL DEFAULT coalesce(current_timestamp(6)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (); SELECT CURRENT_TIMESTAMP(6); CURRENT_TIMESTAMP(6) 2001-01-01 10:20:30.123456 SELECT * FROM t1; a b 2001-01-01 10:20:30 2001-01-01 10:20:30 DROP TABLE t1; CREATE TABLE t1 ( a DECIMAL(30,0) DEFAULT CURRENT_TIMESTAMP(6), b DECIMAL(30,0) DEFAULT COALESCE(CURRENT_TIMESTAMP(6)) ); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` decimal(30,0) DEFAULT current_timestamp(6), `b` decimal(30,0) DEFAULT coalesce(current_timestamp(6)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT IGNORE INTO t1 VALUES (); Warnings: Note 1265 Data truncated for column 'a' at row 1 Note 1265 Data truncated for column 'b' at row 1 SELECT * FROM t1; a b 20010101102030 20010101102030 DROP TABLE t1; # # Check DEFAULT() function # CREATE TABLE `t1` (`a` int(11) DEFAULT (3+3),`b` int(11) DEFAULT '1000'); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT (3 + 3), `b` int(11) DEFAULT 1000 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci insert into t1 values (1,1),(2,2); insert into t1 values (default,default); select * from t1; a b 1 1 2 2 6 1000 select default(a),b from t1; default(a) b 6 1 6 2 6 1000 select a,default(b) from t1; a default(b) 1 1000 2 1000 6 1000 drop table t1; # # Real functions # CREATE TABLE t1 (a DECIMAL(10,1), b DOUBLE DEFAULT CAST(a AS DOUBLE)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` decimal(10,1) DEFAULT NULL, `b` double DEFAULT (cast(`a` as double)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (10.1, DEFAULT); SELECT * FROM t1; a b 10.1 10.1 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; Table Create Table t1 CREATE TABLE `t1` ( `a` double DEFAULT NULL, `b` double DEFAULT exp(`a`), `c` double DEFAULT log(`b`), `d` double DEFAULT log(4,`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (2, DEFAULT, DEFAULT, DEFAULT); SELECT * FROM t1; a b c d 2 7.38905609893065 2 1.4426950408889634 DROP TABLE t1; CREATE TABLE t1 (a INT, b DOUBLE DEFAULT LOG2(a), c DOUBLE DEFAULT LOG10(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` double DEFAULT log2(`a`), `c` double DEFAULT log10(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (4, DEFAULT, DEFAULT); INSERT INTO t1 VALUES (100, DEFAULT, DEFAULT); SELECT * FROM t1; a b c 4 2 0.6020599913279624 100 6.643856189774725 2 DROP TABLE t1; CREATE TABLE t1 (a DOUBLE, b DOUBLE DEFAULT SQRT(a), c DOUBLE DEFAULT POW(a,3)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` double DEFAULT NULL, `b` double DEFAULT sqrt(`a`), `c` double DEFAULT pow(`a`,3) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (4, DEFAULT, DEFAULT); SELECT * FROM t1; a b c 4 2 64 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; Table Create Table t1 CREATE TABLE `t1` ( `a` double DEFAULT NULL, `b` double DEFAULT acos(`a`), `c` double DEFAULT asin(`a`), `d` double DEFAULT atan(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (1, DEFAULT, DEFAULT, DEFAULT); SELECT a, b/PI(), c/PI(), d/PI() FROM t1; a b/PI() c/PI() d/PI() 1 0 0.5 0.25 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; Table Create Table t1 CREATE TABLE `t1` ( `a` double DEFAULT NULL, `b` double DEFAULT cos(`a`), `c` double DEFAULT sin(`a`), `d` double DEFAULT tan(`a`), `e` double DEFAULT cot(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci 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; ROUND(a,3) ROUND(b,3) ROUND(c,3) ROUND(d,3) ROUND(e,3) 1.047 0.500 0.866 1.732 0.577 DROP TABLE t1; CREATE TABLE t1 (a DOUBLE DEFAULT RAND()); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` double DEFAULT rand() ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci 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; Table Create Table t1 CREATE TABLE `t1` ( `a` double DEFAULT NULL, `b` double DEFAULT degrees(`a`), `c` double DEFAULT radians(`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (PI(), DEFAULT, DEFAULT); SELECT * FROM t1; a b c 3.141592653589793 180 3.141592653589793 DROP TABLE t1; # # INT result functions # CREATE TABLE t1 (a INT, b INT DEFAULT INTERVAL(a, 10, 20, 30, 40)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT interval(`a`,10,20,30,40) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES (34); SELECT * FROM t1; a b 34 3 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a DIV b)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT (`a` DIV `b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a, b) VALUES (13, 3); SELECT * FROM t1; a b c 13 3 4 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT DEFAULT SIGN(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT sign(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES (-10),(0), (10); SELECT * FROM t1; a b -10 -1 0 0 10 1 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(30), b INT DEFAULT FIELD(a, 'Hej', 'ej', 'Heja', 'hej', 'foo')); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(30) DEFAULT NULL, `b` int(11) DEFAULT field(`a`,'Hej','ej','Heja','hej','foo') ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('ej'); SELECT * FROM t1; a b ej 2 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; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(30) DEFAULT NULL, `b` int(11) DEFAULT find_in_set(`a`,'Hej,ej,Heja,hej,foo') ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('ej'); SELECT * FROM t1; a b ej 2 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(30), b INT DEFAULT ASCII(a), c INT DEFAULT ORD(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(30) DEFAULT NULL, `b` int(11) DEFAULT ascii(`a`), `c` int(11) DEFAULT ord(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('a'); SELECT * FROM t1; a b c a 97 97 DROP TABLE t1; CREATE TABLE t1 (a TEXT DEFAULT UUID_SHORT()); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` text DEFAULT uuid_short() ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (); SELECT a>0 FROM t1; a>0 1 DROP TABLE t1; CREATE TABLE t1 (a INT DEFAULT BENCHMARK(1,1)); ERROR HY000: Function or expression 'benchmark()' cannot be used in the DEFAULT clause of `a` CREATE TABLE t1 (a INT DEFAULT GET_LOCK('a',1)); ERROR HY000: Function or expression 'get_lock()' cannot be used in the DEFAULT clause of `a` CREATE TABLE t1 (a INT DEFAULT RELEASE_LOCK('a')); ERROR HY000: Function or expression 'release_lock()' cannot be used in the DEFAULT clause of `a` CREATE TABLE t1 (a INT DEFAULT IS_USED_LOCK('a')); ERROR HY000: Function or expression 'is_used_lock()' cannot be used in the DEFAULT clause of `a` CREATE TABLE t1 (a INT DEFAULT IS_FREE_LOCK('a')); ERROR HY000: Function or expression 'is_free_lock()' cannot be used in the DEFAULT clause of `a` CREATE TABLE t1 (a INT DEFAULT SLEEP(1)); ERROR HY000: Function or expression 'sleep()' cannot be used in the DEFAULT clause of `a` CREATE TABLE t1 (a INT DEFAULT ROW_COUNT()); ERROR HY000: Function or expression 'row_count()' cannot be used in the DEFAULT clause of `a` CREATE TABLE t1 (a INT DEFAULT FOUND_ROWS()); ERROR HY000: Function or expression 'found_rows()' cannot be used in the DEFAULT clause of `a` CREATE TABLE t1 (a INT DEFAULT MASTER_POS_WAIT('test',100)); ERROR HY000: Function or expression 'master_pos_wait()' cannot be used in the DEFAULT clause of `a` CREATE TABLE t1 (a INT DEFAULT MASTER_GTID_WAIT('test')); ERROR HY000: Function or expression 'master_gtid_wait()' cannot be used in the DEFAULT clause of `a` CREATE TABLE t1 (a VARCHAR(30), b DOUBLE DEFAULT MATCH (a) AGAINST('bbbb' IN BOOLEAN MODE)); ERROR HY000: Function or expression 'match ... against()' cannot be used in the DEFAULT clause of `b` # # Temporal functions # # Item_temporal_hybrid_func CREATE TABLE t1 (a DATE, b INT, c DATE DEFAULT DATE_ADD(a, INTERVAL b DAY)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` date DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` date DEFAULT (`a` + interval `b` day) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES ('2001-01-01', 30, DEFAULT); SELECT * FROM t1; a b c 2001-01-01 30 2001-01-31 DROP TABLE t1; CREATE TABLE t1 (a DATE, b TIME, c DATETIME DEFAULT ADDTIME(a, b)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` date DEFAULT NULL, `b` time DEFAULT NULL, `c` datetime DEFAULT addtime(`a`,`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES ('2001-01-01', '10:20:30', DEFAULT); SELECT * FROM t1; a b c 2001-01-01 10:20:30 2001-01-01 10:20:30 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(32), b VARCHAR(32), c DATE DEFAULT STR_TO_DATE(a,b)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(32) DEFAULT NULL, `b` varchar(32) DEFAULT NULL, `c` date DEFAULT str_to_date(`a`,`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES ('01,5,2013','%d,%m,%Y', DEFAULT); SELECT * FROM t1; a b c 01,5,2013 %d,%m,%Y 2013-05-01 DROP TABLE t1; # 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; Table Create Table t1 CREATE TABLE `t1` ( `a` date DEFAULT curdate(), `b` date DEFAULT utc_date() ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (); SELECT * FROM t1; a b 2001-01-01 2001-01-02 DROP TABLE t1; SET time_zone=DEFAULT, timestamp= DEFAULT; CREATE TABLE t1 (a INT, b DATE DEFAULT FROM_DAYS(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` date DEFAULT from_days(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (730669, DEFAULT); SELECT * FROM t1; a b 730669 2000-07-03 DROP TABLE t1; CREATE TABLE t1 (a DATE, b DATE DEFAULT LAST_DAY(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` date DEFAULT NULL, `b` date DEFAULT last_day(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES ('2003-02-05', DEFAULT); SELECT * FROM t1; a b 2003-02-05 2003-02-28 DROP TABLE t1; CREATE TABLE t1 (yy INT, yd INT, d DATE DEFAULT MAKEDATE(yy, yd)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `yy` int(11) DEFAULT NULL, `yd` int(11) DEFAULT NULL, `d` date DEFAULT makedate(`yy`,`yd`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (2011,32,DEFAULT); SELECT * FROM t1; yy yd d 2011 32 2011-02-01 DROP TABLE t1; # 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; Table Create Table t1 CREATE TABLE `t1` ( `a` time DEFAULT curtime(), `b` time DEFAULT utc_time() ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (); SELECT * FROM t1; a b 23:59:59 09:59:59 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; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` time DEFAULT sec_to_time(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (2378, DEFAULT); SELECT * FROM t1; a b 2378 00:39:38 DROP TABLE t1; CREATE TABLE t1 (a DATETIME, b DATETIME, c TIME DEFAULT TIMEDIFF(a,b)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` datetime DEFAULT NULL, `b` datetime DEFAULT NULL, `c` time DEFAULT timediff(`a`,`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES ('2000:01:01 00:00:00', '2000:01:02 10:20:30', DEFAULT); SELECT * FROM t1; a b c 2000-01-01 00:00:00 2000-01-02 10:20:30 -34:20:30 DROP TABLE t1; CREATE TABLE t1 (hh INT, mm INT, ss INT, t TIME DEFAULT MAKETIME(hh,mm,ss)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `hh` int(11) DEFAULT NULL, `mm` int(11) DEFAULT NULL, `ss` int(11) DEFAULT NULL, `t` time DEFAULT maketime(`hh`,`mm`,`ss`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (10,20,30,DEFAULT); SELECT * FROM t1; hh mm ss t 10 20 30 10:20:30 DROP TABLE t1; # 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; Table Create Table t1 CREATE TABLE `t1` ( `a` timestamp NULL DEFAULT current_timestamp(), `b` timestamp NULL DEFAULT utc_timestamp() ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (); SELECT * FROM t1; a b 2001-01-01 23:59:59 2001-01-02 09:59:59 DROP TABLE t1; SET time_zone=DEFAULT, timestamp= DEFAULT; CREATE TABLE t1 (a TIMESTAMP(6) DEFAULT SYSDATE(6), s INT, b TIMESTAMP(6) DEFAULT SYSDATE(6)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` timestamp(6) NULL DEFAULT sysdate(6), `s` int(11) DEFAULT NULL, `b` timestamp(6) NULL DEFAULT sysdate(6) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (DEFAULT(a), SLEEP(0.1), DEFAULT(b)); SELECT b>a FROM t1; b>a 1 DROP TABLE t1; SET time_zone='+00:00'; CREATE TABLE t1 (a INT, b TIMESTAMP DEFAULT FROM_UNIXTIME(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` timestamp NULL DEFAULT from_unixtime(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (1447430881, DEFAULT); SELECT * FROM t1; a b 1447430881 2015-11-13 16:08:01 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; Table Create Table t1 CREATE TABLE `t1` ( `a` timestamp NULL DEFAULT NULL, `b` timestamp NULL DEFAULT convert_tz(`a`,'-10:00','+10:00') ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES ('2001-01-01 10:20:30', DEFAULT); SELECT * FROM t1; a b 2001-01-01 10:20:30 2001-01-02 06:20:30 DROP TABLE t1; # Item_temporal_typecast CREATE TABLE t1 (a INT, b DATE DEFAULT CAST(a AS DATE)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` date DEFAULT cast(`a` as date) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (20010203, DEFAULT); SELECT * FROM t1; a b 20010203 2001-02-03 DROP TABLE t1; CREATE TABLE t1 (a INT, b TIME DEFAULT CAST(a AS TIME)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` time DEFAULT cast(`a` as time) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (102030, DEFAULT); SELECT * FROM t1; a b 102030 10:20:30 DROP TABLE t1; CREATE TABLE t1 (a BIGINT, b DATETIME DEFAULT CAST(a AS DATETIME)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` bigint(20) DEFAULT NULL, `b` datetime DEFAULT cast(`a` as datetime) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (20010203102030, DEFAULT); SELECT * FROM t1; a b 20010203102030 2001-02-03 10:20:30 DROP TABLE t1; # # Functions with temporal input # CREATE TABLE t1 (a INT, b INT, c INT DEFAULT PERIOD_ADD(a,b)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT period_add(`a`,`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a,b) VALUES (200801, 2); SELECT * FROM t1; a b c 200801 2 200803 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, c INT DEFAULT PERIOD_DIFF(a,b)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT period_diff(`a`,`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a,b) VALUES (200802, 200703); SELECT * FROM t1; a b c 200802 200703 11 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT DEFAULT TO_DAYS(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT to_days(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES (950501); SELECT * FROM t1; a b 950501 728779 DROP TABLE t1; CREATE TABLE t1 (a DATE, b INT DEFAULT TO_DAYS(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` date DEFAULT NULL, `b` int(11) DEFAULT to_days(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('2007-10-07'); SELECT * FROM t1; a b 2007-10-07 733321 DROP TABLE t1; CREATE TABLE t1 (a INT, b BIGINT DEFAULT TO_SECONDS(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` bigint(20) DEFAULT to_seconds(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES (950501); SELECT * FROM t1; a b 950501 62966505600 DROP TABLE t1; CREATE TABLE t1 (a DATE, b BIGINT DEFAULT TO_SECONDS(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` date DEFAULT NULL, `b` bigint(20) DEFAULT to_seconds(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('2009-11-29'); SELECT * FROM t1; a b 2009-11-29 63426672000 DROP TABLE t1; CREATE TABLE t1 (a DATETIME, b BIGINT DEFAULT TO_SECONDS(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` datetime DEFAULT NULL, `b` bigint(20) DEFAULT to_seconds(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('2009-11-29 13:43:32'); SELECT * FROM t1; a b 2009-11-29 13:43:32 63426721412 DROP TABLE t1; CREATE TABLE t1 (a DATE, b BIGINT DEFAULT DAYOFMONTH(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` date DEFAULT NULL, `b` bigint(20) DEFAULT dayofmonth(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('2007-02-03'); SELECT * FROM t1; a b 2007-02-03 3 DROP TABLE t1; CREATE TABLE t1 (a DATE, b BIGINT DEFAULT DAYOFWEEK(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` date DEFAULT NULL, `b` bigint(20) DEFAULT dayofweek(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('2007-02-03'); SELECT * FROM t1; a b 2007-02-03 7 DROP TABLE t1; CREATE TABLE t1 (a DATE, b BIGINT DEFAULT DAYOFYEAR(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` date DEFAULT NULL, `b` bigint(20) DEFAULT dayofyear(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('2007-02-03'); SELECT * FROM t1; a b 2007-02-03 34 DROP TABLE t1; CREATE TABLE t1 (a TIME, b INT DEFAULT HOUR(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` time DEFAULT NULL, `b` int(11) DEFAULT hour(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('10:05:03'); SELECT * FROM t1; a b 10:05:03 10 DROP TABLE t1; CREATE TABLE t1 (a TIME, b INT DEFAULT MINUTE(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` time DEFAULT NULL, `b` int(11) DEFAULT minute(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('10:05:03'); SELECT * FROM t1; a b 10:05:03 5 DROP TABLE t1; CREATE TABLE t1 (a TIME, b INT DEFAULT SECOND(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` time DEFAULT NULL, `b` int(11) DEFAULT second(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('10:05:03'); SELECT * FROM t1; a b 10:05:03 3 DROP TABLE t1; CREATE TABLE t1 (a DATETIME(6), b INT DEFAULT MICROSECOND(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` datetime(6) DEFAULT NULL, `b` int(11) DEFAULT microsecond(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('2009-12-31 23:59:59.000010'); SELECT * FROM t1; a b 2009-12-31 23:59:59.000010 10 DROP TABLE t1; CREATE TABLE t1 (a DATE, b INT DEFAULT YEAR(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` date DEFAULT NULL, `b` int(11) DEFAULT year(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('1987-01-01'); SELECT * FROM t1; a b 1987-01-01 1987 DROP TABLE t1; CREATE TABLE t1 (a DATE, b INT DEFAULT MONTH(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` date DEFAULT NULL, `b` int(11) DEFAULT month(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('1987-01-01'); SELECT * FROM t1; a b 1987-01-01 1 DROP TABLE t1; CREATE TABLE t1 (a DATE, b INT DEFAULT WEEK(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` date DEFAULT NULL, `b` int(11) DEFAULT week(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('1987-02-01'); SELECT * FROM t1; a b 1987-02-01 5 DROP TABLE t1; CREATE TABLE t1 (a DATE, b INT DEFAULT YEARWEEK(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` date DEFAULT NULL, `b` int(11) DEFAULT yearweek(`a`,0) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('2000-01-01'); SELECT * FROM t1; a b 2000-01-01 199952 DROP TABLE t1; CREATE TABLE t1 (a DATE, b INT DEFAULT QUARTER(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` date DEFAULT NULL, `b` int(11) DEFAULT quarter(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('2008-04-01'); SELECT * FROM t1; a b 2008-04-01 2 DROP TABLE t1; CREATE TABLE t1 (a DATE, b INT DEFAULT EXTRACT(YEAR FROM a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` date DEFAULT NULL, `b` int(11) DEFAULT extract(year from `a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('2009-07-02'); SELECT * FROM t1; a b 2009-07-02 2009 DROP TABLE t1; CREATE TABLE t1 (a DATETIME, b INT DEFAULT EXTRACT(YEAR_MONTH FROM a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` datetime DEFAULT NULL, `b` int(11) DEFAULT extract(year_month from `a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('2009-07-02 01:02:03'); SELECT * FROM t1; a b 2009-07-02 01:02:03 200907 DROP TABLE t1; CREATE TABLE t1 (a DATETIME, b INT DEFAULT EXTRACT(DAY_MINUTE FROM a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` datetime DEFAULT NULL, `b` int(11) DEFAULT extract(day_minute from `a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('2009-07-02 01:02:03'); SELECT * FROM t1; a b 2009-07-02 01:02:03 20102 DROP TABLE t1; CREATE TABLE t1 (a DATETIME(6), b INT DEFAULT EXTRACT(MICROSECOND FROM a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` datetime(6) DEFAULT NULL, `b` int(11) DEFAULT extract(microsecond from `a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('2009-07-02 01:02:03.000123'); SELECT * FROM t1; a b 2009-07-02 01:02:03.000123 123 DROP TABLE t1; CREATE TABLE t1 (a DATE, b DATE, c INT DEFAULT TIMESTAMPDIFF(MONTH,a,b)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` date DEFAULT NULL, `b` date DEFAULT NULL, `c` int(11) DEFAULT timestampdiff(MONTH,`a`,`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a,b) VALUES ('2003-02-01','2003-05-01'); SELECT * FROM t1; a b c 2003-02-01 2003-05-01 3 DROP TABLE t1; CREATE TABLE t1 (a DATE, b DATE, c INT DEFAULT TIMESTAMPDIFF(YEAR,a,b)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` date DEFAULT NULL, `b` date DEFAULT NULL, `c` int(11) DEFAULT timestampdiff(YEAR,`a`,`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a,b) VALUES ('2002-05-01','2001-01-01'); SELECT * FROM t1; a b c 2002-05-01 2001-01-01 -1 DROP TABLE t1; CREATE TABLE t1 (a DATE, b DATETIME, c INT DEFAULT TIMESTAMPDIFF(MINUTE,a,b)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` date DEFAULT NULL, `b` datetime DEFAULT NULL, `c` int(11) DEFAULT timestampdiff(MINUTE,`a`,`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a,b) VALUES ('2003-02-01','2003-05-01 12:05:55'); SELECT * FROM t1; a b c 2003-02-01 2003-05-01 12:05:55 128885 DROP TABLE t1; CREATE OR REPLACE TABLE t1 ( col INT DEFAULT ( 1 LIKE ( NOW() BETWEEN '2000-01-01' AND '2012-12-12' ) ) ); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `col` int(11) DEFAULT (1 like (current_timestamp() between '2000-01-01' and '2012-12-12')) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SET timestamp = UNIX_TIMESTAMP( '2004-04-04' ); INSERT INTO t1 VALUES( DEFAULT ); SET timestamp = DEFAULT; INSERT INTO t1 VALUES( DEFAULT ); SELECT * FROM t1; col 1 0 DROP TABLE t1; # # Hybrid type functions # CREATE TABLE t1 (a INT, b INT, c INT DEFAULT COALESCE(a,b)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT coalesce(`a`,`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (NULL, 1, DEFAULT); SELECT * FROM t1; a b c NULL 1 1 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, c INT DEFAULT IFNULL(a,b)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT ifnull(`a`,`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (NULL, 2, DEFAULT); INSERT INTO t1 VALUES (1, 2, DEFAULT); SELECT * FROM t1; a b c NULL 2 2 1 2 1 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, c INT DEFAULT NULLIF(a,b)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT nullif(`a`,`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (1, 1, DEFAULT); INSERT INTO t1 VALUES (1, 2, DEFAULT); SELECT * FROM t1; a b c 1 1 NULL 1 2 1 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, c INT DEFAULT IF(a,b,2)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT if(`a`,`b`,2) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (0, 1, DEFAULT); INSERT INTO t1 VALUES (1, 1, DEFAULT); SELECT * FROM t1; a b c 0 1 2 1 1 1 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; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT (case when `a` then `b` else 2 end) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (0, 1, DEFAULT); INSERT INTO t1 VALUES (1, 1, DEFAULT); SELECT * FROM t1; a b c 0 1 2 1 1 1 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT DEFAULT (-a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT (-`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT (-`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (10, DEFAULT); SELECT * FROM t1; a b 10 -10 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT DEFAULT ABS(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT abs(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (-10, DEFAULT); SELECT * FROM t1; a b -10 10 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; Table Create Table t1 CREATE TABLE `t1` ( `a` double DEFAULT NULL, `b` int(11) DEFAULT ceiling(`a`), `c` int(11) DEFAULT floor(`a`), `d` int(11) DEFAULT round(`a`,0) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (1.5, DEFAULT, DEFAULT, DEFAULT); INSERT INTO t1 VALUES (-1.5, DEFAULT, DEFAULT, DEFAULT); SELECT * FROM t1; a b c d 1.5 2 1 2 -1.5 -1 -2 -2 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a+b), d INT DEFAULT (a-b)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT (`a` + `b`), `d` int(11) DEFAULT (`a` - `b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (2, 1, DEFAULT, DEFAULT); SELECT * FROM t1; a b c d 2 1 3 1 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; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT (`a` * `b`), `d` int(11) DEFAULT (`a` / `b`), `e` int(11) DEFAULT (`a` MOD `b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT (`a` * `b`), `d` int(11) DEFAULT (`a` / `b`), `e` int(11) DEFAULT (`a` MOD `b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (7, 3, DEFAULT, DEFAULT, DEFAULT); SELECT * FROM t1; a b c d e 7 3 21 2 1 DROP TABLE t1; SET time_zone='+00:00'; CREATE TABLE t1 (a DATETIME, b INT DEFAULT UNIX_TIMESTAMP(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` datetime DEFAULT NULL, `b` int(11) DEFAULT unix_timestamp(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES ('2001-01-01 10:20:30', DEFAULT); SELECT * FROM t1; a b 2001-01-01 10:20:30 978344430 DROP TABLE t1; SET time_zone=DEFAULT; CREATE TABLE t1 (a TIME, b INT DEFAULT TIME_TO_SEC(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` time DEFAULT NULL, `b` int(11) DEFAULT time_to_sec(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES ('22:23:00', DEFAULT); SELECT * FROM t1; a b 22:23:00 80580 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; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT least(`a`,`b`), `d` int(11) DEFAULT greatest(`a`,`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (0, 1, DEFAULT, DEFAULT); INSERT INTO t1 VALUES (1, 1, DEFAULT, DEFAULT); SELECT * FROM t1; a b c d 0 1 0 1 1 1 1 1 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, c INT DEFAULT LAST_VALUE(a,b)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT last_value(`a`,`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (1, 2, DEFAULT); SELECT * FROM t1; a b c 1 2 2 DROP TABLE t1; # # CAST # CREATE TABLE t1 (a VARCHAR(30), b DECIMAL(10,6) DEFAULT CAST(a AS DECIMAL(10,1))); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(30) DEFAULT NULL, `b` decimal(10,6) DEFAULT (cast(`a` as decimal(10,1))) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('123.456'); SELECT * FROM t1; a b 123.456 123.500000 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; Table Create Table t1 CREATE TABLE `t1` ( `a` decimal(10,3) DEFAULT NULL, `b` varchar(10) DEFAULT (cast(`a` as char(10) charset latin1)), `c` varchar(10) DEFAULT (cast(`a` as char(4) charset latin1)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT IGNORE INTO t1 (a) VALUES (123.456); Warnings: Warning 1292 Truncated incorrect CHAR(4) value: '123.456' SELECT * FROM t1; a b c 123.456 123.456 123. DROP TABLE t1; CREATE TABLE t1 (a INT, b INT UNSIGNED DEFAULT CAST(a AS UNSIGNED)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(10) unsigned DEFAULT (cast(`a` as unsigned)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT IGNORE INTO t1 (a) VALUES (-1); Warnings: Note 1105 Cast to unsigned converted negative integer to it's positive complement Warning 1264 Out of range value for column 'b' at row 1 SELECT * FROM t1; a b -1 4294967295 DROP TABLE t1; CREATE TABLE t1 (a BIGINT UNSIGNED, b BIGINT SIGNED DEFAULT CAST(a AS SIGNED)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` bigint(20) unsigned DEFAULT NULL, `b` bigint(20) DEFAULT (cast(`a` as signed)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES (0xFFFFFFFFFFFFFFFF); SELECT * FROM t1; a b 18446744073709551615 -1 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; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, `b` varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT `a`, `c` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT convert(`a` using utf8mb3), `d` varbinary(10) DEFAULT (cast(`a` as char charset binary)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('a'); SELECT * FROM t1; a b c d a a a a DROP TABLE t1; # # Bit functions # CREATE TABLE t1 (a INT, b INT DEFAULT BIT_COUNT(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT bit_count(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES (7); SELECT * FROM t1; a b 7 3 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a|b)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT (`a` | `b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a,b) VALUES (1,2); SELECT * FROM t1; a b c 1 2 3 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a&b)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT (`a` & `b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a,b) VALUES (5,4); SELECT * FROM t1; a b c 5 4 4 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a^b)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT (`a` ^ `b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a,b) VALUES (11,3); SELECT * FROM t1; a b c 11 3 8 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a&~b)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT (`a` & ~`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a,b) VALUES (5,1); SELECT * FROM t1; a b c 5 1 4 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a<>b)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT (`a` << `b`), `d` int(11) DEFAULT (`a` >> `b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a,b) VALUES (5,1); SELECT * FROM t1; a b c d 5 1 10 2 DROP TABLE t1; # # String functions # CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(20) DEFAULT REVERSE(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, `b` varchar(20) DEFAULT reverse(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('abcd'); SELECT * FROM t1; a b abcd dcba 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; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, `b` varchar(10) DEFAULT ucase(`a`), `c` varchar(10) DEFAULT lcase(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('ABcd'); SELECT * FROM t1; a b c ABcd ABCD abcd 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; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, `b` varchar(10) DEFAULT left(`a`,1), `c` varchar(10) DEFAULT right(`a`,1), `d` varchar(10) DEFAULT substr(`a`,2,2) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('abcd'); SELECT * FROM t1; a b c d abcd a d bc DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20) DEFAULT SUBSTRING_INDEX(a,'.',2)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(20) DEFAULT NULL, `b` varchar(20) DEFAULT substring_index(`a`,'.',2) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('www.mariadb.org'); SELECT * FROM t1; a b www.mariadb.org www.mariadb DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10), c VARCHAR(20) DEFAULT CONCAT(a,b)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, `b` varchar(10) DEFAULT NULL, `c` varchar(20) DEFAULT concat(`a`,`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a,b) VALUES ('a','b'); SELECT * FROM t1; a b c a b ab DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10), c VARCHAR(20) DEFAULT CONCAT_WS(',',a,b)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, `b` varchar(10) DEFAULT NULL, `c` varchar(20) DEFAULT concat_ws(',',`a`,`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a,b) VALUES ('a','b'); SELECT * FROM t1; a b c a b a,b DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT REPLACE(a,'a','A')); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, `b` varchar(10) DEFAULT replace(`a`,'a','A') ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('abc'); SELECT * FROM t1; a b abc Abc DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT REGEXP_REPLACE(a,'[0-9]','.')); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, `b` varchar(10) DEFAULT regexp_replace(`a`,'[0-9]','.') ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('a1b2c'); SELECT * FROM t1; a b a1b2c a.b.c DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT REGEXP_SUBSTR(a,'[0-9]+')); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, `b` varchar(10) DEFAULT regexp_substr(`a`,'[0-9]+') ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('ab12cd'); SELECT * FROM t1; a b ab12cd 12 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20) DEFAULT SOUNDEX(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(20) DEFAULT NULL, `b` varchar(20) DEFAULT soundex(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('tester'); SELECT * FROM t1; a b tester T236 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20) DEFAULT QUOTE(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(20) DEFAULT NULL, `b` varchar(20) DEFAULT quote(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('a\'b'); SELECT * FROM t1; a b a'b 'a\'b' 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; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, `b` varchar(10) DEFAULT lpad(`a`,10,'.'), `c` varchar(10) DEFAULT rpad(`a`,10,'.') ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('ab'); SELECT * FROM t1; a b c ab ........ab ab........ 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; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, `b` varchar(10) DEFAULT ltrim(`a`), `c` varchar(10) DEFAULT rtrim(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES (' ab '); SELECT a, HEX(b), HEX(c) FROM t1; a HEX(b) HEX(c) ab 616220 206162 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT TRIM(BOTH 'a' FROM a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, `b` varchar(10) DEFAULT trim(both 'a' from `a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('abba'); SELECT a, b FROM t1; a b abba bb DROP TABLE t1; CREATE TABLE t1 (a INT, b VARCHAR(10) DEFAULT SPACE(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` varchar(10) DEFAULT space(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES (3); SELECT a, HEX(b) FROM t1; a HEX(b) 3 202020 DROP TABLE t1; CREATE TABLE t1 (a INT, b VARCHAR(10), c VARCHAR(10) DEFAULT REPEAT(b,a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` varchar(10) DEFAULT NULL, `c` varchar(10) DEFAULT repeat(`b`,`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a,b) VALUES (3,'x'); SELECT a, b, c FROM t1; a b c 3 x xxx 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; Table Create Table t1 CREATE TABLE `t1` ( `str` varchar(10) DEFAULT NULL, `pos` int(11) DEFAULT NULL, `len` int(11) DEFAULT NULL, `newstr` varchar(10) DEFAULT NULL, `result` varchar(10) DEFAULT insert(`str`,`pos`,`len`,`newstr`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (str,pos,len,newstr) VALUES ('Quadratic', 3, 4, 'What'); SELECT * FROM t1; str pos len newstr result Quadratic 3 4 What QuWhattic DROP TABLE t1; CREATE TABLE t1 (n INT, res VARCHAR(10) DEFAULT ELT(n,'ej', 'Heja', 'hej', 'foo')); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `n` int(11) DEFAULT NULL, `res` varchar(10) DEFAULT elt(`n`,'ej','Heja','hej','foo') ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (n) VALUES (1); SELECT * FROM t1; n res 1 ej DROP TABLE t1; CREATE TABLE t1 (bits INT, res VARCHAR(10) DEFAULT MAKE_SET(bits,'a','b','c','d')); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `bits` int(11) DEFAULT NULL, `res` varchar(10) DEFAULT make_set(`bits`,'a','b','c','d') ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (bits) VALUES (1|4); SELECT * FROM t1; bits res 5 a,c DROP TABLE t1; CREATE TABLE t1 (a INT, b VARCHAR(10) DEFAULT CHAR(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` varchar(10) DEFAULT char(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES (77); SELECT * FROM t1; a b 77 M DROP TABLE t1; CREATE TABLE t1 (a INT, b VARCHAR(10) DEFAULT CONV(a,10,16)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` varchar(10) DEFAULT conv(`a`,10,16) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES (64); SELECT * FROM t1; a b 64 40 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, c VARCHAR(30) DEFAULT FORMAT(a,b)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` varchar(30) DEFAULT format(`a`,`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a,b) VALUES (10000,3); SELECT * FROM t1; a b c 10000 3 10,000.000 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; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `l` varchar(10) DEFAULT NULL, `c` varchar(30) DEFAULT format(`a`,`b`,`l`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a,b,l) VALUES (10000,2,'no_NO'),(10000,2,'ru_RU'),(10000,2,'ar_BH'); SELECT * FROM t1; a b l c 10000 2 no_NO 10.000,00 10000 2 ru_RU 10 000,00 10000 2 ar_BH 10,000.00 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(20) DEFAULT GET_FORMAT(DATE,a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, `b` varchar(20) DEFAULT get_format(DATE, `a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('EUR'),('USA'),('JIS'),('ISO'),('INTERNAL'); SELECT * FROM t1; a b EUR %d.%m.%Y USA %m.%d.%Y JIS %Y-%m-%d ISO %Y-%m-%d INTERNAL %Y%m%d 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; Table Create Table t1 CREATE TABLE `t1` ( `bits` int(11) DEFAULT NULL, `v_on` varchar(10) DEFAULT NULL, `v_off` varchar(10) DEFAULT NULL, `v_separator` varchar(10) DEFAULT NULL, `number_of_bits` int(11) DEFAULT NULL, `x` varchar(30) DEFAULT export_set(`bits`,`v_on`,`v_off`,`v_separator`,`number_of_bits`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT IGNORE INTO t1 VALUES (0x50006,'Y','N','',64,DEFAULT); Warnings: Warning 1265 Data truncated for column 'x' at row 1 SELECT * FROM t1; bits v_on v_off v_separator number_of_bits x 327686 Y N 64 NYYNNNNNNNNNNNNNYNYNNNNNNNNNNN DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(30), b BLOB DEFAULT LOAD_FILE(a)); ERROR HY000: Function or expression 'load_file()' cannot be used in the DEFAULT clause of `b` # # Predicates # CREATE TABLE t1 (a INT, b INT DEFAULT (NOT a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT (`a` = 0) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES (NULL),(0),(1); SELECT * FROM t1; a b NULL NULL 0 1 1 0 DROP TABLE t1; CREATE TABLE t1 (a INT, b INT, x INT DEFAULT (a XOR b)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `x` int(11) DEFAULT (`a` xor `b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a,b) VALUES (0,0),(0,1),(1,0),(1,1); SELECT * FROM t1; a b x 0 0 0 0 1 1 1 0 1 1 1 0 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; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT (`a` is true), `c` int(11) DEFAULT (`a` is not true) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES (NULL),(0),(1); SELECT * FROM t1; a b c NULL 0 1 0 0 1 1 1 0 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; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT (`a` is false), `c` int(11) DEFAULT (`a` is not false) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES (NULL),(0),(1); SELECT * FROM t1; a b c NULL 0 1 0 1 0 1 0 1 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; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT (`a` is null), `c` int(11) DEFAULT (`a` is not null) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES (NULL),(0),(1); SELECT * FROM t1; a b c NULL 1 0 0 0 1 1 0 1 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; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT (`a` is null), `c` int(11) DEFAULT (`a` is not null) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES (NULL),(0),(1); SELECT * FROM t1; a b c NULL 1 0 0 0 1 1 0 1 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; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `eq` int(11) DEFAULT (`a` = 0), `equal` int(11) DEFAULT (`a` <=> 0), `ne` int(11) DEFAULT (`a` <> 0), `lt` int(11) DEFAULT (`a` < 0), `le` int(11) DEFAULT (`a` <= 0), `gt` int(11) DEFAULT (`a` > 0), `ge` int(11) DEFAULT (`a` >= 0) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES (NULL),(-1),(0),(1); SELECT * FROM t1; a eq equal ne lt le gt ge NULL NULL 0 NULL NULL NULL NULL NULL -1 0 0 1 1 1 0 0 0 1 1 0 0 1 0 1 1 0 0 1 0 0 1 1 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a LIKE 'a%')); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, `b` int(11) DEFAULT (`a` like 'a%') ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'); SELECT * FROM t1; a b AAA 1 aaa 1 bbb 0 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a RLIKE 'a$')); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, `b` int(11) DEFAULT (`a` regexp 'a$') ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'); SELECT * FROM t1; a b AAA 1 aaa 1 bbb 0 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a IN ('aaa','bbb'))); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, `b` int(11) DEFAULT (`a` in ('aaa','bbb')) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'),('ccc'); SELECT * FROM t1; a b AAA 1 aaa 1 bbb 1 ccc 0 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a NOT IN ('aaa','bbb'))); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, `b` int(11) DEFAULT (`a` not in ('aaa','bbb')) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'),('ccc'); SELECT * FROM t1; a b AAA 0 aaa 0 bbb 0 ccc 1 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a BETWEEN 'aaa' AND 'bbb')); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, `b` int(11) DEFAULT (`a` between 'aaa' and 'bbb') ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'),('ccc'); SELECT * FROM t1; a b AAA 1 aaa 1 bbb 1 ccc 0 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a NOT BETWEEN 'aaa' AND 'bbb')); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, `b` int(11) DEFAULT (`a` not between 'aaa' and 'bbb') ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'),('ccc'); SELECT * FROM t1; a b AAA 0 aaa 0 bbb 0 ccc 1 DROP TABLE t1; CREATE TABLE t1 (a TEXT DEFAULT UUID()); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` text DEFAULT uuid() ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (); SELECT LENGTH(a)>0 FROM t1; LENGTH(a)>0 1 DROP TABLE t1; # # Numeric result functions with string input # CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT STRCMP(a,'b')); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, `b` int(11) DEFAULT strcmp(`a`,'b') ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('A'),('a'),('B'),('b'),('C'),('c'); SELECT * FROM t1; a b A -1 a -1 B 0 b 0 C 1 c 1 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; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, `b` int(11) DEFAULT octet_length(`a`), `c` int(11) DEFAULT char_length(`a`), `d` int(11) DEFAULT bit_length(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('a'),('aa'),('aaa'); SELECT * FROM t1; a b c d a 1 1 8 aa 2 2 16 aaa 3 3 24 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT LOCATE('a',a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, `b` int(11) DEFAULT locate('a',`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('xa'),('xxa'),('xxxa'); SELECT * FROM t1; a b xa 2 xxa 3 xxxa 4 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT REGEXP_INSTR(a, 'a')); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, `b` int(11) DEFAULT regexp_instr(`a`,'a') ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('xa'),('xxa'),('xxxa'); SELECT * FROM t1; a b xa 2 xxa 3 xxxa 4 DROP TABLE t1; # # INT result metadata functions # CREATE TABLE t1 ( id SERIAL PRIMARY KEY, b INT DEFAULT LAST_INSERT_ID() ); ERROR HY000: Function or expression 'last_insert_id()' cannot be used in the DEFAULT clause of `b` CREATE TABLE t1 (a INT DEFAULT CONNECTION_ID()); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT connection_id() ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES(); SELECT a>0 FROM t1; a>0 1 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT COERCIBILITY(a), c INT DEFAULT COERCIBILITY(b)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, `b` int(11) DEFAULT coercibility(`a`), `c` int(11) DEFAULT coercibility(`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('test'); SELECT * FROM t1; a b c test 2 5 DROP TABLE t1; # # String result metadata functions # 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; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, `b` varchar(20) DEFAULT charset(`a`), `c` varchar(20) DEFAULT collation(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('test'); SELECT * FROM t1; a b c test latin1 latin1_swedish_ci DROP TABLE t1; # # Hash, compression, encode/decode # CREATE TABLE t1 (a VARCHAR(10), b BIGINT DEFAULT CRC32(a), c TEXT DEFAULT MD5(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, `b` bigint(20) DEFAULT crc32(`a`), `c` text DEFAULT md5(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('a'); SELECT * FROM t1; a b c a 3904355907 0cc175b9c0f1b6a831c399e269772661 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; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, `b` text DEFAULT to_base64(`a`), `c` text DEFAULT from_base64(`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('aaaabbbb'); SELECT * FROM t1; a b c aaaabbbb YWFhYWJiYmI= aaaabbbb DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10), b TEXT DEFAULT HEX(a), c TEXT DEFAULT UNHEX(b)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, `b` text DEFAULT hex(`a`), `c` text DEFAULT unhex(`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('aaaabbbb'); SELECT * FROM t1; a b c aaaabbbb 6161616162626262 aaaabbbb 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; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) DEFAULT NULL, `b` text DEFAULT encode(`a`,'test'), `c` text DEFAULT decode(`b`,'test') ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('aaaabbbb'); SELECT a, HEX(b), c FROM t1; a HEX(b) c aaaabbbb 059AEEE33E4AF848 aaaabbbb DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(30), b TEXT DEFAULT PASSWORD(a)); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(30) DEFAULT NULL, `b` text DEFAULT password(`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('notagoodpwd'); SELECT * FROM t1; a b notagoodpwd *3A70EE9FC6594F88CE9E959CD51C5A1C002DC937 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; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(30) DEFAULT NULL, `b` blob DEFAULT aes_encrypt(`a`,'passwd'), `c` text DEFAULT aes_decrypt(`b`,'passwd') ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (a) VALUES ('test'); SELECT c FROM t1; c test DROP TABLE t1; # # Collations # CREATE TABLE t1 (a VARCHAR(20) CHARACTER SET latin1 DEFAULT CONCAT('ö')) CHARACTER SET koi8r COLLATE koi8r_bin; ERROR 22007: Encountered illegal value 'ö' when converting to koi8r CREATE OR REPLACE TABLE t1 (a char(2) default concat('A') COLLATE utf8mb4_unicode_ci); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT concat('A') ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1; 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; a b c 1 2 1 1 2 NULL drop table t1; create table t1 (a int default 1, b int default (1+1), c int); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT 1, `b` int(11) DEFAULT (1 + 1), `c` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci alter table t1 alter a set default (2+3), alter b set default 4, alter c set default (-a); alter table t1 alter a set default 1+2; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '+2' at line 1 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT (2 + 3), `b` int(11) DEFAULT 4, `c` int(11) DEFAULT (-`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci drop table t1; 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; Table Create Table t2 CREATE TABLE `t2` ( `a` int(11) DEFAULT 5 CHECK (`a` > 10), `b` int(11) DEFAULT (5 + 5), `c` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci show create table t3; Table Create Table t3 CREATE TABLE `t3` ( `max(a)` int(11) DEFAULT NULL, `max(b)` int(11) DEFAULT NULL, `max(c)` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci drop table t1, t2, t3; # # 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); insert into t1 values (ignore, ignore); ERROR 23000: Duplicate entry '0' for key 'PRIMARY' select * from t1 order by a; a b 0 3 1 3 2 3 3 3 4 6 6 7 delete from t1 where a < 4; # actually insert default instead of ignoring # (but REPLACE is non standard operator) replace into t1 values (4, ignore); select * from t1 order by a; a b 4 3 6 7 drop table t1; 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; a b c NULL 20 x NULL 25 x drop table t1; 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; a 0 1 3 truncate table t1; INSERT INTO t1 SELECT a FROM t2 ON DUPLICATE KEY UPDATE a=IGNORE; SELECT * FROM t1 order by a; a 0 1 3 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; execute insstmt using ignore, ignore; ERROR 23000: Duplicate entry '0' for key 'PRIMARY' select * from t1 order by a; a b 0 3 1 3 2 3 3 3 4 6 6 7 delete from t1 where a < 4; execute repstmt using 4, ignore; select * from t1 order by a; a b 4 3 6 7 drop table t1; # # DEVAULT & PS adoption # CREATE TABLE t1 (a INT DEFAULT 10, b INT DEFAULT NULL); EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?,?)' USING IGNORE, IGNORE; SELECT * FROM t1; a b 10 NULL UPDATE t1 SET a=20, b=30; SELECT * FROM t1; a b 20 30 EXECUTE IMMEDIATE 'UPDATE t1 SET a=?,b=?' USING IGNORE, IGNORE; SELECT * FROM t1; a b 20 30 DROP TABLE t1; CREATE TABLE t1 (a INT DEFAULT 10); EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?+1)' USING IGNORE; ERROR HY000: Default/ignore value is not supported for such parameter usage EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (CONCAT(?,?))' USING IGNORE, 'test'; ERROR HY000: Default/ignore value is not supported for such parameter usage DROP TABLE t1; CREATE TABLE t1 (a INT DEFAULT 10); INSERT INTO t1 VALUES (20); EXECUTE IMMEDIATE 'UPDATE t1 SET a=?+1' USING IGNORE; ERROR HY000: Default/ignore value is not supported for such parameter usage EXECUTE IMMEDIATE 'UPDATE t1 SET a=CONCAT(?,?)' USING IGNORE, 'test'; ERROR HY000: Default/ignore value is not supported for such parameter usage DROP TABLE t1; EXECUTE IMMEDIATE 'SELECT CAST(? AS SIGNED)' USING IGNORE; ERROR HY000: Default/ignore value is not supported for such parameter usage EXECUTE IMMEDIATE 'SELECT CAST(? AS DOUBLE)' USING IGNORE; ERROR HY000: Default/ignore value is not supported for such parameter usage EXECUTE IMMEDIATE 'SELECT CAST(? AS CHAR)' USING IGNORE; ERROR HY000: Default/ignore value is not supported for such parameter usage EXECUTE IMMEDIATE 'SELECT CAST(? AS DECIMAL(10,1))' USING IGNORE; ERROR HY000: Default/ignore value is not supported for such parameter usage EXECUTE IMMEDIATE 'SELECT CAST(? AS TIME)' USING IGNORE; ERROR HY000: Default/ignore value is not supported for such parameter usage EXECUTE IMMEDIATE 'SELECT CAST(? AS DATE)' USING IGNORE; ERROR HY000: Default/ignore value is not supported for such parameter usage EXECUTE IMMEDIATE 'SELECT CAST(? AS DATETIME)' USING IGNORE; ERROR HY000: Default/ignore value is not supported for such parameter usage EXECUTE IMMEDIATE 'SELECT ?+1' USING IGNORE; ERROR HY000: Default/ignore value is not supported for such parameter usage EXECUTE IMMEDIATE 'SELECT CONCAT(?,?)' USING IGNORE,'test'; ERROR HY000: Default/ignore value is not supported for such parameter usage EXECUTE IMMEDIATE 'SELECT 1 LIMIT ?' USING IGNORE; ERROR HY000: Default/ignore value is not supported for such parameter usage CREATE TABLE t1 (a INT DEFAULT 10); INSERT INTO t1 VALUES (1),(2),(3); EXECUTE IMMEDIATE 'SELECT * FROM t1 LIMIT ?' USING IGNORE; ERROR HY000: Default/ignore value is not supported for such parameter usage DROP TABLE t1; # The output of this query in 'Note' is a syntactically incorrect query. # But as it's never logged, it's ok. It should be human readable only. EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT ?' USING IGNORE; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 select ignore AS `?` CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2),(3); EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>?+a' USING DEFAULT,DEFAULT; ERROR HY000: Default/ignore value is not supported for such parameter usage DROP TABLE t1; # # MDEV-11134 Assertion `fixed' failed in Item::const_charset_converter(THD*, CHARSET_INFO*, bool, const char*) # 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; Table Create Table t1 CREATE TABLE `t1` ( `c` char(8) DEFAULT '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1; SET @a='A'; EXECUTE stmt USING @a; EXECUTE stmt USING @a; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c` char(8) DEFAULT 'A' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1; SET @a=_utf8 0xC380; EXECUTE stmt USING @a; EXECUTE stmt USING @a; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c` char(8) DEFAULT 'À' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1; SET @a=_utf8 0xD18F; EXECUTE stmt USING @a; ERROR 42000: Invalid default value for 'c' EXECUTE stmt USING @a; ERROR 42000: Invalid default value for 'c' DEALLOCATE PREPARE stmt; set sql_mode=ansi_quotes; create table t1 (a int, b int default (a+1)); show create table t1; Table Create Table t1 CREATE TABLE "t1" ( "a" int(11) DEFAULT NULL, "b" int(11) DEFAULT ("a" + 1) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci insert t1 (a) values (10); set sql_mode=''; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT (`a` + 1) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci insert t1 (a) values (20); flush tables; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT (`a` + 1) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci insert t1 (a) values (30); select * from t1; a b 10 11 20 21 30 31 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 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'); 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; a b t 5 5 1 column is omitted 4 5 2 column gets DEFAULT, keyword 4 5 3 column gets DEFAULT(a), expression 4 5 4 also expression DEFAULT(0)+0 4 5 5 the value of the DEFAULT(a), that is b 5 5 6 reversed, column gets DEFAULT, keyword 5 5 7 reversed, column gets DEFAULT(a), expression 5 5 8 reversed, also expression DEFAULT(0)+0 5 5 9 reversed, the value of the DEFAULT(a), that is b drop table t1; # # MDEV-10352 Server crashes in Field::set_default on CREATE TABLE # create table t1 (col1 int default(-(default(col1)))); ERROR 01000: Expression for field `col1` is referring to uninitialized field `col1` # # MDEV-10354 Assertion `! is_set()' failed in Diagnostics_area::set_ok_status on CREATE TABLE with invalid default # create table t1 (col int default (yearweek((exp(710))))); ERROR 22003: DOUBLE value is out of range in 'exp(710)' # # MDEV-13707 Server in ORACLE mode crashes on ALTER with wrong DEFAULT clause # CREATE OR REPLACE TABLE t1(i int); ALTER TABLE t1 ADD b CHAR(255) DEFAULT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa`; ERROR 42S22: Unknown column 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' in 'DEFAULT' DROP TABLE t1; # # MDEV-18278 Misleading error message in error log upon failed table creation # create table t1 (a int as (a)); ERROR 01000: Expression for field `a` is referring to uninitialized field `a` show warnings; Level Code Message Error 4029 Expression for field `a` is referring to uninitialized field `a` # # MDEV-26423: MariaDB server crash in Create_tmp_table::finalize # CREATE TABLE t1 (pk varchar(36) DEFAULT uuid()); INSERT INTO t1 VALUES (),(); SELECT 1 FROM t1 GROUP BY DEFAULT(pk); 1 1 1 DROP TABLE t1; # # MDEV-28402: ASAN heap-use-after-free in create_tmp_table, # Assertion `l_offset >= 0 && table->s->rec_buff_length - l_offset > 0' # 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; DEFAULT(a) CASE a WHEN 0 THEN 1 ELSE 2 END NULL 2 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; DEFAULT(a) CASE a WHEN 0 THEN 1 ELSE 2 END NULL 2 DROP TABLE t; DROP VIEW v; # # End of 10.2 test # # # MDEV-22703 DEFAULT() on a BLOB column can overwrite the default # record, which can cause crashes when accessing already released # memory. # CREATE TEMPORARY TABLE t1 (h POINT DEFAULT ST_GEOMFROMTEXT('Point(1 1)')) ENGINE=InnoDB; INSERT INTO t1 () VALUES (),(); ALTER TABLE t1 FORCE; SELECT DEFAULT(h) FROM t1; SELECT length(DEFAULT(h)) FROM t1; length(DEFAULT(h)) 25 25 INSERT INTO t1 () VALUES (); drop table t1; # # End of 10.3 test # # # MDEV-26423: MariaDB server crash in Create_tmp_table::finalize # CREATE TABLE t1 (pk text DEFAULT length(uuid())); INSERT INTO t1 VALUES (),(); SELECT 1 FROM t1 GROUP BY DEFAULT(pk); 1 1 DROP TABLE t1; # # MDEV-29890 Update with inner join false row count result # 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'; column_name column_default has_default is_nullable a NULL 0 NO 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'; column_name column_default has_default is_nullable a NULL 0 NO 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'; column_name column_default has_default is_nullable a NULL 0 NO 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'; column_name column_default has_default is_nullable a NULL 1 YES drop view v1; drop table t1; # # End of 10.4 test #