v22.12.3.6-clib

Release v22.12.3.6-clib

v22.11.2.31-clib

Release v22.11.2.31-clib

Image is published at icr.io/clickhouse/clickhouse:22.11.2.31-1-stable-ibm

• This Release is of Latest 22.11 commit (v22.11.2.30-stable) + KQL
• From branch Kusto-phase3-ibm

---

December 09, 2022

KQL implemented features

Functions

• count_distinct
  Customers | summarize count_distinct(Education);
• count_distinctif
  Customers | summarize count_distinctif(Education, Age > 30);
• iff
  Customers | extend t = iff(Age <= 10, "smaller", "bigger");
• iif
  Customers | extend t = iif(Age <= 10, "smaller", "bigger");

bug fixed

• indexOf function doesn't work for extended parameters
• Create generic function for time arithmetic
• KQL Phase 2: tolong should return the number of ticks when supplied with a timespan

v22.8.9.26-clib

Release v22.8.9.26-clib

Image is published at icr.io/clickhouse/clickhouse:22.8.9.26-1-clib-ibm

• This Release is on top of v22.8.9.24-lts
• From branch Kusto-phase3

---

November 25, 2022

KQL implemented features

Operator

• join

  CREATE TABLE X (Key String, Value1 Int64) ENGINE = Memory;
  INSERT INTO X VALUES  ('a',1);
  INSERT INTO X VALUES  ('b',2);
  INSERT INTO X VALUES  ('b',3);
  INSERT INTO X VALUES  ('c',4);
  
  CREATE TABLE Y  (Key String, Value2 Int64) ENGINE = Memory;
  INSERT INTO  Y  VALUES  ('b',10);
  INSERT INTO  Y  VALUES  ('c',20);
  INSERT INTO  Y  VALUES  ('c',30);
  INSERT INTO  Y  VALUES  ('d',40);
  
  Join flavor : 
  
  Default join is innerunique
     X | join Y  on $left.Key == $right.Key ;
     X | join kind=innerunique Y  on Key ;
  
  Inner-join
     X | join kind=inner Y  on Key ;
  
  Left outer-join
     X | join kind=leftouter Y  on Key ;
  
  Right outer-join
     X | join kind=rightouter Y  on Key ;
  
  Full outer-join 
     X | join kind=fullouter Y  on Key ;
  
  Left anti-join
     X | join kind=leftanti Y  on Key ;
  
  Right anti-join
     X | join kind=rightanti Y  on Key ;
  
  Left semi-join
     X | join kind=leftsemi Y  on Key ;
  
  Right semi-join
     X | join kind=rightsemi Y  on Key ;
  

  Deviation from ADX
  Becasue of the limitation between KQL and SQL. the result may different from ADX.(KQL-CH take the result of ClickHouse)
  • columns
    ADX : common columns are duplicatedc in output
    KQL-CH : only one column for common columns
  • column name
    ADX : column with same name (not common) ->column1
    KQL-CH : column with same name (not common) -> right_.column
  • filters
    ADX: Kusto is optimized to push filters that come after the join, towards the appropriate join side, left or right, when possible
    KQL-CH: because in the domanin of KQL, does not know the schema of tables, so the push need to manualy done by user, like:

    t1|join kind = innerunique t2 on key | where value == 'val1.2'
    

    need to chang as the fowllowing by user(if user want) :

    t1| where value == 'val1.2' | join kind = innerunique t2 on key 
    

  • semi join flavor
    ADX : only returns left side or right side columns
    KQL-CH : returns columns from both side
  • Join hints : not supported yet
• lookup
  lookup is a subset of join, only support : kind=leftouter and kind=inner. if kind unspecified, kind=leftouter

  DROP TABLE IF EXISTS FactTable;
  CREATE TABLE FactTable (Row String, Personal String, Family String) ENGINE = Memory;
  INSERT INTO FactTable VALUES  ('1', 'Bill',   'Gates');
  INSERT INTO FactTable VALUES  ('2', 'Bill',   'Clinton');
  INSERT INTO FactTable VALUES  ('3', 'Bill',   'Clinton');
  INSERT INTO FactTable VALUES  ('4', 'Steve',  'Ballmer');
  INSERT INTO FactTable VALUES  ('5', 'Tim',    'Cook');
  
  DROP TABLE IF EXISTS DimTable;
  CREATE TABLE DimTable (Personal String, Family String, Alias String) ENGINE = Memory;
  INSERT INTO DimTable VALUES  ('Bill',  'Gates',   'billg');
  INSERT INTO DimTable VALUES  ('Bill',  'Clinton', 'billc');
  INSERT INTO DimTable VALUES  ('Steve', 'Ballmer', 'steveb');
  INSERT INTO DimTable VALUES  ('Tim',   'Cook',    'timc');  
  
  FactTable | lookup kind=leftouter DimTable on Personal, Family 
  
  FactTable | lookup kind=inner  DimTable on Personal, Family 
  

Bugs fixed

• Incorrect Regx convertion
• KQL phase 2 - timespan calculation results in exception
• KQL phase 2 - format_timespan returns incorrect results
• Bin function should support time intervals less than 1 second
• KQL Phase 2: datetime subtraction results in exception
• Timespan() doesn't parse bareword arguments.
• KQL-phase2 distinct operator does not support alias

v22.8.6.73-clib

Release v22.8.6.73-clib

Image is published at icr.io/clickhouse/clickhouse:22.8.6.73-1-clib-ibm

• This Release is on top of v22.8.6.71-lts
• From branch Kusto-phase3

---

October 25, 2022

KQL implemented features

New Operators

• count
  Customers | count;
Customers | where Age< 30 | count;
Customers | where Age< 30 | limit 2 | count;
Customers | where Age< 30 | limit 2 | count | project Count;

• top
  Customers | top 3 by Age;
Customers | top 3 by Age desc;
Customers | top 3 by Age asc | order by FirstName;
Customers | top 3 by FirstName desc nulls first;
Customers | top 3 by FirstName desc nulls last;
Customers | top 3 by Age | top 2 by FirstName;

• top-hitters
  Customers | top-hitters a = 2 of Age by extra;
Customers | top-hitters 2 of Age;
Customers | top-hitters 2 of Age by extra | top-hitters 2 of Age | order by Age;
Customers | top-hitters 2 of Age by extra | where Age > 30;
Customers | top-hitters 2 of Age by extra | where approximate_sum_extra < 200;
Customers | top-hitters 2 of Age | where approximate_count_Age > 2;

Bugs fixed

• parse_version needs to return null when parameter is empty string
• Different expressions with the same alias in function substring
• parse_version needs to return null when parameter is empty string
• parse_url() output mismatch for empty string
• array_sum and array_length return incorrect results

v22.8.6.72-clib

Release v22.8.6.72-clib

Image is published at icr.io/clickhouse/clickhouse:22.8.6.72-1-clib-ibm

• This Release is on top of v22.8.6.71-lts
• From branch Kusto-phase3

---

October 12, 2022

operator

• distinct
  Customers | distinct *
Customers | distinct Occupation
Customers | distinct Occupation, Education
Customers | where Age <30 | distinct Occupation, Education
Customers | where Age <30 | order by Age| distinct Occupation, Education

String functions

• reverse
  print reverse(123)
print reverse(123.34)
print reverse('clickhouse')
print reverse(3h)
print reverse(datetime(2017-1-1 12:23:34))

• parse_command_line
  print parse_command_line('echo \"hello world!\" print$?', \"Windows\")

• parse_csv
  print result=parse_csv('aa,b,cc')
print result_multi_record=parse_csv('record1,a,b,c\nrecord2,x,y,z')

• parse_json
  print parse_json( dynamic([1, 2, 3]))
print parse_json('{"a":123.5, "b":"{\\"c\\":456}"}')

• extract_json
  print extract_json( "$.a" , '{"a":123, "b":"{\\"c\\":456}"}' , typeof(int))

• parse_version
  print parse_version('1')
print parse_version('1.2.3.40')

Bug fixed

• correct array index in expression
  array index should start with 0

• Summarize should generate alias or use correct columns

  • if bin is used , the column should be in select list if no alias include
  • if no column included in aggregate functions, ( like count() ), should has alias with fun name + '',e.g count
  • if column name included in aggregate functions, should have fun name + "_" + column name , like count(Age) -> count_Age
  • if argument of an aggregate functions is an exprision, Columns1 ... Columnsn should be used as alias

    Customers | summarize count() by bin(Age, 10)  
    ┌─Age─┬─count_─┐
    │  40 │      2 │
    │  20 │      6 │
    │  30 │      4 │
    └─────┴────────┘
    Customers | summarize count(Age) by bin(Age, 10)  
    ┌─Age─┬─count_Age─┐
    │  40 │         2 │
    │  20 │         6 │
    │  30 │         4 │
    └─────┴───────────┘
    Customers | summarize count(Age+1) by bin(Age+1, 10)
    ┌─Columns1─┬─count_─┐
    │       40 │      2 │
    │       20 │      6 │
    │       30 │      4 │
    └──────────┴────────┘
    

• extend doesn't replace existing columns

• throw exception if use quoted string as alias

• repeat() doesn't work with count argument as negative value

• substring() doesn't work right with negative offsets

• endofmonth() doesn't return correct result

• split() outputs array instead of string

• split() returns empty string when arg goes out of bound

• split() doesn't work with negative index

v22.8.5.31-clib

Release v22.8.5.31-clib

Image is published at icr.io/clickhouse/clickhouse:22.8.5.31-1-clib-ibm

• This Release is on top of v22.8.5.29-lts
• From branch Kusto-phase2

---

September 26, 2022

Bug fixed :

• "select * from kql" results in syntax error
• Parsing ipv4 with arrayStringConcat throws exception
• CH Client crashes on invalid function name
• extract() doesn't work right with 4th argument i.e typeof()
• parse_ipv6_mask return incorrect results
• timespan returns wrong output in seconds
• timespan doesn't work for nanoseconds and tick
• totimespan() doesn't work for nanoseconds and tick timespan unit
• data types should throw exception in certain cases
• decimal does not support scientific notation
• extend statement causes client core dumping
• extend crashes with array sorting
• Core dump happens when WHERE keyword doesn't follow field name
• Null values are missing in the result of `make_list_with_nulls'
• trim functions use non-unique aliases
• format_ipv4_mask returns incorrect mask value

v22.8.8.4-clib

Release v22.8.8.4-clib

Image is published at icr.io/clickhouse/clickhouse:22.8.8.4-1-clib-ibm

• This Release is on top of v22.8.8.3-lts
• From branch Kusto-phase3

---

November 7, 2022

KQL implemented features

Improvement

• array_sort_asc and array_sort_desc

  Returns the same number of arrays as in the input, with the first array sorted in ascending order, and the remaining arrays ordered to match the reordered first array.
  
  null will be returned for every array that differs in length from the first one.
  

  Becasue array in ClickHouse is not nullable, so an array with a single NULL ( [NULL]) is returned instead of a null if array that differs in length from the first one:

  array_sort_asc(dynamic([2, 1, 3]), dynamic([20, 40, 30]), dynamic([100, 200])) -> [1,2,3,NULL],[10,20,30,40],[NULL]
  

  the result can be used as a condition

  DROP TABLE IF EXISTS visit;
  CREATE TABLE visit(pageid UInt8, ip_country Array(Nullable(String)), hit Array(Int64),duration Array(Int64)) ENGINE = Memory;
  INSERT INTO visit VALUES (1,['CA', 'US','FR','Eng'], [11,16,12,20],[100,500,300,200]);
  INSERT INTO visit VALUES (2,['Japan', 'Gem','FR','Eng'], [31,22,33,10],[510,410,310,210]);
  INSERT INTO visit VALUES (3,['CA', 'Gem','Japan','Eng'], [25,10,23,11],[120,110,130]);
  INSERT INTO visit VALUES (4,['CA', 'Gem',null,'Eng'], [5,10,3,2],[220,320,310,150]);
  INSERT INTO visit VALUES (5,['FR', null,'US','Eng'], [16,12,23,10],[210,250,110,260]);
  
  visit | project *, array_sort_asc(ip_country, hit, duration)
  ┌─pageid─┬─ip_country─────────────────┬─hit───────────┬─duration──────────┬─kql_array_sort_asc(ip_country, hit, duration)────────────────┐
  │      2 │ ['Japan','Gem','FR','Eng'] │ [31,22,33,10] │ [510,410,310,210] │ (['Eng','FR','Gem','Japan'],[10,33,22,31],[210,310,410,510]) │
  └────────┴────────────────────────────┴───────────────┴───────────────────┴──────────────────────────────────────────────────────────────┘
  ┌─pageid─┬─ip_country─────────────┬─hit───────────┬─duration──────────┬─kql_array_sort_asc(ip_country, hit, duration)────────────┐
  │      1 │ ['CA','US','FR','Eng'] │ [11,16,12,20] │ [100,500,300,200] │ (['CA','Eng','FR','US'],[11,20,12,16],[100,200,300,500]) │
  └────────┴────────────────────────┴───────────────┴───────────────────┴──────────────────────────────────────────────────────────┘
  ┌─pageid─┬─ip_country──────────────┬─hit────────┬─duration──────────┬─kql_array_sort_asc(ip_country, hit, duration)──────────┐
  │      4 │ ['CA','Gem',NULL,'Eng'] │ [5,10,3,2] │ [220,320,310,150] │ (['CA','Eng','Gem',NULL],[5,2,10,3],[220,150,320,310]) │
  └────────┴─────────────────────────┴────────────┴───────────────────┴────────────────────────────────────────────────────────┘
  ┌─pageid─┬─ip_country─────────────────┬─hit───────────┬─duration──────┬─kql_array_sort_asc(ip_country, hit, duration)─────┐
  │      3 │ ['CA','Gem','Japan','Eng'] │ [25,10,23,11] │ [120,110,130] │ (['CA','Eng','Gem','Japan'],[25,11,10,23],[NULL]) │
  └────────┴────────────────────────────┴───────────────┴───────────────┴───────────────────────────────────────────────────┘
  ┌─pageid─┬─ip_country─────────────┬─hit───────────┬─duration──────────┬─kql_array_sort_asc(ip_country, hit, duration)────────────┐
  │      5 │ ['FR',NULL,'US','Eng'] │ [16,12,23,10] │ [210,250,110,260] │ (['Eng','FR','US',NULL],[10,16,23,12],[260,210,110,250]) │
  └────────┴────────────────────────┴───────────────┴───────────────────┴──────────────────────────────────────────────────────────┘  
  
  visit | where isnull((array_sort_asc(ip_country, hit, duration))[2][0])
  ┌─pageid─┬─ip_country─────────────────┬─hit───────────┬─duration──────┐
  │      3 │ ['CA','Gem','Japan','Eng'] │ [25,10,23,11] │ [120,110,130] │
  └────────┴────────────────────────────┴───────────────┴───────────────┘
  

  the following behavious are same as Azure Data Explorer
  if no alias specified, the functions return a single tuple includes arrays. can use array sbscripon to access the element inside. for exapmple:

  print array_sort_asc(dynamic([2, 1, 3]), dynamic([20, 40, 30]), dynamic([100, 200]))[0] -> [1,2,3]
  

  if a signle alias is used the firt array as an column is returned :

  print t = array_sort_asc(dynamic([2, 1, 3]), dynamic([20, 40, 30]), dynamic([100, 200]))
  ┌─t───────┐
  │ [1,2,3] │
  └─────────┘
  

  if a n aliasies are used the first n arrays as columns are returned :

  print 5, (t,w) = array_sort_asc(dynamic([2, 1, 3]), dynamic([20, 40, 30]), dynamic([100, 200]))
  ┌─5─┬─t───────┬─w──────────┐
  │ 5 │ [1,2,3] │ [40,20,30] │
  └───┴─────────┴────────────┘
  

New Functions

• case
  Customers | extend t = case(Age <= 10, "A", Age <= 20, "B", Age <= 30, "C", "D");

Bug fixed

• summarize crash if aggregation function is missing

  fixed with throw exception:
  
  Exception on client:
  Code: 62. DB::Exception: Syntax error near keyword "by". (SYNTAX_ERROR)
  

• make_datetime creates wrong date time

• todecimal() doesn't work with column arguments

v22.8.4.8-clib

Release v22.8.4.8-clib

Image is published at icr.io/clickhouse/clickhouse:22.8.4.8-1-clib-ibm

• This Release is on top of v22.8.4.7-lts
• From branch Kusto-release

---

KQL implemented features

September 12, 2022

Extend operator

https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/extendoperator
T | extend T | extend duration = endTime - startTime
T | project endTime, startTime | extend duration = endTime - startTime

Array functions

• array_reverse
  print array_reverse(dynamic(["this", "is", "an", "example"])) == dynamic(["example","an","is","this"])

• array_rotate_left
  print array_rotate_left(dynamic([1,2,3,4,5]), 2) == dynamic([3,4,5,1,2])
print array_rotate_left(dynamic([1,2,3,4,5]), -2) == dynamic([4,5,1,2,3])

• array_rotate_right
  print array_rotate_right(dynamic([1,2,3,4,5]), -2) == dynamic([3,4,5,1,2])
print array_rotate_right(dynamic([1,2,3,4,5]), 2) == dynamic([4,5,1,2,3])

• array_shift_left
  print array_shift_left(dynamic([1,2,3,4,5]), 2) == dynamic([3,4,5,null,null])
print array_shift_left(dynamic([1,2,3,4,5]), -2) == dynamic([null,null,1,2,3])
print array_shift_left(dynamic([1,2,3,4,5]), 2, -1) == dynamic([3,4,5,-1,-1])
print array_shift_left(dynamic(['a', 'b', 'c']), 2) == dynamic(['c','',''])

• array_shift_right
  print array_shift_right(dynamic([1,2,3,4,5]), -2) == dynamic([3,4,5,null,null])
print array_shift_right(dynamic([1,2,3,4,5]), 2) == dynamic([null,null,1,2,3])
print array_shift_right(dynamic([1,2,3,4,5]), -2, -1) == dynamic([3,4,5,-1,-1])
print array_shift_right(dynamic(['a', 'b', 'c']), -2) == dynamic(['c','',''])

• pack_array
  print x = 1, y = x * 2, z = y * 2, pack_array(x,y,z)

  Please note that only arrays of elements of the same type may be created at this time. The underlying reasons are explained under the release note section of the dynamic data type.

• repeat
  print repeat(1, 0) == dynamic([])
print repeat(1, 3) == dynamic([1, 1, 1])
print repeat("asd", 3) == dynamic(['asd', 'asd', 'asd'])
print repeat(timespan(1d), 3) == dynamic([86400, 86400, 86400])
print repeat(true, 3) == dynamic([true, true, true])

• zip
  print zip(dynamic([1,3,5]), dynamic([2,4,6]))

  Please note that only arrays of the same type are supported in our current implementation. The underlying reasons are explained under the release note section of the dynamic data type.

Data types

• dynamic
  print isnull(dynamic(null))
print dynamic(1) == 1
print dynamic(timespan(1d)) == 86400
print dynamic([1, 2, 3])
print dynamic([[1], [2], [3]])
print dynamic(['a', "b", 'c'])

  According to the KQL specifications dynamic is a literal, which means that no function calls are permitted. Expressions producing literals such as datetime and timespan and their aliases (ie. date and time, respectively) along with nested dynamic literals are allowed.

  Please note that our current implementation supports only scalars and arrays made up of elements of the same type. Support for mixed types and property bags is deferred for now, based on our understanding of the required effort and discussion with representatives of the QRadar team.

Mathematical functions

• isnan
  print isnan(double(nan)) == true
print isnan(4.2) == false
print isnan(4) == false
print isnan(real(+inf)) == false

Set functions

Please note that functions returning arrays with set semantics may return them in any particular order, which may be subject to change in the future.

• jaccard_index
  print jaccard_index(dynamic([1, 1, 2, 2, 3, 3]), dynamic([1, 2, 3, 4, 4, 4])) == 0.75
print jaccard_index(dynamic([1, 2, 3]), dynamic([])) == 0
print jaccard_index(dynamic([]), dynamic([1, 2, 3, 4])) == 0
print isnan(jaccard_index(dynamic([]), dynamic([])))
print jaccard_index(dynamic([1, 2, 3]), dynamic([4, 5, 6, 7])) == 0
print jaccard_index(dynamic(['a', 's', 'd']), dynamic(['f', 'd', 's', 'a'])) == 0.75
print jaccard_index(dynamic(['Chewbacca', 'Darth Vader', 'Han Solo']), dynamic(['Darth Sidious', 'Darth Vader'])) == 0.25

• set_difference
  print set_difference(dynamic([1, 1, 2, 2, 3, 3]), dynamic([1, 2, 3])) == dynamic([])
print array_sort_asc(set_difference(dynamic([1, 4, 2, 3, 5, 4, 6]), dynamic([1, 2, 3])))[1] == dynamic([4, 5, 6])
print set_difference(dynamic([4]), dynamic([1, 2, 3])) == dynamic([4])
print array_sort_asc(set_difference(dynamic([1, 2, 3, 4, 5]), dynamic([5]), dynamic([2, 4])))[1] == dynamic([1, 3])
print array_sort_asc(set_difference(dynamic([1, 2, 3]), dynamic([])))[1] == dynamic([1, 2, 3])
print array_sort_asc(set_difference(dynamic(['a', 's', 'd']), dynamic(['a', 'f'])))[1] == dynamic(['d', 's'])
print array_sort_asc(set_difference(dynamic(['Chewbacca', 'Darth Vader', 'Han Solo']), dynamic(['Darth Sidious', 'Darth Vader'])))[1] == dynamic(['Chewbacca', 'Han Solo'])

• set_has_element
  print set_has_element(dynamic(["this", "is", "an", "example"]), "example") == true
print set_has_element(dynamic(["this", "is", "an", "example"]), "examplee") == false
print set_has_element(dynamic([1, 2, 3]), 2) == true
print set_has_element(dynamic([1, 2, 3, 4.2]), 4) == false

• set_intersect
  print array_sort_asc(set_intersect(dynamic([1, 1, 2, 2, 3, 3]), dynamic([1, 2, 3])))[1] == dynamic([1, 2, 3])
print array_sort_asc(set_intersect(dynamic([1, 4, 2, 3, 5, 4, 6]), dynamic([1, 2, 3])))[1] == dynamic([1, 2, 3])
print set_intersect(dynamic([4]), dynamic([1, 2, 3])) == dynamic([])
print set_intersect(dynamic([1, 2, 3, 4, 5]), dynamic([1, 3, 5]), dynamic([2, 5])) == dynamic([5])
print set_intersect(dynamic([1, 2, 3]), dynamic([])) == dynamic([])
print set_intersect(dynamic(['a', 's', 'd']), dynamic(['a', 'f'])) == dynamic(['a'])
print set_intersect(dynamic(['Chewbacca', 'Darth Vader', 'Han Solo']), dynamic(['Darth Sidious', 'Darth Vader'])) == dynamic(['Darth Vader'])

• set_union
  print array_sort_asc(set_union(dynamic([1, 1, 2, 2, 3, 3]), dynamic([1, 2, 3])))[1] == dynamic([1, 2, 3])
print array_sort_asc(set_union(dynamic([1, 4, 2, 3, 5, 4, 6]), dynamic([1, 2, 3])))[1] == dynamic([1, 2, 3, 4, 5, 6])
print array_sort_asc(set_union(dynamic([4]), dynamic([1, 2, 3])))[1] == dynamic([1, 2, 3, 4])
print array_sort_asc(set_union(dynamic([1, 3, 4]), dynamic([5]), dynamic([2, 4])))[1] == dynamic([1, 2, 3, 4, 5])
print array_sort_asc(set_union(dynamic([1, 2, 3]), dynamic([])))[1] == dynamic([1, 2, 3])
print array_sort_asc(set_union(dynamic(['a', 's', 'd']), dynamic(['a', 'f'])))[1] == dynamic(['a', 'd', 'f', 's'])
print array_sort_asc(set_union(dynamic(['Chewbacca', 'Darth Vader', 'Han Solo']), dynamic(['Darth Sidious', 'Darth Vader'])))[1] == dynamic(['Chewbacca', 'Darth Sidious', 'Darth Vader', 'Han Solo'])

v22.8.1.4-clib

Release v22.8.1.4-clib

Image is published at icr.io/clickhouse/clickhouse:22.8.1.4-1-clib-ibm

• release from branch Kusto-release

KQL implemented features

---

August 29, 2022

mv-expand operator

https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/mvexpandoperator
Note: expand on array columns only

• test cases

  CREATE TABLE T
  (    
     a UInt8,
     b Array(String),
     c Array(Int8),
     d Array(Int8)
  ) ENGINE = Memory;
  
  INSERT INTO T VALUES (1, ['Salmon', 'Steak','Chicken'],[1,2,3,4],[5,6,7,8])
  
  T | mv-expand c  
  T | mv-expand c, d  
  T | mv-expand b | mv-expand c  
  T | mv-expand c to typeof(bool)  
  T | mv-expand with_itemindex=index b, c, d  
  T | mv-expand array_concat(c,d)   
  T | mv-expand x = c, y = d   
  T | mv-expand xy = array_concat(c, d)  
  T | mv-expand with_itemindex=index c,d to typeof(bool)  
  

make-series operator

https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/make-seriesoperator

• test case make-series on datetime column

  CREATE TABLE T
  (    
     Supplier Nullable(String),
     Fruit String ,
     Price Float64,
     Purchase Date 
  ) ENGINE = Memory;
  
  INSERT INTO T VALUES  ('Aldi','Apple',4,'2016-09-10');
  INSERT INTO T VALUES  ('Costco','Apple',2,'2016-09-11');
  INSERT INTO T VALUES  ('Aldi','Apple',6,'2016-09-10');
  INSERT INTO T VALUES  ('Costco','Snargaluff',100,'2016-09-12');
  INSERT INTO T VALUES  ('Aldi','Apple',7,'2016-09-12');
  INSERT INTO T VALUES  ('Aldi','Snargaluff',400,'2016-09-11');
  INSERT INTO T VALUES  ('Costco','Snargaluff',104,'2016-09-12');
  INSERT INTO T VALUES  ('Aldi','Apple',5,'2016-09-12');
  INSERT INTO T VALUES  ('Aldi','Snargaluff',600,'2016-09-11');
  INSERT INTO T VALUES  ('Costco','Snargaluff',200,'2016-09-10');
  

  Have from and to

  T |  make-series PriceAvg = avg(Price) default=0 on Purchase from datetime(2016-09-10)  to datetime(2016-09-13) step 1d by Supplier, Fruit
  

  Has from , without to

  T |  make-series PriceAvg = avg(Price) default=0 on Purchase from datetime(2016-09-10)  step 1d by Supplier, Fruit
  

  Without from , has to

  T |  make-series PriceAvg = avg(Price) default=0 on Purchase  to datetime(2016-09-13) step 1d by Supplier, Fruit
  

  Without from , without to

  T |  make-series PriceAvg = avg(Price) default=0 on Purchase step 1d by Supplier, Fruit
  

  Without by clause

  T |  make-series PriceAvg = avg(Price) default=0 on Purchase step 1d
  

  Without aggregation alias

  T |  make-series avg(Price) default=0 on Purchase step 1d by Supplier, Fruit
  

  Has group expression alias

  T |  make-series avg(Price) default=0 on Purchase step 1d by Supplier_Name = Supplier, Fruit
  

  Use different step value

  T |  make-series PriceAvg = avg(Price) default=0 on Purchase from datetime(2016-09-10)  to datetime(2016-09-13) step 3d by Supplier, Fruit
  

• test case make-series on numeric column

  CREATE TABLE T2
  (    
     Supplier Nullable(String),
     Fruit String ,
     Price Int32,
     Purchase Int32  
  ) ENGINE = Memory;
  
  INSERT INTO T2 VALUES  ('Aldi','Apple',4,10);
  INSERT INTO T2 VALUES  ('Costco','Apple',2,11);
  INSERT INTO T2 VALUES  ('Aldi','Apple',6,10);
  INSERT INTO T2 VALUES  ('Costco','Snargaluff',100,12);
  INSERT INTO T2 VALUES  ('Aldi','Apple',7,12);
  INSERT INTO T2 VALUES  ('Aldi','Snargaluff',400,11);
  INSERT INTO T2 VALUES  ('Costco','Snargaluff',104,12);
  INSERT INTO T2 VALUES  ('Aldi','Apple',5,12);
  INSERT INTO T2 VALUES  ('Aldi','Snargaluff',600,11);
  INSERT INTO T2 VALUES  ('Costco','Snargaluff',200,10);
  

  Have from and to

  T2 | make-series PriceAvg=avg(Price) default=0 on Purchase from 10 to  15 step  1.0  by Supplier, Fruit;
  

  Has from , without to

  T2 | make-series PriceAvg=avg(Price) default=0 on Purchase from 10 step  1.0  by Supplier, Fruit;
  

  Without from , has to

  T2 | make-series PriceAvg=avg(Price) default=0 on Purchase to 18 step  4.0  by Supplier, Fruit;
  

  Without from , without to

  T2 | make-series PriceAvg=avg(Price) default=0 on Purchase step  2.0  by Supplier, Fruit;
  

  Without by clause

  T2 | make-series PriceAvg=avg(Price) default=0 on Purchase step  2.0;
  

Aggregate Functions

• bin
  print bin(4.5, 1)
print bin(time(16d), 7d)
print bin(datetime(1970-05-11 13:45:07), 1d)

• stdev
  Customers | summarize t = stdev(Age) by FirstName

• stdevif
  Customers | summarize t = stdevif(Age, Age < 10) by FirstName

• binary_all_and
  Customers | summarize t = binary_all_and(Age) by FirstName

• binary_all_or
  Customers | summarize t = binary_all_or(Age) by FirstName

• binary_all_xor
  Customers | summarize t = binary_all_xor(Age) by FirstName

• percentiles
  Customers | summarize percentiles(Age, 30, 40, 50, 60, 70) by FirstName

• percentilesw
  DataTable | summarize t = percentilesw(Bucket, Frequency, 50, 75, 99.9)

• percentile
  Customers | summarize t = percentile(Age, 50) by FirstName

• percentilew
  DataTable | summarize t = percentilew(Bucket, Frequency, 50)

Dynamic functions

• array_sort_asc
  (only support the constant dynamic array)
  print t = array_sort_asc(dynamic([null, 'd', 'a', 'c', 'c']))
print t = array_sort_asc(dynamic([4, 1, 3, 2]))
print array_sort_asc(dynamic(['b', 'a', 'c']), dynamic([20, 10, 30]))
print array_sort_asc(dynamic([2, 1, 3]), dynamic(['clickhouse','hello', 'world']))
print t = array_sort_asc( dynamic(['d', null, 'a', 'c', 'c']) , false)
print t = array_sort_asc( dynamic(['d', null, 'a', 'c', 'c']) , 1 > 2)
print t = array_sort_asc( dynamic([null, 'd', null, null, 'a', 'c', 'c', null, null, null]) , false)
print t = array_sort_asc( dynamic([null, null, null]) , false)
print array_sort_asc(dynamic([2, 1, null,3, null]), dynamic([20, 10, 40, 30, 50]), 1 > 2)
print array_sort_asc(dynamic([2, 1, null,3, null]), dynamic([20, 10, 40, 30, 50, 3]), 1 > 2)

• array_sort_desc (only support the constant dynamic array)

  print t = array_sort_desc(dynamic([null, 'd', 'a', 'c', 'c']))
print t = array_sort_desc(dynamic([4, 1, 3, 2]))
print array_sort_desc(dynamic(['b', 'a', 'c']), dynamic([20, 10, 30]))
print array_sort_desc(dynamic([2, 1, 3]), dynamic(['clickhouse','hello', 'world']))
print t = array_sort_desc( dynamic(['d', null, 'a', 'c', 'c']) , 1 < 2)
print t = array_sort_desc( dynamic(['d', null, 'a', 'c', 'c']) , false)
print t = array_sort_desc( dynamic([null, 'd', null, null, 'a', 'c', 'c', null, null, null]) , false)
print t = array_sort_desc( dynamic([null, null, null]) , false)
print array_sort_desc(dynamic([2, 1, null,3, null]), dynamic([20, 10, 40, 30, 50]), 1 > 2)

• array_concat
  print array_concat(dynamic([1, 2, 3]), dynamic([4, 5]), dynamic([6, 7, 8, 9])) == dynamic([1, 2, 3, 4, 5, 6, 7, 8, 9])

• array_iff / array_iif
  print array_iif(dynamic([true, false, true]), dynamic([1, 2, 3]), dynamic([4, 5, 6])) == dynamic([1, 5, 3])
print array_iif(dynamic([true, false, true]), dynamic([1, 2, 3, 4]), dynamic([4, 5, 6])) == dynamic([1, 5, 3])
print array_iif(dynamic([true, false, true, false]), dynamic([1, 2, 3, 4]), dynamic([4, 5, 6])) == dynamic([1, 5, 3, null])
print array_iif(dynamic([1, 0, -1, 44, 0]), dynamic([1, 2, 3, 4]), dynamic([4, 5, 6])) == dynamic([1, 5, 3, 4, null])

• array_slice
  print array_slice(dynamic([1,2,3]), 1, 2) == dynamic([2, 3])
print array_slice(dynamic([1,2,3,4,5]), 2, -1) == dynamic([3, 4, 5])
print array_slice(dynamic([1,2,3,4,5]), -3, -2) == dynamic([3, 4])

• array_split
  print array_split(dynamic([1,2,3,4,5]), 2) == dynamic([[1,2],[3,4,5]])
print array_split(dynamic([1,2,3,4,5]), dynamic([1,3])) == dynamic([[1],[2,3],[4,5]])

DateTimeFunctions

• ago
  print ago(2h)

• [endofday](https://docs.microsoft.com/en-us/a...

v22.8.1.3-clib

Release v22.8.1.3-clib

Image is published at icr.io/clickhouse/clickhouse:22.8.1.3-1-clib-ibm

KQL implemented features

---

August 15, 2022

double quote support
print res = strcat("double ","quote")

Aggregate functions

• bin_at
  print res = bin_at(6.5, 2.5, 7)
print res = bin_at(1h, 1d, 12h)
print res = bin_at(datetime(2017-05-15 10:20:00.0), 1d, datetime(1970-01-01 12:00:00.0))
print res = bin_at(datetime(2017-05-17 10:20:00.0), 7d, datetime(2017-06-04 00:00:00.0))

• array_index_of
  Supports only basic lookup. Do not support start_index, length and occurrence
  print output = array_index_of(dynamic(['John', 'Denver', 'Bob', 'Marley']), 'Marley')
print output = array_index_of(dynamic([1, 2, 3]), 2)

• array_sum
  print output = array_sum(dynamic([2, 5, 3]))
print output = array_sum(dynamic([2.5, 5.5, 3]))

• array_length
  print output = array_length(dynamic(['John', 'Denver', 'Bob', 'Marley']))
print output = array_length(dynamic([1, 2, 3]))

Conversion

• tobool / toboolean
  print tobool(true) == true
print toboolean(false) == false
print tobool(0) == false
print toboolean(19819823) == true
print tobool(-2) == true
print isnull(toboolean('a'))
print tobool('true') == true
print toboolean('false') == false

• todouble / toreal
  print todouble(4) == 4
print toreal(4.2) == 4.2
print isnull(todouble('a'))
print toreal('-0.3') == -0.3

• toint
  print isnull(toint('a'))
print toint(4) == 4
print toint('4') == 4
print isnull(toint(4.2))

• tostring
  print tostring(123) == '123'
print tostring('asd') == 'asd'

DateType

• dynamic
  Supports only 1D array
  print output = dynamic(['a', 'b', 'c'])
print output = dynamic([1, 2, 3])

• bool,boolean
  print bool(1)
print boolean(0)

• datetime
  print datetime(2015-12-31 23:59:59.9)
print datetime('2015-12-31 23:59:59.9')
print datetime("2015-12-31:)

• guid
  print guid(74be27de-1e4e-49d9-b579-fe0b331d3642)
print guid('74be27de-1e4e-49d9-b579-fe0b331d3642')
print guid('74be27de1e4e49d9b579fe0b331d3642')

• int
  print int(1)

• long
  print long(16)

• real
  print real(1)

• timespan ,time
  Note the timespan is used for calculating datatime, so the output is in seconds. e.g. time(1h) = 3600
  print 1d
print 30m
print time('0.12:34:56.7')
print time(2h)
print timespan(2h)

StringFunctions

• base64_encode_fromguid
  print Quine = base64_encode_fromguid('ae3133f2-6e22-49ae-b06a-16e6a9b212eb')
• base64_decode_toarray
  print base64_decode_toarray('S3VzdG8=')
• base64_decode_toguid
  print base64_decode_toguid('YWUzMTMzZjItNmUyMi00OWFlLWIwNmEtMTZlNmE5YjIxMmVi')
• replace_regex
  print replace_regex('Hello, World!', '.', '\\0\\0')
• has_any_index
  print idx = has_any_index('this is an example', dynamic(['this', 'example']))
• translate
  print translate('krasp', 'otsku', 'spark')
• trim
  print trim('--', '--https://bing.com--')
• trim_end
  print trim_end('.com', 'bing.com')
• trim_start
  print trim_start('[^\\w]+', strcat('- ','Te st1','// $'))

DateTimeFunctions

• startofyear
  print startofyear(datetime(2017-01-01 10:10:17), -1)
print startofyear(datetime(2017-01-01 10:10:17), 0)
print startofyear(datetime(2017-01-01 10:10:17), 1)

• weekofyear
  print week_of_year(datetime(2020-12-31))
print week_of_year(datetime(2020-06-15))
print week_of_year(datetime(1970-01-01))
print week_of_year(datetime(2000-01-01))

• startofweek
  print startofweek(datetime(2017-01-01 10:10:17), -1)
print startofweek(datetime(2017-01-01 10:10:17), 0)
print startofweek(datetime(2017-01-01 10:10:17), 1)

• startofmonth
  print startofmonth(datetime(2017-01-01 10:10:17), -1)
print startofmonth(datetime(2017-01-01 10:10:17), 0)
print startofmonth(datetime(2017-01-01 10:10:17), 1)

• startofday
  print startofday(datetime(2017-01-01 10:10:17), -1)
print startofday(datetime(2017-01-01 10:10:17), 0)
print startofday(datetime(2017-01-01 10:10:17), 1)

• monthofyear
  print monthofyear(datetime("2015-12-14"))

• hourofday
  print hourofday(datetime(2015-12-14 18:54:00))

• getyear
  print getyear(datetime(2015-10-12))

• getmonth
  print getmonth(datetime(2015-10-12))

• dayofyear
  print dayofyear(datetime(2015-12-14))

• dayofmonth
  print (datetime(2015-12-14))

• unixtime_seconds_todatetime
  print unixtime_seconds_todatetime(1546300800)

• dayofweek
  print dayofweek(datetime(2015-12-20))

• now
  print now()
print now(2d)
print now(-2h)
print now(5microseconds)
print now(5seconds)
print now(6minutes)
print now(-2d)
print now(time(1d))

Binary functions

• binary_and
  print binary_and(15, 3) == 3
print binary_and(1, 2) == 0
• binary_not
  print binary_not(1) == -2
• binary_or
  print binary_or(3, 8) == 11
print binary_or(1, 2) == 3
• binary_shift_left
  print binary_shift_left(1, 1) == 2
print binary_shift_left(1, 64) == 1
• binary_shift_right
  print binary_shift_right(1, 1) == 0
print binary_shift_right(1, 64) == 1
• binary_xor
  print binary_xor(1, 3) == 2
• bitset_count_ones
  print bitset_count_ones(42) == 3

IP functions

• [format_ipv4](https://docs.microsoft.com/en-us/azure/data-...