~drizzle-trunk/drizzle/development

# Description

# -----------

# Numeric floating point.

--disable_warnings

drop table if exists t1,t2;

--enable_warnings

SELECT 10,10.0,10.,.1e+2,100.0e-1;

SELECT 6e-16, -6e-16, --6e-16, -6e-16+1.000000;

SELECT 1e1,1.e1,1.0e1,1e+1,1.e+1,1.0e+1,1e-1,1.e-1,1.0e-1;

SELECT 0.001e+1,0.001e-1, -0.001e+01,-0.001e-01;

SELECT 123.23E+02,-123.23E-02,"123.23E+02"+0.0,"-123.23E-02"+0.0;

SELECT 2147483647E+02,21474836.47E+06;

create table t1 (f1 float,f2 float);

# We mask out Privileges column because it differs for embedded server

--replace_column 8 #

show columns from t1;

insert into t1 values(10,10),(1e+5,1e+5),(1234567890,1234567890),(1e+10,1e+10),(1e+15,1e+15),(1e+20,1e+20),(1e+50,1e+50),(1e+150,1e+150);

insert into t1 values(-10,-10),(1e-5,1e-5),(1e-10,1e-10),(1e-15,1e-15),(1e-20,1e-20),(1e-50,1e-50),(1e-150,1e-150);

select * from t1;

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;

select * from t1 where datum < 1.5;

select * from t1 where datum > 1.5;

select * from t1 where 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;

select min(a) from t1;

drop table t1;

#

# BUG#3612, BUG#4393, BUG#4356, BUG#4394

#

create table t1 (c1 double, c2 varchar(20));

insert t1 values (121,"16");

select c1 + c1 * (c2 / 100) as col from t1;

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;

# Floats are a bit different in PS

--disable_ps_protocol

select * from t2;

--enable_ps_protocol

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

show create table t2;

drop table t1,t2;

# Bug #1022: When a table contains a 'float' field, 

# and one of the functions MAX, MIN, or AVG is used on that field,

# the system crashes.

create table t1 (a float);

insert into t1 values (1);

select max(a),min(a),avg(a) from t1;

drop table t1;

#

# FLOAT/DOUBLE/DECIMAL handling

#

create table t1 (f float, f2 float, f3 float, d double, d2 float, d3 double, de decimal, de2 decimal(6), de3 decimal(5,2), n numeric, n2 numeric(8), n3 numeric(7,6));

# We mask out Privileges column because it differs for embedded server

--replace_column 8 #

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

show columns from t1;

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;

select min(a) from t1;

drop table t1;

#

# float in a char(1) field

#

create table t1 (c20 char);

--error ER_DATA_TOO_LONG

insert into t1 values (5000.0);

--error ER_DATA_TOO_LONG

insert into t1 values (0.5e4);

drop table t1;

# Don't allow 'double' to be set to a negative value (Bug #7700)

create table t1 (d1 double, d2 double);

insert into t1 set d1 = -1.0;

update t1 set d2 = d1;

select * from t1;

drop table t1;

# Ensure that maximum values as the result of number of decimals

# being specified in table schema are enforced (Bug #7361)

create table t1 (f float(4,3));

--error ER_WARN_DATA_OUT_OF_RANGE

insert into t1 values (-11.0),(-11),("-11"),(11.0),(11),("11");

select * from t1;

drop table if exists t1;

create table t1 (f double(4,3));

--error ER_WARN_DATA_OUT_OF_RANGE

insert into t1 values (-11.0),(-11),("-11"),(11.0),(11),("11");

select * from t1;

drop table if exists t1;

# Check conversion of floats to character field (Bug #7774)

create table t1 (c char(20));

insert into t1 values (5e-28);

select * from t1;

drop table t1;

create table t1 (c char(6));

insert into t1 values (2e5),(2e6),(2e-4),(2e-5);

select * from t1;

drop table t1;

#

# Test of comparison of integer with float-in-range (Bug #7840)

# This is needed because some ODBC applications (like Foxpro) uses

# floats for everything.

#

CREATE TEMPORARY TABLE t1 (

  reckey int NOT NULL,

  recdesc varchar(50) NOT NULL,

  PRIMARY KEY  (reckey)

) ENGINE=MyISAM;

INSERT INTO t1 VALUES (108, 'Has 108 as key');

INSERT INTO t1 VALUES (109, 'Has 109 as key');

select * from t1 where reckey=108;

select * from t1 where reckey=1.08E2;

select * from t1 where reckey=109;

select * from t1 where reckey=1.09E2;

drop table t1;

#

# Bug #13372 (decimal union)

#

create table t1 (d double);

create table t2 (d double);

insert into t1 values ("100000000.0");

insert into t2 values ("1.23456780");

create table t3 select * from t2 union select * from t1;

select * from t3;

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

show create table t3;

drop table t1, t2, t3;

#

# Bug #9855 (inconsistent column type for create select

#

create table t1 select  105213674794682365.00 + 0.0 x;

show warnings;

desc  t1;

drop table t1;

create table t1 select 0.0 x;

desc t1;

create table t2 select 105213674794682365.00 y;

desc t2;

create table t3 select x+y a from t1 CROSS JOIN t2;

show warnings;

desc t3;

drop table t1,t2,t3;

#

# Bug #22129: A small double precision number becomes zero

#

# check if underflows are detected correctly

select 1e-308, 1.00000001e-300, 100000000e-300;

# check if overflows are detected correctly

select 10e307;

#

# Bug #19690: ORDER BY eliminates rows from the result

#

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;

select sum(b) s from t1 group by a;

select sum(b) s from t1 group by a having s <> 0;

select sum(b) s from t1 group by a having s <> 0 order by s;

select sum(b) s from t1 group by a having s <=> 0;

select sum(b) s from t1 group by a having s <=> 0 order by s;

alter table t1 add key (a, b);

explain select sum(b) s from t1 group by a;

select sum(b) s from t1 group by a;

select sum(b) s from t1 group by a having s <> 0;

select sum(b) s from t1 group by a having s <> 0 order by s;

select sum(b) s from t1 group by a having s <=> 0;

select sum(b) s from t1 group by a having s <=> 0 order by s;

drop table t1;

--echo End of 4.1 tests

#

# bug #12694 (float(m,d) specifications)

#

--error ER_M_BIGGER_THAN_D

create table t1 (s1 float(0,2));

--error ER_M_BIGGER_THAN_D

create table t1 (s1 float(1,2));

# Bug #28121 "INSERT or UPDATE into DOUBLE(200,0) field being truncated to 31 digits"

#

create table t1 (f1 double(200, 0));

insert into t1 values (1e199), (-1e199);

insert into t1 values (1e200), (-1e200);

--error ER_WARN_DATA_OUT_OF_RANGE

insert into t1 values (2e200), (-2e200);

select f1 + 0e0 from t1;

drop table t1;

create table t1 (f1 float(30, 0));

insert into t1 values (1e29), (-1e29);

insert into t1 values (1e30), (-1e30);

--error ER_WARN_DATA_OUT_OF_RANGE

insert into t1 values (2e30), (-2e30);

select f1 + 0e0 from t1;

drop table t1;

#

# Bug #12860 "Difference in zero padding of exponent between Unix and Windows"

#

create table t1 (c char(6));

insert into t1 values (2e6),(2e-5);

select * from t1;

drop table t1;

#

# Bug #21497 "DOUBLE truncated to unusable value"

#

CREATE TABLE d1 (d DOUBLE);

INSERT INTO d1 VALUES (1.7976931348623157E+308);

SELECT * FROM d1;

--error ER_ILLEGAL_VALUE_FOR_TYPE

INSERT INTO d1 VALUES (1.79769313486232e+308);

SELECT * FROM d1;

DROP TABLE d1;

#

# Bug #26788 "mysqld (debug) aborts when inserting specific numbers into char 

#             fields"

#

create table t1 (a char(20));

insert into t1 values (1.225e-05);

select a+0 from t1;

drop table t1;

--echo End of 5.0 tests