4493d40a-92aa-4162-9dfc-bfc4ec0996e6
/
polardbxengine
2
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
polardbxengine/mysql-test/suite/engines/iuds/r/delete_year.result

398 lines
5.3 KiB
Plaintext
Raw Permalink Blame History

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(4) NOT NULL,c2 YEAR(4), PRIMARY KEY(c1));
CREATE TABLE t4(c1 YEAR(4), c2 YEAR(4), UNIQUE INDEX idx(c1,c2));
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;
Reference in New Issue View Git Blame Copy Permalink