1drop table if exists t1,t2,t3,t4,t5,t6; 2drop database if exists mysqltest; 3set sql_mode=""; 4CREATE TABLE t1 (a varchar(30) binary NOT NULL DEFAULT ' ', 5b varchar(1) binary NOT NULL DEFAULT ' ', 6c varchar(4) binary NOT NULL DEFAULT '0000', 7d tinyblob NULL, 8e tinyblob NULL, 9f tinyblob NULL, 10g tinyblob NULL, 11h tinyblob NULL, 12i tinyblob NULL, 13j tinyblob NULL, 14k tinyblob NULL, 15l tinyblob NULL, 16m tinyblob NULL, 17n tinyblob NULL, 18o tinyblob NULL, 19p tinyblob NULL, 20q varchar(30) binary NOT NULL DEFAULT ' ', 21r varchar(30) binary NOT NULL DEFAULT ' ', 22s tinyblob NULL, 23t varchar(4) binary NOT NULL DEFAULT ' ', 24u varchar(1) binary NOT NULL DEFAULT ' ', 25v varchar(30) binary NOT NULL DEFAULT ' ', 26w varchar(30) binary NOT NULL DEFAULT ' ', 27x tinyblob NULL, 28y varchar(5) binary NOT NULL DEFAULT ' ', 29z varchar(20) binary NOT NULL DEFAULT ' ', 30a1 varchar(30) binary NOT NULL DEFAULT ' ', 31b1 tinyblob NULL) 32ENGINE=InnoDB DEFAULT CHARACTER SET = latin1 COLLATE latin1_bin; 33set sql_mode=default; 34INSERT into t1 (b) values ('1'); 35SHOW WARNINGS; 36Level Code Message 37SELECT * from t1; 38a 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 39 1 0000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 40CREATE TABLE t2 (a varchar(30) binary NOT NULL DEFAULT ' ', 41b varchar(1) binary NOT NULL DEFAULT ' ', 42c varchar(4) binary NOT NULL DEFAULT '0000', 43d tinyblob NULL, 44e tinyblob NULL, 45f tinyblob NULL, 46g tinyblob NULL, 47h tinyblob NULL, 48i tinyblob NULL, 49j tinyblob NULL, 50k tinyblob NULL, 51l tinyblob NULL, 52m tinyblob NULL, 53n tinyblob NULL, 54o tinyblob NULL, 55p tinyblob NULL, 56q varchar(30) binary NOT NULL DEFAULT ' ', 57r varchar(30) binary NOT NULL DEFAULT ' ', 58s tinyblob NULL, 59t varchar(4) binary NOT NULL DEFAULT ' ', 60u varchar(1) binary NOT NULL DEFAULT ' ', 61v varchar(30) binary NOT NULL DEFAULT ' ', 62w varchar(30) binary NOT NULL DEFAULT ' ', 63x tinyblob NULL, 64y varchar(5) binary NOT NULL DEFAULT ' ', 65z varchar(20) binary NOT NULL DEFAULT ' ', 66a1 varchar(30) binary NOT NULL DEFAULT ' ', 67b1 tinyblob NULL) 68ENGINE=MyISAM DEFAULT CHARACTER SET = latin1 COLLATE latin1_bin; 69SHOW CREATE TABLE t2; 70Table Create Table 71t2 CREATE TABLE `t2` ( 72 `a` varchar(30) COLLATE latin1_bin NOT NULL DEFAULT ' ', 73 `b` varchar(1) COLLATE latin1_bin NOT NULL DEFAULT ' ', 74 `c` varchar(4) COLLATE latin1_bin NOT NULL DEFAULT '0000', 75 `d` tinyblob DEFAULT NULL, 76 `e` tinyblob DEFAULT NULL, 77 `f` tinyblob DEFAULT NULL, 78 `g` tinyblob DEFAULT NULL, 79 `h` tinyblob DEFAULT NULL, 80 `i` tinyblob DEFAULT NULL, 81 `j` tinyblob DEFAULT NULL, 82 `k` tinyblob DEFAULT NULL, 83 `l` tinyblob DEFAULT NULL, 84 `m` tinyblob DEFAULT NULL, 85 `n` tinyblob DEFAULT NULL, 86 `o` tinyblob DEFAULT NULL, 87 `p` tinyblob DEFAULT NULL, 88 `q` varchar(30) COLLATE latin1_bin NOT NULL DEFAULT ' ', 89 `r` varchar(30) COLLATE latin1_bin NOT NULL DEFAULT ' ', 90 `s` tinyblob DEFAULT NULL, 91 `t` varchar(4) COLLATE latin1_bin NOT NULL DEFAULT ' ', 92 `u` varchar(1) COLLATE latin1_bin NOT NULL DEFAULT ' ', 93 `v` varchar(30) COLLATE latin1_bin NOT NULL DEFAULT ' ', 94 `w` varchar(30) COLLATE latin1_bin NOT NULL DEFAULT ' ', 95 `x` tinyblob DEFAULT NULL, 96 `y` varchar(5) COLLATE latin1_bin NOT NULL DEFAULT ' ', 97 `z` varchar(20) COLLATE latin1_bin NOT NULL DEFAULT ' ', 98 `a1` varchar(30) COLLATE latin1_bin NOT NULL DEFAULT ' ', 99 `b1` tinyblob DEFAULT NULL 100) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin 101INSERT into t2 (b) values ('1'); 102SHOW WARNINGS; 103Level Code Message 104SELECT * from t2; 105a 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 106 1 0000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 107drop table t1; 108drop table t2; 109create table bug20691 (i int, d datetime NOT NULL, dn datetime not null default '0000-00-00 00:00:00'); 110insert ignore into bug20691 values (1, DEFAULT, DEFAULT), (1, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (1, DEFAULT, DEFAULT); 111Warnings: 112Warning 1364 Field 'd' doesn't have a default value 113Warning 1364 Field 'd' doesn't have a default value 114insert ignore into bug20691 (i) values (2); 115Warnings: 116Warning 1364 Field 'd' doesn't have a default value 117desc bug20691; 118Field Type Null Key Default Extra 119i int(11) YES NULL 120d datetime NO NULL 121dn datetime NO 0000-00-00 00:00:00 122insert ignore into bug20691 values (3, DEFAULT, DEFAULT), (3, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (3, DEFAULT, DEFAULT); 123Warnings: 124Warning 1364 Field 'd' doesn't have a default value 125Warning 1364 Field 'd' doesn't have a default value 126insert ignore into bug20691 (i) values (4); 127Warnings: 128Warning 1364 Field 'd' doesn't have a default value 129insert ignore into bug20691 values (5, DEFAULT, DEFAULT), (5, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (5, DEFAULT, DEFAULT); 130Warnings: 131Warning 1364 Field 'd' doesn't have a default value 132Warning 1364 Field 'd' doesn't have a default value 133SET sql_mode = 'ALLOW_INVALID_DATES'; 134insert into bug20691 values (6, DEFAULT, DEFAULT), (6, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (6, DEFAULT, DEFAULT); 135Warnings: 136Warning 1364 Field 'd' doesn't have a default value 137Warning 1364 Field 'd' doesn't have a default value 138SET sql_mode = 'STRICT_ALL_TABLES'; 139insert into bug20691 values (7, DEFAULT, DEFAULT), (7, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (7, DEFAULT, DEFAULT); 140ERROR HY000: Field 'd' doesn't have a default value 141select * from bug20691 order by i asc; 142i d dn 1431 0000-00-00 00:00:00 0000-00-00 00:00:00 1441 1975-07-10 07:10:03 1978-01-13 14:08:51 1451 0000-00-00 00:00:00 0000-00-00 00:00:00 1462 0000-00-00 00:00:00 0000-00-00 00:00:00 1473 0000-00-00 00:00:00 0000-00-00 00:00:00 1483 1975-07-10 07:10:03 1978-01-13 14:08:51 1493 0000-00-00 00:00:00 0000-00-00 00:00:00 1504 0000-00-00 00:00:00 0000-00-00 00:00:00 1515 0000-00-00 00:00:00 0000-00-00 00:00:00 1525 1975-07-10 07:10:03 1978-01-13 14:08:51 1535 0000-00-00 00:00:00 0000-00-00 00:00:00 1546 0000-00-00 00:00:00 0000-00-00 00:00:00 1556 1975-07-10 07:10:03 1978-01-13 14:08:51 1566 0000-00-00 00:00:00 0000-00-00 00:00:00 157drop table bug20691; 158SET sql_mode = ''; 159create table bug20691 ( 160a set('one', 'two', 'three') not null, 161b enum('small', 'medium', 'large', 'enormous', 'ellisonego') not null, 162c time not null, 163d date not null, 164e int not null, 165f long not null, 166g blob not null, 167h datetime not null, 168i decimal not null, 169x int); 170insert into bug20691 values (2, 3, 5, '0007-01-01', 11, 13, 17, '0019-01-01 00:00:00', 23, 1); 171insert into bug20691 (x) values (2); 172Warnings: 173Warning 1364 Field 'a' doesn't have a default value 174Warning 1364 Field 'c' doesn't have a default value 175Warning 1364 Field 'd' doesn't have a default value 176Warning 1364 Field 'e' doesn't have a default value 177Warning 1364 Field 'f' doesn't have a default value 178Warning 1364 Field 'g' doesn't have a default value 179Warning 1364 Field 'h' doesn't have a default value 180Warning 1364 Field 'i' doesn't have a default value 181insert into bug20691 values (2, 3, 5, '0007-01-01', 11, 13, 17, '0019-01-01 00:00:00', 23, 3); 182insert into bug20691 values (DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, 4); 183Warnings: 184Warning 1364 Field 'a' doesn't have a default value 185Warning 1364 Field 'c' doesn't have a default value 186Warning 1364 Field 'd' doesn't have a default value 187Warning 1364 Field 'e' doesn't have a default value 188Warning 1364 Field 'f' doesn't have a default value 189Warning 1364 Field 'g' doesn't have a default value 190Warning 1364 Field 'h' doesn't have a default value 191Warning 1364 Field 'i' doesn't have a default value 192select * from bug20691 order by x asc; 193a b c d e f g h i x 194two large 00:00:05 0007-01-01 11 13 17 0019-01-01 00:00:00 23 1 195 small 00:00:00 0000-00-00 0 0000-00-00 00:00:00 0 2 196two large 00:00:05 0007-01-01 11 13 17 0019-01-01 00:00:00 23 3 197 small 00:00:00 0000-00-00 0 0000-00-00 00:00:00 0 4 198drop table bug20691; 199create table t1 (id int not null); 200insert into t1 values(default); 201Warnings: 202Warning 1364 Field 'id' doesn't have a default value 203create view v1 (c) as select id from t1; 204insert into t1 values(default); 205Warnings: 206Warning 1364 Field 'id' doesn't have a default value 207drop view v1; 208drop table t1; 209create table t1 (a int unique); 210create table t2 (b int default 10); 211insert into t1 (a) values (1); 212insert into t2 (b) values (1); 213insert into t1 (a) select b from t2 on duplicate key update a=default; 214select * from t1; 215a 216NULL 217insert into t1 (a) values (1); 218insert into t1 (a) select b from t2 on duplicate key update a=default(b); 219select * from t1; 220a 221NULL 22210 223drop table t1, t2; 224# End of 5.0 tests 225# 226# Start of 10.0 tests 227# 228# 229# MDEV-11265 Access defied when CREATE VIIEW v1 AS SELECT DEFAULT(column) FROM t1 230# 231CREATE TABLE t1 (a INT DEFAULT 10); 232INSERT INTO t1 VALUES (11); 233CREATE VIEW v1 AS SELECT a AS a FROM t1; 234CREATE VIEW v2 AS SELECT DEFAULT(a) AS a FROM t1; 235CREATE VIEW v3 AS SELECT VALUE(a) AS a FROM t1; 236SELECT * FROM v1; 237a 23811 239SELECT * FROM v2; 240a 24110 242SELECT * FROM v3; 243a 244NULL 245UPDATE v2 SET a=123; 246ERROR HY000: Column 'a' is not updatable 247UPDATE v3 SET a=123; 248ERROR HY000: Column 'a' is not updatable 249DROP VIEW v3; 250DROP VIEW v2; 251DROP VIEW v1; 252DROP TABLE t1; 253# 254# MDEV-10780 Server crashes in in create_tmp_table 255# 256connect con1,localhost,root,,test; 257CREATE TABLE t1 (pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=MyISAM; 258INSERT INTO t1 VALUES (); 259INSERT INTO t1 VALUES (); 260SELECT DISTINCT DEFAULT (pk) FROM t1 GROUP BY RAND() WITH ROLLUP; 261DEFAULT (pk) 2620 263disconnect con1; 264connection default; 265DROP TABLE t1; 266# 267# End of 10.0 tests 268# 269# 270# Start of 10.1 tests 271# 272CREATE TABLE t1 (a INT DEFAULT 100, b INT DEFAULT NULL); 273INSERT INTO t1 VALUES (); 274SELECT * FROM t1 WHERE DEFAULT(a); 275a b 276100 NULL 277SELECT * FROM t1 WHERE DEFAULT(b); 278a b 279DROP TABLE IF EXISTS t1; 280# 281# End of 10.1 tests 282# 283# 284# Start of 10.2 tests 285# 286SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30.123456'); 287# 288# Check that CURRENT_TIMESTAMP works as before 289# 290CREATE or replace TABLE t1 (event_time TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 291SHOW CREATE TABLE t1; 292Table Create Table 293t1 CREATE TABLE `t1` ( 294 `event_time` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6) 295) ENGINE=MyISAM DEFAULT CHARSET=latin1 296CREATE or replace TABLE t1 (event_time TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(2) ON UPDATE CURRENT_TIMESTAMP); 297SHOW CREATE TABLE t1; 298Table Create Table 299t1 CREATE TABLE `t1` ( 300 `event_time` timestamp(6) NOT NULL DEFAULT current_timestamp(2) ON UPDATE current_timestamp(6) 301) ENGINE=MyISAM DEFAULT CHARSET=latin1 302CREATE or replace TABLE t1 (event_time TIMESTAMP(6) NOT NULL DEFAULT SYSDATE(2) ON UPDATE CURRENT_TIMESTAMP); 303SHOW CREATE TABLE t1; 304Table Create Table 305t1 CREATE TABLE `t1` ( 306 `event_time` timestamp(6) NOT NULL DEFAULT sysdate(2) ON UPDATE current_timestamp(6) 307) ENGINE=MyISAM DEFAULT CHARSET=latin1 308drop table t1; 309# 310# Check default expressions 311# 312create or replace table t1 (a int default 1, b int default (a+1), c int default (a+b)) engine myisam; 313show create table t1; 314Table Create Table 315t1 CREATE TABLE `t1` ( 316 `a` int(11) DEFAULT 1, 317 `b` int(11) DEFAULT (`a` + 1), 318 `c` int(11) DEFAULT (`a` + `b`) 319) ENGINE=MyISAM DEFAULT CHARSET=latin1 320insert into t1 values (); 321insert into t1 (a) values (2); 322insert into t1 (a,b) values (10,20); 323insert into t1 (a,b,c) values (100,200,400); 324select * from t1; 325a b c 3261 2 3 3272 3 5 32810 20 30 329100 200 400 330truncate table t1; 331insert delayed into t1 values (); 332insert delayed into t1 (a) values (2); 333insert delayed into t1 (a,b) values (10,20); 334insert delayed into t1 (a,b,c) values (100,200,400); 335flush tables t1; 336select * from t1; 337a b c 3381 2 3 3392 3 5 34010 20 30 341100 200 400 342create or replace table t1 (a int, b blob default (1), c blob default "hello", t text default concat(a,b,c)) engine=myisam; 343show create table t1; 344Table Create Table 345t1 CREATE TABLE `t1` ( 346 `a` int(11) DEFAULT NULL, 347 `b` blob DEFAULT 1, 348 `c` blob DEFAULT 'hello', 349 `t` text DEFAULT concat(`a`,`b`,`c`) 350) ENGINE=MyISAM DEFAULT CHARSET=latin1 351insert into t1 (a) values (2); 352insert into t1 (a,b) values (10,"test1"); 353insert into t1 (a,b,c) values (10,"test2","test3"); 354insert delayed into t1 (a,b) values (10,"test4"); 355flush tables t1; 356select * from t1; 357a b c t 3582 1 hello 21hello 35910 test1 hello 10test1hello 36010 test2 test3 10test2test3 36110 test4 hello 10test4hello 362drop table t1; 363create or replace table t1 (a bigint default uuid_short()); 364insert into t1 values(); 365select a > 0 from t1; 366a > 0 3671 368drop table t1; 369create or replace table t1 (param_list int DEFAULT (1+1) NOT NULL); 370create or replace table t1 (param_list int DEFAULT 1+1 NOT NULL); 371ERROR 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 372create or replace table t1 (param_list blob DEFAULT "" NOT NULL); 373drop table t1; 374create table t1 (a int); 375insert into t1 values(-1); 376alter table t1 add b int default 1, add c int default -1, add d int default (1+1), add e timestamp; 377select a,b,c,d,e from t1; 378a b c d e 379-1 1 -1 2 2001-01-01 10:20:30 380insert into t1 values(10,10,10,10,0); 381alter 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); 382select a,b,c,d,e,f,g,h from t1; 383a b c d e f g h 384-1 1 -1 2 2001-01-01 10:20:30 3 4 8 38510 10 10 10 0000-00-00 00:00:00 3 4 8 386show create table t1; 387Table Create Table 388t1 CREATE TABLE `t1` ( 389 `a` int(11) DEFAULT NULL, 390 `b` int(11) DEFAULT 1, 391 `c` int(11) DEFAULT -1, 392 `d` int(11) DEFAULT (1 + 1), 393 `e` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), 394 `f` int(11) DEFAULT (1 + 1 + 1), 395 `g` int(11) NOT NULL DEFAULT (1 + 1 + 1 + 1), 396 `h` int(11) DEFAULT (2 + 2 + 2 + 2) 397) ENGINE=MyISAM DEFAULT CHARSET=latin1 398create table t2 like t1; 399show create table t2; 400Table Create Table 401t2 CREATE TABLE `t2` ( 402 `a` int(11) DEFAULT NULL, 403 `b` int(11) DEFAULT 1, 404 `c` int(11) DEFAULT -1, 405 `d` int(11) DEFAULT (1 + 1), 406 `e` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), 407 `f` int(11) DEFAULT (1 + 1 + 1), 408 `g` int(11) NOT NULL DEFAULT (1 + 1 + 1 + 1), 409 `h` int(11) DEFAULT (2 + 2 + 2 + 2) 410) ENGINE=MyISAM DEFAULT CHARSET=latin1 411insert into t2 (a) values (100); 412select a,b,c,d,e,f,g,h from t2; 413a b c d e f g h 414100 1 -1 2 2001-01-01 10:20:30 3 4 8 415drop table t1,t2; 416create table t1 (a int default (1----1), b int default - 1, c int default +1, e int default (--1)); 417show create table t1; 418Table Create Table 419t1 CREATE TABLE `t1` ( 420 `a` int(11) DEFAULT (1 - -1), 421 `b` int(11) DEFAULT -1, 422 `c` int(11) DEFAULT 1, 423 `e` int(11) DEFAULT 1 424) ENGINE=MyISAM DEFAULT CHARSET=latin1 425insert into t1 values(); 426insert into t1 values(); 427select * from t1; 428a b c e 4292 -1 1 1 4302 -1 1 1 431drop table t1; 432# 433# Create or replace can delete a table on error 434# 435create table t1 (a int); 436create or replace table t1 (a int default b, b int default a); 437ERROR 01000: Expression for field `a` is referring to uninitialized field `b` 438show create table t1; 439ERROR 42S02: Table 'test.t1' doesn't exist 440# 441# Refering to other columns 442# 443create or replace table t1 (a int default 1, b int default a); 444create or replace table t1 (a int default 1, b int as (a)); 445create or replace table t1 (a int default b, b int default 1); 446create or replace table t1 (a int as (b), b int default 1); 447create or replace table t1 (a int as (b), b int default (1+1)); 448create or replace table t1 (a int default 1, b int as (c), c int default (a+1)); 449create or replace table t1 (a int default (1+1), b int as (c), c int default (a+1)); 450create or replace table t1 (a varchar(128) default @@version); 451create or replace table t1 (a int not null, b int as (a)); 452create or replace table t1 (a int not null, b int default (a+1)); 453create or replace table t1 (a int default a); 454ERROR 01000: Expression for field `a` is referring to uninitialized field `a` 455create or replace table t1 (a int default b, b int default (1+1)); 456create or replace table t1 (a int default 1, b int as (c), c int as (a+1)); 457ERROR 01000: Expression for field `b` is referring to uninitialized field `c` 458CREATE TABLE t1 (a INT DEFAULT (DEFAULT(a))); 459ERROR 01000: Expression for field `a` is referring to uninitialized field `a` 460CREATE TABLE t1 (a INT DEFAULT(DEFAULT(b)), b INT DEFAULT(DEFAULT(a))); 461ERROR 01000: Expression for field `a` is referring to uninitialized field `b` 462CREATE TABLE t1 (a INT DEFAULT(DEFAULT(b)) NOT NULL, b INT DEFAULT(DEFAULT(a)) NOT NULL); 463ERROR 01000: Expression for field `a` is referring to uninitialized field `b` 464# 465# Allow defaults to refer to not default fields 466# 467create or replace table t1 (a int default b, b int not null); 468insert into t1 values(); 469Warnings: 470Warning 1364 Field 'b' doesn't have a default value 471insert into t1 (a) values(1); 472Warnings: 473Warning 1364 Field 'b' doesn't have a default value 474insert into t1 (b) values(2); 475insert into t1 (a,b) values(3,4); 476select * from t1; 477a b 4780 0 4791 0 4802 2 4813 4 482drop table t1; 483CREATE OR REPLACE TABLE t1 (a INT DEFAULT @v); 484drop table t1; 485CREATE TABLE t1 (a INT DEFAULT @v:=1); 486drop table t1; 487# 488# Error handling 489# 490create or replace table t1 (a bigint default xxx()); 491ERROR HY000: Function or expression '`xxx`()' cannot be used in the DEFAULT clause of `a` 492create or replace table t1 (a bigint default (select (1))); 493ERROR HY000: Function or expression 'select ...' cannot be used in the DEFAULT clause of `a` 494create or replace table t1 (a bigint default (1,2,3)); 495ERROR 21000: Operand should contain 1 column(s) 496create or replace table t1 (a bigint default ((1,2,3))); 497ERROR 21000: Operand should contain 1 column(s) 498CREATE TABLE t1 (a INT, b INT, c INT DEFAULT a DIV b); 499ERROR 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 500CREATE TABLE t1 (a INT, b INT DEFAULT -a); 501ERROR 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 502# 503# Invalid DEFAULT expressions 504# 505CREATE TABLE t1 (a INT DEFAULT ((SELECT 1))); 506ERROR HY000: Function or expression 'select ...' cannot be used in the DEFAULT clause of `a` 507CREATE TABLE t1 (a INT DEFAULT (EXISTS (SELECT 1))); 508ERROR HY000: Function or expression 'select ...' cannot be used in the DEFAULT clause of `a` 509CREATE TABLE t1 (a INT DEFAULT (1=ANY (SELECT 1))); 510ERROR HY000: Function or expression 'select ...' cannot be used in the DEFAULT clause of `a` 511CREATE TABLE t1 (a INT DEFAULT ROW(1,1)); 512ERROR 21000: Operand should contain 1 column(s) 513CREATE TABLE t1 (a INT DEFAULT (1,1)); 514ERROR 21000: Operand should contain 1 column(s) 515CREATE TABLE t1 (a INT DEFAULT ((1,1))); 516ERROR 21000: Operand should contain 1 column(s) 517CREATE TABLE t1 (a INT DEFAULT ?); 518Got one of the listed errors 519CREATE TABLE t1 (a INT DEFAULT(?)); 520Got one of the listed errors 521CREATE TABLE t1 (a INT DEFAULT (b), b INT DEFAULT(a)); 522ERROR 01000: Expression for field `a` is referring to uninitialized field `b` 523CREATE TABLE t1 (a INT DEFAULT(NAME_CONST('xxx', 'yyy')); 524ERROR HY000: Function or expression 'name_const()' cannot be used in the DEFAULT clause of `a` 525CREATE TABLE t1 (a INT DEFAULT COUNT(*)); 526ERROR HY000: Function or expression 'count()' cannot be used in the DEFAULT clause of `a` 527CREATE TABLE t1 (a INT DEFAULT COUNT(1)); 528ERROR HY000: Function or expression 'count()' cannot be used in the DEFAULT clause of `a` 529CREATE TABLE t1 (a INT DEFAULT AVG(1)); 530ERROR HY000: Function or expression 'avg()' cannot be used in the DEFAULT clause of `a` 531CREATE TABLE t1 (a INT DEFAULT MIN(1)); 532ERROR HY000: Function or expression 'min()' cannot be used in the DEFAULT clause of `a` 533CREATE TABLE t1 (a INT DEFAULT GROUP_CONCAT(1)); 534ERROR HY000: Function or expression 'group_concat()' cannot be used in the DEFAULT clause of `a` 535CREATE TABLE t1 (a INT DEFAULT ROW_NUMBER() OVER ()); 536ERROR HY000: Function or expression 'row_number()' cannot be used in the DEFAULT clause of `a` 537CREATE FUNCTION f1() RETURNS INT RETURN 1; 538CREATE TABLE t1 (a INT DEFAULT f1()); 539ERROR HY000: Function or expression '`f1`()' cannot be used in the DEFAULT clause of `a` 540DROP FUNCTION f1; 541CREATE PROCEDURE p1(par INT) CREATE TABLE t1 (a INT DEFAULT par); 542ERROR HY000: Function or expression 'par' cannot be used in the DEFAULT clause of `a` 543CREATE TABLE t1 (a INT DEFAULT par); 544ERROR 42S22: Unknown column 'par' in 'DEFAULT' 545CREATE PROCEDURE p1() CREATE TABLE t1 (a INT DEFAULT par); 546CALL p1; 547ERROR 42S22: Unknown column 'par' in 'DEFAULT' 548DROP PROCEDURE p1; 549CREATE TABLE t1 (a INT DEFAULT VALUE(a)); 550ERROR HY000: Function or expression 'value()' cannot be used in the DEFAULT clause of `a` 551CREATE TABLE t1 (a INT); 552CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW CREATE TABLE t2 (a INT DEFAULT NEW.a); 553ERROR HY000: Function or expression 'NEW.a' cannot be used in the DEFAULT clause of `a` 554CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW CREATE TEMPORARY TABLE t2 (a INT DEFAULT NEW.a); 555ERROR HY000: Function or expression 'NEW.a' cannot be used in the DEFAULT clause of `a` 556DROP TABLE t1; 557# 558# Prepared statements 559# 560PREPARE stmt FROM 'CREATE TABLE t1 (a INT DEFAULT(?))'; 561set @a=1; 562execute stmt using @a; 563show create table t1; 564Table Create Table 565t1 CREATE TABLE `t1` ( 566 `a` int(11) DEFAULT 1 567) ENGINE=MyISAM DEFAULT CHARSET=latin1 568drop table t1; 569set @a=-1; 570execute stmt using @a; 571show create table t1; 572Table Create Table 573t1 CREATE TABLE `t1` ( 574 `a` int(11) DEFAULT -1 575) ENGINE=MyISAM DEFAULT CHARSET=latin1 576drop table t1; 577DEALLOCATE PREPARE stmt; 578PREPARE stmt FROM 'CREATE TABLE t1 (a INT DEFAULT(?), b INT DEFAULT(?))'; 579set @a=1, @b=2; 580execute stmt using @a,@b; 581show create table t1; 582Table Create Table 583t1 CREATE TABLE `t1` ( 584 `a` int(11) DEFAULT 1, 585 `b` int(11) DEFAULT 2 586) ENGINE=MyISAM DEFAULT CHARSET=latin1 587drop table t1; 588DEALLOCATE PREPARE stmt; 589prepare stmt from 'create table t1 (a int default(?+?))'; 590set @a=1; 591execute stmt using @a,@a; 592deallocate prepare stmt; 593show create table t1; 594Table Create Table 595t1 CREATE TABLE `t1` ( 596 `a` int(11) DEFAULT (1 + 1) 597) ENGINE=MyISAM DEFAULT CHARSET=latin1 598drop table t1; 599# 600# Parenthesized Item_basic_constant 601# 602CREATE TABLE t1 ( 603i01 INT DEFAULT (((1))), 604i02 INT DEFAULT (((0x3939))), 605i03 INT DEFAULT (((1.0))), 606i04 INT DEFAULT (((1e0))), 607i05 INT DEFAULT (((NULL))), 608f01 DOUBLE DEFAULT (((PI()))), 609s01 VARCHAR(10) DEFAULT (((_latin1'test'))), 610s02 VARCHAR(10) DEFAULT ((('test'))), 611s03 VARCHAR(10) DEFAULT (((0x40))), 612s04 VARCHAR(10) DEFAULT (((X'40'))), 613s05 VARCHAR(10) DEFAULT (((B'1000000'))), 614d01 TIME DEFAULT (((TIME'10:20:30'))), 615d02 DATE DEFAULT (((DATE'2001-01-01'))), 616d03 DATETIME DEFAULT (((TIMESTAMP'2001-01-01 10:20:30'))) 617); 618SHOW CREATE TABLE t1; 619Table Create Table 620t1 CREATE TABLE `t1` ( 621 `i01` int(11) DEFAULT 1, 622 `i02` int(11) DEFAULT 14649, 623 `i03` int(11) DEFAULT 1, 624 `i04` int(11) DEFAULT 1, 625 `i05` int(11) DEFAULT NULL, 626 `f01` double DEFAULT 3.141592653589793, 627 `s01` varchar(10) DEFAULT 'test', 628 `s02` varchar(10) DEFAULT 'test', 629 `s03` varchar(10) DEFAULT '@', 630 `s04` varchar(10) DEFAULT '@', 631 `s05` varchar(10) DEFAULT '@', 632 `d01` time DEFAULT '10:20:30', 633 `d02` date DEFAULT '2001-01-01', 634 `d03` datetime DEFAULT '2001-01-01 10:20:30' 635) ENGINE=MyISAM DEFAULT CHARSET=latin1 636INSERT INTO t1 VALUES (); 637SELECT * FROM t1; 638i01 1 639i02 14649 640i03 1 641i04 1 642i05 NULL 643f01 3.141592653589793 644s01 test 645s02 test 646s03 @ 647s04 @ 648s05 @ 649d01 10:20:30 650d02 2001-01-01 651d03 2001-01-01 10:20:30 652DROP TABLE t1; 653# 654# COALESCE(Item_basic_constant) 655# 656CREATE TABLE t1 ( 657i01 INT DEFAULT COALESCE(1), 658i02 INT DEFAULT COALESCE(0x3939), 659i03 INT DEFAULT COALESCE(1.0), 660i04 INT DEFAULT COALESCE(1e0), 661i05 INT DEFAULT COALESCE(NULL), 662f01 DOUBLE DEFAULT COALESCE(PI()), 663s01 VARCHAR(10) DEFAULT COALESCE(_latin1'test'), 664s02 VARCHAR(10) DEFAULT COALESCE('test'), 665s03 VARCHAR(10) DEFAULT COALESCE(0x40), 666s04 VARCHAR(10) DEFAULT COALESCE(X'40'), 667s05 VARCHAR(10) DEFAULT COALESCE(B'1000000'), 668d01 TIME DEFAULT COALESCE(TIME'10:20:30'), 669d02 DATE DEFAULT COALESCE(DATE'2001-01-01'), 670d03 DATETIME DEFAULT COALESCE(TIMESTAMP'2001-01-01 10:20:30') 671); 672SHOW CREATE TABLE t1; 673Table Create Table 674t1 CREATE TABLE `t1` ( 675 `i01` int(11) DEFAULT coalesce(1), 676 `i02` int(11) DEFAULT coalesce(0x3939), 677 `i03` int(11) DEFAULT coalesce(1.0), 678 `i04` int(11) DEFAULT coalesce(1e0), 679 `i05` int(11) DEFAULT coalesce(NULL), 680 `f01` double DEFAULT coalesce(pi()), 681 `s01` varchar(10) DEFAULT coalesce(_latin1'test'), 682 `s02` varchar(10) DEFAULT coalesce('test'), 683 `s03` varchar(10) DEFAULT coalesce(0x40), 684 `s04` varchar(10) DEFAULT coalesce(X'40'), 685 `s05` varchar(10) DEFAULT coalesce(0x40), 686 `d01` time DEFAULT coalesce(TIME'10:20:30'), 687 `d02` date DEFAULT coalesce(DATE'2001-01-01'), 688 `d03` datetime DEFAULT coalesce(TIMESTAMP'2001-01-01 10:20:30') 689) ENGINE=MyISAM DEFAULT CHARSET=latin1 690INSERT INTO t1 VALUES (); 691SELECT * FROM t1; 692i01 1 693i02 99 694i03 1 695i04 1 696i05 NULL 697f01 3.141592653589793 698s01 test 699s02 test 700s03 @ 701s04 @ 702s05 @ 703d01 10:20:30 704d02 2001-01-01 705d03 2001-01-01 10:20:30 706DROP TABLE t1; 707# 708# TINYINT: out of range 709# 710CREATE TABLE t1 (a TINYINT DEFAULT 300 NOT NULL); 711ERROR 42000: Invalid default value for 'a' 712CREATE TABLE t1 (a TINYINT DEFAULT 128 NOT NULL); 713ERROR 42000: Invalid default value for 'a' 714CREATE TABLE t1 (a TINYINT DEFAULT -500 NOT NULL); 715ERROR 42000: Invalid default value for 'a' 716# 717# INT: simple numeric expressions 718# 719CREATE TABLE t1 (a INT DEFAULT 1 NOT NULL); 720SHOW CREATE TABLE t1; 721Table Create Table 722t1 CREATE TABLE `t1` ( 723 `a` int(11) NOT NULL DEFAULT 1 724) ENGINE=MyISAM DEFAULT CHARSET=latin1 725INSERT INTO t1 VALUES (DEFAULT); 726SELECT * FROM t1; 727a 7281 729DROP TABLE t1; 730CREATE TABLE t1 (a INT DEFAULT COALESCE(1) NOT NULL); 731SHOW CREATE TABLE t1; 732Table Create Table 733t1 CREATE TABLE `t1` ( 734 `a` int(11) NOT NULL DEFAULT coalesce(1) 735) ENGINE=MyISAM DEFAULT CHARSET=latin1 736INSERT INTO t1 VALUES (DEFAULT); 737SELECT * FROM t1; 738a 7391 740DROP TABLE t1; 741# 742# INT: simple string expressions 743# 744CREATE TABLE t1 (a INT DEFAULT '1' NOT NULL); 745SHOW CREATE TABLE t1; 746Table Create Table 747t1 CREATE TABLE `t1` ( 748 `a` int(11) NOT NULL DEFAULT 1 749) ENGINE=MyISAM DEFAULT CHARSET=latin1 750INSERT INTO t1 VALUES (DEFAULT); 751SELECT * FROM t1; 752a 7531 754DROP TABLE t1; 755CREATE TABLE t1 (a INT DEFAULT CONCAT('1') NOT NULL); 756SHOW CREATE TABLE t1; 757Table Create Table 758t1 CREATE TABLE `t1` ( 759 `a` int(11) NOT NULL DEFAULT concat('1') 760) ENGINE=MyISAM DEFAULT CHARSET=latin1 761INSERT INTO t1 VALUES (DEFAULT); 762SELECT * FROM t1; 763a 7641 765DROP TABLE t1; 766CREATE TABLE t1 (a INT DEFAULT COALESCE('1') NOT NULL); 767SHOW CREATE TABLE t1; 768Table Create Table 769t1 CREATE TABLE `t1` ( 770 `a` int(11) NOT NULL DEFAULT coalesce('1') 771) ENGINE=MyISAM DEFAULT CHARSET=latin1 772INSERT INTO t1 VALUES (DEFAULT); 773SELECT * FROM t1; 774a 7751 776DROP TABLE t1; 777# 778# INT: string expressions with garbage 779# 780CREATE TABLE t1 (a INT DEFAULT 'x'); 781ERROR 42000: Invalid default value for 'a' 782CREATE TABLE t1 (a INT DEFAULT CONCAT('x')); 783ERROR 42000: Invalid default value for 'a' 784CREATE TABLE t1 (a INT DEFAULT COALESCE('x')); 785ERROR 42000: Invalid default value for 'a' 786CREATE TABLE t1 (a INT DEFAULT (((((COALESCE('x'))))))); 787ERROR 42000: Invalid default value for 'a' 788# 789# INT: string expressions with numbers + garbage 790# 791CREATE TABLE t1 (a INT DEFAULT '1x'); 792ERROR 42000: Invalid default value for 'a' 793CREATE TABLE t1 (a INT DEFAULT COALESCE('1x')); 794ERROR 42000: Invalid default value for 'a' 795CREATE TABLE t1 (a INT DEFAULT CONCAT('1x')); 796ERROR 42000: Invalid default value for 'a' 797# 798# INT: string expressions with numbers + trailing space 799# 800CREATE TABLE t1 (a INT DEFAULT '1 '); 801Warnings: 802Note 1265 Data truncated for column 'a' at row 1 803SHOW CREATE TABLE t1; 804Table Create Table 805t1 CREATE TABLE `t1` ( 806 `a` int(11) DEFAULT 1 807) ENGINE=MyISAM DEFAULT CHARSET=latin1 808INSERT INTO t1 VALUES (DEFAULT); 809SELECT * FROM t1; 810a 8111 812DROP TABLE t1; 813CREATE TABLE t1 (a INT DEFAULT CONCAT('1 ')); 814Warnings: 815Note 1265 Data truncated for column 'a' at row 1 816SHOW CREATE TABLE t1; 817Table Create Table 818t1 CREATE TABLE `t1` ( 819 `a` int(11) DEFAULT concat('1 ') 820) ENGINE=MyISAM DEFAULT CHARSET=latin1 821INSERT INTO t1 VALUES (DEFAULT); 822Warnings: 823Note 1265 Data truncated for column 'a' at row 1 824SELECT * FROM t1; 825a 8261 827DROP TABLE t1; 828CREATE TABLE t1 (a INT DEFAULT COALESCE('1 ')); 829Warnings: 830Note 1265 Data truncated for column 'a' at row 1 831SHOW CREATE TABLE t1; 832Table Create Table 833t1 CREATE TABLE `t1` ( 834 `a` int(11) DEFAULT coalesce('1 ') 835) ENGINE=MyISAM DEFAULT CHARSET=latin1 836INSERT INTO t1 VALUES (DEFAULT); 837Warnings: 838Note 1265 Data truncated for column 'a' at row 1 839SELECT * FROM t1; 840a 8411 842DROP TABLE t1; 843# 844# INT: a HEX value 845# 846CREATE TABLE t1 (a INT DEFAULT 0x61 NOT NULL); 847SHOW CREATE TABLE t1; 848Table Create Table 849t1 CREATE TABLE `t1` ( 850 `a` int(11) NOT NULL DEFAULT 97 851) ENGINE=MyISAM DEFAULT CHARSET=latin1 852INSERT INTO t1 VALUES (DEFAULT); 853SELECT * FROM t1; 854a 85597 856DROP TABLE t1; 857# 858# VARCHAR: good defaults 859# 860CREATE TABLE t1 (a VARCHAR(30) DEFAULT 'xxx' NOT NULL); 861SHOW CREATE TABLE t1; 862Table Create Table 863t1 CREATE TABLE `t1` ( 864 `a` varchar(30) NOT NULL DEFAULT 'xxx' 865) ENGINE=MyISAM DEFAULT CHARSET=latin1 866INSERT INTO t1 VALUES (DEFAULT); 867SELECT * FROM t1; 868a 869xxx 870DROP TABLE t1; 871CREATE TABLE t1 (a VARCHAR(2) DEFAULT 0x41 NOT NULL); 872SHOW CREATE TABLE t1; 873Table Create Table 874t1 CREATE TABLE `t1` ( 875 `a` varchar(2) NOT NULL DEFAULT 'A' 876) ENGINE=MyISAM DEFAULT CHARSET=latin1 877DROP TABLE t1; 878CREATE TABLE t1 (a VARCHAR(2) DEFAULT CONCAT(0x41) NOT NULL); 879SHOW CREATE TABLE t1; 880Table Create Table 881t1 CREATE TABLE `t1` ( 882 `a` varchar(2) NOT NULL DEFAULT concat(0x41) 883) ENGINE=MyISAM DEFAULT CHARSET=latin1 884INSERT INTO t1 VALUES (DEFAULT); 885SELECT * FROM t1; 886a 887A 888DROP TABLE t1; 889CREATE TABLE t1 (a VARCHAR(2) DEFAULT COALESCE(0x41) NOT NULL); 890SHOW CREATE TABLE t1; 891Table Create Table 892t1 CREATE TABLE `t1` ( 893 `a` varchar(2) NOT NULL DEFAULT coalesce(0x41) 894) ENGINE=MyISAM DEFAULT CHARSET=latin1 895INSERT INTO t1 VALUES (DEFAULT); 896SELECT * FROM t1; 897a 898A 899DROP TABLE t1; 900CREATE TABLE t1 (a VARCHAR(2) DEFAULT CONCAT(_utf8 0x41) NOT NULL); 901SHOW CREATE TABLE t1; 902Table Create Table 903t1 CREATE TABLE `t1` ( 904 `a` varchar(2) NOT NULL DEFAULT concat(_utf8'A') 905) ENGINE=MyISAM DEFAULT CHARSET=latin1 906INSERT INTO t1 VALUES (DEFAULT); 907SELECT * FROM t1; 908a 909A 910DROP TABLE t1; 911CREATE TABLE t1 (a VARCHAR(2) DEFAULT CONCAT(_utf8 X'41') NOT NULL); 912SHOW CREATE TABLE t1; 913Table Create Table 914t1 CREATE TABLE `t1` ( 915 `a` varchar(2) NOT NULL DEFAULT concat(_utf8'A') 916) ENGINE=MyISAM DEFAULT CHARSET=latin1 917INSERT INTO t1 VALUES (DEFAULT); 918SELECT * FROM t1; 919a 920A 921DROP TABLE t1; 922# 923# VARCHAR: Too long default 924# 925CREATE TABLE t1 (a VARCHAR(2) DEFAULT 'xxx' NOT NULL); 926ERROR 42000: Invalid default value for 'a' 927CREATE TABLE t1 (a VARCHAR(2) DEFAULT CONCAT('xxx') NOT NULL); 928ERROR 42000: Invalid default value for 'a' 929# 930# VARCHAR: Too long default with non-important data 931# 932CREATE TABLE t1 (a VARCHAR(2) DEFAULT 'xx ' NOT NULL); 933ERROR 42000: Invalid default value for 'a' 934CREATE TABLE t1 (a VARCHAR(2) DEFAULT CONCAT('xx ') NOT NULL); 935ERROR 42000: Invalid default value for 'a' 936# 937# VARCHAR: conversion failures 938# 939CREATE TABLE t1 (a VARCHAR(2) CHARACTER SET latin1 DEFAULT _utf8 X'D18F' NOT NULL); 940ERROR 42000: Invalid default value for 'a' 941CREATE TABLE t1 (a VARCHAR(2) CHARACTER SET latin1 DEFAULT CONCAT(_utf8 X'D18F') NOT NULL); 942ERROR 42000: Invalid default value for 'a' 943CREATE TABLE t1 (a VARCHAR(2) CHARACTER SET latin1 DEFAULT CONCAT(_utf8 0xD18F) NOT NULL); 944ERROR 42000: Invalid default value for 'a' 945# 946# Field as a default value 947# 948CREATE TABLE t1 (a INT, b INT DEFAULT (a)); 949SHOW CREATE TABLE t1; 950Table Create Table 951t1 CREATE TABLE `t1` ( 952 `a` int(11) DEFAULT NULL, 953 `b` int(11) DEFAULT `a` 954) ENGINE=MyISAM DEFAULT CHARSET=latin1 955INSERT INTO t1 VALUES (1, 1); 956INSERT INTO t1 VALUES (DEFAULT, DEFAULT); 957INSERT INTO t1 VALUES (1, DEFAULT); 958INSERT INTO t1 VALUES (DEFAULT, 1); 959SELECT * FROM t1; 960a b 9611 1 962NULL NULL 9631 1 964NULL 1 965DROP TABLE t1; 966# 967# Function DEFAULT(field) 968# 969CREATE TABLE t1 (a INT DEFAULT(DEFAULT(b)), b INT DEFAULT 1); 970SHOW CREATE TABLE t1; 971Table Create Table 972t1 CREATE TABLE `t1` ( 973 `a` int(11) DEFAULT default(`b`), 974 `b` int(11) DEFAULT 1 975) ENGINE=MyISAM DEFAULT CHARSET=latin1 976INSERT INTO t1 VALUES (DEFAULT, DEFAULT); 977SELECT * FROM t1; 978a b 9791 1 980DROP TABLE t1; 981CREATE TABLE t1 (a INT DEFAULT 1, b INT DEFAULT(DEFAULT(a))); 982SHOW CREATE TABLE t1; 983Table Create Table 984t1 CREATE TABLE `t1` ( 985 `a` int(11) DEFAULT 1, 986 `b` int(11) DEFAULT default(`a`) 987) ENGINE=MyISAM DEFAULT CHARSET=latin1 988INSERT INTO t1 VALUES (DEFAULT, DEFAULT); 989SELECT * FROM t1; 990a b 9911 1 992DROP TABLE t1; 993# 994# SQL Standard <datetime value function> as a <default option> 995# 996CREATE TABLE t1 (a DATETIME DEFAULT CURRENT_TIMESTAMP); 997SHOW CREATE TABLE t1; 998Table Create Table 999t1 CREATE TABLE `t1` ( 1000 `a` datetime DEFAULT current_timestamp() 1001) ENGINE=MyISAM DEFAULT CHARSET=latin1 1002DROP TABLE t1; 1003CREATE TABLE t1 (a TIME DEFAULT CURRENT_TIME); 1004SHOW CREATE TABLE t1; 1005Table Create Table 1006t1 CREATE TABLE `t1` ( 1007 `a` time DEFAULT curtime() 1008) ENGINE=MyISAM DEFAULT CHARSET=latin1 1009DROP TABLE t1; 1010CREATE TABLE t1 (a DATE DEFAULT CURRENT_DATE); 1011SHOW CREATE TABLE t1; 1012Table Create Table 1013t1 CREATE TABLE `t1` ( 1014 `a` date DEFAULT curdate() 1015) ENGINE=MyISAM DEFAULT CHARSET=latin1 1016DROP TABLE t1; 1017# 1018# DECIMAL + CURRENT_TIMESTAMP, no truncation 1019# 1020CREATE TABLE t1 (a DECIMAL(30,6) DEFAULT CURRENT_TIMESTAMP(6)); 1021SHOW CREATE TABLE t1; 1022Table Create Table 1023t1 CREATE TABLE `t1` ( 1024 `a` decimal(30,6) DEFAULT current_timestamp(6) 1025) ENGINE=MyISAM DEFAULT CHARSET=latin1 1026INSERT INTO t1 VALUES (); 1027SELECT * FROM t1; 1028a 102920010101102030.123456 1030DROP TABLE t1; 1031CREATE TABLE t1 (a DECIMAL(30,6) DEFAULT COALESCE(CURRENT_TIMESTAMP(6))); 1032SHOW CREATE TABLE t1; 1033Table Create Table 1034t1 CREATE TABLE `t1` ( 1035 `a` decimal(30,6) DEFAULT coalesce(current_timestamp(6)) 1036) ENGINE=MyISAM DEFAULT CHARSET=latin1 1037INSERT INTO t1 VALUES(); 1038INSERT IGNORE INTO t1 VALUES(); 1039SET sql_mode = 'STRICT_ALL_TABLES'; 1040INSERT INTO t1 VALUES(); 1041SET sql_mode = DEFAULT; 1042DROP TABLE t1; 1043# 1044# DECIMAL + CURRENT_TIME, no truncation 1045# 1046CREATE TABLE t1 (a DECIMAL(30,6) DEFAULT COALESCE(CURRENT_TIME(6))); 1047SHOW CREATE TABLE t1; 1048Table Create Table 1049t1 CREATE TABLE `t1` ( 1050 `a` decimal(30,6) DEFAULT coalesce(curtime(6)) 1051) ENGINE=MyISAM DEFAULT CHARSET=latin1 1052INSERT IGNORE INTO t1 VALUES(); 1053DROP TABLE t1; 1054# 1055# DECIMAL + CURRENT_DATE, no truncation 1056# 1057CREATE TABLE t1 (a DECIMAL(30,6) DEFAULT COALESCE(CURRENT_DATE)); 1058SHOW CREATE TABLE t1; 1059Table Create Table 1060t1 CREATE TABLE `t1` ( 1061 `a` decimal(30,6) DEFAULT coalesce(curdate()) 1062) ENGINE=MyISAM DEFAULT CHARSET=latin1 1063INSERT IGNORE INTO t1 VALUES(); 1064DROP TABLE t1; 1065# 1066# COALESCE for SQL Standard <datetime value function> 1067# 1068CREATE TABLE t1 (a TIMESTAMP DEFAULT COALESCE(CURRENT_TIMESTAMP)); 1069SHOW CREATE TABLE t1; 1070Table Create Table 1071t1 CREATE TABLE `t1` ( 1072 `a` timestamp NOT NULL DEFAULT coalesce(current_timestamp()) 1073) ENGINE=MyISAM DEFAULT CHARSET=latin1 1074DROP TABLE t1; 1075CREATE TABLE t1 (a DATE DEFAULT COALESCE(CURRENT_DATE)); 1076SHOW CREATE TABLE t1; 1077Table Create Table 1078t1 CREATE TABLE `t1` ( 1079 `a` date DEFAULT coalesce(curdate()) 1080) ENGINE=MyISAM DEFAULT CHARSET=latin1 1081DROP TABLE t1; 1082CREATE TABLE t1 (a TIME DEFAULT COALESCE(CURRENT_TIME)); 1083SHOW CREATE TABLE t1; 1084Table Create Table 1085t1 CREATE TABLE `t1` ( 1086 `a` time DEFAULT coalesce(curtime()) 1087) ENGINE=MyISAM DEFAULT CHARSET=latin1 1088DROP TABLE t1; 1089CREATE TABLE t1 ( 1090a TIMESTAMP DEFAULT CURRENT_TIMESTAMP(6), 1091b TIMESTAMP DEFAULT COALESCE(CURRENT_TIMESTAMP(6)) 1092); 1093SHOW CREATE TABLE t1; 1094Table Create Table 1095t1 CREATE TABLE `t1` ( 1096 `a` timestamp NOT NULL DEFAULT current_timestamp(), 1097 `b` timestamp NOT NULL DEFAULT coalesce(current_timestamp(6)) 1098) ENGINE=MyISAM DEFAULT CHARSET=latin1 1099INSERT INTO t1 VALUES (); 1100SELECT CURRENT_TIMESTAMP(6); 1101CURRENT_TIMESTAMP(6) 11022001-01-01 10:20:30.123456 1103SELECT * FROM t1; 1104a b 11052001-01-01 10:20:30 2001-01-01 10:20:30 1106DROP TABLE t1; 1107CREATE TABLE t1 ( 1108a DECIMAL(30,0) DEFAULT CURRENT_TIMESTAMP(6), 1109b DECIMAL(30,0) DEFAULT COALESCE(CURRENT_TIMESTAMP(6)) 1110); 1111SHOW CREATE TABLE t1; 1112Table Create Table 1113t1 CREATE TABLE `t1` ( 1114 `a` decimal(30,0) DEFAULT current_timestamp(6), 1115 `b` decimal(30,0) DEFAULT coalesce(current_timestamp(6)) 1116) ENGINE=MyISAM DEFAULT CHARSET=latin1 1117INSERT IGNORE INTO t1 VALUES (); 1118Warnings: 1119Note 1265 Data truncated for column 'a' at row 1 1120Note 1265 Data truncated for column 'b' at row 1 1121SELECT * FROM t1; 1122a b 112320010101102030 20010101102030 1124DROP TABLE t1; 1125# 1126# Check DEFAULT() function 1127# 1128CREATE TABLE `t1` (`a` int(11) DEFAULT (3+3),`b` int(11) DEFAULT '1000'); 1129SHOW CREATE TABLE t1; 1130Table Create Table 1131t1 CREATE TABLE `t1` ( 1132 `a` int(11) DEFAULT (3 + 3), 1133 `b` int(11) DEFAULT 1000 1134) ENGINE=MyISAM DEFAULT CHARSET=latin1 1135insert into t1 values (1,1),(2,2); 1136insert into t1 values (default,default); 1137select * from t1; 1138a b 11391 1 11402 2 11416 1000 1142select default(a),b from t1; 1143default(a) b 11446 1 11456 2 11466 1000 1147select a,default(b) from t1; 1148a default(b) 11491 1000 11502 1000 11516 1000 1152drop table t1; 1153# 1154# Real functions 1155# 1156CREATE TABLE t1 (a DECIMAL(10,1), b DOUBLE DEFAULT CAST(a AS DOUBLE)); 1157SHOW CREATE TABLE t1; 1158Table Create Table 1159t1 CREATE TABLE `t1` ( 1160 `a` decimal(10,1) DEFAULT NULL, 1161 `b` double DEFAULT (cast(`a` as double)) 1162) ENGINE=MyISAM DEFAULT CHARSET=latin1 1163INSERT INTO t1 VALUES (10.1, DEFAULT); 1164SELECT * FROM t1; 1165a b 116610.1 10.1 1167DROP TABLE t1; 1168CREATE TABLE t1 (a DOUBLE, b DOUBLE DEFAULT EXP(a), c DOUBLE DEFAULT LOG(b), d DOUBLE DEFAULT LOG(4, b)); 1169SHOW CREATE TABLE t1; 1170Table Create Table 1171t1 CREATE TABLE `t1` ( 1172 `a` double DEFAULT NULL, 1173 `b` double DEFAULT exp(`a`), 1174 `c` double DEFAULT log(`b`), 1175 `d` double DEFAULT log(4,`b`) 1176) ENGINE=MyISAM DEFAULT CHARSET=latin1 1177INSERT INTO t1 VALUES (2, DEFAULT, DEFAULT, DEFAULT); 1178SELECT * FROM t1; 1179a b c d 11802 7.38905609893065 2 1.4426950408889634 1181DROP TABLE t1; 1182CREATE TABLE t1 (a INT, b DOUBLE DEFAULT LOG2(a), c DOUBLE DEFAULT LOG10(a)); 1183SHOW CREATE TABLE t1; 1184Table Create Table 1185t1 CREATE TABLE `t1` ( 1186 `a` int(11) DEFAULT NULL, 1187 `b` double DEFAULT log2(`a`), 1188 `c` double DEFAULT log10(`a`) 1189) ENGINE=MyISAM DEFAULT CHARSET=latin1 1190INSERT INTO t1 VALUES (4, DEFAULT, DEFAULT); 1191INSERT INTO t1 VALUES (100, DEFAULT, DEFAULT); 1192SELECT * FROM t1; 1193a b c 11944 2 0.6020599913279624 1195100 6.643856189774725 2 1196DROP TABLE t1; 1197CREATE TABLE t1 (a DOUBLE, b DOUBLE DEFAULT SQRT(a), c DOUBLE DEFAULT POW(a,3)); 1198SHOW CREATE TABLE t1; 1199Table Create Table 1200t1 CREATE TABLE `t1` ( 1201 `a` double DEFAULT NULL, 1202 `b` double DEFAULT sqrt(`a`), 1203 `c` double DEFAULT pow(`a`,3) 1204) ENGINE=MyISAM DEFAULT CHARSET=latin1 1205INSERT INTO t1 VALUES (4, DEFAULT, DEFAULT); 1206SELECT * FROM t1; 1207a b c 12084 2 64 1209DROP TABLE t1; 1210CREATE TABLE t1 (a DOUBLE, b DOUBLE DEFAULT ACOS(a), c DOUBLE DEFAULT ASIN(a), d DOUBLE DEFAULT ATAN(a)); 1211SHOW CREATE TABLE t1; 1212Table Create Table 1213t1 CREATE TABLE `t1` ( 1214 `a` double DEFAULT NULL, 1215 `b` double DEFAULT acos(`a`), 1216 `c` double DEFAULT asin(`a`), 1217 `d` double DEFAULT atan(`a`) 1218) ENGINE=MyISAM DEFAULT CHARSET=latin1 1219INSERT INTO t1 VALUES (1, DEFAULT, DEFAULT, DEFAULT); 1220SELECT a, b/PI(), c/PI(), d/PI() FROM t1; 1221a b/PI() c/PI() d/PI() 12221 0 0.5 0.25 1223DROP TABLE t1; 1224CREATE TABLE t1 (a DOUBLE, b DOUBLE DEFAULT COS(a), c DOUBLE DEFAULT SIN(a), d DOUBLE DEFAULT TAN(a), e DOUBLE DEFAULT COT(a)); 1225SHOW CREATE TABLE t1; 1226Table Create Table 1227t1 CREATE TABLE `t1` ( 1228 `a` double DEFAULT NULL, 1229 `b` double DEFAULT cos(`a`), 1230 `c` double DEFAULT sin(`a`), 1231 `d` double DEFAULT tan(`a`), 1232 `e` double DEFAULT cot(`a`) 1233) ENGINE=MyISAM DEFAULT CHARSET=latin1 1234INSERT INTO t1 (a) VALUES (PI()/3); 1235SELECT ROUND(a,3), ROUND(b,3), ROUND(c,3), ROUND(d,3), ROUND(e,3) FROM t1; 1236ROUND(a,3) ROUND(b,3) ROUND(c,3) ROUND(d,3) ROUND(e,3) 12371.047 0.500 0.866 1.732 0.577 1238DROP TABLE t1; 1239CREATE TABLE t1 (a DOUBLE DEFAULT RAND()); 1240SHOW CREATE TABLE t1; 1241Table Create Table 1242t1 CREATE TABLE `t1` ( 1243 `a` double DEFAULT rand() 1244) ENGINE=MyISAM DEFAULT CHARSET=latin1 1245INSERT INTO t1 VALUES (DEFAULT); 1246DROP TABLE t1; 1247CREATE TABLE t1 (a DOUBLE, b DOUBLE DEFAULT DEGREES(a), c DOUBLE DEFAULT RADIANS(b)); 1248SHOW CREATE TABLE t1; 1249Table Create Table 1250t1 CREATE TABLE `t1` ( 1251 `a` double DEFAULT NULL, 1252 `b` double DEFAULT degrees(`a`), 1253 `c` double DEFAULT radians(`b`) 1254) ENGINE=MyISAM DEFAULT CHARSET=latin1 1255INSERT INTO t1 VALUES (PI(), DEFAULT, DEFAULT); 1256SELECT * FROM t1; 1257a b c 12583.141592653589793 180 3.141592653589793 1259DROP TABLE t1; 1260# 1261# INT result functions 1262# 1263CREATE TABLE t1 (a INT, b INT DEFAULT INTERVAL(a, 10, 20, 30, 40)); 1264SHOW CREATE TABLE t1; 1265Table Create Table 1266t1 CREATE TABLE `t1` ( 1267 `a` int(11) DEFAULT NULL, 1268 `b` int(11) DEFAULT interval(`a`,10,20,30,40) 1269) ENGINE=MyISAM DEFAULT CHARSET=latin1 1270INSERT INTO t1 (a) VALUES (34); 1271SELECT * FROM t1; 1272a b 127334 3 1274DROP TABLE t1; 1275CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a DIV b)); 1276SHOW CREATE TABLE t1; 1277Table Create Table 1278t1 CREATE TABLE `t1` ( 1279 `a` int(11) DEFAULT NULL, 1280 `b` int(11) DEFAULT NULL, 1281 `c` int(11) DEFAULT (`a` DIV `b`) 1282) ENGINE=MyISAM DEFAULT CHARSET=latin1 1283INSERT INTO t1 (a, b) VALUES (13, 3); 1284SELECT * FROM t1; 1285a b c 128613 3 4 1287DROP TABLE t1; 1288CREATE TABLE t1 (a INT, b INT DEFAULT SIGN(a)); 1289SHOW CREATE TABLE t1; 1290Table Create Table 1291t1 CREATE TABLE `t1` ( 1292 `a` int(11) DEFAULT NULL, 1293 `b` int(11) DEFAULT sign(`a`) 1294) ENGINE=MyISAM DEFAULT CHARSET=latin1 1295INSERT INTO t1 (a) VALUES (-10),(0), (10); 1296SELECT * FROM t1; 1297a b 1298-10 -1 12990 0 130010 1 1301DROP TABLE t1; 1302CREATE TABLE t1 (a VARCHAR(30), b INT DEFAULT FIELD(a, 'Hej', 'ej', 'Heja', 'hej', 'foo')); 1303SHOW CREATE TABLE t1; 1304Table Create Table 1305t1 CREATE TABLE `t1` ( 1306 `a` varchar(30) DEFAULT NULL, 1307 `b` int(11) DEFAULT field(`a`,'Hej','ej','Heja','hej','foo') 1308) ENGINE=MyISAM DEFAULT CHARSET=latin1 1309INSERT INTO t1 (a) VALUES ('ej'); 1310SELECT * FROM t1; 1311a b 1312ej 2 1313DROP TABLE t1; 1314CREATE TABLE t1 (a VARCHAR(30), b INT DEFAULT FIND_IN_SET(a, 'Hej,ej,Heja,hej,foo')); 1315SHOW CREATE TABLE t1; 1316Table Create Table 1317t1 CREATE TABLE `t1` ( 1318 `a` varchar(30) DEFAULT NULL, 1319 `b` int(11) DEFAULT find_in_set(`a`,'Hej,ej,Heja,hej,foo') 1320) ENGINE=MyISAM DEFAULT CHARSET=latin1 1321INSERT INTO t1 (a) VALUES ('ej'); 1322SELECT * FROM t1; 1323a b 1324ej 2 1325DROP TABLE t1; 1326CREATE TABLE t1 (a VARCHAR(30), b INT DEFAULT ASCII(a), c INT DEFAULT ORD(a)); 1327SHOW CREATE TABLE t1; 1328Table Create Table 1329t1 CREATE TABLE `t1` ( 1330 `a` varchar(30) DEFAULT NULL, 1331 `b` int(11) DEFAULT ascii(`a`), 1332 `c` int(11) DEFAULT ord(`a`) 1333) ENGINE=MyISAM DEFAULT CHARSET=latin1 1334INSERT INTO t1 (a) VALUES ('a'); 1335SELECT * FROM t1; 1336a b c 1337a 97 97 1338DROP TABLE t1; 1339CREATE TABLE t1 (a TEXT DEFAULT UUID_SHORT()); 1340SHOW CREATE TABLE t1; 1341Table Create Table 1342t1 CREATE TABLE `t1` ( 1343 `a` text DEFAULT uuid_short() 1344) ENGINE=MyISAM DEFAULT CHARSET=latin1 1345INSERT INTO t1 VALUES (); 1346SELECT a>0 FROM t1; 1347a>0 13481 1349DROP TABLE t1; 1350CREATE TABLE t1 (a INT DEFAULT BENCHMARK(1,1)); 1351ERROR HY000: Function or expression 'benchmark()' cannot be used in the DEFAULT clause of `a` 1352CREATE TABLE t1 (a INT DEFAULT GET_LOCK('a',1)); 1353ERROR HY000: Function or expression 'get_lock()' cannot be used in the DEFAULT clause of `a` 1354CREATE TABLE t1 (a INT DEFAULT RELEASE_LOCK('a')); 1355ERROR HY000: Function or expression 'release_lock()' cannot be used in the DEFAULT clause of `a` 1356CREATE TABLE t1 (a INT DEFAULT IS_USED_LOCK('a')); 1357ERROR HY000: Function or expression 'is_used_lock()' cannot be used in the DEFAULT clause of `a` 1358CREATE TABLE t1 (a INT DEFAULT IS_FREE_LOCK('a')); 1359ERROR HY000: Function or expression 'is_free_lock()' cannot be used in the DEFAULT clause of `a` 1360CREATE TABLE t1 (a INT DEFAULT SLEEP(1)); 1361ERROR HY000: Function or expression 'sleep()' cannot be used in the DEFAULT clause of `a` 1362CREATE TABLE t1 (a INT DEFAULT ROW_COUNT()); 1363ERROR HY000: Function or expression 'row_count()' cannot be used in the DEFAULT clause of `a` 1364CREATE TABLE t1 (a INT DEFAULT FOUND_ROWS()); 1365ERROR HY000: Function or expression 'found_rows()' cannot be used in the DEFAULT clause of `a` 1366CREATE TABLE t1 (a INT DEFAULT MASTER_POS_WAIT('test',100)); 1367ERROR HY000: Function or expression 'master_pos_wait()' cannot be used in the DEFAULT clause of `a` 1368CREATE TABLE t1 (a INT DEFAULT MASTER_GTID_WAIT('test')); 1369ERROR HY000: Function or expression 'master_gtid_wait()' cannot be used in the DEFAULT clause of `a` 1370CREATE TABLE t1 (a VARCHAR(30), b DOUBLE DEFAULT MATCH (a) AGAINST('bbbb' IN BOOLEAN MODE)); 1371ERROR HY000: Function or expression 'match ... against()' cannot be used in the DEFAULT clause of `b` 1372# 1373# Temporal functions 1374# 1375# Item_temporal_hybrid_func 1376CREATE TABLE t1 (a DATE, b INT, c DATE DEFAULT DATE_ADD(a, INTERVAL b DAY)); 1377SHOW CREATE TABLE t1; 1378Table Create Table 1379t1 CREATE TABLE `t1` ( 1380 `a` date DEFAULT NULL, 1381 `b` int(11) DEFAULT NULL, 1382 `c` date DEFAULT (`a` + interval `b` day) 1383) ENGINE=MyISAM DEFAULT CHARSET=latin1 1384INSERT INTO t1 VALUES ('2001-01-01', 30, DEFAULT); 1385SELECT * FROM t1; 1386a b c 13872001-01-01 30 2001-01-31 1388DROP TABLE t1; 1389CREATE TABLE t1 (a DATE, b TIME, c DATETIME DEFAULT ADDTIME(a, b)); 1390SHOW CREATE TABLE t1; 1391Table Create Table 1392t1 CREATE TABLE `t1` ( 1393 `a` date DEFAULT NULL, 1394 `b` time DEFAULT NULL, 1395 `c` datetime DEFAULT addtime(`a`,`b`) 1396) ENGINE=MyISAM DEFAULT CHARSET=latin1 1397INSERT INTO t1 VALUES ('2001-01-01', '10:20:30', DEFAULT); 1398SELECT * FROM t1; 1399a b c 14002001-01-01 10:20:30 2001-01-01 10:20:30 1401DROP TABLE t1; 1402CREATE TABLE t1 (a VARCHAR(32), b VARCHAR(32), c DATE DEFAULT STR_TO_DATE(a,b)); 1403SHOW CREATE TABLE t1; 1404Table Create Table 1405t1 CREATE TABLE `t1` ( 1406 `a` varchar(32) DEFAULT NULL, 1407 `b` varchar(32) DEFAULT NULL, 1408 `c` date DEFAULT str_to_date(`a`,`b`) 1409) ENGINE=MyISAM DEFAULT CHARSET=latin1 1410INSERT INTO t1 VALUES ('01,5,2013','%d,%m,%Y', DEFAULT); 1411SELECT * FROM t1; 1412a b c 141301,5,2013 %d,%m,%Y 2013-05-01 1414DROP TABLE t1; 1415# Item_datefunc 1416SET time_zone='-10:00'; 1417SET timestamp=UNIX_TIMESTAMP('2001-01-01 23:59:59'); 1418CREATE TABLE t1 (a DATE DEFAULT CURDATE(), b DATE DEFAULT UTC_DATE()); 1419SHOW CREATE TABLE t1; 1420Table Create Table 1421t1 CREATE TABLE `t1` ( 1422 `a` date DEFAULT curdate(), 1423 `b` date DEFAULT utc_date() 1424) ENGINE=MyISAM DEFAULT CHARSET=latin1 1425INSERT INTO t1 VALUES (); 1426SELECT * FROM t1; 1427a b 14282001-01-01 2001-01-02 1429DROP TABLE t1; 1430SET time_zone=DEFAULT, timestamp= DEFAULT; 1431CREATE TABLE t1 (a INT, b DATE DEFAULT FROM_DAYS(a)); 1432SHOW CREATE TABLE t1; 1433Table Create Table 1434t1 CREATE TABLE `t1` ( 1435 `a` int(11) DEFAULT NULL, 1436 `b` date DEFAULT from_days(`a`) 1437) ENGINE=MyISAM DEFAULT CHARSET=latin1 1438INSERT INTO t1 VALUES (730669, DEFAULT); 1439SELECT * FROM t1; 1440a b 1441730669 2000-07-03 1442DROP TABLE t1; 1443CREATE TABLE t1 (a DATE, b DATE DEFAULT LAST_DAY(a)); 1444SHOW CREATE TABLE t1; 1445Table Create Table 1446t1 CREATE TABLE `t1` ( 1447 `a` date DEFAULT NULL, 1448 `b` date DEFAULT last_day(`a`) 1449) ENGINE=MyISAM DEFAULT CHARSET=latin1 1450INSERT INTO t1 VALUES ('2003-02-05', DEFAULT); 1451SELECT * FROM t1; 1452a b 14532003-02-05 2003-02-28 1454DROP TABLE t1; 1455CREATE TABLE t1 (yy INT, yd INT, d DATE DEFAULT MAKEDATE(yy, yd)); 1456SHOW CREATE TABLE t1; 1457Table Create Table 1458t1 CREATE TABLE `t1` ( 1459 `yy` int(11) DEFAULT NULL, 1460 `yd` int(11) DEFAULT NULL, 1461 `d` date DEFAULT makedate(`yy`,`yd`) 1462) ENGINE=MyISAM DEFAULT CHARSET=latin1 1463INSERT INTO t1 VALUES (2011,32,DEFAULT); 1464SELECT * FROM t1; 1465yy yd d 14662011 32 2011-02-01 1467DROP TABLE t1; 1468# Item_timefunc 1469SET time_zone='-10:00'; 1470SET timestamp=UNIX_TIMESTAMP('2001-01-01 23:59:59'); 1471CREATE TABLE t1 (a TIME DEFAULT CURTIME(), b TIME DEFAULT UTC_TIME()); 1472SHOW CREATE TABLE t1; 1473Table Create Table 1474t1 CREATE TABLE `t1` ( 1475 `a` time DEFAULT curtime(), 1476 `b` time DEFAULT utc_time() 1477) ENGINE=MyISAM DEFAULT CHARSET=latin1 1478INSERT INTO t1 VALUES (); 1479SELECT * FROM t1; 1480a b 148123:59:59 09:59:59 1482DROP TABLE t1; 1483SET time_zone=DEFAULT, timestamp= DEFAULT; 1484CREATE TABLE t1 (a INT, b TIME DEFAULT SEC_TO_TIME(a)); 1485SHOW CREATE TABLE t1; 1486Table Create Table 1487t1 CREATE TABLE `t1` ( 1488 `a` int(11) DEFAULT NULL, 1489 `b` time DEFAULT sec_to_time(`a`) 1490) ENGINE=MyISAM DEFAULT CHARSET=latin1 1491INSERT INTO t1 VALUES (2378, DEFAULT); 1492SELECT * FROM t1; 1493a b 14942378 00:39:38 1495DROP TABLE t1; 1496CREATE TABLE t1 (a DATETIME, b DATETIME, c TIME DEFAULT TIMEDIFF(a,b)); 1497SHOW CREATE TABLE t1; 1498Table Create Table 1499t1 CREATE TABLE `t1` ( 1500 `a` datetime DEFAULT NULL, 1501 `b` datetime DEFAULT NULL, 1502 `c` time DEFAULT timediff(`a`,`b`) 1503) ENGINE=MyISAM DEFAULT CHARSET=latin1 1504INSERT INTO t1 VALUES ('2000:01:01 00:00:00', '2000:01:02 10:20:30', DEFAULT); 1505SELECT * FROM t1; 1506a b c 15072000-01-01 00:00:00 2000-01-02 10:20:30 -34:20:30 1508DROP TABLE t1; 1509CREATE TABLE t1 (hh INT, mm INT, ss INT, t TIME DEFAULT MAKETIME(hh,mm,ss)); 1510SHOW CREATE TABLE t1; 1511Table Create Table 1512t1 CREATE TABLE `t1` ( 1513 `hh` int(11) DEFAULT NULL, 1514 `mm` int(11) DEFAULT NULL, 1515 `ss` int(11) DEFAULT NULL, 1516 `t` time DEFAULT maketime(`hh`,`mm`,`ss`) 1517) ENGINE=MyISAM DEFAULT CHARSET=latin1 1518INSERT INTO t1 VALUES (10,20,30,DEFAULT); 1519SELECT * FROM t1; 1520hh mm ss t 152110 20 30 10:20:30 1522DROP TABLE t1; 1523# Item_datetimefunc 1524SET time_zone='-10:00'; 1525SET timestamp=UNIX_TIMESTAMP('2001-01-01 23:59:59'); 1526CREATE TABLE t1 (a TIMESTAMP DEFAULT NOW(), b TIMESTAMP DEFAULT UTC_TIMESTAMP()); 1527SHOW CREATE TABLE t1; 1528Table Create Table 1529t1 CREATE TABLE `t1` ( 1530 `a` timestamp NOT NULL DEFAULT current_timestamp(), 1531 `b` timestamp NOT NULL DEFAULT utc_timestamp() 1532) ENGINE=MyISAM DEFAULT CHARSET=latin1 1533INSERT INTO t1 VALUES (); 1534SELECT * FROM t1; 1535a b 15362001-01-01 23:59:59 2001-01-02 09:59:59 1537DROP TABLE t1; 1538SET time_zone=DEFAULT, timestamp= DEFAULT; 1539CREATE TABLE t1 (a TIMESTAMP(6) DEFAULT SYSDATE(6), s INT, b TIMESTAMP(6) DEFAULT SYSDATE(6)); 1540SHOW CREATE TABLE t1; 1541Table Create Table 1542t1 CREATE TABLE `t1` ( 1543 `a` timestamp(6) NOT NULL DEFAULT sysdate(6), 1544 `s` int(11) DEFAULT NULL, 1545 `b` timestamp(6) NOT NULL DEFAULT sysdate(6) 1546) ENGINE=MyISAM DEFAULT CHARSET=latin1 1547INSERT INTO t1 VALUES (DEFAULT(a), SLEEP(0.1), DEFAULT(b)); 1548SELECT b>a FROM t1; 1549b>a 15501 1551DROP TABLE t1; 1552SET time_zone='+00:00'; 1553CREATE TABLE t1 (a INT, b TIMESTAMP DEFAULT FROM_UNIXTIME(a)); 1554SHOW CREATE TABLE t1; 1555Table Create Table 1556t1 CREATE TABLE `t1` ( 1557 `a` int(11) DEFAULT NULL, 1558 `b` timestamp NOT NULL DEFAULT from_unixtime(`a`) 1559) ENGINE=MyISAM DEFAULT CHARSET=latin1 1560INSERT INTO t1 VALUES (1447430881, DEFAULT); 1561SELECT * FROM t1; 1562a b 15631447430881 2015-11-13 16:08:01 1564DROP TABLE t1; 1565SET time_zone=DEFAULT; 1566CREATE TABLE t1 (a TIMESTAMP, b TIMESTAMP DEFAULT CONVERT_TZ(a, '-10:00', '+10:00')); 1567SHOW CREATE TABLE t1; 1568Table Create Table 1569t1 CREATE TABLE `t1` ( 1570 `a` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), 1571 `b` timestamp NOT NULL DEFAULT convert_tz(`a`,'-10:00','+10:00') 1572) ENGINE=MyISAM DEFAULT CHARSET=latin1 1573INSERT INTO t1 VALUES ('2001-01-01 10:20:30', DEFAULT); 1574SELECT * FROM t1; 1575a b 15762001-01-01 10:20:30 2001-01-02 06:20:30 1577DROP TABLE t1; 1578# Item_temporal_typecast 1579CREATE TABLE t1 (a INT, b DATE DEFAULT CAST(a AS DATE)); 1580SHOW CREATE TABLE t1; 1581Table Create Table 1582t1 CREATE TABLE `t1` ( 1583 `a` int(11) DEFAULT NULL, 1584 `b` date DEFAULT cast(`a` as date) 1585) ENGINE=MyISAM DEFAULT CHARSET=latin1 1586INSERT INTO t1 VALUES (20010203, DEFAULT); 1587SELECT * FROM t1; 1588a b 158920010203 2001-02-03 1590DROP TABLE t1; 1591CREATE TABLE t1 (a INT, b TIME DEFAULT CAST(a AS TIME)); 1592SHOW CREATE TABLE t1; 1593Table Create Table 1594t1 CREATE TABLE `t1` ( 1595 `a` int(11) DEFAULT NULL, 1596 `b` time DEFAULT cast(`a` as time) 1597) ENGINE=MyISAM DEFAULT CHARSET=latin1 1598INSERT INTO t1 VALUES (102030, DEFAULT); 1599SELECT * FROM t1; 1600a b 1601102030 10:20:30 1602DROP TABLE t1; 1603CREATE TABLE t1 (a BIGINT, b DATETIME DEFAULT CAST(a AS DATETIME)); 1604SHOW CREATE TABLE t1; 1605Table Create Table 1606t1 CREATE TABLE `t1` ( 1607 `a` bigint(20) DEFAULT NULL, 1608 `b` datetime DEFAULT cast(`a` as datetime) 1609) ENGINE=MyISAM DEFAULT CHARSET=latin1 1610INSERT INTO t1 VALUES (20010203102030, DEFAULT); 1611SELECT * FROM t1; 1612a b 161320010203102030 2001-02-03 10:20:30 1614DROP TABLE t1; 1615# 1616# Functions with temporal input 1617# 1618CREATE TABLE t1 (a INT, b INT, c INT DEFAULT PERIOD_ADD(a,b)); 1619SHOW CREATE TABLE t1; 1620Table Create Table 1621t1 CREATE TABLE `t1` ( 1622 `a` int(11) DEFAULT NULL, 1623 `b` int(11) DEFAULT NULL, 1624 `c` int(11) DEFAULT period_add(`a`,`b`) 1625) ENGINE=MyISAM DEFAULT CHARSET=latin1 1626INSERT INTO t1 (a,b) VALUES (200801, 2); 1627SELECT * FROM t1; 1628a b c 1629200801 2 200803 1630DROP TABLE t1; 1631CREATE TABLE t1 (a INT, b INT, c INT DEFAULT PERIOD_DIFF(a,b)); 1632SHOW CREATE TABLE t1; 1633Table Create Table 1634t1 CREATE TABLE `t1` ( 1635 `a` int(11) DEFAULT NULL, 1636 `b` int(11) DEFAULT NULL, 1637 `c` int(11) DEFAULT period_diff(`a`,`b`) 1638) ENGINE=MyISAM DEFAULT CHARSET=latin1 1639INSERT INTO t1 (a,b) VALUES (200802, 200703); 1640SELECT * FROM t1; 1641a b c 1642200802 200703 11 1643DROP TABLE t1; 1644CREATE TABLE t1 (a INT, b INT DEFAULT TO_DAYS(a)); 1645SHOW CREATE TABLE t1; 1646Table Create Table 1647t1 CREATE TABLE `t1` ( 1648 `a` int(11) DEFAULT NULL, 1649 `b` int(11) DEFAULT to_days(`a`) 1650) ENGINE=MyISAM DEFAULT CHARSET=latin1 1651INSERT INTO t1 (a) VALUES (950501); 1652SELECT * FROM t1; 1653a b 1654950501 728779 1655DROP TABLE t1; 1656CREATE TABLE t1 (a DATE, b INT DEFAULT TO_DAYS(a)); 1657SHOW CREATE TABLE t1; 1658Table Create Table 1659t1 CREATE TABLE `t1` ( 1660 `a` date DEFAULT NULL, 1661 `b` int(11) DEFAULT to_days(`a`) 1662) ENGINE=MyISAM DEFAULT CHARSET=latin1 1663INSERT INTO t1 (a) VALUES ('2007-10-07'); 1664SELECT * FROM t1; 1665a b 16662007-10-07 733321 1667DROP TABLE t1; 1668CREATE TABLE t1 (a INT, b BIGINT DEFAULT TO_SECONDS(a)); 1669SHOW CREATE TABLE t1; 1670Table Create Table 1671t1 CREATE TABLE `t1` ( 1672 `a` int(11) DEFAULT NULL, 1673 `b` bigint(20) DEFAULT to_seconds(`a`) 1674) ENGINE=MyISAM DEFAULT CHARSET=latin1 1675INSERT INTO t1 (a) VALUES (950501); 1676SELECT * FROM t1; 1677a b 1678950501 62966505600 1679DROP TABLE t1; 1680CREATE TABLE t1 (a DATE, b BIGINT DEFAULT TO_SECONDS(a)); 1681SHOW CREATE TABLE t1; 1682Table Create Table 1683t1 CREATE TABLE `t1` ( 1684 `a` date DEFAULT NULL, 1685 `b` bigint(20) DEFAULT to_seconds(`a`) 1686) ENGINE=MyISAM DEFAULT CHARSET=latin1 1687INSERT INTO t1 (a) VALUES ('2009-11-29'); 1688SELECT * FROM t1; 1689a b 16902009-11-29 63426672000 1691DROP TABLE t1; 1692CREATE TABLE t1 (a DATETIME, b BIGINT DEFAULT TO_SECONDS(a)); 1693SHOW CREATE TABLE t1; 1694Table Create Table 1695t1 CREATE TABLE `t1` ( 1696 `a` datetime DEFAULT NULL, 1697 `b` bigint(20) DEFAULT to_seconds(`a`) 1698) ENGINE=MyISAM DEFAULT CHARSET=latin1 1699INSERT INTO t1 (a) VALUES ('2009-11-29 13:43:32'); 1700SELECT * FROM t1; 1701a b 17022009-11-29 13:43:32 63426721412 1703DROP TABLE t1; 1704CREATE TABLE t1 (a DATE, b BIGINT DEFAULT DAYOFMONTH(a)); 1705SHOW CREATE TABLE t1; 1706Table Create Table 1707t1 CREATE TABLE `t1` ( 1708 `a` date DEFAULT NULL, 1709 `b` bigint(20) DEFAULT dayofmonth(`a`) 1710) ENGINE=MyISAM DEFAULT CHARSET=latin1 1711INSERT INTO t1 (a) VALUES ('2007-02-03'); 1712SELECT * FROM t1; 1713a b 17142007-02-03 3 1715DROP TABLE t1; 1716CREATE TABLE t1 (a DATE, b BIGINT DEFAULT DAYOFWEEK(a)); 1717SHOW CREATE TABLE t1; 1718Table Create Table 1719t1 CREATE TABLE `t1` ( 1720 `a` date DEFAULT NULL, 1721 `b` bigint(20) DEFAULT dayofweek(`a`) 1722) ENGINE=MyISAM DEFAULT CHARSET=latin1 1723INSERT INTO t1 (a) VALUES ('2007-02-03'); 1724SELECT * FROM t1; 1725a b 17262007-02-03 7 1727DROP TABLE t1; 1728CREATE TABLE t1 (a DATE, b BIGINT DEFAULT DAYOFYEAR(a)); 1729SHOW CREATE TABLE t1; 1730Table Create Table 1731t1 CREATE TABLE `t1` ( 1732 `a` date DEFAULT NULL, 1733 `b` bigint(20) DEFAULT dayofyear(`a`) 1734) ENGINE=MyISAM DEFAULT CHARSET=latin1 1735INSERT INTO t1 (a) VALUES ('2007-02-03'); 1736SELECT * FROM t1; 1737a b 17382007-02-03 34 1739DROP TABLE t1; 1740CREATE TABLE t1 (a TIME, b INT DEFAULT HOUR(a)); 1741SHOW CREATE TABLE t1; 1742Table Create Table 1743t1 CREATE TABLE `t1` ( 1744 `a` time DEFAULT NULL, 1745 `b` int(11) DEFAULT hour(`a`) 1746) ENGINE=MyISAM DEFAULT CHARSET=latin1 1747INSERT INTO t1 (a) VALUES ('10:05:03'); 1748SELECT * FROM t1; 1749a b 175010:05:03 10 1751DROP TABLE t1; 1752CREATE TABLE t1 (a TIME, b INT DEFAULT MINUTE(a)); 1753SHOW CREATE TABLE t1; 1754Table Create Table 1755t1 CREATE TABLE `t1` ( 1756 `a` time DEFAULT NULL, 1757 `b` int(11) DEFAULT minute(`a`) 1758) ENGINE=MyISAM DEFAULT CHARSET=latin1 1759INSERT INTO t1 (a) VALUES ('10:05:03'); 1760SELECT * FROM t1; 1761a b 176210:05:03 5 1763DROP TABLE t1; 1764CREATE TABLE t1 (a TIME, b INT DEFAULT SECOND(a)); 1765SHOW CREATE TABLE t1; 1766Table Create Table 1767t1 CREATE TABLE `t1` ( 1768 `a` time DEFAULT NULL, 1769 `b` int(11) DEFAULT second(`a`) 1770) ENGINE=MyISAM DEFAULT CHARSET=latin1 1771INSERT INTO t1 (a) VALUES ('10:05:03'); 1772SELECT * FROM t1; 1773a b 177410:05:03 3 1775DROP TABLE t1; 1776CREATE TABLE t1 (a DATETIME(6), b INT DEFAULT MICROSECOND(a)); 1777SHOW CREATE TABLE t1; 1778Table Create Table 1779t1 CREATE TABLE `t1` ( 1780 `a` datetime(6) DEFAULT NULL, 1781 `b` int(11) DEFAULT microsecond(`a`) 1782) ENGINE=MyISAM DEFAULT CHARSET=latin1 1783INSERT INTO t1 (a) VALUES ('2009-12-31 23:59:59.000010'); 1784SELECT * FROM t1; 1785a b 17862009-12-31 23:59:59.000010 10 1787DROP TABLE t1; 1788CREATE TABLE t1 (a DATE, b INT DEFAULT YEAR(a)); 1789SHOW CREATE TABLE t1; 1790Table Create Table 1791t1 CREATE TABLE `t1` ( 1792 `a` date DEFAULT NULL, 1793 `b` int(11) DEFAULT year(`a`) 1794) ENGINE=MyISAM DEFAULT CHARSET=latin1 1795INSERT INTO t1 (a) VALUES ('1987-01-01'); 1796SELECT * FROM t1; 1797a b 17981987-01-01 1987 1799DROP TABLE t1; 1800CREATE TABLE t1 (a DATE, b INT DEFAULT MONTH(a)); 1801SHOW CREATE TABLE t1; 1802Table Create Table 1803t1 CREATE TABLE `t1` ( 1804 `a` date DEFAULT NULL, 1805 `b` int(11) DEFAULT month(`a`) 1806) ENGINE=MyISAM DEFAULT CHARSET=latin1 1807INSERT INTO t1 (a) VALUES ('1987-01-01'); 1808SELECT * FROM t1; 1809a b 18101987-01-01 1 1811DROP TABLE t1; 1812CREATE TABLE t1 (a DATE, b INT DEFAULT WEEK(a)); 1813SHOW CREATE TABLE t1; 1814Table Create Table 1815t1 CREATE TABLE `t1` ( 1816 `a` date DEFAULT NULL, 1817 `b` int(11) DEFAULT week(`a`) 1818) ENGINE=MyISAM DEFAULT CHARSET=latin1 1819INSERT INTO t1 (a) VALUES ('1987-02-01'); 1820SELECT * FROM t1; 1821a b 18221987-02-01 5 1823DROP TABLE t1; 1824CREATE TABLE t1 (a DATE, b INT DEFAULT YEARWEEK(a)); 1825SHOW CREATE TABLE t1; 1826Table Create Table 1827t1 CREATE TABLE `t1` ( 1828 `a` date DEFAULT NULL, 1829 `b` int(11) DEFAULT yearweek(`a`,0) 1830) ENGINE=MyISAM DEFAULT CHARSET=latin1 1831INSERT INTO t1 (a) VALUES ('2000-01-01'); 1832SELECT * FROM t1; 1833a b 18342000-01-01 199952 1835DROP TABLE t1; 1836CREATE TABLE t1 (a DATE, b INT DEFAULT QUARTER(a)); 1837SHOW CREATE TABLE t1; 1838Table Create Table 1839t1 CREATE TABLE `t1` ( 1840 `a` date DEFAULT NULL, 1841 `b` int(11) DEFAULT quarter(`a`) 1842) ENGINE=MyISAM DEFAULT CHARSET=latin1 1843INSERT INTO t1 (a) VALUES ('2008-04-01'); 1844SELECT * FROM t1; 1845a b 18462008-04-01 2 1847DROP TABLE t1; 1848CREATE TABLE t1 (a DATE, b INT DEFAULT EXTRACT(YEAR FROM a)); 1849SHOW CREATE TABLE t1; 1850Table Create Table 1851t1 CREATE TABLE `t1` ( 1852 `a` date DEFAULT NULL, 1853 `b` int(11) DEFAULT extract(year from `a`) 1854) ENGINE=MyISAM DEFAULT CHARSET=latin1 1855INSERT INTO t1 (a) VALUES ('2009-07-02'); 1856SELECT * FROM t1; 1857a b 18582009-07-02 2009 1859DROP TABLE t1; 1860CREATE TABLE t1 (a DATETIME, b INT DEFAULT EXTRACT(YEAR_MONTH FROM a)); 1861SHOW CREATE TABLE t1; 1862Table Create Table 1863t1 CREATE TABLE `t1` ( 1864 `a` datetime DEFAULT NULL, 1865 `b` int(11) DEFAULT extract(year_month from `a`) 1866) ENGINE=MyISAM DEFAULT CHARSET=latin1 1867INSERT INTO t1 (a) VALUES ('2009-07-02 01:02:03'); 1868SELECT * FROM t1; 1869a b 18702009-07-02 01:02:03 200907 1871DROP TABLE t1; 1872CREATE TABLE t1 (a DATETIME, b INT DEFAULT EXTRACT(DAY_MINUTE FROM a)); 1873SHOW CREATE TABLE t1; 1874Table Create Table 1875t1 CREATE TABLE `t1` ( 1876 `a` datetime DEFAULT NULL, 1877 `b` int(11) DEFAULT extract(day_minute from `a`) 1878) ENGINE=MyISAM DEFAULT CHARSET=latin1 1879INSERT INTO t1 (a) VALUES ('2009-07-02 01:02:03'); 1880SELECT * FROM t1; 1881a b 18822009-07-02 01:02:03 20102 1883DROP TABLE t1; 1884CREATE TABLE t1 (a DATETIME(6), b INT DEFAULT EXTRACT(MICROSECOND FROM a)); 1885SHOW CREATE TABLE t1; 1886Table Create Table 1887t1 CREATE TABLE `t1` ( 1888 `a` datetime(6) DEFAULT NULL, 1889 `b` int(11) DEFAULT extract(microsecond from `a`) 1890) ENGINE=MyISAM DEFAULT CHARSET=latin1 1891INSERT INTO t1 (a) VALUES ('2009-07-02 01:02:03.000123'); 1892SELECT * FROM t1; 1893a b 18942009-07-02 01:02:03.000123 123 1895DROP TABLE t1; 1896CREATE TABLE t1 (a DATE, b DATE, c INT DEFAULT TIMESTAMPDIFF(MONTH,a,b)); 1897SHOW CREATE TABLE t1; 1898Table Create Table 1899t1 CREATE TABLE `t1` ( 1900 `a` date DEFAULT NULL, 1901 `b` date DEFAULT NULL, 1902 `c` int(11) DEFAULT timestampdiff(MONTH,`a`,`b`) 1903) ENGINE=MyISAM DEFAULT CHARSET=latin1 1904INSERT INTO t1 (a,b) VALUES ('2003-02-01','2003-05-01'); 1905SELECT * FROM t1; 1906a b c 19072003-02-01 2003-05-01 3 1908DROP TABLE t1; 1909CREATE TABLE t1 (a DATE, b DATE, c INT DEFAULT TIMESTAMPDIFF(YEAR,a,b)); 1910SHOW CREATE TABLE t1; 1911Table Create Table 1912t1 CREATE TABLE `t1` ( 1913 `a` date DEFAULT NULL, 1914 `b` date DEFAULT NULL, 1915 `c` int(11) DEFAULT timestampdiff(YEAR,`a`,`b`) 1916) ENGINE=MyISAM DEFAULT CHARSET=latin1 1917INSERT INTO t1 (a,b) VALUES ('2002-05-01','2001-01-01'); 1918SELECT * FROM t1; 1919a b c 19202002-05-01 2001-01-01 -1 1921DROP TABLE t1; 1922CREATE TABLE t1 (a DATE, b DATETIME, c INT DEFAULT TIMESTAMPDIFF(MINUTE,a,b)); 1923SHOW CREATE TABLE t1; 1924Table Create Table 1925t1 CREATE TABLE `t1` ( 1926 `a` date DEFAULT NULL, 1927 `b` datetime DEFAULT NULL, 1928 `c` int(11) DEFAULT timestampdiff(MINUTE,`a`,`b`) 1929) ENGINE=MyISAM DEFAULT CHARSET=latin1 1930INSERT INTO t1 (a,b) VALUES ('2003-02-01','2003-05-01 12:05:55'); 1931SELECT * FROM t1; 1932a b c 19332003-02-01 2003-05-01 12:05:55 128885 1934DROP TABLE t1; 1935CREATE OR REPLACE TABLE t1 ( col INT DEFAULT ( 1 LIKE ( NOW() BETWEEN '2000-01-01' AND '2012-12-12' ) ) ); 1936SHOW CREATE TABLE t1; 1937Table Create Table 1938t1 CREATE TABLE `t1` ( 1939 `col` int(11) DEFAULT (1 like (current_timestamp() between '2000-01-01' and '2012-12-12')) 1940) ENGINE=MyISAM DEFAULT CHARSET=latin1 1941SET timestamp = UNIX_TIMESTAMP( '2004-04-04' ); 1942INSERT INTO t1 VALUES( DEFAULT ); 1943SET timestamp = DEFAULT; 1944INSERT INTO t1 VALUES( DEFAULT ); 1945SELECT * FROM t1; 1946col 19471 19480 1949DROP TABLE t1; 1950# 1951# Hybrid type functions 1952# 1953CREATE TABLE t1 (a INT, b INT, c INT DEFAULT COALESCE(a,b)); 1954SHOW CREATE TABLE t1; 1955Table Create Table 1956t1 CREATE TABLE `t1` ( 1957 `a` int(11) DEFAULT NULL, 1958 `b` int(11) DEFAULT NULL, 1959 `c` int(11) DEFAULT coalesce(`a`,`b`) 1960) ENGINE=MyISAM DEFAULT CHARSET=latin1 1961INSERT INTO t1 VALUES (NULL, 1, DEFAULT); 1962SELECT * FROM t1; 1963a b c 1964NULL 1 1 1965DROP TABLE t1; 1966CREATE TABLE t1 (a INT, b INT, c INT DEFAULT IFNULL(a,b)); 1967SHOW CREATE TABLE t1; 1968Table Create Table 1969t1 CREATE TABLE `t1` ( 1970 `a` int(11) DEFAULT NULL, 1971 `b` int(11) DEFAULT NULL, 1972 `c` int(11) DEFAULT ifnull(`a`,`b`) 1973) ENGINE=MyISAM DEFAULT CHARSET=latin1 1974INSERT INTO t1 VALUES (NULL, 2, DEFAULT); 1975INSERT INTO t1 VALUES (1, 2, DEFAULT); 1976SELECT * FROM t1; 1977a b c 1978NULL 2 2 19791 2 1 1980DROP TABLE t1; 1981CREATE TABLE t1 (a INT, b INT, c INT DEFAULT NULLIF(a,b)); 1982SHOW CREATE TABLE t1; 1983Table Create Table 1984t1 CREATE TABLE `t1` ( 1985 `a` int(11) DEFAULT NULL, 1986 `b` int(11) DEFAULT NULL, 1987 `c` int(11) DEFAULT nullif(`a`,`b`) 1988) ENGINE=MyISAM DEFAULT CHARSET=latin1 1989INSERT INTO t1 VALUES (1, 1, DEFAULT); 1990INSERT INTO t1 VALUES (1, 2, DEFAULT); 1991SELECT * FROM t1; 1992a b c 19931 1 NULL 19941 2 1 1995DROP TABLE t1; 1996CREATE TABLE t1 (a INT, b INT, c INT DEFAULT IF(a,b,2)); 1997SHOW CREATE TABLE t1; 1998Table Create Table 1999t1 CREATE TABLE `t1` ( 2000 `a` int(11) DEFAULT NULL, 2001 `b` int(11) DEFAULT NULL, 2002 `c` int(11) DEFAULT if(`a`,`b`,2) 2003) ENGINE=MyISAM DEFAULT CHARSET=latin1 2004INSERT INTO t1 VALUES (0, 1, DEFAULT); 2005INSERT INTO t1 VALUES (1, 1, DEFAULT); 2006SELECT * FROM t1; 2007a b c 20080 1 2 20091 1 1 2010DROP TABLE t1; 2011CREATE TABLE t1 (a INT, b INT, c INT DEFAULT CASE WHEN a THEN b ELSE 2 END); 2012SHOW CREATE TABLE t1; 2013Table Create Table 2014t1 CREATE TABLE `t1` ( 2015 `a` int(11) DEFAULT NULL, 2016 `b` int(11) DEFAULT NULL, 2017 `c` int(11) DEFAULT (case when `a` then `b` else 2 end) 2018) ENGINE=MyISAM DEFAULT CHARSET=latin1 2019INSERT INTO t1 VALUES (0, 1, DEFAULT); 2020INSERT INTO t1 VALUES (1, 1, DEFAULT); 2021SELECT * FROM t1; 2022a b c 20230 1 2 20241 1 1 2025DROP TABLE t1; 2026CREATE TABLE t1 (a INT, b INT DEFAULT (-a)); 2027SHOW CREATE TABLE t1; 2028Table Create Table 2029t1 CREATE TABLE `t1` ( 2030 `a` int(11) DEFAULT NULL, 2031 `b` int(11) DEFAULT (-`a`) 2032) ENGINE=MyISAM DEFAULT CHARSET=latin1 2033SHOW CREATE TABLE t1; 2034Table Create Table 2035t1 CREATE TABLE `t1` ( 2036 `a` int(11) DEFAULT NULL, 2037 `b` int(11) DEFAULT (-`a`) 2038) ENGINE=MyISAM DEFAULT CHARSET=latin1 2039INSERT INTO t1 VALUES (10, DEFAULT); 2040SELECT * FROM t1; 2041a b 204210 -10 2043DROP TABLE t1; 2044CREATE TABLE t1 (a INT, b INT DEFAULT ABS(a)); 2045SHOW CREATE TABLE t1; 2046Table Create Table 2047t1 CREATE TABLE `t1` ( 2048 `a` int(11) DEFAULT NULL, 2049 `b` int(11) DEFAULT abs(`a`) 2050) ENGINE=MyISAM DEFAULT CHARSET=latin1 2051INSERT INTO t1 VALUES (-10, DEFAULT); 2052SELECT * FROM t1; 2053a b 2054-10 10 2055DROP TABLE t1; 2056CREATE TABLE t1 (a DOUBLE, b INT DEFAULT CEILING(a), c INT DEFAULT FLOOR(a), d INT DEFAULT ROUND(a)); 2057SHOW CREATE TABLE t1; 2058Table Create Table 2059t1 CREATE TABLE `t1` ( 2060 `a` double DEFAULT NULL, 2061 `b` int(11) DEFAULT ceiling(`a`), 2062 `c` int(11) DEFAULT floor(`a`), 2063 `d` int(11) DEFAULT round(`a`,0) 2064) ENGINE=MyISAM DEFAULT CHARSET=latin1 2065INSERT INTO t1 VALUES (1.5, DEFAULT, DEFAULT, DEFAULT); 2066INSERT INTO t1 VALUES (-1.5, DEFAULT, DEFAULT, DEFAULT); 2067SELECT * FROM t1; 2068a b c d 20691.5 2 1 2 2070-1.5 -1 -2 -2 2071DROP TABLE t1; 2072CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a+b), d INT DEFAULT (a-b)); 2073SHOW CREATE TABLE t1; 2074Table Create Table 2075t1 CREATE TABLE `t1` ( 2076 `a` int(11) DEFAULT NULL, 2077 `b` int(11) DEFAULT NULL, 2078 `c` int(11) DEFAULT (`a` + `b`), 2079 `d` int(11) DEFAULT (`a` - `b`) 2080) ENGINE=MyISAM DEFAULT CHARSET=latin1 2081INSERT INTO t1 VALUES (2, 1, DEFAULT, DEFAULT); 2082SELECT * FROM t1; 2083a b c d 20842 1 3 1 2085DROP TABLE t1; 2086CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a*b), d INT DEFAULT (a/b), e INT DEFAULT (a MOD b)); 2087SHOW CREATE TABLE t1; 2088Table Create Table 2089t1 CREATE TABLE `t1` ( 2090 `a` int(11) DEFAULT NULL, 2091 `b` int(11) DEFAULT NULL, 2092 `c` int(11) DEFAULT (`a` * `b`), 2093 `d` int(11) DEFAULT (`a` / `b`), 2094 `e` int(11) DEFAULT (`a` MOD `b`) 2095) ENGINE=MyISAM DEFAULT CHARSET=latin1 2096SHOW CREATE TABLE t1; 2097Table Create Table 2098t1 CREATE TABLE `t1` ( 2099 `a` int(11) DEFAULT NULL, 2100 `b` int(11) DEFAULT NULL, 2101 `c` int(11) DEFAULT (`a` * `b`), 2102 `d` int(11) DEFAULT (`a` / `b`), 2103 `e` int(11) DEFAULT (`a` MOD `b`) 2104) ENGINE=MyISAM DEFAULT CHARSET=latin1 2105INSERT INTO t1 VALUES (7, 3, DEFAULT, DEFAULT, DEFAULT); 2106SELECT * FROM t1; 2107a b c d e 21087 3 21 2 1 2109DROP TABLE t1; 2110SET time_zone='+00:00'; 2111CREATE TABLE t1 (a DATETIME, b INT DEFAULT UNIX_TIMESTAMP(a)); 2112SHOW CREATE TABLE t1; 2113Table Create Table 2114t1 CREATE TABLE `t1` ( 2115 `a` datetime DEFAULT NULL, 2116 `b` int(11) DEFAULT unix_timestamp(`a`) 2117) ENGINE=MyISAM DEFAULT CHARSET=latin1 2118INSERT INTO t1 VALUES ('2001-01-01 10:20:30', DEFAULT); 2119SELECT * FROM t1; 2120a b 21212001-01-01 10:20:30 978344430 2122DROP TABLE t1; 2123SET time_zone=DEFAULT; 2124CREATE TABLE t1 (a TIME, b INT DEFAULT TIME_TO_SEC(a)); 2125SHOW CREATE TABLE t1; 2126Table Create Table 2127t1 CREATE TABLE `t1` ( 2128 `a` time DEFAULT NULL, 2129 `b` int(11) DEFAULT time_to_sec(`a`) 2130) ENGINE=MyISAM DEFAULT CHARSET=latin1 2131INSERT INTO t1 VALUES ('22:23:00', DEFAULT); 2132SELECT * FROM t1; 2133a b 213422:23:00 80580 2135DROP TABLE t1; 2136CREATE TABLE t1 (a INT, b INT, c INT DEFAULT LEAST(a,b), d INT DEFAULT GREATEST(a,b)); 2137SHOW CREATE TABLE t1; 2138Table Create Table 2139t1 CREATE TABLE `t1` ( 2140 `a` int(11) DEFAULT NULL, 2141 `b` int(11) DEFAULT NULL, 2142 `c` int(11) DEFAULT least(`a`,`b`), 2143 `d` int(11) DEFAULT greatest(`a`,`b`) 2144) ENGINE=MyISAM DEFAULT CHARSET=latin1 2145INSERT INTO t1 VALUES (0, 1, DEFAULT, DEFAULT); 2146INSERT INTO t1 VALUES (1, 1, DEFAULT, DEFAULT); 2147SELECT * FROM t1; 2148a b c d 21490 1 0 1 21501 1 1 1 2151DROP TABLE t1; 2152CREATE TABLE t1 (a INT, b INT, c INT DEFAULT LAST_VALUE(a,b)); 2153SHOW CREATE TABLE t1; 2154Table Create Table 2155t1 CREATE TABLE `t1` ( 2156 `a` int(11) DEFAULT NULL, 2157 `b` int(11) DEFAULT NULL, 2158 `c` int(11) DEFAULT last_value(`a`,`b`) 2159) ENGINE=MyISAM DEFAULT CHARSET=latin1 2160INSERT INTO t1 VALUES (1, 2, DEFAULT); 2161SELECT * FROM t1; 2162a b c 21631 2 2 2164DROP TABLE t1; 2165# 2166# CAST 2167# 2168CREATE TABLE t1 (a VARCHAR(30), b DECIMAL(10,6) DEFAULT CAST(a AS DECIMAL(10,1))); 2169SHOW CREATE TABLE t1; 2170Table Create Table 2171t1 CREATE TABLE `t1` ( 2172 `a` varchar(30) DEFAULT NULL, 2173 `b` decimal(10,6) DEFAULT (cast(`a` as decimal(10,1))) 2174) ENGINE=MyISAM DEFAULT CHARSET=latin1 2175INSERT INTO t1 (a) VALUES ('123.456'); 2176SELECT * FROM t1; 2177a b 2178123.456 123.500000 2179DROP TABLE t1; 2180CREATE TABLE t1 (a DECIMAL(10,3), 2181b VARCHAR(10) DEFAULT CAST(a AS CHAR(10)), 2182c VARCHAR(10) DEFAULT CAST(a AS CHAR(4))); 2183SHOW CREATE TABLE t1; 2184Table Create Table 2185t1 CREATE TABLE `t1` ( 2186 `a` decimal(10,3) DEFAULT NULL, 2187 `b` varchar(10) DEFAULT (cast(`a` as char(10) charset latin1)), 2188 `c` varchar(10) DEFAULT (cast(`a` as char(4) charset latin1)) 2189) ENGINE=MyISAM DEFAULT CHARSET=latin1 2190INSERT IGNORE INTO t1 (a) VALUES (123.456); 2191Warnings: 2192Warning 1292 Truncated incorrect CHAR(4) value: '123.456' 2193SELECT * FROM t1; 2194a b c 2195123.456 123.456 123. 2196DROP TABLE t1; 2197CREATE TABLE t1 (a INT, b INT UNSIGNED DEFAULT CAST(a AS UNSIGNED)); 2198SHOW CREATE TABLE t1; 2199Table Create Table 2200t1 CREATE TABLE `t1` ( 2201 `a` int(11) DEFAULT NULL, 2202 `b` int(10) unsigned DEFAULT (cast(`a` as unsigned)) 2203) ENGINE=MyISAM DEFAULT CHARSET=latin1 2204INSERT IGNORE INTO t1 (a) VALUES (-1); 2205Warnings: 2206Note 1105 Cast to unsigned converted negative integer to it's positive complement 2207Warning 1264 Out of range value for column 'b' at row 1 2208SELECT * FROM t1; 2209a b 2210-1 4294967295 2211DROP TABLE t1; 2212CREATE TABLE t1 (a BIGINT UNSIGNED, b BIGINT SIGNED DEFAULT CAST(a AS SIGNED)); 2213SHOW CREATE TABLE t1; 2214Table Create Table 2215t1 CREATE TABLE `t1` ( 2216 `a` bigint(20) unsigned DEFAULT NULL, 2217 `b` bigint(20) DEFAULT (cast(`a` as signed)) 2218) ENGINE=MyISAM DEFAULT CHARSET=latin1 2219INSERT INTO t1 (a) VALUES (0xFFFFFFFFFFFFFFFF); 2220SELECT * FROM t1; 2221a b 222218446744073709551615 -1 2223DROP TABLE t1; 2224CREATE TABLE t1 ( 2225a VARCHAR(10) CHARACTER SET latin1, 2226b VARCHAR(10) CHARACTER SET latin1 DEFAULT a COLLATE latin1_bin, 2227c VARCHAR(10) CHARACTER SET utf8 DEFAULT CONVERT(a USING utf8), 2228d VARBINARY(10) DEFAULT (BINARY(a)) 2229); 2230SHOW CREATE TABLE t1; 2231Table Create Table 2232t1 CREATE TABLE `t1` ( 2233 `a` varchar(10) DEFAULT NULL, 2234 `b` varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT `a`, 2235 `c` varchar(10) CHARACTER SET utf8 DEFAULT convert(`a` using utf8), 2236 `d` varbinary(10) DEFAULT (cast(`a` as char charset binary)) 2237) ENGINE=MyISAM DEFAULT CHARSET=latin1 2238INSERT INTO t1 (a) VALUES ('a'); 2239SELECT * FROM t1; 2240a b c d 2241a a a a 2242DROP TABLE t1; 2243# 2244# Bit functions 2245# 2246CREATE TABLE t1 (a INT, b INT DEFAULT BIT_COUNT(a)); 2247SHOW CREATE TABLE t1; 2248Table Create Table 2249t1 CREATE TABLE `t1` ( 2250 `a` int(11) DEFAULT NULL, 2251 `b` int(11) DEFAULT bit_count(`a`) 2252) ENGINE=MyISAM DEFAULT CHARSET=latin1 2253INSERT INTO t1 (a) VALUES (7); 2254SELECT * FROM t1; 2255a b 22567 3 2257DROP TABLE t1; 2258CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a|b)); 2259SHOW CREATE TABLE t1; 2260Table Create Table 2261t1 CREATE TABLE `t1` ( 2262 `a` int(11) DEFAULT NULL, 2263 `b` int(11) DEFAULT NULL, 2264 `c` int(11) DEFAULT (`a` | `b`) 2265) ENGINE=MyISAM DEFAULT CHARSET=latin1 2266INSERT INTO t1 (a,b) VALUES (1,2); 2267SELECT * FROM t1; 2268a b c 22691 2 3 2270DROP TABLE t1; 2271CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a&b)); 2272SHOW CREATE TABLE t1; 2273Table Create Table 2274t1 CREATE TABLE `t1` ( 2275 `a` int(11) DEFAULT NULL, 2276 `b` int(11) DEFAULT NULL, 2277 `c` int(11) DEFAULT (`a` & `b`) 2278) ENGINE=MyISAM DEFAULT CHARSET=latin1 2279INSERT INTO t1 (a,b) VALUES (5,4); 2280SELECT * FROM t1; 2281a b c 22825 4 4 2283DROP TABLE t1; 2284CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a^b)); 2285SHOW CREATE TABLE t1; 2286Table Create Table 2287t1 CREATE TABLE `t1` ( 2288 `a` int(11) DEFAULT NULL, 2289 `b` int(11) DEFAULT NULL, 2290 `c` int(11) DEFAULT (`a` ^ `b`) 2291) ENGINE=MyISAM DEFAULT CHARSET=latin1 2292INSERT INTO t1 (a,b) VALUES (11,3); 2293SELECT * FROM t1; 2294a b c 229511 3 8 2296DROP TABLE t1; 2297CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a&~b)); 2298SHOW CREATE TABLE t1; 2299Table Create Table 2300t1 CREATE TABLE `t1` ( 2301 `a` int(11) DEFAULT NULL, 2302 `b` int(11) DEFAULT NULL, 2303 `c` int(11) DEFAULT (`a` & ~`b`) 2304) ENGINE=MyISAM DEFAULT CHARSET=latin1 2305INSERT INTO t1 (a,b) VALUES (5,1); 2306SELECT * FROM t1; 2307a b c 23085 1 4 2309DROP TABLE t1; 2310CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a<<b), d INT DEFAULT (a>>b)); 2311SHOW CREATE TABLE t1; 2312Table Create Table 2313t1 CREATE TABLE `t1` ( 2314 `a` int(11) DEFAULT NULL, 2315 `b` int(11) DEFAULT NULL, 2316 `c` int(11) DEFAULT (`a` << `b`), 2317 `d` int(11) DEFAULT (`a` >> `b`) 2318) ENGINE=MyISAM DEFAULT CHARSET=latin1 2319INSERT INTO t1 (a,b) VALUES (5,1); 2320SELECT * FROM t1; 2321a b c d 23225 1 10 2 2323DROP TABLE t1; 2324# 2325# String functions 2326# 2327CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(20) DEFAULT REVERSE(a)); 2328SHOW CREATE TABLE t1; 2329Table Create Table 2330t1 CREATE TABLE `t1` ( 2331 `a` varchar(10) DEFAULT NULL, 2332 `b` varchar(20) DEFAULT reverse(`a`) 2333) ENGINE=MyISAM DEFAULT CHARSET=latin1 2334INSERT INTO t1 (a) VALUES ('abcd'); 2335SELECT * FROM t1; 2336a b 2337abcd dcba 2338DROP TABLE t1; 2339CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT UPPER(a), c VARCHAR(10) DEFAULT LOWER(a)); 2340SHOW CREATE TABLE t1; 2341Table Create Table 2342t1 CREATE TABLE `t1` ( 2343 `a` varchar(10) DEFAULT NULL, 2344 `b` varchar(10) DEFAULT ucase(`a`), 2345 `c` varchar(10) DEFAULT lcase(`a`) 2346) ENGINE=MyISAM DEFAULT CHARSET=latin1 2347INSERT INTO t1 (a) VALUES ('ABcd'); 2348SELECT * FROM t1; 2349a b c 2350ABcd ABCD abcd 2351DROP TABLE t1; 2352CREATE 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)); 2353SHOW CREATE TABLE t1; 2354Table Create Table 2355t1 CREATE TABLE `t1` ( 2356 `a` varchar(10) DEFAULT NULL, 2357 `b` varchar(10) DEFAULT left(`a`,1), 2358 `c` varchar(10) DEFAULT right(`a`,1), 2359 `d` varchar(10) DEFAULT substr(`a`,2,2) 2360) ENGINE=MyISAM DEFAULT CHARSET=latin1 2361INSERT INTO t1 (a) VALUES ('abcd'); 2362SELECT * FROM t1; 2363a b c d 2364abcd a d bc 2365DROP TABLE t1; 2366CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20) DEFAULT SUBSTRING_INDEX(a,'.',2)); 2367SHOW CREATE TABLE t1; 2368Table Create Table 2369t1 CREATE TABLE `t1` ( 2370 `a` varchar(20) DEFAULT NULL, 2371 `b` varchar(20) DEFAULT substring_index(`a`,'.',2) 2372) ENGINE=MyISAM DEFAULT CHARSET=latin1 2373INSERT INTO t1 (a) VALUES ('www.mariadb.org'); 2374SELECT * FROM t1; 2375a b 2376www.mariadb.org www.mariadb 2377DROP TABLE t1; 2378CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10), c VARCHAR(20) DEFAULT CONCAT(a,b)); 2379SHOW CREATE TABLE t1; 2380Table Create Table 2381t1 CREATE TABLE `t1` ( 2382 `a` varchar(10) DEFAULT NULL, 2383 `b` varchar(10) DEFAULT NULL, 2384 `c` varchar(20) DEFAULT concat(`a`,`b`) 2385) ENGINE=MyISAM DEFAULT CHARSET=latin1 2386INSERT INTO t1 (a,b) VALUES ('a','b'); 2387SELECT * FROM t1; 2388a b c 2389a b ab 2390DROP TABLE t1; 2391CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10), c VARCHAR(20) DEFAULT CONCAT_WS(',',a,b)); 2392SHOW CREATE TABLE t1; 2393Table Create Table 2394t1 CREATE TABLE `t1` ( 2395 `a` varchar(10) DEFAULT NULL, 2396 `b` varchar(10) DEFAULT NULL, 2397 `c` varchar(20) DEFAULT concat_ws(',',`a`,`b`) 2398) ENGINE=MyISAM DEFAULT CHARSET=latin1 2399INSERT INTO t1 (a,b) VALUES ('a','b'); 2400SELECT * FROM t1; 2401a b c 2402a b a,b 2403DROP TABLE t1; 2404CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT REPLACE(a,'a','A')); 2405SHOW CREATE TABLE t1; 2406Table Create Table 2407t1 CREATE TABLE `t1` ( 2408 `a` varchar(10) DEFAULT NULL, 2409 `b` varchar(10) DEFAULT replace(`a`,'a','A') 2410) ENGINE=MyISAM DEFAULT CHARSET=latin1 2411INSERT INTO t1 (a) VALUES ('abc'); 2412SELECT * FROM t1; 2413a b 2414abc Abc 2415DROP TABLE t1; 2416CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT REGEXP_REPLACE(a,'[0-9]','.')); 2417SHOW CREATE TABLE t1; 2418Table Create Table 2419t1 CREATE TABLE `t1` ( 2420 `a` varchar(10) DEFAULT NULL, 2421 `b` varchar(10) DEFAULT regexp_replace(`a`,'[0-9]','.') 2422) ENGINE=MyISAM DEFAULT CHARSET=latin1 2423INSERT INTO t1 (a) VALUES ('a1b2c'); 2424SELECT * FROM t1; 2425a b 2426a1b2c a.b.c 2427DROP TABLE t1; 2428CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT REGEXP_SUBSTR(a,'[0-9]+')); 2429SHOW CREATE TABLE t1; 2430Table Create Table 2431t1 CREATE TABLE `t1` ( 2432 `a` varchar(10) DEFAULT NULL, 2433 `b` varchar(10) DEFAULT regexp_substr(`a`,'[0-9]+') 2434) ENGINE=MyISAM DEFAULT CHARSET=latin1 2435INSERT INTO t1 (a) VALUES ('ab12cd'); 2436SELECT * FROM t1; 2437a b 2438ab12cd 12 2439DROP TABLE t1; 2440CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20) DEFAULT SOUNDEX(a)); 2441SHOW CREATE TABLE t1; 2442Table Create Table 2443t1 CREATE TABLE `t1` ( 2444 `a` varchar(20) DEFAULT NULL, 2445 `b` varchar(20) DEFAULT soundex(`a`) 2446) ENGINE=MyISAM DEFAULT CHARSET=latin1 2447INSERT INTO t1 (a) VALUES ('tester'); 2448SELECT * FROM t1; 2449a b 2450tester T236 2451DROP TABLE t1; 2452CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20) DEFAULT QUOTE(a)); 2453SHOW CREATE TABLE t1; 2454Table Create Table 2455t1 CREATE TABLE `t1` ( 2456 `a` varchar(20) DEFAULT NULL, 2457 `b` varchar(20) DEFAULT quote(`a`) 2458) ENGINE=MyISAM DEFAULT CHARSET=latin1 2459INSERT INTO t1 (a) VALUES ('a\'b'); 2460SELECT * FROM t1; 2461a b 2462a'b 'a\'b' 2463DROP TABLE t1; 2464CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT LPAD(a,10,'.'), c VARCHAR(10) DEFAULT RPAD(a,10,'.')); 2465SHOW CREATE TABLE t1; 2466Table Create Table 2467t1 CREATE TABLE `t1` ( 2468 `a` varchar(10) DEFAULT NULL, 2469 `b` varchar(10) DEFAULT lpad(`a`,10,'.'), 2470 `c` varchar(10) DEFAULT rpad(`a`,10,'.') 2471) ENGINE=MyISAM DEFAULT CHARSET=latin1 2472INSERT INTO t1 (a) VALUES ('ab'); 2473SELECT * FROM t1; 2474a b c 2475ab ........ab ab........ 2476DROP TABLE t1; 2477CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT LTRIM(a), c VARCHAR(10) DEFAULT RTRIM(a)); 2478SHOW CREATE TABLE t1; 2479Table Create Table 2480t1 CREATE TABLE `t1` ( 2481 `a` varchar(10) DEFAULT NULL, 2482 `b` varchar(10) DEFAULT ltrim(`a`), 2483 `c` varchar(10) DEFAULT rtrim(`a`) 2484) ENGINE=MyISAM DEFAULT CHARSET=latin1 2485INSERT INTO t1 (a) VALUES (' ab '); 2486SELECT a, HEX(b), HEX(c) FROM t1; 2487a HEX(b) HEX(c) 2488 ab 616220 206162 2489DROP TABLE t1; 2490CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT TRIM(BOTH 'a' FROM a)); 2491SHOW CREATE TABLE t1; 2492Table Create Table 2493t1 CREATE TABLE `t1` ( 2494 `a` varchar(10) DEFAULT NULL, 2495 `b` varchar(10) DEFAULT trim(both 'a' from `a`) 2496) ENGINE=MyISAM DEFAULT CHARSET=latin1 2497INSERT INTO t1 (a) VALUES ('abba'); 2498SELECT a, b FROM t1; 2499a b 2500abba bb 2501DROP TABLE t1; 2502CREATE TABLE t1 (a INT, b VARCHAR(10) DEFAULT SPACE(a)); 2503SHOW CREATE TABLE t1; 2504Table Create Table 2505t1 CREATE TABLE `t1` ( 2506 `a` int(11) DEFAULT NULL, 2507 `b` varchar(10) DEFAULT space(`a`) 2508) ENGINE=MyISAM DEFAULT CHARSET=latin1 2509INSERT INTO t1 (a) VALUES (3); 2510SELECT a, HEX(b) FROM t1; 2511a HEX(b) 25123 202020 2513DROP TABLE t1; 2514CREATE TABLE t1 (a INT, b VARCHAR(10), c VARCHAR(10) DEFAULT REPEAT(b,a)); 2515SHOW CREATE TABLE t1; 2516Table Create Table 2517t1 CREATE TABLE `t1` ( 2518 `a` int(11) DEFAULT NULL, 2519 `b` varchar(10) DEFAULT NULL, 2520 `c` varchar(10) DEFAULT repeat(`b`,`a`) 2521) ENGINE=MyISAM DEFAULT CHARSET=latin1 2522INSERT INTO t1 (a,b) VALUES (3,'x'); 2523SELECT a, b, c FROM t1; 2524a b c 25253 x xxx 2526DROP TABLE t1; 2527CREATE TABLE t1 (str VARCHAR(10), pos INT, len INT, newstr VARCHAR(10), result VARCHAR(10) DEFAULT INSERT(str,pos,len,newstr)); 2528SHOW CREATE TABLE t1; 2529Table Create Table 2530t1 CREATE TABLE `t1` ( 2531 `str` varchar(10) DEFAULT NULL, 2532 `pos` int(11) DEFAULT NULL, 2533 `len` int(11) DEFAULT NULL, 2534 `newstr` varchar(10) DEFAULT NULL, 2535 `result` varchar(10) DEFAULT insert(`str`,`pos`,`len`,`newstr`) 2536) ENGINE=MyISAM DEFAULT CHARSET=latin1 2537INSERT INTO t1 (str,pos,len,newstr) VALUES ('Quadratic', 3, 4, 'What'); 2538SELECT * FROM t1; 2539str pos len newstr result 2540Quadratic 3 4 What QuWhattic 2541DROP TABLE t1; 2542CREATE TABLE t1 (n INT, res VARCHAR(10) DEFAULT ELT(n,'ej', 'Heja', 'hej', 'foo')); 2543SHOW CREATE TABLE t1; 2544Table Create Table 2545t1 CREATE TABLE `t1` ( 2546 `n` int(11) DEFAULT NULL, 2547 `res` varchar(10) DEFAULT elt(`n`,'ej','Heja','hej','foo') 2548) ENGINE=MyISAM DEFAULT CHARSET=latin1 2549INSERT INTO t1 (n) VALUES (1); 2550SELECT * FROM t1; 2551n res 25521 ej 2553DROP TABLE t1; 2554CREATE TABLE t1 (bits INT, res VARCHAR(10) DEFAULT MAKE_SET(bits,'a','b','c','d')); 2555SHOW CREATE TABLE t1; 2556Table Create Table 2557t1 CREATE TABLE `t1` ( 2558 `bits` int(11) DEFAULT NULL, 2559 `res` varchar(10) DEFAULT make_set(`bits`,'a','b','c','d') 2560) ENGINE=MyISAM DEFAULT CHARSET=latin1 2561INSERT INTO t1 (bits) VALUES (1|4); 2562SELECT * FROM t1; 2563bits res 25645 a,c 2565DROP TABLE t1; 2566CREATE TABLE t1 (a INT, b VARCHAR(10) DEFAULT CHAR(a)); 2567SHOW CREATE TABLE t1; 2568Table Create Table 2569t1 CREATE TABLE `t1` ( 2570 `a` int(11) DEFAULT NULL, 2571 `b` varchar(10) DEFAULT char(`a`) 2572) ENGINE=MyISAM DEFAULT CHARSET=latin1 2573INSERT INTO t1 (a) VALUES (77); 2574SELECT * FROM t1; 2575a b 257677 M 2577DROP TABLE t1; 2578CREATE TABLE t1 (a INT, b VARCHAR(10) DEFAULT CONV(a,10,16)); 2579SHOW CREATE TABLE t1; 2580Table Create Table 2581t1 CREATE TABLE `t1` ( 2582 `a` int(11) DEFAULT NULL, 2583 `b` varchar(10) DEFAULT conv(`a`,10,16) 2584) ENGINE=MyISAM DEFAULT CHARSET=latin1 2585INSERT INTO t1 (a) VALUES (64); 2586SELECT * FROM t1; 2587a b 258864 40 2589DROP TABLE t1; 2590CREATE TABLE t1 (a INT, b INT, c VARCHAR(30) DEFAULT FORMAT(a,b)); 2591SHOW CREATE TABLE t1; 2592Table Create Table 2593t1 CREATE TABLE `t1` ( 2594 `a` int(11) DEFAULT NULL, 2595 `b` int(11) DEFAULT NULL, 2596 `c` varchar(30) DEFAULT format(`a`,`b`) 2597) ENGINE=MyISAM DEFAULT CHARSET=latin1 2598INSERT INTO t1 (a,b) VALUES (10000,3); 2599SELECT * FROM t1; 2600a b c 260110000 3 10,000.000 2602DROP TABLE t1; 2603CREATE TABLE t1 (a INT, b INT, l VARCHAR(10), c VARCHAR(30) DEFAULT FORMAT(a,b,l)); 2604SHOW CREATE TABLE t1; 2605Table Create Table 2606t1 CREATE TABLE `t1` ( 2607 `a` int(11) DEFAULT NULL, 2608 `b` int(11) DEFAULT NULL, 2609 `l` varchar(10) DEFAULT NULL, 2610 `c` varchar(30) DEFAULT format(`a`,`b`,`l`) 2611) ENGINE=MyISAM DEFAULT CHARSET=latin1 2612INSERT INTO t1 (a,b,l) VALUES (10000,2,'no_NO'),(10000,2,'ru_RU'),(10000,2,'ar_BH'); 2613SELECT * FROM t1; 2614a b l c 261510000 2 no_NO 10.000,00 261610000 2 ru_RU 10 000,00 261710000 2 ar_BH 10,000.00 2618DROP TABLE t1; 2619CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(20) DEFAULT GET_FORMAT(DATE,a)); 2620SHOW CREATE TABLE t1; 2621Table Create Table 2622t1 CREATE TABLE `t1` ( 2623 `a` varchar(10) DEFAULT NULL, 2624 `b` varchar(20) DEFAULT get_format(DATE, `a`) 2625) ENGINE=MyISAM DEFAULT CHARSET=latin1 2626INSERT INTO t1 (a) VALUES ('EUR'),('USA'),('JIS'),('ISO'),('INTERNAL'); 2627SELECT * FROM t1; 2628a b 2629EUR %d.%m.%Y 2630USA %m.%d.%Y 2631JIS %Y-%m-%d 2632ISO %Y-%m-%d 2633INTERNAL %Y%m%d 2634DROP TABLE t1; 2635CREATE TABLE t1 ( 2636bits INT, 2637v_on VARCHAR(10), 2638v_off VARCHAR(10), 2639v_separator VARCHAR(10), 2640number_of_bits INT, 2641x VARCHAR(30) DEFAULT EXPORT_SET(bits, v_on, v_off, v_separator, number_of_bits) 2642); 2643SHOW CREATE TABLE t1; 2644Table Create Table 2645t1 CREATE TABLE `t1` ( 2646 `bits` int(11) DEFAULT NULL, 2647 `v_on` varchar(10) DEFAULT NULL, 2648 `v_off` varchar(10) DEFAULT NULL, 2649 `v_separator` varchar(10) DEFAULT NULL, 2650 `number_of_bits` int(11) DEFAULT NULL, 2651 `x` varchar(30) DEFAULT export_set(`bits`,`v_on`,`v_off`,`v_separator`,`number_of_bits`) 2652) ENGINE=MyISAM DEFAULT CHARSET=latin1 2653INSERT IGNORE INTO t1 VALUES (0x50006,'Y','N','',64,DEFAULT); 2654Warnings: 2655Warning 1265 Data truncated for column 'x' at row 1 2656SELECT * FROM t1; 2657bits v_on v_off v_separator number_of_bits x 2658327686 Y N 64 NYYNNNNNNNNNNNNNYNYNNNNNNNNNNN 2659DROP TABLE t1; 2660CREATE TABLE t1 (a VARCHAR(30), b BLOB DEFAULT LOAD_FILE(a)); 2661ERROR HY000: Function or expression 'load_file()' cannot be used in the DEFAULT clause of `b` 2662# 2663# Predicates 2664# 2665CREATE TABLE t1 (a INT, b INT DEFAULT (NOT a)); 2666SHOW CREATE TABLE t1; 2667Table Create Table 2668t1 CREATE TABLE `t1` ( 2669 `a` int(11) DEFAULT NULL, 2670 `b` int(11) DEFAULT (`a` = 0) 2671) ENGINE=MyISAM DEFAULT CHARSET=latin1 2672INSERT INTO t1 (a) VALUES (NULL),(0),(1); 2673SELECT * FROM t1; 2674a b 2675NULL NULL 26760 1 26771 0 2678DROP TABLE t1; 2679CREATE TABLE t1 (a INT, b INT, x INT DEFAULT (a XOR b)); 2680SHOW CREATE TABLE t1; 2681Table Create Table 2682t1 CREATE TABLE `t1` ( 2683 `a` int(11) DEFAULT NULL, 2684 `b` int(11) DEFAULT NULL, 2685 `x` int(11) DEFAULT (`a` xor `b`) 2686) ENGINE=MyISAM DEFAULT CHARSET=latin1 2687INSERT INTO t1 (a,b) VALUES (0,0),(0,1),(1,0),(1,1); 2688SELECT * FROM t1; 2689a b x 26900 0 0 26910 1 1 26921 0 1 26931 1 0 2694DROP TABLE t1; 2695CREATE TABLE t1 (a INT, b INT DEFAULT (a IS TRUE), c INT DEFAULT (a IS NOT TRUE)); 2696SHOW CREATE TABLE t1; 2697Table Create Table 2698t1 CREATE TABLE `t1` ( 2699 `a` int(11) DEFAULT NULL, 2700 `b` int(11) DEFAULT (`a` is true), 2701 `c` int(11) DEFAULT (`a` is not true) 2702) ENGINE=MyISAM DEFAULT CHARSET=latin1 2703INSERT INTO t1 (a) VALUES (NULL),(0),(1); 2704SELECT * FROM t1; 2705a b c 2706NULL 0 1 27070 0 1 27081 1 0 2709DROP TABLE t1; 2710CREATE TABLE t1 (a INT, b INT DEFAULT (a IS FALSE), c INT DEFAULT (a IS NOT FALSE)); 2711SHOW CREATE TABLE t1; 2712Table Create Table 2713t1 CREATE TABLE `t1` ( 2714 `a` int(11) DEFAULT NULL, 2715 `b` int(11) DEFAULT (`a` is false), 2716 `c` int(11) DEFAULT (`a` is not false) 2717) ENGINE=MyISAM DEFAULT CHARSET=latin1 2718INSERT INTO t1 (a) VALUES (NULL),(0),(1); 2719SELECT * FROM t1; 2720a b c 2721NULL 0 1 27220 1 0 27231 0 1 2724DROP TABLE t1; 2725CREATE TABLE t1 (a INT, b INT DEFAULT (a IS NULL), c INT DEFAULT (a IS NOT NULL)); 2726SHOW CREATE TABLE t1; 2727Table Create Table 2728t1 CREATE TABLE `t1` ( 2729 `a` int(11) DEFAULT NULL, 2730 `b` int(11) DEFAULT (`a` is null), 2731 `c` int(11) DEFAULT (`a` is not null) 2732) ENGINE=MyISAM DEFAULT CHARSET=latin1 2733INSERT INTO t1 (a) VALUES (NULL),(0),(1); 2734SELECT * FROM t1; 2735a b c 2736NULL 1 0 27370 0 1 27381 0 1 2739DROP TABLE t1; 2740CREATE TABLE t1 (a INT, b INT DEFAULT (a IS UNKNOWN), c INT DEFAULT (a IS NOT UNKNOWN)); 2741SHOW CREATE TABLE t1; 2742Table Create Table 2743t1 CREATE TABLE `t1` ( 2744 `a` int(11) DEFAULT NULL, 2745 `b` int(11) DEFAULT (`a` is null), 2746 `c` int(11) DEFAULT (`a` is not null) 2747) ENGINE=MyISAM DEFAULT CHARSET=latin1 2748INSERT INTO t1 (a) VALUES (NULL),(0),(1); 2749SELECT * FROM t1; 2750a b c 2751NULL 1 0 27520 0 1 27531 0 1 2754DROP TABLE t1; 2755CREATE TABLE t1 (a INT, 2756eq INT DEFAULT (a=0), equal INT DEFAULT (a<=>0), 2757ne INT DEFAULT (a<>0), 2758lt INT DEFAULT (a<0), le INT DEFAULT (a<=0), 2759gt INT DEFAULT (a>0), ge INT DEFAULT (a>=0)); 2760SHOW CREATE TABLE t1; 2761Table Create Table 2762t1 CREATE TABLE `t1` ( 2763 `a` int(11) DEFAULT NULL, 2764 `eq` int(11) DEFAULT (`a` = 0), 2765 `equal` int(11) DEFAULT (`a` <=> 0), 2766 `ne` int(11) DEFAULT (`a` <> 0), 2767 `lt` int(11) DEFAULT (`a` < 0), 2768 `le` int(11) DEFAULT (`a` <= 0), 2769 `gt` int(11) DEFAULT (`a` > 0), 2770 `ge` int(11) DEFAULT (`a` >= 0) 2771) ENGINE=MyISAM DEFAULT CHARSET=latin1 2772INSERT INTO t1 (a) VALUES (NULL),(-1),(0),(1); 2773SELECT * FROM t1; 2774a eq equal ne lt le gt ge 2775NULL NULL 0 NULL NULL NULL NULL NULL 2776-1 0 0 1 1 1 0 0 27770 1 1 0 0 1 0 1 27781 0 0 1 0 0 1 1 2779DROP TABLE t1; 2780CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a LIKE 'a%')); 2781SHOW CREATE TABLE t1; 2782Table Create Table 2783t1 CREATE TABLE `t1` ( 2784 `a` varchar(10) DEFAULT NULL, 2785 `b` int(11) DEFAULT (`a` like 'a%') 2786) ENGINE=MyISAM DEFAULT CHARSET=latin1 2787INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'); 2788SELECT * FROM t1; 2789a b 2790AAA 1 2791aaa 1 2792bbb 0 2793DROP TABLE t1; 2794CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a RLIKE 'a$')); 2795SHOW CREATE TABLE t1; 2796Table Create Table 2797t1 CREATE TABLE `t1` ( 2798 `a` varchar(10) DEFAULT NULL, 2799 `b` int(11) DEFAULT (`a` regexp 'a$') 2800) ENGINE=MyISAM DEFAULT CHARSET=latin1 2801INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'); 2802SELECT * FROM t1; 2803a b 2804AAA 1 2805aaa 1 2806bbb 0 2807DROP TABLE t1; 2808CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a IN ('aaa','bbb'))); 2809SHOW CREATE TABLE t1; 2810Table Create Table 2811t1 CREATE TABLE `t1` ( 2812 `a` varchar(10) DEFAULT NULL, 2813 `b` int(11) DEFAULT (`a` in ('aaa','bbb')) 2814) ENGINE=MyISAM DEFAULT CHARSET=latin1 2815INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'),('ccc'); 2816SELECT * FROM t1; 2817a b 2818AAA 1 2819aaa 1 2820bbb 1 2821ccc 0 2822DROP TABLE t1; 2823CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a NOT IN ('aaa','bbb'))); 2824SHOW CREATE TABLE t1; 2825Table Create Table 2826t1 CREATE TABLE `t1` ( 2827 `a` varchar(10) DEFAULT NULL, 2828 `b` int(11) DEFAULT (`a` not in ('aaa','bbb')) 2829) ENGINE=MyISAM DEFAULT CHARSET=latin1 2830INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'),('ccc'); 2831SELECT * FROM t1; 2832a b 2833AAA 0 2834aaa 0 2835bbb 0 2836ccc 1 2837DROP TABLE t1; 2838CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a BETWEEN 'aaa' AND 'bbb')); 2839SHOW CREATE TABLE t1; 2840Table Create Table 2841t1 CREATE TABLE `t1` ( 2842 `a` varchar(10) DEFAULT NULL, 2843 `b` int(11) DEFAULT (`a` between 'aaa' and 'bbb') 2844) ENGINE=MyISAM DEFAULT CHARSET=latin1 2845INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'),('ccc'); 2846SELECT * FROM t1; 2847a b 2848AAA 1 2849aaa 1 2850bbb 1 2851ccc 0 2852DROP TABLE t1; 2853CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a NOT BETWEEN 'aaa' AND 'bbb')); 2854SHOW CREATE TABLE t1; 2855Table Create Table 2856t1 CREATE TABLE `t1` ( 2857 `a` varchar(10) DEFAULT NULL, 2858 `b` int(11) DEFAULT (`a` not between 'aaa' and 'bbb') 2859) ENGINE=MyISAM DEFAULT CHARSET=latin1 2860INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'),('ccc'); 2861SELECT * FROM t1; 2862a b 2863AAA 0 2864aaa 0 2865bbb 0 2866ccc 1 2867DROP TABLE t1; 2868CREATE TABLE t1 (a TEXT DEFAULT UUID()); 2869SHOW CREATE TABLE t1; 2870Table Create Table 2871t1 CREATE TABLE `t1` ( 2872 `a` text DEFAULT uuid() 2873) ENGINE=MyISAM DEFAULT CHARSET=latin1 2874INSERT INTO t1 VALUES (); 2875SELECT LENGTH(a)>0 FROM t1; 2876LENGTH(a)>0 28771 2878DROP TABLE t1; 2879# 2880# Numeric result functions with string input 2881# 2882CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT STRCMP(a,'b')); 2883SHOW CREATE TABLE t1; 2884Table Create Table 2885t1 CREATE TABLE `t1` ( 2886 `a` varchar(10) DEFAULT NULL, 2887 `b` int(11) DEFAULT strcmp(`a`,'b') 2888) ENGINE=MyISAM DEFAULT CHARSET=latin1 2889INSERT INTO t1 (a) VALUES ('A'),('a'),('B'),('b'),('C'),('c'); 2890SELECT * FROM t1; 2891a b 2892A -1 2893a -1 2894B 0 2895b 0 2896C 1 2897c 1 2898DROP TABLE t1; 2899CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT LENGTH(a), c INT DEFAULT CHAR_LENGTH(a), d INT DEFAULT BIT_LENGTH(a)); 2900SHOW CREATE TABLE t1; 2901Table Create Table 2902t1 CREATE TABLE `t1` ( 2903 `a` varchar(10) DEFAULT NULL, 2904 `b` int(11) DEFAULT octet_length(`a`), 2905 `c` int(11) DEFAULT char_length(`a`), 2906 `d` int(11) DEFAULT bit_length(`a`) 2907) ENGINE=MyISAM DEFAULT CHARSET=latin1 2908INSERT INTO t1 (a) VALUES ('a'),('aa'),('aaa'); 2909SELECT * FROM t1; 2910a b c d 2911a 1 1 8 2912aa 2 2 16 2913aaa 3 3 24 2914DROP TABLE t1; 2915CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT LOCATE('a',a)); 2916SHOW CREATE TABLE t1; 2917Table Create Table 2918t1 CREATE TABLE `t1` ( 2919 `a` varchar(10) DEFAULT NULL, 2920 `b` int(11) DEFAULT locate('a',`a`) 2921) ENGINE=MyISAM DEFAULT CHARSET=latin1 2922INSERT INTO t1 (a) VALUES ('xa'),('xxa'),('xxxa'); 2923SELECT * FROM t1; 2924a b 2925xa 2 2926xxa 3 2927xxxa 4 2928DROP TABLE t1; 2929CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT REGEXP_INSTR(a, 'a')); 2930SHOW CREATE TABLE t1; 2931Table Create Table 2932t1 CREATE TABLE `t1` ( 2933 `a` varchar(10) DEFAULT NULL, 2934 `b` int(11) DEFAULT regexp_instr(`a`,'a') 2935) ENGINE=MyISAM DEFAULT CHARSET=latin1 2936INSERT INTO t1 (a) VALUES ('xa'),('xxa'),('xxxa'); 2937SELECT * FROM t1; 2938a b 2939xa 2 2940xxa 3 2941xxxa 4 2942DROP TABLE t1; 2943# 2944# INT result metadata functions 2945# 2946CREATE TABLE t1 2947( 2948id SERIAL PRIMARY KEY, 2949b INT DEFAULT LAST_INSERT_ID() 2950); 2951ERROR HY000: Function or expression 'last_insert_id()' cannot be used in the DEFAULT clause of `b` 2952CREATE TABLE t1 (a INT DEFAULT CONNECTION_ID()); 2953SHOW CREATE TABLE t1; 2954Table Create Table 2955t1 CREATE TABLE `t1` ( 2956 `a` int(11) DEFAULT connection_id() 2957) ENGINE=MyISAM DEFAULT CHARSET=latin1 2958INSERT INTO t1 VALUES(); 2959SELECT a>0 FROM t1; 2960a>0 29611 2962DROP TABLE t1; 2963CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT COERCIBILITY(a), c INT DEFAULT COERCIBILITY(b)); 2964SHOW CREATE TABLE t1; 2965Table Create Table 2966t1 CREATE TABLE `t1` ( 2967 `a` varchar(10) DEFAULT NULL, 2968 `b` int(11) DEFAULT coercibility(`a`), 2969 `c` int(11) DEFAULT coercibility(`b`) 2970) ENGINE=MyISAM DEFAULT CHARSET=latin1 2971INSERT INTO t1 (a) VALUES ('test'); 2972SELECT * FROM t1; 2973a b c 2974test 2 5 2975DROP TABLE t1; 2976# 2977# String result metadata functions 2978# 2979CREATE TABLE t1 ( 2980a VARCHAR(10) CHARACTER SET latin1, 2981b VARCHAR(20) DEFAULT CHARSET(a), 2982c VARCHAR(20) DEFAULT COLLATION(a) 2983); 2984SHOW CREATE TABLE t1; 2985Table Create Table 2986t1 CREATE TABLE `t1` ( 2987 `a` varchar(10) DEFAULT NULL, 2988 `b` varchar(20) DEFAULT charset(`a`), 2989 `c` varchar(20) DEFAULT collation(`a`) 2990) ENGINE=MyISAM DEFAULT CHARSET=latin1 2991INSERT INTO t1 (a) VALUES ('test'); 2992SELECT * FROM t1; 2993a b c 2994test latin1 latin1_swedish_ci 2995DROP TABLE t1; 2996# 2997# Hash, compression, encode/decode 2998# 2999CREATE TABLE t1 (a VARCHAR(10), b BIGINT DEFAULT CRC32(a), c TEXT DEFAULT MD5(a)); 3000SHOW CREATE TABLE t1; 3001Table Create Table 3002t1 CREATE TABLE `t1` ( 3003 `a` varchar(10) DEFAULT NULL, 3004 `b` bigint(20) DEFAULT crc32(`a`), 3005 `c` text DEFAULT md5(`a`) 3006) ENGINE=MyISAM DEFAULT CHARSET=latin1 3007INSERT INTO t1 (a) VALUES ('a'); 3008SELECT * FROM t1; 3009a b c 3010a 3904355907 0cc175b9c0f1b6a831c399e269772661 3011DROP TABLE t1; 3012CREATE TABLE t1 (a VARCHAR(10), b TEXT DEFAULT TO_BASE64(a), c TEXT DEFAULT FROM_BASE64(b)); 3013SHOW CREATE TABLE t1; 3014Table Create Table 3015t1 CREATE TABLE `t1` ( 3016 `a` varchar(10) DEFAULT NULL, 3017 `b` text DEFAULT to_base64(`a`), 3018 `c` text DEFAULT from_base64(`b`) 3019) ENGINE=MyISAM DEFAULT CHARSET=latin1 3020INSERT INTO t1 (a) VALUES ('aaaabbbb'); 3021SELECT * FROM t1; 3022a b c 3023aaaabbbb YWFhYWJiYmI= aaaabbbb 3024DROP TABLE t1; 3025CREATE TABLE t1 (a VARCHAR(10), b TEXT DEFAULT HEX(a), c TEXT DEFAULT UNHEX(b)); 3026SHOW CREATE TABLE t1; 3027Table Create Table 3028t1 CREATE TABLE `t1` ( 3029 `a` varchar(10) DEFAULT NULL, 3030 `b` text DEFAULT hex(`a`), 3031 `c` text DEFAULT unhex(`b`) 3032) ENGINE=MyISAM DEFAULT CHARSET=latin1 3033INSERT INTO t1 (a) VALUES ('aaaabbbb'); 3034SELECT * FROM t1; 3035a b c 3036aaaabbbb 6161616162626262 aaaabbbb 3037DROP TABLE t1; 3038CREATE TABLE t1 (a VARCHAR(10), b TEXT DEFAULT ENCODE(a,'test'), c TEXT DEFAULT DECODE(b,'test')); 3039SHOW CREATE TABLE t1; 3040Table Create Table 3041t1 CREATE TABLE `t1` ( 3042 `a` varchar(10) DEFAULT NULL, 3043 `b` text DEFAULT encode(`a`,'test'), 3044 `c` text DEFAULT decode(`b`,'test') 3045) ENGINE=MyISAM DEFAULT CHARSET=latin1 3046INSERT INTO t1 (a) VALUES ('aaaabbbb'); 3047SELECT a, HEX(b), c FROM t1; 3048a HEX(b) c 3049aaaabbbb 059AEEE33E4AF848 aaaabbbb 3050DROP TABLE t1; 3051CREATE TABLE t1 (a VARCHAR(30), b TEXT DEFAULT PASSWORD(a)); 3052SHOW CREATE TABLE t1; 3053Table Create Table 3054t1 CREATE TABLE `t1` ( 3055 `a` varchar(30) DEFAULT NULL, 3056 `b` text DEFAULT password(`a`) 3057) ENGINE=MyISAM DEFAULT CHARSET=latin1 3058INSERT INTO t1 (a) VALUES ('notagoodpwd'); 3059SELECT * FROM t1; 3060a b 3061notagoodpwd *3A70EE9FC6594F88CE9E959CD51C5A1C002DC937 3062DROP TABLE t1; 3063CREATE TABLE t1 ( 3064a VARCHAR(30), 3065b BLOB DEFAULT AES_ENCRYPT(a, 'passwd'), 3066c TEXT DEFAULT AES_DECRYPT(b, 'passwd') 3067); 3068SHOW CREATE TABLE t1; 3069Table Create Table 3070t1 CREATE TABLE `t1` ( 3071 `a` varchar(30) DEFAULT NULL, 3072 `b` blob DEFAULT aes_encrypt(`a`,'passwd'), 3073 `c` text DEFAULT aes_decrypt(`b`,'passwd') 3074) ENGINE=MyISAM DEFAULT CHARSET=latin1 3075INSERT INTO t1 (a) VALUES ('test'); 3076SELECT c FROM t1; 3077c 3078test 3079DROP TABLE t1; 3080# 3081# Collations 3082# 3083CREATE TABLE t1 (a VARCHAR(20) CHARACTER SET latin1 DEFAULT CONCAT('�')) CHARACTER SET koi8r COLLATE koi8r_bin; 3084ERROR 22007: Encountered illegal value '�' when converting to koi8r 3085CREATE OR REPLACE TABLE t1 (a char(2) default concat('A') COLLATE utf8mb4_unicode_ci); 3086SHOW CREATE TABLE t1; 3087Table Create Table 3088t1 CREATE TABLE `t1` ( 3089 `a` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT concat('A') 3090) ENGINE=MyISAM DEFAULT CHARSET=latin1 3091DROP TABLE t1; 3092create table t1 (a int default 1, b int default (rand()*0+2), c int); 3093insert t1 (c) values (a); 3094insert t1 (c) values (b); 3095select * from t1; 3096a b c 30971 2 1 30981 2 NULL 3099drop table t1; 3100create table t1 (a int default 1, b int default (1+1), c int); 3101show create table t1; 3102Table Create Table 3103t1 CREATE TABLE `t1` ( 3104 `a` int(11) DEFAULT 1, 3105 `b` int(11) DEFAULT (1 + 1), 3106 `c` int(11) DEFAULT NULL 3107) ENGINE=MyISAM DEFAULT CHARSET=latin1 3108alter table t1 alter a set default (2+3), alter b set default 4, 3109alter c set default (-a); 3110alter table t1 alter a set default 1+2; 3111ERROR 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 3112show create table t1; 3113Table Create Table 3114t1 CREATE TABLE `t1` ( 3115 `a` int(11) DEFAULT (2 + 3), 3116 `b` int(11) DEFAULT 4, 3117 `c` int(11) DEFAULT (-`a`) 3118) ENGINE=MyISAM DEFAULT CHARSET=latin1 3119drop table t1; 3120create table t1 (a int default 5 check (a>10), b int default (5+5), c int as (a+b)); 3121create table t2 as select a, b, c from t1; 3122create table t3 as select max(a), max(b), max(c) from t1; 3123show create table t2; 3124Table Create Table 3125t2 CREATE TABLE `t2` ( 3126 `a` int(11) DEFAULT 5 CHECK (`a` > 10), 3127 `b` int(11) DEFAULT (5 + 5), 3128 `c` int(11) DEFAULT NULL 3129) ENGINE=MyISAM DEFAULT CHARSET=latin1 3130show create table t3; 3131Table Create Table 3132t3 CREATE TABLE `t3` ( 3133 `max(a)` int(11) DEFAULT NULL, 3134 `max(b)` int(11) DEFAULT NULL, 3135 `max(c)` int(11) DEFAULT NULL 3136) ENGINE=MyISAM DEFAULT CHARSET=latin1 3137drop table t1, t2, t3; 3138# MDEV-11359: Implement IGNORE for bulk operation 3139create table t1 (a int primary key default 0, b int default 3); 3140insert into t1 values (1, ignore); 3141insert into t1 values (2, ignore); 3142replace into t1 values (2, ignore); 3143replace into t1 values (3, ignore); 3144replace into t1 values (4, 6); 3145replace into t1 values (5, 7); 3146update t1 set a=6,b=ignore where a=5; 3147insert into t1 values (ignore, ignore); 3148insert into t1 values (ignore, ignore); 3149ERROR 23000: Duplicate entry '0' for key 'PRIMARY' 3150select * from t1 order by a; 3151a b 31520 3 31531 3 31542 3 31553 3 31564 6 31576 7 3158delete from t1 where a < 4; 3159# actually insert default instead of ignoring 3160# (but REPLACE is non standard operator) 3161replace into t1 values (4, ignore); 3162select * from t1 order by a; 3163a b 31644 3 31656 7 3166drop table t1; 3167create table t1 (a int default 100, b int, c varchar(60) default 'x'); 3168load data infile '../../std_data/rpl_loaddata.dat' into table t1 (a, @b) set b=@b+10, c=ignore; 3169select * from t1; 3170a b c 3171NULL 20 x 3172NULL 25 x 3173drop table t1; 3174CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT); 3175CREATE TABLE t2 (a INT); 3176INSERT INTO t2 VALUES (1),(2),(3),(2); 3177INSERT INTO t1 SELECT a FROM t2 ON DUPLICATE KEY UPDATE a=DEFAULT; 3178SELECT * FROM t1 order by a; 3179a 31800 31811 31823 3183truncate table t1; 3184INSERT INTO t1 SELECT a FROM t2 ON DUPLICATE KEY UPDATE a=IGNORE; 3185SELECT * FROM t1 order by a; 3186a 31870 31881 31893 3190DROP TABLE t1,t2; 3191create table t1 (a int primary key default 0, b int default 3); 3192prepare insstmt from "insert into t1 values (?, ?)"; 3193prepare repstmt from "replace into t1 values (?, ?)"; 3194prepare updstmt from "update t1 set a=6,b=? where a=5"; 3195execute insstmt using 1, ignore; 3196execute insstmt using 2, ignore; 3197execute repstmt using 2, ignore; 3198execute repstmt using 3, ignore; 3199execute repstmt using 4, 6; 3200execute repstmt using 5, 7; 3201execute updstmt using ignore; 3202execute insstmt using ignore, ignore; 3203execute insstmt using ignore, ignore; 3204ERROR 23000: Duplicate entry '0' for key 'PRIMARY' 3205select * from t1 order by a; 3206a b 32070 3 32081 3 32092 3 32103 3 32114 6 32126 7 3213delete from t1 where a < 4; 3214execute repstmt using 4, ignore; 3215select * from t1 order by a; 3216a b 32174 3 32186 7 3219drop table t1; 3220# 3221# DEVAULT & PS adoption 3222# 3223CREATE TABLE t1 (a INT DEFAULT 10, b INT DEFAULT NULL); 3224EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?,?)' USING IGNORE, IGNORE; 3225SELECT * FROM t1; 3226a b 322710 NULL 3228UPDATE t1 SET a=20, b=30; 3229SELECT * FROM t1; 3230a b 323120 30 3232EXECUTE IMMEDIATE 'UPDATE t1 SET a=?,b=?' USING IGNORE, IGNORE; 3233SELECT * FROM t1; 3234a b 323520 30 3236DROP TABLE t1; 3237CREATE TABLE t1 (a INT DEFAULT 10); 3238EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?+1)' USING IGNORE; 3239ERROR HY000: Default/ignore value is not supported for such parameter usage 3240EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (CONCAT(?,?))' USING IGNORE, 'test'; 3241ERROR HY000: Default/ignore value is not supported for such parameter usage 3242DROP TABLE t1; 3243CREATE TABLE t1 (a INT DEFAULT 10); 3244INSERT INTO t1 VALUES (20); 3245EXECUTE IMMEDIATE 'UPDATE t1 SET a=?+1' USING IGNORE; 3246ERROR HY000: Default/ignore value is not supported for such parameter usage 3247EXECUTE IMMEDIATE 'UPDATE t1 SET a=CONCAT(?,?)' USING IGNORE, 'test'; 3248ERROR HY000: Default/ignore value is not supported for such parameter usage 3249DROP TABLE t1; 3250EXECUTE IMMEDIATE 'SELECT CAST(? AS SIGNED)' USING IGNORE; 3251ERROR HY000: Default/ignore value is not supported for such parameter usage 3252EXECUTE IMMEDIATE 'SELECT CAST(? AS DOUBLE)' USING IGNORE; 3253ERROR HY000: Default/ignore value is not supported for such parameter usage 3254EXECUTE IMMEDIATE 'SELECT CAST(? AS CHAR)' USING IGNORE; 3255ERROR HY000: Default/ignore value is not supported for such parameter usage 3256EXECUTE IMMEDIATE 'SELECT CAST(? AS DECIMAL(10,1))' USING IGNORE; 3257ERROR HY000: Default/ignore value is not supported for such parameter usage 3258EXECUTE IMMEDIATE 'SELECT CAST(? AS TIME)' USING IGNORE; 3259ERROR HY000: Default/ignore value is not supported for such parameter usage 3260EXECUTE IMMEDIATE 'SELECT CAST(? AS DATE)' USING IGNORE; 3261ERROR HY000: Default/ignore value is not supported for such parameter usage 3262EXECUTE IMMEDIATE 'SELECT CAST(? AS DATETIME)' USING IGNORE; 3263ERROR HY000: Default/ignore value is not supported for such parameter usage 3264EXECUTE IMMEDIATE 'SELECT ?+1' USING IGNORE; 3265ERROR HY000: Default/ignore value is not supported for such parameter usage 3266EXECUTE IMMEDIATE 'SELECT CONCAT(?,?)' USING IGNORE,'test'; 3267ERROR HY000: Default/ignore value is not supported for such parameter usage 3268EXECUTE IMMEDIATE 'SELECT 1 LIMIT ?' USING IGNORE; 3269ERROR HY000: Default/ignore value is not supported for such parameter usage 3270CREATE TABLE t1 (a INT DEFAULT 10); 3271INSERT INTO t1 VALUES (1),(2),(3); 3272EXECUTE IMMEDIATE 'SELECT * FROM t1 LIMIT ?' USING IGNORE; 3273ERROR HY000: Default/ignore value is not supported for such parameter usage 3274DROP TABLE t1; 3275# The output of this query in 'Note' is a syntactically incorrect query. 3276# But as it's never logged, it's ok. It should be human readable only. 3277EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT ?' USING IGNORE; 3278id select_type table type possible_keys key key_len ref rows filtered Extra 32791 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 3280Warnings: 3281Note 1003 select ignore AS `?` 3282CREATE TABLE t1 (a INT); 3283INSERT INTO t1 VALUES (1),(2),(3); 3284EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>?+a' USING DEFAULT,DEFAULT; 3285ERROR HY000: Default/ignore value is not supported for such parameter usage 3286DROP TABLE t1; 3287# 3288# MDEV-11134 Assertion `fixed' failed in Item::const_charset_converter(THD*, CHARSET_INFO*, bool, const char*) 3289# 3290SET NAMES utf8; 3291PREPARE stmt FROM "CREATE OR REPLACE TABLE t1 (c CHAR(8) DEFAULT ?)"; 3292SET @a=''; 3293EXECUTE stmt USING @a; 3294EXECUTE stmt USING @a; 3295SHOW CREATE TABLE t1; 3296Table Create Table 3297t1 CREATE TABLE `t1` ( 3298 `c` char(8) DEFAULT '' 3299) ENGINE=MyISAM DEFAULT CHARSET=latin1 3300DROP TABLE t1; 3301SET @a='A'; 3302EXECUTE stmt USING @a; 3303EXECUTE stmt USING @a; 3304SHOW CREATE TABLE t1; 3305Table Create Table 3306t1 CREATE TABLE `t1` ( 3307 `c` char(8) DEFAULT 'A' 3308) ENGINE=MyISAM DEFAULT CHARSET=latin1 3309DROP TABLE t1; 3310SET @a=_utf8 0xC380; 3311EXECUTE stmt USING @a; 3312EXECUTE stmt USING @a; 3313SHOW CREATE TABLE t1; 3314Table Create Table 3315t1 CREATE TABLE `t1` ( 3316 `c` char(8) DEFAULT 'À' 3317) ENGINE=MyISAM DEFAULT CHARSET=latin1 3318DROP TABLE t1; 3319SET @a=_utf8 0xD18F; 3320EXECUTE stmt USING @a; 3321ERROR 42000: Invalid default value for 'c' 3322EXECUTE stmt USING @a; 3323ERROR 42000: Invalid default value for 'c' 3324DEALLOCATE PREPARE stmt; 3325set sql_mode=ansi_quotes; 3326create table t1 (a int, b int default (a+1)); 3327show create table t1; 3328Table Create Table 3329t1 CREATE TABLE "t1" ( 3330 "a" int(11) DEFAULT NULL, 3331 "b" int(11) DEFAULT ("a" + 1) 3332) ENGINE=MyISAM DEFAULT CHARSET=latin1 3333insert t1 (a) values (10); 3334set sql_mode=''; 3335show create table t1; 3336Table Create Table 3337t1 CREATE TABLE `t1` ( 3338 `a` int(11) DEFAULT NULL, 3339 `b` int(11) DEFAULT (`a` + 1) 3340) ENGINE=MyISAM DEFAULT CHARSET=latin1 3341insert t1 (a) values (20); 3342flush tables; 3343show create table t1; 3344Table Create Table 3345t1 CREATE TABLE `t1` ( 3346 `a` int(11) DEFAULT NULL, 3347 `b` int(11) DEFAULT (`a` + 1) 3348) ENGINE=MyISAM DEFAULT CHARSET=latin1 3349insert t1 (a) values (30); 3350select * from t1; 3351a b 335210 11 335320 21 335430 31 3355drop table t1; 3356set sql_mode=default; 3357create table t1 (a int default b, b int default 4, t text); 3358insert t1 (b, t) values (5, '1 column is omitted'); 3359insert t1 values (default, 5, '2 column gets DEFAULT, keyword'); 3360insert t1 values (default(a), 5, '3 column gets DEFAULT(a), expression'); 3361insert t1 values (default(a)+0, 5, '4 also expression DEFAULT(0)+0'); 3362insert t1 values (b, 5, '5 the value of the DEFAULT(a), that is b'); 3363insert t1 (t,b,a) values ('6 reversed, column gets DEFAULT, keyword', 5, default); 3364insert t1 (t,b,a) values ('7 reversed, column gets DEFAULT(a), expression', 5, default(a)); 3365insert t1 (t,b,a) values ('8 reversed, also expression DEFAULT(0)+0', 5, default(a)+0); 3366insert t1 (t,b,a) values ('9 reversed, the value of the DEFAULT(a), that is b', 5, b); 3367select * from t1 order by t; 3368a b t 33695 5 1 column is omitted 33704 5 2 column gets DEFAULT, keyword 33714 5 3 column gets DEFAULT(a), expression 33724 5 4 also expression DEFAULT(0)+0 33734 5 5 the value of the DEFAULT(a), that is b 33745 5 6 reversed, column gets DEFAULT, keyword 33755 5 7 reversed, column gets DEFAULT(a), expression 33765 5 8 reversed, also expression DEFAULT(0)+0 33775 5 9 reversed, the value of the DEFAULT(a), that is b 3378drop table t1; 3379create table t1 (col1 int default(-(default(col1)))); 3380ERROR 01000: Expression for field `col1` is referring to uninitialized field `col1` 3381create table t1 (col int default (yearweek((exp(710))))); 3382ERROR 22003: DOUBLE value is out of range in 'exp(710)' 3383# 3384# MDEV-13707 Server in ORACLE mode crashes on ALTER with wrong DEFAULT clause 3385# 3386CREATE OR REPLACE TABLE t1(i int); 3387ALTER TABLE t1 ADD b CHAR(255) DEFAULT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa`; 3388ERROR 42S22: Unknown column 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' in 'DEFAULT' 3389DROP TABLE t1; 3390# 3391# MDEV-18278 Misleading error message in error log upon failed table creation 3392# 3393create table t1 (a int as (a)); 3394ERROR 01000: Expression for field `a` is referring to uninitialized field `a` 3395show warnings; 3396Level Code Message 3397Error 4029 Expression for field `a` is referring to uninitialized field `a` 3398# end of 10.2 test 3399# 3400# MDEV-22703 DEFAULT() on a BLOB column can overwrite the default 3401# record, which can cause crashes when accessing already released 3402# memory. 3403# 3404CREATE TEMPORARY TABLE t1 (h POINT DEFAULT ST_GEOMFROMTEXT('Point(1 1)')) ENGINE=InnoDB; 3405INSERT INTO t1 () VALUES (),(); 3406ALTER TABLE t1 FORCE; 3407SELECT DEFAULT(h) FROM t1; 3408SELECT length(DEFAULT(h)) FROM t1; 3409length(DEFAULT(h)) 341025 341125 3412INSERT INTO t1 () VALUES (); 3413drop table t1; 3414# end of 10.3 test 3415