You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Hrudaya Reddy <hr...@csod.com.INVALID> on 2021/01/25 02:21:48 UTC
Request help in implementing subquery in WHERE condition
Hi everyone,
I am trying to implement a subquery in the WHERE condition.
My query is similar to below,
SELECT [org].[ref],
[users].[ref]
FROM [req]
LEFT JOIN [org] ON ([req].[location_id] = [org].[org_id])
LEFT JOIN [appl] ON ([req].[req_id] = [appl].[req_id])
LEFT JOIN [users] ON ([appl].[user_id] = [users].[user_id])
WHERE org.org_id NOT IN (SELECT org_hier.child_id
FROM [org]
INNER JOIN [org_hier] ON [org].[org_id] = [org_hier].[parent_id]
WHERE [org].[ref] = 'India')
One of the ways I found to achieve the above was using antijoin with which I am able to implement the below query
SELECT [org].[ref]
FROM [req]
LEFT JOIN [org] ON ([req].[location_id] = [org].[org_id])
WHERE NOT EXISTS (SELECT 1
FROM [org]
INNER JOIN [org_hier] ON [org].[org_id] = [org_hier].[parent_id]
WHERE [org].[ref] = 'India' AND [req].[location_id] = [org_hier].[child_id])
But when I try to modify the relbuilder for the above query to add additional scans and joins for the 'users' and 'appl' tables, my output query remains the same.
I would really appreciate any help with this.
Thank you.
Regards,
Hrudaya
This message, together with any attachments, is intended only for the use of the individual or entity to which it is addressed and may contain confidential and/or privileged information. If you are not the intended recipient(s), or the employee or agent responsible for delivery of this message to the intended recipient(s), you are hereby notified that any dissemination, distribution or copying of this message, or any attachment, is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete the message, together with any attachments, from your computer. Thank you for your cooperation.
RE: Re: Re: Request help in implementing subquery in WHERE condition
Posted by Hrudaya Reddy <hr...@csod.com.INVALID>.
Thanks Stamatis. The link you shared is really helpul.
-----Original Message-----
From: Stamatis Zampetakis <za...@gmail.com>
Sent: Wednesday, January 27, 2021 2:33 AM
To: dev@calcite.apache.org
Subject: [EXTERNAL] Re: Re: Request help in implementing subquery in WHERE condition
Hi Hrudaya,
In the code you shared previously you seem to miss the connecting operation between the two sub plans.
RelBuilder is closer to relational algebra (plan) than it is to SQL so trying to replicate what an SQL query does with the builder may not be straightforward and requires a bit of practice.
If you have an SQL query and you can obtain the plan then you may be able to replicate it with the RelBuilder by introducing the operations following a post-order traversal of the tree.
For your use-case you can find an example combining left joins and anti joins in [1].
Best,
Stamatis
[1]
https://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_zabetak_calcite_blob_ad1e9e30a62ac9070581ac280df5fd38279dbcaa_core_src_test_java_org_apache_calcite_test_RelBuilderTest.java-23L2224&d=DwIFaQ&c=IrjCfGj3zOfzN2DRIASEFg&r=MjX6IuQXq8W7yQ_nB4S_UQ&m=qGjLh0-NVxYeRVlTidxZrFk5z-1bVEEVcbafMRYGwCY&s=xBlCfalV3xNBQT8znhuvy_44VD7iPVb3QGj3wwMcdOM&e=
On Tue, Jan 26, 2021 at 6:28 PM Hrudaya Reddy <hr...@csod.com.invalid>
wrote:
> Hi Julian/Stamatis,
>
> Thanks for your input.
>
> I am doing a build() only at the end after adding the additional scans
> and joins.
> I just gave the intermediate sql query as a reference.
>
> This is essentially what we are trying to implement:
> https://urldefense.proofpoint.com/v2/url?u=https-3A__issues.apache.org
> _jira_browse_CALCITE-2D1493&d=DwIFaQ&c=IrjCfGj3zOfzN2DRIASEFg&r=MjX6Iu
> QXq8W7yQ_nB4S_UQ&m=qGjLh0-NVxYeRVlTidxZrFk5z-1bVEEVcbafMRYGwCY&s=vI6nZ
> XrDcCbOrqtavdiedlZ4BK7HQcZg9MpceetsSDs&e=
> select sal from emp where empno NOT IN (select deptno from dept where
> emp.job = dept.name) I am very new to calcite and still trying to
> understand how relbuilder works.
> I am not sure how to construct the relbuilder for the query in
> https://urldefense.proofpoint.com/v2/url?u=https-3A__issues.apache.org
> _jira_browse_CALCITE-2D1493&d=DwIFaQ&c=IrjCfGj3zOfzN2DRIASEFg&r=MjX6Iu
> QXq8W7yQ_nB4S_UQ&m=qGjLh0-NVxYeRVlTidxZrFk5z-1bVEEVcbafMRYGwCY&s=vI6nZ
> XrDcCbOrqtavdiedlZ4BK7HQcZg9MpceetsSDs&e=
> I would appreciate any help with this.
>
> Regards,
> Hrudaya
>
> -----Original Message-----
> From: Julian Hyde <jh...@gmail.com>
> Sent: Tuesday, January 26, 2021 9:25 AM
> To: dev@calcite.apache.org
> Subject: [EXTERNAL] Re: Request help in implementing subquery in WHERE
> condition
>
> Yes; I usually only call build() once per RelBuilder program, and
> leave nodes on the stack meantime. If you build and then push the same
> RelNode back, RelBuilder loses a bit of information (the mapping of
> table and column aliases to fields) and therefore may not be able to
> resolve the expression if you use aliases.
>
> Julian
>
> > On Jan 26, 2021, at 1:30 AM, Stamatis Zampetakis <za...@gmail.com>
> wrote:
> >
> > Hi Hrudaya,
> >
> > Not sure how you display the SQL query and what interleaves the
> > first and second calls to RelBuilder but note that
> > RelBuilder#build() [1] method pops an element from the stack.
> > This means that if you call build to display the first query then in
> > this case the builder will be empty afterwards and you start from
> scratch.
> >
> > Best,
> > Stamatis
> >
> > [1]
> > https://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_apac
> > he
> > _calcite_blob_a16a47351aa25caf538c1955edd171ec871569f7_core_src_main
> > _j
> > ava_org_apache_calcite_tools_RelBuilder.java-23L354&d=DwIFaQ&c=IrjCf
> > Gj
> > 3zOfzN2DRIASEFg&r=MjX6IuQXq8W7yQ_nB4S_UQ&m=TwlF9jcqt67uz1C-azDyw8Imy
> > 9Q kikPpnxKdef3TU4g&s=XNJp00_GyX-SkEBU9fPsTM-UB89idB-H1NcVk6NHrr4&e=
> >
> >> On Mon, Jan 25, 2021 at 6:08 PM Hrudaya Reddy
> >> <hr...@csod.com.invalid>
> >> wrote:
> >>
> >> Hi everyone,
> >>
> >> Please find my current implementation for the subquery in where
> >> condition
> >>
> >> final FrameworkConfig config = config().build();
> >> final RelBuilder relBuilder = RelBuilder.create(config);
> >> RelNode left1 = relBuilder.scan("req").build();
> >> RelNode right1 = relBuilder.scan("org").build();
> >> RelNode right2 = relBuilder.scan("org_hier").build();
> >>
> >> relBuilder
> >> .push(left1)
> >> .push(right1)
> >> .push(right2)
> >> .join(
> >> JoinRelType.INNER,
> >> relBuilder.equals(
> >> relBuilder.field(2, "org", "org_id"),
> >> relBuilder.field(2, "org_hier", "parent_id")))
> >> .antiJoin(
> >> relBuilder.equals(
> >> relBuilder.field(2, "org", "ref"),
> >> relBuilder.literal("India")),
> >> relBuilder.equals(
> >> relBuilder.field(2, "req", "location_id"),
> >> relBuilder.field(2, "org_hier", "child_id")));
> >>
> >> /// At this point relbuilder returns the below query
> >>
> >> SELECT * FROM [req]
> >> LEFT JOIN [org] ON ([req].[location_id] = [org].[org_id])
> >> WHERE NOT EXISTS (SELECT 1 FROM [org] INNER JOIN
> >> [org_hier] ON [org].[org_id] = [org_hier].[parent_id] WHERE
> >> [org].[ref] = 'India' AND [req].[location_id] =
> >> [org_hier].[child_id])
> >>
> >> relBuilder.scan("req").as("jr")
> >> .scan("org").as("org1")
> >> .join(JoinRelType.LEFT,
> >> relBuilder.equals(
> >> relBuilder.field(2, "jr", "location_id"),
> >> relBuilder.field(2, "org1", "org_id")))
> >> .scan("appl").as("ja")
> >> .join(JoinRelType.LEFT,
> >> relBuilder.equals(
> >> relBuilder.field(2, "jr", "req_id"),
> >> relBuilder.field(2, "ja", "req_id")))
> >> .scan("users").as("u")
> >> .join(JoinRelType.LEFT,
> >> relBuilder.equals(
> >> relBuilder.field(2, "ja", "user_id"),
> >> relBuilder.field(2, "u", "user_id")));
> >>
> >> relBuilder.project(relBuilder.field("org1", "ref"));
> >>
> >> /// When I do additional scans and joins, my query changes to
> >>
> >> SELECT [org].[ref]
> >> FROM [req]
> >> LEFT JOIN [org] ON [req].[location_id] = [org].[org_id] LEFT JOIN
> >> [appl] ON [req].[req_id] = [appl].[req_id] LEFT JOIN [users] ON
> >> [appl].[user_id] = [users].[user_id]
> >>
> >> I would really appreciate if you could point out any mistakes I am
> >> making with my implementation.
> >>
> >> Thanks in advance.
> >>
> >> Regards,
> >> Hrudaya
> >>
> >> From: Hrudaya Reddy
> >> Sent: Sunday, January 24, 2021 6:22 PM
> >> To: dev@calcite.apache.org
> >> Subject: Request help in implementing subquery in WHERE condition
> >>
> >> Hi everyone,
> >>
> >> I am trying to implement a subquery in the WHERE condition.
> >> My query is similar to below,
> >>
> >> SELECT [org].[ref],
> >> [users].[ref]
> >> FROM [req]
> >> LEFT JOIN [org] ON ([req].[location_id] = [org].[org_id])
> >> LEFT JOIN [appl] ON ([req].[req_id] = [appl].[req_id])
> >> LEFT JOIN [users] ON ([appl].[user_id] = [users].[user_id])
> >> WHERE org.org_id NOT IN (SELECT org_hier.child_id
> >> FROM [org] INNER JOIN [org_hier] ON [org].[org_id] =
> >> [org_hier].[parent_id] WHERE [org].[ref] = 'India')
> >>
> >> One of the ways I found to achieve the above was using antijoin
> >> with which I am able to implement the below query
> >>
> >> SELECT [org].[ref]
> >> FROM [req]
> >> LEFT JOIN [org] ON ([req].[location_id] = [org].[org_id])
> >> WHERE NOT EXISTS (SELECT 1 FROM [org] INNER JOIN
> >> [org_hier] ON [org].[org_id] = [org_hier].[parent_id] WHERE
> >> [org].[ref] = 'India' AND [req].[location_id] =
> >> [org_hier].[child_id])
> >>
> >> But when I try to modify the relbuilder for the above query to add
> >> additional scans and joins for the 'users' and 'appl' tables, my
> >> output query remains the same.
> >> I would really appreciate any help with this.
> >>
> >> Thank you.
> >>
> >> Regards,
> >> Hrudaya
> >>
> >> This message, together with any attachments, is intended only for
> >> the use of the individual or entity to which it is addressed and
> >> may contain confidential and/or privileged information. If you are
> >> not the intended recipient(s), or the employee or agent responsible
> >> for delivery of this message to the intended recipient(s), you are
> >> hereby notified that any dissemination, distribution or copying of
> >> this message, or any attachment, is strictly prohibited. If you
> >> have received this message in error, please immediately notify the
> >> sender and delete the message, together with any attachments, from
> >> your
> computer. Thank you for your cooperation.
> >>
>
> ----------------------------------------------------------------------
> *EXTERNAL MESSAGE WARNING: This email originated from outside of
> Cornerstone. Do not click links or open attachments unless you
> recognize the sender and know the content is safe. Please see this
> wiki for more information on email safety:
> https://wiki.cornerstoneondemand.com/display/ISS/Security+Awareness
> This message, together with any attachments, is intended only for the
> use of the individual or entity to which it is addressed and may
> contain confidential and/or privileged information. If you are not the
> intended recipient(s), or the employee or agent responsible for
> delivery of this message to the intended recipient(s), you are hereby
> notified that any dissemination, distribution or copying of this
> message, or any attachment, is strictly prohibited. If you have
> received this message in error, please immediately notify the sender
> and delete the message, together with any attachments, from your computer. Thank you for your cooperation.
>
----------------------------------------------------------------------
*EXTERNAL MESSAGE WARNING: This email originated from outside of Cornerstone. Do not click links or open attachments unless you recognize the sender and know the content is safe. Please see this wiki for more information on email safety: https://wiki.cornerstoneondemand.com/display/ISS/Security+Awareness
This message, together with any attachments, is intended only for the use of the individual or entity to which it is addressed and may contain confidential and/or privileged information. If you are not the intended recipient(s), or the employee or agent responsible for delivery of this message to the intended recipient(s), you are hereby notified that any dissemination, distribution or copying of this message, or any attachment, is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete the message, together with any attachments, from your computer. Thank you for your cooperation.
Re: Re: Request help in implementing subquery in WHERE condition
Posted by Stamatis Zampetakis <za...@gmail.com>.
Hi Hrudaya,
In the code you shared previously you seem to miss the connecting operation
between the two sub plans.
RelBuilder is closer to relational algebra (plan) than it is to SQL so
trying to replicate what an SQL query does with the builder may not be
straightforward and requires a bit of practice.
If you have an SQL query and you can obtain the plan then you may be able
to replicate it with the RelBuilder by introducing the operations following
a post-order traversal of the tree.
For your use-case you can find an example combining left joins and anti
joins in [1].
Best,
Stamatis
[1]
https://github.com/zabetak/calcite/blob/ad1e9e30a62ac9070581ac280df5fd38279dbcaa/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java#L2224
On Tue, Jan 26, 2021 at 6:28 PM Hrudaya Reddy <hr...@csod.com.invalid>
wrote:
> Hi Julian/Stamatis,
>
> Thanks for your input.
>
> I am doing a build() only at the end after adding the additional scans and
> joins.
> I just gave the intermediate sql query as a reference.
>
> This is essentially what we are trying to implement:
> https://issues.apache.org/jira/browse/CALCITE-1493
> select sal from emp where empno NOT IN (select deptno from dept where
> emp.job = dept.name)
> I am very new to calcite and still trying to understand how relbuilder
> works.
> I am not sure how to construct the relbuilder for the query in
> https://issues.apache.org/jira/browse/CALCITE-1493
> I would appreciate any help with this.
>
> Regards,
> Hrudaya
>
> -----Original Message-----
> From: Julian Hyde <jh...@gmail.com>
> Sent: Tuesday, January 26, 2021 9:25 AM
> To: dev@calcite.apache.org
> Subject: [EXTERNAL] Re: Request help in implementing subquery in WHERE
> condition
>
> Yes; I usually only call build() once per RelBuilder program, and leave
> nodes on the stack meantime. If you build and then push the same RelNode
> back, RelBuilder loses a bit of information (the mapping of table and
> column aliases to fields) and therefore may not be able to resolve the
> expression if you use aliases.
>
> Julian
>
> > On Jan 26, 2021, at 1:30 AM, Stamatis Zampetakis <za...@gmail.com>
> wrote:
> >
> > Hi Hrudaya,
> >
> > Not sure how you display the SQL query and what interleaves the first
> > and second calls to RelBuilder but note that RelBuilder#build() [1]
> > method pops an element from the stack.
> > This means that if you call build to display the first query then in
> > this case the builder will be empty afterwards and you start from
> scratch.
> >
> > Best,
> > Stamatis
> >
> > [1]
> > https://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_apache
> > _calcite_blob_a16a47351aa25caf538c1955edd171ec871569f7_core_src_main_j
> > ava_org_apache_calcite_tools_RelBuilder.java-23L354&d=DwIFaQ&c=IrjCfGj
> > 3zOfzN2DRIASEFg&r=MjX6IuQXq8W7yQ_nB4S_UQ&m=TwlF9jcqt67uz1C-azDyw8Imy9Q
> > kikPpnxKdef3TU4g&s=XNJp00_GyX-SkEBU9fPsTM-UB89idB-H1NcVk6NHrr4&e=
> >
> >> On Mon, Jan 25, 2021 at 6:08 PM Hrudaya Reddy
> >> <hr...@csod.com.invalid>
> >> wrote:
> >>
> >> Hi everyone,
> >>
> >> Please find my current implementation for the subquery in where
> >> condition
> >>
> >> final FrameworkConfig config = config().build();
> >> final RelBuilder relBuilder = RelBuilder.create(config);
> >> RelNode left1 = relBuilder.scan("req").build();
> >> RelNode right1 = relBuilder.scan("org").build();
> >> RelNode right2 = relBuilder.scan("org_hier").build();
> >>
> >> relBuilder
> >> .push(left1)
> >> .push(right1)
> >> .push(right2)
> >> .join(
> >> JoinRelType.INNER,
> >> relBuilder.equals(
> >> relBuilder.field(2, "org", "org_id"),
> >> relBuilder.field(2, "org_hier", "parent_id")))
> >> .antiJoin(
> >> relBuilder.equals(
> >> relBuilder.field(2, "org", "ref"),
> >> relBuilder.literal("India")),
> >> relBuilder.equals(
> >> relBuilder.field(2, "req", "location_id"),
> >> relBuilder.field(2, "org_hier", "child_id")));
> >>
> >> /// At this point relbuilder returns the below query
> >>
> >> SELECT * FROM [req]
> >> LEFT JOIN [org] ON ([req].[location_id] = [org].[org_id])
> >> WHERE NOT EXISTS (SELECT 1 FROM [org] INNER JOIN
> >> [org_hier] ON [org].[org_id] = [org_hier].[parent_id] WHERE
> >> [org].[ref] = 'India' AND [req].[location_id] =
> >> [org_hier].[child_id])
> >>
> >> relBuilder.scan("req").as("jr")
> >> .scan("org").as("org1")
> >> .join(JoinRelType.LEFT,
> >> relBuilder.equals(
> >> relBuilder.field(2, "jr", "location_id"),
> >> relBuilder.field(2, "org1", "org_id")))
> >> .scan("appl").as("ja")
> >> .join(JoinRelType.LEFT,
> >> relBuilder.equals(
> >> relBuilder.field(2, "jr", "req_id"),
> >> relBuilder.field(2, "ja", "req_id")))
> >> .scan("users").as("u")
> >> .join(JoinRelType.LEFT,
> >> relBuilder.equals(
> >> relBuilder.field(2, "ja", "user_id"),
> >> relBuilder.field(2, "u", "user_id")));
> >>
> >> relBuilder.project(relBuilder.field("org1", "ref"));
> >>
> >> /// When I do additional scans and joins, my query changes to
> >>
> >> SELECT [org].[ref]
> >> FROM [req]
> >> LEFT JOIN [org] ON [req].[location_id] = [org].[org_id] LEFT JOIN
> >> [appl] ON [req].[req_id] = [appl].[req_id] LEFT JOIN [users] ON
> >> [appl].[user_id] = [users].[user_id]
> >>
> >> I would really appreciate if you could point out any mistakes I am
> >> making with my implementation.
> >>
> >> Thanks in advance.
> >>
> >> Regards,
> >> Hrudaya
> >>
> >> From: Hrudaya Reddy
> >> Sent: Sunday, January 24, 2021 6:22 PM
> >> To: dev@calcite.apache.org
> >> Subject: Request help in implementing subquery in WHERE condition
> >>
> >> Hi everyone,
> >>
> >> I am trying to implement a subquery in the WHERE condition.
> >> My query is similar to below,
> >>
> >> SELECT [org].[ref],
> >> [users].[ref]
> >> FROM [req]
> >> LEFT JOIN [org] ON ([req].[location_id] = [org].[org_id])
> >> LEFT JOIN [appl] ON ([req].[req_id] = [appl].[req_id])
> >> LEFT JOIN [users] ON ([appl].[user_id] = [users].[user_id])
> >> WHERE org.org_id NOT IN (SELECT org_hier.child_id FROM
> >> [org] INNER JOIN [org_hier] ON [org].[org_id] =
> >> [org_hier].[parent_id] WHERE [org].[ref] = 'India')
> >>
> >> One of the ways I found to achieve the above was using antijoin with
> >> which I am able to implement the below query
> >>
> >> SELECT [org].[ref]
> >> FROM [req]
> >> LEFT JOIN [org] ON ([req].[location_id] = [org].[org_id])
> >> WHERE NOT EXISTS (SELECT 1 FROM [org] INNER JOIN
> >> [org_hier] ON [org].[org_id] = [org_hier].[parent_id] WHERE
> >> [org].[ref] = 'India' AND [req].[location_id] =
> >> [org_hier].[child_id])
> >>
> >> But when I try to modify the relbuilder for the above query to add
> >> additional scans and joins for the 'users' and 'appl' tables, my
> >> output query remains the same.
> >> I would really appreciate any help with this.
> >>
> >> Thank you.
> >>
> >> Regards,
> >> Hrudaya
> >>
> >> This message, together with any attachments, is intended only for the
> >> use of the individual or entity to which it is addressed and may
> >> contain confidential and/or privileged information. If you are not
> >> the intended recipient(s), or the employee or agent responsible for
> >> delivery of this message to the intended recipient(s), you are hereby
> >> notified that any dissemination, distribution or copying of this
> >> message, or any attachment, is strictly prohibited. If you have
> >> received this message in error, please immediately notify the sender
> >> and delete the message, together with any attachments, from your
> computer. Thank you for your cooperation.
> >>
>
> ----------------------------------------------------------------------
> *EXTERNAL MESSAGE WARNING: This email originated from outside of
> Cornerstone. Do not click links or open attachments unless you recognize
> the sender and know the content is safe. Please see this wiki for more
> information on email safety:
> https://wiki.cornerstoneondemand.com/display/ISS/Security+Awareness
> This message, together with any attachments, is intended only for the use
> of the individual or entity to which it is addressed and may contain
> confidential and/or privileged information. If you are not the intended
> recipient(s), or the employee or agent responsible for delivery of this
> message to the intended recipient(s), you are hereby notified that any
> dissemination, distribution or copying of this message, or any attachment,
> is strictly prohibited. If you have received this message in error, please
> immediately notify the sender and delete the message, together with any
> attachments, from your computer. Thank you for your cooperation.
>
RE: Re: Request help in implementing subquery in WHERE condition
Posted by Hrudaya Reddy <hr...@csod.com.INVALID>.
Hi Julian/Stamatis,
Thanks for your input.
I am doing a build() only at the end after adding the additional scans and joins.
I just gave the intermediate sql query as a reference.
This is essentially what we are trying to implement: https://issues.apache.org/jira/browse/CALCITE-1493
select sal from emp where empno NOT IN (select deptno from dept where emp.job = dept.name)
I am very new to calcite and still trying to understand how relbuilder works.
I am not sure how to construct the relbuilder for the query in https://issues.apache.org/jira/browse/CALCITE-1493
I would appreciate any help with this.
Regards,
Hrudaya
-----Original Message-----
From: Julian Hyde <jh...@gmail.com>
Sent: Tuesday, January 26, 2021 9:25 AM
To: dev@calcite.apache.org
Subject: [EXTERNAL] Re: Request help in implementing subquery in WHERE condition
Yes; I usually only call build() once per RelBuilder program, and leave nodes on the stack meantime. If you build and then push the same RelNode back, RelBuilder loses a bit of information (the mapping of table and column aliases to fields) and therefore may not be able to resolve the expression if you use aliases.
Julian
> On Jan 26, 2021, at 1:30 AM, Stamatis Zampetakis <za...@gmail.com> wrote:
>
> Hi Hrudaya,
>
> Not sure how you display the SQL query and what interleaves the first
> and second calls to RelBuilder but note that RelBuilder#build() [1]
> method pops an element from the stack.
> This means that if you call build to display the first query then in
> this case the builder will be empty afterwards and you start from scratch.
>
> Best,
> Stamatis
>
> [1]
> https://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_apache
> _calcite_blob_a16a47351aa25caf538c1955edd171ec871569f7_core_src_main_j
> ava_org_apache_calcite_tools_RelBuilder.java-23L354&d=DwIFaQ&c=IrjCfGj
> 3zOfzN2DRIASEFg&r=MjX6IuQXq8W7yQ_nB4S_UQ&m=TwlF9jcqt67uz1C-azDyw8Imy9Q
> kikPpnxKdef3TU4g&s=XNJp00_GyX-SkEBU9fPsTM-UB89idB-H1NcVk6NHrr4&e=
>
>> On Mon, Jan 25, 2021 at 6:08 PM Hrudaya Reddy
>> <hr...@csod.com.invalid>
>> wrote:
>>
>> Hi everyone,
>>
>> Please find my current implementation for the subquery in where
>> condition
>>
>> final FrameworkConfig config = config().build();
>> final RelBuilder relBuilder = RelBuilder.create(config);
>> RelNode left1 = relBuilder.scan("req").build();
>> RelNode right1 = relBuilder.scan("org").build();
>> RelNode right2 = relBuilder.scan("org_hier").build();
>>
>> relBuilder
>> .push(left1)
>> .push(right1)
>> .push(right2)
>> .join(
>> JoinRelType.INNER,
>> relBuilder.equals(
>> relBuilder.field(2, "org", "org_id"),
>> relBuilder.field(2, "org_hier", "parent_id")))
>> .antiJoin(
>> relBuilder.equals(
>> relBuilder.field(2, "org", "ref"),
>> relBuilder.literal("India")),
>> relBuilder.equals(
>> relBuilder.field(2, "req", "location_id"),
>> relBuilder.field(2, "org_hier", "child_id")));
>>
>> /// At this point relbuilder returns the below query
>>
>> SELECT * FROM [req]
>> LEFT JOIN [org] ON ([req].[location_id] = [org].[org_id])
>> WHERE NOT EXISTS (SELECT 1 FROM [org] INNER JOIN
>> [org_hier] ON [org].[org_id] = [org_hier].[parent_id] WHERE
>> [org].[ref] = 'India' AND [req].[location_id] =
>> [org_hier].[child_id])
>>
>> relBuilder.scan("req").as("jr")
>> .scan("org").as("org1")
>> .join(JoinRelType.LEFT,
>> relBuilder.equals(
>> relBuilder.field(2, "jr", "location_id"),
>> relBuilder.field(2, "org1", "org_id")))
>> .scan("appl").as("ja")
>> .join(JoinRelType.LEFT,
>> relBuilder.equals(
>> relBuilder.field(2, "jr", "req_id"),
>> relBuilder.field(2, "ja", "req_id")))
>> .scan("users").as("u")
>> .join(JoinRelType.LEFT,
>> relBuilder.equals(
>> relBuilder.field(2, "ja", "user_id"),
>> relBuilder.field(2, "u", "user_id")));
>>
>> relBuilder.project(relBuilder.field("org1", "ref"));
>>
>> /// When I do additional scans and joins, my query changes to
>>
>> SELECT [org].[ref]
>> FROM [req]
>> LEFT JOIN [org] ON [req].[location_id] = [org].[org_id] LEFT JOIN
>> [appl] ON [req].[req_id] = [appl].[req_id] LEFT JOIN [users] ON
>> [appl].[user_id] = [users].[user_id]
>>
>> I would really appreciate if you could point out any mistakes I am
>> making with my implementation.
>>
>> Thanks in advance.
>>
>> Regards,
>> Hrudaya
>>
>> From: Hrudaya Reddy
>> Sent: Sunday, January 24, 2021 6:22 PM
>> To: dev@calcite.apache.org
>> Subject: Request help in implementing subquery in WHERE condition
>>
>> Hi everyone,
>>
>> I am trying to implement a subquery in the WHERE condition.
>> My query is similar to below,
>>
>> SELECT [org].[ref],
>> [users].[ref]
>> FROM [req]
>> LEFT JOIN [org] ON ([req].[location_id] = [org].[org_id])
>> LEFT JOIN [appl] ON ([req].[req_id] = [appl].[req_id])
>> LEFT JOIN [users] ON ([appl].[user_id] = [users].[user_id])
>> WHERE org.org_id NOT IN (SELECT org_hier.child_id FROM
>> [org] INNER JOIN [org_hier] ON [org].[org_id] =
>> [org_hier].[parent_id] WHERE [org].[ref] = 'India')
>>
>> One of the ways I found to achieve the above was using antijoin with
>> which I am able to implement the below query
>>
>> SELECT [org].[ref]
>> FROM [req]
>> LEFT JOIN [org] ON ([req].[location_id] = [org].[org_id])
>> WHERE NOT EXISTS (SELECT 1 FROM [org] INNER JOIN
>> [org_hier] ON [org].[org_id] = [org_hier].[parent_id] WHERE
>> [org].[ref] = 'India' AND [req].[location_id] =
>> [org_hier].[child_id])
>>
>> But when I try to modify the relbuilder for the above query to add
>> additional scans and joins for the 'users' and 'appl' tables, my
>> output query remains the same.
>> I would really appreciate any help with this.
>>
>> Thank you.
>>
>> Regards,
>> Hrudaya
>>
>> This message, together with any attachments, is intended only for the
>> use of the individual or entity to which it is addressed and may
>> contain confidential and/or privileged information. If you are not
>> the intended recipient(s), or the employee or agent responsible for
>> delivery of this message to the intended recipient(s), you are hereby
>> notified that any dissemination, distribution or copying of this
>> message, or any attachment, is strictly prohibited. If you have
>> received this message in error, please immediately notify the sender
>> and delete the message, together with any attachments, from your computer. Thank you for your cooperation.
>>
----------------------------------------------------------------------
*EXTERNAL MESSAGE WARNING: This email originated from outside of Cornerstone. Do not click links or open attachments unless you recognize the sender and know the content is safe. Please see this wiki for more information on email safety: https://wiki.cornerstoneondemand.com/display/ISS/Security+Awareness
This message, together with any attachments, is intended only for the use of the individual or entity to which it is addressed and may contain confidential and/or privileged information. If you are not the intended recipient(s), or the employee or agent responsible for delivery of this message to the intended recipient(s), you are hereby notified that any dissemination, distribution or copying of this message, or any attachment, is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete the message, together with any attachments, from your computer. Thank you for your cooperation.
Re: Request help in implementing subquery in WHERE condition
Posted by Julian Hyde <jh...@gmail.com>.
Yes; I usually only call build() once per RelBuilder program, and leave nodes on the stack meantime. If you build and then push the same RelNode back, RelBuilder loses a bit of information (the mapping of table and column aliases to fields) and therefore may not be able to resolve the expression if you use aliases.
Julian
> On Jan 26, 2021, at 1:30 AM, Stamatis Zampetakis <za...@gmail.com> wrote:
>
> Hi Hrudaya,
>
> Not sure how you display the SQL query and what interleaves the first and
> second calls to RelBuilder but note that RelBuilder#build() [1] method pops
> an element from the stack.
> This means that if you call build to display the first query then in this
> case the builder will be empty afterwards and you start from scratch.
>
> Best,
> Stamatis
>
> [1]
> https://github.com/apache/calcite/blob/a16a47351aa25caf538c1955edd171ec871569f7/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L354
>
>> On Mon, Jan 25, 2021 at 6:08 PM Hrudaya Reddy <hr...@csod.com.invalid>
>> wrote:
>>
>> Hi everyone,
>>
>> Please find my current implementation for the subquery in where condition
>>
>> final FrameworkConfig config = config().build();
>> final RelBuilder relBuilder = RelBuilder.create(config);
>> RelNode left1 = relBuilder.scan("req").build();
>> RelNode right1 = relBuilder.scan("org").build();
>> RelNode right2 = relBuilder.scan("org_hier").build();
>>
>> relBuilder
>> .push(left1)
>> .push(right1)
>> .push(right2)
>> .join(
>> JoinRelType.INNER,
>> relBuilder.equals(
>> relBuilder.field(2, "org", "org_id"),
>> relBuilder.field(2, "org_hier", "parent_id")))
>> .antiJoin(
>> relBuilder.equals(
>> relBuilder.field(2, "org", "ref"),
>> relBuilder.literal("India")),
>> relBuilder.equals(
>> relBuilder.field(2, "req", "location_id"),
>> relBuilder.field(2, "org_hier", "child_id")));
>>
>> /// At this point relbuilder returns the below query
>>
>> SELECT * FROM [req]
>> LEFT JOIN [org] ON ([req].[location_id] = [org].[org_id])
>> WHERE NOT EXISTS (SELECT 1
>> FROM [org]
>> INNER JOIN [org_hier] ON [org].[org_id] = [org_hier].[parent_id]
>> WHERE [org].[ref] = 'India' AND [req].[location_id] =
>> [org_hier].[child_id])
>>
>> relBuilder.scan("req").as("jr")
>> .scan("org").as("org1")
>> .join(JoinRelType.LEFT,
>> relBuilder.equals(
>> relBuilder.field(2, "jr", "location_id"),
>> relBuilder.field(2, "org1", "org_id")))
>> .scan("appl").as("ja")
>> .join(JoinRelType.LEFT,
>> relBuilder.equals(
>> relBuilder.field(2, "jr", "req_id"),
>> relBuilder.field(2, "ja", "req_id")))
>> .scan("users").as("u")
>> .join(JoinRelType.LEFT,
>> relBuilder.equals(
>> relBuilder.field(2, "ja", "user_id"),
>> relBuilder.field(2, "u", "user_id")));
>>
>> relBuilder.project(relBuilder.field("org1", "ref"));
>>
>> /// When I do additional scans and joins, my query changes to
>>
>> SELECT [org].[ref]
>> FROM [req]
>> LEFT JOIN [org] ON [req].[location_id] = [org].[org_id]
>> LEFT JOIN [appl] ON [req].[req_id] = [appl].[req_id]
>> LEFT JOIN [users] ON [appl].[user_id] = [users].[user_id]
>>
>> I would really appreciate if you could point out any mistakes I am making
>> with my implementation.
>>
>> Thanks in advance.
>>
>> Regards,
>> Hrudaya
>>
>> From: Hrudaya Reddy
>> Sent: Sunday, January 24, 2021 6:22 PM
>> To: dev@calcite.apache.org
>> Subject: Request help in implementing subquery in WHERE condition
>>
>> Hi everyone,
>>
>> I am trying to implement a subquery in the WHERE condition.
>> My query is similar to below,
>>
>> SELECT [org].[ref],
>> [users].[ref]
>> FROM [req]
>> LEFT JOIN [org] ON ([req].[location_id] = [org].[org_id])
>> LEFT JOIN [appl] ON ([req].[req_id] = [appl].[req_id])
>> LEFT JOIN [users] ON ([appl].[user_id] = [users].[user_id])
>> WHERE org.org_id NOT IN (SELECT org_hier.child_id
>> FROM [org]
>> INNER JOIN [org_hier] ON [org].[org_id] = [org_hier].[parent_id]
>> WHERE [org].[ref] = 'India')
>>
>> One of the ways I found to achieve the above was using antijoin with which
>> I am able to implement the below query
>>
>> SELECT [org].[ref]
>> FROM [req]
>> LEFT JOIN [org] ON ([req].[location_id] = [org].[org_id])
>> WHERE NOT EXISTS (SELECT 1
>> FROM [org]
>> INNER JOIN [org_hier] ON [org].[org_id] = [org_hier].[parent_id]
>> WHERE [org].[ref] = 'India' AND [req].[location_id] =
>> [org_hier].[child_id])
>>
>> But when I try to modify the relbuilder for the above query to add
>> additional scans and joins for the 'users' and 'appl' tables, my output
>> query remains the same.
>> I would really appreciate any help with this.
>>
>> Thank you.
>>
>> Regards,
>> Hrudaya
>>
>> This message, together with any attachments, is intended only for the use
>> of the individual or entity to which it is addressed and may contain
>> confidential and/or privileged information. If you are not the intended
>> recipient(s), or the employee or agent responsible for delivery of this
>> message to the intended recipient(s), you are hereby notified that any
>> dissemination, distribution or copying of this message, or any attachment,
>> is strictly prohibited. If you have received this message in error, please
>> immediately notify the sender and delete the message, together with any
>> attachments, from your computer. Thank you for your cooperation.
>>
RE: Re: Request help in implementing subquery in WHERE condition
Posted by Hrudaya Reddy <hr...@csod.com.INVALID>.
Hi Stamatis,
I am doing a build() only at the end after adding the additional scans and joins.
This is essentially what we are trying to implement: https://issues.apache.org/jira/browse/CALCITE-1493
select sal from emp where empno NOT IN (select deptno from dept where emp.job = dept.name)
I am very new to calcite and still trying to understand how relbuilder works.
I am not sure how to construct the relbuilder for the query in https://issues.apache.org/jira/browse/CALCITE-1493
I would appreciate any help with this.
Regards,
Hrudaya
-----Original Message-----
From: Stamatis Zampetakis <za...@gmail.com>
Sent: Tuesday, January 26, 2021 1:30 AM
To: dev@calcite.apache.org
Subject: [EXTERNAL] Re: Request help in implementing subquery in WHERE condition
Hi Hrudaya,
Not sure how you display the SQL query and what interleaves the first and second calls to RelBuilder but note that RelBuilder#build() [1] method pops an element from the stack.
This means that if you call build to display the first query then in this case the builder will be empty afterwards and you start from scratch.
Best,
Stamatis
[1]
https://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_apache_calcite_blob_a16a47351aa25caf538c1955edd171ec871569f7_core_src_main_java_org_apache_calcite_tools_RelBuilder.java-23L354&d=DwIBaQ&c=IrjCfGj3zOfzN2DRIASEFg&r=MjX6IuQXq8W7yQ_nB4S_UQ&m=79kIpYszHYO193IjqUr3R52fHjr9jtPNAzBAXnXgouU&s=ePdd9sN7L41tnoRpcBFs6A2HOVDk6S2zNdaTce6JN7U&e=
On Mon, Jan 25, 2021 at 6:08 PM Hrudaya Reddy <hr...@csod.com.invalid>
wrote:
> Hi everyone,
>
> Please find my current implementation for the subquery in where
> condition
>
> final FrameworkConfig config = config().build();
> final RelBuilder relBuilder = RelBuilder.create(config);
> RelNode left1 = relBuilder.scan("req").build();
> RelNode right1 = relBuilder.scan("org").build();
> RelNode right2 = relBuilder.scan("org_hier").build();
>
> relBuilder
> .push(left1)
> .push(right1)
> .push(right2)
> .join(
> JoinRelType.INNER,
> relBuilder.equals(
> relBuilder.field(2, "org", "org_id"),
> relBuilder.field(2, "org_hier", "parent_id")))
> .antiJoin(
> relBuilder.equals(
> relBuilder.field(2, "org", "ref"),
> relBuilder.literal("India")),
> relBuilder.equals(
> relBuilder.field(2, "req", "location_id"),
> relBuilder.field(2, "org_hier", "child_id")));
>
> /// At this point relbuilder returns the below query
>
> SELECT * FROM [req]
> LEFT JOIN [org] ON ([req].[location_id] = [org].[org_id])
> WHERE NOT EXISTS (SELECT 1 FROM [org] INNER JOIN
> [org_hier] ON [org].[org_id] = [org_hier].[parent_id] WHERE
> [org].[ref] = 'India' AND [req].[location_id] =
> [org_hier].[child_id])
>
> relBuilder.scan("req").as("jr")
> .scan("org").as("org1")
> .join(JoinRelType.LEFT,
> relBuilder.equals(
> relBuilder.field(2, "jr", "location_id"),
> relBuilder.field(2, "org1", "org_id")))
> .scan("appl").as("ja")
> .join(JoinRelType.LEFT,
> relBuilder.equals(
> relBuilder.field(2, "jr", "req_id"),
> relBuilder.field(2, "ja", "req_id")))
> .scan("users").as("u")
> .join(JoinRelType.LEFT,
> relBuilder.equals(
> relBuilder.field(2, "ja", "user_id"),
> relBuilder.field(2, "u", "user_id")));
>
> relBuilder.project(relBuilder.field("org1", "ref"));
>
> /// When I do additional scans and joins, my query changes to
>
> SELECT [org].[ref]
> FROM [req]
> LEFT JOIN [org] ON [req].[location_id] = [org].[org_id] LEFT JOIN
> [appl] ON [req].[req_id] = [appl].[req_id] LEFT JOIN [users] ON
> [appl].[user_id] = [users].[user_id]
>
> I would really appreciate if you could point out any mistakes I am
> making with my implementation.
>
> Thanks in advance.
>
> Regards,
> Hrudaya
>
> From: Hrudaya Reddy
> Sent: Sunday, January 24, 2021 6:22 PM
> To: dev@calcite.apache.org
> Subject: Request help in implementing subquery in WHERE condition
>
> Hi everyone,
>
> I am trying to implement a subquery in the WHERE condition.
> My query is similar to below,
>
> SELECT [org].[ref],
> [users].[ref]
> FROM [req]
> LEFT JOIN [org] ON ([req].[location_id] = [org].[org_id])
> LEFT JOIN [appl] ON ([req].[req_id] = [appl].[req_id])
> LEFT JOIN [users] ON ([appl].[user_id] = [users].[user_id])
> WHERE org.org_id NOT IN (SELECT org_hier.child_id FROM
> [org] INNER JOIN [org_hier] ON [org].[org_id] = [org_hier].[parent_id]
> WHERE [org].[ref] = 'India')
>
> One of the ways I found to achieve the above was using antijoin with
> which I am able to implement the below query
>
> SELECT [org].[ref]
> FROM [req]
> LEFT JOIN [org] ON ([req].[location_id] = [org].[org_id])
> WHERE NOT EXISTS (SELECT 1 FROM [org] INNER JOIN
> [org_hier] ON [org].[org_id] = [org_hier].[parent_id] WHERE
> [org].[ref] = 'India' AND [req].[location_id] =
> [org_hier].[child_id])
>
> But when I try to modify the relbuilder for the above query to add
> additional scans and joins for the 'users' and 'appl' tables, my
> output query remains the same.
> I would really appreciate any help with this.
>
> Thank you.
>
> Regards,
> Hrudaya
>
> This message, together with any attachments, is intended only for the
> use of the individual or entity to which it is addressed and may
> contain confidential and/or privileged information. If you are not the
> intended recipient(s), or the employee or agent responsible for
> delivery of this message to the intended recipient(s), you are hereby
> notified that any dissemination, distribution or copying of this
> message, or any attachment, is strictly prohibited. If you have
> received this message in error, please immediately notify the sender
> and delete the message, together with any attachments, from your computer. Thank you for your cooperation.
>
----------------------------------------------------------------------
*EXTERNAL MESSAGE WARNING: This email originated from outside of Cornerstone. Do not click links or open attachments unless you recognize the sender and know the content is safe. Please see this wiki for more information on email safety: https://wiki.cornerstoneondemand.com/display/ISS/Security+Awareness
This message, together with any attachments, is intended only for the use of the individual or entity to which it is addressed and may contain confidential and/or privileged information. If you are not the intended recipient(s), or the employee or agent responsible for delivery of this message to the intended recipient(s), you are hereby notified that any dissemination, distribution or copying of this message, or any attachment, is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete the message, together with any attachments, from your computer. Thank you for your cooperation.
Re: Request help in implementing subquery in WHERE condition
Posted by Stamatis Zampetakis <za...@gmail.com>.
Hi Hrudaya,
Not sure how you display the SQL query and what interleaves the first and
second calls to RelBuilder but note that RelBuilder#build() [1] method pops
an element from the stack.
This means that if you call build to display the first query then in this
case the builder will be empty afterwards and you start from scratch.
Best,
Stamatis
[1]
https://github.com/apache/calcite/blob/a16a47351aa25caf538c1955edd171ec871569f7/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L354
On Mon, Jan 25, 2021 at 6:08 PM Hrudaya Reddy <hr...@csod.com.invalid>
wrote:
> Hi everyone,
>
> Please find my current implementation for the subquery in where condition
>
> final FrameworkConfig config = config().build();
> final RelBuilder relBuilder = RelBuilder.create(config);
> RelNode left1 = relBuilder.scan("req").build();
> RelNode right1 = relBuilder.scan("org").build();
> RelNode right2 = relBuilder.scan("org_hier").build();
>
> relBuilder
> .push(left1)
> .push(right1)
> .push(right2)
> .join(
> JoinRelType.INNER,
> relBuilder.equals(
> relBuilder.field(2, "org", "org_id"),
> relBuilder.field(2, "org_hier", "parent_id")))
> .antiJoin(
> relBuilder.equals(
> relBuilder.field(2, "org", "ref"),
> relBuilder.literal("India")),
> relBuilder.equals(
> relBuilder.field(2, "req", "location_id"),
> relBuilder.field(2, "org_hier", "child_id")));
>
> /// At this point relbuilder returns the below query
>
> SELECT * FROM [req]
> LEFT JOIN [org] ON ([req].[location_id] = [org].[org_id])
> WHERE NOT EXISTS (SELECT 1
> FROM [org]
> INNER JOIN [org_hier] ON [org].[org_id] = [org_hier].[parent_id]
> WHERE [org].[ref] = 'India' AND [req].[location_id] =
> [org_hier].[child_id])
>
> relBuilder.scan("req").as("jr")
> .scan("org").as("org1")
> .join(JoinRelType.LEFT,
> relBuilder.equals(
> relBuilder.field(2, "jr", "location_id"),
> relBuilder.field(2, "org1", "org_id")))
> .scan("appl").as("ja")
> .join(JoinRelType.LEFT,
> relBuilder.equals(
> relBuilder.field(2, "jr", "req_id"),
> relBuilder.field(2, "ja", "req_id")))
> .scan("users").as("u")
> .join(JoinRelType.LEFT,
> relBuilder.equals(
> relBuilder.field(2, "ja", "user_id"),
> relBuilder.field(2, "u", "user_id")));
>
> relBuilder.project(relBuilder.field("org1", "ref"));
>
> /// When I do additional scans and joins, my query changes to
>
> SELECT [org].[ref]
> FROM [req]
> LEFT JOIN [org] ON [req].[location_id] = [org].[org_id]
> LEFT JOIN [appl] ON [req].[req_id] = [appl].[req_id]
> LEFT JOIN [users] ON [appl].[user_id] = [users].[user_id]
>
> I would really appreciate if you could point out any mistakes I am making
> with my implementation.
>
> Thanks in advance.
>
> Regards,
> Hrudaya
>
> From: Hrudaya Reddy
> Sent: Sunday, January 24, 2021 6:22 PM
> To: dev@calcite.apache.org
> Subject: Request help in implementing subquery in WHERE condition
>
> Hi everyone,
>
> I am trying to implement a subquery in the WHERE condition.
> My query is similar to below,
>
> SELECT [org].[ref],
> [users].[ref]
> FROM [req]
> LEFT JOIN [org] ON ([req].[location_id] = [org].[org_id])
> LEFT JOIN [appl] ON ([req].[req_id] = [appl].[req_id])
> LEFT JOIN [users] ON ([appl].[user_id] = [users].[user_id])
> WHERE org.org_id NOT IN (SELECT org_hier.child_id
> FROM [org]
> INNER JOIN [org_hier] ON [org].[org_id] = [org_hier].[parent_id]
> WHERE [org].[ref] = 'India')
>
> One of the ways I found to achieve the above was using antijoin with which
> I am able to implement the below query
>
> SELECT [org].[ref]
> FROM [req]
> LEFT JOIN [org] ON ([req].[location_id] = [org].[org_id])
> WHERE NOT EXISTS (SELECT 1
> FROM [org]
> INNER JOIN [org_hier] ON [org].[org_id] = [org_hier].[parent_id]
> WHERE [org].[ref] = 'India' AND [req].[location_id] =
> [org_hier].[child_id])
>
> But when I try to modify the relbuilder for the above query to add
> additional scans and joins for the 'users' and 'appl' tables, my output
> query remains the same.
> I would really appreciate any help with this.
>
> Thank you.
>
> Regards,
> Hrudaya
>
> This message, together with any attachments, is intended only for the use
> of the individual or entity to which it is addressed and may contain
> confidential and/or privileged information. If you are not the intended
> recipient(s), or the employee or agent responsible for delivery of this
> message to the intended recipient(s), you are hereby notified that any
> dissemination, distribution or copying of this message, or any attachment,
> is strictly prohibited. If you have received this message in error, please
> immediately notify the sender and delete the message, together with any
> attachments, from your computer. Thank you for your cooperation.
>
RE: Request help in implementing subquery in WHERE condition
Posted by Hrudaya Reddy <hr...@csod.com.INVALID>.
Hi everyone,
Please find my current implementation for the subquery in where condition
final FrameworkConfig config = config().build();
final RelBuilder relBuilder = RelBuilder.create(config);
RelNode left1 = relBuilder.scan("req").build();
RelNode right1 = relBuilder.scan("org").build();
RelNode right2 = relBuilder.scan("org_hier").build();
relBuilder
.push(left1)
.push(right1)
.push(right2)
.join(
JoinRelType.INNER,
relBuilder.equals(
relBuilder.field(2, "org", "org_id"),
relBuilder.field(2, "org_hier", "parent_id")))
.antiJoin(
relBuilder.equals(
relBuilder.field(2, "org", "ref"),
relBuilder.literal("India")),
relBuilder.equals(
relBuilder.field(2, "req", "location_id"),
relBuilder.field(2, "org_hier", "child_id")));
/// At this point relbuilder returns the below query
SELECT * FROM [req]
LEFT JOIN [org] ON ([req].[location_id] = [org].[org_id])
WHERE NOT EXISTS (SELECT 1
FROM [org]
INNER JOIN [org_hier] ON [org].[org_id] = [org_hier].[parent_id]
WHERE [org].[ref] = 'India' AND [req].[location_id] = [org_hier].[child_id])
relBuilder.scan("req").as("jr")
.scan("org").as("org1")
.join(JoinRelType.LEFT,
relBuilder.equals(
relBuilder.field(2, "jr", "location_id"),
relBuilder.field(2, "org1", "org_id")))
.scan("appl").as("ja")
.join(JoinRelType.LEFT,
relBuilder.equals(
relBuilder.field(2, "jr", "req_id"),
relBuilder.field(2, "ja", "req_id")))
.scan("users").as("u")
.join(JoinRelType.LEFT,
relBuilder.equals(
relBuilder.field(2, "ja", "user_id"),
relBuilder.field(2, "u", "user_id")));
relBuilder.project(relBuilder.field("org1", "ref"));
/// When I do additional scans and joins, my query changes to
SELECT [org].[ref]
FROM [req]
LEFT JOIN [org] ON [req].[location_id] = [org].[org_id]
LEFT JOIN [appl] ON [req].[req_id] = [appl].[req_id]
LEFT JOIN [users] ON [appl].[user_id] = [users].[user_id]
I would really appreciate if you could point out any mistakes I am making with my implementation.
Thanks in advance.
Regards,
Hrudaya
From: Hrudaya Reddy
Sent: Sunday, January 24, 2021 6:22 PM
To: dev@calcite.apache.org
Subject: Request help in implementing subquery in WHERE condition
Hi everyone,
I am trying to implement a subquery in the WHERE condition.
My query is similar to below,
SELECT [org].[ref],
[users].[ref]
FROM [req]
LEFT JOIN [org] ON ([req].[location_id] = [org].[org_id])
LEFT JOIN [appl] ON ([req].[req_id] = [appl].[req_id])
LEFT JOIN [users] ON ([appl].[user_id] = [users].[user_id])
WHERE org.org_id NOT IN (SELECT org_hier.child_id
FROM [org]
INNER JOIN [org_hier] ON [org].[org_id] = [org_hier].[parent_id]
WHERE [org].[ref] = 'India')
One of the ways I found to achieve the above was using antijoin with which I am able to implement the below query
SELECT [org].[ref]
FROM [req]
LEFT JOIN [org] ON ([req].[location_id] = [org].[org_id])
WHERE NOT EXISTS (SELECT 1
FROM [org]
INNER JOIN [org_hier] ON [org].[org_id] = [org_hier].[parent_id]
WHERE [org].[ref] = 'India' AND [req].[location_id] = [org_hier].[child_id])
But when I try to modify the relbuilder for the above query to add additional scans and joins for the 'users' and 'appl' tables, my output query remains the same.
I would really appreciate any help with this.
Thank you.
Regards,
Hrudaya
This message, together with any attachments, is intended only for the use of the individual or entity to which it is addressed and may contain confidential and/or privileged information. If you are not the intended recipient(s), or the employee or agent responsible for delivery of this message to the intended recipient(s), you are hereby notified that any dissemination, distribution or copying of this message, or any attachment, is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete the message, together with any attachments, from your computer. Thank you for your cooperation.