[
https://issues.apache.org/jira/browse/CALCITE-6967?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan resolved CALCITE-6967.
---------------------------------
Resolution: Fixed
> Unparsing STARTS_WITH/ENDS_WITH/BIT functions is incorrect for the Clickhouse
> dialect
> -------------------------------------------------------------------------------------
>
> Key: CALCITE-6967
> URL: https://issues.apache.org/jira/browse/CALCITE-6967
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.39.0
> Reporter: Yu Xu
> Assignee: Yu Xu
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.40.0
>
>
> In ClickHouse select STARTWITH('aa', 'a'); would convert to
> {code:java}
> select STARTS_WITH('aa', 'a'); {code}
> this is not work and error out:
> {code:java}
> Code: 46. DB::Exception: Received from localhost:9000. DB::Exception:
> Function with name `starts_with` does not exist. In scope SELECT
> starts_with('aa', 'a'). Maybe you meant: ['startsWith']. (UNKNOWN_FUNCTION)
> {code}
> at the same time, startswith also not work(lower/upper):
> {code:java}
> select startswith('aa', 'a');
> >Code: 46. DB::Exception: Received from localhost:9000. DB::Exception:
> >Function with name `startswith` does not exist. In scope SELECT
> >startswith('aa', 'a'). Maybe you meant: ['startsWith'].
> >(UNKNOWN_FUNCTION){code}
> {code:java}
> select STARTSWITH('aa', 'a');
> >Code: 46. DB::Exception: Received from localhost:9000. DB::Exception:
> >Function with name `STARTSWITH` does not exist. In scope SELECT
> >STARTSWITH('aa', 'a'). Maybe you meant: ['startsWith']. (UNKNOWN_FUNCTION)
> {code}
> it is the same with endswith function, this is a bit strange because
> startsWith/endsWith is need camel case and other string functions not
> need(such as char_length/CHAR_LENGTH both work well in ClickHouse).
> we can refer to the document:
> [https://clickhouse.com/docs/sql-reference/functions/string-functions#startswith]
>
> {code:java}
> select startsWith('aa', 'a');
> >1 {code}
> [https://clickhouse.com/docs/sql-reference/functions/string-functions#endswith]
> {code:java}
> select endsWith('aa', 'a');
> >1 {code}
> so we need adapt it with convert startswith to startsWith and endswith to
> endswith.
>
> I had list funcions which need to be adapted in SqlLibary(standard functions
> in String and Bit funcions， ClickHouse can refer to
> https://clickhouse.com/docs/sql-reference/functions/bit-functions):
> |*Calcite*|*ClickHouse*|
> |*String*|
> |STARTS_WITH|startsWith|
> |ENDS_WITH|endsWith|
> |*Bit*|
> |BITAND|bitAnd|
> |BITOR|bitOr|
> |BITXOR|bitXor|
> |BITNOT|bitNot|
>
> test it in ClickHouse and work well:
> bitAnd
> {code:java}
> select bitAnd(2,1);
> >0 {code}
> {code:java}
> SELECT BITAND(2,1);
> >Code: 46. DB::Exception: Received from localhost:9000. DB::Exception:
> >Function with name `BITAND` does not exist. In scope SELECT BITAND(2, 1).
> >Maybe you meant: ['bitAnd','BIT_AND']. (UNKNOWN_FUNCTION)
> SELECT BIT_AND(2,1);
> >Code: 42. DB::Exception: Received from localhost:9000. DB::Exception:
> >Aggregate function groupBitAnd requires single argument.
> >(NUMBER_OF_ARGUMENTS_DOESNT_MATCH)
> {code}
> bitOr
> {code:java}
> select bitOr(2,1);
> >3{code}
> {code:java}
> SELECT BITOR(2,1);
> >Code: 46. DB::Exception: Received from localhost:9000. DB::Exception:
> >Function with name `BITOR` does not exist. In scope SELECT BITOR(2, 1).
> >Maybe you meant: ['bitOr','bitXor']. (UNKNOWN_FUNCTION)
> SELECT BIT_OR(2,1);
> >Code: 42. DB::Exception: Received from localhost:9000. DB::Exception:
> >Aggregate function groupBitOr requires single argument.
> >(NUMBER_OF_ARGUMENTS_DOESNT_MATCH)
> {code}
> bitXor
> {code:java}
> select bitXor(2,1);
> >3{code}
> {code:java}
> SELECT BITXOR(2,1);
> >Code: 46. DB::Exception: Received from localhost:9000. DB::Exception:
> >Function with name `BITXOR` does not exist. In scope SELECT BITXOR(2). Maybe
> >you meant: ['bitXor','bitOr']. (UNKNOWN_FUNCTION)
> SELECT BIT_XOR(2,1);
> >Code: 42. DB::Exception: Received from localhost:9000. DB::Exception:
> >Aggregate function groupBitXor requires single argument.
> >(NUMBER_OF_ARGUMENTS_DOESNT_MATCH)
> {code}
> bitNot
> {code:java}
> select bitXor(2);
> >3{code}
> {code:java}
> SELECT BITNOT(2);
> >Code: 46. DB::Exception: Received from localhost:9000. DB::Exception:
> >Function with name `BITNOT` does not exist. In scope SELECT BITNOT(2). Maybe
> >you meant: ['bitNot','bitXor']. (UNKNOWN_FUNCTION)
> SELECT BIT_NOT(2);
> >Code: 46. DB::Exception: Received from localhost:9000. DB::Exception:
> >Function with name `BIT_NOT` does not exist. In scope SELECT BIT_NOT(2).
> >Maybe you meant: ['bitNot','BIT_OR']. (UNKNOWN_FUNCTION)
> {code}
> so we can unparse string and bit functions correctly.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
