[
https://issues.apache.org/jira/browse/CALCITE-7309?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18042183#comment-18042183
]
Zhen Chen commented on CALCITE-7309:
------------------------------------
This sql works well in ClickHouse.
{code:java}
SELECT POSITION('a' IN 'abca');{code}
ClickHouse simply doesn't support the FROM clause, and the FROM clause isn't
supported in PostgreSQL either.
I believe that for ClickHouse, queries without FROM clauses don't require
conversion, while those containing FROM clauses should undergo corresponding
transformations. I've tested SQLite and Oracle, and their behaviors appear to
be different as well.
> Position is unparsed incorrectly for ClickHouseSqlDialect
> ---------------------------------------------------------
>
> Key: CALCITE-7309
> URL: https://issues.apache.org/jira/browse/CALCITE-7309
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.41.0
> Reporter: Yu Xu
> Assignee: Yu Xu
> Priority: Minor
> Labels: pull-request-available
> Fix For: 1.42.0
>
>
> The SQL：
> {code:java}
> SELECT POSITION('a' IN 'abca' FROM 1) {code}
> Generate Hive SQL should be：
> {code:java}
> SELECT POSITION('abca', 'a', 1) {code}
> result is ok:
> {code:java}
> 1
> {code}
> But is:
> {code:java}
> SELECT POSITION('a' IN 'abca' FROM 1) {code}
>
> execute would error out in ClickHouse:
> {code:java}
> Code: 62. DB::Exception: Syntax error: failed at position 30 ('FROM') (line
> 1, col 30): FROM 1)-- SELECT POSITION('abca', 'a', 2). Expected one of:
> DoubleColon, LIKE, GLOBAL NOT IN, end of query, AS, DIV, IS, OR,
> QuestionMark, BETWEEN, NOT LIKE, MOD, AND, Comma, alias, IN, ILIKE, NOT
> ILIKE, NOT, token, NOT IN, GLOBAL IN {code}
> we can refer ClickHouse document about Position:
> [https://clickhouse.com/docs/sql-reference/functions/string-search-functions#position]
>
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
