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 <hre...@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 <jhyde.apa...@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 <zabe...@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 > >> <hre...@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. >
