[jira] [Closed] (CALCITE-2964) testEmptyFilterProjectUnion fails when copyPorject is disabled in FilterProjectTransposeRule
[
https://issues.apache.org/jira/browse/CALCITE-2964?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Haisheng Yuan closed CALCITE-2964.
--
Resolution: Not A Bug
> testEmptyFilterProjectUnion fails when copyPorject is disabled in
> FilterProjectTransposeRule
>
>
> Key: CALCITE-2964
> URL: https://issues.apache.org/jira/browse/CALCITE-2964
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Haisheng Yuan
>Priority: Major
>
> Changing
> {code:java}
> public static final FilterProjectTransposeRule INSTANCE =
> new FilterProjectTransposeRule(Filter.class, Project.class, true, true,
> RelFactories.LOGICAL_BUILDER);
> {code}
> to
> {code:java}
> public static final FilterProjectTransposeRule INSTANCE =
> new FilterProjectTransposeRule(Filter.class, Project.class, true, false,
> RelFactories.LOGICAL_BUILDER);
> {code}
> will cause testEmptyFilterProjectUnion plan diff:
> {code:java}
> LogicalProject(X=[$0], Y=[$1])
> LogicalUnion(all=[true])
> LogicalFilter(condition=[>(+($0, $1), 30)])
> LogicalValues(tuples=[[{ 10, 1 }, { 30, 3 }]])
> LogicalFilter(condition=[>(+($0, $1), 30)])
> LogicalValues(tuples=[[{ 20, 2 }]])
> {code}
> The expected plan is:
> {code:java}
> LogicalProject(X=[$0], Y=[$1])
> LogicalValues(tuples=[[{ 30, 3 }]])
> {code}
> I am assuming it will generate the same plan no matter copyFilter/copyProject
> is on or off. Is this wrong assumption?
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2964) testEmptyFilterProjectUnion fails when copyPorject is disabled in FilterProjectTransposeRule
[
https://issues.apache.org/jira/browse/CALCITE-2964?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16806398#comment-16806398
]
Haisheng Yuan commented on CALCITE-2964:
Looks like we just need to add ValuesReduceRule.FILTER_INSTANCE to
testEmptyFilterProjectUnion. So this is not a bug, I will close this issue.
Feel free to reopen it if you don't think so.
> testEmptyFilterProjectUnion fails when copyPorject is disabled in
> FilterProjectTransposeRule
>
>
> Key: CALCITE-2964
> URL: https://issues.apache.org/jira/browse/CALCITE-2964
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Haisheng Yuan
>Priority: Major
>
> Changing
> {code:java}
> public static final FilterProjectTransposeRule INSTANCE =
> new FilterProjectTransposeRule(Filter.class, Project.class, true, true,
> RelFactories.LOGICAL_BUILDER);
> {code}
> to
> {code:java}
> public static final FilterProjectTransposeRule INSTANCE =
> new FilterProjectTransposeRule(Filter.class, Project.class, true, false,
> RelFactories.LOGICAL_BUILDER);
> {code}
> will cause testEmptyFilterProjectUnion plan diff:
> {code:java}
> LogicalProject(X=[$0], Y=[$1])
> LogicalUnion(all=[true])
> LogicalFilter(condition=[>(+($0, $1), 30)])
> LogicalValues(tuples=[[{ 10, 1 }, { 30, 3 }]])
> LogicalFilter(condition=[>(+($0, $1), 30)])
> LogicalValues(tuples=[[{ 20, 2 }]])
> {code}
> The expected plan is:
> {code:java}
> LogicalProject(X=[$0], Y=[$1])
> LogicalValues(tuples=[[{ 30, 3 }]])
> {code}
> I am assuming it will generate the same plan no matter copyFilter/copyProject
> is on or off. Is this wrong assumption?
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2964) testEmptyFilterProjectUnion fails when copyPorject is disabled in FilterProjectTransposeRule
[
https://issues.apache.org/jira/browse/CALCITE-2964?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16806388#comment-16806388
]
Feng Zhu commented on CALCITE-2964:
---
The *FilterProjectTransposeRule* rule does not create a Project RelNode because
it is identity.
As a result, the *ValuesReduceRule* rule fails to match. I think it is
reasonable to create a Project even it is identity
{code:java}
***Before Rule: FilterProjectTransposeRule
LogicalFilter(condition=[>(+($0, $1), 30)])
LogicalProject(EXPR$0=[$0], EXPR$1=[$1])
LogicalValues(tuples=[[{ 20, 2 }]])
---After Rule: FilterProjectTransposeRule
LogicalFilter(condition=[>(+($0, $1), 30)])
LogicalValues(tuples=[[{ 20, 2 }]]){code}
> testEmptyFilterProjectUnion fails when copyPorject is disabled in
> FilterProjectTransposeRule
>
>
> Key: CALCITE-2964
> URL: https://issues.apache.org/jira/browse/CALCITE-2964
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Haisheng Yuan
>Priority: Major
>
> Changing
> {code:java}
> public static final FilterProjectTransposeRule INSTANCE =
> new FilterProjectTransposeRule(Filter.class, Project.class, true, true,
> RelFactories.LOGICAL_BUILDER);
> {code}
> to
> {code:java}
> public static final FilterProjectTransposeRule INSTANCE =
> new FilterProjectTransposeRule(Filter.class, Project.class, true, false,
> RelFactories.LOGICAL_BUILDER);
> {code}
> will cause testEmptyFilterProjectUnion plan diff:
> {code:java}
> LogicalProject(X=[$0], Y=[$1])
> LogicalUnion(all=[true])
> LogicalFilter(condition=[>(+($0, $1), 30)])
> LogicalValues(tuples=[[{ 10, 1 }, { 30, 3 }]])
> LogicalFilter(condition=[>(+($0, $1), 30)])
> LogicalValues(tuples=[[{ 20, 2 }]])
> {code}
> The expected plan is:
> {code:java}
> LogicalProject(X=[$0], Y=[$1])
> LogicalValues(tuples=[[{ 30, 3 }]])
> {code}
> I am assuming it will generate the same plan no matter copyFilter/copyProject
> is on or off. Is this wrong assumption?
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Created] (CALCITE-2966) Problem with Code Generation
Danny Chan created CALCITE-2966: --- Summary: Problem with Code Generation Key: CALCITE-2966 URL: https://issues.apache.org/jira/browse/CALCITE-2966 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.20.0 Reporter: Danny Chan Assignee: Danny Chan Fix For: 1.20.0 >From the mailing list: Hi all, I have some problems with the code generation from Linq4j which I'm unable to resolve myself. Basically, I want to translate a condition from Rex to a Linq4j expression to use it in generated code. In my example the Condition is from Match Recognize and in SQL is: `up."commission" > prev(up."commission")`. ``` RexBuilder rexBuilder = new RexBuilder(implementor.getTypeFactory()); RexProgramBuilder rexProgramBuilder = new RexProgramBuilder(physType.getRowType(), rexBuilder); rexProgramBuilder.addCondition(entry.getValue()); final Expression condition = RexToLixTranslator.translateCondition(rexProgramBuilder.getProgram(), (JavaTypeFactory) getCluster().getTypeFactory(), builder2, inputGetter1, implementor.allCorrelateVariables, implementor.getConformance()); builder2.add(Expressions.return_(null, condition)); ``` Here, the condition seems okay, it is: ">(PREV(UP.$4, 0), PREV(UP.$4, 1))", so it should be a comparison of two variables (I rewrite the PREV with a custom Input Getter". But, the generated code (for Janino) is: ``` Object p1 = row_.get($L4J$C$0_1); org.apache.calcite.test.JdbcTest.Employee p0 = (org.apache.calcite.test.JdbcTest.Employee) p1; Object p3 = row_.get($L4J$C$1_1); org.apache.calcite.test.JdbcTest.Employee p2 = (org.apache.calcite.test.JdbcTest.Employee) p3; Object p5 = row_.get($L4J$C$0_1); org.apache.calcite.test.JdbcTest.Employee p4 = (org.apache.calcite.test.JdbcTest.Employee) p5; Object p7 = row_.get($L4J$C$1_1); org.apache.calcite.test.JdbcTest.Employee p6 = (org.apache.calcite.test.JdbcTest.Employee) p7; return p0.commission && p2.commission && p4.commission > p6.commission; ``` This confuses me a lot as I do not know where the check for p0.commission and p2.commission comes from. It seems that Linq4j adds them as it expects these variables to be nullable, but I have no idea on how to avoid this. These fields are Numeric so I always get a compilation exception. Can someone help me with this issue? -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-2914) Improve how LatticeSuggester deduces foreign keys
[
https://issues.apache.org/jira/browse/CALCITE-2914?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16806348#comment-16806348
]
Danny Chan commented on CALCITE-2914:
-
[~julianhyde] Reviewing now.
> Improve how LatticeSuggester deduces foreign keys
> -
>
> Key: CALCITE-2914
> URL: https://issues.apache.org/jira/browse/CALCITE-2914
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Julian Hyde
>Assignee: Julian Hyde
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.20.0
>
> Time Spent: 50m
> Remaining Estimate: 0h
>
> Improve how LatticeSuggester deduces foreign keys.
> Currently LatticeSuggester uses SqlStatisticProvider and deduces that a join
> between two tables is "many to one" if the left table has more rows than the
> right table. Clearly this is wrong, because it does not take into account the
> keys of the join.
> I think we will change the method
> {code:java}
> double tableCardinality(List qualifiedTableName){code}
> to
> {code:java}
> double tableCardinality(RelOptTable table){code}
> and add a method
> {code:java}
> boolean isForeignKey(RelOptTable fromTable, List fromColumns,
>RelOptTable toTable, List toColumns){code}
> For example, {{isForeignKey(empTable, ["DEPTNO"], deptTable, ["DEPTNO"])}}
> would return true.
> One valid implementation might look at the catalog for foreign key
> constraints; another might execute a query to see whether every row in emp is
> matched by exaclty one row in dept. Here is a query that might do
> it:{code:sql}
> select emp.deptno
> from (select distinct deptno from emp) as emp
> left join dept using (deptno)
> group by emp.deptno
> having count(dept.deptno) != 1{code}
> If the query returns any rows it is not a foreign key.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Assigned] (CALCITE-2846) Document Oracle-specific functions, such as NVL and LTRIM, in the SQL reference
[ https://issues.apache.org/jira/browse/CALCITE-2846?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Hongze Zhang reassigned CALCITE-2846: - Assignee: (was: Hongze Zhang) > Document Oracle-specific functions, such as NVL and LTRIM, in the SQL > reference > --- > > Key: CALCITE-2846 > URL: https://issues.apache.org/jira/browse/CALCITE-2846 > Project: Calcite > Issue Type: Improvement > Components: site >Reporter: Julian Hyde >Priority: Major > Labels: documentation > > Document Oracle-specific functions (DECODE, NVL, LTRIM, RTRIM, SUBSTR, > GREATEST, LEAST) in the [SQL > reference|https://calcite.apache.org/docs/reference.html]. > Same goes for MySQL-specific functions (e.g. JSON_TYPE). > I don't think we should have separate lists of Oracle-specific functions and > MySQL-specific functions. Because quite a few functions appear in more than > one place. Better, I think, to have a concise annotation against each > function which tables it occurs in. > The current list of tables is standard, oracle, spatial, mysql. Perhaps also > indicate whether a function is an extension to the SQL standard but still > occurs in Calcite's default table. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-2846) Document Oracle-specific functions, such as NVL and LTRIM, in the SQL reference
[ https://issues.apache.org/jira/browse/CALCITE-2846?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16806346#comment-16806346 ] Hongze Zhang commented on CALCITE-2846: --- I see this issue has been mentioned in CALCITE-2965 as likely a blocker. But unfortunately I am not currently working on this so I have unassigned myself from the issue. If would be much appreciated if someone decide to help then. > Document Oracle-specific functions, such as NVL and LTRIM, in the SQL > reference > --- > > Key: CALCITE-2846 > URL: https://issues.apache.org/jira/browse/CALCITE-2846 > Project: Calcite > Issue Type: Improvement > Components: site >Reporter: Julian Hyde >Assignee: Hongze Zhang >Priority: Major > Labels: documentation > > Document Oracle-specific functions (DECODE, NVL, LTRIM, RTRIM, SUBSTR, > GREATEST, LEAST) in the [SQL > reference|https://calcite.apache.org/docs/reference.html]. > Same goes for MySQL-specific functions (e.g. JSON_TYPE). > I don't think we should have separate lists of Oracle-specific functions and > MySQL-specific functions. Because quite a few functions appear in more than > one place. Better, I think, to have a concise annotation against each > function which tables it occurs in. > The current list of tables is standard, oracle, spatial, mysql. Perhaps also > indicate whether a function is an extension to the SQL standard but still > occurs in Calcite's default table. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-2948) SqlToRelConverter generates complicated logical plan for subquery
[
https://issues.apache.org/jira/browse/CALCITE-2948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16806335#comment-16806335
]
Danny Chan commented on CALCITE-2948:
-
[~julianhyde] [~hyuan] [~vgarg] The value generator for the first join is right
for the scope, it join on the correlate variables with the outer view[1] and
make a distinct agg to remove duplicates. The second join is kind of like a dim
join which supplement the remaining fields of the outer view[2]. So, we can
only simplify the plan if:
a. It is a semi-join, for this case, it is an in subquery.
b. The outer most projection must only project the fields which are among the
correlated variables.
c. The outer most projection fields must be unique in the outer view (primary
key or unique keys), this must be satisfied cause the first join will make
cartesian product on the correlate condition, and that is the reason why we
must make a distinct agg. If the outer view do not project on unique fields, we
can not remove the second join.
For a when we rewrite in clause for the query plan in SubQueryRemoveRule[3],
the join type passed in is JoinRelType.INNER, and the constructed
LogicalCorrelate is also with SemiJoinType.INNER, So we do not have any info to
know the LogicalCorrelate is a semi-join when we do[2].
For b and c i think we should do it in PlannerRule and query meta in
MetaDataQuery.
[1]
https://github.com/apache/calcite/blob/d7946a94adfd2e788f5d324910944dd65dab11ee/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java#L1040
[2]
https://github.com/apache/calcite/blob/d7946a94adfd2e788f5d324910944dd65dab11ee/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java#L1187
[3]
https://github.com/apache/calcite/blob/d7946a94adfd2e788f5d324910944dd65dab11ee/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java#L411
> SqlToRelConverter generates complicated logical plan for subquery
> -
>
> Key: CALCITE-2948
> URL: https://issues.apache.org/jira/browse/CALCITE-2948
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Haisheng Yuan
>Assignee: Danny Chan
>Priority: Major
> Labels: sub-query
>
> Repro:
> Add the following test to SqlToRelConverterTest.java.
> {code:java}
> @Test public void testSubQueryIN() {
> final String sql = "select deptno\n"
> + "from EMP e\n"
> + "where deptno in (select deptno\n"
> + "from EMP where empno=e.empno+1)";
> sql(sql).ok();
> }
> {code}
> Plan:
> {code:java}
> LogicalProject(DEPTNO=[$7])
> LogicalJoin(condition=[AND(=($0, $10), =($7, $9))], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{0, 1}])
> LogicalProject(DEPTNO=[$7], EMPNO0=[$9])
> LogicalJoin(condition=[=($0, +($9, 1))], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalProject(EMPNO=[$0])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> One join would suffice.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Comment Edited] (CALCITE-2964) testEmptyFilterProjectUnion fails when copyPorject is disabled in FilterProjectTransposeRule
[
https://issues.apache.org/jira/browse/CALCITE-2964?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16806333#comment-16806333
]
Chunwei Lei edited comment on CALCITE-2964 at 4/1/19 2:22 AM:
--
I debugged a little since it is a little weird. I find that it generates a
Filter instead of Project when copyProject is false using RelBuilder in that it
is a trivial project[1].
[1][https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L1327]
was (Author: chunwei lei):
I debugged a little since it is a little weird. I find that it generates a
Filter instead of Project when copyProject is false in that it is a trivial
project[1].
[1][https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L1327]
> testEmptyFilterProjectUnion fails when copyPorject is disabled in
> FilterProjectTransposeRule
>
>
> Key: CALCITE-2964
> URL: https://issues.apache.org/jira/browse/CALCITE-2964
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Haisheng Yuan
>Priority: Major
>
> Changing
> {code:java}
> public static final FilterProjectTransposeRule INSTANCE =
> new FilterProjectTransposeRule(Filter.class, Project.class, true, true,
> RelFactories.LOGICAL_BUILDER);
> {code}
> to
> {code:java}
> public static final FilterProjectTransposeRule INSTANCE =
> new FilterProjectTransposeRule(Filter.class, Project.class, true, false,
> RelFactories.LOGICAL_BUILDER);
> {code}
> will cause testEmptyFilterProjectUnion plan diff:
> {code:java}
> LogicalProject(X=[$0], Y=[$1])
> LogicalUnion(all=[true])
> LogicalFilter(condition=[>(+($0, $1), 30)])
> LogicalValues(tuples=[[{ 10, 1 }, { 30, 3 }]])
> LogicalFilter(condition=[>(+($0, $1), 30)])
> LogicalValues(tuples=[[{ 20, 2 }]])
> {code}
> The expected plan is:
> {code:java}
> LogicalProject(X=[$0], Y=[$1])
> LogicalValues(tuples=[[{ 30, 3 }]])
> {code}
> I am assuming it will generate the same plan no matter copyFilter/copyProject
> is on or off. Is this wrong assumption?
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2964) testEmptyFilterProjectUnion fails when copyPorject is disabled in FilterProjectTransposeRule
[
https://issues.apache.org/jira/browse/CALCITE-2964?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16806333#comment-16806333
]
Chunwei Lei commented on CALCITE-2964:
--
I debugged a little since it is a little weird. I find that it generates a
Filter instead of Project when copyProject is false in that it is a trivial
project[1].
[1][https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L1327]
> testEmptyFilterProjectUnion fails when copyPorject is disabled in
> FilterProjectTransposeRule
>
>
> Key: CALCITE-2964
> URL: https://issues.apache.org/jira/browse/CALCITE-2964
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Haisheng Yuan
>Priority: Major
>
> Changing
> {code:java}
> public static final FilterProjectTransposeRule INSTANCE =
> new FilterProjectTransposeRule(Filter.class, Project.class, true, true,
> RelFactories.LOGICAL_BUILDER);
> {code}
> to
> {code:java}
> public static final FilterProjectTransposeRule INSTANCE =
> new FilterProjectTransposeRule(Filter.class, Project.class, true, false,
> RelFactories.LOGICAL_BUILDER);
> {code}
> will cause testEmptyFilterProjectUnion plan diff:
> {code:java}
> LogicalProject(X=[$0], Y=[$1])
> LogicalUnion(all=[true])
> LogicalFilter(condition=[>(+($0, $1), 30)])
> LogicalValues(tuples=[[{ 10, 1 }, { 30, 3 }]])
> LogicalFilter(condition=[>(+($0, $1), 30)])
> LogicalValues(tuples=[[{ 20, 2 }]])
> {code}
> The expected plan is:
> {code:java}
> LogicalProject(X=[$0], Y=[$1])
> LogicalValues(tuples=[[{ 30, 3 }]])
> {code}
> I am assuming it will generate the same plan no matter copyFilter/copyProject
> is on or off. Is this wrong assumption?
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2954) SubQueryJoinRemoveRule and SubQueryProjectRemoveRule passing on empty set instead of set of correlation id
[
https://issues.apache.org/jira/browse/CALCITE-2954?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16806327#comment-16806327
]
Haisheng Yuan commented on CALCITE-2954:
OK
> SubQueryJoinRemoveRule and SubQueryProjectRemoveRule passing on empty set
> instead of set of correlation id
> --
>
> Key: CALCITE-2954
> URL: https://issues.apache.org/jira/browse/CALCITE-2954
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Labels: pull-request-available, sub-query
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Call to {{apply}} takes set of {{CorrelationId}} but both
> {{SubQueryJoinRemoveRule}} and {{SubQueryProjectRemoveRule}} are passing
> empty set for whatever reason.
> I believe the correct thing to do here is to pass on the actual set of
> {{CorrelationId}} like {{SubQueryFilterRemoveRule}}
> Ref:
> {code:java}
> final RexNode target = apply(e, ImmutableSet.of(),
> logic, builder, 2, fieldCount);
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-883) Support RESPECT NULLS, IGNORE NULLS option for LEAD, LAG, FIRST_VALUE, LAST_VALUE and NTH_VALUE window functions
[ https://issues.apache.org/jira/browse/CALCITE-883?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16806324#comment-16806324 ] Chunwei Lei commented on CALCITE-883: - [~zhztheplayer], thank you for your kindly help.;) > Support RESPECT NULLS, IGNORE NULLS option for LEAD, LAG, FIRST_VALUE, > LAST_VALUE and NTH_VALUE window functions > > > Key: CALCITE-883 > URL: https://issues.apache.org/jira/browse/CALCITE-883 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Sean Hsuan-Yi Chu >Assignee: Julian Hyde >Priority: Major > Labels: pull-request-available > Fix For: 1.20.0 > > Time Spent: 1.5h > Remaining Estimate: 0h > > This issue was brought up in Drill community: > (https://issues.apache.org/jira/browse/DRILL-3597) > For convenience, I copied and pasted the reporter Khurram Faraaz's > description here: > "The SQL standard defines a RESPECT NULLS or IGNORE NULLS option for lead, > lag, first_value, and last_value window functions." > In Calcite, we should have been able to recognized this option. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Resolved] (CALCITE-883) Support RESPECT NULLS, IGNORE NULLS option for LEAD, LAG, FIRST_VALUE, LAST_VALUE window functions
[ https://issues.apache.org/jira/browse/CALCITE-883?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Julian Hyde resolved CALCITE-883. - Resolution: Fixed Fix Version/s: 1.20.0 Fixed in [d7946a94|https://github.com/apache/calcite/commit/d7946a94adfd2e788f5d324910944dd65dab11ee]. Thanks for the PR, [~Chunwei Lei]! > Support RESPECT NULLS, IGNORE NULLS option for LEAD, LAG, FIRST_VALUE, > LAST_VALUE window functions > -- > > Key: CALCITE-883 > URL: https://issues.apache.org/jira/browse/CALCITE-883 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Sean Hsuan-Yi Chu >Assignee: Julian Hyde >Priority: Major > Labels: pull-request-available > Fix For: 1.20.0 > > Time Spent: 1.5h > Remaining Estimate: 0h > > This issue was brought up in Drill community: > (https://issues.apache.org/jira/browse/DRILL-3597) > For convenience, I copied and pasted the reporter Khurram Faraaz's > description here: > "The SQL standard defines a RESPECT NULLS or IGNORE NULLS option for lead, > lag, first_value, and last_value window functions." > In Calcite, we should have been able to recognized this option. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Updated] (CALCITE-883) Support RESPECT NULLS, IGNORE NULLS option for LEAD, LAG, FIRST_VALUE, LAST_VALUE and NTH_VALUE window functions
[ https://issues.apache.org/jira/browse/CALCITE-883?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Julian Hyde updated CALCITE-883: Summary: Support RESPECT NULLS, IGNORE NULLS option for LEAD, LAG, FIRST_VALUE, LAST_VALUE and NTH_VALUE window functions (was: Support RESPECT NULLS, IGNORE NULLS option for LEAD, LAG, FIRST_VALUE, LAST_VALUE window functions) > Support RESPECT NULLS, IGNORE NULLS option for LEAD, LAG, FIRST_VALUE, > LAST_VALUE and NTH_VALUE window functions > > > Key: CALCITE-883 > URL: https://issues.apache.org/jira/browse/CALCITE-883 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: Sean Hsuan-Yi Chu >Assignee: Julian Hyde >Priority: Major > Labels: pull-request-available > Fix For: 1.20.0 > > Time Spent: 1.5h > Remaining Estimate: 0h > > This issue was brought up in Drill community: > (https://issues.apache.org/jira/browse/DRILL-3597) > For convenience, I copied and pasted the reporter Khurram Faraaz's > description here: > "The SQL standard defines a RESPECT NULLS or IGNORE NULLS option for lead, > lag, first_value, and last_value window functions." > In Calcite, we should have been able to recognized this option. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Updated] (CALCITE-2914) Improve how LatticeSuggester deduces foreign keys
[
https://issues.apache.org/jira/browse/CALCITE-2914?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated CALCITE-2914:
Labels: pull-request-available (was: )
> Improve how LatticeSuggester deduces foreign keys
> -
>
> Key: CALCITE-2914
> URL: https://issues.apache.org/jira/browse/CALCITE-2914
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Julian Hyde
>Assignee: Julian Hyde
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.20.0
>
>
> Improve how LatticeSuggester deduces foreign keys.
> Currently LatticeSuggester uses SqlStatisticProvider and deduces that a join
> between two tables is "many to one" if the left table has more rows than the
> right table. Clearly this is wrong, because it does not take into account the
> keys of the join.
> I think we will change the method
> {code:java}
> double tableCardinality(List qualifiedTableName){code}
> to
> {code:java}
> double tableCardinality(RelOptTable table){code}
> and add a method
> {code:java}
> boolean isForeignKey(RelOptTable fromTable, List fromColumns,
>RelOptTable toTable, List toColumns){code}
> For example, {{isForeignKey(empTable, ["DEPTNO"], deptTable, ["DEPTNO"])}}
> would return true.
> One valid implementation might look at the catalog for foreign key
> constraints; another might execute a query to see whether every row in emp is
> matched by exaclty one row in dept. Here is a query that might do
> it:{code:sql}
> select emp.deptno
> from (select distinct deptno from emp) as emp
> left join dept using (deptno)
> group by emp.deptno
> having count(dept.deptno) != 1{code}
> If the query returns any rows it is not a foreign key.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2914) Improve how LatticeSuggester deduces foreign keys
[
https://issues.apache.org/jira/browse/CALCITE-2914?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16806281#comment-16806281
]
Julian Hyde commented on CALCITE-2914:
--
[~danny0405] Would you like to review my branch now?
> Improve how LatticeSuggester deduces foreign keys
> -
>
> Key: CALCITE-2914
> URL: https://issues.apache.org/jira/browse/CALCITE-2914
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Julian Hyde
>Assignee: Julian Hyde
>Priority: Major
> Fix For: 1.20.0
>
>
> Improve how LatticeSuggester deduces foreign keys.
> Currently LatticeSuggester uses SqlStatisticProvider and deduces that a join
> between two tables is "many to one" if the left table has more rows than the
> right table. Clearly this is wrong, because it does not take into account the
> keys of the join.
> I think we will change the method
> {code:java}
> double tableCardinality(List qualifiedTableName){code}
> to
> {code:java}
> double tableCardinality(RelOptTable table){code}
> and add a method
> {code:java}
> boolean isForeignKey(RelOptTable fromTable, List fromColumns,
>RelOptTable toTable, List toColumns){code}
> For example, {{isForeignKey(empTable, ["DEPTNO"], deptTable, ["DEPTNO"])}}
> would return true.
> One valid implementation might look at the catalog for foreign key
> constraints; another might execute a query to see whether every row in emp is
> matched by exaclty one row in dept. Here is a query that might do
> it:{code:sql}
> select emp.deptno
> from (select distinct deptno from emp) as emp
> left join dept using (deptno)
> group by emp.deptno
> having count(dept.deptno) != 1{code}
> If the query returns any rows it is not a foreign key.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2954) SubQueryJoinRemoveRule and SubQueryProjectRemoveRule passing on empty set instead of set of correlation id
[
https://issues.apache.org/jira/browse/CALCITE-2954?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16806272#comment-16806272
]
Vineet Garg commented on CALCITE-2954:
--
[~hyuan] This test case fails even with the patch. I'll open a separate Jira
for this
> SubQueryJoinRemoveRule and SubQueryProjectRemoveRule passing on empty set
> instead of set of correlation id
> --
>
> Key: CALCITE-2954
> URL: https://issues.apache.org/jira/browse/CALCITE-2954
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Vineet Garg
>Assignee: Vineet Garg
>Priority: Major
> Labels: pull-request-available, sub-query
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Call to {{apply}} takes set of {{CorrelationId}} but both
> {{SubQueryJoinRemoveRule}} and {{SubQueryProjectRemoveRule}} are passing
> empty set for whatever reason.
> I believe the correct thing to do here is to pass on the actual set of
> {{CorrelationId}} like {{SubQueryFilterRemoveRule}}
> Ref:
> {code:java}
> final RexNode target = apply(e, ImmutableSet.of(),
> logic, builder, 2, fieldCount);
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (CALCITE-2959) Support collation on struct fields
[
https://issues.apache.org/jira/browse/CALCITE-2959?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16806083#comment-16806083
]
Stamatis Zampetakis commented on CALCITE-2959:
--
Postres and other major databases provide function indexes. The documentation
of Postgres says that it is possible to define indexes on expressions involving
on one or more columns of the table. I was a bit suprised to find out that the
following is not possible in Postgres:
{code:sql}
CREATE INDEX ON person (address.city);
{code}
The idea of defining specific operators for struct types is nice but does not
solve the problem of most complicated queries like the second example of
[~rubenql].
I was thinking that when we create the EnhancedScan operator we could set the
collation of the project on top to reflect that some columns are sorted. For
instance:
{noformat}
Sort(1) // --> Collation: [1]
Project(0=$1, 1=$2.city) // --> Collation: [1]
EnhancedScan(table=Person, sort=$2.city) // --> Collation: []
{noformat}
There are two problems with this approach.
# It appears that the project is doing the sort which is not the case.
# The collation of the project can easily get lost since rules tend to recreate
operators and during creation the collation of the project will be derived from
its input.
> Support collation on struct fields
> --
>
> Key: CALCITE-2959
> URL: https://issues.apache.org/jira/browse/CALCITE-2959
> Project: Calcite
> Issue Type: New Feature
>Reporter: Ruben Quesada Lopez
>Priority: Major
>
> Currently, the class {{RelFieldCollation}} is used to define _"the ordering
> of one field of a RelNode whose output is to be sorted"_. This representation
> can hold only "simple" fields. In case of struct fields, a projection needs
> to be applied in order to reference the struct field as a simple one. For
> example, given this table:
> {code}
> CREATE TYPE Address AS (
> street VARCHAR(20) NOT NULL,
> zipcode VARCHAR(20) NOT NULL,
> city VARCHAR(20) NOT NULL);
> CREATE TABLE Person (
> id VARCHAR(20) NOT NULL,
> name VARCHAR(20) NOT NULL,
> address Address NOT NULL);
> {code}
> With a SQL query such as: "{{SELECT p.name, p.address.city FROM Person p
> ORDER BY p.address.city}}" the pseudo-plan generated would look like:
> {code}
> Sort(1) // --> Collation: [1]
> Project(0=$1, 1=$2.city)
> Scan(table=Person)
> {code}
> However, what would happen if we had a specific Scan operator that would
> guarantee us that the records would be scanned already ordered by
> address.city? Something like:
> {code}
> EnhancedScan(table=Person, sort=$2.city) // --> Collation???
> {code}
> The collation of such an operator cannot be represented with the current
> Calcite capabilities (RelFieldCollation), because it would not be a "simple"
> field, but a struct field, i.e. we would need a new collation abstraction to
> represent it, e.g. [2.city] or [2.2]
> I would like to open the discuss to see if / how we could find a solution to
> represent this case.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
