Re: Ignite Sql query problem using IN clause

[Naveen <na...@gmail.com>]

Has it been fixed in 2.4 ?

Looks like it is not, when I generate the explain plan for a query which has
a composite key

Here are the queries I ran and explain plan results.
When I use IN, it uses the full table scan, otherwise It does look up on
primary key

explain SELECT * FROM "MapLonglistCache".MAP_LONGLIST  WHERE 
(ENTITY_ID,RELATIONSHIP,SEQUENCE_NO) = ('12345', 'OutageID_ServiceIDs', '9') 

SELECT
    __Z0.ENTITY_ID AS __C0_0,
    __Z0.MAPPING_ID_LIST AS __C0_1,
    __Z0.RELATIONSHIP AS __C0_2,
    __Z0.UPDATEDDATETIME AS __C0_3,
    __Z0.UPDATEDBY AS __C0_4,
    __Z0.SEQUENCE_NO AS __C0_5,
    __Z0.TUPLE_COUNT AS __C0_6
FROM "MapLonglistCache".MAP_LONGLIST __Z0
    /* "MapLonglistCache".MAP_LONGLIST.__SCAN_ */
WHERE (__Z0.ENTITY_ID, __Z0.RELATIONSHIP, __Z0.SEQUENCE_NO) = ('12345',
'OutageID_ServiceIDs', '9')

explain SELECT * FROM "MapLonglistCache".MAP_LONGLIST  WHERE 
ENTITY_ID = '12345' AND 
RELATIONSHIP = 'OutageID_ServiceIDs' AND 
SEQUENCE_NO='9' 


SELECT
    __Z0.ENTITY_ID AS __C0_0,
    __Z0.MAPPING_ID_LIST AS __C0_1,
    __Z0.RELATIONSHIP AS __C0_2,
    __Z0.UPDATEDDATETIME AS __C0_3,
    __Z0.UPDATEDBY AS __C0_4,
    __Z0.SEQUENCE_NO AS __C0_5,
    __Z0.TUPLE_COUNT AS __C0_6
FROM "MapLonglistCache".MAP_LONGLIST __Z0
    /* "MapLonglistCache".MAP_LONGLIST_PK: SEQUENCE_NO = '9'
        AND ENTITY_ID = '12345'
        AND RELATIONSHIP = 'OutageID_ServiceIDs'
     */
WHERE (__Z0.SEQUENCE_NO = '9')
    AND ((__Z0.ENTITY_ID = '12345')
    AND (__Z0.RELATIONSHIP = 'OutageID_ServiceIDs'))

Any suggestions on this.

Thanks
naveen



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Ignite Sql query problem using IN clause

[mike-griggs <en...@gmail.com>]

There is a suggested workaround in
https://apacheignite-sql.readme.io/docs/performance-and-debugging#section-sql-performance-and-usability-considerations,
item 2

 As a workaround...you can rewrite the query in the following way: select
p.name from Person p join table(id bigint = ?) i on p.id = i.id.



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/