~jaypipes/drizzle/bug530870

drop table if exists t1,t2;
SELECT 10,10.0,10.,.1e+2,100.0e-1;
10	10.0	10.	.1e+2	100.0e-1
10	10.0	10	10	10
SELECT 6e-16, -6e-16, --6e-16, -6e-16+1.000000;
6e-16	-6e-16	--6e-16	-6e-16+1.000000
6e-16	-6e-16	6e-16	0.999999999999999
SELECT 1e1,1.e1,1.0e1,1e+1,1.e+1,1.0e+1,1e-1,1.e-1,1.0e-1;
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	0.1	0.1	0.1
SELECT 0.001e+1,0.001e-1, -0.001e+01,-0.001e-01;
0.001e+1	0.001e-1	-0.001e+01	-0.001e-01
0.01	0.0001	-0.01	-0.0001
SELECT 123.23E+02,-123.23E-02,"123.23E+02"+0.0,"-123.23E-02"+0.0;
123.23E+02	-123.23E-02	"123.23E+02"+0.0	"-123.23E-02"+0.0
12323	-1.2323	12323	-1.2323
SELECT 2147483647E+02,21474836.47E+06;
2147483647E+02	21474836.47E+06
214748364700	21474836470000
create table t1 (f1 float,f2 float);
show columns from t1;
Field	Type	Null	Default	Default_is_NULL	On_Update
f1	DOUBLE	TRUE		TRUE	
f2	DOUBLE	TRUE		TRUE	
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);
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);
select * from t1;
f1	f2
10	10
100000	100000
1234567890	1234567890
10000000000	10000000000
1e15	1e15
1e20	1e20
1e50	1e50
1e150	1e150
-10	-10
0.00001	0.00001
0.0000000001	0.0000000001
0.000000000000001	0.000000000000001
1e-20	1e-20
1e-50	1e-50
1e-150	1e-150
drop table t1;
create table t1 (datum double);
insert into t1 values (0.5),(1.0),(1.5),(2.0),(2.5);
select * from t1;
datum
0.5
1
1.5
2
2.5
select * from t1 where datum < 1.5;
datum
0.5
1
select * from t1 where datum > 1.5;
datum
2
2.5
select * from t1 where datum = 1.5;
datum
1.5
drop table t1;
create table t1 (a  decimal(7,3) not null, key (a));
insert into t1 values ("0"),("-0.00"),("-0.01"),("-0.002"),("1");
select a from t1 order by a;
a
-0.010
-0.002
0.000
0.000
1.000
select min(a) from t1;
min(a)
-0.010
drop table t1;
create table t1 (c1 double, c2 varchar(20));
insert t1 values (121,"16");
select c1 + c1 * (c2 / 100) as col from t1;
col
140.36
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;
select * from t2;
col1	col2	col3	col4
140.36	121.00000	121	0.000000347850542618522
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `col1` double DEFAULT NULL,
  `col2` double(22,5) DEFAULT NULL,
  `col3` double DEFAULT NULL,
  `col4` double DEFAULT NULL
) ENGINE=DEFAULT
drop table t1,t2;
create table t1 (a float);
insert into t1 values (1);
select max(a),min(a),avg(a) from t1;
max(a)	min(a)	avg(a)
1	1	1
drop table t1;
create table t1 (f float, f2 float, f3 float, d double, d2 float, d3 double, de decimal, de2 decimal(6), de3 decimal(5,2), n numeric, n2 numeric(8), n3 numeric(7,6));
show columns from t1;
Field	Type	Null	Default	Default_is_NULL	On_Update
f	DOUBLE	TRUE		TRUE	
f2	DOUBLE	TRUE		TRUE	
f3	DOUBLE	TRUE		TRUE	
d	DOUBLE	TRUE		TRUE	
d2	DOUBLE	TRUE		TRUE	
d3	DOUBLE	TRUE		TRUE	
de	DECIMAL	TRUE		TRUE	
de2	DECIMAL	TRUE		TRUE	
de3	DECIMAL	TRUE		TRUE	
n	DECIMAL	TRUE		TRUE	
n2	DECIMAL	TRUE		TRUE	
n3	DECIMAL	TRUE		TRUE	
drop table t1;
create table t1 (a  decimal(7,3) not null, key (a));
insert into t1 values ("0"),("-0.00"),("-0.01"),("-0.002"),("1");
select a from t1 order by a;
a
-0.010
-0.002
0.000
0.000
1.000
select min(a) from t1;
min(a)
-0.010
drop table t1;
create table t1 (c20 char);
insert into t1 values (5000.0);
ERROR 22001: Data too long for column 'c20' at row 1
insert into t1 values (0.5e4);
ERROR 22001: Data too long for column 'c20' at row 1
drop table t1;
create table t1 (d1 double, d2 double);
insert into t1 set d1 = -1.0;
update t1 set d2 = d1;
select * from t1;
d1	d2
-1	-1
drop table t1;
create table t1 (f float(4,3));
insert into t1 values (-11.0),(-11),("-11"),(11.0),(11),("11");
ERROR 22003: Out of range value for column 'f' at row 1
select * from t1;
f
drop table if exists t1;
create table t1 (f double(4,3));
insert into t1 values (-11.0),(-11),("-11"),(11.0),(11),("11");
ERROR 22003: Out of range value for column 'f' at row 1
select * from t1;
f
drop table if exists t1;
create table t1 (c char(20));
insert into t1 values (5e-28);
select * from t1;
c
5e-28
drop table t1;
create table t1 (c char(6));
insert into t1 values (2e5),(2e6),(2e-4),(2e-5);
select * from t1;
c
200000
2e6
0.0002
2e-5
drop table t1;
CREATE TEMPORARY TABLE t1 (
reckey int NOT NULL,
recdesc varchar(50) NOT NULL,
PRIMARY KEY  (reckey)
) ENGINE=MyISAM;
INSERT INTO t1 VALUES (108, 'Has 108 as key');
INSERT INTO t1 VALUES (109, 'Has 109 as key');
select * from t1 where reckey=108;
reckey	recdesc
108	Has 108 as key
select * from t1 where reckey=1.08E2;
reckey	recdesc
108	Has 108 as key
select * from t1 where reckey=109;
reckey	recdesc
109	Has 109 as key
select * from t1 where reckey=1.09E2;
reckey	recdesc
109	Has 109 as key
drop table t1;
create table t1 (d double);
create table t2 (d double);
insert into t1 values ("100000000.0");
insert into t2 values ("1.23456780");
create table t3 select * from t2 union select * from t1;
select * from t3;
d
1.2345678
100000000
show create table t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `d` double DEFAULT NULL
) ENGINE=DEFAULT
drop table t1, t2, t3;
create table t1 select  105213674794682365.00 + 0.0 x;
show warnings;
Level	Code	Message
desc  t1;
Field	Type	Null	Default	Default is NULL	On Update
x	DECIMAL	FALSE		FALSE	
drop table t1;
create table t1 select 0.0 x;
desc t1;
Field	Type	Null	Default	Default is NULL	On Update
x	DECIMAL	FALSE		FALSE	
create table t2 select 105213674794682365.00 y;
desc t2;
Field	Type	Null	Default	Default is NULL	On Update
y	DECIMAL	FALSE		FALSE	
create table t3 select x+y a from t1,t2;
show warnings;
Level	Code	Message
desc t3;
Field	Type	Null	Default	Default is NULL	On Update
a	DECIMAL	FALSE		FALSE	
drop table t1,t2,t3;
select 1e-308, 1.00000001e-300, 100000000e-300;
1e-308	1.00000001e-300	100000000e-300
1e-308	1.00000001e-300	1e-292
select 10e307;
10e307
1e308
create table t1(a int, b double(8, 2));
insert into t1 values
(1, 28.50), (1, 121.85), (1, 157.23), (1, 1351.00), (1, -1965.35), (1, 81.75), 
(1, 217.08), (1, 7.94), (4, 96.07), (4, 6404.65), (4, -6500.72), (2, 100.00),
(5, 5.00), (5, -2104.80), (5, 2033.80), (5, 0.07), (5, 65.93),
(3, -4986.24), (3, 5.00), (3, 4857.34), (3, 123.74), (3,  0.16),
(6, -1695.31), (6, 1003.77), (6, 499.72), (6, 191.82);
explain select sum(b) s from t1 group by a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	26	Using temporary; Using filesort
select sum(b) s from t1 group by a;
s
0.00
100.00
0.00
-0.00
-0.00
0.00
select sum(b) s from t1 group by a having s <> 0;
s
100.00
select sum(b) s from t1 group by a having s <> 0 order by s;
s
100.00
select sum(b) s from t1 group by a having s <=> 0;
s
0.00
0.00
-0.00
-0.00
0.00
select sum(b) s from t1 group by a having s <=> 0 order by s;
s
-0.00
-0.00
0.00
0.00
0.00
alter table t1 add key (a, b);
explain select sum(b) s from t1 group by a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	a	14	NULL	26	Using index
select sum(b) s from t1 group by a;
s
0.00
100.00
0.00
-0.00
0.00
0.00
select sum(b) s from t1 group by a having s <> 0;
s
100.00
select sum(b) s from t1 group by a having s <> 0 order by s;
s
100.00
select sum(b) s from t1 group by a having s <=> 0;
s
0.00
0.00
-0.00
0.00
0.00
select sum(b) s from t1 group by a having s <=> 0 order by s;
s
-0.00
0.00
0.00
0.00
0.00
drop table t1;
End of 4.1 tests
create table t1 (s1 float(0,2));
ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column 's1').
create table t1 (s1 float(1,2));
ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column 's1').
create table t1 (f1 double(200, 0));
insert into t1 values (1e199), (-1e199);
insert into t1 values (1e200), (-1e200);
insert into t1 values (2e200), (-2e200);
ERROR 22003: Out of range value for column 'f1' at row 1
select f1 + 0e0 from t1;
f1 + 0e0
1e199
-1e199
1e200
-1e200
drop table t1;
create table t1 (f1 float(30, 0));
insert into t1 values (1e29), (-1e29);
insert into t1 values (1e30), (-1e30);
insert into t1 values (2e30), (-2e30);
ERROR 22003: Out of range value for column 'f1' at row 1
select f1 + 0e0 from t1;
f1 + 0e0
1e29
-1e29
1e30
-1e30
drop table t1;
create table t1 (c char(6));
insert into t1 values (2e6),(2e-5);
select * from t1;
c
2e6
2e-5
drop table t1;
CREATE TABLE d1 (d DOUBLE);
INSERT INTO d1 VALUES (1.7976931348623157E+308);
SELECT * FROM d1;
d
1.79769313486232e308
INSERT INTO d1 VALUES (1.79769313486232e+308);
ERROR 22007: Illegal double '1.79769313486232e+308' value found during parsing
SELECT * FROM d1;
d
1.79769313486232e308
DROP TABLE d1;
create table t1 (a char(20));
insert into t1 values (1.225e-05);
select a+0 from t1;
a+0
0.00001225
drop table t1;
End of 5.0 tests