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