database/mariadb-columnstore-engine
1
0
Fork 0
mirror of https://github.com/mariadb-corporation/mariadb-columnstore-engine.git synced 2025-11-03 17:13:17 +03:00
Code Activity
Files
cmapi-json-in-out-by-default
mariadb-columnstore-engine/mysql-test/columnstore/basic/r/mcol641-functions.result
Theresa Hradilak 48562e41f9 feat(datatypes): MCOL-4632 and MCOL-4648, fix cast leads to NULL. 
Remove redundant cast.

As C-style casts with a type name in parantheses are interpreted as static_casts this literally just changes the interpretation around (and forces an implicit cast to match the return value of the function).

Switch UBIGINTNULL and UBIGINTEMPTYROW constants for consistency.

Make consistent with relation between BIGINTNULL and BIGINTEMPTYROW & make adapted cast behaviour due to NULL markers more intuitive. (After this change we can simply block the highest possible uint64_t value and if a cast results in it, print the next lower value (2^64 - 2). Previously, (2^64 - 1) was able to be printed, but (2^64 - 2) as being blocked by the UBIGINTNULL constant was not, making finding the appropiate replacement value to give out more confusing.

Introduce MAX_MCS_UBIGINT and MIN_MCS_BIGINT and adapt casts.

Adapt casting to BIGINT to remove NULL marker error.

Add bugfix regression test for MCOL 4632

Add regression test for mcol_4648

Revert "Switch UBIGINTNULL and UBIGINTEMPTYROW constants for consistency."

This reverts commit 83eac11b18937ecb0b4c754dd48e4cb47310f620.
Due to backwards compatability issues.

Refactor casting to MCS[U]Int to datatype functions.

Update regression tests to include other affected datatypes.

Apply formatting.

Refactor according to PR review

Remove redundant new constant, switch to using already existing constant.

Adapt nullstring casting to EMPTYROW markers for backwards compatability.

Adapt tests for backward compatability behaviour allowing text datatypes to be casted to EMPTYROW constant.

Adapt mcol641-functions test according to bug fix.

Update tests according to new expected behaviour.

Adapt tests to new understanding of issue.

Update comments/documentation for MCOL_4632 test.

Adapt to new cast limit logic.

Make bracketing consistent.

Adapt previous regression test to new expected behaviour.
2023-08-11 13:00:30 +00:00

1279 lines
84 KiB
Plaintext
Raw Permalink Blame History

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 -9223372036854775807 -9223372036854775807 0
convert_test -9223372036854775807 -9223372036854775807 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;
View Git Blame Copy Permalink