| Quellcodebibliothek Statistik Leitseite products/Sources/formale Sprachen/C/MariaDB/mysql-test/suite/engines/iuds/r/ (MariaDB Server Version 8.1-8.4©) Datei vom 1.9.2026 mit Größe 98 kB |
|
SSL insert_decimal.result Sprache: Lisp |
|||||||||||||||
|
DROP TABLE IF EXISTS t1,t2; CREATE TABLE t1(c1 DECIMAL(10,5) UNSIGNED NOT NULL, c2 DECIMAL(10,5) SIGNED NULL, c3 DECIMAL CREATE TABLE t2(c1 DECIMAL(10,0) SIGNED NOT NULL, c2 DECIMAL(10) UNSIGNED NULL, c3 DECIMAL, c INSERT INTO t1 VALUES('00100.05000','-00100.05000','00100.05000',1); Warnings: Note 1265 Data truncated for column 'c3' at row 1 INSERT INTO t2 VALUES('-1000000000','1000000000','1000000000',2); INSERT INTO t1(c1) VALUES('12345.000009'); Warnings: Note 1265 Data truncated for column 'c1' at row 1 INSERT INTO t1 VALUES('99999.99999','-99999.99999','99999.99999',3); Warnings: Note 1265 Data truncated for column 'c3' at row 1 INSERT IGNORE INTO t2 VALUES('-9999999999','-9999999999','9999999999',4); Warnings: Warning 1264 Out of range value for column 'c2' at row 1 SELECT * FROM t1; c1 c2 c3 c4 100.05000 -100.05000 100 1 12345.00001 NULL NULL NULL 99999.99999 -99999.99999 100000 3 SET STATEMENT sql_mode = '' FOR INSERT INTO t1 VALUES('100000.000002','-100000.000002','100000.000002',5); ERROR 23000: Duplicate entry '99999.99999--99999.99999' for key 'idx' DELETE FROM t1 WHERE c1='99999.99999' AND c2='-99999.99999'; SELECT * FROM t1; c1 c2 c3 c4 100.05000 -100.05000 100 1 12345.00001 NULL NULL NULL INSERT IGNORE INTO t1 VALUES('100000.000002','-100000.000002','100000.000002',5); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 Warning 1264 Out of range value for column 'c2' at row 1 Note 1265 Data truncated for column 'c3' at row 1 INSERT IGNORE INTO t2 VALUES('10000000000','-10000000000','10000000000',6); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 Warning 1264 Out of range value for column 'c2' at row 1 Warning 1264 Out of range value for column 'c3' at row 1 insert into t1 values ("0.0","0.0","0.0",7),("01.0","01.0","01.0",10); insert ignore into t1 values ("-.1","-.1","-.1",13); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 Note 1265 Data truncated for column 'c3' at row 1 insert ignore into t1 values ("+111111111.11","+111111111.11","+111111111.11",19); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 Warning 1264 Out of range value for column 'c2' at row 1 Note 1265 Data truncated for column 'c3' at row 1 insert ignore into t2 values ("0.0","0.0","0.0",7),("-0.0","-0.0","-0.0",8),("+0.0"," Warnings: Warning 1264 Out of range value for column 'c2' at row 6 insert ignore into t2 values ("-.1","-.1","-.1",13),("+.1","+.1","+.1",14),(".1",".1" Warnings: Note 1265 Data truncated for column 'c1' at row 1 Warning 1264 Out of range value for column 'c2' at row 1 Note 1265 Data truncated for column 'c3' at row 1 Note 1265 Data truncated for column 'c1' at row 2 Note 1265 Data truncated for column 'c2' at row 2 Note 1265 Data truncated for column 'c3' at row 2 Note 1265 Data truncated for column 'c1' at row 3 Note 1265 Data truncated for column 'c2' at row 3 Note 1265 Data truncated for column 'c3' at row 3 insert ignore into t2 values ("00000000000001","00000000000001","00000000000001",16), Warnings: Warning 1264 Out of range value for column 'c2' at row 3 insert ignore into t2 values ("+111111111.11","+111111111.11","+111111111.11",19),("1 Warnings: Note 1265 Data truncated for column 'c1' at row 1 Note 1265 Data truncated for column 'c2' at row 1 Note 1265 Data truncated for column 'c3' at row 1 Note 1265 Data truncated for column 'c1' at row 2 Note 1265 Data truncated for column 'c2' at row 2 Note 1265 Data truncated for column 'c3' at row 2 Note 1265 Data truncated for column 'c1' at row 3 Warning 1264 Out of range value for column 'c2' at row 3 Note 1265 Data truncated for column 'c3' at row 3 insert ignore into t2 values ("-111111111.11","-111111111.11","-111111111.11",22),("+ Warnings: Note 1265 Data truncated for column 'c1' at row 1 Warning 1264 Out of range value for column 'c2' at row 1 Note 1265 Data truncated for column 'c3' at row 1 Note 1265 Data truncated for column 'c1' at row 2 Note 1265 Data truncated for column 'c2' at row 2 Note 1265 Data truncated for column 'c3' at row 2 Note 1265 Data truncated for column 'c1' at row 3 Note 1265 Data truncated for column 'c2' at row 3 Note 1265 Data truncated for column 'c3' at row 3 insert ignore into t2 values ("1e+1000","1e+1000","1e+1000",25),("1e-1000","1e-1000", Warnings: Warning 1264 Out of range value for column 'c1' at row 1 Warning 1264 Out of range value for column 'c2' at row 1 Warning 1264 Out of range value for column 'c3' at row 1 Note 1265 Data truncated for column 'c1' at row 2 Note 1265 Data truncated for column 'c2' at row 2 Note 1265 Data truncated for column 'c3' at row 2 Warning 1264 Out of range value for column 'c1' at row 3 Warning 1264 Out of range value for column 'c2' at row 3 Warning 1264 Out of range value for column 'c3' at row 3 insert ignore into t2 values ("1e+4294967296","1e+4294967296","1e+4294967296",28),("1 Warnings: Warning 1264 Out of range value for column 'c1' at row 1 Warning 1264 Out of range value for column 'c2' at row 1 Warning 1264 Out of range value for column 'c3' at row 1 Note 1265 Data truncated for column 'c1' at row 2 Note 1265 Data truncated for column 'c2' at row 2 Note 1265 Data truncated for column 'c3' at row 2 insert ignore into t2 values ("1e+18446744073709551615","1e+18446744073709551615","1e Warnings: Warning 1264 Out of range value for column 'c1' at row 1 Warning 1264 Out of range value for column 'c2' at row 1 Warning 1264 Out of range value for column 'c3' at row 1 Warning 1264 Out of range value for column 'c1' at row 2 Warning 1264 Out of range value for column 'c2' at row 2 Warning 1264 Out of range value for column 'c3' at row 2 Note 1265 Data truncated for column 'c1' at row 3 Note 1265 Data truncated for column 'c2' at row 3 Note 1265 Data truncated for column 'c3' at row 3 SELECT * FROM t1; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 12345.00001 NULL NULL NULL 99999.99999 -99999.99999 100000 5 99999.99999 99999.99999 111111111 19 SELECT * FROM t2; c1 c2 c3 c4 -1 0 -1 12 -1 0 -1 18 -1000000000 1000000000 1000000000 2 -11111111 0 -11111111 21 -111111111 0 -111111111 22 -9999999999 0 -9999999999 27 -9999999999 0 9999999999 4 0 0 0 13 0 0 0 14 0 0 0 15 0 0 0 26 0 0 0 29 0 0 0 32 0 0 0 33 0 0 0 7 0 0 0 8 0 0 0 9 1 1 1 10 1 1 1 11 1 1 1 16 1 1 1 17 111111111 111111111 111111111 19 111111111 111111111 111111111 20 1111111111 1111111111 1111111111 23 1111111111 1111111111 1111111111 24 9999999999 0 9999999999 6 9999999999 9999999999 9999999999 25 9999999999 9999999999 9999999999 28 9999999999 9999999999 9999999999 30 9999999999 9999999999 9999999999 31 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) as max_value, sum(c1) as sum, av total_rows min_value max_value sum avg 7 0.00000 99999.99999 212446.04999 30349.435712857 SELECT count(*) as total_rows, min(c2) as min_value, max(c2) as max_value, sum(c2) as sum, av total_rows min_value max_value sum avg 7 -99999.99999 99999.99999 -99.15000 -16.525000000 SELECT count(*) as total_rows, min(c3) as min_value, max(c3) as max_value, sum(c3) as sum, av total_rows min_value max_value sum avg 7 0 111111111 111211212 18535202.0000 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) as max_value, sum(c1) as sum, av total_rows min_value max_value sum avg 30 -9999999999 9999999999 31322222221 1044074074.0333 SELECT count(*) as total_rows, min(c2) as min_value, max(c2) as max_value, sum(c2) as sum, av total_rows min_value max_value sum avg 30 0 9999999999 43444444444 1448148148.1333 SELECT count(*) as total_rows, min(c3) as min_value, max(c3) as max_value, sum(c3) as sum, av total_rows min_value max_value sum avg 30 -9999999999 9999999999 53322222219 1777407407.3000 SELECT * FROM t1; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 12345.00001 NULL NULL NULL 99999.99999 -99999.99999 100000 5 99999.99999 99999.99999 111111111 19 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t1; total_rows min_value max(c1) 7 0.00000 99999.99999 SELECT * FROM t1 WHERE c3 = '0.0'; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 SELECT c1,c2 FROM t1; c1 c2 0.00000 -0.10000 0.00000 0.00000 1.00000 1.00000 100.05000 -100.05000 12345.00001 NULL 99999.99999 -99999.99999 99999.99999 99999.99999 SELECT c1,c2 FROM t1 ORDER BY c1,c2 DESC; c1 c2 0.00000 0.00000 0.00000 -0.10000 1.00000 1.00000 100.05000 -100.05000 12345.00001 NULL 99999.99999 99999.99999 99999.99999 -99999.99999 SELECT * FROM t1 ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 SELECT * FROM t1 ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2; c1 c2 c3 c4 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 12345.00001 NULL NULL NULL 99999.99999 -99999.99999 100000 5 99999.99999 99999.99999 111111111 19 SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2; c1 c2 c3 c4 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 12345.00001 NULL NULL NULL 99999.99999 -99999.99999 100000 5 99999.99999 99999.99999 111111111 19 SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 12345.00001 NULL NULL NULL 99999.99999 -99999.99999 100000 5 99999.99999 99999.99999 111111111 19 SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 12345.00001 NULL NULL NULL 99999.99999 -99999.99999 100000 5 99999.99999 99999.99999 111111111 19 SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 99999.99999 -99999.99999 100000 5 99999.99999 99999.99999 111111111 19 SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2; c1 c2 c3 c4 1.00000 1.00000 1 10 SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 1.00000 1.00000 1 10 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 12345.00001 NULL NULL NULL 99999.99999 -99999.99999 100000 5 99999.99999 99999.99999 111111111 19 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2 DESC; c1 c2 c3 c4 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 12345.00001 NULL NULL NULL 99999.99999 99999.99999 111111111 19 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2 DESC; c1 c2 c3 c4 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 12345.00001 NULL NULL NULL 99999.99999 99999.99999 111111111 19 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 12345.00001 NULL NULL NULL 99999.99999 99999.99999 111111111 19 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2 DESC; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 12345.00001 NULL NULL NULL 99999.99999 99999.99999 111111111 19 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 99999.99999 99999.99999 111111111 19 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2 DESC; c1 c2 c3 c4 1.00000 1.00000 1 10 SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 1.00000 1.00000 1 10 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 12345.00001 NULL NULL NULL 99999.99999 99999.99999 111111111 19 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 SELECT * FROM t1; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 12345.00001 NULL NULL NULL 99999.99999 -99999.99999 100000 5 99999.99999 99999.99999 111111111 19 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t1; total_rows min_value max(c1) 7 0.00000 99999.99999 SELECT * FROM t1 WHERE c3 = '-0.0'; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 SELECT c1,c2 FROM t1; c1 c2 0.00000 -0.10000 0.00000 0.00000 1.00000 1.00000 100.05000 -100.05000 12345.00001 NULL 99999.99999 -99999.99999 99999.99999 99999.99999 SELECT c1,c2 FROM t1 ORDER BY c1,c2 DESC; c1 c2 0.00000 0.00000 0.00000 -0.10000 1.00000 1.00000 100.05000 -100.05000 12345.00001 NULL 99999.99999 99999.99999 99999.99999 -99999.99999 SELECT * FROM t1 ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 SELECT * FROM t1 ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 99999.99999 -99999.99999 100000 5 99999.99999 99999.99999 111111111 19 SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 99999.99999 -99999.99999 100000 5 99999.99999 99999.99999 111111111 19 SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 99999.99999 99999.99999 111111111 19 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 99999.99999 99999.99999 111111111 19 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 12345.00001 NULL NULL NULL SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 99999.99999 -99999.99999 100000 5 99999.99999 99999.99999 111111111 19 SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 99999.99999 -99999.99999 100000 5 99999.99999 99999.99999 111111111 19 SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 12345.00001 NULL NULL NULL SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 12345.00001 NULL NULL NULL 99999.99999 -99999.99999 100000 5 99999.99999 99999.99999 111111111 19 SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 99999.99999 -99999.99999 100000 5 99999.99999 99999.99999 111111111 19 SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 99999.99999 -99999.99999 100000 5 99999.99999 99999.99999 111111111 19 SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 100.05000 -100.05000 100 1 12345.00001 NULL NULL NULL 99999.99999 -99999.99999 100000 5 99999.99999 99999.99999 111111111 19 SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 100.05000 -100.05000 100 1 12345.00001 NULL NULL NULL SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2; c1 c2 c3 c4 100.05000 -100.05000 100 1 99999.99999 -99999.99999 100000 5 99999.99999 99999.99999 111111111 19 SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 100.05000 -100.05000 100 1 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2 LI c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 12345.00001 NULL NULL NULL 99999.99999 -99999.99999 100000 5 99999.99999 99999.99999 111111111 19 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 12345.00001 NULL NULL NULL SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 99999.99999 99999.99999 111111111 19 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 99999.99999 99999.99999 111111111 19 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 12345.00001 NULL NULL NULL SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 12345.00001 NULL NULL NULL 99999.99999 99999.99999 111111111 19 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 99999.99999 99999.99999 111111111 19 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 99999.99999 99999.99999 111111111 19 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 100.05000 -100.05000 100 1 12345.00001 NULL NULL NULL 99999.99999 99999.99999 111111111 19 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 100.05000 -100.05000 100 1 12345.00001 NULL NULL NULL SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2 DESC; c1 c2 c3 c4 100.05000 -100.05000 100 1 99999.99999 99999.99999 111111111 19 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 100.05000 -100.05000 100 1 99999.99999 99999.99999 111111111 19 SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2 DE c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2 DE c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 12345.00001 NULL NULL NULL 99999.99999 99999.99999 111111111 19 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 SELECT * FROM t1; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 12345.00001 NULL NULL NULL 99999.99999 -99999.99999 100000 5 99999.99999 99999.99999 111111111 19 SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t1; total_rows min_value max(c2) 7 -99999.99999 99999.99999 SELECT * FROM t1 WHERE c3 = '-0.0'; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 SELECT c1,c2 FROM t1; c1 c2 0.00000 -0.10000 0.00000 0.00000 1.00000 1.00000 100.05000 -100.05000 12345.00001 NULL 99999.99999 -99999.99999 99999.99999 99999.99999 SELECT c1,c2 FROM t1 ORDER BY c1,c2 DESC; c1 c2 0.00000 0.00000 0.00000 -0.10000 1.00000 1.00000 100.05000 -100.05000 12345.00001 NULL 99999.99999 99999.99999 99999.99999 -99999.99999 SELECT * FROM t1 ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 SELECT * FROM t1 ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 99999.99999 99999.99999 111111111 19 SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 99999.99999 99999.99999 111111111 19 SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 99999.99999 -99999.99999 100000 5 99999.99999 99999.99999 111111111 19 SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 100.05000 -100.05000 100 1 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2; c1 c2 c3 c4 0.00000 0.00000 0 7 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2 LIMI c1 c2 c3 c4 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2; c1 c2 c3 c4 12345.00001 NULL NULL NULL SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 12345.00001 NULL NULL NULL SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 99999.99999 -99999.99999 100000 5 99999.99999 99999.99999 111111111 19 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 0 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 99999.99999 99999.99999 111111111 19 SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 99999.99999 99999.99999 111111111 19 SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 99999.99999 99999.99999 111111111 19 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 100.05000 -100.05000 100 1 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2 DESC c1 c2 c3 c4 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2 DESC c1 c2 c3 c4 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2 DESC; c1 c2 c3 c4 12345.00001 NULL NULL NULL SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 12345.00001 NULL NULL NULL SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 1.00000 1.00000 1 10 100.05000 -100.05000 100 1 99999.99999 99999.99999 111111111 19 99999.99999 -99999.99999 100000 5 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 0 13 SELECT c1 FROM t1 GROUP BY c1; c1 0.00000 1.00000 100.05000 12345.00001 99999.99999 SELECT DISTINCT c1 FROM t1; c1 0.00000 1.00000 100.05000 12345.00001 99999.99999 SELECT c1,MIN(c2) FROM t1 GROUP BY c1; c1 MIN(c2) 0.00000 -0.10000 1.00000 1.00000 100.05000 -100.05000 12345.00001 NULL 99999.99999 -99999.99999 CREATE TABLE t5(c1 DECIMAL(65,0) UNSIGNED NOT NULL, c2 DECIMAL(65,0) SIGNED NULL, c3 DECIMAL INSERT IGNORE INTO t5 VALUES('1e+64','-1e+64','1e+64',1),('1e-64','-1e-64','1e-64',2 Warnings: Warning 1264 Out of range value for column 'c3' at row 1 Note 1265 Data truncated for column 'c1' at row 2 Note 1265 Data truncated for column 'c2' at row 2 Note 1265 Data truncated for column 'c3' at row 2 INSERT IGNORE INTO t5 VALUES('1.2e+65','-1.2e+65','1.2e+65',3),('1.2e-65','-1.2e-65' Warnings: Warning 1264 Out of range value for column 'c1' at row 1 Warning 1264 Out of range value for column 'c2' at row 1 Warning 1264 Out of range value for column 'c3' at row 1 Note 1265 Data truncated for column 'c1' at row 2 Note 1265 Data truncated for column 'c2' at row 2 Note 1265 Data truncated for column 'c3' at row 2 SELECT * FROM t5; c1 c2 c3 c4 0 0 0 2 0 0 0 4 10000000000000000000000000000000000000000000000000000000000000000 -10000000000000 99999999999999999999999999999999999999999999999999999999999999999 -99999999999999 DROP TABLE t5; CREATE TABLE t5(c1 DECIMAL(18,9) UNSIGNED, c2 DECIMAL(18,9) SIGNED) /* requires 8 bytes */; INSERT INTO t5 VALUES(123456789.123456789,-123456789.123456789); SELECT c1,c2 FROM t5; c1 c2 123456789.123456789 -123456789.123456789 ALTER TABLE t5 CHANGE c1 c1 DECIMAL(19,9) UNSIGNED, CHANGE c2 c2 DECIMAL(19,9) SIGNED /* requi INSERT INTO t5 VALUES(1234567891.123456789,-1234567891.123456789); SELECT c1,c2 FROM t5; c1 c2 123456789.123456789 -123456789.123456789 1234567891.123456789 -1234567891.123456789 ALTER TABLE t5 CHANGE c1 c1 DECIMAL(21,10) UNSIGNED, CHANGE c2 c2 DECIMAL(21,10) SIGNED /* req INSERT INTO t5 VALUES(12345678912.1234567891,-12345678912.1234567891); SELECT c1,c2 FROM t5; c1 c2 123456789.1234567890 -123456789.1234567890 1234567891.1234567890 -1234567891.1234567890 12345678912.1234567891 -12345678912.1234567891 ALTER TABLE t5 CHANGE c1 c1 DECIMAL(23,11) UNSIGNED, CHANGE c2 c2 DECIMAL(23,11) SIGNED /* req INSERT INTO t5 VALUES(123456789123.12345678912,-123456789123.12345678912); SELECT c1,c2 FROM t5; c1 c2 123456789.12345678900 -123456789.12345678900 1234567891.12345678900 -1234567891.12345678900 12345678912.12345678910 -12345678912.12345678910 123456789123.12345678912 -123456789123.12345678912 ALTER TABLE t5 CHANGE c1 c1 DECIMAL(25,12) UNSIGNED, CHANGE c2 c2 DECIMAL(25,12) SIGNED /* req INSERT INTO t5 VALUES(1234567891234.123456789123,-1234567891234.123456789123); SELECT c1,c2 FROM t5; c1 c2 123456789.123456789000 -123456789.123456789000 1234567891.123456789000 -1234567891.123456789000 12345678912.123456789100 -12345678912.123456789100 123456789123.123456789120 -123456789123.123456789120 1234567891234.123456789123 -1234567891234.123456789123 SELECT SUM(c1),SUM(c2) FROM t5; SUM(c1) SUM(c2) 1371728383949.617283945343 -1371728383949.617283945343 DROP TABLE t5; 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; ROUND(c1,c2) TRUNCATE(c1,c2) 1.133000 1.132000 DROP TABLE t5; CREATE TABLE t7(c1 DECIMAL(66,0)); ERROR 42000: Too big precision specified for 'c1'. Maximum is 65 CREATE TABLE t7(c1 DECIMAL(5,10)); ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column 'c1') DROP TABLE t1,t2; CREATE TABLE t1(c1 FLOAT(10,5) UNSIGNED NOT NULL, c2 FLOAT(10,5) SIGNED NULL, c3 FLOAT, c4 INT CREATE TABLE t2(c1 FLOAT(10,0) SIGNED NOT NULL, c2 FLOAT(10,0) UNSIGNED NULL, c3 FLOAT, c4 INT INSERT INTO t1 VALUES('00100.05000','-00100.05000','00100.05000',1); INSERT INTO t2 VALUES('-1000000000','1000000000','1000000000',2); INSERT INTO t1(c1) VALUES('12345.000009'); INSERT INTO t1 VALUES('99999.99999','-99999.99999','99999.99999',3); INSERT IGNORE INTO t2 VALUES('-9999999999','-9999999999','9999999999',4); Warnings: Warning 1264 Out of range value for column 'c2' at row 1 SELECT * FROM t1; c1 c2 c3 c4 100.05000 -100.05000 100.05 1 12345.00000 NULL NULL NULL 100000.00000 -100000.00000 100000 3 SET STATEMENT sql_mode = '' FOR INSERT INTO t1 VALUES('100000.000002','-100000.000002','100000.000002',5); ERROR 23000: Duplicate entry '100000.00000--100000.00000' for key 'idx' DELETE FROM t1 WHERE c1='100000.00000' AND c2='-100000.00000'; INSERT IGNORE INTO t1 VALUES('100000.000002','-100000.000002','100000.000002',5); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 Warning 1264 Out of range value for column 'c2' at row 1 INSERT IGNORE INTO t2 VALUES('10000000000','-10000000000','10000000000',6); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 Warning 1264 Out of range value for column 'c2' at row 1 insert into t1 values ("0.0","0.0","0.0",7),("01.0","01.0","01.0",10); insert ignore into t1 values ("-.1","-.1","-.1",13); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 insert ignore into t1 values ("+111111111.11","+111111111.11","+111111111.11",19); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 Warning 1264 Out of range value for column 'c2' at row 1 insert ignore into t2 values ("0.0","0.0","0.0",7),("-0.0","-0.0","-0.0",8),("+0.0"," Warnings: Warning 1264 Out of range value for column 'c2' at row 6 insert ignore into t2 values ("-.1","-.1","-.1",13),("+.1","+.1","+.1",14),(".1",".1" Warnings: Warning 1264 Out of range value for column 'c2' at row 1 insert ignore into t2 values ("00000000000001","00000000000001","00000000000001",16), Warnings: Warning 1264 Out of range value for column 'c2' at row 3 insert ignore into t2 values ("+111111111.11","+111111111.11","+111111111.11",19),("1 Warnings: Warning 1264 Out of range value for column 'c2' at row 3 insert ignore into t2 values ("-111111111.11","-111111111.11","-111111111.11",22),("+ Warnings: Warning 1264 Out of range value for column 'c2' at row 1 insert ignore into t2 values ("1e+1000","1e+1000","1e+1000",25),("1e-1000","1e-1000", Warnings: Warning 1264 Out of range value for column 'c1' at row 1 Warning 1264 Out of range value for column 'c1' at row 1 Warning 1264 Out of range value for column 'c2' at row 1 Warning 1264 Out of range value for column 'c2' at row 1 Warning 1264 Out of range value for column 'c3' at row 1 Warning 1264 Out of range value for column 'c3' at row 1 Warning 1264 Out of range value for column 'c1' at row 3 Warning 1264 Out of range value for column 'c1' at row 3 Warning 1264 Out of range value for column 'c2' at row 3 Warning 1264 Out of range value for column 'c2' at row 3 Warning 1264 Out of range value for column 'c3' at row 3 Warning 1264 Out of range value for column 'c3' at row 3 insert ignore into t2 values ("1e+4294967296","1e+4294967296","1e+4294967296",28),("1 Warnings: Warning 1264 Out of range value for column 'c1' at row 1 Warning 1264 Out of range value for column 'c1' at row 1 Warning 1264 Out of range value for column 'c2' at row 1 Warning 1264 Out of range value for column 'c2' at row 1 Warning 1264 Out of range value for column 'c3' at row 1 Warning 1264 Out of range value for column 'c3' at row 1 insert ignore into t2 values ("1e+18446744073709551615","1e+18446744073709551615","1e Warnings: Warning 1264 Out of range value for column 'c1' at row 1 Warning 1264 Out of range value for column 'c1' at row 1 Warning 1264 Out of range value for column 'c2' at row 1 Warning 1264 Out of range value for column 'c2' at row 1 Warning 1264 Out of range value for column 'c3' at row 1 Warning 1264 Out of range value for column 'c3' at row 1 Warning 1264 Out of range value for column 'c1' at row 2 Warning 1264 Out of range value for column 'c1' at row 2 Warning 1264 Out of range value for column 'c2' at row 2 Warning 1264 Out of range value for column 'c2' at row 2 Warning 1264 Out of range value for column 'c3' at row 2 Warning 1264 Out of range value for column 'c3' at row 2 SELECT * FROM t1; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 100000.00000 -100000.00000 100000 5 100000.00000 100000.00000 111111000 19 12345.00000 NULL NULL NULL SELECT * FROM t2; c1 c2 c3 c4 -1 0 -1 12 -1 0 -1 18 -1000000000 1000000000 1000000000 2 -10000000000 0 -3.40282e38 27 -10000000000 0 10000000000 4 -11111111 0 -11111100 21 -111111112 0 -111111000 22 0 0 -0.1 13 0 0 0 26 0 0 0 29 0 0 0 32 0 0 0 33 0 0 0 7 0 0 0 8 0 0 0 9 0 0 0.1 14 0 0 0.1 15 1 1 1 10 1 1 1 11 1 1 1 16 1 1 1 17 10000000000 0 10000000000 6 10000000000 10000000000 3.40282e38 25 10000000000 10000000000 3.40282e38 28 10000000000 10000000000 3.40282e38 30 10000000000 10000000000 3.40282e38 31 111111112 111111112 111111000 19 111111112 111111112 111111000 20 1111111168 1111111168 1111110000 23 1111111168 1111111168 1111110000 24 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) as max_value, sum(c1) as sum, av total_rows min_value max_value sum avg 7 0.00000 100000.00000 212446.05000 30349.435714722 SELECT count(*) as total_rows, min(c2) as min_value, max(c2) as max_value, sum(c2) as sum, av total_rows min_value max_value sum avg 7 -100000.00000 100000.00000 -99.15000 -16.525000509 SELECT count(*) as total_rows, min(c3) as min_value, max(c3) as max_value, sum(c3) as sum, av total_rows min_value max_value sum avg 7 -0.1 111111000 111211212.95000306 18535202.15833384 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) as max_value, sum(c1) as sum, av total_rows min_value max_value sum avg 30 -10000000000 10000000000 31322222339 1044074077.9667 SELECT count(*) as total_rows, min(c2) as min_value, max(c2) as max_value, sum(c2) as sum, av total_rows min_value max_value sum avg 30 0 10000000000 43444444564 1448148152.1333 SELECT count(*) as total_rows, min(c3) as min_value, max(c3) as max_value, sum(c3) as sum, av total_rows min_value max_value sum avg 30 -3.40282e38 3.40282e38 1.0208470399155866e39 3.4028234663852886e37 SELECT * FROM t1; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 100000.00000 -100000.00000 100000 5 100000.00000 100000.00000 111111000 19 12345.00000 NULL NULL NULL SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t1; total_rows min_value max(c1) 7 0.00000 100000.00000 SELECT * FROM t1 WHERE c3 = '0.0'; c1 c2 c3 c4 0.00000 0.00000 0 7 SELECT c1,c2 FROM t1; c1 c2 0.00000 -0.10000 0.00000 0.00000 1.00000 1.00000 100.05000 -100.05000 100000.00000 -100000.00000 100000.00000 100000.00000 12345.00000 NULL SELECT c1,c2 FROM t1 ORDER BY c1,c2 DESC; c1 c2 0.00000 0.00000 0.00000 -0.10000 1.00000 1.00000 100.05000 -100.05000 12345.00000 NULL 100000.00000 100000.00000 100000.00000 -100000.00000 SELECT * FROM t1 ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2; c1 c2 c3 c4 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00000 NULL NULL NULL 100000.00000 -100000.00000 100000 5 100000.00000 100000.00000 111111000 19 SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2; c1 c2 c3 c4 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00000 NULL NULL NULL 100000.00000 -100000.00000 100000 5 100000.00000 100000.00000 111111000 19 SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00000 NULL NULL NULL 100000.00000 -100000.00000 100000 5 100000.00000 100000.00000 111111000 19 SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00000 NULL NULL NULL SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2; c1 c2 c3 c4 1.00000 1.00000 1 10 SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 1.00000 1.00000 1 10 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00000 NULL NULL NULL 100000.00000 -100000.00000 100000 5 100000.00000 100000.00000 111111000 19 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2 DESC; c1 c2 c3 c4 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00000 NULL NULL NULL 100000.00000 100000.00000 111111000 19 100000.00000 -100000.00000 100000 5 SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2 DESC; c1 c2 c3 c4 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00000 NULL NULL NULL 100000.00000 100000.00000 111111000 19 100000.00000 -100000.00000 100000 5 SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00000 NULL NULL NULL 100000.00000 100000.00000 111111000 19 100000.00000 -100000.00000 100000 5 SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2 DESC; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00000 NULL NULL NULL SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2 DESC; c1 c2 c3 c4 1.00000 1.00000 1 10 SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 1.00000 1.00000 1 10 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00000 NULL NULL NULL 100000.00000 100000.00000 111111000 19 100000.00000 -100000.00000 100000 5 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 100000.00000 -100000.00000 100000 5 100000.00000 100000.00000 111111000 19 12345.00000 NULL NULL NULL SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t1; total_rows min_value max(c1) 7 0.00000 100000.00000 SELECT * FROM t1 WHERE c3 = '-0.0'; c1 c2 c3 c4 0.00000 0.00000 0 7 SELECT c1,c2 FROM t1; c1 c2 0.00000 -0.10000 0.00000 0.00000 1.00000 1.00000 100.05000 -100.05000 100000.00000 -100000.00000 100000.00000 100000.00000 12345.00000 NULL SELECT c1,c2 FROM t1 ORDER BY c1,c2 DESC; c1 c2 0.00000 0.00000 0.00000 -0.10000 1.00000 1.00000 100.05000 -100.05000 12345.00000 NULL 100000.00000 100000.00000 100000.00000 -100000.00000 SELECT * FROM t1 ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00000 NULL NULL NULL 100000.00000 -100000.00000 100000 5 100000.00000 100000.00000 111111000 19 SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 100000.00000 -100000.00000 100000 5 100000.00000 100000.00000 111111000 19 SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 100000.00000 -100000.00000 100000 5 100000.00000 100000.00000 111111000 19 SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 100000.00000 -100000.00000 100000 5 100000.00000 100000.00000 111111000 19 SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 100000.00000 -100000.00000 100000 5 100000.00000 100000.00000 111111000 19 SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00000 NULL NULL NULL SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00000 NULL NULL NULL SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 100.05000 -100.05000 100.05 1 12345.00000 NULL NULL NULL SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 100.05000 -100.05000 100.05 1 12345.00000 NULL NULL NULL SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2 LI c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00000 NULL NULL NULL 100000.00000 -100000.00000 100000 5 100000.00000 100000.00000 111111000 19 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00000 NULL NULL NULL 100000.00000 100000.00000 111111000 19 100000.00000 -100000.00000 100000 5 SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 100000.00000 100000.00000 111111000 19 100000.00000 -100000.00000 100000 5 SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 100000.00000 100000.00000 111111000 19 100000.00000 -100000.00000 100000 5 SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 100000.00000 100000.00000 111111000 19 100000.00000 -100000.00000 100000 5 SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 100000.00000 100000.00000 111111000 19 100000.00000 -100000.00000 100000 5 SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00000 NULL NULL NULL SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00000 NULL NULL NULL SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 100.05000 -100.05000 100.05 1 12345.00000 NULL NULL NULL SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 100.05000 -100.05000 100.05 1 12345.00000 NULL NULL NULL SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2 DESC; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2 DE c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2 DE c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00000 NULL NULL NULL 100000.00000 100000.00000 111111000 19 100000.00000 -100000.00000 100000 5 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 100000.00000 -100000.00000 100000 5 100000.00000 100000.00000 111111000 19 12345.00000 NULL NULL NULL SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t1; total_rows min_value max(c2) 7 -100000.00000 100000.00000 SELECT * FROM t1 WHERE c3 = '-0.0'; c1 c2 c3 c4 0.00000 0.00000 0 7 SELECT c1,c2 FROM t1; c1 c2 0.00000 -0.10000 0.00000 0.00000 1.00000 1.00000 100.05000 -100.05000 100000.00000 -100000.00000 100000.00000 100000.00000 12345.00000 NULL SELECT c1,c2 FROM t1 ORDER BY c1,c2 DESC; c1 c2 0.00000 0.00000 0.00000 -0.10000 1.00000 1.00000 100.05000 -100.05000 12345.00000 NULL 100000.00000 100000.00000 100000.00000 -100000.00000 SELECT * FROM t1 ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 100000.00000 -100000.00000 100000 5 100000.00000 100000.00000 111111000 19 SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 100000.00000 100000.00000 111111000 19 SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 100000.00000 100000.00000 111111000 19 SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 100000.00000 -100000.00000 100000 5 SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 100000.00000 -100000.00000 100000 5 SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 100000.00000 -100000.00000 100000 5 SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 100000.00000 -100000.00000 100000 5 SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 100.05000 -100.05000 100.05 1 SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2; c1 c2 c3 c4 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2 LIMI c1 c2 c3 c4 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2; c1 c2 c3 c4 12345.00000 NULL NULL NULL SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 12345.00000 NULL NULL NULL SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 100000.00000 -100000.00000 100000 5 100000.00000 100000.00000 111111000 19 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 100000.00000 100000.00000 111111000 19 100000.00000 -100000.00000 100000 5 SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 100000.00000 100000.00000 111111000 19 SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 100000.00000 100000.00000 111111000 19 SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 100000.00000 -100000.00000 100000 5 SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 100000.00000 -100000.00000 100000 5 SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 100000.00000 -100000.00000 100000 5 SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 100000.00000 -100000.00000 100000 5 SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 100.05000 -100.05000 100.05 1 SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2 DESC c1 c2 c3 c4 SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2 DESC c1 c2 c3 c4 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2 DESC; c1 c2 c3 c4 12345.00000 NULL NULL NULL SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 12345.00000 NULL NULL NULL SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 100000.00000 100000.00000 111111000 19 100000.00000 -100000.00000 100000 5 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT c1 FROM t1 GROUP BY c1; c1 0.00000 1.00000 100.05000 100000.00000 12345.00000 SELECT DISTINCT c1 FROM t1; c1 0.00000 1.00000 100.05000 100000.00000 12345.00000 SELECT c1,MIN(c2) FROM t1 GROUP BY c1; c1 MIN(c2) 0.00000 -0.10000 1.00000 1.00000 100.05000 -100.05000 100000.00000 -100000.00000 12345.00000 NULL CREATE TABLE t5(c1 FLOAT(23,0) UNSIGNED NOT NULL, c2 FLOAT(23,0) SIGNED NULL, c3 FLOAT, c4 INT INSERT INTO t5 VALUES('1e+22','-1e+22','1e+22',1),('1e-22','-1e-22','1e-22',2); SELECT * FROM t5; SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR ALTER TABLE t5 CHANGE c1 c1 FLOAT(24,0) SIGNED NOT NULL, CHANGE c2 c2 FLOAT(24,0) UNSIGNED NULL Warnings: Warning 1264 Out of range value for column 'c2' at row 1 INSERT INTO t5 VALUES('-1e+23','1e+23','1e+23',3),('-1e-23','1e-23','1e-23',4); SELECT * FROM t5; SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR ALTER TABLE t5 CHANGE c1 c1 FLOAT(53,0) UNSIGNED, CHANGE c2 c2 FLOAT(53,0) SIGNED; Warnings: Warning 1264 Out of range value for column 'c1' at row 3 INSERT IGNORE INTO t5 VALUES('1e+52','-1e+52','1e+52',5),('1e-52','-1e-52','1e-52',6 Warnings: Warning 1264 Out of range value for column 'c1' at row 1 Warning 1264 Out of range value for column 'c2' at row 1 Warning 1264 Out of range value for column 'c3' at row 1 INSERT IGNORE INTO t5 VALUES('1e+53','-1e+53','1e+53',7),('1e-53','-1e-53','1e-53',8 Warnings: Warning 1264 Out of range value for column 'c1' at row 1 Warning 1264 Out of range value for column 'c2' at row 1 Warning 1264 Out of range value for column 'c3' at row 1 SELECT * FROM t5; c1 c2 c3 c4 # # 0 6 # # 0 8 # # 1e-22 2 # # 1e-23 4 # # 1e22 1 # # 1e23 3 # # 3.40282e38 5 # # 3.40282e38 7 DROP TABLE t5; 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; ROUND(c1,c2) TRUNCATE(c1,c2) 1.133000 1.132000 DROP TABLE t5; CREATE TABLE t7(c1 FLOAT(5,10)); ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column 'c1') DROP TABLE t1,t2; CREATE TABLE t1(c1 DOUBLE(10,5) UNSIGNED NOT NULL, c2 DOUBLE(10,5) SIGNED NULL, c3 DOUBLE, c4 CREATE TABLE t2(c1 DOUBLE(10,0) SIGNED NOT NULL, c2 DOUBLE(10,0) UNSIGNED NULL, c3 DOUBLE, c4 INSERT INTO t1 VALUES('00100.05000','-00100.05000','00100.05000',1); INSERT INTO t2 VALUES('-1000000000','1000000000','1000000000',2); INSERT INTO t1(c1) VALUES('12345.000009'); INSERT INTO t1 VALUES('99999.99999','-99999.99999','99999.99999',3); INSERT IGNORE INTO t2 VALUES('-9999999999','-9999999999','9999999999',4); Warnings: Warning 1264 Out of range value for column 'c2' at row 1 SELECT * FROM t1; c1 c2 c3 c4 100.05000 -100.05000 100.05 1 12345.00001 NULL NULL NULL 99999.99999 -99999.99999 99999.99999 3 SET STATEMENT sql_mode = '' FOR INSERT INTO t1 VALUES('100000.000002','-100000.000002','100000.000002',5); ERROR 23000: Duplicate entry '99999.99999--99999.99999' for key 'idx' DELETE FROM t1 WHERE c1='99999.99999' AND c2='-99999.99999'; INSERT IGNORE INTO t1 VALUES('100000.000002','-100000.000002','100000.000002',5); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 Warning 1264 Out of range value for column 'c2' at row 1 INSERT IGNORE INTO t2 VALUES('10000000000','-10000000000','10000000000',6); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 Warning 1264 Out of range value for column 'c2' at row 1 insert into t1 values ("0.0","0.0","0.0",7),("01.0","01.0","01.0",10); insert ignore into t1 values ("-.1","-.1","-.1",13); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 insert ignore into t1 values ("+111111111.11","+111111111.11","+111111111.11",19); Warnings: Warning 1264 Out of range value for column 'c1' at row 1 Warning 1264 Out of range value for column 'c2' at row 1 insert ignore into t2 values ("0.0","0.0","0.0",7),("-0.0","-0.0","-0.0",8),("+0.0"," Warnings: Warning 1264 Out of range value for column 'c2' at row 6 insert ignore into t2 values ("-.1","-.1","-.1",13),("+.1","+.1","+.1",14),(".1",".1" Warnings: Warning 1264 Out of range value for column 'c2' at row 1 insert ignore into t2 values ("00000000000001","00000000000001","00000000000001",16), Warnings: Warning 1264 Out of range value for column 'c2' at row 3 insert ignore into t2 values ("+111111111.11","+111111111.11","+111111111.11",19),("1 Warnings: Warning 1264 Out of range value for column 'c2' at row 3 insert ignore into t2 values ("-111111111.11","-111111111.11","-111111111.11",22),("+ Warnings: Warning 1264 Out of range value for column 'c2' at row 1 insert ignore into t2 values ("1e+1000","1e+1000","1e+1000",25),("1e-1000","1e-1000", Warnings: Warning 1264 Out of range value for column 'c1' at row 1 Warning 1264 Out of range value for column 'c1' at row 1 Warning 1264 Out of range value for column 'c2' at row 1 Warning 1264 Out of range value for column 'c2' at row 1 Warning 1264 Out of range value for column 'c3' at row 1 Warning 1264 Out of range value for column 'c1' at row 3 Warning 1264 Out of range value for column 'c1' at row 3 Warning 1264 Out of range value for column 'c2' at row 3 Warning 1264 Out of range value for column 'c2' at row 3 Warning 1264 Out of range value for column 'c3' at row 3 insert ignore into t2 values ("1e+4294967296","1e+4294967296","1e+4294967296",28),("1 Warnings: Warning 1264 Out of range value for column 'c1' at row 1 Warning 1264 Out of range value for column 'c1' at row 1 Warning 1264 Out of range value for column 'c2' at row 1 Warning 1264 Out of range value for column 'c2' at row 1 Warning 1264 Out of range value for column 'c3' at row 1 insert ignore into t2 values ("1e+18446744073709551615","1e+18446744073709551615","1e Warnings: Warning 1264 Out of range value for column 'c1' at row 1 Warning 1264 Out of range value for column 'c1' at row 1 Warning 1264 Out of range value for column 'c2' at row 1 Warning 1264 Out of range value for column 'c2' at row 1 Warning 1264 Out of range value for column 'c3' at row 1 Warning 1264 Out of range value for column 'c1' at row 2 Warning 1264 Out of range value for column 'c1' at row 2 Warning 1264 Out of range value for column 'c2' at row 2 Warning 1264 Out of range value for column 'c2' at row 2 Warning 1264 Out of range value for column 'c3' at row 2 SELECT * FROM t1; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00001 NULL NULL NULL 99999.99999 -99999.99999 100000.000002 5 99999.99999 99999.99999 111111111.11 19 SELECT * FROM t2; c1 c2 c3 c4 -1 0 -1 12 -1 0 -1 18 -1000000000 1000000000 1000000000 2 -11111111 0 -11111111.11 21 -111111111 0 -111111111.11 22 -9999999999 0 -1.7976931348623157e308 27 -9999999999 0 9999999999 4 0 0 -0.1 13 0 0 0 26 0 0 0 29 0 0 0 32 0 0 0 33 0 0 0 7 0 0 0 8 0 0 0 9 0 0 0.1 14 0 0 0.1 15 1 1 1 10 1 1 1 11 1 1 1 16 1 1 1 17 111111111 111111111 111111111.11 19 111111111 111111111 111111111.11 20 1111111111 1111111111 1111111111.11 23 1111111111 1111111111 1111111111.11 24 9999999999 0 10000000000 6 9999999999 9999999999 1.7976931348623157e308 25 9999999999 9999999999 1.7976931348623157e308 28 9999999999 9999999999 1.7976931348623157e308 30 9999999999 9999999999 1.7976931348623157e308 31 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) as max_value, sum(c1) as sum, av total_rows min_value max_value sum avg 7 0.00000 99999.99999 212446.04999 30349.435712857 SELECT count(*) as total_rows, min(c2) as min_value, max(c2) as max_value, sum(c2) as sum, av total_rows min_value max_value sum avg 7 -99999.99999 99999.99999 -99.15000 -16.525000000 SELECT count(*) as total_rows, min(c3) as min_value, max(c3) as max_value, sum(c3) as sum, av total_rows min_value max_value sum avg 7 -0.1 111111111.11 111211212.060002 18535202.010000333 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) as max_value, sum(c1) as sum, av total_rows min_value max_value sum avg 30 -9999999999 9999999999 31322222221 1044074074.0333 SELECT count(*) as total_rows, min(c2) as min_value, max(c2) as max_value, sum(c2) as sum, av total_rows min_value max_value sum avg 30 0 9999999999 43444444444 1448148148.1333 SELECT count(*) as total_rows, min(c3) as min_value, max(c3) as max_value, sum(c3) as sum, av total_rows min_value max_value sum avg 30 -1.7976931348623157e308 1.7976931348623157e308 inf inf SELECT * FROM t1; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00001 NULL NULL NULL 99999.99999 -99999.99999 100000.000002 5 99999.99999 99999.99999 111111111.11 19 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t1; total_rows min_value max(c1) 7 0.00000 99999.99999 SELECT * FROM t1 WHERE c3 = '0.0'; c1 c2 c3 c4 0.00000 0.00000 0 7 SELECT c1,c2 FROM t1; c1 c2 0.00000 -0.10000 0.00000 0.00000 1.00000 1.00000 100.05000 -100.05000 12345.00001 NULL 99999.99999 -99999.99999 99999.99999 99999.99999 SELECT c1,c2 FROM t1 ORDER BY c1,c2 DESC; c1 c2 0.00000 0.00000 0.00000 -0.10000 1.00000 1.00000 100.05000 -100.05000 12345.00001 NULL 99999.99999 99999.99999 99999.99999 -99999.99999 SELECT * FROM t1 ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c1 = '0.0' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2; c1 c2 c3 c4 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00001 NULL NULL NULL 99999.99999 -99999.99999 100000.000002 5 99999.99999 99999.99999 111111111.11 19 SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2; c1 c2 c3 c4 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00001 NULL NULL NULL 99999.99999 -99999.99999 100000.000002 5 99999.99999 99999.99999 111111111.11 19 SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00001 NULL NULL NULL 99999.99999 -99999.99999 100000.000002 5 99999.99999 99999.99999 111111111.11 19 SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00001 NULL NULL NULL 99999.99999 -99999.99999 100000.000002 5 99999.99999 99999.99999 111111111.11 19 SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 99999.99999 -99999.99999 100000.000002 5 99999.99999 99999.99999 111111111.11 19 SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2; c1 c2 c3 c4 1.00000 1.00000 1 10 SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 1.00000 1.00000 1 10 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00001 NULL NULL NULL 99999.99999 -99999.99999 100000.000002 5 99999.99999 99999.99999 111111111.11 19 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2 DESC; c1 c2 c3 c4 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00001 NULL NULL NULL 99999.99999 99999.99999 111111111.11 19 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c1 <> '0.0' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2 DESC; c1 c2 c3 c4 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00001 NULL NULL NULL 99999.99999 99999.99999 111111111.11 19 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c1 > '0.0' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00001 NULL NULL NULL 99999.99999 99999.99999 111111111.11 19 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c1 >= '0.0' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2 DESC; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 < '0.0' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c1 <= '0.0' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c1 <=> '0.0' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00001 NULL NULL NULL 99999.99999 99999.99999 111111111.11 19 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c1 BETWEEN '0.0' AND '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 99999.99999 99999.99999 111111111.11 19 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c1 IN ('0.0','99999.99999') ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2 DESC; c1 c2 c3 c4 1.00000 1.00000 1 10 SELECT * FROM t1 WHERE c1 >= '0.0' AND c1 < '99999.99999' AND c2 = '1.0' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 1.00000 1.00000 1 10 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00001 NULL NULL NULL 99999.99999 99999.99999 111111111.11 19 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00001 NULL NULL NULL 99999.99999 -99999.99999 100000.000002 5 99999.99999 99999.99999 111111111.11 19 SELECT count(*) as total_rows, min(c1) as min_value, max(c1) FROM t1; total_rows min_value max(c1) 7 0.00000 99999.99999 SELECT * FROM t1 WHERE c3 = '-0.0'; c1 c2 c3 c4 0.00000 0.00000 0 7 SELECT c1,c2 FROM t1; c1 c2 0.00000 -0.10000 0.00000 0.00000 1.00000 1.00000 100.05000 -100.05000 12345.00001 NULL 99999.99999 -99999.99999 99999.99999 99999.99999 SELECT c1,c2 FROM t1 ORDER BY c1,c2 DESC; c1 c2 0.00000 0.00000 0.00000 -0.10000 1.00000 1.00000 100.05000 -100.05000 12345.00001 NULL 99999.99999 99999.99999 99999.99999 -99999.99999 SELECT * FROM t1 ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 99999.99999 -99999.99999 100000.000002 5 99999.99999 99999.99999 111111111.11 19 SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 99999.99999 -99999.99999 100000.000002 5 99999.99999 99999.99999 111111111.11 19 SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 99999.99999 99999.99999 111111111.11 19 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c1 = '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 99999.99999 99999.99999 111111111.11 19 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00001 NULL NULL NULL SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 99999.99999 -99999.99999 100000.000002 5 99999.99999 99999.99999 111111111.11 19 SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 99999.99999 -99999.99999 100000.000002 5 99999.99999 99999.99999 111111111.11 19 SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00001 NULL NULL NULL SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00001 NULL NULL NULL 99999.99999 -99999.99999 100000.000002 5 99999.99999 99999.99999 111111111.11 19 SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 99999.99999 -99999.99999 100000.000002 5 99999.99999 99999.99999 111111111.11 19 SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 99999.99999 -99999.99999 100000.000002 5 99999.99999 99999.99999 111111111.11 19 SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 100.05000 -100.05000 100.05 1 12345.00001 NULL NULL NULL 99999.99999 -99999.99999 100000.000002 5 99999.99999 99999.99999 111111111.11 19 SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 100.05000 -100.05000 100.05 1 12345.00001 NULL NULL NULL SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2; c1 c2 c3 c4 100.05000 -100.05000 100.05 1 99999.99999 -99999.99999 100000.000002 5 99999.99999 99999.99999 111111111.11 19 SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 100.05000 -100.05000 100.05 1 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2 LI c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00001 NULL NULL NULL 99999.99999 -99999.99999 100000.000002 5 99999.99999 99999.99999 111111111.11 19 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00001 NULL NULL NULL SELECT * FROM t1 WHERE c1 <> '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 > '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 99999.99999 99999.99999 111111111.11 19 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c1 >= '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 99999.99999 99999.99999 111111111.11 19 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00001 NULL NULL NULL SELECT * FROM t1 WHERE c1 < '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00001 NULL NULL NULL 99999.99999 99999.99999 111111111.11 19 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c1 <= '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 99999.99999 99999.99999 111111111.11 19 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c1 <=> '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 99999.99999 99999.99999 111111111.11 19 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 100.05000 -100.05000 100.05 1 12345.00001 NULL NULL NULL 99999.99999 99999.99999 111111111.11 19 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c1 BETWEEN '100.05' AND '99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 100.05000 -100.05000 100.05 1 12345.00001 NULL NULL NULL SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2 DESC; c1 c2 c3 c4 100.05000 -100.05000 100.05 1 99999.99999 99999.99999 111111111.11 19 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c1 IN ('100.05','99999.99999') ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 100.05000 -100.05000 100.05 1 99999.99999 99999.99999 111111111.11 19 SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2 DE c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2 DE c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 IS NULL ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00001 NULL NULL NULL 99999.99999 99999.99999 111111111.11 19 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c1 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 12345.00001 NULL NULL NULL 99999.99999 -99999.99999 100000.000002 5 99999.99999 99999.99999 111111111.11 19 SELECT count(*) as total_rows, min(c2) as min_value, max(c2) FROM t1; total_rows min_value max(c2) 7 -99999.99999 99999.99999 SELECT * FROM t1 WHERE c3 = '-0.0'; c1 c2 c3 c4 0.00000 0.00000 0 7 SELECT c1,c2 FROM t1; c1 c2 0.00000 -0.10000 0.00000 0.00000 1.00000 1.00000 100.05000 -100.05000 12345.00001 NULL 99999.99999 -99999.99999 99999.99999 99999.99999 SELECT c1,c2 FROM t1 ORDER BY c1,c2 DESC; c1 c2 0.00000 0.00000 0.00000 -0.10000 1.00000 1.00000 100.05000 -100.05000 12345.00001 NULL 99999.99999 99999.99999 99999.99999 -99999.99999 SELECT * FROM t1 ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c2 = '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 99999.99999 99999.99999 111111111.11 19 SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 99999.99999 99999.99999 111111111.11 19 SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 99999.99999 -99999.99999 100000.000002 5 99999.99999 99999.99999 111111111.11 19 SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 100.05000 -100.05000 100.05 1 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2; c1 c2 c3 c4 0.00000 0.00000 0 7 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2; c1 c2 c3 c4 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2 LIMI c1 c2 c3 c4 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2; c1 c2 c3 c4 12345.00001 NULL NULL NULL SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 12345.00001 NULL NULL NULL SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 99999.99999 -99999.99999 100000.000002 5 99999.99999 99999.99999 111111111.11 19 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2 LIMIT 2; c1 c2 c3 c4 0.00000 -0.10000 -0.1 13 0.00000 0.00000 0 7 SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 99999.99999 99999.99999 111111111.11 19 SELECT * FROM t1 WHERE c2 <> '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 99999.99999 99999.99999 111111111.11 19 SELECT * FROM t1 WHERE c2 > '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 99999.99999 99999.99999 111111111.11 19 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c2 >= '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 SELECT * FROM t1 WHERE c2 < '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c2 <= '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2 DESC; c1 c2 c3 c4 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c2 <=> '-99999.99999' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 100.05000 -100.05000 100.05 1 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c2 BETWEEN '-99999.99999' AND '0.0' ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c2 IN ('-99999.99999','0.0') ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2 DESC c1 c2 c3 c4 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c2 >= '-99999.99999' AND c2 < '0.0' AND c1 = '99999.99999' ORDER BY c1,c2 DESC c1 c2 c3 c4 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2 DESC; c1 c2 c3 c4 12345.00001 NULL NULL NULL SELECT * FROM t1 WHERE c2 IS NULL ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 12345.00001 NULL NULL NULL SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 1.00000 1.00000 1 10 100.05000 -100.05000 100.05 1 99999.99999 99999.99999 111111111.11 19 99999.99999 -99999.99999 100000.000002 5 SELECT * FROM t1 WHERE c2 IS NOT NULL ORDER BY c1,c2 DESC LIMIT 2; c1 c2 c3 c4 0.00000 0.00000 0 7 0.00000 -0.10000 -0.1 13 SELECT c1 FROM t1 GROUP BY c1; c1 0.00000 1.00000 100.05000 12345.00001 99999.99999 SELECT DISTINCT c1 FROM t1; c1 0.00000 1.00000 100.05000 12345.00001 99999.99999 SELECT c1,MIN(c2) FROM t1 GROUP BY c1; c1 MIN(c2) 0.00000 -0.10000 1.00000 1.00000 100.05000 -100.05000 12345.00001 NULL 99999.99999 -99999.99999 CREATE TABLE t5(c1 DOUBLE(23,0) UNSIGNED NOT NULL, c2 DOUBLE(23,0) SIGNED NULL, c3 DOUBLE, c4 INSERT INTO t5 VALUES('1e+22','-1e+22','1e+22',1),('1e-22','-1e-22','1e-22',2); SELECT * FROM t5; c1 c2 c3 c4 0 0 1e-22 2 10000000000000000000000 -10000000000000000000000 1e22 1 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR ALTER TABLE t5 CHANGE c1 c1 DOUBLE(24,0) SIGNED NOT NULL, CHANGE c2 c2 DOUBLE(24,0) UNSIGNED NU Warnings: Warning 1264 Out of range value for column 'c2' at row 1 INSERT INTO t5 VALUES('-1e+23','1e+23','1e+23',3),('-1e-23','1e-23','1e-23',4); SELECT * FROM t5; SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR ALTER TABLE t5 CHANGE c1 c1 DOUBLE(53,0) UNSIGNED, CHANGE c2 c2 DOUBLE(53,0) SIGNED; Warnings: Warning 1264 Out of range value for column 'c1' at row 3 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); SELECT * FROM t5; DROP TABLE t5; 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; ROUND(c1,c2) TRUNCATE(c1,c2) 1.132000 1.132000 DROP TABLE t5; CREATE TABLE t7(c1 DOUBLE(5,10)); ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column 'c1') DROP TABLE t1,t2;
¤ Dauer der Verarbeitung: 0.77 Sekunden (vorverarbeitet am 2026-10-08) ¤*© Formatika GbR, Deutschland |
|
||||||||||||||
2026-10-10