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