1SET TIME_ZONE = "+00:00"; 2 3--echo # 4--echo # Test of errors for column data types that dont support function 5--echo # defaults. 6--echo # 7 8eval CREATE OR REPLACE TABLE t1( a BIT DEFAULT $current_timestamp ); 9eval CREATE OR REPLACE TABLE t1( a TINYINT DEFAULT $current_timestamp ); 10eval CREATE OR REPLACE TABLE t1( a SMALLINT DEFAULT $current_timestamp ); 11eval CREATE OR REPLACE TABLE t1( a MEDIUMINT DEFAULT $current_timestamp ); 12eval CREATE OR REPLACE TABLE t1( a INT DEFAULT $current_timestamp ); 13eval CREATE OR REPLACE TABLE t1( a BIGINT DEFAULT $current_timestamp ); 14eval CREATE OR REPLACE TABLE t1( a FLOAT DEFAULT $current_timestamp ); 15eval CREATE OR REPLACE TABLE t1( a DECIMAL DEFAULT $current_timestamp ); 16eval CREATE OR REPLACE TABLE t1( a DATE DEFAULT $current_timestamp ); 17eval CREATE OR REPLACE TABLE t1( a TIME DEFAULT $current_timestamp ); 18eval CREATE OR REPLACE TABLE t1( a YEAR DEFAULT $current_timestamp ); 19 20--error ER_INVALID_ON_UPDATE 21eval CREATE OR REPLACE TABLE t1( a BIT ON UPDATE $current_timestamp ); 22--error ER_INVALID_ON_UPDATE 23eval CREATE OR REPLACE TABLE t1( a TINYINT ON UPDATE $current_timestamp ); 24--error ER_INVALID_ON_UPDATE 25eval CREATE OR REPLACE TABLE t1( a SMALLINT ON UPDATE $current_timestamp ); 26--error ER_INVALID_ON_UPDATE 27eval CREATE OR REPLACE TABLE t1( a MEDIUMINT ON UPDATE $current_timestamp ); 28--error ER_INVALID_ON_UPDATE 29eval CREATE OR REPLACE TABLE t1( a INT ON UPDATE $current_timestamp ); 30--error ER_INVALID_ON_UPDATE 31eval CREATE OR REPLACE TABLE t1( a BIGINT ON UPDATE $current_timestamp ); 32--error ER_INVALID_ON_UPDATE 33eval CREATE OR REPLACE TABLE t1( a FLOAT ON UPDATE $current_timestamp ); 34--error ER_INVALID_ON_UPDATE 35eval CREATE OR REPLACE TABLE t1( a DECIMAL ON UPDATE $current_timestamp ); 36--error ER_INVALID_ON_UPDATE 37eval CREATE OR REPLACE TABLE t1( a DATE ON UPDATE $current_timestamp ); 38--error ER_INVALID_ON_UPDATE 39eval CREATE OR REPLACE TABLE t1( a TIME ON UPDATE $current_timestamp ); 40--error ER_INVALID_ON_UPDATE 41eval CREATE OR REPLACE TABLE t1( a YEAR ON UPDATE $current_timestamp ); 42 43drop table if exists t1; 44 45--echo # 46--echo # Test that the default clause behaves like NOW() regarding time zones. 47--echo # 48eval CREATE TABLE t1 ( 49 a $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp, 50 b $timestamp NOT NULL DEFAULT $current_timestamp, 51 c $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE $current_timestamp, 52 d $timestamp NULL, 53 e $datetime DEFAULT $current_timestamp ON UPDATE $current_timestamp, 54 f $datetime DEFAULT $current_timestamp, 55 g $datetime ON UPDATE $current_timestamp, 56 h $datetime 57); 58 59--echo # 2011-09-27 14:11:08 UTC 60SET TIMESTAMP = 1317132668.654321; 61SET @old_time_zone = @@TIME_ZONE; 62SET TIME_ZONE = "+05:00"; 63 64eval INSERT INTO t1( d, h ) VALUES ( $now, $now ); 65SELECT * FROM t1; 66 67--echo # 1989-05-13 01:02:03 68SET TIMESTAMP = 611017323.543212; 69eval UPDATE t1 SET d = $now, h = $now; 70SELECT * FROM t1; 71 72SET TIME_ZONE = @old_time_zone; 73DROP TABLE t1; 74 75--echo # 76--echo # Test of several TIMESTAMP columns with different function defaults. 77--echo # 78eval CREATE TABLE t1 ( 79 a $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp, 80 b $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp, 81 c $timestamp NOT NULL DEFAULT $current_timestamp, 82 d $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE $current_timestamp, 83 e $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE $current_timestamp, 84 f INT 85); 86 87--echo # 2011-04-19 07:22:02 UTC 88SET TIMESTAMP = 1303197722.534231; 89 90INSERT INTO t1 ( f ) VALUES (1); 91SELECT * FROM t1; 92 93--echo # 2011-04-19 07:23:18 UTC 94SET TIMESTAMP = 1303197798.132435; 95 96UPDATE t1 SET f = 2; 97SELECT * FROM t1; 98 99DROP TABLE t1; 100 101--echo # 102--echo # Test of inserted values out of order. 103--echo # 104eval CREATE TABLE t1 ( 105 a INT, 106 b $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp, 107 c $timestamp NOT NULL DEFAULT $current_timestamp, 108 d $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE $current_timestamp, 109 e $timestamp NULL, 110 f $datetime, 111 g $datetime DEFAULT $current_timestamp, 112 h $datetime ON UPDATE $current_timestamp, 113 i $datetime DEFAULT $current_timestamp ON UPDATE $current_timestamp, 114 j INT 115); 116 117--echo # 2011-04-19 07:22:02 UTC 118SET TIMESTAMP = 1303197722.534231; 119 120INSERT INTO t1 ( j, a ) VALUES ( 1, 1 ); 121SELECT * FROM t1; 122 123DROP TABLE t1; 124 125--echo # 126--echo # Test of ON DUPLICATE KEY UPDATE 127--echo # 128eval CREATE TABLE t1 ( 129 a INT PRIMARY KEY, 130 b INT, 131 c $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp, 132 d $timestamp NOT NULL DEFAULT $current_timestamp, 133 e $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE $current_timestamp, 134 f $timestamp NOT NULL DEFAULT '1986-09-27 03:00:00.098765', 135 g $timestamp NULL, 136 h $datetime DEFAULT $current_timestamp ON UPDATE $current_timestamp, 137 i $datetime DEFAULT $current_timestamp, 138 j $datetime ON UPDATE $current_timestamp, 139 k $datetime NULL, 140 l $datetime DEFAULT '1986-09-27 03:00:00.098765' 141); 142 143--echo # 1977-12-21 23:00:00 UTC 144SET TIMESTAMP = 251593200.192837; 145INSERT INTO t1(a) VALUES (1) ON DUPLICATE KEY UPDATE b = 2; 146SELECT * FROM t1; 147 148--echo # 1975-05-21 23:00:00 UTC 149SET TIMESTAMP = 169945200.918273; 150INSERT INTO t1(a) VALUES (1) ON DUPLICATE KEY UPDATE b = 2; 151SELECT * FROM t1; 152 153--echo # 1973-08-14 09:11:22 UTC 154SET TIMESTAMP = 114167482.534231; 155INSERT INTO t1(a) VALUES (2) ON DUPLICATE KEY UPDATE b = 2; 156SELECT * FROM t1; 157 158DROP TABLE t1; 159 160eval CREATE TABLE t1 ( a INT PRIMARY KEY, b INT, c $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp ); 161 162--echo # 2011-04-19 07:23:18 UTC 163SET TIMESTAMP = 1303197798.945156; 164 165INSERT INTO t1 VALUES 166 (1, 0, '2001-01-01 01:01:01.111111'), 167 (2, 0, '2002-02-02 02:02:02.222222'), 168 (3, 0, '2003-03-03 03:03:03.333333'); 169SELECT * FROM t1; 170 171UPDATE t1 SET b = 2, c = c WHERE a = 2; 172SELECT * FROM t1; 173 174INSERT INTO t1 (a) VALUES (4); 175SELECT * FROM t1; 176 177UPDATE t1 SET c = '2004-04-04 04:04:04.444444' WHERE a = 4; 178SELECT * FROM t1; 179 180INSERT INTO t1 ( a ) VALUES ( 3 ), ( 5 ) ON DUPLICATE KEY UPDATE b = 3, c = c; 181SELECT * FROM t1; 182 183INSERT INTO t1 (a, c) VALUES 184 (4, '2004-04-04 00:00:00.444444'), 185 (6, '2006-06-06 06:06:06.666666') 186ON DUPLICATE KEY UPDATE b = 4; 187 188SELECT * FROM t1; 189 190DROP TABLE t1; 191 192 193--echo # 194--echo # Test of REPLACE INTO executed as UPDATE. 195--echo # 196eval CREATE TABLE t1 ( 197 a INT PRIMARY KEY, 198 b $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp, 199 c $datetime DEFAULT $current_timestamp ON UPDATE $current_timestamp, 200 d $timestamp NOT NULL DEFAULT $current_timestamp, 201 e $datetime DEFAULT $current_timestamp, 202 f $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE $current_timestamp, 203 g $datetime ON UPDATE $current_timestamp, 204 h $timestamp NULL, 205 i $datetime 206); 207 208--echo # 1970-09-21 09:11:12 UTC 209SET TIMESTAMP = 22756272.163584; 210 211REPLACE INTO t1 ( a ) VALUES ( 1 ); 212SELECT * FROM t1; 213 214--echo # 1970-11-10 14:16:17 UTC 215SET TIMESTAMP = 27094577.852954; 216 217 218REPLACE INTO t1 ( a ) VALUES ( 1 ); 219SELECT * FROM t1; 220 221DROP TABLE t1; 222 223 224--echo # 225--echo # Test of insertion of NULL, DEFAULT and an empty row for DEFAULT 226--echo # CURRENT_TIMESTAMP. 227--echo # 228eval CREATE TABLE t1 ( 229 a $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp, 230 b $datetime DEFAULT $current_timestamp, 231 c INT 232); 233 234--echo # 2011-04-20 09:53:41 UTC 235SET TIMESTAMP = 1303293221.163578; 236 237INSERT INTO t1 VALUES (NULL, NULL, 1), (DEFAULT, DEFAULT, 2); 238INSERT INTO t1 ( a, b, c ) VALUES (NULL, NULL, 3), (DEFAULT, DEFAULT, 4); 239SELECT * FROM t1; 240 241SET TIME_ZONE = "+03:00"; 242SELECT * FROM t1; 243SET TIME_ZONE = "+00:00"; 244 245DROP TABLE t1; 246 247--echo # 2011-04-20 07:05:39 UTC 248SET TIMESTAMP = 1303283139.195624; 249eval CREATE TABLE t1 ( 250 a $timestamp NOT NULL DEFAULT '2010-10-11 12:34:56' ON UPDATE $current_timestamp, 251 b $datetime DEFAULT '2010-10-11 12:34:56' 252); 253 254INSERT INTO t1 VALUES (NULL, NULL), (DEFAULT, DEFAULT); 255INSERT INTO t1 ( a, b ) VALUES (NULL, NULL), (DEFAULT, DEFAULT); 256SELECT * FROM t1; 257 258DROP TABLE t1; 259 260--echo # 2011-04-20 09:53:41 UTC 261SET TIMESTAMP = 1303293221.136952; 262 263eval CREATE TABLE t1 ( 264a $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp, 265b $timestamp NOT NULL DEFAULT $current_timestamp, 266c $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE $current_timestamp, 267d $timestamp NOT NULL DEFAULT '1986-09-27 03:00:00.098765', 268e $timestamp NULL, 269f $datetime DEFAULT $current_timestamp ON UPDATE $current_timestamp, 270g $datetime DEFAULT $current_timestamp, 271h $datetime ON UPDATE $current_timestamp, 272i $datetime NULL, 273j $datetime DEFAULT '1986-09-27 03:00:00.098765' 274); 275 276INSERT INTO t1 VALUES (); 277 278INSERT INTO t1 SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL; 279 280SELECT * FROM t1; 281 282DROP TABLE t1; 283 284--echo # 285--echo # Test of multiple-table UPDATE for DEFAULT CURRENT_TIMESTAMP 286--echo # 287eval CREATE TABLE t1 ( 288 a $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp, 289 b $datetime DEFAULT $current_timestamp, 290 c INT 291); 292 293INSERT INTO t1 ( c ) VALUES (1); 294SELECT * FROM t1; 295 296--echo # 2011-04-20 17:06:13 UTC 297SET TIMESTAMP = 1303311973.163587; 298 299UPDATE t1 t11, t1 t12 SET t11.c = 1; 300SELECT * FROM t1; 301 302UPDATE t1 t11, t1 t12 SET t11.c = 2; 303 304SELECT * FROM t1; 305 306DROP TABLE t1; 307 308eval CREATE TABLE t1 ( 309 a $timestamp NOT NULL DEFAULT $current_timestamp, 310 b $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE $current_timestamp, 311 c $datetime DEFAULT $current_timestamp, 312 d $datetime ON UPDATE $current_timestamp, 313 e INT 314); 315 316eval CREATE TABLE t2 ( 317 f INT, 318 g $datetime ON UPDATE $current_timestamp, 319 h $datetime DEFAULT $current_timestamp, 320 i $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE $current_timestamp, 321 j $timestamp NOT NULL DEFAULT $current_timestamp 322); 323 324--echo # 1995-03-11 00:02:03 UTC 325SET TIMESTAMP = 794880123.195676; 326 327INSERT INTO t1 ( e ) VALUES ( 1 ), ( 2 ); 328 329INSERT INTO t2 ( f ) VALUES ( 1 ), ( 2 ); 330 331SELECT * FROM t1; 332SELECT * FROM t2; 333 334--echo # 1980-12-13 02:02:01 UTC 335SET TIMESTAMP = 345520921.196755; 336 337UPDATE t1, t2 SET t1.e = 3, t2.f = 4; 338 339SELECT * FROM t1; 340SELECT * FROM t2; 341 342DROP TABLE t1, t2; 343 344--echo # 345--echo # Test of multiple table update with temporary table and on the fly. 346--echo # 347eval CREATE TABLE t1 ( 348 a $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE $current_timestamp, 349 b $datetime ON UPDATE $current_timestamp, 350 c INT, 351 d INT 352); 353 354eval CREATE TABLE t2 ( 355 a $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE $current_timestamp, 356 b $datetime ON UPDATE $current_timestamp, 357 c INT KEY, 358 d INT 359); 360 361INSERT INTO t1 ( c ) VALUES (1), (2); 362INSERT INTO t2 ( c ) VALUES (1), (2); 363 364--echo # Test of multiple table update done on the fly 365--echo # 2011-04-20 15:06:13 UTC 366SET TIMESTAMP = 1303311973.194685; 367UPDATE t1 JOIN t2 USING ( c ) SET t2.d = 1; 368SELECT * FROM t1; 369SELECT * FROM t2; 370 371--echo # Test of multiple table update done with temporary table. 372--echo # 1979-01-15 03:02:01 373SET TIMESTAMP = 285213721.134679; 374UPDATE t1 JOIN t2 USING ( c ) SET t1.d = 1; 375SELECT * FROM t1; 376SELECT * FROM t2; 377 378DROP TABLE t1, t2; 379 380 381--echo # 382--echo # Test of ON UPDATE CURRENT_TIMESTAMP. 383--echo # 384eval CREATE TABLE t1 ( 385 a $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE $current_timestamp, 386 b $datetime ON UPDATE $current_timestamp, 387 c INT 388); 389 390--echo # 2011-04-20 09:53:41 UTC 391SET TIMESTAMP = 1303293221.794613; 392 393INSERT INTO t1 ( c ) VALUES ( 1 ); 394SELECT * FROM t1; 395 396UPDATE t1 SET c = 1; 397SELECT * FROM t1; 398 399UPDATE t1 SET c = 2; 400SELECT * FROM t1; 401 402--echo # 403--echo # Test that ON UPDATE CURRENT_TIMESTAMP works after non-changing UPDATE. 404--echo # 405 406--echo # 2011-04-20 09:54:13 UTC 407SET TIMESTAMP = 1303293253.794613; 408 409UPDATE t1 SET c = 2, b = '2011-04-20 09:53:41.794613'; 410SELECT * FROM t1; 411 412UPDATE t1 SET c = 3; 413SELECT * FROM t1; 414 415--echo # 416--echo # Test of multiple-table UPDATE for ON UPDATE CURRENT_TIMESTAMP 417--echo # 418--echo # 2011-04-20 15:06:13 UTC 419SET TIMESTAMP = 1303311973.534231; 420 421UPDATE t1 t11, t1 t12 SET t11.c = 3; 422SELECT * FROM t1; 423 424UPDATE t1 t11, t1 t12 SET t11.c = 2; 425SELECT * FROM t1; 426 427DROP TABLE t1; 428 429--echo # 430--echo # Test of a multiple-table update where only one table is updated and 431--echo # the updated table has a primary key. 432--echo # 433eval CREATE TABLE t1 ( a INT, b INT, PRIMARY KEY (a) ); 434INSERT INTO t1 VALUES (1, 1),(2, 2),(3, 3),(4, 4); 435 436eval CREATE TABLE t2 ( a INT, b INT ); 437INSERT INTO t2 VALUES (1, 1),(2, 2),(3, 3),(4, 4),(5, 5); 438 439UPDATE t1, t2 SET t1.b = 100 WHERE t1.a = t2.a; 440 441SELECT * FROM t1; 442SELECT * FROM t2; 443 444DROP TABLE t1, t2; 445 446--echo # 447--echo # Test of ALTER TABLE, reordering columns. 448--echo # 449eval CREATE TABLE t1 ( a $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp, b INT );eval ALTER TABLE t1 MODIFY a $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp AFTER b; 450SHOW CREATE TABLE t1; 451DROP TABLE t1; 452 453eval CREATE TABLE t1 ( a INT, b $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp, c $timestamp NULL );eval ALTER TABLE t1 MODIFY b $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp FIRST; 454SHOW CREATE TABLE t1; 455DROP TABLE t1; 456 457eval CREATE TABLE t1 ( a INT, b $timestamp NULL );eval ALTER TABLE t1 MODIFY b $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp FIRST; 458SHOW CREATE TABLE t1; 459DROP TABLE t1; 460 461eval CREATE TABLE t1 ( a $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp, b $timestamp NULL );eval ALTER TABLE t1 MODIFY a $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER b; 462SHOW CREATE TABLE t1; 463DROP TABLE t1; 464 465eval CREATE TABLE t1 ( a $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp, b $timestamp NULL );eval ALTER TABLE t1 MODIFY a $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER b; 466SHOW CREATE TABLE t1; 467DROP TABLE t1; 468 469eval CREATE TABLE t1 ( a $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE $now, b INT, c $timestamp NULL ); 470SHOW CREATE TABLE t1;eval ALTER TABLE t1 MODIFY a $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp AFTER b; 471SHOW CREATE TABLE t1; 472DROP TABLE t1; 473 474eval CREATE TABLE t1 ( a $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE $now, b INT, c $timestamp NULL );eval ALTER TABLE t1 MODIFY c $timestamp NULL FIRST; 475SHOW CREATE TABLE t1; 476DROP TABLE t1; 477 478eval CREATE TABLE t1 ( a $timestamp NOT NULL DEFAULT $now ON UPDATE $current_timestamp, b INT, c $timestamp NULL ); 479SHOW CREATE TABLE t1;eval ALTER TABLE t1 MODIFY a $timestamp NOT NULL DEFAULT $now ON UPDATE $current_timestamp AFTER b; 480SHOW CREATE TABLE t1; 481DROP TABLE t1; 482 483eval CREATE TABLE t1 ( a $timestamp NOT NULL DEFAULT $now ON UPDATE $current_timestamp, b INT, c $timestamp NULL );eval ALTER TABLE t1 MODIFY c $timestamp NULL FIRST; 484SHOW CREATE TABLE t1; 485DROP TABLE t1; 486 487 488--echo # 489--echo # Test of ALTER TABLE, adding columns. 490--echo # 491eval CREATE TABLE t1 ( a INT ); 492eval ALTER TABLE t1 ADD COLUMN b $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp; 493SHOW CREATE TABLE t1; 494DROP TABLE t1; 495 496--echo # 497--echo # Test of INSERT SELECT. 498--echo # 499eval CREATE TABLE t1 ( 500 a $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp, 501 b $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp, 502 c $datetime DEFAULT $current_timestamp ON UPDATE $current_timestamp, 503 d $datetime DEFAULT $current_timestamp ON UPDATE $current_timestamp 504); 505 506eval CREATE TABLE t2 ( 507 placeholder1 INT, 508 placeholder2 INT, 509 placeholder3 INT, 510 placeholder4 INT, 511 a $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp, 512 b $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 513 c $datetime, 514 d $datetime 515); 516 517--echo # 1977-08-16 15:30:01 UTC 518SET TIMESTAMP = 240589801.654312; 519 520INSERT INTO t2 (a, b, c, d) VALUES ( 521 '1977-08-16 15:30:01.123456', 522 '1977-08-16 15:30:01.234567', 523 '1977-08-16 15:30:01.345678', 524 '1977-08-16 15:30:01.456789' 525); 526 527--echo # 1986-09-27 01:00:00 UTC 528SET TIMESTAMP = 528166800.132435; 529 530INSERT INTO t1 ( a, c ) SELECT a, c FROM t2; 531 532SELECT * FROM t1; 533 534DROP TABLE t1, t2; 535 536--echo # 537--echo # Test of CREATE TABLE SELECT. 538--echo # 539--echo # We test that the columns of the source table are not used to determine 540--echo # function defaults for the receiving table. 541--echo # 542 543--echo # 1970-04-11 20:13:57 UTC 544SET TIMESTAMP = 8712837.657898; 545eval CREATE TABLE t1 ( 546 a $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp, 547 b $timestamp NOT NULL DEFAULT $current_timestamp, 548 c $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE $current_timestamp, 549 d $timestamp NOT NULL DEFAULT '1986-09-27 03:00:00.098765', 550 e $timestamp NULL, 551 f $datetime DEFAULT $current_timestamp ON UPDATE $current_timestamp, 552 g $datetime DEFAULT $current_timestamp, 553 h $datetime ON UPDATE $current_timestamp, 554 i $datetime NULL, 555 j $datetime DEFAULT '1986-09-27 03:00:00.098765' 556); 557 558INSERT INTO t1 VALUES (); 559 560--echo # 1971-01-31 21:13:57 UTC 561SET TIMESTAMP = 34200837.164937; 562 563eval CREATE TABLE t2 SELECT a FROM t1; SHOW CREATE TABLE t2; SELECT * FROM t2; 564eval CREATE TABLE t3 SELECT b FROM t1; SHOW CREATE TABLE t3; SELECT * FROM t3; 565eval CREATE TABLE t4 SELECT c FROM t1; SHOW CREATE TABLE t4; SELECT * FROM t4; 566eval CREATE TABLE t5 SELECT d FROM t1; SHOW CREATE TABLE t5; SELECT * FROM t5; 567eval CREATE TABLE t6 SELECT e FROM t1; SHOW CREATE TABLE t6; SELECT * FROM t6; 568eval CREATE TABLE t7 SELECT f FROM t1; SHOW CREATE TABLE t7; SELECT * FROM t7; 569eval CREATE TABLE t8 SELECT g FROM t1; SHOW CREATE TABLE t8; SELECT * FROM t8; 570eval CREATE TABLE t9 SELECT h FROM t1; SHOW CREATE TABLE t9; SELECT * FROM t9; 571eval CREATE TABLE t10 SELECT i FROM t1; SHOW CREATE TABLE t10; SELECT * FROM t10; 572eval CREATE TABLE t11 SELECT j FROM t1; SHOW CREATE TABLE t11; SELECT * FROM t11; 573 574eval CREATE TABLE t12 ( 575 k $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp, 576 l $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp, 577 m $timestamp NOT NULL DEFAULT $current_timestamp, 578 n $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE $current_timestamp, 579 o $timestamp NOT NULL DEFAULT '1986-09-27 03:00:00.098765', 580 p $timestamp NULL, 581 q $datetime DEFAULT $current_timestamp ON UPDATE $current_timestamp, 582 r $datetime DEFAULT $current_timestamp, 583 s $datetime ON UPDATE $current_timestamp, 584 t $datetime NULL, 585 u $datetime DEFAULT '1986-09-27 03:00:00.098765' 586) 587SELECT * FROM t1; 588 589SHOW CREATE TABLE t12; 590 591DROP TABLE t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12; 592 593--echo # 1970-04-11 20:13:57 UTC 594SET TIMESTAMP = 8712837.164953; 595eval CREATE TABLE t1 ( 596 a $datetime DEFAULT $current_timestamp ON UPDATE $current_timestamp, 597 b $datetime DEFAULT $current_timestamp, 598 c $datetime ON UPDATE $current_timestamp, 599 d $datetime NULL, 600 e $datetime DEFAULT '1986-09-27 03:00:00.098765' 601); 602 603INSERT INTO t1 VALUES (); 604 605--echo # 1971-01-31 20:13:57 UTC 606SET TIMESTAMP = 34200837.915736; 607 608eval CREATE TABLE t2 SELECT a FROM t1; 609SHOW CREATE TABLE t2; 610SELECT * FROM t2; 611 612eval CREATE TABLE t3 SELECT b FROM t1; 613SHOW CREATE TABLE t3; 614SELECT * FROM t3; 615 616eval CREATE TABLE t4 SELECT c FROM t1; 617SHOW CREATE TABLE t4; 618SELECT * FROM t4; 619 620eval CREATE TABLE t5 SELECT d FROM t1; 621SHOW CREATE TABLE t5; 622SELECT * FROM t5; 623 624eval CREATE TABLE t6 SELECT e FROM t1; 625SHOW CREATE TABLE t6; 626SELECT * FROM t6; 627 628DROP TABLE t1, t2, t3, t4, t5, t6; 629 630--echo # 631--echo # Test of a CREATE TABLE SELECT that also declared columns. In this case 632--echo # the function default should be de-activated during the execution of the 633--echo # CREATE TABLE statement. 634--echo # 635--echo # 1970-01-01 03:16:40 636SET TIMESTAMP = 1000.987654; 637eval CREATE TABLE t1 ( a INT ); 638INSERT INTO t1 VALUES ( 1 ), ( 2 ); 639 640eval CREATE TABLE t2 ( b $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp) SELECT a FROM t1; 641 642SHOW CREATE TABLE t2; 643SET TIMESTAMP = 2000.876543; 644INSERT INTO t2( a ) VALUES ( 3 ); 645SELECT * FROM t2; 646 647DROP TABLE t1, t2; 648 649--echo # 650--echo # Test of updating a view. 651--echo # 652eval CREATE TABLE t1 ( a INT, b $datetime DEFAULT $current_timestamp ); 653eval CREATE TABLE t2 ( a INT, b $datetime ON UPDATE $current_timestamp ); 654 655eval CREATE VIEW v1 AS SELECT * FROM t1; 656SHOW CREATE VIEW v1; 657 658eval CREATE VIEW v2 AS SELECT * FROM t2; 659SHOW CREATE VIEW v2; 660 661--echo # 1971-01-31 21:13:57 UTC 662SET TIMESTAMP = 34200837.348564; 663 664INSERT INTO v1 ( a ) VALUES ( 1 ); 665INSERT INTO v2 ( a ) VALUES ( 1 ); 666 667SELECT * FROM t1; 668SELECT * FROM v1; 669 670SELECT * FROM t2; 671SELECT * FROM v2; 672 673--echo # 1970-04-11 20:13:57 UTC 674SET TIMESTAMP = 8712837.567332; 675UPDATE v1 SET a = 2; 676UPDATE v2 SET a = 2; 677 678SELECT * FROM t1; 679SELECT * FROM v1; 680 681SELECT * FROM t2; 682SELECT * FROM v2; 683 684DROP VIEW v1, v2; 685DROP TABLE t1, t2; 686 687--echo # 688--echo # Test with stored procedures. 689--echo # 690eval CREATE TABLE t1 ( 691 a INT, 692 b $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp, 693 c $timestamp NOT NULL DEFAULT $current_timestamp, 694 d $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE $current_timestamp, 695 e $timestamp NULL, 696 f $datetime DEFAULT $current_timestamp, 697 g $datetime ON UPDATE $current_timestamp 698); 699CREATE PROCEDURE p1() INSERT INTO test.t1( a ) VALUES ( 1 ); 700CREATE PROCEDURE p2() UPDATE t1 SET a = 2 WHERE a = 1; 701 702--echo # 1971-01-31 20:13:57 UTC 703SET TIMESTAMP = 34200837.876544; 704CALL p1(); 705SELECT * FROM t1; 706 707--echo # 1970-04-11 21:13:57 UTC 708SET TIMESTAMP = 8712837.143546; 709CALL p2(); 710SELECT * FROM t1; 711 712DROP PROCEDURE p1; 713DROP PROCEDURE p2; 714DROP TABLE t1; 715 716--echo # 717--echo # Test with triggers. 718--echo # 719eval CREATE TABLE t1 ( 720 a INT, 721 b $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp, 722 c $timestamp NOT NULL DEFAULT $current_timestamp, 723 d $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE $current_timestamp, 724 e $timestamp NULL, 725 f $datetime, 726 g $datetime DEFAULT $current_timestamp, 727 h $datetime ON UPDATE $current_timestamp, 728 i $datetime DEFAULT $current_timestamp ON UPDATE $current_timestamp 729); 730 731eval CREATE TABLE t2 ( a INT ); 732 733DELIMITER |; 734eval CREATE TRIGGER t2_trg BEFORE INSERT ON t2 FOR EACH ROW 735BEGIN 736 INSERT INTO t1 ( a ) VALUES ( 1 ); 737END| 738DELIMITER ;| 739 740--echo # 1971-01-31 21:13:57 UTC 741SET TIMESTAMP = 34200837.978675; 742 743INSERT INTO t2 ( a ) VALUES ( 1 ); 744SELECT * FROM t1; 745 746DROP TRIGGER t2_trg; 747 748DELIMITER |; 749eval CREATE TRIGGER t2_trg BEFORE INSERT ON t2 FOR EACH ROW 750BEGIN 751 UPDATE t1 SET a = 2; 752END| 753DELIMITER ;| 754 755--echo # 1970-04-11 21:13:57 UTC 756SET TIMESTAMP = 8712837.456789; 757 758INSERT INTO t2 ( a ) VALUES ( 1 ); 759SELECT * FROM t1; 760 761DROP TABLE t1, t2; 762 763--echo # 764--echo # Test where the assignment target is not a column. 765--echo # 766eval CREATE TABLE t1 ( a $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp ); 767eval CREATE TABLE t2 ( a $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp ); 768eval CREATE TABLE t3 ( a $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE $current_timestamp ); 769eval CREATE TABLE t4 ( a $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE $current_timestamp ); 770 771eval CREATE VIEW v1 AS SELECT a COLLATE latin1_german1_ci AS b FROM t1; 772eval CREATE VIEW v2 ( b ) AS SELECT a COLLATE latin1_german1_ci FROM t2; 773eval CREATE VIEW v3 AS SELECT a COLLATE latin1_german1_ci AS b FROM t3; 774eval CREATE VIEW v4 ( b ) AS SELECT a COLLATE latin1_german1_ci FROM t4; 775 776INSERT INTO v1 ( b ) VALUES ( '2007-10-24 00:03:34.010203' ); 777SELECT a FROM t1; 778 779INSERT INTO v2 ( b ) VALUES ( '2007-10-24 00:03:34.010203' ); 780SELECT a FROM t2; 781 782INSERT INTO t3 VALUES (); 783UPDATE v3 SET b = '2007-10-24 00:03:34.010203'; 784SELECT a FROM t3; 785 786INSERT INTO t4 VALUES (); 787UPDATE v4 SET b = '2007-10-24 00:03:34.010203'; 788SELECT a FROM t4; 789 790DROP VIEW v1, v2, v3, v4; 791DROP TABLE t1, t2, t3, t4; 792 793--echo # 794--echo # Test of LOAD DATA/XML INFILE 795--echo # This tests behavior of function defaults for TIMESTAMP and DATETIME 796--echo # columns. during LOAD ... INFILE. 797--echo # As can be seen here, a TIMESTAMP column with only ON UPDATE 798--echo # CURRENT_TIMESTAMP will still have CURRENT_TIMESTAMP inserted on LOAD 799--echo # ... INFILE if the value is missing. For DATETIME columns a NULL value 800--echo # is inserted instead. 801--echo # 802 803eval CREATE TABLE t1 ( 804 a INT, 805 b $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp, 806 c $timestamp NOT NULL DEFAULT $current_timestamp, 807 d $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE $current_timestamp, 808 e $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp, 809 f $datetime, 810 g $datetime DEFAULT $current_timestamp, 811 h $datetime ON UPDATE $current_timestamp, 812 i $datetime DEFAULT $current_timestamp ON UPDATE $current_timestamp 813); 814 815eval CREATE TABLE t2 ( 816 a $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp, 817 b $timestamp NOT NULL DEFAULT $current_timestamp, 818 c $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE $current_timestamp, 819 d $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp, 820 e $datetime NOT NULL, 821 f $datetime NOT NULL DEFAULT '1977-01-02 12:13:14', 822 g $datetime DEFAULT $current_timestamp NOT NULL, 823 h $datetime ON UPDATE $current_timestamp NOT NULL, 824 i $datetime DEFAULT $current_timestamp ON UPDATE $current_timestamp NOT NULL 825); 826 827SELECT 1 INTO OUTFILE 't3.dat' FROM dual; 828 829SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL 830INTO OUTFILE 't4.dat' 831FROM dual; 832 833SELECT 1, 2 INTO OUTFILE 't5.dat' FROM dual; 834 835--echo # Mon Aug 1 15:11:19 2011 UTC 836SET TIMESTAMP = 1312211479.918273; 837 838LOAD DATA INFILE 't3.dat' IGNORE INTO TABLE t1; 839--query_vertical SELECT * FROM t1 840 841LOAD DATA INFILE 't4.dat' IGNORE INTO TABLE t2; 842SELECT a FROM t2; 843SELECT b FROM t2; 844SELECT c FROM t2; 845SELECT d FROM t2; 846--echo # As shown here, supplying a NULL value to a non-nullable 847--echo # column with no default value results in the zero date. 848SELECT e FROM t2; 849--echo # As shown here, supplying a NULL value to a non-nullable column with a 850--echo # default value results in the zero date. 851SELECT f FROM t2; 852--echo # As shown here, supplying a NULL value to a non-nullable column with a 853--echo # default function results in the zero date. 854SELECT g FROM t2; 855--echo # As shown here, supplying a NULL value to a non-nullable DATETIME ON 856--echo # UPDATE CURRENT_TIMESTAMP column with no default value results in the 857--echo # zero date. 858SELECT h FROM t2; 859SELECT i FROM t2; 860 861DELETE FROM t1; 862DELETE FROM t2; 863 864--echo # Read t3 file into t1 865--echo # The syntax will cause a different code path to be taken 866--echo # (read_fixed_length()) than under the LOAD ... INTO TABLE t1 command 867--echo # above. The code in this path is copy-pasted code from the path taken 868--echo # under the syntax used in the previous LOAD command. 869LOAD DATA INFILE 't3.dat' IGNORE INTO TABLE t1 870FIELDS TERMINATED BY '' ENCLOSED BY ''; 871 872SELECT b FROM t1; 873SELECT c FROM t1; 874SELECT d FROM t1; 875SELECT e FROM t1; 876--echo # Yes, a missing field cannot be NULL using this syntax, so it will 877--echo # zero date instead. Says a comment in read_fixed_length() : "No fields 878--echo # specified in fields_vars list can be NULL in this format." 879--echo # It appears to be by design. This is inconsistent with LOAD DATA INFILE 880--echo # syntax in previous test. 881SELECT f FROM t1; 882SELECT g FROM t1; 883--echo # See comment above "SELECT f FROM f1". 884SELECT h FROM t1; 885SELECT i FROM t1; 886DELETE FROM t1; 887 888LOAD DATA INFILE 't5.dat' INTO TABLE t1 ( a, @dummy ); 889SELECT * FROM t1; 890SELECT @dummy; 891DELETE FROM t1; 892 893LOAD DATA INFILE 't3.dat' INTO TABLE t1 ( a ) SET c = '2005-06-06 08:09:10'; 894SELECT * FROM t1; 895DELETE FROM t1; 896 897LOAD DATA INFILE 't3.dat' INTO TABLE t1 ( a ) SET g = '2005-06-06 08:09:10'; 898SELECT * FROM t1; 899DELETE FROM t1; 900 901--echo # Load a static XML file 902LOAD XML INFILE '../../std_data/onerow.xml' INTO TABLE t1 903ROWS IDENTIFIED BY '<row>'; 904 905--echo Missing tags are treated as NULL 906--query_vertical SELECT * FROM t1 907 908DROP TABLE t1, t2; 909 910let $MYSQLD_DATADIR= `select @@datadir`; 911remove_file $MYSQLD_DATADIR/test/t3.dat; 912remove_file $MYSQLD_DATADIR/test/t4.dat; 913remove_file $MYSQLD_DATADIR/test/t5.dat; 914 915 916--echo # 917--echo # Similar LOAD DATA tests in another form 918--echo # 919--echo # All of this test portion has been run on a pre-WL5874 trunk 920--echo # (except that like_b and like_c didn't exist) and all result 921--echo # differences are a bug. 922--echo # Regarding like_b its definition is the same as b's except 923--echo # that the constant default is replaced with a function 924--echo # default. Our expectation is that like_b would behave 925--echo # like b: if b is set to NULL, or set to 0000-00-00, or set to 926--echo # its default, then the same should apply to like_b. Same for 927--echo # like_c vs c. 928 929--echo # Mon Aug 1 15:11:19 2011 UTC 930SET TIMESTAMP = 1312211479.089786; 931 932SELECT 1 INTO OUTFILE "file1.dat" FROM dual; 933SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL 934 INTO OUTFILE "file2.dat" FROM dual; 935 936--echo # Too short row 937 938eval 939CREATE TABLE t1 ( 940 dummy INT, 941 a $datetime NULL DEFAULT NULL, 942 b $datetime NULL DEFAULT "2011-11-18", 943 like_b $datetime NULL DEFAULT $current_timestamp, 944 c $datetime NOT NULL DEFAULT "2011-11-18", 945 like_c $datetime NOT NULL DEFAULT $current_timestamp, 946 d $timestamp NULL DEFAULT "2011-05-03" ON UPDATE $current_timestamp, 947 e $timestamp NOT NULL DEFAULT "2011-05-03", 948 f $timestamp NOT NULL DEFAULT $current_timestamp, 949 g $timestamp NULL DEFAULT NULL, 950 h INT NULL, 951 i INT NOT NULL DEFAULT 42 952); 953 954--echo # There is no promotion 955SHOW CREATE TABLE t1; 956 957LOAD DATA INFILE "file1.dat" IGNORE INTO table t1; 958 959--echo # It is strange that "like_b" gets NULL when "b" gets 0. But 960--echo # this is consistent with how "a" gets NULL when "b" gets 0, 961--echo # with how "g" gets NULL when "d" gets 0, and with how "h" gets 962--echo # NULL when "i" gets 0. Looks like "DEFAULT 963--echo # <non-NULL-constant>" is changed to 0, whereas DEFAULT NULL 964--echo # and DEFAULT NOW are changed to NULL. 965--query_vertical SELECT * FROM t1 966delete from t1; 967 968alter table t1 969modify f TIMESTAMP NULL default CURRENT_TIMESTAMP; 970 971--echo # There is no promotion 972SHOW CREATE TABLE t1; 973 974LOAD DATA INFILE "file1.dat" IGNORE INTO table t1; 975 976--query_vertical SELECT * FROM t1 977delete from t1; 978 979drop table t1; 980 981--echo # Conclusion derived from trunk's results: 982--echo # DATETIME DEFAULT <non-NULL-constant> (b,c) gets 0000-00-00, 983--echo # DATETIME DEFAULT NULL (a) gets NULL, 984--echo # TIMESTAMP NULL DEFAULT <non-NULL-constant> (d) gets 0000-00-00, 985--echo # TIMESTAMP NULL DEFAULT NULL (g) gets NULL, 986--echo # TIMESTAMP NULL DEFAULT NOW (f after ALTER) gets NULL, 987--echo # TIMESTAMP NOT NULL (f before ALTER, e) gets NOW. 988 989--echo ### Loading NULL ### 990 991eval 992CREATE TABLE t1 ( 993 dummy INT, 994 a $datetime NULL DEFAULT NULL, 995 b $datetime NULL DEFAULT "2011-11-18", 996 like_b $datetime NULL DEFAULT $current_timestamp, 997 c $datetime NOT NULL DEFAULT "2011-11-18", 998 like_c $datetime NOT NULL DEFAULT $current_timestamp, 999 d $timestamp NULL DEFAULT "2011-05-03" ON UPDATE $current_timestamp, 1000 e $timestamp NOT NULL DEFAULT "2011-05-03", 1001 f $timestamp NOT NULL DEFAULT $current_timestamp, 1002 g $timestamp NULL DEFAULT NULL, 1003 h INT NULL, 1004 i INT NOT NULL DEFAULT 42 1005); 1006 1007--echo # There is no promotion 1008SHOW CREATE TABLE t1; 1009 1010LOAD DATA INFILE "file2.dat" IGNORE INTO table t1; 1011 1012--query_vertical SELECT * FROM t1 1013delete from t1; 1014 1015alter table t1 1016modify f TIMESTAMP NULL default CURRENT_TIMESTAMP; 1017 1018--echo # There is no promotion 1019SHOW CREATE TABLE t1; 1020 1021LOAD DATA INFILE "file2.dat" IGNORE INTO table t1; 1022 1023--query_vertical SELECT * FROM t1 1024delete from t1; 1025 1026--echo # Conclusion derived from trunk's results: 1027--echo # DATETIME NULL (a,b) gets NULL, 1028--echo # DATETIME NOT NULL (c) gets 0000-00-00, 1029--echo # TIMESTAMP NULL (d,f,g) gets NULL, 1030--echo # TIMESTAMP NOT NULL (e) gets NOW. 1031 1032drop table t1; 1033remove_file $MYSQLD_DATADIR/test/file1.dat; 1034remove_file $MYSQLD_DATADIR/test/file2.dat; 1035 1036--echo # 1037--echo # Test of updatable views with check options. The option can be violated 1038--echo # using ON UPDATE updates which is very strange as this offers a loophole 1039--echo # in this integrity check. 1040--echo # 1041SET TIME_ZONE = "+03:00"; 1042--echo # 1970-01-01 03:16:40 1043SET TIMESTAMP = 1000.123456; 1044 1045eval CREATE TABLE t1 ( a INT, b $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp); 1046 1047SHOW CREATE TABLE t1; 1048 1049INSERT INTO t1 ( a ) VALUES ( 1 ); 1050 1051SELECT * FROM t1; 1052 1053eval CREATE VIEW v1 AS SELECT * FROM t1 WHERE b <= '1970-01-01 03:16:40.123456' 1054WITH CHECK OPTION; 1055 1056SELECT * FROM v1; 1057 1058--echo # 1970-01-01 03:33:20 1059SET TIMESTAMP = 2000.000234; 1060 1061--error ER_VIEW_CHECK_FAILED 1062UPDATE v1 SET a = 2; 1063SELECT * FROM t1; 1064 1065DROP VIEW v1; 1066DROP TABLE t1; 1067 1068eval CREATE TABLE t1 ( 1069 a $timestamp NOT NULL DEFAULT '1973-08-14 09:11:22.089786' ON UPDATE $current_timestamp, 1070 c INT KEY 1071); 1072--echo # 1973-08-14 09:11:22 UTC 1073SET TIMESTAMP = 114167482.534231; 1074INSERT INTO t1 ( c ) VALUES ( 1 ); 1075 1076eval CREATE VIEW v1 AS 1077SELECT * 1078FROM t1 1079WHERE a >= '1973-08-14 09:11:22' 1080WITH LOCAL CHECK OPTION; 1081 1082SELECT * FROM v1; 1083 1084SET TIMESTAMP = 1.126789; 1085 1086--error ER_VIEW_CHECK_FAILED 1087INSERT INTO v1 ( c ) VALUES ( 1 ) ON DUPLICATE KEY UPDATE c = 2; 1088 1089SELECT * FROM v1; 1090 1091DROP VIEW v1; 1092DROP TABLE t1; 1093 1094--echo # 1095--echo # Bug 13095459 - MULTI-TABLE UPDATE MODIFIES A ROW TWICE 1096--echo # 1097eval CREATE TABLE t1 ( 1098 a INT, 1099 b INT, 1100 ts $timestamp NOT NULL DEFAULT $current_timestamp ON UPDATE $current_timestamp, 1101 PRIMARY KEY ( a, ts ) 1102); 1103INSERT INTO t1( a, b, ts ) VALUES ( 1, 0, '2000-09-28 17:44:34' ); 1104 1105eval CREATE TABLE t2 ( a INT ); 1106INSERT INTO t2 VALUES ( 1 ); 1107 1108UPDATE t1 STRAIGHT_JOIN t2 1109SET t1.b = t1.b + 1 1110WHERE t1.a = 1 AND t1.ts >= '2000-09-28 00:00:00'; 1111 1112SELECT b FROM t1; 1113 1114DROP TABLE t1, t2; 1115 1116--echo # 1117--echo # Bug#11745578: 17392: ALTER TABLE ADD COLUMN TIMESTAMP DEFAULT 1118--echo # CURRENT_TIMESTAMP INSERTS ZERO 1119--echo # 1120SET timestamp = 1000; 1121 1122CREATE TABLE t1 ( b INT ); 1123INSERT INTO t1 VALUES (1); 1124 1125eval ALTER TABLE t1 ADD COLUMN a6 $datetime DEFAULT $now ON UPDATE $now FIRST; 1126eval ALTER TABLE t1 ADD COLUMN a5 $datetime DEFAULT $now FIRST; 1127eval ALTER TABLE t1 ADD COLUMN a4 $datetime ON UPDATE $now FIRST; 1128 1129eval ALTER TABLE t1 ADD COLUMN a3 $timestamp NOT NULL DEFAULT $now ON UPDATE $now FIRST; 1130eval ALTER TABLE t1 ADD COLUMN a2 $timestamp NOT NULL DEFAULT $now FIRST; 1131eval ALTER TABLE t1 ADD COLUMN a1 $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE $now FIRST; 1132 1133eval ALTER TABLE t1 ADD COLUMN c1 $timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE $now AFTER b; 1134eval ALTER TABLE t1 ADD COLUMN c2 $timestamp NOT NULL DEFAULT $now AFTER c1; 1135eval ALTER TABLE t1 ADD COLUMN c3 $timestamp NOT NULL DEFAULT $now ON UPDATE $now AFTER c2; 1136 1137eval ALTER TABLE t1 ADD COLUMN c4 $datetime ON UPDATE $now AFTER c3; 1138eval ALTER TABLE t1 ADD COLUMN c5 $datetime DEFAULT $now AFTER c4; 1139eval ALTER TABLE t1 ADD COLUMN c6 $datetime DEFAULT $now ON UPDATE $now AFTER c5; 1140 1141query_vertical SELECT * FROM t1; 1142DROP TABLE t1; 1143 1144 1145eval CREATE TABLE t1 ( a $timestamp NOT NULL DEFAULT $now ON UPDATE $current_timestamp, b $datetime DEFAULT $now ); 1146INSERT INTO t1 VALUES (); 1147 1148SET timestamp = 1000000000; 1149 1150ALTER TABLE t1 MODIFY COLUMN a TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3); 1151ALTER TABLE t1 MODIFY COLUMN b DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3); 1152 1153SELECT * FROM t1; 1154 1155DROP TABLE t1; 1156 1157 1158eval CREATE TABLE t1 ( 1159 a $timestamp NOT NULL DEFAULT '1999-12-01 11:22:33' ON UPDATE $current_timestamp, 1160 b $datetime DEFAULT '1999-12-01 11:22:33' 1161); 1162INSERT INTO t1 VALUES (); 1163 1164eval ALTER TABLE t1 MODIFY COLUMN a $timestamp DEFAULT $now; 1165eval ALTER TABLE t1 MODIFY COLUMN b $datetime DEFAULT $now; 1166INSERT INTO t1 VALUES (); 1167 1168SELECT * FROM t1; 1169 1170DROP TABLE t1; 1171