[
https://issues.apache.org/jira/browse/CALCITE-7316?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18043745#comment-18043745
]
Zhen Chen commented on CALCITE-7316:
------------------------------------
Will this conversion be better?
{code:java}
-- original sql:
SELECT INSTR('ABCABC', 'C', 4);
-- change to following:
SELECT
CASE
WHEN INSTR(SUBSTR('ABCABC', 4), 'C') > 0
THEN INSTR(SUBSTR('ABCABC', 4), 'C') + 3
ELSE 0
END AS position; {code}
> The POSITION function in SQLite is missing the FROM clause
> ----------------------------------------------------------
>
> Key: CALCITE-7316
> URL: https://issues.apache.org/jira/browse/CALCITE-7316
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.41.0
> Reporter: Zhen Chen
> Assignee: Yu Xu
> Priority: Minor
>
> The original test:
> {code:java}
> @Test void testPositionFunctionForSqlite() {
> final String query = "select position('A' IN 'ABC') from \"product\"";
> final String expected = "SELECT INSTR('ABC', 'A')\n"
> + "FROM \"foodmart\".\"product\"";
> sql(query).withSQLite().ok(expected);
> } {code}
> But I change position('A' IN 'ABC') to position('A' IN 'ABC' FROM 2), this
> case alse works well.
> {code:java}
> @Test void testPositionFunctionForSqlite2() {
> final String query = "select position('A' IN 'ABC' FROM 2) from
> \"product\"";
> final String expected = "SELECT INSTR('ABC', 'A')\n"
> + "FROM \"foodmart\".\"product\"";
> sql(query).withSQLite().ok(expected);
> } {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
