summaryrefslogtreecommitdiffstats
path: root/mysql-test/suite/engines/iuds/t/insert_time.test
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
commit3f619478f796eddbba6e39502fe941b285dd97b1 (patch)
treee2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/suite/engines/iuds/t/insert_time.test
parentInitial commit. (diff)
downloadmariadb-upstream.tar.xz
mariadb-upstream.zip
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/suite/engines/iuds/t/insert_time.test')
-rw-r--r--mysql-test/suite/engines/iuds/t/insert_time.test1065
1 files changed, 1065 insertions, 0 deletions
diff --git a/mysql-test/suite/engines/iuds/t/insert_time.test b/mysql-test/suite/engines/iuds/t/insert_time.test
new file mode 100644
index 00000000..0e5e2150
--- /dev/null
+++ b/mysql-test/suite/engines/iuds/t/insert_time.test
@@ -0,0 +1,1065 @@
+--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;
+--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 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;
+