Edit File: insert_time.test
--disable_warnings DROP TABLE IF EXISTS t1,t2,t3,t4; --enable_warnings ######## Running INSERT tests for TIME ######## # Create tables CREATE TABLE t1(c1 TIME NOT NULL, c2 TIME NULL, c3 DATE, PRIMARY KEY(c1), UNIQUE INDEX(c2)); CREATE TABLE t2(c1 TIME NOT NULL, c2 TIME NULL, c3 DATE, PRIMARY KEY(c1,c2)); CREATE TABLE t3(c1 TIME NOT NULL, c2 TIME NULL, c3 DATE, UNIQUE INDEX idx(c1,c2)); CREATE TABLE t4(c1 TIME NOT NULL, c2 TIME NULL, c3 DATE); # Insert some rows with targeted values # As a string in 'D HH:MM:SS.fraction' format INSERT INTO t1 VALUES('12:34:56.78','12:34:56.78','2009-01-01'),('10:22:33','10:22:33','2009-01-02'),('1:23','1:23','2009-01-03'),('20 11:22:33','20 11:22:33','2009-01-04'),('34 9:23','34 9:23','2009-01-05'),('0 10','0 10','2009-01-06'),('45','45','2009-01-07'),('-838:59:59','-838:59:59','2009-01-08'),('0','0','2009-01-09'),('838:59:59','838:59:59','2009-01-10'); INSERT INTO t2 VALUES('12:34:56.78','12:34:56.78','2009-01-01'),('10:22:33','10:22:33','2009-01-02'),('1:23','1:23','2009-01-03'),('20 11:22:33','20 11:22:33','2009-01-04'),('34 9:23','34 9:23','2009-01-05'),('0 10','0 10','2009-01-06'),('45','45','2009-01-07'),('-838:59:59','-838:59:59','2009-01-08'),('0','0','2009-01-09'),('838:59:59','838:59:59','2009-01-10'); INSERT INTO t3 VALUES('12:34:56.78','12:34:56.78','2009-01-01'),('10:22:33','10:22:33','2009-01-02'),('1:23','1:23','2009-01-03'),('20 11:22:33','20 11:22:33','2009-01-04'),('34 9:23','34 9:23','2009-01-05'),('0 10','0 10','2009-01-06'),('45','45','2009-01-07'),('-838:59:59','-838:59:59','2009-01-08'),('0','0','2009-01-09'),('838:59:59','838:59:59','2009-01-10'); INSERT INTO t4 VALUES('12:34:56.78','12:34:56.78','2009-01-01'),('10:22:33','10:22:33','2009-01-02'),('1:23','1:23','2009-01-03'),('20 11:22:33','20 11:22:33','2009-01-04'),('34 9:23','34 9:23','2009-01-05'),('0 10','0 10','2009-01-06'),('45','45','2009-01-07'),('-838:59:59','-838:59:59','2009-01-08'),('0','0','2009-01-09'),('838:59:59','838:59:59','2009-01-10'); # As a string with no delimiters in 'HHMMSS' format INSERT INTO t1 VALUES('101112','101112','2009-01-11'); INSERT INTO t2 VALUES('101112','101112','2009-01-11'); INSERT INTO t3 VALUES('101112','101112','2009-01-11'); INSERT INTO t4 VALUES('101112','101112','2009-01-11'); # As a number in HHMMSS format INSERT INTO t1 VALUES(111112,111112,'2009-01-12'),(12,12,'2009-01-13'),(1234,1234,'2009-01-14'),(123458,123458,'2009-01-15'),(123556.99,123556.99,'2009-01-16'); INSERT INTO t2 VALUES(111112,111112,'2009-01-12'),(12,12,'2009-01-13'),(1234,1234,'2009-01-14'),(123458,123458,'2009-01-15'),(123556.99,123556.99,'2009-01-16'); INSERT INTO t3 VALUES(111112,111112,'2009-01-12'),(12,12,'2009-01-13'),(1234,1234,'2009-01-14'),(123458,123458,'2009-01-15'),(123556.99,123556.99,'2009-01-16'); INSERT INTO t4 VALUES(111112,111112,'2009-01-12'),(12,12,'2009-01-13'),(1234,1234,'2009-01-14'),(123458,123458,'2009-01-15'),(123556.99,123556.99,'2009-01-16'); # As the result of a function SET TIMESTAMP=1233216687; # 2009-01-29 13:41:27 INSERT INTO t1 VALUES(CURRENT_TIME(),CURRENT_TIME(),'2009-01-17'); INSERT INTO t2 VALUES(CURRENT_TIME(),CURRENT_TIME(),'2009-01-17'); INSERT INTO t3 VALUES(CURRENT_TIME(),CURRENT_TIME(),'2009-01-17'); INSERT INTO t4 VALUES(CURRENT_TIME(),CURRENT_TIME(),'2009-01-17'); # Other valid formats (single digit, zero hrs,mins,secs.) INSERT INTO t1 VALUES('8:3:2','8:3:2','2009-01-18'),('1112','1112','2009-01-19'),(11,11,'2009-01-20'),('00:12:30','00:12:30','2009-01-23'),('9:00:45','9:00:45','2009-01-24'),('9:36:00','9:36:00','2009-01-25'); INSERT INTO t2 VALUES('8:3:2','8:3:2','2009-01-18'),('1112','1112','2009-01-19'),(11,11,'2009-01-20'),('00:12:30','00:12:30','2009-01-23'),('9:00:45','9:00:45','2009-01-24'),('9:36:00','9:36:00','2009-01-25'); INSERT INTO t3 VALUES('8:3:2','8:3:2','2009-01-18'),('1112','1112','2009-01-19'),(11,11,'2009-01-20'),('00:12:30','00:12:30','2009-01-23'),('9:00:45','9:00:45','2009-01-24'),('9:36:00','9:36:00','2009-01-25'); INSERT INTO t4 VALUES('8:3:2','8:3:2','2009-01-18'),('1112','1112','2009-01-19'),(11,11,'2009-01-20'),('00:12:30','00:12:30','2009-01-23'),('9:00:45','9:00:45','2009-01-24'),('9:36:00','9:36:00','2009-01-25'); # Outside range would be clipped to closest end point --error ER_DUP_ENTRY INSERT INTO t1 VALUES('-850:00:00','-850:00:00','2009-01-21'); --error ER_DUP_ENTRY INSERT INTO t1 VALUES('850:00:00','850:00:00','2009-01-21'); DELETE FROM t1 WHERE c1='-838:59:59' AND c2='-838:59:59'; DELETE FROM t1 WHERE c1='838:59:59' AND c2='838:59:59'; INSERT INTO t1 VALUES('-850:00:00','-850:00:00','2009-01-21'),('850:00:00','850:00:00','2009-01-21'); # Insert invalid values INSERT INTO t4 VALUES('10.22.22','10.22.22','2009-01-26'),(1234567,1234567,'2009-01-27'),(123456789,123456789,'2009-01-28'),(123456789.10,123456789.10,'2009-01-29'),('10 22:22','10 22:22','2009-01-30'),('12.45a','12.45a','2009-01-31'); # Insert permissible NULLs INSERT INTO t1 VALUES('8:29:45',NULL,'2009-02-01'); INSERT INTO t3 VALUES('8:29:45',NULL,'2009-02-01'); INSERT INTO t4 VALUES('8:29:45',NULL,'2009-02-01'); # Insert duplicate NULLs to unique column INSERT INTO t1(c1,c2) VALUES('9:30',NULL); DELETE FROM t1 WHERE c1='9:30' AND c2 IS 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 t1; --sorted_result SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t1; --sorted_result SELECT * FROM t1 WHERE c3 = '2009-01-17'; ## Forward index scan, covering ## --sorted_result SELECT c1 FROM t1; ## Backward index scan, covering ## SELECT c1 FROM t1 ORDER BY c1 DESC; ## Forward index scan, non-covering ## SELECT * FROM t1 ORDER BY c1 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t1 ORDER BY c1 DESC LIMIT 2; ## ref type access SELECT * FROM t1 WHERE c1 = '838:59:59' ORDER BY c1; SELECT * FROM t1 WHERE c1 = '838:59:59' ORDER BY c1 LIMIT 2; SELECT * FROM t1 WHERE c1 = '838:59:59' ORDER BY c1 DESC; SELECT * FROM t1 WHERE c1 = '838:59:59' ORDER BY c1 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t1 WHERE c1 <> '838:59:59' ORDER BY c1; SELECT * FROM t1 WHERE c1 <> '838:59:59' ORDER BY c1 LIMIT 2; SELECT * FROM t1 WHERE c1 > '838:59:59' ORDER BY c1; SELECT * FROM t1 WHERE c1 > '838:59:59' ORDER BY c1 LIMIT 2; SELECT * FROM t1 WHERE c1 >= '838:59:59' ORDER BY c1; SELECT * FROM t1 WHERE c1 >= '838:59:59' ORDER BY c1 LIMIT 2; SELECT * FROM t1 WHERE c1 < '838:59:59' ORDER BY c1; SELECT * FROM t1 WHERE c1 < '838:59:59' ORDER BY c1 LIMIT 2; SELECT * FROM t1 WHERE c1 <= '838:59:59' ORDER BY c1; SELECT * FROM t1 WHERE c1 <= '838:59:59' ORDER BY c1 LIMIT 2; SELECT * FROM t1 WHERE c1 <=> '838:59:59' ORDER BY c1; SELECT * FROM t1 WHERE c1 <=> '838:59:59' ORDER BY c1 LIMIT 2; SELECT * FROM t1 WHERE c1 BETWEEN '00:00:00' AND '838:59:59' ORDER BY c1; SELECT * FROM t1 WHERE c1 BETWEEN '00:00:00' AND '838:59:59' ORDER BY c1 LIMIT 2; SELECT * FROM t1 WHERE c1 IN ('00:00:00','838:59:59') ORDER BY c1; SELECT * FROM t1 WHERE c1 IN ('00:00:00','838:59:59') ORDER BY c1 LIMIT 2; SELECT * FROM t1 WHERE c1 >= '00:00:00' AND c1 < '838:59:59' AND c2 = '11:11:12' ORDER BY c1; SELECT * FROM t1 WHERE c1 >= '00:00:00' AND c1 < '838:59:59' AND c2 = '11:11:12' ORDER BY c1 LIMIT 2; SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1; SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1 LIMIT 2; SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1; SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t1 WHERE c1 <> '838:59:59' ORDER BY c1 DESC; SELECT * FROM t1 WHERE c1 <> '838:59:59' ORDER BY c1 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 > '838:59:59' ORDER BY c1 DESC; SELECT * FROM t1 WHERE c1 > '838:59:59' ORDER BY c1 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 >= '838:59:59' ORDER BY c1 DESC; SELECT * FROM t1 WHERE c1 >= '838:59:59' ORDER BY c1 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 < '838:59:59' ORDER BY c1 DESC; SELECT * FROM t1 WHERE c1 < '838:59:59' ORDER BY c1 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 <= '838:59:59' ORDER BY c1 DESC; SELECT * FROM t1 WHERE c1 <= '838:59:59' ORDER BY c1 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 <=> '838:59:59' ORDER BY c1 DESC; SELECT * FROM t1 WHERE c1 <=> '838:59:59' ORDER BY c1 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 BETWEEN '00:00:00' AND '838:59:59' ORDER BY c1 DESC; SELECT * FROM t1 WHERE c1 BETWEEN '00:00:00' AND '838:59:59' ORDER BY c1 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 IN ('00:00:00','838:59:59') ORDER BY c1 DESC; SELECT * FROM t1 WHERE c1 IN ('00:00:00','838:59:59') ORDER BY c1 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 >= '00:00:00' AND c1 < '838:59:59' AND c2 = '11:11:12' ORDER BY c1 DESC; SELECT * FROM t1 WHERE c1 >= '00:00:00' AND c1 < '838:59:59' AND c2 = '11:11:12' ORDER BY c1 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1 DESC; SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1 DESC; SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t1; --sorted_result SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t1; --sorted_result SELECT * FROM t1 WHERE c3 = '2009-01-16'; ## Forward index scan, covering ## --sorted_result SELECT c1 FROM t1; ## Backward index scan, covering ## SELECT c1 FROM t1 ORDER BY c1 DESC; ## Forward index scan, non-covering ## SELECT * FROM t1 ORDER BY c1 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t1 ORDER BY c1 DESC LIMIT 2; ## ref type access SELECT * FROM t1 WHERE c1 = '00:00:00' ORDER BY c1; SELECT * FROM t1 WHERE c1 = '00:00:00' ORDER BY c1 LIMIT 2; SELECT * FROM t1 WHERE c1 = '00:00:00' ORDER BY c1 DESC; SELECT * FROM t1 WHERE c1 = '00:00:00' ORDER BY c1 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t1 WHERE c1 <> '00:00:00' ORDER BY c1; SELECT * FROM t1 WHERE c1 <> '00:00:00' ORDER BY c1 LIMIT 2; SELECT * FROM t1 WHERE c1 > '00:00:00' ORDER BY c1; SELECT * FROM t1 WHERE c1 > '00:00:00' ORDER BY c1 LIMIT 2; SELECT * FROM t1 WHERE c1 >= '00:00:00' ORDER BY c1; SELECT * FROM t1 WHERE c1 >= '00:00:00' ORDER BY c1 LIMIT 2; SELECT * FROM t1 WHERE c1 < '00:00:00' ORDER BY c1; SELECT * FROM t1 WHERE c1 < '00:00:00' ORDER BY c1 LIMIT 2; SELECT * FROM t1 WHERE c1 <= '00:00:00' ORDER BY c1; SELECT * FROM t1 WHERE c1 <= '00:00:00' ORDER BY c1 LIMIT 2; SELECT * FROM t1 WHERE c1 <=> '00:00:00' ORDER BY c1; SELECT * FROM t1 WHERE c1 <=> '00:00:00' ORDER BY c1 LIMIT 2; SELECT * FROM t1 WHERE c1 BETWEEN '00:00:00' AND '09:36:00' ORDER BY c1; SELECT * FROM t1 WHERE c1 BETWEEN '00:00:00' AND '09:36:00' ORDER BY c1 LIMIT 2; SELECT * FROM t1 WHERE c1 IN ('00:00:00','09:36:00') ORDER BY c1; SELECT * FROM t1 WHERE c1 IN ('00:00:00','09:36:00') ORDER BY c1 LIMIT 2; SELECT * FROM t1 WHERE c1 >= '00:00:00' AND c1 < '09:36:00' AND c2 = '01:23:00' ORDER BY c1; SELECT * FROM t1 WHERE c1 >= '00:00:00' AND c1 < '09:36:00' AND c2 = '01:23:00' ORDER BY c1 LIMIT 2; SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1; SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1 LIMIT 2; SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1; SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t1 WHERE c1 <> '00:00:00' ORDER BY c1 DESC; SELECT * FROM t1 WHERE c1 <> '00:00:00' ORDER BY c1 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 > '00:00:00' ORDER BY c1 DESC; SELECT * FROM t1 WHERE c1 > '00:00:00' ORDER BY c1 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 >= '00:00:00' ORDER BY c1 DESC; SELECT * FROM t1 WHERE c1 >= '00:00:00' ORDER BY c1 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 < '00:00:00' ORDER BY c1 DESC; SELECT * FROM t1 WHERE c1 < '00:00:00' ORDER BY c1 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 <= '00:00:00' ORDER BY c1 DESC; SELECT * FROM t1 WHERE c1 <= '00:00:00' ORDER BY c1 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 <=> '00:00:00' ORDER BY c1 DESC; SELECT * FROM t1 WHERE c1 <=> '00:00:00' ORDER BY c1 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 BETWEEN '00:00:00' AND '09:36:00' ORDER BY c1 DESC; SELECT * FROM t1 WHERE c1 BETWEEN '00:00:00' AND '09:36:00' ORDER BY c1 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 IN ('00:00:00','09:36:00') ORDER BY c1 DESC; SELECT * FROM t1 WHERE c1 IN ('00:00:00','09:36:00') ORDER BY c1 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 >= '00:00:00' AND c1 < '09:36:00' AND c2 = '01:23:00' ORDER BY c1 DESC; SELECT * FROM t1 WHERE c1 >= '00:00:00' AND c1 < '09:36:00' AND c2 = '01:23:00' ORDER BY c1 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1 DESC; SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1 DESC; SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t1; --sorted_result SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t1; --sorted_result SELECT * FROM t1 WHERE c3 = '2009-01-15'; ## Forward index scan, covering ## --sorted_result SELECT c2 FROM t1; ## Backward index scan, covering ## SELECT c2 FROM t1 ORDER BY c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t1 ORDER BY c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t1 ORDER BY c2 DESC LIMIT 2; ## ref type access SELECT * FROM t1 WHERE c2 = '-838:59:59' ORDER BY c2; SELECT * FROM t1 WHERE c2 = '-838:59:59' ORDER BY c2 LIMIT 2; SELECT * FROM t1 WHERE c2 = '-838:59:59' ORDER BY c2 DESC; SELECT * FROM t1 WHERE c2 = '-838:59:59' ORDER BY c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t1 WHERE c2 <> '-838:59:59' ORDER BY c2; SELECT * FROM t1 WHERE c2 <> '-838:59:59' ORDER BY c2 LIMIT 2; SELECT * FROM t1 WHERE c2 > '-838:59:59' ORDER BY c2; SELECT * FROM t1 WHERE c2 > '-838:59:59' ORDER BY c2 LIMIT 2; SELECT * FROM t1 WHERE c2 >= '-838:59:59' ORDER BY c2; SELECT * FROM t1 WHERE c2 >= '-838:59:59' ORDER BY c2 LIMIT 2; SELECT * FROM t1 WHERE c2 < '-838:59:59' ORDER BY c2; SELECT * FROM t1 WHERE c2 < '-838:59:59' ORDER BY c2 LIMIT 2; SELECT * FROM t1 WHERE c2 <= '-838:59:59' ORDER BY c2; SELECT * FROM t1 WHERE c2 <= '-838:59:59' ORDER BY c2 LIMIT 2; SELECT * FROM t1 WHERE c2 <=> '-838:59:59' ORDER BY c2; SELECT * FROM t1 WHERE c2 <=> '-838:59:59' ORDER BY c2 LIMIT 2; SELECT * FROM t1 WHERE c2 BETWEEN '-838:59:59' AND '10:00:00' ORDER BY c2; SELECT * FROM t1 WHERE c2 BETWEEN '-838:59:59' AND '10:00:00' ORDER BY c2 LIMIT 2; SELECT * FROM t1 WHERE c2 IN ('-838:59:59','10:00:00') ORDER BY c2; SELECT * FROM t1 WHERE c2 IN ('-838:59:59','10:00:00') ORDER BY c2 LIMIT 2; SELECT * FROM t1 WHERE c2 >= '-838:59:59' AND c2 < '10:00:00' AND c1 = '00:11:12' ORDER BY c2; SELECT * FROM t1 WHERE c2 >= '-838:59:59' AND c2 < '10:00:00' AND c1 = '00:11:12' ORDER BY c2 LIMIT 2; SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2; SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2 LIMIT 2; SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2; SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t1 WHERE c2 <> '-838:59:59' ORDER BY c2 DESC; SELECT * FROM t1 WHERE c2 <> '-838:59:59' ORDER BY c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 > '-838:59:59' ORDER BY c2 DESC; SELECT * FROM t1 WHERE c2 > '-838:59:59' ORDER BY c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 >= '-838:59:59' ORDER BY c2 DESC; SELECT * FROM t1 WHERE c2 >= '-838:59:59' ORDER BY c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 < '-838:59:59' ORDER BY c2 DESC; SELECT * FROM t1 WHERE c2 < '-838:59:59' ORDER BY c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 <= '-838:59:59' ORDER BY c2 DESC; SELECT * FROM t1 WHERE c2 <= '-838:59:59' ORDER BY c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 <=> '-838:59:59' ORDER BY c2 DESC; SELECT * FROM t1 WHERE c2 <=> '-838:59:59' ORDER BY c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 BETWEEN '-838:59:59' AND '10:00:00' ORDER BY c2 DESC; SELECT * FROM t1 WHERE c2 BETWEEN '-838:59:59' AND '10:00:00' ORDER BY c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 IN ('-838:59:59','10:00:00') ORDER BY c2 DESC; SELECT * FROM t1 WHERE c2 IN ('-838:59:59','10:00:00') ORDER BY c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 >= '-838:59:59' AND c2 < '10:00:00' AND c1 = '00:11:12' ORDER BY c2 DESC; SELECT * FROM t1 WHERE c2 >= '-838:59:59' AND c2 < '10:00:00' AND c1 = '00:11:12' ORDER BY c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2 DESC; SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2 DESC; SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t1; --sorted_result SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t1; --sorted_result SELECT * FROM t1 WHERE c3 = '2009-01-11'; ## Forward index scan, covering ## --sorted_result SELECT c2 FROM t1; ## Backward index scan, covering ## SELECT c2 FROM t1 ORDER BY c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t1 ORDER BY c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t1 ORDER BY c2 DESC LIMIT 2; ## ref type access SELECT * FROM t1 WHERE c2 = NULL ORDER BY c2; SELECT * FROM t1 WHERE c2 = NULL ORDER BY c2 LIMIT 2; SELECT * FROM t1 WHERE c2 = NULL ORDER BY c2 DESC; SELECT * FROM t1 WHERE c2 = NULL ORDER BY c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t1 WHERE c2 <> NULL ORDER BY c2; SELECT * FROM t1 WHERE c2 <> NULL ORDER BY c2 LIMIT 2; SELECT * FROM t1 WHERE c2 > NULL ORDER BY c2; SELECT * FROM t1 WHERE c2 > NULL ORDER BY c2 LIMIT 2; SELECT * FROM t1 WHERE c2 >= NULL ORDER BY c2; SELECT * FROM t1 WHERE c2 >= NULL ORDER BY c2 LIMIT 2; SELECT * FROM t1 WHERE c2 < NULL ORDER BY c2; SELECT * FROM t1 WHERE c2 < NULL ORDER BY c2 LIMIT 2; SELECT * FROM t1 WHERE c2 <= NULL ORDER BY c2; SELECT * FROM t1 WHERE c2 <= NULL ORDER BY c2 LIMIT 2; SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c2; SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c2 LIMIT 2; SELECT * FROM t1 WHERE c2 BETWEEN NULL AND '10:22:33' ORDER BY c2; SELECT * FROM t1 WHERE c2 BETWEEN NULL AND '10:22:33' ORDER BY c2 LIMIT 2; SELECT * FROM t1 WHERE c2 IN (NULL,'10:22:33') ORDER BY c2; SELECT * FROM t1 WHERE c2 IN (NULL,'10:22:33') ORDER BY c2 LIMIT 2; SELECT * FROM t1 WHERE c2 >= NULL AND c2 < '10:22:33' AND c1 = '491:22:33' ORDER BY c2; SELECT * FROM t1 WHERE c2 >= NULL AND c2 < '10:22:33' AND c1 = '491:22:33' ORDER BY c2 LIMIT 2; SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2; SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2 LIMIT 2; SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2; SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t1 WHERE c2 <> NULL ORDER BY c2 DESC; SELECT * FROM t1 WHERE c2 <> NULL ORDER BY c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 > NULL ORDER BY c2 DESC; SELECT * FROM t1 WHERE c2 > NULL ORDER BY c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 >= NULL ORDER BY c2 DESC; SELECT * FROM t1 WHERE c2 >= NULL ORDER BY c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 < NULL ORDER BY c2 DESC; SELECT * FROM t1 WHERE c2 < NULL ORDER BY c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 <= NULL ORDER BY c2 DESC; SELECT * FROM t1 WHERE c2 <= NULL ORDER BY c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c2 DESC; SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 BETWEEN NULL AND '10:22:33' ORDER BY c2 DESC; SELECT * FROM t1 WHERE c2 BETWEEN NULL AND '10:22:33' ORDER BY c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 IN (NULL,'10:22:33') ORDER BY c2 DESC; SELECT * FROM t1 WHERE c2 IN (NULL,'10:22:33') ORDER BY c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 >= NULL AND c2 < '10:22:33' AND c1 = '491:22:33' ORDER BY c2 DESC; SELECT * FROM t1 WHERE c2 >= NULL AND c2 < '10:22:33' AND c1 = '491:22:33' ORDER BY c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2 DESC; SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2 DESC; SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c2 DESC LIMIT 2; # Index-merge access --sorted_result SELECT * FROM t1 WHERE c1='838:59:59' OR c2='-838:59:59'; ## 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 = '2009-01-17'; ## 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 = '838:59:59' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 = '838:59:59' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 = '838:59:59' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 = '838:59:59' ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t2 WHERE c1 <> '838:59:59' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 <> '838:59:59' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 > '838:59:59' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 > '838:59:59' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 >= '838:59:59' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 >= '838:59:59' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 < '838:59:59' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 < '838:59:59' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 <= '838:59:59' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 <= '838:59:59' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 <=> '838:59:59' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 <=> '838:59:59' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 BETWEEN '00:00:00' AND '838:59:59' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 BETWEEN '00:00:00' AND '838:59:59' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 IN ('00:00:00','838:59:59') ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 IN ('00:00:00','838:59:59') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 >= '00:00:00' AND c1 < '838:59:59' AND c2 = '11:11:12' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 >= '00:00:00' AND c1 < '838:59:59' AND c2 = '11:11:12' 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 <> '838:59:59' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 <> '838:59:59' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 > '838:59:59' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 > '838:59:59' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 >= '838:59:59' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 >= '838:59:59' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 < '838:59:59' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 < '838:59:59' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 <= '838:59:59' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 <= '838:59:59' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 <=> '838:59:59' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 <=> '838:59:59' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 BETWEEN '00:00:00' AND '838:59:59' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 BETWEEN '00:00:00' AND '838:59:59' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 IN ('00:00:00','838:59:59') ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 IN ('00:00:00','838:59:59') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 >= '00:00:00' AND c1 < '838:59:59' AND c2 = '11:11:12' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 >= '00:00:00' AND c1 < '838:59:59' AND c2 = '11:11:12' 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 = '2009-01-16'; ## 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 = '00:00:00' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 = '00:00:00' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 = '00:00:00' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 = '00:00:00' ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t2 WHERE c1 <> '00:00:00' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 <> '00:00:00' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 > '00:00:00' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 > '00:00:00' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 >= '00:00:00' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 >= '00:00:00' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 < '00:00:00' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 < '00:00:00' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 <= '00:00:00' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 <= '00:00:00' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 <=> '00:00:00' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 <=> '00:00:00' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 BETWEEN '00:00:00' AND '09:36:00' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 BETWEEN '00:00:00' AND '09:36:00' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 IN ('00:00:00','09:36:00') ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 IN ('00:00:00','09:36:00') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 >= '00:00:00' AND c1 < '09:36:00' AND c2 = '01:23:00' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 >= '00:00:00' AND c1 < '09:36:00' AND c2 = '01:23:00' 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 <> '00:00:00' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 <> '00:00:00' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 > '00:00:00' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 > '00:00:00' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 >= '00:00:00' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 >= '00:00:00' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 < '00:00:00' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 < '00:00:00' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 <= '00:00:00' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 <= '00:00:00' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 <=> '00:00:00' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 <=> '00:00:00' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 BETWEEN '00:00:00' AND '09:36:00' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 BETWEEN '00:00:00' AND '09:36:00' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 IN ('00:00:00','09:36:00') ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 IN ('00:00:00','09:36:00') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 >= '00:00:00' AND c1 < '09:36:00' AND c2 = '01:23:00' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 >= '00:00:00' AND c1 < '09:36:00' AND c2 = '01:23:00' 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 = '2009-01-15'; ## 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 = '-838:59:59' ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 = '-838:59:59' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 = '-838:59:59' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 = '-838:59:59' ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t2 WHERE c2 <> '-838:59:59' ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 <> '-838:59:59' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 > '-838:59:59' ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 > '-838:59:59' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 >= '-838:59:59' ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 >= '-838:59:59' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 < '-838:59:59' ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 < '-838:59:59' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 <= '-838:59:59' ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 <= '-838:59:59' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 <=> '-838:59:59' ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 <=> '-838:59:59' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 BETWEEN '-838:59:59' AND '10:00:00' ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 BETWEEN '-838:59:59' AND '10:00:00' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 IN ('-838:59:59','10:00:00') ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 IN ('-838:59:59','10:00:00') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 >= '-838:59:59' AND c2 < '10:00:00' AND c1 = '00:11:12' ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 >= '-838:59:59' AND c2 < '10:00:00' AND c1 = '00:11:12' 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 <> '-838:59:59' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 <> '-838:59:59' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 > '-838:59:59' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 > '-838:59:59' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 >= '-838:59:59' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 >= '-838:59:59' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 < '-838:59:59' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 < '-838:59:59' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 <= '-838:59:59' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 <= '-838:59:59' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 <=> '-838:59:59' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 <=> '-838:59:59' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 BETWEEN '-838:59:59' AND '10:00:00' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 BETWEEN '-838:59:59' AND '10:00:00' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 IN ('-838:59:59','10:00:00') ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 IN ('-838:59:59','10:00:00') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 >= '-838:59:59' AND c2 < '10:00:00' AND c1 = '00:11:12' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 >= '-838:59:59' AND c2 < '10:00:00' AND c1 = '00:11:12' 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 = '2009-01-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 '10:22:33' ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 BETWEEN NULL AND '10:22:33' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 IN (NULL,'10:22:33') ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 IN (NULL,'10:22:33') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 >= NULL AND c2 < '10:22:33' AND c1 = '491:22:33' ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 >= NULL AND c2 < '10:22:33' AND c1 = '491:22:33' 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 '10:22:33' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 BETWEEN NULL AND '10:22:33' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 IN (NULL,'10:22:33') ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 IN (NULL,'10:22:33') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 >= NULL AND c2 < '10:22:33' AND c1 = '491:22:33' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 >= NULL AND c2 < '10:22:33' AND c1 = '491:22:33' 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 = '2009-01-17'; ## 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 = '838:59:59' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 = '838:59:59' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 = '838:59:59' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 = '838:59:59' ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t3 WHERE c1 <> '838:59:59' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 <> '838:59:59' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 > '838:59:59' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 > '838:59:59' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 >= '838:59:59' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 >= '838:59:59' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 < '838:59:59' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 < '838:59:59' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 <= '838:59:59' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 <= '838:59:59' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 <=> '838:59:59' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 <=> '838:59:59' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 BETWEEN '00:00:00' AND '838:59:59' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 BETWEEN '00:00:00' AND '838:59:59' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 IN ('00:00:00','838:59:59') ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 IN ('00:00:00','838:59:59') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 >= '00:00:00' AND c1 < '838:59:59' AND c2 = '11:11:12' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 >= '00:00:00' AND c1 < '838:59:59' AND c2 = '11:11:12' 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 <> '838:59:59' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 <> '838:59:59' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 > '838:59:59' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 > '838:59:59' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 >= '838:59:59' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 >= '838:59:59' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 < '838:59:59' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 < '838:59:59' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 <= '838:59:59' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 <= '838:59:59' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 <=> '838:59:59' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 <=> '838:59:59' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 BETWEEN '00:00:00' AND '838:59:59' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 BETWEEN '00:00:00' AND '838:59:59' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 IN ('00:00:00','838:59:59') ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 IN ('00:00:00','838:59:59') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 >= '00:00:00' AND c1 < '838:59:59' AND c2 = '11:11:12' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 >= '00:00:00' AND c1 < '838:59:59' AND c2 = '11:11:12' 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 = '2009-01-16'; ## 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 = '00:00:00' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 = '00:00:00' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 = '00:00:00' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 = '00:00:00' ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t3 WHERE c1 <> '00:00:00' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 <> '00:00:00' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 > '00:00:00' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 > '00:00:00' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 >= '00:00:00' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 >= '00:00:00' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 < '00:00:00' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 < '00:00:00' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 <= '00:00:00' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 <= '00:00:00' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 <=> '00:00:00' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 <=> '00:00:00' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 BETWEEN '00:00:00' AND '09:36:00' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 BETWEEN '00:00:00' AND '09:36:00' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 IN ('00:00:00','09:36:00') ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 IN ('00:00:00','09:36:00') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 >= '00:00:00' AND c1 < '09:36:00' AND c2 = '01:23:00' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 >= '00:00:00' AND c1 < '09:36:00' AND c2 = '01:23:00' 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 <> '00:00:00' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 <> '00:00:00' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 > '00:00:00' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 > '00:00:00' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 >= '00:00:00' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 >= '00:00:00' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 < '00:00:00' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 < '00:00:00' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 <= '00:00:00' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 <= '00:00:00' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 <=> '00:00:00' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 <=> '00:00:00' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 BETWEEN '00:00:00' AND '09:36:00' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 BETWEEN '00:00:00' AND '09:36:00' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 IN ('00:00:00','09:36:00') ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 IN ('00:00:00','09:36:00') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 >= '00:00:00' AND c1 < '09:36:00' AND c2 = '01:23:00' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 >= '00:00:00' AND c1 < '09:36:00' AND c2 = '01:23:00' 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 = '2009-01-15'; ## 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 = '-838:59:59' ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 = '-838:59:59' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 = '-838:59:59' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 = '-838:59:59' ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t3 WHERE c2 <> '-838:59:59' ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 <> '-838:59:59' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 > '-838:59:59' ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 > '-838:59:59' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 >= '-838:59:59' ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 >= '-838:59:59' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 < '-838:59:59' ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 < '-838:59:59' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 <= '-838:59:59' ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 <= '-838:59:59' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 <=> '-838:59:59' ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 <=> '-838:59:59' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 BETWEEN '-838:59:59' AND '10:00:00' ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 BETWEEN '-838:59:59' AND '10:00:00' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 IN ('-838:59:59','10:00:00') ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 IN ('-838:59:59','10:00:00') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 >= '-838:59:59' AND c2 < '10:00:00' AND c1 = '00:11:12' ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 >= '-838:59:59' AND c2 < '10:00:00' AND c1 = '00:11:12' 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 <> '-838:59:59' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 <> '-838:59:59' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 > '-838:59:59' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 > '-838:59:59' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 >= '-838:59:59' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 >= '-838:59:59' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 < '-838:59:59' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 < '-838:59:59' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 <= '-838:59:59' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 <= '-838:59:59' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 <=> '-838:59:59' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 <=> '-838:59:59' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 BETWEEN '-838:59:59' AND '10:00:00' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 BETWEEN '-838:59:59' AND '10:00:00' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 IN ('-838:59:59','10:00:00') ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 IN ('-838:59:59','10:00:00') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 >= '-838:59:59' AND c2 < '10:00:00' AND c1 = '00:11:12' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 >= '-838:59:59' AND c2 < '10:00:00' AND c1 = '00:11:12' 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 = '2009-01-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 '10:22:33' ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 BETWEEN NULL AND '10:22:33' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 IN (NULL,'10:22:33') ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 IN (NULL,'10:22:33') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 >= NULL AND c2 < '10:22:33' AND c1 = '491:22:33' ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 >= NULL AND c2 < '10:22:33' AND c1 = '491:22:33' 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 '10:22:33' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 BETWEEN NULL AND '10:22:33' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 IN (NULL,'10:22:33') ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 IN (NULL,'10:22:33') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 >= NULL AND c2 < '10:22:33' AND c1 = '491:22:33' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 >= NULL AND c2 < '10:22:33' AND c1 = '491:22:33' 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; # Some more testing......... # These must return normal result SELECT CAST(235959.123456 AS TIME); SELECT CAST(0.235959123456e+6 AS TIME); SELECT CAST(235959123456e-6 AS TIME); # These must cut fraction part and produce warning SELECT CAST(235959.1234567 AS TIME); SELECT CAST(0.2359591234567e6 AS TIME); # This must return NULL and produce warning --replace_result 2.359591234567e+029 2.359591234567e+29 SELECT CAST(0.2359591234567e+30 AS TIME); # Compare time values as strings select cast('100:55:50' as time) < cast('24:00:00' as time); select cast('100:55:50' as time) < cast('024:00:00' as time); select cast('300:55:50' as time) < cast('240:00:00' as time); select cast('100:55:50' as time) > cast('24:00:00' as time); select cast('100:55:50' as time) > cast('024:00:00' as time); select cast('300:55:50' as time) > cast('240:00:00' as time); create table t5 (f1 time); insert into t5 values ('24:00:00'); select cast('24:00:00' as time) = (select f1 from t5); drop table t5; # 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('10:22:33','10:22:34') /* throws error as row exists with c1='10:22:33' */; # Insert duplicate value to clustered pk, throws error --error ER_DUP_ENTRY INSERT INTO t2(c1,c2) VALUES('12:34:56.78','12:34:56.78') /* throws error as row exists with c1='12:34:56.78',c2='12:34:56.78' */; # Insert duplicate value to unique column, throws error --error ER_DUP_ENTRY INSERT INTO t1(c1,c2) VALUES('10:22:34','34 9:23') /* throws error as row exists with c2='34 9:23' */; # Insert duplicate value to clustered unique column, throws error --error ER_DUP_ENTRY INSERT INTO t3(c1,c2) VALUES('34 9:23','34 9:23') /* throws error as row exists with c1='34 9:23',c2='34 9:23' */; # Test 'INSERT IGNORE' with the same rows that reported constraint violation above # Ignore pk constraint INSERT IGNORE INTO t1(c1,c2) VALUES('10:22:33','10:22:34') /* doesn't throw error */; INSERT IGNORE INTO t2(c1,c2) VALUES('12:34:56.78','12:34:56.78') /*doesn't throw error */; # Ignore unique constraint INSERT IGNORE INTO t1(c1,c2) VALUES('10:22:34','34 9:23') /*doesn't throw error */; INSERT IGNORE INTO t3(c1,c2) VALUES('34 9:23','34 9:23') /*doesn't throw error */; # Select # Test 'INSERT ON DUPLICATE KEY UPDATE' with PK one column/multi-column SELECT * FROM t1 WHERE c1='10:23:33' /* no rows */; INSERT INTO t1(c1) VALUES('10:22:33') ON DUPLICATE KEY UPDATE c1='10:23:33'; SELECT * FROM t1 WHERE c1='10:23:33' /* 1 row */; SELECT * FROM t2 WHERE c1='12:34:56.79' AND c2='12:34:57.78' /* no rows */; INSERT INTO t2(c1,c2) VALUES('12:34:56.78','12:34:56.78') ON DUPLICATE KEY UPDATE c1='12:34:56.79',c2='12:34:57.78'; SELECT * FROM t2 WHERE c1='12:34:56.79' AND c2='12:34:57.78' /* 1 row */; # Test 'INSERT ON DUPLICATE KEY UPDATE' with unique one column/multi-column SELECT * FROM t1 WHERE c1='10:22:35' AND c2='33 9:23' /* no rows */; INSERT INTO t1(c1,c2) VALUES('10:22:34','34 9:23') ON DUPLICATE KEY UPDATE c1='10:22:35',c2='33 9:23'; SELECT * FROM t1 WHERE c1='10:22:35' AND c2='33 9:23' /* 1 row */; SELECT * FROM t3 WHERE c1='32 9:23' AND c2='33 9:23' /* no rows */; INSERT INTO t3(c1,c2) VALUES('34 9:23','34 9:23') ON DUPLICATE KEY UPDATE c1='32 9:23',c2='33 9:23'; SELECT * FROM t3 WHERE c1='32 9:23' AND c2='33 9:23' /* 1 row */; # Test 'INSERT SET' INSERT INTO t1 SET c1='07:23:55',c2='13 06:23:55'; INSERT INTO t2 SET c1='07:23:55',c2='13 06:23:55'; INSERT INTO t3 SET c1='07:23:55'; INSERT INTO t4 SET c2='07:23:55'; SELECT * FROM t1 WHERE c1='07:23:55' AND c2='13 06:23:55' /* Returns 1 row with values for other column as NULL */; SELECT * FROM t2 WHERE c1='07:23:55' AND c2='13 06:23:55' /* Returns 1 row with values for other column as NULL */; SELECT * FROM t3 WHERE c1='07:23:55' /* Returns 1 row with values for other column as NULL */; SELECT * FROM t4 WHERE c2='07:23:55' /* Returns 1 row with values for other column as NULL */; # Test 'INSERT INTO SELECT FROM' CREATE TABLE t5(c1 TIME NOT NULL, c2 TIME NULL, c3 DATE, INDEX idx(c1,c2)); INSERT INTO t5 SELECT * FROM t4; SELECT * FROM t5; TRUNCATE TABLE t5; INSERT INTO t5 SELECT * FROM t4 WHERE c1 >='12colon34colon56'; SELECT * FROM t5; TRUNCATE TABLE t5; DROP TABLE t5; DROP TABLE t1,t2,t3,t4;
Back to File Manager