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));
6
Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
7
Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
8
CREATE TABLE t4(c1 YEAR(2), c2 YEAR(2), UNIQUE INDEX idx(c1,c2));
10
Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
11
Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead
12
INSERT INTO t1 VALUES (1901,1901),(1970,1970),(1999,1999),(2000,2000),(2155,2155);
13
INSERT INTO t2 VALUES (1901,1901),(1970,1970),(1999,1999),(2000,2000),(2155,2155);
14
INSERT INTO t3 VALUES (1901,1901),(1970,1970),(1999,1999),(2000,2000),(2155,2155);
15
INSERT INTO t4 VALUES (1901,1901),(1970,1970),(1999,1999),(2000,2000),(2155,2155);
16
INSERT INTO t1 VALUES ('1902','1902'),('1971','1971'),('1998','1998'),('2001','2001'),('2154','2154');
17
INSERT INTO t2 VALUES ('1902','1902'),('1971','1971'),('1998','1998'),('2001','2001'),('2154','2154');
18
INSERT INTO t3 VALUES ('1902','1902'),('1971','1971'),('1998','1998'),('2001','2001'),('2154','2154');
19
INSERT INTO t4 VALUES ('1902','1902'),('1971','1971'),('1998','1998'),('2001','2001'),('2154','2154');
20
INSERT INTO t1 VALUES (04,04),(64,64),(69,69),(97,97);
21
INSERT INTO t2 VALUES (04,04),(64,64),(69,69),(97,97);
22
INSERT INTO t3 VALUES (04,04),(64,64),(69,69),(97,97);
23
INSERT INTO t4 VALUES (04,04),(64,64),(69,69),(97,97);
24
INSERT INTO t1 VALUES ('05','05'),('65','65'),('75','75'),('95','95');
25
INSERT INTO t2 VALUES ('05','05'),('65','65'),('75','75'),('95','95');
26
INSERT INTO t3 VALUES ('05','05'),('65','65'),('75','75'),('95','95');
27
INSERT INTO t4 VALUES ('05','05'),('65','65'),('75','75'),('95','95');
28
INSERT INTO t1 VALUES ('09',null),('61',null),('79',null),('96',null);
29
INSERT INTO t2 VALUES ('09',null),('61',null),('79',null),('96',null);
30
INSERT INTO t3 VALUES ('09',null),('61',null),('79',null),('96',null);
31
INSERT INTO t4 VALUES ('09',null),('61',null),('79',null),('96',null);
128
SELECT c1 FROM t1 WHERE c1='2005';
131
UPDATE t1 SET c1='2003' WHERE c1='2005';
156
SELECT c2 FROM t2 WHERE c2=null;
158
UPDATE t2 SET c2='2070' WHERE c2=null AND c1='1996';
183
SELECT c2 FROM t3 WHERE c2 < '64';
193
UPDATE t3 SET c2=null WHERE c2 < '64' ORDER BY c2 LIMIT 3;
218
SELECT c1 FROM t4 WHERE c1 < '55';
227
UPDATE t4 SET c1='00' WHERE c1 < '65' ORDER BY c1 LIMIT 5;
252
SELECT c1 FROM t3 WHERE c1='05';
255
UPDATE t3 SET c1=c1+c2 WHERE c1='05';
280
SELECT c1 FROM t4 WHERE c2=4;
283
UPDATE t4 SET c1=c1 + 04 WHERE c2=04;
308
SELECT c2 FROM t3 WHERE c2=75;
311
UPDATE t3 SET c2=c2 + 1902 WHERE c2='75';
336
SELECT c1 FROM t4 WHERE c1=09;
339
UPDATE t4 SET c1=c1 - c2 WHERE c1='09';
364
SELECT c1 FROM t3 WHERE c1=75;
367
UPDATE t3 SET c1=c1 - 01 WHERE c1='75';
392
SELECT c1 FROM t4 WHERE c1=98;
395
UPDATE t4 SET c1=c1 / 2 WHERE c1='98';
420
SELECT c2 FROM t3 WHERE c2=03;
422
UPDATE t3 SET c2=c2 * 03 WHERE c2='03';
447
SELECT c1 FROM t1 WHERE c1 > 2060 AND c1 < 2070;
453
UPDATE t1 SET c1=c1 + 10 WHERE c1 > 2060 AND c1 < 2070;
478
SELECT c1 FROM t2 WHERE c1 > 1969 AND c1 < 1980;
484
UPDATE t2 SET c1=c1 - 10 WHERE c1 > 1969 AND c1 < 1980 ORDER BY c1 LIMIT 2;
509
UPDATE t2 SET c2=2009 WHERE c1=2009 AND c2=null;
534
UPDATE t1 SET c2=2155 WHERE c1=1979 OR c1=2154;
559
UPDATE t4 SET c1=99 WHERE c2 IN (01,54,65,69,null);
584
UPDATE t1 SET c2=-1 WHERE c1=2071;
586
Warning 1264 Out of range value for column 'c2' at row 1
611
UPDATE t1 SET c2=0 WHERE c1=2003;
636
UPDATE t1 SET c2=2156 WHERE c1=2001;
638
Warning 1264 Out of range value for column 'c2' at row 1
663
UPDATE t4 SET c2=-1 WHERE c1=71;
665
Warning 1264 Out of range value for column 'c2' at row 1
690
UPDATE t4 SET c2=0 WHERE c1=70;
715
UPDATE t4 SET c2=100 WHERE c1=79;
717
Warning 1264 Out of range value for column 'c2' at row 1
742
SELECT c1 FROM t1 WHERE c2 < 1975;
751
SET SQL_MODE=STRICT_ALL_TABLES;
752
UPDATE t1 SET c1=NULL WHERE c2 < 1975;
753
ERROR 23000: Column 'c1' cannot be null
754
UPDATE IGNORE t1 SET c1=NULL WHERE c2 < 1975;
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
Warning 1048 Column 'c1' cannot be null
763
SELECT c1 from t1 WHERE c2 < 1975;
773
#Update with invalid values
774
#UPDATE t2 SET c1='def' WHERE c2=2064;
775
UPDATE t4 SET c1=-70 WHERE c2=75;
777
Warning 1264 Out of range value for column 'c1' at row 17
802
UPDATE t1 SET c1=1971 WHERE c2=1995;
803
ERROR 23000: Duplicate entry '1971' for key 'PRIMARY'
804
UPDATE t1,t2,t3,t4 SET t3.c1=t4.c1+t4.c2 WHERE t3.c1=61 AND t4.c2=4;
805
UPDATE t1 SET t1.c1=2073 WHERE c1=2074 AND c2=2064;
830
UPDATE t1 SET t1.c2=0000 WHERE c1 BETWEEN 2000 AND 2010;
855
UPDATE t1 SET c1=c1-1,c2=c2+1 WHERE c1 IN (2154,2009,1979);
857
Warning 1264 Out of range value for column 'c2' at row 1
858
Warning 1264 Out of range value for column 'c2' at row 3
883
UPDATE t1,t2 SET t1.c2='2155' WHERE t1.c1=t2.c1 AND t1.c2=t2.c2;
957
DROP TABLE IF EXISTS t1,t2,t3,t4;