Edit File: insert_decimal.test
--disable_warnings DROP TABLE IF EXISTS t1,t2; --enable_warnings ######## 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;