Edit File: insert_year.test
--disable_warnings DROP TABLE IF EXISTS t1,t2,t3,t4; --enable_warnings ######## Running INSERT tests for YEAR(4) ######## # Create tables CREATE TABLE t1(c1 YEAR(4) NOT NULL, c2 YEAR(4) NULL, c3 DATE, c4 DATETIME, PRIMARY KEY(c1), UNIQUE INDEX(c2)); CREATE TABLE t2(c1 YEAR(4) NOT NULL, c2 YEAR(4) NULL, c3 DATE, c4 DATETIME, PRIMARY KEY(c1,c2)); CREATE TABLE t3(c1 YEAR(4) NOT NULL, c2 YEAR(4) NULL, c3 DATE, c4 DATETIME, UNIQUE INDEX idx(c1,c2)); CREATE TABLE t4(c1 YEAR(4) NOT NULL, c2 YEAR(4) NULL, c3 DATE, c4 DATETIME); # Insert some rows with targeted values # As a four-digit string in the range '1901' to '2155' INSERT INTO t1 VALUES('1901','1901','98-12-31','98.12.31 11:30:45'),('1999','1999','98-12-30','98.12.30 11:30:45'),('2000','2000','98-12-29','98.12.29 11:30:45'),('2001','2001','98-12-28','98.12.28 11:30:45'),('2099','2099','98-12-27','98.12.27 11:30:45'),('2100','2100','98-12-26','98.12.26 11:30:45'),('2155','2155','98-12-26','98.12.26 11:30:45'); INSERT INTO t2 VALUES('1901','1901','98-12-31','98.12.31 11:30:45'),('1999','1999','98-12-30','98.12.30 11:30:45'),('2000','2000','98-12-29','98.12.29 11:30:45'),('2001','2001','98-12-28','98.12.28 11:30:45'),('2099','2099','98-12-27','98.12.27 11:30:45'),('2100','2100','98-12-26','98.12.26 11:30:45'),('2155','2155','98-12-26','98.12.26 11:30:45'); INSERT INTO t3 VALUES('1901','1901','98-12-31','98.12.31 11:30:45'),('1999','1999','98-12-30','98.12.30 11:30:45'),('2000','2000','98-12-29','98.12.29 11:30:45'),('2001','2001','98-12-28','98.12.28 11:30:45'),('2099','2099','98-12-27','98.12.27 11:30:45'),('2100','2100','98-12-26','98.12.26 11:30:45'),('2155','2155','98-12-26','98.12.26 11:30:45'); INSERT INTO t4 VALUES('1901','1901','98-12-31','98.12.31 11:30:45'),('1999','1999','98-12-30','98.12.30 11:30:45'),('2000','2000','98-12-29','98.12.29 11:30:45'),('2001','2001','98-12-28','98.12.28 11:30:45'),('2099','2099','98-12-27','98.12.27 11:30:45'),('2100','2100','98-12-26','98.12.26 11:30:45'),('2155','2155','98-12-25','98.12.25 11:30:45'); # As a four-digit number in the range 1901 to 2155 INSERT INTO t4 VALUES(1901,1901,'98-12-24','98.12.24 11:30:45'),(1999,1999,'98-12-23','98.12.23 11:30:45'),(2000,2000,'98-12-22','98.12.22 11:30:45'),(2001,2001,'98-12-21','98.12.21 11:30:45'),(2099,2099,'98-12-20','98.12.20 11:30:45'),(2100,2100,'98-12-19','98.12.19 11:30:45'),(2155,2155,'98-12-18','98.12.18 11:30:45'); # As a two-digit string in the range '00' to '99' INSERT INTO t1 VALUES('20','20','98-12-16','98.12.16 11:30:45'),('40','40','98-12-15','98.12.15 11:30:45'),('60','60','98-12-14','98.12.14 11:30:45'),('69','69','98-12-13','98.12.13 11:30:45'),('70','70','98-12-12','98.12.12 11:30:45'),('90','90','98-12-11','98.12.11 11:30:45'); INSERT INTO t2 VALUES('00','10','98-12-17','98.12.17 11:30:45'),('20','20','98-12-16','98.12.16 11:30:45'),('40','40','98-12-15','98.12.15 11:30:45'),('60','60','98-12-14','98.12.14 11:30:45'),('69','69','98-12-13','98.12.13 11:30:45'),('70','70','98-12-12','98.12.12 11:30:45'),('90','90','98-12-11','98.12.11 11:30:45'),('99','98','98-12-10','98.12.10 11:30:45'); INSERT INTO t3 VALUES('00','10','98-12-17','98.12.17 11:30:45'),('20','20','98-12-16','98.12.16 11:30:45'),('40','40','98-12-15','98.12.15 11:30:45'),('60','60','98-12-14','98.12.14 11:30:45'),('69','69','98-12-13','98.12.13 11:30:45'),('70','70','98-12-12','98.12.12 11:30:45'),('90','90','98-12-11','98.12.11 11:30:45'),('99','98','98-12-10','98.12.10 11:30:45'); INSERT INTO t4 VALUES('00','00','98-12-17','98.12.17 11:30:45'),('20','20','98-12-16','98.12.16 11:30:45'),('40','40','98-12-15','98.12.15 11:30:45'),('60','60','98-12-14','98.12.14 11:30:45'),('69','69','98-12-13','98.12.13 11:30:45'),('70','70','98-12-12','98.12.12 11:30:45'),('90','90','98-12-11','98.12.11 11:30:45'),('99','99','98-12-10','98.12.10 11:30:45'); # As a two-digit number in the range 1 to 99 INSERT INTO t4 VALUES(1,1,'98-12-9','98.12.9 11:30:45'),(20,20,'98-12-8','98.12.8 11:30:45'),(40,40,'98-12-7','98.12.7 11:30:45'),(60,60,'98-12-6','98.12.6 11:30:45'),(69,69,'98-12-5','98.12.5 11:30:45'),(70,70,'98-12-4','98.12.4 11:30:45'),(90,90,'98-12-3','98.12.3 11:30:45'),(99,99,'98-12-2','98.12.2 11:30:45'); # As the result of a function SET TIMESTAMP=1233216687; # 2009-01-29 13:41:27 INSERT INTO t1 VALUES(NOW(),CURRENT_DATE,NOW(),CURRENT_DATE); INSERT INTO t2 VALUES(NOW(),CURRENT_DATE,NOW(),CURRENT_DATE); INSERT INTO t3 VALUES(NOW(),CURRENT_DATE,NOW(),CURRENT_DATE); INSERT INTO t4 VALUES(NOW(),CURRENT_DATE,NOW(),CURRENT_DATE); # Insert other valid year values one digit strings, '0', '000' INSERT INTO t1(c1) VALUES('5'); INSERT INTO t2(c1) VALUES('5'); INSERT INTO t3(c1) VALUES('5'); INSERT INTO t4(c1,c2) VALUES('0','000'); # Insert invalid year values, outside range, would be converted to zero value INSERT INTO t2 VALUES(1900,2156,'08-01-06','08/01/07'); INSERT INTO t3 VALUES('1900','2156','08-01-08','08/1/9'); INSERT INTO t4 VALUES('1900','2156','08-01-08','08/1/9'),(1900,2156,'08-01-08','08/1/9'),(0,00,'08-01-08','08/1/9'),(000,000,'08-01-08','08/1/9'),('-20','100','08-01-08','08/1/9'),(-20,100,'08-01-08','08/1/9'); # Insert empty string '', would be converted to zero INSERT INTO t1 VALUES('','','08-01-04','08/01/05') /* Inserts zero dates for '' strings */; # Insert non-date value(absurd values), would be converted to zero INSERT INTO t4 VALUES('abcd','abcd','08-01-10','08/01/11'),(1234,1234,'08-01-12','08/01/13') /* Inserts zero dates for absurd dates */; # Insert duplicates for parts of the clustered key/unique index INSERT INTO t2 VALUES('20','30','98-12-16','98.12.16 11:30:45'),('40','20','98-12-15','98.12.15 11:30:45'); INSERT INTO t3 VALUES('00','20','98-12-17','98.12.17 11:30:45'),('20','40','98-12-10','98.12.10 11:30:45'); # Insert permissible NULLs INSERT INTO t1 VALUES(80,NULL,'08-01-02','08/01/03'); INSERT INTO t3 VALUES(80,NULL,'08-01-02','08/01/03'); INSERT INTO t4 VALUES(80,NULL,'08-01-02','08/01/03'); # Insert duplicate NULLs to unique column INSERT INTO t1(c1,c2) VALUES(1992,NULL); --sorted_result SELECT * FROM t1; --sorted_result SELECT * FROM t2; --sorted_result SELECT * FROM t3; --sorted_result SELECT * FROM t4; # Now select using various table access methods (full table scan, range scan, index scan etc.) ## Full table scan ## --sorted_result SELECT * FROM t2; --sorted_result SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t2; --sorted_result SELECT * FROM t2 WHERE c3 = '1998-12-11'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t2; ## Backward index scan, covering ## SELECT c1,c2 FROM t2 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t2 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t2 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t2 WHERE c1 = '1901' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 = '1901' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 = '1901' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 = '1901' ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t2 WHERE c1 <> '1901' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 <> '1901' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 > '1901' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 > '1901' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 >= '1901' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 >= '1901' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 < '1901' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 < '1901' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 <= '1901' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 <= '1901' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 <=> '1901' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 <=> '1901' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 BETWEEN '1901' AND '2020' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 BETWEEN '1901' AND '2020' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 IN ('1901','2020') ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 IN ('1901','2020') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 >= '1901' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 >= '1901' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t2 WHERE c1 <> '1901' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 <> '1901' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 > '1901' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 > '1901' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 >= '1901' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 >= '1901' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 < '1901' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 < '1901' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 <= '1901' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 <= '1901' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 <=> '1901' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 <=> '1901' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 BETWEEN '1901' AND '2020' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 BETWEEN '1901' AND '2020' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 IN ('1901','2020') ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 IN ('1901','2020') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 >= '1901' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 >= '1901' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t2; --sorted_result SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t2; --sorted_result SELECT * FROM t2 WHERE c3 = '1998-12-11'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t2; ## Backward index scan, covering ## SELECT c1,c2 FROM t2 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t2 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t2 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t2 WHERE c1 = '10' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 = '10' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 = '10' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 = '10' ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t2 WHERE c1 <> '10' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 <> '10' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 > '10' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 > '10' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 >= '10' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 >= '10' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 < '10' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 < '10' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 <= '10' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 <= '10' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 <=> '10' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 <=> '10' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 BETWEEN '10' AND '2020' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 BETWEEN '10' AND '2020' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 IN ('10','2020') ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 IN ('10','2020') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 >= '10' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 >= '10' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t2 WHERE c1 <> '10' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 <> '10' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 > '10' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 > '10' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 >= '10' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 >= '10' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 < '10' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 < '10' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 <= '10' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 <= '10' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 <=> '10' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 <=> '10' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 BETWEEN '10' AND '2020' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 BETWEEN '10' AND '2020' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 IN ('10','2020') ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 IN ('10','2020') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 >= '10' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 >= '10' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t2; --sorted_result SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t2; --sorted_result SELECT * FROM t2 WHERE c3 = '1998-12-11'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t2; ## Backward index scan, covering ## SELECT c1,c2 FROM t2 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t2 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t2 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t2 WHERE c2 = 2155 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 = 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 = 2155 ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 = 2155 ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t2 WHERE c2 <> 2155 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 <> 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 > 2155 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 > 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 >= 2155 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 >= 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 < 2155 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 < 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 <= 2155 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 <= 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 <=> 2155 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 <=> 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 BETWEEN '1970' AND 2155 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 BETWEEN '1970' AND 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 IN ('1970',2155) ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 IN ('1970',2155) ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 >= '1970' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 >= '1970' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t2 WHERE c2 <> 2155 ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 <> 2155 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 > 2155 ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 > 2155 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 >= 2155 ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 >= 2155 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 < 2155 ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 < 2155 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 <= 2155 ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 <= 2155 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 <=> 2155 ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 <=> 2155 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 BETWEEN '1970' AND 2155 ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 BETWEEN '1970' AND 2155 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 IN ('1970',2155) ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 IN ('1970',2155) ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 >= '1970' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 >= '1970' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t2; --sorted_result SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t2; --sorted_result SELECT * FROM t2 WHERE c3 = '1998-12-11'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t2; ## Backward index scan, covering ## SELECT c1,c2 FROM t2 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t2 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t2 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t2 WHERE c2 = NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 = NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 = NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 = NULL ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t2 WHERE c2 <> NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 <> NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 > NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 > NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 >= NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 >= NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 < NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 < NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 <= NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 <= NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 <=> NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t2 WHERE c2 <> NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 <> NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 > NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 > NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 >= NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 >= NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 < NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 < NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 <= NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 <= NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 <=> NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 <=> NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t3; --sorted_result SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t3; --sorted_result SELECT * FROM t3 WHERE c3 = '1998-12-11'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t3; ## Backward index scan, covering ## SELECT c1,c2 FROM t3 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t3 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t3 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t3 WHERE c1 = '1970' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 = '1970' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 = '1970' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 = '1970' ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t3 WHERE c1 <> '1970' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 <> '1970' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 > '1970' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 > '1970' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 >= '1970' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 >= '1970' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 < '1970' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 < '1970' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 <= '1970' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 <= '1970' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 <=> '1970' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 <=> '1970' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 BETWEEN '1970' AND '2020' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 BETWEEN '1970' AND '2020' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 IN ('1970','2020') ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 IN ('1970','2020') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 >= '1970' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 >= '1970' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t3 WHERE c1 <> '1970' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 <> '1970' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 > '1970' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 > '1970' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 >= '1970' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 >= '1970' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 < '1970' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 < '1970' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 <= '1970' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 <= '1970' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 <=> '1970' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 <=> '1970' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 BETWEEN '1970' AND '2020' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 BETWEEN '1970' AND '2020' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 IN ('1970','2020') ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 IN ('1970','2020') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 >= '1970' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 >= '1970' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t3; --sorted_result SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t3; --sorted_result SELECT * FROM t3 WHERE c3 = '1998-12-11'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t3; ## Backward index scan, covering ## SELECT c1,c2 FROM t3 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t3 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t3 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t3 WHERE c1 = '69' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 = '69' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 = '69' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 = '69' ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t3 WHERE c1 <> '69' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 <> '69' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 > '69' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 > '69' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 >= '69' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 >= '69' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 < '69' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 < '69' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 <= '69' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 <= '69' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 <=> '69' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 <=> '69' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 BETWEEN 69 AND '2020' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 BETWEEN 69 AND '2020' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 IN (69,'2020') ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 IN (69,'2020') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 >= 69 AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 >= 69 AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t3 WHERE c1 <> '69' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 <> '69' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 > '69' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 > '69' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 >= '69' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 >= '69' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 < '69' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 < '69' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 <= '69' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 <= '69' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 <=> '69' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 <=> '69' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 BETWEEN 69 AND '2020' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 BETWEEN 69 AND '2020' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 IN (69,'2020') ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 IN (69,'2020') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 >= 69 AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 >= 69 AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t3; --sorted_result SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t3; --sorted_result SELECT * FROM t3 WHERE c3 = '1998-12-11'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t3; ## Backward index scan, covering ## SELECT c1,c2 FROM t3 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t3 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t3 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t3 WHERE c2 = 2000 ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 = 2000 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 = 2000 ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 = 2000 ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t3 WHERE c2 <> 2000 ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 <> 2000 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 > 2000 ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 > 2000 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 >= 2000 ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 >= 2000 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 < 2000 ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 < 2000 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 <= 2000 ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 <= 2000 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 <=> 2000 ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 <=> 2000 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 BETWEEN '2000' AND 2155 ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 BETWEEN '2000' AND 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 IN ('2000',2155) ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 IN ('2000',2155) ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 >= '2000' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 >= '2000' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t3 WHERE c2 <> 2000 ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 <> 2000 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 > 2000 ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 > 2000 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 >= 2000 ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 >= 2000 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 < 2000 ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 < 2000 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 <= 2000 ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 <= 2000 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 <=> 2000 ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 <=> 2000 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 BETWEEN '2000' AND 2155 ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 BETWEEN '2000' AND 2155 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 IN ('2000',2155) ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 IN ('2000',2155) ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 >= '2000' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 >= '2000' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t3; --sorted_result SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t3; --sorted_result SELECT * FROM t3 WHERE c3 = '1998-12-11'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t3; ## Backward index scan, covering ## SELECT c1,c2 FROM t3 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t3 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t3 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t3 WHERE c2 = NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 = NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 = NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 = NULL ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t3 WHERE c2 <> NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 <> NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 > NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 > NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 >= NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 >= NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 < NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 < NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 <= NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 <= NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 <=> NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t3 WHERE c2 <> NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 <> NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 > NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 > NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 >= NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 >= NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 < NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 < NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 <= NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 <= NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 <=> NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 <=> NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; # Using index for group-by --sorted_result SELECT c1 FROM t3 GROUP BY c1; --sorted_result SELECT DISTINCT c1 FROM t3; --sorted_result SELECT c1,MIN(c2) FROM t3 GROUP BY c1; # Using Index-merge --sorted_result SELECT * FROM t1 WHERE c1='1901' OR c2='2155'; # Test Error conditions- PK constraint violation, Unique constraint violation # Insert duplicate value to pk column --error ER_DUP_ENTRY INSERT INTO t1(c1,c2) VALUES(01,'99'); # Insert duplicate value to clustered pk, throws error --error ER_DUP_ENTRY INSERT INTO t2(c1,c2) VALUES(99,99); # Insert duplicate value to unique column, throws error --error ER_DUP_ENTRY INSERT INTO t1(c1,c2) VALUES('2098',69); # Insert duplicate value to clustered unique column, throws error --error ER_DUP_ENTRY INSERT INTO t3(c1,c2) VALUES(00,00); # Test 'INSERT IGNORE' # Ignore pk constraint INSERT IGNORE INTO t1(c1,c2) VALUES(01,'99'); INSERT IGNORE INTO t2(c1,c2) VALUES('1999','1999'); # Ignore unique constraint INSERT IGNORE INTO t1(c1,c2) VALUES('2098','69'); INSERT IGNORE INTO t3(c1,c2) VALUES(00,00); SELECT * FROM t1 WHERE c1='01' /* Returns 1 row */; SELECT * FROM t2 WHERE c1='1999' AND c2='1999' /* Returns 1 row */; SELECT * FROM t1 WHERE c2='69' /* Returns 1 row */; SELECT * FROM t3 WHERE c1=0 AND c2=0 /* Returns 1 row */; # Test 'INSERT ON DUPLICATE KEY UPDATE' with PK one column/multi-column SELECT * FROM t1 WHERE c1='02' /* Returns no rows */; INSERT INTO t1(c1) VALUES('00') ON DUPLICATE KEY UPDATE c1='02'; SELECT * FROM t1 WHERE c1='2002' /* Returns 1 row */; SELECT * FROM t2 WHERE c1=69 AND c2=70 /* Returns no rows */; INSERT INTO t2 VALUES(99,99,'98-12-20','98.12.20 11:30:45') ON DUPLICATE KEY UPDATE c1=69,c2=70; SELECT * FROM t2 WHERE c1=2069 AND c2=1970 /* Returns 1 row */; # Test 'INSERT ON DUPLICATE KEY UPDATE' with unique one column/multi-column SELECT * FROM t1 WHERE c1=54 AND c2=53 /* Returns no rows */; INSERT INTO t1 VALUES(69,69,'98-12-18','98.12.18 11:30:45') ON DUPLICATE KEY UPDATE c1=54,c2=53; SELECT * FROM t1 WHERE c1=54 AND c2=53 /* Returns 1 row */; SELECT * FROM t3 WHERE c1=6 AND c2=1970 /* Returns no rows */; INSERT INTO t3(c1,c2) VALUES('69','69') ON DUPLICATE KEY UPDATE c1=06,c2=70; SELECT * FROM t3 WHERE c1=2006 AND c2=1970 /* Returns 1 row */; # Test 'INSERT INTO SELECT FROM' CREATE TABLE t5(c1 YEAR NOT NULL, c2 YEAR NULL, c3 DATE, c4 DATETIME, INDEX idx(c1,c2)); INSERT INTO t5 SELECT * FROM t1; --sorted_result SELECT * FROM t5; TRUNCATE TABLE t5; INSERT INTO t5 SELECT * FROM t2 WHERE c1 >=1970 AND c1 < 1999 AND c2 <> '1990'; --sorted_result SELECT * FROM t5; TRUNCATE TABLE t5; INSERT INTO t5 SELECT * FROM t3 WHERE c2 BETWEEN '2000' AND '2155'; --sorted_result SELECT * FROM t5; TRUNCATE TABLE t5; DROP TABLE t5; # Test 'INSERT SET' INSERT INTO t1 SET c1='00'; INSERT INTO t2 SET c1=69; INSERT INTO t3 SET c1=70; INSERT INTO t4 SET c2=99; --sorted_result SELECT * FROM t1 WHERE c1=0; --sorted_result SELECT * FROM t2 WHERE c1=69; --sorted_result SELECT * FROM t3 WHERE c1=70; --sorted_result SELECT * FROM t4 WHERE c2=99; DROP TABLE t1,t2,t3,t4; ######## Running INSERT tests for YEAR(2) ######## # Create tables CREATE TABLE t1(c1 YEAR(2) NOT NULL, c2 YEAR(2) NULL, c3 DATE, c4 DATETIME, PRIMARY KEY(c1), UNIQUE INDEX(c2)); CREATE TABLE t2(c1 YEAR(2) NOT NULL, c2 YEAR(2) NULL, c3 DATE, c4 DATETIME, PRIMARY KEY(c1,c2)); CREATE TABLE t3(c1 YEAR(2) NOT NULL, c2 YEAR(2) NULL, c3 DATE, c4 DATETIME, UNIQUE INDEX idx(c1,c2)); CREATE TABLE t4(c1 YEAR(2) NOT NULL, c2 YEAR(2) NULL, c3 DATE, c4 DATETIME); # Insert some rows with targeted values # As a four-digit string in the range '1901' to '2155' INSERT INTO t1 VALUES('1901','1901','98-12-31','98.12.31 11:30:45'),('1999','1999','98-12-30','98.12.30 11:30:45'),('2000','2000','98-12-29','98.12.29 11:30:45'),('2001','2001','98-12-28','98.12.28 11:30:45'),('2099','2099','98-12-27','98.12.27 11:30:45'),('2100','2100','98-12-26','98.12.26 11:30:45'),('2155','2155','98-12-26','98.12.26 11:30:45'); INSERT INTO t2 VALUES('1901','1901','98-12-31','98.12.31 11:30:45'),('1999','1999','98-12-30','98.12.30 11:30:45'),('2000','2000','98-12-29','98.12.29 11:30:45'),('2001','2001','98-12-28','98.12.28 11:30:45'),('2099','2099','98-12-27','98.12.27 11:30:45'),('2100','2100','98-12-26','98.12.26 11:30:45'),('2155','2155','98-12-26','98.12.26 11:30:45'); INSERT INTO t3 VALUES('1901','1901','98-12-31','98.12.31 11:30:45'),('1999','1999','98-12-30','98.12.30 11:30:45'),('2000','2000','98-12-29','98.12.29 11:30:45'),('2001','2001','98-12-28','98.12.28 11:30:45'),('2099','2099','98-12-27','98.12.27 11:30:45'),('2100','2100','98-12-26','98.12.26 11:30:45'),('2155','2155','98-12-26','98.12.26 11:30:45'); INSERT INTO t4 VALUES('1901','1901','98-12-31','98.12.31 11:30:45'),('1999','1999','98-12-30','98.12.30 11:30:45'),('2000','2000','98-12-29','98.12.29 11:30:45'),('2001','2001','98-12-28','98.12.28 11:30:45'),('2099','2099','98-12-27','98.12.27 11:30:45'),('2100','2100','98-12-26','98.12.26 11:30:45'),('2155','2155','98-12-25','98.12.25 11:30:45'); # As a four-digit number in the range 1901 to 2155 INSERT INTO t4 VALUES(1901,1901,'98-12-24','98.12.24 11:30:45'),(1999,1999,'98-12-23','98.12.23 11:30:45'),(2000,2000,'98-12-22','98.12.22 11:30:45'),(2001,2001,'98-12-21','98.12.21 11:30:45'),(2099,2099,'98-12-20','98.12.20 11:30:45'),(2100,2100,'98-12-19','98.12.19 11:30:45'),(2155,2155,'98-12-18','98.12.18 11:30:45'); # As a two-digit string in the range '00' to '99' INSERT INTO t1 VALUES('20','20','98-12-16','98.12.16 11:30:45'),('40','40','98-12-15','98.12.15 11:30:45'),('60','60','98-12-14','98.12.14 11:30:45'),('69','69','98-12-13','98.12.13 11:30:45'),('70','70','98-12-12','98.12.12 11:30:45'),('90','90','98-12-11','98.12.11 11:30:45'); INSERT INTO t2 VALUES('00','10','98-12-17','98.12.17 11:30:45'),('20','20','98-12-16','98.12.16 11:30:45'),('40','40','98-12-15','98.12.15 11:30:45'),('60','60','98-12-14','98.12.14 11:30:45'),('69','69','98-12-13','98.12.13 11:30:45'),('70','70','98-12-12','98.12.12 11:30:45'),('90','90','98-12-11','98.12.11 11:30:45'),('99','98','98-12-10','98.12.10 11:30:45'); INSERT INTO t3 VALUES('00','10','98-12-17','98.12.17 11:30:45'),('20','20','98-12-16','98.12.16 11:30:45'),('40','40','98-12-15','98.12.15 11:30:45'),('60','60','98-12-14','98.12.14 11:30:45'),('69','69','98-12-13','98.12.13 11:30:45'),('70','70','98-12-12','98.12.12 11:30:45'),('90','90','98-12-11','98.12.11 11:30:45'),('99','98','98-12-10','98.12.10 11:30:45'); INSERT INTO t4 VALUES('00','00','98-12-17','98.12.17 11:30:45'),('20','20','98-12-16','98.12.16 11:30:45'),('40','40','98-12-15','98.12.15 11:30:45'),('60','60','98-12-14','98.12.14 11:30:45'),('69','69','98-12-13','98.12.13 11:30:45'),('70','70','98-12-12','98.12.12 11:30:45'),('90','90','98-12-11','98.12.11 11:30:45'),('99','99','98-12-10','98.12.10 11:30:45'); # As a two-digit number in the range 1 to 99 INSERT INTO t4 VALUES(1,1,'98-12-9','98.12.9 11:30:45'),(20,20,'98-12-8','98.12.8 11:30:45'),(40,40,'98-12-7','98.12.7 11:30:45'),(60,60,'98-12-6','98.12.6 11:30:45'),(69,69,'98-12-5','98.12.5 11:30:45'),(70,70,'98-12-4','98.12.4 11:30:45'),(90,90,'98-12-3','98.12.3 11:30:45'),(99,99,'98-12-2','98.12.2 11:30:45'); # As the result of a function SET TIMESTAMP=1233216687; # 2009-01-29 13:41:27 INSERT INTO t1 VALUES(NOW(),CURRENT_DATE,NOW(),CURRENT_DATE); INSERT INTO t2 VALUES(NOW(),CURRENT_DATE,NOW(),CURRENT_DATE); INSERT INTO t3 VALUES(NOW(),CURRENT_DATE,NOW(),CURRENT_DATE); INSERT INTO t4 VALUES(NOW(),CURRENT_DATE,NOW(),CURRENT_DATE); # Insert other valid year values one digit strings, '0', '000' INSERT INTO t1(c1) VALUES('5'); INSERT INTO t2(c1) VALUES('5'); INSERT INTO t3(c1) VALUES('5'); INSERT INTO t4(c1,c2) VALUES('0','000'); # Insert invalid year values, outside range, would be converted to zero value INSERT INTO t2 VALUES(1900,2156,'08-01-06','08/01/07'); INSERT INTO t3 VALUES('1900','2156','08-01-08','08/1/9'); INSERT INTO t4 VALUES('1900','2156','08-01-08','08/1/9'),(1900,2156,'08-01-08','08/1/9'),(0,00,'08-01-08','08/1/9'),(000,000,'08-01-08','08/1/9'),('-20','100','08-01-08','08/1/9'),(-20,100,'08-01-08','08/1/9'); # Insert empty string '', would be converted to zero INSERT INTO t1 VALUES('','','08-01-04','08/01/05') /* Inserts zero dates for '' strings */; # Insert non-date value(absurd values), would be converted to zero INSERT INTO t4 VALUES('abcd','abcd','08-01-10','08/01/11'),(1234,1234,'08-01-12','08/01/13') /* Inserts zero dates for absurd dates */; # Insert duplicates for parts of the clustered key/unique index INSERT INTO t2 VALUES('20','30','98-12-16','98.12.16 11:30:45'),('40','20','98-12-15','98.12.15 11:30:45'); INSERT INTO t3 VALUES('00','20','98-12-17','98.12.17 11:30:45'),('20','40','98-12-10','98.12.10 11:30:45'); # Insert permissible NULLs INSERT INTO t1 VALUES(80,NULL,'08-01-02','08/01/03'); INSERT INTO t3 VALUES(80,NULL,'08-01-02','08/01/03'); INSERT INTO t4 VALUES(80,NULL,'08-01-02','08/01/03'); # Insert duplicate NULLs to unique column INSERT INTO t1(c1,c2) VALUES(1992,NULL); --sorted_result SELECT * FROM t1; --sorted_result SELECT * FROM t2; --sorted_result SELECT * FROM t3; --sorted_result SELECT * FROM t4; # Now select using various table access methods (full table scan, range scan, index scan etc.) ## Full table scan ## --sorted_result SELECT * FROM t2; --sorted_result SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t2; --sorted_result SELECT * FROM t2 WHERE c3 = '1998-12-11'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t2; ## Backward index scan, covering ## SELECT c1,c2 FROM t2 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t2 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t2 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t2 WHERE c1 = '1901' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 = '1901' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 = '1901' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 = '1901' ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t2 WHERE c1 <> '1901' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 <> '1901' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 > '1901' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 > '1901' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 >= '1901' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 >= '1901' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 < '1901' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 < '1901' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 <= '1901' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 <= '1901' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 <=> '1901' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 <=> '1901' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 BETWEEN '1901' AND '2020' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 BETWEEN '1901' AND '2020' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 IN ('1901','2020') ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 IN ('1901','2020') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 >= '1901' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 >= '1901' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t2 WHERE c1 <> '1901' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 <> '1901' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 > '1901' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 > '1901' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 >= '1901' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 >= '1901' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 < '1901' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 < '1901' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 <= '1901' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 <= '1901' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 <=> '1901' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 <=> '1901' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 BETWEEN '1901' AND '2020' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 BETWEEN '1901' AND '2020' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 IN ('1901','2020') ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 IN ('1901','2020') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 >= '1901' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 >= '1901' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t2; --sorted_result SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t2; --sorted_result SELECT * FROM t2 WHERE c3 = '1998-12-11'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t2; ## Backward index scan, covering ## SELECT c1,c2 FROM t2 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t2 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t2 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t2 WHERE c1 = '10' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 = '10' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 = '10' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 = '10' ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t2 WHERE c1 <> '10' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 <> '10' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 > '10' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 >= '10' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 < '10' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 <= '10' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 <=> '10' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 <=> '10' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 BETWEEN '10' AND '2020' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 BETWEEN '10' AND '2020' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 IN ('10','2020') ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 IN ('10','2020') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 >= '10' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 >= '10' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t2 WHERE c1 <> '10' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 <> '10' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 > '10' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 >= '10' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 < '10' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 <= '10' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 <=> '10' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 <=> '10' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 BETWEEN '10' AND '2020' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 BETWEEN '10' AND '2020' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 IN ('10','2020') ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 IN ('10','2020') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 >= '10' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 >= '10' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t2; --sorted_result SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t2; --sorted_result SELECT * FROM t2 WHERE c3 = '1998-12-11'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t2; ## Backward index scan, covering ## SELECT c1,c2 FROM t2 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t2 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t2 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t2 WHERE c2 = 2155 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 = 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 = 2155 ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 = 2155 ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t2 WHERE c2 <> 2155 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 <> 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 > 2155 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 > 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 >= 2155 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 >= 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 < 2155 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 < 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 <= 2155 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 <= 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 <=> 2155 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 <=> 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 BETWEEN '1970' AND 2155 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 BETWEEN '1970' AND 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 IN ('1970',2155) ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 IN ('1970',2155) ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 >= '1970' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 >= '1970' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t2 WHERE c2 <> 2155 ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 <> 2155 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 > 2155 ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 > 2155 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 >= 2155 ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 >= 2155 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 < 2155 ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 < 2155 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 <= 2155 ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 <= 2155 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 <=> 2155 ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 <=> 2155 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 BETWEEN '1970' AND 2155 ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 BETWEEN '1970' AND 2155 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 IN ('1970',2155) ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 IN ('1970',2155) ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 >= '1970' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 >= '1970' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t2; --sorted_result SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t2; --sorted_result SELECT * FROM t2 WHERE c3 = '1998-12-11'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t2; ## Backward index scan, covering ## SELECT c1,c2 FROM t2 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t2 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t2 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t2 WHERE c2 = NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 = NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 = NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 = NULL ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t2 WHERE c2 <> NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 <> NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 > NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 > NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 >= NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 >= NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 < NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 < NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 <= NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 <= NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 <=> NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t2 WHERE c2 <> NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 <> NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 > NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 > NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 >= NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 >= NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 < NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 < NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 <= NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 <= NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 <=> NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 <=> NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t3; --sorted_result SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t3; --sorted_result SELECT * FROM t3 WHERE c3 = '1998-12-11'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t3; ## Backward index scan, covering ## SELECT c1,c2 FROM t3 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t3 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t3 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t3 WHERE c1 = '1970' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 = '1970' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 = '1970' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 = '1970' ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t3 WHERE c1 <> '1970' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 <> '1970' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 > '1970' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 > '1970' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 >= '1970' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 >= '1970' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 < '1970' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 < '1970' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 <= '1970' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 <= '1970' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 <=> '1970' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 <=> '1970' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 BETWEEN '1970' AND '2020' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 BETWEEN '1970' AND '2020' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 IN ('1970','2020') ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 IN ('1970','2020') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 >= '1970' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 >= '1970' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t3 WHERE c1 <> '1970' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 <> '1970' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 > '1970' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 > '1970' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 >= '1970' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 >= '1970' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 < '1970' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 < '1970' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 <= '1970' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 <= '1970' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 <=> '1970' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 <=> '1970' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 BETWEEN '1970' AND '2020' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 BETWEEN '1970' AND '2020' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 IN ('1970','2020') ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 IN ('1970','2020') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 >= '1970' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 >= '1970' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t3; --sorted_result SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t3; --sorted_result SELECT * FROM t3 WHERE c3 = '1998-12-11'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t3; ## Backward index scan, covering ## SELECT c1,c2 FROM t3 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t3 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t3 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t3 WHERE c1 = '69' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 = '69' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 = '69' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 = '69' ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t3 WHERE c1 <> '69' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 <> '69' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 > '69' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 > '69' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 >= '69' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 >= '69' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 < '69' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 < '69' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 <= '69' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 <= '69' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 <=> '69' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 <=> '69' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 BETWEEN 69 AND '2020' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 BETWEEN 69 AND '2020' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 IN (69,'2020') ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 IN (69,'2020') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 >= 69 AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 >= 69 AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t3 WHERE c1 <> '69' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 <> '69' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 > '69' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 > '69' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 >= '69' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 >= '69' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 < '69' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 < '69' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 <= '69' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 <= '69' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 <=> '69' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 <=> '69' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 BETWEEN 69 AND '2020' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 BETWEEN 69 AND '2020' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 IN (69,'2020') ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 IN (69,'2020') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 >= 69 AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 >= 69 AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t3; --sorted_result SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t3; --sorted_result SELECT * FROM t3 WHERE c3 = '1998-12-11'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t3; ## Backward index scan, covering ## SELECT c1,c2 FROM t3 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t3 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t3 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t3 WHERE c2 = 2000 ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 = 2000 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 = 2000 ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 = 2000 ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t3 WHERE c2 <> 2000 ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 <> 2000 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 > 2000 ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 > 2000 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 >= 2000 ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 >= 2000 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 < 2000 ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 < 2000 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 <= 2000 ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 <= 2000 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 <=> 2000 ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 <=> 2000 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 BETWEEN '2000' AND 2155 ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 BETWEEN '2000' AND 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 IN ('2000',2155) ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 IN ('2000',2155) ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 >= '2000' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 >= '2000' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t3 WHERE c2 <> 2000 ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 <> 2000 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 > 2000 ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 > 2000 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 >= 2000 ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 >= 2000 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 < 2000 ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 < 2000 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 <= 2000 ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 <= 2000 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 <=> 2000 ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 <=> 2000 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 BETWEEN '2000' AND 2155 ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 BETWEEN '2000' AND 2155 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 IN ('2000',2155) ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 IN ('2000',2155) ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 >= '2000' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 >= '2000' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t3; --sorted_result SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t3; --sorted_result SELECT * FROM t3 WHERE c3 = '1998-12-11'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t3; ## Backward index scan, covering ## SELECT c1,c2 FROM t3 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t3 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t3 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t3 WHERE c2 = NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 = NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 = NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 = NULL ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t3 WHERE c2 <> NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 <> NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 > NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 > NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 >= NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 >= NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 < NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 < NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 <= NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 <= NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 <=> NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t3 WHERE c2 <> NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 <> NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 > NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 > NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 >= NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 >= NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 < NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 < NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 <= NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 <= NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 <=> NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 <=> NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; # Using index for group-by --sorted_result SELECT c1 FROM t3 GROUP BY c1; --sorted_result SELECT DISTINCT c1 FROM t3; --sorted_result SELECT c1,MIN(c2) FROM t3 GROUP BY c1; # Using Index-merge --sorted_result SELECT * FROM t1 WHERE c1='1901' OR c2='2155'; # Test Error conditions- PK constraint violation, Unique constraint violation # Insert duplicate value to pk column --error ER_DUP_ENTRY INSERT INTO t1(c1,c2) VALUES(01,'99'); # Insert duplicate value to clustered pk, throws error --error ER_DUP_ENTRY INSERT INTO t2(c1,c2) VALUES(99,99); # Insert duplicate value to unique column, throws error --error ER_DUP_ENTRY INSERT INTO t1(c1,c2) VALUES('2098',69); # Insert duplicate value to clustered unique column, throws error --error ER_DUP_ENTRY INSERT INTO t3(c1,c2) VALUES(00,00); # Test 'INSERT IGNORE' # Ignore pk constraint INSERT IGNORE INTO t1(c1,c2) VALUES(01,'99'); INSERT IGNORE INTO t2(c1,c2) VALUES('1999','1999'); # Ignore unique constraint INSERT IGNORE INTO t1(c1,c2) VALUES('2098','69'); INSERT IGNORE INTO t3(c1,c2) VALUES(00,00); SELECT * FROM t1 WHERE c1='01' /* Returns 1 row */; SELECT * FROM t2 WHERE c1='1999' AND c2='1999' /* Returns 1 row */; SELECT * FROM t1 WHERE c2='69' /* Returns 1 row */; SELECT * FROM t3 WHERE c1=0 AND c2=0 /* Returns 1 row */; # Test 'INSERT ON DUPLICATE KEY UPDATE' with PK one column/multi-column SELECT * FROM t1 WHERE c1='02' /* Returns no rows */; INSERT INTO t1(c1) VALUES('00') ON DUPLICATE KEY UPDATE c1='02'; SELECT * FROM t1 WHERE c1='2002' /* Returns 1 row */; SELECT * FROM t2 WHERE c1=69 AND c2=70 /* Returns no rows */; INSERT INTO t2 VALUES(99,99,'98-12-20','98.12.20 11:30:45') ON DUPLICATE KEY UPDATE c1=69,c2=70; SELECT * FROM t2 WHERE c1=2069 AND c2=1970 /* Returns 1 row */; # Test 'INSERT ON DUPLICATE KEY UPDATE' with unique one column/multi-column SELECT * FROM t1 WHERE c1=54 AND c2=53 /* Returns no rows */; INSERT INTO t1 VALUES(69,69,'98-12-18','98.12.18 11:30:45') ON DUPLICATE KEY UPDATE c1=54,c2=53; SELECT * FROM t1 WHERE c1=54 AND c2=53 /* Returns 1 row */; SELECT * FROM t3 WHERE c1=6 AND c2=1970 /* Returns no rows */; INSERT INTO t3(c1,c2) VALUES('69','69') ON DUPLICATE KEY UPDATE c1=06,c2=70; SELECT * FROM t3 WHERE c1=2006 AND c2=1970 /* Returns 1 row */; # Test 'INSERT INTO SELECT FROM' CREATE TABLE t5(c1 YEAR NOT NULL, c2 YEAR NULL, c3 DATE, c4 DATETIME, INDEX idx(c1,c2)); INSERT INTO t5 SELECT * FROM t1; --sorted_result SELECT * FROM t5; TRUNCATE TABLE t5; INSERT INTO t5 SELECT * FROM t2 WHERE c1 >=1970 AND c1 < 1999 AND c2 <> '1990'; --sorted_result SELECT * FROM t5; TRUNCATE TABLE t5; INSERT INTO t5 SELECT * FROM t3 WHERE c2 BETWEEN '2000' AND '2155'; --sorted_result SELECT * FROM t5; TRUNCATE TABLE t5; DROP TABLE t5; # Test 'INSERT SET' INSERT INTO t1 SET c1='00'; INSERT INTO t2 SET c1=69; INSERT INTO t3 SET c1=70; INSERT INTO t4 SET c2=99; --sorted_result SELECT * FROM t1 WHERE c1=0; --sorted_result SELECT * FROM t2 WHERE c1=69; --sorted_result SELECT * FROM t3 WHERE c1=70; --sorted_result SELECT * FROM t4 WHERE c2=99; DROP TABLE t1,t2,t3,t4;
Back to File Manager