diff options
Diffstat (limited to 'mysql-test/suite/engines/iuds/r/update_year.result')
| -rw-r--r-- | mysql-test/suite/engines/iuds/r/update_year.result | 957 |
1 files changed, 957 insertions, 0 deletions
diff --git a/mysql-test/suite/engines/iuds/r/update_year.result b/mysql-test/suite/engines/iuds/r/update_year.result new file mode 100644 index 00000000..b766531f --- /dev/null +++ b/mysql-test/suite/engines/iuds/r/update_year.result @@ -0,0 +1,957 @@ +DROP TABLE IF EXISTS t1,t2,t3,t4; +CREATE TABLE t1(c1 YEAR NOT NULL,c2 YEAR, PRIMARY KEY(c1)); +CREATE TABLE t2(c1 YEAR NOT NULL, c2 YEAR, UNIQUE INDEX idx(c1,c2)); +CREATE TABLE t3(c1 YEAR(2) NOT NULL,c2 YEAR(2), PRIMARY KEY(c1)); +Warnings: +Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead +Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead +CREATE TABLE t4(c1 YEAR(2), c2 YEAR(2), UNIQUE INDEX idx(c1,c2)); +Warnings: +Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead +Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead +INSERT INTO t1 VALUES (1901,1901),(1970,1970),(1999,1999),(2000,2000),(2155,2155); +INSERT INTO t2 VALUES (1901,1901),(1970,1970),(1999,1999),(2000,2000),(2155,2155); +INSERT INTO t3 VALUES (1901,1901),(1970,1970),(1999,1999),(2000,2000),(2155,2155); +INSERT INTO t4 VALUES (1901,1901),(1970,1970),(1999,1999),(2000,2000),(2155,2155); +INSERT INTO t1 VALUES ('1902','1902'),('1971','1971'),('1998','1998'),('2001','2001'),('2154','2154'); +INSERT INTO t2 VALUES ('1902','1902'),('1971','1971'),('1998','1998'),('2001','2001'),('2154','2154'); +INSERT INTO t3 VALUES ('1902','1902'),('1971','1971'),('1998','1998'),('2001','2001'),('2154','2154'); +INSERT INTO t4 VALUES ('1902','1902'),('1971','1971'),('1998','1998'),('2001','2001'),('2154','2154'); +INSERT INTO t1 VALUES (04,04),(64,64),(69,69),(97,97); +INSERT INTO t2 VALUES (04,04),(64,64),(69,69),(97,97); +INSERT INTO t3 VALUES (04,04),(64,64),(69,69),(97,97); +INSERT INTO t4 VALUES (04,04),(64,64),(69,69),(97,97); +INSERT INTO t1 VALUES ('05','05'),('65','65'),('75','75'),('95','95'); +INSERT INTO t2 VALUES ('05','05'),('65','65'),('75','75'),('95','95'); +INSERT INTO t3 VALUES ('05','05'),('65','65'),('75','75'),('95','95'); +INSERT INTO t4 VALUES ('05','05'),('65','65'),('75','75'),('95','95'); +INSERT INTO t1 VALUES ('09',null),('61',null),('79',null),('96',null); +INSERT INTO t2 VALUES ('09',null),('61',null),('79',null),('96',null); +INSERT INTO t3 VALUES ('09',null),('61',null),('79',null),('96',null); +INSERT INTO t4 VALUES ('09',null),('61',null),('79',null),('96',null); +SELECT * FROM t1; +c1 c2 +1901 1901 +1902 1902 +1970 1970 +1971 1971 +1975 1975 +1979 NULL +1995 1995 +1996 NULL +1997 1997 +1998 1998 +1999 1999 +2000 2000 +2001 2001 +2004 2004 +2005 2005 +2009 NULL +2061 NULL +2064 2064 +2065 2065 +2069 2069 +2154 2154 +2155 2155 +SELECT * FROM t2; +c1 c2 +1901 1901 +1902 1902 +1970 1970 +1971 1971 +1975 1975 +1979 NULL +1995 1995 +1996 NULL +1997 1997 +1998 1998 +1999 1999 +2000 2000 +2001 2001 +2004 2004 +2005 2005 +2009 NULL +2061 NULL +2064 2064 +2065 2065 +2069 2069 +2154 2154 +2155 2155 +SELECT * FROM t3; +c1 c2 +00 00 +01 01 +01 01 +02 02 +04 04 +05 05 +09 NULL +54 54 +55 55 +61 NULL +64 64 +65 65 +69 69 +70 70 +71 71 +75 75 +79 NULL +95 95 +96 NULL +97 97 +98 98 +99 99 +SELECT * FROM t4; +c1 c2 +00 00 +01 01 +01 01 +02 02 +04 04 +05 05 +09 NULL +54 54 +55 55 +61 NULL +64 64 +65 65 +69 69 +70 70 +71 71 +75 75 +79 NULL +95 95 +96 NULL +97 97 +98 98 +99 99 +SELECT c1 FROM t1 WHERE c1='2005'; +c1 +2005 +UPDATE t1 SET c1='2003' WHERE c1='2005'; +SELECT c1 FROM t1; +c1 +1901 +1902 +1970 +1971 +1975 +1979 +1995 +1996 +1997 +1998 +1999 +2000 +2001 +2003 +2004 +2009 +2061 +2064 +2065 +2069 +2154 +2155 +SELECT c2 FROM t2 WHERE c2=null; +c2 +UPDATE t2 SET c2='2070' WHERE c2=null AND c1='1996'; +SELECT c2 FROM t2; +c2 +1901 +1902 +1970 +1971 +1975 +1995 +1997 +1998 +1999 +2000 +2001 +2004 +2005 +2064 +2065 +2069 +2154 +2155 +NULL +NULL +NULL +NULL +SELECT c2 FROM t3 WHERE c2 < '64'; +c2 +00 +01 +01 +02 +04 +05 +54 +55 +UPDATE t3 SET c2=null WHERE c2 < '64' ORDER BY c2 LIMIT 3; +SELECT c2 FROM t3; +c2 +01 +04 +05 +54 +55 +64 +65 +69 +70 +71 +75 +95 +97 +98 +99 +NULL +NULL +NULL +NULL +NULL +NULL +NULL +SELECT c1 FROM t4 WHERE c1 < '55'; +c1 +00 +01 +01 +02 +04 +05 +09 +UPDATE t4 SET c1='00' WHERE c1 < '65' ORDER BY c1 LIMIT 5; +SELECT c1 FROM t4; +c1 +00 +00 +00 +00 +00 +05 +09 +54 +55 +61 +64 +65 +69 +70 +71 +75 +79 +95 +96 +97 +98 +99 +SELECT c1 FROM t3 WHERE c1='05'; +c1 +05 +UPDATE t3 SET c1=c1+c2 WHERE c1='05'; +SELECT c1 FROM t3; +c1 +00 +01 +01 +02 +04 +09 +10 +54 +55 +61 +64 +65 +69 +70 +71 +75 +79 +95 +96 +97 +98 +99 +SELECT c1 FROM t4 WHERE c2=4; +c1 +00 +UPDATE t4 SET c1=c1 + 04 WHERE c2=04; +SELECT c1 FROM t4; +c1 +00 +00 +00 +00 +04 +05 +09 +54 +55 +61 +64 +65 +69 +70 +71 +75 +79 +95 +96 +97 +98 +99 +SELECT c2 FROM t3 WHERE c2=75; +c2 +75 +UPDATE t3 SET c2=c2 + 1902 WHERE c2='75'; +SELECT c2 FROM t3; +c2 +01 +04 +05 +54 +55 +64 +65 +69 +70 +71 +77 +95 +97 +98 +99 +NULL +NULL +NULL +NULL +NULL +NULL +NULL +SELECT c1 FROM t4 WHERE c1=09; +c1 +09 +UPDATE t4 SET c1=c1 - c2 WHERE c1='09'; +SELECT c1 FROM t4; +c1 +00 +00 +00 +00 +04 +05 +54 +55 +61 +64 +65 +69 +70 +71 +75 +79 +95 +96 +97 +98 +99 +NULL +SELECT c1 FROM t3 WHERE c1=75; +c1 +75 +UPDATE t3 SET c1=c1 - 01 WHERE c1='75'; +SELECT c1 FROM t3; +c1 +00 +01 +01 +02 +04 +09 +10 +54 +55 +61 +64 +65 +69 +70 +71 +74 +79 +95 +96 +97 +98 +99 +SELECT c1 FROM t4 WHERE c1=98; +c1 +98 +UPDATE t4 SET c1=c1 / 2 WHERE c1='98'; +SELECT c1 FROM t4; +c1 +00 +00 +00 +00 +04 +05 +49 +54 +55 +61 +64 +65 +69 +70 +71 +75 +79 +95 +96 +97 +99 +NULL +SELECT c2 FROM t3 WHERE c2=03; +c2 +UPDATE t3 SET c2=c2 * 03 WHERE c2='03'; +SELECT c2 FROM t3; +c2 +01 +04 +05 +54 +55 +64 +65 +69 +70 +71 +77 +95 +97 +98 +99 +NULL +NULL +NULL +NULL +NULL +NULL +NULL +SELECT c1 FROM t1 WHERE c1 > 2060 AND c1 < 2070; +c1 +2061 +2064 +2065 +2069 +UPDATE t1 SET c1=c1 + 10 WHERE c1 > 2060 AND c1 < 2070; +SELECT c1 FROM t1; +c1 +1901 +1902 +1970 +1971 +1975 +1979 +1995 +1996 +1997 +1998 +1999 +2000 +2001 +2003 +2004 +2009 +2071 +2074 +2075 +2079 +2154 +2155 +SELECT c1 FROM t2 WHERE c1 > 1969 AND c1 < 1980; +c1 +1970 +1971 +1975 +1979 +UPDATE t2 SET c1=c1 - 10 WHERE c1 > 1969 AND c1 < 1980 ORDER BY c1 LIMIT 2; +SELECT c1 FROM t2; +c1 +1901 +1902 +1960 +1961 +1975 +1979 +1995 +1996 +1997 +1998 +1999 +2000 +2001 +2004 +2005 +2009 +2061 +2064 +2065 +2069 +2154 +2155 +UPDATE t2 SET c2=2009 WHERE c1=2009 AND c2=null; +SELECT c2 FROM t2; +c2 +1901 +1902 +1970 +1971 +1975 +1995 +1997 +1998 +1999 +2000 +2001 +2004 +2005 +2064 +2065 +2069 +2154 +2155 +NULL +NULL +NULL +NULL +UPDATE t1 SET c2=2155 WHERE c1=1979 OR c1=2154; +SELECT c2 FROM t1; +c2 +1901 +1902 +1970 +1971 +1975 +1995 +1997 +1998 +1999 +2000 +2001 +2004 +2005 +2064 +2065 +2069 +2155 +2155 +2155 +NULL +NULL +NULL +UPDATE t4 SET c1=99 WHERE c2 IN (01,54,65,69,null); +SELECT c1 FROM t4; +c1 +00 +00 +04 +05 +49 +55 +61 +64 +70 +71 +75 +79 +95 +96 +97 +99 +99 +99 +99 +99 +99 +NULL +UPDATE IGNORE t1 SET c2=-1 WHERE c1=2071; +Warnings: +Warning 1264 Out of range value for column 'c2' at row 1 +SELECT c2 FROM t1; +c2 +0000 +1901 +1902 +1970 +1971 +1975 +1995 +1997 +1998 +1999 +2000 +2001 +2004 +2005 +2064 +2065 +2069 +2155 +2155 +2155 +NULL +NULL +UPDATE t1 SET c2=0 WHERE c1=2003; +SELECT c2 FROM t1; +c2 +0000 +0000 +1901 +1902 +1970 +1971 +1975 +1995 +1997 +1998 +1999 +2000 +2001 +2004 +2064 +2065 +2069 +2155 +2155 +2155 +NULL +NULL +UPDATE IGNORE t1 SET c2=2156 WHERE c1=2001; +Warnings: +Warning 1264 Out of range value for column 'c2' at row 1 +SELECT c2 FROM t1; +c2 +0000 +0000 +0000 +1901 +1902 +1970 +1971 +1975 +1995 +1997 +1998 +1999 +2000 +2004 +2064 +2065 +2069 +2155 +2155 +2155 +NULL +NULL +UPDATE IGNORE t4 SET c2=-1 WHERE c1=71; +Warnings: +Warning 1264 Out of range value for column 'c2' at row 1 +SELECT c2 FROM t4; +c2 +00 +00 +01 +01 +02 +04 +05 +54 +55 +64 +65 +69 +70 +75 +95 +97 +98 +99 +NULL +NULL +NULL +NULL +UPDATE t4 SET c2=0 WHERE c1=70; +SELECT c2 FROM t4; +c2 +00 +00 +00 +01 +01 +02 +04 +05 +54 +55 +64 +65 +69 +75 +95 +97 +98 +99 +NULL +NULL +NULL +NULL +UPDATE IGNORE t4 SET c2=100 WHERE c1=79; +Warnings: +Warning 1264 Out of range value for column 'c2' at row 1 +SELECT c2 FROM t4; +c2 +00 +00 +00 +00 +01 +01 +02 +04 +05 +54 +55 +64 +65 +69 +75 +95 +97 +98 +99 +NULL +NULL +NULL +SELECT c1 FROM t1 WHERE c2 < 1975; +c1 +1901 +1902 +1970 +1971 +2001 +2003 +2071 +SET SQL_MODE=STRICT_ALL_TABLES; +UPDATE t1 SET c1=NULL WHERE c2 < 1975; +ERROR 23000: Column 'c1' cannot be null +UPDATE IGNORE t1 SET c1=NULL WHERE c2 < 1975; +Warnings: +Warning 1048 Column 'c1' cannot be null +Warning 1048 Column 'c1' cannot be null +Warning 1048 Column 'c1' cannot be null +Warning 1048 Column 'c1' cannot be null +Warning 1048 Column 'c1' cannot be null +Warning 1048 Column 'c1' cannot be null +Warning 1048 Column 'c1' cannot be null +SELECT c1 from t1 WHERE c2 < 1975; +c1 +0000 +1902 +1970 +1971 +2001 +2003 +2071 +SET SQL_MODE=DEFAULT +#Update with invalid values +#UPDATE t2 SET c1='def' WHERE c2=2064; +UPDATE IGNORE t4 SET c1=-70 WHERE c2=75; +Warnings: +Warning 1264 Out of range value for column 'c1' at row 17 +SELECT c1 FROM t4; +c1 +00 +00 +00 +04 +05 +49 +55 +61 +64 +70 +71 +79 +95 +96 +97 +99 +99 +99 +99 +99 +99 +NULL +UPDATE t1 SET c1=1971 WHERE c2=1995; +ERROR 23000: Duplicate entry '1971' for key 'PRIMARY' +UPDATE t1,t2,t3,t4 SET t3.c1=t4.c1+t4.c2 WHERE t3.c1=61 AND t4.c2=4; +UPDATE t1 SET t1.c1=2073 WHERE c1=2074 AND c2=2064; +SELECT * FROM t1; +c1 c2 +0000 1901 +1902 1902 +1970 1970 +1971 1971 +1975 1975 +1979 2155 +1995 1995 +1996 NULL +1997 1997 +1998 1998 +1999 1999 +2000 2000 +2001 0000 +2003 0000 +2004 2004 +2009 NULL +2071 0000 +2073 2064 +2075 2065 +2079 2069 +2154 2155 +2155 2155 +UPDATE t1 SET t1.c2=0000 WHERE c1 BETWEEN 2000 AND 2010; +SELECT * FROM t1; +c1 c2 +0000 1901 +1902 1902 +1970 1970 +1971 1971 +1975 1975 +1979 2155 +1995 1995 +1996 NULL +1997 1997 +1998 1998 +1999 1999 +2000 0000 +2001 0000 +2003 0000 +2004 0000 +2009 0000 +2071 0000 +2073 2064 +2075 2065 +2079 2069 +2154 2155 +2155 2155 +UPDATE IGNORE t1 SET c1=c1-1,c2=c2+1 WHERE c1 IN (2154,2009,1979); +Warnings: +Warning 1264 Out of range value for column 'c2' at row 1 +Warning 1264 Out of range value for column 'c2' at row 3 +SELECT * FROM t1; +c1 c2 +0000 1901 +1902 1902 +1970 1970 +1971 1971 +1975 1975 +1978 0000 +1995 1995 +1996 NULL +1997 1997 +1998 1998 +1999 1999 +2000 0000 +2001 0000 +2003 0000 +2004 0000 +2008 2001 +2071 0000 +2073 2064 +2075 2065 +2079 2069 +2153 0000 +2155 2155 +UPDATE t1,t2 SET t1.c2='2155' WHERE t1.c1=t2.c1 AND t1.c2=t2.c2; +SELECT * FROM t1; +SELECT * FROM t2; +c1 c2 +1901 1901 +1902 1902 +1960 1970 +1961 1971 +1975 1975 +1979 NULL +1995 1995 +1996 NULL +1997 1997 +1998 1998 +1999 1999 +2000 2000 +2001 2001 +2004 2004 +2005 2005 +2009 NULL +2061 NULL +2064 2064 +2065 2065 +2069 2069 +2154 2154 +2155 2155 +SELECT * FROM t3; +c1 c2 +00 NULL +01 01 +01 NULL +02 NULL +04 04 +08 NULL +09 NULL +10 05 +54 54 +55 55 +64 64 +65 65 +69 69 +70 70 +71 71 +74 77 +79 NULL +95 95 +96 NULL +97 97 +98 98 +99 99 +SELECT * FROM t4; +c1 c2 +00 00 +00 02 +00 75 +04 04 +05 05 +49 98 +55 55 +61 NULL +64 64 +70 00 +71 00 +79 00 +95 95 +96 NULL +97 97 +99 01 +99 01 +99 54 +99 65 +99 69 +99 99 +NULL NULL +DROP TABLE IF EXISTS t1,t2,t3,t4; |