mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 02:46:29 +01:00
d25b10fede
User visible change: Removing the length specified by user from error messages: ER_TOO_BIG_SCALE and ER_TOO_BIG_PRECISION as discussed with Sergei.
2855 lines
98 KiB
Text
2855 lines
98 KiB
Text
DROP TABLE IF EXISTS t1,t2;
|
|
CREATE TABLE t1(c1 DECIMAL(10,5) UNSIGNED NOT NULL, c2 DECIMAL(10,5) SIGNED NULL, c3 DECIMAL, c4 INT, UNIQUE INDEX idx(c1,c2));
|
|
CREATE TABLE t2(c1 DECIMAL(10,0) SIGNED NOT NULL, c2 DECIMAL(10) UNSIGNED NULL, c3 DECIMAL, c4 INT);
|
|
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","+0.0","+0.0",9),("01.0","01.0","01.0",10),("+01.0","+01.0","+01.0",11),("-01.0","-01.0","-01.0",12);
|
|
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",".1",15);
|
|
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),("+0000000000001","+0000000000001","+0000000000001",17),("-0000000000001","-0000000000001","-0000000000001",18);
|
|
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),("111111111.11","111111111.11","111111111.11",20),("-11111111.11","-11111111.11","-11111111.11",21);
|
|
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),("+1111111111.11","+1111111111.11","+1111111111.11",23),("1111111111.11","1111111111.11","1111111111.11",24);
|
|
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","1e-1000",26),("-1e+1000","-1e+1000","-1e+1000",27);
|
|
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),("1e-4294967296","1e-4294967296","1e-4294967296",29);
|
|
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+18446744073709551615",30),("1e+18446744073709551616","1e+18446744073709551616","1e+18446744073709551616",31),("1e-9223372036854775807","1e-9223372036854775807","1e-9223372036854775807",32),("1e-9223372036854775809","1e-9223372036854775809","1e-9223372036854775809",33);
|
|
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, avg(c1) as avg FROM t1;
|
|
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, avg(c2) as avg FROM t1;
|
|
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, avg(c3) as avg FROM t1;
|
|
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, avg(c1) as avg FROM t2;
|
|
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, avg(c2) as avg FROM t2;
|
|
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, avg(c3) as avg FROM t2;
|
|
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 LIMIT 2;
|
|
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 DESC;
|
|
c1 c2 c3 c4
|
|
SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
|
|
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 LIMIT 2;
|
|
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 LIMIT 2;
|
|
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, c4 INT, INDEX idx(c1,c2));
|
|
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','1.2e-65',4);
|
|
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 -10000000000000000000000000000000000000000000000000000000000000000 9999999999 1
|
|
99999999999999999999999999999999999999999999999999999999999999999 -99999999999999999999999999999999999999999999999999999999999999999 9999999999 3
|
|
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 /* requires 9 bytes */;
|
|
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 /* requires 10 bytes */;
|
|
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 /* requires 11 bytes */;
|
|
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 /* requires 12 bytes */;
|
|
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, UNIQUE INDEX idx(c1,c2));
|
|
CREATE TABLE t2(c1 FLOAT(10,0) SIGNED NOT NULL, c2 FLOAT(10,0) UNSIGNED NULL, c3 FLOAT, c4 INT);
|
|
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","+0.0","+0.0",9),("01.0","01.0","01.0",10),("+01.0","+01.0","+01.0",11),("-01.0","-01.0","-01.0",12);
|
|
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",".1",15);
|
|
Warnings:
|
|
Warning 1264 Out of range value for column 'c2' at row 1
|
|
insert ignore into t2 values ("00000000000001","00000000000001","00000000000001",16),("+0000000000001","+0000000000001","+0000000000001",17),("-0000000000001","-0000000000001","-0000000000001",18);
|
|
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),("111111111.11","111111111.11","111111111.11",20),("-11111111.11","-11111111.11","-11111111.11",21);
|
|
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),("+1111111111.11","+1111111111.11","+1111111111.11",23),("1111111111.11","1111111111.11","1111111111.11",24);
|
|
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","1e-1000",26),("-1e+1000","-1e+1000","-1e+1000",27);
|
|
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),("1e-4294967296","1e-4294967296","1e-4294967296",29);
|
|
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+18446744073709551615",30),("1e+18446744073709551616","1e+18446744073709551616","1e+18446744073709551616",31),("1e-9223372036854775807","1e-9223372036854775807","1e-9223372036854775807",32),("1e-9223372036854775809","1e-9223372036854775809","1e-9223372036854775809",33);
|
|
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 8
|
|
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 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, avg(c1) as avg FROM t1;
|
|
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, avg(c2) as avg FROM t1;
|
|
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, avg(c3) as avg FROM t1;
|
|
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, avg(c1) as avg FROM t2;
|
|
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, avg(c2) as avg FROM t2;
|
|
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, avg(c3) as avg FROM t2;
|
|
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 LIMIT 2;
|
|
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 DESC;
|
|
c1 c2 c3 c4
|
|
SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
|
|
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 LIMIT 2;
|
|
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 LIMIT 2;
|
|
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, INDEX idx(c1,c2));
|
|
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 INT, UNIQUE INDEX idx(c1,c2));
|
|
CREATE TABLE t2(c1 DOUBLE(10,0) SIGNED NOT NULL, c2 DOUBLE(10,0) UNSIGNED NULL, c3 DOUBLE, c4 INT);
|
|
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","+0.0","+0.0",9),("01.0","01.0","01.0",10),("+01.0","+01.0","+01.0",11),("-01.0","-01.0","-01.0",12);
|
|
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",".1",15);
|
|
Warnings:
|
|
Warning 1264 Out of range value for column 'c2' at row 1
|
|
insert ignore into t2 values ("00000000000001","00000000000001","00000000000001",16),("+0000000000001","+0000000000001","+0000000000001",17),("-0000000000001","-0000000000001","-0000000000001",18);
|
|
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),("111111111.11","111111111.11","111111111.11",20),("-11111111.11","-11111111.11","-11111111.11",21);
|
|
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),("+1111111111.11","+1111111111.11","+1111111111.11",23),("1111111111.11","1111111111.11","1111111111.11",24);
|
|
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","1e-1000",26),("-1e+1000","-1e+1000","-1e+1000",27);
|
|
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),("1e-4294967296","1e-4294967296","1e-4294967296",29);
|
|
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+18446744073709551615",30),("1e+18446744073709551616","1e+18446744073709551616","1e+18446744073709551616",31),("1e-9223372036854775807","1e-9223372036854775807","1e-9223372036854775807",32),("1e-9223372036854775809","1e-9223372036854775809","1e-9223372036854775809",33);
|
|
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 8
|
|
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 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, avg(c1) as avg FROM t1;
|
|
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, avg(c2) as avg FROM t1;
|
|
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, avg(c3) as avg FROM t1;
|
|
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, avg(c1) as avg FROM t2;
|
|
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, avg(c2) as avg FROM t2;
|
|
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, avg(c3) as avg FROM t2;
|
|
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 LIMIT 2;
|
|
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 DESC;
|
|
c1 c2 c3 c4
|
|
SELECT * FROM t1 WHERE c1 >= '100.05' AND c1 < '99999.99999' AND c2 = '99999.99999' ORDER BY c1,c2 DESC LIMIT 2;
|
|
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 LIMIT 2;
|
|
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 LIMIT 2;
|
|
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 INT, INDEX idx(c1,c2));
|
|
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 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 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;
|