Re: Re: Re: Join, SemiJoin, Correlate
SubPlan (in Postgres’ term) is a Postgres physical relational node to evaluate correlated subquery. What I mean is correlated subquery that can’t be decorrelated can’t be implemented by hashjoin or mergejoin. But it is off topic. Thanks ~ Haisheng Yuan -- 发件人：Walaa Eldin Moustafa 日 期：2019年03月21日 09:31:41 收件人： 抄 送：Stamatis Zampetakis 主 题：Re: Re: Join, SemiJoin, Correlate Agreed with Stamatis. Currently: 1) Correlate is tied to IN, EXISTS, NOT IN, NOT EXISTS, and 2) is used as an equivalent to nested loops join. The issues here are: 1) IN, EXISTS, NOT IN, NOT EXISTS can be rewritten as semi/anti joins, and 2) nested loops join is more of a physical operator. It seems that the minimal set of logical join types are INNER, LEFT, RIGHT, OUTER, SEMI, ANTI. So I think Calciate could have one LogicalJoin operator with an attribute to specify the join type (from the above), and a number of physical join operators (hash, merge, nested loops) whose implementation details depend on the the join type. What we lose by this model is the structure of the query (whether there was a sub-plan or not), but I would say that this is actually what is desired from a logical representation -- to abstract away from how the query is written, and how it is structured, as long as there is a canonical representation. There could also be a world where both models coexist (Correlate first then Decorrelate but in the light of a single logical join operator?). @Haisheng, generally, a sub-plan can also be implemented using a variant of hash or merge joins as long as we evaluate the sub-plan independently (without the join predicate), but that is up to the optimizer. Thanks, Walaa. On Wed, Mar 20, 2019 at 5:23 PM Haisheng Yuan wrote: > > SemiJoinType and its relationship with JoinRelType do confuse me a little bit. > > But I don’t think we should not have LogicalCorrelate. It is useful to > represent the lateral or correlated subquery (aka SubPlan in Postgres > jargon). The LogicalCorrelate can be implemented as NestLoopJoin in Calcite, > or SubPlan in Postgres’s terminology, but it can’t be implemented as HashJoin > or MergeJoin. > > Thanks ~ > Haisheng Yuan > -- > 发件人：Stamatis Zampetakis > 日 期：2019年03月21日 07:13:15 > 收件人： > 主 题：Re: Join, SemiJoin, Correlate > > I have bumped into this quite a few times and I think we should really try > to improve the design of the join hierarchy. > > From a logical point of view I think it makes sense to have the following > operators: > InnerJoin, LeftOuterJoin, FullOuterJoin, SemiJoin, AntiJoin, (GroupJoin) > > Yet I have not thought thoroughly what should become a class, and what a > property of the class (e.g., JoinRelType, SemiJoinType). > > Moreover, Correlate as it is right now, is basically a nested loop join (as > its Javadoc also indicates). > Nested loop join is most often encountered as a physical operator so I am > not sure if it should remain as is (in particular the LogicalCorrelate). > As we do not have HashJoin, MergeJoin, etc., operators at the logical > level, I think we should not have a NestedLoopJoin (aka., LogicalCorrelate). > There are valid reasons why Correlate was introduced in the first place but > I think we should rethink a bit the design and the needs. > > @Julian: I do not know to what extend you would like to rethink the > hierarchy but I have the impression that even small changes can easily > break backward compatibility. > > > Στις Τετ, 20 Μαρ 2019 στις 8:07 μ.μ., ο/η Julian Hyde > έγραψε: > > > I just discovered that Correlate, which is neither a Join nor a SemiJoin, > > uses SemiJoinType, but SemiJoin does not use SemiJoinType. > > > > Yuck. The Join/SemiJoin/Correlate type hierarchy needs some thought. > > > > Julian > > > > > > >
Re: cost
Hi: Your are right. Thanks a lot for your help Best Jocean.shi Vladimir Sitnikov 于2019年3月20日周三 下午8:42写道： > Jocean.shi> The result of this method is aways infinity > > Are you using Convention.NONE? > VolcanoPlanner assigns infinite cost for all the relations with Logical > convention (==Convention.NONE), so it is probably the reason. > > If you want to perform cost-based optimization, you probably want to > implement your own set of RelNodes and your own Convention. > > The relevant issue is https://issues.apache.org/jira/browse/CALCITE-2827 > > Vladimir >
Re: Re: Join, SemiJoin, Correlate
Agreed with Stamatis. Currently: 1) Correlate is tied to IN, EXISTS, NOT IN, NOT EXISTS, and 2) is used as an equivalent to nested loops join. The issues here are: 1) IN, EXISTS, NOT IN, NOT EXISTS can be rewritten as semi/anti joins, and 2) nested loops join is more of a physical operator. It seems that the minimal set of logical join types are INNER, LEFT, RIGHT, OUTER, SEMI, ANTI. So I think Calciate could have one LogicalJoin operator with an attribute to specify the join type (from the above), and a number of physical join operators (hash, merge, nested loops) whose implementation details depend on the the join type. What we lose by this model is the structure of the query (whether there was a sub-plan or not), but I would say that this is actually what is desired from a logical representation -- to abstract away from how the query is written, and how it is structured, as long as there is a canonical representation. There could also be a world where both models coexist (Correlate first then Decorrelate but in the light of a single logical join operator?). @Haisheng, generally, a sub-plan can also be implemented using a variant of hash or merge joins as long as we evaluate the sub-plan independently (without the join predicate), but that is up to the optimizer. Thanks, Walaa. On Wed, Mar 20, 2019 at 5:23 PM Haisheng Yuan wrote: > > SemiJoinType and its relationship with JoinRelType do confuse me a little bit. > > But I don’t think we should not have LogicalCorrelate. It is useful to > represent the lateral or correlated subquery (aka SubPlan in Postgres > jargon). The LogicalCorrelate can be implemented as NestLoopJoin in Calcite, > or SubPlan in Postgres’s terminology, but it can’t be implemented as HashJoin > or MergeJoin. > > Thanks ~ > Haisheng Yuan > -- > 发件人：Stamatis Zampetakis > 日 期：2019年03月21日 07:13:15 > 收件人： > 主 题：Re: Join, SemiJoin, Correlate > > I have bumped into this quite a few times and I think we should really try > to improve the design of the join hierarchy. > > From a logical point of view I think it makes sense to have the following > operators: > InnerJoin, LeftOuterJoin, FullOuterJoin, SemiJoin, AntiJoin, (GroupJoin) > > Yet I have not thought thoroughly what should become a class, and what a > property of the class (e.g., JoinRelType, SemiJoinType). > > Moreover, Correlate as it is right now, is basically a nested loop join (as > its Javadoc also indicates). > Nested loop join is most often encountered as a physical operator so I am > not sure if it should remain as is (in particular the LogicalCorrelate). > As we do not have HashJoin, MergeJoin, etc., operators at the logical > level, I think we should not have a NestedLoopJoin (aka., LogicalCorrelate). > There are valid reasons why Correlate was introduced in the first place but > I think we should rethink a bit the design and the needs. > > @Julian: I do not know to what extend you would like to rethink the > hierarchy but I have the impression that even small changes can easily > break backward compatibility. > > > Στις Τετ, 20 Μαρ 2019 στις 8:07 μ.μ., ο/η Julian Hyde > έγραψε: > > > I just discovered that Correlate, which is neither a Join nor a SemiJoin, > > uses SemiJoinType, but SemiJoin does not use SemiJoinType. > > > > Yuck. The Join/SemiJoin/Correlate type hierarchy needs some thought. > > > > Julian > > > > > > >
Re: Re: Join, SemiJoin, Correlate
SemiJoinType and its relationship with JoinRelType do confuse me a little bit. But I don’t think we should not have LogicalCorrelate. It is useful to represent the lateral or correlated subquery (aka SubPlan in Postgres jargon). The LogicalCorrelate can be implemented as NestLoopJoin in Calcite, or SubPlan in Postgres’s terminology, but it can’t be implemented as HashJoin or MergeJoin. Thanks ~ Haisheng Yuan -- 发件人：Stamatis Zampetakis 日 期：2019年03月21日 07:13:15 收件人： 主 题：Re: Join, SemiJoin, Correlate I have bumped into this quite a few times and I think we should really try to improve the design of the join hierarchy. From a logical point of view I think it makes sense to have the following operators: InnerJoin, LeftOuterJoin, FullOuterJoin, SemiJoin, AntiJoin, (GroupJoin) Yet I have not thought thoroughly what should become a class, and what a property of the class (e.g., JoinRelType, SemiJoinType). Moreover, Correlate as it is right now, is basically a nested loop join (as its Javadoc also indicates). Nested loop join is most often encountered as a physical operator so I am not sure if it should remain as is (in particular the LogicalCorrelate). As we do not have HashJoin, MergeJoin, etc., operators at the logical level, I think we should not have a NestedLoopJoin (aka., LogicalCorrelate). There are valid reasons why Correlate was introduced in the first place but I think we should rethink a bit the design and the needs. @Julian: I do not know to what extend you would like to rethink the hierarchy but I have the impression that even small changes can easily break backward compatibility. Στις Τετ, 20 Μαρ 2019 στις 8:07 μ.μ., ο/η Julian Hyde έγραψε: > I just discovered that Correlate, which is neither a Join nor a SemiJoin, > uses SemiJoinType, but SemiJoin does not use SemiJoinType. > > Yuck. The Join/SemiJoin/Correlate type hierarchy needs some thought. > > Julian > > >
Re: Join, SemiJoin, Correlate
I have bumped into this quite a few times and I think we should really try to improve the design of the join hierarchy. >From a logical point of view I think it makes sense to have the following operators: InnerJoin, LeftOuterJoin, FullOuterJoin, SemiJoin, AntiJoin, (GroupJoin) Yet I have not thought thoroughly what should become a class, and what a property of the class (e.g., JoinRelType, SemiJoinType). Moreover, Correlate as it is right now, is basically a nested loop join (as its Javadoc also indicates). Nested loop join is most often encountered as a physical operator so I am not sure if it should remain as is (in particular the LogicalCorrelate). As we do not have HashJoin, MergeJoin, etc., operators at the logical level, I think we should not have a NestedLoopJoin (aka., LogicalCorrelate). There are valid reasons why Correlate was introduced in the first place but I think we should rethink a bit the design and the needs. @Julian: I do not know to what extend you would like to rethink the hierarchy but I have the impression that even small changes can easily break backward compatibility. Στις Τετ, 20 Μαρ 2019 στις 8:07 μ.μ., ο/η Julian Hyde έγραψε: > I just discovered that Correlate, which is neither a Join nor a SemiJoin, > uses SemiJoinType, but SemiJoin does not use SemiJoinType. > > Yuck. The Join/SemiJoin/Correlate type hierarchy needs some thought. > > Julian > > >
Re: How to decide the fix version ?
It sort of depends. My view is that if you're going to assign it and work on it yourself so that it can make it into the ongoing release, set the fix version to the ongoing release, otherwise, set leave it as blank. If someone else picks up an issue with a blank fix version and is able to get it into the ongoing release, they should then update the field to the ongoing release. If you opened the issue and set it to the ongoing release, but were not able to work on it or felt that the issue wasn't that urgent after all, update the version to blank. During a release, the release manager will update the issues that were not completed for the current release to the next release. Francis On 21/03/2019 9:31 am, Muhammad Gelbana wrote: When a new Jira is created, should the fix version be set to the ongoing release ? The next one ? Or even left blank till its decided ? And how/when will it be decided ? Thanks, Gelbana
How to decide the fix version ?
When a new Jira is created, should the fix version be set to the ongoing release ? The next one ? Or even left blank till its decided ? And how/when will it be decided ? Thanks, Gelbana
Re: [VOTE] Release apache-calcite-1.19.0 (release candidate 2)
+1 (binding) Environment: maven:latest docker image (Maven 3.6.0, OpenJDK 11.0.2, Debian stretch). Verified SHA256 hash - OK Verified GPG signature - OK Ran tests using ./mvnw -DskipTests clean install and ./mvnw test - OK Checked history.md - OK Francis On 21/03/2019 5:05 am, Kevin Risden wrote: Hi all, I have created a build for Apache Calcite 1.19.0, release candidate 2. Thanks to everyone who has contributed to this release. Since RC 1, we have fixed the following issues: * [CALCITE-2929] Simplification of IS NULL checks are incorrectly assuming that CAST-s are possible * [CALCITE-2931] Mongo Adapter- Compare Bson (not string) query representation in tests * [CALCITE-2932] Update stale Druid integration test cases You can read the release notes here: https://github.com/apache/calcite/blob/branch-1.19/site/_docs/history.md The commit to be voted upon: https://gitbox.apache.org/repos/asf?p=calcite.git;a=commit;h=4143176acdb2860b3a80eb18e4cb1557f5969d13 Its hash is 4143176acdb2860b3a80eb18e4cb1557f5969d13. The artifacts to be voted on are located here: https://dist.apache.org/repos/dist/dev/calcite/apache-calcite-1.19.0-rc2/ The hashes of the artifacts are as follows: src.tar.gz.sha256 833fa3e9c97d8e89443f3b7a62fc6cce5f0e734836d82db1443425be6ac5dc65 A staged Maven repository is available for review at: https://repository.apache.org/content/repositories/orgapachecalcite-1057/ Release artifacts are signed with the following key: https://people.apache.org/keys/committer/krisden.asc Please vote on releasing this package as Apache Calcite 1.19.0. The vote is open for the next 72 hours and passes if a majority of at least three +1 PMC votes are cast. [ ] +1 Release this package as Apache Calcite 1.19.0 [ ] 0 I don't feel strongly about it, but I'm okay with the release [ ] -1 Do not release this package because... Here is my vote: +1 (binding) Kevin Risden
Join, SemiJoin, Correlate
I just discovered that Correlate, which is neither a Join nor a SemiJoin, uses SemiJoinType, but SemiJoin does not use SemiJoinType. Yuck. The Join/SemiJoin/Correlate type hierarchy needs some thought. Julian
Conditionally Adding Item to a Map
Hey All, New to Calcite, trying to express a select statement in SQL where i build a map. The thing is i want to conditionally add items to it, so for example have key 'c' with value from column 'c' only if the value is not null SELECT a,b, map['c',c, 'd', d] as my_map FROM... Is there a way to conditionally have key/values in the map? Thanks! Shahar
[VOTE] Release apache-calcite-1.19.0 (release candidate 2)
Hi all, I have created a build for Apache Calcite 1.19.0, release candidate 2. Thanks to everyone who has contributed to this release. Since RC 1, we have fixed the following issues: * [CALCITE-2929] Simplification of IS NULL checks are incorrectly assuming that CAST-s are possible * [CALCITE-2931] Mongo Adapter- Compare Bson (not string) query representation in tests * [CALCITE-2932] Update stale Druid integration test cases You can read the release notes here: https://github.com/apache/calcite/blob/branch-1.19/site/_docs/history.md The commit to be voted upon: https://gitbox.apache.org/repos/asf?p=calcite.git;a=commit;h=4143176acdb2860b3a80eb18e4cb1557f5969d13 Its hash is 4143176acdb2860b3a80eb18e4cb1557f5969d13. The artifacts to be voted on are located here: https://dist.apache.org/repos/dist/dev/calcite/apache-calcite-1.19.0-rc2/ The hashes of the artifacts are as follows: src.tar.gz.sha256 833fa3e9c97d8e89443f3b7a62fc6cce5f0e734836d82db1443425be6ac5dc65 A staged Maven repository is available for review at: https://repository.apache.org/content/repositories/orgapachecalcite-1057/ Release artifacts are signed with the following key: https://people.apache.org/keys/committer/krisden.asc Please vote on releasing this package as Apache Calcite 1.19.0. The vote is open for the next 72 hours and passes if a majority of at least three +1 PMC votes are cast. [ ] +1 Release this package as Apache Calcite 1.19.0 [ ] 0 I don't feel strongly about it, but I'm okay with the release [ ] -1 Do not release this package because... Here is my vote: +1 (binding) Kevin Risden
Re: [DISCUSS] Towards Calcite 1.19.0
RC0 and RC1 had a few issues identified. Those issues have been resolved and RC2 is being created and should be out later today. Kevin Risden On Mon, Mar 11, 2019 at 10:24 PM Yuzhao Chen wrote: > Nice job, Kevin > > Best, > Yuzhao Chen > 在 2019年3月11日 +0800 PM9:05， > Kevin Risden > ，写道： > > As of this morning, all JIRAs tagged for 1.19.0 have been resolved or > moved > > out to 1.20.0. > > > > I will be starting the release steps this morning. Please hold off on > > commits to master while the 1.19.0 release is happening. > > > > Kevin Risden > > > > > > On Wed, Mar 6, 2019 at 9:02 AM Kevin Risden wrote: > > > > > It looks like we haven't made any progress (JIRAs have been > opened/closed) > > > towards closing down JIRAs tagged for 1.19.0. There are still 14 (14 on > > > 2/27) open JIRAs tagged for 1.19.0. > > > > > > I will start moving those JIRAs out today to 1.20.0 so I can start to > > > close those this release. We are getting to mid March at this point > since > > > we keep postponing this release. > > > > > > Kevin Risden > > > > > > > > > On Thu, Feb 28, 2019 at 5:55 AM YuZhao Chan > wrote: > > > > > > > I would help to review some PRs this weekend，especially [1]. Hope to > help > > > > and release some of the burden. > > > > [1] > > > > > https://issues.apache.org/jira/browse/CALCITE-2018?jql=project%20%3D%20CALCITE%20AND%20resolution%20%3D%20Unresolved%20AND%20fixVersion%20in%20(EMPTY%2C%20%22next%22)%20AND%20labels%20%3D%20pull-request-available%20ORDER%20BY%20priority%20DESC > > > > > > > > Best, > > > > YuZhao Chan > > > > 在 2019年2月26日 +0800 AM8:14，Julian Hyde ，写道： > > > > > Hey everyone. > > > > > > > > > > There are 108 open pull requests. What are we going to do about it? > > > > > > > > > > Last release I reviewed and committed dozens of pull requests, and > I > > > > burned out. > > > > > > > > > > Julian > > > > > > > > > > > > > > > > On Feb 22, 2019, at 1:20 PM, > > > > Kevin Risden > > > > wrote: > > > > > > > > > > > > Yea I don't mind pushing out the RC towards the end of next week > > > > (beginning > > > > > > of March). I'm not in a rush to build the RC. I just picked > Monday to > > > > try > > > > > > to hit the target of February that was arbitrarily picked in the > > > > sand. At > > > > > > this point, the release will most likely happen in early March. > > > > > > > > > > > > Kevin Risden > > > > > > > > > > > > > > > > > > On Fri, Feb 22, 2019 at 4:15 PM Julian Hyde > wrote: > > > > > > > > > > > > > Can you do the RC towards the end of next week? > > > > > > > > > > > > > > I only just saw this message - 4 days after you sent it - > because > > > > I’ve > > > > > > > been fighting down a backlog of 500 Apache messages. There are > some > > > > changes > > > > > > > I would like to get into the release but I will need a few > days. > > > > > > > > > > > > > > Julian > > > > > > > > > > > > > > > > > > > > > > On Feb 18, 2019, at 9:56 AM, > > > > > > > Kevin Risden > > > > > > > wrote: > > > > > > > > > > > > > > > > It looks like there are some PRs to be reviewed and some > changes > > > > to get > > > > > > > > merged in this week. > > > > > > > > > > > > > > > > How does creating the first RC on Monday Feb 25th sound? Does > > > > that give > > > > > > > > enough time this week to get changes into Calcite 1.19.0? > > > > > > > > > > > > > > > > Kevin Risden > > > > > > > > > > > > > > > > > > > > > > > > On Wed, Feb 13, 2019 at 11:08 AM Zoltan Haindrich < > k...@rxd.hu> > > > > wrote: > > > > > > > > > > > > > > > > > > > > > > > > > > On 2/13/19 4:24 PM, Julian Hyde wrote: > > > > > > > > > > Sorry, there’s been a misunderstanding. Let me clarify. I > > > > didn’t say > > > > > > > > > that your patches were too small. Or intend to imply it. > > > > > > > > > > > > > > > > > > > > When I said “widespread changes for no good reason” - or > > > > something like > > > > > > > > > that - I meant changes to the RexNode format due to > removing IS > > > > TRUE > > > > > > > nodes. > > > > > > > > > > > > > > > > > > > > I like small patches, provided each one fixes a well > defined > > > > issue and > > > > > > > > > has appropriate tests. > > > > > > > > > > > > > > > > > > From now on I will try to provide a testcase when opening > the > > > > jira. > > > > > > > > > Sorry for the misunderstanding, thank you for making it > clear! > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Julian > > > > > > > > > > > > > > > > > > > > > On Feb 13, 2019, at 3:40 AM, Zoltan Haindrich < > k...@rxd.hu> > > > > wrote: > > > > > > > > > > > > > > > > > > > > > > Hello, > > > > > > > > > > > > > > > > > > > > > > In Hive I'm a little bit behind in upgrading to 1.18 > and > > > > although the > > > > > > > > > upgrade would not cause any correctness issues; but in a > sense > > > > it's more > > > > > > > > > conservative in doing some simplifications - which could be > > > > interpreted > > > > > > > as > > > > > > > > > regressions; if we take that into account that
Calcite-Master - Build # 1077 - Still Failing
The Apache Jenkins build system has built Calcite-Master (build #1077) Status: Still Failing Check console output at https://builds.apache.org/job/Calcite-Master/1077/ to view the results.
Re: How to start up the vm environment after i do Vagrant halt ?
There may be a manual way to start Druid but I wasn't sure. I poked around for a bit. I ran into the same issues while testing the test vm. halt / up doesn't restart all the services. I just created https://github.com/vlsi/calcite-test-dataset/issues/29 Kevin Risden On Wed, Mar 20, 2019 at 6:18 AM Yuzhao Chen wrote: > Hi, guys. > > After I did the cmd: vagrant halt and vagrant up, i can not connect to the > druid broker again, so what it the right way to halt and start up ? > > > Best, > Danny Chan >
[jira] [Created] (CALCITE-2938) Druid adapter do wrong behavior for casts from timestamp type to date type
Danny Chan created CALCITE-2938:
---
Summary: Druid adapter do wrong behavior for casts from timestamp
type to date type
Key: CALCITE-2938
URL: https://issues.apache.org/jira/browse/CALCITE-2938
Project: Calcite
Issue Type: Improvement
Components: druid
Affects Versions: 1.19.0
Reporter: Danny Chan
Assignee: Danny Chan
Fix For: 1.20.0
For sql like
{code:java}
Select cast("timestamp" as date) as t from "foodmart" order by t limit 1
{code}
It produces results:
3242-06-03
while it expects to be:
1997-01-01
You can past the code
{code:java}
@Test
public void testCastTimestamp() {
final String sql = "Select cast(\"timestamp\" as date) as t"
+ " from \"foodmart\" order by t limit 1";
sql(sql, FOODMART)
.returnsOrdered("T=1997-01-01 00:00:00")
.queryContains(
druidChecker("UTC"));
}
{code}
in DruidAdapterIT2 and reproduce it.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
Re: cost
Jocean.shi> The result of this method is aways infinity Are you using Convention.NONE? VolcanoPlanner assigns infinite cost for all the relations with Logical convention (==Convention.NONE), so it is probably the reason. If you want to perform cost-based optimization, you probably want to implement your own set of RelNodes and your own Convention. The relevant issue is https://issues.apache.org/jira/browse/CALCITE-2827 Vladimir
cost
Hi:
I define some rules, and the cost method of my RelNode is
@Override
public RelOptCost computeSelfCost(RelOptPlanner planner, RelMetadataQuery mq) {
return super.computeSelfCost(planner, mq).multiplyBy(.1);
}
The result of this method is aways infinity.
why? , and How do volcano calculate cost?
Best
Jocean.shi
How to start up the vm environment after i do Vagrant halt ?
Hi, guys. After I did the cmd: vagrant halt and vagrant up, i can not connect to the druid broker again, so what it the right way to halt and start up ? Best, Danny Chan
[jira] [Created] (CALCITE-2937) Linq4j: implement LazyEnumerable
Ruben Quesada Lopez created CALCITE-2937: Summary: Linq4j: implement LazyEnumerable Key: CALCITE-2937 URL: https://issues.apache.org/jira/browse/CALCITE-2937 Project: Calcite Issue Type: New Feature Reporter: Ruben Quesada Lopez Implement a LazyEnumerable: an Enumerable whose computation (via a Supplier) will be postponed until it is actually required. An example of use case could be CALCITE-2909, where a semiJoin implementation can be optimized by delaying the computation of the innerLookup until the moment when we are sure that it will be really needed, i.e. when the first outer enumerator item is processed. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Created] (CALCITE-2936) Existential subquery that has aggregate without grouping key should be simplified
Haisheng Yuan created CALCITE-2936:
--
Summary: Existential subquery that has aggregate without grouping
key should be simplified
Key: CALCITE-2936
URL: https://issues.apache.org/jira/browse/CALCITE-2936
Project: Calcite
Issue Type: New Feature
Reporter: Haisheng Yuan
An existential subquery whose inner child is an aggregate with no grouping
columns should be simplified to a Boolean constant.
Example:
exists(select sum(i) from X) --> true
not exists(select sum(i) from X) --> false
Repro:
{code:java}
@Test public void testExistentialSubquery() {
final String sql = "SELECT e1.empno\n"
+ "FROM emp e1 where exists\n"
+ "(select avg(sal) from emp e2 where e1.empno = e2.empno )";
sql(sql).decorrelate(true).ok();
}
{code}
We got plan:
{code:java}
LogicalProject(EMPNO=[$0])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[CAST($9):INTEGER],
$f1=[CAST($10):BOOLEAN])
LogicalJoin(condition=[=($0, $9)], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
LogicalProject(EMPNO=[$0], $f0=[true])
LogicalAggregate(group=[{0}], EXPR$0=[AVG($1)])
LogicalProject(EMPNO=[$0], SAL=[$5])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
{code}
The preferred plan should be:
{code:java}
LogicalProject(EMPNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
{code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
