[jira] [Assigned] (CALCITE-3415) Supports REGEXP_SUBSTR function
[
https://issues.apache.org/jira/browse/CALCITE-3415?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pranay Parmar reassigned CALCITE-3415:
--
Assignee: Pranay Parmar (was: Danny Chen)
> Supports REGEXP_SUBSTR function
> ---
>
> Key: CALCITE-3415
> URL: https://issues.apache.org/jira/browse/CALCITE-3415
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.21.0
>Reporter: Pranay Parmar
>Assignee: Pranay Parmar
>Priority: Minor
> Labels: pull-request-available
> Time Spent: 3h
> Remaining Estimate: 0h
>
> REGEXP_SUBSTR error :
> {code:java}
> No match found for function signature REGEXP_SUBSTR(, ,
> [, , ]){code}
>
> Example query:
> {code:sql}
> SELECT REGEXP_SUBSTR('chocolate Chip cookies', 'c+.{2}', 1, product_id, 'i')
> FROM public.account{code}
> There are *4* variations of this function with 2, 3, 4 or 5 parameters :
> *1. REGEXP_SUBSTR(, ) [2 params] :*
> {code:sql}
> SELECT REGEXP_SUBSTR('choco chico chipo', 'c+.{2}') FROM foodmart.product
> {code}
> For BigQuery it will be unparsed into :
> {code:sql}
> SELECT REGEXP_EXTRACT('choco chico chipo', 'c+.{2}') FROM foodmart.product
> {code}
> *2. REGEXP_SUBSTR(, , ) [3 params] :*
> {code:sql}
> SELECT REGEXP_SUBSTR('choco chico chipo', 'c+.{2}', 7) FROM foodmart.product
> {code}
> For BigQuery it will be unparsed into :
> {code:sql}
> SELECT REGEXP_EXTRACT(SUBSTR('choco chico chipo', 7), 'c+.{2}') FROM
> foodmart.product
> {code}
> *3. REGEXP_SUBSTR(, , , ) [4 params] :*
> {code:sql}
> SELECT REGEXP_SUBSTR('chocolate chip cookies', 'c+.{2}', 4, 2) FROM
> foodmart.product
> {code}
> For BigQuery it will be unparsed into :
> {code:sql}
> SELECT REGEXP_EXTRACT_ALL(SUBSTR('chocolate chip cookies', 4), 'c+.{2}')
> [OFFSET(3)] FROM foodmart.product
> {code}
> *4. REGEXP_SUBSTR(, , , , ) [5
> params] :*
> {code:sql}
> SELECT REGEXP_SUBSTR('chocolate Chip cookies', 'c+.{2}', 4, 2, 'i') FROM
> foodmart.product
> {code}
> For BigQuery it will be unparsed into :
> {code:sql}
> SELECT REGEXP_EXTRACT_ALL(SUBSTR('chocolate Chip cookies', 4), '(?i)c+.{2}')
> [OFFSET(3)] FROM foodmart.product
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3611) Introduce TIME/TIMESTAMP WITH TIME ZONE types
[ https://issues.apache.org/jira/browse/CALCITE-3611?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17000674#comment-17000674 ] Rui Wang commented on CALCITE-3611: --- Hi Zhenghua, I find this type is also useful for Apache Beam's use cases. In Apache Beam we need both TIMESTAMP_WITHOUT_TIME_ZONE and TIMESTAMP_WITH_TIME_ZONE Please let me know if you want to parallelize this work and I can help on it. This work will also help Apache Beam community. > Introduce TIME/TIMESTAMP WITH TIME ZONE types > - > > Key: CALCITE-3611 > URL: https://issues.apache.org/jira/browse/CALCITE-3611 > Project: Calcite > Issue Type: New Feature > Components: core >Affects Versions: 1.21.0 >Reporter: Zhenghua Gao >Priority: Major > Labels: timestamp > > As we discussed in ML [1], this umbrella issue would introduce TIME/TIMESTAMP > WITH TIME ZONE types in Calcite, and the follow-up works. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-3603) SqlLateralOperator's unparse add additional keyword 'LATERAL' when the inner operator is SqlSnapshot
[
https://issues.apache.org/jira/browse/CALCITE-3603?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17000635#comment-17000635
]
Danny Chen edited comment on CALCITE-3603 at 12/20/19 6:07 AM:
---
Thanks [~julianhyde] ~ I did a little research of standard SQL 2011 and here is
what i found about LATERAL keyword and temporal table join:
{code:xml}
SQL-2011 IWD 9075-2:201?(E) 7.6
Syntax Rules
1) If QSTPS is specified, then:
a) b)
c)
The table identified by the shall be a system-versioned
table.
If BETWEEN is specified and neither SYMMETRIC nor ASYMMETRIC is specified, then
ASYM- METRIC is implicit.
QSTPS shall not contain a or an .
9) Case:
a) If TR is contained in a FC with no intervening , then the scope clause SC of TR is the or innermost that contains FC. The scope of a range
variable of TR is the , , , , and of SC, together with every
that is simply contained in FC and is preceded by TR, and every that is simply contained in FC and is preceded by TR, and the
of all s contained in SC that contain TR. If SC
is the that is the of a simple
table query STQ, then the scope of a range variable of TR also includes the
of STQ.
28) A is possibly non-deterministic if the simply contained
, , , , , or is possibly non- deterministic, or if
the contains a that
does not implicitly or explicitly specify FOR SYSTEM_TIME AS OF
CURRENT_TIMESTAMP.
{code}
So from my side, what i understood is that: yes, there is no direct
relation-ship between LATERAL and temporal table join. So we definitely should
not add an implicitly LATERAL keyword here.
The standard only allows constant(or literal) for , while we support an expression here, so without the LATERAL,
what we need to fix is the scope of the referenced names in the expression.
was (Author: danny0405):
Thanks [~julianhyde] ~ I did a little research standard SQL 2011 and here is
what i found about LATERAL keyword and temporal table join:
{code:xml}
SQL-2011 IWD 9075-2:201?(E) 7.6
Syntax Rules
1) If QSTPS is specified, then:
a) b)
c)
The table identified by the shall be a system-versioned
table.
If BETWEEN is specified and neither SYMMETRIC nor ASYMMETRIC is specified, then
ASYM- METRIC is implicit.
QSTPS shall not contain a or an .
9) Case:
a) If TR is contained in a FC with no intervening , then the scope clause SC of TR is the or innermost that contains FC. The scope of a range
variable of TR is the , , , , and of SC, together with every
that is simply contained in FC and is preceded by TR, and every that is simply contained in FC and is preceded by TR, and the
of all s contained in SC that contain TR. If SC
is the that is the of a simple
table query STQ, then the scope of a range variable of TR also includes the
of STQ.
28) A is possibly non-deterministic if the simply contained
, , , , , or is possibly non- deterministic, or if
the contains a that
does not implicitly or explicitly specify FOR SYSTEM_TIME AS OF
CURRENT_TIMESTAMP.
{code}
So from my side, what i understood is that: yes, there is no direct
relation-ship between LATERAL and temporal table join. So we definitely should
not add an implicitly LATERAL keyword here.
The standard only allows constant(or literal) for , while we support an expression here, so without the LATERAL,
what we need to fix is the scope of the referenced names in the expression.
> SqlLateralOperator's unparse add additional keyword 'LATERAL' when the inner
> operator is SqlSnapshot
>
>
> Key: CALCITE-3603
> URL: https://issues.apache.org/jira/browse/CALCITE-3603
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.21.0
>Reporter: Kevin Zhang
>Assignee: Danny Chen
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.22.0
>
> Time Spent: 20m
> Remaining Estimate: 0h
>
> When joining with a dimension table using the following sql
> {code:sql}
> SELECT
> o.amout, o.currency, r.rate, o.amount * r.rate
> FROM
> Orders AS o
> JOIN LatestRates FOR SYSTEM_TIME AS OF o.proctime AS r
> ON r.currency = o.currency
> {code}
> the unparsed sql is:
> {code:sql}
> SELECT `o`.`amout`, `o`.`currency`, `r`.`rate`, `o`.`amount` * `r`.`rate`
> FROM `Orders` AS `o`
> INNER JOIN LATERAL `LatestRates` FOR SYSTEM_TIME AS OF `o`.`proctime` AS `r`
> ON `r`.`currency` = `o`.`currency`
> {code}
> which has a syntax error because an additional "LATERAL" is added after
> "JOIN".
> The problem lies in SqlLateralOperator's unparse method, if the kind of the
> first operand is SqlSnapshot, we should not write out the operator's name.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-3603) SqlLateralOperator's unparse add additional keyword 'LATERAL' when the inner operator is SqlSnapshot
[
https://issues.apache.org/jira/browse/CALCITE-3603?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17000635#comment-17000635
]
Danny Chen edited comment on CALCITE-3603 at 12/20/19 5:48 AM:
---
Thanks [~julianhyde] ~ I did a little research standard SQL 2011 and here is
what i found about LATERAL keyword and temporal table join:
{code:xml}
SQL-2011 IWD 9075-2:201?(E) 7.6
Syntax Rules
1) If QSTPS is specified, then:
a) b)
c)
The table identified by the shall be a system-versioned
table.
If BETWEEN is specified and neither SYMMETRIC nor ASYMMETRIC is specified, then
ASYM- METRIC is implicit.
QSTPS shall not contain a or an .
9) Case:
a) If TR is contained in a FC with no intervening , then the scope clause SC of TR is the or innermost that contains FC. The scope of a range
variable of TR is the , , , , and of SC, together with every
that is simply contained in FC and is preceded by TR, and every that is simply contained in FC and is preceded by TR, and the
of all s contained in SC that contain TR. If SC
is the that is the of a simple
table query STQ, then the scope of a range variable of TR also includes the
of STQ.
28) A is possibly non-deterministic if the simply contained
, , , , , or is possibly non- deterministic, or if
the contains a that
does not implicitly or explicitly specify FOR SYSTEM_TIME AS OF
CURRENT_TIMESTAMP.
{code}
So from my side, what i understood is that: yes, there is no direct
relation-ship between LATERAL and temporal table join. So we definitely should
not add an implicitly LATERAL keyword here.
The standard only allows constant(or literal) for , while we support an expression here, so without the LATERAL,
what we need to fix is the scope of the referenced names in the expression.
was (Author: danny0405):
Thanks [~julianhyde] ~ I did a little research standard SQL 2011 and here is
what i found about LATERAL keyword and temporal table join:
{code:sql}
SQL-2011 IWD 9075-2:201?(E) 7.6
Syntax Rules
1) If QSTPS is specified, then:
a) b)
c)
The table identified by the shall be a system-versioned
table.
If BETWEEN is specified and neither SYMMETRIC nor ASYMMETRIC is specified, then
ASYM- METRIC is implicit.
QSTPS shall not contain a or an .
9) Case:
a) If TR is contained in a FC with no intervening , then the scope clause SC of TR is the or innermost that contains FC. The scope of a range
variable of TR is the , , , , and of SC, together with every
that is simply contained in FC and is preceded by TR, and every that is simply contained in FC and is preceded by TR, and the
of all s contained in SC that contain TR. If SC
is the that is the of a simple
table query STQ, then the scope of a range variable of TR also includes the
of STQ.
28) A is possibly non-deterministic if the simply contained
, , , , , or is possibly non- deterministic, or if
the contains a that
does not implicitly or explicitly specify FOR SYSTEM_TIME AS OF
CURRENT_TIMESTAMP.
{code}
So from my side, what i understood is that: yes, there is no direct
relation-ship between LATERAL and temporal table join. So we definitely should
not add an implicitly LATERAL keyword here.
The standard only allows constant(or literal) for , while we support an expression here, so with the LATERAL, what
we need to fix is the scope of the referenced names in the expression.
> SqlLateralOperator's unparse add additional keyword 'LATERAL' when the inner
> operator is SqlSnapshot
>
>
> Key: CALCITE-3603
> URL: https://issues.apache.org/jira/browse/CALCITE-3603
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.21.0
>Reporter: Kevin Zhang
>Assignee: Danny Chen
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.22.0
>
> Time Spent: 20m
> Remaining Estimate: 0h
>
> When joining with a dimension table using the following sql
> {code:sql}
> SELECT
> o.amout, o.currency, r.rate, o.amount * r.rate
> FROM
> Orders AS o
> JOIN LatestRates FOR SYSTEM_TIME AS OF o.proctime AS r
> ON r.currency = o.currency
> {code}
> the unparsed sql is:
> {code:sql}
> SELECT `o`.`amout`, `o`.`currency`, `r`.`rate`, `o`.`amount` * `r`.`rate`
> FROM `Orders` AS `o`
> INNER JOIN LATERAL `LatestRates` FOR SYSTEM_TIME AS OF `o`.`proctime` AS `r`
> ON `r`.`currency` = `o`.`currency`
> {code}
> which has a syntax error because an additional "LATERAL" is added after
> "JOIN".
> The problem lies in SqlLateralOperator's unparse method, if the kind of the
> first operand is SqlSnapshot, we should not write out the operator's name.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3603) SqlLateralOperator's unparse add additional keyword 'LATERAL' when the inner operator is SqlSnapshot
[
https://issues.apache.org/jira/browse/CALCITE-3603?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17000635#comment-17000635
]
Danny Chen commented on CALCITE-3603:
-
Thanks [~julianhyde] ~ I did a little research standard SQL 2011 and here is
what i found about LATERAL keyword and temporal table join:
{code:sql}
SQL-2011 IWD 9075-2:201?(E) 7.6
Syntax Rules
1) If QSTPS is specified, then:
a) b)
c)
The table identified by the shall be a system-versioned
table.
If BETWEEN is specified and neither SYMMETRIC nor ASYMMETRIC is specified, then
ASYM- METRIC is implicit.
QSTPS shall not contain a or an .
9) Case:
a) If TR is contained in a FC with no intervening , then the scope clause SC of TR is the or innermost that contains FC. The scope of a range
variable of TR is the , , , , and of SC, together with every
that is simply contained in FC and is preceded by TR, and every that is simply contained in FC and is preceded by TR, and the
of all s contained in SC that contain TR. If SC
is the that is the of a simple
table query STQ, then the scope of a range variable of TR also includes the
of STQ.
28) A is possibly non-deterministic if the simply contained
, , , , , or is possibly non- deterministic, or if
the contains a that
does not implicitly or explicitly specify FOR SYSTEM_TIME AS OF
CURRENT_TIMESTAMP.
{code}
So from my side, what i understood is that: yes, there is no direct
relation-ship between LATERAL and temporal table join. So we definitely should
not add an implicitly LATERAL keyword here.
The standard only allows constant(or literal) for , while we support an expression here, so with the LATERAL, what
we need to fix is the scope of the referenced names in the expression.
> SqlLateralOperator's unparse add additional keyword 'LATERAL' when the inner
> operator is SqlSnapshot
>
>
> Key: CALCITE-3603
> URL: https://issues.apache.org/jira/browse/CALCITE-3603
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.21.0
>Reporter: Kevin Zhang
>Assignee: Danny Chen
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.22.0
>
> Time Spent: 20m
> Remaining Estimate: 0h
>
> When joining with a dimension table using the following sql
> {code:sql}
> SELECT
> o.amout, o.currency, r.rate, o.amount * r.rate
> FROM
> Orders AS o
> JOIN LatestRates FOR SYSTEM_TIME AS OF o.proctime AS r
> ON r.currency = o.currency
> {code}
> the unparsed sql is:
> {code:sql}
> SELECT `o`.`amout`, `o`.`currency`, `r`.`rate`, `o`.`amount` * `r`.`rate`
> FROM `Orders` AS `o`
> INNER JOIN LATERAL `LatestRates` FOR SYSTEM_TIME AS OF `o`.`proctime` AS `r`
> ON `r`.`currency` = `o`.`currency`
> {code}
> which has a syntax error because an additional "LATERAL" is added after
> "JOIN".
> The problem lies in SqlLateralOperator's unparse method, if the kind of the
> first operand is SqlSnapshot, we should not write out the operator's name.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3387) Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is ambiguous" error
[
https://issues.apache.org/jira/browse/CALCITE-3387?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17000630#comment-17000630
]
Jin Xing commented on CALCITE-3387:
---
Thanks for ping me, Chunwei ~
I just added minor comments.
> Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is
> ambiguous" error
> --
>
> Key: CALCITE-3387
> URL: https://issues.apache.org/jira/browse/CALCITE-3387
> Project: Calcite
> Issue Type: Bug
>Reporter: Julian Hyde
>Assignee: Chunwei Lei
>Priority: Major
> Labels: pull-request-available
> Time Spent: 1.5h
> Remaining Estimate: 0h
>
> Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is
> ambiguous" error. Here is the query:
> {code}
> select deptno, count(*)
> from emp
> join dept using (deptno)
> group by deptno;
> {code}
> Because of USING, the two deptno fields should be merged into one, and
> therefore {{deptno}} is not ambiguous. That query works in Oracle. Also note
> that
> {code}select deptno
> from emp
> join dept using (deptno)
> {code}
> works correctly in Calcite, and has since CALCITE-2227 was fixed.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-3617) Add BOOL_OR Aggregate Function
[ https://issues.apache.org/jira/browse/CALCITE-3617?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Ritesh updated CALCITE-3617: Description: [https://docs.aws.amazon.com/redshift/latest/dg/r_BOOL_OR.html] > Add BOOL_OR Aggregate Function > -- > > Key: CALCITE-3617 > URL: https://issues.apache.org/jira/browse/CALCITE-3617 > Project: Calcite > Issue Type: New Feature >Reporter: Ritesh >Priority: Major > > [https://docs.aws.amazon.com/redshift/latest/dg/r_BOOL_OR.html] -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-3616) Add BOOL_AND Aggregate Function
Ritesh created CALCITE-3616: --- Summary: Add BOOL_AND Aggregate Function Key: CALCITE-3616 URL: https://issues.apache.org/jira/browse/CALCITE-3616 Project: Calcite Issue Type: New Feature Reporter: Ritesh [https://docs.aws.amazon.com/redshift/latest/dg/r_BOOL_AND.html] -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-3617) Add BOOL_OR Aggregate Function
Ritesh created CALCITE-3617: --- Summary: Add BOOL_OR Aggregate Function Key: CALCITE-3617 URL: https://issues.apache.org/jira/browse/CALCITE-3617 Project: Calcite Issue Type: New Feature Reporter: Ritesh -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Resolved] (CALCITE-3595) Test infrastructure overwrites reference log with wrong results
[ https://issues.apache.org/jira/browse/CALCITE-3595?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Danny Chen resolved CALCITE-3595. - Fix Version/s: 1.22.0 Assignee: Danny Chen Resolution: Fixed Fixed in [edc386d|https://github.com/apache/calcite/commit/edc386d960b12642cdca40abb692beab6f75ce72], thanks for your PR, [~yanlin-Lynn] ! > Test infrastructure overwrites reference log with wrong results > --- > > Key: CALCITE-3595 > URL: https://issues.apache.org/jira/browse/CALCITE-3595 > Project: Calcite > Issue Type: Bug > Components: build >Reporter: Julian Hyde >Assignee: Danny Chen >Priority: Major > Labels: pull-request-available > Fix For: 1.22.0 > > Time Spent: 20m > Remaining Estimate: 0h > > When I add a test case to a test that is based on DiffRepository (such as > SqlPrettyWriterTest), and run it in Intellij, the first time gives an error > because the actual output does not match the expected output. This is good. > I would expect that if I run it again, it will fail again, but it does not. > I'm not sure exactly what is going on, but maybe the test is reading from an > output file instead of a reference log. This was probably broken during the > migration to gradle, because the paths for reference logs have all changed. > This bug prevents me from using the workflow where I add a test case, run it, > and when it fails, change the code until the output looks right, then > overwrite the reference log. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Updated] (CALCITE-3611) Introduce TIME/TIMESTAMP WITH TIME ZONE types
[ https://issues.apache.org/jira/browse/CALCITE-3611?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Danny Chen updated CALCITE-3611: Component/s: core > Introduce TIME/TIMESTAMP WITH TIME ZONE types > - > > Key: CALCITE-3611 > URL: https://issues.apache.org/jira/browse/CALCITE-3611 > Project: Calcite > Issue Type: New Feature > Components: core >Affects Versions: 1.21.0 >Reporter: Zhenghua Gao >Priority: Major > > As we discussed in ML [1], this umbrella issue would introduce TIME/TIMESTAMP > WITH TIME ZONE types in Calcite, and the follow-up works. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Updated] (CALCITE-3611) Introduce TIME/TIMESTAMP WITH TIME ZONE types
[ https://issues.apache.org/jira/browse/CALCITE-3611?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Danny Chen updated CALCITE-3611: Affects Version/s: 1.21.0 > Introduce TIME/TIMESTAMP WITH TIME ZONE types > - > > Key: CALCITE-3611 > URL: https://issues.apache.org/jira/browse/CALCITE-3611 > Project: Calcite > Issue Type: New Feature >Affects Versions: 1.21.0 >Reporter: Zhenghua Gao >Priority: Major > > As we discussed in ML [1], this umbrella issue would introduce TIME/TIMESTAMP > WITH TIME ZONE types in Calcite, and the follow-up works. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Updated] (CALCITE-3611) Introduce TIME/TIMESTAMP WITH TIME ZONE types
[ https://issues.apache.org/jira/browse/CALCITE-3611?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Danny Chen updated CALCITE-3611: Labels: timestamp (was: ) > Introduce TIME/TIMESTAMP WITH TIME ZONE types > - > > Key: CALCITE-3611 > URL: https://issues.apache.org/jira/browse/CALCITE-3611 > Project: Calcite > Issue Type: New Feature > Components: core >Affects Versions: 1.21.0 >Reporter: Zhenghua Gao >Priority: Major > Labels: timestamp > > As we discussed in ML [1], this umbrella issue would introduce TIME/TIMESTAMP > WITH TIME ZONE types in Calcite, and the follow-up works. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Updated] (CALCITE-3614) Support TIME/TIMESTAMP WITH TIME ZONE literals
[ https://issues.apache.org/jira/browse/CALCITE-3614?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Zhenghua Gao updated CALCITE-3614: -- Component/s: core > Support TIME/TIMESTAMP WITH TIME ZONE literals > -- > > Key: CALCITE-3614 > URL: https://issues.apache.org/jira/browse/CALCITE-3614 > Project: Calcite > Issue Type: Sub-task > Components: core >Reporter: Zhenghua Gao >Priority: Major > -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Updated] (CALCITE-3615) Improve JDBC driver to support java.time classes
[ https://issues.apache.org/jira/browse/CALCITE-3615?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Zhenghua Gao updated CALCITE-3615: -- Component/s: jdbc-adapter > Improve JDBC driver to support java.time classes > > > Key: CALCITE-3615 > URL: https://issues.apache.org/jira/browse/CALCITE-3615 > Project: Calcite > Issue Type: Sub-task > Components: jdbc-adapter >Reporter: Zhenghua Gao >Priority: Major > -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-3615) Improve JDBC driver to support java.time classes
Zhenghua Gao created CALCITE-3615: - Summary: Improve JDBC driver to support java.time classes Key: CALCITE-3615 URL: https://issues.apache.org/jira/browse/CALCITE-3615 Project: Calcite Issue Type: Sub-task Reporter: Zhenghua Gao -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-3614) Support TIME/TIMESTAMP WITH TIME ZONE literals
Zhenghua Gao created CALCITE-3614: - Summary: Support TIME/TIMESTAMP WITH TIME ZONE literals Key: CALCITE-3614 URL: https://issues.apache.org/jira/browse/CALCITE-3614 Project: Calcite Issue Type: Sub-task Reporter: Zhenghua Gao -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-3613) Support TIME/TIMESTAMP WITH TIME ZONE in Calcite SQL parser and builtin functions
Zhenghua Gao created CALCITE-3613: - Summary: Support TIME/TIMESTAMP WITH TIME ZONE in Calcite SQL parser and builtin functions Key: CALCITE-3613 URL: https://issues.apache.org/jira/browse/CALCITE-3613 Project: Calcite Issue Type: Sub-task Reporter: Zhenghua Gao -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-3612) Add TIME/TIMESTAMP WITH TIME ZONE in optimizer
Zhenghua Gao created CALCITE-3612: - Summary: Add TIME/TIMESTAMP WITH TIME ZONE in optimizer Key: CALCITE-3612 URL: https://issues.apache.org/jira/browse/CALCITE-3612 Project: Calcite Issue Type: Sub-task Components: core Reporter: Zhenghua Gao -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-3611) Introduce TIME/TIMESTAMP WITH TIME ZONE types
Zhenghua Gao created CALCITE-3611: - Summary: Introduce TIME/TIMESTAMP WITH TIME ZONE types Key: CALCITE-3611 URL: https://issues.apache.org/jira/browse/CALCITE-3611 Project: Calcite Issue Type: New Feature Reporter: Zhenghua Gao As we discussed in ML [1], this umbrella issue would introduce TIME/TIMESTAMP WITH TIME ZONE types in Calcite, and the follow-up works. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-3594) Support hot Groupby keys hint
[
https://issues.apache.org/jira/browse/CALCITE-3594?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17000551#comment-17000551
]
Danny Chen edited comment on CALCITE-3594 at 12/20/19 1:48 AM:
---
Thanks [~amaliujia] Let's have a clear conclusion before firing a PR , you also
do not need to fire a new one, commits based on the original PR is okey.
1. Does the `key1` and `key2` have real values ? I mean are they real grouping
keys or just "key1" and "key2" literal.
2. Why it must be a key value pair of Identifiers ? Do you think the value can
be a string literal ?
was (Author: danny0405):
Thanks [~amaliujia] Let's have a clear conclusion before firing a PR , you also
do not need to fire a new one, commits based on the original PR is okey.
1. Does the `key1` and `key2` has real values ? I mean are they real grouping
keys or just "key1" and "key2" literal.
2. Why it must be a key value pair of Identifiers ? Do you think the value can
be a string literal ?
> Support hot Groupby keys hint
> -
>
> Key: CALCITE-3594
> URL: https://issues.apache.org/jira/browse/CALCITE-3594
> Project: Calcite
> Issue Type: Sub-task
>Reporter: Rui Wang
>Assignee: Rui Wang
>Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> It will be useful for Apache Beam if we support the following SqlHint:
> SELECT * FROM t
> GROUP BY t.key_column /* + hot_key(key1=fanout_factor, ...) */)
> The hot key strategy works on aggregation and it provides a list of hot keys
> with fanout factor for a column. The fanout factor says how many partition
> should be created for that specific key, such that we can have a per
> partition aggregate and then have a final aggregate. One example to explain
> it:
> SELECT * FROM t
> GROUP BY t.key_column /* + hot_key("value1"=2) */)
> // for the key_column, there is a "value1" which appear so many times (so
> it's hot), please consider split it into two partition and process separately.
> Such problem is common for big data processing, where hot key creates slowest
> machine which either slow down the whole pipeline or make retries. In such
> case, one common resolution is to split data to multiple partition and
> aggregate per partition, and then have a final combine.
> Usually execution engine won't know what is the hot key(s). SqlHint provides
> a good way to tell the engine which key is useful to deal with it.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3594) Support hot Groupby keys hint
[
https://issues.apache.org/jira/browse/CALCITE-3594?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17000551#comment-17000551
]
Danny Chen commented on CALCITE-3594:
-
Thanks [~amaliujia] Let's have a clear conclusion before firing a PR , you also
do not need to fire a new one, commits based on the original PR is okey.
1. Does the `key1` and `key2` has real values ? I mean are they real grouping
keys or just "key1" and "key2" literal.
2. Why it must be a key value pair of Identifiers ? Do you think the value can
be a string literal ?
> Support hot Groupby keys hint
> -
>
> Key: CALCITE-3594
> URL: https://issues.apache.org/jira/browse/CALCITE-3594
> Project: Calcite
> Issue Type: Sub-task
>Reporter: Rui Wang
>Assignee: Rui Wang
>Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> It will be useful for Apache Beam if we support the following SqlHint:
> SELECT * FROM t
> GROUP BY t.key_column /* + hot_key(key1=fanout_factor, ...) */)
> The hot key strategy works on aggregation and it provides a list of hot keys
> with fanout factor for a column. The fanout factor says how many partition
> should be created for that specific key, such that we can have a per
> partition aggregate and then have a final aggregate. One example to explain
> it:
> SELECT * FROM t
> GROUP BY t.key_column /* + hot_key("value1"=2) */)
> // for the key_column, there is a "value1" which appear so many times (so
> it's hot), please consider split it into two partition and process separately.
> Such problem is common for big data processing, where hot key creates slowest
> machine which either slow down the whole pipeline or make retries. In such
> case, one common resolution is to split data to multiple partition and
> aggregate per partition, and then have a final combine.
> Usually execution engine won't know what is the hot key(s). SqlHint provides
> a good way to tell the engine which key is useful to deal with it.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3594) Support hot Groupby keys hint
[
https://issues.apache.org/jira/browse/CALCITE-3594?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17000404#comment-17000404
]
Rui Wang commented on CALCITE-3594:
---
[~danny0405] thanks!
I revise a bit on you suggestion:
```
/*+ AGG_HOT_KEY(`key1`=`a:10,b:2;c:7`, `key2`=`d:7,e:2,f:9`) */
```
Because not every key is hot so it has to be a key value pair of Identifiers. I
opened a PR accordingly: https://github.com/apache/calcite/pull/1677
I would want to try this idea in BeamSQL and then to see if we have future need
to revise it.
> Support hot Groupby keys hint
> -
>
> Key: CALCITE-3594
> URL: https://issues.apache.org/jira/browse/CALCITE-3594
> Project: Calcite
> Issue Type: Sub-task
>Reporter: Rui Wang
>Assignee: Rui Wang
>Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> It will be useful for Apache Beam if we support the following SqlHint:
> SELECT * FROM t
> GROUP BY t.key_column /* + hot_key(key1=fanout_factor, ...) */)
> The hot key strategy works on aggregation and it provides a list of hot keys
> with fanout factor for a column. The fanout factor says how many partition
> should be created for that specific key, such that we can have a per
> partition aggregate and then have a final aggregate. One example to explain
> it:
> SELECT * FROM t
> GROUP BY t.key_column /* + hot_key("value1"=2) */)
> // for the key_column, there is a "value1" which appear so many times (so
> it's hot), please consider split it into two partition and process separately.
> Such problem is common for big data processing, where hot key creates slowest
> machine which either slow down the whole pipeline or make retries. In such
> case, one common resolution is to split data to multiple partition and
> aggregate per partition, and then have a final combine.
> Usually execution engine won't know what is the hot key(s). SqlHint provides
> a good way to tell the engine which key is useful to deal with it.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3603) SqlLateralOperator's unparse add additional keyword 'LATERAL' when the inner operator is SqlSnapshot
[
https://issues.apache.org/jira/browse/CALCITE-3603?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17000342#comment-17000342
]
Julian Hyde commented on CALCITE-3603:
--
I don't see why a temporal table join should be implicitly LATERAL. Should we
revisit that decision?
For the record, the biggest mistake in CALCITE-1912 was that it did not add
tests to SqlParserTest. This change effectively rectifies that.
> SqlLateralOperator's unparse add additional keyword 'LATERAL' when the inner
> operator is SqlSnapshot
>
>
> Key: CALCITE-3603
> URL: https://issues.apache.org/jira/browse/CALCITE-3603
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.21.0
>Reporter: Kevin Zhang
>Assignee: Danny Chen
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.22.0
>
> Time Spent: 20m
> Remaining Estimate: 0h
>
> When joining with a dimension table using the following sql
> {code:sql}
> SELECT
> o.amout, o.currency, r.rate, o.amount * r.rate
> FROM
> Orders AS o
> JOIN LatestRates FOR SYSTEM_TIME AS OF o.proctime AS r
> ON r.currency = o.currency
> {code}
> the unparsed sql is:
> {code:sql}
> SELECT `o`.`amout`, `o`.`currency`, `r`.`rate`, `o`.`amount` * `r`.`rate`
> FROM `Orders` AS `o`
> INNER JOIN LATERAL `LatestRates` FOR SYSTEM_TIME AS OF `o`.`proctime` AS `r`
> ON `r`.`currency` = `o`.`currency`
> {code}
> which has a syntax error because an additional "LATERAL" is added after
> "JOIN".
> The problem lies in SqlLateralOperator's unparse method, if the kind of the
> first operand is SqlSnapshot, we should not write out the operator's name.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-3591) Add BIT_XOR BIT aggregate function
[ https://issues.apache.org/jira/browse/CALCITE-3591?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ASF GitHub Bot updated CALCITE-3591: Labels: pull-request-available (was: ) > Add BIT_XOR BIT aggregate function > -- > > Key: CALCITE-3591 > URL: https://issues.apache.org/jira/browse/CALCITE-3591 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.21.0 >Reporter: hailong wang >Assignee: hailong wang >Priority: Major > Labels: pull-request-available > Fix For: 1.21.0, 1.22.0 > > > In https://issues.apache.org/jira/browse/CALCITE-2770 , We have support > bit_and、bit_or aggregate function. I think we sholud also support BIT_XOR -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3606) batch insert failed
[
https://issues.apache.org/jira/browse/CALCITE-3606?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16999883#comment-16999883
]
Ran Cao commented on CALCITE-3606:
--
{code:java}
final RelToSqlConverter converter = new
RelToSqlConverter(SqlDialect.DatabaseProduct.CALCITE.getDialect());
@Test public void test() throws Exception {
String singleColumnSql = "INSERT INTO DEPT VALUES (?,?)";
String multiColumnsSql = "INSERT INTO DEPT VALUES (?,?),(?,?)";
assertEquals(transform(singleColumnSql), "INSERT INTO
\"CATALOG\".\"SALES\".\"DEPT\" (\"DEPTNO\", \"NAME\")\r\n" + "(SELECT ? AS
\"DEPTNO\", ? AS \"NAME\"\r\n" + "FROM (VALUES (0)) AS \"t\" (\"ZERO\"))");
assertEquals(transform(multiColumnsSql), "INSERT INTO
\"CATALOG\".\"SALES\".\"DEPT\" (\"EXPR$0\", \"EXPR$1\")\r\n" + "SELECT ?,
?\r\n" + "FROM (VALUES (0)) AS \"t\" (\"ZERO\")\r\n" + "UNION ALL\r\n" +
"SELECT ?, ?\r\n" + "FROM (VALUES (0)) AS \"t\" (\"ZERO\")");
}
private String transform(String sql) {
RelRoot relRoot = tester.convertSqlToRel(sql);
final SqlNode sqlNode = converter.visitChild(0, relRoot.rel).asStatement();
return
sqlNode.toSqlString(SqlDialect.DatabaseProduct.CALCITE.getDialect()).getSql();
}{code}
after transform, the second sql's column name was wrong.
> batch insert failed
> ---
>
> Key: CALCITE-3606
> URL: https://issues.apache.org/jira/browse/CALCITE-3606
> Project: Calcite
> Issue Type: Wish
> Components: core
>Affects Versions: 1.21.0
>Reporter: Ran Cao
>Priority: Major
>
> when I try to execute sql like (insert into example_table (column1,column2)
> values (value1,value2),(value1,value2)), it will failed with error message
> like this: column "EXPR$0" of relation "example_table" does not exist. I find
> the reason is that when converting SqlNode(insert sql) to
> RelNode(TableModify), one of the steps is to change the column that stored in
> RelDataType from the fake column name (like "EXPR$0") to the real column name
> (like "id"). But when the values part in sql is more than one , the step
> above-mentioned will skip because the RelNode is instance of LogicalUnion
> instead of Project, the code refered to org.apache.calcite.tools.RelBuilder
> line 1461:
> if (input instanceof Project && fieldNames != null) {
> // change the column name
> }
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Resolved] (CALCITE-3568) BigQuery, Hive, Spark SQL dialect don't support nested aggregates
[ https://issues.apache.org/jira/browse/CALCITE-3568?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Chunwei Lei resolved CALCITE-3568. -- Resolution: Fixed Fixed in [https://github.com/apache/calcite/commit/f3020bc59be668b36a131361b486b45064b16f50]! Thanks for your pr, [~divyanshu.s95]. And thanks for verifying, [~amaliujia]. > BigQuery, Hive, Spark SQL dialect don't support nested aggregates > - > > Key: CALCITE-3568 > URL: https://issues.apache.org/jira/browse/CALCITE-3568 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: Divyanshu Srivastava >Assignee: Divyanshu Srivastava >Priority: Major > Labels: pull-request-available > Fix For: 1.22.0 > > Time Spent: 3.5h > Remaining Estimate: 0h > > The BigQuerySqlDialect, HiveSqlDialect, SparkSqlDialect do not support nested > aggregates, hence overriding the supportsNestedAggregations() method in them. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3387) Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is ambiguous" error
[
https://issues.apache.org/jira/browse/CALCITE-3387?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16999836#comment-16999836
]
Chunwei Lei commented on CALCITE-3387:
--
Thanks for your review, [~jinxing6...@126.com]. PR was updated.
> Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is
> ambiguous" error
> --
>
> Key: CALCITE-3387
> URL: https://issues.apache.org/jira/browse/CALCITE-3387
> Project: Calcite
> Issue Type: Bug
>Reporter: Julian Hyde
>Assignee: Chunwei Lei
>Priority: Major
> Labels: pull-request-available
> Time Spent: 1.5h
> Remaining Estimate: 0h
>
> Query with GROUP BY and JOIN ... USING wrongly fails with "Column 'DEPTNO' is
> ambiguous" error. Here is the query:
> {code}
> select deptno, count(*)
> from emp
> join dept using (deptno)
> group by deptno;
> {code}
> Because of USING, the two deptno fields should be merged into one, and
> therefore {{deptno}} is not ambiguous. That query works in Oracle. Also note
> that
> {code}select deptno
> from emp
> join dept using (deptno)
> {code}
> works correctly in Calcite, and has since CALCITE-2227 was fixed.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
