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 | : 13.59.48.34
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_decimal.test
--disable_warnings DROP TABLE IF EXISTS t1,t2; --enable_warnings SET sql_mode='NO_ENGINE_SUBSTITUTION'; ######## Running INSERT tests for DECIMAL ######## # Create tables CREATE TABLE t1(c1 DECIMAL(10,5) UNSIGNED NOT NULL, c2 DECIMAL(10,5) SIGNED NULL, c3 DECIMAL, c4 INT, UNIQUE INDEX idx(c1,c2)); CREATE TABLE t2(c1 DECIMAL(10,0) SIGNED NOT NULL, c2 DECIMAL(10) UNSIGNED NULL, c3 DECIMAL, c4 INT); # Test insert values, trailing zeroes are not stripped INSERT INTO t1 VALUES('00100.05000','-00100.05000','00100.05000',1); INSERT INTO t2 VALUES('-1000000000','1000000000','1000000000',2); # Test no approximation INSERT INTO t1(c1) VALUES('12345.000009'); # Test insert max values INSERT INTO t1 VALUES('99999.99999','-99999.99999','99999.99999',3); INSERT INTO t2 VALUES('-9999999999','-9999999999','9999999999',4); # Test insert outside max value, would be clipped to closest endpoint --sorted_result SELECT * FROM t1; --error ER_DUP_ENTRY INSERT INTO t1 VALUES('100000.000002','-100000.000002','100000.000002',5); DELETE FROM t1 WHERE c1='99999.99999' AND c2='-99999.99999'; --sorted_result SELECT * FROM t1; INSERT INTO t1 VALUES('100000.000002','-100000.000002','100000.000002',5); INSERT INTO t2 VALUES('10000000000','-10000000000','10000000000',6); # Test insert leading zero, +/- signs, overflow handling insert into t1 values ("0.0","0.0","0.0",7),("01.0","01.0","01.0",10); insert into t1 values ("-.1","-.1","-.1",13); insert into t1 values ("+111111111.11","+111111111.11","+111111111.11",19); insert into t2 values ("0.0","0.0","0.0",7),("-0.0","-0.0","-0.0",8),("+0.0","+0.0","+0.0",9),("01.0","01.0","01.0",10),("+01.0","+01.0","+01.0",11),("-01.0","-01.0","-01.0",12); insert into t2 values ("-.1","-.1","-.1",13),("+.1","+.1","+.1",14),(".1",".1",".1",15); insert into t2 values ("00000000000001","00000000000001","00000000000001",16),("+0000000000001","+0000000000001","+0000000000001",17),("-0000000000001","-0000000000001","-0000000000001",18); insert into t2 values ("+111111111.11","+111111111.11","+111111111.11",19),("111111111.11","111111111.11","111111111.11",20),("-11111111.11","-11111111.11","-11111111.11",21); insert into t2 values ("-111111111.11","-111111111.11","-111111111.11",22),("+1111111111.11","+1111111111.11","+1111111111.11",23),("1111111111.11","1111111111.11","1111111111.11",24); insert into t2 values ("1e+1000","1e+1000","1e+1000",25),("1e-1000","1e-1000","1e-1000",26),("-1e+1000","-1e+1000","-1e+1000",27); insert into t2 values ("1e+4294967296","1e+4294967296","1e+4294967296",28),("1e-4294967296","1e-4294967296","1e-4294967296",29); insert into t2 values ("1e+18446744073709551615","1e+18446744073709551615","1e+18446744073709551615",30),("1e+18446744073709551616","1e+18446744073709551616","1e+18446744073709551616",31),("1e-9223372036854775807","1e-9223372036854775807","1e-9223372036854775807",32),("1e-9223372036854775809","1e-9223372036854775809","1e-9223372036854775809",33); # Now Select --sorted_result SELECT * FROM t1; --sorted_result SELECT * FROM t2; SELECT count(*) as total_rows, min(c1) as min_value, max(c1) as max_value, sum(c1) as sum, avg(c1) as avg FROM t1; SELECT count(*) as total_rows, min(c2) as min_value, max(c2) as max_value, sum(c2) as sum, avg(c2) as avg FROM t1; SELECT count(*) as total_rows, min(c3) as min_value, max(c3) as max_value, sum(c3) as sum, avg(c3) as avg FROM t1; SELECT count(*) as total_rows, min(c1) as min_value, max(c1) as max_value, sum(c1) as sum, avg(c1) as avg FROM t2; SELECT count(*) as total_rows, min(c2) as min_value, max(c2) as max_value, sum(c2) as sum, avg(c2) as avg FROM t2; SELECT count(*) as total_rows, min(c3) as min_value, max(c3) as max_value, sum(c3) as sum, avg(c3) as avg FROM t2; # Select using various access methods ## Full table scan ## --sorted_result SELECT * FROM t1; --sorted_result SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t1; --sorted_result SELECT * FROM t1 WHERE c3 = '0.0'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t1; ## Backward index scan, covering ## SELECT c1,c2 FROM t1 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t1 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t1 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t1; --sorted_result SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t1; --sorted_result SELECT * FROM t1 WHERE c3 = '-0.0'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t1; ## Backward index scan, covering ## SELECT c1,c2 FROM t1 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t1 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t1 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t1; --sorted_result SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t1; --sorted_result SELECT * FROM t1 WHERE c3 = '-0.0'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t1; ## Backward index scan, covering ## SELECT c1,c2 FROM t1 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t1 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t1 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; # Using index for group-by --sorted_result SELECT c1 FROM t1 GROUP BY c1; --sorted_result SELECT DISTINCT c1 FROM t1; --sorted_result SELECT c1,MIN(c2) FROM t1 GROUP BY c1; # Test extremes CREATE TABLE t5(c1 DECIMAL(65,0) UNSIGNED NOT NULL, c2 DECIMAL(65,0) SIGNED NULL, c3 DECIMAL, c4 INT, INDEX idx(c1,c2)); INSERT INTO t5 VALUES('1e+64','-1e+64','1e+64',1),('1e-64','-1e-64','1e-64',2); INSERT INTO t5 VALUES('1.2e+65','-1.2e+65','1.2e+65',3),('1.2e-65','-1.2e-65','1.2e-65',4); --sorted_result SELECT * FROM t5; DROP TABLE t5; # Test encoding to various bytes CREATE TABLE t5(c1 DECIMAL(18,9) UNSIGNED, c2 DECIMAL(18,9) SIGNED) /* requires 8 bytes */; INSERT INTO t5 VALUES(123456789.123456789,-123456789.123456789); --sorted_result SELECT c1,c2 FROM t5; ALTER TABLE t5 CHANGE c1 c1 DECIMAL(19,9) UNSIGNED, CHANGE c2 c2 DECIMAL(19,9) SIGNED /* requires 9 bytes */; INSERT INTO t5 VALUES(1234567891.123456789,-1234567891.123456789); --sorted_result SELECT c1,c2 FROM t5; ALTER TABLE t5 CHANGE c1 c1 DECIMAL(21,10) UNSIGNED, CHANGE c2 c2 DECIMAL(21,10) SIGNED /* requires 10 bytes */; INSERT INTO t5 VALUES(12345678912.1234567891,-12345678912.1234567891); --sorted_result SELECT c1,c2 FROM t5; ALTER TABLE t5 CHANGE c1 c1 DECIMAL(23,11) UNSIGNED, CHANGE c2 c2 DECIMAL(23,11) SIGNED /* requires 11 bytes */; INSERT INTO t5 VALUES(123456789123.12345678912,-123456789123.12345678912); --sorted_result SELECT c1,c2 FROM t5; ALTER TABLE t5 CHANGE c1 c1 DECIMAL(25,12) UNSIGNED, CHANGE c2 c2 DECIMAL(25,12) SIGNED /* requires 12 bytes */; INSERT INTO t5 VALUES(1234567891234.123456789123,-1234567891234.123456789123); --sorted_result SELECT c1,c2 FROM t5; SELECT SUM(c1),SUM(c2) FROM t5; DROP TABLE t5; # Test ROUND() and TRUNCATE() CREATE TABLE t5(c1 DECIMAL(16,6), c2 tinyint); INSERT INTO t5 VALUES(1.1325,3); SELECT ROUND(c1,c2),TRUNCATE(c1,c2) FROM t5; DROP TABLE t5; # Test wrong decimal types --error ER_TOO_BIG_PRECISION CREATE TABLE t7(c1 DECIMAL(66,0)); --error ER_M_BIGGER_THAN_D CREATE TABLE t7(c1 DECIMAL(5,10)); DROP TABLE t1,t2; ######## Running INSERT tests for FLOAT ######## # Create tables CREATE TABLE t1(c1 FLOAT(10,5) UNSIGNED NOT NULL, c2 FLOAT(10,5) SIGNED NULL, c3 FLOAT, c4 INT, UNIQUE INDEX idx(c1,c2)); CREATE TABLE t2(c1 FLOAT(10,0) SIGNED NOT NULL, c2 FLOAT(10,0) UNSIGNED NULL, c3 FLOAT, c4 INT); # Test insert values, trailing zeroes are not stripped INSERT INTO t1 VALUES('00100.05000','-00100.05000','00100.05000',1); INSERT INTO t2 VALUES('-1000000000','1000000000','1000000000',2); # Test approximation INSERT INTO t1(c1) VALUES('12345.000009'); # Test insert max values INSERT INTO t1 VALUES('99999.99999','-99999.99999','99999.99999',3); INSERT INTO t2 VALUES('-9999999999','-9999999999','9999999999',4); # Test insert outside max value, would be clipped to closest endpoint SELECT * FROM t1; --error ER_DUP_ENTRY INSERT INTO t1 VALUES('100000.000002','-100000.000002','100000.000002',5); DELETE FROM t1 WHERE c1='100000.00000' AND c2='-100000.00000'; INSERT INTO t1 VALUES('100000.000002','-100000.000002','100000.000002',5); INSERT INTO t2 VALUES('10000000000','-10000000000','10000000000',6); # Test insert leading zero, +/- signs, overflow handling insert into t1 values ("0.0","0.0","0.0",7),("01.0","01.0","01.0",10); insert into t1 values ("-.1","-.1","-.1",13); insert into t1 values ("+111111111.11","+111111111.11","+111111111.11",19); insert into t2 values ("0.0","0.0","0.0",7),("-0.0","-0.0","-0.0",8),("+0.0","+0.0","+0.0",9),("01.0","01.0","01.0",10),("+01.0","+01.0","+01.0",11),("-01.0","-01.0","-01.0",12); insert into t2 values ("-.1","-.1","-.1",13),("+.1","+.1","+.1",14),(".1",".1",".1",15); insert into t2 values ("00000000000001","00000000000001","00000000000001",16),("+0000000000001","+0000000000001","+0000000000001",17),("-0000000000001","-0000000000001","-0000000000001",18); insert into t2 values ("+111111111.11","+111111111.11","+111111111.11",19),("111111111.11","111111111.11","111111111.11",20),("-11111111.11","-11111111.11","-11111111.11",21); insert into t2 values ("-111111111.11","-111111111.11","-111111111.11",22),("+1111111111.11","+1111111111.11","+1111111111.11",23),("1111111111.11","1111111111.11","1111111111.11",24); insert into t2 values ("1e+1000","1e+1000","1e+1000",25),("1e-1000","1e-1000","1e-1000",26),("-1e+1000","-1e+1000","-1e+1000",27); insert into t2 values ("1e+4294967296","1e+4294967296","1e+4294967296",28),("1e-4294967296","1e-4294967296","1e-4294967296",29); insert into t2 values ("1e+18446744073709551615","1e+18446744073709551615","1e+18446744073709551615",30),("1e+18446744073709551616","1e+18446744073709551616","1e+18446744073709551616",31),("1e-9223372036854775807","1e-9223372036854775807","1e-9223372036854775807",32),("1e-9223372036854775809","1e-9223372036854775809","1e-9223372036854775809",33); # Now Select --sorted_result SELECT * FROM t1; --sorted_result SELECT * FROM t2; SELECT count(*) as total_rows, min(c1) as min_value, max(c1) as max_value, sum(c1) as sum, avg(c1) as avg FROM t1; SELECT count(*) as total_rows, min(c2) as min_value, max(c2) as max_value, sum(c2) as sum, avg(c2) as avg FROM t1; SELECT count(*) as total_rows, min(c3) as min_value, max(c3) as max_value, sum(c3) as sum, avg(c3) as avg FROM t1; SELECT count(*) as total_rows, min(c1) as min_value, max(c1) as max_value, sum(c1) as sum, avg(c1) as avg FROM t2; SELECT count(*) as total_rows, min(c2) as min_value, max(c2) as max_value, sum(c2) as sum, avg(c2) as avg FROM t2; SELECT count(*) as total_rows, min(c3) as min_value, max(c3) as max_value, sum(c3) as sum, avg(c3) as avg FROM t2; # Select using various access methods ## Full table scan ## --sorted_result SELECT * FROM t1; --sorted_result SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t1; --sorted_result SELECT * FROM t1 WHERE c3 = '0.0'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t1; ## Backward index scan, covering ## SELECT c1,c2 FROM t1 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t1 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t1 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t1; --sorted_result SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t1; --sorted_result SELECT * FROM t1 WHERE c3 = '-0.0'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t1; ## Backward index scan, covering ## SELECT c1,c2 FROM t1 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t1 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t1 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t1; --sorted_result SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t1; --sorted_result SELECT * FROM t1 WHERE c3 = '-0.0'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t1; ## Backward index scan, covering ## SELECT c1,c2 FROM t1 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t1 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t1 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; # Using index for group-by --sorted_result SELECT c1 FROM t1 GROUP BY c1; --sorted_result SELECT DISTINCT c1 FROM t1; --sorted_result SELECT c1,MIN(c2) FROM t1 GROUP BY c1; # Test encoding to 4 or 8 bytes CREATE TABLE t5(c1 FLOAT(23,0) UNSIGNED NOT NULL, c2 FLOAT(23,0) SIGNED NULL, c3 FLOAT, c4 INT, INDEX idx(c1,c2)); INSERT INTO t5 VALUES('1e+22','-1e+22','1e+22',1),('1e-22','-1e-22','1e-22',2); #Output is platform dependent --disable_result_log --sorted_result SELECT * FROM t5; --enable_result_log ALTER TABLE t5 CHANGE c1 c1 FLOAT(24,0) SIGNED NOT NULL, CHANGE c2 c2 FLOAT(24,0) UNSIGNED NULL; INSERT INTO t5 VALUES('-1e+23','1e+23','1e+23',3),('-1e-23','1e-23','1e-23',4); #Output is platform dependent --disable_result_log --sorted_result SELECT * FROM t5; --enable_result_log ALTER TABLE t5 CHANGE c1 c1 FLOAT(53,0) UNSIGNED, CHANGE c2 c2 FLOAT(53,0) SIGNED; INSERT INTO t5 VALUES('1e+52','-1e+52','1e+52',5),('1e-52','-1e-52','1e-52',6); INSERT INTO t5 VALUES('1e+53','-1e+53','1e+53',7),('1e-53','-1e-53','1e-53',8); #Output is platform dependent --replace_column 1 # 2 # --sorted_result SELECT * FROM t5; DROP TABLE t5; # Test ROUND() and TRUNCATE() CREATE TABLE t5(c1 FLOAT(16,6), c2 tinyint); INSERT INTO t5 VALUES(1.1325,3); SELECT ROUND(c1,c2),TRUNCATE(c1,c2) FROM t5; DROP TABLE t5; # Test wrong decimal types --error ER_M_BIGGER_THAN_D CREATE TABLE t7(c1 FLOAT(5,10)); DROP TABLE t1,t2; ######## Running INSERT tests for DOUBLE ######## # Create tables CREATE TABLE t1(c1 DOUBLE(10,5) UNSIGNED NOT NULL, c2 DOUBLE(10,5) SIGNED NULL, c3 DOUBLE, c4 INT, UNIQUE INDEX idx(c1,c2)); CREATE TABLE t2(c1 DOUBLE(10,0) SIGNED NOT NULL, c2 DOUBLE(10,0) UNSIGNED NULL, c3 DOUBLE, c4 INT); # Test insert values, trailing zeroes are not stripped INSERT INTO t1 VALUES('00100.05000','-00100.05000','00100.05000',1); INSERT INTO t2 VALUES('-1000000000','1000000000','1000000000',2); # Test approximation INSERT INTO t1(c1) VALUES('12345.000009'); # Test insert max values INSERT INTO t1 VALUES('99999.99999','-99999.99999','99999.99999',3); INSERT INTO t2 VALUES('-9999999999','-9999999999','9999999999',4); # Test insert outside max value, would be clipped to closest endpoint SELECT * FROM t1; --error ER_DUP_ENTRY INSERT INTO t1 VALUES('100000.000002','-100000.000002','100000.000002',5); DELETE FROM t1 WHERE c1='99999.99999' AND c2='-99999.99999'; INSERT INTO t1 VALUES('100000.000002','-100000.000002','100000.000002',5); INSERT INTO t2 VALUES('10000000000','-10000000000','10000000000',6); # Test insert leading zero, +/- signs, overflow handling insert into t1 values ("0.0","0.0","0.0",7),("01.0","01.0","01.0",10); insert into t1 values ("-.1","-.1","-.1",13); insert into t1 values ("+111111111.11","+111111111.11","+111111111.11",19); insert into t2 values ("0.0","0.0","0.0",7),("-0.0","-0.0","-0.0",8),("+0.0","+0.0","+0.0",9),("01.0","01.0","01.0",10),("+01.0","+01.0","+01.0",11),("-01.0","-01.0","-01.0",12); insert into t2 values ("-.1","-.1","-.1",13),("+.1","+.1","+.1",14),(".1",".1",".1",15); insert into t2 values ("00000000000001","00000000000001","00000000000001",16),("+0000000000001","+0000000000001","+0000000000001",17),("-0000000000001","-0000000000001","-0000000000001",18); insert into t2 values ("+111111111.11","+111111111.11","+111111111.11",19),("111111111.11","111111111.11","111111111.11",20),("-11111111.11","-11111111.11","-11111111.11",21); insert into t2 values ("-111111111.11","-111111111.11","-111111111.11",22),("+1111111111.11","+1111111111.11","+1111111111.11",23),("1111111111.11","1111111111.11","1111111111.11",24); insert into t2 values ("1e+1000","1e+1000","1e+1000",25),("1e-1000","1e-1000","1e-1000",26),("-1e+1000","-1e+1000","-1e+1000",27); insert into t2 values ("1e+4294967296","1e+4294967296","1e+4294967296",28),("1e-4294967296","1e-4294967296","1e-4294967296",29); insert into t2 values ("1e+18446744073709551615","1e+18446744073709551615","1e+18446744073709551615",30),("1e+18446744073709551616","1e+18446744073709551616","1e+18446744073709551616",31),("1e-9223372036854775807","1e-9223372036854775807","1e-9223372036854775807",32),("1e-9223372036854775809","1e-9223372036854775809","1e-9223372036854775809",33); # Now Select --sorted_result SELECT * FROM t1; --sorted_result SELECT * FROM t2; SELECT count(*) as total_rows, min(c1) as min_value, max(c1) as max_value, sum(c1) as sum, avg(c1) as avg FROM t1; SELECT count(*) as total_rows, min(c2) as min_value, max(c2) as max_value, sum(c2) as sum, avg(c2) as avg FROM t1; SELECT count(*) as total_rows, min(c3) as min_value, max(c3) as max_value, sum(c3) as sum, avg(c3) as avg FROM t1; SELECT count(*) as total_rows, min(c1) as min_value, max(c1) as max_value, sum(c1) as sum, avg(c1) as avg FROM t2; SELECT count(*) as total_rows, min(c2) as min_value, max(c2) as max_value, sum(c2) as sum, avg(c2) as avg FROM t2; #SELECT below outputs 'inf' and 'infinity' for Linux/SunOS respectively --replace_column 4 inf 5 inf SELECT count(*) as total_rows, min(c3) as min_value, max(c3) as max_value, sum(c3) as sum, avg(c3) as avg FROM t2; # Select using various access methods ## Full table scan ## --sorted_result SELECT * FROM t1; --sorted_result SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t1; --sorted_result SELECT * FROM t1 WHERE c3 = '0.0'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t1; ## Backward index scan, covering ## SELECT c1,c2 FROM t1 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t1 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t1 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t1; --sorted_result SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t1; --sorted_result SELECT * FROM t1 WHERE c3 = '-0.0'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t1; ## Backward index scan, covering ## SELECT c1,c2 FROM t1 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t1 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t1 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; ## Full table scan ## --sorted_result SELECT * FROM t1; --sorted_result SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t1; --sorted_result SELECT * FROM t1 WHERE c3 = '-0.0'; ## Forward index scan, covering ## --sorted_result SELECT c1,c2 FROM t1; ## Backward index scan, covering ## SELECT c1,c2 FROM t1 ORDER BY c1,c2 DESC; ## Forward index scan, non-covering ## SELECT * FROM t1 ORDER BY c1,c2 LIMIT 2; ## Backward index scan, non-covering ## SELECT * FROM t1 ORDER BY c1,c2 DESC LIMIT 2; ## ref type access SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; ## Range access, ordered ## SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2; SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2; SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2; ## Range access, backwards scan ## SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2; SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC; SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; # Using index for group-by --sorted_result SELECT c1 FROM t1 GROUP BY c1; --sorted_result SELECT DISTINCT c1 FROM t1; --sorted_result SELECT c1,MIN(c2) FROM t1 GROUP BY c1; # Test encoding to 4 or 8 bytes CREATE TABLE t5(c1 DOUBLE(23,0) UNSIGNED NOT NULL, c2 DOUBLE(23,0) SIGNED NULL, c3 DOUBLE, c4 INT, INDEX idx(c1,c2)); INSERT INTO t5 VALUES('1e+22','-1e+22','1e+22',1),('1e-22','-1e-22','1e-22',2); --sorted_result SELECT * FROM t5; ALTER TABLE t5 CHANGE c1 c1 DOUBLE(24,0) SIGNED NOT NULL, CHANGE c2 c2 DOUBLE(24,0) UNSIGNED NULL; INSERT INTO t5 VALUES('-1e+23','1e+23','1e+23',3),('-1e-23','1e-23','1e-23',4); #Output is platform dependent --disable_result_log --sorted_result SELECT * FROM t5; --enable_result_log ALTER TABLE t5 CHANGE c1 c1 DOUBLE(53,0) UNSIGNED, CHANGE c2 c2 DOUBLE(53,0) SIGNED; INSERT INTO t5 VALUES('1e+52','-1e+52','1e+52',5),('1e-52','-1e-52','1e-52',6); INSERT INTO t5 VALUES('1e+53','-1e+53','1e+53',7),('1e-53','-1e-53','1e-53',8); #Output is platform dependent --disable_result_log --sorted_result SELECT * FROM t5; --enable_result_log DROP TABLE t5; # Test ROUND() and TRUNCATE() CREATE TABLE t5(c1 DOUBLE(16,6), c2 tinyint); INSERT INTO t5 VALUES(1.1325,3); SELECT ROUND(c1,c2),TRUNCATE(c1,c2) FROM t5; DROP TABLE t5; # Test wrong decimal types --error ER_M_BIGGER_THAN_D CREATE TABLE t7(c1 DOUBLE(5,10)); DROP TABLE t1,t2; SET sql_mode=default;
Close
