drop table if exists t1; select floor(5.5),floor(-5.5); floor(5.5) floor(-5.5) 5 -6 explain extended select floor(5.5),floor(-5.5); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 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 extended select ceiling(5.5),ceiling(-5.5); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 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 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); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 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 extended select round(5.5),round(-5.5); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 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 extended select abs(-10), sign(-5), sign(5), sign(0); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 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 1365 Division by 0 Warning 1365 Division by 0 Warning 1365 Division by 0 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); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 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 1365 Division by 0 Warning 1365 Division by 0 explain extended select ln(exp(10)),exp(ln(sqrt(10))*2),ln(-1),ln(0),ln(NULL); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 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 1365 Division by 0 Warning 1365 Division by 0 explain extended select log2(8),log2(15),log2(-2),log2(0),log2(NULL); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 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 1365 Division by 0 Warning 1365 Division by 0 explain extended select log10(100),log10(18),log10(-4),log10(0),log10(NULL); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 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 extended select pow(10,log10(10)),power(2,4); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 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 extended select rand(999999),rand(); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 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 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); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 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 extended select degrees(pi()),radians(360); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 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 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_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 sql_mode=""; create table t1 (a varchar(90), ts datetime not null, index (a)) engine=innodb default charset=utf8; set sql_mode=default; 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/ drop table t1; set sql_mode='traditional'; select ln(-1); ln(-1) NULL Warnings: Warning 1365 Division by 0 select log10(-1); log10(-1) NULL Warnings: Warning 1365 Division by 0 select log2(-1); log2(-1) NULL Warnings: Warning 1365 Division by 0 select log(2,-1); log(2,-1) NULL Warnings: Warning 1365 Division by 0 select log(-2,1); log(-2,1) NULL Warnings: Warning 1365 Division by 0 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; 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; format(t2.f2-t2.f1+1,0) 10,000 10,000 drop table t1, t2; set names latin1; select cast(-2 as unsigned), 18446744073709551614, -2; cast(-2 as unsigned) 18446744073709551614 -2 18446744073709551614 18446744073709551614 -2 Warnings: Note 1105 Cast to unsigned converted negative integer to it's positive complement select abs(cast(-2 as unsigned)), abs(18446744073709551614), abs(-2); abs(cast(-2 as unsigned)) abs(18446744073709551614) abs(-2) 18446744073709551614 18446744073709551614 2 Warnings: Note 1105 Cast to unsigned converted negative integer to it's positive complement select ceiling(cast(-2 as unsigned)), ceiling(18446744073709551614), ceiling(-2); ceiling(cast(-2 as unsigned)) ceiling(18446744073709551614) ceiling(-2) 18446744073709551614 18446744073709551614 -2 Warnings: Note 1105 Cast to unsigned converted negative integer to it's positive complement select floor(cast(-2 as unsigned)), floor(18446744073709551614), floor(-2); floor(cast(-2 as unsigned)) floor(18446744073709551614) floor(-2) 18446744073709551614 18446744073709551614 -2 Warnings: Note 1105 Cast to unsigned converted negative integer to it's positive complement 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 Warnings: Note 1105 Cast to unsigned converted negative integer to it's positive complement select sqrt(cast(-2 as unsigned)), sqrt(18446744073709551614), sqrt(-2); sqrt(cast(-2 as unsigned)) sqrt(18446744073709551614) sqrt(-2) 4294967296 4294967296 NULL Warnings: Note 1105 Cast to unsigned converted negative integer to it's positive complement 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 Warnings: Note 1105 Cast to unsigned converted negative integer to it's positive complement 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 Warnings: Note 1105 Cast to unsigned converted negative integer to it's positive complement Note 1105 Cast to unsigned converted negative integer to it's positive complement 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 Warnings: Note 1105 Cast to unsigned converted negative integer to it's positive complement 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 Warnings: Note 1105 Cast to unsigned converted negative integer to it's positive complement 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.50000000000000000000000000000000000000 1.50000000000000000000000000000000000000 select round(1.5, -2147483649), round(1.5, 2147483648); round(1.5, -2147483649) round(1.5, 2147483648) 0 1.50000000000000000000000000000000000000 select truncate(1.5, -2147483649), truncate(1.5, 2147483648); truncate(1.5, -2147483649) truncate(1.5, 2147483648) 0 1.50000000000000000000000000000000000000 select round(1.5, -4294967296), round(1.5, 4294967296); round(1.5, -4294967296) round(1.5, 4294967296) 0 1.50000000000000000000000000000000000000 select truncate(1.5, -4294967296), truncate(1.5, 4294967296); truncate(1.5, -4294967296) truncate(1.5, 4294967296) 0 1.50000000000000000000000000000000000000 select round(1.5, -9223372036854775808), round(1.5, 9223372036854775808); round(1.5, -9223372036854775808) round(1.5, 9223372036854775808) 0 1.50000000000000000000000000000000000000 select truncate(1.5, -9223372036854775808), truncate(1.5, 9223372036854775808); truncate(1.5, -9223372036854775808) truncate(1.5, 9223372036854775808) 0 1.50000000000000000000000000000000000000 select round(1.5, 18446744073709551615), truncate(1.5, 18446744073709551615); round(1.5, 18446744073709551615) truncate(1.5, 18446744073709551615) 1.50000000000000000000000000000000000000 1.50000000000000000000000000000000000000 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 Warnings: Note 1105 Cast to unsigned converted negative integer to it's positive complement 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 Warnings: Note 1105 Cast to unsigned converted negative integer to it's positive complement 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 Warnings: Note 1105 Cast to unsigned converted negative integer to it's positive complement 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 1292 Truncated incorrect DECIMAL value: 'str1' 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) engine=myisam; INSERT INTO t1 VALUES ('a'); SELECT 1 FROM (SELECT ROUND(f1) AS a FROM t1) AS s WHERE a LIKE 'a'; 1 Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'a' SELECT 1 FROM (SELECT ROUND(f1, f1) AS a FROM t1) AS s WHERE a LIKE 'a'; 1 Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'a' Warning 1292 Truncated incorrect INTEGER value: 'a' 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 1916 Got overflow when converting '123456789012345678901234567890' to INT. Value truncated 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(23) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1; # # Bug#11764994 57900: CREATE TABLE .. SELECT ASSERTS SCALE >= 0 && PRECISION > 0 && SCALE <= PR # CREATE TABLE t1 SELECT CEIL(LINESTRINGFROMWKB(1) DIV NULL); ERROR HY000: Illegal parameter data type int for operation 'st_geometryfromwkb' CREATE TABLE t1 SELECT FLOOR(LINESTRINGFROMWKB(1) DIV NULL); ERROR HY000: Illegal parameter data type int for operation 'st_geometryfromwkb' # # 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`' INSERT INTO t1 VALUES(0,0); 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 Warnings: Warning 1292 Truncated incorrect DOUBLE value: '53064635.445796e3130837' Warning 1292 Truncated incorrect DOUBLE value: '179,769,313,486,231,570,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,0...' # # 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 1918 Encountered illegal value '' when converting to DECIMAL 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 1916 Got overflow when converting '' to DECIMAL. Value truncated # # 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 # # MDEV-10467 Assertion `nr >= 0.0' failed in Item_sum_std::val_real() # CREATE TABLE t1 (i INT); INSERT INTO t1 VALUES (1),(2); SELECT STDDEV_SAMP(ROUND('0', 309)) FROM t1; STDDEV_SAMP(ROUND('0', 309)) 0 DROP TABLE t1; # # MDEV-20495 Assertion `precision > 0' failed in decimal_bin_size upon CREATE .. SELECT with zerofilled decimal # # Testing that dyadic arithmetic operations are symmetric # for (+1) and (-1) and produce the same length in CONCAT(), # because (+1) and (-1) have the same data type: signed int. CREATE TABLE t1 AS SELECT CONCAT(+1%2.0), CONCAT(-1%2.0), CONCAT(+1/2.0), CONCAT(-1/2.0), CONCAT(+1*2.0), CONCAT(-1*2.0), CONCAT(+1+2.0), CONCAT(-1+2.0), CONCAT(+1-2.0), CONCAT(-1-2.0); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `CONCAT(+1%2.0)` varchar(4) DEFAULT NULL, `CONCAT(-1%2.0)` varchar(4) DEFAULT NULL, `CONCAT(+1/2.0)` varchar(8) DEFAULT NULL, `CONCAT(-1/2.0)` varchar(8) DEFAULT NULL, `CONCAT(+1*2.0)` varchar(5) DEFAULT NULL, `CONCAT(-1*2.0)` varchar(5) DEFAULT NULL, `CONCAT(+1+2.0)` varchar(5) DEFAULT NULL, `CONCAT(-1+2.0)` varchar(5) DEFAULT NULL, `CONCAT(+1-2.0)` varchar(5) DEFAULT NULL, `CONCAT(-1-2.0)` varchar(5) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1; CREATE TABLE t1 AS SELECT CONCAT(+1%2), CONCAT(-1%2), CONCAT(+1/2), CONCAT(-1/2), CONCAT(+1*2), CONCAT(-1*2), CONCAT(+1+2), CONCAT(-1+2), CONCAT(+1-2), CONCAT(-1-2); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `CONCAT(+1%2)` varchar(2) DEFAULT NULL, `CONCAT(-1%2)` varchar(2) DEFAULT NULL, `CONCAT(+1/2)` varchar(7) DEFAULT NULL, `CONCAT(-1/2)` varchar(7) DEFAULT NULL, `CONCAT(+1*2)` varchar(3) DEFAULT NULL, `CONCAT(-1*2)` varchar(3) DEFAULT NULL, `CONCAT(+1+2)` varchar(3) DEFAULT NULL, `CONCAT(-1+2)` varchar(3) DEFAULT NULL, `CONCAT(+1-2)` varchar(3) DEFAULT NULL, `CONCAT(-1-2)` varchar(3) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1; # # Bug #29723340: MYSQL SERVER CRASH AFTER SQL QUERY WITH DATA ?AST # create table t1(a int); insert ignore t1 values("1e-214748364"); Warnings: Warning 1265 Data truncated for column 'a' at row 1 insert ignore t1 values("1e-2147483648"); Warnings: Warning 1265 Data truncated for column 'a' at row 1 insert ignore t1 values("1e-21474836480"); Warnings: Warning 1265 Data truncated for column 'a' at row 1 insert ignore t1 values("1e+214748364"); Warnings: Warning 1264 Out of range value for column 'a' at row 1 insert ignore t1 values("1e+2147483647"); Warnings: Warning 1264 Out of range value for column 'a' at row 1 insert ignore t1 values("1e+21474836470"); Warnings: Warning 1264 Out of range value for column 'a' at row 1 set global max_allowed_packet= cast(2*1024*1024*1024+1024 as unsigned); Warnings: Warning 1292 Truncated incorrect max_allowed_packet value: '2147484672' connect foo,localhost,root; set @a=2147483647; insert ignore t1 values (concat('1', repeat('0', @a+18), 'e-', @a-1, '0')); Warnings: Warning 1301 Result of repeat() was larger than max_allowed_packet (1073741824) - truncated disconnect foo; connection default; set global max_allowed_packet=default; select * from t1; a 0 0 0 2147483647 2147483647 2147483647 NULL drop table t1; # # End of 5.5 tests # # # MDEV-5781 Item_sum_std::val_real(): Assertion `nr >= 0.0' fails on query with STDDEV_POP, ROUND and variable # SELECT STDDEV_POP(ROUND(0,@A:=2009)) FROM (SELECT 1 UNION SELECT 2) fake_table; STDDEV_POP(ROUND(0,@A:=2009)) 0.0000 # # Start of 10.1 tests # # # MDEV-17643 Assertion `nr >= 0.0' failed in Item_sum_std::val_real() # CREATE TABLE t1 ( pk int NOT NULL, i1 int NOT NULL, d1 date NOT NULL, t1 time); INSERT INTO t1 VALUES (7,9,'2007-08-15','03:55:02'),(8,7,'1993-06-05','04:17:51'),(9,7,'2034-07-01','17:31:12'),(10,0,'1998-08-24','08:09:27'); SELECT DISTINCT STDDEV_SAMP(EXPORT_SET(t1, -1379790335835635712, (i1 + 'o'), (MD5(d1)))) as exp FROM t1; exp NULL Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'o' Warning 1292 Truncated incorrect DOUBLE value: '98e466c7ff40fe6b95cde24200f376303-13797903358356357128e466c7ff40fe6b95cde24200f376303-13797903358356357128e466c7ff40fe6b95cde...' Warning 1292 Truncated incorrect DOUBLE value: 'o' Warning 1292 Truncated incorrect DOUBLE value: '-1379790335835635712e315457d879863c6ccf2ddee5562fc24-1379790335835635712e315457d879863c6ccf2ddee5562fc24-1379790335835635712e...' Warning 1292 Truncated incorrect DOUBLE value: 'o' Warning 1292 Truncated incorrect DOUBLE value: '7b4dd517b633f1f6304b773523b5279747b4dd517b633f1f6304b773523b5279747b4dd517b633f1f6304b773523b527974-1379790335835635712b4dd51...' Warning 1292 Truncated incorrect DOUBLE value: 'o' Warning 1292 Truncated incorrect DOUBLE value: '-1379790335835635712b0e107767ea830fd3318893e40412a43-1379790335835635712b0e107767ea830fd3318893e40412a43-1379790335835635712b...' DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(128)); INSERT INTO t1 VALUES ('1e310'); INSERT INTO t1 VALUES ('-1e310'); INSERT INTO t1 VALUES ('0'); SELECT STDDEV_SAMP(a) FROM t1; STDDEV_SAMP(a) NULL Warnings: Warning 1292 Truncated incorrect DOUBLE value: '1e310' Warning 1292 Truncated incorrect DOUBLE value: '-1e310' DROP TABLE t1; CREATE OR REPLACE TABLE t1 (a DOUBLE); INSERT INTO t1 VALUES (1.7e+308); INSERT INTO t1 VALUES (-1.7e+308); INSERT INTO t1 VALUES (0); SELECT STDDEV_SAMP(a) FROM t1; STDDEV_SAMP(a) NULL DROP TABLE t1; # # MDEV-21977 main.func_math fails due to undefined behaviour # SELECT 9223372036854775808 DIV 1; 9223372036854775808 DIV 1 9223372036854775808 SELECT 9223372036854775808 DIV -1; ERROR 22003: BIGINT UNSIGNED value is out of range in '9223372036854775808 DIV -1' SELECT -9223372036854775808 DIV 1; -9223372036854775808 DIV 1 -9223372036854775808 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 9223372036854775808 MOD -1; 9223372036854775808 MOD -1 0 SELECT -9223372036854775808 MOD 1; -9223372036854775808 MOD 1 0 SELECT -9223372036854775808 MOD -1; -9223372036854775808 MOD -1 0 SELECT 1 MOD 9223372036854775808; 1 MOD 9223372036854775808 1 SELECT -1 MOD 9223372036854775808; -1 MOD 9223372036854775808 -1 SELECT 1 MOD -9223372036854775808; 1 MOD -9223372036854775808 1 SELECT -1 MOD -9223372036854775808; -1 MOD -9223372036854775808 -1 SELECT 9223372036854775808 MOD 9223372036854775808; 9223372036854775808 MOD 9223372036854775808 0 SELECT 9223372036854775808 MOD -9223372036854775808; 9223372036854775808 MOD -9223372036854775808 0 SELECT -9223372036854775808 MOD 9223372036854775808; -9223372036854775808 MOD 9223372036854775808 0 SELECT -9223372036854775808 MOD -9223372036854775808; -9223372036854775808 MOD -9223372036854775808 0 # # MDEV-22502 MDB crashes in CREATE TABLE AS SELECT when the precision of returning type = 0 # CREATE TABLE t1 (d decimal(5,5)); INSERT INTO t1 VALUES (0.55555); SELECT TRUNCATE(d,0) FROM t1; TRUNCATE(d,0) 0 CREATE TABLE t2 AS SELECT TRUNCATE(d,0) FROM t1; SELECT * FROM t2; TRUNCATE(d,0) 0 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `TRUNCATE(d,0)` decimal(1,0) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1, t2; # # MDEV-22503 MDB limits DECIMAL column precision to 16 doing CTAS with floor/ceil over DECIMAL(X,Y) where X > 16 # CREATE TABLE t44 (d1 decimal(38,0) DEFAULT NULL); INSERT INTO t44 VALUES (12345678901234567890123456789012345678); SELECT FLOOR(d1) FROM t44; FLOOR(d1) 12345678901234567890123456789012345678 CREATE TABLE t45 AS SELECT FLOOR(d1) FROM t44; SELECT * FROM t45; FLOOR(d1) 12345678901234567890123456789012345678 SHOW CREATE TABLE t45; Table Create Table t45 CREATE TABLE `t45` ( `FLOOR(d1)` decimal(38,0) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t44, t45; CREATE PROCEDURE p1(prec INT, scale INT) BEGIN DECLARE maxval VARCHAR(128) DEFAULT ''; SET @type= CONCAT('DECIMAL(', prec, ',', scale,')'); SET @stmt= CONCAT('CREATE TABLE t1 (a ', @type, ',b ', @type, 'unsigned)'); PREPARE stmt FROM @stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET maxval= CONCAT(REPEAT('9', prec-scale), '.', REPEAT('9',scale)); INSERT INTO t1 VALUES (maxval, maxval); CREATE TABLE t2 AS SELECT a, b, FLOOR(a) AS fa, FLOOR(b) AS fb FROM t1; SHOW CREATE TABLE t2; SELECT * FROM t2; DROP TABLE t1, t2; END; $$ CREATE PROCEDURE p2(prec INT) BEGIN DECLARE scale INT DEFAULT 0; WHILE scale < prec AND scale <= 30 DO CALL p1(prec, scale); SET scale= scale + 1; END WHILE; END; $$ CALL p2(38); Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,0) DEFAULT NULL, `b` decimal(38,0) unsigned DEFAULT NULL, `fa` decimal(38,0) DEFAULT NULL, `fb` decimal(38,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 99999999999999999999999999999999999999 b 99999999999999999999999999999999999999 fa 99999999999999999999999999999999999999 fb 99999999999999999999999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,1) DEFAULT NULL, `b` decimal(38,1) unsigned DEFAULT NULL, `fa` decimal(38,0) DEFAULT NULL, `fb` decimal(37,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 9999999999999999999999999999999999999.9 b 9999999999999999999999999999999999999.9 fa 9999999999999999999999999999999999999 fb 9999999999999999999999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,2) DEFAULT NULL, `b` decimal(38,2) unsigned DEFAULT NULL, `fa` decimal(37,0) DEFAULT NULL, `fb` decimal(36,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 999999999999999999999999999999999999.99 b 999999999999999999999999999999999999.99 fa 999999999999999999999999999999999999 fb 999999999999999999999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,3) DEFAULT NULL, `b` decimal(38,3) unsigned DEFAULT NULL, `fa` decimal(36,0) DEFAULT NULL, `fb` decimal(35,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 99999999999999999999999999999999999.999 b 99999999999999999999999999999999999.999 fa 99999999999999999999999999999999999 fb 99999999999999999999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,4) DEFAULT NULL, `b` decimal(38,4) unsigned DEFAULT NULL, `fa` decimal(35,0) DEFAULT NULL, `fb` decimal(34,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 9999999999999999999999999999999999.9999 b 9999999999999999999999999999999999.9999 fa 9999999999999999999999999999999999 fb 9999999999999999999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,5) DEFAULT NULL, `b` decimal(38,5) unsigned DEFAULT NULL, `fa` decimal(34,0) DEFAULT NULL, `fb` decimal(33,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 999999999999999999999999999999999.99999 b 999999999999999999999999999999999.99999 fa 999999999999999999999999999999999 fb 999999999999999999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,6) DEFAULT NULL, `b` decimal(38,6) unsigned DEFAULT NULL, `fa` decimal(33,0) DEFAULT NULL, `fb` decimal(32,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 99999999999999999999999999999999.999999 b 99999999999999999999999999999999.999999 fa 99999999999999999999999999999999 fb 99999999999999999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,7) DEFAULT NULL, `b` decimal(38,7) unsigned DEFAULT NULL, `fa` decimal(32,0) DEFAULT NULL, `fb` decimal(31,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 9999999999999999999999999999999.9999999 b 9999999999999999999999999999999.9999999 fa 9999999999999999999999999999999 fb 9999999999999999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,8) DEFAULT NULL, `b` decimal(38,8) unsigned DEFAULT NULL, `fa` decimal(31,0) DEFAULT NULL, `fb` decimal(30,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 999999999999999999999999999999.99999999 b 999999999999999999999999999999.99999999 fa 999999999999999999999999999999 fb 999999999999999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,9) DEFAULT NULL, `b` decimal(38,9) unsigned DEFAULT NULL, `fa` decimal(30,0) DEFAULT NULL, `fb` decimal(29,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 99999999999999999999999999999.999999999 b 99999999999999999999999999999.999999999 fa 99999999999999999999999999999 fb 99999999999999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,10) DEFAULT NULL, `b` decimal(38,10) unsigned DEFAULT NULL, `fa` decimal(29,0) DEFAULT NULL, `fb` decimal(28,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 9999999999999999999999999999.9999999999 b 9999999999999999999999999999.9999999999 fa 9999999999999999999999999999 fb 9999999999999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,11) DEFAULT NULL, `b` decimal(38,11) unsigned DEFAULT NULL, `fa` decimal(28,0) DEFAULT NULL, `fb` decimal(27,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 999999999999999999999999999.99999999999 b 999999999999999999999999999.99999999999 fa 999999999999999999999999999 fb 999999999999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,12) DEFAULT NULL, `b` decimal(38,12) unsigned DEFAULT NULL, `fa` decimal(27,0) DEFAULT NULL, `fb` decimal(26,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 99999999999999999999999999.999999999999 b 99999999999999999999999999.999999999999 fa 99999999999999999999999999 fb 99999999999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,13) DEFAULT NULL, `b` decimal(38,13) unsigned DEFAULT NULL, `fa` decimal(26,0) DEFAULT NULL, `fb` decimal(25,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 9999999999999999999999999.9999999999999 b 9999999999999999999999999.9999999999999 fa 9999999999999999999999999 fb 9999999999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,14) DEFAULT NULL, `b` decimal(38,14) unsigned DEFAULT NULL, `fa` decimal(25,0) DEFAULT NULL, `fb` decimal(24,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 999999999999999999999999.99999999999999 b 999999999999999999999999.99999999999999 fa 999999999999999999999999 fb 999999999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,15) DEFAULT NULL, `b` decimal(38,15) unsigned DEFAULT NULL, `fa` decimal(24,0) DEFAULT NULL, `fb` decimal(23,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 99999999999999999999999.999999999999999 b 99999999999999999999999.999999999999999 fa 99999999999999999999999 fb 99999999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,16) DEFAULT NULL, `b` decimal(38,16) unsigned DEFAULT NULL, `fa` decimal(23,0) DEFAULT NULL, `fb` decimal(22,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 9999999999999999999999.9999999999999999 b 9999999999999999999999.9999999999999999 fa 9999999999999999999999 fb 9999999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,17) DEFAULT NULL, `b` decimal(38,17) unsigned DEFAULT NULL, `fa` decimal(22,0) DEFAULT NULL, `fb` decimal(21,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 999999999999999999999.99999999999999999 b 999999999999999999999.99999999999999999 fa 999999999999999999999 fb 999999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,18) DEFAULT NULL, `b` decimal(38,18) unsigned DEFAULT NULL, `fa` decimal(21,0) DEFAULT NULL, `fb` decimal(20,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 99999999999999999999.999999999999999999 b 99999999999999999999.999999999999999999 fa 99999999999999999999 fb 99999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,19) DEFAULT NULL, `b` decimal(38,19) unsigned DEFAULT NULL, `fa` decimal(20,0) DEFAULT NULL, `fb` decimal(19,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 9999999999999999999.9999999999999999999 b 9999999999999999999.9999999999999999999 fa 9999999999999999999 fb 9999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,20) DEFAULT NULL, `b` decimal(38,20) unsigned DEFAULT NULL, `fa` decimal(19,0) DEFAULT NULL, `fb` bigint(18) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 999999999999999999.99999999999999999999 b 999999999999999999.99999999999999999999 fa 999999999999999999 fb 999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,21) DEFAULT NULL, `b` decimal(38,21) unsigned DEFAULT NULL, `fa` bigint(19) DEFAULT NULL, `fb` bigint(17) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 99999999999999999.999999999999999999999 b 99999999999999999.999999999999999999999 fa 99999999999999999 fb 99999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,22) DEFAULT NULL, `b` decimal(38,22) unsigned DEFAULT NULL, `fa` bigint(18) DEFAULT NULL, `fb` bigint(16) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 9999999999999999.9999999999999999999999 b 9999999999999999.9999999999999999999999 fa 9999999999999999 fb 9999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,23) DEFAULT NULL, `b` decimal(38,23) unsigned DEFAULT NULL, `fa` bigint(17) DEFAULT NULL, `fb` bigint(15) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 999999999999999.99999999999999999999999 b 999999999999999.99999999999999999999999 fa 999999999999999 fb 999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,24) DEFAULT NULL, `b` decimal(38,24) unsigned DEFAULT NULL, `fa` bigint(16) DEFAULT NULL, `fb` bigint(14) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 99999999999999.999999999999999999999999 b 99999999999999.999999999999999999999999 fa 99999999999999 fb 99999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,25) DEFAULT NULL, `b` decimal(38,25) unsigned DEFAULT NULL, `fa` bigint(15) DEFAULT NULL, `fb` bigint(13) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 9999999999999.9999999999999999999999999 b 9999999999999.9999999999999999999999999 fa 9999999999999 fb 9999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,26) DEFAULT NULL, `b` decimal(38,26) unsigned DEFAULT NULL, `fa` bigint(14) DEFAULT NULL, `fb` bigint(12) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 999999999999.99999999999999999999999999 b 999999999999.99999999999999999999999999 fa 999999999999 fb 999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,27) DEFAULT NULL, `b` decimal(38,27) unsigned DEFAULT NULL, `fa` bigint(13) DEFAULT NULL, `fb` bigint(11) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 99999999999.999999999999999999999999999 b 99999999999.999999999999999999999999999 fa 99999999999 fb 99999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,28) DEFAULT NULL, `b` decimal(38,28) unsigned DEFAULT NULL, `fa` bigint(12) DEFAULT NULL, `fb` bigint(10) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 9999999999.9999999999999999999999999999 b 9999999999.9999999999999999999999999999 fa 9999999999 fb 9999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,29) DEFAULT NULL, `b` decimal(38,29) unsigned DEFAULT NULL, `fa` bigint(11) DEFAULT NULL, `fb` int(9) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 999999999.99999999999999999999999999999 b 999999999.99999999999999999999999999999 fa 999999999 fb 999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(38,30) DEFAULT NULL, `b` decimal(38,30) unsigned DEFAULT NULL, `fa` int(10) DEFAULT NULL, `fb` int(8) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 99999999.999999999999999999999999999999 b 99999999.999999999999999999999999999999 fa 99999999 fb 99999999 CALL p2(30); Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(30,0) DEFAULT NULL, `b` decimal(30,0) unsigned DEFAULT NULL, `fa` decimal(30,0) DEFAULT NULL, `fb` decimal(30,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 999999999999999999999999999999 b 999999999999999999999999999999 fa 999999999999999999999999999999 fb 999999999999999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(30,1) DEFAULT NULL, `b` decimal(30,1) unsigned DEFAULT NULL, `fa` decimal(30,0) DEFAULT NULL, `fb` decimal(29,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 99999999999999999999999999999.9 b 99999999999999999999999999999.9 fa 99999999999999999999999999999 fb 99999999999999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(30,2) DEFAULT NULL, `b` decimal(30,2) unsigned DEFAULT NULL, `fa` decimal(29,0) DEFAULT NULL, `fb` decimal(28,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 9999999999999999999999999999.99 b 9999999999999999999999999999.99 fa 9999999999999999999999999999 fb 9999999999999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(30,3) DEFAULT NULL, `b` decimal(30,3) unsigned DEFAULT NULL, `fa` decimal(28,0) DEFAULT NULL, `fb` decimal(27,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 999999999999999999999999999.999 b 999999999999999999999999999.999 fa 999999999999999999999999999 fb 999999999999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(30,4) DEFAULT NULL, `b` decimal(30,4) unsigned DEFAULT NULL, `fa` decimal(27,0) DEFAULT NULL, `fb` decimal(26,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 99999999999999999999999999.9999 b 99999999999999999999999999.9999 fa 99999999999999999999999999 fb 99999999999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(30,5) DEFAULT NULL, `b` decimal(30,5) unsigned DEFAULT NULL, `fa` decimal(26,0) DEFAULT NULL, `fb` decimal(25,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 9999999999999999999999999.99999 b 9999999999999999999999999.99999 fa 9999999999999999999999999 fb 9999999999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(30,6) DEFAULT NULL, `b` decimal(30,6) unsigned DEFAULT NULL, `fa` decimal(25,0) DEFAULT NULL, `fb` decimal(24,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 999999999999999999999999.999999 b 999999999999999999999999.999999 fa 999999999999999999999999 fb 999999999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(30,7) DEFAULT NULL, `b` decimal(30,7) unsigned DEFAULT NULL, `fa` decimal(24,0) DEFAULT NULL, `fb` decimal(23,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 99999999999999999999999.9999999 b 99999999999999999999999.9999999 fa 99999999999999999999999 fb 99999999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(30,8) DEFAULT NULL, `b` decimal(30,8) unsigned DEFAULT NULL, `fa` decimal(23,0) DEFAULT NULL, `fb` decimal(22,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 9999999999999999999999.99999999 b 9999999999999999999999.99999999 fa 9999999999999999999999 fb 9999999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(30,9) DEFAULT NULL, `b` decimal(30,9) unsigned DEFAULT NULL, `fa` decimal(22,0) DEFAULT NULL, `fb` decimal(21,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 999999999999999999999.999999999 b 999999999999999999999.999999999 fa 999999999999999999999 fb 999999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(30,10) DEFAULT NULL, `b` decimal(30,10) unsigned DEFAULT NULL, `fa` decimal(21,0) DEFAULT NULL, `fb` decimal(20,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 99999999999999999999.9999999999 b 99999999999999999999.9999999999 fa 99999999999999999999 fb 99999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(30,11) DEFAULT NULL, `b` decimal(30,11) unsigned DEFAULT NULL, `fa` decimal(20,0) DEFAULT NULL, `fb` decimal(19,0) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 9999999999999999999.99999999999 b 9999999999999999999.99999999999 fa 9999999999999999999 fb 9999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(30,12) DEFAULT NULL, `b` decimal(30,12) unsigned DEFAULT NULL, `fa` decimal(19,0) DEFAULT NULL, `fb` bigint(18) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 999999999999999999.999999999999 b 999999999999999999.999999999999 fa 999999999999999999 fb 999999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(30,13) DEFAULT NULL, `b` decimal(30,13) unsigned DEFAULT NULL, `fa` bigint(19) DEFAULT NULL, `fb` bigint(17) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 99999999999999999.9999999999999 b 99999999999999999.9999999999999 fa 99999999999999999 fb 99999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(30,14) DEFAULT NULL, `b` decimal(30,14) unsigned DEFAULT NULL, `fa` bigint(18) DEFAULT NULL, `fb` bigint(16) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 9999999999999999.99999999999999 b 9999999999999999.99999999999999 fa 9999999999999999 fb 9999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(30,15) DEFAULT NULL, `b` decimal(30,15) unsigned DEFAULT NULL, `fa` bigint(17) DEFAULT NULL, `fb` bigint(15) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 999999999999999.999999999999999 b 999999999999999.999999999999999 fa 999999999999999 fb 999999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(30,16) DEFAULT NULL, `b` decimal(30,16) unsigned DEFAULT NULL, `fa` bigint(16) DEFAULT NULL, `fb` bigint(14) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 99999999999999.9999999999999999 b 99999999999999.9999999999999999 fa 99999999999999 fb 99999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(30,17) DEFAULT NULL, `b` decimal(30,17) unsigned DEFAULT NULL, `fa` bigint(15) DEFAULT NULL, `fb` bigint(13) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 9999999999999.99999999999999999 b 9999999999999.99999999999999999 fa 9999999999999 fb 9999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(30,18) DEFAULT NULL, `b` decimal(30,18) unsigned DEFAULT NULL, `fa` bigint(14) DEFAULT NULL, `fb` bigint(12) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 999999999999.999999999999999999 b 999999999999.999999999999999999 fa 999999999999 fb 999999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(30,19) DEFAULT NULL, `b` decimal(30,19) unsigned DEFAULT NULL, `fa` bigint(13) DEFAULT NULL, `fb` bigint(11) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 99999999999.9999999999999999999 b 99999999999.9999999999999999999 fa 99999999999 fb 99999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(30,20) DEFAULT NULL, `b` decimal(30,20) unsigned DEFAULT NULL, `fa` bigint(12) DEFAULT NULL, `fb` bigint(10) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 9999999999.99999999999999999999 b 9999999999.99999999999999999999 fa 9999999999 fb 9999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(30,21) DEFAULT NULL, `b` decimal(30,21) unsigned DEFAULT NULL, `fa` bigint(11) DEFAULT NULL, `fb` int(9) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 999999999.999999999999999999999 b 999999999.999999999999999999999 fa 999999999 fb 999999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(30,22) DEFAULT NULL, `b` decimal(30,22) unsigned DEFAULT NULL, `fa` int(10) DEFAULT NULL, `fb` int(8) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 99999999.9999999999999999999999 b 99999999.9999999999999999999999 fa 99999999 fb 99999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(30,23) DEFAULT NULL, `b` decimal(30,23) unsigned DEFAULT NULL, `fa` int(9) DEFAULT NULL, `fb` int(7) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 9999999.99999999999999999999999 b 9999999.99999999999999999999999 fa 9999999 fb 9999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(30,24) DEFAULT NULL, `b` decimal(30,24) unsigned DEFAULT NULL, `fa` int(8) DEFAULT NULL, `fb` int(6) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 999999.999999999999999999999999 b 999999.999999999999999999999999 fa 999999 fb 999999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(30,25) DEFAULT NULL, `b` decimal(30,25) unsigned DEFAULT NULL, `fa` int(7) DEFAULT NULL, `fb` int(5) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 99999.9999999999999999999999999 b 99999.9999999999999999999999999 fa 99999 fb 99999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(30,26) DEFAULT NULL, `b` decimal(30,26) unsigned DEFAULT NULL, `fa` int(6) DEFAULT NULL, `fb` int(4) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 9999.99999999999999999999999999 b 9999.99999999999999999999999999 fa 9999 fb 9999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(30,27) DEFAULT NULL, `b` decimal(30,27) unsigned DEFAULT NULL, `fa` int(5) DEFAULT NULL, `fb` int(3) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 999.999999999999999999999999999 b 999.999999999999999999999999999 fa 999 fb 999 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(30,28) DEFAULT NULL, `b` decimal(30,28) unsigned DEFAULT NULL, `fa` int(4) DEFAULT NULL, `fb` int(2) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 99.9999999999999999999999999999 b 99.9999999999999999999999999999 fa 99 fb 99 Table t2 Create Table CREATE TABLE `t2` ( `a` decimal(30,29) DEFAULT NULL, `b` decimal(30,29) unsigned DEFAULT NULL, `fa` int(3) DEFAULT NULL, `fb` int(1) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci a 9.99999999999999999999999999999 b 9.99999999999999999999999999999 fa 9 fb 9 DROP PROCEDURE p2; DROP PROCEDURE p1; # # End of 10.1 tests # # # Start of 10.2 tests # # Test zero select 0=0, 0=-0, 0.0= -0.0, 0.0 = -(0.0), 0.0E1=-0.0E1, 0.0E1=-(0.0E1); 0=0 0=-0 0.0= -0.0 0.0 = -(0.0) 0.0E1=-0.0E1 0.0E1=-(0.0E1) 1 1 1 1 1 1 # # CRC32 tests # select CRC32(NULL), CRC32(''), CRC32('MySQL'), CRC32('mysql'), CRC32('01234567'), CRC32('012345678'), CRC32(REPEAT('ABCDEfghij', 20)), CRC32(REPEAT('0123456789', 200)); CRC32(NULL) CRC32('') CRC32('MySQL') CRC32('mysql') CRC32('01234567') CRC32('012345678') CRC32(REPEAT('ABCDEfghij', 20)) CRC32(REPEAT('0123456789', 200)) NULL 0 3259397556 2501908538 763378421 939184570 3823776386 1428305034 # # Start of 10.3 tests # # # MDEV-12000 ROUND(expr,const_expr_returning_NULL) creates DOUBLE(0,0) # CREATE OR REPLACE TABLE t1 AS SELECT ROUND(10,NULL) AS c1, ROUND(10.1,NULL) AS c2, ROUND(10e0,NULL) AS c3; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(2) DEFAULT NULL, `c2` double DEFAULT NULL, `c3` double DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1; # # MDEV-12857 Out-of-range errors on CREATE..SELECT 2222222222 DIV 1 # SET sql_mode=STRICT_ALL_TABLES; CREATE OR REPLACE TABLE t1 AS SELECT 2 DIV 1 AS d01, 222222222 DIV 1 AS d09, 2222222222 DIV 1 AS d10; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `d01` int(1) DEFAULT NULL, `d09` int(9) DEFAULT NULL, `d10` bigint(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SELECT * FROM t1; d01 2 d09 222222222 d10 2222222222 DROP TABLE t1; SET sql_mode=DEFAULT; # # MDEV-12858 Out-of-range error for CREATE..SELECT unsigned_int_column+1 # SET sql_mode=STRICT_ALL_TABLES; CREATE OR REPLACE TABLE t1 (a INT UNSIGNED NOT NULL); INSERT INTO t1 VALUES (0xFFFFFFFF); CREATE OR REPLACE TABLE t2 AS SELECT a+1 AS a FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `a` bigint(11) unsigned NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SELECT a, HEX(a) FROM t2; a HEX(a) 4294967296 100000000 DROP TABLE t2; DROP TABLE t1; SET sql_mode=DEFAULT; # # 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 SELECT CRC32C(NULL), CRC32C(''), CRC32C('MariaDB'), CRC32C('mariadb'); CRC32C(NULL) CRC32C('') CRC32C('MariaDB') CRC32C('mariadb') NULL 0 809606978 1378644259 SELECT CRC32(NULL,1),CRC32C(NULL,1), CRC32(1,''), CRC32C(1,''); CRC32(NULL,1) CRC32C(NULL,1) CRC32(1,'') CRC32C(1,'') NULL NULL 1 1 SELECT CRC32(42,''),CRC32C(42,''),CRC32('42',''),CRC32C('42',''); CRC32(42,'') CRC32C(42,'') CRC32('42','') CRC32C('42','') 42 42 42 42 SELECT CRC32(42,NULL),CRC32C(42,NULL); CRC32(42,NULL) CRC32C(42,NULL) NULL NULL SELECT CRC32 ('5c',''),CRC32 ('5c',0),CRC32 ('5c', '0'),CRC32 ('5c',NULL); CRC32 ('5c','') CRC32 ('5c',0) CRC32 ('5c', '0') CRC32 ('5c',NULL) 5 2226203566 2226203566 NULL Warnings: Warning 1292 Truncated incorrect INTEGER value: '5c' Warning 1292 Truncated incorrect INTEGER value: '5c' Warning 1292 Truncated incorrect INTEGER value: '5c' Warning 1292 Truncated incorrect INTEGER value: '5c' SELECT CRC32C('5c',''),CRC32C('5c',0),CRC32C('5c', '0'),CRC32C('5c',NULL); CRC32C('5c','') CRC32C('5c',0) CRC32C('5c', '0') CRC32C('5c',NULL) 5 1466896124 1466896124 NULL Warnings: Warning 1292 Truncated incorrect INTEGER value: '5c' Warning 1292 Truncated incorrect INTEGER value: '5c' Warning 1292 Truncated incorrect INTEGER value: '5c' Warning 1292 Truncated incorrect INTEGER value: '5c' SELECT CRC32('MariaDB',NULL),CRC32C('MariaDB',NULL); CRC32('MariaDB',NULL) CRC32C('MariaDB',NULL) NULL NULL Warnings: Warning 1292 Truncated incorrect INTEGER value: 'MariaDB' Warning 1292 Truncated incorrect INTEGER value: 'MariaDB' SELECT CRC32(CRC32('MySQL'),''),CRC32(CRC32('My'),'SQL'),CRC32(0,'MySQL'); CRC32(CRC32('MySQL'),'') CRC32(CRC32('My'),'SQL') CRC32(0,'MySQL') 3259397556 3259397556 3259397556 SELECT CRC32C(CRC32C('MariaDB'),''),CRC32C(CRC32C('Maria'),'DB'),CRC32C(0,'MariaDB'); CRC32C(CRC32C('MariaDB'),'') CRC32C(CRC32C('Maria'),'DB') CRC32C(0,'MariaDB') 809606978 809606978 809606978 select crc32(0,'My','SQL'); ERROR 42000: Incorrect parameter count in the call to native function 'crc32' select crc32c(0,'Maria','DB'); ERROR 42000: Incorrect parameter count in the call to native function 'crc32c' select crc32(); ERROR 42000: Incorrect parameter count in the call to native function 'crc32' select crc32c(); ERROR 42000: Incorrect parameter count in the call to native function 'crc32c' select crc32('' as empty); ERROR 42000: Incorrect parameters in the call to native function 'crc32' select crc32c('' as empty); ERROR 42000: Incorrect parameters in the call to native function 'crc32c' select crc32(0, '' as empty); ERROR 42000: Incorrect parameters in the call to native function 'crc32' select crc32c(0, '' as empty); ERROR 42000: Incorrect parameters in the call to native function 'crc32c' select crc32(0 as zero, ''); ERROR 42000: Incorrect parameters in the call to native function 'crc32' select crc32c(0 as zero, ''); ERROR 42000: Incorrect parameters in the call to native function 'crc32c' CREATE TEMPORARY TABLE t (a CHAR(2), i INT UNSIGNED, c INT UNSIGNED AS (CRC32C(i,a))); INSERT INTO t (a,i) VALUES ('DB',CRC32C('Maria')); SELECT * FROM t; a i c DB 1253907744 809606978 DROP TEMPORARY TABLE t; select crc32(4294967296,''), hex(char(4294967296)); crc32(4294967296,'') hex(char(4294967296)) 0 00 select crc32(1e100,''), hex(char(1e100)); crc32(1e100,'') hex(char(1e100)) 4294967295 FFFFFFFF select crc32(10.11,''), hex(char(10.11)); crc32(10.11,'') hex(char(10.11)) 10 0A select crc32(-1,''), hex(char(-1)); crc32(-1,'') hex(char(-1)) 4294967295 FFFFFFFF select crc32('',''), hex(char('')); crc32('','') hex(char('')) 0 00 Warnings: Warning 1292 Truncated incorrect INTEGER value: '' Warning 1292 Truncated incorrect INTEGER value: '' select crc32(429496729656755555555555555555555555555555555555555555555555555555555555555555555555555,'a') as x; x 3310005809 Warnings: Warning 1916 Got overflow when converting '' to DECIMAL. Value truncated Warning 1916 Got overflow when converting '99999999999999999999999999999999999999999999999999999999999999999' to INT. Value truncated select crc32(429496729656755555555555555555555555555555555555555555555555555555555555555555555555555,'a') as x; x 3310005809 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)),crc32c(SUM(a)) FROM t; crc32(SUM(a)) crc32c(SUM(a)) 1790921346 3058990603 SELECT crc32(AVG(a)),crc32c(AVG(a)) FROM t GROUP BY b; crc32(AVG(a)) crc32c(AVG(a)) 768278432 1816172052 2875100430 1492934094 2875100430 1492934094 SELECT crc32(MAX(b)),crc32c(MAX(b)) FROM t GROUP BY a; crc32(MAX(b)) crc32c(MAX(b)) 2238339752 3833565251 3114057431 4173859780 2238339752 3833565251 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)); 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, CRC32(a) FROM t1; a CRC32(a) {"name" : "goodyear"} 3258555818 {"name" : "verygood-year"} 3616384779 SELECT CRC32(json_query(a, '$.name')) FROM t1 WHERE json_query(a, '$.name') = 'goodyear'; CRC32(json_query(a, '$.name')) 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 stmt1; SET NAMES utf8; CREATE TABLE t1 (a TEXT) CHARACTER SET = utf8; LOAD DATA INFILE '../../std_data/loaddata_utf8.dat' INTO TABLE t1 CHARACTER SET utf8; SELECT HEX(a), CRC32(a) from t1; HEX(a) CRC32(a) E4B880E4BA8CE4B889 1785250883 E59B9BE4BA94E585AD 2914501801 E4B883E585ABE4B99D 2374586519 DROP TABLE t1; SET NAMES default; # # MDEV-17759 Assertion `precision > 0' failed in decimal_bin_size upon CREATE TABLE .. SELECT # SET sql_mode=''; CREATE TABLE t1 (d DECIMAL(43,0) UNSIGNED); INSERT INTO t1 VALUES (1); CREATE TABLE t2 AS SELECT NULL DIV d AS d_null, '' DIV d AS d_empty_string, X'32' DIV d AS d_hex_string2, X'3232' DIV d AS d_hex_string4, TIME(0) DIV d AS d_time, CURRENT_DATE DIV d AS d_date, CURRENT_TIMESTAMP DIV d AS d_datetime FROM t1; Warnings: Warning 1292 Truncated incorrect DECIMAL value: '' SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `d_null` int(1) unsigned DEFAULT NULL, `d_empty_string` int(1) unsigned DEFAULT NULL, `d_hex_string2` int(1) unsigned DEFAULT NULL, `d_hex_string4` int(2) unsigned DEFAULT NULL, `d_time` int(7) unsigned DEFAULT NULL, `d_date` int(8) unsigned DEFAULT NULL, `d_datetime` bigint(14) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1, t2; SET sql_mode=DEFAULT; # # MDEV-18150 Assertion `decimals_to_set <= 38' failed in Item_func_round::fix_length_and_dec_decimal # CREATE TABLE t1 (i INT(23)); SELECT ROUND( i, 18446744073709551594 ) AS f FROM t1; f DROP TABLE t1; # # MDEV-22268 virtual longlong Item_func_div::int_op(): Assertion `0' failed in Item_func_div::int_op # SET sql_mode=''; SET @@SESSION.div_precision_increment=0; SELECT UTC_TIME / 0; UTC_TIME / 0 NULL SELECT TIMESTAMP'2001-01-01 00:00:00'/0; TIMESTAMP'2001-01-01 00:00:00'/0 NULL SELECT TIME'00:00:00'/0; TIME'00:00:00'/0 NULL CREATE TABLE t1 AS SELECT UTC_TIME / 0 AS c1, TIMESTAMP'2001-01-01 00:00:00'/0 AS c3, TIME'00:00:00'/0 AS c4; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` decimal(7,0) DEFAULT NULL, `c3` decimal(14,0) DEFAULT NULL, `c4` decimal(7,0) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1; SELECT(-0 * MOD((UTC_TIME / -0)MOD (ATAN('') MOD COT(0)),-0)) MOD (0 DIV 0); ERROR 22003: DOUBLE value is out of range in 'cot(0)' SET @@SESSION.div_precision_increment=DEFAULT; SET sql_mode=DEFAULT; # # End of 10.3 tests # # # Start of 10.4 tests # # # MDEV-20732 max_char_length() doesn't account for FORMAT() with doubles in scientific notation # CREATE OR REPLACE TABLE t1 AS SELECT FORMAT(-1e308,2); SELECT * FROM t1; FORMAT(-1e308,2) -100,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000.00 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `FORMAT(-1e308,2)` varchar(417) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1; CREATE OR REPLACE TABLE t1 AS SELECT FORMAT('-1e308',2); SELECT * FROM t1; FORMAT('-1e308',2) -100,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000.00 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `FORMAT('-1e308',2)` varchar(417) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1; CREATE OR REPLACE TABLE t1 AS SELECT FORMAT(DATE'20191231',0),FORMAT(TIME'99:05:00',0),FORMAT(TIMESTAMP'2019-12-31 23:59:59.123456',0); SELECT * FROM t1; FORMAT(DATE'20191231',0) FORMAT(TIME'99:05:00',0) FORMAT(TIMESTAMP'2019-12-31 23:59:59.123456',0) 20,191,231 990,500 20,191,231,235,959 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `FORMAT(DATE'20191231',0)` varchar(11) DEFAULT NULL, `FORMAT(TIME'99:05:00',0)` varchar(10) DEFAULT NULL, `FORMAT(TIMESTAMP'2019-12-31 23:59:59.123456',0)` varchar(21) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1; CREATE OR REPLACE TABLE t1 (y YEAR); INSERT INTO t1 VALUES ('2099'),('99'); CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(y,0) FROM t1; SELECT * FROM t2; FORMAT(y,0) 2,099 1,999 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `FORMAT(y,0)` varchar(6) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1, t2; CREATE OR REPLACE TABLE t1 (bi BIGINT UNSIGNED, bis BIGINT); INSERT INTO t1 VALUES (18446744073709551615,-9223372036854775808),(0,0); CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(bi,0),FORMAT(bis,0) FROM t1; SELECT * FROM t2; FORMAT(bi,0) FORMAT(bis,0) 18,446,744,073,709,551,615 -9,223,372,036,854,775,808 0 0 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `FORMAT(bi,0)` varchar(27) DEFAULT NULL, `FORMAT(bis,0)` varchar(26) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (d DECIMAL(65,0)); INSERT INTO t1 VALUES (99999999999999999999999999999999999999999999999999999999999999999),(-99999999999999999999999999999999999999999999999999999999999999999),(0); CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(d,0) FROM t1; SELECT * FROM t2; FORMAT(d,0) 99,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999 -99,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999 0 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `FORMAT(d,0)` varchar(87) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 AS SELECT -99999999999999999999999999999999999999999999999999999999999999999 as c1; SELECT * FROM t1; c1 -99999999999999999999999999999999999999999999999999999999999999999 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` decimal(65,0) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1; CREATE OR REPLACE TABLE t1 (b1 BIT(1), b2 BIT(2), b3 BIT(3), b4 BIT(4), b5 BIT(5), b6 BIT(6), b7 BIT(7), b8 BIT(8), b9 BIT(9), b10 BIT(10), b11 BIT(11), b12 BIT(12), b13 BIT(13), b14 BIT(14), b15 BIT(15), b16 BIT(16), b17 BIT(17), b18 BIT(18), b19 BIT(19), b20 BIT(20), b21 BIT(21), b22 BIT(22), b23 BIT(23), b24 BIT(24), b25 BIT(25), b26 BIT(26), b27 BIT(27), b28 BIT(28), b29 BIT(29), b30 BIT(30), b31 BIT(31), b32 BIT(32), b33 BIT(33), b34 BIT(34), b35 BIT(35), b36 BIT(36), b37 BIT(37), b38 BIT(38), b39 BIT(39), b40 BIT(40), b41 BIT(41), b42 BIT(42), b43 BIT(43), b44 BIT(44), b45 BIT(45), b46 BIT(46), b47 BIT(47), b48 BIT(48), b49 BIT(49), b50 BIT(50), b51 BIT(51), b52 BIT(52), b53 BIT(53), b54 BIT(54), b55 BIT(55), b56 BIT(56), b57 BIT(57), b58 BIT(58), b59 BIT(59), b60 BIT(60), b61 BIT(61), b62 BIT(62), b63 BIT(63), b64 BIT(64)); INSERT INTO t1 VALUES(1,3,7,15,31,63,127,255,511,1023,2047,4095,8191,16383, 32767,65535,131071,262143,524287,1048575,2097151,4194303,8388607,16777215, 33554431,67108863,134217727,268435455,536870911,1073741823,2147483647, 4294967295,8589934591,17179869183,34359738367,68719476735,137438953471, 274877906943,549755813887,1099511627775,2199023255551,4398046511103, 8796093022207,17592186044415,35184372088831,70368744177663,140737488355327, 281474976710655,562949953421311,1125899906842623,2251799813685247, 4503599627370495,9007199254740991,18014398509481983,36028797018963967, 72057594037927935,144115188075855871,288230376151711743,576460752303423487, 1152921504606846975,2305843009213693951,4611686018427387903, 9223372036854775807,18446744073709551615); SELECT FORMAT(b1,0), FORMAT(b2,0), FORMAT(b3,0), FORMAT(b4,0), FORMAT(b5,0), FORMAT(b6,0), FORMAT(b7,0), FORMAT(b8,0), FORMAT(b9,0), FORMAT(b10,0), FORMAT(b11,0), FORMAT(b12,0), FORMAT(b13,0), FORMAT(b14,0), FORMAT(b15,0), FORMAT(b16,0), FORMAT(b17,0), FORMAT(b18,0), FORMAT(b19,0), FORMAT(b20,0), FORMAT(b21,0), FORMAT(b22,0), FORMAT(b23,0), FORMAT(b24,0), FORMAT(b25,0), FORMAT(b26,0), FORMAT(b27,0), FORMAT(b28,0), FORMAT(b29,0), FORMAT(b30,0), FORMAT(b31,0), FORMAT(b32,0), FORMAT(b33,0), FORMAT(b34,0), FORMAT(b35,0), FORMAT(b36,0), FORMAT(b37,0), FORMAT(b38,0), FORMAT(b39,0), FORMAT(b40,0), FORMAT(b41,0), FORMAT(b42,0), FORMAT(b43,0), FORMAT(b44,0), FORMAT(b45,0), FORMAT(b46,0), FORMAT(b47,0), FORMAT(b48,0), FORMAT(b49,0), FORMAT(b50,0), FORMAT(b51,0), FORMAT(b52,0), FORMAT(b53,0), FORMAT(b54,0), FORMAT(b55,0), FORMAT(b56,0), FORMAT(b57,0), FORMAT(b58,0), FORMAT(b59,0), FORMAT(b60,0), FORMAT(b61,0), FORMAT(b62,0), FORMAT(b63,0), FORMAT(b64,0) FROM t1; FORMAT(b1,0) 1 FORMAT(b2,0) 3 FORMAT(b3,0) 7 FORMAT(b4,0) 15 FORMAT(b5,0) 31 FORMAT(b6,0) 63 FORMAT(b7,0) 127 FORMAT(b8,0) 255 FORMAT(b9,0) 511 FORMAT(b10,0) 1,023 FORMAT(b11,0) 2,047 FORMAT(b12,0) 4,095 FORMAT(b13,0) 8,191 FORMAT(b14,0) 16,383 FORMAT(b15,0) 32,767 FORMAT(b16,0) 65,535 FORMAT(b17,0) 131,071 FORMAT(b18,0) 262,143 FORMAT(b19,0) 524,287 FORMAT(b20,0) 1,048,575 FORMAT(b21,0) 2,097,151 FORMAT(b22,0) 4,194,303 FORMAT(b23,0) 8,388,607 FORMAT(b24,0) 16,777,215 FORMAT(b25,0) 33,554,431 FORMAT(b26,0) 67,108,863 FORMAT(b27,0) 134,217,727 FORMAT(b28,0) 268,435,455 FORMAT(b29,0) 536,870,911 FORMAT(b30,0) 1,073,741,823 FORMAT(b31,0) 2,147,483,647 FORMAT(b32,0) 4,294,967,295 FORMAT(b33,0) 8,589,934,591 FORMAT(b34,0) 17,179,869,183 FORMAT(b35,0) 34,359,738,367 FORMAT(b36,0) 68,719,476,735 FORMAT(b37,0) 137,438,953,471 FORMAT(b38,0) 274,877,906,943 FORMAT(b39,0) 549,755,813,887 FORMAT(b40,0) 1,099,511,627,775 FORMAT(b41,0) 2,199,023,255,551 FORMAT(b42,0) 4,398,046,511,103 FORMAT(b43,0) 8,796,093,022,207 FORMAT(b44,0) 17,592,186,044,415 FORMAT(b45,0) 35,184,372,088,831 FORMAT(b46,0) 70,368,744,177,663 FORMAT(b47,0) 140,737,488,355,327 FORMAT(b48,0) 281,474,976,710,655 FORMAT(b49,0) 562,949,953,421,311 FORMAT(b50,0) 1,125,899,906,842,623 FORMAT(b51,0) 2,251,799,813,685,247 FORMAT(b52,0) 4,503,599,627,370,495 FORMAT(b53,0) 9,007,199,254,740,991 FORMAT(b54,0) 18,014,398,509,481,983 FORMAT(b55,0) 36,028,797,018,963,967 FORMAT(b56,0) 72,057,594,037,927,935 FORMAT(b57,0) 144,115,188,075,855,871 FORMAT(b58,0) 288,230,376,151,711,743 FORMAT(b59,0) 576,460,752,303,423,487 FORMAT(b60,0) 1,152,921,504,606,846,975 FORMAT(b61,0) 2,305,843,009,213,693,951 FORMAT(b62,0) 4,611,686,018,427,387,903 FORMAT(b63,0) 9,223,372,036,854,775,807 FORMAT(b64,0) 18,446,744,073,709,551,615 DROP TABLE t1; CREATE OR REPLACE TABLE t1 (c1 BIT(1)); INSERT INTO t1 VALUES (b'1'); SELECT HEX(c1) FROM t1; HEX(c1) 1 CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 31 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(2) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(2)); INSERT INTO t1 VALUES (b'11'); SELECT HEX(c1) FROM t1; HEX(c1) 3 CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 33 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(2) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(3)); INSERT INTO t1 VALUES (b'111'); SELECT HEX(c1) FROM t1; HEX(c1) 7 CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 37 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(2) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(4)); INSERT INTO t1 VALUES (b'1111'); SELECT HEX(c1) FROM t1; HEX(c1) F CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 3135 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(3) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(5)); INSERT INTO t1 VALUES (b'11111'); SELECT HEX(c1) FROM t1; HEX(c1) 1F CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 3331 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(3) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(6)); INSERT INTO t1 VALUES (b'111111'); SELECT HEX(c1) FROM t1; HEX(c1) 3F CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 3633 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(3) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(7)); INSERT INTO t1 VALUES (b'1111111'); SELECT HEX(c1) FROM t1; HEX(c1) 7F CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 313237 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(5) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(8)); INSERT INTO t1 VALUES (b'11111111'); SELECT HEX(c1) FROM t1; HEX(c1) FF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 323535 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(5) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(9)); INSERT INTO t1 VALUES (b'111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 1FF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 353131 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(5) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(10)); INSERT INTO t1 VALUES (b'1111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 3FF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 312C303233 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(6) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(11)); INSERT INTO t1 VALUES (b'11111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 7FF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 322C303437 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(6) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(12)); INSERT INTO t1 VALUES (b'111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) FFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 342C303935 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(6) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(13)); INSERT INTO t1 VALUES (b'1111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 1FFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 382C313931 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(6) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(14)); INSERT INTO t1 VALUES (b'11111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 3FFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 31362C333833 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(7) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(15)); INSERT INTO t1 VALUES (b'111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 7FFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 33322C373637 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(7) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(16)); INSERT INTO t1 VALUES (b'1111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) FFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 36352C353335 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(7) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(17)); INSERT INTO t1 VALUES (b'11111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 1FFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 3133312C303731 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(9) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(18)); INSERT INTO t1 VALUES (b'111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 3FFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 3236322C313433 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(9) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(19)); INSERT INTO t1 VALUES (b'1111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 7FFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 3532342C323837 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(9) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(20)); INSERT INTO t1 VALUES (b'11111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) FFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 312C3034382C353735 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(21)); INSERT INTO t1 VALUES (b'111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 1FFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 322C3039372C313531 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(22)); INSERT INTO t1 VALUES (b'1111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 3FFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 342C3139342C333033 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(23)); INSERT INTO t1 VALUES (b'11111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 7FFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 382C3338382C363037 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(24)); INSERT INTO t1 VALUES (b'111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) FFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 31362C3737372C323135 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(25)); INSERT INTO t1 VALUES (b'1111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 1FFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 33332C3535342C343331 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(26)); INSERT INTO t1 VALUES (b'11111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 3FFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 36372C3130382C383633 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(27)); INSERT INTO t1 VALUES (b'111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 7FFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 3133342C3231372C373237 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(13) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(28)); INSERT INTO t1 VALUES (b'1111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) FFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 3236382C3433352C343535 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(13) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(29)); INSERT INTO t1 VALUES (b'11111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 1FFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 3533362C3837302C393131 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(13) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(30)); INSERT INTO t1 VALUES (b'111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 3FFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 312C3037332C3734312C383233 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(14) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(31)); INSERT INTO t1 VALUES (b'1111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 7FFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 322C3134372C3438332C363437 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(14) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(32)); INSERT INTO t1 VALUES (b'11111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) FFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 342C3239342C3936372C323935 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(14) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(33)); INSERT INTO t1 VALUES (b'111111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 1FFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 382C3538392C3933342C353931 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(14) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(34)); INSERT INTO t1 VALUES (b'1111111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 3FFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 31372C3137392C3836392C313833 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(15) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(35)); INSERT INTO t1 VALUES (b'11111111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 7FFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 33342C3335392C3733382C333637 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(15) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(36)); INSERT INTO t1 VALUES (b'111111111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) FFFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 36382C3731392C3437362C373335 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(15) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(37)); INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 1FFFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 3133372C3433382C3935332C343731 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(17) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(38)); INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 3FFFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 3237342C3837372C3930362C393433 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(17) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(39)); INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 7FFFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 3534392C3735352C3831332C383837 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(17) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(40)); INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) FFFFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 312C3039392C3531312C3632372C373735 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(18) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(41)); INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 1FFFFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 322C3139392C3032332C3235352C353531 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(18) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(42)); INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 3FFFFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 342C3339382C3034362C3531312C313033 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(18) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(43)); INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 7FFFFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 382C3739362C3039332C3032322C323037 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(18) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(44)); INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) FFFFFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 31372C3539322C3138362C3034342C343135 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(19) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(45)); INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 1FFFFFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 33352C3138342C3337322C3038382C383331 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(19) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(46)); INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 3FFFFFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 37302C3336382C3734342C3137372C363633 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(19) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(47)); INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 7FFFFFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 3134302C3733372C3438382C3335352C333237 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(21) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(48)); INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) FFFFFFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 3238312C3437342C3937362C3731302C363535 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(21) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(49)); INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 1FFFFFFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 3536322C3934392C3935332C3432312C333131 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(21) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(50)); INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 3FFFFFFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 312C3132352C3839392C3930362C3834322C363233 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(22) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(51)); INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 7FFFFFFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 322C3235312C3739392C3831332C3638352C323437 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(22) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(52)); INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) FFFFFFFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 342C3530332C3539392C3632372C3337302C343935 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(22) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(53)); INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 1FFFFFFFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 392C3030372C3139392C3235342C3734302C393931 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(22) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(54)); INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 3FFFFFFFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 31382C3031342C3339382C3530392C3438312C393833 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(23) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(55)); INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 7FFFFFFFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 33362C3032382C3739372C3031382C3936332C393637 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(23) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(56)); INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) FFFFFFFFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 37322C3035372C3539342C3033372C3932372C393335 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(23) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(57)); INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 1FFFFFFFFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 3134342C3131352C3138382C3037352C3835352C383731 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(25) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(58)); INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 3FFFFFFFFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 3238382C3233302C3337362C3135312C3731312C373433 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(25) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(59)); INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 7FFFFFFFFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 3537362C3436302C3735322C3330332C3432332C343837 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(25) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(60)); INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) FFFFFFFFFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 312C3135322C3932312C3530342C3630362C3834362C393735 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(26) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(61)); INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 1FFFFFFFFFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 322C3330352C3834332C3030392C3231332C3639332C393531 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(26) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(62)); INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 3FFFFFFFFFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 342C3631312C3638362C3031382C3432372C3338372C393033 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(26) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(63)); INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) 7FFFFFFFFFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 392C3232332C3337322C3033362C3835342C3737352C383037 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(26) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (c1 BIT(64)); INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111111111111111111111111'); SELECT HEX(c1) FROM t1; HEX(c1) FFFFFFFFFFFFFFFF CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1; SELECT HEX(c1) FROM t2; HEX(c1) 31382C3434362C3734342C3037332C3730392C3535312C363135 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` varchar(27) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; CREATE OR REPLACE TABLE t1 (f float); INSERT INTO t1 VALUES (3e38), (-3e38), (0), (12.34), (-12.34); CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(f,0) FROM t1; SELECT * FROM t2; FORMAT(f,0) 300,000,000,549,775,580,000,000,000,000,000,000,000 -300,000,000,549,775,580,000,000,000,000,000,000,000 0 12 -12 SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `FORMAT(f,0)` varchar(54) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1,t2; # # MDEV-30932 UBSAN: negation of -X cannot be represented in type 'long long int'; cast to an unsigned type to negate this value to itself in Item_func_mul::int_op and Item_func_round::int_op # SELECT (1 DIV(-1/POW(807,14))*1); ERROR 22003: BIGINT value is out of range in '1 DIV (-1 / pow(807,14))' DO((-9223372036854775808)*(1)); SELECT (-9223372036854775808)*(1); (-9223372036854775808)*(1) -9223372036854775808 SELECT (GET_FORMAT(TIME,'JIS'))DIV(POW(-40,65)DIV(1)*2); ERROR 22003: BIGINT value is out of range in 'pow(-40,65) DIV 1' SELECT -9223372036854775808 MOD 9223372036854775810; -9223372036854775808 MOD 9223372036854775810 -9223372036854775808 CREATE TABLE t1 (c INT); INSERT INTO t1 VALUES(TRUNCATE(0,-1.e+30)); DROP TABLE t1; SELECT TRUNCATE(0, -9223372036854775808); TRUNCATE(0, -9223372036854775808) 0 SELECT GET_FORMAT(TIME,'JIS') DIV ATAN (TRUNCATE (0,'2000000000000000' DIV SIN(1500)*NOW(5))) AS col1; col1 NULL SELECT (GET_FORMAT(TIME,'JIS') DIV ATAN (TRUNCATE (0,'2000000000000000' DIV SIN(1500)*NOW(5))/ROUND(-1)))DIV(-1-LOG2(1))-(-1*POWER(-1,0)) AS col1; col1 NULL # # End of 10.4 tests # # # Start of 10.5 tests # # # MDEV-21278 Assertion `is_unsigned() == attr.unsigned_flag' or Assertion `field.is_sane()' failed # CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT NOW()); INSERT INTO t1 VALUES (NULL),(NULL); SET SESSION SQL_MODE= 'NO_UNSIGNED_SUBTRACTION'; SELECT DISTINCT UUID_SHORT() - a FROM t1; UUID_SHORT() - a xxx xxx CREATE TABLE t2 AS SELECT DISTINCT UUID_SHORT() - a FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `UUID_SHORT() - a` bigint(22) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t2, t1; SET sql_mode=DEFAULT; CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT NOW()); INSERT INTO t1 VALUES (NULL),(NULL); SET SESSION SQL_MODE= 'NO_UNSIGNED_SUBTRACTION'; SELECT UUID_SHORT() - a FROM t1; UUID_SHORT() - a xxx xxx CREATE TABLE t2 AS SELECT UUID_SHORT() - a FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `UUID_SHORT() - a` bigint(22) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t2, t1; # # MDEV-23680 Assertion `data' failed in crcr32_calc_pclmulqdq # SELECT CRC32(ExtractValue('', '/a/b')) AS f; f 0 # # End of 10.5 tests #