~drizzle-trunk/drizzle/development

#

# Test of math functions

#

--disable_warnings                                                              

drop table if exists t1;                                                        

--enable_warnings                                                               

select floor(5.5),floor(-5.5);

explain extended select floor(5.5),floor(-5.5);

select ceiling(5.5),ceiling(-5.5);

explain extended select ceiling(5.5),ceiling(-5.5);

select truncate(52.64,1),truncate(52.64,2),truncate(52.64,-1),truncate(52.64,-2), truncate(-52.64,1),truncate(-52.64,-1);

explain extended select truncate(52.64,1),truncate(52.64,2),truncate(52.64,-1),truncate(52.64,-2), truncate(-52.64,1),truncate(-52.64,-1);

select round(5.5),round(-5.5);

explain extended select round(5.5),round(-5.5);

select round(5.64,1),round(5.64,2),round(5.64,-1),round(5.64,-2);

select abs(-10), sign(-5), sign(5), sign(0);

explain extended select abs(-10), sign(-5), sign(5), sign(0);

select log(exp(10)),exp(log(sqrt(10))*2),log(-1),log(NULL),log(1,1),log(3,9),log(-1,2),log(NULL,2);

explain extended select log(exp(10)),exp(log(sqrt(10))*2),log(-1),log(NULL),log(1,1),log(3,9),log(-1,2),log(NULL,2);

select ln(exp(10)),exp(ln(sqrt(10))*2),ln(-1),ln(0),ln(NULL);

explain extended select ln(exp(10)),exp(ln(sqrt(10))*2),ln(-1),ln(0),ln(NULL);

select log2(8),log2(15),log2(-2),log2(0),log2(NULL);

explain extended select log2(8),log2(15),log2(-2),log2(0),log2(NULL);

select log10(100),log10(18),log10(-4),log10(0),log10(NULL);

explain extended select log10(100),log10(18),log10(-4),log10(0),log10(NULL);

select pow(10,log10(10)),power(2,4);

explain extended select pow(10,log10(10)),power(2,4);

set @@rand_seed1=10000000,@@rand_seed2=1000000;

select rand(999999),rand();

explain extended select rand(999999),rand();

select pi(),format(sin(pi()/2),6),format(cos(pi()/2),6),format(abs(tan(pi())),6),format(cot(1),6),format(asin(1),6),format(acos(0),6),format(atan(1),6);

explain extended select pi(),format(sin(pi()/2),6),format(cos(pi()/2),6),format(abs(tan(pi())),6),format(cot(1),6),format(asin(1),6),format(acos(0),6),format(atan(1),6);

select degrees(pi()),radians(360);

select format(atan(-2, 2), 6);

select format(atan(pi(), 0), 6);

select format(atan2(-2, 2), 6);

select format(atan2(pi(), 0), 6);

#

# Bug #2338 Trignometric arithmatic problems 

#

SELECT ACOS(1.0);

SELECT ASIN(1.0);

SELECT ACOS(0.2*5.0);

SELECT ACOS(0.5*2.0);

SELECT ASIN(0.8+0.2);

SELECT ASIN(1.2-0.2);

#

# Bug #3051 FLOOR returns invalid 

#

# This can't be tested as it's not portable

#select floor(log(4)/log(2));

#select floor(log(8)/log(2));

#select floor(log(16)/log(2));

#

# Bug #9060 (format returns incorrect result)

#

select format(4.55, 1), format(4.551, 1);

explain extended select degrees(pi()),radians(360);

#

# Bug #7281: problem with rand()

#

--error 1054

select rand(rand);

# End of 4.1 tests

#

# Bug #8459 (FORMAT returns incorrect result)

#

create table t1 (col1 int, col2 decimal(60,30));

insert into t1 values(1,1234567890.12345);

select format(col2,7) from t1;

select format(col2,8) from t1;

insert into t1 values(7,1234567890123456.12345);

select format(col2,6) from t1 where col1=7;

drop table t1;

#

# Bug @10632 (Ceiling function returns wrong answer)

#

select ceil(0.09);

select ceil(0.000000000000000009);

#

# Bug #9837: problem with round()

#

create table t1 select round(1, 6);

show create table t1;

select * from t1;

drop table t1;

#

# Bug #11402: abs() forces rest of calculation to unsigned

#

select abs(-2) * -2;

#

# Bug #6172 RAND(a) should only accept constant values as arguments

#

CREATE TABLE t1 (a INT);

INSERT INTO t1 VALUES (1),(1),(1),(2);

SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) 

  FROM t1;

SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) 

  FROM t1 WHERE a = 1;

INSERT INTO t1 VALUES (3);

SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) 

  FROM t1;

SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) 

  FROM t1 WHERE a = 1;

DROP TABLE t1;  

#

# Bug #14009: use of abs() on null value causes problems with filesort

#

# InnoDB is required to reproduce the fault, but it is okay if we default to

# MyISAM when testing.

--disable_warnings

create table t1 (a varchar(90), ts datetime not null, index (a)) engine=innodb default charset=utf8;

--enable_warnings

insert into t1 values ('http://www.foo.com/', now());

select a from t1 where a='http://www.foo.com/' order by abs(timediff(ts, 0));

drop table t1;

# End of 4.1 tests

#

# Bug #8461 truncate() and round() return false results 2nd argument negative.

# 

# round(a,-b) log_10(b) > a

select round(111,-10);

# round on bigint 

select round(-5000111000111000155,-1);

# round on unsigned bigint

select round(15000111000111000155,-1);

# truncate on bigint 

select truncate(-5000111000111000155,-1);

# truncate on unsigned bigint

select truncate(15000111000111000155,-1);

#

# Bug#16678 FORMAT gives wrong result if client run with default-character-set=utf8

#

set names utf8;

create table t1

(f1 varchar(32) not null,

 f2 smallint(5) unsigned not null,

 f3 int(10) unsigned not null default '0')

engine=myisam default charset=utf8;

insert into t1 values ('zombie',0,0),('gold',1,10000),('silver',2,10000);

create table t2

(f1 int(10) unsigned not null,

 f2 int(10) unsigned not null,

 f3 smallint(5) unsigned not null)

engine=myisam default charset=utf8;

insert into t2 values (16777216,16787215,1),(33554432,33564431,2);

select format(t2.f2-t2.f1+1,0) from t1,t2

where t1.f2 = t2.f3 order by t1.f1;

drop table t1, t2;

set names default;

# Bug 24912 -- misc functions have trouble with unsigned

select cast(-2 as unsigned), 18446744073709551614, -2;

select abs(cast(-2 as unsigned)), abs(18446744073709551614), abs(-2);

select ceiling(cast(-2 as unsigned)), ceiling(18446744073709551614), ceiling(-2);

select floor(cast(-2 as unsigned)), floor(18446744073709551614), floor(-2);

select format(cast(-2 as unsigned), 2), format(18446744073709551614, 2), format(-2, 2);

select sqrt(cast(-2 as unsigned)), sqrt(18446744073709551614), sqrt(-2);

select round(cast(-2 as unsigned), 1), round(18446744073709551614, 1), round(-2, 1);

select round(4, cast(-2 as unsigned)), round(4, 18446744073709551614), round(4, -2);

select truncate(cast(-2 as unsigned), 1), truncate(18446744073709551614, 1), truncate(-2, 1);

select truncate(4, cast(-2 as unsigned)), truncate(4, 18446744073709551614), truncate(4, -2);

select round(10000000000000000000, -19), truncate(10000000000000000000, -19);

select round(1e0, -309), truncate(1e0, -309);

select round(1e1,308), truncate(1e1, 308);

select round(1e1, 2147483648), truncate(1e1, 2147483648);

select round(1.1e1, 4294967295), truncate(1.1e1, 4294967295);

select round(1.12e1, 4294967296), truncate(1.12e1, 4294967296);

select round(1.5, 2147483640), truncate(1.5, 2147483640);

select round(1.5, -2147483649), round(1.5, 2147483648);

select truncate(1.5, -2147483649), truncate(1.5, 2147483648);

select round(1.5, -4294967296), round(1.5, 4294967296);

select truncate(1.5, -4294967296), truncate(1.5, 4294967296);

select round(1.5, -9223372036854775808), round(1.5, 9223372036854775808);

select truncate(1.5, -9223372036854775808), truncate(1.5, 9223372036854775808);

select round(1.5, 18446744073709551615), truncate(1.5, 18446744073709551615);

select round(18446744073709551614, -1), truncate(18446744073709551614, -1);

select round(4, -4294967200), truncate(4, -4294967200);

select mod(cast(-2 as unsigned), 3), mod(18446744073709551614, 3), mod(-2, 3);

select mod(5, cast(-2 as unsigned)), mod(5, 18446744073709551614), mod(5, -2);

select pow(cast(-2 as unsigned), 5), pow(18446744073709551614, 5), pow(-2, 5);

#

# Bug #30587: mysql crashes when trying to group by TIME div NUMBER

#

CREATE TABLE t1 (a timestamp, b varchar(20), c bit(1));

INSERT INTO t1 VALUES('1998-09-23', 'str1', 1), ('2003-03-25', 'str2', 0);

SELECT a DIV 900 y FROM t1 GROUP BY y;

SELECT DISTINCT a DIV 900 y FROM t1;

SELECT b DIV 900 y FROM t1 GROUP BY y;

SELECT c DIV 900 y FROM t1 GROUP BY y;

DROP TABLE t1;

CREATE TABLE t1(a LONGBLOB);

INSERT INTO t1 VALUES('1'),('2'),('3');

SELECT DISTINCT (a DIV 254576881) FROM t1;

SELECT (a DIV 254576881) FROM t1 UNION ALL 

  SELECT (a DIV 254576881) FROM t1;

DROP TABLE t1;

CREATE TABLE t1(a SET('a','b','c'));

INSERT INTO t1 VALUES ('a');

SELECT a DIV 2 FROM t1 UNION SELECT a DIV 2 FROM t1;

DROP TABLE t1;

--echo End of 5.0 tests

#

# Bug #31236: Inconsistent division by zero behavior for floating point numbers

#

SELECT 1e308 + 1e308;

SELECT -1e308 - 1e308;

SELECT 1e300 * 1e300;

SELECT 1e300 / 1e-300;

SELECT EXP(750);

SELECT POW(10, 309);

--echo End of 5.1 tests