path: root/mysql-test/main/func_math.test

#
# Test of math functions
#

--source include/default_charset.inc

--disable_warnings                                                              
drop table if exists t1;                                                        
--enable_warnings                                                               

select floor(5.5),floor(-5.5);
explain extended select floor(5.5),floor(-5.5);
select ceiling(5.5),ceiling(-5.5);
explain extended select ceiling(5.5),ceiling(-5.5);
select truncate(52.64,1),truncate(52.64,2),truncate(52.64,-1),truncate(52.64,-2), truncate(-52.64,1),truncate(-52.64,-1);
explain extended select truncate(52.64,1),truncate(52.64,2),truncate(52.64,-1),truncate(52.64,-2), truncate(-52.64,1),truncate(-52.64,-1);
select round(5.5),round(-5.5);
explain extended select round(5.5),round(-5.5);
select round(5.64,1),round(5.64,2),round(5.64,-1),round(5.64,-2);
select abs(-10), sign(-5), sign(5), sign(0);
explain extended select abs(-10), sign(-5), sign(5), sign(0);
--replace_result  2.0000000000000004 2
select log(exp(10)),exp(log(sqrt(10))*2),log(-1),log(NULL),log(1,1),log(3,9),log(-1,2),log(NULL,2);
explain extended select log(exp(10)),exp(log(sqrt(10))*2),log(-1),log(NULL),log(1,1),log(3,9),log(-1,2),log(NULL,2);
select ln(exp(10)),exp(ln(sqrt(10))*2),ln(-1),ln(0),ln(NULL);
explain extended select ln(exp(10)),exp(ln(sqrt(10))*2),ln(-1),ln(0),ln(NULL);
select log2(8),log2(15),log2(-2),log2(0),log2(NULL);
explain extended select log2(8),log2(15),log2(-2),log2(0),log2(NULL);
select log10(100),log10(18),log10(-4),log10(0),log10(NULL);
explain extended select log10(100),log10(18),log10(-4),log10(0),log10(NULL);
select pow(10,log10(10)),power(2,4);
explain extended select pow(10,log10(10)),power(2,4);
set @@rand_seed1=10000000,@@rand_seed2=1000000;
--disable_ps2_protocol
select rand(999999),rand();
--enable_ps2_protocol
explain extended select rand(999999),rand();
select pi(),format(sin(pi()/2),6),format(cos(pi()/2),6),format(abs(tan(pi())),6),format(cot(1),6),format(asin(1),6),format(acos(0),6),format(atan(1),6);
explain extended select pi(),format(sin(pi()/2),6),format(cos(pi()/2),6),format(abs(tan(pi())),6),format(cot(1),6),format(asin(1),6),format(acos(0),6),format(atan(1),6);
select degrees(pi()),radians(360);

select format(atan(-2, 2), 6);
select format(atan(pi(), 0), 6);
select format(atan2(-2, 2), 6);
select format(atan2(pi(), 0), 6);

#
# Bug #2338 Trignometric arithmatic problems 
#

SELECT ACOS(1.0);
SELECT ASIN(1.0);
SELECT ACOS(0.2*5.0);
SELECT ACOS(0.5*2.0);
SELECT ASIN(0.8+0.2);
SELECT ASIN(1.2-0.2);

#
# Bug #3051 FLOOR returns invalid 
#

# This can't be tested as it's not portable
#select floor(log(4)/log(2));
#select floor(log(8)/log(2));
#select floor(log(16)/log(2));

#
# Bug #9060 (format returns incorrect result)
#
select format(4.55, 1), format(4.551, 1);

explain extended select degrees(pi()),radians(360);

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

--error 1054
select rand(rand);

# End of 4.1 tests

#
# Bug #8459 (FORMAT returns incorrect result)
#
create table t1 (col1 int, col2 decimal(60,30));
insert into t1 values(1,1234567890.12345);
select format(col2,7) from t1;
select format(col2,8) from t1;
insert into t1 values(7,1234567890123456.12345);
select format(col2,6) from t1 where col1=7;
drop table t1;


#
# Bug @10632 (Ceiling function returns wrong answer)
#
select ceil(0.09);
select ceil(0.000000000000000009);

#
# Bug #9837: problem with round()
#

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

#
# Bug #11402: abs() forces rest of calculation to unsigned
#
select abs(-2) * -2;

#
# Bug #6172 RAND(a) should only accept constant values as arguments
#
CREATE TABLE t1 (a INT);

INSERT INTO t1 VALUES (1),(1),(1),(2);
SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) 
  FROM t1;
SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) 
  FROM t1 WHERE a = 1;
INSERT INTO t1 VALUES (3);
SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) 
  FROM t1;
SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) 
  FROM t1 WHERE a = 1;
PREPARE stmt FROM 
  "SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(?) * 1000 AS UNSIGNED)
    FROM t1 WHERE a = 1";
set @var=2;
EXECUTE stmt USING @var;

DROP TABLE t1;  

#
# Bug #14009: use of abs() on null value causes problems with filesort
#
# InnoDB is required to reproduce the fault, but it is okay if we default to
# MyISAM when testing.
set sql_mode="";
--disable_warnings
create table t1 (a varchar(90), ts datetime not null, index (a)) engine=innodb default charset=utf8;
--enable_warnings
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));
drop table t1;

# End of 4.1 tests

#
# Bug #13820 (No warning on log(negative)
#
set sql_mode='traditional';
select ln(-1);
select log10(-1);
select log2(-1);
select log(2,-1);
select log(-2,1);
set sql_mode='';

#
# Bug #8461 truncate() and round() return false results 2nd argument negative.
# 
# round(a,-b) log_10(b) > a
select round(111,-10);
# round on bigint 
select round(-5000111000111000155,-1);
# round on unsigned bigint
select round(15000111000111000155,-1);
# truncate on bigint 
select truncate(-5000111000111000155,-1);
# truncate on unsigned bigint
select truncate(15000111000111000155,-1);

#
# Bug#16678 FORMAT gives wrong result if client run with default-character-set=utf8
#
set names utf8;
create table t1
(f1 varchar(32) not null,
 f2 smallint(5) unsigned not null,
 f3 int(10) unsigned not null default '0')
engine=myisam default charset=utf8;
insert into t1 values ('zombie',0,0),('gold',1,10000),('silver',2,10000);

create table t2
(f1 int(10) unsigned not null,
 f2 int(10) unsigned not null,
 f3 smallint(5) unsigned not null)
engine=myisam default charset=utf8;
insert into t2 values (16777216,16787215,1),(33554432,33564431,2);

select format(t2.f2-t2.f1+1,0) from t1,t2
where t1.f2 = t2.f3 order by t1.f1;
drop table t1, t2;
set names latin1;

# Bug 24912 -- misc functions have trouble with unsigned

select cast(-2 as unsigned), 18446744073709551614, -2;
select abs(cast(-2 as unsigned)), abs(18446744073709551614), abs(-2);
select ceiling(cast(-2 as unsigned)), ceiling(18446744073709551614), ceiling(-2);
select floor(cast(-2 as unsigned)), floor(18446744073709551614), floor(-2);
select format(cast(-2 as unsigned), 2), format(18446744073709551614, 2), format(-2, 2);
select sqrt(cast(-2 as unsigned)), sqrt(18446744073709551614), sqrt(-2);
select round(cast(-2 as unsigned), 1), round(18446744073709551614, 1), round(-2, 1);
#view protocol generates additional warning
--disable_view_protocol
select round(4, cast(-2 as unsigned)), round(4, 18446744073709551614), round(4, -2);
--enable_view_protocol
select truncate(cast(-2 as unsigned), 1), truncate(18446744073709551614, 1), truncate(-2, 1);
#view protocol generates additional warning
--disable_view_protocol
select truncate(4, cast(-2 as unsigned)), truncate(4, 18446744073709551614), truncate(4, -2);
--enable_view_protocol
select round(10000000000000000000, -19), truncate(10000000000000000000, -19);
select round(1e0, -309), truncate(1e0, -309);
select round(1e1,308), truncate(1e1, 308);
select round(1e1, 2147483648), truncate(1e1, 2147483648);
select round(1.1e1, 4294967295), truncate(1.1e1, 4294967295);
select round(1.12e1, 4294967296), truncate(1.12e1, 4294967296);
select round(1.5, 2147483640), truncate(1.5, 2147483640);
select round(1.5, -2147483649), round(1.5, 2147483648);
select truncate(1.5, -2147483649), truncate(1.5, 2147483648);
select round(1.5, -4294967296), round(1.5, 4294967296);
select truncate(1.5, -4294967296), truncate(1.5, 4294967296);
select round(1.5, -9223372036854775808), round(1.5, 9223372036854775808);
select truncate(1.5, -9223372036854775808), truncate(1.5, 9223372036854775808);
select round(1.5, 18446744073709551615), truncate(1.5, 18446744073709551615);
select round(18446744073709551614, -1), truncate(18446744073709551614, -1);
select round(4, -4294967200), truncate(4, -4294967200);
select mod(cast(-2 as unsigned), 3), mod(18446744073709551614, 3), mod(-2, 3);
select mod(5, cast(-2 as unsigned)), mod(5, 18446744073709551614), mod(5, -2);
select pow(cast(-2 as unsigned), 5), pow(18446744073709551614, 5), pow(-2, 5);

#
# Bug #30587: mysql crashes when trying to group by TIME div NUMBER
#

CREATE TABLE t1 (a timestamp, b varchar(20), c bit(1));
INSERT INTO t1 VALUES('1998-09-23', 'str1', 1), ('2003-03-25', 'str2', 0);
SELECT a DIV 900 y FROM t1 GROUP BY y;
SELECT DISTINCT a DIV 900 y FROM t1;
SELECT b DIV 900 y FROM t1 GROUP BY y;
SELECT c DIV 900 y FROM t1 GROUP BY y;
DROP TABLE t1;

CREATE TABLE t1(a LONGBLOB);
INSERT INTO t1 VALUES('1'),('2'),('3');
SELECT DISTINCT (a DIV 254576881) FROM t1;
SELECT (a DIV 254576881) FROM t1 UNION ALL 
  SELECT (a DIV 254576881) FROM t1;
DROP TABLE t1;

CREATE TABLE t1(a SET('a','b','c'));
INSERT INTO t1 VALUES ('a');
SELECT a DIV 2 FROM t1 UNION SELECT a DIV 2 FROM t1;
DROP TABLE t1;

#
# Bug #15936: "round" differs on Windows to Unix
#

CREATE TABLE t1 (a DOUBLE);

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

SELECT a, ROUND(a) FROM t1;

DROP TABLE t1;

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

--echo End of 5.0 tests

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

--error ER_DATA_OUT_OF_RANGE
SELECT 1e308 + 1e308;
--error ER_DATA_OUT_OF_RANGE
SELECT -1e308 - 1e308;
--error ER_DATA_OUT_OF_RANGE
SELECT 1e300 * 1e300;
--error ER_DATA_OUT_OF_RANGE
SELECT 1e300 / 1e-300;
--error ER_DATA_OUT_OF_RANGE
SELECT EXP(750);
--error ER_DATA_OUT_OF_RANGE
SELECT POW(10, 309);

--echo #
--echo # Bug #44768: SIGFPE crash when selecting rand from a view
--echo #             containing null
--echo #

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

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

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

--echo #
--echo # Bug#57477 SIGFPE when dividing a huge number a negative number
--echo #
--error ER_DATA_OUT_OF_RANGE
SELECT -9999999999999999991 DIV -1;
--error ER_DATA_OUT_OF_RANGE
SELECT -9223372036854775808 DIV -1;
SELECT -9223372036854775808 MOD -1;
SELECT -9223372036854775808999 MOD -1;

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

--error ER_DATA_OUT_OF_RANGE
select 123456789012345678901234567890.123456789012345678901234567890 div 1 as x;
--error ER_DATA_OUT_OF_RANGE
select "123456789012345678901234567890.123456789012345678901234567890" div 1 as x; 
SHOW WARNINGS;

--echo #
--echo # Bug#57810 case/when/then : Assertion failed: length || !scale
--echo #

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

--echo #
--echo # Bug#11764994  57900: CREATE TABLE .. SELECT ASSERTS SCALE >= 0 && PRECISION > 0 && SCALE <= PR
--echo #

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
CREATE TABLE t1 SELECT CEIL(LINESTRINGFROMWKB(1) DIV NULL);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
CREATE TABLE t1 SELECT FLOOR(LINESTRINGFROMWKB(1) DIV NULL);

--echo #
--echo # Bug#11765923  58937: MANY VALGRIND ERRORS AFTER GROUPING BY RESULT OF DECIMAL COLUMN FUNCTION
--echo #

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

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

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

--echo #
--echo # Bug#12392636  ASSERTION FAILED: SCALE >= 0 && PRECISION > 0 && SCALE <= PRECISION
--echo #

SELECT SUM(DISTINCT (TRUNCATE((.1), NULL)));

--echo End of 5.1 tests

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

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

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

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

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

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

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

--error ER_DATA_OUT_OF_RANGE
SELECT ABS(-9223372036854775808);

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


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

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

# try with two rows now
INSERT INTO t1 VALUES(0,0);

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

DROP TABLE t1;

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

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

# Non-overflow tests to improve code coverage
# ===========================================
SELECT COT(1/0);
SELECT -1 + 9223372036854775808;
SELECT 2 DIV -2;
#view protocol generates additional warning
--disable_view_protocol
SELECT -(1 DIV 0);
--enable_view_protocol
# Crashed the server with SIGFPE before the bugfix
SELECT -9223372036854775808 MOD -1;

--echo #
--echo # Bug #57209 valgrind + Assertion failed: dst > buf 
--echo #
SELECT floor(log10(format(concat_ws(5445796E25, 5306463, 30837), -358821)))
as foo;

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

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

--echo #
--echo # Bug #59498 div function broken in mysql-trunk
--echo #
SELECT 1 div null;

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

--echo #
--echo # Bug#12744991 - DECIMAL_ROUND(X,D) GIVES WRONG RESULTS WHEN D == N*(-9)
--echo #

select round(999999999, -9);
select round(999999999.0, -9);
#enable after fix MDEV-29526
--disable_view_protocol
select round(999999999999999999, -18);
select round(999999999999999999.0, -18);
--enable_view_protocol

--echo #
--echo # Bug#12537160 ASSERTION FAILED:
--echo # STOP0 <= &TO->BUF[TO->LEN] WITH LARGE NUMBER.
--echo #

let $nine_81=
999999999999999999999999999999999999999999999999999999999999999999999999999999999;

eval select $nine_81 % 0.1 as foo;
eval select $nine_81 % 0.0 as foo;

--echo #
--echo # Bug#12711164 - 61676:
--echo # RESULT OF DIV WITH DECIMAL AND INTEGER DOES NOT MAKE SENSE 
--echo #

select 5 div 2;
select 5.0 div 2.0;
select 5.0 div 2;
select 5 div 2.0;
select 5.9 div 2, 1.23456789e3 DIV 2, 1.23456789e9 DIV 2, 1.23456789e19 DIV 2;

--echo #
--echo # MDEV-10467 Assertion `nr >= 0.0' failed in Item_sum_std::val_real()
--echo #
CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES (1),(2);
SELECT STDDEV_SAMP(ROUND('0', 309)) FROM t1;
DROP TABLE t1;


--echo #
--echo # MDEV-20495 Assertion `precision > 0' failed in decimal_bin_size upon CREATE .. SELECT with zerofilled decimal
--echo #

--echo # Testing that dyadic arithmetic operations are symmetric
--echo # for (+1) and (-1) and produce the same length in CONCAT(),
--echo # 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;
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;
DROP TABLE t1;


--echo #
--echo # Bug #29723340: MYSQL SERVER CRASH AFTER SQL QUERY WITH DATA ?AST
--echo #

create table t1(a int);
insert ignore t1 values("1e-214748364");
insert ignore t1 values("1e-2147483648");
insert ignore t1 values("1e-21474836480");
insert ignore t1 values("1e+214748364");
insert ignore t1 values("1e+2147483647");
insert ignore t1 values("1e+21474836470");

# if max max_allowed_packet will ever be increased beyond 2GB, this could
# break again:
set global max_allowed_packet= cast(2*1024*1024*1024+1024 as unsigned);
connect foo,localhost,root;
set @a=2147483647;
insert ignore t1 values (concat('1', repeat('0', @a+18), 'e-', @a-1, '0'));
disconnect foo;
connection default;
set global max_allowed_packet=default;

select * from t1;
drop table t1;

--echo #
--echo # End of 5.5 tests
--echo #

--echo #
--echo # MDEV-5781 Item_sum_std::val_real(): Assertion `nr >= 0.0' fails on query with STDDEV_POP, ROUND and variable
--echo #
SELECT STDDEV_POP(ROUND(0,@A:=2009)) FROM (SELECT 1 UNION SELECT 2) fake_table;

--echo #
--echo # Start of 10.1 tests
--echo #

--echo #
--echo # MDEV-17643 Assertion `nr >= 0.0' failed in Item_sum_std::val_real()
--echo #

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;
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;
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;
DROP TABLE t1;

--echo #
--echo # MDEV-21977 main.func_math fails due to undefined behaviour
--echo #

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

SELECT  9223372036854775808 MOD  1;
SELECT  9223372036854775808 MOD -1;
SELECT -9223372036854775808 MOD  1;
SELECT -9223372036854775808 MOD -1;

SELECT  1 MOD  9223372036854775808;
SELECT -1 MOD  9223372036854775808;
SELECT  1 MOD -9223372036854775808;
SELECT -1 MOD -9223372036854775808;

SELECT  9223372036854775808 MOD  9223372036854775808;
SELECT  9223372036854775808 MOD -9223372036854775808;
SELECT -9223372036854775808 MOD  9223372036854775808;
SELECT -9223372036854775808 MOD -9223372036854775808;

--echo #
--echo # MDEV-22502 MDB crashes in CREATE TABLE AS SELECT when the precision of returning type = 0
--echo #

CREATE TABLE t1 (d decimal(5,5));
INSERT INTO t1 VALUES (0.55555);
SELECT TRUNCATE(d,0) FROM t1;
CREATE TABLE t2 AS SELECT TRUNCATE(d,0) FROM t1;
SELECT * FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1, t2;


--echo #
--echo # MDEV-22503 MDB limits DECIMAL column precision to 16 doing CTAS with floor/ceil over DECIMAL(X,Y) where X > 16
--echo #

CREATE TABLE t44 (d1 decimal(38,0) DEFAULT NULL);
INSERT INTO t44 VALUES (12345678901234567890123456789012345678);
SELECT FLOOR(d1) FROM t44;
CREATE TABLE t45 AS SELECT FLOOR(d1) FROM t44;
SELECT * FROM t45;
SHOW CREATE TABLE t45;
DROP TABLE t44, t45;


DELIMITER $$;
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;
$$
DELIMITER ;$$
--vertical_results
CALL p2(38);
CALL p2(30);
--horizontal_results
DROP PROCEDURE p2;
DROP PROCEDURE p1;


--echo #
--echo # End of 10.1 tests
--echo #

--echo #
--echo # Start of 10.2 tests
--echo #

--echo # Test zero
select 0=0, 0=-0, 0.0= -0.0, 0.0 = -(0.0), 0.0E1=-0.0E1, 0.0E1=-(0.0E1);

--echo #
--echo # CRC32 tests
--echo #

#enable after fix MDEV-28535
--disable_view_protocol
select CRC32(NULL), CRC32(''), CRC32('MySQL'), CRC32('mysql'), CRC32('01234567'), CRC32('012345678'), CRC32(REPEAT('ABCDEfghij', 20)), CRC32(REPEAT('0123456789', 200));
--enable_view_protocol

--echo #
--echo # Start of 10.3 tests
--echo #

--echo #
--echo # MDEV-12000 ROUND(expr,const_expr_returning_NULL) creates DOUBLE(0,0)
--echo #

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;
DROP TABLE t1;

--echo #
--echo # MDEV-12857 Out-of-range errors on CREATE..SELECT 2222222222 DIV 1
--echo #

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;
--vertical_results
SELECT * FROM t1;
--horizontal_results
DROP TABLE t1;
SET sql_mode=DEFAULT;


--echo #
--echo # MDEV-12858 Out-of-range error for CREATE..SELECT unsigned_int_column+1
--echo #

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;
SELECT a, HEX(a) FROM t2;
DROP TABLE t2;
DROP TABLE t1;
SET sql_mode=DEFAULT;


--echo #
--echo # Bug#26495791 - EXPAND TEST SUITE TO INCLUDE CRC32 TESTS
--echo #

#enable after fix MDEV-28535
--disable_view_protocol
SELECT CRC32(NULL), CRC32(''), CRC32('MySQL'), CRC32('mysql');
--enable_view_protocol
SELECT CRC32('01234567'), CRC32('012345678');
SELECT CRC32('~!@$%^*'),  CRC32('-0.0001');
SELECT CRC32(99999999999999999999999999999999);
SELECT CRC32(-99999999999999999999999999999999);
#enable_after fix MDEV-28535
--disable_view_protocol
SELECT CRC32C(NULL), CRC32C(''), CRC32C('MariaDB'), CRC32C('mariadb');
--enable_view_protocol
SELECT CRC32(NULL,1),CRC32C(NULL,1), CRC32(1,''), CRC32C(1,'');
SELECT CRC32(42,''),CRC32C(42,''),CRC32('42',''),CRC32C('42','');
SELECT CRC32(42,NULL),CRC32C(42,NULL);
SELECT CRC32 ('5c',''),CRC32 ('5c',0),CRC32 ('5c', '0'),CRC32 ('5c',NULL);
SELECT CRC32C('5c',''),CRC32C('5c',0),CRC32C('5c', '0'),CRC32C('5c',NULL);
SELECT CRC32('MariaDB',NULL),CRC32C('MariaDB',NULL);
SELECT CRC32(CRC32('MySQL'),''),CRC32(CRC32('My'),'SQL'),CRC32(0,'MySQL');
SELECT CRC32C(CRC32C('MariaDB'),''),CRC32C(CRC32C('Maria'),'DB'),CRC32C(0,'MariaDB');

--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select crc32(0,'My','SQL');
--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select crc32c(0,'Maria','DB');
--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select crc32();
--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select crc32c();
--error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select crc32('' as empty);
--error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select crc32c('' as empty);
--error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select crc32(0, '' as empty);
--error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select crc32c(0, '' as empty);
--error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select crc32(0 as zero, '');
--error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select crc32c(0 as zero, '');

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;
DROP TEMPORARY TABLE t;

select crc32(4294967296,''), hex(char(4294967296));
select crc32(1e100,''), hex(char(1e100));
select crc32(10.11,''), hex(char(10.11));
select crc32(-1,''), hex(char(-1));
select crc32('',''), hex(char(''));
--disable_ps_protocol
select crc32(429496729656755555555555555555555555555555555555555555555555555555555555555555555555555,'a') as x;
--enable_ps_protocol
--disable_warnings
select crc32(429496729656755555555555555555555555555555555555555555555555555555555555555555555555555,'a') as x;
--enable_warnings

# Test cases for using the function in aggregate functions, group-by, having
# and order-by clauses
DROP TABLE IF EXISTS 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;
SELECT crc32(AVG(a)),crc32c(AVG(a)) FROM t GROUP BY b;
SELECT crc32(MAX(b)),crc32c(MAX(b)) FROM t GROUP BY a;
SELECT a, b, crc32(a) FROM t GROUP BY a,b HAVING crc32(MAX(a))=450215437;
SELECT a,b,concat(a,b),crc32(concat(a,b)) FROM t ORDER BY crc32(concat(a,b));
DROP TABLE t;

# Test cases for arithmetic operators and functions
SELECT CRC32(4+2);
SELECT CRC32(4/2);
SELECT CRC32(4-2);
SELECT CRC32(4*2);
SELECT CRC32(ABS(-6));
SELECT CRC32(CEILING(1.23));
SELECT CRC32(FLOOR(1.23));
SELECT CRC32(LOG(10,100));
SELECT CRC32(PI());
SELECT CRC32(POWER(2,2));
SELECT CRC32(ROUND(1.58));
SELECT CRC32(SIGN(0));
SELECT CRC32(SQRT(4));

# Test cases for comparison operators
SELECT CRC32(2 > 4);
SELECT CRC32(2 < 4);
SELECT CRC32(2 >= 4);
SELECT CRC32(2 <= 4);
SELECT CRC32(2 != 4);

# Test cases for logical operators
SELECT CRC32(NOT 1);
SELECT CRC32(1 AND 1);
SELECT CRC32(1 OR 1);
SELECT CRC32(1 XOR 1);

# Test cases for string functions
SELECT CRC32(ASCII('2'));
SELECT CRC32(BIT_LENGTH('text'));
SELECT CRC32(CHAR('77','121','83','81','76'));
SELECT CRC32(CONCAT('good','year'));
SELECT CRC32(INSERT('foodyear', 1, 4, 'good'));
SELECT CRC32(LEFT('goodyear', 4));
SELECT CRC32(LENGTH('text'));
SELECT CRC32(LOWER('GOODYEAR'));
SELECT CRC32(UPPER('goodyear'));
SELECT CRC32(LTRIM('  goodyear'));
SELECT CRC32(RTRIM('goodyear  '));
SELECT CRC32(REPLACE('godyear','o','oo'));
SELECT CRC32(REVERSE('goodyear'));

# Test cases for boolean values
SELECT CRC32(true);
SELECT CRC32(false);

# Test cases for numeric data types
DROP TABLE IF EXISTS 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;
SELECT CRC32(c2) FROM t1;
SELECT CRC32(c3) FROM t1;
SELECT CRC32(c4) FROM t1;
SELECT CRC32(c5) FROM t1;
SELECT CRC32(c6) FROM t1;
SELECT CRC32(c7) FROM t1;
SELECT CRC32(c8) FROM t1;

# Test cases for temporal data types
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;
SELECT CRC32(c2) FROM t1;
SELECT CRC32(c3) FROM t1;
SELECT CRC32(c4) FROM t1;
SELECT CRC32(c5) FROM t1;

# Test cases for string data types
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;
SELECT CRC32(c2) FROM t1;
SELECT CRC32(c3) FROM t1;
SELECT CRC32(c4) FROM t1;
SELECT CRC32(c5) FROM t1;
SELECT CRC32(c6) FROM t1;
SELECT CRC32(c7) FROM t1;
SELECT CRC32(c8) FROM t1;
SELECT CRC32(c9) FROM t1;
SELECT CRC32(c10) FROM t1;
SELECT CRC32(c11) FROM t1;
SELECT CRC32(c12) FROM t1;

# Test cases for geometric data types
SELECT CRC32(ST_GeomFromText('POINT(1 1)'));

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;
SELECT CRC32(ST_NumPoints((lnstr))) FROM geom_data;
SELECT CRC32(ST_Length((mlnstr))) FROM geom_data;
SELECT CRC32(ST_Area((poly))) FROM geom_data;
SELECT CRC32(ST_Area((mpoly))) FROM geom_data;
DROP TABLE geom_data;

# Test cases for ENUM and SET data types
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;
SELECT s1, CRC32(s1) FROM t1;

# Test cases for JSON data types
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;
SELECT CRC32(json_query(a, '$.name')) FROM t1 WHERE json_query(a, '$.name') = 'goodyear';
SELECT CRC32(REPLACE(JSON_EXTRACT(a, "$.name"),'\"',''))
  FROM t1 WHERE JSON_EXTRACT(a, "$.name") = 'goodyear';
DROP TABLE t1;

# Test case for views
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;
SELECT CRC32(CRC32(my_crc)) FROM v1;
DROP VIEW v1;
DROP TABLE t1;

# Test case for triggers
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;
DROP TRIGGER trg1;
DROP TABLE t2;
DROP TABLE t1;

# Test case for a stored procedure
CREATE PROCEDURE crc32_proc (IN a CHAR, OUT b BIGINT)
  SELECT CRC32(a) INTO b;
CALL crc32_proc('a',@val);
SELECT @val;
DROP PROCEDURE crc32_proc;

# Test case for a user defined function
DELIMITER |;
CREATE FUNCTION crc32_func(inputvar CHAR)
RETURNS BIGINT
BEGIN
  DECLARE crcval BIGINT;
  SELECT CRC32(inputvar) INTO crcval;
  RETURN crcval;
END|
DELIMITER ;|
SELECT crc32_func('a');
DROP FUNCTION crc32_func;

# Test case for a prepared statement
PREPARE stmt1 FROM 'SELECT CRC32(?)';
SET @val = 'a';
EXECUTE stmt1 USING @val;
DEALLOCATE PREPARE stmt1;

# Test case for checksum on contents of a file
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;
DROP TABLE t1;
SET NAMES default;

--echo #
--echo # MDEV-17759 Assertion `precision > 0' failed in decimal_bin_size upon CREATE TABLE .. SELECT
--echo #

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;
SHOW CREATE TABLE t2;
DROP TABLE t1, t2;
SET sql_mode=DEFAULT;


--echo #
--echo # MDEV-18150 Assertion `decimals_to_set <= 38' failed in Item_func_round::fix_length_and_dec_decimal
--echo #

CREATE TABLE t1 (i INT(23));
SELECT ROUND( i, 18446744073709551594 ) AS f FROM t1;
DROP TABLE t1;


--echo #
--echo # MDEV-22268 virtual longlong Item_func_div::int_op(): Assertion `0' failed in Item_func_div::int_op
--echo #

SET sql_mode='';
SET @@SESSION.div_precision_increment=0;
SELECT UTC_TIME / 0;
SELECT TIMESTAMP'2001-01-01 00:00:00'/0;
SELECT TIME'00:00:00'/0;
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;
DROP TABLE t1;

--error ER_DATA_OUT_OF_RANGE
SELECT(-0 * MOD((UTC_TIME / -0)MOD (ATAN('<img src_x0=x onerror="javascript:alert(0)">') MOD COT(0)),-0)) MOD (0 DIV 0);

SET @@SESSION.div_precision_increment=DEFAULT;
SET sql_mode=DEFAULT;

--echo #
--echo # End of 10.3 tests
--echo #

--echo #
--echo # Start of 10.4 tests
--echo #

--echo #
--echo # MDEV-20732 max_char_length() doesn't account for FORMAT() with doubles in scientific notation
--echo #

CREATE OR REPLACE TABLE t1 AS SELECT FORMAT(-1e308,2);
SELECT * FROM t1;
SHOW CREATE TABLE t1;
DROP TABLE t1;

CREATE OR REPLACE TABLE t1 AS SELECT FORMAT('-1e308',2);
SELECT * FROM t1;
SHOW CREATE TABLE t1;
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;
SHOW CREATE TABLE t1;
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;
SHOW CREATE TABLE t2;
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;
SHOW CREATE TABLE t2;
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;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 AS SELECT -99999999999999999999999999999999999999999999999999999999999999999 as c1;
SELECT * FROM t1;
SHOW CREATE TABLE t1;
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);
--vertical_results
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;
--horizontal_results

DROP TABLE t1;

CREATE OR REPLACE TABLE t1 (c1 BIT(1)); 
INSERT INTO t1 VALUES (b'1');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(2)); 
INSERT INTO t1 VALUES (b'11');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(3)); 
INSERT INTO t1 VALUES (b'111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(4)); 
INSERT INTO t1 VALUES (b'1111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(5)); 
INSERT INTO t1 VALUES (b'11111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(6)); 
INSERT INTO t1 VALUES (b'111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(7)); 
INSERT INTO t1 VALUES (b'1111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(8)); 
INSERT INTO t1 VALUES (b'11111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(9)); 
INSERT INTO t1 VALUES (b'111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(10)); 
INSERT INTO t1 VALUES (b'1111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(11)); 
INSERT INTO t1 VALUES (b'11111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(12)); 
INSERT INTO t1 VALUES (b'111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(13)); 
INSERT INTO t1 VALUES (b'1111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(14)); 
INSERT INTO t1 VALUES (b'11111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(15)); 
INSERT INTO t1 VALUES (b'111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(16)); 
INSERT INTO t1 VALUES (b'1111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(17)); 
INSERT INTO t1 VALUES (b'11111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(18)); 
INSERT INTO t1 VALUES (b'111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(19)); 
INSERT INTO t1 VALUES (b'1111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(20)); 
INSERT INTO t1 VALUES (b'11111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(21)); 
INSERT INTO t1 VALUES (b'111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(22)); 
INSERT INTO t1 VALUES (b'1111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(23)); 
INSERT INTO t1 VALUES (b'11111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(24)); 
INSERT INTO t1 VALUES (b'111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(25)); 
INSERT INTO t1 VALUES (b'1111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(26)); 
INSERT INTO t1 VALUES (b'11111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(27)); 
INSERT INTO t1 VALUES (b'111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(28)); 
INSERT INTO t1 VALUES (b'1111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(29)); 
INSERT INTO t1 VALUES (b'11111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(30)); 
INSERT INTO t1 VALUES (b'111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(31)); 
INSERT INTO t1 VALUES (b'1111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(32)); 
INSERT INTO t1 VALUES (b'11111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(33)); 
INSERT INTO t1 VALUES (b'111111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(34)); 
INSERT INTO t1 VALUES (b'1111111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(35)); 
INSERT INTO t1 VALUES (b'11111111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(36)); 
INSERT INTO t1 VALUES (b'111111111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(37)); 
INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(38)); 
INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(39)); 
INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(40)); 
INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(41)); 
INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(42)); 
INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(43)); 
INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(44)); 
INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(45)); 
INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(46)); 
INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(47)); 
INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(48)); 
INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(49)); 
INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(50)); 
INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(51)); 
INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(52)); 
INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(53)); 
INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(54)); 
INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(55)); 
INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(56)); 
INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(57)); 
INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(58)); 
INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(59)); 
INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(60)); 
INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(61)); 
INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(62)); 
INSERT INTO t1 VALUES (b'11111111111111111111111111111111111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(63)); 
INSERT INTO t1 VALUES (b'111111111111111111111111111111111111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

CREATE OR REPLACE TABLE t1 (c1 BIT(64)); 
INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111111111111111111111111');
SELECT HEX(c1) FROM t1;
CREATE OR REPLACE TABLE t2 AS SELECT FORMAT(c1,0) as c1 FROM t1;
SELECT HEX(c1) FROM t2;
SHOW CREATE TABLE t2;
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;
SHOW CREATE TABLE t2;
DROP TABLE t1,t2;

--echo #
--echo # 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
--echo #

--error ER_DATA_OUT_OF_RANGE
SELECT (1 DIV(-1/POW(807,14))*1);

DO((-9223372036854775808)*(1));

SELECT (-9223372036854775808)*(1);

--error ER_DATA_OUT_OF_RANGE
SELECT (GET_FORMAT(TIME,'JIS'))DIV(POW(-40,65)DIV(1)*2);

SELECT -9223372036854775808 MOD 9223372036854775810;

CREATE TABLE t1 (c INT);
INSERT INTO t1 VALUES(TRUNCATE(0,-1.e+30));
DROP TABLE t1;
SELECT TRUNCATE(0, -9223372036854775808);

--disable_warnings
SELECT GET_FORMAT(TIME,'JIS') DIV ATAN (TRUNCATE (0,'2000000000000000' DIV SIN(1500)*NOW(5))) AS col1;
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;
--enable_warnings

--echo #
--echo # End of 10.4 tests
--echo #


--echo #
--echo # Start of 10.5 tests
--echo #

--echo #
--echo # MDEV-21278 Assertion `is_unsigned() == attr.unsigned_flag' or Assertion `field.is_sane()' failed
--echo #

CREATE TABLE t1 (a TIMESTAMP NOT NULL DEFAULT NOW());
INSERT INTO t1 VALUES (NULL),(NULL);
SET SESSION SQL_MODE= 'NO_UNSIGNED_SUBTRACTION';
--replace_column 1 xxx
SELECT DISTINCT UUID_SHORT() - a FROM t1;
CREATE TABLE t2 AS SELECT DISTINCT UUID_SHORT() - a FROM t1;
SHOW CREATE TABLE t2;
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';
--replace_column 1 xxx
SELECT UUID_SHORT() - a FROM t1;
CREATE TABLE t2 AS SELECT UUID_SHORT() - a FROM t1;
SHOW CREATE TABLE t2;
DROP TABLE t2, t1;

--echo #
--echo # MDEV-23680 Assertion `data' failed in crcr32_calc_pclmulqdq
--echo #
SELECT CRC32(ExtractValue('<a><b/></a>', '/a/b')) AS f;


--echo #
--echo # End of 10.5 tests
--echo #