1set default_storage_engine='tokudb'; 2drop table if exists t1,t2; 3SELECT 10,10.0,10.,.1e+2,100.0e-1; 410 10.0 10. .1e+2 100.0e-1 510 10.0 10 10 10 6SELECT 6e-16, -6e-16, --6e-16, -6e-16+1.000000; 76e-16 -6e-16 --6e-16 -6e-16+1.000000 86e-16 -6e-16 6e-16 0.9999999999999994 9SELECT 1e1,1.e1,1.0e1,1e+1,1.e+1,1.0e+1,1e-1,1.e-1,1.0e-1; 101e1 1.e1 1.0e1 1e+1 1.e+1 1.0e+1 1e-1 1.e-1 1.0e-1 1110 10 10 10 10 10 0.1 0.1 0.1 12SELECT 0.001e+1,0.001e-1, -0.001e+01,-0.001e-01; 130.001e+1 0.001e-1 -0.001e+01 -0.001e-01 140.01 0.0001 -0.01 -0.0001 15SELECT 123.23E+02,-123.23E-02,"123.23E+02"+0.0,"-123.23E-02"+0.0; 16123.23E+02 -123.23E-02 "123.23E+02"+0.0 "-123.23E-02"+0.0 1712323 -1.2323 12323 -1.2323 18SELECT 2147483647E+02,21474836.47E+06; 192147483647E+02 21474836.47E+06 20214748364700 21474836470000 21create table t1 (f1 float(24),f2 float(52)); 22show full columns from t1; 23Field Type Collation Null Key Default Extra Privileges Comment 24f1 float NULL YES NULL # 25f2 double NULL YES NULL # 26insert 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); 27Warnings: 28Warning 1264 Out of range value for column 'f1' at row 7 29Warning 1264 Out of range value for column 'f1' at row 8 30insert 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); 31select * from t1; 32f1 f2 3310 10 34100000 100000 351234570000 1234567890 3610000000000 10000000000 371e15 1e15 381e20 1e20 393.40282e38 1e50 403.40282e38 1e150 41-10 -10 420.00001 0.00001 430.0000000001 0.0000000001 440.000000000000001 0.000000000000001 451e-20 1e-20 460 1e-50 470 1e-150 48drop table t1; 49create table t1 (datum double); 50insert into t1 values (0.5),(1.0),(1.5),(2.0),(2.5); 51select * from t1; 52datum 530.5 541 551.5 562 572.5 58select * from t1 where datum < 1.5; 59datum 600.5 611 62select * from t1 where datum > 1.5; 63datum 642 652.5 66select * from t1 where datum = 1.5; 67datum 681.5 69drop table t1; 70create table t1 (a decimal(7,3) not null, key (a)); 71insert into t1 values ("0"),("-0.00"),("-0.01"),("-0.002"),("1"); 72select a from t1 order by a; 73a 74-0.010 75-0.002 760.000 770.000 781.000 79select min(a) from t1; 80min(a) 81-0.010 82drop table t1; 83create table t1 (c1 double, c2 varchar(20)); 84insert t1 values (121,"16"); 85select c1 + c1 * (c2 / 100) as col from t1; 86col 87140.36 88create 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; 89select * from t2; 90col1 col2 col3 col4 91140.36 121.00000 121 0.00000034785054261852176 92show create table t2; 93Table Create Table 94t2 CREATE TABLE `t2` ( 95 `col1` double DEFAULT NULL, 96 `col2` double(22,5) DEFAULT NULL, 97 `col3` double DEFAULT NULL, 98 `col4` double DEFAULT NULL 99) ENGINE=ENGINE DEFAULT CHARSET=latin1 100drop table t1,t2; 101create table t1 (a float); 102insert into t1 values (1); 103select max(a),min(a),avg(a) from t1; 104max(a) min(a) avg(a) 1051 1 1 106drop table t1; 107create 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)); 108show full columns from t1; 109Field Type Collation Null Key Default Extra Privileges Comment 110f float NULL YES NULL # 111f2 float NULL YES NULL # 112f3 float(6,2) NULL YES NULL # 113d double NULL YES NULL # 114d2 double NULL YES NULL # 115d3 double(10,3) NULL YES NULL # 116de decimal(10,0) NULL YES NULL # 117de2 decimal(6,0) NULL YES NULL # 118de3 decimal(5,2) NULL YES NULL # 119n decimal(10,0) NULL YES NULL # 120n2 decimal(8,0) NULL YES NULL # 121n3 decimal(7,6) NULL YES NULL # 122drop table t1; 123create table t1 (a decimal(7,3) not null, key (a)); 124insert into t1 values ("0"),("-0.00"),("-0.01"),("-0.002"),("1"); 125select a from t1 order by a; 126a 127-0.010 128-0.002 1290.000 1300.000 1311.000 132select min(a) from t1; 133min(a) 134-0.010 135drop table t1; 136create table t1 (a float(200,100), b double(200,100)); 137ERROR 42000: Too big scale 100 specified for 'a'. Maximum is 30 138create table t1 (c20 char); 139insert into t1 values (5000.0); 140Warnings: 141Warning 1265 Data truncated for column 'c20' at row 1 142insert into t1 values (0.5e4); 143Warnings: 144Warning 1265 Data truncated for column 'c20' at row 1 145drop table t1; 146create table t1 (f float(54)); 147ERROR 42000: Incorrect column specifier for column 'f' 148drop table if exists t1; 149create table t1 (d1 double, d2 double unsigned); 150insert into t1 set d1 = -1.0; 151update t1 set d2 = d1; 152Warnings: 153Warning 1264 Out of range value for column 'd2' at row 1 154select * from t1; 155d1 d2 156-1 0 157drop table t1; 158create table t1 (f float(4,3)); 159insert into t1 values (-11.0),(-11),("-11"),(11.0),(11),("11"); 160Warnings: 161Warning 1264 Out of range value for column 'f' at row 1 162Warning 1264 Out of range value for column 'f' at row 2 163Warning 1264 Out of range value for column 'f' at row 3 164Warning 1264 Out of range value for column 'f' at row 4 165Warning 1264 Out of range value for column 'f' at row 5 166Warning 1264 Out of range value for column 'f' at row 6 167select * from t1; 168f 169-9.999 170-9.999 171-9.999 1729.999 1739.999 1749.999 175drop table if exists t1; 176create table t1 (f double(4,3)); 177insert into t1 values (-11.0),(-11),("-11"),(11.0),(11),("11"); 178Warnings: 179Warning 1264 Out of range value for column 'f' at row 1 180Warning 1264 Out of range value for column 'f' at row 2 181Warning 1264 Out of range value for column 'f' at row 3 182Warning 1264 Out of range value for column 'f' at row 4 183Warning 1264 Out of range value for column 'f' at row 5 184Warning 1264 Out of range value for column 'f' at row 6 185select * from t1; 186f 187-9.999 188-9.999 189-9.999 1909.999 1919.999 1929.999 193drop table if exists t1; 194create table t1 (c char(20)); 195insert into t1 values (5e-28); 196select * from t1; 197c 1985e-28 199drop table t1; 200create table t1 (c char(6)); 201insert into t1 values (2e5),(2e6),(2e-4),(2e-5); 202select * from t1; 203c 204200000 2052e6 2060.0002 2072e-5 208drop table t1; 209CREATE TABLE t1 ( 210reckey int unsigned NOT NULL, 211recdesc varchar(50) NOT NULL, 212PRIMARY KEY (reckey) 213) ENGINE=MyISAM DEFAULT CHARSET=latin1; 214INSERT INTO t1 VALUES (108, 'Has 108 as key'); 215INSERT INTO t1 VALUES (109, 'Has 109 as key'); 216select * from t1 where reckey=108; 217reckey recdesc 218108 Has 108 as key 219select * from t1 where reckey=1.08E2; 220reckey recdesc 221108 Has 108 as key 222select * from t1 where reckey=109; 223reckey recdesc 224109 Has 109 as key 225select * from t1 where reckey=1.09E2; 226reckey recdesc 227109 Has 109 as key 228drop table t1; 229create table t1 (d double(10,1)); 230create table t2 (d double(10,9)); 231insert into t1 values ("100000000.0"); 232insert into t2 values ("1.23456780"); 233create table t3 select * from t2 union select * from t1; 234select * from t3; 235d 2361.234567800 237100000000.000000000 238show create table t3; 239Table Create Table 240t3 CREATE TABLE `t3` ( 241 `d` double(18,9) DEFAULT NULL 242) ENGINE=ENGINE DEFAULT CHARSET=latin1 243drop table t1, t2, t3; 244create table t1 select 105213674794682365.00 + 0.0 x; 245show warnings; 246Level Code Message 247desc t1; 248Field Type Null Key Default Extra 249x decimal(21,2) NO NULL 250drop table t1; 251create table t1 select 0.0 x; 252desc t1; 253Field Type Null Key Default Extra 254x decimal(2,1) NO NULL 255create table t2 select 105213674794682365.00 y; 256desc t2; 257Field Type Null Key Default Extra 258y decimal(20,2) NO NULL 259create table t3 select x+y a from t1,t2; 260show warnings; 261Level Code Message 262desc t3; 263Field Type Null Key Default Extra 264a decimal(21,2) NO NULL 265drop table t1,t2,t3; 266select 1e-308, 1.00000001e-300, 100000000e-300; 2671e-308 1.00000001e-300 100000000e-300 2681e-308 1.00000001e-300 1e-292 269select 10e307; 27010e307 2711e308 272create table t1(a int, b double(8, 2)); 273insert into t1 values 274(1, 28.50), (1, 121.85), (1, 157.23), (1, 1351.00), (1, -1965.35), (1, 81.75), 275(1, 217.08), (1, 7.94), (4, 96.07), (4, 6404.65), (4, -6500.72), (2, 100.00), 276(5, 5.00), (5, -2104.80), (5, 2033.80), (5, 0.07), (5, 65.93), 277(3, -4986.24), (3, 5.00), (3, 4857.34), (3, 123.74), (3, 0.16), 278(6, -1695.31), (6, 1003.77), (6, 499.72), (6, 191.82); 279explain select sum(b) s from t1 group by a; 280id select_type table type possible_keys key key_len ref rows Extra 2811 SIMPLE t1 ALL NULL NULL NULL NULL 26 Using temporary; Using filesort 282select sum(b) s from t1 group by a; 283s 2840.00 285100.00 2860.00 287-0.00 288-0.00 2890.00 290select sum(b) s from t1 group by a having s <> 0; 291s 292100.00 293select sum(b) s from t1 group by a having s <> 0 order by s; 294s 295100.00 296select sum(b) s from t1 group by a having s <=> 0; 297s 2980.00 2990.00 300-0.00 301-0.00 3020.00 303select sum(b) s from t1 group by a having s <=> 0 order by s; 304s 305-0.00 306-0.00 3070.00 3080.00 3090.00 310alter table t1 add key (a, b); 311explain select sum(b) s from t1 group by a; 312id select_type table type possible_keys key key_len ref rows Extra 3131 SIMPLE t1 index NULL a 14 NULL 26 Using index 314select sum(b) s from t1 group by a; 315s 3160.00 317100.00 3180.00 319-0.00 3200.00 3210.00 322select sum(b) s from t1 group by a having s <> 0; 323s 324100.00 325select sum(b) s from t1 group by a having s <> 0 order by s; 326s 327100.00 328select sum(b) s from t1 group by a having s <=> 0; 329s 3300.00 3310.00 332-0.00 3330.00 3340.00 335select sum(b) s from t1 group by a having s <=> 0 order by s; 336s 337-0.00 3380.00 3390.00 3400.00 3410.00 342drop table t1; 343End of 4.1 tests 344create table t1 (s1 float(0,2)); 345ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column 's1') 346create table t1 (s1 float(1,2)); 347ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column 's1') 348CREATE TABLE t1 ( 349f1 real zerofill, 350f2 double zerofill, 351f3 float zerofill); 352INSERT INTO t1 VALUES ( 0.314152e+1, 0.314152e+1, 0.314152e+1); 353PREPARE stmt1 FROM 'select f1, f2, f3 FROM t1'; 354select f1, f2, f3 FROM t1; 355f1 f2 f3 3560000000000000003.14152 0000000000000003.14152 000003.14152 357select f1, f2, f3 FROM t1; 358f1 f2 f3 3590000000000000003.14152 0000000000000003.14152 000003.14152 360EXECUTE stmt1; 361f1 f2 f3 3620000000000000003.14152 0000000000000003.14152 000003.14152 363DROP TABLE t1; 364create table t1 (f1 double(200, 0)); 365insert into t1 values (1e199), (-1e199); 366insert into t1 values (1e200), (-1e200); 367insert into t1 values (2e200), (-2e200); 368Warnings: 369Warning 1264 Out of range value for column 'f1' at row 1 370Warning 1264 Out of range value for column 'f1' at row 2 371select f1 + 0e0 from t1; 372f1 + 0e0 3731e199 374-1e199 3751e200 376-1e200 3771e200 378-1e200 379drop table t1; 380create table t1 (f1 float(30, 0)); 381insert into t1 values (1e29), (-1e29); 382insert into t1 values (1e30), (-1e30); 383insert into t1 values (2e30), (-2e30); 384Warnings: 385Warning 1264 Out of range value for column 'f1' at row 1 386Warning 1264 Out of range value for column 'f1' at row 2 387select f1 + 0e0 from t1; 388f1 + 0e0 3891.0000000150474662e29 390-1.0000000150474662e29 3911.0000000150474662e30 392-1.0000000150474662e30 3931.0000000150474662e30 394-1.0000000150474662e30 395drop table t1; 396create table t1 (c char(6)); 397insert into t1 values (2e6),(2e-5); 398select * from t1; 399c 4002e6 4012e-5 402drop table t1; 403CREATE TABLE d1 (d DOUBLE); 404INSERT INTO d1 VALUES (1.7976931348623157E+308); 405SELECT * FROM d1; 406d 4071.7976931348623157e308 408INSERT INTO d1 VALUES (1.79769313486232e+308); 409ERROR 22007: Illegal double '1.79769313486232e+308' value found during parsing 410SELECT * FROM d1; 411d 4121.7976931348623157e308 413DROP TABLE d1; 414create table t1 (a char(20)); 415insert into t1 values (1.225e-05); 416select a+0 from t1; 417a+0 4180.00001225 419drop table t1; 420create table t1(d double, u bigint unsigned); 421insert into t1(d) values (9.22337203685479e18), 422(1.84e19); 423update t1 set u = d; 424select u from t1; 425u 4269223372036854790144 42718400000000000000000 428drop table t1; 429CREATE TABLE t1 (f1 DOUBLE); 430INSERT INTO t1 VALUES(-1.79769313486231e+308); 431SELECT f1 FROM t1; 432f1 433-1.79769313486231e308 434DROP TABLE t1; 435# 436# Bug#12406055 BUFFER OVERFLOW OF VARIABLE 'BUFF' IN STRING::SET_REAL 437# 438# Ignoring output from misc. float operations 439select format(-1.7976931348623157E+307,256) as foo; 440select least(-1.1111111111111111111111111, 441- group_concat(1.7976931348623157E+308)) as foo; 442select concat((truncate((-1.7976931348623157E+307),(0x1e))), 443(99999999999999999999999999999999999999999999999999999999999999999)) into @a; 444End of 5.0 tests 445# 446# Bug#12368853 FORMAT() CRASHES WITH LARGE NUMBERS AFTER TRUNCATE... 447# 448select format(truncate('1.7976931348623157E+308',-12),1,'fr_BE') as foo; 449foo 4500 451# 452# Bug #13500371 63704: CONVERSION OF '1.' TO A NUMBER GIVES ERROR 1265 453# (WARN_DATA_TRUNCATED) 454# 455CREATE TABLE t1 (f FLOAT); 456INSERT INTO t1 VALUES ('1.'); 457INSERT INTO t1 VALUES ('2.0.'); 458Warnings: 459Warning 1265 Data truncated for column 'f' at row 1 460INSERT INTO t1 VALUES ('.'); 461Warnings: 462Warning 1366 Incorrect double value: '.' for column `test`.`t1`.`f` at row 1 463SELECT * FROM t1 ORDER BY f; 464f 4650 4661 4672 468DROP TABLE t1; 469