Debian Codegraph @

mysql-test/suite/engines/iuds/r/update_year.result

raw
1 CREATE TABLE t1(c1 YEAR NOT NULL,c2 YEAR, PRIMARY KEY(c1));
2 CREATE TABLE t2(c1 YEAR NOT NULL, c2 YEAR, UNIQUE INDEX idx(c1,c2));
3 CREATE TABLE t3(c1 YEAR(2) NOT NULL,c2 YEAR(2), PRIMARY KEY(c1));
4 Warnings:
5 Warning 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use 'YEAR(4)' instead
6 Warning 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use 'YEAR(4)' instead
7 CREATE TABLE t4(c1 YEAR(2), c2 YEAR(2), UNIQUE INDEX idx(c1,c2));
8 Warnings:
9 Warning 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use 'YEAR(4)' instead
10 Warning 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use 'YEAR(4)' instead
11 INSERT INTO t1 VALUES (1901,1901),(1970,1970),(1999,1999),(2000,2000),(2155,2155);
12 INSERT INTO t2 VALUES (1901,1901),(1970,1970),(1999,1999),(2000,2000),(2155,2155);
13 INSERT INTO t3 VALUES (1901,1901),(1970,1970),(1999,1999),(2000,2000),(2155,2155);
14 INSERT INTO t4 VALUES (1901,1901),(1970,1970),(1999,1999),(2000,2000),(2155,2155);
15 INSERT INTO t1 VALUES ('1902','1902'),('1971','1971'),('1998','1998'),('2001','2001'),('2154','2154');
16 INSERT INTO t2 VALUES ('1902','1902'),('1971','1971'),('1998','1998'),('2001','2001'),('2154','2154');
17 INSERT INTO t3 VALUES ('1902','1902'),('1971','1971'),('1998','1998'),('2001','2001'),('2154','2154');
18 INSERT INTO t4 VALUES ('1902','1902'),('1971','1971'),('1998','1998'),('2001','2001'),('2154','2154');
19 INSERT INTO t1 VALUES (04,04),(64,64),(69,69),(97,97);
20 INSERT INTO t2 VALUES (04,04),(64,64),(69,69),(97,97);
21 INSERT INTO t3 VALUES (04,04),(64,64),(69,69),(97,97);
22 INSERT INTO t4 VALUES (04,04),(64,64),(69,69),(97,97);
23 INSERT INTO t1 VALUES ('05','05'),('65','65'),('75','75'),('95','95');
24 INSERT INTO t2 VALUES ('05','05'),('65','65'),('75','75'),('95','95');
25 INSERT INTO t3 VALUES ('05','05'),('65','65'),('75','75'),('95','95');
26 INSERT INTO t4 VALUES ('05','05'),('65','65'),('75','75'),('95','95');
27 INSERT INTO t1 VALUES ('09',null),('61',null),('79',null),('96',null);
28 INSERT INTO t2 VALUES ('09',null),('61',null),('79',null),('96',null);
29 INSERT INTO t3 VALUES ('09',null),('61',null),('79',null),('96',null);
30 INSERT INTO t4 VALUES ('09',null),('61',null),('79',null),('96',null);
31 SELECT * FROM t1;
32 c1 c2
33 1901 1901
34 1902 1902
35 1970 1970
36 1971 1971
37 1975 1975
38 1979 NULL
39 1995 1995
40 1996 NULL
41 1997 1997
42 1998 1998
43 1999 1999
44 2000 2000
45 2001 2001
46 2004 2004
47 2005 2005
48 2009 NULL
49 2061 NULL
50 2064 2064
51 2065 2065
52 2069 2069
53 2154 2154
54 2155 2155
55 SELECT * FROM t2;
56 c1 c2
57 1901 1901
58 1902 1902
59 1970 1970
60 1971 1971
61 1975 1975
62 1979 NULL
63 1995 1995
64 1996 NULL
65 1997 1997
66 1998 1998
67 1999 1999
68 2000 2000
69 2001 2001
70 2004 2004
71 2005 2005
72 2009 NULL
73 2061 NULL
74 2064 2064
75 2065 2065
76 2069 2069
77 2154 2154
78 2155 2155
79 SELECT * FROM t3;
80 c1 c2
81 00 00
82 01 01
83 01 01
84 02 02
85 04 04
86 05 05
87 09 NULL
88 54 54
89 55 55
90 61 NULL
91 64 64
92 65 65
93 69 69
94 70 70
95 71 71
96 75 75
97 79 NULL
98 95 95
99 96 NULL
100 97 97
101 98 98
102 99 99
103 SELECT * FROM t4;
104 c1 c2
105 00 00
106 01 01
107 01 01
108 02 02
109 04 04
110 05 05
111 09 NULL
112 54 54
113 55 55
114 61 NULL
115 64 64
116 65 65
117 69 69
118 70 70
119 71 71
120 75 75
121 79 NULL
122 95 95
123 96 NULL
124 97 97
125 98 98
126 99 99
127 SELECT c1 FROM t1 WHERE c1='2005';
128 c1
129 2005
130 UPDATE t1 SET c1='2003' WHERE c1='2005';
131 SELECT c1 FROM t1;
132 c1
133 1901
134 1902
135 1970
136 1971
137 1975
138 1979
139 1995
140 1996
141 1997
142 1998
143 1999
144 2000
145 2001
146 2003
147 2004
148 2009
149 2061
150 2064
151 2065
152 2069
153 2154
154 2155
155 SELECT c2 FROM t2 WHERE c2=null;
156 c2
157 UPDATE t2 SET c2='2070' WHERE c2=null AND c1='1996';
158 SELECT c2 FROM t2;
159 c2
160 1901
161 1902
162 1970
163 1971
164 1975
165 1995
166 1997
167 1998
168 1999
169 2000
170 2001
171 2004
172 2005
173 2064
174 2065
175 2069
176 2154
177 2155
178 NULL
179 NULL
180 NULL
181 NULL
182 SELECT c2 FROM t3 WHERE c2 < '64';
183 c2
184 00
185 01
186 01
187 02
188 04
189 05
190 54
191 55
192 UPDATE t3 SET c2=null WHERE c2 < '64' ORDER BY c2 LIMIT 3;
193 SELECT c2 FROM t3;
194 c2
195 01
196 04
197 05
198 54
199 55
200 64
201 65
202 69
203 70
204 71
205 75
206 95
207 97
208 98
209 99
210 NULL
211 NULL
212 NULL
213 NULL
214 NULL
215 NULL
216 NULL
217 SELECT c1 FROM t4 WHERE c1 < '55';
218 c1
219 00
220 01
221 01
222 02
223 04
224 05
225 09
226 UPDATE t4 SET c1='00' WHERE c1 < '65' ORDER BY c1 LIMIT 5;
227 SELECT c1 FROM t4;
228 c1
229 00
230 00
231 00
232 00
233 00
234 05
235 09
236 54
237 55
238 61
239 64
240 65
241 69
242 70
243 71
244 75
245 79
246 95
247 96
248 97
249 98
250 99
251 SELECT c1 FROM t3 WHERE c1='05';
252 c1
253 05
254 UPDATE t3 SET c1=c1+c2 WHERE c1='05';
255 SELECT c1 FROM t3;
256 c1
257 00
258 01
259 01
260 02
261 04
262 09
263 10
264 54
265 55
266 61
267 64
268 65
269 69
270 70
271 71
272 75
273 79
274 95
275 96
276 97
277 98
278 99
279 SELECT c1 FROM t4 WHERE c2=4;
280 c1
281 00
282 UPDATE t4 SET c1=c1 + 04 WHERE c2=04;
283 SELECT c1 FROM t4;
284 c1
285 00
286 00
287 00
288 00
289 04
290 05
291 09
292 54
293 55
294 61
295 64
296 65
297 69
298 70
299 71
300 75
301 79
302 95
303 96
304 97
305 98
306 99
307 SELECT c2 FROM t3 WHERE c2=75;
308 c2
309 75
310 UPDATE t3 SET c2=c2 + 1902 WHERE c2='75';
311 SELECT c2 FROM t3;
312 c2
313 01
314 04
315 05
316 54
317 55
318 64
319 65
320 69
321 70
322 71
323 77
324 95
325 97
326 98
327 99
328 NULL
329 NULL
330 NULL
331 NULL
332 NULL
333 NULL
334 NULL
335 SELECT c1 FROM t4 WHERE c1=09;
336 c1
337 09
338 UPDATE t4 SET c1=c1 - c2 WHERE c1='09';
339 SELECT c1 FROM t4;
340 c1
341 00
342 00
343 00
344 00
345 04
346 05
347 54
348 55
349 61
350 64
351 65
352 69
353 70
354 71
355 75
356 79
357 95
358 96
359 97
360 98
361 99
362 NULL
363 SELECT c1 FROM t3 WHERE c1=75;
364 c1
365 75
366 UPDATE t3 SET c1=c1 - 01 WHERE c1='75';
367 SELECT c1 FROM t3;
368 c1
369 00
370 01
371 01
372 02
373 04
374 09
375 10
376 54
377 55
378 61
379 64
380 65
381 69
382 70
383 71
384 74
385 79
386 95
387 96
388 97
389 98
390 99
391 SELECT c1 FROM t4 WHERE c1=98;
392 c1
393 98
394 UPDATE t4 SET c1=c1 / 2 WHERE c1='98';
395 SELECT c1 FROM t4;
396 c1
397 00
398 00
399 00
400 00
401 04
402 05
403 49
404 54
405 55
406 61
407 64
408 65
409 69
410 70
411 71
412 75
413 79
414 95
415 96
416 97
417 99
418 NULL
419 SELECT c2 FROM t3 WHERE c2=03;
420 c2
421 UPDATE t3 SET c2=c2 * 03 WHERE c2='03';
422 SELECT c2 FROM t3;
423 c2
424 01
425 04
426 05
427 54
428 55
429 64
430 65
431 69
432 70
433 71
434 77
435 95
436 97
437 98
438 99
439 NULL
440 NULL
441 NULL
442 NULL
443 NULL
444 NULL
445 NULL
446 SELECT c1 FROM t1 WHERE c1 > 2060 AND c1 < 2070;
447 c1
448 2061
449 2064
450 2065
451 2069
452 UPDATE t1 SET c1=c1 + 10 WHERE c1 > 2060 AND c1 < 2070;
453 SELECT c1 FROM t1;
454 c1
455 1901
456 1902
457 1970
458 1971
459 1975
460 1979
461 1995
462 1996
463 1997
464 1998
465 1999
466 2000
467 2001
468 2003
469 2004
470 2009
471 2071
472 2074
473 2075
474 2079
475 2154
476 2155
477 SELECT c1 FROM t2 WHERE c1 > 1969 AND c1 < 1980;
478 c1
479 1970
480 1971
481 1975
482 1979
483 UPDATE t2 SET c1=c1 - 10 WHERE c1 > 1969 AND c1 < 1980 ORDER BY c1 LIMIT 2;
484 SELECT c1 FROM t2;
485 c1
486 1901
487 1902
488 1960
489 1961
490 1975
491 1979
492 1995
493 1996
494 1997
495 1998
496 1999
497 2000
498 2001
499 2004
500 2005
501 2009
502 2061
503 2064
504 2065
505 2069
506 2154
507 2155
508 UPDATE t2 SET c2=2009 WHERE c1=2009 AND c2=null;
509 SELECT c2 FROM t2;
510 c2
511 1901
512 1902
513 1970
514 1971
515 1975
516 1995
517 1997
518 1998
519 1999
520 2000
521 2001
522 2004
523 2005
524 2064
525 2065
526 2069
527 2154
528 2155
529 NULL
530 NULL
531 NULL
532 NULL
533 UPDATE t1 SET c2=2155 WHERE c1=1979 OR c1=2154;
534 SELECT c2 FROM t1;
535 c2
536 1901
537 1902
538 1970
539 1971
540 1975
541 1995
542 1997
543 1998
544 1999
545 2000
546 2001
547 2004
548 2005
549 2064
550 2065
551 2069
552 2155
553 2155
554 2155
555 NULL
556 NULL
557 NULL
558 UPDATE t4 SET c1=99 WHERE c2 IN (01,54,65,69,null);
559 SELECT c1 FROM t4;
560 c1
561 00
562 00
563 04
564 05
565 49
566 55
567 61
568 64
569 70
570 71
571 75
572 79
573 95
574 96
575 97
576 99
577 99
578 99
579 99
580 99
581 99
582 NULL
583 UPDATE IGNORE t1 SET c2=-1 WHERE c1=2071;
584 Warnings:
585 Warning 1264 Out of range value for column 'c2' at row 1
586 SELECT c2 FROM t1;
587 c2
588 0000
589 1901
590 1902
591 1970
592 1971
593 1975
594 1995
595 1997
596 1998
597 1999
598 2000
599 2001
600 2004
601 2005
602 2064
603 2065
604 2069
605 2155
606 2155
607 2155
608 NULL
609 NULL
610 UPDATE t1 SET c2=0 WHERE c1=2003;
611 SELECT c2 FROM t1;
612 c2
613 0000
614 0000
615 1901
616 1902
617 1970
618 1971
619 1975
620 1995
621 1997
622 1998
623 1999
624 2000
625 2001
626 2004
627 2064
628 2065
629 2069
630 2155
631 2155
632 2155
633 NULL
634 NULL
635 UPDATE IGNORE t1 SET c2=2156 WHERE c1=2001;
636 Warnings:
637 Warning 1264 Out of range value for column 'c2' at row 1
638 SELECT c2 FROM t1;
639 c2
640 0000
641 0000
642 0000
643 1901
644 1902
645 1970
646 1971
647 1975
648 1995
649 1997
650 1998
651 1999
652 2000
653 2004
654 2064
655 2065
656 2069
657 2155
658 2155
659 2155
660 NULL
661 NULL
662 UPDATE IGNORE t4 SET c2=-1 WHERE c1=71;
663 Warnings:
664 Warning 1264 Out of range value for column 'c2' at row 1
665 SELECT c2 FROM t4;
666 c2
667 00
668 00
669 01
670 01
671 02
672 04
673 05
674 54
675 55
676 64
677 65
678 69
679 70
680 75
681 95
682 97
683 98
684 99
685 NULL
686 NULL
687 NULL
688 NULL
689 UPDATE t4 SET c2=0 WHERE c1=70;
690 SELECT c2 FROM t4;
691 c2
692 00
693 00
694 00
695 01
696 01
697 02
698 04
699 05
700 54
701 55
702 64
703 65
704 69
705 75
706 95
707 97
708 98
709 99
710 NULL
711 NULL
712 NULL
713 NULL
714 UPDATE IGNORE t4 SET c2=100 WHERE c1=79;
715 Warnings:
716 Warning 1264 Out of range value for column 'c2' at row 1
717 SELECT c2 FROM t4;
718 c2
719 00
720 00
721 00
722 00
723 01
724 01
725 02
726 04
727 05
728 54
729 55
730 64
731 65
732 69
733 75
734 95
735 97
736 98
737 99
738 NULL
739 NULL
740 NULL
741 SELECT c1 FROM t1 WHERE c2 < 1975;
742 c1
743 1901
744 1902
745 1970
746 1971
747 2001
748 2003
749 2071
750 SET SQL_MODE=STRICT_ALL_TABLES;
751 UPDATE t1 SET c1=NULL WHERE c2 < 1975;
752 ERROR 23000: Column 'c1' cannot be null
753 UPDATE IGNORE t1 SET c1=NULL WHERE c2 < 1975;
754 Warnings:
755 Warning 1048 Column 'c1' cannot be null
756 Warning 1048 Column 'c1' cannot be null
757 Warning 1048 Column 'c1' cannot be null
758 Warning 1048 Column 'c1' cannot be null
759 Warning 1048 Column 'c1' cannot be null
760 Warning 1048 Column 'c1' cannot be null
761 Warning 1048 Column 'c1' cannot be null
762 SELECT c1 from t1 WHERE c2 < 1975;
763 c1
764 0000
765 1902
766 1970
767 1971
768 2001
769 2003
770 2071
771 SET SQL_MODE=DEFAULT
772 #Update with invalid values
773 #UPDATE t2 SET c1='def' WHERE c2=2064;
774 UPDATE IGNORE t4 SET c1=-70 WHERE c2=75;
775 Warnings:
776 Warning 1264 Out of range value for column 'c1' at row 17
777 SELECT c1 FROM t4;
778 c1
779 00
780 00
781 00
782 04
783 05
784 49
785 55
786 61
787 64
788 70
789 71
790 79
791 95
792 96
793 97
794 99
795 99
796 99
797 99
798 99
799 99
800 NULL
801 UPDATE t1 SET c1=1971 WHERE c2=1995;
802 ERROR 23000: Duplicate entry '1971' for key 'PRIMARY'
803 UPDATE t1,t2,t3,t4 SET t3.c1=t4.c1+t4.c2 WHERE t3.c1=61 AND t4.c2=4;
804 UPDATE t1 SET t1.c1=2073 WHERE c1=2074 AND c2=2064;
805 SELECT * FROM t1;
806 c1 c2
807 0000 1901
808 1902 1902
809 1970 1970
810 1971 1971
811 1975 1975
812 1979 2155
813 1995 1995
814 1996 NULL
815 1997 1997
816 1998 1998
817 1999 1999
818 2000 2000
819 2001 0000
820 2003 0000
821 2004 2004
822 2009 NULL
823 2071 0000
824 2073 2064
825 2075 2065
826 2079 2069
827 2154 2155
828 2155 2155
829 UPDATE t1 SET t1.c2=0000 WHERE c1 BETWEEN 2000 AND 2010;
830 SELECT * FROM t1;
831 c1 c2
832 0000 1901
833 1902 1902
834 1970 1970
835 1971 1971
836 1975 1975
837 1979 2155
838 1995 1995
839 1996 NULL
840 1997 1997
841 1998 1998
842 1999 1999
843 2000 0000
844 2001 0000
845 2003 0000
846 2004 0000
847 2009 0000
848 2071 0000
849 2073 2064
850 2075 2065
851 2079 2069
852 2154 2155
853 2155 2155
854 UPDATE IGNORE t1 SET c1=c1-1,c2=c2+1 WHERE c1 IN (2154,2009,1979);
855 Warnings:
856 Warning 1264 Out of range value for column 'c2' at row 1
857 Warning 1264 Out of range value for column 'c2' at row 3
858 SELECT * FROM t1;
859 c1 c2
860 0000 1901
861 1902 1902
862 1970 1970
863 1971 1971
864 1975 1975
865 1978 0000
866 1995 1995
867 1996 NULL
868 1997 1997
869 1998 1998
870 1999 1999
871 2000 0000
872 2001 0000
873 2003 0000
874 2004 0000
875 2008 2001
876 2071 0000
877 2073 2064
878 2075 2065
879 2079 2069
880 2153 0000
881 2155 2155
882 UPDATE t1,t2 SET t1.c2='2155' WHERE t1.c1=t2.c1 AND t1.c2=t2.c2;
883 SELECT * FROM t1;
884 SELECT * FROM t2;
885 c1 c2
886 1901 1901
887 1902 1902
888 1960 1970
889 1961 1971
890 1975 1975
891 1979 NULL
892 1995 1995
893 1996 NULL
894 1997 1997
895 1998 1998
896 1999 1999
897 2000 2000
898 2001 2001
899 2004 2004
900 2005 2005
901 2009 NULL
902 2061 NULL
903 2064 2064
904 2065 2065
905 2069 2069
906 2154 2154
907 2155 2155
908 SELECT * FROM t3;
909 c1 c2
910 00 NULL
911 01 01
912 01 NULL
913 02 NULL
914 04 04
915 08 NULL
916 09 NULL
917 10 05
918 54 54
919 55 55
920 64 64
921 65 65
922 69 69
923 70 70
924 71 71
925 74 77
926 79 NULL
927 95 95
928 96 NULL
929 97 97
930 98 98
931 99 99
932 SELECT * FROM t4;
933 c1 c2
934 00 00
935 00 02
936 00 75
937 04 04
938 05 05
939 49 98
940 55 55
941 61 NULL
942 64 64
943 70 00
944 71 00
945 79 00
946 95 95
947 96 NULL
948 97 97
949 99 01
950 99 01
951 99 54
952 99 65
953 99 69
954 99 99
955 NULL NULL
956 DROP TABLE IF EXISTS t1,t2,t3,t4;