[
https://issues.apache.org/jira/browse/CALCITE-7107?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18009967#comment-18009967
]
Zhen Chen commented on CALCITE-7107:
------------------------------------
[~zargarf] I use these configs, such as
{code:java}
SqlParser.config()
.withUnquotedCasing(Casing.UNCHANGED)
.withQuotedCasing(Casing.UNCHANGED)
{code}
I can transform the sql
{code:java}
SELECT e.empid,
e.NAME,
e.salary,
d.name as DEPT_name,
Array['a', 'b']
FROM emps e
JOIN depts d
ON e.deptno = d.deptno
WHERE d.deptno = 10
ORDER BY e.salary DESC
{code}
to
{code:java}
SELECT `e`.`empid`, `e`.`NAME`, `e`.`salary`, `d`.`name` AS `DEPT_name`,
array('a', 'b')
FROM `emps` AS `e`
INNER JOIN `depts` AS `d` ON `e`.`deptno` = `d`.`deptno`
WHERE `d`.`deptno` = 10
ORDER BY `e`.`salary` DESC
{code}
some code like this
{code:java}
SqlNode parse = planner.parse(sql);
SqlDialect clickhouseDialect =
SqlDialect.DatabaseProduct.CLICKHOUSE.getDialect();
String clickhouseSql = parse.toSqlString(clickhouseDialect).getSql();
{code}
This sql can work in clickhouse. Do you want to parse a sql through calcite and
then convert it into clickhouse sql? Or do you have other concerns?
> Parsing clickhouse query that contains an array
> -----------------------------------------------
>
> Key: CALCITE-7107
> URL: https://issues.apache.org/jira/browse/CALCITE-7107
> Project: Calcite
> Issue Type: Bug
> Reporter: Faisal
> Priority: Major
>
> Hi
> I'm using the calcite-babel 1.40.0 lib and am doing some manipulation on sql
> for Clickhouse. Specifically we're using array functions in the sql - e.g.
> {code:java}
> "Select * from someTable where hasAll(anArrayColumn, ['value1', 'value2']"
> {code}
> When parsing this, calcite throws an exception relating to the "[" character.
> Clickhouse itself supports using this array construct ['value1', 'value2'] or
> using array('value1, 'value2') where "array" is case sensitive in clickhouse.
> If I send in:
> {code:java}
> "Select * from someTable where hasAll(anArrayColumn, array('value1,
> 'value2')" {code}
> for calcite parsing, then calcite detects "array" as a keyword and uppercases
> it by default which does not work in Clickhouse, resulting in
> {code:java}
> "SELECT * FROM someTable WHERE hasAll(anArrayColumn, ARRAY('value1',
> 'value2'))"{code}
> I understand that I can set calcite to lower case keywords by default, but
> that may have knock on consequences
> I am using:
> {code:java}
> final var conf= SqlParser.config().withCaseSensitive(true)
> .withUnquotedCasing(Casing.UNCHANGED).withQuotedCasing(Casing.UNCHANGED)
> .withQuoting(Quoting.DOUBLE_QUOTE).withParserFactory(SqlBabelParserImpl.FACTORY);
> final var sqlParser = SqlParser.create(sql, conf);
> final var sqlNode = sqlParser.parseStmt();{code}
>
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
