[jira] [Commented] (CALCITE-6521) CROSS JOIN UNNEST The results do not match expectations.
[ https://issues.apache.org/jira/browse/CALCITE-6521?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17874087#comment-17874087 ] EveyWu commented on CALCITE-6521: -- [~julianhyde]Thank you for your suggestion, I'll ensure to provide clearer descriptions in future Jira report. I'm also interested in taking on this Jira task. > CROSS JOIN UNNEST The results do not match expectations. > > > Key: CALCITE-6521 > URL: https://issues.apache.org/jira/browse/CALCITE-6521 > Project: Calcite > Issue Type: Bug >Reporter: EveyWu >Priority: Minor > Attachments: image-2024-08-08-22-03-13-643.png > > > {code:java} > SELECT n, a > FROM ( > VALUES > (ARRAY[2, 5], ARRAY['dog', 'cat', 'bird']), > (ARRAY[7, 8, 9], ARRAY['cow', 'pig']) > ) AS x (numbers, animals) > CROSS JOIN UNNEST(numbers, animals) AS t (n, a); > {code} > Postgres result: > |2|dog | > |5|cat | > |null|bird| > |7|cow | > |8|pig | > |9|null | > > Calcite result: > |2|dog | > |2|cat | > |2|bird| > |5|dog | > |5|cat | > |5|bird| > |7|cow | > |7|pig | > |8|cow | > |8|pig | > |9|cow | > |9|pig | > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6242) The "exists" library function throws a "param not found" error when a column is used in lambda evaluation logic.
[ https://issues.apache.org/jira/browse/CALCITE-6242?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17872181#comment-17872181 ] EveyWu commented on CALCITE-6242: -- [~hongyuguo] Hi~ if you don't mind, I would like to work on it. Thanks! > The "exists" library function throws a "param not found" error when a column > is used in lambda evaluation logic. > > > Key: CALCITE-6242 > URL: https://issues.apache.org/jira/browse/CALCITE-6242 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Hanumath Rao Maduri >Priority: Major > > The following query is throwing a "param not found" in calcite whereas the > same query works fine in apache spark. > {code:java} > select * > from (select array(1, 2, 3) as arr) as t1 inner join > (select 1 as v) as t2 on "EXISTS"(arr, x -> x = t2.v); {code} > The following error reported for the above query > {code:java} > > java.sql.SQLException: Error while executing SQL "select * > > from (select array(1, 2, 3) as arr) as t1 inner join > > (select 1 as v) as t2 on "EXISTS"(arr, x -> x = t2.v)": From line 3, > > column 54 to line 3, column 57: Param 'T2.V' not found in lambda expression > > '`X` -> `X` = `T2`.`V`' {code} > Steps to repro: > 1. Place the query in lambda.iq > 2. Run CoreQuidemTest. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6521) CROSS JOIN UNNEST The results do not match expectations.
[ https://issues.apache.org/jira/browse/CALCITE-6521?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17872166#comment-17872166 ] EveyWu edited comment on CALCITE-6521 at 8/9/24 1:30 AM: -- [~julianhyde] Thanks for reply, I found that the above sql execution in calcite is inconsistent with the results of the engine PostgreSQL or Presto, but I am not sure whether it is the design of calcite. Calcite's Cross Join Unnest is more biased towards cross join?The Calcite execution result is consistent with the following sql in PostgreSQL. {code:java} select * from ( SELECT t1.*, t2.* FROM (SELECT unnest(ARRAY[2, 5]) AS num) t1 CROSS JOIN (SELECT unnest(ARRAY['dog', 'cat', 'bird']) AS animal) t2 UNION SELECT t3.*, t4.* FROM (SELECT unnest(ARRAY[7, 8, 9]) AS num) t3 CROSS JOIN (SELECT unnest(ARRAY['cow', 'pig']) AS animal) t4 ); {code} was (Author: eveywu): [~julianhyde] Thanks for reply, I found that the above sql execution in calcite is inconsistent with the results of the engine PostgreSQL or Presto, but I am not sure whether it is the design of calcite. Calcite's Cross Join Unnest is more biased towards cross join?The calcite execution result is consistent with the following sql in PostgreSQL. {code:java} select * from ( SELECT t1.*, t2.* FROM (SELECT unnest(ARRAY[2, 5]) AS num) t1 CROSS JOIN (SELECT unnest(ARRAY['dog', 'cat', 'bird']) AS animal) t2 UNION SELECT t3.*, t4.* FROM (SELECT unnest(ARRAY[7, 8, 9]) AS num) t3 CROSS JOIN (SELECT unnest(ARRAY['cow', 'pig']) AS animal) t4 ); {code} The Result in PostgreSQL: > CROSS JOIN UNNEST The results do not match expectations. > > > Key: CALCITE-6521 > URL: https://issues.apache.org/jira/browse/CALCITE-6521 > Project: Calcite > Issue Type: Bug >Reporter: EveyWu >Priority: Minor > Attachments: image-2024-08-08-22-03-13-643.png > > > {code:java} > SELECT n, a > FROM ( > VALUES > (ARRAY[2, 5], ARRAY['dog', 'cat', 'bird']), > (ARRAY[7, 8, 9], ARRAY['cow', 'pig']) > ) AS x (numbers, animals) > CROSS JOIN UNNEST(numbers, animals) AS t (n, a); > {code} > Postgres result: > |2|dog | > |5|cat | > |null|bird| > |7|cow | > |8|pig | > |9|null | > > Calcite result: > |2|dog | > |2|cat | > |2|bird| > |5|dog | > |5|cat | > |5|bird| > |7|cow | > |7|pig | > |8|cow | > |8|pig | > |9|cow | > |9|pig | > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6521) CROSS JOIN UNNEST The results do not match expectations.
[ https://issues.apache.org/jira/browse/CALCITE-6521?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17872166#comment-17872166 ] EveyWu commented on CALCITE-6521: -- [~julianhyde] Thanks for reply, I found that the above sql execution in calcite is inconsistent with the results of the engine PostgreSQL or Presto, but I am not sure whether it is the design of calcite. Calcite's Cross Join Unnest is more biased towards cross join?The calcite execution result is consistent with the following sql in PostgreSQL. {code:java} select * from ( SELECT t1.*, t2.* FROM (SELECT unnest(ARRAY[2, 5]) AS num) t1 CROSS JOIN (SELECT unnest(ARRAY['dog', 'cat', 'bird']) AS animal) t2 UNION SELECT t3.*, t4.* FROM (SELECT unnest(ARRAY[7, 8, 9]) AS num) t3 CROSS JOIN (SELECT unnest(ARRAY['cow', 'pig']) AS animal) t4 ); {code} The Result in PostgreSQL: > CROSS JOIN UNNEST The results do not match expectations. > > > Key: CALCITE-6521 > URL: https://issues.apache.org/jira/browse/CALCITE-6521 > Project: Calcite > Issue Type: Bug >Reporter: EveyWu >Priority: Minor > Attachments: image-2024-08-08-22-03-13-643.png > > > {code:java} > SELECT n, a > FROM ( > VALUES > (ARRAY[2, 5], ARRAY['dog', 'cat', 'bird']), > (ARRAY[7, 8, 9], ARRAY['cow', 'pig']) > ) AS x (numbers, animals) > CROSS JOIN UNNEST(numbers, animals) AS t (n, a); > {code} > Postgres result: > |2|dog | > |5|cat | > |null|bird| > |7|cow | > |8|pig | > |9|null | > > Calcite result: > |2|dog | > |2|cat | > |2|bird| > |5|dog | > |5|cat | > |5|bird| > |7|cow | > |7|pig | > |8|cow | > |8|pig | > |9|cow | > |9|pig | > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6521) CROSS JOIN UNNEST The results do not match expectations.
[ https://issues.apache.org/jira/browse/CALCITE-6521?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6521: Description: {code:java} SELECT n, a FROM ( VALUES (ARRAY[2, 5], ARRAY['dog', 'cat', 'bird']), (ARRAY[7, 8, 9], ARRAY['cow', 'pig']) ) AS x (numbers, animals) CROSS JOIN UNNEST(numbers, animals) AS t (n, a); {code} Postgres result: |2|dog | |5|cat | |null|bird| |7|cow | |8|pig | |9|null | Calcite result: |2|dog | |2|cat | |2|bird| |5|dog | |5|cat | |5|bird| |7|cow | |7|pig | |8|cow | |8|pig | |9|cow | |9|pig | was: {code:java} SELECT n, a FROM ( VALUES (ARRAY[2, 5], ARRAY['dog', 'cat', 'bird']), (ARRAY[7, 8, 9], ARRAY['cow', 'pig']) ) AS x (numbers, animals) CROSS JOIN UNNEST(numbers, animals) AS t (n, a); {code} Postgres result: |2|dog | |5|cat | |null|bird| |7|cow | |8|pig | |9|null | calcite result: |2|dog | |2|cat | |2|bird| |5|dog | |5|cat | |5|bird| |7|cow | |7|pig | |8|cow | |8|pig | |9|cow | |9|pig | > CROSS JOIN UNNEST The results do not match expectations. > > > Key: CALCITE-6521 > URL: https://issues.apache.org/jira/browse/CALCITE-6521 > Project: Calcite > Issue Type: Bug >Reporter: EveyWu >Priority: Minor > Attachments: image-2024-08-08-22-03-13-643.png > > > {code:java} > SELECT n, a > FROM ( > VALUES > (ARRAY[2, 5], ARRAY['dog', 'cat', 'bird']), > (ARRAY[7, 8, 9], ARRAY['cow', 'pig']) > ) AS x (numbers, animals) > CROSS JOIN UNNEST(numbers, animals) AS t (n, a); > {code} > Postgres result: > |2|dog | > |5|cat | > |null|bird| > |7|cow | > |8|pig | > |9|null | > > Calcite result: > |2|dog | > |2|cat | > |2|bird| > |5|dog | > |5|cat | > |5|bird| > |7|cow | > |7|pig | > |8|cow | > |8|pig | > |9|cow | > |9|pig | > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6521) CROSS JOIN UNNEST The results do not match expectations.
[ https://issues.apache.org/jira/browse/CALCITE-6521?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6521: Description: {code:java} SELECT n, a FROM ( VALUES (ARRAY[2, 5], ARRAY['dog', 'cat', 'bird']), (ARRAY[7, 8, 9], ARRAY['cow', 'pig']) ) AS x (numbers, animals) CROSS JOIN UNNEST(numbers, animals) AS t (n, a); {code} Postgres result: |2|dog | |5|cat | |null|bird| |7|cow | |8|pig | |9|null | calcite result: |2|dog | |2|cat | |2|bird| |5|dog | |5|cat | |5|bird| |7|cow | |7|pig | |8|cow | |8|pig | |9|cow | |9|pig | was: {code:java} SELECT n, a FROM ( VALUES (ARRAY[2, 5], ARRAY['dog', 'cat', 'bird']), (ARRAY[7, 8, 9], ARRAY['cow', 'pig']) ) AS x (numbers, animals) CROSS JOIN UNNEST(numbers, animals) AS t (n, a); {code} Postgres result: !image-2024-08-08-22-03-13-643.png|width=171,height=157! calcite result: |2|dog | |2|cat | |2|bird| |5|dog | |5|cat | |5|bird| |7|cow | |7|pig | |8|cow | |8|pig | |9|cow | |9|pig | > CROSS JOIN UNNEST The results do not match expectations. > > > Key: CALCITE-6521 > URL: https://issues.apache.org/jira/browse/CALCITE-6521 > Project: Calcite > Issue Type: Bug >Reporter: EveyWu >Priority: Minor > Attachments: image-2024-08-08-22-03-13-643.png > > > {code:java} > SELECT n, a > FROM ( > VALUES > (ARRAY[2, 5], ARRAY['dog', 'cat', 'bird']), > (ARRAY[7, 8, 9], ARRAY['cow', 'pig']) > ) AS x (numbers, animals) > CROSS JOIN UNNEST(numbers, animals) AS t (n, a); > {code} > Postgres result: > |2|dog | > |5|cat | > |null|bird| > |7|cow | > |8|pig | > |9|null | > > calcite result: > |2|dog | > |2|cat | > |2|bird| > |5|dog | > |5|cat | > |5|bird| > |7|cow | > |7|pig | > |8|cow | > |8|pig | > |9|cow | > |9|pig | > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6463) alias generation issue about correlated subquery handling
[ https://issues.apache.org/jira/browse/CALCITE-6463?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6463: Issue Type: Improvement (was: Bug) > alias generation issue about correlated subquery handling > - > > Key: CALCITE-6463 > URL: https://issues.apache.org/jira/browse/CALCITE-6463 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.37.0 >Reporter: shiji.mei >Priority: Minor > Fix For: 1.38.0 > > > [dialect|#] is postgresql > original query: > SELECT MAX(distinct l_orderkey) FROM lineitem where exists( SELECT > MAX(c_custkey) FROM customer where c_custkey = l_orderkey GROUP BY c_custkey); > Rewritten query: > SELECT MAX("l_orderkey") > FROM "lineitem" > WHERE EXISTS (SELECT "c_custkey", MAX("c_custkey") > FROM "customer" > WHERE "c_custkey" = "lineitem12"."l_orderkey" > GROUP BY "c_custkey") > lineitem becomes lineitem12,and it will become a different number every time > it runs。 > thank you ! -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6463) alias generation issue about correlated subquery handling
[ https://issues.apache.org/jira/browse/CALCITE-6463?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6463: Priority: Minor (was: Blocker) > alias generation issue about correlated subquery handling > - > > Key: CALCITE-6463 > URL: https://issues.apache.org/jira/browse/CALCITE-6463 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.37.0 >Reporter: shiji.mei >Priority: Minor > Fix For: 1.38.0 > > > [dialect|#] is postgresql > original query: > SELECT MAX(distinct l_orderkey) FROM lineitem where exists( SELECT > MAX(c_custkey) FROM customer where c_custkey = l_orderkey GROUP BY c_custkey); > Rewritten query: > SELECT MAX("l_orderkey") > FROM "lineitem" > WHERE EXISTS (SELECT "c_custkey", MAX("c_custkey") > FROM "customer" > WHERE "c_custkey" = "lineitem12"."l_orderkey" > GROUP BY "c_custkey") > lineitem becomes lineitem12,and it will become a different number every time > it runs。 > thank you ! -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6463) alias generation issue about correlated subquery handling
[ https://issues.apache.org/jira/browse/CALCITE-6463?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6463: Priority: Blocker (was: Minor) > alias generation issue about correlated subquery handling > - > > Key: CALCITE-6463 > URL: https://issues.apache.org/jira/browse/CALCITE-6463 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.37.0 >Reporter: shiji.mei >Priority: Blocker > Fix For: 1.38.0 > > > [dialect|#] is postgresql > original query: > SELECT MAX(distinct l_orderkey) FROM lineitem where exists( SELECT > MAX(c_custkey) FROM customer where c_custkey = l_orderkey GROUP BY c_custkey); > Rewritten query: > SELECT MAX("l_orderkey") > FROM "lineitem" > WHERE EXISTS (SELECT "c_custkey", MAX("c_custkey") > FROM "customer" > WHERE "c_custkey" = "lineitem12"."l_orderkey" > GROUP BY "c_custkey") > lineitem becomes lineitem12,and it will become a different number every time > it runs。 > thank you ! -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6521) CROSS JOIN UNNEST The results do not match expectations.
EveyWu created CALCITE-6521: Summary: CROSS JOIN UNNEST The results do not match expectations. Key: CALCITE-6521 URL: https://issues.apache.org/jira/browse/CALCITE-6521 Project: Calcite Issue Type: Bug Reporter: EveyWu Attachments: image-2024-08-08-22-03-13-643.png {code:java} SELECT n, a FROM ( VALUES (ARRAY[2, 5], ARRAY['dog', 'cat', 'bird']), (ARRAY[7, 8, 9], ARRAY['cow', 'pig']) ) AS x (numbers, animals) CROSS JOIN UNNEST(numbers, animals) AS t (n, a); {code} Postgres result: !image-2024-08-08-22-03-13-643.png|width=171,height=157! calcite result: +---+--+ | N | A | +---+--+ | 2 | dog | | 2 | cat | | 2 | bird | | 5 | dog | | 5 | cat | | 5 | bird | | 7 | cow | | 7 | pig | | 8 | cow | | 8 | pig | | 9 | cow | | 9 | pig | +---+--+ -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6521) CROSS JOIN UNNEST The results do not match expectations.
EveyWu created CALCITE-6521: Summary: CROSS JOIN UNNEST The results do not match expectations. Key: CALCITE-6521 URL: https://issues.apache.org/jira/browse/CALCITE-6521 Project: Calcite Issue Type: Bug Reporter: EveyWu Attachments: image-2024-08-08-22-03-13-643.png {code:java} SELECT n, a FROM ( VALUES (ARRAY[2, 5], ARRAY['dog', 'cat', 'bird']), (ARRAY[7, 8, 9], ARRAY['cow', 'pig']) ) AS x (numbers, animals) CROSS JOIN UNNEST(numbers, animals) AS t (n, a); {code} Postgres result: !image-2024-08-08-22-03-13-643.png|width=171,height=157! calcite result: +---+--+ | N | A | +---+--+ | 2 | dog | | 2 | cat | | 2 | bird | | 5 | dog | | 5 | cat | | 5 | bird | | 7 | cow | | 7 | pig | | 8 | cow | | 8 | pig | | 9 | cow | | 9 | pig | +---+--+ -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6521) CROSS JOIN UNNEST The results do not match expectations.
[ https://issues.apache.org/jira/browse/CALCITE-6521?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6521: Description: {code:java} SELECT n, a FROM ( VALUES (ARRAY[2, 5], ARRAY['dog', 'cat', 'bird']), (ARRAY[7, 8, 9], ARRAY['cow', 'pig']) ) AS x (numbers, animals) CROSS JOIN UNNEST(numbers, animals) AS t (n, a); {code} Postgres result: !image-2024-08-08-22-03-13-643.png|width=171,height=157! calcite result: |2|dog | |2|cat | |2|bird| |5|dog | |5|cat | |5|bird| |7|cow | |7|pig | |8|cow | |8|pig | |9|cow | |9|pig | was: {code:java} SELECT n, a FROM ( VALUES (ARRAY[2, 5], ARRAY['dog', 'cat', 'bird']), (ARRAY[7, 8, 9], ARRAY['cow', 'pig']) ) AS x (numbers, animals) CROSS JOIN UNNEST(numbers, animals) AS t (n, a); {code} Postgres result: !image-2024-08-08-22-03-13-643.png|width=171,height=157! calcite result: +---+--+ | N | A | +---+--+ | 2 | dog | | 2 | cat | | 2 | bird | | 5 | dog | | 5 | cat | | 5 | bird | | 7 | cow | | 7 | pig | | 8 | cow | | 8 | pig | | 9 | cow | | 9 | pig | +---+--+ > CROSS JOIN UNNEST The results do not match expectations. > > > Key: CALCITE-6521 > URL: https://issues.apache.org/jira/browse/CALCITE-6521 > Project: Calcite > Issue Type: Bug >Reporter: EveyWu >Priority: Minor > Attachments: image-2024-08-08-22-03-13-643.png > > > {code:java} > SELECT n, a > FROM ( > VALUES > (ARRAY[2, 5], ARRAY['dog', 'cat', 'bird']), > (ARRAY[7, 8, 9], ARRAY['cow', 'pig']) > ) AS x (numbers, animals) > CROSS JOIN UNNEST(numbers, animals) AS t (n, a); > {code} > Postgres result: > !image-2024-08-08-22-03-13-643.png|width=171,height=157! > > calcite result: > |2|dog | > |2|cat | > |2|bird| > |5|dog | > |5|cat | > |5|bird| > |7|cow | > |7|pig | > |8|cow | > |8|pig | > |9|cow | > |9|pig | > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6463) alias generation issue about correlated subquery handling
[ https://issues.apache.org/jira/browse/CALCITE-6463?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6463: Issue Type: Bug (was: Improvement) > alias generation issue about correlated subquery handling > - > > Key: CALCITE-6463 > URL: https://issues.apache.org/jira/browse/CALCITE-6463 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.37.0 >Reporter: shiji.mei >Priority: Blocker > Fix For: 1.38.0 > > > [dialect|#] is postgresql > original query: > SELECT MAX(distinct l_orderkey) FROM lineitem where exists( SELECT > MAX(c_custkey) FROM customer where c_custkey = l_orderkey GROUP BY c_custkey); > Rewritten query: > SELECT MAX("l_orderkey") > FROM "lineitem" > WHERE EXISTS (SELECT "c_custkey", MAX("c_custkey") > FROM "customer" > WHERE "c_custkey" = "lineitem12"."l_orderkey" > GROUP BY "c_custkey") > lineitem becomes lineitem12,and it will become a different number every time > it runs。 > thank you ! -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6372) Support ASOF joins
[ https://issues.apache.org/jira/browse/CALCITE-6372?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17869943#comment-17869943 ] EveyWu edited comment on CALCITE-6372 at 7/31/24 3:46 PM: --- Why consider using snowflake's syntax definition? I find that the syntax used by DuckDB or ClickHouse aligns more closely with traditional conventions and might be more readily accepted. was (Author: eveywu): Why consider using snowflake's syntax definition? I feel that the syntax of DuckDB or ClickHouse is consistent with the traditional method and is easier to accept. > Support ASOF joins > -- > > Key: CALCITE-6372 > URL: https://issues.apache.org/jira/browse/CALCITE-6372 > Project: Calcite > Issue Type: New Feature > Components: core >Affects Versions: 1.36.0 >Reporter: Mihai Budiu >Assignee: Mihai Budiu >Priority: Minor > Labels: pull-request-available > > Seems that this new kind of JOIN named AS OF is very useful for processing > time-series data. Here is some example documentation from Snowflake: > https://docs.snowflake.com/en/sql-reference/constructs/asof-join > The semantics is similar to a traditional join, but the result always > contains at most one record from the left side, with the last matching > record on the right side (where "time" is any value that can be compared for > inequality). This can be expressed in SQL, but it looks very cumbersome, > using a JOIN, a GROUP BY, and then an aggregation to keep the last value. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6372) Support ASOF joins
[ https://issues.apache.org/jira/browse/CALCITE-6372?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6372: Attachment: image-2024-07-31-23-42-31-044.png > Support ASOF joins > -- > > Key: CALCITE-6372 > URL: https://issues.apache.org/jira/browse/CALCITE-6372 > Project: Calcite > Issue Type: New Feature > Components: core >Affects Versions: 1.36.0 >Reporter: Mihai Budiu >Assignee: Mihai Budiu >Priority: Minor > Labels: pull-request-available > > Seems that this new kind of JOIN named AS OF is very useful for processing > time-series data. Here is some example documentation from Snowflake: > https://docs.snowflake.com/en/sql-reference/constructs/asof-join > The semantics is similar to a traditional join, but the result always > contains at most one record from the left side, with the last matching > record on the right side (where "time" is any value that can be compared for > inequality). This can be expressed in SQL, but it looks very cumbersome, > using a JOIN, a GROUP BY, and then an aggregation to keep the last value. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6372) Support ASOF joins
[ https://issues.apache.org/jira/browse/CALCITE-6372?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6372: Attachment: (was: image-2024-07-31-23-42-31-044.png) > Support ASOF joins > -- > > Key: CALCITE-6372 > URL: https://issues.apache.org/jira/browse/CALCITE-6372 > Project: Calcite > Issue Type: New Feature > Components: core >Affects Versions: 1.36.0 >Reporter: Mihai Budiu >Assignee: Mihai Budiu >Priority: Minor > Labels: pull-request-available > > Seems that this new kind of JOIN named AS OF is very useful for processing > time-series data. Here is some example documentation from Snowflake: > https://docs.snowflake.com/en/sql-reference/constructs/asof-join > The semantics is similar to a traditional join, but the result always > contains at most one record from the left side, with the last matching > record on the right side (where "time" is any value that can be compared for > inequality). This can be expressed in SQL, but it looks very cumbersome, > using a JOIN, a GROUP BY, and then an aggregation to keep the last value. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6372) Support ASOF joins
[ https://issues.apache.org/jira/browse/CALCITE-6372?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17869943#comment-17869943 ] EveyWu commented on CALCITE-6372: -- Why consider using snowflake's syntax definition? I feel that the syntax of DuckDB or ClickHouse is consistent with the traditional method and is easier to accept. > Support ASOF joins > -- > > Key: CALCITE-6372 > URL: https://issues.apache.org/jira/browse/CALCITE-6372 > Project: Calcite > Issue Type: New Feature > Components: core >Affects Versions: 1.36.0 >Reporter: Mihai Budiu >Assignee: Mihai Budiu >Priority: Minor > Labels: pull-request-available > > Seems that this new kind of JOIN named AS OF is very useful for processing > time-series data. Here is some example documentation from Snowflake: > https://docs.snowflake.com/en/sql-reference/constructs/asof-join > The semantics is similar to a traditional join, but the result always > contains at most one record from the left side, with the last matching > record on the right side (where "time" is any value that can be compared for > inequality). This can be expressed in SQL, but it looks very cumbersome, > using a JOIN, a GROUP BY, and then an aggregation to keep the last value. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6450) Postgres CONCAT_WS function throws exception when parameter type is (, )
[ https://issues.apache.org/jira/browse/CALCITE-6450?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17863238#comment-17863238 ] EveyWu commented on CALCITE-6450: -- [~nobigo] If you don't mind, I will take over this issue. Postgres supports arrays differently from spark arrays. Postgres is CONCAT_WS(separator, arg1, ...), arg1 supports any type. > Postgres CONCAT_WS function throws exception when parameter type is > (, ) > > > Key: CALCITE-6450 > URL: https://issues.apache.org/jira/browse/CALCITE-6450 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.37.0 >Reporter: xiong duan >Priority: Major > Attachments: image-2024-06-29-14-57-56-513.png > > > The SQL can run success in Postgres: > {code:java} > select concat_ws(',',ARRAY[1, 1, 1, 1]); {code} > But in Calcite, It will throw exception: > {code:java} > @Test void testConcatFunction() { > final String sql = "select concat_ws(',',ARRAY[1, 1, 1, 1]) > as c"; > fixture() > .withFactory(c -> > c.withOperatorTable(t -> > SqlValidatorTest.operatorTableFor(SqlLibrary.POSTGRESQL))) > .withCatalogReader(MockCatalogReaderExtended::create) > .withSql(sql) > .ok(); > }{code} > {code:java} > From line 1, column 8 to line 1, column 55: Cannot apply 'CONCAT_WS' to > arguments of type 'CONCAT_WS(, )'. Supported form(s): > 'CONCAT_WS()'{code} > This issue find in comment > [CALCITE-6446|https://github.com/apache/calcite/pull/3831] . -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6447) Extract common expressions for disjunctions in Join
[ https://issues.apache.org/jira/browse/CALCITE-6447?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6447: Attachment: (was: image-2024-06-27-15-05-55-779.png) > Extract common expressions for disjunctions in Join > --- > > Key: CALCITE-6447 > URL: https://issues.apache.org/jira/browse/CALCITE-6447 > Project: Calcite > Issue Type: New Feature > Components: core >Affects Versions: 1.37.0 >Reporter: ruanhui >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > Attachments: image-2024-06-25-16-26-15-058.png > > > For SQL: > {code:java} > select * > from tbl_a > join tbl_b on tbl_a.id = tbl_b.id > where (tbl_a.x > 100 and tbl_b.y < 10) >or (tbl_a.x > 100 and tbl_b.z > 20){code} > we can rewrite it to > {code:java} > select * > from tbl_a > join tbl_b on tbl_a.id = tbl_b.id > where tbl_a.x > 100 > and (tbl_b.y < 10 or tbl_b.z > 20){code} > And in this way *tbl_a.x > 100* can be pushed down and it is likely that this > will help reduce the amount of data involved in the join. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6447) Extract common expressions for disjunctions in Join
[ https://issues.apache.org/jira/browse/CALCITE-6447?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17860357#comment-17860357 ] EveyWu edited comment on CALCITE-6447 at 6/27/24 7:08 AM: --- [~frostruan] for test `testWhereAnyCorrelatedInSelect`. I think this is expected too, this PR optimizes conditions. {code:java} (EMP.EMPNO > DEPT.m AND NOT (DEPT.trueLiteral IS NULL OR DEPT.c = 0)) OR (EMP.EMPNO > DEPT.m AND NOT (DEPT.trueLiteral IS NULL OR DEPT.c = 0) AND NOT (EMP.EMPNO > DEPT.m) AND NOT (DEPT.c > DEPT.d)) {code} after optimize: {code:java} EMP.EMPNO > DEPT.m AND NOT (DEPT.trueLiteral IS NULL OR DEPT.c = 0) {code} as DEPT.trueLiteral = `true` is always NOT NULL, can be simplified to {code:java} EMP.EMPNO > DEPT.m AND NOT (DEPT.c = 0){code} was (Author: eveywu): [~frostruan] for test `testWhereAnyCorrelatedInSelect`. I think this is expected too, this PR optimizes conditions. {code:java} (EMP.EMPNO > DEPT.m AND NOT (DEPT.trueLiteral IS NULL OR DEPT.c = 0)) OR (EMP.EMPNO > DEPT.m AND NOT (DEPT.trueLiteral IS NULL OR DEPT.c = 0) AND NOT (EMP.EMPNO > DEPT.m) AND NOT (DEPT.c > DEPT.d)) {code} after optimize: {code:java} EMP.EMPNO > DEPT.m AND NOT (DEPT.trueLiteral IS NULL OR DEPT.c = 0) {code} > Extract common expressions for disjunctions in Join > --- > > Key: CALCITE-6447 > URL: https://issues.apache.org/jira/browse/CALCITE-6447 > Project: Calcite > Issue Type: New Feature > Components: core >Affects Versions: 1.37.0 >Reporter: ruanhui >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > Attachments: image-2024-06-25-16-26-15-058.png > > > For SQL: > {code:java} > select * > from tbl_a > join tbl_b on tbl_a.id = tbl_b.id > where (tbl_a.x > 100 and tbl_b.y < 10) >or (tbl_a.x > 100 and tbl_b.z > 20){code} > we can rewrite it to > {code:java} > select * > from tbl_a > join tbl_b on tbl_a.id = tbl_b.id > where tbl_a.x > 100 > and (tbl_b.y < 10 or tbl_b.z > 20){code} > And in this way *tbl_a.x > 100* can be pushed down and it is likely that this > will help reduce the amount of data involved in the join. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6447) Extract common expressions for disjunctions in Join
[ https://issues.apache.org/jira/browse/CALCITE-6447?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17860357#comment-17860357 ] EveyWu edited comment on CALCITE-6447 at 6/27/24 7:02 AM: --- [~frostruan] for test `testWhereAnyCorrelatedInSelect`. I think this is expected too, this PR optimizes conditions. {code:java} (EMP.EMPNO > DEPT.m AND NOT (DEPT.trueLiteral IS NULL OR DEPT.c = 0)) OR (EMP.EMPNO > DEPT.m AND NOT (DEPT.trueLiteral IS NULL OR DEPT.c = 0) AND NOT (EMP.EMPNO > DEPT.m) AND NOT (DEPT.c > DEPT.d)) {code} after optimize: {code:java} EMP.EMPNO > DEPT.m AND NOT (DEPT.trueLiteral IS NULL OR DEPT.c = 0) {code} was (Author: eveywu): [~frostruan] for test `testWhereAnyCorrelatedInSelect`. I think this is expected too, this PR optimizes conditions. ``` (EMP.EMPNO > DEPT.m AND NOT (DEPT.trueLiteral IS NULL OR DEPT.c = 0)) OR (EMP.EMPNO > DEPT.m AND NOT (DEPT.trueLiteral IS NULL OR DEPT.c = 0) AND NOT (EMP.EMPNO > DEPT.m) AND NOT (DEPT.c > DEPT.d)) ``` after optimize ``` EMP.EMPNO > DEPT.m AND NOT (DEPT.trueLiteral IS NULL OR DEPT.c = 0) ``` > Extract common expressions for disjunctions in Join > --- > > Key: CALCITE-6447 > URL: https://issues.apache.org/jira/browse/CALCITE-6447 > Project: Calcite > Issue Type: New Feature > Components: core >Affects Versions: 1.37.0 >Reporter: ruanhui >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > Attachments: image-2024-06-25-16-26-15-058.png > > > For SQL: > {code:java} > select * > from tbl_a > join tbl_b on tbl_a.id = tbl_b.id > where (tbl_a.x > 100 and tbl_b.y < 10) >or (tbl_a.x > 100 and tbl_b.z > 20){code} > we can rewrite it to > {code:java} > select * > from tbl_a > join tbl_b on tbl_a.id = tbl_b.id > where tbl_a.x > 100 > and (tbl_b.y < 10 or tbl_b.z > 20){code} > And in this way *tbl_a.x > 100* can be pushed down and it is likely that this > will help reduce the amount of data involved in the join. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6447) Extract common expressions for disjunctions in Join
[ https://issues.apache.org/jira/browse/CALCITE-6447?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17860357#comment-17860357 ] EveyWu edited comment on CALCITE-6447 at 6/27/24 7:01 AM: --- [~frostruan] for test `testWhereAnyCorrelatedInSelect`. I think this is expected too, this PR optimizes conditions. ``` (EMP.EMPNO > DEPT.m AND NOT (DEPT.trueLiteral IS NULL OR DEPT.c = 0)) OR (EMP.EMPNO > DEPT.m AND NOT (DEPT.trueLiteral IS NULL OR DEPT.c = 0) AND NOT (EMP.EMPNO > DEPT.m) AND NOT (DEPT.c > DEPT.d)) ``` after optimize ``` EMP.EMPNO > DEPT.m AND NOT (DEPT.trueLiteral IS NULL OR DEPT.c = 0) ``` was (Author: eveywu): [~frostruan] I think this is expected too, this PR optimizes conditions. > Extract common expressions for disjunctions in Join > --- > > Key: CALCITE-6447 > URL: https://issues.apache.org/jira/browse/CALCITE-6447 > Project: Calcite > Issue Type: New Feature > Components: core >Affects Versions: 1.37.0 >Reporter: ruanhui >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > Attachments: image-2024-06-25-16-26-15-058.png > > > For SQL: > {code:java} > select * > from tbl_a > join tbl_b on tbl_a.id = tbl_b.id > where (tbl_a.x > 100 and tbl_b.y < 10) >or (tbl_a.x > 100 and tbl_b.z > 20){code} > we can rewrite it to > {code:java} > select * > from tbl_a > join tbl_b on tbl_a.id = tbl_b.id > where tbl_a.x > 100 > and (tbl_b.y < 10 or tbl_b.z > 20){code} > And in this way *tbl_a.x > 100* can be pushed down and it is likely that this > will help reduce the amount of data involved in the join. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6447) Extract common expressions for disjunctions in Join
[ https://issues.apache.org/jira/browse/CALCITE-6447?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17860357#comment-17860357 ] EveyWu edited comment on CALCITE-6447 at 6/27/24 7:01 AM: --- [~frostruan] for test `testWhereAnyCorrelatedInSelect`. I think this is expected too, this PR optimizes conditions. ``` (EMP.EMPNO > DEPT.m AND NOT (DEPT.trueLiteral IS NULL OR DEPT.c = 0)) OR (EMP.EMPNO > DEPT.m AND NOT (DEPT.trueLiteral IS NULL OR DEPT.c = 0) AND NOT (EMP.EMPNO > DEPT.m) AND NOT (DEPT.c > DEPT.d)) ``` after optimize ``` EMP.EMPNO > DEPT.m AND NOT (DEPT.trueLiteral IS NULL OR DEPT.c = 0) ``` was (Author: eveywu): [~frostruan] for test `testWhereAnyCorrelatedInSelect`. I think this is expected too, this PR optimizes conditions. ``` (EMP.EMPNO > DEPT.m AND NOT (DEPT.trueLiteral IS NULL OR DEPT.c = 0)) OR (EMP.EMPNO > DEPT.m AND NOT (DEPT.trueLiteral IS NULL OR DEPT.c = 0) AND NOT (EMP.EMPNO > DEPT.m) AND NOT (DEPT.c > DEPT.d)) ``` after optimize ``` EMP.EMPNO > DEPT.m AND NOT (DEPT.trueLiteral IS NULL OR DEPT.c = 0) ``` > Extract common expressions for disjunctions in Join > --- > > Key: CALCITE-6447 > URL: https://issues.apache.org/jira/browse/CALCITE-6447 > Project: Calcite > Issue Type: New Feature > Components: core >Affects Versions: 1.37.0 >Reporter: ruanhui >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > Attachments: image-2024-06-25-16-26-15-058.png > > > For SQL: > {code:java} > select * > from tbl_a > join tbl_b on tbl_a.id = tbl_b.id > where (tbl_a.x > 100 and tbl_b.y < 10) >or (tbl_a.x > 100 and tbl_b.z > 20){code} > we can rewrite it to > {code:java} > select * > from tbl_a > join tbl_b on tbl_a.id = tbl_b.id > where tbl_a.x > 100 > and (tbl_b.y < 10 or tbl_b.z > 20){code} > And in this way *tbl_a.x > 100* can be pushed down and it is likely that this > will help reduce the amount of data involved in the join. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6447) Extract common expressions for disjunctions in Join
[ https://issues.apache.org/jira/browse/CALCITE-6447?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17860357#comment-17860357 ] EveyWu commented on CALCITE-6447: -- [~frostruan] I think this is expected too, this PR optimizes conditions. > Extract common expressions for disjunctions in Join > --- > > Key: CALCITE-6447 > URL: https://issues.apache.org/jira/browse/CALCITE-6447 > Project: Calcite > Issue Type: New Feature > Components: core >Affects Versions: 1.37.0 >Reporter: ruanhui >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > Attachments: image-2024-06-25-16-26-15-058.png > > > For SQL: > {code:java} > select * > from tbl_a > join tbl_b on tbl_a.id = tbl_b.id > where (tbl_a.x > 100 and tbl_b.y < 10) >or (tbl_a.x > 100 and tbl_b.z > 20){code} > we can rewrite it to > {code:java} > select * > from tbl_a > join tbl_b on tbl_a.id = tbl_b.id > where tbl_a.x > 100 > and (tbl_b.y < 10 or tbl_b.z > 20){code} > And in this way *tbl_a.x > 100* can be pushed down and it is likely that this > will help reduce the amount of data involved in the join. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6446) Add CONCAT_WS function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6446?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6446: Description: Add Spark functions that have been implemented but have different OperandTypes/Returns. Add Function [CONCAT_WS|https://spark.apache.org/docs/latest/api/sql/index.html#concat_ws] The concat argument type supports not only the string type but also the array type. was: Add Spark functions that have been implemented but have different OperandTypes/Returns. Add Function The parameter type supports not only the string type but also the array type. > Add CONCAT_WS function (enabled in Spark library) > - > > Key: CALCITE-6446 > URL: https://issues.apache.org/jira/browse/CALCITE-6446 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [CONCAT_WS|https://spark.apache.org/docs/latest/api/sql/index.html#concat_ws] > The concat argument type supports not only the string type but also the array > type. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6446) Add CONCAT_WS function (enabled in Spark library)
EveyWu created CALCITE-6446: Summary: Add CONCAT_WS function (enabled in Spark library) Key: CALCITE-6446 URL: https://issues.apache.org/jira/browse/CALCITE-6446 Project: Calcite Issue Type: Improvement Reporter: EveyWu -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6446) Add CONCAT_WS function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6446?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6446: Description: Add Spark functions that have been implemented but have different OperandTypes/Returns. Add Function The parameter type supports not only the string type but also the array type. > Add CONCAT_WS function (enabled in Spark library) > - > > Key: CALCITE-6446 > URL: https://issues.apache.org/jira/browse/CALCITE-6446 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > The parameter type supports not only the string type but also the array type. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6446) Add CONCAT_WS function (enabled in Spark library)
EveyWu created CALCITE-6446: Summary: Add CONCAT_WS function (enabled in Spark library) Key: CALCITE-6446 URL: https://issues.apache.org/jira/browse/CALCITE-6446 Project: Calcite Issue Type: Improvement Reporter: EveyWu -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6445) Add REVERSE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6445?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6445: Description: Add Spark functions that have been implemented but have different OperandTypes/Returns. Add Function [REVERSE|https://spark.apache.org/docs/latest/api/sql/#reverse] The parameter type supports not only the string type but also the array type. was: Add Spark functions that have been implemented but have different OperandTypes/Returns. Add Function [REVERSE(str|array)|https://spark.apache.org/docs/latest/api/sql/#reverse] The parameter type supports not only the string type but also the array type. > Add REVERSE function (enabled in Spark library) > --- > > Key: CALCITE-6445 > URL: https://issues.apache.org/jira/browse/CALCITE-6445 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function [REVERSE|https://spark.apache.org/docs/latest/api/sql/#reverse] > The parameter type supports not only the string type but also the array type. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6445) Add REVERSE function (enabled in Spark library)
EveyWu created CALCITE-6445: Summary: Add REVERSE function (enabled in Spark library) Key: CALCITE-6445 URL: https://issues.apache.org/jira/browse/CALCITE-6445 Project: Calcite Issue Type: Improvement Reporter: EveyWu Add Spark functions that have been implemented but have different OperandTypes/Returns. Add Function [REVERSE(str|array)|https://spark.apache.org/docs/latest/api/sql/#reverse] The parameter type supports not only the string type but also the array type. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6445) Add REVERSE function (enabled in Spark library)
EveyWu created CALCITE-6445: Summary: Add REVERSE function (enabled in Spark library) Key: CALCITE-6445 URL: https://issues.apache.org/jira/browse/CALCITE-6445 Project: Calcite Issue Type: Improvement Reporter: EveyWu Add Spark functions that have been implemented but have different OperandTypes/Returns. Add Function [REVERSE(str|array)|https://spark.apache.org/docs/latest/api/sql/#reverse] The parameter type supports not only the string type but also the array type. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6363) Introduce a rule to derive more filters from inner join condition
[ https://issues.apache.org/jira/browse/CALCITE-6363?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17837032#comment-17837032 ] EveyWu commented on CALCITE-6363: -- It's a good idea to add more dynamic filter rules.(y) > Introduce a rule to derive more filters from inner join condition > - > > Key: CALCITE-6363 > URL: https://issues.apache.org/jira/browse/CALCITE-6363 > Project: Calcite > Issue Type: New Feature > Components: core >Reporter: ruanhui >Priority: Minor > Labels: pull-request-available > > Sometimes we can infer more predicates from inner Join , for example, in the > query > SELECT * FROM ta INNER JOIN tb ON ta.x = tb.y WHERE ta.x > 10 > we can infer condition tb.y > 10 and we can push it down to the table tb. > In this way, it is possible to reduce the amount of data involved in the Join. > To achieve this, here is my idea. > The core data strucature is two Multimap: > predicateMap : a map for inputRef to corresponding predicate such as: $1 -> > [$1 > 10, $1 < 20, $1 = $2] > equivalenceMap : a map for inputRef to corresponding equivalent values or > inputRefs such as: $1 -> [$2, 1] > The filter derivation is divided into 4 steps: > 1. construct predicate map and equivalence map by traversing all conjunctions > in the condition > 2. search map and rewrite predicates with equivalent inputRefs or literals > 2.1 find all inputRefs that are equivalent to the current inputRef, and then > rewrite all predicates involving equivalent inputRefs using inputRef, for > example if we have inputRef $1 = equivInputRef $2, then we can rewrite \{$2 = > 10} to \{$1 = 10}. > 2.2 find all predicates involving current inputRef. If any predicate refers > to another inputRef, rewrite the predicate with the literal/constant > equivalent to that inputRef, such as: if we have inputRef \{$1 > $2} and \{$2 > = 10} then we can infer new condition \{$1 > 10}. > 2.3 derive new predicates based on equivalence relation in equivalenceMultimap > 3. compose all original predicates and derived predicates > 4. simplify expression such as range merging, like \{$1 > 10 AND $1 > 20} => > \{$1 > 20}, \{$1 > $2 AND $1 > $2} => \{$1 > $2} > Anyone interested in this, please feel free to comment on this issue. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17825227#comment-17825227 ] EveyWu commented on CALCITE-6278: -- done, CALCITE-6320 has been added to follow up. > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-03-07-09-32-27-002.png, > image-2024-03-09-11-13-49-064.png, image-2024-03-09-11-37-27-816.png, > image-2024-03-09-11-38-08-797.png > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # -Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite.- > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6320) Parse SQL with Hive-style and Spark-style unescaped character literals
[ https://issues.apache.org/jira/browse/CALCITE-6320?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6320: Description: # Since Spark 2.0, string literals (including regex patterns) are unescaped in the parser phase, details in [AstBuilder|[https://github.com/apache/spark/blob/76b1c122cb7d77e8f175b25b935b9296a669d5d8/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala#L2876C1-L2882C4]] # In Hive, unescape in the node normalization phase(`Dispatcher#dispatch`).[StrExprProcessor|[https://github.com/apache/hive/blob/03a76ac70370fb94a78b00496ec511e671c652f2/ql/src/java/org/apache/hadoop/hive/ql/parse/type/TypeCheckProcFactory.java#L403C1-L405C17]] is the processor for processing string unescape. > Parse SQL with Hive-style and Spark-style unescaped character literals > -- > > Key: CALCITE-6320 > URL: https://issues.apache.org/jira/browse/CALCITE-6320 > Project: Calcite > Issue Type: Bug >Reporter: EveyWu >Priority: Minor > > # Since Spark 2.0, string literals (including regex patterns) are unescaped > in the parser phase, details in > [AstBuilder|[https://github.com/apache/spark/blob/76b1c122cb7d77e8f175b25b935b9296a669d5d8/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala#L2876C1-L2882C4]] > # In Hive, unescape in the node normalization > phase(`Dispatcher#dispatch`).[StrExprProcessor|[https://github.com/apache/hive/blob/03a76ac70370fb94a78b00496ec511e671c652f2/ql/src/java/org/apache/hadoop/hive/ql/parse/type/TypeCheckProcFactory.java#L403C1-L405C17]] > is the processor for processing string unescape. > > > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6320) Parse SQL with Hive-style and Spark-style unescaped character literals
EveyWu created CALCITE-6320: Summary: Parse SQL with Hive-style and Spark-style unescaped character literals Key: CALCITE-6320 URL: https://issues.apache.org/jira/browse/CALCITE-6320 Project: Calcite Issue Type: Bug Reporter: EveyWu -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6320) Parse SQL with Hive-style and Spark-style unescaped character literals
EveyWu created CALCITE-6320: Summary: Parse SQL with Hive-style and Spark-style unescaped character literals Key: CALCITE-6320 URL: https://issues.apache.org/jira/browse/CALCITE-6320 Project: Calcite Issue Type: Bug Reporter: EveyWu -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17825003#comment-17825003 ] EveyWu commented on CALCITE-6278: -- [~julianhyde] The unescape operation has been deleted. Thank you very much for reviewing the code again when you have time. > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-03-07-09-32-27-002.png, > image-2024-03-09-11-13-49-064.png, image-2024-03-09-11-37-27-816.png, > image-2024-03-09-11-38-08-797.png > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # -Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite.- > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17825002#comment-17825002 ] EveyWu commented on CALCITE-6278: -- 1. Sure, in my opinion, in this case, 'unescaping' means handling backslashes in character literals. 2. Indeed, if a query is based on table fields instead of literal strings, there will be query inconsistencies, as escaping may have been done when inserting data. A possible solution is to failover unescape, which may lead to an expansion of the range that `rlike` can support. {code:java} boolean find; try { find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find(); } catch (Exception patternException) { find = false; } if (!find) { s = StringEscapeUtils.unescapeJava(s); pattern = StringEscapeUtils.unescapeJava(pattern); find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find(); } return find; {code} 3. For the `rlike` function, I currently can't think of a better way to ensure that the behavior of table field queries and literal string queries is consistent with spark and hive at the same time. I will roll back the handling of unescape. > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-03-07-09-32-27-002.png, > image-2024-03-09-11-13-49-064.png, image-2024-03-09-11-37-27-816.png, > image-2024-03-09-11-38-08-797.png > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # -Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite.- > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] (CALCITE-6180) Append possibility to escape backslash in LIKE expression
[ https://issues.apache.org/jira/browse/CALCITE-6180 ] EveyWu deleted comment on CALCITE-6180: -- was (Author: eveywu): 1. Sure, in my opinion, in this case, 'unescaping' means handling backslashes in character literals. 2. Indeed, if a query is based on table fields instead of literal strings, there will be query inconsistencies, as escaping may have been done when inserting data. A possible solution is to failover unescape, which may lead to an expansion of the range that `rlike` can support. {code:java} boolean find; try { find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find(); } catch (Exception patternException) { find = false; } if (!find) { s = StringEscapeUtils.unescapeJava(s); pattern = StringEscapeUtils.unescapeJava(pattern); find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find(); } return find; {code} 3. For the `rlike` function, I currently can't think of a better way to ensure that the behavior of table field queries and literal string queries is consistent with spark and hive at the same time. I will roll back the handling of unescape. > Append possibility to escape backslash in LIKE expression > - > > Key: CALCITE-6180 > URL: https://issues.apache.org/jira/browse/CALCITE-6180 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.36.0 >Reporter: Evgeny Stanilovsky >Priority: Major > Labels: pull-request-available > > The expression below must be processed correctly and return positive result > {noformat} > select 'Dev\ops' like 'Dev#\ops' escape '#'; > {noformat} > Insted it returns : > Invalid escape sequence exception. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6278: Description: Add Spark functions that have been implemented but have different OperandTypes/Returns. Add Function [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] # Since this function has the same implementation as the Spark [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] function, the implementation can be directly reused. # -Since Spark 2.0, string literals (including regex patterns) are unescaped in SQL parser, also fix this bug in calcite.- was: Add Spark functions that have been implemented but have different OperandTypes/Returns. Add Function [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] # Since this function has the same implementation as the Spark [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] function, the implementation can be directly reused. # Since Spark 2.0, string literals (including regex patterns) are unescaped in SQL parser, also fix this bug in calcite. > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-03-07-09-32-27-002.png, > image-2024-03-09-11-13-49-064.png, image-2024-03-09-11-37-27-816.png, > image-2024-03-09-11-38-08-797.png > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # -Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite.- > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6180) Append possibility to escape backslash in LIKE expression
[ https://issues.apache.org/jira/browse/CALCITE-6180?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17825000#comment-17825000 ] EveyWu edited comment on CALCITE-6180 at 3/10/24 4:32 AM: --- 1. Sure, in my opinion, in this case, 'unescaping' means handling backslashes in character literals. 2. Indeed, if a query is based on table fields instead of literal strings, there will be query inconsistencies, as escaping may have been done when inserting data. A possible solution is to failover unescape, which may lead to an expansion of the range that `rlike` can support. {code:java} boolean find; try { find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find(); } catch (Exception patternException) { find = false; } if (!find) { s = StringEscapeUtils.unescapeJava(s); pattern = StringEscapeUtils.unescapeJava(pattern); find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find(); } return find; {code} 3. For the `rlike` function, I currently can't think of a better way to ensure that the behavior of table field queries and literal string queries is consistent with spark and hive at the same time. I will roll back the handling of unescape. was (Author: eveywu): 1. Sure, in my opinion, in this case, 'unescaping' means handling backslashes in character literals. 2. Indeed, if a query is based on table fields instead of literal strings, there will be query inconsistencies, as escaping may have been done when inserting data. A possible solution is to failover unescape, which may lead to an expansion of the range that `rlike` can support. {code:java} boolean find; try { find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find(); } catch (Exception patternException) { find = false; } if (!find) { s = StringEscapeUtils.unescapeJava(s); pattern = StringEscapeUtils.unescapeJava(pattern); find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find(); } return find; {code} 3. For the `rlike` function, I currently can't think of a better way to ensure that the behavior of table field queries and literal string queries is consistent with spark and hive at the same time. I will roll back the handling of unescaping. > Append possibility to escape backslash in LIKE expression > - > > Key: CALCITE-6180 > URL: https://issues.apache.org/jira/browse/CALCITE-6180 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.36.0 >Reporter: Evgeny Stanilovsky >Priority: Major > Labels: pull-request-available > > The expression below must be processed correctly and return positive result > {noformat} > select 'Dev\ops' like 'Dev#\ops' escape '#'; > {noformat} > Insted it returns : > Invalid escape sequence exception. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6180) Append possibility to escape backslash in LIKE expression
[ https://issues.apache.org/jira/browse/CALCITE-6180?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17825000#comment-17825000 ] EveyWu edited comment on CALCITE-6180 at 3/10/24 4:31 AM: --- 1. Sure, in my opinion, in this case, 'unescaping' means handling backslashes in character literals. 2. Indeed, if a query is based on table fields instead of literal strings, there will be query inconsistencies, as escaping may have been done when inserting data. A possible solution is to failover unescape, which may lead to an expansion of the range that `rlike` can support. {code:java} boolean find; try { find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find(); } catch (Exception patternException) { find = false; } if (!find) { s = StringEscapeUtils.unescapeJava(s); pattern = StringEscapeUtils.unescapeJava(pattern); find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find(); } return find; {code} 3. For the `rlike` function, I currently can't think of a better way to ensure that the behavior of table field queries and literal string queries is consistent with spark and hive at the same time. I will roll back the handling of unescaping. was (Author: eveywu): 1. Sure, in my opinion, in this case, 'unescaping' means handling backslashes in character literals. 2. Indeed, if a query is based on table fields instead of literal strings, there will be query inconsistencies, as escaping may have been done when inserting data. A possible solution is to failover unescape, which may lead to an expansion of the range that `rlike` can support. {code:java} boolean find; try { find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find(); } catch (Exception patternException) { find = false; } if (!find) { s = StringEscapeUtils.unescapeJava(s); pattern = StringEscapeUtils.unescapeJava(pattern); find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find(); } return find; {code} 3. For the `rlike` function, I currently can't think of a better way to ensure that the behavior of table field queries and literal string queries is consistent with spark and hive at the same time. > Append possibility to escape backslash in LIKE expression > - > > Key: CALCITE-6180 > URL: https://issues.apache.org/jira/browse/CALCITE-6180 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.36.0 >Reporter: Evgeny Stanilovsky >Priority: Major > Labels: pull-request-available > > The expression below must be processed correctly and return positive result > {noformat} > select 'Dev\ops' like 'Dev#\ops' escape '#'; > {noformat} > Insted it returns : > Invalid escape sequence exception. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6180) Append possibility to escape backslash in LIKE expression
[ https://issues.apache.org/jira/browse/CALCITE-6180?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17825000#comment-17825000 ] EveyWu edited comment on CALCITE-6180 at 3/10/24 4:30 AM: --- 1. Sure, in my opinion, in this case, 'unescaping' means handling backslashes in character literals. 2. Indeed, if a query is based on table fields instead of literal strings, there will be query inconsistencies, as escaping may have been done when inserting data. A possible solution is to failover unescape, which may lead to an expansion of the range that `rlike` can support. {code:java} boolean find; try { find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find(); } catch (Exception patternException) { find = false; } if (!find) { s = StringEscapeUtils.unescapeJava(s); pattern = StringEscapeUtils.unescapeJava(pattern); find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find(); } return find; {code} 3. For the `rlike` function, I currently can't think of a better way to ensure that the behavior of table field queries and literal string queries is consistent with spark and hive at the same time. was (Author: eveywu): Sure, in my opinion, in this case, 'unescaping' means handling backslashes in character literals. Indeed, if a query is based on table fields instead of literal strings, there will be query inconsistencies, as escaping may have been done when inserting data. A possible solution is to failover unescape, which may lead to an expansion of the range that `rlike` can support. {code:java} boolean find; try { find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find(); } catch (Exception patternException) { find = false; } if (!find) { s = StringEscapeUtils.unescapeJava(s); pattern = StringEscapeUtils.unescapeJava(pattern); find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find(); } return find; {code} For the `rlike` function, I currently can't think of a better way to ensure that the behavior of table field queries and literal string queries is consistent with spark and hive at the same time. > Append possibility to escape backslash in LIKE expression > - > > Key: CALCITE-6180 > URL: https://issues.apache.org/jira/browse/CALCITE-6180 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.36.0 >Reporter: Evgeny Stanilovsky >Priority: Major > Labels: pull-request-available > > The expression below must be processed correctly and return positive result > {noformat} > select 'Dev\ops' like 'Dev#\ops' escape '#'; > {noformat} > Insted it returns : > Invalid escape sequence exception. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6180) Append possibility to escape backslash in LIKE expression
[ https://issues.apache.org/jira/browse/CALCITE-6180?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17825000#comment-17825000 ] EveyWu edited comment on CALCITE-6180 at 3/10/24 4:30 AM: --- 1. Sure, in my opinion, in this case, 'unescaping' means handling backslashes in character literals. 2. Indeed, if a query is based on table fields instead of literal strings, there will be query inconsistencies, as escaping may have been done when inserting data. A possible solution is to failover unescape, which may lead to an expansion of the range that `rlike` can support. {code:java} boolean find; try { find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find(); } catch (Exception patternException) { find = false; } if (!find) { s = StringEscapeUtils.unescapeJava(s); pattern = StringEscapeUtils.unescapeJava(pattern); find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find(); } return find; {code} 3. For the `rlike` function, I currently can't think of a better way to ensure that the behavior of table field queries and literal string queries is consistent with spark and hive at the same time. was (Author: eveywu): 1. Sure, in my opinion, in this case, 'unescaping' means handling backslashes in character literals. 2. Indeed, if a query is based on table fields instead of literal strings, there will be query inconsistencies, as escaping may have been done when inserting data. A possible solution is to failover unescape, which may lead to an expansion of the range that `rlike` can support. {code:java} boolean find; try { find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find(); } catch (Exception patternException) { find = false; } if (!find) { s = StringEscapeUtils.unescapeJava(s); pattern = StringEscapeUtils.unescapeJava(pattern); find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find(); } return find; {code} 3. For the `rlike` function, I currently can't think of a better way to ensure that the behavior of table field queries and literal string queries is consistent with spark and hive at the same time. > Append possibility to escape backslash in LIKE expression > - > > Key: CALCITE-6180 > URL: https://issues.apache.org/jira/browse/CALCITE-6180 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.36.0 >Reporter: Evgeny Stanilovsky >Priority: Major > Labels: pull-request-available > > The expression below must be processed correctly and return positive result > {noformat} > select 'Dev\ops' like 'Dev#\ops' escape '#'; > {noformat} > Insted it returns : > Invalid escape sequence exception. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6180) Append possibility to escape backslash in LIKE expression
[ https://issues.apache.org/jira/browse/CALCITE-6180?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17825000#comment-17825000 ] EveyWu edited comment on CALCITE-6180 at 3/10/24 4:29 AM: --- Sure, in my opinion, in this case, 'unescaping' means handling backslashes in character literals. Indeed, if a query is based on table fields instead of literal strings, there will be query inconsistencies, as escaping may have been done when inserting data. A possible solution is to failover unescape, which may lead to an expansion of the range that `rlike` can support. {code:java} boolean find; try { find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find(); } catch (Exception patternException) { find = false; } if (!find) { s = StringEscapeUtils.unescapeJava(s); pattern = StringEscapeUtils.unescapeJava(pattern); find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find(); } return find; {code} For the `rlike` function, I currently can't think of a better way to ensure that the behavior of table field queries and literal string queries is consistent with spark and hive at the same time. was (Author: eveywu): Sure, in my opinion, in this case, 'unescaping' means handling backslashes in character literals. Indeed, if a query is based on table fields instead of literal strings, there will be query inconsistencies, as escaping may have been done when inserting data. A possible solution is to failover unescape, which may lead to an expansion of the range that `rlike` can support. ``` boolean find; try { find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find(); } catch (Exception patternException) { find = false; } if (!find) { s = StringEscapeUtils.unescapeJava(s); pattern = StringEscapeUtils.unescapeJava(pattern); find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find(); } return find; ``` For the `rlike` function, I currently can't think of a better way to ensure that the behavior of table field queries and literal string queries is consistent with spark and hive at the same time. > Append possibility to escape backslash in LIKE expression > - > > Key: CALCITE-6180 > URL: https://issues.apache.org/jira/browse/CALCITE-6180 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.36.0 >Reporter: Evgeny Stanilovsky >Priority: Major > Labels: pull-request-available > > The expression below must be processed correctly and return positive result > {noformat} > select 'Dev\ops' like 'Dev#\ops' escape '#'; > {noformat} > Insted it returns : > Invalid escape sequence exception. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6180) Append possibility to escape backslash in LIKE expression
[ https://issues.apache.org/jira/browse/CALCITE-6180?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17825000#comment-17825000 ] EveyWu commented on CALCITE-6180: -- Sure, in my opinion, in this case, 'unescaping' means handling backslashes in character literals. Indeed, if a query is based on table fields instead of literal strings, there will be query inconsistencies, as escaping may have been done when inserting data. A possible solution is to failover unescape, which may lead to an expansion of the range that `rlike` can support. ``` boolean find; try { find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find(); } catch (Exception patternException) { find = false; } if (!find) { s = StringEscapeUtils.unescapeJava(s); pattern = StringEscapeUtils.unescapeJava(pattern); find = cache.getUnchecked(new Key(0, pattern)).matcher(s).find(); } return find; ``` For the `rlike` function, I currently can't think of a better way to ensure that the behavior of table field queries and literal string queries is consistent with spark and hive at the same time. > Append possibility to escape backslash in LIKE expression > - > > Key: CALCITE-6180 > URL: https://issues.apache.org/jira/browse/CALCITE-6180 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.36.0 >Reporter: Evgeny Stanilovsky >Priority: Major > Labels: pull-request-available > > The expression below must be processed correctly and return positive result > {noformat} > select 'Dev\ops' like 'Dev#\ops' escape '#'; > {noformat} > Insted it returns : > Invalid escape sequence exception. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17824900#comment-17824900 ] EveyWu edited comment on CALCITE-6278 at 3/9/24 3:54 AM: -- [~julianhyde] Thanks for the review. 1. "Since Spark 2.0, string literals (including regex patterns) are unescaped in SQL parser", this description comes from Spark [official documentation|#regexp].] !image-2024-03-09-11-13-49-064.png|width=491,height=176! 2. In Spark, unescape is indeed performed in the parser phase. Please view the details in `AstBuilder`: [https://github.com/apache/spark/blob/76b1c122cb7d77e8f175b25b935b9296a669d5d8/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala#L2876C1-L2882C4] The default value of `spark.sql.parser.escapedStringLiterals` is false. !image-2024-03-09-11-38-08-797.png|width=455,height=85! 3. In Hive, unescape is not done in the SQL AST parser phase, but in the Node normalization phase(`Dispatcher#dispatch`). `StrExprProcessor` is the processor for processing string unescape. [https://github.com/apache/hive/blob/03a76ac70370fb94a78b00496ec511e671c652f2/ql/src/java/org/apache/hadoop/hive/ql/parse/type/TypeCheckProcFactory.java#L403C1-L405C17] !image-2024-03-09-11-37-27-816.png|width=520,height=132! 4. "If unescaping is happening in Spark’s parser, Calcite should also do it in the parser", I think this is unnecessary, First, like Spark and Hive, different engines have different processing methods, which do not necessarily have to be processed in the same phase. In addition, this unescape processing is global and not only for the `rlike` function. Finally, Calcite is handled in the `rlike` function, which is by far the simplest and minimal impact modification. If Calcite also needs to perform global string unescape processing, it can be discussed separately in the subsequent Jira. was (Author: eveywu): [~julianhyde] Thanks for the review. 1. "Since Spark 2.0, string literals (including regex patterns) are unescaped in SQL parser", this description comes from Spark [official documentation|[https://spark.apache.org/docs/latest/api/sql/index.html#regexp].] !image-2024-03-09-11-13-49-064.png|width=491,height=176! 2. In Spark, unescape is indeed performed in the parser phase. Please view the details in AstBuilder: [https://github.com/apache/spark/blob/76b1c122cb7d77e8f175b25b935b9296a669d5d8/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala#L2876C1-L2882C4] The default value of `spark.sql.parser.escapedStringLiterals` is false. !image-2024-03-09-11-38-08-797.png|width=455,height=85! 3. In Hive, unescape is not done in the SQL AST parser phase, but in the Node normalization phase(`Dispatcher#dispatch`). `StrExprProcessor` is the processor for processing string unescape. [https://github.com/apache/hive/blob/03a76ac70370fb94a78b00496ec511e671c652f2/ql/src/java/org/apache/hadoop/hive/ql/parse/type/TypeCheckProcFactory.java#L403C1-L405C17] !image-2024-03-09-11-37-27-816.png|width=520,height=132! 4. "If unescaping is happening in Spark’s parser, Calcite should also do it in the parser", I think this is unnecessary, First, like Spark and Hive, different engines have different processing methods, which do not necessarily have to be processed in the same phase. In addition, this unescape processing is global and not only for the `rlike` function. Finally, Calcite is handled in the `rlike` function, which is by far the simplest and minimal impact modification. If Calcite also needs to perform global string unescape processing, it can be discussed separately in the subsequent Jira. > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-03-07-09-32-27-002.png, > image-2024-03-09-11-13-49-064.png, image-2024-03-09-11-37-27-816.png, > image-2024-03-09-11-38-08-797.png > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite. > > -- This message was se
[jira] [Commented] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17824900#comment-17824900 ] EveyWu commented on CALCITE-6278: -- [~julianhyde] Thanks for the review. 1. "Since Spark 2.0, string literals (including regex patterns) are unescaped in SQL parser", this description comes from Spark [official documentation|[https://spark.apache.org/docs/latest/api/sql/index.html#regexp].] !image-2024-03-09-11-13-49-064.png|width=491,height=176! 2. In Spark, unescape is indeed performed in the parser phase. Please view the details in AstBuilder: [https://github.com/apache/spark/blob/76b1c122cb7d77e8f175b25b935b9296a669d5d8/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala#L2876C1-L2882C4] The default value of `spark.sql.parser.escapedStringLiterals` is false. !image-2024-03-09-11-38-08-797.png|width=455,height=85! 3. In Hive, unescape is not done in the SQL AST parser phase, but in the Node normalization phase(`Dispatcher#dispatch`). `StrExprProcessor` is the processor for processing string unescape. [https://github.com/apache/hive/blob/03a76ac70370fb94a78b00496ec511e671c652f2/ql/src/java/org/apache/hadoop/hive/ql/parse/type/TypeCheckProcFactory.java#L403C1-L405C17] !image-2024-03-09-11-37-27-816.png|width=520,height=132! 4. "If unescaping is happening in Spark’s parser, Calcite should also do it in the parser", I think this is unnecessary, First, like Spark and Hive, different engines have different processing methods, which do not necessarily have to be processed in the same phase. In addition, this unescape processing is global and not only for the `rlike` function. Finally, Calcite is handled in the `rlike` function, which is by far the simplest and minimal impact modification. If Calcite also needs to perform global string unescape processing, it can be discussed separately in the subsequent Jira. > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-03-07-09-32-27-002.png, > image-2024-03-09-11-13-49-064.png, image-2024-03-09-11-37-27-816.png, > image-2024-03-09-11-38-08-797.png > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite. > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6278: Attachment: image-2024-03-09-11-38-08-797.png > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-03-07-09-32-27-002.png, > image-2024-03-09-11-13-49-064.png, image-2024-03-09-11-37-27-816.png, > image-2024-03-09-11-38-08-797.png > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite. > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6278: Attachment: image-2024-03-09-11-37-27-816.png > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-03-07-09-32-27-002.png, > image-2024-03-09-11-13-49-064.png, image-2024-03-09-11-37-27-816.png > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite. > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6278: Attachment: image-2024-03-09-11-13-49-064.png > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-03-07-09-32-27-002.png, > image-2024-03-09-11-13-49-064.png > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite. > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6278: Attachment: (was: image-2024-03-09-11-12-51-658.png) > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-03-07-09-32-27-002.png, > image-2024-03-09-11-13-49-064.png > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite. > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6278: Attachment: image-2024-03-09-11-12-51-658.png > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-03-07-09-32-27-002.png, > image-2024-03-09-11-12-51-658.png > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite. > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6278: Fix Version/s: (was: 1.37.0) > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-03-07-09-32-27-002.png > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite. > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17824226#comment-17824226 ] EveyWu edited comment on CALCITE-6278 at 3/7/24 2:39 AM: -- # Fixed summary symbol issue # Sure, in spark, RLIKE has the same semantics as REGEXP_LIKE and REGEXP, and has been combined in the test. # Judging by the engine execution behavior, Hive also performs the unescape. # Add more unescape tests in `SqlOperatorTest` !image-2024-03-07-09-32-27-002.png|width=426,height=392! was (Author: eveywu): # Fixed summary symbol issue # Sure, in spark, RLIKE has the same semantics as REGEXP_LIKE and REGEXP, and has been combined in the test. # Judging by the engine execution behavior, Hive also performs the unescape. !image-2024-03-07-09-32-27-002.png|width=426,height=392! > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > Attachments: image-2024-03-07-09-32-27-002.png > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite. > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17824226#comment-17824226 ] EveyWu edited comment on CALCITE-6278 at 3/7/24 1:32 AM: -- # Fixed summary symbol issue # Sure, in spark, RLIKE has the same semantics as REGEXP_LIKE and REGEXP, and has been combined in the test. # Judging by the engine execution behavior, Hive also performs the unescape. !image-2024-03-07-09-32-27-002.png|width=426,height=392! was (Author: eveywu): # Fixed summary symbol issue # Sure, in spark, RLIKE has the same semantics as REGEXP_LIKE and REGEXP, and has been combined in the test. # Judging by the engine execution behavior, Hive also performs the unescape. jdbc:hive2://127.0.0.1:1> select 'abc' RLIKE '^\abc$'; +---+ | _c0 | +---+ | true | +---+ jdbc:hive2://127.0.0.1:1> select '\abc' RLIKE '^abc$'; +---+ | _c0 | +---+ | true | +---+ > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > Attachments: image-2024-03-07-09-32-27-002.png > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite. > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17824226#comment-17824226 ] EveyWu edited comment on CALCITE-6278 at 3/7/24 1:30 AM: -- # Fixed summary symbol issue # Sure, in spark, RLIKE has the same semantics as REGEXP_LIKE and REGEXP, and has been combined in the test. # Judging by the engine execution behavior, Hive also performs the unescape. jdbc:hive2://127.0.0.1:1> select 'abc' RLIKE '^\abc$'; +---+ | _c0 | +---+ | true | +---+ jdbc:hive2://127.0.0.1:1> select '\abc' RLIKE '^abc$'; +---+ | _c0 | +---+ | true | +---+ was (Author: eveywu): # Fixed summary symbol issue # Sure, in spark, RLIKE has the same semantics as REGEXP_LIKE and REGEXP, and has been combined in the test. # Judging by the engine execution behavior, Hive also performs the unescape. ``` 0: jdbc:hive2://127.0.0.1:1> select 'abc' RLIKE '^\abc$'; +---+ | _c0 | +---+ | true | +---+ 0: jdbc:hive2://127.0.0.1:1> select '\abc' RLIKE '^abc$'; +---+ | _c0 | +---+ | true | +---+ ``` > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite. > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17824226#comment-17824226 ] EveyWu commented on CALCITE-6278: -- # Fixed summary symbol issue # Sure, in spark, RLIKE has the same semantics as REGEXP_LIKE and REGEXP, and has been combined in the test. # Judging by the engine execution behavior, Hive also performs the unescape. ``` 0: jdbc:hive2://127.0.0.1:1> select 'abc' RLIKE '^\abc$'; +---+ | _c0 | +---+ | true | +---+ 0: jdbc:hive2://127.0.0.1:1> select '\abc' RLIKE '^abc$'; +---+ | _c0 | +---+ | true | +---+ ``` > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite. > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6278: Description: Add Spark functions that have been implemented but have different OperandTypes/Returns. Add Function [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] # Since this function has the same implementation as the Spark [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] function, the implementation can be directly reused. # Since Spark 2.0, string literals (including regex patterns) are unescaped in SQL parser, also fix this bug in calcite. was: Add Spark functions that have been implemented but have different OperandTypes/Returns. Add Function [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] Since this function has the same implementation as the Spark [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] function, the implementation can be directly reused. > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite. > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6278: Description: Add Spark functions that have been implemented but have different OperandTypes/Returns. Add Function [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] Since this function has the same implementation as the Spark [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] function, the implementation can be directly reused. was: Add Spark functions that have been implemented but have different OperandTypes/Returns. Add Function [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] Since this function has the same implementation as the Spark [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] function, the implementation can be directly reused. > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6278: Description: Add Spark functions that have been implemented but have different OperandTypes/Returns. Add Function [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] Since this function has the same implementation as the Spark [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] function, the implementation can be directly reused. was: Add Spark functions that have been implemented but have different OperandTypes/Returns. Add Function [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp] Since this function has the same implementation as the Big Query [REGEXP_CONTAINS|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#regexp_contains] function. the implementation can be directly reused. > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6278: Summary: Add REGEXP, REGEXP_LIKE function (enabled in Spark library) (was: Add REGEXP、REGEXP_LIKE function (enabled in Spark library)) > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp] > Since this function has the same implementation as the Big Query > [REGEXP_CONTAINS|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#regexp_contains] > function. the implementation can be directly reused. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6278) Add REGEXP、REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6278: Fix Version/s: 1.37.0 > Add REGEXP、REGEXP_LIKE function (enabled in Spark library) > --- > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp] > Since this function has the same implementation as the Big Query > [REGEXP_CONTAINS|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#regexp_contains] > function. the implementation can be directly reused. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6278) Add REGEXP、REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6278: Summary: Add REGEXP、REGEXP_LIKE function (enabled in Spark library) (was: Add REGEXP function (enabled in Spark library)) > Add REGEXP、REGEXP_LIKE function (enabled in Spark library) > --- > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp] > Since this function has the same implementation as the Big Query > [REGEXP_CONTAINS|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#regexp_contains] > function. the implementation can be directly reused. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6285) Function ARRAY_INSERT produces an incorrect result for negative indices
[ https://issues.apache.org/jira/browse/CALCITE-6285?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17822349#comment-17822349 ] EveyWu commented on CALCITE-6285: -- I agree with you > Function ARRAY_INSERT produces an incorrect result for negative indices > --- > > Key: CALCITE-6285 > URL: https://issues.apache.org/jira/browse/CALCITE-6285 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.36.0 >Reporter: Mihai Budiu >Priority: Minor > > Here is a test taken from the Spark documentation page: > https://spark.apache.org/docs/latest/api/sql/index.html#array_insert > {code} > SELECT array_insert(array(5, 3, 2, 1), -4, 4); > [5,4,3,2,1] > {code} > The result produced by Calcite is: > [4,5,3,2,1] > The strange thing is that there are tests for negative indices. I wonder if > the original tests are wrong, or the behavior of this function in Spark was > changed since the tests were written. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6285) Function ARRAY_INSERT produces an incorrect result for negative indices
[ https://issues.apache.org/jira/browse/CALCITE-6285?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17822347#comment-17822347 ] EveyWu commented on CALCITE-6285: -- Another simpler way, can we keep consistent with the latest implementation of spark? In practice, calculations based on spark Function are more likely to be executed on spark engine, calcite only provides an alternative execution. > Function ARRAY_INSERT produces an incorrect result for negative indices > --- > > Key: CALCITE-6285 > URL: https://issues.apache.org/jira/browse/CALCITE-6285 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.36.0 >Reporter: Mihai Budiu >Priority: Minor > > Here is a test taken from the Spark documentation page: > https://spark.apache.org/docs/latest/api/sql/index.html#array_insert > {code} > SELECT array_insert(array(5, 3, 2, 1), -4, 4); > [5,4,3,2,1] > {code} > The result produced by Calcite is: > [4,5,3,2,1] > The strange thing is that there are tests for negative indices. I wonder if > the original tests are wrong, or the behavior of this function in Spark was > changed since the tests were written. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6285) Function ARRAY_INSERT produces an incorrect result for negative indices
[ https://issues.apache.org/jira/browse/CALCITE-6285?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17822343#comment-17822343 ] EveyWu commented on CALCITE-6285: -- I think the Spark version update causes the problem, -1 for which the function inserts a new element after the current last element. The original definition [3.4.0, |https://spark.apache.org/docs/3.4.0/api/sql/index.html#array_insert]Updated version definition [3.4.2|https://spark.apache.org/docs/3.4.2/api/sql/index.html#array_insert] > Function ARRAY_INSERT produces an incorrect result for negative indices > --- > > Key: CALCITE-6285 > URL: https://issues.apache.org/jira/browse/CALCITE-6285 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.36.0 >Reporter: Mihai Budiu >Priority: Minor > > Here is a test taken from the Spark documentation page: > https://spark.apache.org/docs/latest/api/sql/index.html#array_insert > {code} > SELECT array_insert(array(5, 3, 2, 1), -4, 4); > [5,4,3,2,1] > {code} > The result produced by Calcite is: > [4,5,3,2,1] > The strange thing is that there are tests for negative indices. I wonder if > the original tests are wrong, or the behavior of this function in Spark was > changed since the tests were written. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6285) Function ARRAY_INSERT produces an incorrect result for negative indices
[ https://issues.apache.org/jira/browse/CALCITE-6285?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6285: Attachment: (was: image-2024-03-01-10-35-11-673.png) > Function ARRAY_INSERT produces an incorrect result for negative indices > --- > > Key: CALCITE-6285 > URL: https://issues.apache.org/jira/browse/CALCITE-6285 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.36.0 >Reporter: Mihai Budiu >Priority: Minor > > Here is a test taken from the Spark documentation page: > https://spark.apache.org/docs/latest/api/sql/index.html#array_insert > {code} > SELECT array_insert(array(5, 3, 2, 1), -4, 4); > [5,4,3,2,1] > {code} > The result produced by Calcite is: > [4,5,3,2,1] > The strange thing is that there are tests for negative indices. I wonder if > the original tests are wrong, or the behavior of this function in Spark was > changed since the tests were written. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6285) Function ARRAY_INSERT produces an incorrect result for negative indices
[ https://issues.apache.org/jira/browse/CALCITE-6285?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6285: Attachment: image-2024-03-01-10-35-11-673.png > Function ARRAY_INSERT produces an incorrect result for negative indices > --- > > Key: CALCITE-6285 > URL: https://issues.apache.org/jira/browse/CALCITE-6285 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.36.0 >Reporter: Mihai Budiu >Priority: Minor > > Here is a test taken from the Spark documentation page: > https://spark.apache.org/docs/latest/api/sql/index.html#array_insert > {code} > SELECT array_insert(array(5, 3, 2, 1), -4, 4); > [5,4,3,2,1] > {code} > The result produced by Calcite is: > [4,5,3,2,1] > The strange thing is that there are tests for negative indices. I wonder if > the original tests are wrong, or the behavior of this function in Spark was > changed since the tests were written. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6285) Function ARRAY_INSERT produces an incorrect result for negative indices
[ https://issues.apache.org/jira/browse/CALCITE-6285?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6285: Attachment: (was: image-2024-03-01-10-31-55-853.png) > Function ARRAY_INSERT produces an incorrect result for negative indices > --- > > Key: CALCITE-6285 > URL: https://issues.apache.org/jira/browse/CALCITE-6285 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.36.0 >Reporter: Mihai Budiu >Priority: Minor > > Here is a test taken from the Spark documentation page: > https://spark.apache.org/docs/latest/api/sql/index.html#array_insert > {code} > SELECT array_insert(array(5, 3, 2, 1), -4, 4); > [5,4,3,2,1] > {code} > The result produced by Calcite is: > [4,5,3,2,1] > The strange thing is that there are tests for negative indices. I wonder if > the original tests are wrong, or the behavior of this function in Spark was > changed since the tests were written. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6285) Function ARRAY_INSERT produces an incorrect result for negative indices
[ https://issues.apache.org/jira/browse/CALCITE-6285?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6285: Attachment: image-2024-03-01-10-31-55-853.png > Function ARRAY_INSERT produces an incorrect result for negative indices > --- > > Key: CALCITE-6285 > URL: https://issues.apache.org/jira/browse/CALCITE-6285 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.36.0 >Reporter: Mihai Budiu >Priority: Minor > Attachments: image-2024-03-01-10-31-55-853.png > > > Here is a test taken from the Spark documentation page: > https://spark.apache.org/docs/latest/api/sql/index.html#array_insert > {code} > SELECT array_insert(array(5, 3, 2, 1), -4, 4); > [5,4,3,2,1] > {code} > The result produced by Calcite is: > [4,5,3,2,1] > The strange thing is that there are tests for negative indices. I wonder if > the original tests are wrong, or the behavior of this function in Spark was > changed since the tests were written. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6278) Add REGEXP function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6278: Description: Add Spark functions that have been implemented but have different OperandTypes/Returns. Add Function [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp] Since this function has the same implementation as the Big Query [REGEXP_CONTAINS|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#regexp_contains] function. the implementation can be directly reused. was: Add Spark functions that have been implemented but have different OperandTypes/Returns. Add Function [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp] > Add REGEXP function (enabled in Spark library) > -- > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp] > Since this function has the same implementation as the Big Query > [REGEXP_CONTAINS|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#regexp_contains] > function. the implementation can be directly reused. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6260) Add already implemented functions in Spark library
[ https://issues.apache.org/jira/browse/CALCITE-6260?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6260: Attachment: (was: image-2024-02-22-21-09-22-394.png) > Add already implemented functions in Spark library > -- > > Key: CALCITE-6260 > URL: https://issues.apache.org/jira/browse/CALCITE-6260 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Spark Functions Link:[https://spark.apache.org/docs/latest/api/sql/index.html] > Add function List: > * CHR > * CONCAT_WS > * REGEXP > * REVERSE > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6278) Add REGEXP function (enabled in Spark library)
EveyWu created CALCITE-6278: Summary: Add REGEXP function (enabled in Spark library) Key: CALCITE-6278 URL: https://issues.apache.org/jira/browse/CALCITE-6278 Project: Calcite Issue Type: Improvement Reporter: EveyWu Add Spark functions that have been implemented but have different OperandTypes/Returns. Add Function [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp] -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6278) Add REGEXP function (enabled in Spark library)
EveyWu created CALCITE-6278: Summary: Add REGEXP function (enabled in Spark library) Key: CALCITE-6278 URL: https://issues.apache.org/jira/browse/CALCITE-6278 Project: Calcite Issue Type: Improvement Reporter: EveyWu Add Spark functions that have been implemented but have different OperandTypes/Returns. Add Function [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp] -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6260) Add already implemented functions in Spark library
[ https://issues.apache.org/jira/browse/CALCITE-6260?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17819645#comment-17819645 ] EveyWu commented on CALCITE-6260: -- [~caicancai] Thank you for your advice, I will split multiple jira. > Add already implemented functions in Spark library > -- > > Key: CALCITE-6260 > URL: https://issues.apache.org/jira/browse/CALCITE-6260 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-02-22-21-09-22-394.png > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Spark Functions Link:[https://spark.apache.org/docs/latest/api/sql/index.html] > Add function List: > * CHR > * CONCAT_WS > * REGEXP > * REVERSE > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6260) Add already implemented functions in Spark library
[ https://issues.apache.org/jira/browse/CALCITE-6260?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6260: Attachment: image-2024-02-22-21-09-22-394.png > Add already implemented functions in Spark library > -- > > Key: CALCITE-6260 > URL: https://issues.apache.org/jira/browse/CALCITE-6260 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-02-22-21-09-22-394.png > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Spark Functions Link:[https://spark.apache.org/docs/latest/api/sql/index.html] > Add function List: > * CHR > * CONCAT_WS > * REGEXP > * REVERSE > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6259) The implementation of the Log library operator does not match the actual dialect behavior.
[ https://issues.apache.org/jira/browse/CALCITE-6259?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6259: Attachment: (was: image-2024-02-12-21-52-45-462.png) > The implementation of the Log library operator does not match the actual > dialect behavior. > -- > > Key: CALCITE-6259 > URL: https://issues.apache.org/jira/browse/CALCITE-6259 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.36.0 >Reporter: Caican Cai >Priority: Major > Fix For: 1.37.0 > > Attachments: 302662660-27b21670-5364-463c-b6dc-d750c46d7cd1.png, > 302663876-91173a60-695d-409e-b325-3f91655c6d0d.png > > > The implementation of the Log library operator does not match the actual > dialect behavior. > For example, when log10(0) returns null in mysql and spark, but log10(0) > returns error in postgres, neither is calcite's -Intity > {code:java} > postgres=# select log10(0); > ERROR: cannot take logarithm of zero > postgres=# select log(2,0); > ERROR: cannot take logarithm of zero > {code} -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6260) Add already implemented functions in Spark library
EveyWu created CALCITE-6260: Summary: Add already implemented functions in Spark library Key: CALCITE-6260 URL: https://issues.apache.org/jira/browse/CALCITE-6260 Project: Calcite Issue Type: Improvement Reporter: EveyWu Add Spark functions that have been implemented but have different OperandTypes/Returns. Spark Functions Link:[https://spark.apache.org/docs/latest/api/sql/index.html] Add function List: * CHR * CONCAT_WS * REGEXP * REVERSE -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6260) Add already implemented functions in Spark library
EveyWu created CALCITE-6260: Summary: Add already implemented functions in Spark library Key: CALCITE-6260 URL: https://issues.apache.org/jira/browse/CALCITE-6260 Project: Calcite Issue Type: Improvement Reporter: EveyWu Add Spark functions that have been implemented but have different OperandTypes/Returns. Spark Functions Link:[https://spark.apache.org/docs/latest/api/sql/index.html] Add function List: * CHR * CONCAT_WS * REGEXP * REVERSE -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6259) Return the result with the log10 and log functions of argument 0
[ https://issues.apache.org/jira/browse/CALCITE-6259?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17816612#comment-17816612 ] EveyWu commented on CALCITE-6259: -- log10 is defined in the SqlStdOperatorTable, In my opinion, there is no guarantee that the execution results of different engines will be consistent. Calcite defines its criteria for execution results, which can be similar to common engine results, but exact consistency is not guaranteed. > Return the result with the log10 and log functions of argument 0 > > > Key: CALCITE-6259 > URL: https://issues.apache.org/jira/browse/CALCITE-6259 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.36.0 >Reporter: Caican Cai >Priority: Major > Fix For: 1.37.0 > > Attachments: 302662660-27b21670-5364-463c-b6dc-d750c46d7cd1.png, > 302663876-91173a60-695d-409e-b325-3f91655c6d0d.png, > image-2024-02-12-21-52-45-462.png > > > When log10(0) returns null in mysql and spark, but log10(0) returns error in > postgres, neither is calcite's -Intity > {code:java} > postgres=# select log10(0); > ERROR: cannot take logarithm of zero > postgres=# select log(2,0); > ERROR: cannot take logarithm of zero > {code} -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6259) Return the result with the log10 and log functions of argument 0
[ https://issues.apache.org/jira/browse/CALCITE-6259?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6259: Attachment: image-2024-02-12-21-52-45-462.png > Return the result with the log10 and log functions of argument 0 > > > Key: CALCITE-6259 > URL: https://issues.apache.org/jira/browse/CALCITE-6259 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.36.0 >Reporter: Caican Cai >Priority: Major > Fix For: 1.37.0 > > Attachments: 302662660-27b21670-5364-463c-b6dc-d750c46d7cd1.png, > 302663876-91173a60-695d-409e-b325-3f91655c6d0d.png, > image-2024-02-12-21-52-45-462.png > > > When log10(0) returns null in mysql and spark, but log10(0) returns error in > postgres, neither is calcite's -Intity > {code:java} > postgres=# select log10(0); > ERROR: cannot take logarithm of zero > postgres=# select log(2,0); > ERROR: cannot take logarithm of zero > {code} -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6259) Return the result with the log10 and log functions of argument 0
[ https://issues.apache.org/jira/browse/CALCITE-6259?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17816489#comment-17816489 ] EveyWu edited comment on CALCITE-6259 at 2/12/24 5:25 AM: --- hi [~caicancai] I think we can optimize the log function calculation. According to the [[document]|https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions#log], the current Caclite calculation for critical conditions does not conform to the behavior of bigquery. Such as: LOG(X [, Y]), LOG(0, Y) for bigquery is Error, but for Calcite is -Infinity; LOG10(0) for bigquery is Error, but for Calcite is -Infinity. I also think that the background of this issue should be confirmed with [~tanclary] was (Author: eveywu): hi [~caicancai] I think we can optimize the log function calculation. According to the [[document|https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions#log]|#log]], the current Caclite calculation for critical conditions does not conform to the behavior of bigquery. Such as: LOG(X [, Y]), LOG(0, Y) for bigquery is Error, but for Calcite is -Infinity; LOG10(0) for bigquery is Error, but for Calcite is -Infinity. I also think that the background of this issue should be confirmed with [~tanclary] > Return the result with the log10 and log functions of argument 0 > > > Key: CALCITE-6259 > URL: https://issues.apache.org/jira/browse/CALCITE-6259 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.36.0 >Reporter: Caican Cai >Priority: Major > Fix For: 1.37.0 > > Attachments: 302662660-27b21670-5364-463c-b6dc-d750c46d7cd1.png, > 302663876-91173a60-695d-409e-b325-3f91655c6d0d.png > > > When log10(0) returns null in mysql and spark, but log10(0) returns error in > postgres, neither is calcite's -Intity > {code:java} > postgres=# select log10(0); > ERROR: cannot take logarithm of zero > postgres=# select log(2,0); > ERROR: cannot take logarithm of zero > {code} -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6259) Return the result with the log10 and log functions of argument 0
[ https://issues.apache.org/jira/browse/CALCITE-6259?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17816489#comment-17816489 ] EveyWu commented on CALCITE-6259: -- hi [~caicancai] I think we can optimize the log function calculation. According to the [document|[https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions#log]], the current Caclite calculation for critical conditions does not conform to the behavior of bigquery. Such as: LOG(X [, Y]), LOG(0, Y) for bigquery is Error, but for Calcite is -Infinity; LOG10(0) for bigquery is Error, but for Calcite is -Infinity. I also think that the background of this issue should be confirmed with [~tanclary] > Return the result with the log10 and log functions of argument 0 > > > Key: CALCITE-6259 > URL: https://issues.apache.org/jira/browse/CALCITE-6259 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.36.0 >Reporter: Caican Cai >Priority: Major > Fix For: 1.37.0 > > Attachments: 302662660-27b21670-5364-463c-b6dc-d750c46d7cd1.png, > 302663876-91173a60-695d-409e-b325-3f91655c6d0d.png > > > When log10(0) returns null in mysql and spark, but log10(0) returns error in > postgres, neither is calcite's -Intity > {code:java} > postgres=# select log10(0); > ERROR: cannot take logarithm of zero > postgres=# select log(2,0); > ERROR: cannot take logarithm of zero > {code} -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6259) Return the result with the log10 and log functions of argument 0
[ https://issues.apache.org/jira/browse/CALCITE-6259?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17816489#comment-17816489 ] EveyWu edited comment on CALCITE-6259 at 2/12/24 5:24 AM: --- hi [~caicancai] I think we can optimize the log function calculation. According to the [[document|https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions#log]|#log]], the current Caclite calculation for critical conditions does not conform to the behavior of bigquery. Such as: LOG(X [, Y]), LOG(0, Y) for bigquery is Error, but for Calcite is -Infinity; LOG10(0) for bigquery is Error, but for Calcite is -Infinity. I also think that the background of this issue should be confirmed with [~tanclary] was (Author: eveywu): hi [~caicancai] I think we can optimize the log function calculation. According to the [document|[https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions#log]], the current Caclite calculation for critical conditions does not conform to the behavior of bigquery. Such as: LOG(X [, Y]), LOG(0, Y) for bigquery is Error, but for Calcite is -Infinity; LOG10(0) for bigquery is Error, but for Calcite is -Infinity. I also think that the background of this issue should be confirmed with [~tanclary] > Return the result with the log10 and log functions of argument 0 > > > Key: CALCITE-6259 > URL: https://issues.apache.org/jira/browse/CALCITE-6259 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.36.0 >Reporter: Caican Cai >Priority: Major > Fix For: 1.37.0 > > Attachments: 302662660-27b21670-5364-463c-b6dc-d750c46d7cd1.png, > 302663876-91173a60-695d-409e-b325-3f91655c6d0d.png > > > When log10(0) returns null in mysql and spark, but log10(0) returns error in > postgres, neither is calcite's -Intity > {code:java} > postgres=# select log10(0); > ERROR: cannot take logarithm of zero > postgres=# select log(2,0); > ERROR: cannot take logarithm of zero > {code} -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6257) StarRocks dialect implementation
[ https://issues.apache.org/jira/browse/CALCITE-6257?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17816467#comment-17816467 ] EveyWu commented on CALCITE-6257: -- hi [~caicancai] Similar to Presto and Trino, I don't think it's necessary to implement two sets of dialects at the moment. Since Starrocks is more widely used currently, I chose to implement the StarRocks Dialect. > StarRocks dialect implementation > > > Key: CALCITE-6257 > URL: https://issues.apache.org/jira/browse/CALCITE-6257 > Project: Calcite > Issue Type: New Feature >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > > StarRocks has an MPP architecture and is equipped with a fully vectorized > execution engine, a columnar storage engine that supports real-time updates. > [https://docs.starrocks.io/docs/introduction/StarRocks_intro/] > At present, StarRocks has a wide range of applications. Implementing dialect > in Calcite will be valuable for many bi-services. > It closely follows MySQL but has enough differences to warrant a separate > dialect in Calcite. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6258) Map value constructor is unparsed incorrectly for PrestoSqlDialect
EveyWu created CALCITE-6258: Summary: Map value constructor is unparsed incorrectly for PrestoSqlDialect Key: CALCITE-6258 URL: https://issues.apache.org/jira/browse/CALCITE-6258 Project: Calcite Issue Type: Bug Reporter: EveyWu Presto Map function:{{{}map{}}}({_}array{_}, {_}array{_}) https://teradata.github.io/presto/docs/current/functions/map.html -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6258) Map value constructor is unparsed incorrectly for PrestoSqlDialect
EveyWu created CALCITE-6258: Summary: Map value constructor is unparsed incorrectly for PrestoSqlDialect Key: CALCITE-6258 URL: https://issues.apache.org/jira/browse/CALCITE-6258 Project: Calcite Issue Type: Bug Reporter: EveyWu Presto Map function:{{{}map{}}}({_}array{_}, {_}array{_}) https://teradata.github.io/presto/docs/current/functions/map.html -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6257) StarRocks dialect implementation
EveyWu created CALCITE-6257: Summary: StarRocks dialect implementation Key: CALCITE-6257 URL: https://issues.apache.org/jira/browse/CALCITE-6257 Project: Calcite Issue Type: New Feature Reporter: EveyWu StarRocks has an MPP architecture and is equipped with a fully vectorized execution engine, a columnar storage engine that supports real-time updates. [https://docs.starrocks.io/docs/introduction/StarRocks_intro/] At present, StarRocks has a wide range of applications. Implementing dialect in Calcite will be valuable for many bi-services. It closely follows MySQL but has enough differences to warrant a separate dialect in Calcite. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6257) StarRocks dialect implementation
EveyWu created CALCITE-6257: Summary: StarRocks dialect implementation Key: CALCITE-6257 URL: https://issues.apache.org/jira/browse/CALCITE-6257 Project: Calcite Issue Type: New Feature Reporter: EveyWu StarRocks has an MPP architecture and is equipped with a fully vectorized execution engine, a columnar storage engine that supports real-time updates. [https://docs.starrocks.io/docs/introduction/StarRocks_intro/] At present, StarRocks has a wide range of applications. Implementing dialect in Calcite will be valuable for many bi-services. It closely follows MySQL but has enough differences to warrant a separate dialect in Calcite. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6246) Unable to find the correct function signature when using named parameters with omitting arguments
[ https://issues.apache.org/jira/browse/CALCITE-6246?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17815107#comment-17815107 ] EveyWu edited comment on CALCITE-6246 at 2/7/24 5:51 AM: -- hi [~hackergin] , I can't reproduce this case. In my opinion, function validation can obtain the correct parameter type. Details can be viewed: SqlFunction#deriveType {code:java} final List argTypes = constructArgTypeList(validator, scope,call, args, convertRowArgToColumnList); //.. throw validator.handleUnresolvedFunction(call, this, argTypes, argNames); {code} was (Author: eveywu): I can't reproduce this case. In my opinion, function validation can obtain the correct parameter type. Details can be viewed: SqlFunction#deriveType {code:java} final List argTypes = constructArgTypeList(validator, scope,call, args, convertRowArgToColumnList); //.. throw validator.handleUnresolvedFunction(call, this, argTypes, argNames); {code} > Unable to find the correct function signature when using named parameters > with omitting arguments > - > > Key: CALCITE-6246 > URL: https://issues.apache.org/jira/browse/CALCITE-6246 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Feng Jin >Assignee: Feng Jin >Priority: Major > > When we use named parameters, if a non-mandatory parameter is omitted, it > will be automatically filled with a DEFAULT node. However, the default return > type of the DEFAULT node is ANY. This will cause some issues when inferring > the function signature. > Take the unit test below as an example. > org.apache.calcite.test.UdfTest#testScalarFunctionWithParameterWithOptional > {code:java} > // Some comments here > @Test void testScalarFunctionWithParameterWithOptional() { > final String sql = "select \"adhoc\".my_det_plus() as p\n" > + "from \"adhoc\".EMPLOYEES"; > final AtomicInteger c = > Smalls.MyDeterministicPlusFunction.INSTANCE_COUNT.get(); > final int before = c.get(); > withUdf() > .with("caseSensitive", false) > .query(sql).returnsUnordered("P=110", > "P=120", > "P=110", > "P=110"); > final int after = c.get(); > assertThat(after, is(before + 1)); > } > {code} > We will get the following error: > {code:shell} > java.sql.SQLException: Error while executing SQL "select > "adhoc".my_det_plus() as p > from "adhoc".EMPLOYEES": From line 1, column 16 to line 1, column 28: No > match found for function signature MY_DET_PLUS() > at org.apache.calcite.avatica.Helper.createException(Helper.java:56) > at org.apache.calcite.avatica.Helper.createException(Helper.java:41) > at > org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:164) > at > org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228) > at > org.apache.calcite.test.CalciteAssert.assertQuery(CalciteAssert.java:543) > at > org.apache.calcite.test.CalciteAssert$AssertQuery.lambda$returns$1(CalciteAssert.java:1455) > at > org.apache.calcite.test.CalciteAssert$AssertQuery.withConnection(CalciteAssert.java:1394) > at > org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1453) > at > org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1443) > at > org.apache.calcite.test.CalciteAssert$AssertQuery.returnsUnordered(CalciteAssert.java:1465) > at > org.apache.calcite.test.UdfTest.testScalarFunctionWithParameterWithOptional(UdfTest.java:242) > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) > at > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > at java.lang.reflect.Method.invoke(Method.java:498) > at > org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:727) > at > org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60) > at > org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131) > at > org.junit.jupiter.engine.extension.SameThreadTimeoutInvocation.proceed(SameThreadTimeoutInvocation.java:45) > at > org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:156) > at > org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:147) > at > org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:86) > at > org.junit.jupiter
[jira] [Comment Edited] (CALCITE-6246) Unable to find the correct function signature when using named parameters with omitting arguments
[ https://issues.apache.org/jira/browse/CALCITE-6246?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17815107#comment-17815107 ] EveyWu edited comment on CALCITE-6246 at 2/7/24 5:50 AM: -- I can't reproduce this case. In my opinion, function validation can obtain the correct parameter type. Details can be viewed: SqlFunction#deriveType {code:java} final List argTypes = constructArgTypeList(validator, scope,call, args, convertRowArgToColumnList); //.. throw validator.handleUnresolvedFunction(call, this, argTypes, argNames); {code} was (Author: eveywu): I can't reproduce this case. In my opinion, function validation can obtain the correct parameter type. Details can be viewed: SqlFunction#deriveType ``` final List argTypes = constructArgTypeList(validator, scope,call, args, convertRowArgToColumnList); throw validator.handleUnresolvedFunction(call, this, argTypes, argNames); ``` > Unable to find the correct function signature when using named parameters > with omitting arguments > - > > Key: CALCITE-6246 > URL: https://issues.apache.org/jira/browse/CALCITE-6246 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Feng Jin >Assignee: Feng Jin >Priority: Major > > When we use named parameters, if a non-mandatory parameter is omitted, it > will be automatically filled with a DEFAULT node. However, the default return > type of the DEFAULT node is ANY. This will cause some issues when inferring > the function signature. > Take the unit test below as an example. > org.apache.calcite.test.UdfTest#testScalarFunctionWithParameterWithOptional > {code:java} > // Some comments here > @Test void testScalarFunctionWithParameterWithOptional() { > final String sql = "select \"adhoc\".my_det_plus() as p\n" > + "from \"adhoc\".EMPLOYEES"; > final AtomicInteger c = > Smalls.MyDeterministicPlusFunction.INSTANCE_COUNT.get(); > final int before = c.get(); > withUdf() > .with("caseSensitive", false) > .query(sql).returnsUnordered("P=110", > "P=120", > "P=110", > "P=110"); > final int after = c.get(); > assertThat(after, is(before + 1)); > } > {code} > We will get the following error: > {code:shell} > java.sql.SQLException: Error while executing SQL "select > "adhoc".my_det_plus() as p > from "adhoc".EMPLOYEES": From line 1, column 16 to line 1, column 28: No > match found for function signature MY_DET_PLUS() > at org.apache.calcite.avatica.Helper.createException(Helper.java:56) > at org.apache.calcite.avatica.Helper.createException(Helper.java:41) > at > org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:164) > at > org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228) > at > org.apache.calcite.test.CalciteAssert.assertQuery(CalciteAssert.java:543) > at > org.apache.calcite.test.CalciteAssert$AssertQuery.lambda$returns$1(CalciteAssert.java:1455) > at > org.apache.calcite.test.CalciteAssert$AssertQuery.withConnection(CalciteAssert.java:1394) > at > org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1453) > at > org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1443) > at > org.apache.calcite.test.CalciteAssert$AssertQuery.returnsUnordered(CalciteAssert.java:1465) > at > org.apache.calcite.test.UdfTest.testScalarFunctionWithParameterWithOptional(UdfTest.java:242) > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) > at > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > at java.lang.reflect.Method.invoke(Method.java:498) > at > org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:727) > at > org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60) > at > org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131) > at > org.junit.jupiter.engine.extension.SameThreadTimeoutInvocation.proceed(SameThreadTimeoutInvocation.java:45) > at > org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:156) > at > org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:147) > at > org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:86) > at > org.junit.jupiter.engine.execution.InterceptingExe
[jira] [Commented] (CALCITE-6246) Unable to find the correct function signature when using named parameters with omitting arguments
[ https://issues.apache.org/jira/browse/CALCITE-6246?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17815107#comment-17815107 ] EveyWu commented on CALCITE-6246: -- I can't reproduce this case. In my opinion, function validation can obtain the correct parameter type. Details can be viewed: SqlFunction#deriveType ``` final List argTypes = constructArgTypeList(validator, scope,call, args, convertRowArgToColumnList); throw validator.handleUnresolvedFunction(call, this, argTypes, argNames); ``` > Unable to find the correct function signature when using named parameters > with omitting arguments > - > > Key: CALCITE-6246 > URL: https://issues.apache.org/jira/browse/CALCITE-6246 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Feng Jin >Assignee: Feng Jin >Priority: Major > > When we use named parameters, if a non-mandatory parameter is omitted, it > will be automatically filled with a DEFAULT node. However, the default return > type of the DEFAULT node is ANY. This will cause some issues when inferring > the function signature. > Take the unit test below as an example. > org.apache.calcite.test.UdfTest#testScalarFunctionWithParameterWithOptional > {code:java} > // Some comments here > @Test void testScalarFunctionWithParameterWithOptional() { > final String sql = "select \"adhoc\".my_det_plus() as p\n" > + "from \"adhoc\".EMPLOYEES"; > final AtomicInteger c = > Smalls.MyDeterministicPlusFunction.INSTANCE_COUNT.get(); > final int before = c.get(); > withUdf() > .with("caseSensitive", false) > .query(sql).returnsUnordered("P=110", > "P=120", > "P=110", > "P=110"); > final int after = c.get(); > assertThat(after, is(before + 1)); > } > {code} > We will get the following error: > {code:shell} > java.sql.SQLException: Error while executing SQL "select > "adhoc".my_det_plus() as p > from "adhoc".EMPLOYEES": From line 1, column 16 to line 1, column 28: No > match found for function signature MY_DET_PLUS() > at org.apache.calcite.avatica.Helper.createException(Helper.java:56) > at org.apache.calcite.avatica.Helper.createException(Helper.java:41) > at > org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:164) > at > org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:228) > at > org.apache.calcite.test.CalciteAssert.assertQuery(CalciteAssert.java:543) > at > org.apache.calcite.test.CalciteAssert$AssertQuery.lambda$returns$1(CalciteAssert.java:1455) > at > org.apache.calcite.test.CalciteAssert$AssertQuery.withConnection(CalciteAssert.java:1394) > at > org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1453) > at > org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1443) > at > org.apache.calcite.test.CalciteAssert$AssertQuery.returnsUnordered(CalciteAssert.java:1465) > at > org.apache.calcite.test.UdfTest.testScalarFunctionWithParameterWithOptional(UdfTest.java:242) > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) > at > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > at java.lang.reflect.Method.invoke(Method.java:498) > at > org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:727) > at > org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60) > at > org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131) > at > org.junit.jupiter.engine.extension.SameThreadTimeoutInvocation.proceed(SameThreadTimeoutInvocation.java:45) > at > org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:156) > at > org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:147) > at > org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:86) > at > org.junit.jupiter.engine.execution.InterceptingExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(InterceptingExecutableInvoker.java:103) > at > org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.lambda$invoke$0(InterceptingExecutableInvoker.java:93) > at > org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106) > at > org.junit.jupiter.engine.executio
[jira] [Commented] (CALCITE-6241) Enable a few existing functions to Spark library
[ https://issues.apache.org/jira/browse/CALCITE-6241?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17815032#comment-17815032 ] EveyWu commented on CALCITE-6241: -- Hi [~shenlang] Since many functions cannot be placed in the summary, they have been listed in the commit message and Jira Description. > Enable a few existing functions to Spark library > > > Key: CALCITE-6241 > URL: https://issues.apache.org/jira/browse/CALCITE-6241 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-02-07-07-08-59-868.png > > > Add Spark as a supported library for functions that have already been > implemented for other libraries. > Spark Functions > Link:[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] > Enable function List: > * DECODE > * NVL > * IFNULL > * LEN > * LENGTH > * LPAD > * RPAD > * LTRIM > * RTRIM > * ENDSWITH > * STARTSWITH > * GREATEST > * LEAST > * TRANSLATE > * BOOL_AND > * BOOL_OR > * DATE_FROM_UNIX_DATE > * UNIX_DATE > * LEFT > * REPEAT > * RIGHT > * SPACE > * TIMESTAMP_SECONDS > * TIMESTAMP_MILLIS > * TIMESTAMP_MICROS > * UNIX_SECONDS > * UNIX_MILLIS > * UNIX_MICROS > * MD5 > * SHA1 > * POW > > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6241) Enable a few existing functions to Spark library
[ https://issues.apache.org/jira/browse/CALCITE-6241?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6241: Attachment: image-2024-02-07-07-08-59-868.png > Enable a few existing functions to Spark library > > > Key: CALCITE-6241 > URL: https://issues.apache.org/jira/browse/CALCITE-6241 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-02-07-07-08-59-868.png > > > Add Spark as a supported library for functions that have already been > implemented for other libraries. > Spark Functions > Link:[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] > Enable function List: > * DECODE > * NVL > * IFNULL > * LEN > * LENGTH > * LPAD > * RPAD > * LTRIM > * RTRIM > * ENDSWITH > * STARTSWITH > * GREATEST > * LEAST > * TRANSLATE > * BOOL_AND > * BOOL_OR > * DATE_FROM_UNIX_DATE > * UNIX_DATE > * LEFT > * REPEAT > * RIGHT > * SPACE > * TIMESTAMP_SECONDS > * TIMESTAMP_MILLIS > * TIMESTAMP_MICROS > * UNIX_SECONDS > * UNIX_MILLIS > * UNIX_MICROS > * MD5 > * SHA1 > * POW > > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6241) Enable a few existing functions to Spark library
[ https://issues.apache.org/jira/browse/CALCITE-6241?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6241: Attachment: (was: image-2024-02-06-17-11-31-144.png) > Enable a few existing functions to Spark library > > > Key: CALCITE-6241 > URL: https://issues.apache.org/jira/browse/CALCITE-6241 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > > Add Spark as a supported library for functions that have already been > implemented for other libraries. > Spark Functions > Link:[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] > Enable function List: > * DECODE > * NVL > * IFNULL > * LEN > * LENGTH > * LPAD > * RPAD > * LTRIM > * RTRIM > * ENDSWITH > * STARTSWITH > * GREATEST > * LEAST > * TRANSLATE > * BOOL_AND > * BOOL_OR > * DATE_FROM_UNIX_DATE > * UNIX_DATE > * LEFT > * REPEAT > * RIGHT > * SPACE > * TIMESTAMP_SECONDS > * TIMESTAMP_MILLIS > * TIMESTAMP_MICROS > * UNIX_SECONDS > * UNIX_MILLIS > * UNIX_MICROS > * MD5 > * SHA1 > * POW > > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6241) Enable a few existing functions to Spark library
[ https://issues.apache.org/jira/browse/CALCITE-6241?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17814696#comment-17814696 ] EveyWu commented on CALCITE-6241: -- [~hongyuguo] thanks for your advice,Comparing the duplicated PR, the following additional functions are added * DATE_FROM_UNIX_DATE * TIMESTAMP_MICROS * TIMESTAMP_MILLIS * TIMESTAMP_SECONDS And other functions are not included. The main differences are as follows: * {*}CONVERT_TIMEZONE{*}: OperandType not matched, Spark CONVERT_TIMEZONE([sourceTz, ]targetTz, sourceTs), PostgreSQL CONVERT_TIMEZONE(tz1, tz2, datetime) * {*}SPLIT{*}: OperandType not matched, Spark SPLIT(str, regex, limit), BigQuery SPLIT(string [, delimiter]); * {*}REGEXP_REPLACE{*}: OperandType not matched, Spark REGEXP_REPLACE(str, regexp, rep[, position]), BigQuery REGEXP_REPLACE(value, regexp, rep [, pos [, occurrence [, matchType]]]); * {*}CONCAT_WS{*}:OperandType not matched, Spark CONCAT_WS(sep[, str | array(str)]+), MySQL&PostgreSQL CONCAT_WS(separator, str1 [, string ]*); * {*}TO_DATE{*}: OperandType not matched, Spark TO_DATE(date_str[, fmt]), PostgreSQL TO_DATE(string1, string2); * {*}TO_TIMESTAMP{*}: OperandType not matched; * {*}CHR{*}: Spark returns ASCII code, same as CHAR, BigQuery returns UTF-8 code; * {*}LOG{*}: OperandType not matched, Spark log(base, expr), BigQuery LOG(value [, value2]); > Enable a few existing functions to Spark library > > > Key: CALCITE-6241 > URL: https://issues.apache.org/jira/browse/CALCITE-6241 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-02-06-17-11-31-144.png > > > Add Spark as a supported library for functions that have already been > implemented for other libraries. > Spark Functions > Link:[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] > Enable function List: > * DECODE > * NVL > * IFNULL > * LEN > * LENGTH > * LPAD > * RPAD > * LTRIM > * RTRIM > * ENDSWITH > * STARTSWITH > * GREATEST > * LEAST > * TRANSLATE > * BOOL_AND > * BOOL_OR > * DATE_FROM_UNIX_DATE > * UNIX_DATE > * LEFT > * REPEAT > * RIGHT > * SPACE > * TIMESTAMP_SECONDS > * TIMESTAMP_MILLIS > * TIMESTAMP_MICROS > * UNIX_SECONDS > * UNIX_MILLIS > * UNIX_MICROS > * MD5 > * SHA1 > * POW > > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6241) Enable a few existing functions to Spark library
[ https://issues.apache.org/jira/browse/CALCITE-6241?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6241: Attachment: image-2024-02-06-17-11-31-144.png > Enable a few existing functions to Spark library > > > Key: CALCITE-6241 > URL: https://issues.apache.org/jira/browse/CALCITE-6241 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-02-06-17-11-31-144.png > > > Add Spark as a supported library for functions that have already been > implemented for other libraries. > Spark Functions > Link:[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] > Enable function List: > * DECODE > * NVL > * IFNULL > * LEN > * LENGTH > * LPAD > * RPAD > * LTRIM > * RTRIM > * ENDSWITH > * STARTSWITH > * GREATEST > * LEAST > * TRANSLATE > * BOOL_AND > * BOOL_OR > * DATE_FROM_UNIX_DATE > * UNIX_DATE > * LEFT > * REPEAT > * RIGHT > * SPACE > * TIMESTAMP_SECONDS > * TIMESTAMP_MILLIS > * TIMESTAMP_MICROS > * UNIX_SECONDS > * UNIX_MILLIS > * UNIX_MICROS > * MD5 > * SHA1 > * POW > > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6241) Enable a few existing functions to Spark library
[ https://issues.apache.org/jira/browse/CALCITE-6241?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6241: Description: Add Spark as a supported library for functions that have already been implemented for other libraries. Spark Functions Link:[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] Enable function List: * DECODE * NVL * IFNULL * LEN * LENGTH * LPAD * RPAD * LTRIM * RTRIM * ENDSWITH * STARTSWITH * GREATEST * LEAST * TRANSLATE * BOOL_AND * BOOL_OR * DATE_FROM_UNIX_DATE * UNIX_DATE * LEFT * REPEAT * RIGHT * SPACE * TIMESTAMP_SECONDS * TIMESTAMP_MILLIS * TIMESTAMP_MICROS * UNIX_SECONDS * UNIX_MILLIS * UNIX_MICROS * MD5 * SHA1 * POW was: Add Spark as a supported library for functions that have already been implemented for other libraries. Spark Functions Link:[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] Add function List: * DECODE * NVL * IFNULL * LEN * LENGTH * LPAD * RPAD * LTRIM * RTRIM * ENDSWITH * STARTSWITH * GREATEST * LEAST * TRANSLATE * BOOL_AND * BOOL_OR * DATE_FROM_UNIX_DATE * UNIX_DATE * LEFT * REPEAT * RIGHT * SPACE * TIMESTAMP_SECONDS * TIMESTAMP_MILLIS * TIMESTAMP_MICROS * UNIX_SECONDS * UNIX_MILLIS * UNIX_MICROS * MD5 * SHA1 * POW > Enable a few existing functions to Spark library > > > Key: CALCITE-6241 > URL: https://issues.apache.org/jira/browse/CALCITE-6241 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > > Add Spark as a supported library for functions that have already been > implemented for other libraries. > Spark Functions > Link:[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] > Enable function List: > * DECODE > * NVL > * IFNULL > * LEN > * LENGTH > * LPAD > * RPAD > * LTRIM > * RTRIM > * ENDSWITH > * STARTSWITH > * GREATEST > * LEAST > * TRANSLATE > * BOOL_AND > * BOOL_OR > * DATE_FROM_UNIX_DATE > * UNIX_DATE > * LEFT > * REPEAT > * RIGHT > * SPACE > * TIMESTAMP_SECONDS > * TIMESTAMP_MILLIS > * TIMESTAMP_MICROS > * UNIX_SECONDS > * UNIX_MILLIS > * UNIX_MICROS > * MD5 > * SHA1 > * POW > > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6241) Enable a few existing functions to Spark library
[ https://issues.apache.org/jira/browse/CALCITE-6241?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6241: Description: Add Spark as a supported library for functions that have already been implemented for other libraries. Spark Functions Link:[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] Add function List: * DECODE * NVL * IFNULL * LEN * LENGTH * LPAD * RPAD * LTRIM * RTRIM * ENDSWITH * STARTSWITH * GREATEST * LEAST * TRANSLATE * BOOL_AND * BOOL_OR * DATE_FROM_UNIX_DATE * UNIX_DATE * LEFT * REPEAT * RIGHT * SPACE * TIMESTAMP_SECONDS * TIMESTAMP_MILLIS * TIMESTAMP_MICROS * UNIX_SECONDS * UNIX_MILLIS * UNIX_MICROS * MD5 * SHA1 * POW was: Add Spark as a supported library for functions that have already been implemented for other libraries. Spark Functions Link:[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] Add function List: * DECODE * NVL * IFNULL * LEN * LENGTH * LPAD * RPAD * LTRIM * RTRIM * ENDSWITH * STARTSWITH * GREATEST * LEAST * TRANSLATE * BOOL_AND * BOOL_OR * UNIX_DATE * LEFT * REPEAT * RIGHT * SPACE * UNIX_SECONDS * UNIX_MILLIS * UNIX_MICROS * MD5 * SHA1 * POW > Enable a few existing functions to Spark library > > > Key: CALCITE-6241 > URL: https://issues.apache.org/jira/browse/CALCITE-6241 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > > Add Spark as a supported library for functions that have already been > implemented for other libraries. > Spark Functions > Link:[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] > Add function List: > * DECODE > * NVL > * IFNULL > * LEN > * LENGTH > * LPAD > * RPAD > * LTRIM > * RTRIM > * ENDSWITH > * STARTSWITH > * GREATEST > * LEAST > * TRANSLATE > * BOOL_AND > * BOOL_OR > * DATE_FROM_UNIX_DATE > * UNIX_DATE > * LEFT > * REPEAT > * RIGHT > * SPACE > * TIMESTAMP_SECONDS > * TIMESTAMP_MILLIS > * TIMESTAMP_MICROS > * UNIX_SECONDS > * UNIX_MILLIS > * UNIX_MICROS > * MD5 > * SHA1 > * POW > > > -- This message was sent by Atlassian Jira (v8.20.10#820010)