1
DROP TABLE IF EXISTS t1,t2,t3,t4;
2
CREATE TABLE t1(c1 YEAR NOT NULL,c2 YEAR, PRIMARY KEY(c1));
3
CREATE TABLE t2(c1 YEAR NOT NULL, c2 YEAR, UNIQUE INDEX idx(c1,c2));
4
CREATE TABLE t3(c1 YEAR(2) NOT NULL,c2 YEAR(2), PRIMARY KEY(c1));
5
CREATE TABLE t4(c1 YEAR(2), c2 YEAR(2), UNIQUE INDEX idx(c1,c2));
6
INSERT INTO t1 VALUES (1901,1901),(1970,1970),(1999,1999),(2000,2000),(2155,2155);
7
INSERT INTO t2 VALUES (1901,1901),(1970,1970),(1999,1999),(2000,2000),(2155,2155);
8
INSERT INTO t3 VALUES (1901,1901),(1970,1970),(1999,1999),(2000,2000),(2155,2155);
9
INSERT INTO t4 VALUES (1901,1901),(1970,1970),(1999,1999),(2000,2000),(2155,2155);
10
INSERT INTO t1 VALUES ('1902','1902'),('1971','1971'),('1998','1998'),('2001','2001'),('2154','2154');
11
INSERT INTO t2 VALUES ('1902','1902'),('1971','1971'),('1998','1998'),('2001','2001'),('2154','2154');
12
INSERT INTO t3 VALUES ('1902','1902'),('1971','1971'),('1998','1998'),('2001','2001'),('2154','2154');
13
INSERT INTO t4 VALUES ('1902','1902'),('1971','1971'),('1998','1998'),('2001','2001'),('2154','2154');
14
INSERT INTO t1 VALUES (04,04),(64,64),(69,69),(97,97);
15
INSERT INTO t2 VALUES (04,04),(64,64),(69,69),(97,97);
16
INSERT INTO t3 VALUES (04,04),(64,64),(69,69),(97,97);
17
INSERT INTO t4 VALUES (04,04),(64,64),(69,69),(97,97);
18
INSERT INTO t1 VALUES ('05','05'),('65','65'),('75','75'),('95','95');
19
INSERT INTO t2 VALUES ('05','05'),('65','65'),('75','75'),('95','95');
20
INSERT INTO t3 VALUES ('05','05'),('65','65'),('75','75'),('95','95');
21
INSERT INTO t4 VALUES ('05','05'),('65','65'),('75','75'),('95','95');
22
INSERT INTO t1 VALUES ('09',null),('61',null),('79',null),('96',null);
23
INSERT INTO t2 VALUES ('09',null),('61',null),('79',null),('96',null);
24
INSERT INTO t3 VALUES ('09',null),('61',null),('79',null),('96',null);
25
INSERT INTO t4 VALUES ('09',null),('61',null),('79',null),('96',null);
122
SELECT c1 FROM t1 WHERE c1='2005';
125
UPDATE t1 SET c1='2003' WHERE c1='2005';
150
SELECT c2 FROM t2 WHERE c2=null;
152
UPDATE t2 SET c2='2070' WHERE c2=null AND c1='1996';
177
SELECT c2 FROM t3 WHERE c2 < '64';
187
UPDATE t3 SET c2=null WHERE c2 < '64' ORDER BY c2 LIMIT 3;
212
SELECT c1 FROM t4 WHERE c1 < '55';
221
UPDATE t4 SET c1='00' WHERE c1 < '65' ORDER BY c1 LIMIT 5;
246
SELECT c1 FROM t3 WHERE c1='05';
249
UPDATE t3 SET c1=c1+c2 WHERE c1='05';
274
SELECT c1 FROM t4 WHERE c2=4;
277
UPDATE t4 SET c1=c1 + 04 WHERE c2=04;
302
SELECT c2 FROM t3 WHERE c2=75;
305
UPDATE t3 SET c2=c2 + 1902 WHERE c2='75';
330
SELECT c1 FROM t4 WHERE c1=09;
333
UPDATE t4 SET c1=c1 - c2 WHERE c1='09';
358
SELECT c1 FROM t3 WHERE c1=75;
361
UPDATE t3 SET c1=c1 - 01 WHERE c1='75';
386
SELECT c1 FROM t4 WHERE c1=98;
389
UPDATE t4 SET c1=c1 / 2 WHERE c1='98';
414
SELECT c2 FROM t3 WHERE c2=03;
416
UPDATE t3 SET c2=c2 * 03 WHERE c2='03';
441
SELECT c1 FROM t1 WHERE c1 > 2060 AND c1 < 2070;
447
UPDATE t1 SET c1=c1 + 10 WHERE c1 > 2060 AND c1 < 2070;
472
SELECT c1 FROM t2 WHERE c1 > 1969 AND c1 < 1980;
478
UPDATE t2 SET c1=c1 - 10 WHERE c1 > 1969 AND c1 < 1980 ORDER BY c1 LIMIT 2;
503
UPDATE t2 SET c2=2009 WHERE c1=2009 AND c2=null;
528
UPDATE t1 SET c2=2155 WHERE c1=1979 OR c1=2154;
553
UPDATE t4 SET c1=99 WHERE c2 IN (01,54,65,69,null);
578
UPDATE t1 SET c2=-1 WHERE c1=2071;
580
Warning 1264 Out of range value for column 'c2' at row 1
605
UPDATE t1 SET c2=0 WHERE c1=2003;
630
UPDATE t1 SET c2=2156 WHERE c1=2001;
632
Warning 1264 Out of range value for column 'c2' at row 1
657
UPDATE t4 SET c2=-1 WHERE c1=71;
659
Warning 1264 Out of range value for column 'c2' at row 1
684
UPDATE t4 SET c2=0 WHERE c1=70;
709
UPDATE t4 SET c2=100 WHERE c1=79;
711
Warning 1264 Out of range value for column 'c2' at row 1
736
SELECT c1 FROM t1 WHERE c2 < 1975;
745
SET SQL_MODE=STRICT_ALL_TABLES;
746
UPDATE t1 SET c1=NULL WHERE c2 < 1975;
747
ERROR 23000: Column 'c1' cannot be null
748
UPDATE IGNORE t1 SET c1=NULL WHERE c2 < 1975;
750
Warning 1048 Column 'c1' cannot be null
751
Warning 1048 Column 'c1' cannot be null
752
Warning 1048 Column 'c1' cannot be null
753
Warning 1048 Column 'c1' cannot be null
754
Warning 1048 Column 'c1' cannot be null
755
Warning 1048 Column 'c1' cannot be null
756
Warning 1048 Column 'c1' cannot be null
757
SELECT c1 from t1 WHERE c2 < 1975;
767
#Update with invalid values
768
#UPDATE t2 SET c1='def' WHERE c2=2064;
769
UPDATE t4 SET c1=-70 WHERE c2=75;
771
Warning 1264 Out of range value for column 'c1' at row 17
796
UPDATE t1 SET c1=1971 WHERE c2=1995;
797
ERROR 23000: Duplicate entry '1971' for key 'PRIMARY'
798
UPDATE t1,t2,t3,t4 SET t3.c1=t4.c1+t4.c2 WHERE t3.c1=61 AND t4.c2=4;
799
UPDATE t1 SET t1.c1=2073 WHERE c1=2074 AND c2=2064;
824
UPDATE t1 SET t1.c2=0000 WHERE c1 BETWEEN 2000 AND 2010;
849
UPDATE t1 SET c1=c1-1,c2=c2+1 WHERE c1 IN (2154,2009,1979);
851
Warning 1264 Out of range value for column 'c2' at row 1
852
Warning 1264 Out of range value for column 'c2' at row 3
877
UPDATE t1,t2 SET t1.c2='2155' WHERE t1.c1=t2.c1 AND t1.c2=t2.c2;
951
DROP TABLE IF EXISTS t1,t2,t3,t4;