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