You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@ignite.apache.org by "Stanilovsky Evgeny (Jira)" <ji...@apache.org> on 2020/07/22 10:24:00 UTC
[jira] [Updated] (IGNITE-13280) Improper index usage, fields
enumeration not used with pk index creation.
[ https://issues.apache.org/jira/browse/IGNITE-13280?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Stanilovsky Evgeny updated IGNITE-13280:
----------------------------------------
Description:
For example:
{code:java}
CREATE TABLE PUBLIC.TEST_TABLE (FIRST_NAME VARCHAR, LAST_NAME VARCHAR, ADDRESS VARCHAR, LANG VARCHAR, CONSTRAINT PK_PERSON PRIMARY KEY (FIRST_NAME, LAST_NAME));
CREATE INDEX "idx2" ON PUBLIC.TEST_TABLE (LANG, ADDRESS);
{code}
and further explain:
{code:java}
SELECT
"__Z0"."FIRST_NAME" AS "__C0_0",
"__Z0"."LAST_NAME" AS "__C0_1",
"__Z0"."ADDRESS" AS "__C0_2",
"__Z0"."LANG" AS "__C0_3"
FROM "PUBLIC"."TEST_TABLE" "__Z0"
/* PUBLIC.IDX2: ADDRESS > 0 */
WHERE "__Z0"."ADDRESS" > 0
{code}
this is erroneous to use "idx2" here, because first index field LANG not equals to predicate ADDRESS.
Looks like this bug brings this ticket [1]
[1] https://issues.apache.org/jira/browse/IGNITE-10217
was:
For example:
{code:java}
CREATE TABLE PUBLIC.TEST_TABLE (FIRST_NAME VARCHAR, LAST_NAME VARCHAR, ADDRESS VARCHAR, LANG VARCHAR, CONSTRAINT PK_PERSON PRIMARY KEY (FIRST_NAME, LAST_NAME));
CREATE INDEX "idx2" ON PUBLIC.TEST_TABLE (LANG, ADDRESS);
{code}
and further explain:
{code:java}
SELECT
"__Z0"."FIRST_NAME" AS "__C0_0",
"__Z0"."LAST_NAME" AS "__C0_1",
"__Z0"."ADDRESS" AS "__C0_2",
"__Z0"."LANG" AS "__C0_3"
FROM "PUBLIC"."TEST_TABLE" "__Z0"
/* PUBLIC.IDX2: ADDRESS > 0 */
WHERE "__Z0"."ADDRESS" > 0
{code}
this is erroneous to use "idx2" here, because first index field LANG not equals to predicate ADDRESS.
> Improper index usage, fields enumeration not used with pk index creation.
> -------------------------------------------------------------------------
>
> Key: IGNITE-13280
> URL: https://issues.apache.org/jira/browse/IGNITE-13280
> Project: Ignite
> Issue Type: Bug
> Components: sql
> Affects Versions: 2.8.1
> Reporter: Stanilovsky Evgeny
> Assignee: Stanilovsky Evgeny
> Priority: Major
> Time Spent: 10m
> Remaining Estimate: 0h
>
> For example:
> {code:java}
> CREATE TABLE PUBLIC.TEST_TABLE (FIRST_NAME VARCHAR, LAST_NAME VARCHAR, ADDRESS VARCHAR, LANG VARCHAR, CONSTRAINT PK_PERSON PRIMARY KEY (FIRST_NAME, LAST_NAME));
> CREATE INDEX "idx2" ON PUBLIC.TEST_TABLE (LANG, ADDRESS);
> {code}
> and further explain:
> {code:java}
> SELECT
> "__Z0"."FIRST_NAME" AS "__C0_0",
> "__Z0"."LAST_NAME" AS "__C0_1",
> "__Z0"."ADDRESS" AS "__C0_2",
> "__Z0"."LANG" AS "__C0_3"
> FROM "PUBLIC"."TEST_TABLE" "__Z0"
> /* PUBLIC.IDX2: ADDRESS > 0 */
> WHERE "__Z0"."ADDRESS" > 0
> {code}
> this is erroneous to use "idx2" here, because first index field LANG not equals to predicate ADDRESS.
> Looks like this bug brings this ticket [1]
> [1] https://issues.apache.org/jira/browse/IGNITE-10217
--
This message was sent by Atlassian Jira
(v8.3.4#803005)