diff options
Diffstat (limited to 'mysql-test/main/type_float.result')
| -rw-r--r-- | mysql-test/main/type_float.result | 802 |
1 files changed, 802 insertions, 0 deletions
diff --git a/mysql-test/main/type_float.result b/mysql-test/main/type_float.result new file mode 100644 index 00000000000..57cdd1561df --- /dev/null +++ b/mysql-test/main/type_float.result @@ -0,0 +1,802 @@ +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 +# +# 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 '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; +# +# 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 +# |