1--source include/have_innodb.inc 2 3# 4# test of already fixed bugs 5# 6--disable_warnings 7drop table if exists t1,t2,t3,t4,t5,t6; 8drop database if exists mysqltest; 9 10# 11# Bug 10838 12# Insert causes warnings for no default values and corrupts tables 13# 14set sql_mode=""; 15CREATE TABLE t1 (a varchar(30) binary NOT NULL DEFAULT ' ', 16 b varchar(1) binary NOT NULL DEFAULT ' ', 17 c varchar(4) binary NOT NULL DEFAULT '0000', 18 d tinyblob NULL, 19 e tinyblob NULL, 20 f tinyblob NULL, 21 g tinyblob NULL, 22 h tinyblob NULL, 23 i tinyblob NULL, 24 j tinyblob NULL, 25 k tinyblob NULL, 26 l tinyblob NULL, 27 m tinyblob NULL, 28 n tinyblob NULL, 29 o tinyblob NULL, 30 p tinyblob NULL, 31 q varchar(30) binary NOT NULL DEFAULT ' ', 32 r varchar(30) binary NOT NULL DEFAULT ' ', 33 s tinyblob NULL, 34 t varchar(4) binary NOT NULL DEFAULT ' ', 35 u varchar(1) binary NOT NULL DEFAULT ' ', 36 v varchar(30) binary NOT NULL DEFAULT ' ', 37 w varchar(30) binary NOT NULL DEFAULT ' ', 38 x tinyblob NULL, 39 y varchar(5) binary NOT NULL DEFAULT ' ', 40 z varchar(20) binary NOT NULL DEFAULT ' ', 41 a1 varchar(30) binary NOT NULL DEFAULT ' ', 42 b1 tinyblob NULL) 43ENGINE=InnoDB DEFAULT CHARACTER SET = latin1 COLLATE latin1_bin; 44--enable_warnings 45set sql_mode=default; 46 47INSERT into t1 (b) values ('1'); 48SHOW WARNINGS; 49SELECT * from t1; 50 51CREATE TABLE t2 (a varchar(30) binary NOT NULL DEFAULT ' ', 52 b varchar(1) binary NOT NULL DEFAULT ' ', 53 c varchar(4) binary NOT NULL DEFAULT '0000', 54 d tinyblob NULL, 55 e tinyblob NULL, 56 f tinyblob NULL, 57 g tinyblob NULL, 58 h tinyblob NULL, 59 i tinyblob NULL, 60 j tinyblob NULL, 61 k tinyblob NULL, 62 l tinyblob NULL, 63 m tinyblob NULL, 64 n tinyblob NULL, 65 o tinyblob NULL, 66 p tinyblob NULL, 67 q varchar(30) binary NOT NULL DEFAULT ' ', 68 r varchar(30) binary NOT NULL DEFAULT ' ', 69 s tinyblob NULL, 70 t varchar(4) binary NOT NULL DEFAULT ' ', 71 u varchar(1) binary NOT NULL DEFAULT ' ', 72 v varchar(30) binary NOT NULL DEFAULT ' ', 73 w varchar(30) binary NOT NULL DEFAULT ' ', 74 x tinyblob NULL, 75 y varchar(5) binary NOT NULL DEFAULT ' ', 76 z varchar(20) binary NOT NULL DEFAULT ' ', 77 a1 varchar(30) binary NOT NULL DEFAULT ' ', 78 b1 tinyblob NULL) 79ENGINE=MyISAM DEFAULT CHARACTER SET = latin1 COLLATE latin1_bin; 80 81SHOW CREATE TABLE t2; 82INSERT into t2 (b) values ('1'); 83SHOW WARNINGS; 84SELECT * from t2; 85 86drop table t1; 87drop table t2; 88 89 90# 91# Bug#20691: DATETIME col (NOT NULL, NO DEFAULT) may insert garbage when specifying DEFAULT 92# 93# From the docs: 94# If the column can take NULL as a value, the column is defined with an 95# explicit DEFAULT NULL clause. This is the same as before 5.0.2. 96# 97# If the column cannot take NULL as the value, MySQL defines the column with 98# no explicit DEFAULT clause. For data entry, if an INSERT or REPLACE 99# statement includes no value for the column, MySQL handles the column 100# according to the SQL mode in effect at the time: 101# 102# * If strict SQL mode is not enabled, MySQL sets the column to the 103# implicit default value for the column data type. 104# 105# * If strict mode is enabled, an error occurs for transactional tables and 106# the statement is rolled back. For non-transactional tables, an error 107# occurs, but if this happens for the second or subsequent row of a 108# multiple-row statement, the preceding rows will have been inserted. 109# 110create table bug20691 (i int, d datetime NOT NULL, dn datetime not null default '0000-00-00 00:00:00'); 111insert ignore into bug20691 values (1, DEFAULT, DEFAULT), (1, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (1, DEFAULT, DEFAULT); 112insert ignore into bug20691 (i) values (2); 113desc bug20691; 114insert ignore into bug20691 values (3, DEFAULT, DEFAULT), (3, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (3, DEFAULT, DEFAULT); 115insert ignore into bug20691 (i) values (4); 116insert ignore into bug20691 values (5, DEFAULT, DEFAULT), (5, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (5, DEFAULT, DEFAULT); 117SET sql_mode = 'ALLOW_INVALID_DATES'; 118insert into bug20691 values (6, DEFAULT, DEFAULT), (6, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (6, DEFAULT, DEFAULT); 119SET sql_mode = 'STRICT_ALL_TABLES'; 120--error 1364 121insert into bug20691 values (7, DEFAULT, DEFAULT), (7, '1975-07-10 07:10:03', '1978-01-13 14:08:51'), (7, DEFAULT, DEFAULT); 122select * from bug20691 order by i asc; 123drop table bug20691; 124 125SET sql_mode = ''; 126create table bug20691 ( 127 a set('one', 'two', 'three') not null, 128 b enum('small', 'medium', 'large', 'enormous', 'ellisonego') not null, 129 c time not null, 130 d date not null, 131 e int not null, 132 f long not null, 133 g blob not null, 134 h datetime not null, 135 i decimal not null, 136 x int); 137insert into bug20691 values (2, 3, 5, '0007-01-01', 11, 13, 17, '0019-01-01 00:00:00', 23, 1); 138insert into bug20691 (x) values (2); 139insert into bug20691 values (2, 3, 5, '0007-01-01', 11, 13, 17, '0019-01-01 00:00:00', 23, 3); 140insert into bug20691 values (DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, 4); 141select * from bug20691 order by x asc; 142drop table bug20691; 143 144create table t1 (id int not null); 145insert into t1 values(default); 146 147create view v1 (c) as select id from t1; 148insert into t1 values(default); 149drop view v1; 150drop table t1; 151 152# 153# Bug #39002: crash with 154# INSERT ... SELECT ... ON DUPLICATE KEY UPDATE col=DEFAULT 155# 156 157create table t1 (a int unique); 158create table t2 (b int default 10); 159insert into t1 (a) values (1); 160insert into t2 (b) values (1); 161 162insert into t1 (a) select b from t2 on duplicate key update a=default; 163select * from t1; 164 165insert into t1 (a) values (1); 166insert into t1 (a) select b from t2 on duplicate key update a=default(b); 167select * from t1; 168 169drop table t1, t2; 170 171--echo # End of 5.0 tests 172 173--echo # 174--echo # Start of 10.0 tests 175--echo # 176 177--echo # 178--echo # MDEV-11265 Access defied when CREATE VIIEW v1 AS SELECT DEFAULT(column) FROM t1 179--echo # 180 181CREATE TABLE t1 (a INT DEFAULT 10); 182INSERT INTO t1 VALUES (11); 183CREATE VIEW v1 AS SELECT a AS a FROM t1; 184CREATE VIEW v2 AS SELECT DEFAULT(a) AS a FROM t1; 185CREATE VIEW v3 AS SELECT VALUE(a) AS a FROM t1; 186SELECT * FROM v1; 187SELECT * FROM v2; 188SELECT * FROM v3; 189--error ER_NONUPDATEABLE_COLUMN 190UPDATE v2 SET a=123; 191--error ER_NONUPDATEABLE_COLUMN 192UPDATE v3 SET a=123; 193DROP VIEW v3; 194DROP VIEW v2; 195DROP VIEW v1; 196DROP TABLE t1; 197 198--echo # 199--echo # MDEV-10780 Server crashes in in create_tmp_table 200--echo # 201 202# Note, the problem was not repeatable with a non-fresh connection. 203--connect (con1,localhost,root,,test) 204CREATE TABLE t1 (pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=MyISAM; 205INSERT INTO t1 VALUES (); 206INSERT INTO t1 VALUES (); 207SELECT DISTINCT DEFAULT (pk) FROM t1 GROUP BY RAND() WITH ROLLUP; 208--disconnect con1 209--connection default 210DROP TABLE t1; 211 212--echo # 213--echo # End of 10.0 tests 214--echo # 215 216--echo # 217--echo # Start of 10.1 tests 218--echo # 219 220# Using DEFAULT(col) in WHERE condition 221CREATE TABLE t1 (a INT DEFAULT 100, b INT DEFAULT NULL); 222INSERT INTO t1 VALUES (); 223SELECT * FROM t1 WHERE DEFAULT(a); 224SELECT * FROM t1 WHERE DEFAULT(b); 225DROP TABLE IF EXISTS t1; 226 227 228--echo # 229--echo # End of 10.1 tests 230--echo # 231 232--echo # 233--echo # Start of 10.2 tests 234--echo # 235 236SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30.123456'); 237 238--echo # 239--echo # Check that CURRENT_TIMESTAMP works as before 240--echo # 241 242CREATE or replace TABLE t1 (event_time TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 243SHOW CREATE TABLE t1; 244 245CREATE or replace TABLE t1 (event_time TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(2) ON UPDATE CURRENT_TIMESTAMP); 246SHOW CREATE TABLE t1; 247 248CREATE or replace TABLE t1 (event_time TIMESTAMP(6) NOT NULL DEFAULT SYSDATE(2) ON UPDATE CURRENT_TIMESTAMP); 249SHOW CREATE TABLE t1; 250 251drop table t1; 252 253--echo # 254--echo # Check default expressions 255--echo # 256 257create or replace table t1 (a int default 1, b int default (a+1), c int default (a+b)) engine myisam; 258show create table t1; 259insert into t1 values (); 260insert into t1 (a) values (2); 261insert into t1 (a,b) values (10,20); 262insert into t1 (a,b,c) values (100,200,400); 263select * from t1; 264truncate table t1; 265insert delayed into t1 values (); 266insert delayed into t1 (a) values (2); 267insert delayed into t1 (a,b) values (10,20); 268insert delayed into t1 (a,b,c) values (100,200,400); 269flush tables t1; 270select * from t1; 271 272create or replace table t1 (a int, b blob default (1), c blob default "hello", t text default concat(a,b,c)) engine=myisam; 273show create table t1; 274insert into t1 (a) values (2); 275insert into t1 (a,b) values (10,"test1"); 276insert into t1 (a,b,c) values (10,"test2","test3"); 277insert delayed into t1 (a,b) values (10,"test4"); 278flush tables t1; 279select * from t1; 280drop table t1; 281 282create or replace table t1 (a bigint default uuid_short()); 283insert into t1 values(); 284select a > 0 from t1; 285drop table t1; 286 287create or replace table t1 (param_list int DEFAULT (1+1) NOT NULL); 288--error ER_PARSE_ERROR 289create or replace table t1 (param_list int DEFAULT 1+1 NOT NULL); 290create or replace table t1 (param_list blob DEFAULT "" NOT NULL); 291 292drop table t1; 293 294create table t1 (a int); 295insert into t1 values(-1); 296alter table t1 add b int default 1, add c int default -1, add d int default (1+1), add e timestamp; 297select a,b,c,d,e from t1; 298insert into t1 values(10,10,10,10,0); 299alter 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); 300select a,b,c,d,e,f,g,h from t1; 301show create table t1; 302 303create table t2 like t1; 304show create table t2; 305insert into t2 (a) values (100); 306select a,b,c,d,e,f,g,h from t2; 307drop table t1,t2; 308 309create table t1 (a int default (1----1), b int default - 1, c int default +1, e int default (--1)); 310show create table t1; 311insert into t1 values(); 312insert into t1 values(); 313select * from t1; 314drop table t1; 315 316--echo # 317--echo # Create or replace can delete a table on error 318--echo # 319create table t1 (a int); 320--error ER_EXPRESSION_REFERS_TO_UNINIT_FIELD 321create or replace table t1 (a int default b, b int default a); 322--error ER_NO_SUCH_TABLE 323show create table t1; 324 325--echo # 326--echo # Refering to other columns 327--echo # 328 329create or replace table t1 (a int default 1, b int default a); 330create or replace table t1 (a int default 1, b int as (a)); 331create or replace table t1 (a int default b, b int default 1); 332create or replace table t1 (a int as (b), b int default 1); 333create or replace table t1 (a int as (b), b int default (1+1)); 334create or replace table t1 (a int default 1, b int as (c), c int default (a+1)); 335create or replace table t1 (a int default (1+1), b int as (c), c int default (a+1)); 336create or replace table t1 (a varchar(128) default @@version); 337create or replace table t1 (a int not null, b int as (a)); 338create or replace table t1 (a int not null, b int default (a+1)); 339 340 341--error ER_EXPRESSION_REFERS_TO_UNINIT_FIELD 342create or replace table t1 (a int default a); 343create or replace table t1 (a int default b, b int default (1+1)); 344--error ER_EXPRESSION_REFERS_TO_UNINIT_FIELD 345create or replace table t1 (a int default 1, b int as (c), c int as (a+1)); 346--error ER_EXPRESSION_REFERS_TO_UNINIT_FIELD 347CREATE TABLE t1 (a INT DEFAULT (DEFAULT(a))); 348--error ER_EXPRESSION_REFERS_TO_UNINIT_FIELD 349CREATE TABLE t1 (a INT DEFAULT(DEFAULT(b)), b INT DEFAULT(DEFAULT(a))); 350--error ER_EXPRESSION_REFERS_TO_UNINIT_FIELD 351CREATE TABLE t1 (a INT DEFAULT(DEFAULT(b)) NOT NULL, b INT DEFAULT(DEFAULT(a)) NOT NULL); 352 353--echo # 354--echo # Allow defaults to refer to not default fields 355--echo # 356 357create or replace table t1 (a int default b, b int not null); 358insert into t1 values(); 359insert into t1 (a) values(1); 360insert into t1 (b) values(2); 361insert into t1 (a,b) values(3,4); 362select * from t1; 363drop table t1; 364CREATE OR REPLACE TABLE t1 (a INT DEFAULT @v); drop table t1; 365CREATE TABLE t1 (a INT DEFAULT @v:=1); drop table t1; 366 367--echo # 368--echo # Error handling 369--echo # 370 371--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 372create or replace table t1 (a bigint default xxx()); 373--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 374create or replace table t1 (a bigint default (select (1))); 375--error ER_OPERAND_COLUMNS 376create or replace table t1 (a bigint default (1,2,3)); 377--error ER_OPERAND_COLUMNS 378create or replace table t1 (a bigint default ((1,2,3))); 379--error ER_PARSE_ERROR 380CREATE TABLE t1 (a INT, b INT, c INT DEFAULT a DIV b); 381--error ER_PARSE_ERROR 382CREATE TABLE t1 (a INT, b INT DEFAULT -a); 383 384--echo # 385--echo # Invalid DEFAULT expressions 386--echo # 387 388--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 389CREATE TABLE t1 (a INT DEFAULT ((SELECT 1))); 390 391--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 392CREATE TABLE t1 (a INT DEFAULT (EXISTS (SELECT 1))); 393 394--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 395CREATE TABLE t1 (a INT DEFAULT (1=ANY (SELECT 1))); 396 397--error ER_OPERAND_COLUMNS 398CREATE TABLE t1 (a INT DEFAULT ROW(1,1)); 399 400--error ER_OPERAND_COLUMNS 401CREATE TABLE t1 (a INT DEFAULT (1,1)); 402 403--error ER_OPERAND_COLUMNS 404CREATE TABLE t1 (a INT DEFAULT ((1,1))); 405 406--error ER_PARSE_ERROR,2031 407CREATE TABLE t1 (a INT DEFAULT ?); 408--error ER_PARSE_ERROR,2031 409CREATE TABLE t1 (a INT DEFAULT(?)); 410 411--error ER_EXPRESSION_REFERS_TO_UNINIT_FIELD 412CREATE TABLE t1 (a INT DEFAULT (b), b INT DEFAULT(a)); 413 414--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 415CREATE TABLE t1 (a INT DEFAULT(NAME_CONST('xxx', 'yyy')); 416 417--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 418CREATE TABLE t1 (a INT DEFAULT COUNT(*)); 419 420--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 421CREATE TABLE t1 (a INT DEFAULT COUNT(1)); 422 423--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 424CREATE TABLE t1 (a INT DEFAULT AVG(1)); 425 426--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 427CREATE TABLE t1 (a INT DEFAULT MIN(1)); 428 429--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 430CREATE TABLE t1 (a INT DEFAULT GROUP_CONCAT(1)); 431 432--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 433CREATE TABLE t1 (a INT DEFAULT ROW_NUMBER() OVER ()); 434 435CREATE FUNCTION f1() RETURNS INT RETURN 1; 436--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 437CREATE TABLE t1 (a INT DEFAULT f1()); 438DROP FUNCTION f1; 439 440--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 441CREATE PROCEDURE p1(par INT) CREATE TABLE t1 (a INT DEFAULT par); 442 443--error ER_BAD_FIELD_ERROR 444CREATE TABLE t1 (a INT DEFAULT par); 445 446CREATE PROCEDURE p1() CREATE TABLE t1 (a INT DEFAULT par); 447--error ER_BAD_FIELD_ERROR 448CALL p1; 449DROP PROCEDURE p1; 450 451--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 452CREATE TABLE t1 (a INT DEFAULT VALUE(a)); 453 454 455CREATE TABLE t1 (a INT); 456# "Explicit or implicit commit is not allowed in stored function or trigger 457# because the entire CREATE TABLE is actually not allowed in triggers! 458--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 459CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW CREATE TABLE t2 (a INT DEFAULT NEW.a); 460# This is OK to return Function or expression is not allowed for 'DEFAULT' 461# because CREATE TEMPORARY TABLE is allowed in triggers 462--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 463CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW CREATE TEMPORARY TABLE t2 (a INT DEFAULT NEW.a); 464DROP TABLE t1; 465 466--echo # 467--echo # Prepared statements 468--echo # 469 470PREPARE stmt FROM 'CREATE TABLE t1 (a INT DEFAULT(?))'; 471set @a=1; 472execute stmt using @a; 473show create table t1; 474drop table t1; 475set @a=-1; 476execute stmt using @a; 477show create table t1; 478drop table t1; 479DEALLOCATE PREPARE stmt; 480 481PREPARE stmt FROM 'CREATE TABLE t1 (a INT DEFAULT(?), b INT DEFAULT(?))'; 482set @a=1, @b=2; 483execute stmt using @a,@b; 484show create table t1; 485drop table t1; 486DEALLOCATE PREPARE stmt; 487 488# 489# We can't have an expression for prepared statements 490# 491 492prepare stmt from 'create table t1 (a int default(?+?))'; 493set @a=1; 494execute stmt using @a,@a; 495deallocate prepare stmt; 496show create table t1; 497drop table t1; 498 499--echo # 500--echo # Parenthesized Item_basic_constant 501--echo # 502 503 504# It would be better if SHOW would display PI() rather than '3.141592653589793' 505# The problem is that PI() is declared as a basic constant item and it 506# could cause some problems changing it. 507 508CREATE TABLE t1 ( 509 i01 INT DEFAULT (((1))), 510 i02 INT DEFAULT (((0x3939))), 511 i03 INT DEFAULT (((1.0))), 512 i04 INT DEFAULT (((1e0))), 513 i05 INT DEFAULT (((NULL))), 514 515 f01 DOUBLE DEFAULT (((PI()))), 516 517 s01 VARCHAR(10) DEFAULT (((_latin1'test'))), 518 s02 VARCHAR(10) DEFAULT ((('test'))), 519 s03 VARCHAR(10) DEFAULT (((0x40))), 520 s04 VARCHAR(10) DEFAULT (((X'40'))), 521 s05 VARCHAR(10) DEFAULT (((B'1000000'))), 522 523 d01 TIME DEFAULT (((TIME'10:20:30'))), 524 d02 DATE DEFAULT (((DATE'2001-01-01'))), 525 d03 DATETIME DEFAULT (((TIMESTAMP'2001-01-01 10:20:30'))) 526); 527SHOW CREATE TABLE t1; 528INSERT INTO t1 VALUES (); 529--vertical_results 530SELECT * FROM t1; 531--horizontal_results 532DROP TABLE t1; 533 534 535--echo # 536--echo # COALESCE(Item_basic_constant) 537--echo # 538 539# i02 INT DEFAULT 0x3939 -- gives 14649 (see the previous query), 540# because it treats as a number 541# i02 INT DEFAULT COALESCE(0x3939) -- gives 99, because it converts to string 542# 543# should be at least documented 544 545CREATE TABLE t1 ( 546 i01 INT DEFAULT COALESCE(1), 547 i02 INT DEFAULT COALESCE(0x3939), 548 i03 INT DEFAULT COALESCE(1.0), 549 i04 INT DEFAULT COALESCE(1e0), 550 i05 INT DEFAULT COALESCE(NULL), 551 552 f01 DOUBLE DEFAULT COALESCE(PI()), 553 554 s01 VARCHAR(10) DEFAULT COALESCE(_latin1'test'), 555 s02 VARCHAR(10) DEFAULT COALESCE('test'), 556 s03 VARCHAR(10) DEFAULT COALESCE(0x40), 557 s04 VARCHAR(10) DEFAULT COALESCE(X'40'), 558 s05 VARCHAR(10) DEFAULT COALESCE(B'1000000'), 559 560 d01 TIME DEFAULT COALESCE(TIME'10:20:30'), 561 d02 DATE DEFAULT COALESCE(DATE'2001-01-01'), 562 d03 DATETIME DEFAULT COALESCE(TIMESTAMP'2001-01-01 10:20:30') 563); 564SHOW CREATE TABLE t1; 565INSERT INTO t1 VALUES (); 566--vertical_results 567SELECT * FROM t1; 568--horizontal_results 569DROP TABLE t1; 570 571 572--echo # 573--echo # TINYINT: out of range 574--echo # 575--error ER_INVALID_DEFAULT 576CREATE TABLE t1 (a TINYINT DEFAULT 300 NOT NULL); 577--error ER_INVALID_DEFAULT 578CREATE TABLE t1 (a TINYINT DEFAULT 128 NOT NULL); 579--error ER_INVALID_DEFAULT 580CREATE TABLE t1 (a TINYINT DEFAULT -500 NOT NULL); 581 582--echo # 583--echo # INT: simple numeric expressions 584--echo # 585CREATE TABLE t1 (a INT DEFAULT 1 NOT NULL); 586SHOW CREATE TABLE t1; 587INSERT INTO t1 VALUES (DEFAULT); 588SELECT * FROM t1; 589DROP TABLE t1; 590 591CREATE TABLE t1 (a INT DEFAULT COALESCE(1) NOT NULL); 592SHOW CREATE TABLE t1; 593INSERT INTO t1 VALUES (DEFAULT); 594SELECT * FROM t1; 595DROP TABLE t1; 596 597--echo # 598--echo # INT: simple string expressions 599--echo # 600 601CREATE TABLE t1 (a INT DEFAULT '1' NOT NULL); 602SHOW CREATE TABLE t1; 603INSERT INTO t1 VALUES (DEFAULT); 604SELECT * FROM t1; 605DROP TABLE t1; 606 607CREATE TABLE t1 (a INT DEFAULT CONCAT('1') NOT NULL); 608SHOW CREATE TABLE t1; 609INSERT INTO t1 VALUES (DEFAULT); 610SELECT * FROM t1; 611DROP TABLE t1; 612 613CREATE TABLE t1 (a INT DEFAULT COALESCE('1') NOT NULL); 614SHOW CREATE TABLE t1; 615INSERT INTO t1 VALUES (DEFAULT); 616SELECT * FROM t1; 617DROP TABLE t1; 618 619--echo # 620--echo # INT: string expressions with garbage 621--echo # 622--error ER_INVALID_DEFAULT 623CREATE TABLE t1 (a INT DEFAULT 'x'); 624--error ER_INVALID_DEFAULT 625CREATE TABLE t1 (a INT DEFAULT CONCAT('x')); 626--error ER_INVALID_DEFAULT 627CREATE TABLE t1 (a INT DEFAULT COALESCE('x')); 628--error ER_INVALID_DEFAULT 629CREATE TABLE t1 (a INT DEFAULT (((((COALESCE('x'))))))); 630 631--echo # 632--echo # INT: string expressions with numbers + garbage 633--echo # 634 635--error ER_INVALID_DEFAULT 636CREATE TABLE t1 (a INT DEFAULT '1x'); 637--error ER_INVALID_DEFAULT 638CREATE TABLE t1 (a INT DEFAULT COALESCE('1x')); 639--error ER_INVALID_DEFAULT 640CREATE TABLE t1 (a INT DEFAULT CONCAT('1x')); 641 642--echo # 643--echo # INT: string expressions with numbers + trailing space 644--echo # 645 646CREATE TABLE t1 (a INT DEFAULT '1 '); 647SHOW CREATE TABLE t1; 648INSERT INTO t1 VALUES (DEFAULT); 649SELECT * FROM t1; 650DROP TABLE t1; 651 652# unlike constant, this preserve trailing spaces 653# and sends a note on INSERT. Perhaps CREATE should be rejected 654CREATE TABLE t1 (a INT DEFAULT CONCAT('1 ')); 655SHOW CREATE TABLE t1; 656INSERT INTO t1 VALUES (DEFAULT); 657SELECT * FROM t1; 658DROP TABLE t1; 659 660# unlike constant, this preserve trailing spaces 661# and sends a note on INSERT 662CREATE TABLE t1 (a INT DEFAULT COALESCE('1 ')); 663SHOW CREATE TABLE t1; 664INSERT INTO t1 VALUES (DEFAULT); 665SELECT * FROM t1; 666DROP TABLE t1; 667 668--echo # 669--echo # INT: a HEX value 670--echo # 671CREATE TABLE t1 (a INT DEFAULT 0x61 NOT NULL); 672SHOW CREATE TABLE t1; 673INSERT INTO t1 VALUES (DEFAULT); 674SELECT * FROM t1; 675DROP TABLE t1; 676 677 678--echo # 679--echo # VARCHAR: good defaults 680--echo # 681CREATE TABLE t1 (a VARCHAR(30) DEFAULT 'xxx' NOT NULL); 682SHOW CREATE TABLE t1; 683INSERT INTO t1 VALUES (DEFAULT); 684SELECT * FROM t1; 685DROP TABLE t1; 686 687CREATE TABLE t1 (a VARCHAR(2) DEFAULT 0x41 NOT NULL); 688SHOW CREATE TABLE t1; 689DROP TABLE t1; 690 691CREATE TABLE t1 (a VARCHAR(2) DEFAULT CONCAT(0x41) NOT NULL); 692SHOW CREATE TABLE t1; 693INSERT INTO t1 VALUES (DEFAULT); 694SELECT * FROM t1; 695DROP TABLE t1; 696 697CREATE TABLE t1 (a VARCHAR(2) DEFAULT COALESCE(0x41) NOT NULL); 698SHOW CREATE TABLE t1; 699INSERT INTO t1 VALUES (DEFAULT); 700SELECT * FROM t1; 701DROP TABLE t1; 702 703CREATE TABLE t1 (a VARCHAR(2) DEFAULT CONCAT(_utf8 0x41) NOT NULL); 704SHOW CREATE TABLE t1; 705INSERT INTO t1 VALUES (DEFAULT); 706SELECT * FROM t1; 707DROP TABLE t1; 708 709CREATE TABLE t1 (a VARCHAR(2) DEFAULT CONCAT(_utf8 X'41') NOT NULL); 710SHOW CREATE TABLE t1; 711INSERT INTO t1 VALUES (DEFAULT); 712SELECT * FROM t1; 713DROP TABLE t1; 714 715 716--echo # 717--echo # VARCHAR: Too long default 718--echo # 719--error ER_INVALID_DEFAULT 720CREATE TABLE t1 (a VARCHAR(2) DEFAULT 'xxx' NOT NULL); 721--error ER_INVALID_DEFAULT 722CREATE TABLE t1 (a VARCHAR(2) DEFAULT CONCAT('xxx') NOT NULL); 723 724--echo # 725--echo # VARCHAR: Too long default with non-important data 726--echo # 727--error ER_INVALID_DEFAULT 728CREATE TABLE t1 (a VARCHAR(2) DEFAULT 'xx ' NOT NULL); 729--error ER_INVALID_DEFAULT 730CREATE TABLE t1 (a VARCHAR(2) DEFAULT CONCAT('xx ') NOT NULL); 731 732--echo # 733--echo # VARCHAR: conversion failures 734--echo # 735 736# DEFAULT with a Cyrillic letter for a Latin1 column 737--error ER_INVALID_DEFAULT 738CREATE TABLE t1 (a VARCHAR(2) CHARACTER SET latin1 DEFAULT _utf8 X'D18F' NOT NULL); 739--error ER_INVALID_DEFAULT 740CREATE TABLE t1 (a VARCHAR(2) CHARACTER SET latin1 DEFAULT CONCAT(_utf8 X'D18F') NOT NULL); 741--error ER_INVALID_DEFAULT 742CREATE TABLE t1 (a VARCHAR(2) CHARACTER SET latin1 DEFAULT CONCAT(_utf8 0xD18F) NOT NULL); 743 744--echo # 745--echo # Field as a default value 746--echo # 747 748CREATE TABLE t1 (a INT, b INT DEFAULT (a)); 749SHOW CREATE TABLE t1; 750INSERT INTO t1 VALUES (1, 1); 751INSERT INTO t1 VALUES (DEFAULT, DEFAULT); 752INSERT INTO t1 VALUES (1, DEFAULT); 753INSERT INTO t1 VALUES (DEFAULT, 1); 754SELECT * FROM t1; 755DROP TABLE t1; 756 757--echo # 758--echo # Function DEFAULT(field) 759--echo # 760 761CREATE TABLE t1 (a INT DEFAULT(DEFAULT(b)), b INT DEFAULT 1); 762SHOW CREATE TABLE t1; 763INSERT INTO t1 VALUES (DEFAULT, DEFAULT); 764SELECT * FROM t1; 765DROP TABLE t1; 766 767CREATE TABLE t1 (a INT DEFAULT 1, b INT DEFAULT(DEFAULT(a))); 768SHOW CREATE TABLE t1; 769INSERT INTO t1 VALUES (DEFAULT, DEFAULT); 770SELECT * FROM t1; 771DROP TABLE t1; 772 773--echo # 774--echo # SQL Standard <datetime value function> as a <default option> 775--echo # 776 777CREATE TABLE t1 (a DATETIME DEFAULT CURRENT_TIMESTAMP); 778SHOW CREATE TABLE t1; 779DROP TABLE t1; 780 781CREATE TABLE t1 (a TIME DEFAULT CURRENT_TIME); 782SHOW CREATE TABLE t1; 783DROP TABLE t1; 784 785CREATE TABLE t1 (a DATE DEFAULT CURRENT_DATE); 786SHOW CREATE TABLE t1; 787DROP TABLE t1; 788 789--echo # 790--echo # DECIMAL + CURRENT_TIMESTAMP, no truncation 791--echo # 792CREATE TABLE t1 (a DECIMAL(30,6) DEFAULT CURRENT_TIMESTAMP(6)); 793SHOW CREATE TABLE t1; 794INSERT INTO t1 VALUES (); 795SELECT * FROM t1; 796DROP TABLE t1; 797 798CREATE TABLE t1 (a DECIMAL(30,6) DEFAULT COALESCE(CURRENT_TIMESTAMP(6))); 799SHOW CREATE TABLE t1; 800INSERT INTO t1 VALUES(); 801INSERT IGNORE INTO t1 VALUES(); 802SET sql_mode = 'STRICT_ALL_TABLES'; 803INSERT INTO t1 VALUES(); 804SET sql_mode = DEFAULT; 805DROP TABLE t1; 806 807--echo # 808--echo # DECIMAL + CURRENT_TIME, no truncation 809--echo # 810CREATE TABLE t1 (a DECIMAL(30,6) DEFAULT COALESCE(CURRENT_TIME(6))); 811SHOW CREATE TABLE t1; 812INSERT IGNORE INTO t1 VALUES(); 813DROP TABLE t1; 814 815--echo # 816--echo # DECIMAL + CURRENT_DATE, no truncation 817--echo # 818CREATE TABLE t1 (a DECIMAL(30,6) DEFAULT COALESCE(CURRENT_DATE)); 819SHOW CREATE TABLE t1; 820INSERT IGNORE INTO t1 VALUES(); 821DROP TABLE t1; 822 823 824--echo # 825--echo # COALESCE for SQL Standard <datetime value function> 826--echo # 827 828CREATE TABLE t1 (a TIMESTAMP DEFAULT COALESCE(CURRENT_TIMESTAMP)); 829SHOW CREATE TABLE t1; 830DROP TABLE t1; 831 832CREATE TABLE t1 (a DATE DEFAULT COALESCE(CURRENT_DATE)); 833SHOW CREATE TABLE t1; 834DROP TABLE t1; 835 836CREATE TABLE t1 (a TIME DEFAULT COALESCE(CURRENT_TIME)); 837SHOW CREATE TABLE t1; 838DROP TABLE t1; 839 840CREATE TABLE t1 ( 841 a TIMESTAMP DEFAULT CURRENT_TIMESTAMP(6), 842 b TIMESTAMP DEFAULT COALESCE(CURRENT_TIMESTAMP(6)) 843); 844SHOW CREATE TABLE t1; 845INSERT INTO t1 VALUES (); 846SELECT CURRENT_TIMESTAMP(6); 847SELECT * FROM t1; 848DROP TABLE t1; 849 850CREATE TABLE t1 ( 851 a DECIMAL(30,0) DEFAULT CURRENT_TIMESTAMP(6), 852 b DECIMAL(30,0) DEFAULT COALESCE(CURRENT_TIMESTAMP(6)) 853); 854SHOW CREATE TABLE t1; 855INSERT IGNORE INTO t1 VALUES (); 856SELECT * FROM t1; 857DROP TABLE t1; 858 859--echo # 860--echo # Check DEFAULT() function 861--echo # 862 863CREATE TABLE `t1` (`a` int(11) DEFAULT (3+3),`b` int(11) DEFAULT '1000'); 864SHOW CREATE TABLE t1; 865insert into t1 values (1,1),(2,2); 866insert into t1 values (default,default); 867select * from t1; 868select default(a),b from t1; 869select a,default(b) from t1; 870drop table t1; 871 872 873--echo # 874--echo # Real functions 875--echo # 876 877CREATE TABLE t1 (a DECIMAL(10,1), b DOUBLE DEFAULT CAST(a AS DOUBLE)); 878SHOW CREATE TABLE t1; 879INSERT INTO t1 VALUES (10.1, DEFAULT); 880SELECT * FROM t1; 881DROP TABLE t1; 882 883CREATE TABLE t1 (a DOUBLE, b DOUBLE DEFAULT EXP(a), c DOUBLE DEFAULT LOG(b), d DOUBLE DEFAULT LOG(4, b)); 884SHOW CREATE TABLE t1; 885INSERT INTO t1 VALUES (2, DEFAULT, DEFAULT, DEFAULT); 886SELECT * FROM t1; 887DROP TABLE t1; 888 889CREATE TABLE t1 (a INT, b DOUBLE DEFAULT LOG2(a), c DOUBLE DEFAULT LOG10(a)); 890SHOW CREATE TABLE t1; 891INSERT INTO t1 VALUES (4, DEFAULT, DEFAULT); 892INSERT INTO t1 VALUES (100, DEFAULT, DEFAULT); 893SELECT * FROM t1; 894DROP TABLE t1; 895 896 897CREATE TABLE t1 (a DOUBLE, b DOUBLE DEFAULT SQRT(a), c DOUBLE DEFAULT POW(a,3)); 898SHOW CREATE TABLE t1; 899INSERT INTO t1 VALUES (4, DEFAULT, DEFAULT); 900SELECT * FROM t1; 901DROP TABLE t1; 902 903CREATE TABLE t1 (a DOUBLE, b DOUBLE DEFAULT ACOS(a), c DOUBLE DEFAULT ASIN(a), d DOUBLE DEFAULT ATAN(a)); 904SHOW CREATE TABLE t1; 905INSERT INTO t1 VALUES (1, DEFAULT, DEFAULT, DEFAULT); 906SELECT a, b/PI(), c/PI(), d/PI() FROM t1; 907DROP TABLE t1; 908 909CREATE TABLE t1 (a DOUBLE, b DOUBLE DEFAULT COS(a), c DOUBLE DEFAULT SIN(a), d DOUBLE DEFAULT TAN(a), e DOUBLE DEFAULT COT(a)); 910SHOW CREATE TABLE t1; 911INSERT INTO t1 (a) VALUES (PI()/3); 912SELECT ROUND(a,3), ROUND(b,3), ROUND(c,3), ROUND(d,3), ROUND(e,3) FROM t1; 913DROP TABLE t1; 914 915CREATE TABLE t1 (a DOUBLE DEFAULT RAND()); 916SHOW CREATE TABLE t1; 917INSERT INTO t1 VALUES (DEFAULT); 918DROP TABLE t1; 919 920CREATE TABLE t1 (a DOUBLE, b DOUBLE DEFAULT DEGREES(a), c DOUBLE DEFAULT RADIANS(b)); 921SHOW CREATE TABLE t1; 922INSERT INTO t1 VALUES (PI(), DEFAULT, DEFAULT); 923SELECT * FROM t1; 924DROP TABLE t1; 925 926--echo # 927--echo # INT result functions 928--echo # 929 930CREATE TABLE t1 (a INT, b INT DEFAULT INTERVAL(a, 10, 20, 30, 40)); 931SHOW CREATE TABLE t1; 932INSERT INTO t1 (a) VALUES (34); 933SELECT * FROM t1; 934DROP TABLE t1; 935 936CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a DIV b)); 937SHOW CREATE TABLE t1; 938INSERT INTO t1 (a, b) VALUES (13, 3); 939SELECT * FROM t1; 940DROP TABLE t1; 941 942CREATE TABLE t1 (a INT, b INT DEFAULT SIGN(a)); 943SHOW CREATE TABLE t1; 944INSERT INTO t1 (a) VALUES (-10),(0), (10); 945SELECT * FROM t1; 946DROP TABLE t1; 947 948CREATE TABLE t1 (a VARCHAR(30), b INT DEFAULT FIELD(a, 'Hej', 'ej', 'Heja', 'hej', 'foo')); 949SHOW CREATE TABLE t1; 950INSERT INTO t1 (a) VALUES ('ej'); 951SELECT * FROM t1; 952DROP TABLE t1; 953 954CREATE TABLE t1 (a VARCHAR(30), b INT DEFAULT FIND_IN_SET(a, 'Hej,ej,Heja,hej,foo')); 955SHOW CREATE TABLE t1; 956INSERT INTO t1 (a) VALUES ('ej'); 957SELECT * FROM t1; 958DROP TABLE t1; 959 960CREATE TABLE t1 (a VARCHAR(30), b INT DEFAULT ASCII(a), c INT DEFAULT ORD(a)); 961SHOW CREATE TABLE t1; 962INSERT INTO t1 (a) VALUES ('a'); 963SELECT * FROM t1; 964DROP TABLE t1; 965 966CREATE TABLE t1 (a TEXT DEFAULT UUID_SHORT()); 967SHOW CREATE TABLE t1; 968INSERT INTO t1 VALUES (); 969SELECT a>0 FROM t1; 970DROP TABLE t1; 971 972--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 973CREATE TABLE t1 (a INT DEFAULT BENCHMARK(1,1)); 974 975--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 976CREATE TABLE t1 (a INT DEFAULT GET_LOCK('a',1)); 977 978--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 979CREATE TABLE t1 (a INT DEFAULT RELEASE_LOCK('a')); 980 981--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 982CREATE TABLE t1 (a INT DEFAULT IS_USED_LOCK('a')); 983 984--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 985CREATE TABLE t1 (a INT DEFAULT IS_FREE_LOCK('a')); 986 987--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 988CREATE TABLE t1 (a INT DEFAULT SLEEP(1)); 989 990--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 991CREATE TABLE t1 (a INT DEFAULT ROW_COUNT()); 992 993--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 994CREATE TABLE t1 (a INT DEFAULT FOUND_ROWS()); 995 996--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 997CREATE TABLE t1 (a INT DEFAULT MASTER_POS_WAIT('test',100)); 998 999--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 1000CREATE TABLE t1 (a INT DEFAULT MASTER_GTID_WAIT('test')); 1001 1002--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 1003CREATE TABLE t1 (a VARCHAR(30), b DOUBLE DEFAULT MATCH (a) AGAINST('bbbb' IN BOOLEAN MODE)); 1004 1005--echo # 1006--echo # Temporal functions 1007--echo # 1008 1009--echo # Item_temporal_hybrid_func 1010 1011CREATE TABLE t1 (a DATE, b INT, c DATE DEFAULT DATE_ADD(a, INTERVAL b DAY)); 1012SHOW CREATE TABLE t1; 1013INSERT INTO t1 VALUES ('2001-01-01', 30, DEFAULT); 1014SELECT * FROM t1; 1015DROP TABLE t1; 1016 1017CREATE TABLE t1 (a DATE, b TIME, c DATETIME DEFAULT ADDTIME(a, b)); 1018SHOW CREATE TABLE t1; 1019INSERT INTO t1 VALUES ('2001-01-01', '10:20:30', DEFAULT); 1020SELECT * FROM t1; 1021DROP TABLE t1; 1022 1023CREATE TABLE t1 (a VARCHAR(32), b VARCHAR(32), c DATE DEFAULT STR_TO_DATE(a,b)); 1024SHOW CREATE TABLE t1; 1025INSERT INTO t1 VALUES ('01,5,2013','%d,%m,%Y', DEFAULT); 1026SELECT * FROM t1; 1027DROP TABLE t1; 1028 1029--echo # Item_datefunc 1030 1031SET time_zone='-10:00'; 1032SET timestamp=UNIX_TIMESTAMP('2001-01-01 23:59:59'); 1033CREATE TABLE t1 (a DATE DEFAULT CURDATE(), b DATE DEFAULT UTC_DATE()); 1034SHOW CREATE TABLE t1; 1035INSERT INTO t1 VALUES (); 1036SELECT * FROM t1; 1037DROP TABLE t1; 1038SET time_zone=DEFAULT, timestamp= DEFAULT; 1039 1040CREATE TABLE t1 (a INT, b DATE DEFAULT FROM_DAYS(a)); 1041SHOW CREATE TABLE t1; 1042INSERT INTO t1 VALUES (730669, DEFAULT); 1043SELECT * FROM t1; 1044DROP TABLE t1; 1045 1046CREATE TABLE t1 (a DATE, b DATE DEFAULT LAST_DAY(a)); 1047SHOW CREATE TABLE t1; 1048INSERT INTO t1 VALUES ('2003-02-05', DEFAULT); 1049SELECT * FROM t1; 1050DROP TABLE t1; 1051 1052CREATE TABLE t1 (yy INT, yd INT, d DATE DEFAULT MAKEDATE(yy, yd)); 1053SHOW CREATE TABLE t1; 1054INSERT INTO t1 VALUES (2011,32,DEFAULT); 1055SELECT * FROM t1; 1056DROP TABLE t1; 1057 1058--echo # Item_timefunc 1059 1060SET time_zone='-10:00'; 1061SET timestamp=UNIX_TIMESTAMP('2001-01-01 23:59:59'); 1062CREATE TABLE t1 (a TIME DEFAULT CURTIME(), b TIME DEFAULT UTC_TIME()); 1063SHOW CREATE TABLE t1; 1064INSERT INTO t1 VALUES (); 1065SELECT * FROM t1; 1066DROP TABLE t1; 1067SET time_zone=DEFAULT, timestamp= DEFAULT; 1068 1069CREATE TABLE t1 (a INT, b TIME DEFAULT SEC_TO_TIME(a)); 1070SHOW CREATE TABLE t1; 1071INSERT INTO t1 VALUES (2378, DEFAULT); 1072SELECT * FROM t1; 1073DROP TABLE t1; 1074 1075CREATE TABLE t1 (a DATETIME, b DATETIME, c TIME DEFAULT TIMEDIFF(a,b)); 1076SHOW CREATE TABLE t1; 1077INSERT INTO t1 VALUES ('2000:01:01 00:00:00', '2000:01:02 10:20:30', DEFAULT); 1078SELECT * FROM t1; 1079DROP TABLE t1; 1080 1081CREATE TABLE t1 (hh INT, mm INT, ss INT, t TIME DEFAULT MAKETIME(hh,mm,ss)); 1082SHOW CREATE TABLE t1; 1083INSERT INTO t1 VALUES (10,20,30,DEFAULT); 1084SELECT * FROM t1; 1085DROP TABLE t1; 1086 1087 1088--echo # Item_datetimefunc 1089 1090SET time_zone='-10:00'; 1091SET timestamp=UNIX_TIMESTAMP('2001-01-01 23:59:59'); 1092CREATE TABLE t1 (a TIMESTAMP DEFAULT NOW(), b TIMESTAMP DEFAULT UTC_TIMESTAMP()); 1093SHOW CREATE TABLE t1; 1094INSERT INTO t1 VALUES (); 1095SELECT * FROM t1; 1096DROP TABLE t1; 1097SET time_zone=DEFAULT, timestamp= DEFAULT; 1098 1099# SYSDATE is evaluated during get_date() rather than fix_fields. 1100CREATE TABLE t1 (a TIMESTAMP(6) DEFAULT SYSDATE(6), s INT, b TIMESTAMP(6) DEFAULT SYSDATE(6)); 1101SHOW CREATE TABLE t1; 1102INSERT INTO t1 VALUES (DEFAULT(a), SLEEP(0.1), DEFAULT(b)); 1103SELECT b>a FROM t1; 1104DROP TABLE t1; 1105 1106SET time_zone='+00:00'; 1107CREATE TABLE t1 (a INT, b TIMESTAMP DEFAULT FROM_UNIXTIME(a)); 1108SHOW CREATE TABLE t1; 1109INSERT INTO t1 VALUES (1447430881, DEFAULT); 1110SELECT * FROM t1; 1111DROP TABLE t1; 1112SET time_zone=DEFAULT; 1113 1114CREATE TABLE t1 (a TIMESTAMP, b TIMESTAMP DEFAULT CONVERT_TZ(a, '-10:00', '+10:00')); 1115SHOW CREATE TABLE t1; 1116INSERT INTO t1 VALUES ('2001-01-01 10:20:30', DEFAULT); 1117SELECT * FROM t1; 1118DROP TABLE t1; 1119 1120--echo # Item_temporal_typecast 1121CREATE TABLE t1 (a INT, b DATE DEFAULT CAST(a AS DATE)); 1122SHOW CREATE TABLE t1; 1123INSERT INTO t1 VALUES (20010203, DEFAULT); 1124SELECT * FROM t1; 1125DROP TABLE t1; 1126 1127CREATE TABLE t1 (a INT, b TIME DEFAULT CAST(a AS TIME)); 1128SHOW CREATE TABLE t1; 1129INSERT INTO t1 VALUES (102030, DEFAULT); 1130SELECT * FROM t1; 1131DROP TABLE t1; 1132 1133CREATE TABLE t1 (a BIGINT, b DATETIME DEFAULT CAST(a AS DATETIME)); 1134SHOW CREATE TABLE t1; 1135INSERT INTO t1 VALUES (20010203102030, DEFAULT); 1136SELECT * FROM t1; 1137DROP TABLE t1; 1138 1139 1140--echo # 1141--echo # Functions with temporal input 1142--echo # 1143 1144CREATE TABLE t1 (a INT, b INT, c INT DEFAULT PERIOD_ADD(a,b)); 1145SHOW CREATE TABLE t1; 1146INSERT INTO t1 (a,b) VALUES (200801, 2); 1147SELECT * FROM t1; 1148DROP TABLE t1; 1149 1150CREATE TABLE t1 (a INT, b INT, c INT DEFAULT PERIOD_DIFF(a,b)); 1151SHOW CREATE TABLE t1; 1152INSERT INTO t1 (a,b) VALUES (200802, 200703); 1153SELECT * FROM t1; 1154DROP TABLE t1; 1155 1156CREATE TABLE t1 (a INT, b INT DEFAULT TO_DAYS(a)); 1157SHOW CREATE TABLE t1; 1158INSERT INTO t1 (a) VALUES (950501); 1159SELECT * FROM t1; 1160DROP TABLE t1; 1161CREATE TABLE t1 (a DATE, b INT DEFAULT TO_DAYS(a)); 1162SHOW CREATE TABLE t1; 1163INSERT INTO t1 (a) VALUES ('2007-10-07'); 1164SELECT * FROM t1; 1165DROP TABLE t1; 1166 1167CREATE TABLE t1 (a INT, b BIGINT DEFAULT TO_SECONDS(a)); 1168SHOW CREATE TABLE t1; 1169INSERT INTO t1 (a) VALUES (950501); 1170SELECT * FROM t1; 1171DROP TABLE t1; 1172CREATE TABLE t1 (a DATE, b BIGINT DEFAULT TO_SECONDS(a)); 1173SHOW CREATE TABLE t1; 1174INSERT INTO t1 (a) VALUES ('2009-11-29'); 1175SELECT * FROM t1; 1176DROP TABLE t1; 1177CREATE TABLE t1 (a DATETIME, b BIGINT DEFAULT TO_SECONDS(a)); 1178SHOW CREATE TABLE t1; 1179INSERT INTO t1 (a) VALUES ('2009-11-29 13:43:32'); 1180SELECT * FROM t1; 1181DROP TABLE t1; 1182 1183CREATE TABLE t1 (a DATE, b BIGINT DEFAULT DAYOFMONTH(a)); 1184SHOW CREATE TABLE t1; 1185INSERT INTO t1 (a) VALUES ('2007-02-03'); 1186SELECT * FROM t1; 1187DROP TABLE t1; 1188 1189CREATE TABLE t1 (a DATE, b BIGINT DEFAULT DAYOFWEEK(a)); 1190SHOW CREATE TABLE t1; 1191INSERT INTO t1 (a) VALUES ('2007-02-03'); 1192SELECT * FROM t1; 1193DROP TABLE t1; 1194 1195CREATE TABLE t1 (a DATE, b BIGINT DEFAULT DAYOFYEAR(a)); 1196SHOW CREATE TABLE t1; 1197INSERT INTO t1 (a) VALUES ('2007-02-03'); 1198SELECT * FROM t1; 1199DROP TABLE t1; 1200 1201CREATE TABLE t1 (a TIME, b INT DEFAULT HOUR(a)); 1202SHOW CREATE TABLE t1; 1203INSERT INTO t1 (a) VALUES ('10:05:03'); 1204SELECT * FROM t1; 1205DROP TABLE t1; 1206 1207CREATE TABLE t1 (a TIME, b INT DEFAULT MINUTE(a)); 1208SHOW CREATE TABLE t1; 1209INSERT INTO t1 (a) VALUES ('10:05:03'); 1210SELECT * FROM t1; 1211DROP TABLE t1; 1212 1213CREATE TABLE t1 (a TIME, b INT DEFAULT SECOND(a)); 1214SHOW CREATE TABLE t1; 1215INSERT INTO t1 (a) VALUES ('10:05:03'); 1216SELECT * FROM t1; 1217DROP TABLE t1; 1218 1219CREATE TABLE t1 (a DATETIME(6), b INT DEFAULT MICROSECOND(a)); 1220SHOW CREATE TABLE t1; 1221INSERT INTO t1 (a) VALUES ('2009-12-31 23:59:59.000010'); 1222SELECT * FROM t1; 1223DROP TABLE t1; 1224 1225CREATE TABLE t1 (a DATE, b INT DEFAULT YEAR(a)); 1226SHOW CREATE TABLE t1; 1227INSERT INTO t1 (a) VALUES ('1987-01-01'); 1228SELECT * FROM t1; 1229DROP TABLE t1; 1230 1231CREATE TABLE t1 (a DATE, b INT DEFAULT MONTH(a)); 1232SHOW CREATE TABLE t1; 1233INSERT INTO t1 (a) VALUES ('1987-01-01'); 1234SELECT * FROM t1; 1235DROP TABLE t1; 1236 1237CREATE TABLE t1 (a DATE, b INT DEFAULT WEEK(a)); 1238SHOW CREATE TABLE t1; 1239INSERT INTO t1 (a) VALUES ('1987-02-01'); 1240SELECT * FROM t1; 1241DROP TABLE t1; 1242 1243CREATE TABLE t1 (a DATE, b INT DEFAULT YEARWEEK(a)); 1244SHOW CREATE TABLE t1; 1245INSERT INTO t1 (a) VALUES ('2000-01-01'); 1246SELECT * FROM t1; 1247DROP TABLE t1; 1248 1249CREATE TABLE t1 (a DATE, b INT DEFAULT QUARTER(a)); 1250SHOW CREATE TABLE t1; 1251INSERT INTO t1 (a) VALUES ('2008-04-01'); 1252SELECT * FROM t1; 1253DROP TABLE t1; 1254 1255CREATE TABLE t1 (a DATE, b INT DEFAULT EXTRACT(YEAR FROM a)); 1256SHOW CREATE TABLE t1; 1257INSERT INTO t1 (a) VALUES ('2009-07-02'); 1258SELECT * FROM t1; 1259DROP TABLE t1; 1260CREATE TABLE t1 (a DATETIME, b INT DEFAULT EXTRACT(YEAR_MONTH FROM a)); 1261SHOW CREATE TABLE t1; 1262INSERT INTO t1 (a) VALUES ('2009-07-02 01:02:03'); 1263SELECT * FROM t1; 1264DROP TABLE t1; 1265CREATE TABLE t1 (a DATETIME, b INT DEFAULT EXTRACT(DAY_MINUTE FROM a)); 1266SHOW CREATE TABLE t1; 1267INSERT INTO t1 (a) VALUES ('2009-07-02 01:02:03'); 1268SELECT * FROM t1; 1269DROP TABLE t1; 1270CREATE TABLE t1 (a DATETIME(6), b INT DEFAULT EXTRACT(MICROSECOND FROM a)); 1271SHOW CREATE TABLE t1; 1272INSERT INTO t1 (a) VALUES ('2009-07-02 01:02:03.000123'); 1273SELECT * FROM t1; 1274DROP TABLE t1; 1275 1276CREATE TABLE t1 (a DATE, b DATE, c INT DEFAULT TIMESTAMPDIFF(MONTH,a,b)); 1277SHOW CREATE TABLE t1; 1278INSERT INTO t1 (a,b) VALUES ('2003-02-01','2003-05-01'); 1279SELECT * FROM t1; 1280DROP TABLE t1; 1281CREATE TABLE t1 (a DATE, b DATE, c INT DEFAULT TIMESTAMPDIFF(YEAR,a,b)); 1282SHOW CREATE TABLE t1; 1283INSERT INTO t1 (a,b) VALUES ('2002-05-01','2001-01-01'); 1284SELECT * FROM t1; 1285DROP TABLE t1; 1286CREATE TABLE t1 (a DATE, b DATETIME, c INT DEFAULT TIMESTAMPDIFF(MINUTE,a,b)); 1287SHOW CREATE TABLE t1; 1288INSERT INTO t1 (a,b) VALUES ('2003-02-01','2003-05-01 12:05:55'); 1289SELECT * FROM t1; 1290DROP TABLE t1; 1291 1292# 1293# MDEV-10355 Weird error message upon CREATE TABLE with DEFAULT 1294# 1295# Column default value expression that includes a temporal function 1296# 1297CREATE OR REPLACE TABLE t1 ( col INT DEFAULT ( 1 LIKE ( NOW() BETWEEN '2000-01-01' AND '2012-12-12' ) ) ); 1298SHOW CREATE TABLE t1; 1299SET timestamp = UNIX_TIMESTAMP( '2004-04-04' ); 1300INSERT INTO t1 VALUES( DEFAULT ); 1301SET timestamp = DEFAULT; 1302INSERT INTO t1 VALUES( DEFAULT ); 1303SELECT * FROM t1; 1304DROP TABLE t1; 1305 1306 1307--echo # 1308--echo # Hybrid type functions 1309--echo # 1310 1311CREATE TABLE t1 (a INT, b INT, c INT DEFAULT COALESCE(a,b)); 1312SHOW CREATE TABLE t1; 1313INSERT INTO t1 VALUES (NULL, 1, DEFAULT); 1314SELECT * FROM t1; 1315DROP TABLE t1; 1316 1317 1318CREATE TABLE t1 (a INT, b INT, c INT DEFAULT IFNULL(a,b)); 1319SHOW CREATE TABLE t1; 1320INSERT INTO t1 VALUES (NULL, 2, DEFAULT); 1321INSERT INTO t1 VALUES (1, 2, DEFAULT); 1322SELECT * FROM t1; 1323DROP TABLE t1; 1324 1325 1326CREATE TABLE t1 (a INT, b INT, c INT DEFAULT NULLIF(a,b)); 1327SHOW CREATE TABLE t1; 1328INSERT INTO t1 VALUES (1, 1, DEFAULT); 1329INSERT INTO t1 VALUES (1, 2, DEFAULT); 1330SELECT * FROM t1; 1331DROP TABLE t1; 1332 1333CREATE TABLE t1 (a INT, b INT, c INT DEFAULT IF(a,b,2)); 1334SHOW CREATE TABLE t1; 1335INSERT INTO t1 VALUES (0, 1, DEFAULT); 1336INSERT INTO t1 VALUES (1, 1, DEFAULT); 1337SELECT * FROM t1; 1338DROP TABLE t1; 1339 1340CREATE TABLE t1 (a INT, b INT, c INT DEFAULT CASE WHEN a THEN b ELSE 2 END); 1341SHOW CREATE TABLE t1; 1342INSERT INTO t1 VALUES (0, 1, DEFAULT); 1343INSERT INTO t1 VALUES (1, 1, DEFAULT); 1344SELECT * FROM t1; 1345DROP TABLE t1; 1346 1347CREATE TABLE t1 (a INT, b INT DEFAULT (-a)); 1348SHOW CREATE TABLE t1; 1349SHOW CREATE TABLE t1; 1350INSERT INTO t1 VALUES (10, DEFAULT); 1351SELECT * FROM t1; 1352DROP TABLE t1; 1353 1354CREATE TABLE t1 (a INT, b INT DEFAULT ABS(a)); 1355SHOW CREATE TABLE t1; 1356INSERT INTO t1 VALUES (-10, DEFAULT); 1357SELECT * FROM t1; 1358DROP TABLE t1; 1359 1360CREATE TABLE t1 (a DOUBLE, b INT DEFAULT CEILING(a), c INT DEFAULT FLOOR(a), d INT DEFAULT ROUND(a)); 1361SHOW CREATE TABLE t1; 1362INSERT INTO t1 VALUES (1.5, DEFAULT, DEFAULT, DEFAULT); 1363INSERT INTO t1 VALUES (-1.5, DEFAULT, DEFAULT, DEFAULT); 1364SELECT * FROM t1; 1365DROP TABLE t1; 1366 1367CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a+b), d INT DEFAULT (a-b)); 1368SHOW CREATE TABLE t1; 1369INSERT INTO t1 VALUES (2, 1, DEFAULT, DEFAULT); 1370SELECT * FROM t1; 1371DROP TABLE t1; 1372 1373CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a*b), d INT DEFAULT (a/b), e INT DEFAULT (a MOD b)); 1374SHOW CREATE TABLE t1; 1375SHOW CREATE TABLE t1; 1376INSERT INTO t1 VALUES (7, 3, DEFAULT, DEFAULT, DEFAULT); 1377SELECT * FROM t1; 1378DROP TABLE t1; 1379 1380SET time_zone='+00:00'; 1381CREATE TABLE t1 (a DATETIME, b INT DEFAULT UNIX_TIMESTAMP(a)); 1382SHOW CREATE TABLE t1; 1383INSERT INTO t1 VALUES ('2001-01-01 10:20:30', DEFAULT); 1384SELECT * FROM t1; 1385DROP TABLE t1; 1386SET time_zone=DEFAULT; 1387 1388CREATE TABLE t1 (a TIME, b INT DEFAULT TIME_TO_SEC(a)); 1389SHOW CREATE TABLE t1; 1390INSERT INTO t1 VALUES ('22:23:00', DEFAULT); 1391SELECT * FROM t1; 1392DROP TABLE t1; 1393 1394CREATE TABLE t1 (a INT, b INT, c INT DEFAULT LEAST(a,b), d INT DEFAULT GREATEST(a,b)); 1395SHOW CREATE TABLE t1; 1396INSERT INTO t1 VALUES (0, 1, DEFAULT, DEFAULT); 1397INSERT INTO t1 VALUES (1, 1, DEFAULT, DEFAULT); 1398SELECT * FROM t1; 1399DROP TABLE t1; 1400 1401CREATE TABLE t1 (a INT, b INT, c INT DEFAULT LAST_VALUE(a,b)); 1402SHOW CREATE TABLE t1; 1403INSERT INTO t1 VALUES (1, 2, DEFAULT); 1404SELECT * FROM t1; 1405DROP TABLE t1; 1406 1407--echo # 1408--echo # CAST 1409--echo # 1410CREATE TABLE t1 (a VARCHAR(30), b DECIMAL(10,6) DEFAULT CAST(a AS DECIMAL(10,1))); 1411SHOW CREATE TABLE t1; 1412INSERT INTO t1 (a) VALUES ('123.456'); 1413SELECT * FROM t1; 1414DROP TABLE t1; 1415 1416CREATE TABLE t1 (a DECIMAL(10,3), 1417 b VARCHAR(10) DEFAULT CAST(a AS CHAR(10)), 1418 c VARCHAR(10) DEFAULT CAST(a AS CHAR(4))); 1419SHOW CREATE TABLE t1; 1420INSERT IGNORE INTO t1 (a) VALUES (123.456); 1421SELECT * FROM t1; 1422DROP TABLE t1; 1423 1424CREATE TABLE t1 (a INT, b INT UNSIGNED DEFAULT CAST(a AS UNSIGNED)); 1425SHOW CREATE TABLE t1; 1426INSERT IGNORE INTO t1 (a) VALUES (-1); 1427SELECT * FROM t1; 1428DROP TABLE t1; 1429 1430CREATE TABLE t1 (a BIGINT UNSIGNED, b BIGINT SIGNED DEFAULT CAST(a AS SIGNED)); 1431SHOW CREATE TABLE t1; 1432INSERT INTO t1 (a) VALUES (0xFFFFFFFFFFFFFFFF); 1433SELECT * FROM t1; 1434DROP TABLE t1; 1435 1436CREATE TABLE t1 ( 1437 a VARCHAR(10) CHARACTER SET latin1, 1438 b VARCHAR(10) CHARACTER SET latin1 DEFAULT a COLLATE latin1_bin, 1439 c VARCHAR(10) CHARACTER SET utf8 DEFAULT CONVERT(a USING utf8), 1440 d VARBINARY(10) DEFAULT (BINARY(a)) 1441); 1442SHOW CREATE TABLE t1; 1443INSERT INTO t1 (a) VALUES ('a'); 1444SELECT * FROM t1; 1445DROP TABLE t1; 1446 1447 1448--echo # 1449--echo # Bit functions 1450--echo # 1451 1452CREATE TABLE t1 (a INT, b INT DEFAULT BIT_COUNT(a)); 1453SHOW CREATE TABLE t1; 1454INSERT INTO t1 (a) VALUES (7); 1455SELECT * FROM t1; 1456DROP TABLE t1; 1457 1458CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a|b)); 1459SHOW CREATE TABLE t1; 1460INSERT INTO t1 (a,b) VALUES (1,2); 1461SELECT * FROM t1; 1462DROP TABLE t1; 1463 1464CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a&b)); 1465SHOW CREATE TABLE t1; 1466INSERT INTO t1 (a,b) VALUES (5,4); 1467SELECT * FROM t1; 1468DROP TABLE t1; 1469 1470CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a^b)); 1471SHOW CREATE TABLE t1; 1472INSERT INTO t1 (a,b) VALUES (11,3); 1473SELECT * FROM t1; 1474DROP TABLE t1; 1475 1476CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a&~b)); 1477SHOW CREATE TABLE t1; 1478INSERT INTO t1 (a,b) VALUES (5,1); 1479SELECT * FROM t1; 1480DROP TABLE t1; 1481 1482CREATE TABLE t1 (a INT, b INT, c INT DEFAULT (a<<b), d INT DEFAULT (a>>b)); 1483SHOW CREATE TABLE t1; 1484INSERT INTO t1 (a,b) VALUES (5,1); 1485SELECT * FROM t1; 1486DROP TABLE t1; 1487 1488 1489--echo # 1490--echo # String functions 1491--echo # 1492 1493CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(20) DEFAULT REVERSE(a)); 1494SHOW CREATE TABLE t1; 1495INSERT INTO t1 (a) VALUES ('abcd'); 1496SELECT * FROM t1; 1497DROP TABLE t1; 1498 1499CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT UPPER(a), c VARCHAR(10) DEFAULT LOWER(a)); 1500SHOW CREATE TABLE t1; 1501INSERT INTO t1 (a) VALUES ('ABcd'); 1502SELECT * FROM t1; 1503DROP TABLE t1; 1504 1505CREATE 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)); 1506SHOW CREATE TABLE t1; 1507INSERT INTO t1 (a) VALUES ('abcd'); 1508SELECT * FROM t1; 1509DROP TABLE t1; 1510 1511CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20) DEFAULT SUBSTRING_INDEX(a,'.',2)); 1512SHOW CREATE TABLE t1; 1513INSERT INTO t1 (a) VALUES ('www.mariadb.org'); 1514SELECT * FROM t1; 1515DROP TABLE t1; 1516 1517CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10), c VARCHAR(20) DEFAULT CONCAT(a,b)); 1518SHOW CREATE TABLE t1; 1519INSERT INTO t1 (a,b) VALUES ('a','b'); 1520SELECT * FROM t1; 1521DROP TABLE t1; 1522 1523CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10), c VARCHAR(20) DEFAULT CONCAT_WS(',',a,b)); 1524SHOW CREATE TABLE t1; 1525INSERT INTO t1 (a,b) VALUES ('a','b'); 1526SELECT * FROM t1; 1527DROP TABLE t1; 1528 1529CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT REPLACE(a,'a','A')); 1530SHOW CREATE TABLE t1; 1531INSERT INTO t1 (a) VALUES ('abc'); 1532SELECT * FROM t1; 1533DROP TABLE t1; 1534 1535CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT REGEXP_REPLACE(a,'[0-9]','.')); 1536SHOW CREATE TABLE t1; 1537INSERT INTO t1 (a) VALUES ('a1b2c'); 1538SELECT * FROM t1; 1539DROP TABLE t1; 1540 1541CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT REGEXP_SUBSTR(a,'[0-9]+')); 1542SHOW CREATE TABLE t1; 1543INSERT INTO t1 (a) VALUES ('ab12cd'); 1544SELECT * FROM t1; 1545DROP TABLE t1; 1546 1547CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20) DEFAULT SOUNDEX(a)); 1548SHOW CREATE TABLE t1; 1549INSERT INTO t1 (a) VALUES ('tester'); 1550SELECT * FROM t1; 1551DROP TABLE t1; 1552 1553CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20) DEFAULT QUOTE(a)); 1554SHOW CREATE TABLE t1; 1555INSERT INTO t1 (a) VALUES ('a\'b'); 1556SELECT * FROM t1; 1557DROP TABLE t1; 1558 1559CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT LPAD(a,10,'.'), c VARCHAR(10) DEFAULT RPAD(a,10,'.')); 1560SHOW CREATE TABLE t1; 1561INSERT INTO t1 (a) VALUES ('ab'); 1562SELECT * FROM t1; 1563DROP TABLE t1; 1564 1565CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT LTRIM(a), c VARCHAR(10) DEFAULT RTRIM(a)); 1566SHOW CREATE TABLE t1; 1567INSERT INTO t1 (a) VALUES (' ab '); 1568SELECT a, HEX(b), HEX(c) FROM t1; 1569DROP TABLE t1; 1570 1571CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT TRIM(BOTH 'a' FROM a)); 1572SHOW CREATE TABLE t1; 1573INSERT INTO t1 (a) VALUES ('abba'); 1574SELECT a, b FROM t1; 1575DROP TABLE t1; 1576 1577CREATE TABLE t1 (a INT, b VARCHAR(10) DEFAULT SPACE(a)); 1578SHOW CREATE TABLE t1; 1579INSERT INTO t1 (a) VALUES (3); 1580SELECT a, HEX(b) FROM t1; 1581DROP TABLE t1; 1582 1583CREATE TABLE t1 (a INT, b VARCHAR(10), c VARCHAR(10) DEFAULT REPEAT(b,a)); 1584SHOW CREATE TABLE t1; 1585INSERT INTO t1 (a,b) VALUES (3,'x'); 1586SELECT a, b, c FROM t1; 1587DROP TABLE t1; 1588 1589CREATE TABLE t1 (str VARCHAR(10), pos INT, len INT, newstr VARCHAR(10), result VARCHAR(10) DEFAULT INSERT(str,pos,len,newstr)); 1590SHOW CREATE TABLE t1; 1591INSERT INTO t1 (str,pos,len,newstr) VALUES ('Quadratic', 3, 4, 'What'); 1592SELECT * FROM t1; 1593DROP TABLE t1; 1594 1595CREATE TABLE t1 (n INT, res VARCHAR(10) DEFAULT ELT(n,'ej', 'Heja', 'hej', 'foo')); 1596SHOW CREATE TABLE t1; 1597INSERT INTO t1 (n) VALUES (1); 1598SELECT * FROM t1; 1599DROP TABLE t1; 1600 1601CREATE TABLE t1 (bits INT, res VARCHAR(10) DEFAULT MAKE_SET(bits,'a','b','c','d')); 1602SHOW CREATE TABLE t1; 1603INSERT INTO t1 (bits) VALUES (1|4); 1604SELECT * FROM t1; 1605DROP TABLE t1; 1606 1607CREATE TABLE t1 (a INT, b VARCHAR(10) DEFAULT CHAR(a)); 1608SHOW CREATE TABLE t1; 1609INSERT INTO t1 (a) VALUES (77); 1610SELECT * FROM t1; 1611DROP TABLE t1; 1612 1613CREATE TABLE t1 (a INT, b VARCHAR(10) DEFAULT CONV(a,10,16)); 1614SHOW CREATE TABLE t1; 1615INSERT INTO t1 (a) VALUES (64); 1616SELECT * FROM t1; 1617DROP TABLE t1; 1618 1619CREATE TABLE t1 (a INT, b INT, c VARCHAR(30) DEFAULT FORMAT(a,b)); 1620SHOW CREATE TABLE t1; 1621INSERT INTO t1 (a,b) VALUES (10000,3); 1622SELECT * FROM t1; 1623DROP TABLE t1; 1624 1625CREATE TABLE t1 (a INT, b INT, l VARCHAR(10), c VARCHAR(30) DEFAULT FORMAT(a,b,l)); 1626SHOW CREATE TABLE t1; 1627INSERT INTO t1 (a,b,l) VALUES (10000,2,'no_NO'),(10000,2,'ru_RU'),(10000,2,'ar_BH'); 1628SELECT * FROM t1; 1629DROP TABLE t1; 1630 1631CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(20) DEFAULT GET_FORMAT(DATE,a)); 1632SHOW CREATE TABLE t1; 1633INSERT INTO t1 (a) VALUES ('EUR'),('USA'),('JIS'),('ISO'),('INTERNAL'); 1634SELECT * FROM t1; 1635DROP TABLE t1; 1636 1637CREATE TABLE t1 ( 1638 bits INT, 1639 v_on VARCHAR(10), 1640 v_off VARCHAR(10), 1641 v_separator VARCHAR(10), 1642 number_of_bits INT, 1643 x VARCHAR(30) DEFAULT EXPORT_SET(bits, v_on, v_off, v_separator, number_of_bits) 1644); 1645SHOW CREATE TABLE t1; 1646INSERT IGNORE INTO t1 VALUES (0x50006,'Y','N','',64,DEFAULT); 1647SELECT * FROM t1; 1648DROP TABLE t1; 1649 1650--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 1651CREATE TABLE t1 (a VARCHAR(30), b BLOB DEFAULT LOAD_FILE(a)); 1652 1653--echo # 1654--echo # Predicates 1655--echo # 1656 1657CREATE TABLE t1 (a INT, b INT DEFAULT (NOT a)); 1658SHOW CREATE TABLE t1; 1659INSERT INTO t1 (a) VALUES (NULL),(0),(1); 1660SELECT * FROM t1; 1661DROP TABLE t1; 1662 1663CREATE TABLE t1 (a INT, b INT, x INT DEFAULT (a XOR b)); 1664SHOW CREATE TABLE t1; 1665INSERT INTO t1 (a,b) VALUES (0,0),(0,1),(1,0),(1,1); 1666SELECT * FROM t1; 1667DROP TABLE t1; 1668 1669CREATE TABLE t1 (a INT, b INT DEFAULT (a IS TRUE), c INT DEFAULT (a IS NOT TRUE)); 1670SHOW CREATE TABLE t1; 1671INSERT INTO t1 (a) VALUES (NULL),(0),(1); 1672SELECT * FROM t1; 1673DROP TABLE t1; 1674 1675CREATE TABLE t1 (a INT, b INT DEFAULT (a IS FALSE), c INT DEFAULT (a IS NOT FALSE)); 1676SHOW CREATE TABLE t1; 1677INSERT INTO t1 (a) VALUES (NULL),(0),(1); 1678SELECT * FROM t1; 1679DROP TABLE t1; 1680 1681CREATE TABLE t1 (a INT, b INT DEFAULT (a IS NULL), c INT DEFAULT (a IS NOT NULL)); 1682SHOW CREATE TABLE t1; 1683INSERT INTO t1 (a) VALUES (NULL),(0),(1); 1684SELECT * FROM t1; 1685DROP TABLE t1; 1686 1687CREATE TABLE t1 (a INT, b INT DEFAULT (a IS UNKNOWN), c INT DEFAULT (a IS NOT UNKNOWN)); 1688SHOW CREATE TABLE t1; 1689INSERT INTO t1 (a) VALUES (NULL),(0),(1); 1690SELECT * FROM t1; 1691DROP TABLE t1; 1692 1693CREATE TABLE t1 (a INT, 1694 eq INT DEFAULT (a=0), equal INT DEFAULT (a<=>0), 1695 ne INT DEFAULT (a<>0), 1696 lt INT DEFAULT (a<0), le INT DEFAULT (a<=0), 1697 gt INT DEFAULT (a>0), ge INT DEFAULT (a>=0)); 1698SHOW CREATE TABLE t1; 1699INSERT INTO t1 (a) VALUES (NULL),(-1),(0),(1); 1700SELECT * FROM t1; 1701DROP TABLE t1; 1702 1703CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a LIKE 'a%')); 1704SHOW CREATE TABLE t1; 1705INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'); 1706SELECT * FROM t1; 1707DROP TABLE t1; 1708 1709CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a RLIKE 'a$')); 1710SHOW CREATE TABLE t1; 1711INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'); 1712SELECT * FROM t1; 1713DROP TABLE t1; 1714 1715CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a IN ('aaa','bbb'))); 1716SHOW CREATE TABLE t1; 1717INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'),('ccc'); 1718SELECT * FROM t1; 1719DROP TABLE t1; 1720 1721CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a NOT IN ('aaa','bbb'))); 1722SHOW CREATE TABLE t1; 1723INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'),('ccc'); 1724SELECT * FROM t1; 1725DROP TABLE t1; 1726 1727CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a BETWEEN 'aaa' AND 'bbb')); 1728SHOW CREATE TABLE t1; 1729INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'),('ccc'); 1730SELECT * FROM t1; 1731DROP TABLE t1; 1732 1733CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT (a NOT BETWEEN 'aaa' AND 'bbb')); 1734SHOW CREATE TABLE t1; 1735INSERT INTO t1 (a) VALUES ('AAA'),('aaa'),('bbb'),('ccc'); 1736SELECT * FROM t1; 1737DROP TABLE t1; 1738 1739CREATE TABLE t1 (a TEXT DEFAULT UUID()); 1740SHOW CREATE TABLE t1; 1741INSERT INTO t1 VALUES (); 1742SELECT LENGTH(a)>0 FROM t1; 1743DROP TABLE t1; 1744 1745--echo # 1746--echo # Numeric result functions with string input 1747--echo # 1748 1749CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT STRCMP(a,'b')); 1750SHOW CREATE TABLE t1; 1751INSERT INTO t1 (a) VALUES ('A'),('a'),('B'),('b'),('C'),('c'); 1752SELECT * FROM t1; 1753DROP TABLE t1; 1754 1755CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT LENGTH(a), c INT DEFAULT CHAR_LENGTH(a), d INT DEFAULT BIT_LENGTH(a)); 1756SHOW CREATE TABLE t1; 1757INSERT INTO t1 (a) VALUES ('a'),('aa'),('aaa'); 1758SELECT * FROM t1; 1759DROP TABLE t1; 1760 1761CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT LOCATE('a',a)); 1762SHOW CREATE TABLE t1; 1763INSERT INTO t1 (a) VALUES ('xa'),('xxa'),('xxxa'); 1764SELECT * FROM t1; 1765DROP TABLE t1; 1766 1767CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT REGEXP_INSTR(a, 'a')); 1768SHOW CREATE TABLE t1; 1769INSERT INTO t1 (a) VALUES ('xa'),('xxa'),('xxxa'); 1770SELECT * FROM t1; 1771DROP TABLE t1; 1772 1773--echo # 1774--echo # INT result metadata functions 1775--echo # 1776 1777# QQ: LAST_INSERT_ID() should probably be allowed 1778--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 1779CREATE TABLE t1 1780( 1781 id SERIAL PRIMARY KEY, 1782 b INT DEFAULT LAST_INSERT_ID() 1783); 1784 1785CREATE TABLE t1 (a INT DEFAULT CONNECTION_ID()); 1786SHOW CREATE TABLE t1; 1787INSERT INTO t1 VALUES(); 1788SELECT a>0 FROM t1; 1789DROP TABLE t1; 1790 1791CREATE TABLE t1 (a VARCHAR(10), b INT DEFAULT COERCIBILITY(a), c INT DEFAULT COERCIBILITY(b)); 1792SHOW CREATE TABLE t1; 1793INSERT INTO t1 (a) VALUES ('test'); 1794SELECT * FROM t1; 1795DROP TABLE t1; 1796 1797--echo # 1798--echo # String result metadata functions 1799--echo # 1800CREATE TABLE t1 ( 1801 a VARCHAR(10) CHARACTER SET latin1, 1802 b VARCHAR(20) DEFAULT CHARSET(a), 1803 c VARCHAR(20) DEFAULT COLLATION(a) 1804); 1805SHOW CREATE TABLE t1; 1806INSERT INTO t1 (a) VALUES ('test'); 1807SELECT * FROM t1; 1808DROP TABLE t1; 1809 1810--echo # 1811--echo # Hash, compression, encode/decode 1812--echo # 1813CREATE TABLE t1 (a VARCHAR(10), b BIGINT DEFAULT CRC32(a), c TEXT DEFAULT MD5(a)); 1814SHOW CREATE TABLE t1; 1815INSERT INTO t1 (a) VALUES ('a'); 1816SELECT * FROM t1; 1817DROP TABLE t1; 1818 1819CREATE TABLE t1 (a VARCHAR(10), b TEXT DEFAULT TO_BASE64(a), c TEXT DEFAULT FROM_BASE64(b)); 1820SHOW CREATE TABLE t1; 1821INSERT INTO t1 (a) VALUES ('aaaabbbb'); 1822SELECT * FROM t1; 1823DROP TABLE t1; 1824 1825CREATE TABLE t1 (a VARCHAR(10), b TEXT DEFAULT HEX(a), c TEXT DEFAULT UNHEX(b)); 1826SHOW CREATE TABLE t1; 1827INSERT INTO t1 (a) VALUES ('aaaabbbb'); 1828SELECT * FROM t1; 1829DROP TABLE t1; 1830 1831CREATE TABLE t1 (a VARCHAR(10), b TEXT DEFAULT ENCODE(a,'test'), c TEXT DEFAULT DECODE(b,'test')); 1832SHOW CREATE TABLE t1; 1833INSERT INTO t1 (a) VALUES ('aaaabbbb'); 1834SELECT a, HEX(b), c FROM t1; 1835DROP TABLE t1; 1836 1837CREATE TABLE t1 (a VARCHAR(30), b TEXT DEFAULT PASSWORD(a)); 1838SHOW CREATE TABLE t1; 1839INSERT INTO t1 (a) VALUES ('notagoodpwd'); 1840SELECT * FROM t1; 1841DROP TABLE t1; 1842 1843CREATE TABLE t1 ( 1844 a VARCHAR(30), 1845 b BLOB DEFAULT AES_ENCRYPT(a, 'passwd'), 1846 c TEXT DEFAULT AES_DECRYPT(b, 'passwd') 1847); 1848SHOW CREATE TABLE t1; 1849INSERT INTO t1 (a) VALUES ('test'); 1850SELECT c FROM t1; 1851DROP TABLE t1; 1852 1853--echo # 1854--echo # Collations 1855--echo # 1856 1857--error ER_BAD_DATA 1858CREATE TABLE t1 (a VARCHAR(20) CHARACTER SET latin1 DEFAULT CONCAT('�')) CHARACTER SET koi8r COLLATE koi8r_bin; 1859CREATE OR REPLACE TABLE t1 (a char(2) default concat('A') COLLATE utf8mb4_unicode_ci); 1860SHOW CREATE TABLE t1; 1861DROP TABLE t1; 1862 1863# 1864# Order of evaluation: 1865# 1866create table t1 (a int default 1, b int default (rand()*0+2), c int); 1867insert t1 (c) values (a); 1868insert t1 (c) values (b); 1869select * from t1; 1870drop table t1; 1871 1872# 1873# ALTER ... SET DEFAULT 1874# 1875create table t1 (a int default 1, b int default (1+1), c int); 1876show create table t1; 1877alter table t1 alter a set default (2+3), alter b set default 4, 1878 alter c set default (-a); 1879--error ER_PARSE_ERROR 1880alter table t1 alter a set default 1+2; 1881show create table t1; 1882drop table t1; 1883 1884# 1885# CREATE ... SELECT 1886# 1887create table t1 (a int default 5 check (a>10), b int default (5+5), c int as (a+b)); 1888create table t2 as select a, b, c from t1; 1889create table t3 as select max(a), max(b), max(c) from t1; 1890show create table t2; 1891show create table t3; 1892drop table t1, t2, t3; 1893 1894--echo # MDEV-11359: Implement IGNORE for bulk operation 1895create table t1 (a int primary key default 0, b int default 3); 1896insert into t1 values (1, ignore); 1897insert into t1 values (2, ignore); 1898replace into t1 values (2, ignore); 1899replace into t1 values (3, ignore); 1900replace into t1 values (4, 6); 1901replace into t1 values (5, 7); 1902update t1 set a=6,b=ignore where a=5; 1903insert into t1 values (ignore, ignore); 1904--error ER_DUP_ENTRY 1905insert into t1 values (ignore, ignore); 1906select * from t1 order by a; 1907delete from t1 where a < 4; 1908--echo # actually insert default instead of ignoring 1909--echo # (but REPLACE is non standard operator) 1910replace into t1 values (4, ignore); 1911select * from t1 order by a; 1912drop table t1; 1913 1914#using in load 1915create table t1 (a int default 100, b int, c varchar(60) default 'x'); 1916load data infile '../../std_data/rpl_loaddata.dat' into table t1 (a, @b) set b=@b+10, c=ignore; 1917select * from t1; 1918drop table t1; 1919 1920#using in duplicate 1921CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT); 1922CREATE TABLE t2 (a INT); 1923INSERT INTO t2 VALUES (1),(2),(3),(2); 1924INSERT INTO t1 SELECT a FROM t2 ON DUPLICATE KEY UPDATE a=DEFAULT; 1925SELECT * FROM t1 order by a; 1926truncate table t1; 1927# efectively it is DEFALT 1928INSERT INTO t1 SELECT a FROM t2 ON DUPLICATE KEY UPDATE a=IGNORE; 1929SELECT * FROM t1 order by a; 1930DROP TABLE t1,t2; 1931 1932create table t1 (a int primary key default 0, b int default 3); 1933prepare insstmt from "insert into t1 values (?, ?)"; 1934prepare repstmt from "replace into t1 values (?, ?)"; 1935prepare updstmt from "update t1 set a=6,b=? where a=5"; 1936execute insstmt using 1, ignore; 1937execute insstmt using 2, ignore; 1938execute repstmt using 2, ignore; 1939execute repstmt using 3, ignore; 1940execute repstmt using 4, 6; 1941execute repstmt using 5, 7; 1942execute updstmt using ignore; 1943execute insstmt using ignore, ignore; 1944--error ER_DUP_ENTRY 1945execute insstmt using ignore, ignore; 1946select * from t1 order by a; 1947delete from t1 where a < 4; 1948execute repstmt using 4, ignore; 1949select * from t1 order by a; 1950drop table t1; 1951 1952--echo # 1953--echo # DEVAULT & PS adoption 1954--echo # 1955 1956 1957# Correct usage 1958CREATE TABLE t1 (a INT DEFAULT 10, b INT DEFAULT NULL); 1959EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?,?)' USING IGNORE, IGNORE; 1960SELECT * FROM t1; 1961UPDATE t1 SET a=20, b=30; 1962SELECT * FROM t1; 1963EXECUTE IMMEDIATE 'UPDATE t1 SET a=?,b=?' USING IGNORE, IGNORE; 1964SELECT * FROM t1; 1965DROP TABLE t1; 1966 1967# Incorrect usage in a expression in INSERT..VALUES 1968CREATE TABLE t1 (a INT DEFAULT 10); 1969--error ER_INVALID_DEFAULT_PARAM 1970EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?+1)' USING IGNORE; 1971--error ER_INVALID_DEFAULT_PARAM 1972EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (CONCAT(?,?))' USING IGNORE, 'test'; 1973DROP TABLE t1; 1974 1975# Incorrect usage in UPDATE..SET 1976CREATE TABLE t1 (a INT DEFAULT 10); 1977INSERT INTO t1 VALUES (20); 1978--error ER_INVALID_DEFAULT_PARAM 1979EXECUTE IMMEDIATE 'UPDATE t1 SET a=?+1' USING IGNORE; 1980--error ER_INVALID_DEFAULT_PARAM 1981EXECUTE IMMEDIATE 'UPDATE t1 SET a=CONCAT(?,?)' USING IGNORE, 'test'; 1982DROP TABLE t1; 1983 1984 1985# Incorrect usage in not an UPDATE/INSERT query at all 1986--error ER_INVALID_DEFAULT_PARAM 1987EXECUTE IMMEDIATE 'SELECT CAST(? AS SIGNED)' USING IGNORE; 1988--error ER_INVALID_DEFAULT_PARAM 1989EXECUTE IMMEDIATE 'SELECT CAST(? AS DOUBLE)' USING IGNORE; 1990--error ER_INVALID_DEFAULT_PARAM 1991EXECUTE IMMEDIATE 'SELECT CAST(? AS CHAR)' USING IGNORE; 1992--error ER_INVALID_DEFAULT_PARAM 1993EXECUTE IMMEDIATE 'SELECT CAST(? AS DECIMAL(10,1))' USING IGNORE; 1994--error ER_INVALID_DEFAULT_PARAM 1995EXECUTE IMMEDIATE 'SELECT CAST(? AS TIME)' USING IGNORE; 1996--error ER_INVALID_DEFAULT_PARAM 1997EXECUTE IMMEDIATE 'SELECT CAST(? AS DATE)' USING IGNORE; 1998--error ER_INVALID_DEFAULT_PARAM 1999EXECUTE IMMEDIATE 'SELECT CAST(? AS DATETIME)' USING IGNORE; 2000 2001--error ER_INVALID_DEFAULT_PARAM 2002EXECUTE IMMEDIATE 'SELECT ?+1' USING IGNORE; 2003--error ER_INVALID_DEFAULT_PARAM 2004EXECUTE IMMEDIATE 'SELECT CONCAT(?,?)' USING IGNORE,'test'; 2005 2006 2007# Incorrect usage in the LIMIT clause 2008--error ER_INVALID_DEFAULT_PARAM 2009EXECUTE IMMEDIATE 'SELECT 1 LIMIT ?' USING IGNORE; 2010CREATE TABLE t1 (a INT DEFAULT 10); 2011INSERT INTO t1 VALUES (1),(2),(3); 2012--error ER_INVALID_DEFAULT_PARAM 2013EXECUTE IMMEDIATE 'SELECT * FROM t1 LIMIT ?' USING IGNORE; 2014DROP TABLE t1; 2015 2016 2017--echo # The output of this query in 'Note' is a syntactically incorrect query. 2018--echo # But as it's never logged, it's ok. It should be human readable only. 2019EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT ?' USING IGNORE; 2020 2021 2022# This tests Item_param::eq() for IGNORE as a bound value 2023CREATE TABLE t1 (a INT); 2024INSERT INTO t1 VALUES (1),(2),(3); 2025--error ER_INVALID_DEFAULT_PARAM 2026EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>?+a' USING DEFAULT,DEFAULT; 2027DROP TABLE t1; 2028 2029--echo # 2030--echo # MDEV-11134 Assertion `fixed' failed in Item::const_charset_converter(THD*, CHARSET_INFO*, bool, const char*) 2031--echo # 2032 2033SET NAMES utf8; 2034PREPARE stmt FROM "CREATE OR REPLACE TABLE t1 (c CHAR(8) DEFAULT ?)"; 2035SET @a=''; 2036EXECUTE stmt USING @a; 2037EXECUTE stmt USING @a; 2038SHOW CREATE TABLE t1; 2039DROP TABLE t1; 2040SET @a='A'; 2041EXECUTE stmt USING @a; 2042EXECUTE stmt USING @a; 2043SHOW CREATE TABLE t1; 2044DROP TABLE t1; 2045SET @a=_utf8 0xC380; # LATIN CAPITAL LETTER A WITH GRAVE 2046EXECUTE stmt USING @a; 2047EXECUTE stmt USING @a; 2048SHOW CREATE TABLE t1; 2049DROP TABLE t1; 2050SET @a=_utf8 0xD18F; # Cyrillic letter into a latin1 column 2051--error ER_INVALID_DEFAULT 2052EXECUTE stmt USING @a; 2053--error ER_INVALID_DEFAULT 2054EXECUTE stmt USING @a; 2055DEALLOCATE PREPARE stmt; 2056 2057# 2058# ANSI_QUOTES 2059# 2060set sql_mode=ansi_quotes; 2061create table t1 (a int, b int default (a+1)); 2062show create table t1; 2063insert t1 (a) values (10); 2064set sql_mode=''; 2065show create table t1; 2066insert t1 (a) values (20); 2067flush tables; 2068show create table t1; 2069insert t1 (a) values (30); 2070select * from t1; 2071drop table t1; 2072set sql_mode=default; 2073 2074# 2075# MDEV-10201 Bad results for CREATE TABLE t1 (a INT DEFAULT b, b INT DEFAULT 4) 2076# 2077create table t1 (a int default b, b int default 4, t text); 2078insert t1 (b, t) values (5, '1 column is omitted'); 2079insert t1 values (default, 5, '2 column gets DEFAULT, keyword'); 2080insert t1 values (default(a), 5, '3 column gets DEFAULT(a), expression'); 2081insert t1 values (default(a)+0, 5, '4 also expression DEFAULT(0)+0'); 2082insert t1 values (b, 5, '5 the value of the DEFAULT(a), that is b'); 2083# and the same in a different order 2084insert t1 (t,b,a) values ('6 reversed, column gets DEFAULT, keyword', 5, default); 2085insert t1 (t,b,a) values ('7 reversed, column gets DEFAULT(a), expression', 5, default(a)); 2086insert t1 (t,b,a) values ('8 reversed, also expression DEFAULT(0)+0', 5, default(a)+0); 2087insert t1 (t,b,a) values ('9 reversed, the value of the DEFAULT(a), that is b', 5, b); 2088select * from t1 order by t; 2089drop table t1; 2090 2091# 2092# MDEV-10352 Server crashes in Field::set_default on CREATE TABLE 2093# 2094--error ER_EXPRESSION_REFERS_TO_UNINIT_FIELD 2095create table t1 (col1 int default(-(default(col1)))); 2096 2097# 2098# MDEV-10354 Assertion `! is_set()' failed in Diagnostics_area::set_ok_status on CREATE TABLE with invalid default 2099# 2100--error ER_DATA_OUT_OF_RANGE 2101create table t1 (col int default (yearweek((exp(710))))); 2102 2103--echo # 2104--echo # MDEV-13707 Server in ORACLE mode crashes on ALTER with wrong DEFAULT clause 2105--echo # 2106 2107CREATE OR REPLACE TABLE t1(i int); 2108--error ER_BAD_FIELD_ERROR 2109ALTER TABLE t1 ADD b CHAR(255) DEFAULT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa`; 2110DROP TABLE t1; 2111 2112--echo # 2113--echo # MDEV-18278 Misleading error message in error log upon failed table creation 2114--echo # 2115--error ER_EXPRESSION_REFERS_TO_UNINIT_FIELD 2116create table t1 (a int as (a)); 2117show warnings; 2118 2119--echo # end of 10.2 test 2120 2121--echo # 2122--echo # MDEV-22703 DEFAULT() on a BLOB column can overwrite the default 2123--echo # record, which can cause crashes when accessing already released 2124--echo # memory. 2125--echo # 2126 2127CREATE TEMPORARY TABLE t1 (h POINT DEFAULT ST_GEOMFROMTEXT('Point(1 1)')) ENGINE=InnoDB; 2128INSERT INTO t1 () VALUES (),(); 2129ALTER TABLE t1 FORCE; 2130--disable_result_log 2131SELECT DEFAULT(h) FROM t1; 2132--enable_result_log 2133SELECT length(DEFAULT(h)) FROM t1; 2134INSERT INTO t1 () VALUES (); 2135drop table t1; 2136 2137--echo # end of 10.3 test 2138