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 <zabe...@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 <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.
