[jira] [Assigned] (CALCITE-3078) Duplicate code lastDay in calcite-avatica and calcite
[ https://issues.apache.org/jira/browse/CALCITE-3078?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Stamatis Zampetakis reassigned CALCITE-3078: Assignee: Stamatis Zampetakis > Duplicate code lastDay in calcite-avatica and calcite > - > > Key: CALCITE-3078 > URL: https://issues.apache.org/jira/browse/CALCITE-3078 > Project: Calcite > Issue Type: Bug > Components: avatica, core >Affects Versions: 1.19.0 >Reporter: pingle wang >Assignee: Stamatis Zampetakis >Priority: Trivial > Time Spent: 10m > Remaining Estimate: 0h > > The code of lastDay appears in calcite( > [lastDay|https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java#L2322] > ), and appears in calcite-avatica. I think the duplicate code can change the > calcite-avatica to public function then be used by calcite. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-3078) Duplicate code lastDay in calcite-avatica and calcite
[ https://issues.apache.org/jira/browse/CALCITE-3078?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17622832#comment-17622832 ] Stamatis Zampetakis commented on CALCITE-3078: -- The code duplication was introduced by CALCITE-1608. According to the comments under CALCITE-1608 and CALCITE-1613 the intention was to remove them from Calcite at some point. > Duplicate code lastDay in calcite-avatica and calcite > - > > Key: CALCITE-3078 > URL: https://issues.apache.org/jira/browse/CALCITE-3078 > Project: Calcite > Issue Type: Bug > Components: avatica, core >Affects Versions: 1.19.0 >Reporter: pingle wang >Priority: Trivial > Time Spent: 10m > Remaining Estimate: 0h > > The code of lastDay appears in calcite( > [lastDay|https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java#L2322] > ), and appears in calcite-avatica. I think the duplicate code can change the > calcite-avatica to public function then be used by calcite. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Assigned] (CALCITE-5336) Support inferring constants from predicates with IS NOT DISTINCT FROM operator
[
https://issues.apache.org/jira/browse/CALCITE-5336?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Stamatis Zampetakis reassigned CALCITE-5336:
Assignee: Jasmin Trada
> Support inferring constants from predicates with IS NOT DISTINCT FROM operator
> --
>
> Key: CALCITE-5336
> URL: https://issues.apache.org/jira/browse/CALCITE-5336
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Jasmin Trada
>Assignee: Jasmin Trada
>Priority: Trivial
> Original Estimate: 168h
> Remaining Estimate: 168h
>
> Whenever we have {{IS NOT DISTINCT FROM}} operator in a predicate, the
> {{predicateConstants}} method in {{RexUtil}} is not able to identify the
> constants.
> E.g., If we make a predicate as given below,
> {code}
> //dec20 = IS_NOT_DISTINCT_FROM($0, CAST(2020-12-11):DATE)
> RexNode dec20 = rexB.makeCall(IS_NOT_DISTINCT_FROM,
> rexB.makeInputRef(dateColumnType, 0),
> rexB.makeLiteral(new DateString(2020, 12, 11), dateColumnType,
> false));
> {code}
> and if we call {{RexUtil.predicateConstants(RexNode.class, rexB,
> Arrays.asList(dec20))}},
> it will return an empty map, but it should have returned something like this:
> {code}
> { "$0" -> "CAST(2020-12-11):DATE"}
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5314) Prune empty parts of a query by exploiting stats/metadata
[
https://issues.apache.org/jira/browse/CALCITE-5314?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17619085#comment-17619085
]
Stamatis Zampetakis commented on CALCITE-5314:
--
By combining the new rule instance that matches table scans with the remaining
pruning rules we should be able to prune various parts of a query. In addition,
the new rule can be configured to match any kind of RelNode so in the end the
subject may not need to change.
> Prune empty parts of a query by exploiting stats/metadata
> -
>
> Key: CALCITE-5314
> URL: https://issues.apache.org/jira/browse/CALCITE-5314
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Stamatis Zampetakis
>Assignee: Hanumath Rao Maduri
>Priority: Major
> Labels: pull-request-available
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> Currently there is a collection of rules (i.e.,
> [PruneEmptyRules|https://github.com/apache/calcite/blob/4ef9ffe0d4afb80fe95e66d4d9c9e6f5939f70a8/core/src/main/java/org/apache/calcite/rel/rules/PruneEmptyRules.java])
> which remove sections of a query plan when it does not produce any rows.
> At the moment, the removal requires an empty {{Values}} operator to be in the
> plan in order to take effect. However, there are cases where queries involve
> empty relations/tables and in that case we could remove parts of the plan as
> well.
> The information if a relation is empty or not can be derived from metadata.
> For example, we could check if the
> [RelMdMaxRowCount|https://github.com/apache/calcite/blob/4ef9ffe0d4afb80fe95e66d4d9c9e6f5939f70a8/core/src/main/java/org/apache/calcite/rel/metadata/RelMdMaxRowCount.java]
> returns zero to derive that the relation is empty.
> Implementation wise there are various alternatives to consider:
> * modify the existing PruneEmptyRule to consider metadata;
> * add new pruning rules relying exclusively on metadata;
> * add new rules checking metadata and turning the empty relation to an empty
> {{Values}} which can then be handled by existing rules
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5332) Configuring PruneEmptyRules is cumbersome
[
https://issues.apache.org/jira/browse/CALCITE-5332?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17618889#comment-17618889
]
Stamatis Zampetakis commented on CALCITE-5332:
--
Thanks for the feedback [~julianhyde] .
At the moment:
* all built-in rules in the repo come with at least one DEFAULT config instance
* the Javadoc asks for devs to create a DEFAULT configuration instance for
each new rule
Based on the above, I am inclined to merge the current PR (adding DEFAULT
configs in PruneEmptyRule) simply to align these rules with the rest of the
code base.
Guiding users to use a certain pattern when creating rules is an interesting
discussion but it may be larger than the scope of this JIRA. I am happy to do
it here, but it may be better to do it in the dev list or under a dedicated
Jira. Most likely we will need to adapt/introduce documentation and revisit
parts of our code base to make that happen. People tend to copy existing
patterns existing from the calcite-core, so if there we build rules starting
from DEFAULT config instances, then this is what others will do in projects
using Calcite (given that all config instances are public).
> Configuring PruneEmptyRules is cumbersome
> -
>
> Key: CALCITE-5332
> URL: https://issues.apache.org/jira/browse/CALCITE-5332
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.32.0
>Reporter: Stamatis Zampetakis
>Assignee: Stamatis Zampetakis
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.33.0
>
> Time Spent: 20m
> Remaining Estimate: 0h
>
> After CALCITE-4787/CALCITE-4839 (release 1.28.0) it is pretty complicated to
> create new PruneEmptyRule instances with slightly different configurations.
> The most common use-case is to modify a rule, for instance
> [PruneEmptyProject|https://github.com/apache/calcite/blob/2c30a56158cdd351d35725006bc1f76bb6aac75b/core/src/main/java/org/apache/calcite/rel/rules/PruneEmptyRules.java#L178],
> and change the operands to enlarge or restrict the matching scope; e.g.,
> instead of matching the general {{Project.class}} match only
> {{HiveProject.class}}.
> At the moment this task is not straightforward for various reasons.
> We cannot create the new rule by obtaining the configuration from the
> existing instance (e.g., PROJECT_INSTANCE) cause the latter is declared as
> RelOptRule. Unless we cast the instance to {{RelRule}} we cannot get access
> to its configuration and change it.
> {{ImmutableRemoveEmptySingleRuleConfig}} is package private as every other
> immutable class so again we cannot start from there.
> The constructors of {{RemoveEmptySingleRule}} are either deprecated or
> package private so cannot/should not be used.
> [RemoveEmptySingleRuleConfig|https://github.com/apache/calcite/blob/2c30a56158cdd351d35725006bc1f76bb6aac75b/core/src/main/java/org/apache/calcite/rel/rules/PruneEmptyRules.java#L351]
> does not provide a {{DEFAULT}} configuration instance.
> The only way "recommended" way to create a new rule instance at the moment is
> to implement the respective config interface. This is unnecessary and
> requires quite a bit of work if the consumer is not using the immutables
> library annotation processor.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5332) Configuring PruneEmptyRules is cumbersome
[
https://issues.apache.org/jira/browse/CALCITE-5332?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17617979#comment-17617979
]
Stamatis Zampetakis commented on CALCITE-5332:
--
[~julianhyde] I am bit surprised to hear that extending a rule via the DEFAULT
config is an anti-pattern. Actually at the moment the vast majority of built-in
rules come with at least one DEFAULT config instance. The pruning rules are a
small exception to the general pattern. Moreover, the Javadoc of the
{{RelRule}} interface explicitly asks to [create a DEFAULT configuration
instance|https://github.com/apache/calcite/blob/2c30a56158cdd351d35725006bc1f76bb6aac75b/core/src/main/java/org/apache/calcite/plan/RelRule.java#L86].
> Configuring PruneEmptyRules is cumbersome
> -
>
> Key: CALCITE-5332
> URL: https://issues.apache.org/jira/browse/CALCITE-5332
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.32.0
>Reporter: Stamatis Zampetakis
>Assignee: Stamatis Zampetakis
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.33.0
>
> Time Spent: 20m
> Remaining Estimate: 0h
>
> After CALCITE-4787/CALCITE-4839 (release 1.28.0) it is pretty complicated to
> create new PruneEmptyRule instances with slightly different configurations.
> The most common use-case is to modify a rule, for instance
> [PruneEmptyProject|https://github.com/apache/calcite/blob/2c30a56158cdd351d35725006bc1f76bb6aac75b/core/src/main/java/org/apache/calcite/rel/rules/PruneEmptyRules.java#L178],
> and change the operands to enlarge or restrict the matching scope; e.g.,
> instead of matching the general {{Project.class}} match only
> {{HiveProject.class}}.
> At the moment this task is not straightforward for various reasons.
> We cannot create the new rule by obtaining the configuration from the
> existing instance (e.g., PROJECT_INSTANCE) cause the latter is declared as
> RelOptRule. Unless we cast the instance to {{RelRule}} we cannot get access
> to its configuration and change it.
> {{ImmutableRemoveEmptySingleRuleConfig}} is package private as every other
> immutable class so again we cannot start from there.
> The constructors of {{RemoveEmptySingleRule}} are either deprecated or
> package private so cannot/should not be used.
> [RemoveEmptySingleRuleConfig|https://github.com/apache/calcite/blob/2c30a56158cdd351d35725006bc1f76bb6aac75b/core/src/main/java/org/apache/calcite/rel/rules/PruneEmptyRules.java#L351]
> does not provide a {{DEFAULT}} configuration instance.
> The only way "recommended" way to create a new rule instance at the moment is
> to implement the respective config interface. This is unnecessary and
> requires quite a bit of work if the consumer is not using the immutables
> library annotation processor.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5332) Configuring PruneEmptyRules is cumbersome
[
https://issues.apache.org/jira/browse/CALCITE-5332?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17617013#comment-17617013
]
Stamatis Zampetakis commented on CALCITE-5332:
--
There are two easy ways to address the problem:
* Declare the built-in rule instances as RelRule or a more specific sub-type.
* Add DEFAULT configuration instances for the existing rules inside the
respective config interfaces; common pattern used in other Calcite rules.
Changing the declared type of existing rule instances is a breaking change. On
the other hand adding more static rule instances with different declared type
seems to add a bit of noise but might be acceptable for a short period if
time.
I slightly prefer the option of adding new DEFAULT config instances cause it
doesn't introduce backward compatibility problems neither requires further
cleanup. Furthermore, in terms of readability I find it more natural to create
a rule starting from a configuration rather than starting from a rule (and
obtain the config).
> Configuring PruneEmptyRules is cumbersome
> -
>
> Key: CALCITE-5332
> URL: https://issues.apache.org/jira/browse/CALCITE-5332
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.32.0
>Reporter: Stamatis Zampetakis
>Assignee: Stamatis Zampetakis
>Priority: Major
> Fix For: 1.33.0
>
>
> After CALCITE-4787/CALCITE-4839 (release 1.28.0) it is pretty complicated to
> create new PruneEmptyRule instances with slightly different configurations.
> The most common use-case is to modify a rule, for instance
> [PruneEmptyProject|https://github.com/apache/calcite/blob/2c30a56158cdd351d35725006bc1f76bb6aac75b/core/src/main/java/org/apache/calcite/rel/rules/PruneEmptyRules.java#L178],
> and change the operands to enlarge or restrict the matching scope; e.g.,
> instead of matching the general {{Project.class}} match only
> {{HiveProject.class}}.
> At the moment this task is not straightforward for various reasons.
> We cannot create the new rule by obtaining the configuration from the
> existing instance (e.g., PROJECT_INSTANCE) cause the latter is declared as
> RelOptRule. Unless we cast the instance to {{RelRule}} we cannot get access
> to its configuration and change it.
> {{ImmutableRemoveEmptySingleRuleConfig}} is package private as every other
> immutable class so again we cannot start from there.
> The constructors of {{RemoveEmptySingleRule}} are either deprecated or
> package private so cannot/should not be used.
> [RemoveEmptySingleRuleConfig|https://github.com/apache/calcite/blob/2c30a56158cdd351d35725006bc1f76bb6aac75b/core/src/main/java/org/apache/calcite/rel/rules/PruneEmptyRules.java#L351]
> does not provide a {{DEFAULT}} configuration instance.
> The only way "recommended" way to create a new rule instance at the moment is
> to implement the respective config interface. This is unnecessary and
> requires quite a bit of work if the consumer is not using the immutables
> library annotation processor.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-4787) Move core to use Immutables instead of ImmutableBeans
[ https://issues.apache.org/jira/browse/CALCITE-4787?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17616963#comment-17616963 ] Stamatis Zampetakis commented on CALCITE-4787: -- This change along with CALCITE-4830 adds a significant burden to the configuration of some rules. Unless I missed something, the extensibility of PruneEmptyRules seems to be somewhat broken (see CALCITE-5332). I am afraid that except the pruning rules the change may have affected other rules as wells. I will discuss potential fixes under CALCITE-5332 but just wanted to give a heads up here since many people follow this ticket. > Move core to use Immutables instead of ImmutableBeans > - > > Key: CALCITE-4787 > URL: https://issues.apache.org/jira/browse/CALCITE-4787 > Project: Calcite > Issue Type: Improvement >Reporter: Jacques Nadeau >Assignee: Jacques Nadeau >Priority: Major > Labels: pull-request-available > Fix For: 1.28.0 > > Time Spent: 10h 40m > Remaining Estimate: 0h > > In the creation of CALCITE-3328, [Immutables|https://immutables.github.io/] > was discussed as an alternative to a custom implementation. This ticket is to > evaluate the impact to the codebase of changing. Ideally, introduction of > immutables would both add flexibility and reduce the amount of code > associated with these classes. > Immutables works via annotation processor which means that it is should be > relatively seamless to build systems and IDEs. > The switch would also make it easier to work with these objects types in the > context of aot compilation tools like GraalVM. > > This initial task covers key classes in the core module. Will open up > follow-on tickets for other locations. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-5332) Configuring PruneEmptyRules is cumbersome
Stamatis Zampetakis created CALCITE-5332:
Summary: Configuring PruneEmptyRules is cumbersome
Key: CALCITE-5332
URL: https://issues.apache.org/jira/browse/CALCITE-5332
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.32.0
Reporter: Stamatis Zampetakis
Assignee: Stamatis Zampetakis
Fix For: 1.33.0
After CALCITE-4787/CALCITE-4839 (release 1.28.0) it is pretty complicated to
create new PruneEmptyRule instances with slightly different configurations.
The most common use-case is to modify a rule, for instance
[PruneEmptyProject|https://github.com/apache/calcite/blob/2c30a56158cdd351d35725006bc1f76bb6aac75b/core/src/main/java/org/apache/calcite/rel/rules/PruneEmptyRules.java#L178],
and change the operands to enlarge or restrict the matching scope; e.g.,
instead of matching the general {{Project.class}} match only
{{HiveProject.class}}.
At the moment this task is not straightforward for various reasons.
We cannot create the new rule by obtaining the configuration from the existing
instance (e.g., PROJECT_INSTANCE) cause the latter is declared as RelOptRule.
Unless we cast the instance to {{RelRule}} we cannot get access to its
configuration and change it.
{{ImmutableRemoveEmptySingleRuleConfig}} is package private as every other
immutable class so again we cannot start from there.
The constructors of {{RemoveEmptySingleRule}} are either deprecated or package
private so cannot/should not be used.
[RemoveEmptySingleRuleConfig|https://github.com/apache/calcite/blob/2c30a56158cdd351d35725006bc1f76bb6aac75b/core/src/main/java/org/apache/calcite/rel/rules/PruneEmptyRules.java#L351]
does not provide a {{DEFAULT}} configuration instance.
The only way "recommended" way to create a new rule instance at the moment is
to implement the respective config interface. This is unnecessary and requires
quite a bit of work if the consumer is not using the immutables library
annotation processor.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5315) Error when pushing filters with operations that may throw (CAST/DIVISION) below joins
[
https://issues.apache.org/jira/browse/CALCITE-5315?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17615698#comment-17615698
]
Stamatis Zampetakis commented on CALCITE-5315:
--
SQL is declarative but this doesn't mean that there is no ordering between
SELECT, FROM, WHERE, GROUP BY, ORDER BY etc. The SQL semantics would be broken
if order is taken out of the equation.
For the record Postgres, as every other DBMS, clearly defines the order that
the SQL clauses are evaluated:
https://www.postgresql.org/docs/current/sql-select.html
Anyways I agree that this is an edge case so it may not be worth spending too
much time on this.
[~julianhyde] I don't know if the SQL standard says something about projection
pruning although not throwing an error may be a smaller problem. Most likely
users will complain more if their query fails when it was expected to run than
the other way around (complain cause the query doesn't fail).
> Error when pushing filters with operations that may throw (CAST/DIVISION)
> below joins
> -
>
> Key: CALCITE-5315
> URL: https://issues.apache.org/jira/browse/CALCITE-5315
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.32.0
>Reporter: Stamatis Zampetakis
>Priority: Major
>
> +Steps to reproduce:+
> {code:sql}
> CREATE TABLE emp (empno INT, name VARCHAR, deptno INT);
> INSERT INTO emp VALUES (0, 'Alex', 0);
> INSERT INTO emp VALUES (10, 'Bob', 1);
> CREATE TABLE dept (deptno INT);
> INSERT INTO dept VALUES (1);
> SELECT e.name
> FROM emp e
> INNER JOIN dept d ON e.deptno = d.deptno
> WHERE (10 / e.empno) = 1
> {code}
> *Expected output:*
> Bob
> *Actual output:*
> ERROR: division by zero
> The error is caused when the filter condition in the WHERE clause is
> evaluated before the join. Filter push-down is a very common and powerful
> optimization but when there are operators in the WHERE clause that may throw
> (such as division, cast, etc) this optimization is unsafe and can lead to
> runtime errors.
> The SQL standard (Section 7.4 general rule 1) mandates that WHERE should be
> applied to the result of FROM so in the case above pushing filters below the
> join seems to violate the standard.
> +Citing the standard:+
> "If all optional clauses are omitted, then the result of the expression> is the same as the result of the
> . Otherwise, each specified clause is applied to the result of
> the previously specified clause
> and the result of the is the result of the application of
> the last specified clause."
> One of the optional clauses mentioned in the previous paragraph is the
> . There seems to be a clearly defined order between the clause>, which includes inner joins, and the .
> Note that this problem is *not* the same as the evaluation order of
> predicates in the WHERE clause, which is implementation specific. This is
> about evaluation order of WHERE clause and FROM clause that is not
> implementation specific.
> Original discussion:
> https://lists.apache.org/thread/cp7h28k1yfxv421q12y1wopbwgrzdzrx
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5314) Prune empty parts of a query by exploiting stats/metadata
[
https://issues.apache.org/jira/browse/CALCITE-5314?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17615687#comment-17615687
]
Stamatis Zampetakis commented on CALCITE-5314:
--
I left some review comments in the PR but overall looks good.
The rule can be generalized to match any kind of relational expression as
[~julianhyde] mentioned but I agree with [~hanu.ncr] that it is better to
restrict the default instance to match only table scans. If people want to
enlarge the scope it will be easy to do so via the configuration.
> Prune empty parts of a query by exploiting stats/metadata
> -
>
> Key: CALCITE-5314
> URL: https://issues.apache.org/jira/browse/CALCITE-5314
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Stamatis Zampetakis
>Assignee: Hanumath Rao Maduri
>Priority: Major
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Currently there is a collection of rules (i.e.,
> [PruneEmptyRules|https://github.com/apache/calcite/blob/4ef9ffe0d4afb80fe95e66d4d9c9e6f5939f70a8/core/src/main/java/org/apache/calcite/rel/rules/PruneEmptyRules.java])
> which remove sections of a query plan when it does not produce any rows.
> At the moment, the removal requires an empty {{Values}} operator to be in the
> plan in order to take effect. However, there are cases where queries involve
> empty relations/tables and in that case we could remove parts of the plan as
> well.
> The information if a relation is empty or not can be derived from metadata.
> For example, we could check if the
> [RelMdMaxRowCount|https://github.com/apache/calcite/blob/4ef9ffe0d4afb80fe95e66d4d9c9e6f5939f70a8/core/src/main/java/org/apache/calcite/rel/metadata/RelMdMaxRowCount.java]
> returns zero to derive that the relation is empty.
> Implementation wise there are various alternatives to consider:
> * modify the existing PruneEmptyRule to consider metadata;
> * add new pruning rules relying exclusively on metadata;
> * add new rules checking metadata and turning the empty relation to an empty
> {{Values}} which can then be handled by existing rules
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5314) Prune empty parts of a query by exploiting stats/metadata
[
https://issues.apache.org/jira/browse/CALCITE-5314?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17614141#comment-17614141
]
Stamatis Zampetakis commented on CALCITE-5314:
--
[~hanu.ncr] feel free to pick it up and I will try to review the changes once I
find some time. If you need to break the task into smaller pieces feel free to
create additional JIRAs and link them to this case.
If the stats are stale the metadata providers should return appropriate values
to indicate that. For instance, RelMdMaxRowCount should return null or
something that won't create problems to rules that may use it.
> Prune empty parts of a query by exploiting stats/metadata
> -
>
> Key: CALCITE-5314
> URL: https://issues.apache.org/jira/browse/CALCITE-5314
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Stamatis Zampetakis
>Priority: Major
>
> Currently there is a collection of rules (i.e.,
> [PruneEmptyRules|https://github.com/apache/calcite/blob/4ef9ffe0d4afb80fe95e66d4d9c9e6f5939f70a8/core/src/main/java/org/apache/calcite/rel/rules/PruneEmptyRules.java])
> which remove sections of a query plan when it does not produce any rows.
> At the moment, the removal requires an empty {{Values}} operator to be in the
> plan in order to take effect. However, there are cases where queries involve
> empty relations/tables and in that case we could remove parts of the plan as
> well.
> The information if a relation is empty or not can be derived from metadata.
> For example, we could check if the
> [RelMdMaxRowCount|https://github.com/apache/calcite/blob/4ef9ffe0d4afb80fe95e66d4d9c9e6f5939f70a8/core/src/main/java/org/apache/calcite/rel/metadata/RelMdMaxRowCount.java]
> returns zero to derive that the relation is empty.
> Implementation wise there are various alternatives to consider:
> * modify the existing PruneEmptyRule to consider metadata;
> * add new pruning rules relying exclusively on metadata;
> * add new rules checking metadata and turning the empty relation to an empty
> {{Values}} which can then be handled by existing rules
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5315) Error when pushing filters with operations that may throw (CAST/DIVISION) below joins
[
https://issues.apache.org/jira/browse/CALCITE-5315?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17614134#comment-17614134
]
Stamatis Zampetakis commented on CALCITE-5315:
--
One way to solve the problem would be to tweak
[FilterJoinRule|https://github.com/apache/calcite/blob/4ef9ffe0d4afb80fe95e66d4d9c9e6f5939f70a8/core/src/main/java/org/apache/calcite/rel/rules/FilterJoinRule.java]
to prevent parts of the filtering condition to be pushed below the join when
the operators may throw an exception.
Given that a big majority of users may not actually care to be strictly
compliant with SQL standard the restriction should be configurable and probably
disabled by default.
Note that when dealing with conjunctions it is not necessary to completely
block the pushdown when there are operators that may throw, but only block the
conjunct that contains the CAST/DIVISION operator.
> Error when pushing filters with operations that may throw (CAST/DIVISION)
> below joins
> -
>
> Key: CALCITE-5315
> URL: https://issues.apache.org/jira/browse/CALCITE-5315
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.32.0
>Reporter: Stamatis Zampetakis
>Priority: Major
>
> +Steps to reproduce:+
> {code:sql}
> CREATE TABLE emp (empno INT, name VARCHAR, deptno INT);
> INSERT INTO emp VALUES (0, 'Alex', 0);
> INSERT INTO emp VALUES (10, 'Bob', 1);
> CREATE TABLE dept (deptno INT);
> INSERT INTO dept VALUES (1);
> SELECT e.name
> FROM emp e
> INNER JOIN dept d ON e.deptno = d.deptno
> WHERE (10 / e.empno) = 1
> {code}
> *Expected output:*
> Bob
> *Actual output:*
> ERROR: division by zero
> The error is caused when the filter condition in the WHERE clause is
> evaluated before the join. Filter push-down is a very common and powerful
> optimization but when there are operators in the WHERE clause that may throw
> (such as division, cast, etc) this optimization is unsafe and can lead to
> runtime errors.
> The SQL standard (Section 7.4 general rule 1) mandates that WHERE should be
> applied to the result of FROM so in the case above pushing filters below the
> join seems to violate the standard.
> +Citing the standard:+
> "If all optional clauses are omitted, then the result of the expression> is the same as the result of the
> . Otherwise, each specified clause is applied to the result of
> the previously specified clause
> and the result of the is the result of the application of
> the last specified clause."
> One of the optional clauses mentioned in the previous paragraph is the
> . There seems to be a clearly defined order between the clause>, which includes inner joins, and the .
> Note that this problem is *not* the same as the evaluation order of
> predicates in the WHERE clause, which is implementation specific. This is
> about evaluation order of WHERE clause and FROM clause that is not
> implementation specific.
> Original discussion:
> https://lists.apache.org/thread/cp7h28k1yfxv421q12y1wopbwgrzdzrx
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5315) Error when pushing filters with operations that may throw (CAST/DIVISION) below joins
[
https://issues.apache.org/jira/browse/CALCITE-5315?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17614132#comment-17614132
]
Stamatis Zampetakis commented on CALCITE-5315:
--
The same "bug" exists in various databases such as DERBY-6358,
[Postgres|https://www.postgresql.org/message-id/17623-2bef2f4ff7fe8...@postgresql.org],
and others but the optimization is so common that it is unlikely to be fixed.
> Error when pushing filters with operations that may throw (CAST/DIVISION)
> below joins
> -
>
> Key: CALCITE-5315
> URL: https://issues.apache.org/jira/browse/CALCITE-5315
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.32.0
>Reporter: Stamatis Zampetakis
>Priority: Major
>
> +Steps to reproduce:+
> {code:sql}
> CREATE TABLE emp (empno INT, name VARCHAR, deptno INT);
> INSERT INTO emp VALUES (0, 'Alex', 0);
> INSERT INTO emp VALUES (10, 'Bob', 1);
> CREATE TABLE dept (deptno INT);
> INSERT INTO dept VALUES (1);
> SELECT e.name
> FROM emp e
> INNER JOIN dept d ON e.deptno = d.deptno
> WHERE (10 / e.empno) = 1
> {code}
> *Expected output:*
> Bob
> *Actual output:*
> ERROR: division by zero
> The error is caused when the filter condition in the WHERE clause is
> evaluated before the join. Filter push-down is a very common and powerful
> optimization but when there are operators in the WHERE clause that may throw
> (such as division, cast, etc) this optimization is unsafe and can lead to
> runtime errors.
> The SQL standard (Section 7.4 general rule 1) mandates that WHERE should be
> applied to the result of FROM so in the case above pushing filters below the
> join seems to violate the standard.
> +Citing the standard:+
> "If all optional clauses are omitted, then the result of the expression> is the same as the result of the
> . Otherwise, each specified clause is applied to the result of
> the previously specified clause
> and the result of the is the result of the application of
> the last specified clause."
> One of the optional clauses mentioned in the previous paragraph is the
> . There seems to be a clearly defined order between the clause>, which includes inner joins, and the .
> Note that this problem is *not* the same as the evaluation order of
> predicates in the WHERE clause, which is implementation specific. This is
> about evaluation order of WHERE clause and FROM clause that is not
> implementation specific.
> Original discussion:
> https://lists.apache.org/thread/cp7h28k1yfxv421q12y1wopbwgrzdzrx
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5315) Error when pushing filters with operations that may throw (CAST/DIVISION) below joins
Stamatis Zampetakis created CALCITE-5315:
Summary: Error when pushing filters with operations that may throw
(CAST/DIVISION) below joins
Key: CALCITE-5315
URL: https://issues.apache.org/jira/browse/CALCITE-5315
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.32.0
Reporter: Stamatis Zampetakis
+Steps to reproduce:+
{code:sql}
CREATE TABLE emp (empno INT, name VARCHAR, deptno INT);
INSERT INTO emp VALUES (0, 'Alex', 0);
INSERT INTO emp VALUES (10, 'Bob', 1);
CREATE TABLE dept (deptno INT);
INSERT INTO dept VALUES (1);
SELECT e.name
FROM emp e
INNER JOIN dept d ON e.deptno = d.deptno
WHERE (10 / e.empno) = 1
{code}
*Expected output:*
Bob
*Actual output:*
ERROR: division by zero
The error is caused when the filter condition in the WHERE clause is evaluated
before the join. Filter push-down is a very common and powerful
optimization but when there are operators in the WHERE clause that may throw
(such as division, cast, etc) this optimization is unsafe and can lead to
runtime errors.
The SQL standard (Section 7.4 general rule 1) mandates that WHERE should be
applied to the result of FROM so in the case above pushing filters below the
join seems to violate the standard.
+Citing the standard:+
"If all optional clauses are omitted, then the result of the is the same as the result of the
. Otherwise, each specified clause is applied to the result of
the previously specified clause
and the result of the is the result of the application of
the last specified clause."
One of the optional clauses mentioned in the previous paragraph is the
. There seems to be a clearly defined order between the , which includes inner joins, and the .
Note that this problem is *not* the same as the evaluation order of predicates
in the WHERE clause, which is implementation specific. This is about evaluation
order of WHERE clause and FROM clause that is not implementation specific.
Original discussion:
https://lists.apache.org/thread/cp7h28k1yfxv421q12y1wopbwgrzdzrx
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5314) Prune empty parts of a query by exploiting stats/metadata
Stamatis Zampetakis created CALCITE-5314:
Summary: Prune empty parts of a query by exploiting stats/metadata
Key: CALCITE-5314
URL: https://issues.apache.org/jira/browse/CALCITE-5314
Project: Calcite
Issue Type: Improvement
Components: core
Reporter: Stamatis Zampetakis
Currently there is a collection of rules (i.e.,
[PruneEmptyRules|https://github.com/apache/calcite/blob/4ef9ffe0d4afb80fe95e66d4d9c9e6f5939f70a8/core/src/main/java/org/apache/calcite/rel/rules/PruneEmptyRules.java])
which remove sections of a query plan when it does not produce any rows.
At the moment, the removal requires an empty {{Values}} operator to be in the
plan in order to take effect. However, there are cases where queries involve
empty relations/tables and in that case we could remove parts of the plan as
well.
The information if a relation is empty or not can be derived from metadata. For
example, we could check if the
[RelMdMaxRowCount|https://github.com/apache/calcite/blob/4ef9ffe0d4afb80fe95e66d4d9c9e6f5939f70a8/core/src/main/java/org/apache/calcite/rel/metadata/RelMdMaxRowCount.java]
returns zero to derive that the relation is empty.
Implementation wise there are various alternatives to consider:
* modify the existing PruneEmptyRule to consider metadata;
* add new pruning rules relying exclusively on metadata;
* add new rules checking metadata and turning the empty relation to an empty
{{Values}} which can then be handled by existing rules
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-2736) ReduceExpressionsRule never reduces dynamic expressions but this should be configurable
[
https://issues.apache.org/jira/browse/CALCITE-2736?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17613594#comment-17613594
]
Stamatis Zampetakis commented on CALCITE-2736:
--
As part of this change the following method was modified (a new parameter was
added) and during upgrade to 1.28.0 there were compilation errors cause there
were callers to this method. This is a breaking change and should be part of
the release notes.
{code:java}
protected static boolean reduceExpressions(RelNode rel, List expList,
RelOptPredicateList predicates, boolean unknownAsFalse,
boolean matchNullability)
{code}
Reminder: usually we follow the telescopic pattern; first deprecate the old
method and add new method with new parameter.
> ReduceExpressionsRule never reduces dynamic expressions but this should be
> configurable
> ---
>
> Key: CALCITE-2736
> URL: https://issues.apache.org/jira/browse/CALCITE-2736
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Jacques Nadeau
>Assignee: Jacques Nadeau
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.28.0
>
> Time Spent: 2h
> Remaining Estimate: 0h
>
> There are situations where it is helpful to reduce dynamic SqlCalls. Right
> now, the ReduceExpressionsRule always avoids doing this. We should enhance
> the rule so this can be configurable depending on where in planning the rule
> is used.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-4640) Propagate table scan hints to JDBC
[
https://issues.apache.org/jira/browse/CALCITE-4640?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17613416#comment-17613416
]
Stamatis Zampetakis commented on CALCITE-4640:
--
The change modified the only constructor of {{JdbcTableScan}} causing
compilation problems to projects extending the latter. It is a breaking change
that was not marked in the release notes of 1.28.0. The usual pattern is
deprecate the old constructor and add a new one when modifying RelNodes. Please
keep backward compatibility in mind when proposing/accepting changes for
Calcite.
> Propagate table scan hints to JDBC
> --
>
> Key: CALCITE-4640
> URL: https://issues.apache.org/jira/browse/CALCITE-4640
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.27.0
>Reporter: Ulrich Kramer
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.28.0
>
> Time Spent: 40m
> Remaining Estimate: 0h
>
> We would like to use table scan hints to pass [parameters and variables to
> HANA
> views|https://help.sap.com/viewer/88fe5f56472e40cca6ef3c3dcab4855b/2.0.04/en-US/fafb3ea432e54fca9eff11648df5bccd.html].
> It should be possible to convert the following Calcite SQL
> {code}
> SELECT * FROM VIEW /*+ PLACEHOLDERS("$$PARAMETER_1$$"='Test') */
> {code}
> using a special {{SqlDialect}} to a HANA specific statement
> {code}
> SELECT * FROM VIEW ('PLACEHOLDER' = ('$$PARAMETER_1$$', 'Test'))
> {code}
> See also my [mail in the mailing
> list|https://mail-archives.apache.org/mod_mbox/calcite-dev/202105.mbox/%3CD161B82F-5DEC-49A2-A873-4817F4DEB15F%40contoso.com%3E].
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5310) JSON_OBJECT in scalar sub-query throws AssertionError
[
https://issues.apache.org/jira/browse/CALCITE-5310?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17613382#comment-17613382
]
Stamatis Zampetakis commented on CALCITE-5310:
--
Thanks for looking into this [~libenchao]!
Small suggestion: When you mention other jira tickets as CALCITE- it is a
good idea to create also explicit links between the tickets so that the
correlation can be seen from both tickets. As it is right now someone who is
following CALCITE-5127 cannot see that this ticket is somehow related to that
one.
> JSON_OBJECT in scalar sub-query throws AssertionError
> -
>
> Key: CALCITE-5310
> URL: https://issues.apache.org/jira/browse/CALCITE-5310
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Benchao Li
>Assignee: Benchao Li
>Priority: Major
> Fix For: 1.33.0
>
>
> This is reported originally from CALCITE-5127.
> {code:sql}
> select (select json_object('a': v))
> from unnest(array[1,2,null]) as T(v)
> {code}
> will lead to exception
> {code:java}
> java.lang.AssertionError: No assign rules for SYMBOL defined
> at
> org.apache.calcite.sql.type.SqlTypeMappingRule.canApplyFrom(SqlTypeMappingRule.java:57)
> at
> org.apache.calcite.sql.type.SqlTypeUtil.canCastFrom(SqlTypeUtil.java:956)
> at
> org.apache.calcite.sql.type.SqlTypeFactoryImpl.leastRestrictiveByCast(SqlTypeFactoryImpl.java:184)
> at
> org.apache.calcite.sql.type.SqlTypeFactoryImpl.leastRestrictive(SqlTypeFactoryImpl.java:164)
> at
> org.apache.calcite.sql.fun.SqlCaseOperator.inferTypeFromOperands(SqlCaseOperator.java:320)
> at
> org.apache.calcite.sql.fun.SqlCaseOperator.inferReturnType(SqlCaseOperator.java:223)
> at
> org.apache.calcite.rex.RexBuilder.deriveReturnType(RexBuilder.java:290)
> at org.apache.calcite.rex.RexBuilder.makeCall(RexBuilder.java:264)
> at org.apache.calcite.rex.RexBuilder.makeCall(RexBuilder.java:277)
> at
> org.apache.calcite.sql2rel.RelDecorrelator$RemoveCorrelationRexShuttle.createCaseExpression(RelDecorrelator.java:1731)
> at
> org.apache.calcite.sql2rel.RelDecorrelator$RemoveCorrelationRexShuttle.visitLiteral(RelDecorrelator.java:1798)
> at
> org.apache.calcite.sql2rel.RelDecorrelator$RemoveCorrelationRexShuttle.visitLiteral(RelDecorrelator.java:1672)
> at org.apache.calcite.rex.RexLiteral.accept(RexLiteral.java:1217)
> at org.apache.calcite.rex.RexShuttle.visitList(RexShuttle.java:163)
> at
> org.apache.calcite.sql2rel.RelDecorrelator$RemoveCorrelationRexShuttle.visitCall(RelDecorrelator.java:1807)
> at
> org.apache.calcite.sql2rel.RelDecorrelator$RemoveCorrelationRexShuttle.visitCall(RelDecorrelator.java:1672)
> at org.apache.calcite.rex.RexCall.accept(RexCall.java:189)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.removeCorrelationExpr(RelDecorrelator.java:405)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.projectJoinOutputWithNullability(RelDecorrelator.java:1429)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.access$900(RelDecorrelator.java:147)
> at
> org.apache.calcite.sql2rel.RelDecorrelator$RemoveCorrelationForScalarProjectRule.onMatch(RelDecorrelator.java:2144)
> at
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:337)
> at org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:556)
> at
> org.apache.calcite.plan.hep.HepPlanner.applyRules(HepPlanner.java:420)
> at
> org.apache.calcite.plan.hep.HepPlanner.executeRuleInstance(HepPlanner.java:243)
> at
> org.apache.calcite.plan.hep.HepInstruction$RuleInstance$State.execute(HepInstruction.java:178)
> at
> org.apache.calcite.plan.hep.HepPlanner.lambda$executeProgram$0(HepPlanner.java:211)
> at
> com.google.common.collect.ImmutableList.forEach(ImmutableList.java:422)
> at
> org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:210)
> at
> org.apache.calcite.plan.hep.HepProgram$State.execute(HepProgram.java:118)
> at
> org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:205)
> at
> org.apache.calcite.plan.hep.HepPlanner.findBestExp(HepPlanner.java:191)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.removeCorrelationViaRule(RelDecorrelator.java:378)
> at
> org.apache.calcite.sql2rel.RelDecorrelator.decorrelateQuery(RelDecorrelator.java:221)
> at
> org.apache.calcite.tools.Programs$DecorrelateProgram.run(Programs.java:362)
> at
> org.apache.calcite.tools.Programs$SequenceProgram.run(Programs.java:337)
> at org.apache.calcite.prepare.Prepare.optimize(Prepare.java:177)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepar
[jira] [Updated] (CALCITE-1045) Decorrelate sub-queries in Project and Join
[ https://issues.apache.org/jira/browse/CALCITE-1045?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Stamatis Zampetakis updated CALCITE-1045: - Fix Version/s: (was: 1.33.0) > Decorrelate sub-queries in Project and Join > --- > > Key: CALCITE-1045 > URL: https://issues.apache.org/jira/browse/CALCITE-1045 > Project: Calcite > Issue Type: Bug >Reporter: Julian Hyde >Assignee: Benchao Li >Priority: Major > Labels: pull-request-available, sub-query > Time Spent: 2h > Remaining Estimate: 0h > > In CALCITE-816 we created a representation of sub-query in the relational > algebra, RexSubQuery, so that we could defer expanding and de-correlating > sub-queries until after SqlNode tree has been converted to RelNode tree. But > the RelDecorrelator can only reliably handle sub-queries that occur in a > Filter (such as WHERE EXISTS or WHERE IN). > This task would except RelDecorrelator to handle sub-queries in Project > (which occur with scalar sub-queries in the SELECT clause) and sub-queries in > Join (which occur with scalar sub-queries in the ON clause of a JOIN). -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-1045) Decorrelate sub-queries in Project and Join
[ https://issues.apache.org/jira/browse/CALCITE-1045?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17612776#comment-17612776 ] Stamatis Zampetakis commented on CALCITE-1045: -- OK, I just remembered that there are more references to CALCITE-1045 in particular inside RelOptRulesTest so I am going to re-open this ticket. > Decorrelate sub-queries in Project and Join > --- > > Key: CALCITE-1045 > URL: https://issues.apache.org/jira/browse/CALCITE-1045 > Project: Calcite > Issue Type: Bug >Reporter: Julian Hyde >Assignee: Benchao Li >Priority: Major > Labels: pull-request-available, sub-query > Fix For: 1.33.0 > > Time Spent: 2h > Remaining Estimate: 0h > > In CALCITE-816 we created a representation of sub-query in the relational > algebra, RexSubQuery, so that we could defer expanding and de-correlating > sub-queries until after SqlNode tree has been converted to RelNode tree. But > the RelDecorrelator can only reliably handle sub-queries that occur in a > Filter (such as WHERE EXISTS or WHERE IN). > This task would except RelDecorrelator to handle sub-queries in Project > (which occur with scalar sub-queries in the SELECT clause) and sub-queries in > Join (which occur with scalar sub-queries in the ON clause of a JOIN). -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Reopened] (CALCITE-1045) Decorrelate sub-queries in Project and Join
[ https://issues.apache.org/jira/browse/CALCITE-1045?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Stamatis Zampetakis reopened CALCITE-1045: -- > Decorrelate sub-queries in Project and Join > --- > > Key: CALCITE-1045 > URL: https://issues.apache.org/jira/browse/CALCITE-1045 > Project: Calcite > Issue Type: Bug >Reporter: Julian Hyde >Assignee: Benchao Li >Priority: Major > Labels: pull-request-available, sub-query > Fix For: 1.33.0 > > Time Spent: 2h > Remaining Estimate: 0h > > In CALCITE-816 we created a representation of sub-query in the relational > algebra, RexSubQuery, so that we could defer expanding and de-correlating > sub-queries until after SqlNode tree has been converted to RelNode tree. But > the RelDecorrelator can only reliably handle sub-queries that occur in a > Filter (such as WHERE EXISTS or WHERE IN). > This task would except RelDecorrelator to handle sub-queries in Project > (which occur with scalar sub-queries in the SELECT clause) and sub-queries in > Join (which occur with scalar sub-queries in the ON clause of a JOIN). -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-1045) Decorrelate sub-queries in Project and Join
[ https://issues.apache.org/jira/browse/CALCITE-1045?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17612771#comment-17612771 ] Stamatis Zampetakis commented on CALCITE-1045: -- [~jamesstarr] We discussed under CALCITE-5127 and agreed to close this ticket as part of that change since all tests which were skipped with fixed.calcite1045 are passing. I am pretty sure there are many more bugs to fix but we could do it using new and more specific JIRAs. Having said that if someone wants to keep this JIRA open for some reason, I'm fine with that as well; don't have strong preferences. > Decorrelate sub-queries in Project and Join > --- > > Key: CALCITE-1045 > URL: https://issues.apache.org/jira/browse/CALCITE-1045 > Project: Calcite > Issue Type: Bug >Reporter: Julian Hyde >Assignee: Benchao Li >Priority: Major > Labels: pull-request-available, sub-query > Fix For: 1.33.0 > > Time Spent: 2h > Remaining Estimate: 0h > > In CALCITE-816 we created a representation of sub-query in the relational > algebra, RexSubQuery, so that we could defer expanding and de-correlating > sub-queries until after SqlNode tree has been converted to RelNode tree. But > the RelDecorrelator can only reliably handle sub-queries that occur in a > Filter (such as WHERE EXISTS or WHERE IN). > This task would except RelDecorrelator to handle sub-queries in Project > (which occur with scalar sub-queries in the SELECT clause) and sub-queries in > Join (which occur with scalar sub-queries in the ON clause of a JOIN). -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Resolved] (CALCITE-1045) Decorrelate sub-queries in Project and Join
[
https://issues.apache.org/jira/browse/CALCITE-1045?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Stamatis Zampetakis resolved CALCITE-1045.
--
Fix Version/s: 1.33.0
Assignee: Benchao Li
Resolution: Fixed
All test cases that were skipped with {{fixed.calcite1045}} are passing in
https://github.com/apache/calcite/commit/c2407f59c32d1690d16b641d556bb27f8f1783ac
thus I am marking this as resolved.
> Decorrelate sub-queries in Project and Join
> ---
>
> Key: CALCITE-1045
> URL: https://issues.apache.org/jira/browse/CALCITE-1045
> Project: Calcite
> Issue Type: Bug
>Reporter: Julian Hyde
>Assignee: Benchao Li
>Priority: Major
> Labels: pull-request-available, sub-query
> Fix For: 1.33.0
>
> Time Spent: 2h
> Remaining Estimate: 0h
>
> In CALCITE-816 we created a representation of sub-query in the relational
> algebra, RexSubQuery, so that we could defer expanding and de-correlating
> sub-queries until after SqlNode tree has been converted to RelNode tree. But
> the RelDecorrelator can only reliably handle sub-queries that occur in a
> Filter (such as WHERE EXISTS or WHERE IN).
> This task would except RelDecorrelator to handle sub-queries in Project
> (which occur with scalar sub-queries in the SELECT clause) and sub-queries in
> Join (which occur with scalar sub-queries in the ON clause of a JOIN).
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-5127) Support correlation variables in Project
[
https://issues.apache.org/jira/browse/CALCITE-5127?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Stamatis Zampetakis resolved CALCITE-5127.
--
Resolution: Fixed
Fixed in
https://github.com/apache/calcite/commit/c2407f59c32d1690d16b641d556bb27f8f1783ac.
[~libenchao] many thanks for the great work, discussions, patience and
persistence to pull this ticket through the end; it is a valuable contribution
to Calcite.
[~korlov] thanks for the initial work and the various discussions on the topic;
I added you as co-author in the commit.
[~dmsysolyatin] thanks for reporting the problem and following up to provide
any additional details requested.
[~julianhyde] thanks for the extra pair of eyes and helpful pointers to move
this forward.
> Support correlation variables in Project
>
>
> Key: CALCITE-5127
> URL: https://issues.apache.org/jira/browse/CALCITE-5127
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Dmitry Sysolyatin
>Assignee: Benchao Li
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.33.0
>
> Time Spent: 3h 50m
> Remaining Estimate: 0h
>
> The following queries fail:
> {code}
> SELECT ARRAY(SELECT s.x) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT ARRAY(SELECT * FROM UNNEST(s.x) y) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT (SELECT CARDINALITY(s.x) LIMIT 1) FROM (SELECT ARRAY[1,2,3] as x) s;
>
> {code}
> With exception:
> {code}
> Caused by: java.lang.ClassCastException: java.lang.Integer cannot be cast to
> java.util.List
> {code}
> You can find test cases for this task in
> https://github.com/apache/calcite/commit/27e68ded2c3bea7d7af73dd1dc156e46fb3591a8
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5127) Support correlation variables in Project
[
https://issues.apache.org/jira/browse/CALCITE-5127?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Stamatis Zampetakis updated CALCITE-5127:
-
Summary: Support correlation variables in Project (was: Error when
executing query with correlated sub-query in SELECT clause)
> Support correlation variables in Project
>
>
> Key: CALCITE-5127
> URL: https://issues.apache.org/jira/browse/CALCITE-5127
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Dmitry Sysolyatin
>Assignee: Benchao Li
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.33.0
>
> Time Spent: 3h 50m
> Remaining Estimate: 0h
>
> The following queries fail:
> {code}
> SELECT ARRAY(SELECT s.x) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT ARRAY(SELECT * FROM UNNEST(s.x) y) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT (SELECT CARDINALITY(s.x) LIMIT 1) FROM (SELECT ARRAY[1,2,3] as x) s;
>
> {code}
> With exception:
> {code}
> Caused by: java.lang.ClassCastException: java.lang.Integer cannot be cast to
> java.util.List
> {code}
> You can find test cases for this task in
> https://github.com/apache/calcite/commit/27e68ded2c3bea7d7af73dd1dc156e46fb3591a8
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5300) Cannot access org.immutables.value.Value error when extending standard rules
[
https://issues.apache.org/jira/browse/CALCITE-5300?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17610080#comment-17610080
]
Stamatis Zampetakis commented on CALCITE-5300:
--
A workaround to the problem is to add an explicit dependency to immutables
value annotations:
{noformat}
org.immutables
value-annotations
2.8.8
{noformat}
However, it is not great to force other projects to add a dependency to
immutables in order to compile their code. From my perspective this looks like
a Calcite problem and we should find a way to deal with it.
> Cannot access org.immutables.value.Value error when extending standard rules
>
>
> Key: CALCITE-5300
> URL: https://issues.apache.org/jira/browse/CALCITE-5300
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.32.0
> Environment: Tested with JDK 8, 11, 17, 19
>
>Reporter: Stamatis Zampetakis
>Priority: Major
>
> A compilation error is raised when a project depends on calcite-core (version
> 1.28.0 onwards) and log4j2-core and there is a custom rule extending some
> standard rule from Calcite, which uses the [Immutables
> library](https://immutables.github.io/) {{@Value}} annotation.
> The error is the following:
> {noformat}
> [ERROR] cannot access org.immutables.value.Value
> [ERROR] class file for org.immutables.value.Value not found
> {noformat}
> To reproduce the problem it suffices to do the following.
> 1. Create a maven project with the following dependencies:
> {noformat}
>
> org.apache.calcite
> calcite-core
> 1.32.0
>
>
> org.apache.logging.log4j
> log4j-core
> 2.17.1
>
> {noformat}
> 2. Write a rule that extends some standard rule from Calcite.
> {code:java}
> import org.apache.calcite.rel.rules.ProjectMergeRule;
> public class MyProjectMergeRule extends ProjectMergeRule {
> public MyProjectMergeRule(Config config) {
> super(config);
> }
> }
> {code}
> 3. Compile the project
> {code:java}
> mvn clean install
> {code}
> The problem is not dependent to a specific JDK or maven version but does
> require log4j-core to be in the classpath; log4j-core has an annotation
> processor and this somehow triggers the problem when the annotations are not
> in the classpath (LOG4J2-3609).
> Calcite does not declare the immutable annotations as an implementation
> dependency thus the annatations are not present in (the classpath of) 3rd
> party projects depending on calcite-core.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5300) Cannot access org.immutables.value.Value error when extending standard rules
[
https://issues.apache.org/jira/browse/CALCITE-5300?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17610076#comment-17610076
]
Stamatis Zampetakis commented on CALCITE-5300:
--
A minimal maven project reproducing the problem can be found here:
https://github.com/zabetak/CALCITE-5300
> Cannot access org.immutables.value.Value error when extending standard rules
>
>
> Key: CALCITE-5300
> URL: https://issues.apache.org/jira/browse/CALCITE-5300
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.32.0
> Environment: Tested with JDK 8, 11, 17, 19
>
>Reporter: Stamatis Zampetakis
>Priority: Major
>
> A compilation error is raised when a project depends on calcite-core (version
> 1.28.0 onwards) and log4j2-core and there is a custom rule extending some
> standard rule from Calcite, which uses the [Immutables
> library](https://immutables.github.io/) {{@Value}} annotation.
> The error is the following:
> {noformat}
> [ERROR] cannot access org.immutables.value.Value
> [ERROR] class file for org.immutables.value.Value not found
> {noformat}
> To reproduce the problem it suffices to do the following.
> 1. Create a maven project with the following dependencies:
> {noformat}
>
> org.apache.calcite
> calcite-core
> 1.32.0
>
>
> org.apache.logging.log4j
> log4j-core
> 2.17.1
>
> {noformat}
> 2. Write a rule that extends some standard rule from Calcite.
> {code:java}
> import org.apache.calcite.rel.rules.ProjectMergeRule;
> public class MyProjectMergeRule extends ProjectMergeRule {
> public MyProjectMergeRule(Config config) {
> super(config);
> }
> }
> {code}
> 3. Compile the project
> {code:java}
> mvn clean install
> {code}
> The problem is not dependent to a specific JDK or maven version but does
> require log4j-core to be in the classpath; log4j-core has an annotation
> processor and this somehow triggers the problem when the annotations are not
> in the classpath (LOG4J2-3609).
> Calcite does not declare the immutable annotations as an implementation
> dependency thus the annatations are not present in (the classpath of) 3rd
> party projects depending on calcite-core.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5300) Cannot access org.immutables.value.Value error when extending standard rules
Stamatis Zampetakis created CALCITE-5300:
Summary: Cannot access org.immutables.value.Value error when
extending standard rules
Key: CALCITE-5300
URL: https://issues.apache.org/jira/browse/CALCITE-5300
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.32.0
Environment: Tested with JDK 8, 11, 17, 19
Reporter: Stamatis Zampetakis
A compilation error is raised when a project depends on calcite-core (version
1.28.0 onwards) and log4j2-core and there is a custom rule extending some
standard rule from Calcite, which uses the [Immutables
library](https://immutables.github.io/) {{@Value}} annotation.
The error is the following:
{noformat}
[ERROR] cannot access org.immutables.value.Value
[ERROR] class file for org.immutables.value.Value not found
{noformat}
To reproduce the problem it suffices to do the following.
1. Create a maven project with the following dependencies:
{noformat}
org.apache.calcite
calcite-core
1.32.0
org.apache.logging.log4j
log4j-core
2.17.1
{noformat}
2. Write a rule that extends some standard rule from Calcite.
{code:java}
import org.apache.calcite.rel.rules.ProjectMergeRule;
public class MyProjectMergeRule extends ProjectMergeRule {
public MyProjectMergeRule(Config config) {
super(config);
}
}
{code}
3. Compile the project
{code:java}
mvn clean install
{code}
The problem is not dependent to a specific JDK or maven version but does
require log4j-core to be in the classpath; log4j-core has an annotation
processor and this somehow triggers the problem when the annotations are not in
the classpath (LOG4J2-3609).
Calcite does not declare the immutable annotations as an implementation
dependency thus the annatations are not present in (the classpath of) 3rd party
projects depending on calcite-core.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5127) Error when executing query with correlated sub-query in SELECT clause
[
https://issues.apache.org/jira/browse/CALCITE-5127?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17609420#comment-17609420
]
Stamatis Zampetakis commented on CALCITE-5127:
--
bq. Of course, RelFieldTrimmer is not used in a single way as Calcite did now,
especially for down stream projects.
This is why I started having second thoughts. I don't want to change the
behavior of the trimmer since the problem is not really there.
On the other hand, the approach to add correlation variables in project is
something that has been proposed multiple times in the past few months. It may
be time to accept that we need this :)
> Error when executing query with correlated sub-query in SELECT clause
> -
>
> Key: CALCITE-5127
> URL: https://issues.apache.org/jira/browse/CALCITE-5127
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Dmitry Sysolyatin
>Assignee: Benchao Li
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.33.0
>
> Time Spent: 2h 50m
> Remaining Estimate: 0h
>
> The following queries fail:
> {code}
> SELECT ARRAY(SELECT s.x) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT ARRAY(SELECT * FROM UNNEST(s.x) y) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT (SELECT CARDINALITY(s.x) LIMIT 1) FROM (SELECT ARRAY[1,2,3] as x) s;
>
> {code}
> With exception:
> {code}
> Caused by: java.lang.ClassCastException: java.lang.Integer cannot be cast to
> java.util.List
> {code}
> You can find test cases for this task in
> https://github.com/apache/calcite/commit/27e68ded2c3bea7d7af73dd1dc156e46fb3591a8
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-4509) RexSimplify can't simplify redundant predicate in filter merge rule
[
https://issues.apache.org/jira/browse/CALCITE-4509?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17609417#comment-17609417
]
Stamatis Zampetakis commented on CALCITE-4509:
--
I haven't tried to replicate the problem in the descriptions so I cannot say if
it is resolved or not. [~yanjing.wang] can you reproduce the problem with the
latest master?
> RexSimplify can't simplify redundant predicate in filter merge rule
> ---
>
> Key: CALCITE-4509
> URL: https://issues.apache.org/jira/browse/CALCITE-4509
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.26.0
> Environment: jvm: open-jdk8
>Reporter: yanjing.wang
>Priority: Major
>
> my sql is :
> String sql = "select b.ds, count(distinct a.id), count(a.name) from users a
> join (select * from depts where ds > '0' and ds >= 20200101 ) b on a.dept_id
> = b.id and a.ds = b.ds where a.ds > '1' and a.ds > 20200103 and a.product_id
> in (1,2,3) group by b.ds having count(a.name) > 5 limit 5000";
>
> the rel is
> 46:LogicalJoin(condition=[AND(=($3, $7), =($5, $10))], joinType=[inner])
> 41:LogicalFilter(condition=[>=($5, 20200101)])
> 36:LogicalFilter(condition=[AND(SEARCH($5, Sarg[(20200103..+∞)]),
> SEARCH($6, Sarg[1, 2, 3]), IS NOT NULL($5))])
> 1:JdbcTableScan(table=[[default, users]])
> 42:LogicalFilter(condition=[AND(SEARCH($3, Sarg[(20200103..+∞)]), IS NOT
> NULL($3))])
> 33:LogicalFilter(condition=[>=($3, 20200101)])
> 3:JdbcTableScan(table=[[default, depts]])
>
> after JOIN_PUSH_TRANSITIVE_PREDICATES and FILTER_MERGE rule etc optimize the
> rel,
> 46:LogicalJoin(condition=[AND(=($3, $7), =($5, $10))], joinType=[inner])
> 36:LogicalFilter(condition=[AND(SEARCH($5, Sarg[(20200103..+∞)]),
> SEARCH($6, Sarg[1, 2, 3]), IS NOT NULL($5))])
> 1:JdbcTableScan(table=[[default, users]])
> 49:LogicalFilter(condition=[AND(>=($3, 20200101), SEARCH($3,
> Sarg[(20200103..+∞)]))])
> 3:JdbcTableScan(table=[[default, depts]])
>
> we can find :
> $5 condition has been merged but $3 has redundant '>=($3, 20200101)'.
>
> i dive into the source code and find 'simplifyAnd' method in 'RexSimplify'
> class has the line:
>
> {code:java}
> if (sargCollector.map.values().stream().anyMatch(b -> b.complexity() > 1)) {
> operands.clear(); terms.forEach(t ->
> operands.add(sargCollector.fix(rexBuilder, t))); }
> {code}
>
> so users table has $6 filter with complexity > 1, so the operands get
> refilled.
> but depts table only has $3 filter complexity = 1 , so operands don't change,
> and conditions has not been merged.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-4972) Subfields of array columns containing structs are not qualified in getFieldOrigins
[
https://issues.apache.org/jira/browse/CALCITE-4972?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Stamatis Zampetakis resolved CALCITE-4972.
--
Fix Version/s: 1.33.0
Resolution: Fixed
Fixed in
https://github.com/apache/calcite/commit/7b8b2b96041d0cf7bf69cae336659087739fa495.
Thanks for the PR [~mgthesecond]!
> Subfields of array columns containing structs are not qualified in
> getFieldOrigins
> --
>
> Key: CALCITE-4972
> URL: https://issues.apache.org/jira/browse/CALCITE-4972
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.28.0
>Reporter: Mark Grey
>Assignee: Mark Grey
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.33.0
>
> Time Spent: 40m
> Remaining Estimate: 0h
>
> Attempting to follow on the [suggestions from Julian in this
> thread|https://lists.apache.org/thread/pdpqtzy5mtzlpdbhwghbty6cj0jgc86l],
> found a potential improvement.
> Given a table schema containing a repeated array of structs and a query
> projecting fields of that struct via UNNEST, the present implementation of
> {{getFieldOrigins}} in {{SqlValidatorImpl}} will return a list representation
> suggesting that the subfield is actually a member of the top level table row
> type.
> For example, when using the [DEPT_NESTED table from the
> MockCatalogReaderSimple|https://github.com/apache/calcite/blob/master/testkit/src/main/java/org/apache/calcite/test/catalog/MockCatalogReaderSimple.java#L152-L159]
> within tests with the following query:
> {code:sql}
> select e.empno from dept_nested, unnest(employees) as e
> {code}
> The return value currently is:
> {code:java}
> {CATALOG.SALES.DEPT_NESTED.EMPNO}
> {code}
> Rather than:
> {code:java}
> {CATALOG.SALES.DEPT_NESTED.EMPLOYEES.EMPNO}
> {code}
> No such column {{EMPNO}} exists in the root schema of the DEPT_NESTED table.
> [The javadoc for the method
> |https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/sql/validate/SqlValidator.java#L717-L728]however
> suggests that the return from this method should always be four entries
> long, although it would seem this doesn't account for nesting. Is this
> intended behavior, or should {{column}} entry of this list be of variable
> length?
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5127) Error when executing query with correlated sub-query in SELECT clause
[
https://issues.apache.org/jira/browse/CALCITE-5127?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17608166#comment-17608166
]
Stamatis Zampetakis commented on CALCITE-5127:
--
The new tests added for {{RelFieldTrimmer}} in
https://github.com/apache/calcite/pull/2911, made me realize that the changes
there significantly reduces the capacity of field trimmer to work when there
are sub-queries. Normally, we can trim project fields even when it has
sub-queries but we cannot safely trim its inputs cause there may be problems
when we have correlation variables.
I also realized that the trimmer already contains
[code|https://github.com/apache/calcite/blob/f8dd80fcd2d4d92767936fe7b3dae349f2f0ec40/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java#L212]
to handle the case that some relational expression may reference fields
through correlation variables.
The latter means that if {{Project}} was returning the variables that are set,
the field trimmer would work without problems. It appears that my previous
comment where I stated that nobody uses {{Project#getVariablesSet}} was wrong.
I am really sorry to change my mind again but in the end the
https://github.com/apache/calcite/pull/2813 seems a better solution for the
problems we discovered now and for the long term.
[~libenchao] Let's reopen PR#2813 address the last few minor comments that
remain and merge that one if you agree.
> Error when executing query with correlated sub-query in SELECT clause
> -
>
> Key: CALCITE-5127
> URL: https://issues.apache.org/jira/browse/CALCITE-5127
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Dmitry Sysolyatin
>Assignee: Benchao Li
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.33.0
>
> Time Spent: 2h 40m
> Remaining Estimate: 0h
>
> The following queries fail:
> {code}
> SELECT ARRAY(SELECT s.x) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT ARRAY(SELECT * FROM UNNEST(s.x) y) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT (SELECT CARDINALITY(s.x) LIMIT 1) FROM (SELECT ARRAY[1,2,3] as x) s;
>
> {code}
> With exception:
> {code}
> Caused by: java.lang.ClassCastException: java.lang.Integer cannot be cast to
> java.util.List
> {code}
> You can find test cases for this task in
> https://github.com/apache/calcite/commit/27e68ded2c3bea7d7af73dd1dc156e46fb3591a8
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-5293) Support general set operators in PruneEmptyRules
[
https://issues.apache.org/jira/browse/CALCITE-5293?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Stamatis Zampetakis resolved CALCITE-5293.
--
Fix Version/s: 1.33.0
Resolution: Fixed
Fixed in
https://github.com/apache/calcite/commit/f8dd80fcd2d4d92767936fe7b3dae349f2f0ec40.
Thanks for the PR [~kkasa]!
> Support general set operators in PruneEmptyRules
>
>
> Key: CALCITE-5293
> URL: https://issues.apache.org/jira/browse/CALCITE-5293
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Krisztian Kasa
>Assignee: Krisztian Kasa
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.33.0
>
> Time Spent: 1h 20m
> Remaining Estimate: 0h
>
> Current implementation of {{PruneEmptyRules}} expects Logical set operators:
> {{LogicalUnion}}, {{LogicalMinus}} and {{LogicalIntersect}}.
> The goal is to improve the reusability of these rules in other projects which
> using Calcite and its custom operators derived from {{Union}}, {{Minus}} and
> {{Intersect}}.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-4509) RexSimplify can't simplify redundant predicate in filter merge rule
[
https://issues.apache.org/jira/browse/CALCITE-4509?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17607671#comment-17607671
]
Stamatis Zampetakis commented on CALCITE-4509:
--
[~pauljackson123] I added the following test in {{RexProgramTest}} in current
master and it passes successfully:
{code:java}
@Test void testSimplifyAndOr() {
RexNode exp =
and(eq(vInt(), literal("A")),
or(
eq(vInt(), literal("A")),
eq(vInt(),literal("B";
checkSimplify(exp,"=(?0.int0, 'A')");
}
{code}
RexSimplify is able to transform correctly the expression below, which seems to
match the example you posted above.
{noformat}
AND(=(?0.int0, 'A'), OR(=(?0.int0, 'A'), =(?0.int0, 'B'))) -> =(?0.int0, 'A')
{noformat}
Either the improvement was added very recently (after 1.32.0) or there is a
problem in some other part of the code notably in the rules you mentioned.
> RexSimplify can't simplify redundant predicate in filter merge rule
> ---
>
> Key: CALCITE-4509
> URL: https://issues.apache.org/jira/browse/CALCITE-4509
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.26.0
> Environment: jvm: open-jdk8
>Reporter: yanjing.wang
>Priority: Major
>
> my sql is :
> String sql = "select b.ds, count(distinct a.id), count(a.name) from users a
> join (select * from depts where ds > '0' and ds >= 20200101 ) b on a.dept_id
> = b.id and a.ds = b.ds where a.ds > '1' and a.ds > 20200103 and a.product_id
> in (1,2,3) group by b.ds having count(a.name) > 5 limit 5000";
>
> the rel is
> 46:LogicalJoin(condition=[AND(=($3, $7), =($5, $10))], joinType=[inner])
> 41:LogicalFilter(condition=[>=($5, 20200101)])
> 36:LogicalFilter(condition=[AND(SEARCH($5, Sarg[(20200103..+∞)]),
> SEARCH($6, Sarg[1, 2, 3]), IS NOT NULL($5))])
> 1:JdbcTableScan(table=[[default, users]])
> 42:LogicalFilter(condition=[AND(SEARCH($3, Sarg[(20200103..+∞)]), IS NOT
> NULL($3))])
> 33:LogicalFilter(condition=[>=($3, 20200101)])
> 3:JdbcTableScan(table=[[default, depts]])
>
> after JOIN_PUSH_TRANSITIVE_PREDICATES and FILTER_MERGE rule etc optimize the
> rel,
> 46:LogicalJoin(condition=[AND(=($3, $7), =($5, $10))], joinType=[inner])
> 36:LogicalFilter(condition=[AND(SEARCH($5, Sarg[(20200103..+∞)]),
> SEARCH($6, Sarg[1, 2, 3]), IS NOT NULL($5))])
> 1:JdbcTableScan(table=[[default, users]])
> 49:LogicalFilter(condition=[AND(>=($3, 20200101), SEARCH($3,
> Sarg[(20200103..+∞)]))])
> 3:JdbcTableScan(table=[[default, depts]])
>
> we can find :
> $5 condition has been merged but $3 has redundant '>=($3, 20200101)'.
>
> i dive into the source code and find 'simplifyAnd' method in 'RexSimplify'
> class has the line:
>
> {code:java}
> if (sargCollector.map.values().stream().anyMatch(b -> b.complexity() > 1)) {
> operands.clear(); terms.forEach(t ->
> operands.add(sargCollector.fix(rexBuilder, t))); }
> {code}
>
> so users table has $6 filter with complexity > 1, so the operands get
> refilled.
> but depts table only has $3 filter complexity = 1 , so operands don't change,
> and conditions has not been merged.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-4972) Subfields of array columns containing structs are not qualified in getFieldOrigins
[
https://issues.apache.org/jira/browse/CALCITE-4972?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17607651#comment-17607651
]
Stamatis Zampetakis commented on CALCITE-4972:
--
I see that there are already two +1 on this from Julian, and Michael, so I plan
to merge this as soon as CI come back green. Please ping me if I forget.
> Subfields of array columns containing structs are not qualified in
> getFieldOrigins
> --
>
> Key: CALCITE-4972
> URL: https://issues.apache.org/jira/browse/CALCITE-4972
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.28.0
>Reporter: Mark Grey
>Priority: Minor
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Attempting to follow on the [suggestions from Julian in this
> thread|https://lists.apache.org/thread/pdpqtzy5mtzlpdbhwghbty6cj0jgc86l],
> found a potential improvement.
> Given a table schema containing a repeated array of structs and a query
> projecting fields of that struct via UNNEST, the present implementation of
> {{getFieldOrigins}} in {{SqlValidatorImpl}} will return a list representation
> suggesting that the subfield is actually a member of the top level table row
> type.
> For example, when using the [DEPT_NESTED table from the
> MockCatalogReaderSimple|https://github.com/apache/calcite/blob/master/testkit/src/main/java/org/apache/calcite/test/catalog/MockCatalogReaderSimple.java#L152-L159]
> within tests with the following query:
> {code:sql}
> select e.empno from dept_nested, unnest(employees) as e
> {code}
> The return value currently is:
> {code:java}
> {CATALOG.SALES.DEPT_NESTED.EMPNO}
> {code}
> Rather than:
> {code:java}
> {CATALOG.SALES.DEPT_NESTED.EMPLOYEES.EMPNO}
> {code}
> No such column {{EMPNO}} exists in the root schema of the DEPT_NESTED table.
> [The javadoc for the method
> |https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/sql/validate/SqlValidator.java#L717-L728]however
> suggests that the return from this method should always be four entries
> long, although it would seem this doesn't account for nesting. Is this
> intended behavior, or should {{column}} entry of this list be of variable
> length?
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-4509) RexSimplify can't simplify redundant predicate in filter merge rule
[
https://issues.apache.org/jira/browse/CALCITE-4509?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17607180#comment-17607180
]
Stamatis Zampetakis commented on CALCITE-4509:
--
[~pauljackson123] we are currently in Calcite 1.32.0. Did you test with this
version?
> RexSimplify can't simplify redundant predicate in filter merge rule
> ---
>
> Key: CALCITE-4509
> URL: https://issues.apache.org/jira/browse/CALCITE-4509
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.26.0
> Environment: jvm: open-jdk8
>Reporter: yanjing.wang
>Priority: Major
>
> my sql is :
> String sql = "select b.ds, count(distinct a.id), count(a.name) from users a
> join (select * from depts where ds > '0' and ds >= 20200101 ) b on a.dept_id
> = b.id and a.ds = b.ds where a.ds > '1' and a.ds > 20200103 and a.product_id
> in (1,2,3) group by b.ds having count(a.name) > 5 limit 5000";
>
> the rel is
> 46:LogicalJoin(condition=[AND(=($3, $7), =($5, $10))], joinType=[inner])
> 41:LogicalFilter(condition=[>=($5, 20200101)])
> 36:LogicalFilter(condition=[AND(SEARCH($5, Sarg[(20200103..+∞)]),
> SEARCH($6, Sarg[1, 2, 3]), IS NOT NULL($5))])
> 1:JdbcTableScan(table=[[default, users]])
> 42:LogicalFilter(condition=[AND(SEARCH($3, Sarg[(20200103..+∞)]), IS NOT
> NULL($3))])
> 33:LogicalFilter(condition=[>=($3, 20200101)])
> 3:JdbcTableScan(table=[[default, depts]])
>
> after JOIN_PUSH_TRANSITIVE_PREDICATES and FILTER_MERGE rule etc optimize the
> rel,
> 46:LogicalJoin(condition=[AND(=($3, $7), =($5, $10))], joinType=[inner])
> 36:LogicalFilter(condition=[AND(SEARCH($5, Sarg[(20200103..+∞)]),
> SEARCH($6, Sarg[1, 2, 3]), IS NOT NULL($5))])
> 1:JdbcTableScan(table=[[default, users]])
> 49:LogicalFilter(condition=[AND(>=($3, 20200101), SEARCH($3,
> Sarg[(20200103..+∞)]))])
> 3:JdbcTableScan(table=[[default, depts]])
>
> we can find :
> $5 condition has been merged but $3 has redundant '>=($3, 20200101)'.
>
> i dive into the source code and find 'simplifyAnd' method in 'RexSimplify'
> class has the line:
>
> {code:java}
> if (sargCollector.map.values().stream().anyMatch(b -> b.complexity() > 1)) {
> operands.clear(); terms.forEach(t ->
> operands.add(sargCollector.fix(rexBuilder, t))); }
> {code}
>
> so users table has $6 filter with complexity > 1, so the operands get
> refilled.
> but depts table only has $3 filter complexity = 1 , so operands don't change,
> and conditions has not been merged.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5127) Error when executing query with correlated sub-query in SELECT clause
[
https://issues.apache.org/jira/browse/CALCITE-5127?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Stamatis Zampetakis updated CALCITE-5127:
-
Summary: Error when executing query with correlated sub-query in SELECT
clause (was: Error when executing query with subquery in select list that uses
outer column of array type)
> Error when executing query with correlated sub-query in SELECT clause
> -
>
> Key: CALCITE-5127
> URL: https://issues.apache.org/jira/browse/CALCITE-5127
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Dmitry Sysolyatin
>Assignee: Benchao Li
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.33.0
>
> Time Spent: 1h 50m
> Remaining Estimate: 0h
>
> The following queries fail:
> {code}
> SELECT ARRAY(SELECT s.x) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT ARRAY(SELECT * FROM UNNEST(s.x) y) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT (SELECT CARDINALITY(s.x) LIMIT 1) FROM (SELECT ARRAY[1,2,3] as x) s;
>
> {code}
> With exception:
> {code}
> Caused by: java.lang.ClassCastException: java.lang.Integer cannot be cast to
> java.util.List
> {code}
> You can find test cases for this task in
> https://github.com/apache/calcite/commit/27e68ded2c3bea7d7af73dd1dc156e46fb3591a8
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5287) SQL reference page is missing from website
[ https://issues.apache.org/jira/browse/CALCITE-5287?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17605918#comment-17605918 ] Stamatis Zampetakis commented on CALCITE-5287: -- Link to the respective discussion in the dev list: https://lists.apache.org/thread/4y9jylmd8xjvhof9fqm9tr7c91o9jmqm > SQL reference page is missing from website > -- > > Key: CALCITE-5287 > URL: https://issues.apache.org/jira/browse/CALCITE-5287 > Project: Calcite > Issue Type: Bug > Components: site >Affects Versions: 1.31.0 >Reporter: Stamatis Zampetakis >Assignee: Stamatis Zampetakis >Priority: Major > > Looks like SQL reference [1] page is missing. At least the link on a main [2] > page is broken. > Besides, "SQL language" option is missed from a side navigator on a docs [3] > page as well. > [1] https://calcite.apache.org/docs/reference.html > [2] https://calcite.apache.org > [3] https://calcite.apache.org/docs/ > Problem discovered by [~korlov] -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-5287) SQL reference page is missing from website
Stamatis Zampetakis created CALCITE-5287: Summary: SQL reference page is missing from website Key: CALCITE-5287 URL: https://issues.apache.org/jira/browse/CALCITE-5287 Project: Calcite Issue Type: Bug Components: site Affects Versions: 1.31.0 Reporter: Stamatis Zampetakis Assignee: Stamatis Zampetakis Looks like SQL reference [1] page is missing. At least the link on a main [2] page is broken. Besides, "SQL language" option is missed from a side navigator on a docs [3] page as well. [1] https://calcite.apache.org/docs/reference.html [2] https://calcite.apache.org [3] https://calcite.apache.org/docs/ Problem discovered by [~korlov] -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5285) CalcReduceExpressionsRule may transform to same rel
[
https://issues.apache.org/jira/browse/CALCITE-5285?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17605744#comment-17605744
]
Stamatis Zampetakis commented on CALCITE-5285:
--
Thanks for the explanation [~krzysztof.slusarski], now I understand better the
problem. I guess the problem surfaces when an identical rel is generated and at
the same time we call {{call.getPlanner().prune(rel)}}.
> CalcReduceExpressionsRule may transform to same rel
> ---
>
> Key: CALCITE-5285
> URL: https://issues.apache.org/jira/browse/CALCITE-5285
> Project: Calcite
> Issue Type: Bug
> Components: server
>Reporter: Krzysztof Ślusarski
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.33.0
>
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Method {{CalcReduceExpressionsRule#onMatch}} enters the branch and calculates
> {{reduceExpressions()}} condition. If that one is true it creates new calc.
> Unfortunately logic in {{reduceExpressions()}} is different then in the rest
> of the method and it can create a calc that is equal to the input.
> Example query {{{}SELECT floor(this / 1) FROM test{}}}.
> Input calc contains program with exprs:
> {{0 = \{RexInputRef@11348} "$0"}}
> {{1 = \{RexInputRef@11349} "$1"}}
> {{2 = \{RexCall@11350} "CAST($t1):BIGINT(32)"}}
> {{3 = \{RexLiteral@11351} "1:BIGINT(32)"}}
> {{4 = \{RexCall@11352} "/($t2, $t3)"}}
> {{5 = \{RexCall@11353} "FLOOR($t4)"}}
> The one on index 4 can be simplified (the division can be removed). That
> condition is met in {{{}reduceExpressions(){}}}, but following logic in
> {{onMatch()}} doesn't simplify that expression.
> This makes the output calc equals to input one which breaks the rest of query
> optimizing.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5285) CalcReduceExpressionsRule may transform to same rel
[
https://issues.apache.org/jira/browse/CALCITE-5285?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17604724#comment-17604724
]
Stamatis Zampetakis commented on CALCITE-5285:
--
bq. This makes the output calc equals to input one which breaks the rest of
query optimizing.
bq.
Can you elaborate what exactly is broken by transforming to the same relation?
Please add some tests in the PR to demonstrate the problem and also ensure that
your solutions fixes it.
> CalcReduceExpressionsRule may transform to same rel
> ---
>
> Key: CALCITE-5285
> URL: https://issues.apache.org/jira/browse/CALCITE-5285
> Project: Calcite
> Issue Type: Bug
> Components: server
>Reporter: Krzysztof Ślusarski
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.33.0
>
> Time Spent: 20m
> Remaining Estimate: 0h
>
> Method {{CalcReduceExpressionsRule#onMatch}} enters the branch and calculates
> {{reduceExpressions()}} condition. If that one is true it creates new calc.
> Unfortunately logic in {{reduceExpressions()}} is different then in the rest
> of the method and it can create a calc that is equal to the input.
> Example query {{{}SELECT floor(this / 1) FROM test{}}}.
> Input calc contains program with exprs:
> {{0 = \{RexInputRef@11348} "$0"}}
> {{1 = \{RexInputRef@11349} "$1"}}
> {{2 = \{RexCall@11350} "CAST($t1):BIGINT(32)"}}
> {{3 = \{RexLiteral@11351} "1:BIGINT(32)"}}
> {{4 = \{RexCall@11352} "/($t2, $t3)"}}
> {{5 = \{RexCall@11353} "FLOOR($t4)"}}
> The one on index 4 can be simplified (the division can be removed). That
> condition is met in {{{}reduceExpressions(){}}}, but following logic in
> {{onMatch()}} doesn't simplify that expression.
> This makes the output calc equals to input one which breaks the rest of query
> optimizing.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5282) JdbcValues should add CAST on NULL values
[
https://issues.apache.org/jira/browse/CALCITE-5282?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17603541#comment-17603541
]
Stamatis Zampetakis commented on CALCITE-5282:
--
As soon as the PR gets merged in Hive we are planning to create respective
CALCITE jiras to port the issues in calcite. Hopefully they should be present
in the next Calcite release.
> JdbcValues should add CAST on NULL values
> -
>
> Key: CALCITE-5282
> URL: https://issues.apache.org/jira/browse/CALCITE-5282
> Project: Calcite
> Issue Type: Bug
> Environment: Calcite 1.13.1 on Mac
>Reporter: Ulrich Kramer
>Priority: Major
>
> The following unit test in {{JdbcAdapterTest.java}} is working fine
> {code:java}
> @Test void testNullValuesPlan() {
> final String sql = "select empno, ename, e.deptno, dname\n"
> + "from scott.emp e left outer join (select * from scott.dept where 0
> = 1) d\n"
> + "on e.deptno = d.deptno";
> final String explain = "PLAN=JdbcToEnumerableConverter\n" +
> " JdbcProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$2], DNAME=[$4])\n" +
> "JdbcJoin(condition=[=($2, $3)], joinType=[left])\n" +
> " JdbcProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$7])\n" +
> "JdbcTableScan(table=[[SCOTT, EMP]])\n" +
> " JdbcValues(tuples=[[]])\n\n";
> final String jdbcSql = "SELECT \"t\".\"EMPNO\", \"t\".\"ENAME\",
> \"t\".\"DEPTNO\", \"t0\".\"DNAME\"\n" +
> "FROM (SELECT \"EMPNO\", \"ENAME\", \"DEPTNO\"\n" +
> "FROM \"SCOTT\".\"EMP\") AS \"t\"\n" +
> "LEFT JOIN (SELECT *\n" +
> "FROM (VALUES (NULL, NULL)) AS \"t\" (\"DEPTNO\", \"DNAME\")\n" +
> "WHERE 1 = 0) AS \"t0\" ON \"t\".\"DEPTNO\" = \"t0\".\"DEPTNO\"";
> CalciteAssert.model(JdbcTest.SCOTT_MODEL)
> .query(sql)
> .explainContains(explain)
> .runs();
> }
> {code}
> The problem is that {{JdbcValues}} is loosing the type information for each
> {{NULL}} column
> and postgres complains about that. Inside the join condition {{t.DEPTNO =
> to.DEPTNO}}. postgres doesn't know the type of {{t.DEPTNO}}, assumes it's of
> type {{TEXT}} and raises an error like {{ERROR: operator does not exist: text
> = integer, Hint: No operator matches the given name and argument types. You
> might need to add explicit type casts.}}
> Would it be possible to add a {{CAST}} in case of {{NULL}} values in
> {{JdbcValues}}.
> Changing {{VALUES (NULL, NULL)}} to {{VALUES (CAST(NULL AS ...), CAST(NULL
> AS ...))}} in the resulting SQL statement.
> If it is appreciated, we could provide a PR.
> If you are asking yourself, why we are doing something strange like {{WHERE 1
> = 0}}: We are applying row level access policies as WHERE condition. In this
> case the user has no access to the table at all.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5284) JDBC rules create incorrect plan
[
https://issues.apache.org/jira/browse/CALCITE-5284?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17603427#comment-17603427
]
Stamatis Zampetakis commented on CALCITE-5284:
--
[~kramerul] Incorrect is different from inefficient. Can you please clarify
what is the case here.
> JDBC rules create incorrect plan
>
>
> Key: CALCITE-5284
> URL: https://issues.apache.org/jira/browse/CALCITE-5284
> Project: Calcite
> Issue Type: Bug
> Environment: Calcite 1.31.1. on Mac
>Reporter: Ulrich Kramer
>Priority: Major
>
> The following unit test for {{JdbcAdapterTest}} fails:
> {code:java}
> @Test void testOffset() {
> CalciteAssert.model(FoodmartSchema.FOODMART_MODEL)
> .query("select * from \"sales_fact_1997\" limit 10 offset 20")
> .explainContains("PLAN=JdbcToEnumerableConverter\n" +
> " JdbcSort(offset=[20], fetch=[10])\n" +
> "JdbcTableScan(table=[[foodmart, sales_fact_1997]])")
> .runs();
> }
> {code}
> For an offset less than 13, the correct plan is created. With an offset above
> 13, the plan looks like this:
> {code}
> EnumerableLimit(offset=[20], fetch=[10])
> JdbcToEnumerableConverter
> JdbcTableScan(table=[[foodmart, sales_fact_1997]])
> {code}
> which can lead to enormous latency times, since the entire table is loaded
> via JDBC.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5282) JdbcValues should add CAST on NULL values
[
https://issues.apache.org/jira/browse/CALCITE-5282?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17603425#comment-17603425
]
Stamatis Zampetakis commented on CALCITE-5282:
--
In a similar situation in HIVE-26524, when there is a left/right join with
empty values it seems possible to remove the join and values branch altogether.
I am guessing that if the empty values disappears from the plan maybe the
problem reported here (for Postgres and other DBMS) may also go away.
[~kramerul] please have a look in the PR for HIVE-26524 and share your
thoughts; at some point there is also some code to introduce typed nulls as you
propose here.
> JdbcValues should add CAST on NULL values
> -
>
> Key: CALCITE-5282
> URL: https://issues.apache.org/jira/browse/CALCITE-5282
> Project: Calcite
> Issue Type: Bug
> Environment: Calcite 1.13.1 on Mac
>Reporter: Ulrich Kramer
>Priority: Major
>
> The following unit test in {{JdbcAdapterTest.java}} is working fine
> {code:java}
> @Test void testNullValuesPlan() {
> final String sql = "select empno, ename, e.deptno, dname\n"
> + "from scott.emp e left outer join (select * from scott.dept where 0
> = 1) d\n"
> + "on e.deptno = d.deptno";
> final String explain = "PLAN=JdbcToEnumerableConverter\n" +
> " JdbcProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$2], DNAME=[$4])\n" +
> "JdbcJoin(condition=[=($2, $3)], joinType=[left])\n" +
> " JdbcProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$7])\n" +
> "JdbcTableScan(table=[[SCOTT, EMP]])\n" +
> " JdbcValues(tuples=[[]])\n\n";
> final String jdbcSql = "SELECT \"t\".\"EMPNO\", \"t\".\"ENAME\",
> \"t\".\"DEPTNO\", \"t0\".\"DNAME\"\n" +
> "FROM (SELECT \"EMPNO\", \"ENAME\", \"DEPTNO\"\n" +
> "FROM \"SCOTT\".\"EMP\") AS \"t\"\n" +
> "LEFT JOIN (SELECT *\n" +
> "FROM (VALUES (NULL, NULL)) AS \"t\" (\"DEPTNO\", \"DNAME\")\n" +
> "WHERE 1 = 0) AS \"t0\" ON \"t\".\"DEPTNO\" = \"t0\".\"DEPTNO\"";
> CalciteAssert.model(JdbcTest.SCOTT_MODEL)
> .query(sql)
> .explainContains(explain)
> .runs();
> }
> {code}
> The problem is that {{JdbcValues}} is loosing the type information for each
> {{NULL}} column
> and postgres complains about that. Inside the join condition {{t.DEPTNO =
> to.DEPTNO}}. postgres doesn't know the type of {{t.DEPTNO}}, assumes it's of
> type {{TEXT}} and raises an error like {{ERROR: operator does not exist: text
> = integer, Hint: No operator matches the given name and argument types. You
> might need to add explicit type casts.}}
> Would it be possible to add a {{CAST}} in case of {{NULL}} values in
> {{JdbcValues}}.
> Changing {{VALUES (NULL, NULL)}} to {{VALUES (CAST(NULL AS ...), CAST(NULL
> AS ...))}} in the resulting SQL statement.
> If it is appreciated, we could provide a PR.
> If you are asking yourself, why we are doing something strange like {{WHERE 1
> = 0}}: We are applying row level access policies as WHERE condition. In this
> case the user has no access to the table at all.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5127) Error when executing query with subquery in select list that uses outer column of array type
[
https://issues.apache.org/jira/browse/CALCITE-5127?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17603257#comment-17603257
]
Stamatis Zampetakis commented on CALCITE-5127:
--
The PR allows to set explicitly variables inside the \{{Project}}, they are
technically available via \{{Project#getVariablesSet}}, but nobody really uses
this info at the moment.
> Error when executing query with subquery in select list that uses outer
> column of array type
>
>
> Key: CALCITE-5127
> URL: https://issues.apache.org/jira/browse/CALCITE-5127
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Dmitry Sysolyatin
>Assignee: Benchao Li
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.33.0
>
> Time Spent: 1h 20m
> Remaining Estimate: 0h
>
> The following queries fail:
> {code}
> SELECT ARRAY(SELECT s.x) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT ARRAY(SELECT * FROM UNNEST(s.x) y) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT (SELECT CARDINALITY(s.x) LIMIT 1) FROM (SELECT ARRAY[1,2,3] as x) s;
>
> {code}
> With exception:
> {code}
> Caused by: java.lang.ClassCastException: java.lang.Integer cannot be cast to
> java.util.List
> {code}
> You can find test cases for this task in
> https://github.com/apache/calcite/commit/27e68ded2c3bea7d7af73dd1dc156e46fb3591a8
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5127) Error when executing query with subquery in select list that uses outer column of array type
[
https://issues.apache.org/jira/browse/CALCITE-5127?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17603256#comment-17603256
]
Stamatis Zampetakis commented on CALCITE-5127:
--
[~julianhyde] I have been following this case for a little while so let me try
to summarize briefly the situation (from my perspective).
The problem described in this Jira is really a bug:
{_}SqlToRelConverter/RelBuilder wrongly removes used fields when merging
projections{_}. If we prevent the merge of the projection then all queries
reported here run fine.
Allowing project to set correlating variables is in some sense already possible
without merging the PR. The difference is that the current PR makes it explicit
by adding a new attribute in the {{Project}} class. This change though is not
really needed to solve the bug reported here.
I am pushing back a bit regarding the changes that introduce the new attribute
in {{Project}} since they lead to changes everywhere (RelBuilder APIs, RelNode
APIs, rules, adapters, downstream projects, etc.) and so far I don't see an
obvious advantage in doing this now.
> Error when executing query with subquery in select list that uses outer
> column of array type
>
>
> Key: CALCITE-5127
> URL: https://issues.apache.org/jira/browse/CALCITE-5127
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Dmitry Sysolyatin
>Assignee: Benchao Li
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.33.0
>
> Time Spent: 1h 20m
> Remaining Estimate: 0h
>
> The following queries fail:
> {code}
> SELECT ARRAY(SELECT s.x) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT ARRAY(SELECT * FROM UNNEST(s.x) y) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT (SELECT CARDINALITY(s.x) LIMIT 1) FROM (SELECT ARRAY[1,2,3] as x) s;
>
> {code}
> With exception:
> {code}
> Caused by: java.lang.ClassCastException: java.lang.Integer cannot be cast to
> java.util.List
> {code}
> You can find test cases for this task in
> https://github.com/apache/calcite/commit/27e68ded2c3bea7d7af73dd1dc156e46fb3591a8
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5127) Error when executing query with subquery in select list that uses outer column of array type
[
https://issues.apache.org/jira/browse/CALCITE-5127?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17601756#comment-17601756
]
Stamatis Zampetakis commented on CALCITE-5127:
--
[~libenchao][~julianhyde] As far as I can see the changes proposed in the PR
should not really affect significantly the disabled tests in CALCITE-1045
("fixed.calcite1045" ). Actually, I run the disabled tests in 1.22.0 release
(537b8dbb4b58c61b6c573eb07a51b8d38896a1ff) and they seem to run there as well;
I didn't verify meticulously plan and results but there are no
exceptions/failures. It would be good to enable them but maybe it is better to
do it as a separate jira/commit to avoid giving the false impression that this
PR fixed everything.
[~libenchao] Both ideas below sound reasonable to me. It's a trade-off between
performance/simplicity and accuracy. In this case, I have a slight preference
for performance/simplicity (sub-query approach) but leaving the final decision
to you since you have worked more into this problem.
{quote}Maybe another more coarse-grained way is to identify RexSubQuery without
distinguishing the correlated subquery and non-correlated subquery? Or we can
add another version of RexUtil.containsCorrelation which will go deeply into
the RexSubQuery referenced RelNode?
{quote}
> Error when executing query with subquery in select list that uses outer
> column of array type
>
>
> Key: CALCITE-5127
> URL: https://issues.apache.org/jira/browse/CALCITE-5127
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Dmitry Sysolyatin
>Assignee: Benchao Li
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.32.0
>
> Time Spent: 1h 20m
> Remaining Estimate: 0h
>
> The following queries fail:
> {code}
> SELECT ARRAY(SELECT s.x) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT ARRAY(SELECT * FROM UNNEST(s.x) y) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT (SELECT CARDINALITY(s.x) LIMIT 1) FROM (SELECT ARRAY[1,2,3] as x) s;
>
> {code}
> With exception:
> {code}
> Caused by: java.lang.ClassCastException: java.lang.Integer cannot be cast to
> java.util.List
> {code}
> You can find test cases for this task in
> https://github.com/apache/calcite/commit/27e68ded2c3bea7d7af73dd1dc156e46fb3591a8
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5127) Error when executing query with subquery in select list that uses outer column of array type
[
https://issues.apache.org/jira/browse/CALCITE-5127?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17600701#comment-17600701
]
Stamatis Zampetakis commented on CALCITE-5127:
--
Hi [~libenchao], as I was going again over the PR and in particular as I was
trying to reply to your comment
[here|https://github.com/apache/calcite/pull/2813/files#r945410944] I stuck
again to the point if we really need to add variablesSet in the {{Project}} to
fix the bug reported in this JIRA.
As you correctly identified, the queries reported here are failing due to the
incorrect removal of some projections when there are correlation variables on
the top project. The proposed fix, addresses the problem at the RelBuilder
level by disabling project elimination when the top project has correlation
variables. At its current state the PR solves the problem but at the same time
introduces changes at many places and some of them breaking.
An alternative way of fixing the problem would be to use
{{RexUtil.containsCorrelation}} in {{RelBuilder}} (and other places as needed)
to prevent the merge/removal of projections. It may be less fine grained than
the current proposal (it does not distinguish between variables set and used)
but seems like a less intrusive change with smaller risk for side effects. Do
you see a problem with this approach?
> Error when executing query with subquery in select list that uses outer
> column of array type
>
>
> Key: CALCITE-5127
> URL: https://issues.apache.org/jira/browse/CALCITE-5127
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Dmitry Sysolyatin
>Assignee: Benchao Li
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.32.0
>
> Time Spent: 1h 10m
> Remaining Estimate: 0h
>
> The following queries fail:
> {code}
> SELECT ARRAY(SELECT s.x) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT ARRAY(SELECT * FROM UNNEST(s.x) y) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT (SELECT CARDINALITY(s.x) LIMIT 1) FROM (SELECT ARRAY[1,2,3] as x) s;
>
> {code}
> With exception:
> {code}
> Caused by: java.lang.ClassCastException: java.lang.Integer cannot be cast to
> java.util.List
> {code}
> You can find test cases for this task in
> https://github.com/apache/calcite/commit/27e68ded2c3bea7d7af73dd1dc156e46fb3591a8
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5231) Unable to create Oracle JDBC connection in DBeaver using Calcite-core jar
[
https://issues.apache.org/jira/browse/CALCITE-5231?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17579572#comment-17579572
]
Stamatis Zampetakis commented on CALCITE-5231:
--
There was a somewhat related discussion about shading (fat) jars here:
https://lists.apache.org/thread/8g5fpd4kpt7zhvf4l9397p9k95fny8tq
Personally, I am not much in favor of maintaining a shared calcite jar on the
main Calcite repo.
> Unable to create Oracle JDBC connection in DBeaver using Calcite-core jar
> -
>
> Key: CALCITE-5231
> URL: https://issues.apache.org/jira/browse/CALCITE-5231
> Project: Calcite
> Issue Type: Bug
> Components: core, jdbc-adapter
>Affects Versions: 1.22.0
>Reporter: kartik
>Priority: Major
> Attachments: Calcite JDBC-DBeaver-connection-Issue.png,
> JDBC-calcite-connection-Error-Message.png
>
>
> I am trying to connect my Oracle database using calcite in DBever.
> Driver class used : org.apache.calcite.jdbc.Driver
> URL - jdbc:oracle:thin:XXX:1521:
>
> But when I use same same URL and driver with main java program . it works
> fine .
> Connection con= DriverManager.getConnection("jdbc:calcite:schemaType=JDBC;
> schema.jdbcUser=USERNAME; " +
> "schema.jdbcPassword=XX;
> schema.jdbcUrl=jdbc:oracle:thin:@X:1521:ORCL;
> schema.conformance=ORACLE_10; schema.fun=oracle" );
>
>
> I have cloned calcite project and running main program on that . Please let
> me know if I have done any mistake .
>
> After Oracle , I need to work for mySql using calcite .
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5231) Unable to create Oracle JDBC connection in DBeaver
[
https://issues.apache.org/jira/browse/CALCITE-5231?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17577411#comment-17577411
]
Stamatis Zampetakis commented on CALCITE-5231:
--
It looks like a problem that you should investigate with the DBeaver team.
Unfortunately, I don't have time to look into this.
> Unable to create Oracle JDBC connection in DBeaver
> --
>
> Key: CALCITE-5231
> URL: https://issues.apache.org/jira/browse/CALCITE-5231
> Project: Calcite
> Issue Type: Bug
> Components: core, jdbc-adapter
>Affects Versions: 1.22.0
>Reporter: kartik
>Priority: Major
> Attachments: Calcite JDBC-DBeaver-connection-Issue.png,
> JDBC-calcite-connection-Error-Message.png
>
>
> I am trying to connect my Oracle database using calcite in DBever.
> Driver class used : org.apache.calcite.jdbc.Driver
> URL - jdbc:oracle:thin:XXX:1521:
>
> But when I use same same URL and driver with main java program . it works
> fine .
> Connection con= DriverManager.getConnection("jdbc:calcite:schemaType=JDBC;
> schema.jdbcUser=USERNAME; " +
> "schema.jdbcPassword=XX;
> schema.jdbcUrl=jdbc:oracle:thin:@X:1521:ORCL;
> schema.conformance=ORACLE_10; schema.fun=oracle" );
>
>
> I have cloned calcite project and running main program on that . Please let
> me know if I have done any mistake .
>
> After Oracle , I need to work for mySql using calcite .
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5231) Unable to create Oracle JDBC connection in DBeaver
[
https://issues.apache.org/jira/browse/CALCITE-5231?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17577296#comment-17577296
]
Stamatis Zampetakis commented on CALCITE-5231:
--
According to the error message it seems that required jars are not in the
classpath. This is not really a Calcite problem but a configuration problem of
DBeaver.
By the way [~gavinray] wrote an [interesting
article|https://gist.github.com/GavinRay97/44fcd0f17e5c62ed6e7880fed0b0042e]
for using Calcite with Squirrel. There may be similarities in configuring
Squirrel and DBeaver so possible worth checking out.
> Unable to create Oracle JDBC connection in DBeaver
> --
>
> Key: CALCITE-5231
> URL: https://issues.apache.org/jira/browse/CALCITE-5231
> Project: Calcite
> Issue Type: Bug
> Components: core, jdbc-adapter
>Affects Versions: 1.22.0
>Reporter: kartik
>Priority: Major
> Attachments: Calcite JDBC-DBeaver-connection-Issue.png,
> JDBC-calcite-connection-Error-Message.png
>
>
> I am trying to connect my Oracle database using calcite in DBever.
> Driver class used : org.apache.calcite.jdbc.Driver
> URL - jdbc:oracle:thin:XXX:1521:
>
> But when I use same same URL and driver with main java program . it works
> fine .
> Connection con= DriverManager.getConnection("jdbc:calcite:schemaType=JDBC;
> schema.jdbcUser=USERNAME; " +
> "schema.jdbcPassword=XX;
> schema.jdbcUrl=jdbc:oracle:thin:@X:1521:ORCL;
> schema.conformance=ORACLE_10; schema.fun=oracle" );
>
>
> I have cloned calcite project and running main program on that . Please let
> me know if I have done any mistake .
>
> After Oracle , I need to work for mySql using calcite .
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5212) Attempting to create a Decimal with scale specified but precision not specified results in Decimal with scale 0
[
https://issues.apache.org/jira/browse/CALCITE-5212?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17572505#comment-17572505
]
Stamatis Zampetakis commented on CALCITE-5212:
--
If I understand well the problem, you somehow managed to create a DECIMAL(0,
5). Most DBMS systems raise an error/exception when the user attempts to create
a type with zero precision.
{noformat}
postgres=# CREATE TABLE dec_table_test (dec_col DECIMAL(0,5));
ERROR: NUMERIC precision 0 must be between 1 and 1000
LINE 1: CREATE TABLE dec_table_test (dec_col DECIMAL(0,5));
{noformat}
All in all, I believe this is a bug that should be fixed but not really have
time to work on this myself.
> Attempting to create a Decimal with scale specified but precision not
> specified results in Decimal with scale 0
> ---
>
> Key: CALCITE-5212
> URL: https://issues.apache.org/jira/browse/CALCITE-5212
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.30.0
>Reporter: Ian Bertolacci
>Priority: Minor
>
> (Frankly, I'm not sure if there is really any need to change this behavior,
> as scale without precision is a very odd case that doesn't really make a lot
> of sense in the larger picture (the system we're building on top of uses
> precision to mean digits _before_ the decimal-point and not the total number
> of digits, and we're still playing around with if and how that should be
> expressed in Calcite's type-system), but I'm still writing this out anyways
> just in-case it is of interest to anyone or if someone else encounters this
> behavior and want's an explanation.)
>
> {{`RelDataTypeFactoryImpl.createSqlType(SqlTypeName.DECIMAL,
> RelDataType.PRECISION_NOT_SPECIFIED, 5)`}} could be expected to create a
> Decimal with scale 5.
> However it instead creates a Decimal with scale 0.†
> This happens because:
> # RelDataType does not include scale in its digest if precision is not
> specified.
> Thus: Two Decimal types with different scales but no precision have *the
> same* digest.
> # RelDataType uses the digest to compute a hash code.
> Thus: Two Decimal types with different scales but no precision have *the
> same* hash code.
> # {{`RelDataTypeFactoryImpl.canonize(RelDataType)`}} is used to look up a
> type in {{`DATATYPE_CACHE`}} which uses the hash code as the key.
> Thus: Two Decimal types with different scales but no precision *will collide*
> in the cache.
> # There is no check in RelDataTypeFactoryImpl or SqlTypeFactoryImpl's
> canonize method to assert that the type returned from the cache actually
> matches the type given as a parameter.
> Thus: If the parameter type matches a type in the cache, the cache'd type
> will *always* be returned, even if it the two types are different.
> # RelDataTypeFactoryImpl and its child classes (such as SqlTypeFactoryImpl)
> all use the result canonize *at the end* of the type creation functions (such
> as createSqlType) as the result of the function.
> Thus: If the function creates an intermediate Decimal type with scale but no
> precision, the result of the function will be whatever that intermediate type
> matches with in the cache, which could have any scale.
> † I think it might actually be possible for a scale other than zero to be
> returned if a Decimal with non-zero scale and unspecified precision has been
> created before the Decimal with zero scale and unspecified precision has been
> created.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Assigned] (CALCITE-5217) Implement unparseSqlIntervalLiteral in the Firebolt dialect
[
https://issues.apache.org/jira/browse/CALCITE-5217?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Stamatis Zampetakis reassigned CALCITE-5217:
Assignee: Aymeric Dispa
> Implement unparseSqlIntervalLiteral in the Firebolt dialect
> ---
>
> Key: CALCITE-5217
> URL: https://issues.apache.org/jira/browse/CALCITE-5217
> Project: Calcite
> Issue Type: Bug
>Reporter: Aymeric Dispa
>Assignee: Aymeric Dispa
>Priority: Major
> Labels: pull-request-available
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> The syntax used by Firebolt for the operator INTERVAL does not match with the
> query generated. To fix it, the method unparseSqlIntervalLiteral must be
> implemented in the dialect.
> What we get with the current dialect:
> {code:java}
> + INTERVAL '1' DAY {code}
>
> Format supported by Firebolt
> {code:java}
> + INTERVAL '1 DAY' {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-5221) Upgrade Calcite to Avatica 1.22
[ https://issues.apache.org/jira/browse/CALCITE-5221?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Stamatis Zampetakis resolved CALCITE-5221. -- Resolution: Fixed Fixed in https://github.com/apache/calcite/commit/657a3d352ff81ef54f2bc0be6884363b49741305. > Upgrade Calcite to Avatica 1.22 > --- > > Key: CALCITE-5221 > URL: https://issues.apache.org/jira/browse/CALCITE-5221 > Project: Calcite > Issue Type: Task >Reporter: Ruben Q L >Assignee: Stamatis Zampetakis >Priority: Blocker > Fix For: 1.31.0 > > > Upgrade Calcite to Avatica 1.22, which includes the vulnerability fix in > CALCITE-5218. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-5221) Upgrade Calcite to Avatica 1.22
[ https://issues.apache.org/jira/browse/CALCITE-5221?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Stamatis Zampetakis updated CALCITE-5221: - Description: Upgrade Calcite to Avatica 1.22, which includes the vulnerability fix in CALCITE-5218. (was: Upgrade Calcite to Avatica 1.22, which includes a vulnerability fix.) > Upgrade Calcite to Avatica 1.22 > --- > > Key: CALCITE-5221 > URL: https://issues.apache.org/jira/browse/CALCITE-5221 > Project: Calcite > Issue Type: Task >Reporter: Ruben Q L >Assignee: Stamatis Zampetakis >Priority: Blocker > Fix For: 1.31.0 > > > Upgrade Calcite to Avatica 1.22, which includes the vulnerability fix in > CALCITE-5218. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5221) Upgrade Calcite to Avatica 1.22
[ https://issues.apache.org/jira/browse/CALCITE-5221?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17572417#comment-17572417 ] Stamatis Zampetakis commented on CALCITE-5221: -- PR is ready, I will merge this when all tests run. > Upgrade Calcite to Avatica 1.22 > --- > > Key: CALCITE-5221 > URL: https://issues.apache.org/jira/browse/CALCITE-5221 > Project: Calcite > Issue Type: Task >Reporter: Ruben Q L >Assignee: Stamatis Zampetakis >Priority: Blocker > Fix For: 1.31.0 > > > Upgrade Calcite to Avatica 1.22, which includes a vulnerability fix. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Assigned] (CALCITE-5221) Upgrade Calcite to Avatica 1.22
[ https://issues.apache.org/jira/browse/CALCITE-5221?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Stamatis Zampetakis reassigned CALCITE-5221: Assignee: Stamatis Zampetakis > Upgrade Calcite to Avatica 1.22 > --- > > Key: CALCITE-5221 > URL: https://issues.apache.org/jira/browse/CALCITE-5221 > Project: Calcite > Issue Type: Task >Reporter: Ruben Q L >Assignee: Stamatis Zampetakis >Priority: Blocker > Fix For: 1.31.0 > > > Upgrade Calcite to Avatica 1.22, which includes a vulnerability fix. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-3890) Derive IS NOT NULL filter for the inputs of inner join
[
https://issues.apache.org/jira/browse/CALCITE-3890?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17570946#comment-17570946
]
Stamatis Zampetakis commented on CALCITE-3890:
--
Hey [~Chunwei Lei] , I didn't notice that this Jira was resolved, thats great!
I see that there was a discussion before to take inspiration from
[HiveJoinAddNotNullRule|https://github.com/apache/hive/blob/1c3406ea598e0c2d866b20747602c1a01fa5a425/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveJoinAddNotNullRule.java]
. Can you clarify the similarities/differences (if any) between the new rule
that you added and the one used in Hive. I am asking cause ideally I would like
to avoid maintaining the same code in multiple places.
> Derive IS NOT NULL filter for the inputs of inner join
> ---
>
> Key: CALCITE-3890
> URL: https://issues.apache.org/jira/browse/CALCITE-3890
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Chunwei Lei
>Assignee: Chunwei Lei
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.31.0
>
> Time Spent: 4h 50m
> Remaining Estimate: 0h
>
> We can infer IS NOT NULL predicate from join which implies some columns may
> not be null. For instance,
>
> {code:java}
> select * from a join b on a.id = b.id;
> {code}
> we can infer a.id is not null/b.id is not null and push down them into the
> child node of the join. Then it becomes
> {code:java}
> select * from (select* from a where id is null) t1 join (select * from b
> where id is not null) on t1.id = t2.id;
> {code}
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5127) Error when executing query with subquery in select list that uses outer column of array type
[
https://issues.apache.org/jira/browse/CALCITE-5127?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17558400#comment-17558400
]
Stamatis Zampetakis commented on CALCITE-5127:
--
For this PR let's focus on {{Project}} operator; we can discuss changes to
\{{Filter}} separately and when it becomes necessary.
I agree that we should update the javadoc of \{{RelNode#getVariablesSet}}; I
think I made a similar comment to the previous PR by Konstantin.
Rules should not drop correlation variables from Project silently; if we can
find an easy way to assert this somehow in the planner would be great but let's
treat this better in a follow-up JIRA. It might not be such of an issue after
all since as we discussed previously after \{{SubQueryRemoveRule}} we should
not have correlations in \{{Project}}.
In general, it is common practice to run \{{SubQueryRemoveRule}} at some
preprocessing step since rules in general do not currently apply inside a
subquery. If somebody works with a plan where subqueries are not expanded they
probably miss many interesting transformations.
> Error when executing query with subquery in select list that uses outer
> column of array type
>
>
> Key: CALCITE-5127
> URL: https://issues.apache.org/jira/browse/CALCITE-5127
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Dmitry Sysolyatin
>Assignee: Benchao Li
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.32.0
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> The following queries fail:
> {code}
> SELECT ARRAY(SELECT s.x) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT ARRAY(SELECT * FROM UNNEST(s.x) y) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT (SELECT CARDINALITY(s.x) LIMIT 1) FROM (SELECT ARRAY[1,2,3] as x) s;
>
> {code}
> With exception:
> {code}
> Caused by: java.lang.ClassCastException: java.lang.Integer cannot be cast to
> java.util.List
> {code}
> You can find test cases for this task in
> https://github.com/apache/calcite/commit/27e68ded2c3bea7d7af73dd1dc156e46fb3591a8
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Commented] (CALCITE-5127) Error when executing query with subquery in select list that uses outer column of array type
[
https://issues.apache.org/jira/browse/CALCITE-5127?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17555602#comment-17555602
]
Stamatis Zampetakis commented on CALCITE-5127:
--
Thanks for summary [~libenchao] !
Regarding the decision to put correlation variables into {{LogicalFilter}} and
not {{Filter}} (respectively for {{LogicalProject}} and {{Project}}) it might
be true that it was an attempt to not propagate correlation variables to
physical nodes. However, many extensions of {{Filter}}, {{Project}}, etc., in
downstream projects are not really "physical" nodes but still "logical" with
few or none additional attributes.
Consider for instance, Apache Hive. The latter does not use at all the
{{LogicalXx}} expressions since it has its own {{HiveXx}} variants. If we only
add support for correlation variables in {{LogicalProject}} then how these
downstream projects will resolve problems like those mentioned here?
> Error when executing query with subquery in select list that uses outer
> column of array type
>
>
> Key: CALCITE-5127
> URL: https://issues.apache.org/jira/browse/CALCITE-5127
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Dmitry Sysolyatin
>Assignee: Benchao Li
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.32.0
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> The following queries fail:
> {code}
> SELECT ARRAY(SELECT s.x) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT ARRAY(SELECT * FROM UNNEST(s.x) y) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT (SELECT CARDINALITY(s.x) LIMIT 1) FROM (SELECT ARRAY[1,2,3] as x) s;
>
> {code}
> With exception:
> {code}
> Caused by: java.lang.ClassCastException: java.lang.Integer cannot be cast to
> java.util.List
> {code}
> You can find test cases for this task in
> https://github.com/apache/calcite/commit/27e68ded2c3bea7d7af73dd1dc156e46fb3591a8
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Updated] (CALCITE-5127) Error when executing query with subquery in select list that uses outer column of array type
[
https://issues.apache.org/jira/browse/CALCITE-5127?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Stamatis Zampetakis updated CALCITE-5127:
-
Fix Version/s: 1.32.0
> Error when executing query with subquery in select list that uses outer
> column of array type
>
>
> Key: CALCITE-5127
> URL: https://issues.apache.org/jira/browse/CALCITE-5127
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Dmitry Sysolyatin
>Assignee: Benchao Li
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.32.0
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> The following queries fail:
> {code}
> SELECT ARRAY(SELECT s.x) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT ARRAY(SELECT * FROM UNNEST(s.x) y) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT (SELECT CARDINALITY(s.x) LIMIT 1) FROM (SELECT ARRAY[1,2,3] as x) s;
>
> {code}
> With exception:
> {code}
> Caused by: java.lang.ClassCastException: java.lang.Integer cannot be cast to
> java.util.List
> {code}
> You can find test cases for this task in
> https://github.com/apache/calcite/commit/27e68ded2c3bea7d7af73dd1dc156e46fb3591a8
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Commented] (CALCITE-5127) Error when executing query with subquery in select list that uses outer column of array type
[
https://issues.apache.org/jira/browse/CALCITE-5127?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17554281#comment-17554281
]
Stamatis Zampetakis commented on CALCITE-5127:
--
Thanks for updating the PR [~libenchao] .
After {{SubQueryRemoveRule}} there shouldn't be any sub-queries in the plan.
For this to happen we will most likely need to introduce {{Correlate}}
expressions. I think it is reasonable to say that there will be no correlations
in Project, Filter, and Join.
After {{RelDecorrelator}} there shouldn't be any correlations in the plan so it
is quite different than the {{SubQueryRemoveRule}}.
Nevertheless we cannot enforce downstream users of Calcite to use
{{SubQueryRemoveRule}} or {{RelDecorrelator}} so rules should be aware of
correlation variables.
I will try to think what is the best course of action to move this PR forward
and add further comments.
In the meantime I think we shouldn't block the 1.31.0 release for this ticket.
It seems unlikely to merge this in the next 24 hours thus I would suggest to
move the ticket to 1.32.0. If this is really blocking for downstream projects
then we could cut 1.32.0 right after merging this to main. What do you think
[~libenchao] [~dmsysolyatin]?
> Error when executing query with subquery in select list that uses outer
> column of array type
>
>
> Key: CALCITE-5127
> URL: https://issues.apache.org/jira/browse/CALCITE-5127
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Dmitry Sysolyatin
>Assignee: Benchao Li
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.31.0
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> The following queries fail:
> {code}
> SELECT ARRAY(SELECT s.x) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT ARRAY(SELECT * FROM UNNEST(s.x) y) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT (SELECT CARDINALITY(s.x) LIMIT 1) FROM (SELECT ARRAY[1,2,3] as x) s;
>
> {code}
> With exception:
> {code}
> Caused by: java.lang.ClassCastException: java.lang.Integer cannot be cast to
> java.util.List
> {code}
> You can find test cases for this task in
> https://github.com/apache/calcite/commit/27e68ded2c3bea7d7af73dd1dc156e46fb3591a8
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Commented] (CALCITE-5190) TPC-H testing of the Calcite framework
[
https://issues.apache.org/jira/browse/CALCITE-5190?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17553578#comment-17553578
]
Stamatis Zampetakis commented on CALCITE-5190:
--
Hey [~itxiangkui] , there are various benchmarks around TPC-H but not
everything is open and usually there are various customizations in the middle.
One presentation that comes to mind around this was done by Chris Baynes at
Apache Con ([https://www.youtube.com/watch?v=4JAOkLKrcYE&t=1032s)] a few years
ago.
Also keep in mind that for questions, it is better to use the dev@calcite
mailing list than logging JIRA tickets.
> TPC-H testing of the Calcite framework
> --
>
> Key: CALCITE-5190
> URL: https://issues.apache.org/jira/browse/CALCITE-5190
> Project: Calcite
> Issue Type: Wish
> Components: core, jdbc-adapter
>Affects Versions: 1.30.0
>Reporter: itxiangkui
>Priority: Major
>
> What our project is doing:
> 1. Designed a time series database, made full use of the enumerable adapter,
> and made a horizontal comparison of benchmarks with other popular time
> databases
> 2. Design catalog->database->table, and use the adapter function of jdbc to
> connect multiple databases such as Mysql/Es in the backend to solve the join
> problem of heterogeneous data (for example: select * from catalog1.database
> .table1 ,catalog2,database.table2 .. where t1.c1='xx' )
> But when we tested it, we found that, as far as Jdbc-Adapter is concerned,
> Calcite will disassemble the logical SQL into very fragmented query plan SQL,
> and hand it over to the underlying database instance to execute the specific
> physical SQL. Most of the SQL has no filter conditions to push down and the
> physical SQL looks very clumsy.
> So we did a version of the TPC-H test (an industry standard in the database
> field), taking SQL-19 as an example:
> {code:sql}
> SELECT sum(l_extendedprice* (1 - l_discount)) AS revenue
> FROM lineitem,
> part
> WHERE (p_partkey = l_partkey
>AND p_brand = 'Brand#52'
>AND p_container in ('SM CASE',
>'SM BOX',
>'SM PACK',
>'SM PKG')
>AND l_quantity >= 4
>AND l_quantity <= 4 + 10
>AND p_size BETWEEN 1 AND 5
>AND l_shipmode in ('AIR',
> 'AIR REG')
>AND l_shipinstruct = 'DELIVER IN PERSON')
> OR (p_partkey = l_partkey
> AND p_brand = 'Brand#11'
> AND p_container in ('MED BAG',
> 'MED BOX',
> 'MED PKG',
> 'MED PACK')
> AND l_quantity >= 18
> AND l_quantity <= 18 + 10
> AND p_size BETWEEN 1 AND 10
> AND l_shipmode in ('AIR',
> 'AIR REG')
> AND l_shipinstruct = 'DELIVER IN PERSON')
> OR (p_partkey = l_partkey
> AND p_brand = 'Brand#51'
> AND p_container in ('LG CASE',
> 'LG BOX',
> 'LG PACK',
> 'LG PKG')
> AND l_quantity >= 29
> AND l_quantity <= 29 + 10
> AND p_size BETWEEN 1 AND 15
> AND l_shipmode in ('AIR',
> 'AIR REG')
> AND l_shipinstruct = 'DELIVER IN PERSON');
> {code}
> The physical execution plan is:
> {code:sql}
> [SELECT *
> FROM `tpch`.`lineitem`
> LIMIT 3]
> [SELECT `L_PARTKEY`, `L_QUANTITY`, `L_EXTENDEDPRICE`, `L_DISCOUNT`,
> `L_SHIPINSTRUCT`, `L_SHIPMODE`
> FROM `tpch`.`lineitem`]
> [SELECT `P_PARTKEY`, `P_BRAND`, `P_SIZE`, `P_CONTAINER`
> FROM `tpch`.`part`]
> {code}
> this looks incredible...
>
> My question is: Has Calcite ever tested something like TPC-H.
> In theory, for a specific database instance, the query speed after using
> calcite will not drop much performance compared to the original database, so
> many users are happy to use Calcite to solve the problem of data islands. …
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Commented] (CALCITE-5127) Error when executing query with subquery in select list that uses outer column of array type
[
https://issues.apache.org/jira/browse/CALCITE-5127?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17552674#comment-17552674
]
Stamatis Zampetakis commented on CALCITE-5127:
--
[~libenchao] Forgot to say that if you don't agree with some comments feel free
to debate :) We all want the best for the project.
> Error when executing query with subquery in select list that uses outer
> column of array type
>
>
> Key: CALCITE-5127
> URL: https://issues.apache.org/jira/browse/CALCITE-5127
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Dmitry Sysolyatin
>Assignee: Benchao Li
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.31.0
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> The following queries fail:
> {code}
> SELECT ARRAY(SELECT s.x) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT ARRAY(SELECT * FROM UNNEST(s.x) y) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT (SELECT CARDINALITY(s.x) LIMIT 1) FROM (SELECT ARRAY[1,2,3] as x) s;
>
> {code}
> With exception:
> {code}
> Caused by: java.lang.ClassCastException: java.lang.Integer cannot be cast to
> java.util.List
> {code}
> You can find test cases for this task in
> https://github.com/apache/calcite/commit/27e68ded2c3bea7d7af73dd1dc156e46fb3591a8
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Commented] (CALCITE-5127) Error when executing query with subquery in select list that uses outer column of array type
[
https://issues.apache.org/jira/browse/CALCITE-5127?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17552671#comment-17552671
]
Stamatis Zampetakis commented on CALCITE-5127:
--
[~libenchao] OK, I see your point. Let's then introduce correlated variables
via the constructor as you and Konstantin did.
Please have a look in https://github.com/apache/calcite/pull/2623 and align
your PR with the things that we discussed there with Konstantin. Rebase your PR
based on current master, and ping me for review.
> Error when executing query with subquery in select list that uses outer
> column of array type
>
>
> Key: CALCITE-5127
> URL: https://issues.apache.org/jira/browse/CALCITE-5127
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Dmitry Sysolyatin
>Assignee: Benchao Li
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.31.0
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> The following queries fail:
> {code}
> SELECT ARRAY(SELECT s.x) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT ARRAY(SELECT * FROM UNNEST(s.x) y) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT (SELECT CARDINALITY(s.x) LIMIT 1) FROM (SELECT ARRAY[1,2,3] as x) s;
>
> {code}
> With exception:
> {code}
> Caused by: java.lang.ClassCastException: java.lang.Integer cannot be cast to
> java.util.List
> {code}
> You can find test cases for this task in
> https://github.com/apache/calcite/commit/27e68ded2c3bea7d7af73dd1dc156e46fb3591a8
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Commented] (CALCITE-5127) Error when executing query with subquery in select list that uses outer column of array type
[
https://issues.apache.org/jira/browse/CALCITE-5127?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17552634#comment-17552634
]
Stamatis Zampetakis commented on CALCITE-5127:
--
Instead of adding {{variablesSet}} via a new parameter in the constructor,
which leads to changes everywhere, would it be possible to derive them from
(RexNode) expression list?
I know that adding a new parameter is the pattern that is used in
LogicalFilter, Join, etc., but maybe we can skip this for now by adding some
derivation logic in the constructor or in {{Project#getVariablesSet}} method.
[~libenchao] Does this sound feasible?
> Error when executing query with subquery in select list that uses outer
> column of array type
>
>
> Key: CALCITE-5127
> URL: https://issues.apache.org/jira/browse/CALCITE-5127
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Dmitry Sysolyatin
>Assignee: Benchao Li
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.31.0
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> The following queries fail:
> {code}
> SELECT ARRAY(SELECT s.x) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT ARRAY(SELECT * FROM UNNEST(s.x) y) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT (SELECT CARDINALITY(s.x) LIMIT 1) FROM (SELECT ARRAY[1,2,3] as x) s;
>
> {code}
> With exception:
> {code}
> Caused by: java.lang.ClassCastException: java.lang.Integer cannot be cast to
> java.util.List
> {code}
> You can find test cases for this task in
> https://github.com/apache/calcite/commit/27e68ded2c3bea7d7af73dd1dc156e46fb3591a8
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Commented] (CALCITE-5127) Error when executing query with subquery in select list that uses outer column of array type
[
https://issues.apache.org/jira/browse/CALCITE-5127?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17552445#comment-17552445
]
Stamatis Zampetakis commented on CALCITE-5127:
--
Ok, so if I understand correctly you need the correlations variables exposed in
the Projection so that you can take them into account when you want to check if
a column is used or not, is that right?
In other words we want {{Project#getVariablesSet}} to return all correlated
variables that appear in expressions inside the project, right?
> Error when executing query with subquery in select list that uses outer
> column of array type
>
>
> Key: CALCITE-5127
> URL: https://issues.apache.org/jira/browse/CALCITE-5127
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Dmitry Sysolyatin
>Assignee: Benchao Li
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.31.0
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> The following queries fail:
> {code}
> SELECT ARRAY(SELECT s.x) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT ARRAY(SELECT * FROM UNNEST(s.x) y) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT (SELECT CARDINALITY(s.x) LIMIT 1) FROM (SELECT ARRAY[1,2,3] as x) s;
>
> {code}
> With exception:
> {code}
> Caused by: java.lang.ClassCastException: java.lang.Integer cannot be cast to
> java.util.List
> {code}
> You can find test cases for this task in
> https://github.com/apache/calcite/commit/27e68ded2c3bea7d7af73dd1dc156e46fb3591a8
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Commented] (CALCITE-5127) Error when executing query with subquery in select list that uses outer column of array type
[
https://issues.apache.org/jira/browse/CALCITE-5127?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17552075#comment-17552075
]
Stamatis Zampetakis commented on CALCITE-5127:
--
Hey [~libenchao] , are the problems reported here fixed after merging
CALCITE-4913? If yes then I would suggest to rework the PR#2813 to keep only
the tests and I will review and merge ASAP.
If we need/want to add correlated variables in Project but it is not mandatory
for fixing the problem here I would prefer to discuss this under another
dedicated Jira (cc [~korlov] ).
> Error when executing query with subquery in select list that uses outer
> column of array type
>
>
> Key: CALCITE-5127
> URL: https://issues.apache.org/jira/browse/CALCITE-5127
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Dmitry Sysolyatin
>Assignee: Benchao Li
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.31.0
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> The following queries fail:
> {code}
> SELECT ARRAY(SELECT s.x) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT ARRAY(SELECT * FROM UNNEST(s.x) y) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT (SELECT CARDINALITY(s.x) LIMIT 1) FROM (SELECT ARRAY[1,2,3] as x) s;
>
> {code}
> With exception:
> {code}
> Caused by: java.lang.ClassCastException: java.lang.Integer cannot be cast to
> java.util.List
> {code}
> You can find test cases for this task in
> https://github.com/apache/calcite/commit/27e68ded2c3bea7d7af73dd1dc156e46fb3591a8
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Resolved] (CALCITE-4907) JDBC adapter cannot push down join ON TRUE (cartesian product)
[ https://issues.apache.org/jira/browse/CALCITE-4907?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Stamatis Zampetakis resolved CALCITE-4907. -- Resolution: Fixed Fixed in [https://github.com/apache/calcite/commit/a9aea934dc29395ca8ee81df5dcf0d50ac823023.] Thanks for the PR [~ggigio] ! > JDBC adapter cannot push down join ON TRUE (cartesian product) > --- > > Key: CALCITE-4907 > URL: https://issues.apache.org/jira/browse/CALCITE-4907 > Project: Calcite > Issue Type: Bug > Components: jdbc-adapter >Affects Versions: 1.28.0 >Reporter: Francesco Gini >Assignee: Francesco Gini >Priority: Major > Labels: pull-request-available > Fix For: 1.31.0 > > Time Spent: 3h 10m > Remaining Estimate: 0h > > JDBC adapter (in JdbcJoinRule) cannot push down join ON TRUE (cartesian > product). This results in implementing the join in memory via > _EnumerableNestedLoopJoin_ which is a less efficient implementation. -- This message was sent by Atlassian Jira (v8.20.7#820007)
[jira] [Commented] (CALCITE-4907) JDBC adapter cannot push down join ON TRUE (cartesian product)
[ https://issues.apache.org/jira/browse/CALCITE-4907?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17551083#comment-17551083 ] Stamatis Zampetakis commented on CALCITE-4907: -- I removed the JOIN ON FALSE from the summary and description since it is a bit misleading. In most cases JOIN ON FALSE will be simplified/dropped completely by other rules so thus we don't really have to push something to the underlying DBMS. Moreover, the current PR does not have any tests around JOIN ON FALSE so I am reluctant to claim something that we never tested. > JDBC adapter cannot push down join ON TRUE (cartesian product) > --- > > Key: CALCITE-4907 > URL: https://issues.apache.org/jira/browse/CALCITE-4907 > Project: Calcite > Issue Type: Bug > Components: jdbc-adapter >Affects Versions: 1.28.0 >Reporter: Francesco Gini >Assignee: Francesco Gini >Priority: Major > Labels: pull-request-available > Fix For: 1.31.0 > > Time Spent: 3h > Remaining Estimate: 0h > > JDBC adapter (in JdbcJoinRule) cannot push down join ON TRUE (cartesian > product). This results in implementing the join in memory via > _EnumerableNestedLoopJoin_ which is a less efficient implementation. -- This message was sent by Atlassian Jira (v8.20.7#820007)
[jira] [Updated] (CALCITE-4907) JDBC adapter cannot push down join ON TRUE (cartesian product)
[ https://issues.apache.org/jira/browse/CALCITE-4907?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Stamatis Zampetakis updated CALCITE-4907: - Summary: JDBC adapter cannot push down join ON TRUE (cartesian product) (was: JDBC adapter cannot push down join ON TRUE (cartesian product) or ON FALSE) > JDBC adapter cannot push down join ON TRUE (cartesian product) > --- > > Key: CALCITE-4907 > URL: https://issues.apache.org/jira/browse/CALCITE-4907 > Project: Calcite > Issue Type: Bug > Components: jdbc-adapter >Affects Versions: 1.28.0 >Reporter: Francesco Gini >Assignee: Francesco Gini >Priority: Major > Labels: pull-request-available > Fix For: 1.31.0 > > Time Spent: 3h > Remaining Estimate: 0h > > JDBC adapter (in JdbcJoinRule) cannot push down join ON TRUE (cartesian > product) or ON FALSE. This results in implementing the join in memory via > _EnumerableNestedLoopJoin_ which is a less efficient implementation. -- This message was sent by Atlassian Jira (v8.20.7#820007)
[jira] [Updated] (CALCITE-4907) JDBC adapter cannot push down join ON TRUE (cartesian product)
[ https://issues.apache.org/jira/browse/CALCITE-4907?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Stamatis Zampetakis updated CALCITE-4907: - Description: JDBC adapter (in JdbcJoinRule) cannot push down join ON TRUE (cartesian product). This results in implementing the join in memory via _EnumerableNestedLoopJoin_ which is a less efficient implementation. (was: JDBC adapter (in JdbcJoinRule) cannot push down join ON TRUE (cartesian product) or ON FALSE. This results in implementing the join in memory via _EnumerableNestedLoopJoin_ which is a less efficient implementation.) > JDBC adapter cannot push down join ON TRUE (cartesian product) > --- > > Key: CALCITE-4907 > URL: https://issues.apache.org/jira/browse/CALCITE-4907 > Project: Calcite > Issue Type: Bug > Components: jdbc-adapter >Affects Versions: 1.28.0 >Reporter: Francesco Gini >Assignee: Francesco Gini >Priority: Major > Labels: pull-request-available > Fix For: 1.31.0 > > Time Spent: 3h > Remaining Estimate: 0h > > JDBC adapter (in JdbcJoinRule) cannot push down join ON TRUE (cartesian > product). This results in implementing the join in memory via > _EnumerableNestedLoopJoin_ which is a less efficient implementation. -- This message was sent by Atlassian Jira (v8.20.7#820007)
[jira] [Commented] (CALCITE-4907) JDBC adapter cannot push down join ON TRUE (cartesian product) or ON FALSE
[ https://issues.apache.org/jira/browse/CALCITE-4907?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17550450#comment-17550450 ] Stamatis Zampetakis commented on CALCITE-4907: -- The changes proposed here enable pushing Cartesian products in the underlying JDBC database. In some cases this is desirable (when the JDBC adapter operators are combined with another engine that does not support Cartesian products) but it can also lead to important performance regressions. We tend to agree that the cartesian product pushdown decision should be based on the cost model and not in the rule(s) so if such a case pops up we should probably invest towards this direction. I am planning to merge the current PR in 24 hours. Please let me know if you want to have further discussion here. > JDBC adapter cannot push down join ON TRUE (cartesian product) or ON FALSE > --- > > Key: CALCITE-4907 > URL: https://issues.apache.org/jira/browse/CALCITE-4907 > Project: Calcite > Issue Type: Bug > Components: jdbc-adapter >Affects Versions: 1.28.0 >Reporter: Francesco Gini >Assignee: Francesco Gini >Priority: Major > Labels: pull-request-available > Fix For: 1.31.0 > > Time Spent: 3h > Remaining Estimate: 0h > > JDBC adapter (in JdbcJoinRule) cannot push down join ON TRUE (cartesian > product) or ON FALSE. This results in implementing the join in memory via > _EnumerableNestedLoopJoin_ which is a less efficient implementation. -- This message was sent by Atlassian Jira (v8.20.7#820007)
[jira] [Resolved] (CALCITE-4913) Correlated variables in a select list are not deduplicated
[
https://issues.apache.org/jira/browse/CALCITE-4913?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Stamatis Zampetakis resolved CALCITE-4913.
--
Resolution: Fixed
Fixed in
[https://github.com/apache/calcite/commit/f7aa27ec22e843c5e27022f99237175d159699bb.]
Many thanks for the PR [~korlov] . Apart from the code itself your help in
documenting the current behavior around correlations and willingness to push
this to the end is worth mentioning and much appreciated as well.
Let's discuss the remaining bugs/improvements under separate JIRAs (create new
or use existing ones).
> Correlated variables in a select list are not deduplicated
> --
>
> Key: CALCITE-4913
> URL: https://issues.apache.org/jira/browse/CALCITE-4913
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.28.0
>Reporter: Konstantin Orlov
>Assignee: Konstantin Orlov
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.31.0
>
> Time Spent: 12.5h
> Remaining Estimate: 0h
>
> Currently, the deduplication of correlated variables ain't done for
> correlates in select list. Looks like the root cause here is same as for
> CALCITE-4673, but, unfortunately, suggested patch has addressed only the
> particular problem with a table function. It would be great to provide a more
> gereric fix.
> Besides, the project rel doesn't expose any correlated variables that should
> be set by this node.
> The problematic query is follow:
> {code:java}
> select e.deptno,
>(select count(*) from emp where e.deptno > 0) as c1,
>(select count(*) from emp where e.deptno > 0 and e.deptno < 10) as c2
> from emp e;
> {code}
> Currently after conversion to relational nodes it looks like:
> {code:java}
> LogicalProject(DEPTNO=[$7], EXPR$1=[$SCALAR_QUERY({
> LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
> LogicalProject($f0=[0])
> LogicalFilter(condition=[>($cor0.DEPTNO, 0)])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> })], EXPR$2=[$SCALAR_QUERY({
> LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
> LogicalProject($f0=[0])
> LogicalFilter(condition=[AND(>($cor1.DEPTNO, 0), <($cor2.DEPTNO, 10))])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> })])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> All the correlates are set by the very top project node and could be
> represented by only one variable.
> Moreover, I've tried to add the query above to _sub-query.iq_ and got the
> follow error:
> {code:java}
> > java.sql.SQLException: Error while executing SQL "select e.deptno,
> >(select count(*) from emp where e.deptno > 0) as c1,
> >(select count(*) from emp where e.deptno > 0 and e.deptno < 10) as c2
> > from emp e": cm.mapCorToCorRel.get($cor1)
> > 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:163)
> > at
> > org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:227)
> > at net.hydromatic.quidem.Quidem.checkResult(Quidem.java:325)
> > at net.hydromatic.quidem.Quidem.access$2800(Quidem.java:54)
> > at
> > net.hydromatic.quidem.Quidem$ContextImpl.checkResult(Quidem.java:1748)
> > at
> > net.hydromatic.quidem.Quidem$CheckResultCommand.execute(Quidem.java:1079)
> > at
> > net.hydromatic.quidem.Quidem$CompositeCommand.execute(Quidem.java:1549)
> > at net.hydromatic.quidem.Quidem.execute(Quidem.java:216)
> > at org.apache.calcite.test.QuidemTest.checkRun(QuidemTest.java:173)
> > at org.apache.calcite.test.QuidemTest.test(QuidemTest.java:224)
> > 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:725)
> > 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.TimeoutInvocation.proceed(TimeoutInvocation.java:46)
> > at
> > org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:149)
> > at
> > org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:140)
> >
[jira] [Commented] (CALCITE-4913) Correlated variables in a select list are not deduplicated
[
https://issues.apache.org/jira/browse/CALCITE-4913?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17550377#comment-17550377
]
Stamatis Zampetakis commented on CALCITE-4913:
--
Thanks for the clarifications [~korlov] !
So basically the point worth keeping is that the number of correlated variables
does have an impact on \{{SubQueryRemove}}. By reusing the same correlated
variables when going from SQL to RelNode subqueries may be a bit easier to
unnest when using the \{{SubQueryRemoveRule}} and that's the main benefit of
deduplicating variables in \{{SqlToRelConverter}}.
> Correlated variables in a select list are not deduplicated
> --
>
> Key: CALCITE-4913
> URL: https://issues.apache.org/jira/browse/CALCITE-4913
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.28.0
>Reporter: Konstantin Orlov
>Assignee: Konstantin Orlov
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.31.0
>
> Time Spent: 12.5h
> Remaining Estimate: 0h
>
> Currently, the deduplication of correlated variables ain't done for
> correlates in select list. Looks like the root cause here is same as for
> CALCITE-4673, but, unfortunately, suggested patch has addressed only the
> particular problem with a table function. It would be great to provide a more
> gereric fix.
> Besides, the project rel doesn't expose any correlated variables that should
> be set by this node.
> The problematic query is follow:
> {code:java}
> select e.deptno,
>(select count(*) from emp where e.deptno > 0) as c1,
>(select count(*) from emp where e.deptno > 0 and e.deptno < 10) as c2
> from emp e;
> {code}
> Currently after conversion to relational nodes it looks like:
> {code:java}
> LogicalProject(DEPTNO=[$7], EXPR$1=[$SCALAR_QUERY({
> LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
> LogicalProject($f0=[0])
> LogicalFilter(condition=[>($cor0.DEPTNO, 0)])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> })], EXPR$2=[$SCALAR_QUERY({
> LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
> LogicalProject($f0=[0])
> LogicalFilter(condition=[AND(>($cor1.DEPTNO, 0), <($cor2.DEPTNO, 10))])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> })])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> All the correlates are set by the very top project node and could be
> represented by only one variable.
> Moreover, I've tried to add the query above to _sub-query.iq_ and got the
> follow error:
> {code:java}
> > java.sql.SQLException: Error while executing SQL "select e.deptno,
> >(select count(*) from emp where e.deptno > 0) as c1,
> >(select count(*) from emp where e.deptno > 0 and e.deptno < 10) as c2
> > from emp e": cm.mapCorToCorRel.get($cor1)
> > 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:163)
> > at
> > org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:227)
> > at net.hydromatic.quidem.Quidem.checkResult(Quidem.java:325)
> > at net.hydromatic.quidem.Quidem.access$2800(Quidem.java:54)
> > at
> > net.hydromatic.quidem.Quidem$ContextImpl.checkResult(Quidem.java:1748)
> > at
> > net.hydromatic.quidem.Quidem$CheckResultCommand.execute(Quidem.java:1079)
> > at
> > net.hydromatic.quidem.Quidem$CompositeCommand.execute(Quidem.java:1549)
> > at net.hydromatic.quidem.Quidem.execute(Quidem.java:216)
> > at org.apache.calcite.test.QuidemTest.checkRun(QuidemTest.java:173)
> > at org.apache.calcite.test.QuidemTest.test(QuidemTest.java:224)
> > 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:725)
> > 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.TimeoutInvocation.proceed(TimeoutInvocation.java:46)
> > at
> > org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:149)
> > at
> > org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtensio
[jira] [Commented] (CALCITE-4913) Correlated variables in a select list are not deduplicated
[
https://issues.apache.org/jira/browse/CALCITE-4913?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17545906#comment-17545906
]
Stamatis Zampetakis commented on CALCITE-4913:
--
I went over [PR#2825|https://github.com/apache/calcite/pull/2825] it's much
more contained, thank you for preparing this [~korlov]. Albeit some minor stuff
that I will fix myself I will merge it soon.
I have a few observations/questions which are not blocking for the PR but it
would be great if someone can shed some light around the importance of the
correlation deduplication logic.
The query attached in the PR is the following:
{code:sql}
SELECT deptno,
(SELECT min(1)
FROM emp
WHERE empno > d.deptno) AS i0,
(SELECT min(0)
FROM emp
WHERE deptno = d.deptno
AND ename = 'SMITH'
AND d.deptno > 0) AS i1
FROM dept AS d
{code}
The part of the plan that changes with and without the changes is shown below:
+Subplan before subquery expansion with proposed changes+
{noformat}
LogicalAggregate(group=[{}], EXPR$0=[MIN($0)])
LogicalProject($f0=[0])
LogicalFilter(condition=[AND(=($7, $cor0.DEPTNO), =($1, 'SMITH'),
>($cor0.DEPTNO, 0))])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
{noformat}
+Subplan before subquery expansion without proposed changes+
{noformat}
LogicalAggregate(group=[{}], EXPR$0=[MIN($0)])
LogicalProject($f0=[0])
LogicalFilter(condition=[AND(=($7, $cor1.DEPTNO), =($1, 'SMITH'),
>($cor2.DEPTNO, 0))])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
{noformat}
+Observations:+
* Both plans (with and without the changes) before subquery expansion seem
valid.
* The proposed changes do not seem to affect the plans *after* subquery
expansion.
* The proposed changes do not seem to affect query decorrelation either at
least in this case.
I understand that since the subqueries are referring to the same column it
makes sense to reuse the same correlation variable and this makes the plan
before expansion more intuitive but are there any other benefits of reusing the
same variable? To put the question a bit differently what are the problems if
we don't reuse the correlation variable.
The deduplication logic has been there inside the {{SqlToRelConverter}} for
quite a while so it is very probable that there are pieces of code depending on
this behavior but I didn't find to time experiment with it and see what breaks
if it is removed. It could be an interesting experiment for the future.
> Correlated variables in a select list are not deduplicated
> --
>
> Key: CALCITE-4913
> URL: https://issues.apache.org/jira/browse/CALCITE-4913
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.28.0
>Reporter: Konstantin Orlov
>Assignee: Konstantin Orlov
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.31.0
>
> Time Spent: 12h 10m
> Remaining Estimate: 0h
>
> Currently, the deduplication of correlated variables ain't done for
> correlates in select list. Looks like the root cause here is same as for
> CALCITE-4673, but, unfortunately, suggested patch has addressed only the
> particular problem with a table function. It would be great to provide a more
> gereric fix.
> Besides, the project rel doesn't expose any correlated variables that should
> be set by this node.
> The problematic query is follow:
> {code:java}
> select e.deptno,
>(select count(*) from emp where e.deptno > 0) as c1,
>(select count(*) from emp where e.deptno > 0 and e.deptno < 10) as c2
> from emp e;
> {code}
> Currently after conversion to relational nodes it looks like:
> {code:java}
> LogicalProject(DEPTNO=[$7], EXPR$1=[$SCALAR_QUERY({
> LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
> LogicalProject($f0=[0])
> LogicalFilter(condition=[>($cor0.DEPTNO, 0)])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> })], EXPR$2=[$SCALAR_QUERY({
> LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
> LogicalProject($f0=[0])
> LogicalFilter(condition=[AND(>($cor1.DEPTNO, 0), <($cor2.DEPTNO, 10))])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> })])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> All the correlates are set by the very top project node and could be
> represented by only one variable.
> Moreover, I've tried to add the query above to _sub-query.iq_ and got the
> follow error:
> {code:java}
> > java.sql.SQLException: Error while executing SQL "select e.deptno,
> >(select count(*) from emp where e.deptno > 0) as c1,
> >(select count(*) from emp where e.deptno > 0 and e.deptno < 10) as c2
> > from emp e": cm.mapCorToCorRel.get($cor1)
> > at org.apache.calcite.avatica.Helper.createException(Helper.java:56
[jira] [Commented] (CALCITE-5127) Error when executing query with subquery in select list that uses outer column of array type
[
https://issues.apache.org/jira/browse/CALCITE-5127?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17545763#comment-17545763
]
Stamatis Zampetakis commented on CALCITE-5127:
--
Thanks for working on this [~libenchao] ! I had a brief look in the current PR
and it seems that there is a big overlap with CALCITE-4913 and
[PR#2623|https://github.com/apache/calcite/pull/2623]. Since I started
reviewing that one first let's wait a bit till that one gets in and see what it
remains to be done here.
> Error when executing query with subquery in select list that uses outer
> column of array type
>
>
> Key: CALCITE-5127
> URL: https://issues.apache.org/jira/browse/CALCITE-5127
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Dmitry Sysolyatin
>Assignee: Benchao Li
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.31.0
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> The following queries fail:
> {code}
> SELECT ARRAY(SELECT s.x) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT ARRAY(SELECT * FROM UNNEST(s.x) y) FROM (SELECT ARRAY[1,2,3] as x) s;
> SELECT (SELECT CARDINALITY(s.x) LIMIT 1) FROM (SELECT ARRAY[1,2,3] as x) s;
>
> {code}
> With exception:
> {code}
> Caused by: java.lang.ClassCastException: java.lang.Integer cannot be cast to
> java.util.List
> {code}
> You can find test cases for this task in
> https://github.com/apache/calcite/commit/27e68ded2c3bea7d7af73dd1dc156e46fb3591a8
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Commented] (CALCITE-4913) Correlated variables in a select list are not deduplicated
[
https://issues.apache.org/jira/browse/CALCITE-4913?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17545762#comment-17545762
]
Stamatis Zampetakis commented on CALCITE-4913:
--
Hey [~korlov] , thanks for pushing this forward. I would really like to
finalize this work and get it in the next release so I will try to do my best.
>From your recent
>[email|https://lists.apache.org/thread/p14pjfo003rh3vo9fz1zrtrl2zpxktww] to
>the dev list and after having another look in the PR it seems that you have
>found additional bugs in Calcite. Since the current PR is becoming relatively
>big I would like to see if we can somehow split it up into pieces (if it makes
>sense) and delegate some work to follow up JIRAs/PRs. Smaller PRs are easier
>to get in and also the history is easier to follow from future maintainers.
I get the impression we can divide the PR into 4-5 parts:
# correlate variable deduplication in the SELECT clause when going from SQL to
plan
# expose the correlation variables used in Project
# wrong data type in correlate variables after field trimming
# new RelBuilder APIs to create project with correlations
# update rules etc to propagate correlate variables in project
The main problem in this JIRA seems to be the conversion from SQL to RelNode
(Point 1) so let's try to focus on this here and treat the rest in follow-up
JIRAs/PRs.
I don't remember of why we need to expose the correlation variables in the
Project in order to solve the problem so can't really say for sure if this can
go in another JIRA or not. I think this was the main reason of why we had to
touch a lot of places in the code so if we can avoid this for now for sure we
can merge the first one much faster.
It is good that you addressed also the field trimming problem but if it is not
strictly related with the main problem reported here I would create another
JIRA and have a separate discussion there.
I am pretty confident that we can leave the changes around the RelBuilder for
another JIRA since the new APIs are not used by production code. In my
experience changes to RelBuilder take quite a few iterations till they get in
so it seems better to leave them out for now.
The need for updating the rules came from the fact that we added a new field in
the Project holding explicitly the correlated variables that also came from the
attempt to expose the variables. If we don't necessary need to expose the
variables here then possibly we can also address this propagation in a follow
up. I am also thinking that even if we want to expose the variables it may be
possible to do it without introducing a new constructor but rather extract them
from the RexNode expressions. Again if we can postpone this discussion for
another JIRA it could speed up things.
[~korlov] I would like to know what you think about the above. If you agree
that splitting the PR makes sense then you can close
[PR#2623|https://github.com/apache/calcite/pull/2623] rebase and squash as you
see fit and raise a new PR for this issue focusing on Point 1 and I will review
ASAP. Then you can create follow-up JIRAs/PRs as needed. On the other hand, if
you think that it is not possible to divide PR#2623 further then we can
continue iterating on this one.
> Correlated variables in a select list are not deduplicated
> --
>
> Key: CALCITE-4913
> URL: https://issues.apache.org/jira/browse/CALCITE-4913
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.28.0
>Reporter: Konstantin Orlov
>Assignee: Konstantin Orlov
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.31.0
>
> Time Spent: 11h 40m
> Remaining Estimate: 0h
>
> Currently, the deduplication of correlated variables ain't done for
> correlates in select list. Looks like the root cause here is same as for
> CALCITE-4673, but, unfortunately, suggested patch has addressed only the
> particular problem with a table function. It would be great to provide a more
> gereric fix.
> Besides, the project rel doesn't expose any correlated variables that should
> be set by this node.
> The problematic query is follow:
> {code:java}
> select e.deptno,
>(select count(*) from emp where e.deptno > 0) as c1,
>(select count(*) from emp where e.deptno > 0 and e.deptno < 10) as c2
> from emp e;
> {code}
> Currently after conversion to relational nodes it looks like:
> {code:java}
> LogicalProject(DEPTNO=[$7], EXPR$1=[$SCALAR_QUERY({
> LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
> LogicalProject($f0=[0])
> LogicalFilter(condition=[>($cor0.DEPTNO, 0)])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> })], EXPR$2=[$SCALAR_QUERY({
> LogicalAggregate(group=[{}],
[jira] [Commented] (CALCITE-5151) when create hive jdbc schema,throw new IllegalArgumentException("cannot deduce null collation")
[
https://issues.apache.org/jira/browse/CALCITE-5151?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17536531#comment-17536531
]
Stamatis Zampetakis commented on CALCITE-5151:
--
Thanks for logging this problem [~cwbcom].
Please include the full stack trace from the {{IllegalArgumentException}} in
the description (inside \{noformat\}) tags. It is helpful to understand exactly
when the problem appears and it also help others searching for similar problems
in the future.
> when create hive jdbc schema,throw new IllegalArgumentException("cannot
> deduce null collation")
> ---
>
> Key: CALCITE-5151
> URL: https://issues.apache.org/jira/browse/CALCITE-5151
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.30.0
>Reporter: cwb
>Priority: Major
>
> code:
> Map props=new HashMap();
> props.put("jdbcUrl", "jdbc:hive2://192.168.137.66:1/db_test");
> props.put("jdbcDriver", "org.apache.hive.jdbc.HiveDriver");
> props.put("jdbcUser", "");
> props.put("jdbcPassword", "");
> props.put("jdbcSchema", "db_test");
> hiveSchema = JdbcSchema.create(rootSchema, "hiveSchema",props);
> error :
> java.lang.IllegalArgumentException: cannot deduce null collation
> reason:
> the method SqlDialects.getNullCollation(DatabaseMetaData databaseMetaData)
> throws new IllegalArgumentException("cannot deduce null collation");
> and the method HiveDatabaseMetaData.nullsAreSortedAtEnd() throws the
> follwing exeception
> public boolean nullsAreSortedAtEnd() throws SQLException {
> throw new SQLFeatureNotSupportedException("Method not supported");
> }
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Resolved] (CALCITE-5140) Piglet, Spark tests fail in Github CI with OpenJ9 8
[
https://issues.apache.org/jira/browse/CALCITE-5140?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Stamatis Zampetakis resolved CALCITE-5140.
--
Resolution: Fixed
Fixed in
https://github.com/apache/calcite/commit/bb17ab4a56f5862c1da355f6e2bcf6031a05eca0.
Thanks for the PR [~libenchao]!
> Piglet, Spark tests fail in Github CI with OpenJ9 8
> ---
>
> Key: CALCITE-5140
> URL: https://issues.apache.org/jira/browse/CALCITE-5140
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.30.0
>Reporter: xiong duan
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.31.0
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Many PR filed in this process. This failure first appeared on May 2, 2022
> [6303017566|https://github.com/apache/calcite/runs/6303017566?check_suite_focus=true].
> The info:
> {noformat}
>Caused by: javax.security.auth.login.LoginException: unable to find
> LoginModule class: com.ibm.security.auth.module.LinuxLoginModule
> 11591at
> javax.security.auth.login.LoginContext.invoke(LoginContext.java:794)
> 11592at
> javax.security.auth.login.LoginContext.access$000(LoginContext.java:195)
> 11593at
> javax.security.auth.login.LoginContext$4.run(LoginContext.java:682)
> 11594at
> javax.security.auth.login.LoginContext$4.run(LoginContext.java:680)
> 11595at
> java.security.AccessController.doPrivileged(AccessController.java:783)
> 11596at
> javax.security.auth.login.LoginContext.invokePriv(LoginContext.java:680)
> 11597at
> javax.security.auth.login.LoginContext.login(LoginContext.java:587)
> 11598at
> org.apache.hadoop.security.UserGroupInformation.loginUserFromSubject(UserGroupInformation.java:814)
> 11599... 66 more{noformat}
> I check the CALCITE-5003 change, The failure is irrelevant to this PR. This
> happened before we upgrade the JDK 18 in Calcite.
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Updated] (CALCITE-5140) Piglet, Spark tests fail in Github CI with OpenJ9 8
[
https://issues.apache.org/jira/browse/CALCITE-5140?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Stamatis Zampetakis updated CALCITE-5140:
-
Fix Version/s: 1.31.0
> Piglet, Spark tests fail in Github CI with OpenJ9 8
> ---
>
> Key: CALCITE-5140
> URL: https://issues.apache.org/jira/browse/CALCITE-5140
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.30.0
>Reporter: xiong duan
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.31.0
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Many PR filed in this process. This failure first appeared on May 2, 2022
> [6303017566|https://github.com/apache/calcite/runs/6303017566?check_suite_focus=true].
> The info:
> {noformat}
>Caused by: javax.security.auth.login.LoginException: unable to find
> LoginModule class: com.ibm.security.auth.module.LinuxLoginModule
> 11591at
> javax.security.auth.login.LoginContext.invoke(LoginContext.java:794)
> 11592at
> javax.security.auth.login.LoginContext.access$000(LoginContext.java:195)
> 11593at
> javax.security.auth.login.LoginContext$4.run(LoginContext.java:682)
> 11594at
> javax.security.auth.login.LoginContext$4.run(LoginContext.java:680)
> 11595at
> java.security.AccessController.doPrivileged(AccessController.java:783)
> 11596at
> javax.security.auth.login.LoginContext.invokePriv(LoginContext.java:680)
> 11597at
> javax.security.auth.login.LoginContext.login(LoginContext.java:587)
> 11598at
> org.apache.hadoop.security.UserGroupInformation.loginUserFromSubject(UserGroupInformation.java:814)
> 11599... 66 more{noformat}
> I check the CALCITE-5003 change, The failure is irrelevant to this PR. This
> happened before we upgrade the JDK 18 in Calcite.
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Updated] (CALCITE-5140) Piglet, Spark tests fail in Github CI with OpenJ9 8
[
https://issues.apache.org/jira/browse/CALCITE-5140?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Stamatis Zampetakis updated CALCITE-5140:
-
Summary: Piglet, Spark tests fail in Github CI with OpenJ9 8 (was: The
Github CI failed in Linux (OpenJ9 8))
> Piglet, Spark tests fail in Github CI with OpenJ9 8
> ---
>
> Key: CALCITE-5140
> URL: https://issues.apache.org/jira/browse/CALCITE-5140
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.30.0
>Reporter: xiong duan
>Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Many PR filed in this process. This failure first appeared on May 2, 2022
> [6303017566|https://github.com/apache/calcite/runs/6303017566?check_suite_focus=true].
> The info:
> {noformat}
>Caused by: javax.security.auth.login.LoginException: unable to find
> LoginModule class: com.ibm.security.auth.module.LinuxLoginModule
> 11591at
> javax.security.auth.login.LoginContext.invoke(LoginContext.java:794)
> 11592at
> javax.security.auth.login.LoginContext.access$000(LoginContext.java:195)
> 11593at
> javax.security.auth.login.LoginContext$4.run(LoginContext.java:682)
> 11594at
> javax.security.auth.login.LoginContext$4.run(LoginContext.java:680)
> 11595at
> java.security.AccessController.doPrivileged(AccessController.java:783)
> 11596at
> javax.security.auth.login.LoginContext.invokePriv(LoginContext.java:680)
> 11597at
> javax.security.auth.login.LoginContext.login(LoginContext.java:587)
> 11598at
> org.apache.hadoop.security.UserGroupInformation.loginUserFromSubject(UserGroupInformation.java:814)
> 11599... 66 more{noformat}
> I check the CALCITE-5003 change, The failure is irrelevant to this PR. This
> happened before we upgrade the JDK 18 in Calcite.
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Commented] (CALCITE-5140) The Github CI failed in Linux (OpenJ9 8)
[
https://issues.apache.org/jira/browse/CALCITE-5140?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17535017#comment-17535017
]
Stamatis Zampetakis commented on CALCITE-5140:
--
The problem seems to affect certain tests with dependencies to Hadoop. If the
Hadoop version that we are using cannot run with the given JDK for whatever
reasons can't we simply skip the failing tests for this JDK?
> The Github CI failed in Linux (OpenJ9 8)
>
>
> Key: CALCITE-5140
> URL: https://issues.apache.org/jira/browse/CALCITE-5140
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.30.0
>Reporter: xiong duan
>Priority: Major
>
> Many PR filed in this process. This failure first appeared on May 2, 2022
> [6303017566|https://github.com/apache/calcite/runs/6303017566?check_suite_focus=true].
> The info:
> {noformat}
>Caused by: javax.security.auth.login.LoginException: unable to find
> LoginModule class: com.ibm.security.auth.module.LinuxLoginModule
> 11591at
> javax.security.auth.login.LoginContext.invoke(LoginContext.java:794)
> 11592at
> javax.security.auth.login.LoginContext.access$000(LoginContext.java:195)
> 11593at
> javax.security.auth.login.LoginContext$4.run(LoginContext.java:682)
> 11594at
> javax.security.auth.login.LoginContext$4.run(LoginContext.java:680)
> 11595at
> java.security.AccessController.doPrivileged(AccessController.java:783)
> 11596at
> javax.security.auth.login.LoginContext.invokePriv(LoginContext.java:680)
> 11597at
> javax.security.auth.login.LoginContext.login(LoginContext.java:587)
> 11598at
> org.apache.hadoop.security.UserGroupInformation.loginUserFromSubject(UserGroupInformation.java:814)
> 11599... 66 more{noformat}
> I check the CALCITE-5003 change, The failure is irrelevant to this PR. This
> happened before we upgrade the JDK 18 in Calcite.
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Commented] (CALCITE-5146) Udf-Timestamp Function Rel get a weird value
[
https://issues.apache.org/jira/browse/CALCITE-5146?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17534308#comment-17534308
]
Stamatis Zampetakis commented on CALCITE-5146:
--
Is the problem reproducible without the custom UDFs? If you cannot reproduce it
differently then maybe the problem lies in the code of the UDF.
> Udf-Timestamp Function Rel get a weird value
>
>
> Key: CALCITE-5146
> URL: https://issues.apache.org/jira/browse/CALCITE-5146
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.30.0
>Reporter: itxiangkui
>Priority: Major
>
> I have defined several UDFs:
> {code:java}
> public static String from_unixtime(long unixTimestamp, String format) {
> try {
> if (String.valueOf(unixTimestamp).length() == 10) {
> unixTimestamp = 1000 *
> Long.parseLong(String.valueOf(unixTimestamp));
> }
> return DateFormatUtils.format(unixTimestamp, format);
> } catch (Exception e) {
> return null;
> }
> } {code}
>
> {code:java}
> private static final TimeZone DEFAULT_BEIJING_TIME_ZONE =
> TimeZone.getTimeZone(ZoneId.of("Asia/Shanghai"));
> public static long unix_timestamp(String dateStr) {
> long ts = parseToTimeMillis(dateStr, DEFAULT_BEIJING_TIME_ZONE);
> return ts / 1000;
> }{code}
>
>
> *{color:#ff8b00}But from_unixtime got a wrong parameter when you subtract 25
> days{color}*
>
> {code:java}
> 0: jdbc:avatica:remote:url=http://localhost:6> select
> from_unixtime(unix_timestamp(timestamp'2022-05-01')*1000-24*8640,'-MM-dd');
> ++
> | EXPR$0 |
> ++
> | 2022-04-07 |
> ++
> 1 row selected (0.049 seconds)
> 0: jdbc:avatica:remote:url=http://localhost:6> select
> from_unixtime(unix_timestamp(timestamp'2022-05-01')*1000-25*8640,'-MM-dd');
> ++
> | EXPR$0 |
> ++
> | 2022-05-25 |
> ++
> {code}
>
>
>
>
>
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Commented] (CALCITE-5095) Support Java 18 and Guava 31.1-jre
[
https://issues.apache.org/jira/browse/CALCITE-5095?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17529981#comment-17529981
]
Stamatis Zampetakis commented on CALCITE-5095:
--
Fixed in
https://github.com/apache/calcite-avatica/commit/360c0e7c84d754b7fffb64c0e021dbb1833ee283
for calcite-avatica.
> Support Java 18 and Guava 31.1-jre
> --
>
> Key: CALCITE-5095
> URL: https://issues.apache.org/jira/browse/CALCITE-5095
> Project: Calcite
> Issue Type: Bug
>Reporter: Julian Hyde
>Assignee: Benchao Li
>Priority: Major
> Labels: pull-request-available
> Fix For: avatica-1.21.0, 1.31.0
>
> Time Spent: 1.5h
> Remaining Estimate: 0h
>
> Java 18 is released as of 2022-03-22. Calcite should support it at compile
> time and run time.
> We should fix before Calcite release 1.31. To fix this, add JDK 18 to the CI
> targets in {{.github/workflows/main.yml}} and fix any errors/warnings. Modify
> {{site/_docs/howto.md}} and {{{}site/_docs/history.md{}}}.
> We should also fix before Avatica release 1.21. Let's use the same JIRA case
> for Avatica. To fix in Avatica, there will be similar changes to similar
> files.
> There is a new version of Guava (Calcite's current version is 30.0.1-jre, new
> version is 31.1-jre). It makes sense to upgrade Guava at the same time. IIRC
> you need to change {{{}main.yml{}}}, {{{}history.md{}}},
> {{{}gradle.properties{}}}.
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Commented] (CALCITE-5108) Make websites GDPR compliant
[ https://issues.apache.org/jira/browse/CALCITE-5108?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17526273#comment-17526273 ] Stamatis Zampetakis commented on CALCITE-5108: -- [~francischuang] It seems that the change was not propagated automatically to the calcite-site repo. It seems that it is due to the renaming of the master to main (CALCITE-4147). > Make websites GDPR compliant > > > Key: CALCITE-5108 > URL: https://issues.apache.org/jira/browse/CALCITE-5108 > Project: Calcite > Issue Type: Improvement > Components: avatica, site >Reporter: Francis Chuang >Assignee: Francis Chuang >Priority: Major > Fix For: avatica-1.21.0, 1.31.0 > > > Per the email that was just sent out from privacy, we need to make our sites > compliant with GDPR. > For both the calcite and avatica sites, link to the privacy policy: > https://privacy.apache.org/policies/privacy-policy-public.html > For the calcite site, we need to change the embeds for the youtube videos to > a placeholder image that changes into the video player when clicked: > https://privacy.apache.org/faq/committers.html > I recently changed the jekyll-oembed plugin to jekyll-spaceship, but both > jekyll-oembed and jekyll-spaceship embed the video directly and does not put > a placeholder image, so this will need to be investigated. A potential fix is > to just remove the embedded videos as we already supply direct links to the > videos on youtube. -- This message was sent by Atlassian Jira (v8.20.7#820007)
[jira] [Commented] (CALCITE-5108) Make websites GDPR compliant
[ https://issues.apache.org/jira/browse/CALCITE-5108?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17526270#comment-17526270 ] Stamatis Zampetakis commented on CALCITE-5108: -- Fixed in https://github.com/apache/calcite/commit/bc23290fe67725282b8e2294723c8a9368f05486. > Make websites GDPR compliant > > > Key: CALCITE-5108 > URL: https://issues.apache.org/jira/browse/CALCITE-5108 > Project: Calcite > Issue Type: Improvement > Components: avatica, site >Reporter: Francis Chuang >Assignee: Francis Chuang >Priority: Major > Fix For: avatica-1.21.0, 1.31.0 > > > Per the email that was just sent out from privacy, we need to make our sites > compliant with GDPR. > For both the calcite and avatica sites, link to the privacy policy: > https://privacy.apache.org/policies/privacy-policy-public.html > For the calcite site, we need to change the embeds for the youtube videos to > a placeholder image that changes into the video player when clicked: > https://privacy.apache.org/faq/committers.html > I recently changed the jekyll-oembed plugin to jekyll-spaceship, but both > jekyll-oembed and jekyll-spaceship embed the video directly and does not put > a placeholder image, so this will need to be investigated. A potential fix is > to just remove the embedded videos as we already supply direct links to the > videos on youtube. -- This message was sent by Atlassian Jira (v8.20.7#820007)
[jira] [Commented] (CALCITE-5108) Make websites GDPR compliant
[ https://issues.apache.org/jira/browse/CALCITE-5108?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17526011#comment-17526011 ] Stamatis Zampetakis commented on CALCITE-5108: -- I would rather remove the embeds for youtube videos altogether and just retain plain links. We have very few embeds anyways and removing them would make the page more uniform. What do you think? > Make websites GDPR compliant > > > Key: CALCITE-5108 > URL: https://issues.apache.org/jira/browse/CALCITE-5108 > Project: Calcite > Issue Type: Improvement > Components: avatica, site >Reporter: Francis Chuang >Priority: Major > > Per the email that was just sent out from privacy, we need to make our sites > compliant with GDPR. > For both the calcite and avatica sites, link to the privacy policy: > https://privacy.apache.org/policies/privacy-policy-public.html > For the calcite site, we need to change the embeds for the youtube videos to > a placeholder image that changes into the video player when clicked: > https://privacy.apache.org/faq/committers.html > I recently changed the jekyll-oembed plugin to jekyll-spaceship, but both > jekyll-oembed and jekyll-spaceship embed the video directly and does not put > a placeholder image, so this will need to be investigated. A potential fix is > to just remove the embedded videos as we already supply direct links to the > videos on youtube. -- This message was sent by Atlassian Jira (v8.20.7#820007)
[jira] [Commented] (CALCITE-5093) Quantified comparison operators (e.g. ANY) should support ARRAY arguments
[
https://issues.apache.org/jira/browse/CALCITE-5093?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17525577#comment-17525577
]
Stamatis Zampetakis commented on CALCITE-5093:
--
Thanks for the analysis [~yanjing.wang].
Just to complete the picture the situation hasn't changed much in SQL:2011:
{noformat}
::=
::=
{noformat}
> Quantified comparison operators (e.g. ANY) should support ARRAY arguments
> -
>
> Key: CALCITE-5093
> URL: https://issues.apache.org/jira/browse/CALCITE-5093
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.30.0
>Reporter: Omid B
>Priority: Major
>
> Now Calcite supports quantified comparison predicates like this
> {code:java}
> 1 = any(1, 2, 3)
> {code}
> but not
> {code:java}
> 1 = any(array[1, 2, 3])
> {code}
> I made a test
> {code:java}
> @Test void testArrayType() {
> String expr = "1 = any(array[1, 2, 3])";
> expr(expr).ok();
> } {code}
> and its stack trace is
> {noformat}
> org.apache.calcite.runtime.CalciteContextException: From line 1, column 1 to
> line 1, column 23: Values passed to IN operator must have compatible types
> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
> at
> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
> at
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
> at
> org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:505)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:932)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:917)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5278)
> at
> org.apache.calcite.sql.fun.SqlInOperator.deriveType(SqlInOperator.java:187)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6289)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6276)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:161)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1862)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1847)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1050)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:752)
> at
> org.apache.calcite.sql.test.AbstractSqlTester.convertExprToRex(AbstractSqlTester.java:515)
> at
> org.apache.calcite.sql.test.AbstractSqlTester.assertExprConvertsTo(AbstractSqlTester.java:462)
> at
> org.apache.calcite.sql.test.AbstractSqlTester.assertConvertsTo(AbstractSqlTester.java:453)
> at
> org.apache.calcite.test.SqlToRelFixture.convertsTo(SqlToRelFixture.java:106)
> at org.apache.calcite.test.SqlToRelFixture.ok(SqlToRelFixture.java:94)
> at
> org.apache.calcite.test.SqlToRelConverterTest.testArrayType(SqlToRelConverterTest.java:4514)
> 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:725)
> 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.TimeoutInvocation.proceed(TimeoutInvocation.java:46)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:149)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:140)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:84)
> at
> org.junit.jupiter.engine.execution.ExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(ExecutableInvoker.java:115)
> at
> org.junit.jupiter.engine.execution.ExecutableInvoker.lambda$invoke$0(ExecutableInvoker.java:105)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
> at
> org.junit.jupiter.engine.execution.
[jira] [Commented] (CALCITE-5093) Cannot validate Array type
[
https://issues.apache.org/jira/browse/CALCITE-5093?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17523113#comment-17523113
]
Stamatis Zampetakis commented on CALCITE-5093:
--
Hey [~omidbb], first of all when you create a bug report it helps to include
the error/stacktrace that you get by running the query.
Calcite follows closely the SQL standard and afterwards with various extensions
tries to cover particularities of other DBMS. Before talking about
contributions it should be clear what the SQL standard says with respect to
this query and what other DMBS do (not only Postgres).
Is this a valid query from the SQL standard perspective?
Which other DBMS can handle this query?
> Cannot validate Array type
> --
>
> Key: CALCITE-5093
> URL: https://issues.apache.org/jira/browse/CALCITE-5093
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.30.0
>Reporter: Omid B
>Priority: Major
>
> I'm trying to validate a simple query against a schema:
>
> {code:java}
> // SQL Query
> select id from product
> where 'item0' = any(attributes) {code}
> Running the validator and digging deep in the place that throws an exception
> I get to here:
> [https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/sql/type/SameOperandTypeChecker.java#L100]
>
> The inputs are two types:
> {code:java}
> RecordType(CHAR(5) ROW_VALUE)
> RecordType(VARCHAR ARRAY ROW_VALUE){code}
> As you can see, these types must be compatible but Calcite don't match them
> right.
>
>
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Commented] (CALCITE-3796) Support Array operator <, <=, >, >=
[ https://issues.apache.org/jira/browse/CALCITE-3796?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17523112#comment-17523112 ] Stamatis Zampetakis commented on CALCITE-3796: -- Hey [~omidbb], there is no guarantee that what Postgres supports Calcite will support it as well. Can you be more precise on what exactly you tried and it didn't work? > Support Array operator <, <=, >, >= > --- > > Key: CALCITE-3796 > URL: https://issues.apache.org/jira/browse/CALCITE-3796 > Project: Calcite > Issue Type: Improvement >Reporter: Rui Wang >Priority: Major > Labels: pull-request-available > Time Spent: 1h > Remaining Estimate: 0h > > Seems like Calcite has an =, <> support for array type. But it does not have > <, <=, >, >= support. > Postgresql array operators: > https://www.postgresql.org/docs/9.6/functions-array.html -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (CALCITE-4992) RestClient resource leak in Elasticsearch adapter
[
https://issues.apache.org/jira/browse/CALCITE-4992?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17521698#comment-17521698
]
Stamatis Zampetakis commented on CALCITE-4992:
--
[~julianhyde] It is definitely an improvement over the current state so I am OK
merging this to master. We could possibly introduce a {{CalciteSystemProperty}}
to allow end-users control the size of the cache but I don't consider this a
must do for moving forward.
> RestClient resource leak in Elasticsearch adapter
> -
>
> Key: CALCITE-4992
> URL: https://issues.apache.org/jira/browse/CALCITE-4992
> Project: Calcite
> Issue Type: Bug
> Components: elasticsearch-adapter
>Affects Versions: 1.29.0
>Reporter: James Turton
>Assignee: James Turton
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.31.0
>
> Time Spent: 5h 10m
> Remaining Estimate: 0h
>
> RestClient instances should receive a `close()`call at the end of their
> lifecyle, as documented here.
> [https://www.elastic.co/guide/en/elasticsearch/client/java-rest/current/java-rest-low-usage-initialization.html]
> We have monitored file descriptor leaks on systems running Apache Drill and
> have come to believe (but not proven) that they are associated with Drill's
> ElasticSearch plugin, and that in turn makes use of Calcite's ElasticSearch
> adapter.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Resolved] (CALCITE-4936) Generalize FilterCalcMergeRule/ProjectCalcMergeRule to accept any Filter/Project/Calc operator
[
https://issues.apache.org/jira/browse/CALCITE-4936?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Stamatis Zampetakis resolved CALCITE-4936.
--
Resolution: Fixed
Fixed in
https://github.com/apache/calcite/commit/ef2cc1df21a73ad0268ccb869c976b11eff319b4.
Thanks for the PR [~mgramin]!
> Generalize FilterCalcMergeRule/ProjectCalcMergeRule to accept any
> Filter/Project/Calc operator
> --
>
> Key: CALCITE-4936
> URL: https://issues.apache.org/jira/browse/CALCITE-4936
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.28.0
>Reporter: Maksim Gramin
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.31.0
>
> Time Spent: 1h 40m
> Remaining Estimate: 0h
>
> I noticed that rules ProjectCalcMergeRule and FilterCalcMergeRule strictly
> dependent on LogicalCalc class (e.g. casting call.rel to LogicalCalc, using
> LogicalCalc.create method and etc). I suggest making them more generalized
> (like CalcMergeRule) and use only Project, Filter and Calc interfaces, which
> will expand the range of their applying.
> Concrete:
> 1. In FilterCalcMergeRule get rid of casting to LogicalCalc and LogicalFilter:
> {code:java}
> @Override public void onMatch(RelOptRuleCall call) {
> final Filter filter = call.rel(0);
> final Calc calc = call.rel(1);{code}
>
> 2. In FilterCalcMergeRule and FilterCalcMergeRule replace LogicalCalc.create
> with calc.copy:
> {code:java}
> final Calc newCalc = calc.copy(calc.getTraitSet(), calc.getInput(),
> mergedProgram);{code}
>
> 3. In ProjectCalcMergeRule replace the code:
> {code:java}
> if (RexOver.containsOver(program)) {
> LogicalCalc projectAsCalc = LogicalCalc.create(calc, program);
> call.transformTo(projectAsCalc);
> return;
> }{code}
> with a simple one:
> {code:java}
> if (RexOver.containsOver(program)) {
> return;
> }{code}
> because special rule ProjectToCalcRule convert LogicalProject to LogicalCalc.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Commented] (CALCITE-4936) Generalize FilterCalcMergeRule/ProjectCalcMergeRule to accept any Filter/Project/Calc operator
[
https://issues.apache.org/jira/browse/CALCITE-4936?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17520454#comment-17520454
]
Stamatis Zampetakis commented on CALCITE-4936:
--
After this change there is slight (breaking) change in the behavior of the
{{ProjectCalcMergeRule}} when the top project contains a window function.
+Old behavior:+
The {{Project}} operator is transformed into {{Calc}}.
+New behavior:+
The {{Project}} operator is not transformed and the rule becomes NOOP.
For most users the change will pass unnoticed since the {{ProjectToCalcRule}}
will be used to complete the transformation.
> Generalize FilterCalcMergeRule/ProjectCalcMergeRule to accept any
> Filter/Project/Calc operator
> --
>
> Key: CALCITE-4936
> URL: https://issues.apache.org/jira/browse/CALCITE-4936
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.28.0
>Reporter: Maksim Gramin
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.31.0
>
> Time Spent: 1h 10m
> Remaining Estimate: 0h
>
> I noticed that rules ProjectCalcMergeRule and FilterCalcMergeRule strictly
> dependent on LogicalCalc class (e.g. casting call.rel to LogicalCalc, using
> LogicalCalc.create method and etc). I suggest making them more generalized
> (like CalcMergeRule) and use only Project, Filter and Calc interfaces, which
> will expand the range of their applying.
> Concrete:
> 1. In FilterCalcMergeRule get rid of casting to LogicalCalc and LogicalFilter:
> {code:java}
> @Override public void onMatch(RelOptRuleCall call) {
> final Filter filter = call.rel(0);
> final Calc calc = call.rel(1);{code}
>
> 2. In FilterCalcMergeRule and FilterCalcMergeRule replace LogicalCalc.create
> with calc.copy:
> {code:java}
> final Calc newCalc = calc.copy(calc.getTraitSet(), calc.getInput(),
> mergedProgram);{code}
>
> 3. In ProjectCalcMergeRule replace the code:
> {code:java}
> if (RexOver.containsOver(program)) {
> LogicalCalc projectAsCalc = LogicalCalc.create(calc, program);
> call.transformTo(projectAsCalc);
> return;
> }{code}
> with a simple one:
> {code:java}
> if (RexOver.containsOver(program)) {
> return;
> }{code}
> because special rule ProjectToCalcRule convert LogicalProject to LogicalCalc.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Updated] (CALCITE-4936) Generalize FilterCalcMergeRule/ProjectCalcMergeRule to accept any Filter/Project/Calc operator
[
https://issues.apache.org/jira/browse/CALCITE-4936?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Stamatis Zampetakis updated CALCITE-4936:
-
Fix Version/s: 1.31.0
> Generalize FilterCalcMergeRule/ProjectCalcMergeRule to accept any
> Filter/Project/Calc operator
> --
>
> Key: CALCITE-4936
> URL: https://issues.apache.org/jira/browse/CALCITE-4936
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.28.0
>Reporter: Maksim Gramin
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.31.0
>
> Time Spent: 1h 10m
> Remaining Estimate: 0h
>
> I noticed that rules ProjectCalcMergeRule and FilterCalcMergeRule strictly
> dependent on LogicalCalc class (e.g. casting call.rel to LogicalCalc, using
> LogicalCalc.create method and etc). I suggest making them more generalized
> (like CalcMergeRule) and use only Project, Filter and Calc interfaces, which
> will expand the range of their applying.
> Concrete:
> 1. In FilterCalcMergeRule get rid of casting to LogicalCalc and LogicalFilter:
> {code:java}
> @Override public void onMatch(RelOptRuleCall call) {
> final Filter filter = call.rel(0);
> final Calc calc = call.rel(1);{code}
>
> 2. In FilterCalcMergeRule and FilterCalcMergeRule replace LogicalCalc.create
> with calc.copy:
> {code:java}
> final Calc newCalc = calc.copy(calc.getTraitSet(), calc.getInput(),
> mergedProgram);{code}
>
> 3. In ProjectCalcMergeRule replace the code:
> {code:java}
> if (RexOver.containsOver(program)) {
> LogicalCalc projectAsCalc = LogicalCalc.create(calc, program);
> call.transformTo(projectAsCalc);
> return;
> }{code}
> with a simple one:
> {code:java}
> if (RexOver.containsOver(program)) {
> return;
> }{code}
> because special rule ProjectToCalcRule convert LogicalProject to LogicalCalc.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Updated] (CALCITE-4936) Generalize FilterCalcMergeRule/ProjectCalcMergeRule to accept any Filter/Project/Calc operator
[
https://issues.apache.org/jira/browse/CALCITE-4936?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Stamatis Zampetakis updated CALCITE-4936:
-
Summary: Generalize FilterCalcMergeRule/ProjectCalcMergeRule to accept any
Filter/Project/Calc operator (was: Using LogicalCalc in ProjectCalcMergeRule
and FilterCalcMergeRule)
> Generalize FilterCalcMergeRule/ProjectCalcMergeRule to accept any
> Filter/Project/Calc operator
> --
>
> Key: CALCITE-4936
> URL: https://issues.apache.org/jira/browse/CALCITE-4936
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.28.0
>Reporter: Maksim Gramin
>Priority: Major
> Labels: pull-request-available
> Time Spent: 1h 10m
> Remaining Estimate: 0h
>
> I noticed that rules ProjectCalcMergeRule and FilterCalcMergeRule strictly
> dependent on LogicalCalc class (e.g. casting call.rel to LogicalCalc, using
> LogicalCalc.create method and etc). I suggest making them more generalized
> (like CalcMergeRule) and use only Project, Filter and Calc interfaces, which
> will expand the range of their applying.
> Concrete:
> 1. In FilterCalcMergeRule get rid of casting to LogicalCalc and LogicalFilter:
> {code:java}
> @Override public void onMatch(RelOptRuleCall call) {
> final Filter filter = call.rel(0);
> final Calc calc = call.rel(1);{code}
>
> 2. In FilterCalcMergeRule and FilterCalcMergeRule replace LogicalCalc.create
> with calc.copy:
> {code:java}
> final Calc newCalc = calc.copy(calc.getTraitSet(), calc.getInput(),
> mergedProgram);{code}
>
> 3. In ProjectCalcMergeRule replace the code:
> {code:java}
> if (RexOver.containsOver(program)) {
> LogicalCalc projectAsCalc = LogicalCalc.create(calc, program);
> call.transformTo(projectAsCalc);
> return;
> }{code}
> with a simple one:
> {code:java}
> if (RexOver.containsOver(program)) {
> return;
> }{code}
> because special rule ProjectToCalcRule convert LogicalProject to LogicalCalc.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Commented] (CALCITE-5086) Calcite supports OFFSET start LIMIT count expression
[
https://issues.apache.org/jira/browse/CALCITE-5086?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17519148#comment-17519148
]
Stamatis Zampetakis commented on CALCITE-5086:
--
[~yanjing.wang] Apart from the parser, you probably need to touch also
{{SqlConformance}} so that you are able to throw an exception when the
conformance does not allow its use. You can use the changes in CALCITE-1886 as
a guide.
> Calcite supports OFFSET start LIMIT count expression
>
>
> Key: CALCITE-5086
> URL: https://issues.apache.org/jira/browse/CALCITE-5086
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.30.0
> Environment: jdk8
>Reporter: yanjing.wang
>Assignee: yanjing.wang
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.31.0
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Calcite supports 'LIMIT count OFFSET start' now, otherwise trino supports
> 'OFFSET start LIMIT count', we can make an improvement to support both.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Commented] (CALCITE-5086) Calcite supports OFFSET start LIMIT count expression
[ https://issues.apache.org/jira/browse/CALCITE-5086?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17518837#comment-17518837 ] Stamatis Zampetakis commented on CALCITE-5086: -- We have various other variations of LIMIT/OFFSET in the core parser so I think this change can live in the core as [~yanjing.wang] did in the PR. > Calcite supports OFFSET start LIMIT count expression > > > Key: CALCITE-5086 > URL: https://issues.apache.org/jira/browse/CALCITE-5086 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.30.0 > Environment: jdk8 >Reporter: yanjing.wang >Assignee: yanjing.wang >Priority: Minor > Labels: pull-request-available > Fix For: 1.31.0 > > Time Spent: 10m > Remaining Estimate: 0h > > Calcite supports 'LIMIT count OFFSET start' now, otherwise trino supports > 'OFFSET start LIMIT count', we can make an improvement to support both. -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (CALCITE-5078) ORDER BY gets confused by upper/lower case
[
https://issues.apache.org/jira/browse/CALCITE-5078?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17515776#comment-17515776
]
Stamatis Zampetakis commented on CALCITE-5078:
--
Order depends on locale and collation and is usually configurable in every
DBMS. The result you are seeing seems correct to me.
> ORDER BY gets confused by upper/lower case
> --
>
> Key: CALCITE-5078
> URL: https://issues.apache.org/jira/browse/CALCITE-5078
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.30.0
>Reporter: Magnus Mogren
>Priority: Major
>
> This SQL returnes the values in the wrong order:
> {{SELECT * FROM (VALUES ('a'), ('B')) AS tbl(col1) order by col1 asc}}
> "B" is returned before "a".
>
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
