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