Linux ip-172-26-2-223 5.4.0-1018-aws #18-Ubuntu SMP Wed Jun 24 01:15:00 UTC 2020 x86_64
Apache
: 172.26.2.223 | : 18.116.67.226
Cant Read [ /etc/named.conf ]
8.1.13
www
www.github.com/MadExploits
Terminal
AUTO ROOT
Adminer
Backdoor Destroyer
Linux Exploit
Lock Shell
Lock File
Create User
CREATE RDP
PHP Mailer
BACKCONNECT
UNLOCK SHELL
HASH IDENTIFIER
CPANEL RESET
CREATE WP USER
BLACK DEFEND!
README
+ Create Folder
+ Create File
/
www /
server /
mysql /
mysql-test /
suite /
engines /
iuds /
t /
[ HOME SHELL ]
Name
Size
Permission
Action
delete_decimal.test
14.4
KB
-rw-r--r--
delete_time.test
6.46
KB
-rw-r--r--
delete_year.test
3.79
KB
-rw-r--r--
disabled.def
376
B
-rw-r--r--
hindi.txt
3.27
KB
-rw-r--r--
insert_calendar.test
334.57
KB
-rw-r--r--
insert_decimal.test
49.46
KB
-rw-r--r--
insert_number.test
384.36
KB
-rw-r--r--
insert_time.test
54.11
KB
-rw-r--r--
insert_year.test
73.64
KB
-rw-r--r--
sample.txt
11.23
KB
-rw-r--r--
strings_charsets_update_delete...
10.41
KB
-rw-r--r--
strings_update_delete.test
6.02
KB
-rw-r--r--
type_bit_iuds.test
759.19
KB
-rw-r--r--
update_decimal.test
19.15
KB
-rw-r--r--
update_delete_calendar.test
42.7
KB
-rw-r--r--
update_delete_number.test
73.53
KB
-rw-r--r--
update_time.test
8.61
KB
-rw-r--r--
update_year.test
6.49
KB
-rw-r--r--
Delete
Unzip
Zip
${this.title}
Close
Code Editor : insert_year.test
--disable_warnings DROP TABLE IF EXISTS t1,t2,t3,t4; --enable_warnings SET sql_mode='NO_ENGINE_SUBSTITUTION'; ######## 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) NOT NULL, c3 DATE, c4 DATETIME, PRIMARY KEY(c1,c2)); CREATE TABLE t3(c1 YEAR(4) NOT NULL, c2 YEAR(4) NULL, c3 DATE, c4 DATETIME, UNIQUE INDEX idx(c1,c2)); CREATE TABLE t4(c1 YEAR(4) NOT NULL, c2 YEAR(4) NULL, c3 DATE, c4 DATETIME); # Insert some rows with targeted values # As a four-digit string in the range '1901' to '2155' INSERT INTO t1 VALUES('1901','1901','98-12-31','98.12.31 11:30:45'),('1999','1999','98-12-30','98.12.30 11:30:45'),('2000','2000','98-12-29','98.12.29 11:30:45'),('2001','2001','98-12-28','98.12.28 11:30:45'),('2099','2099','98-12-27','98.12.27 11:30:45'),('2100','2100','98-12-26','98.12.26 11:30:45'),('2155','2155','98-12-26','98.12.26 11:30:45'); INSERT INTO t2 VALUES('1901','1901','98-12-31','98.12.31 11:30:45'),('1999','1999','98-12-30','98.12.30 11:30:45'),('2000','2000','98-12-29','98.12.29 11:30:45'),('2001','2001','98-12-28','98.12.28 11:30:45'),('2099','2099','98-12-27','98.12.27 11:30:45'),('2100','2100','98-12-26','98.12.26 11:30:45'),('2155','2155','98-12-26','98.12.26 11:30:45'); INSERT INTO t3 VALUES('1901','1901','98-12-31','98.12.31 11:30:45'),('1999','1999','98-12-30','98.12.30 11:30:45'),('2000','2000','98-12-29','98.12.29 11:30:45'),('2001','2001','98-12-28','98.12.28 11:30:45'),('2099','2099','98-12-27','98.12.27 11:30:45'),('2100','2100','98-12-26','98.12.26 11:30:45'),('2155','2155','98-12-26','98.12.26 11:30:45'); INSERT INTO t4 VALUES('1901','1901','98-12-31','98.12.31 11:30:45'),('1999','1999','98-12-30','98.12.30 11:30:45'),('2000','2000','98-12-29','98.12.29 11:30:45'),('2001','2001','98-12-28','98.12.28 11:30:45'),('2099','2099','98-12-27','98.12.27 11:30:45'),('2100','2100','98-12-26','98.12.26 11:30:45'),('2155','2155','98-12-25','98.12.25 11:30:45'); # As a four-digit number in the range 1901 to 2155 INSERT INTO t4 VALUES(1901,1901,'98-12-24','98.12.24 11:30:45'),(1999,1999,'98-12-23','98.12.23 11:30:45'),(2000,2000,'98-12-22','98.12.22 11:30:45'),(2001,2001,'98-12-21','98.12.21 11:30:45'),(2099,2099,'98-12-20','98.12.20 11:30:45'),(2100,2100,'98-12-19','98.12.19 11:30:45'),(2155,2155,'98-12-18','98.12.18 11:30:45'); # As a two-digit string in the range '00' to '99' INSERT INTO t1 VALUES('20','20','98-12-16','98.12.16 11:30:45'),('40','40','98-12-15','98.12.15 11:30:45'),('60','60','98-12-14','98.12.14 11:30:45'),('69','69','98-12-13','98.12.13 11:30:45'),('70','70','98-12-12','98.12.12 11:30:45'),('90','90','98-12-11','98.12.11 11:30:45'); INSERT INTO t2 VALUES('00','10','98-12-17','98.12.17 11:30:45'),('20','20','98-12-16','98.12.16 11:30:45'),('40','40','98-12-15','98.12.15 11:30:45'),('60','60','98-12-14','98.12.14 11:30:45'),('69','69','98-12-13','98.12.13 11:30:45'),('70','70','98-12-12','98.12.12 11:30:45'),('90','90','98-12-11','98.12.11 11:30:45'),('99','98','98-12-10','98.12.10 11:30:45'); INSERT INTO t3 VALUES('00','10','98-12-17','98.12.17 11:30:45'),('20','20','98-12-16','98.12.16 11:30:45'),('40','40','98-12-15','98.12.15 11:30:45'),('60','60','98-12-14','98.12.14 11:30:45'),('69','69','98-12-13','98.12.13 11:30:45'),('70','70','98-12-12','98.12.12 11:30:45'),('90','90','98-12-11','98.12.11 11:30:45'),('99','98','98-12-10','98.12.10 11:30:45'); INSERT INTO t4 VALUES('00','00','98-12-17','98.12.17 11:30:45'),('20','20','98-12-16','98.12.16 11:30:45'),('40','40','98-12-15','98.12.15 11:30:45'),('60','60','98-12-14','98.12.14 11:30:45'),('69','69','98-12-13','98.12.13 11:30:45'),('70','70','98-12-12','98.12.12 11:30:45'),('90','90','98-12-11','98.12.11 11:30:45'),('99','99','98-12-10','98.12.10 11:30:45'); # As a two-digit number in the range 1 to 99 INSERT INTO t4 VALUES(1,1,'98-12-9','98.12.9 11:30:45'),(20,20,'98-12-8','98.12.8 11:30:45'),(40,40,'98-12-7','98.12.7 11:30:45'),(60,60,'98-12-6','98.12.6 11:30:45'),(69,69,'98-12-5','98.12.5 11:30:45'),(70,70,'98-12-4','98.12.4 11:30:45'),(90,90,'98-12-3','98.12.3 11:30:45'),(99,99,'98-12-2','98.12.2 11:30:45'); # As the result of a function SET TIMESTAMP=1233216687; # 2009-01-29 13:41:27 INSERT INTO t1 VALUES(NOW(),CURRENT_DATE,NOW(),CURRENT_DATE); INSERT INTO t2 VALUES(NOW(),CURRENT_DATE,NOW(),CURRENT_DATE); INSERT INTO t3 VALUES(NOW(),CURRENT_DATE,NOW(),CURRENT_DATE); INSERT INTO t4 VALUES(NOW(),CURRENT_DATE,NOW(),CURRENT_DATE); # Insert other valid year values one digit strings, '0', '000' INSERT INTO t1(c1) VALUES('5'); INSERT INTO t2(c1) VALUES('5'); INSERT INTO t3(c1) VALUES('5'); INSERT INTO t4(c1,c2) VALUES('0','000'); # Insert invalid year values, outside range, would be converted to zero value INSERT INTO t2 VALUES(1900,2156,'08-01-06','08/01/07'); INSERT INTO t3 VALUES('1900','2156','08-01-08','08/1/9'); INSERT INTO t4 VALUES('1900','2156','08-01-08','08/1/9'),(1900,2156,'08-01-08','08/1/9'),(0,00,'08-01-08','08/1/9'),(000,000,'08-01-08','08/1/9'),('-20','100','08-01-08','08/1/9'),(-20,100,'08-01-08','08/1/9'); # Insert empty string '', would be converted to zero INSERT INTO t1 VALUES('','','08-01-04','08/01/05') /* Inserts zero dates for '' strings */; # Insert non-date value(absurd values), would be converted to zero INSERT INTO t4 VALUES('abcd','abcd','08-01-10','08/01/11'),(1234,1234,'08-01-12','08/01/13') /* Inserts zero dates for absurd dates */; # Insert duplicates for parts of the clustered key/unique index INSERT INTO t2 VALUES('20','30','98-12-16','98.12.16 11:30:45'),('40','20','98-12-15','98.12.15 11:30:45'); INSERT INTO t3 VALUES('00','20','98-12-17','98.12.17 11:30:45'),('20','40','98-12-10','98.12.10 11:30:45'); # Insert permissible NULLs INSERT INTO t1 VALUES(80,NULL,'08-01-02','08/01/03'); INSERT INTO t3 VALUES(80,NULL,'08-01-02','08/01/03'); INSERT INTO t4 VALUES(80,NULL,'08-01-02','08/01/03'); # Insert duplicate NULLs to unique column INSERT INTO t1(c1,c2) VALUES(1992,NULL); --sorted_result SELECT * FROM t1; --sorted_result SELECT * FROM t2; --sorted_result SELECT * FROM t3; --sorted_result SELECT * FROM t4; # Now select using various table access methods (full table scan, range scan, index scan etc.) ## Full table scan ## --sorted_result SELECT * FROM t2; --sorted_result SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t2; --sorted_result SELECT * FROM t2 WHERE c3 = '1998-12-11'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t2; ## Backward index scan, covering ## SELECT c1,c2 FROM t2 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t2 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t2 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t2 WHERE c1 = '1901' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 = '1901' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 = '1901' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 = '1901' ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t2 WHERE c1 <> '1901' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 <> '1901' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 > '1901' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 > '1901' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 >= '1901' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 >= '1901' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 < '1901' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 < '1901' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 <= '1901' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 <= '1901' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 <=> '1901' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 <=> '1901' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 BETWEEN '1901' AND '2020' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 BETWEEN '1901' AND '2020' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 IN ('1901','2020') ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 IN ('1901','2020') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 >= '1901' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 >= '1901' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t2 WHERE c1 <> '1901' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 <> '1901' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 > '1901' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 > '1901' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 >= '1901' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 >= '1901' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 < '1901' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 < '1901' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 <= '1901' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 <= '1901' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 <=> '1901' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 <=> '1901' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 BETWEEN '1901' AND '2020' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 BETWEEN '1901' AND '2020' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 IN ('1901','2020') ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 IN ('1901','2020') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 >= '1901' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 >= '1901' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t2; --sorted_result SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t2; --sorted_result SELECT * FROM t2 WHERE c3 = '1998-12-11'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t2; ## Backward index scan, covering ## SELECT c1,c2 FROM t2 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t2 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t2 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t2 WHERE c1 = '10' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 = '10' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 = '10' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 = '10' ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t2 WHERE c1 <> '10' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 <> '10' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 > '10' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 > '10' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 >= '10' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 >= '10' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 < '10' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 < '10' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 <= '10' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 <= '10' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 <=> '10' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 <=> '10' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 BETWEEN '10' AND '2020' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 BETWEEN '10' AND '2020' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 IN ('10','2020') ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 IN ('10','2020') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 >= '10' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 >= '10' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t2 WHERE c1 <> '10' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 <> '10' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 > '10' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 > '10' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 >= '10' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 >= '10' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 < '10' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 < '10' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 <= '10' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 <= '10' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 <=> '10' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 <=> '10' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 BETWEEN '10' AND '2020' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 BETWEEN '10' AND '2020' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 IN ('10','2020') ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 IN ('10','2020') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 >= '10' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 >= '10' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t2; --sorted_result SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t2; --sorted_result SELECT * FROM t2 WHERE c3 = '1998-12-11'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t2; ## Backward index scan, covering ## SELECT c1,c2 FROM t2 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t2 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t2 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t2 WHERE c2 = 2155 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 = 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 = 2155 ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 = 2155 ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t2 WHERE c2 <> 2155 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 <> 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 > 2155 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 > 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 >= 2155 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 >= 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 < 2155 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 < 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 <= 2155 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 <= 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 <=> 2155 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 <=> 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 BETWEEN '1970' AND 2155 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 BETWEEN '1970' AND 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 IN ('1970',2155) ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 IN ('1970',2155) ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 >= '1970' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 >= '1970' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t2 WHERE c2 <> 2155 ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 <> 2155 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 > 2155 ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 > 2155 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 >= 2155 ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 >= 2155 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 < 2155 ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 < 2155 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 <= 2155 ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 <= 2155 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 <=> 2155 ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 <=> 2155 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 BETWEEN '1970' AND 2155 ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 BETWEEN '1970' AND 2155 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 IN ('1970',2155) ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 IN ('1970',2155) ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 >= '1970' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 >= '1970' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t2; --sorted_result SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t2; --sorted_result SELECT * FROM t2 WHERE c3 = '1998-12-11'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t2; ## Backward index scan, covering ## SELECT c1,c2 FROM t2 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t2 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t2 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t2 WHERE c2 = NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 = NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 = NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 = NULL ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t2 WHERE c2 <> NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 <> NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 > NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 > NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 >= NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 >= NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 < NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 < NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 <= NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 <= NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 <=> NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t2 WHERE c2 <> NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 <> NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 > NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 > NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 >= NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 >= NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 < NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 < NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 <= NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 <= NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 <=> NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 <=> NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t3; --sorted_result SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t3; --sorted_result SELECT * FROM t3 WHERE c3 = '1998-12-11'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t3; ## Backward index scan, covering ## SELECT c1,c2 FROM t3 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t3 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t3 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t3 WHERE c1 = '1970' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 = '1970' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 = '1970' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 = '1970' ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t3 WHERE c1 <> '1970' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 <> '1970' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 > '1970' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 > '1970' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 >= '1970' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 >= '1970' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 < '1970' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 < '1970' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 <= '1970' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 <= '1970' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 <=> '1970' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 <=> '1970' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 BETWEEN '1970' AND '2020' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 BETWEEN '1970' AND '2020' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 IN ('1970','2020') ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 IN ('1970','2020') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 >= '1970' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 >= '1970' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t3 WHERE c1 <> '1970' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 <> '1970' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 > '1970' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 > '1970' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 >= '1970' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 >= '1970' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 < '1970' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 < '1970' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 <= '1970' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 <= '1970' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 <=> '1970' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 <=> '1970' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 BETWEEN '1970' AND '2020' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 BETWEEN '1970' AND '2020' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 IN ('1970','2020') ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 IN ('1970','2020') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 >= '1970' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 >= '1970' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t3; --sorted_result SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t3; --sorted_result SELECT * FROM t3 WHERE c3 = '1998-12-11'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t3; ## Backward index scan, covering ## SELECT c1,c2 FROM t3 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t3 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t3 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t3 WHERE c1 = '69' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 = '69' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 = '69' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 = '69' ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t3 WHERE c1 <> '69' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 <> '69' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 > '69' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 > '69' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 >= '69' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 >= '69' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 < '69' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 < '69' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 <= '69' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 <= '69' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 <=> '69' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 <=> '69' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 BETWEEN 69 AND '2020' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 BETWEEN 69 AND '2020' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 IN (69,'2020') ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 IN (69,'2020') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 >= 69 AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 >= 69 AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t3 WHERE c1 <> '69' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 <> '69' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 > '69' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 > '69' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 >= '69' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 >= '69' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 < '69' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 < '69' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 <= '69' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 <= '69' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 <=> '69' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 <=> '69' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 BETWEEN 69 AND '2020' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 BETWEEN 69 AND '2020' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 IN (69,'2020') ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 IN (69,'2020') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 >= 69 AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 >= 69 AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t3; --sorted_result SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t3; --sorted_result SELECT * FROM t3 WHERE c3 = '1998-12-11'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t3; ## Backward index scan, covering ## SELECT c1,c2 FROM t3 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t3 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t3 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t3 WHERE c2 = 2000 ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 = 2000 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 = 2000 ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 = 2000 ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t3 WHERE c2 <> 2000 ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 <> 2000 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 > 2000 ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 > 2000 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 >= 2000 ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 >= 2000 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 < 2000 ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 < 2000 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 <= 2000 ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 <= 2000 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 <=> 2000 ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 <=> 2000 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 BETWEEN '2000' AND 2155 ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 BETWEEN '2000' AND 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 IN ('2000',2155) ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 IN ('2000',2155) ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 >= '2000' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 >= '2000' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t3 WHERE c2 <> 2000 ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 <> 2000 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 > 2000 ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 > 2000 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 >= 2000 ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 >= 2000 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 < 2000 ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 < 2000 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 <= 2000 ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 <= 2000 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 <=> 2000 ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 <=> 2000 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 BETWEEN '2000' AND 2155 ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 BETWEEN '2000' AND 2155 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 IN ('2000',2155) ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 IN ('2000',2155) ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 >= '2000' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 >= '2000' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t3; --sorted_result SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t3; --sorted_result SELECT * FROM t3 WHERE c3 = '1998-12-11'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t3; ## Backward index scan, covering ## SELECT c1,c2 FROM t3 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t3 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t3 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t3 WHERE c2 = NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 = NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 = NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 = NULL ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t3 WHERE c2 <> NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 <> NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 > NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 > NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 >= NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 >= NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 < NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 < NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 <= NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 <= NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 <=> NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t3 WHERE c2 <> NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 <> NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 > NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 > NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 >= NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 >= NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 < NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 < NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 <= NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 <= NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 <=> NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 <=> NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; # Using index for group-by --sorted_result SELECT c1 FROM t3 GROUP BY c1; --sorted_result SELECT DISTINCT c1 FROM t3; --sorted_result SELECT c1,MIN(c2) FROM t3 GROUP BY c1; # Using Index-merge --sorted_result SELECT * FROM t1 WHERE c1='1901' OR c2='2155'; # Test Error conditions- PK constraint violation, Unique constraint violation # Insert duplicate value to pk column --error ER_DUP_ENTRY INSERT INTO t1(c1,c2) VALUES(01,'99'); # Insert duplicate value to clustered pk, throws error --error ER_DUP_ENTRY INSERT INTO t2(c1,c2) VALUES(99,99); # Insert duplicate value to unique column, throws error --error ER_DUP_ENTRY INSERT INTO t1(c1,c2) VALUES('2098',69); # Insert duplicate value to clustered unique column, throws error --error ER_DUP_ENTRY INSERT INTO t3(c1,c2) VALUES(00,00); # Test 'INSERT IGNORE' # Ignore pk constraint INSERT IGNORE INTO t1(c1,c2) VALUES(01,'99'); INSERT IGNORE INTO t2(c1,c2) VALUES('1999','1999'); # Ignore unique constraint INSERT IGNORE INTO t1(c1,c2) VALUES('2098','69'); INSERT IGNORE INTO t3(c1,c2) VALUES(00,00); SELECT * FROM t1 WHERE c1='01' /* Returns 1 row */; SELECT * FROM t2 WHERE c1='1999' AND c2='1999' /* Returns 1 row */; SELECT * FROM t1 WHERE c2='69' /* Returns 1 row */; SELECT * FROM t3 WHERE c1=0 AND c2=0 /* Returns 1 row */; # Test 'INSERT ON DUPLICATE KEY UPDATE' with PK one column/multi-column SELECT * FROM t1 WHERE c1='02' /* Returns no rows */; INSERT INTO t1(c1) VALUES('00') ON DUPLICATE KEY UPDATE c1='02'; SELECT * FROM t1 WHERE c1='2002' /* Returns 1 row */; SELECT * FROM t2 WHERE c1=69 AND c2=70 /* Returns no rows */; INSERT INTO t2 VALUES(99,99,'98-12-20','98.12.20 11:30:45') ON DUPLICATE KEY UPDATE c1=69,c2=70; SELECT * FROM t2 WHERE c1=2069 AND c2=1970 /* Returns 1 row */; # Test 'INSERT ON DUPLICATE KEY UPDATE' with unique one column/multi-column SELECT * FROM t1 WHERE c1=54 AND c2=53 /* Returns no rows */; INSERT INTO t1 VALUES(69,69,'98-12-18','98.12.18 11:30:45') ON DUPLICATE KEY UPDATE c1=54,c2=53; SELECT * FROM t1 WHERE c1=54 AND c2=53 /* Returns 1 row */; SELECT * FROM t3 WHERE c1=6 AND c2=1970 /* Returns no rows */; INSERT INTO t3(c1,c2) VALUES('69','69') ON DUPLICATE KEY UPDATE c1=06,c2=70; SELECT * FROM t3 WHERE c1=2006 AND c2=1970 /* Returns 1 row */; # Test 'INSERT INTO SELECT FROM' CREATE TABLE t5(c1 YEAR NOT NULL, c2 YEAR NULL, c3 DATE, c4 DATETIME, INDEX idx(c1,c2)); INSERT INTO t5 SELECT * FROM t1; --sorted_result SELECT * FROM t5; TRUNCATE TABLE t5; INSERT INTO t5 SELECT * FROM t2 WHERE c1 >=1970 AND c1 < 1999 AND c2 <> '1990'; --sorted_result SELECT * FROM t5; TRUNCATE TABLE t5; INSERT INTO t5 SELECT * FROM t3 WHERE c2 BETWEEN '2000' AND '2155'; --sorted_result SELECT * FROM t5; TRUNCATE TABLE t5; DROP TABLE t5; # Test 'INSERT SET' INSERT INTO t1 SET c1='00'; INSERT INTO t2 SET c1=69; INSERT INTO t3 SET c1=70; INSERT INTO t4 SET c2=99; --sorted_result SELECT * FROM t1 WHERE c1=0; --sorted_result SELECT * FROM t2 WHERE c1=69; --sorted_result SELECT * FROM t3 WHERE c1=70; --sorted_result SELECT * FROM t4 WHERE c2=99; DROP TABLE t1,t2,t3,t4; ######## Running INSERT tests for YEAR(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) NOT NULL, c3 DATE, c4 DATETIME, PRIMARY KEY(c1,c2)); CREATE TABLE t3(c1 YEAR(4) NOT NULL, c2 YEAR(4) NULL, c3 DATE, c4 DATETIME, UNIQUE INDEX idx(c1,c2)); CREATE TABLE t4(c1 YEAR(4) NOT NULL, c2 YEAR(4) NULL, c3 DATE, c4 DATETIME); # Insert some rows with targeted values # As a four-digit string in the range '1901' to '2155' INSERT INTO t1 VALUES('1901','1901','98-12-31','98.12.31 11:30:45'),('1999','1999','98-12-30','98.12.30 11:30:45'),('2000','2000','98-12-29','98.12.29 11:30:45'),('2001','2001','98-12-28','98.12.28 11:30:45'),('2099','2099','98-12-27','98.12.27 11:30:45'),('2100','2100','98-12-26','98.12.26 11:30:45'),('2155','2155','98-12-26','98.12.26 11:30:45'); INSERT INTO t2 VALUES('1901','1901','98-12-31','98.12.31 11:30:45'),('1999','1999','98-12-30','98.12.30 11:30:45'),('2000','2000','98-12-29','98.12.29 11:30:45'),('2001','2001','98-12-28','98.12.28 11:30:45'),('2099','2099','98-12-27','98.12.27 11:30:45'),('2100','2100','98-12-26','98.12.26 11:30:45'),('2155','2155','98-12-26','98.12.26 11:30:45'); INSERT INTO t3 VALUES('1901','1901','98-12-31','98.12.31 11:30:45'),('1999','1999','98-12-30','98.12.30 11:30:45'),('2000','2000','98-12-29','98.12.29 11:30:45'),('2001','2001','98-12-28','98.12.28 11:30:45'),('2099','2099','98-12-27','98.12.27 11:30:45'),('2100','2100','98-12-26','98.12.26 11:30:45'),('2155','2155','98-12-26','98.12.26 11:30:45'); INSERT INTO t4 VALUES('1901','1901','98-12-31','98.12.31 11:30:45'),('1999','1999','98-12-30','98.12.30 11:30:45'),('2000','2000','98-12-29','98.12.29 11:30:45'),('2001','2001','98-12-28','98.12.28 11:30:45'),('2099','2099','98-12-27','98.12.27 11:30:45'),('2100','2100','98-12-26','98.12.26 11:30:45'),('2155','2155','98-12-25','98.12.25 11:30:45'); # As a four-digit number in the range 1901 to 2155 INSERT INTO t4 VALUES(1901,1901,'98-12-24','98.12.24 11:30:45'),(1999,1999,'98-12-23','98.12.23 11:30:45'),(2000,2000,'98-12-22','98.12.22 11:30:45'),(2001,2001,'98-12-21','98.12.21 11:30:45'),(2099,2099,'98-12-20','98.12.20 11:30:45'),(2100,2100,'98-12-19','98.12.19 11:30:45'),(2155,2155,'98-12-18','98.12.18 11:30:45'); # As a two-digit string in the range '00' to '99' INSERT INTO t1 VALUES('20','20','98-12-16','98.12.16 11:30:45'),('40','40','98-12-15','98.12.15 11:30:45'),('60','60','98-12-14','98.12.14 11:30:45'),('69','69','98-12-13','98.12.13 11:30:45'),('70','70','98-12-12','98.12.12 11:30:45'),('90','90','98-12-11','98.12.11 11:30:45'); INSERT INTO t2 VALUES('00','10','98-12-17','98.12.17 11:30:45'),('20','20','98-12-16','98.12.16 11:30:45'),('40','40','98-12-15','98.12.15 11:30:45'),('60','60','98-12-14','98.12.14 11:30:45'),('69','69','98-12-13','98.12.13 11:30:45'),('70','70','98-12-12','98.12.12 11:30:45'),('90','90','98-12-11','98.12.11 11:30:45'),('99','98','98-12-10','98.12.10 11:30:45'); INSERT INTO t3 VALUES('00','10','98-12-17','98.12.17 11:30:45'),('20','20','98-12-16','98.12.16 11:30:45'),('40','40','98-12-15','98.12.15 11:30:45'),('60','60','98-12-14','98.12.14 11:30:45'),('69','69','98-12-13','98.12.13 11:30:45'),('70','70','98-12-12','98.12.12 11:30:45'),('90','90','98-12-11','98.12.11 11:30:45'),('99','98','98-12-10','98.12.10 11:30:45'); INSERT INTO t4 VALUES('00','00','98-12-17','98.12.17 11:30:45'),('20','20','98-12-16','98.12.16 11:30:45'),('40','40','98-12-15','98.12.15 11:30:45'),('60','60','98-12-14','98.12.14 11:30:45'),('69','69','98-12-13','98.12.13 11:30:45'),('70','70','98-12-12','98.12.12 11:30:45'),('90','90','98-12-11','98.12.11 11:30:45'),('99','99','98-12-10','98.12.10 11:30:45'); # As a two-digit number in the range 1 to 99 INSERT INTO t4 VALUES(1,1,'98-12-9','98.12.9 11:30:45'),(20,20,'98-12-8','98.12.8 11:30:45'),(40,40,'98-12-7','98.12.7 11:30:45'),(60,60,'98-12-6','98.12.6 11:30:45'),(69,69,'98-12-5','98.12.5 11:30:45'),(70,70,'98-12-4','98.12.4 11:30:45'),(90,90,'98-12-3','98.12.3 11:30:45'),(99,99,'98-12-2','98.12.2 11:30:45'); # As the result of a function SET TIMESTAMP=1233216687; # 2009-01-29 13:41:27 INSERT INTO t1 VALUES(NOW(),CURRENT_DATE,NOW(),CURRENT_DATE); INSERT INTO t2 VALUES(NOW(),CURRENT_DATE,NOW(),CURRENT_DATE); INSERT INTO t3 VALUES(NOW(),CURRENT_DATE,NOW(),CURRENT_DATE); INSERT INTO t4 VALUES(NOW(),CURRENT_DATE,NOW(),CURRENT_DATE); # Insert other valid year values one digit strings, '0', '000' INSERT INTO t1(c1) VALUES('5'); INSERT INTO t2(c1) VALUES('5'); INSERT INTO t3(c1) VALUES('5'); INSERT INTO t4(c1,c2) VALUES('0','000'); # Insert invalid year values, outside range, would be converted to zero value INSERT INTO t2 VALUES(1900,2156,'08-01-06','08/01/07'); INSERT INTO t3 VALUES('1900','2156','08-01-08','08/1/9'); INSERT INTO t4 VALUES('1900','2156','08-01-08','08/1/9'),(1900,2156,'08-01-08','08/1/9'),(0,00,'08-01-08','08/1/9'),(000,000,'08-01-08','08/1/9'),('-20','100','08-01-08','08/1/9'),(-20,100,'08-01-08','08/1/9'); # Insert empty string '', would be converted to zero INSERT INTO t1 VALUES('','','08-01-04','08/01/05') /* Inserts zero dates for '' strings */; # Insert non-date value(absurd values), would be converted to zero INSERT INTO t4 VALUES('abcd','abcd','08-01-10','08/01/11'),(1234,1234,'08-01-12','08/01/13') /* Inserts zero dates for absurd dates */; # Insert duplicates for parts of the clustered key/unique index INSERT INTO t2 VALUES('20','30','98-12-16','98.12.16 11:30:45'),('40','20','98-12-15','98.12.15 11:30:45'); INSERT INTO t3 VALUES('00','20','98-12-17','98.12.17 11:30:45'),('20','40','98-12-10','98.12.10 11:30:45'); # Insert permissible NULLs INSERT INTO t1 VALUES(80,NULL,'08-01-02','08/01/03'); INSERT INTO t3 VALUES(80,NULL,'08-01-02','08/01/03'); INSERT INTO t4 VALUES(80,NULL,'08-01-02','08/01/03'); # Insert duplicate NULLs to unique column INSERT INTO t1(c1,c2) VALUES(1992,NULL); --sorted_result SELECT * FROM t1; --sorted_result SELECT * FROM t2; --sorted_result SELECT * FROM t3; --sorted_result SELECT * FROM t4; # Now select using various table access methods (full table scan, range scan, index scan etc.) ## Full table scan ## --sorted_result SELECT * FROM t2; --sorted_result SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t2; --sorted_result SELECT * FROM t2 WHERE c3 = '1998-12-11'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t2; ## Backward index scan, covering ## SELECT c1,c2 FROM t2 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t2 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t2 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t2 WHERE c1 = '1901' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 = '1901' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 = '1901' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 = '1901' ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t2 WHERE c1 <> '1901' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 <> '1901' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 > '1901' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 > '1901' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 >= '1901' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 >= '1901' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 < '1901' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 < '1901' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 <= '1901' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 <= '1901' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 <=> '1901' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 <=> '1901' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 BETWEEN '1901' AND '2020' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 BETWEEN '1901' AND '2020' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 IN ('1901','2020') ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 IN ('1901','2020') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 >= '1901' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 >= '1901' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t2 WHERE c1 <> '1901' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 <> '1901' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 > '1901' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 > '1901' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 >= '1901' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 >= '1901' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 < '1901' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 < '1901' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 <= '1901' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 <= '1901' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 <=> '1901' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 <=> '1901' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 BETWEEN '1901' AND '2020' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 BETWEEN '1901' AND '2020' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 IN ('1901','2020') ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 IN ('1901','2020') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 >= '1901' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 >= '1901' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t2; --sorted_result SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t2; --sorted_result SELECT * FROM t2 WHERE c3 = '1998-12-11'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t2; ## Backward index scan, covering ## SELECT c1,c2 FROM t2 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t2 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t2 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t2 WHERE c1 = '10' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 = '10' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 = '10' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 = '10' ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t2 WHERE c1 <> '10' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 <> '10' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 > '10' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 >= '10' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 < '10' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 <= '10' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 <=> '10' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 <=> '10' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 BETWEEN '10' AND '2020' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 BETWEEN '10' AND '2020' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 IN ('10','2020') ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 IN ('10','2020') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 >= '10' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 >= '10' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t2 WHERE c1 <> '10' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 <> '10' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 > '10' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 >= '10' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 < '10' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 <= '10' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 <=> '10' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 <=> '10' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 BETWEEN '10' AND '2020' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 BETWEEN '10' AND '2020' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 IN ('10','2020') ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 IN ('10','2020') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 >= '10' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 >= '10' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t2; --sorted_result SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t2; --sorted_result SELECT * FROM t2 WHERE c3 = '1998-12-11'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t2; ## Backward index scan, covering ## SELECT c1,c2 FROM t2 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t2 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t2 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t2 WHERE c2 = 2155 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 = 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 = 2155 ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 = 2155 ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t2 WHERE c2 <> 2155 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 <> 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 > 2155 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 > 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 >= 2155 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 >= 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 < 2155 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 < 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 <= 2155 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 <= 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 <=> 2155 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 <=> 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 BETWEEN '1970' AND 2155 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 BETWEEN '1970' AND 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 IN ('1970',2155) ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 IN ('1970',2155) ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 >= '1970' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 >= '1970' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t2 WHERE c2 <> 2155 ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 <> 2155 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 > 2155 ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 > 2155 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 >= 2155 ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 >= 2155 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 < 2155 ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 < 2155 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 <= 2155 ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 <= 2155 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 <=> 2155 ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 <=> 2155 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 BETWEEN '1970' AND 2155 ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 BETWEEN '1970' AND 2155 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 IN ('1970',2155) ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 IN ('1970',2155) ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 >= '1970' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 >= '1970' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t2; --sorted_result SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t2; --sorted_result SELECT * FROM t2 WHERE c3 = '1998-12-11'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t2; ## Backward index scan, covering ## SELECT c1,c2 FROM t2 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t2 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t2 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t2 WHERE c2 = NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 = NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 = NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 = NULL ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t2 WHERE c2 <> NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 <> NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 > NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 > NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 >= NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 >= NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 < NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 < NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 <= NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 <= NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 <=> NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t2 WHERE c2 <> NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 <> NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 > NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 > NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 >= NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 >= NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 < NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 < NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 <= NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 <= NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 <=> NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 <=> NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t2 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t3; --sorted_result SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t3; --sorted_result SELECT * FROM t3 WHERE c3 = '1998-12-11'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t3; ## Backward index scan, covering ## SELECT c1,c2 FROM t3 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t3 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t3 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t3 WHERE c1 = '1970' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 = '1970' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 = '1970' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 = '1970' ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t3 WHERE c1 <> '1970' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 <> '1970' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 > '1970' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 > '1970' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 >= '1970' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 >= '1970' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 < '1970' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 < '1970' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 <= '1970' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 <= '1970' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 <=> '1970' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 <=> '1970' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 BETWEEN '1970' AND '2020' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 BETWEEN '1970' AND '2020' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 IN ('1970','2020') ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 IN ('1970','2020') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 >= '1970' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 >= '1970' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t3 WHERE c1 <> '1970' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 <> '1970' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 > '1970' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 > '1970' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 >= '1970' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 >= '1970' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 < '1970' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 < '1970' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 <= '1970' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 <= '1970' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 <=> '1970' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 <=> '1970' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 BETWEEN '1970' AND '2020' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 BETWEEN '1970' AND '2020' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 IN ('1970','2020') ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 IN ('1970','2020') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 >= '1970' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 >= '1970' AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t3; --sorted_result SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t3; --sorted_result SELECT * FROM t3 WHERE c3 = '1998-12-11'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t3; ## Backward index scan, covering ## SELECT c1,c2 FROM t3 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t3 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t3 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t3 WHERE c1 = '69' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 = '69' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 = '69' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 = '69' ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t3 WHERE c1 <> '69' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 <> '69' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 > '69' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 > '69' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 >= '69' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 >= '69' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 < '69' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 < '69' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 <= '69' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 <= '69' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 <=> '69' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 <=> '69' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 BETWEEN 69 AND '2020' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 BETWEEN 69 AND '2020' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 IN (69,'2020') ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 IN (69,'2020') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 >= 69 AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 >= 69 AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t3 WHERE c1 <> '69' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 <> '69' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 > '69' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 > '69' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 >= '69' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 >= '69' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 < '69' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 < '69' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 <= '69' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 <= '69' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 <=> '69' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 <=> '69' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 BETWEEN 69 AND '2020' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 BETWEEN 69 AND '2020' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 IN (69,'2020') ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 IN (69,'2020') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 >= 69 AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 >= 69 AND c1 < '2020' AND c2 = '2009' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t3; --sorted_result SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t3; --sorted_result SELECT * FROM t3 WHERE c3 = '1998-12-11'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t3; ## Backward index scan, covering ## SELECT c1,c2 FROM t3 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t3 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t3 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t3 WHERE c2 = 2000 ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 = 2000 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 = 2000 ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 = 2000 ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t3 WHERE c2 <> 2000 ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 <> 2000 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 > 2000 ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 > 2000 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 >= 2000 ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 >= 2000 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 < 2000 ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 < 2000 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 <= 2000 ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 <= 2000 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 <=> 2000 ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 <=> 2000 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 BETWEEN '2000' AND 2155 ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 BETWEEN '2000' AND 2155 ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 IN ('2000',2155) ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 IN ('2000',2155) ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 >= '2000' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 >= '2000' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t3 WHERE c2 <> 2000 ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 <> 2000 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 > 2000 ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 > 2000 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 >= 2000 ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 >= 2000 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 < 2000 ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 < 2000 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 <= 2000 ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 <= 2000 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 <=> 2000 ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 <=> 2000 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 BETWEEN '2000' AND 2155 ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 BETWEEN '2000' AND 2155 ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 IN ('2000',2155) ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 IN ('2000',2155) ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 >= '2000' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 >= '2000' AND c2 < 2155 AND c1 = '1999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t3; --sorted_result SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t3; --sorted_result SELECT * FROM t3 WHERE c3 = '1998-12-11'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t3; ## Backward index scan, covering ## SELECT c1,c2 FROM t3 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t3 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t3 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t3 WHERE c2 = NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 = NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 = NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 = NULL ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t3 WHERE c2 <> NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 <> NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 > NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 > NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 >= NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 >= NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 < NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 < NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 <= NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 <= NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 <=> NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t3 WHERE c2 <> NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 <> NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 > NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 > NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 >= NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 >= NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 < NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 < NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 <= NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 <= NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 <=> NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 <=> NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 BETWEEN NULL AND '2069' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 IN (NULL,'2069') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 >= NULL AND c2 < '2069' AND c1 = '2000' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t3 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; # Using index for group-by --sorted_result SELECT c1 FROM t3 GROUP BY c1; --sorted_result SELECT DISTINCT c1 FROM t3; --sorted_result SELECT c1,MIN(c2) FROM t3 GROUP BY c1; # Using Index-merge --sorted_result SELECT * FROM t1 WHERE c1='1901' OR c2='2155'; # Test Error conditions- PK constraint violation, Unique constraint violation # Insert duplicate value to pk column --error ER_DUP_ENTRY INSERT INTO t1(c1,c2) VALUES(01,'99'); # Insert duplicate value to clustered pk, throws error --error ER_DUP_ENTRY INSERT INTO t2(c1,c2) VALUES(99,99); # Insert duplicate value to unique column, throws error --error ER_DUP_ENTRY INSERT INTO t1(c1,c2) VALUES('2098',69); # Insert duplicate value to clustered unique column, throws error --error ER_DUP_ENTRY INSERT INTO t3(c1,c2) VALUES(00,00); # Test 'INSERT IGNORE' # Ignore pk constraint INSERT IGNORE INTO t1(c1,c2) VALUES(01,'99'); INSERT IGNORE INTO t2(c1,c2) VALUES('1999','1999'); # Ignore unique constraint INSERT IGNORE INTO t1(c1,c2) VALUES('2098','69'); INSERT IGNORE INTO t3(c1,c2) VALUES(00,00); SELECT * FROM t1 WHERE c1='01' /* Returns 1 row */; SELECT * FROM t2 WHERE c1='1999' AND c2='1999' /* Returns 1 row */; SELECT * FROM t1 WHERE c2='69' /* Returns 1 row */; SELECT * FROM t3 WHERE c1=0 AND c2=0 /* Returns 1 row */; # Test 'INSERT ON DUPLICATE KEY UPDATE' with PK one column/multi-column SELECT * FROM t1 WHERE c1='02' /* Returns no rows */; INSERT INTO t1(c1) VALUES('00') ON DUPLICATE KEY UPDATE c1='02'; SELECT * FROM t1 WHERE c1='2002' /* Returns 1 row */; SELECT * FROM t2 WHERE c1=69 AND c2=70 /* Returns no rows */; INSERT INTO t2 VALUES(99,99,'98-12-20','98.12.20 11:30:45') ON DUPLICATE KEY UPDATE c1=69,c2=70; SELECT * FROM t2 WHERE c1=2069 AND c2=1970 /* Returns 1 row */; # Test 'INSERT ON DUPLICATE KEY UPDATE' with unique one column/multi-column SELECT * FROM t1 WHERE c1=54 AND c2=53 /* Returns no rows */; INSERT INTO t1 VALUES(69,69,'98-12-18','98.12.18 11:30:45') ON DUPLICATE KEY UPDATE c1=54,c2=53; SELECT * FROM t1 WHERE c1=54 AND c2=53 /* Returns 1 row */; SELECT * FROM t3 WHERE c1=6 AND c2=1970 /* Returns no rows */; INSERT INTO t3(c1,c2) VALUES('69','69') ON DUPLICATE KEY UPDATE c1=06,c2=70; SELECT * FROM t3 WHERE c1=2006 AND c2=1970 /* Returns 1 row */; # Test 'INSERT INTO SELECT FROM' CREATE TABLE t5(c1 YEAR NOT NULL, c2 YEAR NULL, c3 DATE, c4 DATETIME, INDEX idx(c1,c2)); INSERT INTO t5 SELECT * FROM t1; --sorted_result SELECT * FROM t5; TRUNCATE TABLE t5; INSERT INTO t5 SELECT * FROM t2 WHERE c1 >=1970 AND c1 < 1999 AND c2 <> '1990'; --sorted_result SELECT * FROM t5; TRUNCATE TABLE t5; INSERT INTO t5 SELECT * FROM t3 WHERE c2 BETWEEN '2000' AND '2155'; --sorted_result SELECT * FROM t5; TRUNCATE TABLE t5; DROP TABLE t5; # Test 'INSERT SET' INSERT INTO t1 SET c1='00'; INSERT INTO t2 SET c1=69; INSERT INTO t3 SET c1=70; INSERT INTO t4 SET c2=99; --sorted_result SELECT * FROM t1 WHERE c1=0; --sorted_result SELECT * FROM t2 WHERE c1=69; --sorted_result SELECT * FROM t3 WHERE c1=70; --sorted_result SELECT * FROM t4 WHERE c2=99; DROP TABLE t1,t2,t3,t4; SET sql_mode=default;
Close
