You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@ignite.apache.org by "Mausam (JIRA)" <ji...@apache.org> on 2019/03/12 05:42:00 UTC
[jira] [Updated] (IGNITE-11522) Ignite index in not working in a
specific case
[ https://issues.apache.org/jira/browse/IGNITE-11522?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mausam updated IGNITE-11522:
----------------------------
Description:
Below query is getting frequently fired:
SELECT *
FROM TABLE_A __Z0
WHERE (__Z0.COL_A IN ('DL', 'A'))
AND ((__Z0.COL_B IN('100001-W060', '100001-W060'))
AND ((__Z0.COL_C IN('HD', 'ED'))
AND ((__Z0.COL_D = '1')
AND ((__Z0.COL_E = '1')
AND (__Z0.COL_F = '1')))))
Below Index when created on this table is *not working*:
CREATE INDEX CUST_INDEX1
ON TABLE_A
(COL_A ASC, COL_B ASC, COL_C ASC, COL_D ASC, COL_E ASC, COL_F ASC);
Below index is *working perfectly fine:*
CREATE INDEX CUST_INDEX1
ON TABLE_A
(COL_D ASC, COL_A ASC, COL_B ASC, COL_C ASC, COL_E ASC, COL_F ASC);
This is just a change in the ordering. While the first Index is in the same order as the attributes in the where clause and therefore should have worked, second one is just in a random order.
On further analysis, it appears that *Index doesn't apply if the first column of the Index is not an IN clause having multiple values in the Select statement.*
In the second create index statement, COL_D is the first column which is not an IN with multiple values in the select clause and hence this is working fine.
It means, if the first column COL_A has only one value 'DL' then it works, but if it has two values, 'DL' and 'A', the Index doesn't get applied.
This isn't the expected behaviour.
Index should work for both the CREATE INDEX queries, irrespective of number of values in IN clause.
Additional info: If the select query tries to fetch only those columns which are part of the Index, then both the index above work fine.
For example:
Both Index work for below query:
SELECT __Z0.COL_A
FROM TABLE_A __Z0
WHERE (__Z0.COL_A IN ('DL', 'A'))
AND ((__Z0.COL_B IN('100001-W060', '100001-W060'))
AND ((__Z0.COL_C IN('HD', 'ED'))
AND ((__Z0.COL_D = '1')
AND ((__Z0.COL_E = '1')
AND (__Z0.COL_F = '1')))))
But the first index doesn't work for the below query (COL_Z is not a part of Index):
SELECT __Z0.COL_Z
FROM TABLE_A __Z0
WHERE (__Z0.COL_A IN ('DL', 'A'))
AND ((__Z0.COL_B IN('100001-W060', '100001-W060'))
AND ((__Z0.COL_C IN('HD', 'ED'))
AND ((__Z0.COL_D = '1')
AND ((__Z0.COL_E = '1')
AND (__Z0.COL_F = '1')))))
was:
Below query is getting frequently fired:
SELECT *
FROM TABLE_A __Z0
WHERE (__Z0.COL_A IN ('DL', 'A'))
AND ((__Z0.COL_B IN('100001-W060', '100001-W060'))
AND ((__Z0.COL_C IN('HD', 'ED'))
AND ((__Z0.COL_D = '1')
AND ((__Z0.COL_E = '1')
AND (__Z0.COL_F = '1')))))
Below Index when created on this table is *not working*:
CREATE INDEX CUST_INDEX1
ON TABLE_A
(COL_A ASC, COL_B ASC, COL_C ASC, COL_D ASC, COL_E ASC, COL_F ASC);
Below index is *working perfectly fine:*
CREATE INDEX CUST_INDEX1
ON TABLE_A
(COL_D ASC, COL_A ASC, COL_B ASC, COL_C ASC, COL_E ASC, COL_F ASC);
This is just a change in the ordering. While the first Index is in the same order as the attributes in the where clause and therefore should have worked, second one is just in a random order.
On further analysis, it appears that *Index doesn't apply if the first column of the Index is not an IN clause having multiple values in the Select statement.*
In the second create index statement, COL_D is the first column which is not an IN with multiple values in the select clause and hence this is working fine.
It means, if the first column COL_A has only one value 'DL' then it works, but if it has two values, 'DL' and 'A', the Index doesn't get applied.
This isn't the expected behaviour.
Index should work for both the CREATE INDEX queries, irrespective of number of values in IN clause.
> Ignite index in not working in a specific case
> ----------------------------------------------
>
> Key: IGNITE-11522
> URL: https://issues.apache.org/jira/browse/IGNITE-11522
> Project: Ignite
> Issue Type: Bug
> Components: general
> Affects Versions: 2.7
> Environment: Red Hat Linux
> Reporter: Mausam
> Priority: Major
> Labels: INDEX, Index, index
>
> Below query is getting frequently fired:
> SELECT *
> FROM TABLE_A __Z0
> WHERE (__Z0.COL_A IN ('DL', 'A'))
> AND ((__Z0.COL_B IN('100001-W060', '100001-W060'))
> AND ((__Z0.COL_C IN('HD', 'ED'))
> AND ((__Z0.COL_D = '1')
> AND ((__Z0.COL_E = '1')
> AND (__Z0.COL_F = '1')))))
>
> Below Index when created on this table is *not working*:
> CREATE INDEX CUST_INDEX1
> ON TABLE_A
> (COL_A ASC, COL_B ASC, COL_C ASC, COL_D ASC, COL_E ASC, COL_F ASC);
>
> Below index is *working perfectly fine:*
> CREATE INDEX CUST_INDEX1
> ON TABLE_A
> (COL_D ASC, COL_A ASC, COL_B ASC, COL_C ASC, COL_E ASC, COL_F ASC);
>
> This is just a change in the ordering. While the first Index is in the same order as the attributes in the where clause and therefore should have worked, second one is just in a random order.
> On further analysis, it appears that *Index doesn't apply if the first column of the Index is not an IN clause having multiple values in the Select statement.*
> In the second create index statement, COL_D is the first column which is not an IN with multiple values in the select clause and hence this is working fine.
> It means, if the first column COL_A has only one value 'DL' then it works, but if it has two values, 'DL' and 'A', the Index doesn't get applied.
> This isn't the expected behaviour.
> Index should work for both the CREATE INDEX queries, irrespective of number of values in IN clause.
> Additional info: If the select query tries to fetch only those columns which are part of the Index, then both the index above work fine.
> For example:
> Both Index work for below query:
> SELECT __Z0.COL_A
> FROM TABLE_A __Z0
> WHERE (__Z0.COL_A IN ('DL', 'A'))
> AND ((__Z0.COL_B IN('100001-W060', '100001-W060'))
> AND ((__Z0.COL_C IN('HD', 'ED'))
> AND ((__Z0.COL_D = '1')
> AND ((__Z0.COL_E = '1')
> AND (__Z0.COL_F = '1')))))
> But the first index doesn't work for the below query (COL_Z is not a part of Index):
> SELECT __Z0.COL_Z
> FROM TABLE_A __Z0
> WHERE (__Z0.COL_A IN ('DL', 'A'))
> AND ((__Z0.COL_B IN('100001-W060', '100001-W060'))
> AND ((__Z0.COL_C IN('HD', 'ED'))
> AND ((__Z0.COL_D = '1')
> AND ((__Z0.COL_E = '1')
> AND (__Z0.COL_F = '1')))))
>
>
>
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)