mirror of
https://github.com/MariaDB/server.git
synced 2025-10-30 18:36:12 +01:00
0a5e4a0191
Updated tests: cases with bugs or which cannot be run with the cursor-protocol were excluded with "--disable_cursor_protocol"/"--enable_cursor_protocol" Fix for v.10.5
1068 lines
54 KiB
Text
1068 lines
54 KiB
Text
--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
|
|
SET STATEMENT sql_mode = '' FOR
|
|
INSERT INTO t1 VALUES('-850:00:00','-850:00:00','2009-01-21');
|
|
--error ER_DUP_ENTRY
|
|
SET STATEMENT sql_mode = '' FOR
|
|
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 IGNORE 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 IGNORE 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;
|
|
#Check after fix MDEV-31730
|
|
--disable_cursor_protocol
|
|
--sorted_result
|
|
SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t1;
|
|
--enable_cursor_protocol
|
|
--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 IGNORE 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 IGNORE INTO t5 SELECT * FROM t4 WHERE c1 >='12colon34colon56';
|
|
SELECT * FROM t5;
|
|
TRUNCATE TABLE t5;
|
|
DROP TABLE t5;
|
|
DROP TABLE t1,t2,t3,t4;
|
|
|