1# 2# Check some special create statements. 3# 4SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 5--disable_warnings 6drop table if exists t1,t2,t3,t4,t5; 7drop database if exists mysqltest; 8drop view if exists v1; 9--enable_warnings 10 11create table t1 (b char(0)); 12insert into t1 values (""),(null); 13select * from t1; 14drop table if exists t1; 15 16create table t1 (b char(0) not null); 17create table if not exists t1 (b char(0) not null); 18insert into t1 values (""),(null); 19select * from t1; 20drop table t1; 21 22create table t1 (a int not null auto_increment,primary key (a)) engine=heap; 23drop table t1; 24 25# 26# Test of some CREATE TABLE'S that should fail 27# 28 29--error 1146 30create table t2 engine=heap select * from t1; 31--error 1146 32create table t2 select auto+1 from t1; 33drop table if exists t1,t2; 34--error 1167 35create table t1 (b char(0) not null, index(b)); 36# BLOB/TEXT fields are now supported by HEAP 37create table t1 (a int not null,b text) engine=heap; 38drop table if exists t1; 39 40--error 1075 41create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ord,ordid)) engine=heap; 42 43-- error 1049 44create table not_existing_database.test (a int); 45create table `a/a` (a int); 46show create table `a/a`; 47create table t1 like `a/a`; 48drop table `a/a`; 49drop table `t1`; 50--error ER_TOO_LONG_IDENT 51create table `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` (aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa int); 52--error 1059 53create table a (`aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` int); 54 55# 56# Some wrong defaults, so these creates should fail too (Bug #5902) 57# 58--error 1067 59create table t1 (a int default 100 auto_increment); 60--error 1067 61create table t1 (a tinyint default 1000); 62--error 1067 63create table t1 (a varchar(5) default 'abcdef'); 64 65create table t1 (a varchar(5) default 'abcde'); 66insert into t1 values(); 67select * from t1; 68--error 1067 69alter table t1 alter column a set default 'abcdef'; 70drop table t1; 71 72# 73# test of dummy table names 74# 75 76create table 1ea10 (1a20 int,1e int); 77insert into 1ea10 values(1,1); 78select 1ea10.1a20,1e+ 1e+10 from 1ea10; 79drop table 1ea10; 80create table t1 (t1.index int); 81drop table t1; 82# Test that we get warning for this 83drop database if exists mysqltest; 84create database mysqltest; 85create table mysqltest.$test1 (a$1 int, $b int, c$ int); 86insert into mysqltest.$test1 values (1,2,3); 87select a$1, $b, c$ from mysqltest.$test1; 88create table mysqltest.test2$ (a int); 89drop table mysqltest.test2$; 90drop database mysqltest; 91 92--error 1103 93create table `` (a int); 94--error 1103 95drop table if exists ``; 96--error 1166 97create table t1 (`` int); 98--error 1280 99create table t1 (i int, index `` (i)); 100 101# 102# CREATE TABLE under LOCK TABLES 103# 104# We don't allow creation of non-temporary tables under LOCK TABLES 105# as following meta-data locking protocol in this case can lead to 106# deadlock. 107create table t1 (i int); 108lock tables t1 read; 109--error ER_TABLE_NOT_LOCKED 110create table t2 (j int); 111# OTOH creating of temporary table should be OK 112create temporary table t2 (j int); 113drop temporary table t2; 114unlock tables; 115drop table t1; 116 117# 118# Test of CREATE ... SELECT with indexes 119# 120 121create table t1 (a int auto_increment not null primary key, B CHAR(20)); 122insert into t1 (b) values ("hello"),("my"),("world"); 123create table t2 (key (b)) select * from t1; 124explain select * from t2 where b="world"; 125select * from t2 where b="world"; 126drop table t1,t2; 127 128# 129# Test types after CREATE ... SELECT 130# 131 132create table t1(x varchar(50) ); 133create table t2 select x from t1 where 1=2; 134describe t1; 135describe t2; 136drop table t2; 137create table t2 select now() as a , curtime() as b, curdate() as c , 1+1 as d , 1.0 + 1 as e , 33333333333333333 + 3 as f; 138describe t2; 139drop table t2; 140create table t2 select CAST("2001-12-29" AS DATE) as d, CAST("20:45:11" AS TIME) as t, CAST("2001-12-29 20:45:11" AS DATETIME) as dt; 141describe t2; 142drop table t1,t2; 143 144# 145# Test of CREATE ... SELECT with duplicate fields 146# 147 148create table t1 (a tinyint); 149create table t2 (a int) select * from t1; 150describe t1; 151describe t2; 152drop table if exists t2; 153--error 1060 154create table t2 (a int, a float) select * from t1; 155drop table if exists t2; 156--error 1060 157create table t2 (a int) select a as b, a+1 as b from t1; 158drop table if exists t2; 159--error 1060 160create table t2 (b int) select a as b, a+1 as b from t1; 161drop table if exists t1,t2; 162 163# 164# Test CREATE ... SELECT when insert fails 165# 166 167CREATE TABLE t1 (a int not null); 168INSERT INTO t1 values (1),(2),(1); 169--error ER_DUP_ENTRY 170CREATE TABLE t2 (primary key(a)) SELECT * FROM t1; 171--error 1146 172SELECT * from t2; 173DROP TABLE t1; 174DROP TABLE IF EXISTS t2; 175 176# 177# Test of primary key with 32 index 178# 179 180create table t1 (a int not null, b int, primary key(a), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b)); 181show create table t1; 182drop table t1; 183create table t1 select if(1,'1','0'), month("2002-08-02"); 184drop table t1; 185create table t1 select if('2002'='2002','Y','N'); 186select * from t1; 187drop table if exists t1; 188 189# 190# Test default table type 191# 192SET SESSION default_storage_engine="heap"; 193SELECT @@default_storage_engine; 194CREATE TABLE t1 (a int not null); 195show create table t1; 196drop table t1; 197--error 1286 198SET SESSION default_storage_engine="gemini"; 199SELECT @@default_storage_engine; 200CREATE TABLE t1 (a int not null); 201show create table t1; 202SET SESSION default_storage_engine=default; 203drop table t1; 204 205 206# 207# ISO requires that primary keys are implicitly NOT NULL 208# 209create table t1 ( k1 varchar(2), k2 int, primary key(k1,k2)); 210insert into t1 values ("a", 1), ("b", 2); 211--error 1048 212insert into t1 values ("c", NULL); 213--error 1048 214insert into t1 values (NULL, 3); 215--error 1048 216insert into t1 values (NULL, NULL); 217drop table t1; 218 219# 220# Bug # 801 221# 222 223create table t1 select x'4132'; 224drop table t1; 225 226# 227# bug #1434 228# 229 230create table t1 select 1,2,3; 231create table if not exists t1 select 1,2; 232create table if not exists t1 select 1,2,3,4; 233create table if not exists t1 select 1; 234select * from t1; 235drop table t1; 236 237# 238# Test create table if not exists with duplicate key error 239# 240 241flush status; 242create table t1 (a int not null, b int, primary key (a)); 243insert into t1 values (1,1); 244create table if not exists t1 select 2; 245select * from t1; 246create table if not exists t1 select 3 as 'a',4 as 'b'; 247show warnings; 248show status like "Opened_tables"; 249select * from t1; 250drop table t1; 251 252# 253# Test for Bug #2985 254# "Table truncated when creating another table name with Spaces" 255# 256 257--error 1103 258create table `t1 `(a int); 259--error 1102 260create database `db1 `; 261--error 1166 262create table t1(`a ` int); 263 264# 265# Test for Bug #3481 266# "Parser permits multiple commas without syntax error" 267# 268 269--error 1064 270create table t1 (a int,); 271--error 1064 272create table t1 (a int,,b int); 273--error 1064 274create table t1 (,b int); 275 276# 277# Test create with foreign keys 278# 279 280create table t1 (a int, key(a)); 281create table t2 (b int, foreign key(b) references t1(a), key(b)); 282drop table if exists t2,t1; 283 284# 285# Test for CREATE TABLE .. LIKE .. 286# 287 288create table t1(id int not null, name char(20)); 289insert into t1 values(10,'mysql'),(20,'monty- the creator'); 290create table t2(id int not null); 291insert into t2 values(10),(20); 292create table t3 like t1; 293show create table t3; 294select * from t3; 295# Disable PS becasue of @@warning_count 296create table if not exists t3 like t1; 297--disable_ps_protocol 298select @@warning_count; 299--enable_ps_protocol 300create temporary table t3 like t2; 301--replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE 302show create table t3; 303select * from t3; 304drop table t3; 305show create table t3; 306select * from t3; 307drop table t2, t3; 308create database mysqltest; 309create table mysqltest.t3 like t1; 310create temporary table t3 like mysqltest.t3; 311--replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE 312show create table t3; 313create table t2 like t3; 314show create table t2; 315select * from t2; 316create table t3 like t1; 317--error 1050 318create table t3 like mysqltest.t3; 319--error 1049 320create table non_existing_database.t1 like t1; 321--error ER_NO_SUCH_TABLE 322create table t3 like non_existing_table; 323--error 1050 324create temporary table t3 like t1; 325drop table t1, t2, t3; 326drop table t3; 327drop database mysqltest; 328 329# 330# CREATE TABLE LIKE under LOCK TABLES 331# 332# Similarly to ordinary CREATE TABLE we don't allow creation of 333# non-temporary tables under LOCK TABLES. Also we require source 334# table to be locked. 335create table t1 (i int); 336create table t2 (j int); 337lock tables t1 read; 338--error ER_TABLE_NOT_LOCKED 339create table t3 like t1; 340# OTOH creating of temporary table should be OK 341create temporary table t3 like t1; 342drop temporary table t3; 343# Source table should be locked 344--error ER_TABLE_NOT_LOCKED 345create temporary table t3 like t2; 346unlock tables; 347drop tables t1, t2; 348 349# 350# Test default table type 351# 352SET SESSION default_storage_engine="heap"; 353SELECT @@default_storage_engine; 354CREATE TABLE t1 (a int not null); 355show create table t1; 356drop table t1; 357--error 1286 358SET SESSION default_storage_engine="gemini"; 359SELECT @@default_storage_engine; 360CREATE TABLE t1 (a int not null); 361show create table t1; 362SET SESSION default_storage_engine=default; 363drop table t1; 364 365# 366# Test types of data for create select with functions 367# 368 369create table t1(a int,b int,c int unsigned,d date,e char,f datetime,g time,h blob); 370insert into t1(a)values(1); 371insert into t1(a,b,c,d,e,f,g,h) 372values(2,-2,2,'1825-12-14','a','2003-1-1 3:2:1','4:3:2','binary data'); 373select * from t1; 374select a, 375 ifnull(b,cast(-7 as signed)) as b, 376 ifnull(c,cast(7 as unsigned)) as c, 377 ifnull(d,cast('2000-01-01' as date)) as d, 378 ifnull(e,cast('b' as char)) as e, 379 ifnull(f,cast('2000-01-01' as datetime)) as f, 380 ifnull(g,cast('5:4:3' as time)) as g, 381 ifnull(h,cast('yet another binary data' as binary)) as h, 382 addtime(cast('1:0:0' as time),cast('1:0:0' as time)) as dd 383from t1; 384 385create table t2 386select 387 a, 388 ifnull(b,cast(-7 as signed)) as b, 389 ifnull(c,cast(7 as unsigned)) as c, 390 ifnull(d,cast('2000-01-01' as date)) as d, 391 ifnull(e,cast('b' as char)) as e, 392 ifnull(f,cast('2000-01-01' as datetime)) as f, 393 ifnull(g,cast('5:4:3' as time)) as g, 394 ifnull(h,cast('yet another binary data' as binary)) as h, 395 addtime(cast('1:0:0' as time),cast('1:0:0' as time)) as dd 396from t1; 397explain t2; 398select * from t2; 399drop table t1, t2; 400 401create table t1 (a tinyint, b smallint, c mediumint, d int, e bigint, f float(3,2), g double(4,3), h decimal(5,4), i year, j date, k timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, l datetime, m enum('a','b'), n set('a','b'), o char(10)); 402create table t2 select ifnull(a,a), ifnull(b,b), ifnull(c,c), ifnull(d,d), ifnull(e,e), ifnull(f,f), ifnull(g,g), ifnull(h,h), ifnull(i,i), ifnull(j,j), ifnull(k,k), ifnull(l,l), ifnull(m,m), ifnull(n,n), ifnull(o,o) from t1; 403show create table t2; 404drop table t1,t2; 405 406# 407# Test of default() 408# 409create table t1(str varchar(10) default 'def',strnull varchar(10),intg int default '10',rel double default '3.14'); 410insert into t1 values ('','',0,0.0); 411describe t1; 412create table t2 select default(str) as str, default(strnull) as strnull, default(intg) as intg, default(rel) as rel from t1; 413describe t2; 414drop table t1, t2; 415 416# 417# Bug #2075 418# 419 420create table t1(name varchar(10), age smallint default -1); 421describe t1; 422create table t2(name varchar(10), age smallint default - 1); 423describe t2; 424drop table t1, t2; 425 426# 427# test for bug #1427 "enum allows duplicate values in the list" 428# 429 430create table t1(cenum enum('a'), cset set('b')); 431create table t2(cenum enum('a','a'), cset set('b','b')); 432create table t3(cenum enum('a','A','a','c','c'), cset set('b','B','b','d','d')); 433drop table t1, t2, t3; 434 435# 436# Bug #1209 437# 438 439create database mysqltest; 440use mysqltest; 441select database(); 442drop database mysqltest; 443select database(); 444 445# Connect without a database as user mysqltest_1 446create user mysqltest_1; 447connect (user1,localhost,mysqltest_1,,*NO-ONE*); 448connection user1; 449select database(), user(); 450connection default; 451disconnect user1; 452drop user mysqltest_1; 453use test; 454 455# 456# Test for Bug 856 'Naming a key "Primary" causes trouble' 457# 458 459--error 1280 460create table t1 (a int, index `primary` (a)); 461--error 1280 462create table t1 (a int, index `PRIMARY` (a)); 463 464create table t1 (`primary` int, index(`primary`)); 465show create table t1; 466create table t2 (`PRIMARY` int, index(`PRIMARY`)); 467show create table t2; 468 469create table t3 (a int); 470--error 1280 471alter table t3 add index `primary` (a); 472--error 1280 473alter table t3 add index `PRIMARY` (a); 474 475create table t4 (`primary` int); 476alter table t4 add index(`primary`); 477show create table t4; 478create table t5 (`PRIMARY` int); 479alter table t5 add index(`PRIMARY`); 480show create table t5; 481 482drop table t1, t2, t3, t4, t5; 483 484# 485# bug #3266 TEXT in CREATE TABLE SELECT 486# 487 488CREATE TABLE t1(id varchar(10) NOT NULL PRIMARY KEY, dsc longtext); 489INSERT INTO t1 VALUES ('5000000001', NULL),('5000000003', 'Test'),('5000000004', NULL); 490CREATE TABLE t2(id varchar(15) NOT NULL, proc varchar(100) NOT NULL, runID varchar(16) NOT NULL, start datetime NOT NULL, PRIMARY KEY (id,proc,runID,start)); 491 492INSERT INTO t2 VALUES ('5000000001', 'proc01', '20031029090650', '2003-10-29 13:38:40'),('5000000001', 'proc02', '20031029090650', '2003-10-29 13:38:51'),('5000000001', 'proc03', '20031029090650', '2003-10-29 13:38:11'),('5000000002', 'proc09', '20031024013310', '2003-10-24 01:33:11'),('5000000002', 'proc09', '20031024153537', '2003-10-24 15:36:04'),('5000000004', 'proc01', '20031024013641', '2003-10-24 01:37:29'),('5000000004', 'proc02', '20031024013641', '2003-10-24 01:37:39'); 493 494CREATE TABLE t3 SELECT t1.dsc,COUNT(DISTINCT t2.id) AS countOfRuns FROM t1 LEFT JOIN t2 ON (t1.id=t2.id) GROUP BY t1.id; 495SELECT * FROM t3; 496drop table t1, t2, t3; 497 498# 499# Bug#9666: Can't use 'DEFAULT FALSE' for column of type bool 500# 501create table t1 (b bool not null default false); 502create table t2 (b bool not null default true); 503insert into t1 values (); 504insert into t2 values (); 505select * from t1; 506select * from t2; 507drop table t1,t2; 508 509# 510# Bug#10224 - ANALYZE TABLE crashing with simultaneous 511# CREATE ... SELECT statement. 512# This tests two additional possible errors and a hang if 513# an improper fix is present. 514# 515create table t1 (a int); 516--error ER_TABLE_EXISTS_ERROR 517create table t1 select * from t1; 518--error ER_WRONG_OBJECT 519create table t2 union = (t1) select * from t1; 520flush tables with read lock; 521unlock tables; 522drop table t1; 523 524# 525# Bug#10413: Invalid column name is not rejected 526# 527--error 1103 528create table t1(column.name int); 529--error 1103 530create table t1(test.column.name int); 531--error 1102 532create table t1(xyz.t1.name int); 533create table t1(t1.name int); 534create table t2(test.t2.name int); 535drop table t1,t2; 536 537# 538# Bug #12537: UNION produces longtext instead of varchar 539# 540CREATE TABLE t1 (f1 VARCHAR(255) CHARACTER SET utf8); 541CREATE TABLE t2 AS SELECT LEFT(f1,171) AS f2 FROM t1 UNION SELECT LEFT(f1,171) AS f2 FROM t1; 542DESC t2; 543DROP TABLE t1,t2; 544 545# 546# Bug#12913 Simple SQL can crash server or connection 547# 548CREATE TABLE t12913 (f1 ENUM ('a','b')) AS SELECT 'a' AS f1; 549SELECT * FROM t12913; 550DROP TABLE t12913; 551 552# 553# Bug#11028: Crash on create table like 554# 555create database mysqltest; 556use mysqltest; 557drop database mysqltest; 558--error ER_NO_DB_ERROR 559create table test.t1 like x; 560--disable_warnings 561drop table if exists test.t1; 562--enable_warnings 563 564# 565# Bug #6859: Bogus error message on attempt to CREATE TABLE t LIKE view 566# 567create database mysqltest; 568use mysqltest; 569create view v1 as select 'foo' from dual; 570--error 1347 571create table t1 like v1; 572drop view v1; 573drop database mysqltest; 574# Bug #6008 MySQL does not create warnings when 575# creating database and using IF NOT EXISTS 576# 577create database mysqltest; 578create database if not exists mysqltest character set latin2; 579show create database mysqltest; 580drop database mysqltest; 581use test; 582create table t1 (a int); 583create table if not exists t1 (a int); 584drop table t1; 585 586# BUG#14139 587create table t1 ( 588 a varchar(112) charset utf8 collate utf8_bin not null, 589 primary key (a) 590) select 'test' as a ; 591#--warning 1364 592show create table t1; 593drop table t1; 594 595# 596# BUG#14480: assert failure in CREATE ... SELECT because of wrong 597# calculation of number of NULLs. 598# 599CREATE TABLE t2 ( 600 a int(11) default NULL 601); 602insert into t2 values(111); 603 604#--warning 1364 605create table t1 ( 606 a varchar(12) charset utf8 collate utf8_bin not null, 607 b int not null, primary key (a) 608) select a, 1 as b from t2 ; 609show create table t1; 610drop table t1; 611 612#--warning 1364 613create table t1 ( 614 a varchar(12) charset utf8 collate utf8_bin not null, 615 b int not null, primary key (a) 616) select a, 1 as c from t2 ; 617show create table t1; 618drop table t1; 619 620#--warning 1364 621create table t1 ( 622 a varchar(12) charset utf8 collate utf8_bin not null, 623 b int null, primary key (a) 624) select a, 1 as c from t2 ; 625show create table t1; 626drop table t1; 627 628#--warning 1364 629create table t1 ( 630 a varchar(12) charset utf8 collate utf8_bin not null, 631 b int not null, primary key (a) 632) select 'a' as a , 1 as b from t2 ; 633show create table t1; 634drop table t1; 635 636#--warning 1364 637create table t1 ( 638 a varchar(12) charset utf8 collate utf8_bin, 639 b int not null, primary key (a) 640) select 'a' as a , 1 as b from t2 ; 641show create table t1; 642drop table t1, t2; 643 644create table t1 ( 645 a1 int not null, 646 a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int 647); 648insert into t1 values (1,1,1, 1,1,1, 1,1,1); 649 650#--warning 1364 651create table t2 ( 652 a1 varchar(12) charset utf8 collate utf8_bin not null, 653 a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, 654 primary key (a1) 655) select a1,a2,a3,a4,a5,a6,a7,a8,a9 from t1 ; 656drop table t2; 657 658#--warning 1364 659create table t2 ( 660 a1 varchar(12) charset utf8 collate utf8_bin, 661 a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int 662) select a1,a2,a3,a4,a5,a6,a7,a8,a9 from t1; 663 664drop table t1, t2; 665#--warning 1364 666create table t1 ( 667 a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int 668); 669insert into t1 values (1,1,1, 1,1,1, 1,1,1); 670 671#--warning 1364 672create table t2 ( 673 a1 varchar(12) charset utf8 collate utf8_bin not null, 674 a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, 675 primary key (a1) 676) select a1,a2,a3,a4,a5,a6,a7,a8,a9 from t1 ; 677 678# Test the default value 679drop table t2; 680 681create table t2 ( a int default 3, b int default 3) 682 select a1,a2 from t1; 683show create table t2; 684 685drop table t1, t2; 686 687# 688# Bug #15316 SET value having comma not correctly handled 689# 690--error 1367 691create table t1(a set("a,b","c,d") not null); 692 693# End of 4.1 tests 694 695 696# 697# Bug #14155: Maximum value of MAX_ROWS handled incorrectly on 64-bit 698# platforms 699# 700create table t1 (i int) engine=myisam max_rows=100000000000; 701show create table t1; 702alter table t1 max_rows=100; 703show create table t1; 704alter table t1 max_rows=100000000000; 705show create table t1; 706drop table t1; 707 708 709# 710# Tests for errors happening at various stages of CREATE TABLES ... SELECT 711# 712# (Also checks that it behaves atomically in the sense that in case 713# of error it is automatically dropped if it has not existed before.) 714# 715# Error during open_and_lock_tables() of tables 716--error ER_NO_SUCH_TABLE 717create table t1 select * from t2; 718# A special case which is also caught during open tables pahse 719--error ER_NO_SUCH_TABLE 720create table t1 select * from t1; 721# Error which happens before select_create::prepare() 722--error ER_CANT_AGGREGATE_2COLLATIONS 723create table t1 select coalesce('a' collate latin1_swedish_ci,'b' collate latin1_bin); 724# Error during table creation 725--error ER_KEY_COLUMN_DOES_NOT_EXITS 726create table t1 (primary key(a)) select "b" as b; 727# Error in select_create::prepare() which is not related to table creation 728create table t1 (a int); 729create table if not exists t1 select 1 as a, 2 as b; 730drop table t1; 731# Finally error which happens during insert 732--error ER_DUP_ENTRY 733create table t1 (primary key (a)) (select 1 as a) union all (select 1 as a); 734# What happens if table already exists ? 735create table t1 (i int); 736--error ER_TABLE_EXISTS_ERROR 737create table t1 select 1 as i; 738create table if not exists t1 select 1 as i; 739select * from t1; 740# After WL#5370, it just generates a warning that the table already exists. 741create table if not exists t1 select * from t1; 742select * from t1; 743drop table t1; 744# Error before select_create::prepare() 745--error ER_CANT_AGGREGATE_2COLLATIONS 746create table t1 select coalesce('a' collate latin1_swedish_ci,'b' collate latin1_bin); 747 748 749# Base vs temporary tables dillema (a.k.a. bug#24508 "Inconsistent 750# results of CREATE TABLE ... SELECT when temporary table exists"). 751# In this situation we either have to create non-temporary table and 752# insert data in it or insert data in temporary table without creation of 753# permanent table. After patch for Bug#47418, we create the base table and 754# instert data into it, even though a temporary table exists with the same 755# name. 756create temporary table t1 (j int); 757create table if not exists t1 select 1; 758select * from t1; 759drop temporary table t1; 760select * from t1; 761drop table t1; 762 763 764# 765# CREATE TABLE ... SELECT and LOCK TABLES 766# 767# There is little sense in using CREATE TABLE ... SELECT under 768# LOCK TABLES as it mostly does not work. At least we check that 769# the server doesn't crash, hang and produces sensible errors. 770# Includes test for bug #20662 "Infinite loop in CREATE TABLE 771# IF NOT EXISTS ... SELECT with locked tables". 772create table t1 (i int); 773insert into t1 values (1), (2); 774lock tables t1 read; 775--error ER_TABLE_NOT_LOCKED 776create table t2 select * from t1; 777--error ER_TABLE_NOT_LOCKED 778create table if not exists t2 select * from t1; 779unlock tables; 780create table t2 (j int); 781lock tables t1 read; 782--error ER_TABLE_NOT_LOCKED 783create table t2 select * from t1; 784# This should not be ever allowed as it will undermine 785# lock-all-at-once approach 786--error ER_TABLE_NOT_LOCKED 787create table if not exists t2 select * from t1; 788unlock tables; 789lock table t1 read, t2 read; 790--error ER_TABLE_EXISTS_ERROR 791create table t2 select * from t1; 792create table if not exists t2 select * from t1; 793unlock tables; 794lock table t1 read, t2 write; 795--error ER_TABLE_EXISTS_ERROR 796create table t2 select * from t1; 797# This is the only case which really works. 798create table if not exists t2 select * from t1; 799select * from t1; 800unlock tables; 801drop table t2; 802 803# OTOH CREATE TEMPORARY TABLE ... SELECT should work 804# well under LOCK TABLES. 805lock tables t1 read; 806create temporary table t2 select * from t1; 807create temporary table if not exists t2 select * from t1; 808select * from t2; 809unlock tables; 810drop table t1, t2; 811 812 813# 814# Bug#21772: can not name a column 'upgrade' when create a table 815# 816create table t1 (upgrade int); 817drop table t1; 818 819 820--echo 821--echo Bug #26104 Bug on foreign key class constructor 822--echo 823--echo Check that ref_columns is initalized correctly in the constructor 824--echo and semantic checks in mysql_prepare_table work. 825--echo 826--echo We do not need a storage engine that supports foreign keys 827--echo for this test, as the checks are purely syntax-based, and the 828--echo syntax is supported for all engines. 829--echo 830--disable_warnings 831drop table if exists t1,t2; 832--enable_warnings 833 834create table t1(a int not null, b int not null, primary key (a, b)); 835--error ER_WRONG_FK_DEF 836create table t2(a int not null, b int not null, c int not null, primary key (a), 837foreign key fk_bug26104 (b,c) references t1(a)); 838drop table t1; 839 840# 841# Bug#15130:CREATE .. SELECT was denied to use advantages of the SQL_BIG_RESULT. 842# 843create table t1(f1 int,f2 int); 844insert into t1 value(1,1),(1,2),(1,3),(2,1),(2,2),(2,3); 845flush status; 846create table t2 select sql_big_result f1,count(f2) from t1 group by f1; 847show status like 'handler_read%'; 848drop table t1,t2; 849 850# 851# Bug #25162: Backing up DB from 5.1 adds 'USING BTREE' to KEYs on table creates 852# 853 854# Show that the old syntax for index type is supported 855CREATE TABLE t1(c1 VARCHAR(33), KEY USING BTREE (c1)); 856DROP TABLE t1; 857 858# Show that the new syntax for index type is supported 859CREATE TABLE t1(c1 VARCHAR(33), KEY (c1) USING BTREE); 860DROP TABLE t1; 861 862# Show that in case of multiple index type definitions, the last one takes 863# precedence 864 865CREATE TABLE t1(c1 VARCHAR(33), KEY USING BTREE (c1) USING HASH) ENGINE=MEMORY; 866SHOW INDEX FROM t1; 867DROP TABLE t1; 868 869CREATE TABLE t1(c1 VARCHAR(33), KEY USING HASH (c1) USING BTREE) ENGINE=MEMORY; 870SHOW INDEX FROM t1; 871DROP TABLE t1; 872 873# 874# Bug#35924 DEFINER should be stored 'quoted' in I_S 875# 876--error ER_UNKNOWN_ERROR 877create user mysqltest_1@'test@test'; 878 879# 880# Bug#38821: Assert table->auto_increment_field_not_null failed in open_table() 881# 882CREATE TABLE t1 (a INTEGER AUTO_INCREMENT PRIMARY KEY, b INTEGER NOT NULL); 883INSERT IGNORE INTO t1 (b) VALUES (5); 884 885CREATE TABLE IF NOT EXISTS t2 (a INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY) 886 SELECT a FROM t1; 887--error 1062 888INSERT INTO t2 SELECT a FROM t1; 889--error 1062 890INSERT INTO t2 SELECT a FROM t1; 891 892DROP TABLE t1, t2; 893 894--echo # 895--echo # BUG#46384 - mysqld segfault when trying to create table with same 896--echo # name as existing view 897--echo # 898 899CREATE TABLE t1 (a INT); 900CREATE TABLE t2 (a INT); 901 902INSERT INTO t1 VALUES (1),(2),(3); 903INSERT INTO t2 VALUES (1),(2),(3); 904 905CREATE VIEW v1 AS SELECT t1.a FROM t1, t2; 906--error ER_TABLE_EXISTS_ERROR 907CREATE TABLE v1 AS SELECT * FROM t1; 908 909DROP VIEW v1; 910DROP TABLE t1,t2; 911 912--echo End of 5.0 tests 913 914# 915# Test of behaviour with CREATE ... SELECT 916# 917 918CREATE TABLE t1 (a int, b int); 919insert into t1 values (1,1),(1,2); 920--error ER_DUP_ENTRY 921CREATE TABLE t2 (primary key (a)) select * from t1; 922# This should give warning 923drop table if exists t2; 924--error ER_DUP_ENTRY 925CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1; 926# This should give warning 927drop table if exists t2; 928CREATE TABLE t2 (a int, b int, primary key (a)); 929--error ER_DUP_ENTRY 930INSERT INTO t2 select * from t1; 931SELECT * from t2; 932TRUNCATE table t2; 933--error ER_DUP_ENTRY 934INSERT INTO t2 select * from t1; 935SELECT * from t2; 936drop table t2; 937 938CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)) ENGINE=InnoDB; 939--error ER_DUP_ENTRY 940INSERT INTO t2 SELECT * FROM t1; 941SELECT * from t2; 942drop table t1,t2; 943 944 945# 946# Test incorrect database names 947# 948 949--error ER_TOO_LONG_IDENT 950CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa; 951--error ER_TOO_LONG_IDENT 952DROP DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa; 953 954# TODO: enable these tests when RENAME DATABASE is implemented. 955# --error 1049 956# RENAME DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa TO a; 957# --error 1102 958# RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa; 959# create database mysqltest; 960# --error 1102 961# RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa; 962# drop database mysqltest; 963 964--error ER_TOO_LONG_IDENT 965USE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa; 966--error ER_TOO_LONG_IDENT 967SHOW CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa; 968 969# 970# Bug#21432 Database/Table name limited to 64 bytes, not chars, problems with multi-byte 971# 972set names utf8; 973 974create database имя_базы_в_кодировке_утф8_длиной_больше_чем_45; 975use имя_базы_в_кодировке_утф8_длиной_больше_чем_45; 976select database(); 977use test; 978 979select SCHEMA_NAME from information_schema.schemata 980where schema_name='имя_базы_в_кодировке_утф8_длиной_больше_чем_45'; 981 982drop database имя_базы_в_кодировке_утф8_длиной_больше_чем_45; 983create table имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48 984( 985 имя_поля_в_кодировке_утф8_длиной_больше_чем_45 int, 986 index имя_индекса_в_кодировке_утф8_длиной_больше_чем_48 (имя_поля_в_кодировке_утф8_длиной_больше_чем_45) 987); 988 989create view имя_вью_кодировке_утф8_длиной_больше_чем_42 as 990select имя_поля_в_кодировке_утф8_длиной_больше_чем_45 991from имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48; 992 993# database, table, field, key, view 994select * from имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48; 995 996select TABLE_NAME from information_schema.tables where 997table_schema='test'; 998 999select COLUMN_NAME from information_schema.columns where 1000table_schema='test'; 1001 1002select INDEX_NAME from information_schema.statistics where 1003table_schema='test'; 1004 1005select TABLE_NAME from information_schema.views where 1006table_schema='test'; 1007 1008show create table имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48; 1009show create view имя_вью_кодировке_утф8_длиной_больше_чем_42; 1010 1011create trigger имя_триггера_в_кодировке_утф8_длиной_больше_чем_49 1012before insert on имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48 for each row set @a:=1; 1013select TRIGGER_NAME from information_schema.triggers where 1014trigger_schema='test'; 1015drop trigger имя_триггера_в_кодировке_утф8_длиной_больше_чем_49; 1016--error 1059 1017create trigger 1018очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_66 1019before insert on имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48 for each row set @a:=1; 1020--error 1059 1021drop trigger очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_66; 1022 1023create procedure имя_процедуры_в_кодировке_утф8_длиной_больше_чем_50() 1024begin 1025end; 1026select ROUTINE_NAME from information_schema.routines where 1027routine_schema='test'; 1028drop procedure имя_процедуры_в_кодировке_утф8_длиной_больше_чем_50; 1029--error 1059 1030create procedure очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_66() 1031begin 1032end; 1033 1034create function имя_функции_в_кодировке_утф8_длиной_больше_чем_49() 1035 returns int 1036return 0; 1037select ROUTINE_NAME from information_schema.routines where 1038routine_schema='test'; 1039drop function имя_функции_в_кодировке_утф8_длиной_больше_чем_49; 1040--error 1059 1041create function очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_66() 1042 returns int 1043return 0; 1044 1045drop view имя_вью_кодировке_утф8_длиной_больше_чем_42; 1046drop table имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48; 1047set names default; 1048 1049# 1050# Bug#21136 CREATE TABLE SELECT within CREATE TABLE SELECT causes server crash 1051# 1052 1053--disable_warnings 1054drop table if exists t1,t2,t3; 1055drop function if exists f1; 1056--enable_warnings 1057 1058--delimiter | 1059create function f1() returns int 1060begin 1061 declare res int; 1062 create temporary table t3 select 1 i; 1063 set res:= (select count(*) from t1); 1064 drop temporary table t3; 1065 return res; 1066end| 1067--delimiter ; 1068create table t1 as select 1; 1069create table t2 as select f1() from t1; 1070drop table t1,t2; 1071drop function f1; 1072 1073# 1074# Bug#25629 CREATE TABLE LIKE does not work with INFORMATION_SCHEMA 1075# 1076create table t1 like information_schema.processlist; 1077--replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE 1078show create table t1; 1079drop table t1; 1080create temporary table t1 like information_schema.processlist; 1081--replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE 1082show create table t1; 1083drop table t1; 1084create table t1 like information_schema.character_sets; 1085show create table t1; 1086drop table t1; 1087 1088########################################################################### 1089 1090--echo 1091--echo # -- 1092--echo # -- Bug#21380: DEFAULT definition not always transfered by CREATE 1093--echo # -- TABLE/SELECT to the new table. 1094--echo # -- 1095--echo 1096 1097 1098--disable_warnings 1099DROP TABLE IF EXISTS t1; 1100DROP TABLE IF EXISTS t2; 1101--enable_warnings 1102 1103--echo 1104 1105CREATE TABLE t1( 1106 c1 INT DEFAULT 12 COMMENT 'column1', 1107 c2 INT NULL COMMENT 'column2', 1108 c3 INT NOT NULL COMMENT 'column3', 1109 c4 VARCHAR(255) CHARACTER SET utf8 NOT NULL DEFAULT 'a', 1110 c5 VARCHAR(255) COLLATE utf8_unicode_ci NULL DEFAULT 'b', 1111 c6 VARCHAR(255)) 1112 COLLATE latin1_bin; 1113 1114--echo 1115 1116SHOW CREATE TABLE t1; 1117 1118--echo 1119 1120CREATE TABLE t2 AS SELECT * FROM t1; 1121 1122--echo 1123 1124SHOW CREATE TABLE t2; 1125 1126--echo 1127 1128DROP TABLE t2; 1129DROP TABLE t1; 1130 1131--echo 1132--echo # -- End of test case for Bug#21380. 1133 1134########################################################################### 1135 1136--echo 1137--echo # -- 1138--echo # -- Bug#18834: ALTER TABLE ADD INDEX on table with two timestamp fields 1139--echo # -- 1140--echo 1141 1142--disable_warnings 1143DROP TABLE IF EXISTS t1; 1144DROP TABLE IF EXISTS t2; 1145DROP TABLE IF EXISTS t3; 1146--enable_warnings 1147 1148--echo 1149 1150CREATE TABLE t1(c1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c2 TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'); 1151 1152--echo 1153 1154SET sql_mode = 'NO_ZERO_DATE'; 1155 1156--echo 1157CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP DEFAULT 0); 1158DROP TABLE t2; 1159 1160 1161--echo 1162--error ER_INVALID_DEFAULT 1163CREATE TABLE t2(c1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, c2 TIMESTAMP NOT NULL); 1164 1165--echo 1166--echo # -- Check that NULL column still can be created. 1167CREATE TABLE t2(c1 TIMESTAMP NULL); 1168 1169--echo 1170--echo # -- Check ALTER TABLE. 1171ALTER TABLE t1 ADD INDEX(c1); 1172 1173--echo 1174--echo # -- Check DATETIME. 1175SET sql_mode = ''; 1176 1177--echo 1178 1179CREATE TABLE t3(c1 DATETIME NOT NULL) ENGINE=MYISAM; 1180INSERT INTO t3 VALUES (0); 1181 1182--echo 1183SET sql_mode = TRADITIONAL; 1184 1185--echo 1186--error ER_TRUNCATED_WRONG_VALUE 1187ALTER TABLE t3 ADD INDEX(c1); 1188 1189--echo 1190--echo # -- Cleanup. 1191 1192SET sql_mode = ''; 1193DROP TABLE t1; 1194DROP TABLE t2; 1195DROP TABLE t3; 1196 1197--echo 1198--echo # -- End of Bug#18834. 1199 1200########################################################################### 1201 1202--echo 1203--echo # -- 1204--echo # -- Bug#34274: Invalid handling of 'DEFAULT 0' for YEAR data type. 1205--echo # -- 1206--echo 1207 1208--disable_warnings 1209DROP TABLE IF EXISTS t1; 1210--enable_warnings 1211 1212--echo 1213CREATE TABLE t1(c1 YEAR DEFAULT 2008, c2 YEAR DEFAULT 0); 1214 1215--echo 1216SHOW CREATE TABLE t1; 1217 1218--echo 1219INSERT INTO t1 VALUES(); 1220 1221--echo 1222SELECT * FROM t1; 1223 1224--echo 1225ALTER TABLE t1 MODIFY c1 YEAR DEFAULT 0; 1226 1227--echo 1228SHOW CREATE TABLE t1; 1229 1230--echo 1231INSERT INTO t1 VALUES(); 1232 1233--echo 1234SELECT * FROM t1; 1235 1236--echo 1237DROP TABLE t1; 1238 1239--echo 1240--echo # -- End of Bug#34274 1241 1242########################################################################### 1243 1244# 1245# Bug#40104 regression with table names? 1246# 1247create table `me:i`(id int); 1248drop table `me:i`; 1249 1250########################################################################### 1251 1252# 1253# Bug#45829 CREATE TABLE TRANSACTIONAL PAGE_CHECKSUM ROW_FORMAT=PAGE accepted, does nothing 1254# 1255 1256--echo 1257--echo # -- 1258--echo # -- Bug#45829: CREATE TABLE TRANSACTIONAL PAGE_CHECKSUM ROW_FORMAT=PAGE accepted, does nothing 1259--echo # -- 1260--echo 1261 1262--disable_warnings 1263drop table if exists t1,t2,t3; 1264--enable_warnings 1265--error ER_PARSE_ERROR 1266create table t1 (a int) transactional=0; 1267--error ER_PARSE_ERROR 1268create table t2 (a int) page_checksum=1; 1269--error ER_PARSE_ERROR 1270create table t3 (a int) row_format=page; 1271--echo 1272--echo # -- End of Bug#45829 1273 1274--echo 1275--echo End of 5.1 tests 1276 1277 1278########################################################################### 1279 1280--echo 1281--echo # -- 1282--echo # -- Bug #43054 Assertion `!table->auto_increment_field_not_null' 1283--echo # -- failed when redefining trigger 1284--echo 1285 1286#--disable_abort_on_error 1287 1288CREATE TABLE B ( 1289 pk INTEGER AUTO_INCREMENT, 1290 int_key INTEGER NOT NULL, 1291 PRIMARY KEY (pk), 1292 KEY (int_key) 1293); 1294 1295INSERT IGNORE INTO B VALUES ('9', '9'); 1296 1297CREATE TABLE IF NOT EXISTS t1 ( 1298 `pk` INTEGER NOT NULL AUTO_INCREMENT , 1299 `int` INTEGER , 1300 PRIMARY KEY ( `pk` ) 1301) SELECT `pk` , `int_key` FROM B ; 1302 1303--delimiter | 1304 1305CREATE TRIGGER f BEFORE INSERT ON t1 FOR EACH ROW 1306BEGIN 1307 INSERT INTO t1 ( `int` ) VALUES (4 ),( 8 ),( 2 ) ; 1308END ; | 1309 1310--delimiter ; 1311--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG 1312INSERT INTO t1 (pk, int_key) SELECT `pk` , `int_key` FROM B ; 1313 1314--delimiter | 1315--error ER_TRG_ALREADY_EXISTS 1316CREATE TRIGGER f BEFORE INSERT ON t1 FOR EACH ROW 1317BEGIN 1318 UPDATE A SET `pk`=1 WHERE `pk`=0 ; 1319END ;| 1320 1321--delimiter ; 1322 1323DROP TABLE t1; 1324DROP TABLE B; 1325 1326--echo # 1327--echo # Bug #47107 assert in notify_shared_lock on incorrect 1328--echo # CREATE TABLE , HANDLER 1329--echo # 1330 1331--disable_warnings 1332DROP TABLE IF EXISTS t1; 1333--enable_warnings 1334 1335CREATE TABLE t1(f1 integer); 1336 1337--echo # The following CREATE TABLEs before gave an assert. 1338 1339HANDLER t1 OPEN AS A; 1340--error ER_TABLE_EXISTS_ERROR 1341CREATE TABLE t1 SELECT 1 AS f2; 1342 1343HANDLER t1 OPEN AS A; 1344--error ER_TABLE_EXISTS_ERROR 1345CREATE TABLE t1(f1 integer); 1346 1347CREATE TABLE t2(f1 integer); 1348HANDLER t1 OPEN AS A; 1349--error ER_TABLE_EXISTS_ERROR 1350CREATE TABLE t1 LIKE t2; 1351 1352DROP TABLE t2; 1353DROP TABLE t1; 1354 1355--echo # 1356--echo # Bug #48800 CREATE TABLE t...SELECT fails if t is a 1357--echo # temporary table 1358--echo # 1359 1360CREATE TEMPORARY TABLE t1 (a INT); 1361CREATE TABLE t1 (a INT); 1362 1363CREATE TEMPORARY TABLE t2 (a INT); 1364CREATE VIEW t2 AS SELECT 1; 1365 1366CREATE TABLE t3 (a INT); 1367CREATE TEMPORARY TABLE t3 SELECT 1; 1368 1369CREATE TEMPORARY TABLE t4 (a INT); 1370CREATE TABLE t4 AS SELECT 1; 1371 1372DROP TEMPORARY TABLE t1, t2, t3, t4; 1373DROP TABLE t1, t3, t4; 1374DROP VIEW t2; 1375 1376--echo # 1377--echo # Bug #49193 CREATE TABLE reacts differently depending 1378--echo # on whether data is selected or not 1379--echo # 1380 1381CREATE TEMPORARY TABLE t2 (ID INT); 1382INSERT INTO t2 VALUES (1),(2),(3); 1383 1384# Case 1 -- did not fail 1385CREATE TEMPORARY TABLE t1 (ID INT); 1386CREATE TABLE IF NOT EXISTS t1 (ID INT); 1387INSERT INTO t1 SELECT * FROM t2; 1388SELECT * FROM t1; 1389DROP TEMPORARY TABLE t1; 1390SELECT * FROM t1; 1391 1392DROP TABLE t1; 1393 1394# Case 2 -- The DROP TABLE t1 failed with 1395# Table 'test.t1' doesn't exist in the SELECT * 1396# as the (permanent) table was not created 1397CREATE TEMPORARY TABLE t1 (ID INT); 1398CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t2; 1399SELECT * FROM t1; 1400DROP TEMPORARY TABLE t1; 1401SELECT * FROM t1; 1402 1403DROP TABLE t1; 1404 1405# Case 3 -- The CREATE TABLE failed with 1406# Table 't1' already exists 1407CREATE TEMPORARY TABLE t1 (ID INT); 1408CREATE TABLE t1 SELECT * FROM t2; 1409SELECT * FROM t1; 1410DROP TEMPORARY TABLE t1; 1411SELECT * FROM t1; 1412 1413DROP TABLE t1; 1414 1415DROP TEMPORARY TABLE t2; 1416 1417 1418--echo # 1419--echo # Bug #22909 "Using CREATE ... LIKE is possible to create field 1420--echo # with invalid default value" 1421--echo # 1422--echo # Altough original bug report suggests to use older version of MySQL 1423--echo # for producing .FRM with invalid defaults we use sql_mode to achieve 1424--echo # the same effect. 1425--disable_warnings 1426drop tables if exists t1, t2; 1427--enable_warnings 1428--echo # Attempt to create table with invalid default should fail in normal mode 1429--error ER_INVALID_DEFAULT 1430create table t1 (dt datetime default '2008-02-31 00:00:00'); 1431set @old_mode= @@sql_mode; 1432set @@sql_mode='ALLOW_INVALID_DATES'; 1433--echo # The same should be possible in relaxed mode 1434create table t1 (dt datetime default '2008-02-31 00:00:00'); 1435set @@sql_mode= @old_mode; 1436--echo # In normal mode attempt to create copy of table with invalid 1437--echo # default should fail 1438--error ER_INVALID_DEFAULT 1439create table t2 like t1; 1440set @@sql_mode='ALLOW_INVALID_DATES'; 1441--echo # But should work in relaxed mode 1442create table t2 like t1; 1443--echo # Check that table definitions match 1444show create table t1; 1445show create table t2; 1446set @@sql_mode= @old_mode; 1447drop tables t1, t2; 1448# 1449# Bug#47132 CREATE TABLE.. SELECT.. data not inserted if table 1450# is view over multiple tables 1451# 1452 1453CREATE TABLE t1 (id int); 1454CREATE TABLE t2 (id int); 1455INSERT INTO t1 VALUES (1), (1); 1456INSERT INTO t2 VALUES (2), (2); 1457 1458CREATE VIEW v1 AS SELECT id FROM t2; 1459CREATE TABLE IF NOT EXISTS v1(a int, b int) SELECT id, id FROM t1; 1460SHOW CREATE TABLE v1; 1461SELECT * FROM t2; 1462SELECT * FROM v1; 1463DROP VIEW v1; 1464 1465CREATE TEMPORARY TABLE tt1 AS SELECT id FROM t2; 1466CREATE TEMPORARY TABLE IF NOT EXISTS tt1(a int, b int) SELECT id, id FROM t1; 1467SELECT * FROM t2; 1468SELECT * FROM tt1; 1469DROP TEMPORARY TABLE tt1; 1470 1471DROP TABLE t1, t2; 1472 1473 1474--echo # 1475--echo # WL#5370 "Changing 'CREATE TABLE IF NOT EXISTS ... SELECT' 1476--echo # behaviour. 1477--echo # 1478 1479--echo # 1480--echo # 1. Basic case: a base table. 1481--echo # 1482 1483create table if not exists t1 (a int) select 1 as a; 1484select * from t1; 1485--error ER_TABLE_EXISTS_ERROR 1486create table t1 (a int) select 2 as a; 1487select * from t1; 1488--echo # Produces an essential warning ER_TABLE_EXISTS. 1489create table if not exists t1 (a int) select 2 as a; 1490--echo # No new data in t1. 1491select * from t1; 1492drop table t1; 1493 1494--echo # 1495--echo # 2. A temporary table. 1496--echo # 1497 1498create temporary table if not exists t1 (a int) select 1 as a; 1499select * from t1; 1500--error ER_TABLE_EXISTS_ERROR 1501create temporary table t1 (a int) select 2 as a; 1502select * from t1; 1503--echo # An essential warning. 1504create temporary table if not exists t1 (a int) select 2 as a; 1505--echo # No new data in t1. 1506select * from t1; 1507drop temporary table t1; 1508 1509--echo # 1510--echo # 3. Creating a base table in presence of a temporary table. 1511--echo # 1512 1513create table t1 (a int); 1514--echo # Create a view for convenience of querying t1 shadowed by a temp. 1515create view v1 as select a from t1; 1516drop table t1; 1517create temporary table t1 (a int) select 1 as a; 1518create table if not exists t1 (a int) select 2 as a; 1519select * from t1; 1520select * from v1; 1521--echo # Note: an essential warning. 1522create table if not exists t1 (a int) select 3 as a; 1523select * from t1; 1524select * from v1; 1525drop temporary table t1; 1526select * from t1; 1527drop view v1; 1528drop table t1; 1529 1530--echo # 1531--echo # 4. Creating a temporary table in presence of a base table. 1532--echo # 1533 1534create table t1 (a int) select 1 as a; 1535create temporary table if not exists t1 select 2 as a; 1536select * from t1; 1537--echo # Note: an essential warning. 1538create temporary table if not exists t1 select 3 as a; 1539select * from t1; 1540drop temporary table t1; 1541select * from t1; 1542drop table t1; 1543 1544--echo # 1545--echo # 5. Creating a base table in presence of an updatable view. 1546--echo # 1547create table t2 (a int unique); 1548create view t1 as select a from t2; 1549insert into t1 (a) values (1); 1550--error ER_TABLE_EXISTS_ERROR 1551create table t1 (a int); 1552--echo # Note: an essential warning. 1553create table if not exists t1 (a int); 1554--error ER_TABLE_EXISTS_ERROR 1555create table t1 (a int) select 2 as a; 1556select * from t1; 1557--echo # Note: an essential warning. 1558create table if not exists t1 (a int) select 2 as a; 1559select * from t1; 1560select * from t2; 1561create temporary table if not exists t1 (a int) select 3 as a; 1562select * from t1; 1563select * from t2; 1564--echo # Note: an essential warning. 1565create temporary table if not exists t1 (a int) select 4 as a; 1566select * from t1; 1567select * from t2; 1568drop temporary table t1; 1569 1570--echo # 1571--echo # Repeating the test with a non-updatable view. 1572--echo # 1573drop view t1; 1574create view t1 as select a + 5 as a from t2; 1575--error ER_NONUPDATEABLE_COLUMN 1576insert into t1 (a) values (1); 1577--error ER_NONUPDATEABLE_COLUMN 1578update t1 set a=3 where a=2; 1579 1580--error ER_TABLE_EXISTS_ERROR 1581create table t1 (a int); 1582--echo # Note: an essential warning. 1583create table if not exists t1 (a int); 1584--error ER_TABLE_EXISTS_ERROR 1585create table t1 (a int) select 2 as a; 1586select * from t1; 1587--echo # Note: an essential warning. 1588create table if not exists t1 (a int) select 2 as a; 1589select * from t1; 1590select * from t2; 1591create temporary table if not exists t1 (a int) select 3 as a; 1592select * from t1; 1593select * from t2; 1594--echo # Note: an essential warning. 1595create temporary table if not exists t1 (a int) select 4 as a; 1596select * from t1; 1597select * from t2; 1598drop temporary table t1; 1599drop view t1; 1600drop table t2; 1601 1602--echo # 1603--echo # Repeating the test with a view select a constant number 1604--echo # 1605create view t1 as select 1 as a; 1606--error ER_NON_INSERTABLE_TABLE 1607insert into t1 (a) values (1); 1608--error ER_NON_UPDATABLE_TABLE 1609update t1 set a=3 where a=2; 1610 1611--error ER_TABLE_EXISTS_ERROR 1612create table t1 (a int); 1613--echo # Note: an essential warning. 1614create table if not exists t1 (a int); 1615--error ER_TABLE_EXISTS_ERROR 1616create table t1 (a int) select 2 as a; 1617select * from t1; 1618--echo # Note: an essential warning. 1619create table if not exists t1 (a int) select 2 as a; 1620select * from t1; 1621create temporary table if not exists t1 (a int) select 3 as a; 1622select * from t1; 1623--echo # Note: an essential warning. 1624create temporary table if not exists t1 (a int) select 4 as a; 1625select * from t1; 1626drop temporary table t1; 1627drop view t1; 1628 1629 1630--echo # 1631--echo # 6. Test of unique_table(). 1632--echo # 1633 1634create table t1 (a int) select 1 as a; 1635create temporary table if not exists t1 (a int) select * from t1; 1636--error ER_CANT_REOPEN_TABLE 1637create temporary table if not exists t1 (a int) select * from t1; 1638select * from t1; 1639drop temporary table t1; 1640select * from t1; 1641drop table t1; 1642create temporary table t1 (a int) select 1 as a; 1643create table if not exists t1 (a int) select * from t1; 1644create table if not exists t1 (a int) select * from t1; 1645select * from t1; 1646drop temporary table t1; 1647select * from t1; 1648drop table t1; 1649--error ER_NO_SUCH_TABLE 1650create table if not exists t1 (a int) select * from t1; 1651 1652--echo # 1653--echo # 7. Test of non-matching columns, REPLACE and IGNORE. 1654--echo # 1655 1656create table t1 (a int) select 1 as b, 2 as c; 1657select * from t1; 1658drop table t1; 1659create table if not exists t1 (a int, b date, c date) select 1 as b, 2 as c; 1660select * from t1; 1661drop table t1; 1662set @@session.sql_mode=default; 1663--error ER_TRUNCATED_WRONG_VALUE 1664create table if not exists t1 (a int, b date, c date) select 1 as b, 2 as c; 1665--error ER_NO_SUCH_TABLE 1666select * from t1; 1667--error ER_TRUNCATED_WRONG_VALUE 1668create table if not exists t1 (a int, b date, c date) 1669 replace select 1 as b, 2 as c; 1670--error ER_NO_SUCH_TABLE 1671select * from t1; 1672 1673create table if not exists t1 (a int, b date, c date) 1674 ignore select 1 as b, 2 as c; 1675select * from t1; 1676drop table t1; 1677 1678create table if not exists t1 (a int unique, b int) 1679 replace select 1 as a, 1 as b union select 1 as a, 2 as b; 1680select * from t1; 1681drop table t1; 1682create table if not exists t1 (a int unique, b int) 1683 ignore select 1 as a, 1 as b union select 1 as a, 2 as b; 1684select * from t1; 1685drop table t1; 1686--echo # 1687 1688--echo # 1689--echo # WL#5576 Prohibit CREATE TABLE ... SELECT to modify other tables 1690--echo # 1691 1692delimiter |; 1693create function f() 1694returns int 1695begin 1696insert into t2 values(1); 1697return 1; 1698end| 1699delimiter ;| 1700 1701--echo # 1702--echo # 1. The function updates a base table 1703--echo # 1704create table t2(c1 int); 1705 1706--error ER_CANT_UPDATE_TABLE_IN_CREATE_TABLE_SELECT 1707create table t1 select f(); 1708--error ER_CANT_UPDATE_TABLE_IN_CREATE_TABLE_SELECT 1709create temporary table t1 select f(); 1710 1711 1712drop table t2; 1713 1714--echo # 1715--echo # 2. The function updates a view which derives from a base table 1716--echo # 1717create table t3(c1 int); 1718create view t2 as select c1 from t3; 1719 1720--error ER_CANT_UPDATE_TABLE_IN_CREATE_TABLE_SELECT 1721create table t1 select f(); 1722--error ER_CANT_UPDATE_TABLE_IN_CREATE_TABLE_SELECT 1723create temporary table t1 select f(); 1724 1725drop view t2; 1726 1727--echo # 1728--echo # 3. The function updates a view which derives from two base tables 1729--echo # 1730create table t4(c1 int); 1731create view t2 as select t3.c1 as c1 from t3, t4; 1732 1733--error ER_CANT_UPDATE_TABLE_IN_CREATE_TABLE_SELECT 1734create table t1 select f(); 1735--error ER_CANT_UPDATE_TABLE_IN_CREATE_TABLE_SELECT 1736create temporary table t1 select f(); 1737 1738drop view t2; 1739drop tables t3, t4; 1740 1741--echo # 1742--echo # 4. The function updates a view which selects a constant number 1743--echo # 1744create view t2 as select 1; 1745 1746--error ER_CANT_UPDATE_TABLE_IN_CREATE_TABLE_SELECT 1747create table t1 select f(); 1748--error ER_CANT_UPDATE_TABLE_IN_CREATE_TABLE_SELECT 1749create temporary table t1 select f(); 1750 1751drop view t2; 1752drop function f; 1753 1754--echo # 1755--echo # BUG#11762377 - 54963: ENHANCE THE ERROR MESSAGE TO 1756--echo # REDUCE USER CONFUSION 1757--echo # 1758 1759--error ER_TOO_BIG_ROWSIZE 1760CREATE TABLE t1 (v varchar(65535)); 1761 1762--echo # 1763--echo # Bug#11746295 - 25168: "INCORRECT TABLE NAME" INSTEAD OF "IDENTIFIER TOO 1764--echo # LONG" IF TABLE NAME > 64 CHARACTERS 1765--echo # 1766 1767--error ER_TOO_LONG_IDENT 1768CREATE TABLE t01234567890123456789012345678901234567890123456789012345678901234567890123456789(a int); 1769--error ER_TOO_LONG_IDENT 1770CREATE DATABASE t01234567890123456789012345678901234567890123456789012345678901234567890123456789; 1771 1772--echo # 1773--echo # Bug #20573701 DROP DATABASE ASSERT ON DEBUG WHEN OTHER FILES PRESENT IN 1774--echo # DB FOLDER. 1775--echo # 1776let $MYSQLD_DATADIR= `SELECT @@datadir`; 1777# Case 1: A database with no tables and has an unrelated file in it's database 1778# directory. Dropping such database should throw ER_DB_DROP_RMDIR 1779# error. 1780CREATE DATABASE db_with_no_tables_and_an_unrelated_file_in_data_directory; 1781--write_file $MYSQLD_DATADIR/db_with_no_tables_and_an_unrelated_file_in_data_directory/intruder.txt 1782EOF 1783--replace_result $MYSQLD_DATADIR ./ \\ / 1784--error ER_DB_DROP_RMDIR 1785DROP DATABASE db_with_no_tables_and_an_unrelated_file_in_data_directory; 1786# Cleanup 1787--remove_file $MYSQLD_DATADIR/db_with_no_tables_and_an_unrelated_file_in_data_directory/intruder.txt 1788DROP DATABASE db_with_no_tables_and_an_unrelated_file_in_data_directory; 1789 1790# Case 2: A database with tables in it and has an unrelated file in it's database 1791# directory. Dropping such database should throw ER_DB_DROP_RMDIR 1792# error. 1793CREATE DATABASE db_with_tables_and_an_unrelated_file_in_data_directory; 1794--write_file $MYSQLD_DATADIR/db_with_tables_and_an_unrelated_file_in_data_directory/intruder.txt 1795EOF 1796--replace_result $MYSQLD_DATADIR ./ \\ / 1797--error ER_DB_DROP_RMDIR 1798DROP DATABASE db_with_tables_and_an_unrelated_file_in_data_directory; 1799# Cleanup 1800--remove_file $MYSQLD_DATADIR/db_with_tables_and_an_unrelated_file_in_data_directory/intruder.txt 1801DROP DATABASE db_with_tables_and_an_unrelated_file_in_data_directory; 1802 1803# Case 3: A database (fakely created using mkdir) and has an unrelated file in it's database 1804# directory. Dropping such database should throw ER_DB_DROP_RMDIR 1805# error. 1806--mkdir $MYSQLD_DATADIR/db_created_with_mkdir_and_an_unrelated_file_in_data_directory 1807--write_file $MYSQLD_DATADIR/db_created_with_mkdir_and_an_unrelated_file_in_data_directory/intruder.txt 1808EOF 1809--replace_result $MYSQLD_DATADIR ./ \\ / 1810--error ER_DB_DROP_RMDIR 1811DROP DATABASE db_created_with_mkdir_and_an_unrelated_file_in_data_directory; 1812# Cleanup 1813--remove_file $MYSQLD_DATADIR/db_created_with_mkdir_and_an_unrelated_file_in_data_directory/intruder.txt 1814DROP DATABASE db_created_with_mkdir_and_an_unrelated_file_in_data_directory; 1815 1816 1817--echo # 1818--echo # BUG 27516741 - MYSQL SERVER DOES NOT WRITE INNODB ROW_TYPE 1819--echo # TO .FRM FILE WHEN DEFAULT USED 1820 1821--echo # Set up. 1822SET @saved_innodb_default_row_format= @@global.innodb_default_row_format; 1823SET @saved_show_create_table_verbosity= @@session.show_create_table_verbosity; 1824 1825--echo # Current InnoDB default row format and 'show_create_table_verbosity' 1826--echo # values respectively. 1827SELECT @@global.innodb_default_row_format; 1828SELECT @@session.show_create_table_verbosity; 1829 1830CREATE TABLE t1(fld1 INT) ENGINE= InnoDB; 1831CREATE TABLE t2(fld1 INT) ENGINE= InnoDB, ROW_FORMAT= DEFAULT; 1832SET GLOBAL innodb_default_row_format= 'COMPACT'; 1833CREATE TABLE t3(fld1 INT) ENGINE= InnoDB; 1834CREATE TABLE t4(fl1 INT) ENGINE= InnoDB, ROW_FORMAT= COMPRESSED; 1835 1836--echo # Test without show_create_table_verbosity enabled. 1837--echo # Row format used is not displayed for all tables 1838--echo # except t4 where it is explicitly specified. 1839SHOW CREATE TABLE t1; 1840SHOW CREATE TABLE t2; 1841SHOW CREATE TABLE t3; 1842SHOW CREATE TABLE t4; 1843 1844--echo # Test with show_create_table_verbosity enabled. 1845--echo # Row format used is displayed for all tables. 1846SET SESSION show_create_table_verbosity= ON; 1847SHOW CREATE TABLE t1; 1848SHOW CREATE TABLE t2; 1849SHOW CREATE TABLE t3; 1850SHOW CREATE TABLE t4; 1851 1852SET GLOBAL innodb_default_row_format= 'DYNAMIC'; 1853SET SESSION show_create_table_verbosity= OFF; 1854 1855--echo # Test with corresponding temporary tables. 1856CREATE TEMPORARY TABLE t1(fld1 INT) ENGINE= InnoDB; 1857CREATE TEMPORARY TABLE t2(fld1 INT) ENGINE= InnoDB, ROW_FORMAT= DEFAULT; 1858SET GLOBAL innodb_default_row_format= 'COMPACT'; 1859CREATE TEMPORARY TABLE t3(fld1 INT) ENGINE= InnoDB; 1860CREATE TEMPORARY TABLE t4(fl1 INT) ENGINE= InnoDB, ROW_FORMAT= COMPRESSED; 1861 1862--echo # Test without show_create_table_verbosity enabled. 1863--echo # Row format used is not displayed for all tables 1864--echo # except t4 where it is explicitly specified. 1865SHOW CREATE TABLE t1; 1866SHOW CREATE TABLE t2; 1867SHOW CREATE TABLE t3; 1868SHOW CREATE TABLE t4; 1869 1870--echo # Test with show_create_table_verbosity enabled. 1871--echo # Row format used is displayed for all tables. 1872SET SESSION show_create_table_verbosity= ON; 1873SHOW CREATE TABLE t1; 1874SHOW CREATE TABLE t2; 1875SHOW CREATE TABLE t3; 1876SHOW CREATE TABLE t4; 1877 1878--echo # Clean up. 1879DROP TABLE t1, t2, t3, t4; 1880DROP TABLE t1, t2, t3, t4; 1881SET GLOBAL innodb_default_row_format= @saved_innodb_default_row_format; 1882SET SESSION show_create_table_verbosity= @saved_show_create_table_verbosity; 1883 1884 1885--echo # 1886--echo # Bug#28022129: NOW() DOESN?T HONOR NO_ZERO_DATE SQL_MODE 1887--echo # 1888 1889SET @saved_mode= @@sql_mode; 1890 1891CREATE TABLE t1(fld1 int); 1892 1893--echo # NO_ZERO_DATE and STRICT SQL mode. 1894 1895CREATE TABLE t2 SELECT fld1, CURDATE() fld2 FROM t1; 1896CREATE TABLE t3 AS SELECT now(); 1897 1898--echo # With patch, zero date is not generated as default. 1899SHOW CREATE TABLE t2; 1900SHOW CREATE TABLE t3; 1901DROP TABLE t2, t3; 1902 1903SET SQL_MODE= "NO_ZERO_DATE"; 1904 1905--echo # NO_ZERO_DATE SQL mode. 1906CREATE TABLE t2 SELECT fld1, CURDATE() fld2 FROM t1; 1907CREATE TABLE t3 AS SELECT now(); 1908 1909--echo # Zero date is generated as default in non strict mode. 1910SHOW CREATE TABLE t2; 1911SHOW CREATE TABLE t3; 1912DROP TABLE t1, t2, t3; 1913 1914SET SQL_MODE= DEFAULT; 1915 1916--echo # Test cases added for coverage. 1917 1918CREATE TABLE t1(fld1 DATETIME NOT NULL DEFAULT '1111:11:11'); 1919 1920--echo # CREATE TABLE..SELECT using fields of another table. 1921CREATE TABLE t2 AS SELECT * FROM t1; 1922--echo # Default value is copied from the source table column. 1923SHOW CREATE TABLE t2; 1924 1925DROP TABLE t1, t2; 1926 1927--echo # CREATE TABLE..SELECT based on trigger fields. 1928CREATE TABLE t1 (fld1 INT, fld2 DATETIME DEFAULT '1211:1:1'); 1929 1930DELIMITER |; 1931CREATE TRIGGER t1_bi BEFORE INSERT ON t1 1932FOR EACH ROW 1933BEGIN 1934 CREATE TEMPORARY TABLE t2 AS SELECT NEW.fld1, NEW.fld2; 1935END 1936| 1937DELIMITER ;| 1938 1939INSERT INTO t1 VALUES (1, '1111:11:11'); 1940SHOW CREATE TABLE t2; 1941 1942DROP TABLE t1; 1943DROP TEMPORARY TABLE t2; 1944SET SQL_MODE= @saved_mode; 1945