[jira] [Updated] (CALCITE-4323) If a view definition has an ORDER BY clause, retain the sort if the view is used in a query at top level
[
https://issues.apache.org/jira/browse/CALCITE-4323?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-4323:
-
Description:
If you have a view that has an ORDER BY clause, and try to execute a query on
that view, SqlToRelConverter throws AssertionError during view expansion. For
example,
{noformat}
create view v as select * from "EMPLOYEES" order by "deptno";
select * from V;
java.lang.AssertionError
at
org.apache.calcite.sql2rel.SqlToRelConverter.requiredCollation(SqlToRelConverter.java:634)
at
org.apache.calcite.sql2rel.SqlToRelConverter.requiredCollation(SqlToRelConverter.java:629)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:578)
at
org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.expandView(CalcitePrepareImpl.java:1072)
at
org.apache.calcite.plan.ViewExpanders$1.expandView(ViewExpanders.java:52)
at
org.apache.calcite.schema.impl.ViewTable.expandView(ViewTable.java:127)
at org.apache.calcite.schema.impl.ViewTable.toRel(ViewTable.java:120)
at
org.apache.calcite.prepare.RelOptTableImpl.toRel(RelOptTableImpl.java:285)
at
org.apache.calcite.sql2rel.SqlToRelConverter.toRel(SqlToRelConverter.java:3605)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertIdentifier(SqlToRelConverter.java:2522)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2160)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2109)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2066)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:662)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:643)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3458)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:569)
at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:242)
{noformat}
I suspected that it would be a problem for when the view does not project the
sort column(s), but it seems to be a problem even if the columns are projected.
We should retain the ORDER BY of the view if it is used in the query at top
level (i.e. if the query contains expressions in the SELECT list and optionally
a WHERE clause, but not a GROUP BY, ORDER BY, HAVING, DISTINCT, aggregates,
UNION, and not if the view appears in a sub-query).
If the ORDER BY has FETCH or OFFSET, it should always be retained, because
those clauses affect which rows are returned, not just he order of rows.
was:
If you have a view that has an ORDER BY clause, and try to execute a query on
that view, SqlToRelConverter throws AssertionError during view expansion. For
example,
{noformat}
create view v as select * from "EMPLOYEES" order by "deptno";
select * from V;
java.lang.AssertionError
at
org.apache.calcite.sql2rel.SqlToRelConverter.requiredCollation(SqlToRelConverter.java:634)
at
org.apache.calcite.sql2rel.SqlToRelConverter.requiredCollation(SqlToRelConverter.java:629)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:578)
at
org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.expandView(CalcitePrepareImpl.java:1072)
at
org.apache.calcite.plan.ViewExpanders$1.expandView(ViewExpanders.java:52)
at
org.apache.calcite.schema.impl.ViewTable.expandView(ViewTable.java:127)
at org.apache.calcite.schema.impl.ViewTable.toRel(ViewTable.java:120)
at
org.apache.calcite.prepare.RelOptTableImpl.toRel(RelOptTableImpl.java:285)
at
org.apache.calcite.sql2rel.SqlToRelConverter.toRel(SqlToRelConverter.java:3605)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertIdentifier(SqlToRelConverter.java:2522)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2160)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2109)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2066)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:662)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:643)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3458)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:569)
at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:242)
{noformat}
I suspected that it would be a problem for when the view does not project the
sort column(s), but it seems to be a proble
[jira] [Updated] (CALCITE-4323) If a view definition has an ORDER BY clause, retain the sort if the view is used in a query at top level
[
https://issues.apache.org/jira/browse/CALCITE-4323?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-4323:
-
Description:
If you have a view that has an ORDER BY clause, and try to execute a query on
that view, SqlToRelConverter throws AssertionError during view expansion. For
example,
{noformat}
create view v as select * from "EMPLOYEES" order by "deptno";
select * from V;
java.lang.AssertionError
at
org.apache.calcite.sql2rel.SqlToRelConverter.requiredCollation(SqlToRelConverter.java:634)
at
org.apache.calcite.sql2rel.SqlToRelConverter.requiredCollation(SqlToRelConverter.java:629)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:578)
at
org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.expandView(CalcitePrepareImpl.java:1072)
at
org.apache.calcite.plan.ViewExpanders$1.expandView(ViewExpanders.java:52)
at
org.apache.calcite.schema.impl.ViewTable.expandView(ViewTable.java:127)
at org.apache.calcite.schema.impl.ViewTable.toRel(ViewTable.java:120)
at
org.apache.calcite.prepare.RelOptTableImpl.toRel(RelOptTableImpl.java:285)
at
org.apache.calcite.sql2rel.SqlToRelConverter.toRel(SqlToRelConverter.java:3605)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertIdentifier(SqlToRelConverter.java:2522)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2160)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2109)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2066)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:662)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:643)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3458)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:569)
at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:242)
{noformat}
I suspected that it would be a problem for when the view does not project the
sort column(s), but it seems to be a problem even if the columns are projected.
We should
was:
If you have a view that has an ORDER BY clause, and try to execute a query on
that view, SqlToRelConverter throws AssertionError during view expansion. For
example,
{noformat}
create view v as select * from "EMPLOYEES" order by "deptno";
select * from V;
java.lang.AssertionError
at
org.apache.calcite.sql2rel.SqlToRelConverter.requiredCollation(SqlToRelConverter.java:634)
at
org.apache.calcite.sql2rel.SqlToRelConverter.requiredCollation(SqlToRelConverter.java:629)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:578)
at
org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.expandView(CalcitePrepareImpl.java:1072)
at
org.apache.calcite.plan.ViewExpanders$1.expandView(ViewExpanders.java:52)
at
org.apache.calcite.schema.impl.ViewTable.expandView(ViewTable.java:127)
at org.apache.calcite.schema.impl.ViewTable.toRel(ViewTable.java:120)
at
org.apache.calcite.prepare.RelOptTableImpl.toRel(RelOptTableImpl.java:285)
at
org.apache.calcite.sql2rel.SqlToRelConverter.toRel(SqlToRelConverter.java:3605)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertIdentifier(SqlToRelConverter.java:2522)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2160)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2109)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2066)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:662)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:643)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3458)
at
org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:569)
at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:242)
{noformat}
I suspected that it would be a problem for when the view does not project the
sort column(s), but it seems to be a problem even if the columns are projected.
> If a view definition has an ORDER BY clause, retain the sort if the view is
> used in a query at top level
>
>
> Key: CALCITE-4323
> URL: https://issues.apache.org/jira/browse/CALCITE-4323
> Project: Calcite
> Issue Type: Bug
>
[jira] [Updated] (CALCITE-4323) If a view definition has an ORDER BY clause, retain the sort if the view is used in a query at top level
[
https://issues.apache.org/jira/browse/CALCITE-4323?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-4323:
-
Summary: If a view definition has an ORDER BY clause, retain the sort if
the view is used in a query at top level (was: View with ORDER BY throws
AssertionError during view expansion)
> If a view definition has an ORDER BY clause, retain the sort if the view is
> used in a query at top level
>
>
> Key: CALCITE-4323
> URL: https://issues.apache.org/jira/browse/CALCITE-4323
> Project: Calcite
> Issue Type: Bug
>Reporter: Julian Hyde
>Assignee: Jiatao Tao
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.30.0
>
> Attachments: image-2020-10-10-23-58-39-822.png,
> image-2020-10-10-23-59-22-297.png
>
> Time Spent: 1.5h
> Remaining Estimate: 0h
>
> If you have a view that has an ORDER BY clause, and try to execute a query on
> that view, SqlToRelConverter throws AssertionError during view expansion. For
> example,
> {noformat}
> create view v as select * from "EMPLOYEES" order by "deptno";
> select * from V;
> java.lang.AssertionError
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.requiredCollation(SqlToRelConverter.java:634)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.requiredCollation(SqlToRelConverter.java:629)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:578)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.expandView(CalcitePrepareImpl.java:1072)
> at
> org.apache.calcite.plan.ViewExpanders$1.expandView(ViewExpanders.java:52)
> at
> org.apache.calcite.schema.impl.ViewTable.expandView(ViewTable.java:127)
> at org.apache.calcite.schema.impl.ViewTable.toRel(ViewTable.java:120)
> at
> org.apache.calcite.prepare.RelOptTableImpl.toRel(RelOptTableImpl.java:285)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.toRel(SqlToRelConverter.java:3605)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertIdentifier(SqlToRelConverter.java:2522)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2160)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2109)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2066)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:662)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:643)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3458)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:569)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:242)
> {noformat}
> I suspected that it would be a problem for when the view does not project the
> sort column(s), but it seems to be a problem even if the columns are
> projected.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Resolved] (CALCITE-4323) View with ORDER BY throws AssertionError during view expansion
[
https://issues.apache.org/jira/browse/CALCITE-4323?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde resolved CALCITE-4323.
--
Resolution: Fixed
Fixed in
[de86d4f8|https://github.com/apache/calcite/commit/de86d4f8dc2d70b84f712446a285f5357ebf9d2c];
thanks for the PR, [~jiajunbernoulli]!
I modified your fix a little, so that it now retains the view's ORDER BY when
the query just contains a WHERE. I also re-organized the logic in
SqlToRelConverter to make it (in my opinion) clearer.
> View with ORDER BY throws AssertionError during view expansion
> --
>
> Key: CALCITE-4323
> URL: https://issues.apache.org/jira/browse/CALCITE-4323
> Project: Calcite
> Issue Type: Bug
>Reporter: Julian Hyde
>Assignee: Jiatao Tao
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.30.0
>
> Attachments: image-2020-10-10-23-58-39-822.png,
> image-2020-10-10-23-59-22-297.png
>
> Time Spent: 1.5h
> Remaining Estimate: 0h
>
> If you have a view that has an ORDER BY clause, and try to execute a query on
> that view, SqlToRelConverter throws AssertionError during view expansion. For
> example,
> {noformat}
> create view v as select * from "EMPLOYEES" order by "deptno";
> select * from V;
> java.lang.AssertionError
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.requiredCollation(SqlToRelConverter.java:634)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.requiredCollation(SqlToRelConverter.java:629)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:578)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.expandView(CalcitePrepareImpl.java:1072)
> at
> org.apache.calcite.plan.ViewExpanders$1.expandView(ViewExpanders.java:52)
> at
> org.apache.calcite.schema.impl.ViewTable.expandView(ViewTable.java:127)
> at org.apache.calcite.schema.impl.ViewTable.toRel(ViewTable.java:120)
> at
> org.apache.calcite.prepare.RelOptTableImpl.toRel(RelOptTableImpl.java:285)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.toRel(SqlToRelConverter.java:3605)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertIdentifier(SqlToRelConverter.java:2522)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2160)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2109)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2066)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:662)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:643)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3458)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:569)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:242)
> {noformat}
> I suspected that it would be a problem for when the view does not project the
> sort column(s), but it seems to be a problem even if the columns are
> projected.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Commented] (CALCITE-4999) `least restrictive` type inference does not work when one operand is scalar and another is row with one field
[
https://issues.apache.org/jira/browse/CALCITE-4999?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17493478#comment-17493478
]
Julian Hyde commented on CALCITE-4999:
--
Thanks for checking, [~rubenql]. That's as I suspected. So I think the solution
is to return an array of scalars (e.g. {{INTEGER ARRAY}}) when the query
returns 1 column, and continue to return an array of {{ROW}} when the query has
2 or more columns. And do the same for {{MULTISET}}.
[~dmsysolyatin] Can you change the subject (and, if necessary, description) of
this Jira now we know the underlying cause?
> `least restrictive` type inference does not work when one operand is scalar
> and another is row with one field
> -
>
> Key: CALCITE-4999
> URL: https://issues.apache.org/jira/browse/CALCITE-4999
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Dmitry Sysolyatin
>Assignee: Dmitry Sysolyatin
>Priority: Major
> Labels: pull-request-available
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> I faced with a issue that the following query:
> {code:java}
> SELECT ARRAY_CONCAT(ARRAY['1', '2'], array(select 'toast.' || x from
> unnest(ARRAY['1','2']) x)) {code}
> didn't work, because of:
> {code:java}
> java.lang.IllegalArgumentException: Cannot infer return type for
> ARRAY_CONCAT; operand types: [CHAR(1) ARRAY, RecordType(CHAR(7) EXPR$0)
> ARRAY]{code}
> `least restrictive` type inference does not work when one operand is scalar
> and another is row with one field
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Commented] (CALCITE-4999) `least restrictive` type inference does not work when one operand is scalar and another is row with one field
[
https://issues.apache.org/jira/browse/CALCITE-4999?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17493206#comment-17493206
]
Ruben Q L commented on CALCITE-4999:
[~dmsysolyatin] you can find some draft versions of the standard in Wikipedia:
https://en.wikipedia.org/wiki/SQL#SQL_standards_documents
I have checked the standard (relevant parts: and
); and it would seem that, indeed, Calcite is
not following it:
{noformat}
6.38
Function
Specify construction of an array.
Format
::=
|
[...]
::=
ARRAY
Syntax Rules
[...]
3) If is specified, then
a) The QE simply contained in the shall be
of degree 1 (one). Let ET be the declared type of the column in the result of
.
b) The declared type of the is array with
element type ET and maximum cardinality equal to the implementation-defined
maximum cardinality IMDC for such array types.
{noformat}
> `least restrictive` type inference does not work when one operand is scalar
> and another is row with one field
> -
>
> Key: CALCITE-4999
> URL: https://issues.apache.org/jira/browse/CALCITE-4999
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Dmitry Sysolyatin
>Assignee: Dmitry Sysolyatin
>Priority: Major
> Labels: pull-request-available
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> I faced with a issue that the following query:
> {code:java}
> SELECT ARRAY_CONCAT(ARRAY['1', '2'], array(select 'toast.' || x from
> unnest(ARRAY['1','2']) x)) {code}
> didn't work, because of:
> {code:java}
> java.lang.IllegalArgumentException: Cannot infer return type for
> ARRAY_CONCAT; operand types: [CHAR(1) ARRAY, RecordType(CHAR(7) EXPR$0)
> ARRAY]{code}
> `least restrictive` type inference does not work when one operand is scalar
> and another is row with one field
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Comment Edited] (CALCITE-4993) Simplify EQUALS or NOT-EQUALS with other number comparison
[
https://issues.apache.org/jira/browse/CALCITE-4993?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17493193#comment-17493193
]
xiong duan edited comment on CALCITE-4993 at 2/16/22, 11:59 AM:
If we just test the "x = 5 AND x > 6 AND x < 10" in RexProgramTest, then can be
simplified to "false".
This issue is because EQUALS and NOT-EQUALS use LEAST_RESTRICTIVE strategy
to convert all arguments to the least restrictive type.
So
{code:java}
"cust_id"<>5{code}
become
{code:java}
expr#2=[CAST($t0):INTEGER NOT NULL], expr#3=[5], expr#4=[<>($t2, $t3)]{code}
But
{code:java}
"cust_id">3{code}
become
{code:java}
expr#5=[3], expr#6=[>($t0, $t5)]{code}
The left RexNode([CAST($t0):INTEGER NOT NULL] and $t0) become different,
That's why can't simplified.
was (Author: nobigo):
If we just test the "x = 5 AND x > 6 AND x < 10" in RexProgramTest, then can be
simplified to "false".
This issue is because EQUALS and NOT-EQUALS use LEAST_RESTRICTIVE strategy
to convert all arguments to the least restrictive type.
So
"cust_id"<>5
become
{code:java}
expr#2=[CAST($t0):INTEGER NOT NULL], expr#3=[5], expr#4=[<>($t2, $t3)]{code}
But
"cust_id">3
become
{code:java}
expr#5=[3], expr#6=[>($t0, $t5)]{code}
The left RexNode([CAST($t0):INTEGER NOT NULL] and $t0) become different,
That's why can't simplified.
> Simplify EQUALS or NOT-EQUALS with other number comparison
> --
>
> Key: CALCITE-4993
> URL: https://issues.apache.org/jira/browse/CALCITE-4993
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.29.0
>Reporter: xiong duan
>Priority: Major
>
> For example(SELECT expression FROM table), The Plan is EnumerableCalc
> description:
> 1:
> {code:java}
> "cust_id"<>5 and "cust_id">3 and "cust_id"< 10{code}
> Before:
> {noformat}
> expr#0..1=[{inputs}], expr#2=[CAST($t0):INTEGER NOT NULL], expr#3=[5],
> expr#4=[<>($t2, $t3)], expr#5=[3], expr#6=[>($t0, $t5)], expr#7=[10],
> expr#8=[<($t0, $t7)], expr#9=[AND($t4, $t6, $t8)], EXPR$0=[$t9]{noformat}
> After:
> {noformat}
> (expr#0..1=[{inputs}], expr#2=[Sarg[(3..5), (5..10)]], expr#3=[SEARCH($t0,
> $t2)], EXPR$0=[$t3]){noformat}
> 2:
> {code:java}
> "cust_id"=5 and "cust_id">3 and "cust_id"< 10{code}
> Before:
> {noformat}
> expr#0..1=[{inputs}], expr#2=[CAST($t0):INTEGER NOT NULL], expr#3=[5],
> expr#4=[=($t2, $t3)], expr#5=[3], expr#6=[>($t0, $t5)], expr#7=[10],
> expr#8=[<($t0, $t7)], expr#9=[AND($t4, $t6, $t8)], EXPR$0=[$t9]{noformat}
> After:
> {noformat}
> expr#0..1=[{inputs}], expr#2=[5], expr#3=[=($t0, $t2)], EXPR$0=[$t3]{noformat}
> 3:
> {code:java}
> "cust_id"=5 and "cust_id">6 and "cust_id"< 10{code}
> Before:
> {noformat}
> expr#0..1=[{inputs}], expr#2=[CAST($t0):INTEGER NOT NULL], expr#3=[5],
> expr#4=[=($t2, $t3)], expr#5=[6], expr#6=[>($t0, $t5)], expr#7=[10],
> expr#8=[<($t0, $t7)], expr#9=[AND($t4, $t6, $t8)], EXPR$0=[$t9]{noformat}
> After:
> {noformat}
> expr#0..1=[{inputs}], expr#2=[false], EXPR$0=[$t2]{noformat}
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Commented] (CALCITE-4993) Simplify EQUALS or NOT-EQUALS with other number comparison
[
https://issues.apache.org/jira/browse/CALCITE-4993?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17493193#comment-17493193
]
xiong duan commented on CALCITE-4993:
-
If we just test the "x = 5 AND x > 6 AND x < 10" in RexProgramTest, then can be
simplified to "false".
This issue is because EQUALS and NOT-EQUALS use LEAST_RESTRICTIVE strategy
to convert all arguments to the least restrictive type.
So
"cust_id"<>5
become
{code:java}
expr#2=[CAST($t0):INTEGER NOT NULL], expr#3=[5], expr#4=[<>($t2, $t3)]{code}
But
"cust_id">3
become
{code:java}
expr#5=[3], expr#6=[>($t0, $t5)]{code}
The left RexNode([CAST($t0):INTEGER NOT NULL] and $t0) become different,
That's why can't simplified.
> Simplify EQUALS or NOT-EQUALS with other number comparison
> --
>
> Key: CALCITE-4993
> URL: https://issues.apache.org/jira/browse/CALCITE-4993
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.29.0
>Reporter: xiong duan
>Priority: Major
>
> For example(SELECT expression FROM table), The Plan is EnumerableCalc
> description:
> 1:
> {code:java}
> "cust_id"<>5 and "cust_id">3 and "cust_id"< 10{code}
> Before:
> {noformat}
> expr#0..1=[{inputs}], expr#2=[CAST($t0):INTEGER NOT NULL], expr#3=[5],
> expr#4=[<>($t2, $t3)], expr#5=[3], expr#6=[>($t0, $t5)], expr#7=[10],
> expr#8=[<($t0, $t7)], expr#9=[AND($t4, $t6, $t8)], EXPR$0=[$t9]{noformat}
> After:
> {noformat}
> (expr#0..1=[{inputs}], expr#2=[Sarg[(3..5), (5..10)]], expr#3=[SEARCH($t0,
> $t2)], EXPR$0=[$t3]){noformat}
> 2:
> {code:java}
> "cust_id"=5 and "cust_id">3 and "cust_id"< 10{code}
> Before:
> {noformat}
> expr#0..1=[{inputs}], expr#2=[CAST($t0):INTEGER NOT NULL], expr#3=[5],
> expr#4=[=($t2, $t3)], expr#5=[3], expr#6=[>($t0, $t5)], expr#7=[10],
> expr#8=[<($t0, $t7)], expr#9=[AND($t4, $t6, $t8)], EXPR$0=[$t9]{noformat}
> After:
> {noformat}
> expr#0..1=[{inputs}], expr#2=[5], expr#3=[=($t0, $t2)], EXPR$0=[$t3]{noformat}
> 3:
> {code:java}
> "cust_id"=5 and "cust_id">6 and "cust_id"< 10{code}
> Before:
> {noformat}
> expr#0..1=[{inputs}], expr#2=[CAST($t0):INTEGER NOT NULL], expr#3=[5],
> expr#4=[=($t2, $t3)], expr#5=[6], expr#6=[>($t0, $t5)], expr#7=[10],
> expr#8=[<($t0, $t7)], expr#9=[AND($t4, $t6, $t8)], EXPR$0=[$t9]{noformat}
> After:
> {noformat}
> expr#0..1=[{inputs}], expr#2=[false], EXPR$0=[$t2]{noformat}
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Updated] (CALCITE-4912) Confusing javadoc of RexSimplify.simplify
[
https://issues.apache.org/jira/browse/CALCITE-4912?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan updated CALCITE-4912:
Labels: pull-request-available (was: )
> Confusing javadoc of RexSimplify.simplify
> -
>
> Key: CALCITE-4912
> URL: https://issues.apache.org/jira/browse/CALCITE-4912
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.28.0
>Reporter: Alexander Trushev
>Assignee: xiong duan
>Priority: Minor
> Labels: pull-request-available
>
> Javadoc of RexSimplify.simplify says that
> {code}
> simplify(x = 1 AND y = 2 AND NOT x = 1) returns y = 2
> {code}
> but it is wrong for any policy:
> x <- 1
> y <- 2
> x = 1 AND y = 2 AND NOT x = 1 == false
> y = 2 == true
> I guess it should have been like
> {code}
> simplify(y = 2 AND (x = 1 OR NOT x = 1 OR x IS NULL)) returns y = 2
> {code}
> which is true for any policy:
> {code}
> Origin: AND(=($1, CAST(2):INTEGER), OR(=($0, CAST(1):INTEGER),
> NOT(=($0, CAST(1):INTEGER)), IS NULL($0)))
> Simplify(Unknown): =($1, 2)
> Simplify(False): =($1, 2)
> Simplify(True): =($1, 2)
> {code}
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Assigned] (CALCITE-4912) Confusing javadoc of RexSimplify.simplify
[
https://issues.apache.org/jira/browse/CALCITE-4912?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan reassigned CALCITE-4912:
---
Assignee: xiong duan
> Confusing javadoc of RexSimplify.simplify
> -
>
> Key: CALCITE-4912
> URL: https://issues.apache.org/jira/browse/CALCITE-4912
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.28.0
>Reporter: Alexander Trushev
>Assignee: xiong duan
>Priority: Minor
>
> Javadoc of RexSimplify.simplify says that
> {code}
> simplify(x = 1 AND y = 2 AND NOT x = 1) returns y = 2
> {code}
> but it is wrong for any policy:
> x <- 1
> y <- 2
> x = 1 AND y = 2 AND NOT x = 1 == false
> y = 2 == true
> I guess it should have been like
> {code}
> simplify(y = 2 AND (x = 1 OR NOT x = 1 OR x IS NULL)) returns y = 2
> {code}
> which is true for any policy:
> {code}
> Origin: AND(=($1, CAST(2):INTEGER), OR(=($0, CAST(1):INTEGER),
> NOT(=($0, CAST(1):INTEGER)), IS NULL($0)))
> Simplify(Unknown): =($1, 2)
> Simplify(False): =($1, 2)
> Simplify(True): =($1, 2)
> {code}
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Commented] (CALCITE-4912) Confusing javadoc of RexSimplify.simplify
[
https://issues.apache.org/jira/browse/CALCITE-4912?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17493140#comment-17493140
]
xiong duan commented on CALCITE-4912:
-
Looks like the PR link is a auto job but not real-time. The PR link is
[pull-2721|https://github.com/apache/calcite/pull/2721].
> Confusing javadoc of RexSimplify.simplify
> -
>
> Key: CALCITE-4912
> URL: https://issues.apache.org/jira/browse/CALCITE-4912
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.28.0
>Reporter: Alexander Trushev
>Priority: Minor
>
> Javadoc of RexSimplify.simplify says that
> {code}
> simplify(x = 1 AND y = 2 AND NOT x = 1) returns y = 2
> {code}
> but it is wrong for any policy:
> x <- 1
> y <- 2
> x = 1 AND y = 2 AND NOT x = 1 == false
> y = 2 == true
> I guess it should have been like
> {code}
> simplify(y = 2 AND (x = 1 OR NOT x = 1 OR x IS NULL)) returns y = 2
> {code}
> which is true for any policy:
> {code}
> Origin: AND(=($1, CAST(2):INTEGER), OR(=($0, CAST(1):INTEGER),
> NOT(=($0, CAST(1):INTEGER)), IS NULL($0)))
> Simplify(Unknown): =($1, 2)
> Simplify(False): =($1, 2)
> Simplify(True): =($1, 2)
> {code}
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Commented] (CALCITE-4912) Confusing javadoc of RexSimplify.simplify
[
https://issues.apache.org/jira/browse/CALCITE-4912?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17493105#comment-17493105
]
Xurenhe commented on CALCITE-4912:
--
Hi [~nobigo]
Could you put the PR's url to help me link it?
> Confusing javadoc of RexSimplify.simplify
> -
>
> Key: CALCITE-4912
> URL: https://issues.apache.org/jira/browse/CALCITE-4912
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.28.0
>Reporter: Alexander Trushev
>Priority: Minor
>
> Javadoc of RexSimplify.simplify says that
> {code}
> simplify(x = 1 AND y = 2 AND NOT x = 1) returns y = 2
> {code}
> but it is wrong for any policy:
> x <- 1
> y <- 2
> x = 1 AND y = 2 AND NOT x = 1 == false
> y = 2 == true
> I guess it should have been like
> {code}
> simplify(y = 2 AND (x = 1 OR NOT x = 1 OR x IS NULL)) returns y = 2
> {code}
> which is true for any policy:
> {code}
> Origin: AND(=($1, CAST(2):INTEGER), OR(=($0, CAST(1):INTEGER),
> NOT(=($0, CAST(1):INTEGER)), IS NULL($0)))
> Simplify(Unknown): =($1, 2)
> Simplify(False): =($1, 2)
> Simplify(True): =($1, 2)
> {code}
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
[jira] [Commented] (CALCITE-4912) Confusing javadoc of RexSimplify.simplify
[
https://issues.apache.org/jira/browse/CALCITE-4912?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17493067#comment-17493067
]
xiong duan commented on CALCITE-4912:
-
The PR is ready for review. Welcome, everyone.
> Confusing javadoc of RexSimplify.simplify
> -
>
> Key: CALCITE-4912
> URL: https://issues.apache.org/jira/browse/CALCITE-4912
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.28.0
>Reporter: Alexander Trushev
>Priority: Minor
>
> Javadoc of RexSimplify.simplify says that
> {code}
> simplify(x = 1 AND y = 2 AND NOT x = 1) returns y = 2
> {code}
> but it is wrong for any policy:
> x <- 1
> y <- 2
> x = 1 AND y = 2 AND NOT x = 1 == false
> y = 2 == true
> I guess it should have been like
> {code}
> simplify(y = 2 AND (x = 1 OR NOT x = 1 OR x IS NULL)) returns y = 2
> {code}
> which is true for any policy:
> {code}
> Origin: AND(=($1, CAST(2):INTEGER), OR(=($0, CAST(1):INTEGER),
> NOT(=($0, CAST(1):INTEGER)), IS NULL($0)))
> Simplify(Unknown): =($1, 2)
> Simplify(False): =($1, 2)
> Simplify(True): =($1, 2)
> {code}
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
