Edit File: delete_year.result
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: Warning 1818 YEAR(2) column type is deprecated. Creating YEAR(4) column instead. Warning 1818 YEAR(2) column type is deprecated. Creating YEAR(4) column instead. CREATE TABLE t4(c1 YEAR(2), c2 YEAR(2), UNIQUE INDEX idx(c1,c2)); Warnings: Warning 1818 YEAR(2) column type is deprecated. Creating YEAR(4) column instead. Warning 1818 YEAR(2) column type is deprecated. Creating YEAR(4) column 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 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 t4; 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 c1 FROM t1 WHERE c1='2005'; c1 2005 DELETE FROM t1 WHERE c1='2005'; SELECT c1 FROM t1; c1 1901 1902 1970 1971 1975 1979 1995 1996 1997 1998 1999 2000 2001 2004 2009 2061 2064 2065 2069 2154 2155 SELECT c2 FROM t2 WHERE c2=null; c2 DELETE FROM t2 WHERE c2=null LIMIT 2; 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 c1 FROM t4 WHERE c1 < '55'; c1 1901 1902 1970 1971 1975 1979 1995 1996 1997 1998 1999 2000 2001 2004 2005 2009 DELETE FROM t4 WHERE c1 < '65' ORDER BY c1 LIMIT 5; SELECT c1 FROM t4; c1 1979 1995 1996 1997 1998 1999 2000 2001 2004 2005 2009 2061 2064 2065 2069 2154 2155 DELETE FROM t2 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 DELETE FROM t1 WHERE c1=1979 OR c1=2154; SELECT c2 FROM t1; c2 1901 1902 1970 1971 1975 1995 1997 1998 1999 2000 2001 2004 2064 2065 2069 2155 NULL NULL NULL DELETE FROM t4 WHERE c2 IN (01,54,65,69,null) LIMIT 2; SELECT c1 FROM t4; c1 1979 1995 1996 1997 1998 1999 2000 2001 2004 2005 2009 2061 2064 2065 2069 2154 2155 DELETE t3,t4 FROM t3,t4 WHERE t3.c1=61 AND t4.c2=4; DELETE FROM t1 WHERE c1=2064 AND c2=2064; SELECT * FROM t1; c1 c2 1901 1901 1902 1902 1970 1970 1971 1971 1975 1975 1995 1995 1996 NULL 1997 1997 1998 1998 1999 1999 2000 2000 2001 2001 2004 2004 2009 NULL 2061 NULL 2065 2065 2069 2069 2155 2155 DELETE FROM t1 WHERE c1 BETWEEN 2000 AND 2010 LIMIT 2; SELECT * FROM t1; c1 c2 1901 1901 1902 1902 1970 1970 1971 1971 1975 1975 1995 1995 1996 NULL 1997 1997 1998 1998 1999 1999 2004 2004 2009 NULL 2061 NULL 2065 2065 2069 2069 2155 2155 DELETE FROM t1 WHERE c1 IN (2155,2009,1975) LIMIT 2; SELECT * FROM t1; c1 c2 1901 1901 1902 1902 1970 1970 1971 1971 1995 1995 1996 NULL 1997 1997 1998 1998 1999 1999 2004 2004 2061 NULL 2065 2065 2069 2069 2155 2155 DELETE t1,t2 FROM t1,t2 WHERE t1.c1=t2.c1 AND t1.c2=t2.c2; SELECT * FROM t1; c1 c2 1996 NULL 2061 NULL SELECT * FROM t2; c1 c2 1975 1975 1979 NULL 1996 NULL 2000 2000 2001 2001 2005 2005 2009 NULL 2061 NULL 2064 2064 2154 2154 SELECT * FROM t3; 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 2064 2064 2065 2065 2069 2069 2154 2154 2155 2155 SELECT * FROM t4; c1 c2 1979 NULL 1995 1995 1996 NULL 1997 1997 1998 1998 1999 1999 2000 2000 2001 2001 2005 2005 2009 NULL 2061 NULL 2064 2064 2065 2065 2069 2069 2154 2154 2155 2155 DROP TABLE IF EXISTS t1,t2,t3,t4;