diff options
Diffstat (limited to 'mysql-test/main/func_math.result')
| -rw-r--r-- | mysql-test/main/func_math.result | 3715 |
1 files changed, 3715 insertions, 0 deletions
diff --git a/mysql-test/main/func_math.result b/mysql-test/main/func_math.result new file mode 100644 index 00000000..1c6c780e --- /dev/null +++ b/mysql-test/main/func_math.result @@ -0,0 +1,3715 @@ +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('<img src_x0=x onerror="javascript:alert(0)">') 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/></a>', '/a/b')) AS f; +f +0 +# +# End of 10.5 tests +# |