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/