Debian Codegraph @

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

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