path: root/mysql-test/main/type_float.result

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.9999999999999994
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(24),f2 float(52));
show full columns from t1;
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
f1	float	NULL	YES		NULL		#	
f2	double	NULL	YES		NULL		#	
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);
Warnings:
Warning	1264	Out of range value for column 'f1' at row 7
Warning	1264	Out of range value for column 'f1' at row 8
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
1234570000	1234567890
10000000000	10000000000
1e15	1e15
1e20	1e20
3.40282e38	1e50
3.40282e38	1e150
-10	-10
0.00001	0.00001
0.0000000001	0.0000000001
0.000000000000001	0.000000000000001
1e-20	1e-20
0	1e-50
0	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.00000034785054261852176
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=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
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(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));
show full columns from t1;
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
f	float	NULL	YES		NULL		#	
f2	float	NULL	YES		NULL		#	
f3	float(6,2)	NULL	YES		NULL		#	
d	double	NULL	YES		NULL		#	
d2	double	NULL	YES		NULL		#	
d3	double(10,3)	NULL	YES		NULL		#	
de	decimal(10,0)	NULL	YES		NULL		#	
de2	decimal(6,0)	NULL	YES		NULL		#	
de3	decimal(5,2)	NULL	YES		NULL		#	
n	decimal(10,0)	NULL	YES		NULL		#	
n2	decimal(8,0)	NULL	YES		NULL		#	
n3	decimal(7,6)	NULL	YES		NULL		#	
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 (a float(200,100), b double(200,100));
ERROR 42000: Too big scale 100 specified for 'a'. Maximum is 30
create table t1 (c20 char);
insert ignore into t1 values (5000.0);
Warnings:
Warning	1265	Data truncated for column 'c20' at row 1
insert ignore into t1 values (0.5e4);
Warnings:
Warning	1265	Data truncated for column 'c20' at row 1
drop table t1;
create table t1 (f float(54));
ERROR 42000: Incorrect column specifier for column 'f'
drop table if exists t1;
create table t1 (d1 double, d2 double unsigned);
insert into t1 set d1 = -1.0;
update ignore t1 set d2 = d1;
Warnings:
Warning	1264	Out of range value for column 'd2' at row 1
select * from t1;
d1	d2
-1	0
drop table t1;
create table t1 (f float(4,3));
insert ignore into t1 values (-11.0),(-11),("-11"),(11.0),(11),("11");
Warnings:
Warning	1264	Out of range value for column 'f' at row 1
Warning	1264	Out of range value for column 'f' at row 2
Warning	1264	Out of range value for column 'f' at row 3
Warning	1264	Out of range value for column 'f' at row 4
Warning	1264	Out of range value for column 'f' at row 5
Warning	1264	Out of range value for column 'f' at row 6
select * from t1;
f
-9.999
-9.999
-9.999
9.999
9.999
9.999
drop table if exists t1;
create table t1 (f double(4,3));
insert ignore into t1 values (-11.0),(-11),("-11"),(11.0),(11),("11");
Warnings:
Warning	1264	Out of range value for column 'f' at row 1
Warning	1264	Out of range value for column 'f' at row 2
Warning	1264	Out of range value for column 'f' at row 3
Warning	1264	Out of range value for column 'f' at row 4
Warning	1264	Out of range value for column 'f' at row 5
Warning	1264	Out of range value for column 'f' at row 6
select * from t1;
f
-9.999
-9.999
-9.999
9.999
9.999
9.999
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 TABLE t1 (
reckey int unsigned NOT NULL,
recdesc varchar(50) NOT NULL,
PRIMARY KEY  (reckey)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
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(10,1));
create table t2 (d double(10,9));
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.234567800
100000000.000000000
show create table t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `d` double(18,9) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
drop table t1, t2, t3;
create table t1 select  105213674794682365.00 + 0.0 x;
show warnings;
Level	Code	Message
desc  t1;
Field	Type	Null	Key	Default	Extra
x	decimal(21,2)	NO		NULL	
drop table t1;
create table t1 select 0.0 x;
desc t1;
Field	Type	Null	Key	Default	Extra
x	decimal(2,1)	NO		NULL	
create table t2 select 105213674794682365.00 y;
desc t2;
Field	Type	Null	Key	Default	Extra
y	decimal(20,2)	NO		NULL	
create table t3 select x+y a from t1,t2;
show warnings;
Level	Code	Message
desc t3;
Field	Type	Null	Key	Default	Extra
a	decimal(21,2)	NO		NULL	
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 real zerofill,
f2 double zerofill,
f3 float zerofill);
INSERT INTO t1 VALUES ( 0.314152e+1, 0.314152e+1, 0.314152e+1);
PREPARE stmt1 FROM 'select f1, f2, f3 FROM t1';
select f1, f2, f3 FROM t1;
f1	f2	f3
0000000000000003.14152	0000000000000003.14152	000003.14152
select f1, f2, f3 FROM t1;
f1	f2	f3
0000000000000003.14152	0000000000000003.14152	000003.14152
EXECUTE stmt1;
f1	f2	f3
0000000000000003.14152	0000000000000003.14152	000003.14152
DROP TABLE t1;
create table t1 (f1 double(200, 0));
insert into t1 values (1e199), (-1e199);
insert into t1 values (1e200), (-1e200);
insert ignore into t1 values (2e200), (-2e200);
Warnings:
Warning	1264	Out of range value for column 'f1' at row 1
Warning	1264	Out of range value for column 'f1' at row 2
select f1 + 0e0 from t1;
f1 + 0e0
1e199
-1e199
1e200
-1e200
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 ignore into t1 values (2e30), (-2e30);
Warnings:
Warning	1264	Out of range value for column 'f1' at row 1
Warning	1264	Out of range value for column 'f1' at row 2
select f1 + 0e0 from t1;
f1 + 0e0
1.0000000150474662e29
-1.0000000150474662e29
1.0000000150474662e30
-1.0000000150474662e30
1.0000000150474662e30
-1.0000000150474662e30
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.7976931348623157e308
INSERT INTO d1 VALUES (1.79769313486232e+308);
ERROR 22007: Illegal double '1.79769313486232e+308' value found during parsing
SELECT * FROM d1;
d
1.7976931348623157e308
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;
create table t1(d double, u bigint unsigned);
insert into t1(d) values (9.22337203685479e18),
(1.84e19);
update t1 set u = d;
select u from t1;
u
9223372036854790144
18400000000000000000
drop table t1;
CREATE TABLE t1 (f1 DOUBLE);
INSERT INTO t1 VALUES(-1.79769313486231e+308);
SELECT f1 FROM t1;
f1
-1.79769313486231e308
DROP TABLE t1;
#
# Bug#12406055 BUFFER OVERFLOW OF VARIABLE 'BUFF' IN STRING::SET_REAL
# 
# Ignoring output from misc. float operations
select format(-1.7976931348623157E+307,256) as foo;
select least(-1.1111111111111111111111111,
- group_concat(1.7976931348623157E+308)) as foo;
select concat((truncate((-1.7976931348623157E+307),(0x1e))),
(99999999999999999999999999999999999999999999999999999999999999999)) into @a;
End of 5.0 tests
#
# Bug#12368853 FORMAT() CRASHES WITH LARGE NUMBERS AFTER TRUNCATE...
#
select format(truncate('1.7976931348623157E+308',-12),1,'fr_BE') as foo;
foo
0
#
# MDEV-17249 MAKETIME(-1e50,0,0) returns a wrong result
#
SELECT LEFT('a',EXP(50));
LEFT('a',EXP(50))
a
SELECT LEFT('a', COALESCE(1e30));
LEFT('a', COALESCE(1e30))
a
CREATE TABLE t1 (a FLOAT);
INSERT INTO t1 VALUES (1e30);
SELECT LEFT('a',a), LEFT('a',1e30) FROM t1;
LEFT('a',a)	LEFT('a',1e30)
a	a
DROP TABLE t1;
PREPARE stmt FROM 'SELECT LEFT(111,?)';
SET @a=1e30;
EXECUTE stmt USING @a;
LEFT(111,?)
111
DEALLOCATE PREPARE stmt;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2),(3);
SELECT LEFT('a',(SELECT 1e30 FROM t1 LIMIT 1));
LEFT('a',(SELECT 1e30 FROM t1 LIMIT 1))
a
DROP TABLE t1;
CREATE TABLE t1 (a DOUBLE);
INSERT INTO t1 VALUES (1e30),(0);
SELECT LEFT('a', SUM(a)) FROM t1;
LEFT('a', SUM(a))
a
Warnings:
Warning	1916	Got overflow when converting '1e30' to INT. Value truncated
SELECT LEFT('a', AVG(a)) FROM t1;
LEFT('a', AVG(a))
a
Warnings:
Warning	1916	Got overflow when converting '5e29' to INT. Value truncated
DROP TABLE t1;
#
# Bug #13500371 63704: CONVERSION OF '1.' TO A NUMBER GIVES ERROR 1265
# (WARN_DATA_TRUNCATED)
#
CREATE TABLE t1 (f FLOAT);
INSERT INTO t1 VALUES ('1.');
INSERT IGNORE INTO t1 VALUES ('2.0.');
Warnings:
Warning	1265	Data truncated for column 'f' at row 1
INSERT IGNORE INTO t1 VALUES ('.');
Warnings:
Warning	1366	Incorrect double value: '.' for column `test`.`t1`.`f` at row 1
SELECT * FROM t1 ORDER BY f;
f
0
1
2
DROP TABLE t1;
#
# Start of 10.0 tests
#
#
# MDEV-6950 Bad results with joins comparing DATE/DATETIME and INT/DECIMAL/DOUBLE/ENUM/VARCHAR columns
#
CREATE TABLE t1 (a DATETIME PRIMARY KEY);
INSERT INTO t1 VALUES ('1999-01-01 00:00:00');
CREATE TABLE t2 (a DOUBLE);
INSERT INTO t2 VALUES (19990101000000);
INSERT INTO t2 VALUES (990101000000);
SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a;
a
1999-01-01 00:00:00
1999-01-01 00:00:00
SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
a
1999-01-01 00:00:00
1999-01-01 00:00:00
ALTER TABLE t2 ADD PRIMARY KEY(a);
SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a;
a
1999-01-01 00:00:00
1999-01-01 00:00:00
SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
a
1999-01-01 00:00:00
1999-01-01 00:00:00
# t2 should NOT be eliminated
EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
1	SIMPLE	t2	index	PRIMARY	PRIMARY	8	NULL	2	Using where; Using index
DROP TABLE t1,t2;
#
# MDEV-6971 Bad results with joins comparing TIME and DOUBLE/DECIMAL columns
#
CREATE TABLE t1 (a TIME(6) PRIMARY KEY);
INSERT INTO t1 VALUES ('10:20:30');
CREATE TABLE t2 (a DOUBLE);
INSERT INTO t2 VALUES (102030),(102030.000000001);
SELECT t1.* FROM t1 JOIN t2 USING(a);
a
10:20:30.000000
10:20:30.000000
SELECT t1.* FROM t1 LEFT JOIN t2 USING(a);
a
10:20:30.000000
10:20:30.000000
ALTER TABLE t2 ADD PRIMARY KEY(a);
SELECT t1.* FROM t1 JOIN t2 USING(a);
a
10:20:30.000000
10:20:30.000000
SELECT t1.* FROM t1 LEFT JOIN t2 USING(a);
a
10:20:30.000000
10:20:30.000000
# t2 should NOT be elimitated
EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING(a);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
1	SIMPLE	t2	index	PRIMARY	PRIMARY	8	NULL	2	Using where; Using index
DROP TABLE t1,t2;
#
# End of 10.0 tests
#
#
# MDEV-8704 Wrong result for SELECT..WHERE LENGTH(double_column)!=6 AND double_column=100e0
#
CREATE TABLE t1 (a DOUBLE(9,2));
INSERT INTO t1 VALUES (100),(110);
SELECT * FROM t1 WHERE LENGTH(a)!=6;
a
SELECT * FROM t1 WHERE LENGTH(a)!=6 AND a=100e0;
a
DROP TABLE t1;
CREATE TABLE t1 (a DOUBLE);
INSERT INTO t1 VALUES (100),(110);
SELECT * FROM t1 WHERE LENGTH(a)!=6;
a
100
110
SELECT * FROM t1 WHERE LENGTH(a)!=6 AND a=100e0;
a
100
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)!=6 AND a=100e0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 100e0
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)!=RAND() AND a=100e0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 100e0 and <cache>(octet_length(100)) <> rand()
DROP TABLE t1;
CREATE TABLE t1 (a DOUBLE(10,1));
INSERT INTO t1 VALUES (1.1),(1.2),(1.3);
SELECT * FROM t1 WHERE LENGTH(a)!=3;
a
SELECT * FROM t1 WHERE LENGTH(a)!=3 AND a=1.10e0;
a
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)!=3 AND a=1.10e0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0
# Notice 1.1 instead of 1.10 in the final WHERE condition
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)!=RAND() AND a=1.10e0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1.10e0 and <cache>(octet_length(1.1)) <> rand()
DROP TABLE t1;
CREATE TABLE t1 (a DOUBLE(10,2));
INSERT INTO t1 VALUES (1.1),(1.2),(1.3);
SELECT * FROM t1 WHERE LENGTH(a)!=4;
a
SELECT * FROM t1 WHERE LENGTH(a)!=4 AND a=1.10e0;
a
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)!=4 AND a=1.10e0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0
# Notice 1.10 in the final WHERE condition
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)!=RAND() AND a=1.10e0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1.10e0 and <cache>(octet_length(1.10)) <> rand()
DROP TABLE t1;
CREATE TABLE t1 (a DOUBLE(10,3));
INSERT INTO t1 VALUES (1.1),(1.2),(1.3);
SELECT * FROM t1 WHERE LENGTH(a)!=5;
a
SELECT * FROM t1 WHERE LENGTH(a)!=5 AND a=1.10e0;
a
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)!=5 AND a=1.10e0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0
# Notice 1.100 rather than 1.10 in the final WHERE condition
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)!=RAND() AND a=1.10e0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1.10e0 and <cache>(octet_length(1.100)) <> rand()
DROP TABLE t1;
#
# MDEV-8741 Equal field propagation leaves some remainders after simplifying WHERE zerofill_column=2010 AND zerofill_column>=2010
#
CREATE TABLE t1 (a DOUBLE ZEROFILL);
INSERT INTO t1 VALUES (2010),(2020);
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010e0 AND a>=2010e0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2010e0
DROP TABLE t1;
#
# MDEV-23282 FLOAT(53,0) badly handles out-of-range values
#
CREATE OR REPLACE TABLE t1 (c1 FLOAT NOT NULL, c2 FLOAT NOT NULL);
INSERT IGNORE INTO t1 VALUES (1e+40, -1e+40);
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
SELECT c1, c2 FROM t1;
c1	c2
3.40282e38	-3.40282e38
DROP TABLE t1;
CREATE OR REPLACE TABLE t1 (c1 FLOAT(53,0) NOT NULL, c2 FLOAT(53,0) NOT NULL);
INSERT IGNORE INTO t1 VALUES (1e+40, -1e+40);
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
SELECT c1, c2 FROM t1;
c1	c2
340282346638528860000000000000000000000	-340282346638528860000000000000000000000
DROP TABLE t1;
#
# End of 10.1 tests
#
#
# Start of 10.2 tests
#
#
# MDEV-4102 Limitation on DOUBLE or REAL length is ignored with INSERT .. SELECT
#
CREATE TABLE t1 (d1 DOUBLE(5,2), d2 DOUBLE(10,2));
INSERT IGNORE INTO t1 VALUES (10000000.55, 10000000.55);
Warnings:
Warning	1264	Out of range value for column 'd1' at row 1
INSERT IGNORE INTO t1 SELECT d2, d2 FROM t1;
Warnings:
Warning	1264	Out of range value for column 'd1' at row 1
SELECT * FROM t1;
d1	d2
999.99	10000000.55
999.99	10000000.55
DROP TABLE t1;
#
# MDEV-9709 Unexpected modification of value and warning about out of range value upon ALTER
#
CREATE TABLE t1 (
f FLOAT,
d10_10 DOUBLE PRECISION (10,10),
d53_10 DOUBLE(53,10)
);
INSERT IGNORE INTO t1 (f,d10_10,d53_10) VALUES (
-9999999999999999999999999999999999999999999.9999999999,
-9999999999999999999999999999999999999999999.9999999999,
-9999999999999999999999999999999999999999999.9999999999
);
Warnings:
Warning	1264	Out of range value for column 'f' at row 1
Warning	1264	Out of range value for column 'd10_10' at row 1
SELECT * FROM t1;
f	-3.40282e38
d10_10	-0.9999999999
d53_10	-10000000000000000000000000000000000000000000.0000000000
INSERT IGNORE INTO t1 (f,d10_10,d53_10) SELECT d53_10, d53_10, d53_10 FROM t1;
Warnings:
Level	Warning
Code	1264
Message	Out of range value for column 'f' at row 1
Level	Warning
Code	1264
Message	Out of range value for column 'd10_10' at row 1
SELECT * FROM t1;
f	-3.40282e38
d10_10	-0.9999999999
d53_10	-10000000000000000000000000000000000000000000.0000000000
f	-3.40282e38
d10_10	-0.9999999999
d53_10	-10000000000000000000000000000000000000000000.0000000000
ALTER TABLE t1 ADD COLUMN i INT;
SELECT * FROM t1;
f	-3.40282e38
d10_10	-0.9999999999
d53_10	-10000000000000000000000000000000000000000000.0000000000
i	NULL
f	-3.40282e38
d10_10	-0.9999999999
d53_10	-10000000000000000000000000000000000000000000.0000000000
i	NULL
DROP TABLE t1;
CREATE TABLE t1 (d10_10 DOUBLE (10,10));
CREATE TABLE t2 (d53_10 DOUBLE (53,10));
INSERT INTO t2 VALUES (-9999999999999999999999999999999999999999999.9999999999);
INSERT IGNORE INTO t1 (d10_10) SELECT d53_10 FROM t2;
Warnings:
Warning	1264	Out of range value for column 'd10_10' at row 1
SELECT * FROM t1;
d10_10
-0.9999999999
DROP TABLE t1,t2;
CREATE TABLE t1 (d2_2 FLOAT (2,2));
CREATE TABLE t2 (d4_2 FLOAT (4,2));
INSERT INTO t2 VALUES (99.99);
INSERT IGNORE INTO t1 (d2_2) SELECT d4_2 FROM t2;
Warnings:
Warning	1264	Out of range value for column 'd2_2' at row 1
SELECT * FROM t1;
d2_2
0.99
DROP TABLE t1,t2;
#
# Test of using wrong scale
#
create or replace table t1 (a double(40,30));
create or replace table t1 (a double(40,31));
ERROR 42000: Too big scale 31 specified for 'a'. Maximum is 30
create or replace table t1 as select 1.01e1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `1.01e1` double NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
create or replace table t1 as select truncate(10.000000000001e1, 30) as t;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `t` double(47,30) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
create or replace table t1 as select truncate(10.000000000001e1, 31) as t;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `t` double NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
create or replace table t1 as select truncate(10.000000000001e1, 39) as t;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `t` double NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
create or replace table t1 as select truncate(10.000000000001e1, 51) as t;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `t` double NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
create or replace table t1 as select truncate(10.000000000001e1, 20)/2 as t;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `t` double(41,24) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
create or replace table t1 as select truncate(10.000000000001e1, 28)/2 as t;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `t` double DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
drop table if exists t1;
#
# MDEV-11586 UNION of FLOAT type results in erroneous precision
#
CREATE TABLE t1 (f FLOAT);
INSERT INTO t1 VALUES (1.1);
SELECT f FROM t1 UNION SELECT 1;
f
1.100000023841858
1
SELECT 1 UNION SELECT f FROM t1;
1
1
1.100000023841858
SELECT f FROM t1 UNION SELECT 2147483647;
f
1.100000023841858
2147483647
SELECT 2147483647 UNION SELECT f FROM t1;
2147483647
2147483647
1.100000023841858
SELECT CASE WHEN 0 THEN (SELECT f FROM t1) ELSE 2147483647 END AS c1,
CASE WHEN 1 THEN 2147483647 ELSE (SELECT f FROM t1) END AS c2;
c1	c2
2147483647	2147483647
DROP TABLE t1;
#
# End of 10.2 tests
#
#
# MDEV-19468 Hybrid type expressions return wrong format for FLOAT
#
CREATE TABLE t1 (a FLOAT);
INSERT INTO t1 VALUES (0.671437);
SELECT a, COALESCE(a), MAX(a), LEAST(a,a), (SELECT a FROM t1) AS c FROM t1;
a	COALESCE(a)	MAX(a)	LEAST(a,a)	c
0.671437	0.671437	0.671437	0.671437	0.671437
DROP TABLE t1;
CREATE TABLE t1 (a FLOAT);
INSERT INTO t1 VALUES (0.671437);
SELECT
CONCAT(a),
CONCAT(COALESCE(a)),
CONCAT(LEAST(a,a)),
CONCAT(MAX(a)),
CONCAT((SELECT a FROM t1)) AS c
FROM t1;
CONCAT(a)	CONCAT(COALESCE(a))	CONCAT(LEAST(a,a))	CONCAT(MAX(a))	c
0.671437	0.671437	0.671437	0.671437	0.671437
CREATE TABLE t2 AS SELECT
CONCAT(a),
CONCAT(COALESCE(a)),
CONCAT(LEAST(a,a)),
CONCAT(MAX(a)),
CONCAT((SELECT a FROM t1)) AS c
FROM t1;
SELECT * FROM t2;
CONCAT(a)	CONCAT(COALESCE(a))	CONCAT(LEAST(a,a))	CONCAT(MAX(a))	c
0.671437	0.671437	0.671437	0.671437	0.671437
DROP TABLE t1, t2;
#
# MDEV-16872 Add CAST(expr AS FLOAT)
#
SELECT CAST(0.671437 AS FLOAT), CONCAT(CAST(0.671437 AS FLOAT));
CAST(0.671437 AS FLOAT)	CONCAT(CAST(0.671437 AS FLOAT))
0.671437	0.671437
SELECT CAST(1e40 AS FLOAT), CONCAT(CAST(1e40 AS FLOAT));
CAST(1e40 AS FLOAT)	CONCAT(CAST(1e40 AS FLOAT))
3.40282e38	3.40282e38
Warnings:
Note	1264	Out of range value for column 'CAST(1e40 AS FLOAT)' at row 1
Note	1264	Out of range value for column 'CAST(1e40 AS FLOAT)' at row 1
SELECT CAST(-1e40 AS FLOAT), CONCAT(CAST(-1e40 AS FLOAT));
CAST(-1e40 AS FLOAT)	CONCAT(CAST(-1e40 AS FLOAT))
-3.40282e38	-3.40282e38
Warnings:
Note	1264	Out of range value for column 'CAST(-1e40 AS FLOAT)' at row 1
Note	1264	Out of range value for column 'CAST(-1e40 AS FLOAT)' at row 1
SET sql_mode='STRICT_ALL_TABLES,STRICT_TRANS_TABLES';
CREATE TABLE t1 (a FLOAT);
INSERT INTO t1 VALUES (CAST(1e40 AS FLOAT));
Warnings:
Note	1264	Out of range value for column 'CAST(1e40 AS FLOAT)' at row 1
SELECT * FROM t1;
a
3.40282e38
DROP TABLE t1;
SET sql_mode=DEFAULT;
EXPLAIN EXTENDED SELECT CAST(0.671437 AS FLOAT);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select cast(0.671437 as float) AS `CAST(0.671437 AS FLOAT)`
CREATE TABLE t1 AS SELECT CAST(0.671437 AS FLOAT) AS c1;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` float DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
SELECT * FROM t1;
c1
0.671437
DROP TABLE t1;
CREATE TABLE t1 (a FLOAT);
CREATE TABLE t2 AS SELECT CONCAT(a) AS c1, CONCAT(CAST(a AS FLOAT)) AS c2 FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `c1` varchar(12) DEFAULT NULL,
  `c2` varchar(12) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
DROP TABLE t1, t2;
CREATE TABLE t1 (a FLOAT DEFAULT CAST(0.671437 AS FLOAT));
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` float DEFAULT (cast(0.671437 as float))
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
DROP TABLE t1;
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, a FLOAT);
INSERT INTO t1 VALUES (1, 0.671437),(2, 0.671437);
DELETE FROM t1 WHERE a=0.671437;
SELECT * FROM t1;
id	a
1	0.671437
2	0.671437
DELETE FROM t1 WHERE a=CAST(0.671437 AS FLOAT);
DROP TABLE t1;
#
# MDEV-29473 UBSAN: Signed integer overflow: X * Y cannot be represented in type 'int' in strings/dtoa.c
#
CREATE TABLE t1 (c DOUBLE);
INSERT INTO t1 VALUES ('1e4294967297');
ERROR 22003: Out of range value for column 'c' at row 1
DROP TABLE t1;
#
# End of 10.3 tests
#
#
# MDEV-11362 True condition elimination does not work for DECIMAL and temporal dynamic SQL parameters
#
CREATE TABLE t1 (a DOUBLE);
INSERT INTO t1 VALUES (1),(2),(3);
EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1e0+a<=>1e0+a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1
EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>?+a' USING 1e0,1e0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1
EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>1e0+a' USING 1e0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1
EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1e0+a<=>?+a' USING 1e0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1
DROP TABLE t1;
#
# MDEV-23415 Server crash or Assertion `dec_length <= str_length' failed in Item_func_format::val_str_ascii
#
SELECT FORMAT('0', 50, 'de_DE');
FORMAT('0', 50, 'de_DE')
0,00000000000000000000000000000000000000
SELECT FORMAT(0e0, 50, 'de_DE');
FORMAT(0e0, 50, 'de_DE')
0,00000000000000000000000000000000000000
FOR d IN 0..50
DO
SELECT
d,
FORMAT(123456789.123456789e0, d, 'de_DE') AS fdbl,
FORMAT(123456789.123456789, d, 'de_DE') AS fdec;
END FOR;
$$
d	0
fdbl	123.456.789
fdec	123.456.789
d	1
fdbl	123.456.789,1
fdec	123.456.789,1
d	2
fdbl	123.456.789,12
fdec	123.456.789,12
d	3
fdbl	123.456.789,123
fdec	123.456.789,123
d	4
fdbl	123.456.789,1235
fdec	123.456.789,1235
d	5
fdbl	123.456.789,12346
fdec	123.456.789,12346
d	6
fdbl	123.456.789,123457
fdec	123.456.789,123457
d	7
fdbl	123.456.789,1234568
fdec	123.456.789,1234568
d	8
fdbl	123.456.789,12345680
fdec	123.456.789,12345679
d	9
fdbl	123.456.789,123456790
fdec	123.456.789,123456789
d	10
fdbl	123.456.789,1234567900
fdec	123.456.789,1234567890
d	11
fdbl	123.456.789,12345680000
fdec	123.456.789,12345678900
d	12
fdbl	123.456.789,123456790000
fdec	123.456.789,123456789000
d	13
fdbl	123.456.789,1234568000000
fdec	123.456.789,1234567890000
d	14
fdbl	123.456.789,12345679000000
fdec	123.456.789,12345678900000
d	15
fdbl	123.456.789,123456790000000
fdec	123.456.789,123456789000000
d	16
fdbl	123.456.789,1234567800000000
fdec	123.456.789,1234567890000000
d	17
fdbl	123.456.789,12345679000000000
fdec	123.456.789,12345678900000000
d	18
fdbl	123.456.789,123456790000000000
fdec	123.456.789,123456789000000000
d	19
fdbl	123.456.789,1234567900000000000
fdec	123.456.789,1234567890000000000
d	20
fdbl	123.456.789,12345679000000000000
fdec	123.456.789,12345678900000000000
d	21
fdbl	123.456.789,123456800000000000000
fdec	123.456.789,123456789000000000000
d	22
fdbl	123.456.789,1234567900000000000000
fdec	123.456.789,1234567890000000000000
d	23
fdbl	123.456.789,12345680000000000000000
fdec	123.456.789,12345678900000000000000
d	24
fdbl	123.456.789,123456790000000000000000
fdec	123.456.789,123456789000000000000000
d	25
fdbl	123.456.789,1234567900000000000000000
fdec	123.456.789,1234567890000000000000000
d	26
fdbl	123.456.789,12345679000000000000000000
fdec	123.456.789,12345678900000000000000000
d	27
fdbl	123.456.789,123456780000000000000000000
fdec	123.456.789,123456789000000000000000000
d	28
fdbl	123.456.789,1234567900000000000000000000
fdec	123.456.789,1234567890000000000000000000
d	29
fdbl	123.456.789,12345678000000000000000000000
fdec	123.456.789,12345678900000000000000000000
d	30
fdbl	123.456.789,123456790000000000000000000000
fdec	123.456.789,123456789000000000000000000000
d	31
fdbl	123.456.789,1234567900000000000000000000000
fdec	123.456.789,1234567890000000000000000000000
d	32
fdbl	123.456.789,12345679000000000000000000000000
fdec	123.456.789,12345678900000000000000000000000
d	33
fdbl	123.456.789,123456790000000000000000000000000
fdec	123.456.789,123456789000000000000000000000000
d	34
fdbl	123.456.789,1234567900000000000000000000000000
fdec	123.456.789,1234567890000000000000000000000000
d	35
fdbl	123.456.789,12345679000000000000000000000000000
fdec	123.456.789,12345678900000000000000000000000000
d	36
fdbl	123.456.789,123456790000000000000000000000000000
fdec	123.456.789,123456789000000000000000000000000000
d	37
fdbl	123.456.789,1234567900000000000000000000000000000
fdec	123.456.789,1234567890000000000000000000000000000
d	38
fdbl	123.456.789,12345678000000000000000000000000000000
fdec	123.456.789,12345678900000000000000000000000000000
d	39
fdbl	123.456.789,12345678000000000000000000000000000000
fdec	123.456.789,12345678900000000000000000000000000000
d	40
fdbl	123.456.789,12345678000000000000000000000000000000
fdec	123.456.789,12345678900000000000000000000000000000
d	41
fdbl	123.456.789,12345678000000000000000000000000000000
fdec	123.456.789,12345678900000000000000000000000000000
d	42
fdbl	123.456.789,12345678000000000000000000000000000000
fdec	123.456.789,12345678900000000000000000000000000000
d	43
fdbl	123.456.789,12345678000000000000000000000000000000
fdec	123.456.789,12345678900000000000000000000000000000
d	44
fdbl	123.456.789,12345678000000000000000000000000000000
fdec	123.456.789,12345678900000000000000000000000000000
d	45
fdbl	123.456.789,12345678000000000000000000000000000000
fdec	123.456.789,12345678900000000000000000000000000000
d	46
fdbl	123.456.789,12345678000000000000000000000000000000
fdec	123.456.789,12345678900000000000000000000000000000
d	47
fdbl	123.456.789,12345678000000000000000000000000000000
fdec	123.456.789,12345678900000000000000000000000000000
d	48
fdbl	123.456.789,12345678000000000000000000000000000000
fdec	123.456.789,12345678900000000000000000000000000000
d	49
fdbl	123.456.789,12345678000000000000000000000000000000
fdec	123.456.789,12345678900000000000000000000000000000
d	50
fdbl	123.456.789,12345678000000000000000000000000000000
fdec	123.456.789,12345678900000000000000000000000000000
#
# End of 10.4 tests
#