func_math.test 16.3 KB
Newer Older
1 2 3 4
#
# Test of math functions
#

5 6 7 8
--disable_warnings                                                              
drop table if exists t1;                                                        
--enable_warnings                                                               

9
select floor(5.5),floor(-5.5);
10
explain extended select floor(5.5),floor(-5.5);
11
select ceiling(5.5),ceiling(-5.5);
12
explain extended select ceiling(5.5),ceiling(-5.5);
unknown's avatar
unknown committed
13
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);
14
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);
15
select round(5.5),round(-5.5);
16
explain extended select round(5.5),round(-5.5);
17 18
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);
19
explain extended select abs(-10), sign(-5), sign(5), sign(0);
20
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);
21
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);
22
select ln(exp(10)),exp(ln(sqrt(10))*2),ln(-1),ln(0),ln(NULL);
23
explain extended select ln(exp(10)),exp(ln(sqrt(10))*2),ln(-1),ln(0),ln(NULL);
24
select log2(8),log2(15),log2(-2),log2(0),log2(NULL);
25
explain extended select log2(8),log2(15),log2(-2),log2(0),log2(NULL);
26
select log10(100),log10(18),log10(-4),log10(0),log10(NULL);
27
explain extended select log10(100),log10(18),log10(-4),log10(0),log10(NULL);
28
select pow(10,log10(10)),power(2,4);
29
explain extended select pow(10,log10(10)),power(2,4);
30
set @@rand_seed1=10000000,@@rand_seed2=1000000;
31
select rand(999999),rand();
32
explain extended select rand(999999),rand();
33
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);
unknown's avatar
unknown committed
34
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);
35
select degrees(pi()),radians(360);
36

37 38 39 40 41
select format(atan(-2, 2), 6);
select format(atan(pi(), 0), 6);
select format(atan2(-2, 2), 6);
select format(atan2(pi(), 0), 6);

42 43 44 45 46 47 48 49 50 51
#
# 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);
52 53 54 55 56

#
# Bug #3051 FLOOR returns invalid 
#

unknown's avatar
unknown committed
57 58 59 60
# 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));
61

62 63 64 65 66
#
# Bug #9060 (format returns incorrect result)
#
select format(4.55, 1), format(4.551, 1);

67
explain extended select degrees(pi()),radians(360);
68 69 70 71 72 73 74

#
# Bug #7281: problem with rand()
#

--error 1054
select rand(rand);
75

unknown's avatar
unknown committed
76 77
# End of 4.1 tests

78 79 80 81 82 83 84 85 86 87 88
#
# 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;

89

90 91 92 93 94 95
#
# Bug @10632 (Ceiling function returns wrong answer)
#
select ceil(0.09);
select ceil(0.000000000000000009);

96 97 98 99 100 101 102 103
#
# Bug #9837: problem with round()
#

create table t1 select round(1, 6);
show create table t1;
select * from t1;
drop table t1;
104

105 106 107 108
#
# Bug #11402: abs() forces rest of calculation to unsigned
#
select abs(-2) * -2;
109

110 111 112
#
# Bug #6172 RAND(a) should only accept constant values as arguments
#
113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131
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;  
132

133 134 135 136 137
#
# 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.
138
--disable_warnings
139
create table t1 (a varchar(90), ts datetime not null, index (a)) engine=innodb default charset=utf8;
140
--enable_warnings
141 142 143 144
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;

145
# End of 4.1 tests
146 147 148 149 150 151 152 153 154 155 156 157

#
# 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='';

unknown's avatar
unknown committed
158 159 160 161 162 163 164 165 166 167 168 169 170 171
#
# 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);

172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193
#
# 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;
194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226

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

227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250
#
# 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;

251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270
#
# Bug #15936: "round" differs on Windows to Unix
#

CREATE TABLE t1 (a DOUBLE);

INSERT INTO t1 VALUES (-1.1), (1.1),
                      (-1.5), (1.5),
                      (-1.9), (1.9),
                      (-2.1), (2.1),
                      (-2.5), (2.5),
                      (-2.9), (2.9),
# Check numbers with absolute values > 2^53 - 1 
# (see comments for MAX_EXACT_INTEGER)
                      (-1e16 - 0.5), (1e16 + 0.5),
                      (-1e16 - 1.5), (1e16 + 1.5);

SELECT a, ROUND(a) FROM t1;

DROP TABLE t1;
271

272 273 274 275 276 277 278 279 280
#
# Bug#45152 crash with round() function on longtext column in a derived table
#
CREATE TABLE t1(f1 LONGTEXT) engine=myisam;
INSERT INTO t1 VALUES ('a');
SELECT 1 FROM (SELECT ROUND(f1) AS a FROM t1) AS s WHERE a LIKE 'a';
SELECT 1 FROM (SELECT ROUND(f1, f1) AS a FROM t1) AS s WHERE a LIKE 'a';
DROP TABLE t1;

281
--echo End of 5.0 tests
282 283 284 285

#
# Bug #31236: Inconsistent division by zero behavior for floating point numbers
#
286 287
# After the fix for bug #8433 we throw an error in the below test cases
# rather than just return a NULL value.
288

289
--error ER_DATA_OUT_OF_RANGE
290
SELECT 1e308 + 1e308;
291
--error ER_DATA_OUT_OF_RANGE
292
SELECT -1e308 - 1e308;
293
--error ER_DATA_OUT_OF_RANGE
294
SELECT 1e300 * 1e300;
295
--error ER_DATA_OUT_OF_RANGE
296
SELECT 1e300 / 1e-300;
297
--error ER_DATA_OUT_OF_RANGE
298
SELECT EXP(750);
299
--error ER_DATA_OUT_OF_RANGE
300 301
SELECT POW(10, 309);

302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318
--echo #
--echo # Bug #44768: SIGFPE crash when selecting rand from a view
--echo #             containing null
--echo #

CREATE OR REPLACE VIEW v1 AS SELECT NULL AS a;
SELECT RAND(a) FROM v1;
DROP VIEW v1;

SELECT RAND(a) FROM (SELECT NULL AS a) b;

CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES (NULL);
SELECT RAND(i) FROM t1;
DROP TABLE t1;

--echo #
319 320
--echo # Bug#57477 SIGFPE when dividing a huge number a negative number
--echo #
Georgi Kodinov's avatar
merge  
Georgi Kodinov committed
321
--error ER_DATA_OUT_OF_RANGE
322
SELECT -9999999999999999991 DIV -1;
Georgi Kodinov's avatar
merge  
Georgi Kodinov committed
323
--error ER_DATA_OUT_OF_RANGE
324 325 326
SELECT -9223372036854775808 DIV -1;
SELECT -9223372036854775808 MOD -1;
SELECT -9223372036854775808999 MOD -1;
327

328 329 330 331 332
#
# Bug #8457: Precision math:
#            DIV returns incorrect result with large decimal value
# Bug #46606:Casting error for large numbers in 5.4 when 'div' is used

333
--error ER_DATA_OUT_OF_RANGE
334
select 123456789012345678901234567890.123456789012345678901234567890 div 1 as x;
335
--error ER_DATA_OUT_OF_RANGE
336 337 338
select "123456789012345678901234567890.123456789012345678901234567890" div 1 as x; 
SHOW WARNINGS;

339 340 341 342 343 344 345 346 347
--echo #
--echo # Bug#57810 case/when/then : Assertion failed: length || !scale
--echo #

SELECT CASE(('')) WHEN (CONVERT(1, CHAR(1))) THEN (('' / 1)) END;
CREATE TABLE t1 SELECT CAST((CASE(('')) WHEN (CONVERT(1, CHAR(1))) THEN (('' / 1)) END) AS CHAR) as C;
SHOW CREATE TABLE t1;
DROP TABLE t1;

348 349 350 351 352 353 354 355 356
--echo #
--echo # Bug#11764994  57900: CREATE TABLE .. SELECT ASSERTS SCALE >= 0 && PRECISION > 0 && SCALE <= PR
--echo #

CREATE TABLE t1 SELECT CEIL(LINESTRINGFROMWKB(1) DIV NULL);
DROP TABLE t1;
CREATE TABLE t1 SELECT FLOOR(LINESTRINGFROMWKB(1) DIV NULL);
DROP TABLE t1;

357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372
--echo #
--echo # Bug#11765923  58937: MANY VALGRIND ERRORS AFTER GROUPING BY RESULT OF DECIMAL COLUMN FUNCTION
--echo #

CREATE TABLE t1(f1 DECIMAL(22,1));
INSERT INTO t1 VALUES (0),(1);
SELECT ROUND(f1, f1) FROM t1;
SELECT ROUND(f1, f1) FROM t1 GROUP BY 1;
DROP TABLE t1;

--echo #
--echo # Bug#11764671  57533: UNINITIALISED VALUES IN COPY_AND_CONVERT (SQL_STRING.CC) WITH CERTAIN CHA
--echo #

SELECT ROUND(LEAST(15, -4939092, 0.2704), STDDEV('a'));

373
--echo End of 5.1 tests
374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502

--echo #
--echo # Bug #8433: Overflow must be an error
--echo #

# Floating point overflows
# ========================
--error ER_DATA_OUT_OF_RANGE
SELECT 1e308 + 1e308;
--error ER_DATA_OUT_OF_RANGE
SELECT -1e308 - 1e308;
--error ER_DATA_OUT_OF_RANGE
SELECT 1e300 * 1e300;
--error ER_DATA_OUT_OF_RANGE
SELECT 1e300 / 1e-300;
--error ER_DATA_OUT_OF_RANGE
SELECT EXP(750);
--error ER_DATA_OUT_OF_RANGE
SELECT POW(10, 309);
--error ER_DATA_OUT_OF_RANGE
SELECT COT(0);
--error ER_DATA_OUT_OF_RANGE
SELECT DEGREES(1e307);

# Integer overflows
# =================

--error ER_DATA_OUT_OF_RANGE
SELECT 9223372036854775808 + 9223372036854775808;
--error ER_DATA_OUT_OF_RANGE
SELECT 18446744073709551615 + 1;
--error ER_DATA_OUT_OF_RANGE
SELECT 1 + 18446744073709551615;
--error ER_DATA_OUT_OF_RANGE
SELECT -2 + CAST(1 AS UNSIGNED);
--error ER_DATA_OUT_OF_RANGE
SELECT CAST(1 AS UNSIGNED) + -2;
--error ER_DATA_OUT_OF_RANGE
SELECT -9223372036854775808 + -9223372036854775808;
--error ER_DATA_OUT_OF_RANGE
SELECT 9223372036854775807 + 9223372036854775807;

--error ER_DATA_OUT_OF_RANGE
SELECT CAST(0 AS UNSIGNED) - 9223372036854775809;
--error ER_DATA_OUT_OF_RANGE
SELECT 9223372036854775808 - 9223372036854775809;
--error ER_DATA_OUT_OF_RANGE
SELECT CAST(1 AS UNSIGNED) - 2;
--error ER_DATA_OUT_OF_RANGE
SELECT 18446744073709551615 - (-1);
--error ER_DATA_OUT_OF_RANGE
SELECT -1 - 9223372036854775808;
--error ER_DATA_OUT_OF_RANGE
SELECT -1 - CAST(1 AS UNSIGNED);
--error ER_DATA_OUT_OF_RANGE
SELECT -9223372036854775808 - 1;
--error ER_DATA_OUT_OF_RANGE
SELECT 9223372036854775807 - -9223372036854775808;

# To test SIGNED overflow when subtraction arguments are both UNSIGNED
set SQL_MODE='NO_UNSIGNED_SUBTRACTION';
--error ER_DATA_OUT_OF_RANGE
SELECT 18446744073709551615 - 1;
--error ER_DATA_OUT_OF_RANGE
SELECT 18446744073709551615 - CAST(1 AS UNSIGNED);
--error ER_DATA_OUT_OF_RANGE
SELECT 18446744073709551614 - (-1);
--error ER_DATA_OUT_OF_RANGE
SELECT 9223372036854775807 - -1;
set SQL_MODE=default;

--error ER_DATA_OUT_OF_RANGE
SELECT 4294967296 * 4294967296;
--error ER_DATA_OUT_OF_RANGE
SELECT 9223372036854775808 * 2;
--error ER_DATA_OUT_OF_RANGE
SELECT 9223372036854775808 * 2;
# The following one triggers condition #3 from the comments in
# Item_func_mul::int_op()
--error ER_DATA_OUT_OF_RANGE
SELECT 7158278827 * 3221225472;
--error ER_DATA_OUT_OF_RANGE
SELECT 9223372036854775807 * (-2);
--error ER_DATA_OUT_OF_RANGE
SELECT CAST(1 as UNSIGNED) * (-1);
--error ER_DATA_OUT_OF_RANGE
SELECT 9223372036854775807 * 2;

--error ER_DATA_OUT_OF_RANGE
SELECT ABS(-9223372036854775808);

--error ER_DATA_OUT_OF_RANGE
SELECT -9223372036854775808 DIV -1;
--error ER_DATA_OUT_OF_RANGE
SELECT 18446744073709551615 DIV -1;


# Have to create a table because the negation op may convert literals to DECIMAL
CREATE TABLE t1(a BIGINT, b BIGINT UNSIGNED);
INSERT INTO t1 VALUES(-9223372036854775808, 9223372036854775809);

--error ER_DATA_OUT_OF_RANGE
SELECT -a FROM t1;
--error ER_DATA_OUT_OF_RANGE
SELECT -b FROM t1;

DROP TABLE t1;

# Decimal overflows
# =================

SET @a:=999999999999999999999999999999999999999999999999999999999999999999999999999999999;
--error ER_DATA_OUT_OF_RANGE
SELECT @a + @a;
--error ER_DATA_OUT_OF_RANGE
SELECT @a * @a;
--error ER_DATA_OUT_OF_RANGE
SELECT -@a - @a;
--error ER_DATA_OUT_OF_RANGE
SELECT @a / 0.5;

# Non-overflow tests to improve code coverage
# ===========================================
SELECT COT(1/0);
SELECT -1 + 9223372036854775808;
SELECT 2 DIV -2;
SELECT -(1 DIV 0);
# Crashed the server with SIGFPE before the bugfix
SELECT -9223372036854775808 MOD -1;
503 504 505 506 507 508

--echo #
--echo # Bug #57209 valgrind + Assertion failed: dst > buf 
--echo #
SELECT floor(log10(format(concat_ws(5445796E25, 5306463, 30837), -358821)))
as foo;
509 510 511 512 513 514 515 516

--echo #
--echo # Bug #58137 char(0) column cause:
--echo #            my_gcvt: Assertion `width > 0 && to != ((void *)0)' failed
--echo #
CREATE TABLE t1(a char(0));
INSERT INTO t1 (SELECT -pi());
DROP TABLE t1;
517 518 519 520 521 522

--echo #
--echo # Bug #59241 invalid memory read
--echo #            in do_div_mod with doubly assigned variables
--echo #
SELECT ((@a:=@b:=1.0) div (@b:=@a:=get_format(datetime, 'usa')));
523 524 525 526 527

--echo #
--echo # Bug #59498 div function broken in mysql-trunk
--echo #
SELECT 1 div null;
528 529 530 531 532

--echo #
--echo # Bug #11792200 - DIVIDING LARGE NUMBERS CAUSES STACK CORRUPTIONS
--echo #
select (1.175494351E-37 div 1.7976931348623157E+308);