1
drop table if exists t1,t2;
2
SELECT 10,10.0,10.,.1e+2,100.0e-1;
3
10 10.0 10. .1e+2 100.0e-1
5
SELECT 6e-05, -6e-05, --6e-05, -6e-05+1.000000;
6
6e-05 -6e-05 --6e-05 -6e-05+1.000000
7
6e-05 -6e-05 6e-05 0.99994
8
SELECT 1e1,1.e1,1.0e1,1e+1,1.e+1,1.0e+1,1e-1,1.e-1,1.0e-1;
9
1e1 1.e1 1.0e1 1e+1 1.e+1 1.0e+1 1e-1 1.e-1 1.0e-1
10
10 10 10 10 10 10 0.1 0.1 0.1
11
SELECT 0.001e+1,0.001e-1, -0.001e+01,-0.001e-01;
12
0.001e+1 0.001e-1 -0.001e+01 -0.001e-01
13
0.01 0.0001 -0.01 -0.0001
14
SELECT 123.23E+02,-123.23E-02,"123.23E+02"+0.0,"-123.23E-02"+0.0;
15
123.23E+02 -123.23E-02 "123.23E+02"+0.0 "-123.23E-02"+0.0
16
12323 -1.2323 12323 -1.2323
17
SELECT 2147483647E+02,21474836.47E+06;
18
2147483647E+02 21474836.47E+06
19
214748364700 21474836470000
20
create table t1 (f1 float(24),f2 float(52));
21
show full columns from t1;
22
Field Type Collation Null Key Default Extra Privileges Comment
23
f1 float NULL YES NULL #
24
f2 double NULL YES NULL #
25
insert into t1 values(10,10),(1e+5,1e+5),(1234567890,1234567890),(1e+10,1e+10),(1e+15,1e+15),(1e+20,1e+20),(1e+50,1e+50),(1e+150,1e+150);
27
Warning 1264 Out of range value for column 'f1' at row 7
28
Warning 1264 Out of range value for column 'f1' at row 8
29
insert into t1 values(-10,-10),(1e-5,1e-5),(1e-10,1e-10),(1e-15,1e-15),(1e-20,1e-20),(1e-50,1e-50),(1e-150,1e-150);
34
1.23457e+09 1234567890
48
create table t1 (datum double);
49
insert into t1 values (0.5),(1.0),(1.5),(2.0),(2.5);
57
select * from t1 where datum < 1.5;
61
select * from t1 where datum > 1.5;
65
select * from t1 where datum = 1.5;
69
create table t1 (a decimal(7,3) not null, key (a));
70
insert into t1 values ("0"),("-0.00"),("-0.01"),("-0.002"),("1");
71
select a from t1 order by a;
78
select min(a) from t1;
82
create table t1 (c1 double, c2 varchar(20));
83
insert t1 values (121,"16");
84
select c1 + c1 * (c2 / 100) as col from t1;
87
create table t2 select c1 + c1 * (c2 / 100) as col1, round(c1, 5) as col2, round(c1, 35) as col3, sqrt(c1*1e-15) col4 from t1;
90
140.36 121.00000 121 3.47850542618522e-07
93
t2 CREATE TABLE `t2` (
94
`col1` double DEFAULT NULL,
95
`col2` double(53,5) DEFAULT NULL,
96
`col3` double DEFAULT NULL,
97
`col4` double DEFAULT NULL
98
) ENGINE=PBXT DEFAULT CHARSET=latin1
100
create table t1 (a float);
101
insert into t1 values (1);
102
select max(a),min(a),avg(a) from t1;
106
create table t1 (f float, f2 float(24), f3 float(6,2), d double, d2 float(53), d3 double(10,3), de decimal, de2 decimal(6), de3 decimal(5,2), n numeric, n2 numeric(8), n3 numeric(7,6));
107
show full columns from t1;
108
Field Type Collation Null Key Default Extra Privileges Comment
109
f float NULL YES NULL #
110
f2 float NULL YES NULL #
111
f3 float(6,2) NULL YES NULL #
112
d double NULL YES NULL #
113
d2 double NULL YES NULL #
114
d3 double(10,3) NULL YES NULL #
115
de decimal(10,0) NULL YES NULL #
116
de2 decimal(6,0) NULL YES NULL #
117
de3 decimal(5,2) NULL YES NULL #
118
n decimal(10,0) NULL YES NULL #
119
n2 decimal(8,0) NULL YES NULL #
120
n3 decimal(7,6) NULL YES NULL #
122
create table t1 (a decimal(7,3) not null, key (a));
123
insert into t1 values ("0"),("-0.00"),("-0.01"),("-0.002"),("1");
124
select a from t1 order by a;
131
select min(a) from t1;
135
create table t1 (a float(200,100), b double(200,100));
136
ERROR 42000: Too big scale 100 specified for column 'a'. Maximum is 30.
137
create table t1 (c20 char);
138
insert into t1 values (5000.0);
140
Warning 1265 Data truncated for column 'c20' at row 1
141
insert into t1 values (0.5e4);
143
Warning 1265 Data truncated for column 'c20' at row 1
145
create table t1 (f float(54));
146
ERROR 42000: Incorrect column specifier for column 'f'
147
drop table if exists t1;
148
create table t1 (d1 double, d2 double unsigned);
149
insert into t1 set d1 = -1.0;
150
update t1 set d2 = d1;
152
Warning 1264 Out of range value for column 'd2' at row 1
157
create table t1 (f float(4,3));
158
insert into t1 values (-11.0),(-11),("-11"),(11.0),(11),("11");
160
Warning 1264 Out of range value for column 'f' at row 1
161
Warning 1264 Out of range value for column 'f' at row 2
162
Warning 1264 Out of range value for column 'f' at row 3
163
Warning 1264 Out of range value for column 'f' at row 4
164
Warning 1264 Out of range value for column 'f' at row 5
165
Warning 1264 Out of range value for column 'f' at row 6
174
drop table if exists t1;
175
create table t1 (f double(4,3));
176
insert into t1 values (-11.0),(-11),("-11"),(11.0),(11),("11");
178
Warning 1264 Out of range value for column 'f' at row 1
179
Warning 1264 Out of range value for column 'f' at row 2
180
Warning 1264 Out of range value for column 'f' at row 3
181
Warning 1264 Out of range value for column 'f' at row 4
182
Warning 1264 Out of range value for column 'f' at row 5
183
Warning 1264 Out of range value for column 'f' at row 6
192
drop table if exists t1;
193
create table t1 (c char(20));
194
insert into t1 values (5e-28);
199
create table t1 (c char(6));
200
insert into t1 values (2e5),(2e6),(2e-4),(2e-5);
209
reckey int unsigned NOT NULL,
210
recdesc varchar(50) NOT NULL,
212
) ENGINE=PBXT DEFAULT CHARSET=latin1;
213
INSERT INTO t1 VALUES (108, 'Has 108 as key');
214
INSERT INTO t1 VALUES (109, 'Has 109 as key');
215
select * from t1 where reckey=108;
218
select * from t1 where reckey=1.08E2;
221
select * from t1 where reckey=109;
224
select * from t1 where reckey=1.09E2;
228
create table t1 (d double(10,1));
229
create table t2 (d double(10,9));
230
insert into t1 values ("100000000.0");
231
insert into t2 values ("1.23456780");
232
create table t3 select * from t2 union select * from t1;
237
show create table t3;
239
t3 CREATE TABLE `t3` (
240
`d` double(22,9) DEFAULT NULL
241
) ENGINE=PBXT DEFAULT CHARSET=latin1
242
drop table t1, t2, t3;
243
create table t1 select 105213674794682365.00 + 0.0 x;
247
Field Type Null Key Default Extra
248
x decimal(21,2) NO 0.00
250
create table t1 select 0.0 x;
252
Field Type Null Key Default Extra
253
x decimal(2,1) NO 0.0
254
create table t2 select 105213674794682365.00 y;
256
Field Type Null Key Default Extra
257
y decimal(20,2) NO 0.00
258
create table t3 select x+y a from t1,t2;
262
Field Type Null Key Default Extra
263
a decimal(21,2) NO 0.00
265
create table t1 (s1 float(0,2));
266
ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column 's1').
267
create table t1 (s1 float(1,2));
268
ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column 's1').