You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by JP <jo...@gmail.com> on 2021/05/25 08:31:42 UTC
IN Operator not working with sub query on different cache
Hi,
I am using apache Ignite v2.9.1 as .Net client server.
I have two caches and each cache is belong to separate cache group.
Not working query:
SELECT * FROM
"F2DEDF6E-393E-42BC-9BB3-E835A1063B30_6EFB69B0-269F-4F92-98CF-24BC0D34BA98_COMMON".CommonConstruction
Where TableName='common_project' AND UUID IN (SELECT ProjectId FROM
"F2DEDF6E-393E-42BC-9BB3-E835A1063B30_6EFB69B0-269F-4F92-98CF-24BC0D34BA98_CONSTRUCTIONPROJECTROLE".ProjectsRole
where PersonId = 'a91f8995-2c28-4f33-9587-817749526a55'
AND ProjectAccessStatus = 'true' and IsDeleted != 'true')
Result:No rows selected
With same query if i am passing subquery result as constants uuid inside IN
operator query working.
for example query:
SELECT * FROM
"F2DEDF6E-393E-42BC-9BB3-E835A1063B30_6EFB69B0-269F-4F92-98CF-24BC0D34BA98_COMMON".CommonConstruction
Where TableName='common_project' AND UUID IN
('de2f8754-e791-4333-87a5-8391146770af');
Result: 1 row selected.
Not getting why not with subquery on different cache not working.
Could please help me where i am doing wrong.
Thanks.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: IN Operator not working with sub query on different cache
Posted by Pavel Tupitsyn <pt...@apache.org>.
Hi,
IN operator has some limitations in Ignite, it is recommended to use joins
instead [1]
As I understand from your second message, with JOIN you get correct results.
[1]
https://apacheignite.readme.io/docs/sql-performance-and-debugging#sql-performance-and-usability-considerations
On Tue, May 25, 2021 at 1:55 PM JP <jo...@gmail.com> wrote:
> Continue with previous query
>
> I also tried with distrubutedJoin=true in result no selected rows.
>
>
> But if am using inner join result is returning.for example query:
>
> SELECT * FROM
>
> "F2DEDF6E-393E-42BC-9BB3-E835A1063B30_6EFB69B0-269F-4F92-98CF-24BC0D34BA98_COMMON".CommonConstruction
> AS t1
> INNER Join
>
>
> "F2DEDF6E-393E-42BC-9BB3-E835A1063B30_6EFB69B0-269F-4F92-98CF-24BC0D34BA98_CONSTRUCTIONPROJECTROLE".ProjectsRole
> AS t2
> ON
> t1.UUID =t2.ProjectId
> WHERE t2.PersonId = 'a91f8995-2c28-4f33-9587-817749526a55'
> AND t2.ProjectAccessStatus = 'true' and t2.IsDeleted != 'true'
> AND t1.TableName='common_project'
>
> Thanks.
>
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: IN Operator not working with sub query on different cache
Posted by JP <jo...@gmail.com>.
Continue with previous query
I also tried with distrubutedJoin=true in result no selected rows.
But if am using inner join result is returning.for example query:
SELECT * FROM
"F2DEDF6E-393E-42BC-9BB3-E835A1063B30_6EFB69B0-269F-4F92-98CF-24BC0D34BA98_COMMON".CommonConstruction
AS t1
INNER Join
"F2DEDF6E-393E-42BC-9BB3-E835A1063B30_6EFB69B0-269F-4F92-98CF-24BC0D34BA98_CONSTRUCTIONPROJECTROLE".ProjectsRole
AS t2
ON
t1.UUID =t2.ProjectId
WHERE t2.PersonId = 'a91f8995-2c28-4f33-9587-817749526a55'
AND t2.ProjectAccessStatus = 'true' and t2.IsDeleted != 'true'
AND t1.TableName='common_project'
Thanks.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/