DROP DATABASE IF EXISTS test_mcol641_functions;
CREATE DATABASE test_mcol641_functions;
USE test_mcol641_functions;
DROP TABLE IF EXISTS cs1;
CREATE TABLE cs1 (d1 DECIMAL(38), d2 DECIMAL(38)) ENGINE=columnstore;
INSERT INTO cs1 VALUES (123, 234), (999999999999999999999999999999, -999999999999999999999999999999), (999999999999999999, -999999999999999999), (1329227995784915872903807060280344576, 1), (-1329227995784915872903807060280344576, 1);
SELECT "bitwise_and_test", d1 & d2, d1 & d1, d2 & d2, d1 & 10, d2 & 10 FROM cs1;
bitwise_and_test	d1 & d2	d1 & d1	d2 & d2	d1 & 10	d2 & 10
bitwise_and_test	106	123	234	10	10
bitwise_and_test	9223372036854775808	18446744073709551615	9223372036854775808	10	0
bitwise_and_test	1	999999999999999999	17446744073709551617	10	0
bitwise_and_test	1	18446744073709551615	1	10	0
bitwise_and_test	0	9223372036854775808	1	0	0
SELECT "bitwise_or_test", d1 | d2, d1 | d1, d2 | d2, d1 | 10, d2 | 10 FROM cs1;
bitwise_or_test	d1 | d2	d1 | d1	d2 | d2	d1 | 10	d2 | 10
bitwise_or_test	251	123	234	123	234
bitwise_or_test	18446744073709551615	18446744073709551615	9223372036854775808	18446744073709551615	9223372036854775818
bitwise_or_test	18446744073709551615	999999999999999999	17446744073709551617	999999999999999999	17446744073709551627
bitwise_or_test	18446744073709551615	18446744073709551615	1	18446744073709551615	11
bitwise_or_test	9223372036854775809	9223372036854775808	1	9223372036854775818	11
SELECT "bitwise_xor_test", d1 ^ d2, d1 ^ d1, d2 ^ d2, d1 ^ 10, d2 ^ 10 FROM cs1 WHERE d1 != 999999999999999999;
bitwise_xor_test	d1 ^ d2	d1 ^ d1	d2 ^ d2	d1 ^ 10	d2 ^ 10
bitwise_xor_test	145	0	0	113	224
bitwise_xor_test	9223372036854775807	0	0	18446744073709551605	9223372036854775818
bitwise_xor_test	NULL	0	0	18446744073709551605	11
bitwise_xor_test	9223372036854775809	0	0	9223372036854775818	11
SELECT "bitwise_leftshift_test", d1 << 1, d1 << 10, d1 << 20, d2 << 1, d2 << 10, d2 << 20 FROM cs1 WHERE d1 <= 999999999999999999;
bitwise_leftshift_test	d1 << 1	d1 << 10	d1 << 20	d2 << 1	d2 << 10	d2 << 20
bitwise_leftshift_test	246	125952	128974848	468	239616	245366784
bitwise_leftshift_test	1999999999999999998	9429075945974660096	7726618127956443136	16446744073709551618	9017668127734891520	10720125945753108480
bitwise_leftshift_test	0	0	0	2	1024	1048576
SELECT "bitwise_rightshift_test", d1 >> 1, d1 >> 10, d1 >> 20, d2 >> 1, d2 >> 10, d2 >> 20 FROM cs1;
bitwise_rightshift_test	d1 >> 1	d1 >> 10	d1 >> 20	d2 >> 1	d2 >> 10	d2 >> 20
bitwise_rightshift_test	61	0	0	117	0	0
bitwise_rightshift_test	9223372036854775807	18014398509481983	17592186044415	4611686018427387904	9007199254740992	8796093022208
bitwise_rightshift_test	499999999999999999	976562499999999	953674316406	8723372036854775808	17037836009481984	16638511728009
bitwise_rightshift_test	9223372036854775807	18014398509481983	17592186044415	0	0	0
bitwise_rightshift_test	4611686018427387904	9007199254740992	8796093022208	0	0	0
SELECT "bitcount_test", bit_count(d1), bit_count(d2) FROM cs1;
bitcount_test	bit_count(d1)	bit_count(d2)
bitcount_test	6	5
bitcount_test	64	1
bitcount_test	41	24
bitcount_test	64	1
bitcount_test	1	1
DELETE FROM cs1;
INSERT INTO cs1 VALUES (2001, 2011), (199001, 199010);
SELECT "period_diff_test", PERIOD_DIFF(d1, d2), PERIOD_DIFF(d2, d1), PERIOD_DIFF(d1, d1) FROM cs1;
period_diff_test	PERIOD_DIFF(d1, d2)	PERIOD_DIFF(d2, d1)	PERIOD_DIFF(d1, d1)
period_diff_test	-10	10	0
period_diff_test	-9	9	0
DROP TABLE IF EXISTS cs1;
CREATE TABLE cs1 (d1 DECIMAL(38)) ENGINE=columnstore;
INSERT INTO cs1 VALUES (123), (-234), (99999999999999999999999999999999999999), (-99999999999999999999999999999999999999), (12345678901234567890123456789012345678), (-12345678901234567890123456789012345678), (1329227995784915872903807060280344576), (-1329227995784915872903807060280344576);
SELECT "cast_test1", CAST(d1 AS SIGNED) FROM cs1 WHERE d1 > -9223372036854775808;
cast_test1	CAST(d1 AS SIGNED)
cast_test1	123
cast_test1	-234
cast_test1	9223372036854775807
cast_test1	9223372036854775807
cast_test1	9223372036854775807
SELECT "cast_test2", CAST(d1 AS UNSIGNED), CAST(d1 AS CHAR(50)) FROM cs1;
cast_test2	CAST(d1 AS UNSIGNED)	CAST(d1 AS CHAR(50))
cast_test2	123	123
cast_test2	0	-234
cast_test2	18446744073709551615	99999999999999999999999999999999999999
cast_test2	0	-99999999999999999999999999999999999999
cast_test2	18446744073709551615	12345678901234567890123456789012345678
cast_test2	0	-12345678901234567890123456789012345678
cast_test2	18446744073709551615	1329227995784915872903807060280344576
cast_test2	0	-1329227995784915872903807060280344576
DELETE FROM cs1;
INSERT INTO cs1 VALUES (20201212), (19901028183200);
SELECT "cast_test3", CAST(d1 AS DATETIME), CAST(d1 AS DATE) FROM cs1;
cast_test3	CAST(d1 AS DATETIME)	CAST(d1 AS DATE)
cast_test3	2020-12-12 00:00:00	2020-12-12
cast_test3	1990-10-28 18:32:00	1990-10-28
DROP TABLE IF EXISTS cs1;
CREATE TABLE cs1 (d1 DECIMAL(38,10)) ENGINE=columnstore;
INSERT INTO cs1 VALUES (123), (-123), (123.456), (-123.456), (1234567890123456789012345678.9012345678), (-1234567890123456789012345678.9012345678);
SELECT "cast_test4", CAST(d1 AS DOUBLE) FROM cs1;
cast_test4	CAST(d1 AS DOUBLE)
cast_test4	123
cast_test4	-123
cast_test4	123.456
cast_test4	-123.456
cast_test4	1.2345678901234569e27
cast_test4	-1.2345678901234569e27
DROP TABLE IF EXISTS cs1;
CREATE TABLE cs1 (d1 DECIMAL(38), d2 DECIMAL(38,10), d3 DECIMAL(38,38), d4 BIGINT, d5 CHAR(40), d6 FLOAT, d7 DOUBLE) ENGINE=columnstore;
INSERT INTO cs1 VALUES (123, 123.456, .123, 123, "123", 123.456, 123.456),
(12345678901234567890123456789012345678, 1234567890123456789012345678.9012345678, .12345678901234567890123456789012345678, 123456789012345678, "12345678901234567890123456789012345678", 1234567890.1234567890, 12345678901234567.123456789012345678),
(99999999999999999999999999999999999999, 9999999999999999999999999999.9999999999, .99999999999999999999999999999999999999, 999999999999999999, "99999999999999999999999999999999999999", 9999999999.9999999999, 99999999999999999999.999999999999999999);
INSERT INTO cs1(d5) VALUES ("1.1234567890123456789012345e25");
SELECT "cast_test5", CAST(d1 as DECIMAL(38,0)), CAST(d2 as DECIMAL(38,0)), CAST(d3 as DECIMAL(38,0)), CAST(d4 as DECIMAL(38,0)), CAST(d5 as DECIMAL(38,0)), CAST(d6 as DECIMAL(38,0)), CAST(d7 as DECIMAL(38,0)) FROM cs1;
cast_test5	CAST(d1 as DECIMAL(38,0))	CAST(d2 as DECIMAL(38,0))	CAST(d3 as DECIMAL(38,0))	CAST(d4 as DECIMAL(38,0))	CAST(d5 as DECIMAL(38,0))	CAST(d6 as DECIMAL(38,0))	CAST(d7 as DECIMAL(38,0))
cast_test5	123	123	0	123	123	123	123
cast_test5	12345678901234567890123456789012345678	1234567890123456789012345679	0	123456789012345678	12345678901234567890123456789012345678	1234567936	12345678901234568
cast_test5	99999999999999999999999999999999999999	10000000000000000000000000000	1	999999999999999999	99999999999999999999999999999999999999	10000000000	100000000000000000000
cast_test5	NULL	NULL	NULL	NULL	11234567890123456789012345	NULL	NULL
SELECT "cast_test5", CAST(d2 as DECIMAL(38,10)), CAST(d3 as DECIMAL(38,10)), CAST(d4 as DECIMAL(38,10)), CAST(d6 as DECIMAL(38,10)) FROM cs1;
cast_test5	CAST(d2 as DECIMAL(38,10))	CAST(d3 as DECIMAL(38,10))	CAST(d4 as DECIMAL(38,10))	CAST(d6 as DECIMAL(38,10))
cast_test5	123.4560000000	0.1230000000	123.0000000000	123.4560012817
cast_test5	1234567890123456789012345678.9012345678	0.1234567890	123456789012345678.0000000000	1234567936.0000000000
cast_test5	9999999999999999999999999999.9999999999	1.0000000000	999999999999999999.0000000000	10000000000.0000000000
cast_test5	NULL	NULL	NULL	NULL
SELECT "cast_test5", CAST(d3 as DECIMAL(38,38)), CAST(d6 as DECIMAL(38,38)), CAST(d7 as DECIMAL(38,38)) FROM cs1;
cast_test5	CAST(d3 as DECIMAL(38,38))	CAST(d6 as DECIMAL(38,38))	CAST(d7 as DECIMAL(38,38))
cast_test5	0.12300000000000000000000000000000000000	0.99999999999999999999999999999999999999	0.99999999999999999999999999999999999999
cast_test5	0.12345678901234567890123456789012345678	0.99999999999999999999999999999999999999	0.99999999999999999999999999999999999999
cast_test5	0.99999999999999999999999999999999999999	0.99999999999999999999999999999999999999	0.99999999999999999999999999999999999999
cast_test5	NULL	NULL	NULL
DROP TABLE IF EXISTS cs1;
CREATE TABLE cs1 (d1 DECIMAL(38)) ENGINE=columnstore;
INSERT INTO cs1 VALUES (127), (1278), (1234567890);
SELECT "inet_test", INET_NTOA(d1) FROM cs1;
inet_test	INET_NTOA(d1)
inet_test	0.0.0.127
inet_test	0.0.4.254
inet_test	73.150.2.210
SELECT "inet_test", d1 FROM cs1 where INET_ATON(d1) = d1;
inet_test	d1
inet_test	127
DELETE FROM cs1;
INSERT INTO cs1 VALUES (1296911693), (77);
SELECT "char_test", CHAR(d1) FROM cs1;
char_test	CHAR(d1)
char_test	MMMM
char_test	M
INSERT INTO cs1 VALUES(20201010), (19901230), (1234), (12345);
SELECT "monthname_test", MONTHNAME(d1), SEC_TO_TIME(d1) FROM cs1;
monthname_test	MONTHNAME(d1)	SEC_TO_TIME(d1)
monthname_test	NULL	838:59:59
monthname_test	NULL	00:01:17
monthname_test	October	838:59:59
monthname_test	December	838:59:59
monthname_test	NULL	00:20:34
monthname_test	NULL	03:25:45
DROP TABLE IF EXISTS cs1;
CREATE TABLE cs1 (d1 DECIMAL(38), d2 DECIMAL(38, 10), d3 DECIMAL(38, 38)) ENGINE=columnstore;
INSERT INTO cs1 VALUES
(-123456, -123.456, -0.123456),
(-99999999999999999999999999999999999999, -9999999999999999999999999999.9999999999, -0.99999999999999999999999999999999999999),
(NULL, NULL, NULL),
(-1234, -1234, -0.1234),
(1234, 1234, 0.1234);
SELECT "abs_test", ABS(d1), ABS(d2), ABS(d3) FROM cs1;
abs_test	ABS(d1)	ABS(d2)	ABS(d3)
abs_test	123456	123.4560000000	0.12345600000000000000000000000000000000
abs_test	99999999999999999999999999999999999999	9999999999999999999999999999.9999999999	0.99999999999999999999999999999999999999
abs_test	NULL	NULL	NULL
abs_test	1234	1234.0000000000	0.12340000000000000000000000000000000000
abs_test	1234	1234.0000000000	0.12340000000000000000000000000000000000
DELETE FROM cs1;
INSERT INTO cs1 VALUES
(123456, 123.456, 0.123456),
(-123456, -123.456, -0.123456),
(9999999999999999999999999999999999999, 999999999999999999999999999.9999999999, 0.9999999999999999999999999999999999999),
(-9999999999999999999999999999999999999, -999999999999999999999999999.9999999999, -0.9999999999999999999999999999999999999),
(1234567890123456789012345679012345678, 123456789012345678901234567.9012345678, 0.1234567890123456789012345679012345678),
(-1234567890123456789012345679012345678, -123456789012345678901234567.9012345678, -0.1234567890123456789012345679012345678),
(NULL, NULL, NULL);
SELECT "round_test", ROUND(d1), ROUND(d1,5), ROUND(d1,-5), ROUND(d1,10), ROUND(d1,-10), ROUND(d1,-38) FROM cs1;
round_test	ROUND(d1)	ROUND(d1,5)	ROUND(d1,-5)	ROUND(d1,10)	ROUND(d1,-10)	ROUND(d1,-38)
round_test	123456	123456.00000	100000	123456.0000000000	0	0
round_test	-123456	-123456.00000	-100000	-123456.0000000000	0	0
round_test	9999999999999999999999999999999999999	9999999999999999999999999999999999999.00000	10000000000000000000000000000000000000	9999999999999999999999999999999999999.0000000000	10000000000000000000000000000000000000	0
round_test	-9999999999999999999999999999999999999	-9999999999999999999999999999999999999.00000	-10000000000000000000000000000000000000	-9999999999999999999999999999999999999.0000000000	-10000000000000000000000000000000000000	0
round_test	1234567890123456789012345679012345678	1234567890123456789012345679012345678.00000	1234567890123456789012345679012300000	1234567890123456789012345679012345678.0000000000	1234567890123456789012345680000000000	0
round_test	-1234567890123456789012345679012345678	-1234567890123456789012345679012345678.00000	-1234567890123456789012345679012300000	-1234567890123456789012345679012345678.0000000000	-1234567890123456789012345680000000000	0
round_test	NULL	NULL	NULL	NULL	NULL	NULL
SELECT "round_test", ROUND(d2), ROUND(d2,5), ROUND(d2,-5), ROUND(d2,10), ROUND(d2,-10), ROUND(d2,38), ROUND(d2,-38) FROM cs1;
round_test	ROUND(d2)	ROUND(d2,5)	ROUND(d2,-5)	ROUND(d2,10)	ROUND(d2,-10)	ROUND(d2,38)	ROUND(d2,-38)
round_test	123	123.45600	0	123.4560000000	0	123.45600000000000000000000000000000000000	0
round_test	-123	-123.45600	0	-123.4560000000	0	-123.45600000000000000000000000000000000000	0
round_test	1000000000000000000000000000	1000000000000000000000000000.00000	1000000000000000000000000000	999999999999999999999999999.9999999999	1000000000000000000000000000	999999999999999999999999999.99999999990000000000000000000000000000	0
round_test	-1000000000000000000000000000	-1000000000000000000000000000.00000	-1000000000000000000000000000	-999999999999999999999999999.9999999999	-1000000000000000000000000000	-999999999999999999999999999.99999999990000000000000000000000000000	0
round_test	123456789012345678901234568	123456789012345678901234567.90123	123456789012345678901200000	123456789012345678901234567.9012345678	123456789012345680000000000	123456789012345678901234567.90123456780000000000000000000000000000	0
round_test	-123456789012345678901234568	-123456789012345678901234567.90123	-123456789012345678901200000	-123456789012345678901234567.9012345678	-123456789012345680000000000	-123456789012345678901234567.90123456780000000000000000000000000000	0
round_test	NULL	NULL	NULL	NULL	NULL	NULL	NULL
SELECT "round_test", ROUND(d3), ROUND(d3,5), ROUND(d3,-5), ROUND(d3,10), ROUND(d3,-10), ROUND(d3,38), ROUND(d3,-38) FROM cs1;
round_test	ROUND(d3)	ROUND(d3,5)	ROUND(d3,-5)	ROUND(d3,10)	ROUND(d3,-10)	ROUND(d3,38)	ROUND(d3,-38)
round_test	0	0.12346	0	0.1234560000	0	0.12345600000000000000000000000000000000	0
round_test	0	-0.12346	0	-0.1234560000	0	-0.12345600000000000000000000000000000000	0
round_test	1	1.00000	0	1.0000000000	0	0.99999999999999999999999999999999999990	0
round_test	-1	-1.00000	0	-1.0000000000	0	-0.99999999999999999999999999999999999990	0
round_test	0	0.12346	0	0.1234567890	0	0.12345678901234567890123456790123456780	0
round_test	0	-0.12346	0	-0.1234567890	0	-0.12345678901234567890123456790123456780	0
round_test	NULL	NULL	NULL	NULL	NULL	NULL	NULL
SELECT "round_test", d2 FROM cs1 WHERE ROUND(d2) = "123";
round_test	d2
round_test	123.4560000000
SELECT "truncate_test", TRUNCATE(d1, 0), TRUNCATE(d1,5), TRUNCATE(d1,-5), TRUNCATE(d1,10), TRUNCATE(d1,-10), TRUNCATE(d1,-38) FROM cs1;
truncate_test	TRUNCATE(d1, 0)	TRUNCATE(d1,5)	TRUNCATE(d1,-5)	TRUNCATE(d1,10)	TRUNCATE(d1,-10)	TRUNCATE(d1,-38)
truncate_test	123456	123456.00000	100000	123456.0000000000	0	0
truncate_test	-123456	-123456.00000	-100000	-123456.0000000000	0	0
truncate_test	9999999999999999999999999999999999999	9999999999999999999999999999999999999.00000	9999999999999999999999999999999900000	9999999999999999999999999999999999999.0000000000	9999999999999999999999999990000000000	0
truncate_test	-9999999999999999999999999999999999999	-9999999999999999999999999999999999999.00000	-9999999999999999999999999999999900000	-9999999999999999999999999999999999999.0000000000	-9999999999999999999999999990000000000	0
truncate_test	1234567890123456789012345679012345678	1234567890123456789012345679012345678.00000	1234567890123456789012345679012300000	1234567890123456789012345679012345678.0000000000	1234567890123456789012345670000000000	0
truncate_test	-1234567890123456789012345679012345678	-1234567890123456789012345679012345678.00000	-1234567890123456789012345679012300000	-1234567890123456789012345679012345678.0000000000	-1234567890123456789012345670000000000	0
truncate_test	NULL	NULL	NULL	NULL	NULL	NULL
SELECT "truncate_test", TRUNCATE(d2, 0), TRUNCATE(d2,5), TRUNCATE(d2,-5), TRUNCATE(d2,10), TRUNCATE(d2,-10), TRUNCATE(d2,38), TRUNCATE(d2,-38) FROM cs1;
truncate_test	TRUNCATE(d2, 0)	TRUNCATE(d2,5)	TRUNCATE(d2,-5)	TRUNCATE(d2,10)	TRUNCATE(d2,-10)	TRUNCATE(d2,38)	TRUNCATE(d2,-38)
truncate_test	123	123.45600	0	123.4560000000	0	123.45600000000000000000000000000000000000	0
truncate_test	-123	-123.45600	0	-123.4560000000	0	-123.45600000000000000000000000000000000000	0
truncate_test	999999999999999999999999999	999999999999999999999999999.99999	999999999999999999999900000	999999999999999999999999999.9999999999	999999999999999990000000000	999999999999999999999999999.99999999990000000000000000000000000000	0
truncate_test	-999999999999999999999999999	-999999999999999999999999999.99999	-999999999999999999999900000	-999999999999999999999999999.9999999999	-999999999999999990000000000	-999999999999999999999999999.99999999990000000000000000000000000000	0
truncate_test	123456789012345678901234567	123456789012345678901234567.90123	123456789012345678901200000	123456789012345678901234567.9012345678	123456789012345670000000000	123456789012345678901234567.90123456780000000000000000000000000000	0
truncate_test	-123456789012345678901234567	-123456789012345678901234567.90123	-123456789012345678901200000	-123456789012345678901234567.9012345678	-123456789012345670000000000	-123456789012345678901234567.90123456780000000000000000000000000000	0
truncate_test	NULL	NULL	NULL	NULL	NULL	NULL	NULL
SELECT "truncate_test", TRUNCATE(d3, 0), TRUNCATE(d3,5), TRUNCATE(d3,-5), TRUNCATE(d3,10), TRUNCATE(d3,-10), TRUNCATE(d3,38), TRUNCATE(d3,-38) FROM cs1;
truncate_test	TRUNCATE(d3, 0)	TRUNCATE(d3,5)	TRUNCATE(d3,-5)	TRUNCATE(d3,10)	TRUNCATE(d3,-10)	TRUNCATE(d3,38)	TRUNCATE(d3,-38)
truncate_test	0	0.12345	0	0.1234560000	0	0.12345600000000000000000000000000000000	0
truncate_test	0	-0.12345	0	-0.1234560000	0	-0.12345600000000000000000000000000000000	0
truncate_test	0	0.99999	0	0.9999999999	0	0.99999999999999999999999999999999999990	0
truncate_test	0	-0.99999	0	-0.9999999999	0	-0.99999999999999999999999999999999999990	0
truncate_test	0	0.12345	0	0.1234567890	0	0.12345678901234567890123456790123456780	0
truncate_test	0	-0.12345	0	-0.1234567890	0	-0.12345678901234567890123456790123456780	0
truncate_test	NULL	NULL	NULL	NULL	NULL	NULL	NULL
SELECT "truncate_test", d2 FROM cs1 WHERE TRUNCATE(d2, 0) = "123";
truncate_test	d2
truncate_test	123.4560000000
DROP TABLE IF EXISTS cs1;
CREATE TABLE cs1 (d1 DECIMAL(38,5), d2 DECIMAL(38,5), d3 DECIMAL(38), d4 DECIMAL(38), d5 DOUBLE) ENGINE=columnstore;
INSERT INTO cs1 VALUES
(1234.5678, 123.456, 12345678, 123456, 123456),
(1234567890123456789012345678.90123, 1234567890123456789012345678.90123, 12345678901234567890123456789012345678, 12345678901234567890123456789012345679, 123456781234),
(-1234.5678, 123.456, -12345678, 123456, -123456),
(1234567890123456789012345678.90124, -1234567890123456789012345678.90123, 12345678901234567890123456789012345678, -12345678901234567890123456789012345679, 123456781234);
SELECT "mod_test", MOD(d1,d2), MOD(d2,d1), MOD(d1,d3), MOD(d3,d4), MOD(d4,d3) FROM cs1;
mod_test	MOD(d1,d2)	MOD(d2,d1)	MOD(d1,d3)	MOD(d3,d4)	MOD(d4,d3)
mod_test	0.00780	123.45600	1234.56780	78	123456
mod_test	0.00000	0.00000	1234567890123456789012345678.90123	12345678901234567890123456789012345678	1
mod_test	-0.00780	123.45600	-1234.56780	-78	123456
mod_test	0.00001	-1234567890123456789012345678.90123	1234567890123456789012345678.90124	12345678901234567890123456789012345678	-1
DROP TABLE IF EXISTS cs1;
CREATE TABLE cs1 (d1 DECIMAL(38), d2 DECIMAL(38,10), d3 DECIMAL(38,38), d4 INT, d5 DOUBLE, d6 DECIMAL(18,10)) ENGINE=columnstore;
INSERT INTO cs1 VALUES
(12345678901234567890123456789012345678, 1234567890123456789012345678.9012345678, 0.12345678901234567890123456789012345678, 123456, 123456.789, 12345678.9012345678),
(123456, 123456.789, 0.123456789, 123456, 123456.789, 1234.567),
# Uncomment the below 2 inserts for 999... when MDEV-23032 is fixed.
#(99999999999999999999999999999999999999, 9999999999999999999999999999.9999999999, 0.99999999999999999999999999999999999999, 999999, 99999.999, 99999999.9999999999),
(999999, 999999.999, 0.9999999, 999999, 999999.999, 9999.999),
(999999, 999999.999, 0.9999999, 999999, 999998.999, 9999.999),
(-12345678901234567890123456789012345678, -1234567890123456789012345678.9012345678, -0.12345678901234567890123456789012345678, -123456, -123456.789, -12345678.9012345678),
(-123456, -123456.789, -0.123456789, -123456, -123456.789, -1234.567),
#(-99999999999999999999999999999999999999, -9999999999999999999999999999.9999999999, -0.99999999999999999999999999999999999999, -999999, -99999.999, -99999999.9999999999),
(-999999, -999999.999, -0.9999999, -999999, -999999.999, -9999.999);
SELECT "floor_test", FLOOR(d1), FLOOR(d2), FLOOR(d3), FLOOR(d4), FLOOR(d5), FLOOR(d6) FROM cs1;
floor_test	FLOOR(d1)	FLOOR(d2)	FLOOR(d3)	FLOOR(d4)	FLOOR(d5)	FLOOR(d6)
floor_test	12345678901234567890123456789012345678	1234567890123456789012345678	0	123456	123456	12345678
floor_test	123456	123456	0	123456	123456	1234
floor_test	999999	999999	0	999999	999999	9999
floor_test	999999	999999	0	999999	999998	9999
floor_test	-12345678901234567890123456789012345678	-1234567890123456789012345679	-1	-123456	-123457	-12345679
floor_test	-123456	-123457	-1	-123456	-123457	-1235
floor_test	-999999	-1000000	-1	-999999	-1000000	-10000
SELECT "floor_test", d1, d4 FROM cs1 WHERE FLOOR(d1) = FLOOR(d4);
floor_test	d1	d4
floor_test	123456	123456
floor_test	999999	999999
floor_test	999999	999999
floor_test	-123456	-123456
floor_test	-999999	-999999
SELECT "floor_test", d1, d4 FROM cs1 WHERE FLOOR(d1) = d4;
floor_test	d1	d4
floor_test	123456	123456
floor_test	999999	999999
floor_test	999999	999999
floor_test	-123456	-123456
floor_test	-999999	-999999
SELECT "floor_test", d1, d5 FROM cs1 WHERE FLOOR(d1) = FLOOR(d5);
floor_test	d1	d5
floor_test	123456	123456.789
floor_test	999999	999999.999
SELECT "floor_test", d1, d5 FROM cs1 WHERE FLOOR(d1) = d5;
floor_test	d1	d5
SELECT "floor_test", d1, d2 FROM cs1 WHERE FLOOR(d1) = FLOOR(d2);
floor_test	d1	d2
floor_test	123456	123456.7890000000
floor_test	999999	999999.9990000000
floor_test	999999	999999.9990000000
SELECT "ceil_test", CEIL(d1), CEIL(d2), CEIL(d3), CEIL(d4), CEIL(d5), CEIL(d6) FROM cs1;
ceil_test	CEIL(d1)	CEIL(d2)	CEIL(d3)	CEIL(d4)	CEIL(d5)	CEIL(d6)
ceil_test	12345678901234567890123456789012345678	1234567890123456789012345679	1	123456	123457	12345679
ceil_test	123456	123457	1	123456	123457	1235
ceil_test	999999	1000000	1	999999	1000000	10000
ceil_test	999999	1000000	1	999999	999999	10000
ceil_test	-12345678901234567890123456789012345678	-1234567890123456789012345678	0	-123456	-123456	-12345678
ceil_test	-123456	-123456	0	-123456	-123456	-1234
ceil_test	-999999	-999999	0	-999999	-999999	-9999
SELECT "ceil_test", d1, d4 FROM cs1 WHERE CEIL(d1) = CEIL(d4);
ceil_test	d1	d4
ceil_test	123456	123456
ceil_test	999999	999999
ceil_test	999999	999999
ceil_test	-123456	-123456
ceil_test	-999999	-999999
SELECT "ceil_test", d1, d4 FROM cs1 WHERE CEIL(d1) = d4;
ceil_test	d1	d4
ceil_test	123456	123456
ceil_test	999999	999999
ceil_test	999999	999999
ceil_test	-123456	-123456
ceil_test	-999999	-999999
SELECT "ceil_test", d1, d5 FROM cs1 WHERE CEIL(d1) = CEIL(d5);
ceil_test	d1	d5
ceil_test	999999	999998.999
ceil_test	-123456	-123456.789
ceil_test	-999999	-999999.999
SELECT "ceil_test", d1, d5 FROM cs1 WHERE CEIL(d1) = d5;
ceil_test	d1	d5
SELECT "ceil_test", d1, d2 FROM cs1 WHERE CEIL(d1) = CEIL(d2);
ceil_test	d1	d2
ceil_test	-123456	-123456.7890000000
ceil_test	-999999	-999999.9990000000
DROP TABLE IF EXISTS cs1;
CREATE TABLE cs1 (d1 DECIMAL(38), d2 DECIMAL(38,10), d3 DECIMAL(38,38), d4 DECIMAL(20)) ENGINE=columnstore;
INSERT INTO cs1 VALUES (31, 31, 0.31, 2019), (32, 32, 0.32, 2020), (33, 34.7, 0.345, 18);
SELECT "makedatetime_test", MAKEDATE(d4, d1), MAKEDATE(d4, d2), MAKEDATE(d4, d3), MAKETIME(d4, d2, d1) FROM cs1;
makedatetime_test	MAKEDATE(d4, d1)	MAKEDATE(d4, d2)	MAKEDATE(d4, d3)	MAKETIME(d4, d2, d1)
makedatetime_test	2019-01-31	2019-01-31	NULL	838:59:59
makedatetime_test	2020-02-01	2020-02-01	NULL	838:59:59
makedatetime_test	2018-02-02	2018-02-04	NULL	18:35:33
DROP TABLE IF EXISTS cs1;
CREATE TABLE cs1 (d1 DECIMAL(38), d2 DECIMAL(38,10)) ENGINE=columnstore;
INSERT INTO cs1 VALUES (0, 0.1), (2, 2.1), (1, 1.7), (-1, -1.2);
SELECT "elt_test", ELT(d1, "FIRST", "SECOND"), ELT(d2, "FIRST", "SECOND") FROM cs1;
elt_test	ELT(d1, "FIRST", "SECOND")	ELT(d2, "FIRST", "SECOND")
elt_test	NULL	NULL
elt_test	SECOND	SECOND
elt_test	FIRST	SECOND
elt_test	NULL	NULL
DROP TABLE IF EXISTS cs1;
CREATE TABLE cs1 (d1 DECIMAL(38), d2 DECIMAL(38,10), d3 DECIMAL(38,38), d4 INT, d5 DOUBLE) ENGINE=columnstore;
INSERT INTO cs1 VALUES
(12345678901234567890123456789012345678, 1234567890123456789012345678.9012345678, 0.12345678901234567890123456789012345678, 1234, 1.23e45),
(-12345678901234567890123456789012345678, -1234567890123456789012345678.9012345678, -0.12345678901234567890123456789012345678, -1234, -1.234e40);
SELECT "greatest_test", GREATEST(d1, 12345678901234567890123456789012345679) c1, GREATEST(d2, 1234567890123456789012345678.9012345679) c2,
GREATEST(d3, 0.12345678901234567890123456789012345677) c3, GREATEST(d1, d4) c4 FROM cs1;
greatest_test	c1	c2	c3	c4
greatest_test	12345678901234567890123456789012345679	1234567890123456789012345678.9012345679	0.12345678901234567890123456789012345678	12345678901234567890123456789012345678
greatest_test	12345678901234567890123456789012345679	1234567890123456789012345678.9012345679	0.12345678901234567890123456789012345677	-1234
SELECT "greatest_test", d2 FROM cs1 WHERE GREATEST(d2, d5) = d5;
greatest_test	d2
greatest_test	1234567890123456789012345678.9012345678
SELECT "least_test", LEAST(d1, 12345678901234567890123456789012345679) c1, LEAST(d2, 1234567890123456789012345678.9012345679) c2,
LEAST(d3, 0.12345678901234567890123456789012345677) c3, LEAST(d1, d4) c4 FROM cs1;
least_test	c1	c2	c3	c4
least_test	12345678901234567890123456789012345678	1234567890123456789012345678.9012345678	0.12345678901234567890123456789012345677	1234
least_test	-12345678901234567890123456789012345678	-1234567890123456789012345678.9012345678	-0.12345678901234567890123456789012345678	-12345678901234567890123456789012345678
SELECT "least_test", d2 FROM cs1 WHERE LEAST(d2, d5) = d5;
least_test	d2
least_test	-1234567890123456789012345678.9012345678
DROP TABLE IF EXISTS cs1;
CREATE TABLE cs1 (d1 DECIMAL(38), d2 DECIMAL(38, 10), d3 DECIMAL(38, 38)) ENGINE=columnstore;
INSERT INTO cs1 VALUES
(123456, 123.456, 0.123456),
(-123456, -123.456, -0.123456),
(99999999999999999999999999999999999998, 999999999999999999999999999.9999999998, 0.99999999999999999999999999999999999998),
(-99999999999999999999999999999999999998, -9999999999999999999999999999.9999999998, -0.99999999999999999999999999999999999998),
(12345678901234567890123456789012345678, 1234567890123456789012345678.9012345678, 0.12345678901234567890123456789012345678),
(-12345678901234567890123456789012345678, -1234567890123456789012345678.9012345678, -0.12345678901234567890123456789012345678),
(0,0,0),
(NULL, NULL, NULL);
SELECT "between_test", d1 BETWEEN 123455 AND 123457 f1,
d2 BETWEEN 999999999999999999999999999.9999999997 AND 999999999999999999999999999.9999999999 f2,
d3 BETWEEN -0.12345678901234567890123456789012345679 AND -0.12345678901234567890123456789012345677 f3 FROM cs1;
between_test	f1	f2	f3
between_test	1	0	0
between_test	0	0	0
between_test	0	1	0
between_test	0	0	0
between_test	0	0	0
between_test	0	0	1
between_test	0	0	0
between_test	NULL	NULL	NULL
SELECT "notbetween_test", d1 NOT BETWEEN 123455 AND 123457 f1,
d2 NOT BETWEEN 999999999999999999999999999.9999999997 AND 999999999999999999999999999.9999999999 f2,
d3 NOT BETWEEN -0.12345678901234567890123456789012345679 AND -0.12345678901234567890123456789012345677 f3 FROM cs1;
notbetween_test	f1	f2	f3
notbetween_test	0	1	1
notbetween_test	1	1	1
notbetween_test	1	0	1
notbetween_test	1	1	1
notbetween_test	1	1	1
notbetween_test	1	1	0
notbetween_test	1	1	1
notbetween_test	NULL	NULL	NULL
SELECT "in_test", d1 IN (99999999999999999999999999999999999998, -12345678901234567890123456789012345678) f1,
d2 IN (-9999999999999999999999999999.9999999998, 1234567890123456789012345678.9012345678) f2,
d3 IN (-0.99999999999999999999999999999999999998, -0.12345678901234567890123456789012345678) f3 FROM cs1;
in_test	f1	f2	f3
in_test	0	0	0
in_test	0	0	0
in_test	1	0	0
in_test	0	1	1
in_test	0	1	0
in_test	1	0	1
in_test	0	0	0
in_test	NULL	NULL	NULL
SELECT "notin_test", d1 NOT IN (99999999999999999999999999999999999998, -12345678901234567890123456789012345678) f1,
d2 NOT IN (-9999999999999999999999999999.9999999998, 1234567890123456789012345678.9012345678) f2,
d3 NOT IN (-0.99999999999999999999999999999999999998, -0.12345678901234567890123456789012345678) f3 FROM cs1;
notin_test	f1	f2	f3
notin_test	1	1	1
notin_test	1	1	1
notin_test	0	1	1
notin_test	1	0	0
notin_test	1	0	1
notin_test	0	1	0
notin_test	1	1	1
notin_test	NULL	NULL	NULL
SELECT "regex_test", d1 REGEXP "123", d2 REGEXP "8.9", d3 REGEXP "0.9" FROM cs1;
regex_test	d1 REGEXP "123"	d2 REGEXP "8.9"	d3 REGEXP "0.9"
regex_test	1	0	0
regex_test	1	0	0
regex_test	0	0	1
regex_test	0	0	1
regex_test	1	1	0
regex_test	1	1	0
regex_test	0	0	0
regex_test	NULL	NULL	NULL
SELECT "is(not)null_test", d1 IS NULL, d1 IS NOT NULL, d2 IS NULL, d2 IS NOT NULL, d3 IS NULL, d3 IS NOT NULL FROM cs1;
is(not)null_test	d1 IS NULL	d1 IS NOT NULL	d2 IS NULL	d2 IS NOT NULL	d3 IS NULL	d3 IS NOT NULL
is(not)null_test	0	1	0	1	0	1
is(not)null_test	0	1	0	1	0	1
is(not)null_test	0	1	0	1	0	1
is(not)null_test	0	1	0	1	0	1
is(not)null_test	0	1	0	1	0	1
is(not)null_test	0	1	0	1	0	1
is(not)null_test	0	1	0	1	0	1
is(not)null_test	1	0	1	0	1	0
SELECT "truthfunctions_test", d1 IS TRUE AS f1, d1 IS NOT TRUE AS f2, d1 IS FALSE AS f3, d1 IS NOT FALSE AS f4,
d2 IS TRUE AS f5, d2 IS NOT TRUE AS f6, d2 IS FALSE AS f7, d2 IS NOT FALSE AS f8,
d3 IS TRUE AS f9, d3 IS NOT TRUE AS f10, d3 IS FALSE AS f11, d3 IS NOT FALSE AS f12 FROM cs1;
truthfunctions_test	f1	f2	f3	f4	f5	f6	f7	f8	f9	f10	f11	f12
truthfunctions_test	1	0	0	1	1	0	0	1	1	0	0	1
truthfunctions_test	1	0	0	1	1	0	0	1	1	0	0	1
truthfunctions_test	1	0	0	1	1	0	0	1	1	0	0	1
truthfunctions_test	1	0	0	1	1	0	0	1	1	0	0	1
truthfunctions_test	1	0	0	1	1	0	0	1	1	0	0	1
truthfunctions_test	1	0	0	1	1	0	0	1	1	0	0	1
truthfunctions_test	0	1	1	0	0	1	1	0	0	1	1	0
truthfunctions_test	0	1	0	1	0	1	0	1	0	1	0	1
DROP TABLE IF EXISTS cs1;
CREATE TABLE cs1 (d1 DECIMAL(38, 10), d2 DECIMAL(38, 10), d3 DECIMAL(38, 10)) ENGINE=columnstore;
INSERT INTO cs1 VALUES
(0, 1234567890123456789012345678.9012345678, -1234567890123456789012345678.9012345678),
(1234567890123456789012345678.9012345678, 0, -1234567890123456789012345678.9012345678),
(1234567890123456789012345678.9012345678, -1234567890123456789012345678.9012345678, 0),
(NULL, -9999999999999999999999999999.9999999998, 9999999999999999999999999999.9999999998),
(-9999999999999999999999999999.9999999998, NULL, 9999999999999999999999999999.9999999998),
(-9999999999999999999999999999.9999999998, 9999999999999999999999999999.9999999998, NULL),
(NULL, NULL, NULL);
SELECT "if_test", IF(d1, d2, d3) AS f1, IF(d1, d3, d2) AS f2, IF(d2, d1, d3) AS f3, IF(d2, d3, d1) AS f4,
IF(d3, d1, d2) AS f5, IF(d3, d2, d1) AS f6 FROM cs1;
if_test	f1	f2	f3	f4	f5	f6
if_test	-1234567890123456789012345678.9012345678	1234567890123456789012345678.9012345678	0.0000000000	-1234567890123456789012345678.9012345678	0.0000000000	1234567890123456789012345678.9012345678
if_test	0.0000000000	-1234567890123456789012345678.9012345678	-1234567890123456789012345678.9012345678	1234567890123456789012345678.9012345678	1234567890123456789012345678.9012345678	0.0000000000
if_test	-1234567890123456789012345678.9012345678	0.0000000000	1234567890123456789012345678.9012345678	0.0000000000	-1234567890123456789012345678.9012345678	1234567890123456789012345678.9012345678
if_test	9999999999999999999999999999.9999999998	-9999999999999999999999999999.9999999998	NULL	9999999999999999999999999999.9999999998	NULL	-9999999999999999999999999999.9999999998
if_test	NULL	9999999999999999999999999999.9999999998	9999999999999999999999999999.9999999998	-9999999999999999999999999999.9999999998	-9999999999999999999999999999.9999999998	NULL
if_test	9999999999999999999999999999.9999999998	NULL	-9999999999999999999999999999.9999999998	NULL	9999999999999999999999999999.9999999998	-9999999999999999999999999999.9999999998
if_test	NULL	NULL	NULL	NULL	NULL	NULL
SELECT "coalesce_test", COALESCE(d1, d2, d3) AS f1, COALESCE(d1, d3, d2) AS f2, COALESCE(d2, d1, d3) AS f3,
COALESCE(d2, d3, d1) AS f4, COALESCE(d3, d1, d2) AS f5, COALESCE(d3, d2, d1) AS f6 FROM cs1;
coalesce_test	f1	f2	f3	f4	f5	f6
coalesce_test	0.0000000000	0.0000000000	1234567890123456789012345678.9012345678	1234567890123456789012345678.9012345678	-1234567890123456789012345678.9012345678	-1234567890123456789012345678.9012345678
coalesce_test	1234567890123456789012345678.9012345678	1234567890123456789012345678.9012345678	0.0000000000	0.0000000000	-1234567890123456789012345678.9012345678	-1234567890123456789012345678.9012345678
coalesce_test	1234567890123456789012345678.9012345678	1234567890123456789012345678.9012345678	-1234567890123456789012345678.9012345678	-1234567890123456789012345678.9012345678	0.0000000000	0.0000000000
coalesce_test	-9999999999999999999999999999.9999999998	9999999999999999999999999999.9999999998	-9999999999999999999999999999.9999999998	-9999999999999999999999999999.9999999998	9999999999999999999999999999.9999999998	9999999999999999999999999999.9999999998
coalesce_test	-9999999999999999999999999999.9999999998	-9999999999999999999999999999.9999999998	-9999999999999999999999999999.9999999998	9999999999999999999999999999.9999999998	9999999999999999999999999999.9999999998	9999999999999999999999999999.9999999998
coalesce_test	-9999999999999999999999999999.9999999998	-9999999999999999999999999999.9999999998	9999999999999999999999999999.9999999998	9999999999999999999999999999.9999999998	-9999999999999999999999999999.9999999998	9999999999999999999999999999.9999999998
coalesce_test	NULL	NULL	NULL	NULL	NULL	NULL
SELECT "case_test", CASE d1 WHEN 1234567890123456789012345678.9012345678 THEN d2 ELSE d3 END AS f1,
CASE d2 WHEN -1234567890123456789012345678.9012345678 THEN d1 ELSE d3 END AS f2,
CASE d3 WHEN -d1 THEN 9999999999999999999999999999.9999999999 ELSE -9999999999999999999999999999.9999999999 END AS f3
FROM cs1;
case_test	f1	f2	f3
case_test	-1234567890123456789012345678.9012345678	-1234567890123456789012345678.9012345678	-9999999999999999999999999999.9999999999
case_test	0.0000000000	-1234567890123456789012345678.9012345678	9999999999999999999999999999.9999999999
case_test	-1234567890123456789012345678.9012345678	1234567890123456789012345678.9012345678	-9999999999999999999999999999.9999999999
case_test	9999999999999999999999999999.9999999998	9999999999999999999999999999.9999999998	-9999999999999999999999999999.9999999999
case_test	9999999999999999999999999999.9999999998	9999999999999999999999999999.9999999998	9999999999999999999999999999.9999999999
case_test	NULL	NULL	-9999999999999999999999999999.9999999999
case_test	NULL	NULL	-9999999999999999999999999999.9999999999
DROP TABLE IF EXISTS cs1;
CREATE TABLE cs1 (d1 DECIMAL(38) UNSIGNED, d2 DECIMAL(38) UNSIGNED) ENGINE=columnstore;
INSERT INTO cs1 VALUES (123, 234), (999999999999999999999999999999, 999999999999999999999999999998), (999999999999999999, 999999999999999998), (1329227995784915872903807060280344576, 1), (1, 1329227995784915872903807060280344576);
SELECT "bitwise_and_test", d1 & d2, d1 & d1, d2 & d2, d1 & 10, d2 & 10 FROM cs1;
bitwise_and_test	d1 & d2	d1 & d1	d2 & d2	d1 & 10	d2 & 10
bitwise_and_test	106	123	234	10	10
bitwise_and_test	18446744073709551615	18446744073709551615	18446744073709551615	10	10
bitwise_and_test	999999999999999998	999999999999999999	999999999999999998	10	10
bitwise_and_test	1	18446744073709551615	1	10	0
bitwise_and_test	1	1	18446744073709551615	0	10
SELECT "bitwise_or_test", d1 | d2, d1 | d1, d2 | d2, d1 | 10, d2 | 10 FROM cs1;
bitwise_or_test	d1 | d2	d1 | d1	d2 | d2	d1 | 10	d2 | 10
bitwise_or_test	251	123	234	123	234
bitwise_or_test	18446744073709551615	18446744073709551615	18446744073709551615	18446744073709551615	18446744073709551615
bitwise_or_test	999999999999999999	999999999999999999	999999999999999998	999999999999999999	999999999999999998
bitwise_or_test	18446744073709551615	18446744073709551615	1	18446744073709551615	11
bitwise_or_test	18446744073709551615	1	18446744073709551615	11	18446744073709551615
SELECT "bitwise_xor_test", d1 ^ d2, d1 ^ d1, d2 ^ d2, d1 ^ 10, d2 ^ 10 FROM cs1 WHERE d1 != 999999999999999999;
bitwise_xor_test	d1 ^ d2	d1 ^ d1	d2 ^ d2	d1 ^ 10	d2 ^ 10
bitwise_xor_test	145	0	0	113	224
bitwise_xor_test	0	0	0	18446744073709551605	18446744073709551605
bitwise_xor_test	NULL	0	0	18446744073709551605	11
bitwise_xor_test	NULL	0	0	11	18446744073709551605
SELECT "bitwise_leftshift_test", d1 << 1, d1 << 10, d1 << 20, d2 << 1, d2 << 10, d2 << 20 FROM cs1 WHERE d1 <= 999999999999999999;
bitwise_leftshift_test	d1 << 1	d1 << 10	d1 << 20	d2 << 1	d2 << 10	d2 << 20
bitwise_leftshift_test	246	125952	128974848	468	239616	245366784
bitwise_leftshift_test	1999999999999999998	9429075945974660096	7726618127956443136	1999999999999999996	9429075945974659072	7726618127955394560
bitwise_leftshift_test	2	1024	1048576	NULL	18446744073709550592	18446744073708503040
SELECT "bitwise_rightshift_test", d1 >> 1, d1 >> 10, d1 >> 20, d2 >> 1, d2 >> 10, d2 >> 20 FROM cs1;
bitwise_rightshift_test	d1 >> 1	d1 >> 10	d1 >> 20	d2 >> 1	d2 >> 10	d2 >> 20
bitwise_rightshift_test	61	0	0	117	0	0
bitwise_rightshift_test	9223372036854775807	18014398509481983	17592186044415	9223372036854775807	18014398509481983	17592186044415
bitwise_rightshift_test	499999999999999999	976562499999999	953674316406	499999999999999999	976562499999999	953674316406
bitwise_rightshift_test	9223372036854775807	18014398509481983	17592186044415	0	0	0
bitwise_rightshift_test	0	0	0	9223372036854775807	18014398509481983	17592186044415
SELECT "bitcount_test", bit_count(d1), bit_count(d2) FROM cs1;
bitcount_test	bit_count(d1)	bit_count(d2)
bitcount_test	6	5
bitcount_test	64	64
bitcount_test	41	40
bitcount_test	64	1
bitcount_test	1	64
DELETE FROM cs1;
INSERT INTO cs1 VALUES (2001, 2011), (199001, 199010);
SELECT "period_diff_test", PERIOD_DIFF(d1, d2), PERIOD_DIFF(d2, d1), PERIOD_DIFF(d1, d1) FROM cs1;
period_diff_test	PERIOD_DIFF(d1, d2)	PERIOD_DIFF(d2, d1)	PERIOD_DIFF(d1, d1)
period_diff_test	-10	10	0
period_diff_test	-9	9	0
DROP TABLE IF EXISTS cs1;
CREATE TABLE cs1 (d1 DECIMAL(38) UNSIGNED) ENGINE=columnstore;
INSERT INTO cs1 VALUES (123), (234), (99999999999999999999999999999999999999), (99999999999999999999999999999999999998), (12345678901234567890123456789012345678), (12345678901234567890123456789012345679), (1329227995784915872903807060280344576), (1329227995784915872903807060280344577);
SELECT "cast_test1", CAST(d1 AS SIGNED) FROM cs1 WHERE d1 > 12345678901234567890123456789012345678;
cast_test1	CAST(d1 AS SIGNED)
cast_test1	9223372036854775807
cast_test1	9223372036854775807
cast_test1	9223372036854775807
SELECT "cast_test2", CAST(d1 AS UNSIGNED), CAST(d1 AS CHAR(50)) FROM cs1;
cast_test2	CAST(d1 AS UNSIGNED)	CAST(d1 AS CHAR(50))
cast_test2	123	123
cast_test2	234	234
cast_test2	18446744073709551615	99999999999999999999999999999999999999
cast_test2	18446744073709551615	99999999999999999999999999999999999998
cast_test2	18446744073709551615	12345678901234567890123456789012345678
cast_test2	18446744073709551615	12345678901234567890123456789012345679
cast_test2	18446744073709551615	1329227995784915872903807060280344576
cast_test2	18446744073709551615	1329227995784915872903807060280344577
DELETE FROM cs1;
INSERT INTO cs1 VALUES (20201212), (19901028183200);
SELECT "cast_test3", CAST(d1 AS DATETIME), CAST(d1 AS DATE) FROM cs1;
cast_test3	CAST(d1 AS DATETIME)	CAST(d1 AS DATE)
cast_test3	2020-12-12 00:00:00	2020-12-12
cast_test3	1990-10-28 18:32:00	1990-10-28
DROP TABLE IF EXISTS cs1;
CREATE TABLE cs1 (d1 DECIMAL(38,10) UNSIGNED) ENGINE=columnstore;
INSERT INTO cs1 VALUES (123), (234), (123.456), (234.567), (1234567890123456789012345678.9012345678), (9999999999999999999999999999.9999999999);
SELECT "cast_test4", CAST(d1 AS DOUBLE) FROM cs1;
cast_test4	CAST(d1 AS DOUBLE)
cast_test4	123
cast_test4	234
cast_test4	123.456
cast_test4	234.567
cast_test4	1.2345678901234569e27
cast_test4	1e28
DROP TABLE IF EXISTS cs1;
CREATE TABLE cs1 (d1 DECIMAL(38) UNSIGNED, d2 DECIMAL(38,10) UNSIGNED, d3 DECIMAL(38,38) UNSIGNED, d4 BIGINT UNSIGNED, d5 CHAR(40), d6 FLOAT UNSIGNED, d7 DOUBLE UNSIGNED) ENGINE=columnstore;
INSERT INTO cs1 VALUES (123, 123.456, .123, 123, "123", 123.456, 123.456),
(12345678901234567890123456789012345678, 1234567890123456789012345678.9012345678, .12345678901234567890123456789012345678, 123456789012345678, "12345678901234567890123456789012345678", 1234567890.1234567890, 12345678901234567.123456789012345678),
(99999999999999999999999999999999999999, 9999999999999999999999999999.9999999999, .99999999999999999999999999999999999999, 999999999999999999, "99999999999999999999999999999999999999", 9999999999.9999999999, 99999999999999999999.999999999999999999);
INSERT INTO cs1(d5) VALUES ("1.1234567890123456789012345e25");
SELECT "cast_test5", CAST(d1 as DECIMAL(38,0)), CAST(d2 as DECIMAL(38,0)), CAST(d3 as DECIMAL(38,0)), CAST(d4 as DECIMAL(38,0)), CAST(d5 as DECIMAL(38,0)), CAST(d6 as DECIMAL(38,0)), CAST(d7 as DECIMAL(38,0)) FROM cs1;
cast_test5	CAST(d1 as DECIMAL(38,0))	CAST(d2 as DECIMAL(38,0))	CAST(d3 as DECIMAL(38,0))	CAST(d4 as DECIMAL(38,0))	CAST(d5 as DECIMAL(38,0))	CAST(d6 as DECIMAL(38,0))	CAST(d7 as DECIMAL(38,0))
cast_test5	123	123	0	123	123	123	123
cast_test5	12345678901234567890123456789012345678	1234567890123456789012345679	0	123456789012345678	12345678901234567890123456789012345678	1234567936	12345678901234568
cast_test5	99999999999999999999999999999999999999	10000000000000000000000000000	1	999999999999999999	99999999999999999999999999999999999999	10000000000	100000000000000000000
cast_test5	NULL	NULL	NULL	NULL	11234567890123456789012345	NULL	NULL
SELECT "cast_test5", CAST(d2 as DECIMAL(38,10)), CAST(d3 as DECIMAL(38,10)), CAST(d4 as DECIMAL(38,10)), CAST(d6 as DECIMAL(38,10)) FROM cs1;
cast_test5	CAST(d2 as DECIMAL(38,10))	CAST(d3 as DECIMAL(38,10))	CAST(d4 as DECIMAL(38,10))	CAST(d6 as DECIMAL(38,10))
cast_test5	123.4560000000	0.1230000000	123.0000000000	123.4560012817
cast_test5	1234567890123456789012345678.9012345678	0.1234567890	123456789012345678.0000000000	1234567936.0000000000
cast_test5	9999999999999999999999999999.9999999999	1.0000000000	999999999999999999.0000000000	10000000000.0000000000
cast_test5	NULL	NULL	NULL	NULL
SELECT "cast_test5", CAST(d3 as DECIMAL(38,38)), CAST(d6 as DECIMAL(38,38)), CAST(d7 as DECIMAL(38,38)) FROM cs1;
cast_test5	CAST(d3 as DECIMAL(38,38))	CAST(d6 as DECIMAL(38,38))	CAST(d7 as DECIMAL(38,38))
cast_test5	0.12300000000000000000000000000000000000	0.99999999999999999999999999999999999999	0.99999999999999999999999999999999999999
cast_test5	0.12345678901234567890123456789012345678	0.99999999999999999999999999999999999999	0.99999999999999999999999999999999999999
cast_test5	0.99999999999999999999999999999999999999	0.99999999999999999999999999999999999999	0.99999999999999999999999999999999999999
cast_test5	NULL	NULL	NULL
DROP TABLE IF EXISTS cs1;
CREATE TABLE cs1 (d1 DECIMAL(38) UNSIGNED) ENGINE=columnstore;
INSERT INTO cs1 VALUES (127), (1278), (1234567890);
SELECT "inet_test", INET_NTOA(d1) FROM cs1;
inet_test	INET_NTOA(d1)
inet_test	0.0.0.127
inet_test	0.0.4.254
inet_test	73.150.2.210
SELECT "inet_test", d1 FROM cs1 where INET_ATON(d1) = d1;
inet_test	d1
inet_test	127
DELETE FROM cs1;
INSERT INTO cs1 VALUES (1296911693), (77);
SELECT "char_test", CHAR(d1) FROM cs1;
char_test	CHAR(d1)
char_test	MMMM
char_test	M
INSERT INTO cs1 VALUES(20201010), (19901230), (1234), (12345);
SELECT "monthname_test", MONTHNAME(d1), SEC_TO_TIME(d1) FROM cs1;
monthname_test	MONTHNAME(d1)	SEC_TO_TIME(d1)
monthname_test	NULL	838:59:59
monthname_test	NULL	00:01:17
monthname_test	October	838:59:59
monthname_test	December	838:59:59
monthname_test	NULL	00:20:34
monthname_test	NULL	03:25:45
DROP TABLE IF EXISTS cs1;
CREATE TABLE cs1 (d1 DECIMAL(38) UNSIGNED, d2 DECIMAL(38, 10) UNSIGNED, d3 DECIMAL(38, 38) UNSIGNED) ENGINE=columnstore;
INSERT INTO cs1 VALUES
(123456, 123.456, 0.123456),
(99999999999999999999999999999999999999, 9999999999999999999999999999.9999999999, 0.99999999999999999999999999999999999999),
(NULL, NULL, NULL),
(1234, 1234, 0.1234),
(1234, 1234, 0.1234);
SELECT "abs_test", ABS(d1), ABS(d2), ABS(d3) FROM cs1;
abs_test	ABS(d1)	ABS(d2)	ABS(d3)
abs_test	123456	123.4560000000	0.12345600000000000000000000000000000000
abs_test	99999999999999999999999999999999999999	9999999999999999999999999999.9999999999	0.99999999999999999999999999999999999999
abs_test	NULL	NULL	NULL
abs_test	1234	1234.0000000000	0.12340000000000000000000000000000000000
abs_test	1234	1234.0000000000	0.12340000000000000000000000000000000000
DELETE FROM cs1;
INSERT INTO cs1 VALUES
(123456, 123.456, 0.123456),
(9999999999999999999999999999999999999, 999999999999999999999999999.9999999999, 0.9999999999999999999999999999999999999),
(1234567890123456789012345679012345678, 123456789012345678901234567.9012345678, 0.1234567890123456789012345679012345678),
(NULL, NULL, NULL);
SELECT "round_test", ROUND(d1), ROUND(d1,5), ROUND(d1,-5), ROUND(d1,10), ROUND(d1,-10), ROUND(d1,-38) FROM cs1;
round_test	ROUND(d1)	ROUND(d1,5)	ROUND(d1,-5)	ROUND(d1,10)	ROUND(d1,-10)	ROUND(d1,-38)
round_test	123456	123456.00000	100000	123456.0000000000	0	0
round_test	9999999999999999999999999999999999999	9999999999999999999999999999999999999.00000	10000000000000000000000000000000000000	9999999999999999999999999999999999999.0000000000	10000000000000000000000000000000000000	0
round_test	1234567890123456789012345679012345678	1234567890123456789012345679012345678.00000	1234567890123456789012345679012300000	1234567890123456789012345679012345678.0000000000	1234567890123456789012345680000000000	0
round_test	NULL	NULL	NULL	NULL	NULL	NULL
SELECT "round_test", ROUND(d2), ROUND(d2,5), ROUND(d2,-5), ROUND(d2,10), ROUND(d2,-10), ROUND(d2,38), ROUND(d2,-38) FROM cs1;
round_test	ROUND(d2)	ROUND(d2,5)	ROUND(d2,-5)	ROUND(d2,10)	ROUND(d2,-10)	ROUND(d2,38)	ROUND(d2,-38)
round_test	123	123.45600	0	123.4560000000	0	123.45600000000000000000000000000000000000	0
round_test	1000000000000000000000000000	1000000000000000000000000000.00000	1000000000000000000000000000	999999999999999999999999999.9999999999	1000000000000000000000000000	999999999999999999999999999.99999999990000000000000000000000000000	0
round_test	123456789012345678901234568	123456789012345678901234567.90123	123456789012345678901200000	123456789012345678901234567.9012345678	123456789012345680000000000	123456789012345678901234567.90123456780000000000000000000000000000	0
round_test	NULL	NULL	NULL	NULL	NULL	NULL	NULL
SELECT "round_test", ROUND(d3), ROUND(d3,5), ROUND(d3,-5), ROUND(d3,10), ROUND(d3,-10), ROUND(d3,38), ROUND(d3,-38) FROM cs1;
round_test	ROUND(d3)	ROUND(d3,5)	ROUND(d3,-5)	ROUND(d3,10)	ROUND(d3,-10)	ROUND(d3,38)	ROUND(d3,-38)
round_test	0	0.12346	0	0.1234560000	0	0.12345600000000000000000000000000000000	0
round_test	1	1.00000	0	1.0000000000	0	0.99999999999999999999999999999999999990	0
round_test	0	0.12346	0	0.1234567890	0	0.12345678901234567890123456790123456780	0
round_test	NULL	NULL	NULL	NULL	NULL	NULL	NULL
SELECT "round_test", d2 FROM cs1 WHERE ROUND(d2) = "123";
round_test	d2
round_test	123.4560000000
SELECT "truncate_test", TRUNCATE(d1, 0), TRUNCATE(d1,5), TRUNCATE(d1,-5), TRUNCATE(d1,10), TRUNCATE(d1,-10), TRUNCATE(d1,-38) FROM cs1;
truncate_test	TRUNCATE(d1, 0)	TRUNCATE(d1,5)	TRUNCATE(d1,-5)	TRUNCATE(d1,10)	TRUNCATE(d1,-10)	TRUNCATE(d1,-38)
truncate_test	123456	123456.00000	100000	123456.0000000000	0	0
truncate_test	9999999999999999999999999999999999999	9999999999999999999999999999999999999.00000	9999999999999999999999999999999900000	9999999999999999999999999999999999999.0000000000	9999999999999999999999999990000000000	0
truncate_test	1234567890123456789012345679012345678	1234567890123456789012345679012345678.00000	1234567890123456789012345679012300000	1234567890123456789012345679012345678.0000000000	1234567890123456789012345670000000000	0
truncate_test	NULL	NULL	NULL	NULL	NULL	NULL
SELECT "truncate_test", TRUNCATE(d2, 0), TRUNCATE(d2,5), TRUNCATE(d2,-5), TRUNCATE(d2,10), TRUNCATE(d2,-10), TRUNCATE(d2,38), TRUNCATE(d2,-38) FROM cs1;
truncate_test	TRUNCATE(d2, 0)	TRUNCATE(d2,5)	TRUNCATE(d2,-5)	TRUNCATE(d2,10)	TRUNCATE(d2,-10)	TRUNCATE(d2,38)	TRUNCATE(d2,-38)
truncate_test	123	123.45600	0	123.4560000000	0	123.45600000000000000000000000000000000000	0
truncate_test	999999999999999999999999999	999999999999999999999999999.99999	999999999999999999999900000	999999999999999999999999999.9999999999	999999999999999990000000000	999999999999999999999999999.99999999990000000000000000000000000000	0
truncate_test	123456789012345678901234567	123456789012345678901234567.90123	123456789012345678901200000	123456789012345678901234567.9012345678	123456789012345670000000000	123456789012345678901234567.90123456780000000000000000000000000000	0
truncate_test	NULL	NULL	NULL	NULL	NULL	NULL	NULL
SELECT "truncate_test", TRUNCATE(d3, 0), TRUNCATE(d3,5), TRUNCATE(d3,-5), TRUNCATE(d3,10), TRUNCATE(d3,-10), TRUNCATE(d3,38), TRUNCATE(d3,-38) FROM cs1;
truncate_test	TRUNCATE(d3, 0)	TRUNCATE(d3,5)	TRUNCATE(d3,-5)	TRUNCATE(d3,10)	TRUNCATE(d3,-10)	TRUNCATE(d3,38)	TRUNCATE(d3,-38)
truncate_test	0	0.12345	0	0.1234560000	0	0.12345600000000000000000000000000000000	0
truncate_test	0	0.99999	0	0.9999999999	0	0.99999999999999999999999999999999999990	0
truncate_test	0	0.12345	0	0.1234567890	0	0.12345678901234567890123456790123456780	0
truncate_test	NULL	NULL	NULL	NULL	NULL	NULL	NULL
SELECT "truncate_test", d2 FROM cs1 WHERE TRUNCATE(d2, 0) = "123";
truncate_test	d2
truncate_test	123.4560000000
DROP TABLE IF EXISTS cs1;
CREATE TABLE cs1 (d1 DECIMAL(38,5) UNSIGNED, d2 DECIMAL(38,5) UNSIGNED, d3 DECIMAL(38) UNSIGNED, d4 DECIMAL(38) UNSIGNED, d5 DOUBLE UNSIGNED) ENGINE=columnstore;
INSERT INTO cs1 VALUES
(1234.5678, 123.456, 12345678, 123456, 123456),
(1234567890123456789012345678.90123, 1234567890123456789012345678.90123, 12345678901234567890123456789012345678, 12345678901234567890123456789012345679, 123456781234);
SELECT "mod_test", MOD(d1,d2), MOD(d2,d1), MOD(d1,d3), MOD(d3,d4), MOD(d4,d3) FROM cs1;
mod_test	MOD(d1,d2)	MOD(d2,d1)	MOD(d1,d3)	MOD(d3,d4)	MOD(d4,d3)
mod_test	0.00780	123.45600	1234.56780	78	123456
mod_test	0.00000	0.00000	1234567890123456789012345678.90123	12345678901234567890123456789012345678	1
DROP TABLE IF EXISTS cs1;
CREATE TABLE cs1 (d1 DECIMAL(38) UNSIGNED, d2 DECIMAL(38,10) UNSIGNED, d3 DECIMAL(38,38) UNSIGNED, d4 INT UNSIGNED, d5 DOUBLE UNSIGNED, d6 DECIMAL(18,10) UNSIGNED) ENGINE=columnstore;
INSERT INTO cs1 VALUES
(12345678901234567890123456789012345678, 1234567890123456789012345678.9012345678, 0.12345678901234567890123456789012345678, 123456, 123456.789, 12345678.9012345678),
(123456, 123456.789, 0.123456789, 123456, 123456.789, 1234.567),
# Uncomment the below 2 inserts for 999... when MDEV-23032 is fixed.
#(99999999999999999999999999999999999999, 9999999999999999999999999999.9999999999, 0.99999999999999999999999999999999999999, 999999, 99999.999, 99999999.9999999999),
(999999, 999999.999, 0.9999999, 999999, 999999.999, 9999.999),
(999999, 999999.999, 0.9999999, 999999, 999998.999, 9999.999);
SELECT "floor_test", FLOOR(d1), FLOOR(d2), FLOOR(d3), FLOOR(d4), FLOOR(d5), FLOOR(d6) FROM cs1;
floor_test	FLOOR(d1)	FLOOR(d2)	FLOOR(d3)	FLOOR(d4)	FLOOR(d5)	FLOOR(d6)
floor_test	12345678901234567890123456789012345678	1234567890123456789012345678	0	123456	123456	12345678
floor_test	123456	123456	0	123456	123456	1234
floor_test	999999	999999	0	999999	999999	9999
floor_test	999999	999999	0	999999	999998	9999
SELECT "floor_test", d1, d4 FROM cs1 WHERE FLOOR(d1) = FLOOR(d4);
floor_test	d1	d4
floor_test	123456	123456
floor_test	999999	999999
floor_test	999999	999999
SELECT "floor_test", d1, d4 FROM cs1 WHERE FLOOR(d1) = d4;
floor_test	d1	d4
floor_test	123456	123456
floor_test	999999	999999
floor_test	999999	999999
SELECT "floor_test", d1, d5 FROM cs1 WHERE FLOOR(d1) = FLOOR(d5);
floor_test	d1	d5
floor_test	123456	123456.789
floor_test	999999	999999.999
SELECT "floor_test", d1, d5 FROM cs1 WHERE FLOOR(d1) = d5;
floor_test	d1	d5
SELECT "floor_test", d1, d2 FROM cs1 WHERE FLOOR(d1) = FLOOR(d2);
floor_test	d1	d2
floor_test	123456	123456.7890000000
floor_test	999999	999999.9990000000
floor_test	999999	999999.9990000000
SELECT "ceil_test", CEIL(d1), CEIL(d2), CEIL(d3), CEIL(d4), CEIL(d5), CEIL(d6) FROM cs1;
ceil_test	CEIL(d1)	CEIL(d2)	CEIL(d3)	CEIL(d4)	CEIL(d5)	CEIL(d6)
ceil_test	12345678901234567890123456789012345678	1234567890123456789012345679	1	123456	123457	12345679
ceil_test	123456	123457	1	123456	123457	1235
ceil_test	999999	1000000	1	999999	1000000	10000
ceil_test	999999	1000000	1	999999	999999	10000
SELECT "ceil_test", d1, d4 FROM cs1 WHERE CEIL(d1) = CEIL(d4);
ceil_test	d1	d4
ceil_test	123456	123456
ceil_test	999999	999999
ceil_test	999999	999999
SELECT "ceil_test", d1, d4 FROM cs1 WHERE CEIL(d1) = d4;
ceil_test	d1	d4
ceil_test	123456	123456
ceil_test	999999	999999
ceil_test	999999	999999
SELECT "ceil_test", d1, d5 FROM cs1 WHERE CEIL(d1) = CEIL(d5);
ceil_test	d1	d5
ceil_test	999999	999998.999
SELECT "ceil_test", d1, d5 FROM cs1 WHERE CEIL(d1) = d5;
ceil_test	d1	d5
SELECT "ceil_test", d1, d2 FROM cs1 WHERE CEIL(d1) = CEIL(d2);
ceil_test	d1	d2
DROP TABLE IF EXISTS cs1;
CREATE TABLE cs1 (d1 DECIMAL(38) UNSIGNED, d2 DECIMAL(38,10) UNSIGNED, d3 DECIMAL(38,38) UNSIGNED, d4 DECIMAL(20) UNSIGNED) ENGINE=columnstore;
INSERT INTO cs1 VALUES (31, 31, 0.31, 2019), (32, 32, 0.32, 2020), (33, 34.7, 0.345, 18);
SELECT "makedatetime_test", MAKEDATE(d4, d1), MAKEDATE(d4, d2), MAKEDATE(d4, d3), MAKETIME(d4, d2, d1) FROM cs1;
makedatetime_test	MAKEDATE(d4, d1)	MAKEDATE(d4, d2)	MAKEDATE(d4, d3)	MAKETIME(d4, d2, d1)
makedatetime_test	2019-01-31	2019-01-31	NULL	838:59:59
makedatetime_test	2020-02-01	2020-02-01	NULL	838:59:59
makedatetime_test	2018-02-02	2018-02-04	NULL	18:35:33
DROP TABLE IF EXISTS cs1;
CREATE TABLE cs1 (d1 DECIMAL(38) UNSIGNED, d2 DECIMAL(38,10) UNSIGNED) ENGINE=columnstore;
INSERT INTO cs1 VALUES (0, 0.1), (2, 2.1), (1, 1.7), (1, 1.2);
SELECT "elt_test", ELT(d1, "FIRST", "SECOND"), ELT(d2, "FIRST", "SECOND") FROM cs1;
elt_test	ELT(d1, "FIRST", "SECOND")	ELT(d2, "FIRST", "SECOND")
elt_test	NULL	NULL
elt_test	SECOND	SECOND
elt_test	FIRST	SECOND
elt_test	FIRST	FIRST
DROP TABLE IF EXISTS cs1;
CREATE TABLE cs1 (d1 DECIMAL(38) UNSIGNED, d2 DECIMAL(38,10) UNSIGNED, d3 DECIMAL(38,38) UNSIGNED, d4 INT UNSIGNED, d5 DOUBLE UNSIGNED) ENGINE=columnstore;
INSERT INTO cs1 VALUES
(12345678901234567890123456789012345678, 1234567890123456789012345678.9012345678, 0.12345678901234567890123456789012345678, 1234, 1.23e45);
SELECT "greatest_test", GREATEST(d1, 12345678901234567890123456789012345679) c1, GREATEST(d2, 1234567890123456789012345678.9012345679) c2,
GREATEST(d3, 0.12345678901234567890123456789012345677) c3, GREATEST(d1, d4) c4 FROM cs1;
greatest_test	c1	c2	c3	c4
greatest_test	12345678901234567890123456789012345679	1234567890123456789012345678.9012345679	0.12345678901234567890123456789012345678	12345678901234567890123456789012345678
SELECT "greatest_test", d2 FROM cs1 WHERE GREATEST(d2, d5) = d5;
greatest_test	d2
greatest_test	1234567890123456789012345678.9012345678
SELECT "least_test", LEAST(d1, 12345678901234567890123456789012345679) c1, LEAST(d2, 1234567890123456789012345678.9012345679) c2,
LEAST(d3, 0.12345678901234567890123456789012345677) c3, LEAST(d1, d4) c4 FROM cs1;
least_test	c1	c2	c3	c4
least_test	12345678901234567890123456789012345678	1234567890123456789012345678.9012345678	0.12345678901234567890123456789012345677	1234
SELECT "least_test", d2 FROM cs1 WHERE LEAST(d2, d5) = d5;
least_test	d2
DROP TABLE IF EXISTS cs1;
CREATE TABLE cs1 (d1 DECIMAL(38) UNSIGNED, d2 DECIMAL(38, 10) UNSIGNED, d3 DECIMAL(38, 38) UNSIGNED) ENGINE=columnstore;
INSERT INTO cs1 VALUES
(123456, 123.456, 0.123456),
(99999999999999999999999999999999999998, 999999999999999999999999999.9999999998, 0.99999999999999999999999999999999999998),
(12345678901234567890123456789012345678, 1234567890123456789012345678.9012345678, 0.12345678901234567890123456789012345678),
(0,0,0),
(NULL, NULL, NULL);
SELECT "between_test", d1 BETWEEN 123455 AND 123457 f1,
d2 BETWEEN 999999999999999999999999999.9999999997 AND 999999999999999999999999999.9999999999 f2,
d3 BETWEEN 0.12345678901234567890123456789012345679 AND 0.12345678901234567890123456789012345677 f3 FROM cs1;
between_test	f1	f2	f3
between_test	1	0	0
between_test	0	1	0
between_test	0	0	0
between_test	0	0	0
between_test	NULL	NULL	NULL
SELECT "notbetween_test", d1 NOT BETWEEN 123455 AND 123457 f1,
d2 NOT BETWEEN 999999999999999999999999999.9999999997 AND 999999999999999999999999999.9999999999 f2,
d3 NOT BETWEEN 0.12345678901234567890123456789012345679 AND 0.12345678901234567890123456789012345677 f3 FROM cs1;
notbetween_test	f1	f2	f3
notbetween_test	0	1	1
notbetween_test	1	0	1
notbetween_test	1	1	1
notbetween_test	1	1	1
notbetween_test	NULL	NULL	NULL
SELECT "in_test", d1 IN (99999999999999999999999999999999999998, 12345678901234567890123456789012345678) f1,
d2 IN (9999999999999999999999999999.9999999998, 123.456) f2,
d3 IN (0.123456, 0.12345678901234567890123456789012345678) f3 FROM cs1;
in_test	f1	f2	f3
in_test	0	1	1
in_test	1	0	0
in_test	1	0	1
in_test	0	0	0
in_test	NULL	NULL	NULL
SELECT "notin_test", d1 NOT IN (99999999999999999999999999999999999998, 12345678901234567890123456789012345678) f1,
d2 NOT IN (9999999999999999999999999999.9999999998, 123.456) f2,
d3 NOT IN (0.123456, 0.12345678901234567890123456789012345678) f3 FROM cs1;
notin_test	f1	f2	f3
notin_test	1	0	0
notin_test	0	1	1
notin_test	0	1	0
notin_test	1	1	1
notin_test	NULL	NULL	NULL
SELECT "regex_test", d1 REGEXP "123", d2 REGEXP "8.9", d3 REGEXP "0.9" FROM cs1;
regex_test	d1 REGEXP "123"	d2 REGEXP "8.9"	d3 REGEXP "0.9"
regex_test	1	0	0
regex_test	0	0	1
regex_test	1	1	0
regex_test	0	0	0
regex_test	NULL	NULL	NULL
SELECT "is(not)null_test", d1 IS NULL, d1 IS NOT NULL, d2 IS NULL, d2 IS NOT NULL, d3 IS NULL, d3 IS NOT NULL FROM cs1;
is(not)null_test	d1 IS NULL	d1 IS NOT NULL	d2 IS NULL	d2 IS NOT NULL	d3 IS NULL	d3 IS NOT NULL
is(not)null_test	0	1	0	1	0	1
is(not)null_test	0	1	0	1	0	1
is(not)null_test	0	1	0	1	0	1
is(not)null_test	0	1	0	1	0	1
is(not)null_test	1	0	1	0	1	0
SELECT "truthfunctions_test", d1 IS TRUE AS f1, d1 IS NOT TRUE AS f2, d1 IS FALSE AS f3, d1 IS NOT FALSE AS f4,
d2 IS TRUE AS f5, d2 IS NOT TRUE AS f6, d2 IS FALSE AS f7, d2 IS NOT FALSE AS f8,
d3 IS TRUE AS f9, d3 IS NOT TRUE AS f10, d3 IS FALSE AS f11, d3 IS NOT FALSE AS f12 FROM cs1;
truthfunctions_test	f1	f2	f3	f4	f5	f6	f7	f8	f9	f10	f11	f12
truthfunctions_test	1	0	0	1	1	0	0	1	1	0	0	1
truthfunctions_test	1	0	0	1	1	0	0	1	1	0	0	1
truthfunctions_test	1	0	0	1	1	0	0	1	1	0	0	1
truthfunctions_test	0	1	1	0	0	1	1	0	0	1	1	0
truthfunctions_test	0	1	0	1	0	1	0	1	0	1	0	1
DROP TABLE IF EXISTS cs1;
CREATE TABLE cs1 (d1 DECIMAL(38, 10) UNSIGNED, d2 DECIMAL(38, 10) UNSIGNED, d3 DECIMAL(38, 10) UNSIGNED) ENGINE=columnstore;
INSERT INTO cs1 VALUES
(0, 1234567890123456789012345678.9012345678, 1234567890123456789012345678.9012345679),
(1234567890123456789012345678.9012345678, 0, 1234567890123456789012345678.9012345679),
(1234567890123456789012345678.9012345678, 1234567890123456789012345678.9012345679, 0),
(NULL, 9999999999999999999999999999.9999999999, 9999999999999999999999999999.9999999998),
(9999999999999999999999999999.9999999999, NULL, 9999999999999999999999999999.9999999998),
(9999999999999999999999999999.9999999999, 9999999999999999999999999999.9999999998, NULL),
(NULL, NULL, NULL);
SELECT "if_test", IF(d1, d2, d3) AS f1, IF(d1, d3, d2) AS f2, IF(d2, d1, d3) AS f3, IF(d2, d3, d1) AS f4,
IF(d3, d1, d2) AS f5, IF(d3, d2, d1) AS f6 FROM cs1;
if_test	f1	f2	f3	f4	f5	f6
if_test	1234567890123456789012345678.9012345679	1234567890123456789012345678.9012345678	0.0000000000	1234567890123456789012345678.9012345679	0.0000000000	1234567890123456789012345678.9012345678
if_test	0.0000000000	1234567890123456789012345678.9012345679	1234567890123456789012345678.9012345679	1234567890123456789012345678.9012345678	1234567890123456789012345678.9012345678	0.0000000000
if_test	1234567890123456789012345678.9012345679	0.0000000000	1234567890123456789012345678.9012345678	0.0000000000	1234567890123456789012345678.9012345679	1234567890123456789012345678.9012345678
if_test	9999999999999999999999999999.9999999998	9999999999999999999999999999.9999999999	NULL	9999999999999999999999999999.9999999998	NULL	9999999999999999999999999999.9999999999
if_test	NULL	9999999999999999999999999999.9999999998	9999999999999999999999999999.9999999998	9999999999999999999999999999.9999999999	9999999999999999999999999999.9999999999	NULL
if_test	9999999999999999999999999999.9999999998	NULL	9999999999999999999999999999.9999999999	NULL	9999999999999999999999999999.9999999998	9999999999999999999999999999.9999999999
if_test	NULL	NULL	NULL	NULL	NULL	NULL
SELECT "coalesce_test", COALESCE(d1, d2, d3) AS f1, COALESCE(d1, d3, d2) AS f2, COALESCE(d2, d1, d3) AS f3,
COALESCE(d2, d3, d1) AS f4, COALESCE(d3, d1, d2) AS f5, COALESCE(d3, d2, d1) AS f6 FROM cs1;
coalesce_test	f1	f2	f3	f4	f5	f6
coalesce_test	0.0000000000	0.0000000000	1234567890123456789012345678.9012345678	1234567890123456789012345678.9012345678	1234567890123456789012345678.9012345679	1234567890123456789012345678.9012345679
coalesce_test	1234567890123456789012345678.9012345678	1234567890123456789012345678.9012345678	0.0000000000	0.0000000000	1234567890123456789012345678.9012345679	1234567890123456789012345678.9012345679
coalesce_test	1234567890123456789012345678.9012345678	1234567890123456789012345678.9012345678	1234567890123456789012345678.9012345679	1234567890123456789012345678.9012345679	0.0000000000	0.0000000000
coalesce_test	9999999999999999999999999999.9999999999	9999999999999999999999999999.9999999998	9999999999999999999999999999.9999999999	9999999999999999999999999999.9999999999	9999999999999999999999999999.9999999998	9999999999999999999999999999.9999999998
coalesce_test	9999999999999999999999999999.9999999999	9999999999999999999999999999.9999999999	9999999999999999999999999999.9999999999	9999999999999999999999999999.9999999998	9999999999999999999999999999.9999999998	9999999999999999999999999999.9999999998
coalesce_test	9999999999999999999999999999.9999999999	9999999999999999999999999999.9999999999	9999999999999999999999999999.9999999998	9999999999999999999999999999.9999999998	9999999999999999999999999999.9999999999	9999999999999999999999999999.9999999998
coalesce_test	NULL	NULL	NULL	NULL	NULL	NULL
SELECT "case_test", CASE d1 WHEN 1234567890123456789012345678.9012345678 THEN d2 ELSE d3 END AS f1,
CASE d2 WHEN 1234567890123456789012345678.9012345679 THEN d1 ELSE d3 END AS f2,
CASE d3 WHEN d1 + 0.0000000001 THEN 9999999999999999999999999999.9999999999 ELSE -9999999999999999999999999999.9999999999 END AS f3
FROM cs1;
case_test	f1	f2	f3
case_test	1234567890123456789012345678.9012345679	1234567890123456789012345678.9012345679	-9999999999999999999999999999.9999999999
case_test	0.0000000000	1234567890123456789012345678.9012345679	9999999999999999999999999999.9999999999
case_test	1234567890123456789012345678.9012345679	1234567890123456789012345678.9012345678	-9999999999999999999999999999.9999999999
case_test	9999999999999999999999999999.9999999998	9999999999999999999999999999.9999999998	-9999999999999999999999999999.9999999999
case_test	9999999999999999999999999999.9999999998	9999999999999999999999999999.9999999998	-9999999999999999999999999999.9999999999
case_test	NULL	NULL	-9999999999999999999999999999.9999999999
case_test	NULL	NULL	-9999999999999999999999999999.9999999999
DROP TABLE IF EXISTS cs1;
DROP TABLE IF EXISTS cs2;
CREATE TABLE cs1 (d1 DECIMAL(38), d2 DECIMAL(38, 10), d3 DECIMAL(38, 38)) ENGINE=columnstore;
CREATE TABLE cs2 (d1 DECIMAL(38) UNSIGNED, d2 DECIMAL(38, 10) UNSIGNED, d3 DECIMAL(38, 38) UNSIGNED) ENGINE=columnstore;
INSERT INTO cs1 VALUES 
(NULL, NULL, NULL),
(0,0,0),
(-99999999999999999999999999999999999998, -9999999999999999999999999999.9999999998, -0.99999999999999999999999999999999999998),
(-12345678901234567890123456789012345678, -1234567890123456789012345678.9012345678, -0.12345678901234567890123456789012345678),
(-123456, -123.456, -0.123456),
(123456, 123.456, 0.123456),
(12345678901234567890123456789012345678, 1234567890123456789012345678.9012345678, 0.12345678901234567890123456789012345678),
(99999999999999999999999999999999999998, 999999999999999999999999999.9999999998, 0.99999999999999999999999999999999999998);
INSERT INTO cs2 VALUES 
(NULL, NULL, NULL),
(0,0,0),
(123456, 123.456, 0.123456),
(12345678901234567890123456789012345678, 1234567890123456789012345678.9012345678, 0.12345678901234567890123456789012345678),
(99999999999999999999999999999999999999, 999999999999999999999999999.9999999999, 0.99999999999999999999999999999999999999);
SELECT "acos_test", ACOS(d1), ACOS(d2), ACOS(d3) FROM cs1;
acos_test	ACOS(d1)	ACOS(d2)	ACOS(d3)
acos_test	NULL	NULL	NULL
acos_test	1.5707963267948966	1.5707963267948966	1.5707963267948966
acos_test	NULL	NULL	3.141592653589793
acos_test	NULL	NULL	1.6945688992340546
acos_test	NULL	NULL	1.694568104139232
acos_test	NULL	NULL	1.4470245494505614
acos_test	NULL	NULL	1.4470237543557387
acos_test	NULL	NULL	0
SELECT "asin_test", ASIN(d1), ASIN(d2), ASIN(d3) FROM cs1;
asin_test	ASIN(d1)	ASIN(d2)	ASIN(d3)
asin_test	NULL	NULL	NULL
asin_test	0	0	0
asin_test	NULL	NULL	-1.5707963267948966
asin_test	NULL	NULL	-0.12377257243915793
asin_test	NULL	NULL	-0.12377177734433531
asin_test	NULL	NULL	0.12377177734433531
asin_test	NULL	NULL	0.12377257243915793
asin_test	NULL	NULL	1.5707963267948966
SELECT "atan_test", ATAN(d1), ATAN(d2), ATAN(d3) FROM cs1;
atan_test	ATAN(d1)	ATAN(d2)	ATAN(d3)
atan_test	NULL	NULL	NULL
atan_test	0	0	0
atan_test	-1.5707963267948966	-1.5707963267948966	-0.7853981633974483
atan_test	-1.5707963267948966	-1.5707963267948966	-0.12283523778346472
atan_test	-1.5707882267430564	-1.5626964520979927	-0.12283446061629792
atan_test	1.5707882267430564	1.5626964520979927	0.12283446061629792
atan_test	1.5707963267948966	1.5707963267948966	0.12283523778346472
atan_test	1.5707963267948966	1.5707963267948966	0.7853981633974483
SELECT "acos_test", ACOS(d1), ACOS(d2), ACOS(d3) FROM cs2;
acos_test	ACOS(d1)	ACOS(d2)	ACOS(d3)
acos_test	NULL	NULL	NULL
acos_test	1.5707963267948966	1.5707963267948966	1.5707963267948966
acos_test	NULL	NULL	1.4470245494505614
acos_test	NULL	NULL	1.4470237543557387
acos_test	NULL	NULL	0
SELECT "asin_test", ASIN(d1), ASIN(d2), ASIN(d3) FROM cs2;
asin_test	ASIN(d1)	ASIN(d2)	ASIN(d3)
asin_test	NULL	NULL	NULL
asin_test	0	0	0
asin_test	NULL	NULL	0.12377177734433531
asin_test	NULL	NULL	0.12377257243915793
asin_test	NULL	NULL	1.5707963267948966
SELECT "atan_test", ATAN(d1), ATAN(d2), ATAN(d3) FROM cs2;
atan_test	ATAN(d1)	ATAN(d2)	ATAN(d3)
atan_test	NULL	NULL	NULL
atan_test	0	0	0
atan_test	1.5707882267430564	1.5626964520979927	0.12283446061629792
atan_test	1.5707963267948966	1.5707963267948966	0.12283523778346472
atan_test	1.5707963267948966	1.5707963267948966	0.7853981633974483
SELECT "cos_test", TRUNCATE(COS(d1), 15), COS(d2), COS(d3) FROM cs1;
cos_test	TRUNCATE(COS(d1), 15)	COS(d2)	COS(d3)
cos_test	NULL	NULL	NULL
cos_test	1.000000000000000	1	1
cos_test	-0.192619228016809	0.8518103524718695	0.5403023058681398
cos_test	0.854620092798549	-0.9857846013208577	0.992388885112192
cos_test	-0.672294881656584	-0.5947139710921574	0.9923889822735581
cos_test	-0.672294881656584	-0.5947139710921574	0.9923889822735581
cos_test	0.854620092798549	-0.9857846013208577	0.992388885112192
cos_test	-0.192619228016809	-0.3850065443966674	0.5403023058681398
SELECT "sin_test", SIN(d1), SIN(d2), SIN(d3) FROM cs1;
sin_test	SIN(d1)	SIN(d2)	SIN(d3)
sin_test	NULL	NULL	NULL
sin_test	0	0	0
sin_test	-0.9812735770406785	-0.523850287221215	-0.8414709848078965
sin_test	-0.519253788609191	0.16801404643266502	-0.12314341519456258
sin_test	0.7402834538866575	0.8039373685728239	-0.12314263218744217
sin_test	-0.7402834538866575	-0.8039373685728239	0.12314263218744217
sin_test	0.519253788609191	-0.16801404643266502	0.12314341519456258
sin_test	0.9812735770406785	-0.922913842550721	0.8414709848078965
SELECT "tan_test", TAN(d1), TAN(d2), TAN(d3) FROM cs1;
tan_test	TAN(d1)	TAN(d2)	TAN(d3)
tan_test	NULL	NULL	NULL
tan_test	0	0	0
tan_test	5.0943697944581485	-0.6149846449987996	-1.5574077246549023
tan_test	-0.6075843441836666	-0.17043687455407822	-0.12408786216971879
tan_test	-1.1011290939216198	-1.3518050821917635	-0.12408706100839918
tan_test	1.1011290939216198	1.3518050821917635	0.12408706100839918
tan_test	0.6075843441836666	0.17043687455407822	0.12408786216971879
tan_test	-5.0943697944581485	2.397138064229512	1.5574077246549023
SELECT "cos_test", TRUNCATE(COS(d1), 15), COS(d2), COS(d3) FROM cs2;
cos_test	TRUNCATE(COS(d1), 15)	COS(d2)	COS(d3)
cos_test	NULL	NULL	NULL
cos_test	1.000000000000000	1	1
cos_test	-0.672294881656584	-0.5947139710921574	0.9923889822735581
cos_test	0.854620092798549	-0.9857846013208577	0.992388885112192
cos_test	-0.192619228016809	-0.3850065443966674	0.5403023058681398
SELECT "sin_test", SIN(d1), SIN(d2), SIN(d3) FROM cs2;
sin_test	SIN(d1)	SIN(d2)	SIN(d3)
sin_test	NULL	NULL	NULL
sin_test	0	0	0
sin_test	-0.7402834538866575	-0.8039373685728239	0.12314263218744217
sin_test	0.519253788609191	-0.16801404643266502	0.12314341519456258
sin_test	0.9812735770406785	-0.922913842550721	0.8414709848078965
SELECT "tan_test", TAN(d1), TAN(d2), TAN(d3) FROM cs2;
tan_test	TAN(d1)	TAN(d2)	TAN(d3)
tan_test	NULL	NULL	NULL
tan_test	0	0	0
tan_test	1.1011290939216198	1.3518050821917635	0.12408706100839918
tan_test	0.6075843441836666	0.17043687455407822	0.12408786216971879
tan_test	-5.0943697944581485	2.397138064229512	1.5574077246549023
SELECT "md5_test", MD5(d1), MD5(d2), MD5(d3) FROM cs1;
md5_test	MD5(d1)	MD5(d2)	MD5(d3)
md5_test	NULL	NULL	NULL
md5_test	cfcd208495d565ef66e7dff9f98764da	0132f0d7ac42e300d9b512a2f04a31a5	e024d99d8195d9c0d0bdc69bcc39269d
md5_test	ed2c9dc0c987fb2e3d76248a621b58eb	86f46152e8fa0ecc405b36a439269877	a2ebc2a7d465abe7fadf3e980e92601b
md5_test	ebd52417c199c5822b95d1b363648986	283a1f7484d83d7e3a64735a0d8a902c	2fdfd658f046f11a9d4387e4812ce22e
md5_test	aea5744c725af3e41ec43c849fbe73ca	29b14c358de0b0ad3e02f5ca67a3c94b	01918ee2a282c286897da46cbb464386
md5_test	e10adc3949ba59abbe56e057f20f883e	5e0f694d589fbe96c4eee3dcfab07986	2091beece11975a01e63fbfe6c51088a
md5_test	85352f0dc3cbe904c4826bad1d592d26	79809fdd65e2c31723f99dec1fd9f004	2d026ae80ca1e4d12a5530934cb9927f
md5_test	85d34caf123878e1f16d848622f27489	bdd96d4d2fc0b5b271370014b3c6cb1e	b62c080b27d88cc2830e1de0d643996e
SELECT "sha1_test", SHA1(d1), SHA1(d2), SHA1(d3) FROM cs1;
sha1_test	SHA1(d1)	SHA1(d2)	SHA1(d3)
sha1_test	NULL	NULL	NULL
sha1_test	b6589fc6ab0dc82cf12099d1c2d40ab994e8410c	1f9d99565073f82e0aa5df4234f080ecf2900588	e9a0f15a1864eb57275e67cc45a2faf32626fe86
sha1_test	7362367700f5b48b61e237b341fad67128c53703	6dba12eb5ddf9bdcb8394554d12247c0aa37c9ea	15b523467decc87066d05634c57ee7b4c73eed48
sha1_test	6370558cbf3ea19d48d822980f872c3f5cf3c352	e673dd05243caab41d8141b39957775eb6b9d515	29cc081b39cfa747403a5f45c2ff4d9d3a4f9435
sha1_test	570b5a494985a5dfd8b2596823542ce9286f9741	f5169a06b68765bfe926526cd94d30e44a3faddc	0b66c49f959255cb447b1c71154971a17de11026
sha1_test	7c4a8d09ca3762af61e59520943dc26494f8941b	efe793be68a61381a15f5beeae10b8fd95e148c5	5ca1b8c852dbff7a928efabac8aeb23e9e5d2b35
sha1_test	e85666a4004e32eccf19dcc06e6016c8d675272e	1767909f5c1753c9d1071df3504f51c339fac425	8163dac51d8d7e8a26b31a9d9b7ee69dfd45f8ce
sha1_test	0d3e81771f14863d54669d579298b51afef32d3d	c1adf7fd02a085ca78f4dcd9d510d6cc0fe659cb	647d1d61bebdd21d93a79e5acc53eb687556e3e3
SELECT "sha_test", SHA(d1), SHA(d2), SHA(d3) FROM cs1;
sha_test	SHA(d1)	SHA(d2)	SHA(d3)
sha_test	NULL	NULL	NULL
sha_test	b6589fc6ab0dc82cf12099d1c2d40ab994e8410c	1f9d99565073f82e0aa5df4234f080ecf2900588	e9a0f15a1864eb57275e67cc45a2faf32626fe86
sha_test	7362367700f5b48b61e237b341fad67128c53703	6dba12eb5ddf9bdcb8394554d12247c0aa37c9ea	15b523467decc87066d05634c57ee7b4c73eed48
sha_test	6370558cbf3ea19d48d822980f872c3f5cf3c352	e673dd05243caab41d8141b39957775eb6b9d515	29cc081b39cfa747403a5f45c2ff4d9d3a4f9435
sha_test	570b5a494985a5dfd8b2596823542ce9286f9741	f5169a06b68765bfe926526cd94d30e44a3faddc	0b66c49f959255cb447b1c71154971a17de11026
sha_test	7c4a8d09ca3762af61e59520943dc26494f8941b	efe793be68a61381a15f5beeae10b8fd95e148c5	5ca1b8c852dbff7a928efabac8aeb23e9e5d2b35
sha_test	e85666a4004e32eccf19dcc06e6016c8d675272e	1767909f5c1753c9d1071df3504f51c339fac425	8163dac51d8d7e8a26b31a9d9b7ee69dfd45f8ce
sha_test	0d3e81771f14863d54669d579298b51afef32d3d	c1adf7fd02a085ca78f4dcd9d510d6cc0fe659cb	647d1d61bebdd21d93a79e5acc53eb687556e3e3
SELECT "md5_test", MD5(d1), MD5(d2), MD5(d3) FROM cs2;
md5_test	MD5(d1)	MD5(d2)	MD5(d3)
md5_test	NULL	NULL	NULL
md5_test	cfcd208495d565ef66e7dff9f98764da	0132f0d7ac42e300d9b512a2f04a31a5	e024d99d8195d9c0d0bdc69bcc39269d
md5_test	e10adc3949ba59abbe56e057f20f883e	5e0f694d589fbe96c4eee3dcfab07986	2091beece11975a01e63fbfe6c51088a
md5_test	85352f0dc3cbe904c4826bad1d592d26	79809fdd65e2c31723f99dec1fd9f004	2d026ae80ca1e4d12a5530934cb9927f
md5_test	2acdad59d64ba30d4e25ffb4fdc6293e	bd96c786b7d5e5c464353cd205d82a8d	fa78108eccbe98d11ec5d95968aa7cf0
SELECT "sha1_test", SHA1(d1), SHA1(d2), SHA1(d3) FROM cs2;
sha1_test	SHA1(d1)	SHA1(d2)	SHA1(d3)
sha1_test	NULL	NULL	NULL
sha1_test	b6589fc6ab0dc82cf12099d1c2d40ab994e8410c	1f9d99565073f82e0aa5df4234f080ecf2900588	e9a0f15a1864eb57275e67cc45a2faf32626fe86
sha1_test	7c4a8d09ca3762af61e59520943dc26494f8941b	efe793be68a61381a15f5beeae10b8fd95e148c5	5ca1b8c852dbff7a928efabac8aeb23e9e5d2b35
sha1_test	e85666a4004e32eccf19dcc06e6016c8d675272e	1767909f5c1753c9d1071df3504f51c339fac425	8163dac51d8d7e8a26b31a9d9b7ee69dfd45f8ce
sha1_test	cfa71165016e02cbdc89c958a0a9f83671b84188	fd906e6d6a82f35e395eaacc767dd45e80d3639b	c56aed82fc2e412e46c39c5a118484fc32b875e2
SELECT "sha_test", SHA(d1), SHA(d2), SHA(d3) FROM cs2;
sha_test	SHA(d1)	SHA(d2)	SHA(d3)
sha_test	NULL	NULL	NULL
sha_test	b6589fc6ab0dc82cf12099d1c2d40ab994e8410c	1f9d99565073f82e0aa5df4234f080ecf2900588	e9a0f15a1864eb57275e67cc45a2faf32626fe86
sha_test	7c4a8d09ca3762af61e59520943dc26494f8941b	efe793be68a61381a15f5beeae10b8fd95e148c5	5ca1b8c852dbff7a928efabac8aeb23e9e5d2b35
sha_test	e85666a4004e32eccf19dcc06e6016c8d675272e	1767909f5c1753c9d1071df3504f51c339fac425	8163dac51d8d7e8a26b31a9d9b7ee69dfd45f8ce
sha_test	cfa71165016e02cbdc89c958a0a9f83671b84188	fd906e6d6a82f35e395eaacc767dd45e80d3639b	c56aed82fc2e412e46c39c5a118484fc32b875e2
SELECT "ceiling_test", CEILING(d1), CEILING(d2), CEILING(d3) FROM cs1;
ceiling_test	CEILING(d1)	CEILING(d2)	CEILING(d3)
ceiling_test	NULL	NULL	NULL
ceiling_test	0	0	0
ceiling_test	-99999999999999999999999999999999999998	-9999999999999999999999999999	0
ceiling_test	-12345678901234567890123456789012345678	-1234567890123456789012345678	0
ceiling_test	-123456	-123	0
ceiling_test	123456	124	1
ceiling_test	12345678901234567890123456789012345678	1234567890123456789012345679	1
ceiling_test	99999999999999999999999999999999999998	1000000000000000000000000000	1
SELECT "ceiling_test", CEILING(d1), CEILING(d2), CEILING(d3) FROM cs2;
ceiling_test	CEILING(d1)	CEILING(d2)	CEILING(d3)
ceiling_test	NULL	NULL	NULL
ceiling_test	0	0	0
ceiling_test	123456	124	1
ceiling_test	12345678901234567890123456789012345678	1234567890123456789012345679	1
ceiling_test	99999999999999999999999999999999999999	1000000000000000000000000000	1
SELECT "sign_test", SIGN(d1), SIGN(d2), SIGN(d3) FROM cs1;
sign_test	SIGN(d1)	SIGN(d2)	SIGN(d3)
sign_test	NULL	NULL	NULL
sign_test	0	0	0
sign_test	-1	-1	-1
sign_test	-1	-1	-1
sign_test	-1	-1	-1
sign_test	1	1	1
sign_test	1	1	1
sign_test	1	1	1
SELECT "sign_test", SIGN(d1), SIGN(d2), SIGN(d3) FROM cs2;
sign_test	SIGN(d1)	SIGN(d2)	SIGN(d3)
sign_test	NULL	NULL	NULL
sign_test	0	0	0
sign_test	1	1	1
sign_test	1	1	1
sign_test	1	1	1
SELECT "concat_ws_test", CONCAT_WS(' = d1, d2 = ', d1, d2) FROM cs1;
concat_ws_test	CONCAT_WS(' = d1, d2 = ', d1, d2)
concat_ws_test	NULL
concat_ws_test	0 = d1, d2 = 0.0000000000
concat_ws_test	-99999999999999999999999999999999999998 = d1, d2 = -9999999999999999999999999999.9999999998
concat_ws_test	-12345678901234567890123456789012345678 = d1, d2 = -1234567890123456789012345678.9012345678
concat_ws_test	-123456 = d1, d2 = -123.4560000000
concat_ws_test	123456 = d1, d2 = 123.4560000000
concat_ws_test	12345678901234567890123456789012345678 = d1, d2 = 1234567890123456789012345678.9012345678
concat_ws_test	99999999999999999999999999999999999998 = d1, d2 = 999999999999999999999999999.9999999998
SELECT "convert_test", CONVERT(d1, SIGNED), CONVERT(d2, SIGNED), CONVERT(d3, SIGNED) FROM cs1;
convert_test	CONVERT(d1, SIGNED)	CONVERT(d2, SIGNED)	CONVERT(d3, SIGNED)
convert_test	NULL	NULL	NULL
convert_test	0	0	0
convert_test	NULL	NULL	0
convert_test	NULL	NULL	0
convert_test	-123456	-123	0
convert_test	123456	123	0
convert_test	9223372036854775807	9223372036854775807	0
convert_test	9223372036854775807	9223372036854775807	1
SELECT "conv_test", CONV(d1, 10, 8), CONV(d2, 10, 8), CONV(d3, 10, 8) FROM cs1;
conv_test	CONV(d1, 10, 8)	CONV(d2, 10, 8)	CONV(d3, 10, 8)
conv_test	NULL	NULL	NULL
conv_test	0	0	0
conv_test	1777777777777777777777	1777777777777777777777	0
conv_test	1777777777777777777777	1777777777777777777777	0
conv_test	1777777777777777416700	1777777777777777777605	0
conv_test	361100	173	0
conv_test	1777777777777777777777	1777777777777777777777	0
conv_test	1777777777777777777777	1777777777777777777777	0
SELECT "concat_ws_test", CONCAT_WS(' = d2, d3 = ', d2, d3) FROM cs2;
concat_ws_test	CONCAT_WS(' = d2, d3 = ', d2, d3)
concat_ws_test	NULL
concat_ws_test	0.0000000000 = d2, d3 = 0.00000000000000000000000000000000000000
concat_ws_test	123.4560000000 = d2, d3 = 0.12345600000000000000000000000000000000
concat_ws_test	1234567890123456789012345678.9012345678 = d2, d3 = 0.1234567890123456789012345678901234567
concat_ws_test	999999999999999999999999999.9999999999 = d2, d3 = 0.99999999999999999999999999999999999999
SELECT "convert_test", CONVERT(d1, SIGNED), CONVERT(d2, SIGNED), CONVERT(d3, SIGNED) FROM cs2;
convert_test	CONVERT(d1, SIGNED)	CONVERT(d2, SIGNED)	CONVERT(d3, SIGNED)
convert_test	NULL	NULL	NULL
convert_test	0	0	0
convert_test	123456	123	0
convert_test	9223372036854775807	9223372036854775807	0
convert_test	9223372036854775807	9223372036854775807	1
SELECT "conv_test", CONV(d1, 10, 8), CONV(d2, 10, 8), CONV(d3, 10, 8) FROM cs2;
conv_test	CONV(d1, 10, 8)	CONV(d2, 10, 8)	CONV(d3, 10, 8)
conv_test	NULL	NULL	NULL
conv_test	0	0	0
conv_test	361100	173	0
conv_test	1777777777777777777777	1777777777777777777777	0
conv_test	1777777777777777777777	1777777777777777777777	0
SELECT "reverse_test", REVERSE(d1), REVERSE(d2), REVERSE(d3) FROM cs1;
reverse_test	REVERSE(d1)	REVERSE(d2)	REVERSE(d3)
reverse_test	NULL	NULL	NULL
reverse_test	0	0000000000.0	00000000000000000000000000000000000000.0
reverse_test	89999999999999999999999999999999999999-	8999999999.9999999999999999999999999999-	89999999999999999999999999999999999999.0
reverse_test	87654321098765432109876543210987654321-	8765432109.8765432109876543210987654321-	87654321098765432109876543210987654321.0
reverse_test	654321-	0000000654.321-	00000000000000000000000000000000654321.0
reverse_test	654321	0000000654.321	00000000000000000000000000000000654321.0
reverse_test	87654321098765432109876543210987654321	8765432109.8765432109876543210987654321	87654321098765432109876543210987654321.0
reverse_test	89999999999999999999999999999999999999	8999999999.999999999999999999999999999	89999999999999999999999999999999999999.0
SELECT "strcmp_test", STRCMP(d1, d2), STRCMP(d2, d3), STRCMP(d3, d1), STRCMP(d1, 123456) FROM cs1;
strcmp_test	STRCMP(d1, d2)	STRCMP(d2, d3)	STRCMP(d3, d1)	STRCMP(d1, 123456)
strcmp_test	NULL	NULL	NULL	NULL
strcmp_test	-1	-1	1	-1
strcmp_test	1	1	-1	-1
strcmp_test	1	1	-1	-1
strcmp_test	1	1	-1	-1
strcmp_test	1	1	-1	0
strcmp_test	1	1	-1	1
strcmp_test	1	1	-1	1
SELECT "reverse_test", REVERSE(d1), REVERSE(d2), REVERSE(d3) FROM cs2;
reverse_test	REVERSE(d1)	REVERSE(d2)	REVERSE(d3)
reverse_test	NULL	NULL	NULL
reverse_test	0	0000000000.0	00000000000000000000000000000000000000.
reverse_test	654321	0000000654.321	00000000000000000000000000000000654321.
reverse_test	87654321098765432109876543210987654321	8765432109.8765432109876543210987654321	87654321098765432109876543210987654321.
reverse_test	99999999999999999999999999999999999999	9999999999.999999999999999999999999999	99999999999999999999999999999999999999.
SELECT "strcmp_test", STRCMP(d1, d2), STRCMP(d2, d3), STRCMP(d3, d1), STRCMP(d1, 123456) FROM cs2;
strcmp_test	STRCMP(d1, d2)	STRCMP(d2, d3)	STRCMP(d3, d1)	STRCMP(d1, 123456)
strcmp_test	NULL	NULL	NULL	NULL
strcmp_test	-1	-1	1	-1
strcmp_test	1	1	-1	0
strcmp_test	1	1	-1	1
strcmp_test	1	1	-1	1
SELECT "repeat_test", REPEAT(d3, 2) FROM cs2;
repeat_test	REPEAT(d3, 2)
repeat_test	NULL
repeat_test	0.000000000000000000000000000000000000000.000000000000000000000000000000000000
repeat_test	0.123456000000000000000000000000000000000.123456000000000000000000000000000000
repeat_test	0.123456789012345678901234567890123456780.123456789012345678901234567890123456
repeat_test	0.999999999999999999999999999999999999990.999999999999999999999999999999999999
SELECT "crc32_test", CRC32(d1), CRC32(d2), CRC32(d3) FROM cs1;
crc32_test	CRC32(d1)	CRC32(d2)	CRC32(d3)
crc32_test	NULL	NULL	NULL
crc32_test	4108050209	1493058618	3222633636
crc32_test	3632967550	3551748494	1158150103
crc32_test	1890616683	3502341582	3980254658
crc32_test	2344023520	591531716	2592241953
crc32_test	158520161	1373714550	2077423265
crc32_test	3117531155	2396448133	208491074
crc32_test	300673542	2519087950	2757195863
SELECT "degrees_test", DEGREES(d1), DEGREES(d2), DEGREES(d3) FROM cs1;
degrees_test	DEGREES(d1)	DEGREES(d2)	DEGREES(d3)
degrees_test	NULL	NULL	NULL
degrees_test	0	0	0
degrees_test	-5.729577951308232e39	-5.7295779513082316e29	-57.29577951308232
degrees_test	-7.073552962644482e38	-7.073552962644483e28	-7.073552962644483
degrees_test	-7073507.7555670915	-7073.507755567091	-7.073507755567091
degrees_test	7073507.7555670915	7073.507755567091	7.073507755567091
degrees_test	7.073552962644482e38	7.073552962644483e28	7.073552962644483
degrees_test	5.729577951308232e39	5.729577951308232e28	57.29577951308232
SELECT "radians_test", RADIANS(d1), RADIANS(d2), RADIANS(d3) FROM cs1;
radians_test	RADIANS(d1)	RADIANS(d2)	RADIANS(d3)
radians_test	NULL	NULL	NULL
radians_test	0	0	0
radians_test	-1.7453292519943294e36	-1.7453292519943295e26	-0.017453292519943295
radians_test	-2.1547274522053902e35	-2.1547274522053906e25	-0.0021547274522053907
radians_test	-2154.7136813421193	-2.1547136813421197	-0.0021547136813421194
radians_test	2154.7136813421193	2.1547136813421197	0.0021547136813421194
radians_test	2.1547274522053902e35	2.1547274522053906e25	0.0021547274522053907
radians_test	1.7453292519943294e36	1.7453292519943295e25	0.017453292519943295
SELECT "crc32_test", CRC32(d1), CRC32(d2), CRC32(d3) FROM cs2;
crc32_test	CRC32(d1)	CRC32(d2)	CRC32(d3)
crc32_test	NULL	NULL	NULL
crc32_test	4108050209	1493058618	3222633636
crc32_test	158520161	1373714550	2077423265
crc32_test	3117531155	2396448133	208491074
crc32_test	1726798480	3777039320	3545254081
SELECT "degrees_test", DEGREES(d1), DEGREES(d2), DEGREES(d3) FROM cs2;
degrees_test	DEGREES(d1)	DEGREES(d2)	DEGREES(d3)
degrees_test	NULL	NULL	NULL
degrees_test	0	0	0
degrees_test	7073507.7555670915	7073.507755567091	7.073507755567091
degrees_test	7.073552962644482e38	7.073552962644483e28	7.073552962644483
degrees_test	5.729577951308232e39	5.729577951308232e28	57.29577951308232
SELECT "radians_test", RADIANS(d1), RADIANS(d2), RADIANS(d3) FROM cs2;
radians_test	RADIANS(d1)	RADIANS(d2)	RADIANS(d3)
radians_test	NULL	NULL	NULL
radians_test	0	0	0
radians_test	2154.7136813421193	2.1547136813421197	0.0021547136813421194
radians_test	2.1547274522053902e35	2.1547274522053906e25	0.0021547274522053907
radians_test	1.7453292519943294e36	1.7453292519943295e25	0.017453292519943295
SELECT "hex_test", HEX(d1), HEX(d2), HEX(d3) FROM cs1;
hex_test	HEX(d1)	HEX(d2)	HEX(d3)
hex_test	NULL	NULL	NULL
hex_test	0	0	0
hex_test	FFFFFFFFFFFFFFFF	FFFFFFFFFFFFFFFF	FFFFFFFFFFFFFFFF
hex_test	FFFFFFFFFFFFFFFF	FFFFFFFFFFFFFFFF	0
hex_test	FFFFFFFFFFFE1DC0	FFFFFFFFFFFFFF85	0
hex_test	1E240	7B	0
hex_test	FFFFFFFFFFFFFFFF	FFFFFFFFFFFFFFFF	0
hex_test	FFFFFFFFFFFFFFFF	FFFFFFFFFFFFFFFF	1
SELECT "hex_test", HEX(d1), HEX(d2), HEX(d3) FROM cs2;
hex_test	HEX(d1)	HEX(d2)	HEX(d3)
hex_test	NULL	NULL	NULL
hex_test	0	0	0
hex_test	1E240	7B	0
hex_test	FFFFFFFFFFFFFFFF	FFFFFFFFFFFFFFFF	0
hex_test	FFFFFFFFFFFFFFFF	FFFFFFFFFFFFFFFF	1
SELECT "insert_test", INSERT(d1,6,1,d2), INSERT(d2,0,1,d3) FROM cs1;
insert_test	INSERT(d1,6,1,d2)	INSERT(d2,0,1,d3)
insert_test	NULL	NULL
insert_test	0	0.0000000000
insert_test	-9999-9999999999999999999999999999.9999999998999999999999999999999999999999998	-9999999999999999999999999999.9999999998
insert_test	-1234-1234567890123456789012345678.9012345678678901234567890123456789012345678	-1234567890123456789012345678.9012345678
insert_test	-1234-123.45600000006	-123.4560000000
insert_test	12345123.4560000000	123.4560000000
insert_test	123451234567890123456789012345678.901234567878901234567890123456789012345678	1234567890123456789012345678.9012345678
insert_test	99999999999999999999999999999999.999999999899999999999999999999999999999998	999999999999999999999999999.9999999998
SELECT "insert_test", INSERT(d1,6,1,d2), INSERT(d2,0,1,d3) FROM cs2;
insert_test	INSERT(d1,6,1,d2)	INSERT(d2,0,1,d3)
insert_test	NULL	NULL
insert_test	0	0.0000000000
insert_test	12345123.4560000000	123.4560000000
insert_test	123451234567890123456789012345678.901234567878901234567890123456789012345678	1234567890123456789012345678.9012345678
insert_test	99999999999999999999999999999999.999999999999999999999999999999999999999999	999999999999999999999999999.9999999999
DROP DATABASE test_mcol641_functions;