Adding upstream version 1:10.11.6.upstream/1%10.11.6

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 1506 insertions, 0 deletions

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