database/mariadb-columnstore-engine
1
0
Fork 0
mirror of https://github.com/mariadb-corporation/mariadb-columnstore-engine.git synced 2025-04-21 19:45:56 +03:00
Code
mariadb-columnstore-engine/mysql-test/columnstore/basic/t/mcol641-functions.test
Gagan Goel 78dbf3a7e1 MCOL-641 Move MTR tests from future/ to basic/
2021-07-12 13:01:45 +00:00

619 lines
36 KiB
Plaintext
Raw Permalink Blame History

-- source ../include/have_columnstore.inc
--disable_warnings
DROP DATABASE IF EXISTS test_mcol641_functions;
--enable_warnings
CREATE DATABASE test_mcol641_functions;
USE test_mcol641_functions;
# bitwise functions
--disable_warnings
DROP TABLE IF EXISTS cs1;
--enable_warnings
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;
SELECT "bitwise_or_test", d1 | d2, d1 | d1, d2 | d2, d1 | 10, d2 | 10 FROM cs1;
SELECT "bitwise_xor_test", d1 ^ d2, d1 ^ d1, d2 ^ d2, d1 ^ 10, d2 ^ 10 FROM cs1 WHERE d1 != 999999999999999999;
SELECT "bitwise_leftshift_test", d1 << 1, d1 << 10, d1 << 20, d2 << 1, d2 << 10, d2 << 20 FROM cs1 WHERE d1 <= 999999999999999999;
SELECT "bitwise_rightshift_test", d1 >> 1, d1 >> 10, d1 >> 20, d2 >> 1, d2 >> 10, d2 >> 20 FROM cs1;
SELECT "bitcount_test", bit_count(d1), bit_count(d2) FROM cs1;
# period_diff function
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;
# cast a decimal as signed/unsigned/char
--disable_warnings
DROP TABLE IF EXISTS cs1;
--enable_warnings
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; # -9223372036854775808 results in a NULL output
SELECT "cast_test2", CAST(d1 AS UNSIGNED), CAST(d1 AS CHAR(50)) FROM cs1;
# cast a decimal as date, datetime
DELETE FROM cs1;
INSERT INTO cs1 VALUES (20201212), (19901028183200);
SELECT "cast_test3", CAST(d1 AS DATETIME), CAST(d1 AS DATE) FROM cs1;
# cast a decimal as double
--disable_warnings
DROP TABLE IF EXISTS cs1;
--enable_warnings
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 a decimal/int/char/float/double as decimal
--disable_warnings
DROP TABLE IF EXISTS cs1;
--enable_warnings
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;
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;
SELECT "cast_test5", CAST(d3 as DECIMAL(38,38)), CAST(d6 as DECIMAL(38,38)), CAST(d7 as DECIMAL(38,38)) FROM cs1;
# inet_ntoa() and inet_aton()
--disable_warnings
DROP TABLE IF EXISTS cs1;
--enable_warnings
CREATE TABLE cs1 (d1 DECIMAL(38)) ENGINE=columnstore;
INSERT INTO cs1 VALUES (127), (1278), (1234567890);
SELECT "inet_test", INET_NTOA(d1) FROM cs1;
SELECT "inet_test", d1 FROM cs1 where INET_ATON(d1) = d1;
# char()
DELETE FROM cs1;
INSERT INTO cs1 VALUES (1296911693), (77);
SELECT "char_test", CHAR(d1) FROM cs1;
# string functions
INSERT INTO cs1 VALUES(20201010), (19901230), (1234), (12345);
SELECT "monthname_test", MONTHNAME(d1), SEC_TO_TIME(d1) FROM cs1;
# real functions
# abs()
--disable_warnings
DROP TABLE IF EXISTS cs1;
--enable_warnings
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;
# round()/truncate()
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(d1,38) has a bug
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;
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;
SELECT "round_test", d2 FROM cs1 WHERE ROUND(d2) = "123"; # triggers getDoubleVal()
SELECT "truncate_test", TRUNCATE(d1, 0), TRUNCATE(d1,5), TRUNCATE(d1,-5), TRUNCATE(d1,10), TRUNCATE(d1,-10), TRUNCATE(d1,-38) FROM cs1; # TRUNCATE(d1,38) has a bug
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;
# See MDEV-22502. Fixed now! So enabling back the test for d3.
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;
SELECT "truncate_test", d2 FROM cs1 WHERE TRUNCATE(d2, 0) = "123"; # triggers getDoubleVal()
# format()
# Commenting out format() tests until MDEV-23118 is fixed.
#SELECT "format_test", FORMAT(d1, 0), FORMAT(d1, 10), FORMAT(d1, 38),
# FORMAT(d2, 0), FORMAT(d2, 10), FORMAT(d2, 38),
# FORMAT(d3, 0), FORMAT(d3, 10), FORMAT(d3, 38) FROM cs1;
# mod()
--disable_warnings
DROP TABLE IF EXISTS cs1;
--enable_warnings
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);
# MOD(d3,d1) is a floating point division which can result in different results from innodb due to rounding
# Also, MOD() between decimal and double is excluded from the query as we return the correct result but innodb does not.
#SELECT "mod_test", MOD(d1,d2), MOD(d2,d1), MOD(d1,d3), MOD(d3,d4), MOD(d4,d3), MOD(d1,d5), MOD(d2,d5), MOD(d3,d5), MOD(d4,d5) FROM cs1;
SELECT "mod_test", MOD(d1,d2), MOD(d2,d1), MOD(d1,d3), MOD(d3,d4), MOD(d4,d3) FROM cs1;
# floor(), ceil()
--disable_warnings
DROP TABLE IF EXISTS cs1;
--enable_warnings
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;
SELECT "floor_test", d1, d4 FROM cs1 WHERE FLOOR(d1) = FLOOR(d4); # triggers predicateoperator numericCompare()
SELECT "floor_test", d1, d4 FROM cs1 WHERE FLOOR(d1) = d4;
SELECT "floor_test", d1, d5 FROM cs1 WHERE FLOOR(d1) = FLOOR(d5);
SELECT "floor_test", d1, d5 FROM cs1 WHERE FLOOR(d1) = d5; # triggers Func_floor::getDoubleVal()
SELECT "floor_test", d1, d2 FROM cs1 WHERE FLOOR(d1) = FLOOR(d2);
SELECT "ceil_test", CEIL(d1), CEIL(d2), CEIL(d3), CEIL(d4), CEIL(d5), CEIL(d6) FROM cs1;
SELECT "ceil_test", d1, d4 FROM cs1 WHERE CEIL(d1) = CEIL(d4); # triggers predicateoperator numericCompare()
SELECT "ceil_test", d1, d4 FROM cs1 WHERE CEIL(d1) = d4;
SELECT "ceil_test", d1, d5 FROM cs1 WHERE CEIL(d1) = CEIL(d5);
SELECT "ceil_test", d1, d5 FROM cs1 WHERE CEIL(d1) = d5; # triggers Func_floor::getDoubleVal()
SELECT "ceil_test", d1, d2 FROM cs1 WHERE CEIL(d1) = CEIL(d2);
# makedate(), maketime()
--disable_warnings
DROP TABLE IF EXISTS cs1;
--enable_warnings
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;
# ELT()
--disable_warnings
DROP TABLE IF EXISTS cs1;
--enable_warnings
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;
# least(), greatest()
--disable_warnings
DROP TABLE IF EXISTS cs1;
--enable_warnings
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;
SELECT "greatest_test", d2 FROM cs1 WHERE GREATEST(d2, d5) = d5; # triggers getDoubleVal()
SELECT "least_test", LEAST(d1, 12345678901234567890123456789012345679) c1, LEAST(d2, 1234567890123456789012345678.9012345679) c2,
LEAST(d3, 0.12345678901234567890123456789012345677) c3, LEAST(d1, d4) c4 FROM cs1;
SELECT "least_test", d2 FROM cs1 WHERE LEAST(d2, d5) = d5; # triggers getDoubleVal()
# functor_bool: (not)between, (not)in, regexp, is(not)null, is(not)true, is(not)false
--disable_warnings
DROP TABLE IF EXISTS cs1;
--enable_warnings
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;
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;
SELECT "in_test", d1 IN (99999999999999999999999999999999999998, -12345678901234567890123456789012345678) f1,
d2 IN (-9999999999999999999999999999.9999999998, 1234567890123456789012345678.9012345678) f2,
d3 IN (-0.99999999999999999999999999999999999998, -0.12345678901234567890123456789012345678) f3 FROM cs1;
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;
SELECT "regex_test", d1 REGEXP "123", d2 REGEXP "8.9", d3 REGEXP "0.9" FROM cs1;
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;
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;
# if()/coalesce()
# for the case "select if(a, b, c)" where a=decimal(38), b=decimal(38,10), c=decimal(38,38),
# we can have an overflow, since the resulting column can be of type decimal(M,D) where M > 38
# which we currently do not support in ColumnStore. Same is true for coalesce.
--disable_warnings
DROP TABLE IF EXISTS cs1;
--enable_warnings
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;
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;
# case()
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;
# repeat the tests for unsigned
# bitwise functions
--disable_warnings
DROP TABLE IF EXISTS cs1;
--enable_warnings
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;
SELECT "bitwise_or_test", d1 | d2, d1 | d1, d2 | d2, d1 | 10, d2 | 10 FROM cs1;
# The d1 ^ d2 produces 18446744073709551614 that is the magic value for UBIGINT NULL
SELECT "bitwise_xor_test", d1 ^ d2, d1 ^ d1, d2 ^ d2, d1 ^ 10, d2 ^ 10 FROM cs1 WHERE d1 != 999999999999999999;
# The d1 << 1 produces 18446744073709551614 that is the magic value for UBIGINT NULL
SELECT "bitwise_leftshift_test", d1 << 1, d1 << 10, d1 << 20, d2 << 1, d2 << 10, d2 << 20 FROM cs1 WHERE d1 <= 999999999999999999;
SELECT "bitwise_rightshift_test", d1 >> 1, d1 >> 10, d1 >> 20, d2 >> 1, d2 >> 10, d2 >> 20 FROM cs1;
SELECT "bitcount_test", bit_count(d1), bit_count(d2) FROM cs1;
# period_diff function
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;
# cast a decimal as signed/unsigned/char
--disable_warnings
DROP TABLE IF EXISTS cs1;
--enable_warnings
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;
SELECT "cast_test2", CAST(d1 AS UNSIGNED), CAST(d1 AS CHAR(50)) FROM cs1;
# cast a decimal as date, datetime
DELETE FROM cs1;
INSERT INTO cs1 VALUES (20201212), (19901028183200);
SELECT "cast_test3", CAST(d1 AS DATETIME), CAST(d1 AS DATE) FROM cs1;
# cast a decimal as double
--disable_warnings
DROP TABLE IF EXISTS cs1;
--enable_warnings
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 a decimal/int/char/float/double as decimal
--disable_warnings
DROP TABLE IF EXISTS cs1;
--enable_warnings
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;
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;
SELECT "cast_test5", CAST(d3 as DECIMAL(38,38)), CAST(d6 as DECIMAL(38,38)), CAST(d7 as DECIMAL(38,38)) FROM cs1;
# inet_ntoa() and inet_aton()
--disable_warnings
DROP TABLE IF EXISTS cs1;
--enable_warnings
CREATE TABLE cs1 (d1 DECIMAL(38) UNSIGNED) ENGINE=columnstore;
INSERT INTO cs1 VALUES (127), (1278), (1234567890);
SELECT "inet_test", INET_NTOA(d1) FROM cs1;
SELECT "inet_test", d1 FROM cs1 where INET_ATON(d1) = d1;
# char()
DELETE FROM cs1;
INSERT INTO cs1 VALUES (1296911693), (77);
SELECT "char_test", CHAR(d1) FROM cs1;
# string functions
INSERT INTO cs1 VALUES(20201010), (19901230), (1234), (12345);
SELECT "monthname_test", MONTHNAME(d1), SEC_TO_TIME(d1) FROM cs1;
# real functions
# abs()
--disable_warnings
DROP TABLE IF EXISTS cs1;
--enable_warnings
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;
# round()/truncate()
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(d1,38) has a bug
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;
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;
SELECT "round_test", d2 FROM cs1 WHERE ROUND(d2) = "123"; # triggers getDoubleVal()
SELECT "truncate_test", TRUNCATE(d1, 0), TRUNCATE(d1,5), TRUNCATE(d1,-5), TRUNCATE(d1,10), TRUNCATE(d1,-10), TRUNCATE(d1,-38) FROM cs1; # TRUNCATE(d1,38) has a bug
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;
# See MDEV-22502. Fixed now! So enabling back the test for d3.
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;
SELECT "truncate_test", d2 FROM cs1 WHERE TRUNCATE(d2, 0) = "123"; # triggers getDoubleVal()
# format()
# Commenting out format() tests until MDEV-23118 is fixed.
#SELECT "format_test", FORMAT(d1, 0), FORMAT(d1, 10), FORMAT(d1, 38),
# FORMAT(d2, 0), FORMAT(d2, 10), FORMAT(d2, 38),
# FORMAT(d3, 0), FORMAT(d3, 10), FORMAT(d3, 38) FROM cs1;
# mod()
--disable_warnings
DROP TABLE IF EXISTS cs1;
--enable_warnings
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);
# MOD(d3,d1) is a floating point division which can result in different results from innodb due to rounding
# Also, MOD() between decimal and double is excluded from the query as we return the correct result but innodb does not.
#SELECT "mod_test", MOD(d1,d2), MOD(d2,d1), MOD(d1,d3), MOD(d3,d4), MOD(d4,d3), MOD(d1,d5), MOD(d2,d5), MOD(d3,d5), MOD(d4,d5) FROM cs1;
SELECT "mod_test", MOD(d1,d2), MOD(d2,d1), MOD(d1,d3), MOD(d3,d4), MOD(d4,d3) FROM cs1;
# floor(), ceil()
--disable_warnings
DROP TABLE IF EXISTS cs1;
--enable_warnings
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);
#(-99999999999999999999999999999999999999, -9999999999999999999999999999.9999999999, -0.99999999999999999999999999999999999999, -999999, -99999.999, -99999999.9999999999),
SELECT "floor_test", FLOOR(d1), FLOOR(d2), FLOOR(d3), FLOOR(d4), FLOOR(d5), FLOOR(d6) FROM cs1;
SELECT "floor_test", d1, d4 FROM cs1 WHERE FLOOR(d1) = FLOOR(d4); # triggers predicateoperator numericCompare()
SELECT "floor_test", d1, d4 FROM cs1 WHERE FLOOR(d1) = d4;
SELECT "floor_test", d1, d5 FROM cs1 WHERE FLOOR(d1) = FLOOR(d5);
SELECT "floor_test", d1, d5 FROM cs1 WHERE FLOOR(d1) = d5; # triggers Func_floor::getDoubleVal()
SELECT "floor_test", d1, d2 FROM cs1 WHERE FLOOR(d1) = FLOOR(d2);
SELECT "ceil_test", CEIL(d1), CEIL(d2), CEIL(d3), CEIL(d4), CEIL(d5), CEIL(d6) FROM cs1;
SELECT "ceil_test", d1, d4 FROM cs1 WHERE CEIL(d1) = CEIL(d4); # triggers predicateoperator numericCompare()
SELECT "ceil_test", d1, d4 FROM cs1 WHERE CEIL(d1) = d4;
SELECT "ceil_test", d1, d5 FROM cs1 WHERE CEIL(d1) = CEIL(d5);
SELECT "ceil_test", d1, d5 FROM cs1 WHERE CEIL(d1) = d5; # triggers Func_floor::getDoubleVal()
SELECT "ceil_test", d1, d2 FROM cs1 WHERE CEIL(d1) = CEIL(d2);
# makedate(), maketime()
--disable_warnings
DROP TABLE IF EXISTS cs1;
--enable_warnings
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;
# ELT()
--disable_warnings
DROP TABLE IF EXISTS cs1;
--enable_warnings
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;
# least(), greatest()
--disable_warnings
DROP TABLE IF EXISTS cs1;
--enable_warnings
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;
SELECT "greatest_test", d2 FROM cs1 WHERE GREATEST(d2, d5) = d5; # triggers getDoubleVal()
SELECT "least_test", LEAST(d1, 12345678901234567890123456789012345679) c1, LEAST(d2, 1234567890123456789012345678.9012345679) c2,
LEAST(d3, 0.12345678901234567890123456789012345677) c3, LEAST(d1, d4) c4 FROM cs1;
SELECT "least_test", d2 FROM cs1 WHERE LEAST(d2, d5) = d5; # triggers getDoubleVal()
# functor_bool: (not)between, (not)in, regexp, is(not)null, is(not)true, is(not)false
--disable_warnings
DROP TABLE IF EXISTS cs1;
--enable_warnings
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;
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;
SELECT "in_test", d1 IN (99999999999999999999999999999999999998, 12345678901234567890123456789012345678) f1,
d2 IN (9999999999999999999999999999.9999999998, 123.456) f2,
d3 IN (0.123456, 0.12345678901234567890123456789012345678) f3 FROM cs1;
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;
SELECT "regex_test", d1 REGEXP "123", d2 REGEXP "8.9", d3 REGEXP "0.9" FROM cs1;
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;
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;
# if()/coalesce()
# for the case "select if(a, b, c)" where a=decimal(38), b=decimal(38,10), c=decimal(38,38),
# we can have an overflow, since the resulting column can be of type decimal(M,D) where M > 38
# which we currently do not support in ColumnStore. Same is true for coalesce.
--disable_warnings
DROP TABLE IF EXISTS cs1;
--enable_warnings
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;
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;
# case()
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;
--disable_warnings
DROP TABLE IF EXISTS cs1;
DROP TABLE IF EXISTS cs2;
--enable_warnings
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);
# ACOS(), ASIN(), ATAN()
# signed
SELECT "acos_test", ACOS(d1), ACOS(d2), ACOS(d3) FROM cs1;
SELECT "asin_test", ASIN(d1), ASIN(d2), ASIN(d3) FROM cs1;
SELECT "atan_test", ATAN(d1), ATAN(d2), ATAN(d3) FROM cs1;
# unsigned
SELECT "acos_test", ACOS(d1), ACOS(d2), ACOS(d3) FROM cs2;
SELECT "asin_test", ASIN(d1), ASIN(d2), ASIN(d3) FROM cs2;
SELECT "atan_test", ATAN(d1), ATAN(d2), ATAN(d3) FROM cs2;
# SIN(), COS(), COT(), TAN()
# signed
# Remove the TRUNCATE() wrapper from the below once MCOL-4811 is fixed.
SELECT "cos_test", TRUNCATE(COS(d1), 15), COS(d2), COS(d3) FROM cs1;
SELECT "sin_test", SIN(d1), SIN(d2), SIN(d3) FROM cs1;
SELECT "tan_test", TAN(d1), TAN(d2), TAN(d3) FROM cs1;
# unsigned
# Remove the TRUNCATE() wrapper from the below once MCOL-4811 is fixed.
SELECT "cos_test", TRUNCATE(COS(d1), 15), COS(d2), COS(d3) FROM cs2;
SELECT "sin_test", SIN(d1), SIN(d2), SIN(d3) FROM cs2;
SELECT "tan_test", TAN(d1), TAN(d2), TAN(d3) FROM cs2;
# MD5(), SHA1(), SHA()
# signed
SELECT "md5_test", MD5(d1), MD5(d2), MD5(d3) FROM cs1;
SELECT "sha1_test", SHA1(d1), SHA1(d2), SHA1(d3) FROM cs1;
SELECT "sha_test", SHA(d1), SHA(d2), SHA(d3) FROM cs1;
# unsigned
SELECT "md5_test", MD5(d1), MD5(d2), MD5(d3) FROM cs2;
SELECT "sha1_test", SHA1(d1), SHA1(d2), SHA1(d3) FROM cs2;
SELECT "sha_test", SHA(d1), SHA(d2), SHA(d3) FROM cs2;
# CEILING()
# signed
SELECT "ceiling_test", CEILING(d1), CEILING(d2), CEILING(d3) FROM cs1;
# unsigned
SELECT "ceiling_test", CEILING(d1), CEILING(d2), CEILING(d3) FROM cs2;
# SIGN()
# signed
SELECT "sign_test", SIGN(d1), SIGN(d2), SIGN(d3) FROM cs1;
# unsigned
SELECT "sign_test", SIGN(d1), SIGN(d2), SIGN(d3) FROM cs2;
# CONCAT_WS(), CONV(), CONVERT()
# signed
SELECT "concat_ws_test", CONCAT_WS(' = d1, d2 = ', d1, d2) FROM cs1;
SELECT "convert_test", CONVERT(d1, SIGNED), CONVERT(d2, SIGNED), CONVERT(d3, SIGNED) FROM cs1;
SELECT "conv_test", CONV(d1, 10, 8), CONV(d2, 10, 8), CONV(d3, 10, 8) FROM cs1;
# unsigned
SELECT "concat_ws_test", CONCAT_WS(' = d2, d3 = ', d2, d3) FROM cs2;
SELECT "convert_test", CONVERT(d1, SIGNED), CONVERT(d2, SIGNED), CONVERT(d3, SIGNED) FROM cs2;
SELECT "conv_test", CONV(d1, 10, 8), CONV(d2, 10, 8), CONV(d3, 10, 8) FROM cs2;
#REVERSE(), STRCMP(), REPEAT()
# signed
SELECT "reverse_test", REVERSE(d1), REVERSE(d2), REVERSE(d3) FROM cs1;
SELECT "strcmp_test", STRCMP(d1, d2), STRCMP(d2, d3), STRCMP(d3, d1), STRCMP(d1, 123456) FROM cs1;
# unsigned
SELECT "reverse_test", REVERSE(d1), REVERSE(d2), REVERSE(d3) FROM cs2;
SELECT "strcmp_test", STRCMP(d1, d2), STRCMP(d2, d3), STRCMP(d3, d1), STRCMP(d1, 123456) FROM cs2;
SELECT "repeat_test", REPEAT(d3, 2) FROM cs2;
# CRC32(), DEGREES(), RADIANS()
# signed
SELECT "crc32_test", CRC32(d1), CRC32(d2), CRC32(d3) FROM cs1;
SELECT "degrees_test", DEGREES(d1), DEGREES(d2), DEGREES(d3) FROM cs1;
SELECT "radians_test", RADIANS(d1), RADIANS(d2), RADIANS(d3) FROM cs1;
# usigned
SELECT "crc32_test", CRC32(d1), CRC32(d2), CRC32(d3) FROM cs2;
SELECT "degrees_test", DEGREES(d1), DEGREES(d2), DEGREES(d3) FROM cs2;
SELECT "radians_test", RADIANS(d1), RADIANS(d2), RADIANS(d3) FROM cs2;
# HEX()
# signed
SELECT "hex_test", HEX(d1), HEX(d2), HEX(d3) FROM cs1;
# unsigned
SELECT "hex_test", HEX(d1), HEX(d2), HEX(d3) FROM cs2;
# INSERT()
# signed
SELECT "insert_test", INSERT(d1,6,1,d2), INSERT(d2,0,1,d3) FROM cs1;
# usigned
SELECT "insert_test", INSERT(d1,6,1,d2), INSERT(d2,0,1,d3) FROM cs2;
DROP DATABASE test_mcol641_functions;
View Git Blame Copy Permalink