[jira] [Commented] (CALCITE-6020) SqlToRelConverter should not replace windowed SUM with equivalent expression using SUM0
[
https://issues.apache.org/jira/browse/CALCITE-6020?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17868608#comment-17868608
]
Zoltan Haindrich commented on CALCITE-6020:
---
seems like I've never updated the ticket about what I have in mindI do
remember that I've wrote a longer comment but I guess that was lost...
[~zabetak] suggested above to possibly enhance
[AggregateReduceFunctionsRule|https://github.com/apache/calcite/blob/b33dddeb3a79cf4da1ac3c72ae004a893945fc60/core/src/main/java/org/apache/calcite/rel/rules/AggregateReduceFunctionsRule.java#L288]
; which could do the same rewrite and some more for non-windowed aggregates; I
was planning to explore:
* extend that rule to also consider windowed aggregates
* possibly enhance it (if needed) to enable fine grain control whether to
rewrite a given agg call or not
As that's completely different approach - it could be considered independently.
The ability to control the sql2rel level conversion would be also usefull!
> SqlToRelConverter should not replace windowed SUM with equivalent expression
> using SUM0
> ---
>
> Key: CALCITE-6020
> URL: https://issues.apache.org/jira/browse/CALCITE-6020
> Project: Calcite
> Issue Type: Improvement
>Reporter: Zoltan Haindrich
>Assignee: Zoltan Haindrich
>Priority: Major
> Labels: pull-request-available
>
> {{SqlToRelConverter}} replaces {{SUM}} with {{SUM0}} around
> [here|https://github.com/apache/calcite/blob/e1991e08a225ef08c2402ab35c310d88fff3c222/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L5885]
> This might have been needed at some point in the past - but I think it will
> be better to leave it as {{SUM}} - as in case there is no {{SUM0}} in the
> system that will be replaced with a {{COALESCE(SUM(...) , 0 )}} to provide it
> - as see
> [here|https://github.com/apache/calcite/blob/e1991e08a225ef08c2402ab35c310d88fff3c222/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java#L1288]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-6473) HAVING clauses may not contain window functions
[
https://issues.apache.org/jira/browse/CALCITE-6473?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zoltan Haindrich resolved CALCITE-6473.
---
Fix Version/s: 1.38.0
Resolution: Fixed
merged into master as
[b33dddeb3a79cf4da1ac3c72ae004a893945fc60|https://github.com/apache/calcite/commit/b33dddeb3a79cf4da1ac3c72ae004a893945fc60]
> HAVING clauses may not contain window functions
> ---
>
> Key: CALCITE-6473
> URL: https://issues.apache.org/jira/browse/CALCITE-6473
> Project: Calcite
> Issue Type: Bug
>Reporter: Zoltan Haindrich
>Assignee: Zoltan Haindrich
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> according to the standard:
> ISO/IEC 9075-2:2011(E)
> 7.10
> Syntax Rules # 5
> {code}
> The shall not contain a without an
> intervening .
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6492) Support aggregate functions which could process DISTINCT natively
[
https://issues.apache.org/jira/browse/CALCITE-6492?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17868048#comment-17868048
]
Zoltan Haindrich edited comment on CALCITE-6492 at 7/23/24 3:22 PM:
I might have missed the distinction between the two because
`getDistinctOptionality` was used in
[AggregateExpandDistinctAggregatesRule|https://github.com/apache/calcite/blob/ea1a255fd071a518fe8d30e361efa0696164a037/core/src/main/java/org/apache/calcite/rel/rules/AggregateExpandDistinctAggregatesRule.java#L826]
as well.
Thank you for the pointers.
I feel like adding properties like these should follow is the interface way
{{singleton}} and {{splittable}} was using - and use it with {{instanceof`}} or
introduce either an {{as}} method or some Util functions.
I don't feel like it should be more confined than just an interface; as pieces
which could do more like rewrites/etc could benefit from the freedom of having
an interface more.
However in this case I feel like it would make sense to add it to the main
interface; as `distinct` is part of the `SqlAggFunction`'s API.
I believe the best default answer for the question {{isNativelyExecutable}} by
the current engine would be to `return !distinct;` - so that implementors have
to override it to show that its allowed.
It would be nice to also check this for the produced plan - I'll see if that's
possible
EDIT: I've just looked into these things a bit - and I was thinking that
`distinct` (and some other things) are part of the operator (its not) - however
its bound to the AggCall - and I see a few more things here which was mentioned
above - which kinda places the above things in a new view (for me at least) -
I'll keep thinking about it a bit...
was (Author: kgyrtkirk):
I might have missed the distinction between the two because
`getDistinctOptionality` was used in
[AggregateExpandDistinctAggregatesRule|https://github.com/apache/calcite/blob/ea1a255fd071a518fe8d30e361efa0696164a037/core/src/main/java/org/apache/calcite/rel/rules/AggregateExpandDistinctAggregatesRule.java#L826]
as well.
Thank you for the pointers.
I feel like adding properties like these should follow is the interface way
{{singleton}} and {{splittable}} was using - and use it with {{instanceof`}} or
introduce either an {{as}} method or some Util functions.
I don't feel like it should be more confined than just an interface; as pieces
which could do more like rewrites/etc could benefit from the freedom of having
an interface more.
However in this case I feel like it would make sense to add it to the main
interface; as `distinct` is part of the `SqlAggFunction`'s API.
I believe the best default answer for the question {{isNativelyExecutable}} by
the current engine would be to `return !distinct;` - so that implementors have
to override it to show that its allowed.
It would be nice to also check this for the produced plan - I'll see if that's
possible
> Support aggregate functions which could process DISTINCT natively
> -
>
> Key: CALCITE-6492
> URL: https://issues.apache.org/jira/browse/CALCITE-6492
> Project: Calcite
> Issue Type: Improvement
>Reporter: Zoltan Haindrich
>Assignee: Zoltan Haindrich
>Priority: Major
>
> This could be usefull if the execution engine natively supports some distinct
> aggregations natively - there is no rewrite necessary for these functions.
> Currently there is support
> [SqlAggFunction#getDistinctOptionality|https://github.com/apache/calcite/blob/0deab6f7e0cb4ec63eae8b59477d6f0fadfd11e8/core/src/main/java/org/apache/calcite/sql/SqlAggFunction.java#L187-L189]
> - which have overlaps with this - possibly the closest would be to set it to
> *IGNORED* if its supported natively...however
> * that's a bit misleading as its not IGNORED; but supported...
> * there is also
> [checkArgument|https://github.com/apache/calcite/blob/0deab6f7e0cb4ec63eae8b59477d6f0fadfd11e8/core/src/main/java/org/apache/calcite/rel/core/AggregateCall.java#L125]
> which ensures that *distinct* is not accepted in tht case.
> More or less the end result would be to also enhance
> AggregateExpandDistinctAggregatesRule with the ability to ignore aggregates.
> note: In Druid
> * if approximationCountDistinct is disabled ; that [enables a calcite rule
> which rewrites *all* disitnct
> aggregates|https://github.com/apache/druid/blob/master/sql/src/main/java/org/apache/druid/sql/calcite/planner/CalciteRulesManager.java#L496-L503]
> * in the meantime there are also some aggregate functions which support
> *distinct* natively like
> [string_agg|https://github.com/apache/druid/blob/c9aae9d8e683c0cc9c4687e526b8270f744c57c2/sql/src/main/java/org/apache/druid/sql/calcite/aggregation/builtin/StringSqlAggregator.java#L154]
[jira] [Commented] (CALCITE-6492) Support aggregate functions which could process DISTINCT natively
[
https://issues.apache.org/jira/browse/CALCITE-6492?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17868048#comment-17868048
]
Zoltan Haindrich commented on CALCITE-6492:
---
I might have missed the distinction between the two because
`getDistinctOptionality` was used in
[AggregateExpandDistinctAggregatesRule|https://github.com/apache/calcite/blob/ea1a255fd071a518fe8d30e361efa0696164a037/core/src/main/java/org/apache/calcite/rel/rules/AggregateExpandDistinctAggregatesRule.java#L826]
as well.
Thank you for the pointers.
I feel like adding properties like these should follow is the interface way
{{singleton}} and {{splittable}} was using - and use it with {{instanceof`}} or
introduce either an {{as}} method or some Util functions.
I don't feel like it should be more confined than just an interface; as pieces
which could do more like rewrites/etc could benefit from the freedom of having
an interface more.
However in this case I feel like it would make sense to add it to the main
interface; as `distinct` is part of the `SqlAggFunction`'s API.
I believe the best default answer for the question {{isNativelyExecutable}} by
the current engine would be to `return !distinct;` - so that implementors have
to override it to show that its allowed.
It would be nice to also check this for the produced plan - I'll see if that's
possible
> Support aggregate functions which could process DISTINCT natively
> -
>
> Key: CALCITE-6492
> URL: https://issues.apache.org/jira/browse/CALCITE-6492
> Project: Calcite
> Issue Type: Improvement
>Reporter: Zoltan Haindrich
>Assignee: Zoltan Haindrich
>Priority: Major
>
> This could be usefull if the execution engine natively supports some distinct
> aggregations natively - there is no rewrite necessary for these functions.
> Currently there is support
> [SqlAggFunction#getDistinctOptionality|https://github.com/apache/calcite/blob/0deab6f7e0cb4ec63eae8b59477d6f0fadfd11e8/core/src/main/java/org/apache/calcite/sql/SqlAggFunction.java#L187-L189]
> - which have overlaps with this - possibly the closest would be to set it to
> *IGNORED* if its supported natively...however
> * that's a bit misleading as its not IGNORED; but supported...
> * there is also
> [checkArgument|https://github.com/apache/calcite/blob/0deab6f7e0cb4ec63eae8b59477d6f0fadfd11e8/core/src/main/java/org/apache/calcite/rel/core/AggregateCall.java#L125]
> which ensures that *distinct* is not accepted in tht case.
> More or less the end result would be to also enhance
> AggregateExpandDistinctAggregatesRule with the ability to ignore aggregates.
> note: In Druid
> * if approximationCountDistinct is disabled ; that [enables a calcite rule
> which rewrites *all* disitnct
> aggregates|https://github.com/apache/druid/blob/master/sql/src/main/java/org/apache/druid/sql/calcite/planner/CalciteRulesManager.java#L496-L503]
> * in the meantime there are also some aggregate functions which support
> *distinct* natively like
> [string_agg|https://github.com/apache/druid/blob/c9aae9d8e683c0cc9c4687e526b8270f744c57c2/sql/src/main/java/org/apache/druid/sql/calcite/aggregation/builtin/StringSqlAggregator.java#L154]
> - which doesn't need any rewrites
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6492) Support aggregate functions which could process DISTINCT natively
[ https://issues.apache.org/jira/browse/CALCITE-6492?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17867770#comment-17867770 ] Zoltan Haindrich commented on CALCITE-6492: --- the *Optionality* class have the following values * MANDATORY * OPTIONAL * IGNORED * FORBIDDEN Option A: Introduce a separate enum should be introduced instead of reusing *Optionality* I think for the distinct keyword we may have the following cases: | is distinct allowed | distinct natively supported | ? | | no | * | FORBIDDEN | | yes | no | ALLOWED_WITH_REWRITES | | yes | yes | NATIVELY_SUPPORTED | Option B: Declare two functions one with *MANDATORY* and another with *FORBIDDEN* - and try to utilize that. Haven't yet explored this. Option C: remove the *checkArgument* call to allow *IGNORED* [here|https://github.com/apache/calcite/blob/0deab6f7e0cb4ec63eae8b59477d6f0fadfd11e8/core/src/main/java/org/apache/calcite/rel/core/AggregateCall.java#L125] and consider it a valid setting. I believe the best would be to do (A) > Support aggregate functions which could process DISTINCT natively > - > > Key: CALCITE-6492 > URL: https://issues.apache.org/jira/browse/CALCITE-6492 > Project: Calcite > Issue Type: Improvement >Reporter: Zoltan Haindrich >Assignee: Zoltan Haindrich >Priority: Major > > This could be usefull if the execution engine natively supports some distinct > aggregations natively - there is no rewrite necessary for these functions. > Currently there is support > [SqlAggFunction#getDistinctOptionality|https://github.com/apache/calcite/blob/0deab6f7e0cb4ec63eae8b59477d6f0fadfd11e8/core/src/main/java/org/apache/calcite/sql/SqlAggFunction.java#L187-L189] > - which have overlaps with this - possibly the closest would be to set it to > *IGNORED* if its supported natively...however > * that's a bit misleading as its not IGNORED; but supported... > * there is also > [checkArgument|https://github.com/apache/calcite/blob/0deab6f7e0cb4ec63eae8b59477d6f0fadfd11e8/core/src/main/java/org/apache/calcite/rel/core/AggregateCall.java#L125] > which ensures that *distinct* is not accepted in tht case. > More or less the end result would be to also enhance > AggregateExpandDistinctAggregatesRule with the ability to ignore aggregates. > note: In Druid > * if approximationCountDistinct is disabled ; that [enables a calcite rule > which rewrites *all* disitnct > aggregates|https://github.com/apache/druid/blob/master/sql/src/main/java/org/apache/druid/sql/calcite/planner/CalciteRulesManager.java#L496-L503] > * in the meantime there are also some aggregate functions which support > *distinct* natively like > [string_agg|https://github.com/apache/druid/blob/c9aae9d8e683c0cc9c4687e526b8270f744c57c2/sql/src/main/java/org/apache/druid/sql/calcite/aggregation/builtin/StringSqlAggregator.java#L154] > - which doesn't need any rewrites -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6492) Support aggregate functions which could process DISTINCT natively
Zoltan Haindrich created CALCITE-6492: - Summary: Support aggregate functions which could process DISTINCT natively Key: CALCITE-6492 URL: https://issues.apache.org/jira/browse/CALCITE-6492 Project: Calcite Issue Type: Improvement Reporter: Zoltan Haindrich Assignee: Zoltan Haindrich This could be usefull if the execution engine natively supports some distinct aggregations natively - there is no rewrite necessary for these functions. Currently there is support [SqlAggFunction#getDistinctOptionality|https://github.com/apache/calcite/blob/0deab6f7e0cb4ec63eae8b59477d6f0fadfd11e8/core/src/main/java/org/apache/calcite/sql/SqlAggFunction.java#L187-L189] - which have overlaps with this - possibly the closest would be to set it to *IGNORED* if its supported natively...however * that's a bit misleading as its not IGNORED; but supported... * there is also [checkArgument|https://github.com/apache/calcite/blob/0deab6f7e0cb4ec63eae8b59477d6f0fadfd11e8/core/src/main/java/org/apache/calcite/rel/core/AggregateCall.java#L125] which ensures that *distinct* is not accepted in tht case. More or less the end result would be to also enhance AggregateExpandDistinctAggregatesRule with the ability to ignore aggregates. note: In Druid * if approximationCountDistinct is disabled ; that [enables a calcite rule which rewrites *all* disitnct aggregates|https://github.com/apache/druid/blob/master/sql/src/main/java/org/apache/druid/sql/calcite/planner/CalciteRulesManager.java#L496-L503] * in the meantime there are also some aggregate functions which support *distinct* natively like [string_agg](https://github.com/apache/druid/blob/c9aae9d8e683c0cc9c4687e526b8270f744c57c2/sql/src/main/java/org/apache/druid/sql/calcite/aggregation/builtin/StringSqlAggregator.java#L154) - which doesn't need any rewrites -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6492) Support aggregate functions which could process DISTINCT natively
[ https://issues.apache.org/jira/browse/CALCITE-6492?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Zoltan Haindrich updated CALCITE-6492: -- Description: This could be usefull if the execution engine natively supports some distinct aggregations natively - there is no rewrite necessary for these functions. Currently there is support [SqlAggFunction#getDistinctOptionality|https://github.com/apache/calcite/blob/0deab6f7e0cb4ec63eae8b59477d6f0fadfd11e8/core/src/main/java/org/apache/calcite/sql/SqlAggFunction.java#L187-L189] - which have overlaps with this - possibly the closest would be to set it to *IGNORED* if its supported natively...however * that's a bit misleading as its not IGNORED; but supported... * there is also [checkArgument|https://github.com/apache/calcite/blob/0deab6f7e0cb4ec63eae8b59477d6f0fadfd11e8/core/src/main/java/org/apache/calcite/rel/core/AggregateCall.java#L125] which ensures that *distinct* is not accepted in tht case. More or less the end result would be to also enhance AggregateExpandDistinctAggregatesRule with the ability to ignore aggregates. note: In Druid * if approximationCountDistinct is disabled ; that [enables a calcite rule which rewrites *all* disitnct aggregates|https://github.com/apache/druid/blob/master/sql/src/main/java/org/apache/druid/sql/calcite/planner/CalciteRulesManager.java#L496-L503] * in the meantime there are also some aggregate functions which support *distinct* natively like [string_agg|https://github.com/apache/druid/blob/c9aae9d8e683c0cc9c4687e526b8270f744c57c2/sql/src/main/java/org/apache/druid/sql/calcite/aggregation/builtin/StringSqlAggregator.java#L154] - which doesn't need any rewrites was: This could be usefull if the execution engine natively supports some distinct aggregations natively - there is no rewrite necessary for these functions. Currently there is support [SqlAggFunction#getDistinctOptionality|https://github.com/apache/calcite/blob/0deab6f7e0cb4ec63eae8b59477d6f0fadfd11e8/core/src/main/java/org/apache/calcite/sql/SqlAggFunction.java#L187-L189] - which have overlaps with this - possibly the closest would be to set it to *IGNORED* if its supported natively...however * that's a bit misleading as its not IGNORED; but supported... * there is also [checkArgument|https://github.com/apache/calcite/blob/0deab6f7e0cb4ec63eae8b59477d6f0fadfd11e8/core/src/main/java/org/apache/calcite/rel/core/AggregateCall.java#L125] which ensures that *distinct* is not accepted in tht case. More or less the end result would be to also enhance AggregateExpandDistinctAggregatesRule with the ability to ignore aggregates. note: In Druid * if approximationCountDistinct is disabled ; that [enables a calcite rule which rewrites *all* disitnct aggregates|https://github.com/apache/druid/blob/master/sql/src/main/java/org/apache/druid/sql/calcite/planner/CalciteRulesManager.java#L496-L503] * in the meantime there are also some aggregate functions which support *distinct* natively like [string_agg](https://github.com/apache/druid/blob/c9aae9d8e683c0cc9c4687e526b8270f744c57c2/sql/src/main/java/org/apache/druid/sql/calcite/aggregation/builtin/StringSqlAggregator.java#L154) - which doesn't need any rewrites > Support aggregate functions which could process DISTINCT natively > - > > Key: CALCITE-6492 > URL: https://issues.apache.org/jira/browse/CALCITE-6492 > Project: Calcite > Issue Type: Improvement >Reporter: Zoltan Haindrich >Assignee: Zoltan Haindrich >Priority: Major > > This could be usefull if the execution engine natively supports some distinct > aggregations natively - there is no rewrite necessary for these functions. > Currently there is support > [SqlAggFunction#getDistinctOptionality|https://github.com/apache/calcite/blob/0deab6f7e0cb4ec63eae8b59477d6f0fadfd11e8/core/src/main/java/org/apache/calcite/sql/SqlAggFunction.java#L187-L189] > - which have overlaps with this - possibly the closest would be to set it to > *IGNORED* if its supported natively...however > * that's a bit misleading as its not IGNORED; but supported... > * there is also > [checkArgument|https://github.com/apache/calcite/blob/0deab6f7e0cb4ec63eae8b59477d6f0fadfd11e8/core/src/main/java/org/apache/calcite/rel/core/AggregateCall.java#L125] > which ensures that *distinct* is not accepted in tht case. > More or less the end result would be to also enhance > AggregateExpandDistinctAggregatesRule with the ability to ignore aggregates. > note: In Druid > * if approximationCountDistinct is disabled ; that [enables a calcite rule > which rewrites *all* disitnct > aggregates|https://github.com/apache/druid/blob/master/sql/src/main/java/org/apache/druid/sql/calcite/planner/CalciteRulesManager.java#L496-L503] > * in the meantime there are
[jira] [Updated] (CALCITE-6473) HAVING clauses may not contain window functions
[
https://issues.apache.org/jira/browse/CALCITE-6473?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zoltan Haindrich updated CALCITE-6473:
--
Description:
according to the standard:
ISO/IEC 9075-2:2011(E)
7.10
Syntax Rules # 5
{code}
The shall not contain a without an
intervening .
{code}
was:
according to the standard:
{code}
The shall not contain a without an
intervening .
{code}
> HAVING clauses may not contain window functions
> ---
>
> Key: CALCITE-6473
> URL: https://issues.apache.org/jira/browse/CALCITE-6473
> Project: Calcite
> Issue Type: Bug
>Reporter: Zoltan Haindrich
>Assignee: Zoltan Haindrich
>Priority: Major
> Labels: pull-request-available
>
> according to the standard:
> ISO/IEC 9075-2:2011(E)
> 7.10
> Syntax Rules # 5
> {code}
> The shall not contain a without an
> intervening .
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6473) HAVING clauses may not contain window functions
Zoltan Haindrich created CALCITE-6473:
-
Summary: HAVING clauses may not contain window functions
Key: CALCITE-6473
URL: https://issues.apache.org/jira/browse/CALCITE-6473
Project: Calcite
Issue Type: Bug
Reporter: Zoltan Haindrich
Assignee: Zoltan Haindrich
according to the standard:
{code}
The shall not contain a without an
intervening .
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-6435) SqlToRel conversion of IN expressions may lead to incorrect simplifications
[
https://issues.apache.org/jira/browse/CALCITE-6435?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zoltan Haindrich resolved CALCITE-6435.
---
Fix Version/s: 1.38.0
Resolution: Fixed
> SqlToRel conversion of IN expressions may lead to incorrect simplifications
> ---
>
> Key: CALCITE-6435
> URL: https://issues.apache.org/jira/browse/CALCITE-6435
> Project: Calcite
> Issue Type: Bug
>Reporter: Zoltan Haindrich
>Assignee: Zoltan Haindrich
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> the query must have the following features:
> * not all columns are selected
> ** to enable {{RelFieldTrimmer}} to start a cycle
> * two equivalent eq filters
> ** one in {{IN}} form ({{ename in ( 'Sebastian' )}})
> ** a regular {{=}} ({{ename = 'Sebastian'}})
> * an unrelated filter like {{deptno < 100}}
> the optimizer should more-or-less start with the `RelFieldTrimmer`
> the issue happens like:
> * at parse time both literals are parsed as {{CHAR( n )}}
> * the number of values in the `IN` is below `inSubqueryThreshold` - so it
> gets converted to a set of `=` filters
> ** expression is converted to OR form
> ** during conversion
> [SqlToRelConverter#ensureSqlType|https://github.com/apache/calcite/blob/fb15511e76c660cbd440578421645ebe63941bf7/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L1777-L1779]
> is called
> *** which skips the conversion for *CHAR / VARCHAR*
> * the *=* filter goes thru the "regular" rex conversion - which involves
> calling *rexBuilder#ensureType*
> * the filter condition contains *ename = 'Sebastian'* twice; however the
> types differ
> * *RelFieldTrimmer* starts a change cycle ; which induces the simplification
> of the filter condition
> * *RexSimplify* is executed with predicate elimination disabled (this will
> be important)
> * simplification compares the two literals with
> [equals|https://github.com/apache/calcite/blob/fb15511e76c660cbd440578421645ebe63941bf7/core/src/main/java/org/apache/calcite/rex/RexSimplify.java#L1685]
> and returns `false`
> workarounds:
> * disable the conversion by setting *inSubqueryThreshold=1*
> * run a rule which executes `RexSimplify` with predicate elimination enabled
> earlier than the trimmer (ex: *ReduceExpressionsRule*)
> ** I think this bug remained hidden because this might happen easily
> testcase for `RelOptRulesTest`
> {code:java}
> @Test void testIncorrectInType() {
> final String sql = "select ename from emp "
> + " where ename in ( 'Sebastian' ) and ename = 'Sebastian' and
> deptno < 100";
> sql(sql)
> .withTrim(true)
> .withRule()
> .checkUnchanged();
> }
> {code}
> results in plan
> {code:java}
> LogicalProject(ENAME=[$0])
> LogicalValues(tuples=[[]])
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6435) SqlToRel conversion of IN expressions may lead to incorrect simplifications
[
https://issues.apache.org/jira/browse/CALCITE-6435?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17865022#comment-17865022
]
Zoltan Haindrich commented on CALCITE-6435:
---
merged into master with
[73846cceb1841a3c7f7ab5cbe3c40611db07c148](https://github.com/apache/calcite/commit/73846cceb1841a3c7f7ab5cbe3c40611db07c148)
Thank you Mihai and [~caicancai] for reviewing the changes!
> SqlToRel conversion of IN expressions may lead to incorrect simplifications
> ---
>
> Key: CALCITE-6435
> URL: https://issues.apache.org/jira/browse/CALCITE-6435
> Project: Calcite
> Issue Type: Bug
>Reporter: Zoltan Haindrich
>Assignee: Zoltan Haindrich
>Priority: Major
> Labels: pull-request-available
>
> the query must have the following features:
> * not all columns are selected
> ** to enable {{RelFieldTrimmer}} to start a cycle
> * two equivalent eq filters
> ** one in {{IN}} form ({{ename in ( 'Sebastian' )}})
> ** a regular {{=}} ({{ename = 'Sebastian'}})
> * an unrelated filter like {{deptno < 100}}
> the optimizer should more-or-less start with the `RelFieldTrimmer`
> the issue happens like:
> * at parse time both literals are parsed as {{CHAR( n )}}
> * the number of values in the `IN` is below `inSubqueryThreshold` - so it
> gets converted to a set of `=` filters
> ** expression is converted to OR form
> ** during conversion
> [SqlToRelConverter#ensureSqlType|https://github.com/apache/calcite/blob/fb15511e76c660cbd440578421645ebe63941bf7/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L1777-L1779]
> is called
> *** which skips the conversion for *CHAR / VARCHAR*
> * the *=* filter goes thru the "regular" rex conversion - which involves
> calling *rexBuilder#ensureType*
> * the filter condition contains *ename = 'Sebastian'* twice; however the
> types differ
> * *RelFieldTrimmer* starts a change cycle ; which induces the simplification
> of the filter condition
> * *RexSimplify* is executed with predicate elimination disabled (this will
> be important)
> * simplification compares the two literals with
> [equals|https://github.com/apache/calcite/blob/fb15511e76c660cbd440578421645ebe63941bf7/core/src/main/java/org/apache/calcite/rex/RexSimplify.java#L1685]
> and returns `false`
> workarounds:
> * disable the conversion by setting *inSubqueryThreshold=1*
> * run a rule which executes `RexSimplify` with predicate elimination enabled
> earlier than the trimmer (ex: *ReduceExpressionsRule*)
> ** I think this bug remained hidden because this might happen easily
> testcase for `RelOptRulesTest`
> {code:java}
> @Test void testIncorrectInType() {
> final String sql = "select ename from emp "
> + " where ename in ( 'Sebastian' ) and ename = 'Sebastian' and
> deptno < 100";
> sql(sql)
> .withTrim(true)
> .withRule()
> .checkUnchanged();
> }
> {code}
> results in plan
> {code:java}
> LogicalProject(ENAME=[$0])
> LogicalValues(tuples=[[]])
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6435) SqlToRel conversion of IN expressions may lead to incorrect simplifications
[
https://issues.apache.org/jira/browse/CALCITE-6435?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17860930#comment-17860930
]
Zoltan Haindrich commented on CALCITE-6435:
---
I don't see what kind of simpler fix you have in mind - because I think the
main problem is that `SqlToRel` have a duplicate route to produce comparisions;
the PR for this mainly solves just that for `x IN (literals)` by ensuring that
the same logic is followed for these generated comparision as for a simple `a =
1` present in the input query (and restricts RexSimplify from using equals for
literals if the type differs - as that have caused the correctness issue)
> SqlToRel conversion of IN expressions may lead to incorrect simplifications
> ---
>
> Key: CALCITE-6435
> URL: https://issues.apache.org/jira/browse/CALCITE-6435
> Project: Calcite
> Issue Type: Bug
>Reporter: Zoltan Haindrich
>Assignee: Zoltan Haindrich
>Priority: Major
>
> the query must have the following features:
> * not all columns are selected
> ** to enable {{RelFieldTrimmer}} to start a cycle
> * two equivalent eq filters
> ** one in {{IN}} form ({{ename in ( 'Sebastian' )}})
> ** a regular {{=}} ({{ename = 'Sebastian'}})
> * an unrelated filter like {{deptno < 100}}
> the optimizer should more-or-less start with the `RelFieldTrimmer`
> the issue happens like:
> * at parse time both literals are parsed as {{CHAR( n )}}
> * the number of values in the `IN` is below `inSubqueryThreshold` - so it
> gets converted to a set of `=` filters
> ** expression is converted to OR form
> ** during conversion
> [SqlToRelConverter#ensureSqlType|https://github.com/apache/calcite/blob/fb15511e76c660cbd440578421645ebe63941bf7/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L1777-L1779]
> is called
> *** which skips the conversion for *CHAR / VARCHAR*
> * the *=* filter goes thru the "regular" rex conversion - which involves
> calling *rexBuilder#ensureType*
> * the filter condition contains *ename = 'Sebastian'* twice; however the
> types differ
> * *RelFieldTrimmer* starts a change cycle ; which induces the simplification
> of the filter condition
> * *RexSimplify* is executed with predicate elimination disabled (this will
> be important)
> * simplification compares the two literals with
> [equals|https://github.com/apache/calcite/blob/fb15511e76c660cbd440578421645ebe63941bf7/core/src/main/java/org/apache/calcite/rex/RexSimplify.java#L1685]
> and returns `false`
> workarounds:
> * disable the conversion by setting *inSubqueryThreshold=1*
> * run a rule which executes `RexSimplify` with predicate elimination enabled
> earlier than the trimmer (ex: *ReduceExpressionsRule*)
> ** I think this bug remained hidden because this might happen easily
> testcase for `RelOptRulesTest`
> {code:java}
> @Test void testIncorrectInType() {
> final String sql = "select ename from emp "
> + " where ename in ( 'Sebastian' ) and ename = 'Sebastian' and
> deptno < 100";
> sql(sql)
> .withTrim(true)
> .withRule()
> .checkUnchanged();
> }
> {code}
> results in plan
> {code:java}
> LogicalProject(ENAME=[$0])
> LogicalValues(tuples=[[]])
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6435) SqlToRel conversion of IN expressions may lead to incorrect simplifications
[
https://issues.apache.org/jira/browse/CALCITE-6435?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17860561#comment-17860561
]
Zoltan Haindrich commented on CALCITE-6435:
---
after some back-and-forth with a failing test in `InnodbAdapterTest` I've
decided to reduce the level of customizations here - and let
`convertExpression` see the whole sql comparision operator; so that it could
make the *same* decisions as it would make in case the original query would
have contained this.
I've also added a safeguard check to `RexSimplify` - to ensure that it doesn't
do the same problematic simplification in the future.
> SqlToRel conversion of IN expressions may lead to incorrect simplifications
> ---
>
> Key: CALCITE-6435
> URL: https://issues.apache.org/jira/browse/CALCITE-6435
> Project: Calcite
> Issue Type: Bug
>Reporter: Zoltan Haindrich
>Assignee: Zoltan Haindrich
>Priority: Major
>
> the query must have the following features:
> * not all columns are selected
> ** to enable {{RelFieldTrimmer}} to start a cycle
> * two equivalent eq filters
> ** one in {{IN}} form ({{ename in ( 'Sebastian' )}})
> ** a regular {{=}} ({{ename = 'Sebastian'}})
> * an unrelated filter like {{deptno < 100}}
> the optimizer should more-or-less start with the `RelFieldTrimmer`
> the issue happens like:
> * at parse time both literals are parsed as {{CHAR( n )}}
> * the number of values in the `IN` is below `inSubqueryThreshold` - so it
> gets converted to a set of `=` filters
> ** expression is converted to OR form
> ** during conversion
> [SqlToRelConverter#ensureSqlType|https://github.com/apache/calcite/blob/fb15511e76c660cbd440578421645ebe63941bf7/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L1777-L1779]
> is called
> *** which skips the conversion for *CHAR / VARCHAR*
> * the *=* filter goes thru the "regular" rex conversion - which involves
> calling *rexBuilder#ensureType*
> * the filter condition contains *ename = 'Sebastian'* twice; however the
> types differ
> * *RelFieldTrimmer* starts a change cycle ; which induces the simplification
> of the filter condition
> * *RexSimplify* is executed with predicate elimination disabled (this will
> be important)
> * simplification compares the two literals with
> [equals|https://github.com/apache/calcite/blob/fb15511e76c660cbd440578421645ebe63941bf7/core/src/main/java/org/apache/calcite/rex/RexSimplify.java#L1685]
> and returns `false`
> workarounds:
> * disable the conversion by setting *inSubqueryThreshold=1*
> * run a rule which executes `RexSimplify` with predicate elimination enabled
> earlier than the trimmer (ex: *ReduceExpressionsRule*)
> ** I think this bug remained hidden because this might happen easily
> testcase for `RelOptRulesTest`
> {code:java}
> @Test void testIncorrectInType() {
> final String sql = "select ename from emp "
> + " where ename in ( 'Sebastian' ) and ename = 'Sebastian' and
> deptno < 100";
> sql(sql)
> .withTrim(true)
> .withRule()
> .checkUnchanged();
> }
> {code}
> results in plan
> {code:java}
> LogicalProject(ENAME=[$0])
> LogicalValues(tuples=[[]])
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6435) SqlToRel conversion of IN expressions may lead to incorrect simplifications
Zoltan Haindrich created CALCITE-6435:
-
Summary: SqlToRel conversion of IN expressions may lead to
incorrect simplifications
Key: CALCITE-6435
URL: https://issues.apache.org/jira/browse/CALCITE-6435
Project: Calcite
Issue Type: Bug
Reporter: Zoltan Haindrich
Assignee: Zoltan Haindrich
the query must have the following features:
* not all columns are selected
** to enable `RelFieldTrimmer` to start a cycle
* two equivalent eq filters
** one in `IN` form (`ename in ( 'Sebastian' )`)
** a regular `=` (`ename = 'Sebastian'`)
* an unrelated filter like `deptno < 100`
the optimizer should more-or-less start with the `RelFieldTrimmer`
the issue happens like:
* at parse time both literals are parsed as `CHAR(n)`
* the number of values in the `IN` is below `inSubqueryThreshold` - so it gets
converted to a set of `=` filters
** expression is converted to OR form
** during conversion
[SqlToRelConverter#ensureSqlType|#ensureSqlType]([https://github.com/apache/calcite/blob/fb15511e76c660cbd440578421645ebe63941bf7/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L1777-L1779])
is called
*** which skips the conversion for `CHAR` / `VARCHAR`
* the `=` filter goes thru the "regular" rex conversion - which involves
calling `rexBuilder#ensureType`
* the filter condition contains `ename = 'Sebastian'` twice; however the types
differ
* `RelFieldTrimmer` start a change cycle ; which induces the simplification of
the filter condition
* `RexSimplify` is executed with predicate elimination disabled (this will be
important)
* simplification compares the two literals with
[equals]([https://github.com/apache/calcite/blob/fb15511e76c660cbd440578421645ebe63941bf7/core/src/main/java/org/apache/calcite/rex/RexSimplify.java#L1685])
and returns `false`
workarounds:
* disable the conversion by setting `inSubqueryThreshold` to `1`
* run a rule which executes `RexSimplify` with predicate elimination enabled
earlier than the trimmer (ex: `ReduceExpressionsRule`)
** I think this bug remained hidden because this might happen easily
testcase for `RelOptRulesTest`
{code:java}
@Test void testIncorrectInType() {
final String sql = "select ename from emp "
+ " where ename in ( 'Sebastian' ) and ename = 'Sebastian' and deptno
< 100";
sql(sql)
.withTrim(true)
.withRule()
.checkUnchanged();
}
{code}
results in plan
{code:java}
LogicalProject(ENAME=[$0])
LogicalValues(tuples=[[]])
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6435) SqlToRel conversion of IN expressions may lead to incorrect simplifications
[
https://issues.apache.org/jira/browse/CALCITE-6435?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17854764#comment-17854764
]
Zoltan Haindrich commented on CALCITE-6435:
---
notes relating to fixing this:
*
[SqlToRelConverter#ensureSqlType|https://github.com/apache/calcite/blob/fb15511e76c660cbd440578421645ebe63941bf7/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L1777-L1779]
** should either not have that customization
** ...or if its needed - it should probably reside in *relBuilder.ensureType*
* *RexSimplify* comparing the two literals by using
[RexLiteral#equals|https://github.com/apache/calcite/blob/fb15511e76c660cbd440578421645ebe63941bf7/core/src/main/java/org/apache/calcite/rex/RexSimplify.java#L1685]
** I believe this is unfortunate - maybe this should be removed; and leave
these simplifications to executions which do have predicate elimination enabled
** for the record
[simplifyUsingPredicates|https://github.com/apache/calcite/blob/fb15511e76c660cbd440578421645ebe63941bf7/core/src/main/java/org/apache/calcite/rex/RexSimplify.java#L1853]
uses
[residue](https://github.com/apache/calcite/blob/fb15511e76c660cbd440578421645ebe63941bf7/core/src/main/java/org/apache/calcite/rex/RexSimplify.java#L1911)
which uses *RexLiteral#getValueAs*
> SqlToRel conversion of IN expressions may lead to incorrect simplifications
> ---
>
> Key: CALCITE-6435
> URL: https://issues.apache.org/jira/browse/CALCITE-6435
> Project: Calcite
> Issue Type: Bug
>Reporter: Zoltan Haindrich
>Assignee: Zoltan Haindrich
>Priority: Major
>
> the query must have the following features:
> * not all columns are selected
> ** to enable {{RelFieldTrimmer}} to start a cycle
> * two equivalent eq filters
> ** one in {{IN}} form ({{ename in ( 'Sebastian' )}})
> ** a regular {{=}} ({{ename = 'Sebastian'}})
> * an unrelated filter like {{deptno < 100}}
> the optimizer should more-or-less start with the `RelFieldTrimmer`
> the issue happens like:
> * at parse time both literals are parsed as {{CHAR( n )}}
> * the number of values in the `IN` is below `inSubqueryThreshold` - so it
> gets converted to a set of `=` filters
> ** expression is converted to OR form
> ** during conversion
> [SqlToRelConverter#ensureSqlType|https://github.com/apache/calcite/blob/fb15511e76c660cbd440578421645ebe63941bf7/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L1777-L1779]
> is called
> *** which skips the conversion for *CHAR / VARCHAR*
> * the *=* filter goes thru the "regular" rex conversion - which involves
> calling *rexBuilder#ensureType*
> * the filter condition contains *ename = 'Sebastian'* twice; however the
> types differ
> * *RelFieldTrimmer* starts a change cycle ; which induces the simplification
> of the filter condition
> * *RexSimplify* is executed with predicate elimination disabled (this will
> be important)
> * simplification compares the two literals with
> [equals|https://github.com/apache/calcite/blob/fb15511e76c660cbd440578421645ebe63941bf7/core/src/main/java/org/apache/calcite/rex/RexSimplify.java#L1685]
> and returns `false`
> workarounds:
> * disable the conversion by setting *inSubqueryThreshold=1*
> * run a rule which executes `RexSimplify` with predicate elimination enabled
> earlier than the trimmer (ex: *ReduceExpressionsRule*)
> ** I think this bug remained hidden because this might happen easily
> testcase for `RelOptRulesTest`
> {code:java}
> @Test void testIncorrectInType() {
> final String sql = "select ename from emp "
> + " where ename in ( 'Sebastian' ) and ename = 'Sebastian' and
> deptno < 100";
> sql(sql)
> .withTrim(true)
> .withRule()
> .checkUnchanged();
> }
> {code}
> results in plan
> {code:java}
> LogicalProject(ENAME=[$0])
> LogicalValues(tuples=[[]])
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6435) SqlToRel conversion of IN expressions may lead to incorrect simplifications
[
https://issues.apache.org/jira/browse/CALCITE-6435?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zoltan Haindrich updated CALCITE-6435:
--
Description:
the query must have the following features:
* not all columns are selected
** to enable {{RelFieldTrimmer}} to start a cycle
* two equivalent eq filters
** one in {{IN}} form ({{ename in ( 'Sebastian' )}})
** a regular {{=}} ({{ename = 'Sebastian'}})
* an unrelated filter like {{deptno < 100}}
the optimizer should more-or-less start with the `RelFieldTrimmer`
the issue happens like:
* at parse time both literals are parsed as {{CHAR( n )}}
* the number of values in the `IN` is below `inSubqueryThreshold` - so it gets
converted to a set of `=` filters
** expression is converted to OR form
** during conversion
[SqlToRelConverter#ensureSqlType|https://github.com/apache/calcite/blob/fb15511e76c660cbd440578421645ebe63941bf7/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L1777-L1779]
is called
*** which skips the conversion for *CHAR / VARCHAR*
* the *=* filter goes thru the "regular" rex conversion - which involves
calling *rexBuilder#ensureType*
* the filter condition contains *ename = 'Sebastian'* twice; however the types
differ
* *RelFieldTrimmer* starts a change cycle ; which induces the simplification
of the filter condition
* *RexSimplify* is executed with predicate elimination disabled (this will be
important)
* simplification compares the two literals with
[equals|https://github.com/apache/calcite/blob/fb15511e76c660cbd440578421645ebe63941bf7/core/src/main/java/org/apache/calcite/rex/RexSimplify.java#L1685]
and returns `false`
workarounds:
* disable the conversion by setting *inSubqueryThreshold=1*
* run a rule which executes `RexSimplify` with predicate elimination enabled
earlier than the trimmer (ex: *ReduceExpressionsRule*)
** I think this bug remained hidden because this might happen easily
testcase for `RelOptRulesTest`
{code:java}
@Test void testIncorrectInType() {
final String sql = "select ename from emp "
+ " where ename in ( 'Sebastian' ) and ename = 'Sebastian' and deptno
< 100";
sql(sql)
.withTrim(true)
.withRule()
.checkUnchanged();
}
{code}
results in plan
{code:java}
LogicalProject(ENAME=[$0])
LogicalValues(tuples=[[]])
{code}
was:
the query must have the following features:
* not all columns are selected
** to enable `RelFieldTrimmer` to start a cycle
* two equivalent eq filters
** one in `IN` form (`ename in ( 'Sebastian' )`)
** a regular `=` (`ename = 'Sebastian'`)
* an unrelated filter like `deptno < 100`
the optimizer should more-or-less start with the `RelFieldTrimmer`
the issue happens like:
* at parse time both literals are parsed as `CHAR(n)`
* the number of values in the `IN` is below `inSubqueryThreshold` - so it gets
converted to a set of `=` filters
** expression is converted to OR form
** during conversion
[SqlToRelConverter#ensureSqlType|#ensureSqlType]([https://github.com/apache/calcite/blob/fb15511e76c660cbd440578421645ebe63941bf7/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L1777-L1779])
is called
*** which skips the conversion for `CHAR` / `VARCHAR`
* the `=` filter goes thru the "regular" rex conversion - which involves
calling `rexBuilder#ensureType`
* the filter condition contains `ename = 'Sebastian'` twice; however the types
differ
* `RelFieldTrimmer` start a change cycle ; which induces the simplification of
the filter condition
* `RexSimplify` is executed with predicate elimination disabled (this will be
important)
* simplification compares the two literals with
[equals]([https://github.com/apache/calcite/blob/fb15511e76c660cbd440578421645ebe63941bf7/core/src/main/java/org/apache/calcite/rex/RexSimplify.java#L1685])
and returns `false`
workarounds:
* disable the conversion by setting `inSubqueryThreshold` to `1`
* run a rule which executes `RexSimplify` with predicate elimination enabled
earlier than the trimmer (ex: `ReduceExpressionsRule`)
** I think this bug remained hidden because this might happen easily
testcase for `RelOptRulesTest`
{code:java}
@Test void testIncorrectInType() {
final String sql = "select ename from emp "
+ " where ename in ( 'Sebastian' ) and ename = 'Sebastian' and deptno
< 100";
sql(sql)
.withTrim(true)
.withRule()
.checkUnchanged();
}
{code}
results in plan
{code:java}
LogicalProject(ENAME=[$0])
LogicalValues(tuples=[[]])
{code}
> SqlToRel conversion of IN expressions may lead to incorrect simplifications
> ---
>
> Key: CALCITE-6435
> URL: https://issues.apache.org/jira/browse/CALCITE-6435
> Project: Calcite
> Issue Type: Bug
>Reporter: Zoltan Haindrich
>
[jira] [Commented] (CALCITE-5907) Unexpected boolean expression simplification for And expression
[
https://issues.apache.org/jira/browse/CALCITE-5907?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17776061#comment-17776061
]
Zoltan Haindrich commented on CALCITE-5907:
---
I wonder how valid an `=` is if it has a `varchar` on one side and a `boolean`
on the other...
I had to disable paranoid mode to be able to run such cases...
If your compare operator supports things like that - I think instead of
allowing that it might be better to only remove such casts as a last step
before execution - and let calcite see the cast while its working with it.
I was looking into this a bit; some notes:
* we have the desired simplify via simplifyList ... simplifyComparision ;
which restricts this optimization to take it only into consideration when the
type of op0 is
[boolean|https://github.com/apache/calcite/blob/5151168e9a9035595939c2ae0f21a06984229209/core/src/main/java/org/apache/calcite/rex/RexSimplify.java#L540]
** this can be tricked into a similar bug by giving {{true = x}}
* in the meantime there is also a duplicate of a similar logic in
[here|https://github.com/apache/calcite/blob/5151168e9a9035595939c2ae0f21a06984229209/core/src/main/java/org/apache/calcite/rex/RexSimplify.java#L1641]
- which doesn't take the type of the column into account at all
I'm a bit skeptical about that we have anything to fix herewe could remove
the duplicate logic for sure; and tighten the check at the other - but I would
suspect that an issue like this could hit back from a different direction
later
some testcases I was taking a look at:
{code}
// works correctly ; no simplify
@Test void testSimplify1() {
simplify=simplify.withParanoid(false);
checkSimplifyUnchanged(
eq(vVarchar(), trueLiteral));
}
// triggers a similar issue
@Test void testSimplify3() {
simplify=simplify.withParanoid(false);
checkSimplifyUnchanged(
eq(trueLiteral,vVarchar() ));
}
// triggers the reported issue
@Test void testSimplify2() {
simplify=simplify.withParanoid(false);
checkSimplifyUnchanged(
and(
eq(vVarchar(1), vVarchar(2)),
eq(vVarchar(3), trueLiteral)));
}
@Test void testSimplifyValidUsage() {
checkSimplify(
and(
eq(vVarchar(1), vVarchar(2)),
eq(cast(vVarchar(3),tBool()), trueLiteral))
,"AND(=(?0.varchar1, ?0.varchar2), CAST(?0.varchar3):BOOLEAN NOT NULL)");
}
{code}
> Unexpected boolean expression simplification for And expression
> ---
>
> Key: CALCITE-5907
> URL: https://issues.apache.org/jira/browse/CALCITE-5907
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Yunhong Zheng
>Priority: Major
>
> As FLINK-27402 shown. If we have a table MyTable(a Int, b Boolean, c
> String). Calcite will not simplify this case ( c is Varchar type while
> SqlLiteral is boolean):
> {code:java}
> SELECT * FROM MyTable WHERE c = true;{code}
> As the logical plan is :
> {code:java}
> LogicalSink(table=[*anonymous_collect$1*], fields=[a, b, c])
> +- LogicalProject(inputs=[0..2])
> +- LogicalFilter(condition=[=($2, true)])
> +- LogicalTableScan(table=[[default_catalog, default_database,
> MyTable]]){code}
> However, Calcite will simplify this case while simplifyAnd :
> {code:java}
> SELECT * FROM MyTable WHERE b = true and c = true;{code}
> As the logical plan is shown below: 'b = true' and 'c = true' both were
> simplified to 'b' and 'c':
> {code:java}
> LogicalSink(table=[*anonymous_collect$1*], fields=[a, b, c])
> +- LogicalProject(inputs=[0..2])
> +- LogicalFilter(condition=[AND($1, $2)])
> +- LogicalTableScan(table=[[default_catalog, default_database,
> MyTable]]){code}
> This may cause error because of filter condition is a Varchar type literal.
>
> After reading Calcite's code, I found that. The logic of
> RexSimplify.implify() and RexSimplify.implifyAnd() is different, where the
> logic of RexSimplify.implifyAnd() is problematic:
> {code:java}
> // Simplify BOOLEAN expressions if possible
> while (term.getKind() == SqlKind.EQUALS) {
> RexCall call = (RexCall) term;
> if (call.getOperands().get(0).isAlwaysTrue()) {
> term = call.getOperands().get(1);
> terms.set(i, term);
> continue;
> } else if (call.getOperands().get(1).isAlwaysTrue()) {
> term = call.getOperands().get(0);
> terms.set(i, term);
> continue;
> }
> break;
> } {code}
> The above code cannot make such a simple judgment, as there may not be an
> implicit conversion to ensure that the types on both sides of the condition
> are consistent.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-4189) Simplify 'p OR (p IS NOT TRUE)' to 'TRUE'
[
https://issues.apache.org/jira/browse/CALCITE-4189?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zoltan Haindrich resolved CALCITE-4189.
---
Fix Version/s: 1.36.0
Assignee: hongyu guo
Resolution: Fixed
Fixed in
[ab3f5b0|https://github.com/apache/calcite/commit/ab3f5b0e800b2166f130fa75fc3a67f6961b5082].
Thank you [~hongyuguo] for improving on this!
> Simplify 'p OR (p IS NOT TRUE)' to 'TRUE'
> -
>
> Key: CALCITE-4189
> URL: https://issues.apache.org/jira/browse/CALCITE-4189
> Project: Calcite
> Issue Type: Improvement
>Reporter: Thomas Rebele
>Assignee: hongyu guo
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.36.0
>
>
> Statements of the form 'P OR P IS NOT TRUE' to 'TRUE' should always be true
> (please correct me if I am wrong):
> {code:java}
> > select x, X='A' as EQ,
> X='A' IS NOT TRUE as EQ_NOT_TRUE,
> (X='A') OR ((X='A') IS NOT TRUE) as EQ_OR_EQ_NOT_TRUE
> from (values 'A', 'B', null) as t(x);
> +---+---+-+---+
> | X | EQ | EQ_NOT_TRUE | EQ_OR_EQ_NOT_TRUE |
> +---+---+-+---+
> | A | true | false | true |
> | B | false | true| true |
> | | | true| true |
> +---+---+-+---+
> {code}
> Here a test case for the expected behavior:
> {code:java}
> @Test void testSimplifyPOrPNotTrue() {
> checkSimplify(
> and(
> vBool(),
> or(
> eq(vInt(), literal(1)),
> isNotTrue(eq(vInt(), literal(1)))
> )),
> "?0.bool0");
> }{code}
> There are some other, similar expressions, such as 'P IS NOT FALSE OR NOT P',
> which can be reduced to true. Maybe there's a way to handle all of them?
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6020) SqlToRelConverter should not replace windowed SUM with equivalent expression using SUM0
[
https://issues.apache.org/jira/browse/CALCITE-6020?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17767578#comment-17767578
]
Zoltan Haindrich commented on CALCITE-6020:
---
[~jhyde]: I've tried to look after the origins of this - but the history of
this leads back further than the "Eigenbase source files" commit.
Is there any reason to keep this behaviour?
> SqlToRelConverter should not replace windowed SUM with equivalent expression
> using SUM0
> ---
>
> Key: CALCITE-6020
> URL: https://issues.apache.org/jira/browse/CALCITE-6020
> Project: Calcite
> Issue Type: Improvement
>Reporter: Zoltan Haindrich
>Assignee: Zoltan Haindrich
>Priority: Major
>
> {{SqlToRelConverter}} replaces {{SUM}} with {{SUM0}} around
> [here|https://github.com/apache/calcite/blob/e1991e08a225ef08c2402ab35c310d88fff3c222/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L5885]
> This might have been needed at some point in the past - but I think it will
> be better to leave it as {{SUM}} - as in case there is no {{SUM0}} in the
> system that will be replaced with a {{COALESCE(SUM(...) , 0 )}} to provide it
> - as see
> [here|https://github.com/apache/calcite/blob/e1991e08a225ef08c2402ab35c310d88fff3c222/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java#L1288]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6020) SqlToRelConverter should not replace windowed SUM with equivalent expression using SUM0
Zoltan Haindrich created CALCITE-6020:
-
Summary: SqlToRelConverter should not replace windowed SUM with
equivalent expression using SUM0
Key: CALCITE-6020
URL: https://issues.apache.org/jira/browse/CALCITE-6020
Project: Calcite
Issue Type: Improvement
Reporter: Zoltan Haindrich
Assignee: Zoltan Haindrich
{{SqlToRelConverter}} replaces {{SUM}} with {{SUM0}} around
[here|https://github.com/apache/calcite/blob/e1991e08a225ef08c2402ab35c310d88fff3c222/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L5885]
This might have been needed at some point in the past - but I think it will be
better to leave it as {{SUM}} - as in case there is no {{SUM0}} in the system
that will be replaced with a {{COALESCE(SUM(...) , 0 )}} to provide it - as see
[here|https://github.com/apache/calcite/blob/e1991e08a225ef08c2402ab35c310d88fff3c222/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java#L1288]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5953) AggregateCaseToFilterRule may make inaccurate SUM transformation
[
https://issues.apache.org/jira/browse/CALCITE-5953?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zoltan Haindrich updated CALCITE-5953:
--
Description:
consider: {{sum(case when x = 1 then 2 else 0 end) as b}}
notice that this expression may only be null if there are no rows in the table
{{AggregateCaseToFilterRule}} rewrites the above expression to {{sum(2) filter
(where x=1)}} which broadens when it could be `null` to when there are no
matches to the filter
* *A* is *0* correctly in this case; but I think it will be still *0* in case
there are 0 input rows
* The result for *B* supposed to be *0* but since there are no matches by the
filter it becomes *null*
* *C* is not touched
{code}
# Convert CASE to FILTER without matches
select sum(case when x = 1 then 1 else 0 end) as a,
sum(case when x = 1 then 2 else 0 end) as b,
sum(case when x = 1 then 3 else -1 end) as c
from (values 0, null, 0, 2) as t(x);
+---+---++
| A | B | C |
+---+---++
| 0 | | -4 | # incorrect result for B
| 0 | 0 | -4 | # correct
+---+---++
(1 row)
!ok
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER], A=[$t3],
B=[$t1], C=[$t2])
EnumerableAggregate(group=[{}], A=[COUNT() FILTER $1], B=[SUM($2) FILTER $3],
C=[SUM($0)])
EnumerableCalc(expr#0=[{inputs}], expr#1=[1], expr#2=[=($t0, $t1)],
expr#3=[3], expr#4=[-1], expr#5=[CASE($t2, $t3, $t4)], expr#6=[IS TRUE($t2)],
expr#7=[2], $f2=[$t5], $f3=[$t6], $f4=[$t7], $f5=[$t6])
EnumerableValues(tuples=[[{ 0 }, { null }, { 0 }, { 2 }]])
!plan
# Convert CASE to FILTER with no input rows
select sum(case when x = 1 then 1 else 0 end) as a,
sum(case when x = 1 then 2 else 0 end) as b,
sum(case when x = 1 then 3 else -1 end) as c
from (values 0, null, 0, 2) as t(x) where x*x=1;
+---+---+---+
| A | B | C |
+---+---+---+
| 0 | | | # incorrect results - for 0 input rows sum supposed to be null
| | | | # correct result
+---+---+---+
(1 row)
!ok
{code}
was:
consider: {{sum(case when x = 1 then 2 else 0 end) as b}}
notice that this expression may only be null if there are no rows in the table
{{AggregateCaseToFilterRule}} rewrites the above expression to {{sum(2) filter
(where x=1)}} which broadens when it could be `null` to when there are no
matches to the filter
* *A* is *0* correctly in this case; but I think it will be still *0* in case
there are 0 input rows
* The result for *B* supposed to be *0* but since there are no matches by the
filter it becomes *null*
* *C* is not touched
{code}
# Convert CASE to FILTER without matches
select sum(case when x = 1 then 1 else 0 end) as a,
sum(case when x = 1 then 2 else 0 end) as b,
sum(case when x = 1 then 3 else -1 end) as c
from (values 0, null, 0, 2) as t(x);
+---+---++
| A | B | C |
+---+---++
| 0 | 0 | -4 |
+---+---++
(1 row)
!ok
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER], A=[$t3],
B=[$t1], C=[$t2])
EnumerableAggregate(group=[{}], A=[COUNT() FILTER $1], B=[SUM($2) FILTER $3],
C=[SUM($0)])
EnumerableCalc(expr#0=[{inputs}], expr#1=[1], expr#2=[=($t0, $t1)],
expr#3=[3], expr#4=[-1], expr#5=[CASE($t2, $t3, $t4)], expr#6=[IS TRUE($t2)],
expr#7=[2], $f2=[$t5], $f3=[$t6], $f4=[$t7], $f5=[$t6])
EnumerableValues(tuples=[[{ 0 }, { null }, { 0 }, { 2 }]])
!plan
{code}
> AggregateCaseToFilterRule may make inaccurate SUM transformation
>
>
> Key: CALCITE-5953
> URL: https://issues.apache.org/jira/browse/CALCITE-5953
> Project: Calcite
> Issue Type: Bug
>Reporter: Zoltan Haindrich
>Assignee: Zoltan Haindrich
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.36.0
>
>
> consider: {{sum(case when x = 1 then 2 else 0 end) as b}}
> notice that this expression may only be null if there are no rows in the table
> {{AggregateCaseToFilterRule}} rewrites the above expression to {{sum(2)
> filter (where x=1)}} which broadens when it could be `null` to when there are
> no matches to the filter
> * *A* is *0* correctly in this case; but I think it will be still *0* in case
> there are 0 input rows
> * The result for *B* supposed to be *0* but since there are no matches by the
> filter it becomes *null*
> * *C* is not touched
> {code}
> # Convert CASE to FILTER without matches
> select sum(case when x = 1 then 1 else 0 end) as a,
> sum(case when x = 1 then 2 else 0 end) as b,
> sum(case when x = 1 then 3 else -1 end) as c
> from (values 0, null, 0, 2) as t(x);
> +---+---++
> | A | B | C |
> +---+---++
> | 0 | | -4 | # incorrect result for B
> | 0 | 0 | -4 | # correct
> +---+---++
> (1 row)
> !ok
> EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER], A=[$t3],
> B=[$t1], C=[$t2])
>
[jira] [Updated] (CALCITE-5953) AggregateCaseToFilterRule may make inaccurate SUM transformation
[
https://issues.apache.org/jira/browse/CALCITE-5953?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zoltan Haindrich updated CALCITE-5953:
--
Description:
consider: {{sum(case when x = 1 then 2 else 0 end) as b}}
notice that this expression may only be null if there are no rows in the table
{{AggregateCaseToFilterRule}} rewrites the above expression to {{sum(2) filter
(where x=1)}} which broadens when it could be `null` to when there are no
matches to the filter
* *A* is *0* correctly in this case; but I think it will be still *0* in case
there are 0 input rows
* The result for *B* supposed to be *0* but since there are no matches by the
filter it becomes *null*
* *C* is not touched
{code}
# Convert CASE to FILTER without matches
select sum(case when x = 1 then 1 else 0 end) as a,
sum(case when x = 1 then 2 else 0 end) as b,
sum(case when x = 1 then 3 else -1 end) as c
from (values 0, null, 0, 2) as t(x);
+---+---++
| A | B | C |
+---+---++
| 0 | 0 | -4 |
+---+---++
(1 row)
!ok
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER], A=[$t3],
B=[$t1], C=[$t2])
EnumerableAggregate(group=[{}], A=[COUNT() FILTER $1], B=[SUM($2) FILTER $3],
C=[SUM($0)])
EnumerableCalc(expr#0=[{inputs}], expr#1=[1], expr#2=[=($t0, $t1)],
expr#3=[3], expr#4=[-1], expr#5=[CASE($t2, $t3, $t4)], expr#6=[IS TRUE($t2)],
expr#7=[2], $f2=[$t5], $f3=[$t6], $f4=[$t7], $f5=[$t6])
EnumerableValues(tuples=[[{ 0 }, { null }, { 0 }, { 2 }]])
!plan
{code}
was:
consider: {{sum(case when x = 1 then 2 else 0 end) as b}}
notice that this expression may only be null if there are no rows in the table
{{AggregateCaseToFilterRule}} rewrites the above expression to {{sum(1) filter
(where x=2)}} which broadens when it could be `null` to when there are no
matches to the filter
* *A* is *0* correctly in this case; but I think it will be still *0* in case
there are 0 input rows
* The result for *B* supposed to be *0* but since there are no matches by the
filter it becomes *null*
* *C* is not touched
{code}
# Convert CASE to FILTER without matches
select sum(case when x = 1 then 1 else 0 end) as a,
sum(case when x = 1 then 2 else 0 end) as b,
sum(case when x = 1 then 3 else -1 end) as c
from (values 0, null, 0, 2) as t(x);
+---+---++
| A | B | C |
+---+---++
| 0 | 0 | -4 |
+---+---++
(1 row)
!ok
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER], A=[$t3],
B=[$t1], C=[$t2])
EnumerableAggregate(group=[{}], A=[COUNT() FILTER $1], B=[SUM($2) FILTER $3],
C=[SUM($0)])
EnumerableCalc(expr#0=[{inputs}], expr#1=[1], expr#2=[=($t0, $t1)],
expr#3=[3], expr#4=[-1], expr#5=[CASE($t2, $t3, $t4)], expr#6=[IS TRUE($t2)],
expr#7=[2], $f2=[$t5], $f3=[$t6], $f4=[$t7], $f5=[$t6])
EnumerableValues(tuples=[[{ 0 }, { null }, { 0 }, { 2 }]])
!plan
{code}
> AggregateCaseToFilterRule may make inaccurate SUM transformation
>
>
> Key: CALCITE-5953
> URL: https://issues.apache.org/jira/browse/CALCITE-5953
> Project: Calcite
> Issue Type: Bug
>Reporter: Zoltan Haindrich
>Assignee: Zoltan Haindrich
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.36.0
>
>
> consider: {{sum(case when x = 1 then 2 else 0 end) as b}}
> notice that this expression may only be null if there are no rows in the table
> {{AggregateCaseToFilterRule}} rewrites the above expression to {{sum(2)
> filter (where x=1)}} which broadens when it could be `null` to when there are
> no matches to the filter
> * *A* is *0* correctly in this case; but I think it will be still *0* in case
> there are 0 input rows
> * The result for *B* supposed to be *0* but since there are no matches by the
> filter it becomes *null*
> * *C* is not touched
> {code}
> # Convert CASE to FILTER without matches
> select sum(case when x = 1 then 1 else 0 end) as a,
> sum(case when x = 1 then 2 else 0 end) as b,
> sum(case when x = 1 then 3 else -1 end) as c
> from (values 0, null, 0, 2) as t(x);
> +---+---++
> | A | B | C |
> +---+---++
> | 0 | 0 | -4 |
> +---+---++
> (1 row)
> !ok
> EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER], A=[$t3],
> B=[$t1], C=[$t2])
> EnumerableAggregate(group=[{}], A=[COUNT() FILTER $1], B=[SUM($2) FILTER
> $3], C=[SUM($0)])
> EnumerableCalc(expr#0=[{inputs}], expr#1=[1], expr#2=[=($t0, $t1)],
> expr#3=[3], expr#4=[-1], expr#5=[CASE($t2, $t3, $t4)], expr#6=[IS TRUE($t2)],
> expr#7=[2], $f2=[$t5], $f3=[$t6], $f4=[$t7], $f5=[$t6])
> EnumerableValues(tuples=[[{ 0 }, { null }, { 0 }, { 2 }]])
> !plan
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-5953) AggregateCaseToFilterRule may make inaccurate SUM transformation
[
https://issues.apache.org/jira/browse/CALCITE-5953?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zoltan Haindrich resolved CALCITE-5953.
---
Fix Version/s: 1.36.0
Resolution: Fixed
Fixed in
[05787a67|https://github.com/apache/calcite/commit/05787a6793d5393ee05c99182e05823d12edab12]
> AggregateCaseToFilterRule may make inaccurate SUM transformation
>
>
> Key: CALCITE-5953
> URL: https://issues.apache.org/jira/browse/CALCITE-5953
> Project: Calcite
> Issue Type: Bug
>Reporter: Zoltan Haindrich
>Assignee: Zoltan Haindrich
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.36.0
>
>
> consider: {{sum(case when x = 1 then 2 else 0 end) as b}}
> notice that this expression may only be null if there are no rows in the table
> {{AggregateCaseToFilterRule}} rewrites the above expression to {{sum(1)
> filter (where x=2)}} which broadens when it could be `null` to when there are
> no matches to the filter
> * *A* is *0* correctly in this case; but I think it will be still *0* in case
> there are 0 input rows
> * The result for *B* supposed to be *0* but since there are no matches by the
> filter it becomes *null*
> * *C* is not touched
> {code}
> # Convert CASE to FILTER without matches
> select sum(case when x = 1 then 1 else 0 end) as a,
> sum(case when x = 1 then 2 else 0 end) as b,
> sum(case when x = 1 then 3 else -1 end) as c
> from (values 0, null, 0, 2) as t(x);
> +---+---++
> | A | B | C |
> +---+---++
> | 0 | 0 | -4 |
> +---+---++
> (1 row)
> !ok
> EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER], A=[$t3],
> B=[$t1], C=[$t2])
> EnumerableAggregate(group=[{}], A=[COUNT() FILTER $1], B=[SUM($2) FILTER
> $3], C=[SUM($0)])
> EnumerableCalc(expr#0=[{inputs}], expr#1=[1], expr#2=[=($t0, $t1)],
> expr#3=[3], expr#4=[-1], expr#5=[CASE($t2, $t3, $t4)], expr#6=[IS TRUE($t2)],
> expr#7=[2], $f2=[$t5], $f3=[$t6], $f4=[$t7], $f5=[$t6])
> EnumerableValues(tuples=[[{ 0 }, { null }, { 0 }, { 2 }]])
> !plan
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5963) Simplify 'x OR NOT(x)' to TRUE
[
https://issues.apache.org/jira/browse/CALCITE-5963?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17759620#comment-17759620
]
Zoltan Haindrich commented on CALCITE-5963:
---
I think instead of restricting this by unknownAs mode; it might worth
considering to simply rewritte it to:
{code}
(x is not null) or null
{code}
and let the existing logic handle the rest
> Simplify 'x OR NOT(x)' to TRUE
> --
>
> Key: CALCITE-5963
> URL: https://issues.apache.org/jira/browse/CALCITE-5963
> Project: Calcite
> Issue Type: New Feature
>Reporter: hongyu guo
>Assignee: hongyu guo
>Priority: Major
>
> When unknownAs is TRUE, or type of x is not nullable, Calcite can simpify:
> x OR NOT( x ) ==> TRUE
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5953) AggregateCaseToFilterRule may make inaccurate SUM transformation
[
https://issues.apache.org/jira/browse/CALCITE-5953?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17758383#comment-17758383
]
Zoltan Haindrich commented on CALCITE-5953:
---
I would also find it more natural if SUM would follow the mathematical
definition (so that an empty sum is 0) I believe when `SUM` was placed next to
the other aggregates like `MIN` they have made some generalizations.
I was thinking about possible alternatives to fixing this:
* compute it from multiple aggs
{code:sql}
consider: select SUM(CASE WHEN x=1 THEN 2 ELSE 0 END from t;
select CASE WHEN s>0 THEN 2*s ELSE NULLIF(cnt,0) END
from (select count(1) filter (where x=1) as s,count(1) as cnt from t) tt;
or with ANY_VALUE:
select CASE WHEN s>0 THEN 2*s ELSE a END
from (select count(1) filter (where x=1) as s,any_value(0) as a from t) tt;
{code}
** so we are exchanging 1 agg with a complex CASE into 2 simpler aggs + a CASE
on top of it
** pro of this approach:
*** it could be extended very easily to also cover the case when ELSE is not 0
*** possibly handle more branches for the same variable - at the cost of 1 more
aggregation
*** it could possibly extended to rewrite multi branch cases - but every branch
will cost 1 more aggregation
* consider only `SUM0` to optimize
** ProjectAggregateMergeRule could prepare SUM0 for this
** this fixes all above issues
*** but also disables it from the more natural usages
I'm still thinking about alternatives - let me know what you think about the
above ; or if you have any other ideas!
> AggregateCaseToFilterRule may make inaccurate SUM transformation
>
>
> Key: CALCITE-5953
> URL: https://issues.apache.org/jira/browse/CALCITE-5953
> Project: Calcite
> Issue Type: Bug
>Reporter: Zoltan Haindrich
>Assignee: Zoltan Haindrich
>Priority: Major
>
> consider: {{sum(case when x = 1 then 2 else 0 end) as b}}
> notice that this expression may only be null if there are no rows in the table
> {{AggregateCaseToFilterRule}} rewrites the above expression to {{sum(1)
> filter (where x=2)}} which broadens when it could be `null` to when there are
> no matches to the filter
> * *A* is *0* correctly in this case; but I think it will be still *0* in case
> there are 0 input rows
> * The result for *B* supposed to be *0* but since there are no matches by the
> filter it becomes *null*
> * *C* is not touched
> {code}
> # Convert CASE to FILTER without matches
> select sum(case when x = 1 then 1 else 0 end) as a,
> sum(case when x = 1 then 2 else 0 end) as b,
> sum(case when x = 1 then 3 else -1 end) as c
> from (values 0, null, 0, 2) as t(x);
> +---+---++
> | A | B | C |
> +---+---++
> | 0 | 0 | -4 |
> +---+---++
> (1 row)
> !ok
> EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER], A=[$t3],
> B=[$t1], C=[$t2])
> EnumerableAggregate(group=[{}], A=[COUNT() FILTER $1], B=[SUM($2) FILTER
> $3], C=[SUM($0)])
> EnumerableCalc(expr#0=[{inputs}], expr#1=[1], expr#2=[=($t0, $t1)],
> expr#3=[3], expr#4=[-1], expr#5=[CASE($t2, $t3, $t4)], expr#6=[IS TRUE($t2)],
> expr#7=[2], $f2=[$t5], $f3=[$t6], $f4=[$t7], $f5=[$t6])
> EnumerableValues(tuples=[[{ 0 }, { null }, { 0 }, { 2 }]])
> !plan
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5953) AggregateCaseToFilterRule may make inaccurate SUM transformation
[
https://issues.apache.org/jira/browse/CALCITE-5953?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zoltan Haindrich updated CALCITE-5953:
--
Description:
consider: {{sum(case when x = 1 then 2 else 0 end) as b}}
notice that this expression may only be null if there are no rows in the table
{{AggregateCaseToFilterRule}} rewrites the above expression to {{sum(1) filter
(where x=2)}} which broadens when it could be `null` to when there are no
matches to the filter
* *A* is *0* correctly in this case; but I think it will be still *0* in case
there are 0 input rows
* The result for *B* supposed to be *0* but since there are no matches by the
filter it becomes *null*
* *C* is not touched
{code}
# Convert CASE to FILTER without matches
select sum(case when x = 1 then 1 else 0 end) as a,
sum(case when x = 1 then 2 else 0 end) as b,
sum(case when x = 1 then 3 else -1 end) as c
from (values 0, null, 0, 2) as t(x);
+---+---++
| A | B | C |
+---+---++
| 0 | 0 | -4 |
+---+---++
(1 row)
!ok
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER], A=[$t3],
B=[$t1], C=[$t2])
EnumerableAggregate(group=[{}], A=[COUNT() FILTER $1], B=[SUM($2) FILTER $3],
C=[SUM($0)])
EnumerableCalc(expr#0=[{inputs}], expr#1=[1], expr#2=[=($t0, $t1)],
expr#3=[3], expr#4=[-1], expr#5=[CASE($t2, $t3, $t4)], expr#6=[IS TRUE($t2)],
expr#7=[2], $f2=[$t5], $f3=[$t6], $f4=[$t7], $f5=[$t6])
EnumerableValues(tuples=[[{ 0 }, { null }, { 0 }, { 2 }]])
!plan
{code}
was:
consider: `sum(case when x = 1 then 2 else 0 end) as b`
notice that this expression may only be null if there are no rows in the table
`AggregateCaseToFilterRule` rewrites the above expression to `sum(1) filter
(where x=2)` which broadens when it could be `null` to when there are no
matches to the filter
* `A` is `0` correctly in this case; but I think it will be still `0` in case
there are 0 input rows
* The result for `B` supposed to be `0` but since there are no matches by the
filter it becomes `null`
* `C` is not touched
```
# Convert CASE to FILTER without matches
select sum(case when x = 1 then 1 else 0 end) as a,
sum(case when x = 1 then 2 else 0 end) as b,
sum(case when x = 1 then 3 else -1 end) as c
from (values 0, null, 0, 2) as t(x);
+---+---++
| A | B | C |
+---+---++
| 0 | 0 | -4 |
+---+---++
(1 row)
!ok
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER], A=[$t3],
B=[$t1], C=[$t2])
EnumerableAggregate(group=[{}], A=[COUNT() FILTER $1], B=[SUM($2) FILTER $3],
C=[SUM($0)])
EnumerableCalc(expr#0=[{inputs}], expr#1=[1], expr#2=[=($t0, $t1)],
expr#3=[3], expr#4=[-1], expr#5=[CASE($t2, $t3, $t4)], expr#6=[IS TRUE($t2)],
expr#7=[2], $f2=[$t5], $f3=[$t6], $f4=[$t7], $f5=[$t6])
EnumerableValues(tuples=[[{ 0 }, { null }, { 0 }, { 2 }]])
!plan
```
> AggregateCaseToFilterRule may make inaccurate SUM transformation
>
>
> Key: CALCITE-5953
> URL: https://issues.apache.org/jira/browse/CALCITE-5953
> Project: Calcite
> Issue Type: Bug
>Reporter: Zoltan Haindrich
>Assignee: Zoltan Haindrich
>Priority: Major
>
> consider: {{sum(case when x = 1 then 2 else 0 end) as b}}
> notice that this expression may only be null if there are no rows in the table
> {{AggregateCaseToFilterRule}} rewrites the above expression to {{sum(1)
> filter (where x=2)}} which broadens when it could be `null` to when there are
> no matches to the filter
> * *A* is *0* correctly in this case; but I think it will be still *0* in case
> there are 0 input rows
> * The result for *B* supposed to be *0* but since there are no matches by the
> filter it becomes *null*
> * *C* is not touched
> {code}
> # Convert CASE to FILTER without matches
> select sum(case when x = 1 then 1 else 0 end) as a,
> sum(case when x = 1 then 2 else 0 end) as b,
> sum(case when x = 1 then 3 else -1 end) as c
> from (values 0, null, 0, 2) as t(x);
> +---+---++
> | A | B | C |
> +---+---++
> | 0 | 0 | -4 |
> +---+---++
> (1 row)
> !ok
> EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER], A=[$t3],
> B=[$t1], C=[$t2])
> EnumerableAggregate(group=[{}], A=[COUNT() FILTER $1], B=[SUM($2) FILTER
> $3], C=[SUM($0)])
> EnumerableCalc(expr#0=[{inputs}], expr#1=[1], expr#2=[=($t0, $t1)],
> expr#3=[3], expr#4=[-1], expr#5=[CASE($t2, $t3, $t4)], expr#6=[IS TRUE($t2)],
> expr#7=[2], $f2=[$t5], $f3=[$t6], $f4=[$t7], $f5=[$t6])
> EnumerableValues(tuples=[[{ 0 }, { null }, { 0 }, { 2 }]])
> !plan
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5953) AggregateCaseToFilterRule may make inaccurate SUM transformation
Zoltan Haindrich created CALCITE-5953:
-
Summary: AggregateCaseToFilterRule may make inaccurate SUM
transformation
Key: CALCITE-5953
URL: https://issues.apache.org/jira/browse/CALCITE-5953
Project: Calcite
Issue Type: Bug
Reporter: Zoltan Haindrich
Assignee: Zoltan Haindrich
consider: `sum(case when x = 1 then 2 else 0 end) as b`
notice that this expression may only be null if there are no rows in the table
`AggregateCaseToFilterRule` rewrites the above expression to `sum(1) filter
(where x=2)` which broadens when it could be `null` to when there are no
matches to the filter
* `A` is `0` correctly in this case; but I think it will be still `0` in case
there are 0 input rows
* The result for `B` supposed to be `0` but since there are no matches by the
filter it becomes `null`
* `C` is not touched
```
# Convert CASE to FILTER without matches
select sum(case when x = 1 then 1 else 0 end) as a,
sum(case when x = 1 then 2 else 0 end) as b,
sum(case when x = 1 then 3 else -1 end) as c
from (values 0, null, 0, 2) as t(x);
+---+---++
| A | B | C |
+---+---++
| 0 | 0 | -4 |
+---+---++
(1 row)
!ok
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER], A=[$t3],
B=[$t1], C=[$t2])
EnumerableAggregate(group=[{}], A=[COUNT() FILTER $1], B=[SUM($2) FILTER $3],
C=[SUM($0)])
EnumerableCalc(expr#0=[{inputs}], expr#1=[1], expr#2=[=($t0, $t1)],
expr#3=[3], expr#4=[-1], expr#5=[CASE($t2, $t3, $t4)], expr#6=[IS TRUE($t2)],
expr#7=[2], $f2=[$t5], $f3=[$t6], $f4=[$t7], $f5=[$t6])
EnumerableValues(tuples=[[{ 0 }, { null }, { 0 }, { 2 }]])
!plan
```
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-5907) Unexpected boolean expression simplification for And expression
[
https://issues.apache.org/jira/browse/CALCITE-5907?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17752094#comment-17752094
]
Zoltan Haindrich edited comment on CALCITE-5907 at 8/8/23 3:17 PM:
---
I believe both operands should be of the same type for an `EQUALS` ; I wonder
how it worked in your case ; I believe `RexSimplify` will not remove such casts
unless it can prove they are not needed.
Can you provide a test case on the `RexSimplify` api level?
You could try adding something like these to `RexProgramTest`:
{code}
@Test void testSimplify1() {
checkSimplify(
eq(vVarchar(), trueLiteral),
"something");
}
@Test void testSimplify2() {
checkSimplify(
eq(literal(3), trueLiteral),
"something");
}
{code}
was (Author: kgyrtkirk):
I believe both operands should be of the same type for an `EQUALS` ; I wonder
how it worked in your case ; I believe `RexSimplify` will not remove such casts
unless it can prove they are not needed.
Can you provide a test case on the `RexSimplify` api level?
You could try adding something like these to `RexProgramTest`:
```
@Test void testSimplify1() {
checkSimplify(
eq(vVarchar(), trueLiteral),
"something");
}
@Test void testSimplify2() {
checkSimplify(
eq(literal(3), trueLiteral),
"something");
}
```
> Unexpected boolean expression simplification for And expression
> ---
>
> Key: CALCITE-5907
> URL: https://issues.apache.org/jira/browse/CALCITE-5907
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Yunhong Zheng
>Priority: Major
> Fix For: 1.36.0
>
>
> As FLINK-27402 shown. If we have a table MyTable(a Int, b Boolean, c
> String). Calcite will not simplify this case ( c is Varchar type while
> SqlLiteral is boolean):
> {code:java}
> SELECT * FROM MyTable WHERE c = true;{code}
> As the logical plan is :
> {code:java}
> LogicalSink(table=[*anonymous_collect$1*], fields=[a, b, c])
> +- LogicalProject(inputs=[0..2])
> +- LogicalFilter(condition=[=($2, true)])
> +- LogicalTableScan(table=[[default_catalog, default_database,
> MyTable]]){code}
> However, Calcite will simplify this case while simplifyAnd :
> {code:java}
> SELECT * FROM MyTable WHERE b = true and c = true;{code}
> As the logical plan is shown below: 'b = true' and 'c = true' both were
> simplified to 'b' and 'c':
> {code:java}
> LogicalSink(table=[*anonymous_collect$1*], fields=[a, b, c])
> +- LogicalProject(inputs=[0..2])
> +- LogicalFilter(condition=[AND($1, $2)])
> +- LogicalTableScan(table=[[default_catalog, default_database,
> MyTable]]){code}
> This may cause error because of filter condition is a Varchar type literal.
>
> After reading Calcite's code, I found that. The logic of
> RexSimplify.implify() and RexSimplify.implifyAnd() is different, where the
> logic of RexSimplify.implifyAnd() is problematic:
> {code:java}
> // Simplify BOOLEAN expressions if possible
> while (term.getKind() == SqlKind.EQUALS) {
> RexCall call = (RexCall) term;
> if (call.getOperands().get(0).isAlwaysTrue()) {
> term = call.getOperands().get(1);
> terms.set(i, term);
> continue;
> } else if (call.getOperands().get(1).isAlwaysTrue()) {
> term = call.getOperands().get(0);
> terms.set(i, term);
> continue;
> }
> break;
> } {code}
> The above code cannot make such a simple judgment, as there may not be an
> implicit conversion to ensure that the types on both sides of the condition
> are consistent.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5907) Unexpected boolean expression simplification for And expression
[
https://issues.apache.org/jira/browse/CALCITE-5907?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17752094#comment-17752094
]
Zoltan Haindrich commented on CALCITE-5907:
---
I believe both operands should be of the same type for an `EQUALS` ; I wonder
how it worked in your case ; I believe `RexSimplify` will not remove such casts
unless it can prove they are not needed.
Can you provide a test case on the `RexSimplify` api level?
You could try adding something like these to `RexProgramTest`:
```
@Test void testSimplify1() {
checkSimplify(
eq(vVarchar(), trueLiteral),
"something");
}
@Test void testSimplify2() {
checkSimplify(
eq(literal(3), trueLiteral),
"something");
}
```
> Unexpected boolean expression simplification for And expression
> ---
>
> Key: CALCITE-5907
> URL: https://issues.apache.org/jira/browse/CALCITE-5907
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Yunhong Zheng
>Priority: Major
> Fix For: 1.36.0
>
>
> As FLINK-27402 shown. If we have a table MyTable(a Int, b Boolean, c
> String). Calcite will not simplify this case ( c is Varchar type while
> SqlLiteral is boolean):
> {code:java}
> SELECT * FROM MyTable WHERE c = true;{code}
> As the logical plan is :
> {code:java}
> LogicalSink(table=[*anonymous_collect$1*], fields=[a, b, c])
> +- LogicalProject(inputs=[0..2])
> +- LogicalFilter(condition=[=($2, true)])
> +- LogicalTableScan(table=[[default_catalog, default_database,
> MyTable]]){code}
> However, Calcite will simplify this case while simplifyAnd :
> {code:java}
> SELECT * FROM MyTable WHERE b = true and c = true;{code}
> As the logical plan is shown below: 'b = true' and 'c = true' both were
> simplified to 'b' and 'c':
> {code:java}
> LogicalSink(table=[*anonymous_collect$1*], fields=[a, b, c])
> +- LogicalProject(inputs=[0..2])
> +- LogicalFilter(condition=[AND($1, $2)])
> +- LogicalTableScan(table=[[default_catalog, default_database,
> MyTable]]){code}
> This may cause error because of filter condition is a Varchar type literal.
>
> After reading Calcite's code, I found that. The logic of
> RexSimplify.implify() and RexSimplify.implifyAnd() is different, where the
> logic of RexSimplify.implifyAnd() is problematic:
> {code:java}
> // Simplify BOOLEAN expressions if possible
> while (term.getKind() == SqlKind.EQUALS) {
> RexCall call = (RexCall) term;
> if (call.getOperands().get(0).isAlwaysTrue()) {
> term = call.getOperands().get(1);
> terms.set(i, term);
> continue;
> } else if (call.getOperands().get(1).isAlwaysTrue()) {
> term = call.getOperands().get(0);
> terms.set(i, term);
> continue;
> }
> break;
> } {code}
> The above code cannot make such a simple judgment, as there may not be an
> implicit conversion to ensure that the types on both sides of the condition
> are consistent.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-4910) Enhance simplify to reduce ((a or d) and (a or c) and a and b) to (a and b)
[
https://issues.apache.org/jira/browse/CALCITE-4910?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17451070#comment-17451070
]
Zoltan Haindrich commented on CALCITE-4910:
---
[~Ziwei Liu]: if you use simple equals operations and such; the simplification
happens:
{code}
explain plan for select * from emps where (empno = 0 or deptno = 1) and (
empno=2 or deptno=2) and deptno=2 and empno=0;
{"resultset":[
{"PLAN":"EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t2):INTEGER],
expr#4=[2], expr#5=[=($t3, $t4)], expr#6=[CAST($t0):INTEGER], expr#7=[0],
expr#8=[=($t6, $t7)], expr#9=[AND($t5, $t8)], proj#0..2=[{exprs}],
$condition=[$t9])\n EnumerableTableScan(table=[[SALES, EMPS]])\n"}
]}
{code}
I was thinking that this will not be an issue with simplification - but it
seems like it is; if I use similar IS NULL checks as in the above query:
{code}
explain plan for select * from emps where (replace(name,'a','b') is not null or
deptno = 1) and ( empno=2 or replace(name,'b','x') is not null) and
replace(name,'a','b') is not null and replace(name,'b','x') is not null ;
{"resultset":[
{"PLAN":"EnumerableCalc(expr#0..2=[{inputs}], expr#3=['a'], expr#4=['b'],
expr#5=[REPLACE($t1, $t3, $t4)], expr#6=[IS NOT NULL($t5)],
expr#7=[CAST($t2):INTEGER], expr#8=[1], expr#9=[=($t7, $t8)], expr#10=[OR($t6,
$t9)], expr#11=[CAST($t0):INTEGER], expr#12=[2], expr#13=[=($t11, $t12)],
expr#14=['x'], expr#15=[REPLACE($t1, $t4, $t14)], expr#16=[IS NOT NULL($t15)],
expr#17=[OR($t13, $t16)], expr#18=[AND($t10, $t17, $t6, $t16)],
proj#0..2=[{exprs}], $condition=[$t18])\n EnumerableTableScan(table=[[SALES,
EMPS]])\n"}
]}
{code}
I think this might be connected to some of the following:
* REPLACE is registered as a OTHER_FUNCTION
* the issue seems to be around `IS NULL` handling of predicates
* oddly it seems at first glance that simplifyUsingPredicates is not invoked
for non-comparisions...do we really miss that?
* I think a good point to start debugging this issue is around
RexSimplify#simplifyUsingPredicates
I have to jump into something else now
> Enhance simplify to reduce ((a or d) and (a or c) and a and b) to (a and b)
> ---
>
> Key: CALCITE-4910
> URL: https://issues.apache.org/jira/browse/CALCITE-4910
> Project: Calcite
> Issue Type: Improvement
>Reporter: Ziwei Liu
>Assignee: Ziwei Liu
>Priority: Major
>
> Considering this condition, (a or d) and (a or c) and a and b. This condition
> can be simplified to a and b, but now simplify can not do this.
> This case is found out by materialized view test:
> {code}
> materialized view: select 'a',\"empid\",\"deptno\",\"salary\" as s from
> \"emps\" where (replace(\"name\",'\\2','a') is not null or
> replace(\"name\",'a[[:alpha:]]{5}','c') is not null) and
> (replace(\"name\",'[[:blank:]][[:alnum:]]*','b') is not null or
> replace(\"name\",'(.*)(.)$','d') is not null)
> query: select \"salary\" +1 as s,\"deptno\" from \"emps\" where
> replace(\"name\",'[[:blank:]][[:alnum:]]*','b' ) is not null and
> replace(\"name\",'a[[:alpha:]]{5}','c') is not null and \"salary\">10
> {code}
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Updated] (CALCITE-4910) Enhance simplify to reduce ((a or d) and (a or c) and a and b) to (a and b)
[
https://issues.apache.org/jira/browse/CALCITE-4910?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zoltan Haindrich updated CALCITE-4910:
--
Description:
Considering this condition, (a or d) and (a or c) and a and b. This condition
can be simplified to a and b, but now simplify can not do this.
This case is found out by materialized view test:
{code}
materialized view: select 'a',\"empid\",\"deptno\",\"salary\" as s from
\"emps\" where (replace(\"name\",'\\2','a') is not null or
replace(\"name\",'a[[:alpha:]]{5}','c') is not null) and
(replace(\"name\",'[[:blank:]][[:alnum:]]*','b') is not null or
replace(\"name\",'(.*)(.)$','d') is not null)
query: select \"salary\" +1 as s,\"deptno\" from \"emps\" where
replace(\"name\",'[[:blank:]][[:alnum:]]*','b' ) is not null and
replace(\"name\",'a[[:alpha:]]{5}','c') is not null and \"salary\">10
{code}
was:
Considering this condition, (a or d) and (a or c) and a and b. This condition
can be simplified to a and b, but now simplify can not do this.
This case is found out by materialized view test:
materialized view: select 'a',\"empid\",\"deptno\",\"salary\" as s from
\"emps\" where (replace(\"name\",'\\2','a') is not null or
replace(\"name\",'a[[:alpha:]]{5}','c') is not null) and
(replace(\"name\",'[[:blank:]][[:alnum:]]*','b') is not null or
replace(\"name\",'(.*)(.)$','d') is not null)
query: select \"salary\" +1 as s,\"deptno\" from \"emps\" where
replace(\"name\",'[[:blank:]][[:alnum:]]*','b' ) is not null and
replace(\"name\",'a[[:alpha:]]{5}','c') is not null and \"salary\">10
> Enhance simplify to reduce ((a or d) and (a or c) and a and b) to (a and b)
> ---
>
> Key: CALCITE-4910
> URL: https://issues.apache.org/jira/browse/CALCITE-4910
> Project: Calcite
> Issue Type: Improvement
>Reporter: Ziwei Liu
>Assignee: Ziwei Liu
>Priority: Major
>
> Considering this condition, (a or d) and (a or c) and a and b. This condition
> can be simplified to a and b, but now simplify can not do this.
> This case is found out by materialized view test:
> {code}
> materialized view: select 'a',\"empid\",\"deptno\",\"salary\" as s from
> \"emps\" where (replace(\"name\",'\\2','a') is not null or
> replace(\"name\",'a[[:alpha:]]{5}','c') is not null) and
> (replace(\"name\",'[[:blank:]][[:alnum:]]*','b') is not null or
> replace(\"name\",'(.*)(.)$','d') is not null)
> query: select \"salary\" +1 as s,\"deptno\" from \"emps\" where
> replace(\"name\",'[[:blank:]][[:alnum:]]*','b' ) is not null and
> replace(\"name\",'a[[:alpha:]]{5}','c') is not null and \"salary\">10
> {code}
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Commented] (CALCITE-4398) RexSimplify introduces cast(nullable as nonnull)
[
https://issues.apache.org/jira/browse/CALCITE-4398?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17230898#comment-17230898
]
Zoltan Haindrich commented on CALCITE-4398:
---
">=(?0.int1, 0)" can be null; but the whole expression may not; take a look at
the indented form of the input expression:
{code}
isNotNull(
case_(
case_(
trueLiteral,
isFalse(lt(vInt(1), literal(0))),
trueLiteral),
trueLiteral,
falseLiteral
)
)
{code}
the important part is the outer case statement:
{code}
isNotNull(
case_(
anyConditional(),
trueLiteral,
falseLiteral
)
)
{code}
no matter what the conditional returns it will be either trueLiteral or
falseLiteral ; from which neither is null - so the while case may not be null
> RexSimplify introduces cast(nullable as nonnull)
>
>
> Key: CALCITE-4398
> URL: https://issues.apache.org/jira/browse/CALCITE-4398
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.26.0
>Reporter: Vladimir Sitnikov
>Priority: Major
>
> {noformat}
> isNotNull(case_(case_(trueLiteral, isFalse(lt(vInt(1), literal(0))),
> trueLiteral), trueLiteral, falseLiteral))
> {noformat}
> is optimized to
> {noformat}IS NOT NULL(CAST(>=(?0.int1, 0)):BOOLEAN NOT NULL){noformat}
> which looks strange, since it casts nullable value to a non-null type.
> /cc [~kgyrtkirk]
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-4398) RexSimplify introduces cast(nullable as nonnull)
[
https://issues.apache.org/jira/browse/CALCITE-4398?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17230789#comment-17230789
]
Zoltan Haindrich commented on CALCITE-4398:
---
this is incorrect; at most it could be:
{code}
IS NOT NULL(IS TRUE(>=(?0.int1, 0)))
{code}
which could be further rewritten to {{TRUE}} ; because {{IS TRUE(>=(?0.int1,
0))}} may never be {{null}} .
> RexSimplify introduces cast(nullable as nonnull)
>
>
> Key: CALCITE-4398
> URL: https://issues.apache.org/jira/browse/CALCITE-4398
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.26.0
>Reporter: Vladimir Sitnikov
>Priority: Major
>
> {noformat}
> isNotNull(case_(case_(trueLiteral, isFalse(lt(vInt(1), literal(0))),
> trueLiteral), trueLiteral, falseLiteral))
> {noformat}
> is optimized to
> {noformat}IS NOT NULL(CAST(>=(?0.int1, 0)):BOOLEAN NOT NULL){noformat}
> which looks strange, since it casts nullable value to a non-null type.
> /cc [~kgyrtkirk]
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-4388) RexNode#isAlwaysFalse and isAlwaysTrue should be aligned with RexSimplify#isSafeExpression
[
https://issues.apache.org/jira/browse/CALCITE-4388?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17228430#comment-17228430
]
Zoltan Haindrich commented on CALCITE-4388:
---
oh...but I don't think I should get into these things right now - this is a bug
ticket :)
I think the most straight forward way of handling this would be to introduce an
isSafe method to RexCall and add some safeness checks to the
isAlways(Tru|Fals)e methods
> RexNode#isAlwaysFalse and isAlwaysTrue should be aligned with
> RexSimplify#isSafeExpression
> --
>
> Key: CALCITE-4388
> URL: https://issues.apache.org/jira/browse/CALCITE-4388
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.26.0
>Reporter: Vladimir Sitnikov
>Priority: Major
>
> {{(1/0) IS NULL}} should not result in {{isAlwaysFalse}}.
> Currently, {{RexSimplify}} knows that 1/0 must not be simplified, however,
> {{isAlwaysTrue}} and {{isAlwaysFalse}} are still wrong.
> That might result in wrong data, especially when the client code calls
> {{isAlways...}}
> [~kgyrtkirk], I see you contributed a lot to simplification and
> {{SafeRexVisitor}}. Are you interested in improving {{isAlways...}}?
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-4388) RexNode#isAlwaysFalse and isAlwaysTrue should be aligned with RexSimplify#isSafeExpression
[
https://issues.apache.org/jira/browse/CALCITE-4388?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17228429#comment-17228429
]
Zoltan Haindrich commented on CALCITE-4388:
---
[~vladimirsitnikov] thank you for pinging me; this issue reminds me of an idea
I was thinking about around I've done "saferexvisitor" stuff.
We've defined a few properties for "all" the SqlKind-s in Calcite (we have
Strong and Safe - but there might be others).
However - how the actual checkers for these properties are in different files
from the "Kind"-s. So if someone adds a new kind it will fall into the
"default" handler in these property checker implementations.
Adding a few annotations to SqlKind could enable us to decide these properties
at the time the new kind is introduced - (and probably cover things better by
answering basic questions one-by-one centered around the kind - and not around
the question itself).
I would categorize "isAlwaysTrue" also a property.
I would like to note one more thing: isAlwaysTrue / safe expression check and
all these things could become costly - because they are walking the rex tree
thru the stack...one way around this could be to add something to each rexnode
which could "cache" the computed properties of the node; or pass around some
entity which could take care of caching stuff
> RexNode#isAlwaysFalse and isAlwaysTrue should be aligned with
> RexSimplify#isSafeExpression
> --
>
> Key: CALCITE-4388
> URL: https://issues.apache.org/jira/browse/CALCITE-4388
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.26.0
>Reporter: Vladimir Sitnikov
>Priority: Major
>
> {{(1/0) IS NULL}} should not result in {{isAlwaysFalse}}.
> Currently, {{RexSimplify}} knows that 1/0 must not be simplified, however,
> {{isAlwaysTrue}} and {{isAlwaysFalse}} are still wrong.
> That might result in wrong data, especially when the client code calls
> {{isAlways...}}
> [~kgyrtkirk], I see you contributed a lot to simplification and
> {{SafeRexVisitor}}. Are you interested in improving {{isAlways...}}?
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-4118) RexSimplify might remove CAST from RexNode incorrectly
[
https://issues.apache.org/jira/browse/CALCITE-4118?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17156744#comment-17156744
]
Zoltan Haindrich commented on CALCITE-4118:
---
it turned out that in case the varchar's precision is -1 the bug does not
appear - I've corrected the RexProgramTest testcase ; it's reproducible with:
{code}
@Test void testSimplifyVarbinary() {
checkSimplifyUnchanged(cast(cast(vInt(), tVarchar(true,100)),
tVarbinary(true)));
}
{code}
this was unintentionally caused by CALCITE-3712 by removing the case
[here|https://github.com/apache/calcite/pull/1730/files#diff-5f0afa01652e21f36d8d4172f9af4dfeR1875]
> RexSimplify might remove CAST from RexNode incorrectly
> --
>
> Key: CALCITE-4118
> URL: https://issues.apache.org/jira/browse/CALCITE-4118
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.23.0
>Reporter: Shuo Cheng
>Priority: Major
> Fix For: 1.24.0
>
>
> {code:java}
> @Test void testCastError() {
> final String sql = "select cast(cast(count(distinct empno) as
> varchar(65536)) as varbinary)\n"
> + "from emp group by deptno";
> sql(sql).ok();
> }
> {code}
> Consider the above test case, we get the following plan after SqlToRel.
> {code:java}
> LogicalProject(EXPR$0=[CAST($1):VARBINARY NOT NULL])
> LogicalAggregate(group=[{0}], agg#0=[COUNT(DISTINCT $1)])
> LogicalProject(DEPTNO=[$7], EMPNO=[$0])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> As it is shown, `cast(* as varchar)` is removed (by
> RexSimplify#SimplifyCast), which is obviously wrong, because BIGINT can not
> cast to VARBINARY.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-4118) RexSimplify might remove CAST from RexNode incorrectly
[
https://issues.apache.org/jira/browse/CALCITE-4118?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17155950#comment-17155950
]
Zoltan Haindrich edited comment on CALCITE-4118 at 7/11/20, 11:13 AM:
--
I forgot to make a note here: yesterday I started looking into this - I wrote a
{{RexProgramTest}} testcase - which have cleared {{RexSimplify}} and I think I
also checked {{ReduceExpressionRule}} - I believe the issue might be in
somewhere the SqlToRelConverter; but I had to jump on something...
I'll try to get back to this on monday...and trace the full path of this issue.
was (Author: kgyrtkirk):
I forgot to make a note here: yesterday I started looking into this - I wrote a
{{RexProgramTest}} testcase - which have cleared {{RexSimplify}} and I think I
also checked {{ReduceExpressionRule}} - I believe the issue might be in
somewhere the SqlToRelConverter; but I had to jump on something...
> RexSimplify might remove CAST from RexNode incorrectly
> --
>
> Key: CALCITE-4118
> URL: https://issues.apache.org/jira/browse/CALCITE-4118
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.23.0
>Reporter: Shuo Cheng
>Priority: Major
> Fix For: 1.24.0
>
>
> {code:java}
> @Test void testCastError() {
> final String sql = "select cast(cast(count(distinct empno) as
> varchar(65536)) as varbinary)\n"
> + "from emp group by deptno";
> sql(sql).ok();
> }
> {code}
> Consider the above test case, we get the following plan after SqlToRel.
> {code:java}
> LogicalProject(EXPR$0=[CAST($1):VARBINARY NOT NULL])
> LogicalAggregate(group=[{0}], agg#0=[COUNT(DISTINCT $1)])
> LogicalProject(DEPTNO=[$7], EMPNO=[$0])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> As it is shown, `cast(* as varchar)` is removed (by
> RexSimplify#SimplifyCast), which is obviously wrong, because BIGINT can not
> cast to VARBINARY.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-4118) RexSimplify might remove CAST from RexNode incorrectly
[
https://issues.apache.org/jira/browse/CALCITE-4118?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17155950#comment-17155950
]
Zoltan Haindrich commented on CALCITE-4118:
---
I forgot to make a note here: yesterday I started looking into this - I wrote a
{{RexProgramTest}} testcase - which have cleared {{RexSimplify}} and I think I
also checked {{ReduceExpressionRule}} - I believe the issue might be in
somewhere the SqlToRelConverter; but I had to jump on something...
> RexSimplify might remove CAST from RexNode incorrectly
> --
>
> Key: CALCITE-4118
> URL: https://issues.apache.org/jira/browse/CALCITE-4118
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.23.0
>Reporter: Shuo Cheng
>Priority: Major
> Fix For: 1.24.0
>
>
> {code:java}
> @Test void testCastError() {
> final String sql = "select cast(cast(count(distinct empno) as
> varchar(65536)) as varbinary)\n"
> + "from emp group by deptno";
> sql(sql).ok();
> }
> {code}
> Consider the above test case, we get the following plan after SqlToRel.
> {code:java}
> LogicalProject(EXPR$0=[CAST($1):VARBINARY NOT NULL])
> LogicalAggregate(group=[{0}], agg#0=[COUNT(DISTINCT $1)])
> LogicalProject(DEPTNO=[$7], EMPNO=[$0])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> As it is shown, `cast(* as varchar)` is removed (by
> RexSimplify#SimplifyCast), which is obviously wrong, because BIGINT can not
> cast to VARBINARY.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Created] (CALCITE-4006) Add ordered-set aggregate functions
Zoltan Haindrich created CALCITE-4006: - Summary: Add ordered-set aggregate functions Key: CALCITE-4006 URL: https://issues.apache.org/jira/browse/CALCITE-4006 Project: Calcite Issue Type: Improvement Reporter: Zoltan Haindrich * mode * percentile_cont * percentile_disc -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Resolved] (CALCITE-3979) Simplification might have removed CAST expression(s) incorrectly
[
https://issues.apache.org/jira/browse/CALCITE-3979?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zoltan Haindrich resolved CALCITE-3979.
---
Resolution: Fixed
Thank you all!
fixed in
[ffa22f7e8b94fce1e7c297404453d13f959fd646|https://github.com/apache/calcite/commit/ffa22f7e8b94fce1e7c297404453d13f959fd646]
> Simplification might have removed CAST expression(s) incorrectly
>
>
> Key: CALCITE-3979
> URL: https://issues.apache.org/jira/browse/CALCITE-3979
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.22.0
>Reporter: Shuo Cheng
>Assignee: Zoltan Haindrich
>Priority: Critical
> Labels: pull-request-available
> Fix For: 1.23.0
>
> Time Spent: 40m
> Remaining Estimate: 0h
>
> {code:java}
> @Test public void testCastRemove() throws Exception {
> final String sql = "select\n" +
> "case when cast(ename as double) < 5 then 0.0\n" +
> " else coalesce(cast(ename as double), 1.0)\n" +
> " end as t\n" +
> " from (\n" +
> " select\n" +
> " case when ename > 'abc' then ename\n" +
> " else null\n" +
> " end as ename from emp\n" +
> " )";
> sql(sql).withRule(ReduceExpressionsRule.PROJECT_INSTANCE).check();
> }
> {code}
> As shown in the above test case, `cast(ename as double) < 5` was reduced as
> `ename < 5` when reducing expression, which lead to the following exception:
> {code:java}
> java.math.BigDecimal cannot be cast to
> org.apache.calcite.util.NlsStringjava.math.BigDecimal cannot be cast to
> org.apache.calcite.util.NlsStringjava.lang.ClassCastException:
> java.math.BigDecimal cannot be cast to org.apache.calcite.util.NlsString at
> org.apache.calcite.util.NlsString.compareTo(NlsString.java:47) at
> com.google.common.collect.Range.compareOrThrow(Range.java:672) at
> com.google.common.collect.Cut.compareTo(Cut.java:79) at
> com.google.common.collect.Range.isConnected(Range.java:526) at
> org.apache.calcite.rex.RexSimplify.residue(RexSimplify.java:1702) at
> org.apache.calcite.rex.RexSimplify.simplifyUsingPredicates(RexSimplify.java:1636)
> at
> org.apache.calcite.rex.RexSimplify.simplifyComparison(RexSimplify.java:451)
> at
> org.apache.calcite.rex.RexSimplify.simplifyComparison(RexSimplify.java:321)
> at org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:292) at
> org.apache.calcite.rex.RexSimplify.simplifyAndTerms(RexSimplify.java:492) at
> org.apache.calcite.rex.RexSimplify.simplifyAnd(RexSimplify.java:1275) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:264) at
> org.apache.calcite.rex.RexSimplify.simplifyCase(RexSimplify.java:992) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:270) at
> org.apache.calcite.rex.RexSimplify.simplifyCase(RexSimplify.java:926) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:270) at
> org.apache.calcite.rex.RexSimplify.simplifyUnknownAs(RexSimplify.java:235) at
> org.apache.calcite.rex.RexSimplify.simplifyPreservingType(RexSimplify.java:174)
> at
> org.apache.calcite.rel.rules.ReduceExpressionsRule.reduceExpressions(ReduceExpressionsRule.java:629)
> at
> org.apache.calcite.rel.rules.ReduceExpressionsRule$ProjectReduceExpressionsRule.onMatch(ReduceExpressionsRule.java:305)
> at
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:338)
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3979) Simplification might have removed CAST expression(s) incorrectly
[
https://issues.apache.org/jira/browse/CALCITE-3979?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17106678#comment-17106678
]
Zoltan Haindrich commented on CALCITE-3979:
---
[~hyuan] please consider this for 1.23.0 - it's a bug reported last week - but
it was probably there since 1.18 ; the pr is ready/etc.
if you think it's too much - we can leave it for 1.24
> Simplification might have removed CAST expression(s) incorrectly
>
>
> Key: CALCITE-3979
> URL: https://issues.apache.org/jira/browse/CALCITE-3979
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.22.0
>Reporter: Shuo Cheng
>Assignee: Zoltan Haindrich
>Priority: Critical
> Labels: pull-request-available
> Fix For: 1.23.0
>
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> {code:java}
> @Test public void testCastRemove() throws Exception {
> final String sql = "select\n" +
> "case when cast(ename as double) < 5 then 0.0\n" +
> " else coalesce(cast(ename as double), 1.0)\n" +
> " end as t\n" +
> " from (\n" +
> " select\n" +
> " case when ename > 'abc' then ename\n" +
> " else null\n" +
> " end as ename from emp\n" +
> " )";
> sql(sql).withRule(ReduceExpressionsRule.PROJECT_INSTANCE).check();
> }
> {code}
> As shown in the above test case, `cast(ename as double) < 5` was reduced as
> `ename < 5` when reducing expression, which lead to the following exception:
> {code:java}
> java.math.BigDecimal cannot be cast to
> org.apache.calcite.util.NlsStringjava.math.BigDecimal cannot be cast to
> org.apache.calcite.util.NlsStringjava.lang.ClassCastException:
> java.math.BigDecimal cannot be cast to org.apache.calcite.util.NlsString at
> org.apache.calcite.util.NlsString.compareTo(NlsString.java:47) at
> com.google.common.collect.Range.compareOrThrow(Range.java:672) at
> com.google.common.collect.Cut.compareTo(Cut.java:79) at
> com.google.common.collect.Range.isConnected(Range.java:526) at
> org.apache.calcite.rex.RexSimplify.residue(RexSimplify.java:1702) at
> org.apache.calcite.rex.RexSimplify.simplifyUsingPredicates(RexSimplify.java:1636)
> at
> org.apache.calcite.rex.RexSimplify.simplifyComparison(RexSimplify.java:451)
> at
> org.apache.calcite.rex.RexSimplify.simplifyComparison(RexSimplify.java:321)
> at org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:292) at
> org.apache.calcite.rex.RexSimplify.simplifyAndTerms(RexSimplify.java:492) at
> org.apache.calcite.rex.RexSimplify.simplifyAnd(RexSimplify.java:1275) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:264) at
> org.apache.calcite.rex.RexSimplify.simplifyCase(RexSimplify.java:992) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:270) at
> org.apache.calcite.rex.RexSimplify.simplifyCase(RexSimplify.java:926) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:270) at
> org.apache.calcite.rex.RexSimplify.simplifyUnknownAs(RexSimplify.java:235) at
> org.apache.calcite.rex.RexSimplify.simplifyPreservingType(RexSimplify.java:174)
> at
> org.apache.calcite.rel.rules.ReduceExpressionsRule.reduceExpressions(ReduceExpressionsRule.java:629)
> at
> org.apache.calcite.rel.rules.ReduceExpressionsRule$ProjectReduceExpressionsRule.onMatch(ReduceExpressionsRule.java:305)
> at
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:338)
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3979) Simplification might have removed CAST expression(s) incorrectly
[
https://issues.apache.org/jira/browse/CALCITE-3979?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17106670#comment-17106670
]
Zoltan Haindrich commented on CALCITE-3979:
---
I see - this logic is org.eigenbase old - so it was there from the begining;
and it was most probably worked more or less correctly until RexNode.equals got
fixed.
I've taken a look at other places - I've seen a few interesting situations; but
they should work correctly.
> Simplification might have removed CAST expression(s) incorrectly
>
>
> Key: CALCITE-3979
> URL: https://issues.apache.org/jira/browse/CALCITE-3979
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.22.0
>Reporter: Shuo Cheng
>Assignee: Zoltan Haindrich
>Priority: Critical
> Labels: pull-request-available
> Fix For: 1.23.0
>
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> {code:java}
> @Test public void testCastRemove() throws Exception {
> final String sql = "select\n" +
> "case when cast(ename as double) < 5 then 0.0\n" +
> " else coalesce(cast(ename as double), 1.0)\n" +
> " end as t\n" +
> " from (\n" +
> " select\n" +
> " case when ename > 'abc' then ename\n" +
> " else null\n" +
> " end as ename from emp\n" +
> " )";
> sql(sql).withRule(ReduceExpressionsRule.PROJECT_INSTANCE).check();
> }
> {code}
> As shown in the above test case, `cast(ename as double) < 5` was reduced as
> `ename < 5` when reducing expression, which lead to the following exception:
> {code:java}
> java.math.BigDecimal cannot be cast to
> org.apache.calcite.util.NlsStringjava.math.BigDecimal cannot be cast to
> org.apache.calcite.util.NlsStringjava.lang.ClassCastException:
> java.math.BigDecimal cannot be cast to org.apache.calcite.util.NlsString at
> org.apache.calcite.util.NlsString.compareTo(NlsString.java:47) at
> com.google.common.collect.Range.compareOrThrow(Range.java:672) at
> com.google.common.collect.Cut.compareTo(Cut.java:79) at
> com.google.common.collect.Range.isConnected(Range.java:526) at
> org.apache.calcite.rex.RexSimplify.residue(RexSimplify.java:1702) at
> org.apache.calcite.rex.RexSimplify.simplifyUsingPredicates(RexSimplify.java:1636)
> at
> org.apache.calcite.rex.RexSimplify.simplifyComparison(RexSimplify.java:451)
> at
> org.apache.calcite.rex.RexSimplify.simplifyComparison(RexSimplify.java:321)
> at org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:292) at
> org.apache.calcite.rex.RexSimplify.simplifyAndTerms(RexSimplify.java:492) at
> org.apache.calcite.rex.RexSimplify.simplifyAnd(RexSimplify.java:1275) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:264) at
> org.apache.calcite.rex.RexSimplify.simplifyCase(RexSimplify.java:992) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:270) at
> org.apache.calcite.rex.RexSimplify.simplifyCase(RexSimplify.java:926) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:270) at
> org.apache.calcite.rex.RexSimplify.simplifyUnknownAs(RexSimplify.java:235) at
> org.apache.calcite.rex.RexSimplify.simplifyPreservingType(RexSimplify.java:174)
> at
> org.apache.calcite.rel.rules.ReduceExpressionsRule.reduceExpressions(ReduceExpressionsRule.java:629)
> at
> org.apache.calcite.rel.rules.ReduceExpressionsRule$ProjectReduceExpressionsRule.onMatch(ReduceExpressionsRule.java:305)
> at
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:338)
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3979) Simplification might have removed CAST expression(s) incorrectly
[
https://issues.apache.org/jira/browse/CALCITE-3979?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17104169#comment-17104169
]
Zoltan Haindrich commented on CALCITE-3979:
---
[~julianhyde] yes it can be dangerousIn this case the visitor logic in
ReduceExpressionsRule was actually doing a rex subtree search and replace which
ended up matching the wrong subtree (cause it was context sensitvie - I think
this behaviour have changed around when the equals was fixed)...instead of
fixing it I think it should be removed - we have the same thing already covered
in RexSimplify already.
I think we only do conservative CAST simplifications which are:
* if the cast only widens nullability, it is removed ({{(x:INTEGER NOT
NULL)::INTEGER}})
* if the cast is lossless
* literals like CAST('123' as integer)
but
[RexSimplify#simplifyCast|https://github.com/apache/calcite/blob/2730b4b696fcf1651acfa63e088f9c60c9369386/core/src/main/java/org/apache/calcite/rex/RexSimplify.java#L1882]
is the real source of thruth
Some of the logic in ReduceExpressionsRule is already covered in RexSimplify ;
maybe the best would be to move.
Staying with the topic - do you see any reasons to keep the cast removal logic
in ReduceExpressionsRule?
> Simplification might have removed CAST expression(s) incorrectly
>
>
> Key: CALCITE-3979
> URL: https://issues.apache.org/jira/browse/CALCITE-3979
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.22.0
>Reporter: Shuo Cheng
>Assignee: Zoltan Haindrich
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.23.0
>
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> {code:java}
> @Test public void testCastRemove() throws Exception {
> final String sql = "select\n" +
> "case when cast(ename as double) < 5 then 0.0\n" +
> " else coalesce(cast(ename as double), 1.0)\n" +
> " end as t\n" +
> " from (\n" +
> " select\n" +
> " case when ename > 'abc' then ename\n" +
> " else null\n" +
> " end as ename from emp\n" +
> " )";
> sql(sql).withRule(ReduceExpressionsRule.PROJECT_INSTANCE).check();
> }
> {code}
> As shown in the above test case, `cast(ename as double) < 5` was reduced as
> `ename < 5` when reducing expression, which lead to the following exception:
> {code:java}
> java.math.BigDecimal cannot be cast to
> org.apache.calcite.util.NlsStringjava.math.BigDecimal cannot be cast to
> org.apache.calcite.util.NlsStringjava.lang.ClassCastException:
> java.math.BigDecimal cannot be cast to org.apache.calcite.util.NlsString at
> org.apache.calcite.util.NlsString.compareTo(NlsString.java:47) at
> com.google.common.collect.Range.compareOrThrow(Range.java:672) at
> com.google.common.collect.Cut.compareTo(Cut.java:79) at
> com.google.common.collect.Range.isConnected(Range.java:526) at
> org.apache.calcite.rex.RexSimplify.residue(RexSimplify.java:1702) at
> org.apache.calcite.rex.RexSimplify.simplifyUsingPredicates(RexSimplify.java:1636)
> at
> org.apache.calcite.rex.RexSimplify.simplifyComparison(RexSimplify.java:451)
> at
> org.apache.calcite.rex.RexSimplify.simplifyComparison(RexSimplify.java:321)
> at org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:292) at
> org.apache.calcite.rex.RexSimplify.simplifyAndTerms(RexSimplify.java:492) at
> org.apache.calcite.rex.RexSimplify.simplifyAnd(RexSimplify.java:1275) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:264) at
> org.apache.calcite.rex.RexSimplify.simplifyCase(RexSimplify.java:992) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:270) at
> org.apache.calcite.rex.RexSimplify.simplifyCase(RexSimplify.java:926) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:270) at
> org.apache.calcite.rex.RexSimplify.simplifyUnknownAs(RexSimplify.java:235) at
> org.apache.calcite.rex.RexSimplify.simplifyPreservingType(RexSimplify.java:174)
> at
> org.apache.calcite.rel.rules.ReduceExpressionsRule.reduceExpressions(ReduceExpressionsRule.java:629)
> at
> org.apache.calcite.rel.rules.ReduceExpressionsRule$ProjectReduceExpressionsRule.onMatch(ReduceExpressionsRule.java:305)
> at
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:338)
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-3979) Simplification might have removed CAST expression(s) incorrectly
[
https://issues.apache.org/jira/browse/CALCITE-3979?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zoltan Haindrich updated CALCITE-3979:
--
Labels: pull-request-available (was: )
> Simplification might have removed CAST expression(s) incorrectly
>
>
> Key: CALCITE-3979
> URL: https://issues.apache.org/jira/browse/CALCITE-3979
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.22.0
>Reporter: Shuo Cheng
>Assignee: Zoltan Haindrich
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.23.0
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> {code:java}
> @Test public void testCastRemove() throws Exception {
> final String sql = "select\n" +
> "case when cast(ename as double) < 5 then 0.0\n" +
> " else coalesce(cast(ename as double), 1.0)\n" +
> " end as t\n" +
> " from (\n" +
> " select\n" +
> " case when ename > 'abc' then ename\n" +
> " else null\n" +
> " end as ename from emp\n" +
> " )";
> sql(sql).withRule(ReduceExpressionsRule.PROJECT_INSTANCE).check();
> }
> {code}
> As shown in the above test case, `cast(ename as double) < 5` was reduced as
> `ename < 5` when reducing expression, which lead to the following exception:
> {code:java}
> java.math.BigDecimal cannot be cast to
> org.apache.calcite.util.NlsStringjava.math.BigDecimal cannot be cast to
> org.apache.calcite.util.NlsStringjava.lang.ClassCastException:
> java.math.BigDecimal cannot be cast to org.apache.calcite.util.NlsString at
> org.apache.calcite.util.NlsString.compareTo(NlsString.java:47) at
> com.google.common.collect.Range.compareOrThrow(Range.java:672) at
> com.google.common.collect.Cut.compareTo(Cut.java:79) at
> com.google.common.collect.Range.isConnected(Range.java:526) at
> org.apache.calcite.rex.RexSimplify.residue(RexSimplify.java:1702) at
> org.apache.calcite.rex.RexSimplify.simplifyUsingPredicates(RexSimplify.java:1636)
> at
> org.apache.calcite.rex.RexSimplify.simplifyComparison(RexSimplify.java:451)
> at
> org.apache.calcite.rex.RexSimplify.simplifyComparison(RexSimplify.java:321)
> at org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:292) at
> org.apache.calcite.rex.RexSimplify.simplifyAndTerms(RexSimplify.java:492) at
> org.apache.calcite.rex.RexSimplify.simplifyAnd(RexSimplify.java:1275) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:264) at
> org.apache.calcite.rex.RexSimplify.simplifyCase(RexSimplify.java:992) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:270) at
> org.apache.calcite.rex.RexSimplify.simplifyCase(RexSimplify.java:926) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:270) at
> org.apache.calcite.rex.RexSimplify.simplifyUnknownAs(RexSimplify.java:235) at
> org.apache.calcite.rex.RexSimplify.simplifyPreservingType(RexSimplify.java:174)
> at
> org.apache.calcite.rel.rules.ReduceExpressionsRule.reduceExpressions(ReduceExpressionsRule.java:629)
> at
> org.apache.calcite.rel.rules.ReduceExpressionsRule$ProjectReduceExpressionsRule.onMatch(ReduceExpressionsRule.java:305)
> at
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:338)
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-3979) Simplification might have removed CAST expression(s) incorrectly
[
https://issues.apache.org/jira/browse/CALCITE-3979?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zoltan Haindrich updated CALCITE-3979:
--
Summary: Simplification might have removed CAST expression(s) incorrectly
(was: ReduceExpressionsRule might have removed CAST expression(s) incorrectly)
> Simplification might have removed CAST expression(s) incorrectly
>
>
> Key: CALCITE-3979
> URL: https://issues.apache.org/jira/browse/CALCITE-3979
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.22.0
>Reporter: Shuo Cheng
>Assignee: Zoltan Haindrich
>Priority: Major
> Fix For: 1.23.0
>
>
> {code:java}
> @Test public void testCastRemove() throws Exception {
> final String sql = "select\n" +
> "case when cast(ename as double) < 5 then 0.0\n" +
> " else coalesce(cast(ename as double), 1.0)\n" +
> " end as t\n" +
> " from (\n" +
> " select\n" +
> " case when ename > 'abc' then ename\n" +
> " else null\n" +
> " end as ename from emp\n" +
> " )";
> sql(sql).withRule(ReduceExpressionsRule.PROJECT_INSTANCE).check();
> }
> {code}
> As shown in the above test case, `cast(ename as double) < 5` was reduced as
> `ename < 5` when reducing expression, which lead to the following exception:
> {code:java}
> java.math.BigDecimal cannot be cast to
> org.apache.calcite.util.NlsStringjava.math.BigDecimal cannot be cast to
> org.apache.calcite.util.NlsStringjava.lang.ClassCastException:
> java.math.BigDecimal cannot be cast to org.apache.calcite.util.NlsString at
> org.apache.calcite.util.NlsString.compareTo(NlsString.java:47) at
> com.google.common.collect.Range.compareOrThrow(Range.java:672) at
> com.google.common.collect.Cut.compareTo(Cut.java:79) at
> com.google.common.collect.Range.isConnected(Range.java:526) at
> org.apache.calcite.rex.RexSimplify.residue(RexSimplify.java:1702) at
> org.apache.calcite.rex.RexSimplify.simplifyUsingPredicates(RexSimplify.java:1636)
> at
> org.apache.calcite.rex.RexSimplify.simplifyComparison(RexSimplify.java:451)
> at
> org.apache.calcite.rex.RexSimplify.simplifyComparison(RexSimplify.java:321)
> at org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:292) at
> org.apache.calcite.rex.RexSimplify.simplifyAndTerms(RexSimplify.java:492) at
> org.apache.calcite.rex.RexSimplify.simplifyAnd(RexSimplify.java:1275) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:264) at
> org.apache.calcite.rex.RexSimplify.simplifyCase(RexSimplify.java:992) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:270) at
> org.apache.calcite.rex.RexSimplify.simplifyCase(RexSimplify.java:926) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:270) at
> org.apache.calcite.rex.RexSimplify.simplifyUnknownAs(RexSimplify.java:235) at
> org.apache.calcite.rex.RexSimplify.simplifyPreservingType(RexSimplify.java:174)
> at
> org.apache.calcite.rel.rules.ReduceExpressionsRule.reduceExpressions(ReduceExpressionsRule.java:629)
> at
> org.apache.calcite.rel.rules.ReduceExpressionsRule$ProjectReduceExpressionsRule.onMatch(ReduceExpressionsRule.java:305)
> at
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:338)
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Assigned] (CALCITE-3979) ReduceExpressionsRule might have removed CAST expression(s) incorrectly
[
https://issues.apache.org/jira/browse/CALCITE-3979?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zoltan Haindrich reassigned CALCITE-3979:
-
Assignee: Zoltan Haindrich
> ReduceExpressionsRule might have removed CAST expression(s) incorrectly
> ---
>
> Key: CALCITE-3979
> URL: https://issues.apache.org/jira/browse/CALCITE-3979
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.22.0
>Reporter: Shuo Cheng
>Assignee: Zoltan Haindrich
>Priority: Major
> Fix For: 1.23.0
>
>
> {code:java}
> @Test public void testCastRemove() throws Exception {
> final String sql = "select\n" +
> "case when cast(ename as double) < 5 then 0.0\n" +
> " else coalesce(cast(ename as double), 1.0)\n" +
> " end as t\n" +
> " from (\n" +
> " select\n" +
> " case when ename > 'abc' then ename\n" +
> " else null\n" +
> " end as ename from emp\n" +
> " )";
> sql(sql).withRule(ReduceExpressionsRule.PROJECT_INSTANCE).check();
> }
> {code}
> As shown in the above test case, `cast(ename as double) < 5` was reduced as
> `ename < 5` when reducing expression, which lead to the following exception:
> {code:java}
> java.math.BigDecimal cannot be cast to
> org.apache.calcite.util.NlsStringjava.math.BigDecimal cannot be cast to
> org.apache.calcite.util.NlsStringjava.lang.ClassCastException:
> java.math.BigDecimal cannot be cast to org.apache.calcite.util.NlsString at
> org.apache.calcite.util.NlsString.compareTo(NlsString.java:47) at
> com.google.common.collect.Range.compareOrThrow(Range.java:672) at
> com.google.common.collect.Cut.compareTo(Cut.java:79) at
> com.google.common.collect.Range.isConnected(Range.java:526) at
> org.apache.calcite.rex.RexSimplify.residue(RexSimplify.java:1702) at
> org.apache.calcite.rex.RexSimplify.simplifyUsingPredicates(RexSimplify.java:1636)
> at
> org.apache.calcite.rex.RexSimplify.simplifyComparison(RexSimplify.java:451)
> at
> org.apache.calcite.rex.RexSimplify.simplifyComparison(RexSimplify.java:321)
> at org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:292) at
> org.apache.calcite.rex.RexSimplify.simplifyAndTerms(RexSimplify.java:492) at
> org.apache.calcite.rex.RexSimplify.simplifyAnd(RexSimplify.java:1275) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:264) at
> org.apache.calcite.rex.RexSimplify.simplifyCase(RexSimplify.java:992) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:270) at
> org.apache.calcite.rex.RexSimplify.simplifyCase(RexSimplify.java:926) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:270) at
> org.apache.calcite.rex.RexSimplify.simplifyUnknownAs(RexSimplify.java:235) at
> org.apache.calcite.rex.RexSimplify.simplifyPreservingType(RexSimplify.java:174)
> at
> org.apache.calcite.rel.rules.ReduceExpressionsRule.reduceExpressions(ReduceExpressionsRule.java:629)
> at
> org.apache.calcite.rel.rules.ReduceExpressionsRule$ProjectReduceExpressionsRule.onMatch(ReduceExpressionsRule.java:305)
> at
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:338)
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3979) ReduceExpressionsRule might have removed CAST expression(s) incorrectly
[
https://issues.apache.org/jira/browse/CALCITE-3979?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17103703#comment-17103703
]
Zoltan Haindrich commented on CALCITE-3979:
---
as a matter of fact, this second issue is the real issue here...RexSimplify was
just building upon exising contracts; like a '>' should be on compatible types
(...and I've removed the safety check in RexSimplify cause contracts are more
important to keep)
> ReduceExpressionsRule might have removed CAST expression(s) incorrectly
> ---
>
> Key: CALCITE-3979
> URL: https://issues.apache.org/jira/browse/CALCITE-3979
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.22.0
>Reporter: Shuo Cheng
>Priority: Major
> Fix For: 1.23.0
>
>
> {code:java}
> @Test public void testCastRemove() throws Exception {
> final String sql = "select\n" +
> "case when cast(ename as double) < 5 then 0.0\n" +
> " else coalesce(cast(ename as double), 1.0)\n" +
> " end as t\n" +
> " from (\n" +
> " select\n" +
> " case when ename > 'abc' then ename\n" +
> " else null\n" +
> " end as ename from emp\n" +
> " )";
> sql(sql).withRule(ReduceExpressionsRule.PROJECT_INSTANCE).check();
> }
> {code}
> As shown in the above test case, `cast(ename as double) < 5` was reduced as
> `ename < 5` when reducing expression, which lead to the following exception:
> {code:java}
> java.math.BigDecimal cannot be cast to
> org.apache.calcite.util.NlsStringjava.math.BigDecimal cannot be cast to
> org.apache.calcite.util.NlsStringjava.lang.ClassCastException:
> java.math.BigDecimal cannot be cast to org.apache.calcite.util.NlsString at
> org.apache.calcite.util.NlsString.compareTo(NlsString.java:47) at
> com.google.common.collect.Range.compareOrThrow(Range.java:672) at
> com.google.common.collect.Cut.compareTo(Cut.java:79) at
> com.google.common.collect.Range.isConnected(Range.java:526) at
> org.apache.calcite.rex.RexSimplify.residue(RexSimplify.java:1702) at
> org.apache.calcite.rex.RexSimplify.simplifyUsingPredicates(RexSimplify.java:1636)
> at
> org.apache.calcite.rex.RexSimplify.simplifyComparison(RexSimplify.java:451)
> at
> org.apache.calcite.rex.RexSimplify.simplifyComparison(RexSimplify.java:321)
> at org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:292) at
> org.apache.calcite.rex.RexSimplify.simplifyAndTerms(RexSimplify.java:492) at
> org.apache.calcite.rex.RexSimplify.simplifyAnd(RexSimplify.java:1275) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:264) at
> org.apache.calcite.rex.RexSimplify.simplifyCase(RexSimplify.java:992) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:270) at
> org.apache.calcite.rex.RexSimplify.simplifyCase(RexSimplify.java:926) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:270) at
> org.apache.calcite.rex.RexSimplify.simplifyUnknownAs(RexSimplify.java:235) at
> org.apache.calcite.rex.RexSimplify.simplifyPreservingType(RexSimplify.java:174)
> at
> org.apache.calcite.rel.rules.ReduceExpressionsRule.reduceExpressions(ReduceExpressionsRule.java:629)
> at
> org.apache.calcite.rel.rules.ReduceExpressionsRule$ProjectReduceExpressionsRule.onMatch(ReduceExpressionsRule.java:305)
> at
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:338)
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-3979) ReduceExpressionsRule might have removed CAST expression(s) incorrectly
[
https://issues.apache.org/jira/browse/CALCITE-3979?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zoltan Haindrich updated CALCITE-3979:
--
Summary: ReduceExpressionsRule might have removed CAST expression(s)
incorrectly (was: Unexpected removing of CAST in ReduceExpressionsRule)
> ReduceExpressionsRule might have removed CAST expression(s) incorrectly
> ---
>
> Key: CALCITE-3979
> URL: https://issues.apache.org/jira/browse/CALCITE-3979
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.22.0
>Reporter: Shuo Cheng
>Priority: Major
> Fix For: 1.23.0
>
>
> {code:java}
> @Test public void testCastRemove() throws Exception {
> final String sql = "select\n" +
> "case when cast(ename as double) < 5 then 0.0\n" +
> " else coalesce(cast(ename as double), 1.0)\n" +
> " end as t\n" +
> " from (\n" +
> " select\n" +
> " case when ename > 'abc' then ename\n" +
> " else null\n" +
> " end as ename from emp\n" +
> " )";
> sql(sql).withRule(ReduceExpressionsRule.PROJECT_INSTANCE).check();
> }
> {code}
> As shown in the above test case, `cast(ename as double) < 5` was reduced as
> `ename < 5` when reducing expression, which lead to the following exception:
> {code:java}
> java.math.BigDecimal cannot be cast to
> org.apache.calcite.util.NlsStringjava.math.BigDecimal cannot be cast to
> org.apache.calcite.util.NlsStringjava.lang.ClassCastException:
> java.math.BigDecimal cannot be cast to org.apache.calcite.util.NlsString at
> org.apache.calcite.util.NlsString.compareTo(NlsString.java:47) at
> com.google.common.collect.Range.compareOrThrow(Range.java:672) at
> com.google.common.collect.Cut.compareTo(Cut.java:79) at
> com.google.common.collect.Range.isConnected(Range.java:526) at
> org.apache.calcite.rex.RexSimplify.residue(RexSimplify.java:1702) at
> org.apache.calcite.rex.RexSimplify.simplifyUsingPredicates(RexSimplify.java:1636)
> at
> org.apache.calcite.rex.RexSimplify.simplifyComparison(RexSimplify.java:451)
> at
> org.apache.calcite.rex.RexSimplify.simplifyComparison(RexSimplify.java:321)
> at org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:292) at
> org.apache.calcite.rex.RexSimplify.simplifyAndTerms(RexSimplify.java:492) at
> org.apache.calcite.rex.RexSimplify.simplifyAnd(RexSimplify.java:1275) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:264) at
> org.apache.calcite.rex.RexSimplify.simplifyCase(RexSimplify.java:992) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:270) at
> org.apache.calcite.rex.RexSimplify.simplifyCase(RexSimplify.java:926) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:270) at
> org.apache.calcite.rex.RexSimplify.simplifyUnknownAs(RexSimplify.java:235) at
> org.apache.calcite.rex.RexSimplify.simplifyPreservingType(RexSimplify.java:174)
> at
> org.apache.calcite.rel.rules.ReduceExpressionsRule.reduceExpressions(ReduceExpressionsRule.java:629)
> at
> org.apache.calcite.rel.rules.ReduceExpressionsRule$ProjectReduceExpressionsRule.onMatch(ReduceExpressionsRule.java:305)
> at
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:338)
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3979) Unexpected removing of CAST in ReduceExpressionsRule
[
https://issues.apache.org/jira/browse/CALCITE-3979?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17103696#comment-17103696
]
Zoltan Haindrich commented on CALCITE-3979:
---
I've fixed this; but I see another issue with the simplified version.
for the above query the runtime *may* throw an exception when it tries to cast
the varchar into a double
The new issue I see now is that the explicit cast is being removed and a >($1,
5) is created in which $1 is a varchar - I think that this is just as bad for
the runtime as an explicit casted one; however in this case I feel that the
RexNode is somewhat invalid; it has incompatible left/right type families in
the comparision
After some digging it turned out that RER is doing some changes to remove
CAST(CAST(x,T),T) like casts; which had it's time when RexSimplify was not
handling that kind of things - but right now ; the replacement may also change
the inner cast type...instead of fixing this I think we can just switch off
this feature in RER and let RexSimplify handle it.
> Unexpected removing of CAST in ReduceExpressionsRule
>
>
> Key: CALCITE-3979
> URL: https://issues.apache.org/jira/browse/CALCITE-3979
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.22.0
>Reporter: Shuo Cheng
>Priority: Major
> Fix For: 1.23.0
>
>
> {code:java}
> @Test public void testCastRemove() throws Exception {
> final String sql = "select\n" +
> "case when cast(ename as double) < 5 then 0.0\n" +
> " else coalesce(cast(ename as double), 1.0)\n" +
> " end as t\n" +
> " from (\n" +
> " select\n" +
> " case when ename > 'abc' then ename\n" +
> " else null\n" +
> " end as ename from emp\n" +
> " )";
> sql(sql).withRule(ReduceExpressionsRule.PROJECT_INSTANCE).check();
> }
> {code}
> As shown in the above test case, `cast(ename as double) < 5` was reduced as
> `ename < 5` when reducing expression, which lead to the following exception:
> {code:java}
> java.math.BigDecimal cannot be cast to
> org.apache.calcite.util.NlsStringjava.math.BigDecimal cannot be cast to
> org.apache.calcite.util.NlsStringjava.lang.ClassCastException:
> java.math.BigDecimal cannot be cast to org.apache.calcite.util.NlsString at
> org.apache.calcite.util.NlsString.compareTo(NlsString.java:47) at
> com.google.common.collect.Range.compareOrThrow(Range.java:672) at
> com.google.common.collect.Cut.compareTo(Cut.java:79) at
> com.google.common.collect.Range.isConnected(Range.java:526) at
> org.apache.calcite.rex.RexSimplify.residue(RexSimplify.java:1702) at
> org.apache.calcite.rex.RexSimplify.simplifyUsingPredicates(RexSimplify.java:1636)
> at
> org.apache.calcite.rex.RexSimplify.simplifyComparison(RexSimplify.java:451)
> at
> org.apache.calcite.rex.RexSimplify.simplifyComparison(RexSimplify.java:321)
> at org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:292) at
> org.apache.calcite.rex.RexSimplify.simplifyAndTerms(RexSimplify.java:492) at
> org.apache.calcite.rex.RexSimplify.simplifyAnd(RexSimplify.java:1275) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:264) at
> org.apache.calcite.rex.RexSimplify.simplifyCase(RexSimplify.java:992) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:270) at
> org.apache.calcite.rex.RexSimplify.simplifyCase(RexSimplify.java:926) at
> org.apache.calcite.rex.RexSimplify.simplify(RexSimplify.java:270) at
> org.apache.calcite.rex.RexSimplify.simplifyUnknownAs(RexSimplify.java:235) at
> org.apache.calcite.rex.RexSimplify.simplifyPreservingType(RexSimplify.java:174)
> at
> org.apache.calcite.rel.rules.ReduceExpressionsRule.reduceExpressions(ReduceExpressionsRule.java:629)
> at
> org.apache.calcite.rel.rules.ReduceExpressionsRule$ProjectReduceExpressionsRule.onMatch(ReduceExpressionsRule.java:305)
> at
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:338)
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Resolved] (CALCITE-3887) Filter and Join conditions may not need to retain nullability during simplifications
[ https://issues.apache.org/jira/browse/CALCITE-3887?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Zoltan Haindrich resolved CALCITE-3887. --- Fix Version/s: 1.23.0 Resolution: Fixed > Filter and Join conditions may not need to retain nullability during > simplifications > > > Key: CALCITE-3887 > URL: https://issues.apache.org/jira/browse/CALCITE-3887 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: Zoltan Haindrich >Assignee: Zoltan Haindrich >Priority: Major > Labels: pull-request-available > Fix For: 1.23.0 > > Time Spent: 0.5h > Remaining Estimate: 0h > > Reported by [~icshuo]: that there are situation in which join conditions are > placed inside a redundant `CAST`. > https://mail-archives.apache.org/mod_mbox/calcite-dev/202003.mbox/%3CCABYySnvtrMFWaBqM7_FgKedXELdXeCqciRJV7G4MyYN07HUOnQ%40mail.gmail.com%3E -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3887) Filter and Join conditions may not need to retain nullability during simplifications
[ https://issues.apache.org/jira/browse/CALCITE-3887?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17101193#comment-17101193 ] Zoltan Haindrich commented on CALCITE-3887: --- Fixed in [cecfece459b0f5f155c0498811985e083813aa20|https://github.com/apache/calcite/commit/cecfece459b0f5f155c0498811985e083813aa20] > Filter and Join conditions may not need to retain nullability during > simplifications > > > Key: CALCITE-3887 > URL: https://issues.apache.org/jira/browse/CALCITE-3887 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: Zoltan Haindrich >Assignee: Zoltan Haindrich >Priority: Major > Labels: pull-request-available > Time Spent: 0.5h > Remaining Estimate: 0h > > Reported by [~icshuo]: that there are situation in which join conditions are > placed inside a redundant `CAST`. > https://mail-archives.apache.org/mod_mbox/calcite-dev/202003.mbox/%3CCABYySnvtrMFWaBqM7_FgKedXELdXeCqciRJV7G4MyYN07HUOnQ%40mail.gmail.com%3E -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3887) Filter and Join conditions may not need to retain nullability during simplifications
[ https://issues.apache.org/jira/browse/CALCITE-3887?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17100799#comment-17100799 ] Zoltan Haindrich commented on CALCITE-3887: --- RelBuilder was already doing this by calling the right methods - I've added some tests. > Filter and Join conditions may not need to retain nullability during > simplifications > > > Key: CALCITE-3887 > URL: https://issues.apache.org/jira/browse/CALCITE-3887 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: Zoltan Haindrich >Assignee: Zoltan Haindrich >Priority: Major > Labels: pull-request-available > Time Spent: 0.5h > Remaining Estimate: 0h > > Reported by [~icshuo]: that there are situation in which join conditions are > placed inside a redundant `CAST`. > https://mail-archives.apache.org/mod_mbox/calcite-dev/202003.mbox/%3CCABYySnvtrMFWaBqM7_FgKedXELdXeCqciRJV7G4MyYN07HUOnQ%40mail.gmail.com%3E -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Resolved] (CALCITE-3880) Add SortExchange support to RelFieldTrimmer
[
https://issues.apache.org/jira/browse/CALCITE-3880?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zoltan Haindrich resolved CALCITE-3880.
---
Fix Version/s: 1.23.0
Resolution: Fixed
fixed in
[8849b62e7baff84dcd4262f47809e87a38be7b10|https://github.com/apache/calcite/commit/8849b62e7baff84dcd4262f47809e87a38be7b10].
Thank you [~kkasa]!
> Add SortExchange support to RelFieldTrimmer
> ---
>
> Key: CALCITE-3880
> URL: https://issues.apache.org/jira/browse/CALCITE-3880
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.21.0
>Reporter: Krisztian Kasa
>Assignee: Krisztian Kasa
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.23.0
>
> Time Spent: 1.5h
> Remaining Estimate: 0h
>
> {code}
> LogicalProject(EMPNO=[$0])
> LogicalSortExchange(distribution=[hash[1]], collation=[[0]])
> LogicalProject(EMPNO=[$0], ENAME=[$1], DEPTNO=[$7])
> LogicalTableScan(table=[[scott, EMP]])
> {code}
> In this case the column `DEPTNO` is not required in LogicalSortExchange nor
> in the Project top on it.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3890) Infer IS NOT NULL predicate from join
[
https://issues.apache.org/jira/browse/CALCITE-3890?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17072624#comment-17072624
]
Zoltan Haindrich commented on CALCITE-3890:
---
[~Chunwei Lei]: in Hive we have a rule which somewhat does this
I was sure that a Calcite rule adds all the {{is not null}} conditions (and it
is)...but apparently it was not contributed back; and it's still only available
inside Hive.
it might worth taking a look at it:
https://github.com/apache/hive/blob/master/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveJoinAddNotNullRule.java
> Infer IS NOT NULL predicate from 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
>
> 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 and b.id is not null.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Created] (CALCITE-3887) Filter and Join conditions may not need to retain nullability during simplifications
Zoltan Haindrich created CALCITE-3887: - Summary: Filter and Join conditions may not need to retain nullability during simplifications Key: CALCITE-3887 URL: https://issues.apache.org/jira/browse/CALCITE-3887 Project: Calcite Issue Type: Improvement Components: core Reporter: Zoltan Haindrich Assignee: Zoltan Haindrich Reported by [~icshuo]: that there are situation in which join conditions are placed inside a redundant `CAST`. https://mail-archives.apache.org/mod_mbox/calcite-dev/202003.mbox/%3CCABYySnvtrMFWaBqM7_FgKedXELdXeCqciRJV7G4MyYN07HUOnQ%40mail.gmail.com%3E -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3852) RexSimplify doesn't simplify NOT EQUAL predicates
[
https://issues.apache.org/jira/browse/CALCITE-3852?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17057637#comment-17057637
]
Zoltan Haindrich commented on CALCITE-3852:
---
it would be better to rewrite it to {{AND(null,x is null)}} because in most
cases we are in unknownAsFalse mode;
making broader simplification could enable other parts of RexSimplify to
continue on with simplifying.
> RexSimplify doesn't simplify NOT EQUAL predicates
> -
>
> Key: CALCITE-3852
> URL: https://issues.apache.org/jira/browse/CALCITE-3852
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Chunwei Lei
>Assignee: Chunwei Lei
>Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Given x=9, RexSimplify does not simplify x != 9 to false.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3852) RexSimplify doesn't simplify NOT EQUAL predicates
[
https://issues.apache.org/jira/browse/CALCITE-3852?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17056969#comment-17056969
]
Zoltan Haindrich commented on CALCITE-3852:
---
I always wanted to address this; but forgot :)
[~Chunwei Lei], yes in case we only handle non-nullable variables; I think we
should - and we probably could:
it might be interesting to watch out for null-s during the following case:
{code}
AND(x=9, OR(x!=9, c) )
{code}
I think an option could be to rewrite {{x!=9}} into something which is false in
case x is not null; but null otherwise; something like:
{code}
AND(null,x is null)
{code}
> RexSimplify doesn't simplify NOT EQUAL predicates
> -
>
> Key: CALCITE-3852
> URL: https://issues.apache.org/jira/browse/CALCITE-3852
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Chunwei Lei
>Assignee: Chunwei Lei
>Priority: Major
>
> Given x=9, RexSimplify does not simplify x != 9 to false.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3842) RexSimplify doesn't simplify range predicates, as it looks at the operands in a particular order.
[
https://issues.apache.org/jira/browse/CALCITE-3842?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17056925#comment-17056925
]
Zoltan Haindrich commented on CALCITE-3842:
---
As [~Chunwei Lei] already explained the reason why it works this way is
because we rely on the "predicates" approach in case of ORs - which could do
deeper simplifications in the tree
meanwhile for ANDs simplification we also have a local algorithm which
analyzes all connected comparisions - and that way it's not dependent on the
order.
I think the "predicates" approach could be improved to handle this
independently of the input predicate order - if all the comparision are
processed in some specific order for ORs (and the other way around for ANDs)
* for '<' / '<=' decreasing order
* for '>'/ '>=' increasing order
Example:
{code:java}
# input
OR( a < 3 , a <= 5 , a < 10, a > -2 , a > -5 )
# ordered
OR( a < 10 , a <= 5 , a < 3 , a > -5 , a > -2 )
# result
OR( a < 10 , a > -5 )
{code}
I don't know if doing this could reach the same simplification power what we
already have with the local algorithm for ANDs; (IIRC there is some case in
which that could be better)
it would be awesome to have that - because then we could just remove the old
algorithm (which by the way reorders rexnodes)
...but it would clearly increase our simplification power for ORs; and it would
also use better predicates during AND simplifications
I guess the cost of this simplification optimization would be around doing 1
sort on the operands of the AND/OR
I think if we decide to do this; we should take care to keep the simplification
output in the same order as the input.
> RexSimplify doesn't simplify range predicates, as it looks at the operands in
> a particular order.
> -
>
> Key: CALCITE-3842
> URL: https://issues.apache.org/jira/browse/CALCITE-3842
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: anjali shrishrimal
>Priority: Major
>
> RexSimplify simplifies :
> {noformat}
> (a <= 10) OR (a <= 1) --> a <= 10{noformat}
> but does not simplify
> {noformat}
> (a <= 1) OR (a <= 10) {noformat}
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3803) Enhance RexSimplify to simplify 'a>1 or (a<3 and b)' to 'a>1 or b' if column a is not nullable
[
https://issues.apache.org/jira/browse/CALCITE-3803?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17039476#comment-17039476
]
Zoltan Haindrich commented on CALCITE-3803:
---
Looks good thank you [~Chunwei Lei] for looking into this and that you've kept
asking questions!
> Enhance RexSimplify to simplify 'a>1 or (a<3 and b)' to 'a>1 or b' if column
> a is not nullable
> --
>
> Key: CALCITE-3803
> URL: https://issues.apache.org/jira/browse/CALCITE-3803
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Chunwei Lei
>Assignee: Chunwei Lei
>Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> For {{a>1 or (a<3 and b)}}, with short-circuit, we know {{a<=1}} if {{a>1}}
> is false when column a is not nullable. Then {{(a<3 and b) can be simplified
> to b}}. Thus, {{a>1 or (a<3 and b)is simplified to a>1 or b.}}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3803) Enhance RexSimplify to simplify 'a>1 or (a<3 and b)' to 'a>1 or b' if column a is not nullable
[
https://issues.apache.org/jira/browse/CALCITE-3803?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17038434#comment-17038434
]
Zoltan Haindrich commented on CALCITE-3803:
---
oh...yes; you are right - we can do much better with a check for nullability.
> Enhance RexSimplify to simplify 'a>1 or (a<3 and b)' to 'a>1 or b' if column
> a is not nullable
> --
>
> Key: CALCITE-3803
> URL: https://issues.apache.org/jira/browse/CALCITE-3803
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Chunwei Lei
>Assignee: Chunwei Lei
>Priority: Major
>
> For {{a>1 or (a<3 and b)}}, with short-circuit, we know {{a<=1}} if {{a>1}}
> is false when column a is not nullable. Then {{(a<3 and b) can be simplified
> to b}}. Thus, {{a>1 or (a<3 and b)is simplified to a>1 or b.}}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3803) Enhance RexSimplify to simplify 'a>1 or (a<3 and b)' to 'a>1 or b' if column a is not nullable
[
https://issues.apache.org/jira/browse/CALCITE-3803?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17038427#comment-17038427
]
Zoltan Haindrich commented on CALCITE-3803:
---
I'll take a look at it again with a fresh mindset; right now it seems like I
was wrong(in the last comment I even flipped the '<' the wrong way
around)
> Enhance RexSimplify to simplify 'a>1 or (a<3 and b)' to 'a>1 or b' if column
> a is not nullable
> --
>
> Key: CALCITE-3803
> URL: https://issues.apache.org/jira/browse/CALCITE-3803
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Chunwei Lei
>Assignee: Chunwei Lei
>Priority: Major
>
> For {{a>1 or (a<3 and b)}}, with short-circuit, we know {{a<=1}} if {{a>1}}
> is false when column a is not nullable. Then {{(a<3 and b) can be simplified
> to b}}. Thus, {{a>1 or (a<3 and b)is simplified to a>1 or b.}}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3803) Enhance RexSimplify to simplify 'a>1 or (a<3 and b)' to 'a>1 or b' if column a is not nullable
[
https://issues.apache.org/jira/browse/CALCITE-3803?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17038216#comment-17038216
]
Zoltan Haindrich commented on CALCITE-3803:
---
some notes:
Right now we use predicates during simplification; let's say we have (a<1) as a
predicate; then from that we can conclude that a<0 is also true/etc.
{code}
(a<1) implies (a<0)
{code}
CALCITE-3192 have uncovered that introducing the {{not (a>1)}} predicate will
lead to incorrect results because:
{code}
not (a>1) == a<=1
(a<=1) implies (a<0) -- which is wrong; and it even not touched the example in
the description...
{code}
I think what would be correct is to introduce weak predicates; by weak I mean
they should be implied rather that used to imply other things.
{code}
weak not (a>1) == weak a<1
(a<3) implies a<1 -- which will do the simpliciation correctly
{code}
note: for simple boolean expressions it doesn't matter if we have have it as a
weak predicate or not...
> Enhance RexSimplify to simplify 'a>1 or (a<3 and b)' to 'a>1 or b' if column
> a is not nullable
> --
>
> Key: CALCITE-3803
> URL: https://issues.apache.org/jira/browse/CALCITE-3803
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Chunwei Lei
>Assignee: Chunwei Lei
>Priority: Major
>
> For {{a>1 or (a<3 and b)}}, with short-circuit, we know {{a<=1}} if {{a>1}}
> is false when column a is not nullable. Then {{(a<3 and b) can be simplified
> to b}}. Thus, {{a>1 or (a<3 and b)is simplified to a>1 or b.}}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3192) Simplification may weaken OR conditions containing inequalities
[
https://issues.apache.org/jira/browse/CALCITE-3192?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17038188#comment-17038188
]
Zoltan Haindrich commented on CALCITE-3192:
---
that's a great idea! I was thinking that something went wrong with this
patchor you wanted to understand it more or something :)
from the new jira it's clear that you are looking for an enhancement :)
> Simplification may weaken OR conditions containing inequalities
> ---
>
> Key: CALCITE-3192
> URL: https://issues.apache.org/jira/browse/CALCITE-3192
> Project: Calcite
> Issue Type: Bug
>Reporter: Jess Balint
>Assignee: Zoltan Haindrich
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.21.0
>
> Time Spent: 50m
> Remaining Estimate: 0h
>
> RexSimplify is transforming
> * {{OR(AND(>(999, $8), =($2, 'Franklin')), <(100, $8))}}
> * to {{OR(=($2, 'Franklin'), <(100, $8))}}
> the predicates are accumulated in {{simplifyOrTerms()}} but not discarded
> when iterating the second time
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3192) Simplification may weaken OR conditions containing inequalities
[
https://issues.apache.org/jira/browse/CALCITE-3192?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17031543#comment-17031543
]
Zoltan Haindrich commented on CALCITE-3192:
---
I see now; so you are after whether that issue would be fixed by this patch;
what happens without this patch is that:
{code}
(11) or (a<3 and a<=1 and b=2)
-- but...because of implementation details...the a<=1 is introduced as a
predicate; which could be described like:
(a>1) or ( a<3 and b=2 | when a<=1)
-- which is rewritten to (because a<=1 was a predicate)
(a>1) or (b=2)
{code}
...in then end it will answer the a=null case incorrectly as you have described
I hope this helps clean up things
> Simplification may weaken OR conditions containing inequalities
> ---
>
> Key: CALCITE-3192
> URL: https://issues.apache.org/jira/browse/CALCITE-3192
> Project: Calcite
> Issue Type: Bug
>Reporter: Jess Balint
>Assignee: Zoltan Haindrich
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.21.0
>
> Time Spent: 50m
> Remaining Estimate: 0h
>
> RexSimplify is transforming
> * {{OR(AND(>(999, $8), =($2, 'Franklin')), <(100, $8))}}
> * to {{OR(=($2, 'Franklin'), <(100, $8))}}
> the predicates are accumulated in {{simplifyOrTerms()}} but not discarded
> when iterating the second time
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3192) Simplification may weaken OR conditions containing inequalities
[
https://issues.apache.org/jira/browse/CALCITE-3192?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17028943#comment-17028943
]
Zoltan Haindrich commented on CALCITE-3192:
---
I think I might be interpreting your example a little bit differently; let me
try to convince you that I think your expression is null (in case a is null and
b=2)
{code}
(1 null
{code}
null or (null is null)
{code}
null or true => true
As a result of this patch we don't anymore allow '<' during this
simplification step; so right now RexSimplify should not make any tricky with
'a>1' or similar to that...
If you do have a case in which the simplification still makes an error, please
share it
> Simplification may weaken OR conditions containing inequalities
> ---
>
> Key: CALCITE-3192
> URL: https://issues.apache.org/jira/browse/CALCITE-3192
> Project: Calcite
> Issue Type: Bug
>Reporter: Jess Balint
>Assignee: Zoltan Haindrich
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.21.0
>
> Time Spent: 50m
> Remaining Estimate: 0h
>
> RexSimplify is transforming
> * {{OR(AND(>(999, $8), =($2, 'Franklin')), <(100, $8))}}
> * to {{OR(=($2, 'Franklin'), <(100, $8))}}
> the predicates are accumulated in {{simplifyOrTerms()}} but not discarded
> when iterating the second time
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3464) RexSimplify simplifies plan having filter with NULL to empty values
[
https://issues.apache.org/jira/browse/CALCITE-3464?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16963839#comment-16963839
]
Zoltan Haindrich commented on CALCITE-3464:
---
[~zabetak] you are fast :D
[~yanlin-Lynn] if you want to check that a field is null; use "x IS NULL" there
is also spaceship (<=>) which compares null as equal
> RexSimplify simplifies plan having filter with NULL to empty values
> ---
>
> Key: CALCITE-3464
> URL: https://issues.apache.org/jira/browse/CALCITE-3464
> Project: Calcite
> Issue Type: Bug
>Reporter: Wang Yanlin
>Priority: Major
>
> When filter by comparing to null in sql, the plan will get empty result
> {code:java}
> @Test public void testSimplifyItemEqualNull() {
> String query = "select * from sales.customer as t1 where name = NULL";
> sql(query)
> .withTester(t -> createDynamicTester())
> .withRule(ReduceExpressionsRule.FILTER_INSTANCE)
> .check();
> }
> {code}
> The plan after optimization is like this
> {code:java}
> LogicalProject(**=[$1])
> LogicalValues(tuples=[[]])
> {code}
> The optimized plan will get empty result, is this the result we want?
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3368) Some problems simplifying ‘expression IS NULL’
[
https://issues.apache.org/jira/browse/CALCITE-3368?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16949266#comment-16949266
]
Zoltan Haindrich commented on CALCITE-3368:
---
Could you please change the summary...I think we could be more specific than
"some problems"
> Some problems simplifying ‘expression IS NULL’
> --
>
> Key: CALCITE-3368
> URL: https://issues.apache.org/jira/browse/CALCITE-3368
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.21.0
>Reporter: Leonard Xu
>Assignee: Leonard Xu
>Priority: Major
> Labels: pull-request-available
> Time Spent: 40m
> Remaining Estimate: 0h
>
> 'is null' expression in SQL may be optimized incorrectly in the underlying
> implementation.
>
> When I write a Fink SQL to test overflow just like
> {code:java}
> select
>case when (f0 + f1) is null then 'null' else 'not null' end
> from testTable
> {code}
> , I found expression '(f0 + f1) is null ' has been optimized by Calcite, and
> the optimization may be incorrect.
>
> The underlying implementation is that Calcite's simplification logic of
> isNull expression in SQL will convert from
> *"f(operand0, operand1) IS NULL"* to
> *"operand0 IS NULL OR operand1 IS NULL"* if the Policy of RexNode‘s SqlKind
> is ANY。
> This simplification leads to the expression will not calculate the real
> value of *f(operand0, operand1)* (eg.. '(f0 + f1)' in my case ），but '(f0 +
> f1)' maybe overflows after operation.
> {code:java}
> //org.apache.calcite.rex.RexSimplify.java
> private RexNode simplifyIsNull(RexNode a) {
> // Simplify the argument first,
> // call ourselves recursively to see whether we can make more progress.
> // For example, given
> // "(CASE WHEN FALSE THEN 1 ELSE 2) IS NULL" we first simplify the
> // argument to "2", and only then we can simplify "2 IS NULL" to "FALSE".
> a = simplify(a, UNKNOWN);
> if (!a.getType().isNullable() && isSafeExpression(a)) {
> return rexBuilder.makeLiteral(false);
> }
> if (RexUtil.isNull(a)) {
> return rexBuilder.makeLiteral(true);
> }
> if (a.getKind() == SqlKind.CAST) {
> return null;
> }
> switch (Strong.policy(a.getKind())) {
> case NOT_NULL:
> return rexBuilder.makeLiteral(false);
> case ANY:
> // "f" is a strong operator, so "f(operand0, operand1) IS NULL" simplifies
> // to "operand0 IS NULL OR operand1 IS NULL"
> final List operands = new ArrayList<>();
> for (RexNode operand : ((RexCall) a).getOperands()) {
> final RexNode simplified = simplifyIsNull(operand);
> if (simplified == null) {
> operands.add(
> rexBuilder.makeCall(SqlStdOperatorTable.IS_NULL, operand));
> } else {
> operands.add(simplified);
> }
> }
> return RexUtil.composeDisjunction(rexBuilder, operands, false);
> case AS_IS:
> default:
> return null;
> }
> }{code}
> And most of calculating SqlKinds are assigned *Policy.ANY* at present.
> {code:java}
> //org.apache.calcite.plan.Strong.java
> public static Policy policy(SqlKind kind) {
> return MAP.getOrDefault(kind, Policy.AS_IS);
> }
>
> map.put(SqlKind.PLUS, Policy.ANY);
> map.put(SqlKind.PLUS_PREFIX, Policy.ANY);
> map.put(SqlKind.MINUS, Policy.ANY);
> map.put(SqlKind.MINUS_PREFIX, Policy.ANY);
> map.put(SqlKind.TIMES, Policy.ANY);
> map.put(SqlKind.DIVIDE, Policy.ANY);
> * that operator evaluates to null. */
> public enum Policy {
> /** This kind of expression is never null. No need to look at its arguments,
>* if it has any. */
> NOT_NULL,
> /** This kind of expression has its own particular rules about whether it
>* is null. */
> CUSTOM,
> /** This kind of expression is null if and only if at least one of its
>* arguments is null. */
> ANY,
> /** This kind of expression may be null. There is no way to rewrite. */
> AS_IS,
> }{code}
>
> It may be an obvious nonequivalent simplification in SQL. And this issue come
> from Flink （FLINK-14030）.
> [~danny0405]， Could you have a look at this?
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3368) Some problems simplifying ‘expression IS NULL’
[
https://issues.apache.org/jira/browse/CALCITE-3368?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16949259#comment-16949259
]
Zoltan Haindrich commented on CALCITE-3368:
---
I also think that we should probably add some more control to these kind of
things...
We could probably get back kinda the same simplifiaction power if we could get
min/max values for every column - and using range logics for the nodes; but
that would
I left some comments on the RB; I can understand that if we need
{{isAlwaysTrue()}} changes - but that could have serious consequences...
> Some problems simplifying ‘expression IS NULL’
> --
>
> Key: CALCITE-3368
> URL: https://issues.apache.org/jira/browse/CALCITE-3368
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.21.0
>Reporter: Leonard Xu
>Assignee: Leonard Xu
>Priority: Major
> Labels: pull-request-available
> Time Spent: 40m
> Remaining Estimate: 0h
>
> 'is null' expression in SQL may be optimized incorrectly in the underlying
> implementation.
>
> When I write a Fink SQL to test overflow just like
> {code:java}
> select
>case when (f0 + f1) is null then 'null' else 'not null' end
> from testTable
> {code}
> , I found expression '(f0 + f1) is null ' has been optimized by Calcite, and
> the optimization may be incorrect.
>
> The underlying implementation is that Calcite's simplification logic of
> isNull expression in SQL will convert from
> *"f(operand0, operand1) IS NULL"* to
> *"operand0 IS NULL OR operand1 IS NULL"* if the Policy of RexNode‘s SqlKind
> is ANY。
> This simplification leads to the expression will not calculate the real
> value of *f(operand0, operand1)* (eg.. '(f0 + f1)' in my case ），but '(f0 +
> f1)' maybe overflows after operation.
> {code:java}
> //org.apache.calcite.rex.RexSimplify.java
> private RexNode simplifyIsNull(RexNode a) {
> // Simplify the argument first,
> // call ourselves recursively to see whether we can make more progress.
> // For example, given
> // "(CASE WHEN FALSE THEN 1 ELSE 2) IS NULL" we first simplify the
> // argument to "2", and only then we can simplify "2 IS NULL" to "FALSE".
> a = simplify(a, UNKNOWN);
> if (!a.getType().isNullable() && isSafeExpression(a)) {
> return rexBuilder.makeLiteral(false);
> }
> if (RexUtil.isNull(a)) {
> return rexBuilder.makeLiteral(true);
> }
> if (a.getKind() == SqlKind.CAST) {
> return null;
> }
> switch (Strong.policy(a.getKind())) {
> case NOT_NULL:
> return rexBuilder.makeLiteral(false);
> case ANY:
> // "f" is a strong operator, so "f(operand0, operand1) IS NULL" simplifies
> // to "operand0 IS NULL OR operand1 IS NULL"
> final List operands = new ArrayList<>();
> for (RexNode operand : ((RexCall) a).getOperands()) {
> final RexNode simplified = simplifyIsNull(operand);
> if (simplified == null) {
> operands.add(
> rexBuilder.makeCall(SqlStdOperatorTable.IS_NULL, operand));
> } else {
> operands.add(simplified);
> }
> }
> return RexUtil.composeDisjunction(rexBuilder, operands, false);
> case AS_IS:
> default:
> return null;
> }
> }{code}
> And most of calculating SqlKinds are assigned *Policy.ANY* at present.
> {code:java}
> //org.apache.calcite.plan.Strong.java
> public static Policy policy(SqlKind kind) {
> return MAP.getOrDefault(kind, Policy.AS_IS);
> }
>
> map.put(SqlKind.PLUS, Policy.ANY);
> map.put(SqlKind.PLUS_PREFIX, Policy.ANY);
> map.put(SqlKind.MINUS, Policy.ANY);
> map.put(SqlKind.MINUS_PREFIX, Policy.ANY);
> map.put(SqlKind.TIMES, Policy.ANY);
> map.put(SqlKind.DIVIDE, Policy.ANY);
> * that operator evaluates to null. */
> public enum Policy {
> /** This kind of expression is never null. No need to look at its arguments,
>* if it has any. */
> NOT_NULL,
> /** This kind of expression has its own particular rules about whether it
>* is null. */
> CUSTOM,
> /** This kind of expression is null if and only if at least one of its
>* arguments is null. */
> ANY,
> /** This kind of expression may be null. There is no way to rewrite. */
> AS_IS,
> }{code}
>
> It may be an obvious nonequivalent simplification in SQL. And this issue come
> from Flink （FLINK-14030）.
> [~danny0405]， Could you have a look at this?
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3368) Some problems simplifying ‘expression IS NULL’
[
https://issues.apache.org/jira/browse/CALCITE-3368?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16946753#comment-16946753
]
Zoltan Haindrich commented on CALCITE-3368:
---
iiuc the issue here is that *(max+max) is null* should raise an exception at
runtime - rather than transforming it to *max is null or max is null*
I think the Strongness classifications of the operators are correct but we
should mark the - / + / * operators as unsafe
> Some problems simplifying ‘expression IS NULL’
> --
>
> Key: CALCITE-3368
> URL: https://issues.apache.org/jira/browse/CALCITE-3368
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.21.0
>Reporter: Leonard Xu
>Assignee: Danny Chen
>Priority: Major
>
> 'is null' expression in SQL may be optimized incorrectly in the underlying
> implementation.
>
> When I write a Fink SQL to test overflow just like
> {code:java}
> select
>case when (f0 + f1) is null then 'null' else 'not null' end
> from testTable
> {code}
> , I found expression '(f0 + f1) is null ' has been optimized by Calcite, and
> the optimization may be incorrect.
>
> The underlying implementation is that Calcite's simplification logic of
> isNull expression in SQL will convert from
> *"f(operand0, operand1) IS NULL"* to
> *"operand0 IS NULL OR operand1 IS NULL"* if the Policy of RexNode‘s SqlKind
> is ANY。
> This simplification leads to the expression will not calculate the real
> value of *f(operand0, operand1)* (eg.. '(f0 + f1)' in my case ），but '(f0 +
> f1)' maybe overflows after operation.
> {code:java}
> //org.apache.calcite.rex.RexSimplify.java
> private RexNode simplifyIsNull(RexNode a) {
> // Simplify the argument first,
> // call ourselves recursively to see whether we can make more progress.
> // For example, given
> // "(CASE WHEN FALSE THEN 1 ELSE 2) IS NULL" we first simplify the
> // argument to "2", and only then we can simplify "2 IS NULL" to "FALSE".
> a = simplify(a, UNKNOWN);
> if (!a.getType().isNullable() && isSafeExpression(a)) {
> return rexBuilder.makeLiteral(false);
> }
> if (RexUtil.isNull(a)) {
> return rexBuilder.makeLiteral(true);
> }
> if (a.getKind() == SqlKind.CAST) {
> return null;
> }
> switch (Strong.policy(a.getKind())) {
> case NOT_NULL:
> return rexBuilder.makeLiteral(false);
> case ANY:
> // "f" is a strong operator, so "f(operand0, operand1) IS NULL" simplifies
> // to "operand0 IS NULL OR operand1 IS NULL"
> final List operands = new ArrayList<>();
> for (RexNode operand : ((RexCall) a).getOperands()) {
> final RexNode simplified = simplifyIsNull(operand);
> if (simplified == null) {
> operands.add(
> rexBuilder.makeCall(SqlStdOperatorTable.IS_NULL, operand));
> } else {
> operands.add(simplified);
> }
> }
> return RexUtil.composeDisjunction(rexBuilder, operands, false);
> case AS_IS:
> default:
> return null;
> }
> }{code}
> And most of calculating SqlKinds are assigned *Policy.ANY* at present.
> {code:java}
> //org.apache.calcite.plan.Strong.java
> public static Policy policy(SqlKind kind) {
> return MAP.getOrDefault(kind, Policy.AS_IS);
> }
>
> map.put(SqlKind.PLUS, Policy.ANY);
> map.put(SqlKind.PLUS_PREFIX, Policy.ANY);
> map.put(SqlKind.MINUS, Policy.ANY);
> map.put(SqlKind.MINUS_PREFIX, Policy.ANY);
> map.put(SqlKind.TIMES, Policy.ANY);
> map.put(SqlKind.DIVIDE, Policy.ANY);
> * that operator evaluates to null. */
> public enum Policy {
> /** This kind of expression is never null. No need to look at its arguments,
>* if it has any. */
> NOT_NULL,
> /** This kind of expression has its own particular rules about whether it
>* is null. */
> CUSTOM,
> /** This kind of expression is null if and only if at least one of its
>* arguments is null. */
> ANY,
> /** This kind of expression may be null. There is no way to rewrite. */
> AS_IS,
> }{code}
>
> It may be an obvious nonequivalent simplification in SQL. And this issue come
> from Flink （FLINK-14030）.
> [~danny0405]， Could you have a look at this?
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Resolved] (CALCITE-3215) Simplification may have not fully simplified IS NOT NULL expressions
[
https://issues.apache.org/jira/browse/CALCITE-3215?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zoltan Haindrich resolved CALCITE-3215.
---
Resolution: Fixed
Fix Version/s: 1.21.0
fixed in
[1e999374f3a40c26d1cb3469211f10cd2fdc0622|https://github.com/apache/calcite/commit/1e999374f3a40c26d1cb3469211f10cd2fdc0622]
> Simplification may have not fully simplified IS NOT NULL expressions
>
>
> Key: CALCITE-3215
> URL: https://issues.apache.org/jira/browse/CALCITE-3215
> Project: Calcite
> Issue Type: Bug
>Reporter: Zoltan Haindrich
>Assignee: Zoltan Haindrich
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.21.0
>
> Time Spent: 40m
> Remaining Estimate: 0h
>
> CALCITE-2929 have added a safety check to avoid simplifying problematic cases.
> The safety check apparently misses some kinds, for example: {{UNARY_PLUS}}
> {code}
> @Test public void testIsNullSimplificationWithUnaryPlus() {
> RexNode expr =
> isNotNull(coalesce(unaryPlus(vInt(1)), vIntNotNull(0)));
> RexNode s = simplify.simplifyUnknownAs(expr, RexUnknownAs.UNKNOWN);
> assertThat(expr.isAlwaysTrue(), is(true));
> assertThat(s, is(trueLiteral));
> }
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
[jira] [Resolved] (CALCITE-3192) Simplification may weaken OR conditions containing inequalities
[
https://issues.apache.org/jira/browse/CALCITE-3192?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zoltan Haindrich resolved CALCITE-3192.
---
Resolution: Fixed
Assignee: Zoltan Haindrich
fixed in
[f7762b6a7cd85711a8cc6d7c7174dedca22bdcd2|https://github.com/apache/calcite/commit/f7762b6a7cd85711a8cc6d7c7174dedca22bdcd2]
> Simplification may weaken OR conditions containing inequalities
> ---
>
> Key: CALCITE-3192
> URL: https://issues.apache.org/jira/browse/CALCITE-3192
> Project: Calcite
> Issue Type: Bug
>Reporter: Jess Balint
>Assignee: Zoltan Haindrich
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.21.0
>
> Time Spent: 50m
> Remaining Estimate: 0h
>
> RexSimplify is transforming
> * {{OR(AND(>(999, $8), =($2, 'Franklin')), <(100, $8))}}
> * to {{OR(=($2, 'Franklin'), <(100, $8))}}
> the predicates are accumulated in {{simplifyOrTerms()}} but not discarded
> when iterating the second time
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
[jira] [Updated] (CALCITE-3192) Simplification may weaken OR conditions containing inequalities
[
https://issues.apache.org/jira/browse/CALCITE-3192?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zoltan Haindrich updated CALCITE-3192:
--
Summary: Simplification may weaken OR conditions containing inequalities
(was: Simplification of OR incorrectly weaks condition)
> Simplification may weaken OR conditions containing inequalities
> ---
>
> Key: CALCITE-3192
> URL: https://issues.apache.org/jira/browse/CALCITE-3192
> Project: Calcite
> Issue Type: Bug
>Reporter: Jess Balint
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.21.0
>
> Time Spent: 20m
> Remaining Estimate: 0h
>
> RexSimplify is transforming
> * {{OR(AND(>(999, $8), =($2, 'Franklin')), <(100, $8))}}
> * to {{OR(=($2, 'Franklin'), <(100, $8))}}
> the predicates are accumulated in {{simplifyOrTerms()}} but not discarded
> when iterating the second time
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
[jira] [Updated] (CALCITE-3192) Simplification of OR incorrectly weaks condition
[
https://issues.apache.org/jira/browse/CALCITE-3192?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zoltan Haindrich updated CALCITE-3192:
--
Summary: Simplification of OR incorrectly weaks condition (was: Simplify
OR incorrectly weaks condition)
> Simplification of OR incorrectly weaks condition
> -
>
> Key: CALCITE-3192
> URL: https://issues.apache.org/jira/browse/CALCITE-3192
> Project: Calcite
> Issue Type: Bug
>Reporter: Jess Balint
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.21.0
>
> Time Spent: 20m
> Remaining Estimate: 0h
>
> RexSimplify is transforming
> * {{OR(AND(>(999, $8), =($2, 'Franklin')), <(100, $8))}}
> * to {{OR(=($2, 'Franklin'), <(100, $8))}}
> the predicates are accumulated in {{simplifyOrTerms()}} but not discarded
> when iterating the second time
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
[jira] [Commented] (CALCITE-3215) Simplification may have not fully simplified IS NOT NULL expressions
[
https://issues.apache.org/jira/browse/CALCITE-3215?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16895877#comment-16895877
]
Zoltan Haindrich commented on CALCITE-3215:
---
unfortunately no:
* division is not safe, because of division by zero at runtime; however it is
strong
* for cast; some parsing exception may occur at runtime; however it is strong,
since cast(null as x) doesn't really do anything...
I think both strong; and safe properties should be moved to the "kind" ; to
help decide to do something based on the property of the kind.
> Simplification may have not fully simplified IS NOT NULL expressions
>
>
> Key: CALCITE-3215
> URL: https://issues.apache.org/jira/browse/CALCITE-3215
> Project: Calcite
> Issue Type: Bug
>Reporter: Zoltan Haindrich
>Assignee: Zoltan Haindrich
>Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> CALCITE-2929 have added a safety check to avoid simplifying problematic cases.
> The safety check apparently misses some kinds, for example: {{UNARY_PLUS}}
> {code}
> @Test public void testIsNullSimplificationWithUnaryPlus() {
> RexNode expr =
> isNotNull(coalesce(unaryPlus(vInt(1)), vIntNotNull(0)));
> RexNode s = simplify.simplifyUnknownAs(expr, RexUnknownAs.UNKNOWN);
> assertThat(expr.isAlwaysTrue(), is(true));
> assertThat(s, is(trueLiteral));
> }
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
[jira] [Updated] (CALCITE-3215) Simplification may have not fully simplified IS NOT NULL expressions
[
https://issues.apache.org/jira/browse/CALCITE-3215?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zoltan Haindrich updated CALCITE-3215:
--
Summary: Simplification may have not fully simplified IS NOT NULL
expressions (was: Simplification may not fully simplify IsNotNull expressions)
> Simplification may have not fully simplified IS NOT NULL expressions
>
>
> Key: CALCITE-3215
> URL: https://issues.apache.org/jira/browse/CALCITE-3215
> Project: Calcite
> Issue Type: Bug
>Reporter: Zoltan Haindrich
>Assignee: Zoltan Haindrich
>Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> CALCITE-2929 have added a safety check to avoid simplifying problematic cases.
> The safety check apparently misses some kinds, for example: {{UNARY_PLUS}}
> {code}
> @Test public void testIsNullSimplificationWithUnaryPlus() {
> RexNode expr =
> isNotNull(coalesce(unaryPlus(vInt(1)), vIntNotNull(0)));
> RexNode s = simplify.simplifyUnknownAs(expr, RexUnknownAs.UNKNOWN);
> assertThat(expr.isAlwaysTrue(), is(true));
> assertThat(s, is(trueLiteral));
> }
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
[jira] [Created] (CALCITE-3215) Simplification may not fully simplify IsNotNull expressions
Zoltan Haindrich created CALCITE-3215:
-
Summary: Simplification may not fully simplify IsNotNull
expressions
Key: CALCITE-3215
URL: https://issues.apache.org/jira/browse/CALCITE-3215
Project: Calcite
Issue Type: Bug
Reporter: Zoltan Haindrich
Assignee: Zoltan Haindrich
CALCITE-2929 have added a safety check to avoid simplifying problematic cases.
The safety check apparently misses some kinds, for example: {{UNARY_PLUS}}
{code}
@Test public void testIsNullSimplificationWithUnaryPlus() {
RexNode expr =
isNotNull(coalesce(unaryPlus(vInt(1)), vIntNotNull(0)));
RexNode s = simplify.simplifyUnknownAs(expr, RexUnknownAs.UNKNOWN);
assertThat(expr.isAlwaysTrue(), is(true));
assertThat(s, is(trueLiteral));
}
{code}
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
[jira] [Comment Edited] (CALCITE-3192) Simplify OR incorrectly weaks condition
[
https://issues.apache.org/jira/browse/CALCITE-3192?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16892865#comment-16892865
]
Zoltan Haindrich edited comment on CALCITE-3192 at 7/25/19 3:25 PM:
[~jbal...@gmail.com] throwing away the predicates might save this case; but may
probably not fix all cases; and the usage of this predicates is usefull; could
help further symplify cases - so I try to save it :)
I've further looked into this; and the main problem seems to be that order
relations are antisymmetric.
An expression could be only used as a predicate during an or simplification if
the relation is not antisymmetric.
In case of:
{code}
aRx or ( xRb and c )
{code}
when we use {{not(aRx)}} as a predicate; if antisymmetry applies it could
become {{xRa}}
but note that in case of {{(xRa and xRb)}} we do not know anything about the
relation of a and b.
R could be < or some other inequality operator.
I think the requirement to apply CALCITE-2247 logic is that relation in the
predicate must have "partial equvivalence" properties.
I'm trying to save this feature in [this
branch|https://github.com/kgyrtkirk/calcite/tree/3192-simplifyor-erro].
was (Author: kgyrtkirk):
[~jbal...@gmail.com] throwing away the predicates might save this case; but may
probably not fix all cases; and the usage of this predicates is usefull; could
help further symplify cases - so I try to save it :)
I've further looked into this; and the main problem seems to be that order
relations are antisymmetric.
An expression could be only used as a predicate during an or simplification if
the relation is not antisymmetric.
In case of:
{code}
aRx or ( xRb and c )
{code}
when we use {{not(aRx)}} as a predicate; if antisymmetry applies it could
become {{xRa}}
but note that in case of {{(xRa and xRb)}} we do not know anything about the
relation of a and b.
R could be < or some other inequality operator.
I think the requirement to apply CALCITE-2247 logic is that relation in the
predicate must have "partial equvivalence" properties.
> Simplify OR incorrectly weaks condition
> ---
>
> Key: CALCITE-3192
> URL: https://issues.apache.org/jira/browse/CALCITE-3192
> Project: Calcite
> Issue Type: Bug
>Reporter: Jess Balint
>Priority: Major
> Fix For: 1.21.0
>
>
> RexSimplify is transforming
> * {{OR(AND(>(999, $8), =($2, 'Franklin')), <(100, $8))}}
> * to {{OR(=($2, 'Franklin'), <(100, $8))}}
> the predicates are accumulated in {{simplifyOrTerms()}} but not discarded
> when iterating the second time
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
[jira] [Commented] (CALCITE-3192) Simplify OR incorrectly weaks condition
[
https://issues.apache.org/jira/browse/CALCITE-3192?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16892865#comment-16892865
]
Zoltan Haindrich commented on CALCITE-3192:
---
[~jbal...@gmail.com] throwing away the predicates might save this case; but may
probably not fix all cases; and the usage of this predicates is usefull; could
help further symplify cases - so I try to save it :)
I've further looked into this; and the main problem seems to be that order
relations are antisymmetric.
An expression could be only used as a predicate during an or simplification if
the relation is not antisymmetric.
In case of:
{code}
aRx or ( xRb and c )
{code}
when we use {{not(aRx)}} as a predicate; if antisymmetry applies it could
become {{xRa}}
but note that in case of {{(xRa and xRb)}} we do not know anything about the
relation of a and b.
R could be < or some other inequality operator.
I think the requirement to apply CALCITE-2247 logic is that relation in the
predicate must have "partial equvivalence" properties.
> Simplify OR incorrectly weaks condition
> ---
>
> Key: CALCITE-3192
> URL: https://issues.apache.org/jira/browse/CALCITE-3192
> Project: Calcite
> Issue Type: Bug
>Reporter: Jess Balint
>Priority: Major
> Fix For: 1.21.0
>
>
> RexSimplify is transforming
> * {{OR(AND(>(999, $8), =($2, 'Franklin')), <(100, $8))}}
> * to {{OR(=($2, 'Franklin'), <(100, $8))}}
> the predicates are accumulated in {{simplifyOrTerms()}} but not discarded
> when iterating the second time
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
[jira] [Comment Edited] (CALCITE-3192) Simplify OR incorrectly weaks condition
[
https://issues.apache.org/jira/browse/CALCITE-3192?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16883900#comment-16883900
]
Zoltan Haindrich edited comment on CALCITE-3192 at 7/12/19 3:17 PM:
{code}
z or ( x and y )
z or ( x and y and !z )
{code}
part of the problem is that !z is retained as a predicate; so its not there
physically
but when (y -> !z) is true then y is removed incorrectly
the fully correct way would be to add this things to the expression; but that
would just create a lot of redundant noise...I'll think about it
I think we should fix this in 1.21 or remove this logic
was (Author: kgyrtkirk):
{code}
z or ( x and y )
z or ( x and y and !z )
{code}
part of the problem is that !z is retained as a predicate; so its not there
physically
but when (y -> !z) is true then y is removed incorrectly
the fully correct way would be to add this things to the expression; but that
would just create a lot of redundant noise...I'll think about it
> Simplify OR incorrectly weaks condition
> ---
>
> Key: CALCITE-3192
> URL: https://issues.apache.org/jira/browse/CALCITE-3192
> Project: Calcite
> Issue Type: Bug
>Reporter: Jess Balint
>Priority: Major
> Fix For: 1.21.0
>
>
> RexSimplify is transforming
> * {{OR(AND(>(999, $8), =($2, 'Franklin')), <(100, $8))}}
> * to {{OR(=($2, 'Franklin'), <(100, $8))}}
> the predicates are accumulated in {{simplifyOrTerms()}} but not discarded
> when iterating the second time
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
[jira] [Updated] (CALCITE-3192) Simplify OR incorrectly weaks condition
[
https://issues.apache.org/jira/browse/CALCITE-3192?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zoltan Haindrich updated CALCITE-3192:
--
Fix Version/s: 1.21.0
> Simplify OR incorrectly weaks condition
> ---
>
> Key: CALCITE-3192
> URL: https://issues.apache.org/jira/browse/CALCITE-3192
> Project: Calcite
> Issue Type: Bug
>Reporter: Jess Balint
>Priority: Major
> Fix For: 1.21.0
>
>
> RexSimplify is transforming
> * {{OR(AND(>(999, $8), =($2, 'Franklin')), <(100, $8))}}
> * to {{OR(=($2, 'Franklin'), <(100, $8))}}
> the predicates are accumulated in {{simplifyOrTerms()}} but not discarded
> when iterating the second time
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
[jira] [Commented] (CALCITE-3192) Simplify OR incorrectly weaks condition
[
https://issues.apache.org/jira/browse/CALCITE-3192?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16883900#comment-16883900
]
Zoltan Haindrich commented on CALCITE-3192:
---
{code}
z or ( x and y )
z or ( x and y and !z )
{code}
part of the problem is that !z is retained as a predicate; so its not there
physically
but when (y -> !z) is true then y is removed incorrectly
the fully correct way would be to add this things to the expression; but that
would just create a lot of redundant noise...I'll think about it
> Simplify OR incorrectly weaks condition
> ---
>
> Key: CALCITE-3192
> URL: https://issues.apache.org/jira/browse/CALCITE-3192
> Project: Calcite
> Issue Type: Bug
>Reporter: Jess Balint
>Priority: Major
>
> RexSimplify is transforming
> * {{OR(AND(>(999, $8), =($2, 'Franklin')), <(100, $8))}}
> * to {{OR(=($2, 'Franklin'), <(100, $8))}}
> the predicates are accumulated in {{simplifyOrTerms()}} but not discarded
> when iterating the second time
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
[jira] [Updated] (CALCITE-2955) SQL queries after first query in same statement have no results returned
[
https://issues.apache.org/jira/browse/CALCITE-2955?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zoltan Haindrich updated CALCITE-2955:
--
Fix Version/s: (was: 1.19.0)
> SQL queries after first query in same statement have no results returned
>
>
> Key: CALCITE-2955
> URL: https://issues.apache.org/jira/browse/CALCITE-2955
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.18.0
>Reporter: leesf
>Assignee: leesf
>Priority: Major
> Labels: pull-request-available
> Time Spent: 50m
> Remaining Estimate: 0h
>
> When integrate avatica with calcite, when using same statement, except for
> the first sql query, other sql queries have no result to return.
> The following example
> {code:java}
> private static void testMultiQueryWithSameStatement(Connection connection)
> throws Exception {
> // get avatica remote connection
> Connection connection = getRemoteConnection();
> String sql = "select * from mysql.jobs";
> ResultSet rs;
> Statement st;
> st = connection.createStatement();
> System.out.println("first
> query=");
> rs = st.executeQuery(sql);
> ResultSetMetaData rsmd = rs.getMetaData();
> int columnNum = rsmd.getColumnCount();
> for (int i = 1; i <= columnNum; i++) {
> System.out.print(rsmd.getColumnName(i) + "\t");
> }
> System.out.println();
> while (rs.next()) {
> for (int i = 1; i <= columnNum; i++) {
> System.out.print(rs.getString(i) + "\t");
> }
> System.out.println();
> }
> System.out.println("second
> query=");
> rs = st.executeQuery(sql);
> while (rs.next()) {
> for (int i = 1; i <= columnNum; i++) {
> System.out.print(rs.getString(i) + "\t");
> }
> System.out.println();
> }
> }
> {code}
> The result would be like this. The first query has result while the second
> query has no result returned.
> {code:java}
> first query=
> id name
> 1 abc
> second query=
> {code}
> What's more, use avatica alone and don't integrate with calcite, then query
> sql twice will return normal results.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2829) Fix RexSimplify#processRange cast issue
[ https://issues.apache.org/jira/browse/CALCITE-2829?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16801822#comment-16801822 ] Zoltan Haindrich commented on CALCITE-2829: --- [~Juhwan] I understand that this issue surfaces during simplification; but I feel that the fix shouldn't reside in "simplifyComparision" as I would guess that the issue might arise elsewhere as well.. instead; I would expect that this should be fixed where the "comparison" node is constructed (right now I would guess some RexBuilder part ; but I could be easily wrong) > Fix RexSimplify#processRange cast issue > --- > > Key: CALCITE-2829 > URL: https://issues.apache.org/jira/browse/CALCITE-2829 > Project: Calcite > Issue Type: Bug >Reporter: Siddharth Teotia >Assignee: Juhwan Kim >Priority: Major > Labels: pull-request-available > Time Spent: 20m > Remaining Estimate: 0h > > Range expressions like = 'literal' AND < 'literal' trigger > ClassCastException as literal are implicitly casted differently between =/<> > operators and other comparison operators. Apply the same casting rules for > comparison to =/<> calls when processing ranges, so that all the terms have > the same type for literals. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-2929) Simplification of IS NULL checks are incorrectly assuming that CAST-s are possible
[
https://issues.apache.org/jira/browse/CALCITE-2929?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16796088#comment-16796088
]
Zoltan Haindrich commented on CALCITE-2929:
---
I think I now see your point: Strong is only about null-s and removing CAST
from it because of safetiness might not be the right move.
I'll modify the pr
> Simplification of IS NULL checks are incorrectly assuming that CAST-s are
> possible
> --
>
> Key: CALCITE-2929
> URL: https://issues.apache.org/jira/browse/CALCITE-2929
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.18.0
>Reporter: Zoltan Haindrich
>Assignee: Zoltan Haindrich
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.19.0
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Example:
> {code}
> with ax(s) as (values ('xxx'),(cast(null as character varying)))
> select cast(s as int) IS NULL from ax;
> {code}
> returns a result set; which evaluates
> however 'xxx' is not a valid integer; so an error should be recieved instead
> Another class of almost the same issue:
> {code}
> select cast('xxx' as int) IS NULL;
> {code}
> is also problematic; in that case the not nullability is deduced from the
> fact that the literal's type is not nullable
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-2934) Simplification of a filter =($0, false) to NOT($0) ($0 is boolean)
[
https://issues.apache.org/jira/browse/CALCITE-2934?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16795975#comment-16795975
]
Zoltan Haindrich edited comment on CALCITE-2934 at 3/19/19 11:09 AM:
-
The rational behind it is that we can "push" negation down further... note that
a "real" comparision forces the simplification logic to drop all special modes;
retaining those helps a lot
from your mail:
bq. I would have to rewrite it again to something equivalent to =($0, false)
since many types of indexes do not support negative conditions.
I would guess that doing the above will also enable to use indexes when someone
executes something like {{select 1 from t where booleanColumn}} - so it
actually will enhance index usage overall :)
was (Author: kgyrtkirk):
I don't see any "error" in the description; can you describe that?
The rational behind it is that we can "push" negation down further... note that
a "real" comparision forces the simplification logic to drop all special modes.
I'm not sure what index implementation are you refering to; but I think it's up
to the implementor to handle cases like this that.
"many types of indexes do not support negative conditions": I think it's not
the "index api"-s job to translate this back - more like the thing interacting
with the index...
> Simplification of a filter =($0, false) to NOT($0) ($0 is boolean)
> --
>
> Key: CALCITE-2934
> URL: https://issues.apache.org/jira/browse/CALCITE-2934
> Project: Calcite
> Issue Type: Bug
>Reporter: Stamatis Zampetakis
>Priority: Major
> Fix For: 1.20.0
>
>
> The errors occur due to the simplification of a filter =($0, false) to
> NOT($0) ($0 is boolean). The transformation is valid so in principle the
> tests should not fail. However it makes me wonder if adding negation is
> really a simplification. If I want to push this expression into an index
> (e.g., B+Tree) I would have to rewrite it again to something equivalent to
> =($0, false) since many types of indexes do not support negative conditions.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2934) Simplification of a filter =($0, false) to NOT($0) ($0 is boolean)
[ https://issues.apache.org/jira/browse/CALCITE-2934?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16795975#comment-16795975 ] Zoltan Haindrich commented on CALCITE-2934: --- I don't see any "error" in the description; can you describe that? The rational behind it is that we can "push" negation down further... note that a "real" comparision forces the simplification logic to drop all special modes. I'm not sure what index implementation are you refering to; but I think it's up to the implementor to handle cases like this that. "many types of indexes do not support negative conditions": I think it's not the "index api"-s job to translate this back - more like the thing interacting with the index... > Simplification of a filter =($0, false) to NOT($0) ($0 is boolean) > -- > > Key: CALCITE-2934 > URL: https://issues.apache.org/jira/browse/CALCITE-2934 > Project: Calcite > Issue Type: Bug >Reporter: Stamatis Zampetakis >Priority: Major > Fix For: 1.20.0 > > > The errors occur due to the simplification of a filter =($0, false) to > NOT($0) ($0 is boolean). The transformation is valid so in principle the > tests should not fail. However it makes me wonder if adding negation is > really a simplification. If I want to push this expression into an index > (e.g., B+Tree) I would have to rewrite it again to something equivalent to > =($0, false) since many types of indexes do not support negative conditions. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-2929) Simplification of IS NULL checks are incorrectly assuming that CAST-s are possible
[
https://issues.apache.org/jira/browse/CALCITE-2929?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16795796#comment-16795796
]
Zoltan Haindrich commented on CALCITE-2929:
---
[~danny0405]: for nullability yes; the assumption is correct - however the
"error" is not coming out...
I think {{cast('xxx' as integer)}} should end with an exception...but if I
write {{cast('xxx' as integer}} is null}} then it's just "false" is this
behaviour correct?
Lets assume for a minute that:
* {{cast('xxx' as integer) is null}} is a valid expression
* it evaluates to {{false} (as it is now)
then I would be inclined to ask: What is the value of {{cast('xxx' as
integer)}} - apparently it's not null; so it should be some (existing) integer
value...which one is it?
The sql standard advises to raise error in case the string could not be
interpreted in the target type; it could be found under 9075-2:2011 around
":
{code}
8) If TD is exact numeric, then
Case:
[...]
b) If SD is character string, then SV is replaced by SV with any leading or
trailing s removed.
Case:
i) If SV does not comprise a as defined by the
rules for in
Subclause 5.3, “”, then an exception condition is raised: data
exception — invalid
character value for cast.
[...]
{code}
so, I think CAST may not be considered Strong
> Simplification of IS NULL checks are incorrectly assuming that CAST-s are
> possible
> --
>
> Key: CALCITE-2929
> URL: https://issues.apache.org/jira/browse/CALCITE-2929
> Project: Calcite
> Issue Type: Bug
>Reporter: Zoltan Haindrich
>Assignee: Zoltan Haindrich
>Priority: Major
>
> Example:
> {code}
> with ax(s) as (values ('xxx'),(cast(null as character varying)))
> select cast(s as int) IS NULL from ax;
> {code}
> returns a result set; which evaluates
> however 'xxx' is not a valid integer; so an error should be recieved instead
> Another class of almost the same issue:
> {code}
> select cast('xxx' as int) IS NULL;
> {code}
> is also problematic; in that case the not nullability is deduced from the
> fact that the literal's type is not nullable
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2929) Simplification of IS NULL checks are incorrectly assuming that CAST-s are possible
[
https://issues.apache.org/jira/browse/CALCITE-2929?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16795204#comment-16795204
]
Zoltan Haindrich commented on CALCITE-2929:
---
If I count it correctly there are 4 issues here:
* simplification should not react right away on a non-nullable argument of an
IS NULL ; because it could be cast() IS NULL
* CAST shouldn't be considered "Strong"; as it may hide away errors
* RexImpTable.OptimizedCast should use NullPolicy.NONE
* RexProgram also considers cast as
[strong|https://github.com/apache/calcite/blob/ffca956be03a99cd11e440d652b09674aaa727e6/core/src/main/java/org/apache/calcite/rex/RexProgram.java#L490]
* after this there are some "KNOWN_NON_NULL_METHODS" which contains all the
valueOf methods - because these methods are effectively executing casts; the
fix would mean to "remove" this
[KNOWN_NON_NULL_METHODS|https://github.com/apache/calcite/blob/ffca956be03a99cd11e440d652b09674aaa727e6/linq4j/src/main/java/org/apache/calcite/linq4j/tree/OptimizeShuttle.java#L56]
I right now feel that it would be probably better to split this into 2:
* fix the regression after CALCITE-2469 by including only the first 2 fixes
(Strong + better simplification)
* RexProgram/RexToLix/OptimizeShuttle related fixes
> Simplification of IS NULL checks are incorrectly assuming that CAST-s are
> possible
> --
>
> Key: CALCITE-2929
> URL: https://issues.apache.org/jira/browse/CALCITE-2929
> Project: Calcite
> Issue Type: Bug
>Reporter: Zoltan Haindrich
>Assignee: Zoltan Haindrich
>Priority: Major
>
> Example:
> {code}
> with ax(s) as (values ('xxx'),(cast(null as character varying)))
> select cast(s as int) IS NULL from ax;
> {code}
> returns a result set; which evaluates
> however 'xxx' is not a valid integer; so an error should be recieved instead
> Another class of almost the same issue:
> {code}
> select cast('xxx' as int) IS NULL;
> {code}
> is also problematic; in that case the not nullability is deduced from the
> fact that the literal's type is not nullable
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2929) Simplification of IS NULL checks are incorrectly assuming that CAST-s are possible
[
https://issues.apache.org/jira/browse/CALCITE-2929?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16795088#comment-16795088
]
Zoltan Haindrich commented on CALCITE-2929:
---
there is a 3. occurance of this: CAST should not be considered as a Strong
function.
CALCITE-2469 started relying on Strong-ness of the operator and by that it
caused a little regression.
> Simplification of IS NULL checks are incorrectly assuming that CAST-s are
> possible
> --
>
> Key: CALCITE-2929
> URL: https://issues.apache.org/jira/browse/CALCITE-2929
> Project: Calcite
> Issue Type: Bug
>Reporter: Zoltan Haindrich
>Assignee: Zoltan Haindrich
>Priority: Major
>
> Example:
> {code}
> with ax(s) as (values ('xxx'),(cast(null as character varying)))
> select cast(s as int) IS NULL from ax;
> {code}
> returns a result set; which evaluates
> however 'xxx' is not a valid integer; so an error should be recieved instead
> Another class of almost the same issue:
> {code}
> select cast('xxx' as int) IS NULL;
> {code}
> is also problematic; in that case the not nullability is deduced from the
> fact that the literal's type is not nullable
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2929) Simplification of IS NULL checks are incorrectly assuming that CAST-s are possible
[
https://issues.apache.org/jira/browse/CALCITE-2929?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16794849#comment-16794849
]
Zoltan Haindrich commented on CALCITE-2929:
---
This is starting to get more complicated than it first seemed...I've run some
bisects against a hive issue which lead to CALCITE-2469
however both of my example issues in the description "doesn't changed
behaviour" with that patch :D
> Simplification of IS NULL checks are incorrectly assuming that CAST-s are
> possible
> --
>
> Key: CALCITE-2929
> URL: https://issues.apache.org/jira/browse/CALCITE-2929
> Project: Calcite
> Issue Type: Bug
>Reporter: Zoltan Haindrich
>Assignee: Zoltan Haindrich
>Priority: Major
>
> Example:
> {code}
> with ax(s) as (values ('xxx'),(cast(null as character varying)))
> select cast(s as int) IS NULL from ax;
> {code}
> returns a result set; which evaluates
> however 'xxx' is not a valid integer; so an error should be recieved instead
> Another class of almost the same issue:
> {code}
> select cast('xxx' as int) IS NULL;
> {code}
> is also problematic; in that case the not nullability is deduced from the
> fact that the literal's type is not nullable
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Created] (CALCITE-2929) Simplification of IS NULL checks are incorrectly assuming that CAST-s are possible
Zoltan Haindrich created CALCITE-2929:
-
Summary: Simplification of IS NULL checks are incorrectly assuming
that CAST-s are possible
Key: CALCITE-2929
URL: https://issues.apache.org/jira/browse/CALCITE-2929
Project: Calcite
Issue Type: Bug
Reporter: Zoltan Haindrich
Assignee: Zoltan Haindrich
Example:
{code}
with ax(s) as (values ('xxx'),(cast(null as character varying)))
select cast(s as int) IS NULL from ax;
{code}
returns a result set; which evaluates
however 'xxx' is not a valid integer; so an error should be recieved instead
Another class of almost the same issue:
{code}
select cast('xxx' as int) IS NULL;
{code}
is also problematic; in that case the not nullability is deduced from the fact
that the literal's type is not nullable
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Resolved] (CALCITE-2886) Simplification of AND expressions should push negations earlier
[ https://issues.apache.org/jira/browse/CALCITE-2886?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Zoltan Haindrich resolved CALCITE-2886. --- Resolution: Fixed Fix Version/s: 1.19.0 merged into master as [36d373f0ff549eb079ed91c7cf2dda2475a0721a|https://github.com/apache/calcite/commit/36d373f0ff549eb079ed91c7cf2dda2475a0721a] > Simplification of AND expressions should push negations earlier > --- > > Key: CALCITE-2886 > URL: https://issues.apache.org/jira/browse/CALCITE-2886 > Project: Calcite > Issue Type: Improvement >Reporter: Zoltan Haindrich >Assignee: Zoltan Haindrich >Priority: Major > Labels: pull-request-available > Fix For: 1.19.0 > > Time Spent: 20m > Remaining Estimate: 0h > > * currently simplification decomposes the AND operands > * simplifies all operands > * runs main simplification > * (re) simplifies all operands which are negated > Idea is to make the recursion as a first step; which could also push negation > further down; and skip the simplification at end completely - as its not > connected to a structural change. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-2918) Integration tests against postgres are broken
[
https://issues.apache.org/jira/browse/CALCITE-2918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16791600#comment-16791600
]
Zoltan Haindrich commented on CALCITE-2918:
---
This issue seems to be more general than CALCITE-2687 - which made it more
exposed.
I think we should definetly put some parentheses there to make it clear - we
might end up with similar expressions after doing some simplifications as well..
I'll try to look into it and propose a fix.
> Integration tests against postgres are broken
> -
>
> Key: CALCITE-2918
> URL: https://issues.apache.org/jira/browse/CALCITE-2918
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.18.0, 1.19.0
>Reporter: Kevin Risden
>Priority: Blocker
> Fix For: 1.19.0
>
> Attachments: test.sh
>
>
> As part of the release process, integration testing against postgres found a
> failure.
> {code:java}
> ./mvnw verify -Pit
> [INFO] ---
> [INFO] T E S T S
> [INFO] ---
> [INFO] Running org.apache.calcite.test.JdbcTest
> 2019-03-11 11:23:41,539 [main] INFO - open start - state modified
> 2019-03-11 11:23:41,555 [main] INFO - Checkpoint start
> 2019-03-11 11:23:41,555 [main] INFO - Checkpoint end - txts: 25
> [ERROR] Tests run: 290, Failures: 0, Errors: 1, Skipped: 21, Time elapsed:
> 65.154 s <<< FAILURE! - in org.apache.calcite.test.JdbcTest
> [ERROR] testIsNotDistinctInFilter(org.apache.calcite.test.JdbcTest) Time
> elapsed: 0.041 s <<< ERROR!
> java.sql.SQLException:
> Error while executing SQL "select *
> from "foodmart"."employee" as e1
> where e1."last_name" is distinct from e1."last_name"": While executing SQL
> [SELECT *
> FROM "foodmart"."employee"
> WHERE ("last_name" IS NOT NULL OR "last_name" IS NOT NULL) AND "last_name" =
> "last_name" IS NOT TRUE] on JDBC sub-schema
> at
> org.apache.calcite.test.JdbcTest.testIsNotDistinctInFilter(JdbcTest.java:1585)
> Caused by: java.lang.RuntimeException:
> While executing SQL [SELECT *
> FROM "foodmart"."employee"
> WHERE ("last_name" IS NOT NULL OR "last_name" IS NOT NULL) AND "last_name" =
> "last_name" IS NOT TRUE] on JDBC sub-schema
> at
> org.apache.calcite.test.JdbcTest.testIsNotDistinctInFilter(JdbcTest.java:1585)
> Caused by: org.postgresql.util.PSQLException:
> ERROR: argument of IS NOT TRUE must be type boolean, not type character
> varying
> Position: 114
> at
> org.apache.calcite.test.JdbcTest.testIsNotDistinctInFilter(JdbcTest.java:1585)
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Created] (CALCITE-2886) Simplification of AND expressions should push negations earlier
Zoltan Haindrich created CALCITE-2886: - Summary: Simplification of AND expressions should push negations earlier Key: CALCITE-2886 URL: https://issues.apache.org/jira/browse/CALCITE-2886 Project: Calcite Issue Type: Improvement Reporter: Zoltan Haindrich Assignee: Zoltan Haindrich * currently simplification decomposes the AND operands * simplifies all operands * runs main simplification * (re) simplifies all operands which are negated Idea is to make the recursion as a first step; which could also push negation further down; and skip the simplification at end completely - as its not connected to a structural change. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-2883) HepPlanner subprogram may loop till getting out of memory
[
https://issues.apache.org/jira/browse/CALCITE-2883?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16781747#comment-16781747
]
Zoltan Haindrich commented on CALCITE-2883:
---
I think it would probably make sense to add a program option to make it
possible to control the instruction loop.
> HepPlanner subprogram may loop till getting out of memory
> -
>
> Key: CALCITE-2883
> URL: https://issues.apache.org/jira/browse/CALCITE-2883
> Project: Calcite
> Issue Type: Bug
>Reporter: Jesus Camacho Rodriguez
>Assignee: Jesus Camacho Rodriguez
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.19.0
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Consider the following two hep programs.
> Program 1:
> {code}
> final HepProgramBuilder programBuilder = new HepProgramBuilder();
> programBuilder.addMatchOrder(HepMatchOrder.BOTTOM_UP);
> programBuilder.addRuleInstance(JoinToMultiJoinRule.INSTANCE);
> programBuilder.addRuleInstance(LoptOptimizeJoinRule.INSTANCE);
> final HepProgram program = programBuilder.build();
> {code}
> Program 2:
> {code}
> final HepProgramBuilder programBuilder = new HepProgramBuilder();
> final HepProgramBuilder subprogramBuilder = new HepProgramBuilder();
> subprogramBuilder.addMatchOrder(HepMatchOrder.BOTTOM_UP);
> subprogramBuilder.addRuleInstance(JoinToMultiJoinRule.INSTANCE);
> subprogramBuilder.addRuleInstance(LoptOptimizeJoinRule.INSTANCE);
> programBuilder.addSubprogram(subprogramBuilder.build());
> final HepProgram program = programBuilder.build();
> {code}
> I would expect both programs to behave similarly. However, program 2 will
> loop indefinitely. The reason is that {{HepPlanner}} subprogram execution
> loops if subprogram generates any new expression.
> https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/plan/hep/HepPlanner.java#L339
> This does not seem right since planner can control exiting the program (and
> thus, subprogram) depending on its own internal state and configuration
> properties, e.g., match limit.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Resolved] (CALCITE-2257) Combination of predicates can be proved to be always true
[
https://issues.apache.org/jira/browse/CALCITE-2257?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zoltan Haindrich resolved CALCITE-2257.
---
Resolution: Fixed
I'm closing this because I think right now simplification is already able to
handle these cases.
{code}
@Test public void testSimplify1() {
checkSimplify(or(isNotNull(vInt()), isNull(vInt())), "true");
}
{code}
there are also some
[tests|https://github.com/apache/calcite/blob/564caac262e43d7c27397d7bd17ab0f1689fdf4d/core/src/test/java/org/apache/calcite/test/RexProgramTest.java#L1610]
Please reopen if disagree!
> Combination of predicates can be proved to be always true
> -
>
> Key: CALCITE-2257
> URL: https://issues.apache.org/jira/browse/CALCITE-2257
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.16.0
>Reporter: Vitalii Diravka
>Assignee: Vitalii Diravka
>Priority: Major
> Labels: filter, pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> I have found the case, when Filter operator is not necessary since filter
> condition is always true, but that is not detected by current version of
> Calcite.
> {code}
> select SAL from EMPNULLABLES_20 where SAL IS NOT NULL OR SAL is null
> {code}
> {code}
> LogicalProject(SAL=[$5])
> LogicalFilter(condition=[OR(IS NOT NULL($5), IS NULL($5))])
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], SLACKER=[$8])
> LogicalFilter(condition=[AND(=($7, 20), >($5, 1000))])
> LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
> {code}
> But filter condition _OR(IS NOT NULL($5), IS NULL($5))_ can be proved to be
> always true.
> I have tried _ReduceExpressionsRule_, but it doesn't give effect.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Resolved] (CALCITE-2338) Make Simplification API more conservative
[ https://issues.apache.org/jira/browse/CALCITE-2338?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Zoltan Haindrich resolved CALCITE-2338. --- Resolution: Fixed Fix Version/s: 1.19.0 fixed in [564caac262e43d7c27397d7bd17ab0f1689fdf4d|https://github.com/apache/calcite/commit/564caac262e43d7c27397d7bd17ab0f1689fdf4d] > Make Simplification API more conservative > - > > Key: CALCITE-2338 > URL: https://issues.apache.org/jira/browse/CALCITE-2338 > Project: Calcite > Issue Type: Bug >Reporter: Zoltan Haindrich >Assignee: Zoltan Haindrich >Priority: Major > Labels: pull-request-available > Fix For: 1.19.0 > > Time Spent: 40m > Remaining Estimate: 0h > > Currently many lower level simplification methods are visible. > While I was porting CALCITE-2247 to a branch near 1.16 I've bumped into an > issue because of the fact that by calling a specific simplify method may > leave out some otherwise applicable simplifications. > For master there is already an extra safety feature by the presence of > CALCITE-2205; it seems like using less entry points may even lead to better > simplifications - by changing this; a filter have been removed. > https://github.com/kgyrtkirk/calcite/commit/2e29a659792f6bd9419dc0f97bf5a3bdd9f6f2cc -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-2421) RexSimplify#simplifyAnds foregoes some simplications if unknownAsFalse set to true
[
https://issues.apache.org/jira/browse/CALCITE-2421?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16780700#comment-16780700
]
Zoltan Haindrich commented on CALCITE-2421:
---
[~laurentgo] I hope you don't mind; but I've submitted a new PR for this issue
- because my upcoming change would seem like it causes regressions in some
cases without it.
> RexSimplify#simplifyAnds foregoes some simplications if unknownAsFalse set to
> true
> --
>
> Key: CALCITE-2421
> URL: https://issues.apache.org/jira/browse/CALCITE-2421
> Project: Calcite
> Issue Type: Bug
>Reporter: Laurent Goujon
>Assignee: Laurent Goujon
>Priority: Minor
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> It looks like {{RexSimplify#simplifyAnds}} foregoes some comparison
> simplifications if {{unknownAsFalse}} is set to true, like {{A = A AND B =
> B}} which might be simplified to {{A IS NOT NULL AND B IS NOT NULL}} or even
> {{true}} if {{A}} and {{B}} are known to be not nullable.
> One consequence of this is that the selectivity value might be off as a {{=}}
> comparison has a selectivity of 15% whereas {{IS NOT NULL}} has a selectivity
> of 90%.
> The simplication is skipped because {{RexSimplify#simplifyList}} simplify all
> terms with {{unknownAsFalse}} set to {{false}}, but in
> {{RexSimplify#simplifyAnd2ForUnknownAsFalse}}, there's no attempt at trying
> again to simplify each term.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-2421) RexSimplify#simplifyAnds foregoes some simplications if unknownAsFalse set to true
[
https://issues.apache.org/jira/browse/CALCITE-2421?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16770930#comment-16770930
]
Zoltan Haindrich edited comment on CALCITE-2421 at 2/28/19 3:58 PM:
this right now is only working for {{UnknownAs.FALSE}} - and only when the type
is not nullable. [link to
code|https://github.com/apache/calcite/blob/ef9f926061de21ad713a76ec3ec8110e5cbd92bf/core/src/main/java/org/apache/calcite/rex/RexSimplify.java#L306]
we could instead of that could generally rewrite
* {{A=A}} to {{A IS NOT NULL OR NULL}} (similarily <=,>=)
* {{A!=A}} to {{A IS NULL AND NULL}} (similarily <,>)
With these and the existing unknownAs logics ; this could became true/false A
IS [NOT] NULL correctly ; but when those cases does not apply it would in the
worst case cost one more RexNodes:
* {{w(A=A) = 2w(A) +1 }}
* {{w(A IS NOT NULL OR NULL)=w(A) + 3}}
* if we consider that {{w(A)>=1}} we are at worst added one more nodes
I think {{IS [NOT] NULL}} could help stat estimators is there are some info
about null counts
was (Author: kgyrtkirk):
this right now is only working for {{UnknownAs.FALSE}} - and only when the type
is not nullable. [link to
code|https://github.com/apache/calcite/blob/ef9f926061de21ad713a76ec3ec8110e5cbd92bf/core/src/main/java/org/apache/calcite/rex/RexSimplify.java#L306]
we could instead of that could generally rewrite
* {{A=A}} to {{A IS NOT NULL OR NULL}} (similarily <=,>=)
* {{A!=A}} to {{A IS NULL AND NULL}} (similarily <,>)
With these and the existing unknownAs logics ; this could became true/false A
IS [NOT] NULL correctly ; but when those cases does not apply it would at worst
retain the same number of RexNodes (I was afraid that it would raise it):
* {{w(A=A) = 2w(A) +1 }}
* {{w(A IS NOT NULL OR NULL)=w(A) + 2}}
* if we consider that {{w(A)>=1}} we are at worst at the same node count
> RexSimplify#simplifyAnds foregoes some simplications if unknownAsFalse set to
> true
> --
>
> Key: CALCITE-2421
> URL: https://issues.apache.org/jira/browse/CALCITE-2421
> Project: Calcite
> Issue Type: Bug
>Reporter: Laurent Goujon
>Assignee: Laurent Goujon
>Priority: Minor
> Labels: pull-request-available
>
> It looks like {{RexSimplify#simplifyAnds}} foregoes some comparison
> simplifications if {{unknownAsFalse}} is set to true, like {{A = A AND B =
> B}} which might be simplified to {{A IS NOT NULL AND B IS NOT NULL}} or even
> {{true}} if {{A}} and {{B}} are known to be not nullable.
> One consequence of this is that the selectivity value might be off as a {{=}}
> comparison has a selectivity of 15% whereas {{IS NOT NULL}} has a selectivity
> of 90%.
> The simplication is skipped because {{RexSimplify#simplifyList}} simplify all
> terms with {{unknownAsFalse}} set to {{false}}, but in
> {{RexSimplify#simplifyAnd2ForUnknownAsFalse}}, there's no attempt at trying
> again to simplify each term.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2338) Make Simplification API more conservative
[ https://issues.apache.org/jira/browse/CALCITE-2338?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16778009#comment-16778009 ] Zoltan Haindrich commented on CALCITE-2338: --- I've bumped into this again - these extra entry points lead to that during OR simplification the recursion was invoked 2 times. I agree on to separate them into a multiple groups at some point; right now I think closing down the cases in which simplifaction could be skipped may get some performance back. Right now for example ReduceExpressions is doing a "push-into-case" optimization which might be as well be a member in standard simplifications. > Make Simplification API more conservative > - > > Key: CALCITE-2338 > URL: https://issues.apache.org/jira/browse/CALCITE-2338 > Project: Calcite > Issue Type: Bug >Reporter: Zoltan Haindrich >Assignee: Zoltan Haindrich >Priority: Major > > Currently many lower level simplification methods are visible. > While I was porting CALCITE-2247 to a branch near 1.16 I've bumped into an > issue because of the fact that by calling a specific simplify method may > leave out some otherwise applicable simplifications. > For master there is already an extra safety feature by the presence of > CALCITE-2205; it seems like using less entry points may even lead to better > simplifications - by changing this; a filter have been removed. > https://github.com/kgyrtkirk/calcite/commit/2e29a659792f6bd9419dc0f97bf5a3bdd9f6f2cc -- This message was sent by Atlassian JIRA (v7.6.3#76005)
