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 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 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 (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 (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 (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 (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 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 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 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 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 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 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 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 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 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 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; # # 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 #