drop table if exists t1; select floor(5.5),floor(-5.5); floor(5.5) floor(-5.5) 5 -6 explain select floor(5.5),floor(-5.5); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select floor(5.5) AS `floor(5.5)`,floor(-(5.5)) AS `floor(-5.5)` select ceiling(5.5),ceiling(-5.5); ceiling(5.5) ceiling(-5.5) 6 -5 explain select ceiling(5.5),ceiling(-5.5); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select ceiling(5.5) AS `ceiling(5.5)`,ceiling(-(5.5)) AS `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); truncate(52.64,1) truncate(52.64,2) truncate(52.64,-1) truncate(52.64,-2) truncate(-52.64,1) truncate(-52.64,-1) 52.6 52.64 50 0 -52.6 -50 explain 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); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select truncate(52.64,1) AS `truncate(52.64,1)`,truncate(52.64,2) AS `truncate(52.64,2)`,truncate(52.64,-(1)) AS `truncate(52.64,-1)`,truncate(52.64,-(2)) AS `truncate(52.64,-2)`,truncate(-(52.64),1) AS `truncate(-52.64,1)`,truncate(-(52.64),-(1)) AS `truncate(-52.64,-1)` select round(5.5),round(-5.5); round(5.5) round(-5.5) 6 -6 explain select round(5.5),round(-5.5); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select round(5.5,0) AS `round(5.5)`,round(-(5.5),0) AS `round(-5.5)` select round(5.64,1),round(5.64,2),round(5.64,-1),round(5.64,-2); round(5.64,1) round(5.64,2) round(5.64,-1) round(5.64,-2) 5.6 5.64 10 0 select abs(-10), sign(-5), sign(5), sign(0); abs(-10) sign(-5) sign(5) sign(0) 10 -1 1 0 explain select abs(-10), sign(-5), sign(5), sign(0); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select abs(-(10)) AS `abs(-10)`,sign(-(5)) AS `sign(-5)`,sign(5) AS `sign(5)`,sign(0) AS `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); log(exp(10)) exp(log(sqrt(10))*2) log(-1) log(NULL) log(1,1) log(3,9) log(-1,2) log(NULL,2) 10 10.000000000000002 NULL NULL NULL 2 NULL NULL Warnings: Warning 3020 Invalid argument for logarithm Warning 3020 Invalid argument for logarithm Warning 3020 Invalid argument for logarithm explain 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); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select log(exp(10)) AS `log(exp(10))`,exp((log(sqrt(10)) * 2)) AS `exp(log(sqrt(10))*2)`,log(-(1)) AS `log(-1)`,log(NULL) AS `log(NULL)`,log(1,1) AS `log(1,1)`,log(3,9) AS `log(3,9)`,log(-(1),2) AS `log(-1,2)`,log(NULL,2) AS `log(NULL,2)` select ln(exp(10)),exp(ln(sqrt(10))*2),ln(-1),ln(0),ln(NULL); ln(exp(10)) exp(ln(sqrt(10))*2) ln(-1) ln(0) ln(NULL) 10 10.000000000000002 NULL NULL NULL Warnings: Warning 3020 Invalid argument for logarithm Warning 3020 Invalid argument for logarithm explain select ln(exp(10)),exp(ln(sqrt(10))*2),ln(-1),ln(0),ln(NULL); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select ln(exp(10)) AS `ln(exp(10))`,exp((ln(sqrt(10)) * 2)) AS `exp(ln(sqrt(10))*2)`,ln(-(1)) AS `ln(-1)`,ln(0) AS `ln(0)`,ln(NULL) AS `ln(NULL)` select log2(8),log2(15),log2(-2),log2(0),log2(NULL); log2(8) log2(15) log2(-2) log2(0) log2(NULL) 3 3.9068905956085187 NULL NULL NULL Warnings: Warning 3020 Invalid argument for logarithm Warning 3020 Invalid argument for logarithm explain select log2(8),log2(15),log2(-2),log2(0),log2(NULL); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select log2(8) AS `log2(8)`,log2(15) AS `log2(15)`,log2(-(2)) AS `log2(-2)`,log2(0) AS `log2(0)`,log2(NULL) AS `log2(NULL)` select log10(100),log10(18),log10(-4),log10(0),log10(NULL); log10(100) log10(18) log10(-4) log10(0) log10(NULL) 2 1.255272505103306 NULL NULL NULL Warnings: Warning 3020 Invalid argument for logarithm Warning 3020 Invalid argument for logarithm explain select log10(100),log10(18),log10(-4),log10(0),log10(NULL); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select log10(100) AS `log10(100)`,log10(18) AS `log10(18)`,log10(-(4)) AS `log10(-4)`,log10(0) AS `log10(0)`,log10(NULL) AS `log10(NULL)` select pow(10,log10(10)),power(2,4); pow(10,log10(10)) power(2,4) 10 16 explain select pow(10,log10(10)),power(2,4); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select pow(10,log10(10)) AS `pow(10,log10(10))`,pow(2,4) AS `power(2,4)` set @@rand_seed1=10000000,@@rand_seed2=1000000; select rand(999999),rand(); rand(999999) rand() 0.014231365187309091 0.028870999839968048 explain select rand(999999),rand(); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select rand(999999) AS `rand(999999)`,rand() AS `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); 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) 3.141593 1.000000 0.000000 0.000000 0.642093 1.570796 1.570796 0.785398 explain 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); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select pi() AS `pi()`,format(sin((pi() / 2)),6) AS `format(sin(pi()/2),6)`,format(cos((pi() / 2)),6) AS `format(cos(pi()/2),6)`,format(abs(tan(pi())),6) AS `format(abs(tan(pi())),6)`,format(cot(1),6) AS `format(cot(1),6)`,format(asin(1),6) AS `format(asin(1),6)`,format(acos(0),6) AS `format(acos(0),6)`,format(atan(1),6) AS `format(atan(1),6)` select degrees(pi()),radians(360); degrees(pi()) radians(360) 180 6.283185307179586 select format(atan(-2, 2), 6); format(atan(-2, 2), 6) -0.785398 select format(atan(pi(), 0), 6); format(atan(pi(), 0), 6) 1.570796 select format(atan2(-2, 2), 6); format(atan2(-2, 2), 6) -0.785398 select format(atan2(pi(), 0), 6); format(atan2(pi(), 0), 6) 1.570796 SELECT ACOS(1.0); ACOS(1.0) 0 SELECT ASIN(1.0); ASIN(1.0) 1.5707963267948966 SELECT ACOS(0.2*5.0); ACOS(0.2*5.0) 0 SELECT ACOS(0.5*2.0); ACOS(0.5*2.0) 0 SELECT ASIN(0.8+0.2); ASIN(0.8+0.2) 1.5707963267948966 SELECT ASIN(1.2-0.2); ASIN(1.2-0.2) 1.5707963267948966 select format(4.55, 1), format(4.551, 1); format(4.55, 1) format(4.551, 1) 4.6 4.6 explain select degrees(pi()),radians(360); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select degrees(pi()) AS `degrees(pi())`,radians(360) AS `radians(360)` select rand(rand); ERROR 42S22: Unknown column 'rand' in 'field list' create table t1 (col1 int, col2 decimal(60,30)); insert into t1 values(1,1234567890.12345); select format(col2,7) from t1; format(col2,7) 1,234,567,890.1234500 select format(col2,8) from t1; format(col2,8) 1,234,567,890.12345000 insert into t1 values(7,1234567890123456.12345); select format(col2,6) from t1 where col1=7; format(col2,6) 1,234,567,890,123,456.123450 drop table t1; select ceil(0.09); ceil(0.09) 1 select ceil(0.000000000000000009); ceil(0.000000000000000009) 1 create table t1 select round(1, 6); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `round(1, 6)` int(1) NOT NULL DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci select * from t1; round(1, 6) 1 drop table t1; select abs(-2) * -2; abs(-2) * -2 -4 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; CAST(RAND(2) * 1000 AS UNSIGNED) CAST(RAND(a) * 1000 AS UNSIGNED) 656 405 122 405 645 405 858 656 SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) FROM t1 WHERE a = 1; CAST(RAND(2) * 1000 AS UNSIGNED) CAST(RAND(a) * 1000 AS UNSIGNED) 656 405 122 405 645 405 INSERT INTO t1 VALUES (3); SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) FROM t1; CAST(RAND(2) * 1000 AS UNSIGNED) CAST(RAND(a) * 1000 AS UNSIGNED) 656 405 122 405 645 405 858 656 354 906 SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) FROM t1 WHERE a = 1; CAST(RAND(2) * 1000 AS UNSIGNED) CAST(RAND(a) * 1000 AS UNSIGNED) 656 405 122 405 645 405 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; CAST(RAND(2) * 1000 AS UNSIGNED) CAST(RAND(?) * 1000 AS UNSIGNED) 656 656 122 122 645 645 DROP TABLE t1; SET timestamp=UNIX_TIMESTAMP('2001-01-01 00:00:00'); create table t1 (a varchar(90), ts datetime not null, index (a)) engine=innodb default charset=utf8; 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)); a http://www.foo.com/ Warnings: Warning 1292 Truncated incorrect time value: '17540424:00:00' drop table t1; SET timestamp=DEFAULT; set sql_mode='traditional'; select ln(-1); ln(-1) NULL Warnings: Warning 3020 Invalid argument for logarithm select log10(-1); log10(-1) NULL Warnings: Warning 3020 Invalid argument for logarithm select log2(-1); log2(-1) NULL Warnings: Warning 3020 Invalid argument for logarithm select log(2,-1); log(2,-1) NULL Warnings: Warning 3020 Invalid argument for logarithm select log(-2,1); log(-2,1) NULL Warnings: Warning 3020 Invalid argument for logarithm set sql_mode=''; select round(111,-10); round(111,-10) 0 select round(-5000111000111000155,-1); round(-5000111000111000155,-1) -5000111000111000160 select round(15000111000111000155,-1); round(15000111000111000155,-1) 15000111000111000160 select truncate(-5000111000111000155,-1); truncate(-5000111000111000155,-1) -5000111000111000150 select truncate(15000111000111000155,-1); truncate(15000111000111000155,-1) 15000111000111000150 set names utf8; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. create table t1 (f1 varchar(32) not null, f2 smallint(5) unsigned not null, f3 int(10) unsigned not null default '0') default charset=utf8; Warnings: Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. 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) default charset=utf8; Warnings: Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 1681 Integer display width is deprecated and will be removed in a future release. Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. 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; format(t2.f2-t2.f1+1,0) 10,000 10,000 drop table t1, t2; set names default; select cast(-2 as unsigned), 18446744073709551614, -2; cast(-2 as unsigned) 18446744073709551614 -2 18446744073709551614 18446744073709551614 -2 select abs(cast(-2 as unsigned)), abs(18446744073709551614), abs(-2); abs(cast(-2 as unsigned)) abs(18446744073709551614) abs(-2) 18446744073709551614 18446744073709551614 2 select ceiling(cast(-2 as unsigned)), ceiling(18446744073709551614), ceiling(-2); ceiling(cast(-2 as unsigned)) ceiling(18446744073709551614) ceiling(-2) 18446744073709551614 18446744073709551614 -2 select floor(cast(-2 as unsigned)), floor(18446744073709551614), floor(-2); floor(cast(-2 as unsigned)) floor(18446744073709551614) floor(-2) 18446744073709551614 18446744073709551614 -2 select format(cast(-2 as unsigned), 2), format(18446744073709551614, 2), format(-2, 2); format(cast(-2 as unsigned), 2) format(18446744073709551614, 2) format(-2, 2) 18,446,744,073,709,551,614.00 18,446,744,073,709,551,614.00 -2.00 select sqrt(cast(-2 as unsigned)), sqrt(18446744073709551614), sqrt(-2); sqrt(cast(-2 as unsigned)) sqrt(18446744073709551614) sqrt(-2) 4294967296 4294967296 NULL select round(cast(-2 as unsigned), 1), round(18446744073709551614, 1), round(-2, 1); round(cast(-2 as unsigned), 1) round(18446744073709551614, 1) round(-2, 1) 18446744073709551614 18446744073709551614 -2 select round(4, cast(-2 as unsigned)), round(4, 18446744073709551614), round(4, -2); round(4, cast(-2 as unsigned)) round(4, 18446744073709551614) round(4, -2) 4 4 0 select truncate(cast(-2 as unsigned), 1), truncate(18446744073709551614, 1), truncate(-2, 1); truncate(cast(-2 as unsigned), 1) truncate(18446744073709551614, 1) truncate(-2, 1) 18446744073709551614 18446744073709551614 -2 select truncate(4, cast(-2 as unsigned)), truncate(4, 18446744073709551614), truncate(4, -2); truncate(4, cast(-2 as unsigned)) truncate(4, 18446744073709551614) truncate(4, -2) 4 4 0 select round(10000000000000000000, -19), truncate(10000000000000000000, -19); round(10000000000000000000, -19) truncate(10000000000000000000, -19) 10000000000000000000 10000000000000000000 select round(1e0, -309), truncate(1e0, -309); round(1e0, -309) truncate(1e0, -309) 0 0 select round(1e1,308), truncate(1e1, 308); round(1e1,308) truncate(1e1, 308) 10 10 select round(1e1, 2147483648), truncate(1e1, 2147483648); round(1e1, 2147483648) truncate(1e1, 2147483648) 10 10 select round(1.1e1, 4294967295), truncate(1.1e1, 4294967295); round(1.1e1, 4294967295) truncate(1.1e1, 4294967295) 11 11 select round(1.12e1, 4294967296), truncate(1.12e1, 4294967296); round(1.12e1, 4294967296) truncate(1.12e1, 4294967296) 11.2 11.2 select round(1.5, 2147483640), truncate(1.5, 2147483640); round(1.5, 2147483640) truncate(1.5, 2147483640) 1.500000000000000000000000000000 1.500000000000000000000000000000 select round(1.5, -2147483649), round(1.5, 2147483648); round(1.5, -2147483649) round(1.5, 2147483648) 0 1.500000000000000000000000000000 select truncate(1.5, -2147483649), truncate(1.5, 2147483648); truncate(1.5, -2147483649) truncate(1.5, 2147483648) 0 1.500000000000000000000000000000 select round(1.5, -4294967296), round(1.5, 4294967296); round(1.5, -4294967296) round(1.5, 4294967296) 0 1.500000000000000000000000000000 select truncate(1.5, -4294967296), truncate(1.5, 4294967296); truncate(1.5, -4294967296) truncate(1.5, 4294967296) 0 1.500000000000000000000000000000 select round(1.5, -9223372036854775808), round(1.5, 9223372036854775808); round(1.5, -9223372036854775808) round(1.5, 9223372036854775808) 0 1.500000000000000000000000000000 select truncate(1.5, -9223372036854775808), truncate(1.5, 9223372036854775808); truncate(1.5, -9223372036854775808) truncate(1.5, 9223372036854775808) 0 1.500000000000000000000000000000 select round(1.5, 18446744073709551615), truncate(1.5, 18446744073709551615); round(1.5, 18446744073709551615) truncate(1.5, 18446744073709551615) 1.500000000000000000000000000000 1.500000000000000000000000000000 select round(18446744073709551614, -1), truncate(18446744073709551614, -1); round(18446744073709551614, -1) truncate(18446744073709551614, -1) 18446744073709551610 18446744073709551610 select round(4, -4294967200), truncate(4, -4294967200); round(4, -4294967200) truncate(4, -4294967200) 0 0 select mod(cast(-2 as unsigned), 3), mod(18446744073709551614, 3), mod(-2, 3); mod(cast(-2 as unsigned), 3) mod(18446744073709551614, 3) mod(-2, 3) 2 2 -2 select mod(5, cast(-2 as unsigned)), mod(5, 18446744073709551614), mod(5, -2); mod(5, cast(-2 as unsigned)) mod(5, 18446744073709551614) mod(5, -2) 5 5 1 select pow(cast(-2 as unsigned), 5), pow(18446744073709551614, 5), pow(-2, 5); pow(cast(-2 as unsigned), 5) pow(18446744073709551614, 5) pow(-2, 5) 2.13598703592091e96 2.13598703592091e96 -32 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; y 22201025555 22255916666 SELECT DISTINCT a DIV 900 y FROM t1; y 22201025555 22255916666 SELECT b DIV 900 y FROM t1 GROUP BY y; y 0 Warnings: Warning 1366 Incorrect DECIMAL value: '0' for column '' at row -1 Warning 1292 Truncated incorrect DECIMAL value: 'str1' Warning 1366 Incorrect DECIMAL value: '0' for column '' at row -1 Warning 1292 Truncated incorrect DECIMAL value: 'str2' SELECT c DIV 900 y FROM t1 GROUP BY y; y 0 DROP TABLE t1; CREATE TABLE t1(a LONGBLOB); INSERT INTO t1 VALUES('1'),('2'),('3'); SELECT DISTINCT (a DIV 254576881) FROM t1; (a DIV 254576881) 0 SELECT (a DIV 254576881) FROM t1 UNION ALL SELECT (a DIV 254576881) FROM t1; (a DIV 254576881) 0 0 0 0 0 0 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; a DIV 2 0 DROP TABLE t1; 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; a ROUND(a) -1.1 -1 1.1 1 -1.5 -2 1.5 2 -1.9 -2 1.9 2 -2.1 -2 2.1 2 -2.5 -2 2.5 2 -2.9 -3 2.9 3 -1e16 -10000000000000000 1e16 10000000000000000 -1.0000000000000002e16 -10000000000000002 1.0000000000000002e16 10000000000000002 DROP TABLE t1; CREATE TABLE t1(f1 LONGTEXT); INSERT INTO t1 VALUES ('a'); SELECT 1 FROM (SELECT ROUND(f1) AS a FROM t1) AS s WHERE a LIKE 'a'; 1 SELECT 1 FROM (SELECT ROUND(f1, f1) AS a FROM t1) AS s WHERE a LIKE 'a'; 1 DROP TABLE t1; End of 5.0 tests SELECT 1e308 + 1e308; ERROR 22003: DOUBLE value is out of range in '(1e308 + 1e308)' SELECT -1e308 - 1e308; ERROR 22003: DOUBLE value is out of range in '(-(1e308) - 1e308)' SELECT 1e300 * 1e300; ERROR 22003: DOUBLE value is out of range in '(1e300 * 1e300)' SELECT 1e300 / 1e-300; ERROR 22003: DOUBLE value is out of range in '(1e300 / 1e-300)' SELECT EXP(750); ERROR 22003: DOUBLE value is out of range in 'exp(750)' SELECT POW(10, 309); ERROR 22003: DOUBLE value is out of range in 'pow(10,309)' # # Bug #44768: SIGFPE crash when selecting rand from a view # containing null # CREATE OR REPLACE VIEW v1 AS SELECT NULL AS a; SELECT RAND(a) FROM v1; RAND(a) 0.15522042769493574 DROP VIEW v1; SELECT RAND(a) FROM (SELECT NULL AS a) b; RAND(a) 0.15522042769493574 CREATE TABLE t1 (i INT); INSERT INTO t1 VALUES (NULL); SELECT RAND(i) FROM t1; RAND(i) 0.15522042769493574 DROP TABLE t1; # # Bug#57477 SIGFPE when dividing a huge number a negative number # SELECT -9999999999999999991 DIV -1; ERROR 22003: BIGINT value is out of range in '(-(9999999999999999991) DIV -(1))' SELECT -9223372036854775808 DIV -1; ERROR 22003: BIGINT value is out of range in '(-(9223372036854775808) DIV -(1))' SELECT -9223372036854775808 MOD -1; -9223372036854775808 MOD -1 0 SELECT -9223372036854775808999 MOD -1; -9223372036854775808999 MOD -1 0 select 123456789012345678901234567890.123456789012345678901234567890 div 1 as x; ERROR 22003: BIGINT value is out of range in '(123456789012345678901234567890.123456789012345678901234567890 DIV 1)' select "123456789012345678901234567890.123456789012345678901234567890" div 1 as x; ERROR 22003: BIGINT value is out of range in '('123456789012345678901234567890.123456789012345678901234567890' DIV 1)' SHOW WARNINGS; Level Code Message Warning 1292 Truncated incorrect DECIMAL value: '123456789012345678901234567890' Error 1690 BIGINT value is out of range in '('123456789012345678901234567890.123456789012345678901234567890' DIV 1)' # # Bug#57810 case/when/then : Assertion failed: length || !scale # SELECT CASE(('')) WHEN (CONVERT(1, CHAR(1))) THEN (('' / 1)) END; CASE(('')) WHEN (CONVERT(1, CHAR(1))) THEN (('' / 1)) END NULL CREATE TABLE t1 SELECT CAST((CASE(('')) WHEN (CONVERT(1, CHAR(1))) THEN (('' / 1)) END) AS CHAR) as C; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `C` varchar(22) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DROP TABLE t1; # # Bug#11764994 57900: CREATE TABLE .. SELECT ASSERTS SCALE >= 0 && PRECISION > 0 && SCALE <= PR # CREATE TABLE t1 SELECT CEIL(ST_LINESTRINGFROMWKB(1) DIV NULL); ERROR HY000: Incorrect arguments to DIV CREATE TABLE t1 SELECT FLOOR(ST_LINESTRINGFROMWKB(1) DIV NULL); ERROR HY000: Incorrect arguments to DIV # # Bug#11765923 58937: MANY VALGRIND ERRORS AFTER GROUPING BY RESULT OF DECIMAL COLUMN FUNCTION # CREATE TABLE t1(f1 DECIMAL(22,1)); INSERT INTO t1 VALUES (0),(1); SELECT ROUND(f1, f1) FROM t1; ROUND(f1, f1) 0.0 1.0 SELECT ROUND(f1, f1) FROM t1 GROUP BY 1; ROUND(f1, f1) 0.0 1.0 DROP TABLE t1; # # Bug#11764671 57533: UNINITIALISED VALUES IN COPY_AND_CONVERT (SQL_STRING.CC) WITH CERTAIN CHA # SELECT ROUND(LEAST(15, -4939092, 0.2704), STDDEV('a')); ROUND(LEAST(15, -4939092, 0.2704), STDDEV('a')) -4939092.0000 Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'a' # # Bug#12392636 ASSERTION FAILED: SCALE >= 0 && PRECISION > 0 && SCALE <= PRECISION # SELECT SUM(DISTINCT (TRUNCATE((.1), NULL))); SUM(DISTINCT (TRUNCATE((.1), NULL))) NULL End of 5.1 tests # # Bug #8433: Overflow must be an error # SELECT 1e308 + 1e308; ERROR 22003: DOUBLE value is out of range in '(1e308 + 1e308)' SELECT -1e308 - 1e308; ERROR 22003: DOUBLE value is out of range in '(-(1e308) - 1e308)' SELECT 1e300 * 1e300; ERROR 22003: DOUBLE value is out of range in '(1e300 * 1e300)' SELECT 1e300 / 1e-300; ERROR 22003: DOUBLE value is out of range in '(1e300 / 1e-300)' SELECT EXP(750); ERROR 22003: DOUBLE value is out of range in 'exp(750)' SELECT POW(10, 309); ERROR 22003: DOUBLE value is out of range in 'pow(10,309)' SELECT COT(0); ERROR 22003: DOUBLE value is out of range in 'cot(0)' SELECT DEGREES(1e307); ERROR 22003: DOUBLE value is out of range in 'degrees(1e307)' SELECT 9223372036854775808 + 9223372036854775808; ERROR 22003: BIGINT UNSIGNED value is out of range in '(9223372036854775808 + 9223372036854775808)' SELECT 18446744073709551615 + 1; ERROR 22003: BIGINT UNSIGNED value is out of range in '(18446744073709551615 + 1)' SELECT 1 + 18446744073709551615; ERROR 22003: BIGINT UNSIGNED value is out of range in '(1 + 18446744073709551615)' SELECT -2 + CAST(1 AS UNSIGNED); ERROR 22003: BIGINT UNSIGNED value is out of range in '(-(2) + cast(1 as unsigned))' SELECT CAST(1 AS UNSIGNED) + -2; ERROR 22003: BIGINT UNSIGNED value is out of range in '(cast(1 as unsigned) + -(2))' SELECT -9223372036854775808 + -9223372036854775808; ERROR 22003: BIGINT value is out of range in '(-(9223372036854775808) + -(9223372036854775808))' SELECT 9223372036854775807 + 9223372036854775807; ERROR 22003: BIGINT value is out of range in '(9223372036854775807 + 9223372036854775807)' SELECT CAST(0 AS UNSIGNED) - 9223372036854775809; ERROR 22003: BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 9223372036854775809)' SELECT 9223372036854775808 - 9223372036854775809; ERROR 22003: BIGINT UNSIGNED value is out of range in '(9223372036854775808 - 9223372036854775809)' SELECT CAST(1 AS UNSIGNED) - 2; ERROR 22003: BIGINT UNSIGNED value is out of range in '(cast(1 as unsigned) - 2)' SELECT 18446744073709551615 - (-1); ERROR 22003: BIGINT UNSIGNED value is out of range in '(18446744073709551615 - -(1))' SELECT -1 - 9223372036854775808; ERROR 22003: BIGINT UNSIGNED value is out of range in '(-(1) - 9223372036854775808)' SELECT -1 - CAST(1 AS UNSIGNED); ERROR 22003: BIGINT UNSIGNED value is out of range in '(-(1) - cast(1 as unsigned))' SELECT -9223372036854775808 - 1; ERROR 22003: BIGINT value is out of range in '(-(9223372036854775808) - 1)' SELECT 9223372036854775807 - -9223372036854775808; ERROR 22003: BIGINT value is out of range in '(9223372036854775807 - -(9223372036854775808))' set SQL_MODE='NO_UNSIGNED_SUBTRACTION'; SELECT 18446744073709551615 - 1; ERROR 22003: BIGINT value is out of range in '(18446744073709551615 - 1)' SELECT 18446744073709551615 - CAST(1 AS UNSIGNED); ERROR 22003: BIGINT value is out of range in '(18446744073709551615 - cast(1 as unsigned))' SELECT 18446744073709551614 - (-1); ERROR 22003: BIGINT value is out of range in '(18446744073709551614 - -(1))' SELECT 9223372036854775807 - -1; ERROR 22003: BIGINT value is out of range in '(9223372036854775807 - -(1))' set SQL_MODE=default; SELECT 4294967296 * 4294967296; ERROR 22003: BIGINT value is out of range in '(4294967296 * 4294967296)' SELECT 9223372036854775808 * 2; ERROR 22003: BIGINT UNSIGNED value is out of range in '(9223372036854775808 * 2)' SELECT 9223372036854775808 * 2; ERROR 22003: BIGINT UNSIGNED value is out of range in '(9223372036854775808 * 2)' SELECT 7158278827 * 3221225472; ERROR 22003: BIGINT value is out of range in '(7158278827 * 3221225472)' SELECT 9223372036854775807 * (-2); ERROR 22003: BIGINT value is out of range in '(9223372036854775807 * -(2))' SELECT CAST(1 as UNSIGNED) * (-1); ERROR 22003: BIGINT UNSIGNED value is out of range in '(cast(1 as unsigned) * -(1))' SELECT 9223372036854775807 * 2; ERROR 22003: BIGINT value is out of range in '(9223372036854775807 * 2)' SELECT ABS(-9223372036854775808); ERROR 22003: BIGINT value is out of range in 'abs(-(9223372036854775808))' SELECT -9223372036854775808 DIV -1; ERROR 22003: BIGINT value is out of range in '(-(9223372036854775808) DIV -(1))' SELECT 18446744073709551615 DIV -1; ERROR 22003: BIGINT UNSIGNED value is out of range in '(18446744073709551615 DIV -(1))' CREATE TABLE t1(a BIGINT, b BIGINT UNSIGNED); INSERT INTO t1 VALUES(-9223372036854775808, 9223372036854775809); SELECT -a FROM t1; ERROR 22003: BIGINT value is out of range in '-(`test`.`t1`.`a`)' SELECT -b FROM t1; ERROR 22003: BIGINT value is out of range in '-(`test`.`t1`.`b`)' DROP TABLE t1; SET @a:=999999999999999999999999999999999999999999999999999999999999999999999999999999999; SELECT @a + @a; ERROR 22003: DECIMAL value is out of range in '((@`a`) + (@`a`))' SELECT @a * @a; ERROR 22003: DECIMAL value is out of range in '((@`a`) * (@`a`))' SELECT -@a - @a; ERROR 22003: DECIMAL value is out of range in '(-((@`a`)) - (@`a`))' SELECT @a / 0.5; ERROR 22003: DECIMAL value is out of range in '((@`a`) / 0.5)' SELECT COT(1/0); COT(1/0) NULL Warnings: Warning 1365 Division by 0 SELECT -1 + 9223372036854775808; -1 + 9223372036854775808 9223372036854775807 SELECT 2 DIV -2; 2 DIV -2 -1 SELECT -(1 DIV 0); -(1 DIV 0) NULL Warnings: Warning 1365 Division by 0 Warning 1365 Division by 0 SELECT -9223372036854775808 MOD -1; -9223372036854775808 MOD -1 0 # # Bug #57209 valgrind + Assertion failed: dst > buf # SELECT floor(log10(format(concat_ws(5445796E25, 5306463, 30837), -358821))) as foo; foo 2 # # Bug #58137 char(0) column cause: # my_gcvt: Assertion `width > 0 && to != ((void *)0)' failed # CREATE TABLE t1(a char(0)); INSERT IGNORE INTO t1 (SELECT -pi()); Warnings: Warning 1265 Data truncated for column 'a' at row 1 DROP TABLE t1; # # Bug #59241 invalid memory read # in do_div_mod with doubly assigned variables # SELECT ((@a:=@b:=1.0) div (@b:=@a:=get_format(datetime, 'usa'))); ((@a:=@b:=1.0) div (@b:=@a:=get_format(datetime, 'usa'))) NULL Warnings: Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. Warning 1366 Incorrect DECIMAL value: '0' for column '' at row -1 Warning 1365 Division by 0 # # Bug #59498 div function broken in mysql-trunk # SELECT 1 div null; 1 div null NULL # # Bug #11792200 - DIVIDING LARGE NUMBERS CAUSES STACK CORRUPTIONS # select (1.175494351E-37 div 1.7976931348623157E+308); (1.175494351E-37 div 1.7976931348623157E+308) 0 Warnings: Warning 1292 Truncated incorrect DECIMAL value: '1.7976931348623157' # # Bug#12744991 - DECIMAL_ROUND(X,D) GIVES WRONG RESULTS WHEN D == N*(-9) # select round(999999999, -9); round(999999999, -9) 1000000000 select round(999999999.0, -9); round(999999999.0, -9) 1000000000 select round(999999999999999999, -18); round(999999999999999999, -18) 1000000000000000000 select round(999999999999999999.0, -18); round(999999999999999999.0, -18) 1000000000000000000 # # Bug#12537160 ASSERTION FAILED: # STOP0 <= &TO->BUF[TO->LEN] WITH LARGE NUMBER. # select 999999999999999999999999999999999999999999999999999999999999999999999999999999999 % 0.1 as foo; foo 0.0 select 999999999999999999999999999999999999999999999999999999999999999999999999999999999 % 0.0 as foo; foo NULL Warnings: Warning 1365 Division by 0 # # Bug#12711164 - 61676: # RESULT OF DIV WITH DECIMAL AND INTEGER DOES NOT MAKE SENSE # select 5 div 2; 5 div 2 2 select 5.0 div 2.0; 5.0 div 2.0 2 select 5.0 div 2; 5.0 div 2 2 select 5 div 2.0; 5 div 2.0 2 select 5.9 div 2, 1.23456789e3 DIV 2, 1.23456789e9 DIV 2, 1.23456789e19 DIV 2; 5.9 div 2 1.23456789e3 DIV 2 1.23456789e9 DIV 2 1.23456789e19 DIV 2 2 617 617283945 6172839450000000000 # # Bug#11758319 BEHAVIOR OF LOGARITHMIC FUNCTIONS DEPENDS ON # ERROR_FOR_DIVISION_BY_ZERO # Bug#50507 Behavior of logarithmic functions depends on # ERROR_FOR_DIVISION_BY_ZERO CREATE TABLE t1(a DOUBLE); SET sql_mode=''; INSERT INTO t1 VALUES (ln(1)); INSERT INTO t1 VALUES (ln(0)); Warnings: Warning 3020 Invalid argument for logarithm INSERT INTO t1 VALUES (ln(-1)); Warnings: Warning 3020 Invalid argument for logarithm INSERT INTO t1 VALUES (log(0)); Warnings: Warning 3020 Invalid argument for logarithm INSERT INTO t1 VALUES (log(1,0)); Warnings: Warning 3020 Invalid argument for logarithm INSERT INTO t1 VALUES (log2(0)); Warnings: Warning 3020 Invalid argument for logarithm INSERT INTO t1 VALUES (log10(0)); Warnings: Warning 3020 Invalid argument for logarithm SELECT * FROM t1 ORDER BY a; a NULL NULL NULL NULL NULL NULL 0 SET sql_mode=default; INSERT INTO t1 VALUES (ln(1)); INSERT INTO t1 VALUES (ln(0)); ERROR 2201E: Invalid argument for logarithm INSERT INTO t1 VALUES (ln(-1)); ERROR 2201E: Invalid argument for logarithm INSERT INTO t1 VALUES (log(0)); ERROR 2201E: Invalid argument for logarithm INSERT INTO t1 VALUES (log(1,0)); ERROR 2201E: Invalid argument for logarithm INSERT INTO t1 VALUES (log2(0)); ERROR 2201E: Invalid argument for logarithm INSERT INTO t1 VALUES (log10(0)); ERROR 2201E: Invalid argument for logarithm SELECT * FROM t1 ORDER BY a; a NULL NULL NULL NULL NULL NULL 0 0 UPDATE t1 SET a = ln(0); ERROR 2201E: Invalid argument for logarithm SELECT * FROM t1 ORDER BY a; a NULL NULL NULL NULL NULL NULL 0 0 SET sql_mode=''; UPDATE t1 SET a = ln(0); Warnings: Warning 3020 Invalid argument for logarithm Warning 3020 Invalid argument for logarithm Warning 3020 Invalid argument for logarithm Warning 3020 Invalid argument for logarithm Warning 3020 Invalid argument for logarithm Warning 3020 Invalid argument for logarithm Warning 3020 Invalid argument for logarithm Warning 3020 Invalid argument for logarithm SELECT * FROM t1 ORDER BY a; a NULL NULL NULL NULL NULL NULL NULL NULL SET sql_mode=default; DELETE FROM t1 WHERE a <=> ln(0); ERROR 2201E: Invalid argument for logarithm SELECT * FROM t1 ORDER BY a; a NULL NULL NULL NULL NULL NULL NULL NULL SET sql_mode=''; DELETE FROM t1 WHERE a <=> ln(0); Warnings: Warning 3020 Invalid argument for logarithm Warning 3020 Invalid argument for logarithm Warning 3020 Invalid argument for logarithm Warning 3020 Invalid argument for logarithm Warning 3020 Invalid argument for logarithm Warning 3020 Invalid argument for logarithm Warning 3020 Invalid argument for logarithm Warning 3020 Invalid argument for logarithm SELECT * FROM t1 ORDER BY a; a DROP TABLE t1; SET sql_mode=default; # # Bug#19047527 SQRT() : UNINITIALISED VALUE IN MY_STRTOD # do sqrt(weight_string(_eucjpms "0E+")); # # Bug#21279005 - ROUND FUNCTION INCORRECT IN WHERE-CLAUSE # USING LOCAL VARIABLE CREATE PROCEDURE test_round_fn() BEGIN DECLARE num_digits int; SET num_digits := 3; CREATE TEMPORARY TABLE tmp_test ( number double ); INSERT INTO tmp_test VALUES (1),(0); SELECT number, Round(number, num_digits) > 0, Round(number, 3) > 0 FROM tmp_test; SELECT number, Round(number, num_digits) > 0, Round(number, 3) > 0 FROM tmp_test WHERE Round(number, 3) >= 0; SELECT number, Round(number, num_digits) > 0, Round(number, 3) > 0 FROM tmp_test WHERE Round(number, num_digits) >= 0; END| CALL test_round_fn(); number Round(number, num_digits) > 0 Round(number, 3) > 0 1 1 1 0 0 0 number Round(number, num_digits) > 0 Round(number, 3) > 0 1 1 1 0 0 0 number Round(number, num_digits) > 0 Round(number, 3) > 0 1 1 1 0 0 0 DROP PROCEDURE test_round_fn; # # Bug#23013359 FUNCTION FLOOR(CEIL()) TRUNCATES BIGINT UNSIGNED # SELECT floor(ceil(12345678901234567890)); floor(ceil(12345678901234567890)) 12345678901234567890 SELECT floor(18446744073709551616); floor(18446744073709551616) 18446744073709551616 SELECT floor(floor(18446744073709551616)); floor(floor(18446744073709551616)) 18446744073709551616 SELECT floor(floor(floor(18446744073709551616))); floor(floor(floor(18446744073709551616))) 18446744073709551616 # Bug#25669580: regression: assertion failed: 0 in Item_num_func1::str_op DO ROUND(0xe9b1, NULL); # Bug#25688504: regression: scale >= 0 && precision >= 0 && ... DO COUNT(DISTINCT ROUND(CAST(SLEEP(0) AS DECIMAL), NULL)); # # Bug#26495791 - EXPAND TEST SUITE TO INCLUDE CRC32 TESTS # SELECT CRC32(NULL), CRC32(''), CRC32('MySQL'), CRC32('mysql'); CRC32(NULL) CRC32('') CRC32('MySQL') CRC32('mysql') NULL 0 3259397556 2501908538 SELECT CRC32('01234567'), CRC32('012345678'); CRC32('01234567') CRC32('012345678') 763378421 939184570 SELECT CRC32('~!@$%^*'), CRC32('-0.0001'); CRC32('~!@$%^*') CRC32('-0.0001') 1533935548 324352530 SELECT CRC32(99999999999999999999999999999999); CRC32(99999999999999999999999999999999) 3035957051 SELECT CRC32(-99999999999999999999999999999999); CRC32(-99999999999999999999999999999999) 1052326872 DROP TABLE IF EXISTS t; Warnings: Note 1051 Unknown table 'test.t' CREATE TABLE t(a INT, b VARCHAR(2)); INSERT INTO t VALUES (1,'a'), (2,'qw'), (1,'t'), (3,'t'); SELECT crc32(SUM(a)) FROM t; crc32(SUM(a)) 1790921346 SELECT crc32(AVG(a)) FROM t GROUP BY b; crc32(AVG(a)) 768278432 2875100430 2875100430 SELECT crc32(MAX(b)) FROM t GROUP BY a; crc32(MAX(b)) 2238339752 3114057431 2238339752 SELECT a, b, crc32(a) FROM t GROUP BY a,b HAVING crc32(MAX(a))=450215437; a b crc32(a) 2 qw 450215437 SELECT a,b,concat(a,b),crc32(concat(a,b)) FROM t ORDER BY crc32(concat(a,b)); a b concat(a,b) crc32(concat(a,b)) 2 qw 2qw 552289476 1 a 1a 3174122627 1 t 1t 3505168488 3 t 3t 3805987562 DROP TABLE t; SELECT CRC32(4+2); CRC32(4+2) 498629140 SELECT CRC32(4/2); CRC32(4/2) 2875100430 SELECT CRC32(4-2); CRC32(4-2) 450215437 SELECT CRC32(4*2); CRC32(4*2) 4194326291 SELECT CRC32(ABS(-6)); CRC32(ABS(-6)) 498629140 SELECT CRC32(CEILING(1.23)); CRC32(CEILING(1.23)) 450215437 SELECT CRC32(FLOOR(1.23)); CRC32(FLOOR(1.23)) 2212294583 SELECT CRC32(LOG(10,100)); CRC32(LOG(10,100)) 450215437 SELECT CRC32(PI()); CRC32(PI()) 2969982827 SELECT CRC32(POWER(2,2)); CRC32(POWER(2,2)) 4088798008 SELECT CRC32(ROUND(1.58)); CRC32(ROUND(1.58)) 450215437 SELECT CRC32(SIGN(0)); CRC32(SIGN(0)) 4108050209 SELECT CRC32(SQRT(4)); CRC32(SQRT(4)) 450215437 SELECT CRC32(2 > 4); CRC32(2 > 4) 4108050209 SELECT CRC32(2 < 4); CRC32(2 < 4) 2212294583 SELECT CRC32(2 >= 4); CRC32(2 >= 4) 4108050209 SELECT CRC32(2 <= 4); CRC32(2 <= 4) 2212294583 SELECT CRC32(2 != 4); CRC32(2 != 4) 2212294583 SELECT CRC32(NOT 1); CRC32(NOT 1) 4108050209 SELECT CRC32(1 AND 1); CRC32(1 AND 1) 2212294583 SELECT CRC32(1 OR 1); CRC32(1 OR 1) 2212294583 SELECT CRC32(1 XOR 1); CRC32(1 XOR 1) 4108050209 SELECT CRC32(ASCII('2')); CRC32(ASCII('2')) 3308380389 SELECT CRC32(BIT_LENGTH('text')); CRC32(BIT_LENGTH('text')) 2103780943 SELECT CRC32(CHAR('77','121','83','81','76')); CRC32(CHAR('77','121','83','81','76')) 3259397556 SELECT CRC32(CONCAT('good','year')); CRC32(CONCAT('good','year')) 3441724142 SELECT CRC32(INSERT('foodyear', 1, 4, 'good')); CRC32(INSERT('foodyear', 1, 4, 'good')) 3441724142 SELECT CRC32(LEFT('goodyear', 4)); CRC32(LEFT('goodyear', 4)) 1820610194 SELECT CRC32(LENGTH('text')); CRC32(LENGTH('text')) 4088798008 SELECT CRC32(LOWER('GOODYEAR')); CRC32(LOWER('GOODYEAR')) 3441724142 SELECT CRC32(UPPER('goodyear')); CRC32(UPPER('goodyear')) 186062498 SELECT CRC32(LTRIM(' goodyear')); CRC32(LTRIM(' goodyear')) 3441724142 SELECT CRC32(RTRIM('goodyear ')); CRC32(RTRIM('goodyear ')) 3441724142 SELECT CRC32(REPLACE('godyear','o','oo')); CRC32(REPLACE('godyear','o','oo')) 3441724142 SELECT CRC32(REVERSE('goodyear')); CRC32(REVERSE('goodyear')) 3423698264 SELECT CRC32(true); CRC32(true) 2212294583 SELECT CRC32(false); CRC32(false) 4108050209 DROP TABLE IF EXISTS t1; Warnings: Note 1051 Unknown table 'test.t1' CREATE TABLE t1 (c1 BIT(5), c2 TINYINT, c3 MEDIUMINT, c4 INTEGER, c5 BIGINT, c6 DECIMAL(7,5), c7 FLOAT(7,5), c8 DOUBLE(7,5)); Warnings: Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release. Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release. INSERT INTO t1 VALUES (B'10101', 127, 8388607, 2147483647, 9223372036854775807, 10.5, 11.5, 12.5); SELECT CRC32(c1) FROM t1; CRC32(c1) 3219065702 SELECT CRC32(c2) FROM t1; CRC32(c2) 2401609675 SELECT CRC32(c3) FROM t1; CRC32(c3) 1742563487 SELECT CRC32(c4) FROM t1; CRC32(c4) 365954768 SELECT CRC32(c5) FROM t1; CRC32(c5) 1122634462 SELECT CRC32(c6) FROM t1; CRC32(c6) 4255015174 SELECT CRC32(c7) FROM t1; CRC32(c7) 1542023858 SELECT CRC32(c8) FROM t1; CRC32(c8) 1778479151 DROP TABLE IF EXISTS t1; CREATE TABLE t1 (c1 DATE, c2 DATETIME, c3 TIMESTAMP, c4 TIME, c5 YEAR); INSERT INTO t1 VALUES ('2007-01-01', '2007-01-01 12:00:01', '2007-01-01 00:00:01.000000', '12:00:01.000000', '2007'); SELECT CRC32(c1) FROM t1; CRC32(c1) 2772295888 SELECT CRC32(c2) FROM t1; CRC32(c2) 1449026401 SELECT CRC32(c3) FROM t1; CRC32(c3) 225145558 SELECT CRC32(c4) FROM t1; CRC32(c4) 1077283474 SELECT CRC32(c5) FROM t1; CRC32(c5) 955685210 DROP TABLE IF EXISTS t1; CREATE TABLE t1 (c1 CHAR, c2 VARCHAR(10), c3 BINARY(1), c4 VARBINARY(10), c5 TINYBLOB, c6 TINYTEXT, c7 BLOB, c8 TEXT, c9 MEDIUMBLOB, c10 MEDIUMTEXT, c11 LONGBLOB, c12 LONGTEXT); INSERT INTO t1 VALUES ('a', 'a', 0x61, 0x61, 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a'); SELECT CRC32(c1) FROM t1; CRC32(c1) 3904355907 SELECT CRC32(c2) FROM t1; CRC32(c2) 3904355907 SELECT CRC32(c3) FROM t1; CRC32(c3) 3904355907 SELECT CRC32(c4) FROM t1; CRC32(c4) 3904355907 SELECT CRC32(c5) FROM t1; CRC32(c5) 3904355907 SELECT CRC32(c6) FROM t1; CRC32(c6) 3904355907 SELECT CRC32(c7) FROM t1; CRC32(c7) 3904355907 SELECT CRC32(c8) FROM t1; CRC32(c8) 3904355907 SELECT CRC32(c9) FROM t1; CRC32(c9) 3904355907 SELECT CRC32(c10) FROM t1; CRC32(c10) 3904355907 SELECT CRC32(c11) FROM t1; CRC32(c11) 3904355907 SELECT CRC32(c12) FROM t1; CRC32(c12) 3904355907 SELECT CRC32(ST_GeomFromText('POINT(1 1)')); CRC32(ST_GeomFromText('POINT(1 1)')) 1349318989 CREATE TABLE geom_data(id INT, pt POINT NOT NULL, lnstr LINESTRING NOT NULL, mlnstr MULTILINESTRING NOT NULL, poly POLYGON NOT NULL, mpoly MULTIPOLYGON NOT NULL); INSERT INTO geom_data VALUES (10, ST_GEOMFROMTEXT('POINT(10 20)'), ST_GEOMFROMTEXT('LINESTRING(0 0,5 5,6 6)'), ST_GEOMFROMTEXT('MULTILINESTRING((0 0,2 3,4 5),(6 6,8 8,9 9,10 10))'), ST_GEOMFROMTEXT('POLYGON((0 0,6 7,8 8,3 9,0 0),(3 6,4 6,4 7,3 6))'), ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)), ((2 2,4 5,6 2,2 2)))')); SELECT CRC32(ST_X(pt)) FROM geom_data; CRC32(ST_X(pt)) 2707236321 SELECT CRC32(ST_NumPoints((lnstr))) FROM geom_data; CRC32(ST_NumPoints((lnstr))) 1842515611 SELECT CRC32(ST_Length((mlnstr))) FROM geom_data; CRC32(ST_Length((mlnstr))) 2090153432 SELECT CRC32(ST_Area((poly))) FROM geom_data; CRC32(ST_Area((poly))) 534598600 SELECT CRC32(ST_Area((mpoly))) FROM geom_data; CRC32(ST_Area((mpoly))) 3832313845 DROP TABLE geom_data; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (e1 ENUM ('a','b'), s1 SET('a','b')); INSERT INTO t1 VALUES(2,'a,b'),('a','b,a'); SELECT e1, CRC32(e1) FROM t1; e1 CRC32(e1) b 1908338681 a 3904355907 SELECT s1, CRC32(s1) FROM t1; s1 CRC32(s1) a,b 752423903 a,b 752423903 DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a JSON); INSERT INTO t1 VALUES ('{"name" : "goodyear"}'), ('{"name" : "verygood-year"}'); SELECT a->>'$.name', CRC32(a) FROM t1; a->>'$.name' CRC32(a) goodyear 3664432072 verygood-year 922278888 SELECT CRC32(a->>'$.name') FROM t1 WHERE a->>'$.name' = 'goodyear'; CRC32(a->>'$.name') 3441724142 SELECT CRC32(REPLACE(JSON_EXTRACT(a, "$.name"),'\"','')) FROM t1 WHERE JSON_EXTRACT(a, "$.name") = 'goodyear'; CRC32(REPLACE(JSON_EXTRACT(a, "$.name"),'\"','')) 3441724142 DROP TABLE t1; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES(10); CREATE VIEW v1 AS SELECT CRC32(a) AS my_crc FROM t1; SELECT * FROM v1; my_crc 2707236321 SELECT CRC32(CRC32(my_crc)) FROM v1; CRC32(CRC32(my_crc)) 3114155452 DROP VIEW v1; DROP TABLE t1; CREATE TABLE t1 (a CHAR); CREATE TABLE t2 (b BIGINT); CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES(CRC32(NEW.a)); INSERT INTO t1 VALUES('a'); SELECT * FROM t2; b 3904355907 DROP TRIGGER trg1; DROP TABLE t2; DROP TABLE t1; CREATE PROCEDURE crc32_proc (IN a CHAR, OUT b BIGINT) SELECT CRC32(a) INTO b; CALL crc32_proc('a',@val); SELECT @val; @val 3904355907 DROP PROCEDURE crc32_proc; CREATE FUNCTION crc32_func(inputvar CHAR) RETURNS BIGINT BEGIN DECLARE crcval BIGINT; SELECT CRC32(inputvar) INTO crcval; RETURN crcval; END| SELECT crc32_func('a'); crc32_func('a') 3904355907 DROP FUNCTION crc32_func; PREPARE stmt1 FROM 'SELECT CRC32(?)'; SET @val = 'a'; EXECUTE stmt1 USING @val; CRC32(?) 3904355907 DEALLOCATE PREPARE stmt; SET NAMES utf8; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. CREATE TABLE t1 (a TEXT) CHARACTER SET = utf8; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. LOAD DATA INFILE '../../std_data/loaddata_utf8.dat' INTO TABLE t1 CHARACTER SET utf8; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. SELECT HEX(a), CRC32(a) from t1; HEX(a) CRC32(a) E4B880E4BA8CE4B889 1785250883 E59B9BE4BA94E585AD 2914501801 E4B883E585ABE4B99D 2374586519 E4B880E4BA8CE4B889 1785250883 E59B9BE4BA94E585AD 2914501801 E4B883E585ABE4B99D0A 1546750244 DROP TABLE t1; SET NAMES default; # # Bug#27134168 UBSAN: MY_DOUBLE_ROUND - NEGATION OF BLAH # SELECT truncate( 0x123, -9223372036854775808 ); truncate( 0x123, -9223372036854775808 ) 0 # # Bug#28401869 ARM BUILD ALLOWS NUMERIC VALUES OUT OF BOUNDS # select cast(pow(2,63) as signed) as pp; pp 9223372036854775807 select 1-cast(pow(2,63) as signed) as qq; qq -9223372036854775806 # # Bug#17081376 ITEM_FUNC_MUL::INT_OP() MISHANDLES 9223372036854775809*-1 # SELECT 9223372036854775809 * -1; ERROR 22003: BIGINT UNSIGNED value is out of range in '(9223372036854775809 * -(1))' SELECT -1 * 9223372036854775809; ERROR 22003: BIGINT UNSIGNED value is out of range in '(-(1) * 9223372036854775809)' # # Bug#28505423 UBSAN: SIGNED INTEGER OVERFLOW IN MY_STRNTOULL10RND_8BIT # CREATE TABLE t(a int); INSERT INTO t VALUES(hex(rtrim(1.040739e+308))); ERROR 22003: Out of range value for column 'a' at row 1 DROP TABLE t; # # Bug#28706832 WINDOWS ALLOWS NUMERIC VALUES OUT OF BOUNDS # SELECT 1-CAST(POW(2,100) AS SIGNED); ERROR 22003: BIGINT value is out of range in 'pow(2,100)' SELECT 1-CAST(POW(2,1000) AS SIGNED); ERROR 22003: BIGINT value is out of range in 'pow(2,1000)' # # Bug#29581610 UBSAN ERROR IN ITEM_FUNC_MUL::INT_OP() # do ((3273059463432352943 )* (-9223372036854775808 )) ; ERROR 22003: BIGINT value is out of range in '(3273059463432352943 * -(9223372036854775808))' do ((-9223372036854775808)* ( 3273059463432352943)) ; ERROR 22003: BIGINT value is out of range in '(-(9223372036854775808) * 3273059463432352943)' select -9223372036854775808 * 0 as result; result 0 select 0 * -9223372036854775808 as result; result 0 select -9223372036854775808 * 1 as result; result -9223372036854775808 select 1 * -9223372036854775808 as result; result -9223372036854775808 select -9223372036854775808 * -1 as result; ERROR 22003: BIGINT value is out of range in '(-(9223372036854775808) * -(1))' select -1 * -9223372036854775808 as result; ERROR 22003: BIGINT value is out of range in '(-(1) * -(9223372036854775808))' select -9223372036854775808 * 2 as result; ERROR 22003: BIGINT value is out of range in '(-(9223372036854775808) * 2)' select 2 * -9223372036854775808 as result; ERROR 22003: BIGINT value is out of range in '(2 * -(9223372036854775808))'