# # 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 #10083 (round doesn't increase decimals) # select round(150, 2); # # 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; PREPARE stmt FROM "SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(?) * 1000 AS UNSIGNED) FROM t1 WHERE a = 1"; set @var=2; EXECUTE stmt USING @var; 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 #13820 (No warning on log(negative) # set sql_mode='traditional'; select ln(-1); select log10(-1); select log2(-1); select log(2,-1); select log(-2,1); set sql_mode=''; # # 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; --disable_query_log drop database pbxt; --enable_query_log