This is an automated email from the ASF dual-hosted git repository.
zhangstar333 pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 8ea0457d903 [enhancement](regression-test) Add clickhouse case for SQL
Dialect Convertor (#35231)
8ea0457d903 is described below
commit 8ea0457d9037c3d57cd6ceb90c0586c6ac29aa9e
Author: Jiwen liu <[email protected]>
AuthorDate: Tue Jun 11 11:06:16 2024 +0800
[enhancement](regression-test) Add clickhouse case for SQL Dialect
Convertor (#35231)
In order to improve the test of [SQL Dialect
function](https://doris.apache.org/docs/dev/lakehouse/sql-dialect/), the
clickhouse case was submitted in the first stage. After starting the SQL
Dialect compatible plug-in, you can directly run the SQL of Clickhouse
syntax. This case will not run on the pipeline by default. It is added
to excludeDirectories in the conf and can run normally only after the
sql dialect plug-in is deployed.
---
regression-test/conf/regression-conf.groovy | 5 +-
.../data/dialect_compatible/sql/clickhouse.out | 472 +++++++++++++++++++++
.../pipeline/p0/conf/regression-conf.groovy | 3 +-
.../suites/dialect_compatible/sql/clickhouse.sql | 465 ++++++++++++++++++++
4 files changed, 942 insertions(+), 3 deletions(-)
diff --git a/regression-test/conf/regression-conf.groovy
b/regression-test/conf/regression-conf.groovy
index d03db3da43e..6e9a162df00 100644
--- a/regression-test/conf/regression-conf.groovy
+++ b/regression-test/conf/regression-conf.groovy
@@ -89,8 +89,9 @@ excludeSuites = "test_broker_load"
excludeDirectories = "000_the_start_sentinel_do_not_touch," + // keep this
line as the first line
"segcompaction_p2," +
"workload_manager_p1," +
- "cloud_p0/cache" +
- "zzz_the_end_sentinel_do_not_touch" // keep this line as the last line
+ "cloud_p0/cache," +
+ "zzz_the_end_sentinel_do_not_touch," +
+ "dialect_compatible"// keep this line as the last line
customConf1 = "test_custom_conf_value"
diff --git a/regression-test/data/dialect_compatible/sql/clickhouse.out
b/regression-test/data/dialect_compatible/sql/clickhouse.out
new file mode 100644
index 00000000000..336b29f9b08
--- /dev/null
+++ b/regression-test/data/dialect_compatible/sql/clickhouse.out
@@ -0,0 +1,472 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !clickhouse --
+0
+
+-- !clickhouse_2 --
+3 -1 10 2.0 2 0 2 1 0 1
1
+
+-- !clickhouse_3 --
+64 32 16 8
+
+-- !clickhouse_4 --
+123123 0
+
+-- !clickhouse_5 --
+123123 \N
+
+-- !clickhouse_6 --
+123123 -1
+
+-- !clickhouse_7 --
+123.123 12.3123
+
+-- !clickhouse_8 --
+0.0 0.0
+
+-- !clickhouse_9 --
+\N \N
+
+-- !clickhouse_10 --
+2022-10-10 2022-10-10
+
+-- !clickhouse_11 --
+2022-12-30 1970-01-01
+
+-- !clickhouse_12 --
+2022-12-30 \N
+
+-- !clickhouse_13 --
+2022-12-30T13:44:17
+
+-- !clickhouse_14 --
+2022-12-30T13:44:17 1970-01-01T00:00
+
+-- !clickhouse_15 --
+2022-12-30T13:44:17 \N
+
+-- !clickhouse_16 --
+-1.11100
+
+-- !clickhouse_17 --
+-1.11100
+
+-- !clickhouse_18 --
+2022-12-30 2019-10-30
+
+-- !clickhouse_19 --
+2019-01-08 2019-01-08
+
+-- !clickhouse_20 --
+2021-01-04T23:00 2021-01-04T23:00
+
+-- !clickhouse_21 --
+5 5
+
+-- !clickhouse_22 --
+[0, 1, 2, 3, 4] [1, 2, 3, 4] [1, 3]
+
+-- !clickhouse_23 --
+[1, 2, 3, 4, 5, 6] 1
+
+-- !clickhouse_24 --
+true
+
+-- !clickhouse_25 --
+3
+
+-- !clickhouse_26 --
+3
+
+-- !clickhouse_27 --
+2
+
+-- !clickhouse_28 --
+[1, 2, 3, 4] [1, 1, 1, 1, 2]
+
+-- !clickhouse_29 --
+[1, 2]
+
+-- !clickhouse_30 --
+[2, 3]
+
+-- !clickhouse_31 --
+["a", "b"]
+
+-- !clickhouse_32 --
+["a", "b"]
+
+-- !clickhouse_33 --
+[2, null, 4]
+
+-- !clickhouse_34 --
+["!", "hello", "world"]
+
+-- !clickhouse_35 --
+[3, 3, 1, 0] ["world", "hello", "!"]
+
+-- !clickhouse_36 --
+3
+
+-- !clickhouse_37 --
+[0, 1, 1, 1]
+
+-- !clickhouse_38 --
+[1, 2, 3]
+
+-- !clickhouse_39 --
+[] [1]
+
+-- !clickhouse_40 --
+[3, 2, 1] [3, 2, 1]
+
+-- !clickhouse_41 --
+[1, 2, 3]
+
+-- !clickhouse_42 --
+[3, 4, 5]
+
+-- !clickhouse_43 --
+["abc World"]
+
+-- !clickhouse_44 --
+2
+
+-- !clickhouse_45 --
+3
+
+-- !clickhouse_46 --
+2
+
+-- !clickhouse_47 --
+3
+
+-- !clickhouse_48 --
+1 4
+
+-- !clickhouse_49 --
+[1, 2, 3, 4]
+
+-- !clickhouse_50 --
+720.0
+
+-- !clickhouse_51 --
+1 7 6 0
+
+-- !clickhouse_52 --
+101 25
+
+-- !clickhouse_53 --
+5
+
+-- !clickhouse_54 --
+[30, 31, 32, 33, 100]
+
+-- !clickhouse_55 --
+[30, 31, 32, 33, 100, 200, 500]
+
+-- !clickhouse_56 --
+[30, 31, 32, 33, 100, 200, 500]
+
+-- !clickhouse_57 --
+true
+
+-- !clickhouse_58 --
+true
+
+-- !clickhouse_59 --
+false
+
+-- !clickhouse_60 --
+[3]
+
+-- !clickhouse_61 --
+[1, 2, 3, 4, 5]
+
+-- !clickhouse_62 --
+[1, 2, 4, 5]
+
+-- !clickhouse_63 --
+[1, 2]
+
+-- !clickhouse_64 --
+5
+
+-- !clickhouse_65 --
+1
+
+-- !clickhouse_66 --
+5
+
+-- !clickhouse_67 --
+1
+
+-- !clickhouse_68 --
+5
+
+-- !clickhouse_69 --
+4
+
+-- !clickhouse_70 --
+2
+
+-- !clickhouse_71 --
+1 2 left is smaller
+
+-- !clickhouse_72 --
+2023
+
+-- !clickhouse_73 --
+2
+
+-- !clickhouse_74 --
+4
+
+-- !clickhouse_75 --
+111
+
+-- !clickhouse_76 --
+21
+
+-- !clickhouse_77 --
+10
+
+-- !clickhouse_78 --
+20
+
+-- !clickhouse_79 --
+30
+
+-- !clickhouse_80 --
+2017-11-05 08:07:47 1509840467
+
+-- !clickhouse_81 --
+2023-04-21T00:00
+
+-- !clickhouse_82 --
+2023-04-21T10:00 2023-04-21T00:00
+
+-- !clickhouse_83 --
+2023-04-21T10:20 2023-04-21T10:20
+
+-- !clickhouse_84 --
+2020-01-01T10:20:31
+
+-- !clickhouse_85 --
+2016-12-27 52 52 52
+
+-- !clickhouse_86 --
+2021-01-01 2021-01-01 2021-01-01
+
+-- !clickhouse_87 --
+2015-01-01 2015-01-01
+
+-- !clickhouse_88 --
+2015-01-01 2015-01-01
+
+-- !clickhouse_89 --
+2018-04-01 2018-04-01
+
+-- !clickhouse_90 --
+2018-07-18T01:02:03 2018-07-18T01:02:03
+
+-- !clickhouse_91 --
+2021-01-01
+
+-- !clickhouse_92 --
+2015-01-01
+
+-- !clickhouse_93 --
+202405
+
+-- !clickhouse_94 --
+20240513
+
+-- !clickhouse_95 --
+20240513120733
+
+-- !clickhouse_96 --
+2025-01-01 2025-01-01T00:00 2025-01-01T00:00
+
+-- !clickhouse_97 --
+2024-04-01 2024-04-01T00:00 2024-04-01T00:00
+
+-- !clickhouse_98 --
+2024-07-01 2024-07-01T00:00 2024-07-01T00:00
+
+-- !clickhouse_99 --
+2024-02-05 2024-02-05T00:00 2024-02-05T00:00
+
+-- !clickhouse_100 --
+2024-01-06 2024-01-06T00:00 2024-01-06T00:00
+
+-- !clickhouse_101 --
+2024-01-01T12:00 2024-01-01T12:00 2024-01-01T12:00
+
+-- !clickhouse_102 --
+2024-01-01T00:20 2024-01-01T00:20 2024-01-01T00:20
+
+-- !clickhouse_103 --
+2024-01-01T00:00:30 2024-01-01T00:00:30 2024-01-01T00:00:30
+
+-- !clickhouse_104 --
+2023-01-01 2023-01-01T00:00 2023-01-01T00:00
+
+-- !clickhouse_105 --
+2023-10-01 2023-10-01T00:00 2023-10-01T00:00
+
+-- !clickhouse_106 --
+2023-12-01 2023-12-01T00:00 2023-12-01T00:00
+
+-- !clickhouse_107 --
+2023-12-25 2023-12-25T00:00 2023-12-25T00:00
+
+-- !clickhouse_108 --
+2023-12-01 2023-12-01T00:00 2023-12-01T00:00
+
+-- !clickhouse_109 --
+2023-12-31T12:00 2023-12-31T12:00 2023-12-31T12:00
+
+-- !clickhouse_110 --
+2023-12-31T23:30 2023-12-31T23:30 2023-12-31T23:30
+
+-- !clickhouse_111 --
+2023-12-31T23:59 2023-12-31T23:59 2023-12-31T23:59
+
+-- !clickhouse_112 --
+2010
+
+-- !clickhouse_113 --
+a9993e364706816aba3e25717850c26c9cd0d89d
23097d223405d8228642a477bda255b32aadbce4bda0b3f7e36c9da7
ba7816bf8f01cfea414140de5dae2223b00361a396177a9cb410ff61f20015ad
+
+-- !clickhouse_114 --
+192.168.0.1 192.168.0.1
+
+-- !clickhouse_115 --
+3232235521 3232235521
+
+-- !clickhouse_116 --
+0.0.0.0 0
+
+-- !clickhouse_117 --
+[1, 0, 0]
+
+-- !clickhouse_118 --
+2
+
+-- !clickhouse_119 --
+3
+
+-- !clickhouse_120 --
+1
+
+-- !clickhouse_121 --
+3
+
+-- !clickhouse_122 --
+5
+
+-- !clickhouse_123 --
+2.3333333333333335
+
+-- !clickhouse_124 --
+[1, 2, 3, 4]
+
+-- !clickhouse_125 --
+720.0
+
+-- !clickhouse_126 --
+1
+
+-- !clickhouse_127 --
+1
+
+-- !clickhouse_128 --
+0
+
+-- !clickhouse_129 --
+-1
+
+-- !clickhouse_130 --
+4
+
+-- !clickhouse_131 --
+2
+
+-- !clickhouse_132 --
+5
+
+-- !clickhouse_133 --
+\N
+
+-- !clickhouse_134 --
+[1, 2, 3, 4, 5]
+
+-- !clickhouse_135 --
+[30, 31, 32, 33, 100]
+
+-- !clickhouse_136 --
+[30, 31, 32, 33, 100, 200, 500]
+
+-- !clickhouse_137 --
+[10, 11, 12, 13, 14, 15, 16, 17, 18, 19]
+
+-- !clickhouse_138 --
+true
+
+-- !clickhouse_139 --
+true
+
+-- !clickhouse_140 --
+false
+
+-- !clickhouse_141 --
+5
+
+-- !clickhouse_142 --
+1
+
+-- !clickhouse_143 --
+1
+
+-- !clickhouse_144 --
+[3]
+
+-- !clickhouse_145 --
+[1, 2, 3, 4, 5]
+
+-- !clickhouse_146 --
+[1, 2, 4, 5]
+
+-- !clickhouse_147 --
+[1, 2]
+
+-- !clickhouse_148 --
+1
+
+-- !clickhouse_149 --
+5
+
+-- !clickhouse_150 --
+4
+
+-- !clickhouse_151 --
+2
+
+-- !clickhouse_152 --
+4
+
+-- !clickhouse_153 --
+left is smaller
+
+-- !clickhouse_154 --
+true
+
+-- !clickhouse_155 --
+\N \N \N \N
+
+-- !clickhouse_156 --
+3
+
+-- !clickhouse_157 --
+1
+
diff --git a/regression-test/pipeline/p0/conf/regression-conf.groovy
b/regression-test/pipeline/p0/conf/regression-conf.groovy
index 52e7df89a9d..65cc20093db 100644
--- a/regression-test/pipeline/p0/conf/regression-conf.groovy
+++ b/regression-test/pipeline/p0/conf/regression-conf.groovy
@@ -79,7 +79,8 @@ excludeDirectories = "000_the_start_sentinel_do_not_touch," +
// keep this line
"cloud_p0," +
"nereids_rules_p0/subquery," +
"workload_manager_p1," +
- "zzz_the_end_sentinel_do_not_touch" // keep this line as the last line
+ "zzz_the_end_sentinel_do_not_touch," +
+ "dialect_compatible"// keep this line as the last line
customConf1 = "test_custom_conf_value"
diff --git a/regression-test/suites/dialect_compatible/sql/clickhouse.sql
b/regression-test/suites/dialect_compatible/sql/clickhouse.sql
new file mode 100644
index 00000000000..7d1f1c86ae2
--- /dev/null
+++ b/regression-test/suites/dialect_compatible/sql/clickhouse.sql
@@ -0,0 +1,465 @@
+--dialect option
+set sql_dialect='clickhouse';
+--math operator
+select plus(1, 2),
+ minus(1, 2),
+ multiply(2, 5),
+ divide(4, 2),
+ intDiv(5,2),
+ intDivOrZero(2,0),
+ intDivOrZero(2,1),
+ modulo(3, 2),
+ moduloOrZero(1, 0),
+ moduloOrZero(7, 3),
+ negate(-1);
+
+---Type Conversion
+SELECT toInt64(64), toInt32(32), toInt16('16'), toInt8(8.8);
+SELECT toInt64OrZero('123123'), toInt8OrZero('123qwe123');
+SELECT toInt64OrNull('123123'), toInt8OrNull('123qwe123');
+SELECT toInt64OrDefault('123123', cast('-1' as Int64)),
toInt8OrDefault('123qwe123', cast('-1' as Int8));
+select toFloat32('123.123'),toFloat64('12.3123');
+
+select toFloat32OrZero('123.1d23'),toFloat64OrZero('12.31d23');
+
+select toFloat32OrNull('123.1d23'),toFloat64OrNull('12.31d23');
+
+select toDate('2022-10-10 10:10:10'),toDate('2022-10-10 10:10:10',
'Asia/Shanghai');
+
+SELECT toDateOrZero('2022-12-30'), toDateOrZero('');
+
+SELECT toDateOrNull('2022-12-30'), toDateOrNull('');
+
+SELECT toDateTime('2022-12-30 13:44:17');
+
+SELECT toDateTimeOrZero('2022-12-30 13:44:17'), toDateTimeOrZero('');
+
+SELECT toDateTimeOrNull('2022-12-30 13:44:17'), toDateTimeOrNull('');
+
+SELECT toDecimal32OrNull(toString(-1.111), 5) AS val;
+
+SELECT toDecimal32OrZero(toString(-1.111), 5) AS val;
+
+select toDate('2022-12-30 13:44:17'),t from (select '2019-10-30' as t);
+
+WITH
+ toDate('2019-01-01') AS date,
+ INTERVAL 1 WEEK AS interval_week,
+ toIntervalWeek(1) AS interval_to_week
+SELECT
+ date + interval_week,
+ date + interval_to_week;
+
+
+SELECT parseDateTime('2021-01-04+23:00:00',
'%Y-%m-%d+%H:%i:%s'),parseDateTimeOrNull('2021-01-04+23:00:00',
'%Y-%m-%d+%H:%i:%s');
+
+SELECT length('doris'),OCTET_LENGTH('doris');
+
+SELECT range(5), range(1, 5), range(1, 5, 2);
+
+SELECT arrayConcat([1, 2], [3, 4], [5, 6]) AS res, arrayElement(res,1);
+
+--doris:true,clickhouse:1
+SELECT has([1, 2, NULL], NULL);
+
+SELECT indexOf([1, 3, NULL, NULL], NULL);
+
+select arrayCount(x -> x, [0, 1, 2, 3]);
+
+SELECT countEqual([1, 2, NULL, NULL], NULL);
+
+SELECT arrayEnumerate([0, 1, 2, 3]),arrayEnumerateUniq([0, 1, 2, 3,3]);
+
+SELECT arrayPopBack([1, 2, 3]) AS res;
+
+SELECT arrayPopFront([1, 2, 3]) AS res;
+
+SELECT arrayPushBack(['a'], 'b') AS res;
+
+SELECT arrayPushFront(['b'], 'a') AS res;
+
+SELECT arraySlice([1, 2, NULL, 4, 5], 2, 3) AS res;
+
+SELECT arraySort(['hello', 'world', '!']);
+
+SELECT arrayReverseSort([1, 3, 3, 0]),arrayReverseSort(['hello', 'world',
'!']);
+
+--SELECT arrayShuffle([1, 2, 3, 4]);
+
+SELECT arrayUniq([1, 2, 2, 3]) AS unique_count;
+
+SELECT arrayDifference([1, 2, 3, 4]);
+
+SELECT arrayDistinct([1, 2, 2, 3, 1]);
+
+SELECT
+ arrayIntersect([1, 2], [1, 3], [2, 3]) AS no_intersect,
+ arrayIntersect([1, 2], [1, 3], [1, 4]) AS intersect;
+
+SELECT arrayReverse([1, 2, 3]),reverse([1, 2, 3]);
+
+SELECT arrayCompact([1, 1, 2, 3, 3, 3]);
+
+SELECT arrayMap(x -> (x + 2), [1, 2, 3]) as res;
+
+SELECT arrayFilter(x -> x LIKE '%World%', ['Hello', 'abc World']) AS res;
+
+SELECT arrayFirst(x->x>1,[1,2,3]);
+
+SELECT arrayLast(x->x>1,[1,2,3]);
+
+SELECT arrayFirstIndex(x->x>1,[1,2,3]);
+
+SELECT arrayLastIndex(x->x>1,[1,2,3]);
+
+-- lambda expr not support
+-- SELECT arrayMin([1, 2, 4]) AS res1, arrayMin(x -> (-x), [1, 2, 4]) AS
res2,arrayMax([1, 2, 4]) AS res3,arrayMax(x -> (-x), [1, 2, 4]) AS res4;
+SELECT arrayMin([1, 2, 4]) AS res1,arrayMax([1, 2, 4]) AS res3;
+
+SELECT arrayCumSum([1, 1, 1, 1]) AS res;
+
+SELECT arrayProduct([1,2,3,4,5,6]) as res;
+
+select bitAnd(3,5) ans1,bitOr(3,5) ans2,bitXor(3,5) ans3,bitNot(-1) ans4;
+
+SELECT 101 AS a, bitShiftRight(a, 2) AS a_shifted;
+
+SELECT bitCount(333);
+
+SELECT
bitmapToArray(bitmapSubsetInRange(bitmapBuild([0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,100,200,500]),
toInt32(30), toInt32(200))) AS res;
+
+SELECT
bitmapToArray(bitmapSubsetLimit(bitmapBuild([0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,100,200,500]),
toInt32(30), toInt32(200))) AS res;
+
+SELECT
bitmapToArray(bitmapSubsetLimit(bitmapBuild([0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,100,200,500]),
toInt32(30), toInt32(200))) AS res;
+
+SELECT bitmapContains(bitmapBuild([1,5,7,9]), toInt32(9)) AS res;
+
+SELECT bitmapContains(bitmapBuild([1,5,7,9]), toInt32(9)) AS res;
+
+SELECT bitmapHasAll(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])) AS res;
+
+SELECT bitmapToArray(bitmapAnd(bitmapBuild([1,2,3]),bitmapBuild([3,4,5]))) AS
res;
+
+SELECT bitmapToArray(bitmapOr(bitmapBuild([1,2,3]),bitmapBuild([3,4,5]))) AS
res;
+
+SELECT bitmapToArray(bitmapXor(bitmapBuild([1,2,3]),bitmapBuild([3,4,5]))) AS
res;
+
+SELECT bitmapToArray(bitmapAndnot(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])))
AS res;
+
+SELECT bitmapCardinality(bitmapBuild([1, 2, 3, 4, 5])) AS res;
+
+SELECT bitmapMin(bitmapBuild([1, 2, 3, 4, 5])) AS res;
+
+SELECT bitmapMax(bitmapBuild([1, 2, 3, 4, 5])) AS res;
+
+SELECT bitmapAndCardinality(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])) AS res;
+
+SELECT bitmapOrCardinality(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])) AS res;
+
+SELECT bitmapXorCardinality(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])) AS res;
+
+SELECT bitmapAndnotCardinality(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])) AS
res;
+
+WITH
+ 1 as left1,
+ 2 as right1
+SELECT
+ left1,
+ right1,
+ multiIf(left1 < right1, 'left is smaller', left1 > right1, 'left is
greater', left1 = right1, 'Both equal', 'Null value') AS result;
+
+SELECT toYear(toDateTime('2023-04-21 10:20:30'));
+
+SELECT toQuarter(toDateTime('2023-04-21 10:20:30'));
+
+SELECT toMonth(toDateTime('2023-04-21 10:20:30'));
+
+SELECT toDayOfYear(toDateTime('2023-04-21 10:20:30'));
+
+SELECT toDayOfMonth(toDateTime('2023-04-21 10:20:30'));
+
+SELECT toHour(toDateTime('2023-04-21 10:20:30'));
+
+SELECT toMinute(toDateTime('2023-04-21 10:20:30'));
+
+SELECT toSecond(toDateTime('2023-04-21 10:20:30'));
+
+SELECT
+ '2017-11-05 08:07:47' AS dt_str,
+ toUnixTimestamp(dt_str) AS from_str;
+
+SELECT toStartOfDay(toDateTime('2023-04-21 10:20:30'));
+
+SELECT
+ toStartOfHour(toDateTime('2023-04-21 10:20:30')),
+ toStartOfHour(toDateTime64('2023-04-21', 6));
+
+SELECT
+ toStartOfMinute(toDateTime('2023-04-21 10:20:30')),
+ toStartOfMinute(toDateTime64('2023-04-21 10:20:30.5300', 8));
+
+--doris:2020-01-01 10:20:31 clickhouse:2020-01-01 10:20:30
+WITH toDateTime64('2020-01-01 10:20:30.999', 3) AS dt64
+SELECT toStartOfSecond(dt64);
+
+SELECT toDate('2016-12-27') AS date, toWeek(date) AS week0, toWeek(date,1) AS
week1, toWeek(date,7) AS week7;
+
+SELECT date_add(YEAR, 3, toDate('2018-01-01')),dateAdd(YEAR, 3,
toDate('2018-01-01')),DATE_ADD(YEAR, 3, toDate('2018-01-01'));
+
+SELECT date_sub(YEAR, 3, toDate('2018-01-01')),dateSub(YEAR, 3,
toDate('2018-01-01'));
+
+SELECT date_sub(toDate('2018-01-01'), INTERVAL 3
YEAR),dateSub(toDate('2018-01-01'), INTERVAL 3 YEAR);
+
+select timestamp_add(toDate('2018-01-01'), INTERVAL 3
MONTH),timeStampAdd(toDate('2018-01-01'), INTERVAL 3 MONTH);
+
+select timestamp_sub(MONTH, 5, toDateTime('2018-12-18
01:02:03')),timeStampSub(MONTH, 5, toDateTime('2018-12-18 01:02:03'));
+
+SELECT addDate(toDate('2018-01-01'), INTERVAL 3 YEAR);
+
+SELECT subDate(toDate('2018-01-01'), INTERVAL 3 YEAR);
+
+SELECT
+ toYYYYMM(toDateTime('2024-05-13 12:07:33'), 'US/Eastern');
+
+SELECT toYYYYMMDD(toDateTime('2024-05-13 12:07:33'), 'US/Eastern');
+
+SELECT toYYYYMMDDhhmmss(toDateTime('2024-05-13 12:07:33'), 'US/Eastern');
+
+WITH
+ toDate('2024-01-01') AS `date`,
+ toDateTime('2024-01-01 00:00:00') AS date_time,
+ '2024-01-01 00:00:00' AS date_time_string
+SELECT
+ addYears(date, 1) AS add_years_with_date,
+ addYears(date_time, 1) AS add_years_with_date_time,
+ addYears(date_time_string, 1) AS add_years_with_date_time_string;
+
+WITH
+ toDate('2024-01-01') AS date,
+ toDateTime('2024-01-01 00:00:00') AS date_time,
+ '2024-01-01 00:00:00' AS date_time_string
+SELECT
+ addQuarters(date, 1) AS add_quarters_with_date,
+ addQuarters(date_time, 1) AS add_quarters_with_date_time,
+ addQuarters(date_time_string, 1) AS add_quarters_with_date_time_string;
+
+WITH
+ toDate('2024-01-01') AS date,
+ toDateTime('2024-01-01 00:00:00') AS date_time,
+ '2024-01-01 00:00:00' AS date_time_string
+SELECT
+ addMonths(date, 6) AS add_months_with_date,
+ addMonths(date_time, 6) AS add_months_with_date_time,
+ addMonths(date_time_string, 6) AS add_months_with_date_time_string;
+
+WITH
+ toDate('2024-01-01') AS date,
+ toDateTime('2024-01-01 00:00:00') AS date_time,
+ '2024-01-01 00:00:00' AS date_time_string
+SELECT
+ addWeeks(date, 5) AS add_weeks_with_date,
+ addWeeks(date_time, 5) AS add_weeks_with_date_time,
+ addWeeks(date_time_string, 5) AS add_weeks_with_date_time_string;
+
+WITH
+ toDate('2024-01-01') AS date,
+ toDateTime('2024-01-01 00:00:00') AS date_time,
+ '2024-01-01 00:00:00' AS date_time_string
+SELECT
+ addDays(date, 5) AS add_days_with_date,
+ addDays(date_time, 5) AS add_days_with_date_time,
+ addDays(date_time_string, 5) AS add_days_with_date_time_string;
+
+WITH
+ toDate('2024-01-01') AS date,
+ toDateTime('2024-01-01 00:00:00') AS date_time,
+ '2024-01-01 00:00:00' AS date_time_string
+SELECT
+ addHours(date, 12) AS add_hours_with_date,
+ addHours(date_time, 12) AS add_hours_with_date_time,
+ addHours(date_time_string, 12) AS add_hours_with_date_time_string;
+
+WITH
+ toDate('2024-01-01') AS date,
+ toDateTime('2024-01-01 00:00:00') AS date_time,
+ '2024-01-01 00:00:00' AS date_time_string
+SELECT
+ addMinutes(date, 20) AS add_minutes_with_date,
+ addMinutes(date_time, 20) AS add_minutes_with_date_time,
+ addMinutes(date_time_string, 20) AS add_minutes_with_date_time_string;
+
+WITH
+ toDate('2024-01-01') AS date,
+ toDateTime('2024-01-01 00:00:00') AS date_time,
+ '2024-01-01 00:00:00' AS date_time_string
+SELECT
+ addSeconds(date, 30) AS add_seconds_with_date,
+ addSeconds(date_time, 30) AS add_seconds_with_date_time,
+ addSeconds(date_time_string, 30) AS add_seconds_with_date_time_string;
+
+WITH
+ toDate('2024-01-01') AS date,
+ toDateTime('2024-01-01 00:00:00') AS date_time,
+ '2024-01-01 00:00:00' AS date_time_string
+SELECT
+ subtractYears(date, 1) AS subtract_years_with_date,
+ subtractYears(date_time, 1) AS subtract_years_with_date_time,
+ subtractYears(date_time_string, 1) AS subtract_years_with_date_time_string;
+
+WITH
+ toDate('2024-01-01') AS date,
+ toDateTime('2024-01-01 00:00:00') AS date_time,
+ '2024-01-01 00:00:00' AS date_time_string
+SELECT
+ subtractQuarters(date, 1) AS subtract_quarters_with_date,
+ subtractQuarters(date_time, 1) AS subtract_quarters_with_date_time,
+ subtractQuarters(date_time_string, 1) AS
subtract_quarters_with_date_time_string;
+
+WITH
+ toDate('2024-01-01') AS date,
+ toDateTime('2024-01-01 00:00:00') AS date_time,
+ '2024-01-01 00:00:00' AS date_time_string
+SELECT
+ subtractMonths(date, 1) AS subtract_months_with_date,
+ subtractMonths(date_time, 1) AS subtract_months_with_date_time,
+ subtractMonths(date_time_string, 1) AS
subtract_months_with_date_time_string;
+
+WITH
+ toDate('2024-01-01') AS date,
+ toDateTime('2024-01-01 00:00:00') AS date_time,
+ '2024-01-01 00:00:00' AS date_time_string
+SELECT
+ subtractWeeks(date, 1) AS subtract_weeks_with_date,
+ subtractWeeks(date_time, 1) AS subtract_weeks_with_date_time,
+ subtractWeeks(date_time_string, 1) AS subtract_weeks_with_date_time_string;
+
+WITH
+ toDate('2024-01-01') AS date,
+ toDateTime('2024-01-01 00:00:00') AS date_time,
+ '2024-01-01 00:00:00' AS date_time_string
+SELECT
+ subtractDays(date, 31) AS subtract_days_with_date,
+ subtractDays(date_time, 31) AS subtract_days_with_date_time,
+ subtractDays(date_time_string, 31) AS subtract_days_with_date_time_string;
+
+WITH
+ toDate('2024-01-01') AS date,
+ toDateTime('2024-01-01 00:00:00') AS date_time,
+ '2024-01-01 00:00:00' AS date_time_string
+SELECT
+ subtractHours(date, 12) AS subtract_hours_with_date,
+ subtractHours(date_time, 12) AS subtract_hours_with_date_time,
+ subtractHours(date_time_string, 12) AS
subtract_hours_with_date_time_string;
+
+WITH
+ toDate('2024-01-01') AS date,
+ toDateTime('2024-01-01 00:00:00') AS date_time,
+ '2024-01-01 00:00:00' AS date_time_string
+SELECT
+ subtractMinutes(date, 30) AS subtract_minutes_with_date,
+ subtractMinutes(date_time, 30) AS subtract_minutes_with_date_time,
+ subtractMinutes(date_time_string, 30) AS
subtract_minutes_with_date_time_string;
+
+WITH
+ toDate('2024-01-01') AS date,
+ toDateTime('2024-01-01 00:00:00') AS date_time,
+ '2024-01-01 00:00:00' AS date_time_string
+SELECT
+ subtractSeconds(date, 60) AS subtract_seconds_with_date,
+ subtractSeconds(date_time, 60) AS subtract_seconds_with_date_time,
+ subtractSeconds(date_time_string, 60) AS
subtract_seconds_with_date_time_string;
+
+SELECT formatDateTime(toDate('2010-01-04'), '%Y');
+
+SELECT SHA1('abc'),SHA224('abc'),SHA256('abc');
+
+select IPv4NumToString(3232235521),INET_NTOA(3232235521);
+
+select IPv4StringToNum('192.168.0.1'),INET_ATON('192.168.0.1');
+
+with '0.0.0.0' as str
+select str, IPv4StringToNumOrNull(str);
+
+SELECT arrayExists(x -> x=1,[1,2,3]);
+
+SELECT arrayFirst(x -> x>1,[1,2,3]);
+
+SELECT arrayLast(x -> x>1,[1,2,3]);
+
+SELECT arrayFirstIndex(x -> x>0,[1,2,3]);
+
+SELECT arrayLastIndex(x -> x>1,[1,2,3]);
+
+SELECT arraySum([2, 3]) AS res;
+
+SELECT arrayAvg([1, 2, 4]) AS res;
+
+SELECT arrayCumSum([1, 1, 1, 1]) AS res;
+
+SELECT arrayProduct([1,2,3,4,5,6]) as res;
+
+SELECT bitAnd(1,1);
+
+SELECT bitOr(1,1);
+
+SELECT bitXor(1,1);
+
+SELECT bitNot(0);
+
+SELECT bitShiftLeft(1, 2);
+
+SELECT bitShiftRight(8, 2);
+
+SELECT bitCount(333);
+
+SELECT bitmapBuild([1, 2, 3, 4, 5]) AS res;
+
+SELECT bitmapToArray(bitmapBuild([1, 2, 3, 4, 5])) AS res;
+
+SELECT
bitmapToArray(bitmapSubsetInRange(bitmapBuild([0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,100,200,500]),
toInt32(30), toInt32(200))) AS res;
+
+SELECT
bitmapToArray(bitmapSubsetLimit(bitmapBuild([0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,100,200,500]),
toInt32(30), toInt32(200))) AS res;
+
+SELECT
bitmapToArray(subBitmap(bitmapBuild([0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,100,200,500]),
toInt32(10), toInt32(10))) AS res;
+
+
+SELECT bitmapContains(bitmapBuild([1,5,7,9]), toInt32(9)) AS res;
+
+SELECT bitmapHasAny(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])) AS res;
+
+SELECT bitmapHasAll(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])) AS res;
+
+SELECT bitmapCardinality(bitmapBuild([1, 2, 3, 4, 5])) AS res;
+
+SELECT bitmapMin(bitmapBuild([1, 2, 3, 4, 5])) AS res;
+
+SELECT bitmapMin(bitmapBuild([1, 2, 3, 4, 5])) AS res;
+
+SELECT bitmapToArray(bitmapAnd(bitmapBuild([1,2,3]),bitmapBuild([3,4,5]))) AS
res;
+
+SELECT bitmapToArray(bitmapOr(bitmapBuild([1,2,3]),bitmapBuild([3,4,5]))) AS
res;
+
+SELECT bitmapToArray(bitmapXor(bitmapBuild([1,2,3]),bitmapBuild([3,4,5]))) AS
res;
+
+SELECT bitmapToArray(bitmapAndnot(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])))
AS res;
+
+SELECT bitmapAndCardinality(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])) AS res;
+
+SELECT bitmapOrCardinality(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])) AS res;
+
+SELECT bitmapXorCardinality(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])) AS res;
+
+SELECT bitmapAndnotCardinality(bitmapBuild([1,2,3]),bitmapBuild([3,4,5])) AS
res;
+
+SELECT if(1, plus(2, 2), plus(2, 6));
+
+SELECT multiIf(1 < 2, 'left is smaller', 1 > 2, 'left is greater', 1 = 2,
'Both equal', 'Null value') AS result;
+
+SELECT 1<2;
+
+SELECT NULL < 1, 2 < NULL, NULL < NULL, NULL = NULL;
+
+SELECT greatest(1, 2, toInt8(3), 3.) result;
+
+SELECT least(1, 2, toInt8(3), 3.) result;
+
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
