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.
