You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@ignite.apache.org by "Maksim Timonin (Jira)" <ji...@apache.org> on 2021/01/26 14:59:00 UTC
[jira] [Comment Edited] (IGNITE-13698) SQL EXPLAIN Shows Impossible
Index
[ https://issues.apache.org/jira/browse/IGNITE-13698?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17272142#comment-17272142 ]
Maksim Timonin edited comment on IGNITE-13698 at 1/26/21, 2:58 PM:
-------------------------------------------------------------------
Hi [~kukushal]!
I've investigated for this case and found that cost for TEST_TITLE_ASC_IDX less than for scan index, and it is a feature and not a bug, please check the comment for this flag [1]. For TEST_TITLE_ASC_IDX the value of this flag is False (due to this index contains all required columns for the query). Than by this logic [2] cost of tree idx will be less (11002 vs 11020).
So, by this logic this plan is correct, and it's not a bug. WDYT?
[1] [https://github.com/apache/ignite/blob/2d2044a63440e9b9d8495f35819ccee26d8324e4/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/opt/H2IndexCostedBase.java#L190]
[2]https://github.com/apache/ignite/blob/2d2044a63440e9b9d8495f35819ccee26d8324e4/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/opt/H2IndexCostedBase.java#L229
was (Author: timonin.maksim):
Hi [~kukushal]!
I've investigated for this case and found that cost for TEST_TITLE_ASC_IDX less than for scan index, and it is a feature and not a bug, please check the comment for this flag [1]. For TEST_TITLE_ASC_IDX the value of this flag is False. Than by this logic [2] cost of tree idx will be less (11002 vs 11020).
So, by this logic this plan is correct, and it's not a bug. WDYT?
[1] [https://github.com/apache/ignite/blob/2d2044a63440e9b9d8495f35819ccee26d8324e4/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/opt/H2IndexCostedBase.java#L190]
[2]https://github.com/apache/ignite/blob/2d2044a63440e9b9d8495f35819ccee26d8324e4/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/opt/H2IndexCostedBase.java#L229
> SQL EXPLAIN Shows Impossible Index
> ----------------------------------
>
> Key: IGNITE-13698
> URL: https://issues.apache.org/jira/browse/IGNITE-13698
> Project: Ignite
> Issue Type: Bug
> Components: sql
> Affects Versions: 2.9
> Reporter: Alexey Kukushkin
> Priority: Major
>
> +*Steps to Reproduce*+
> 1. Create a table with a VARCHAR field and an index on that field
> {{CREATE TABLE TEST (ID INT PRIMARY KEY, TITLE VARCHAR);}}
> {{CREATE INDEX TEST_TITLE_ASC_IDX ON TEST(TITLE); }}
> 2. Show a plan for querying the table with a filter UPPER(TITLE) LIKE '%A%'
> {{EXPLAIN SELECT _KEY FROM TEST WHERE UPPER(TITLE) LIKE '%A%';}}
> +*Expected*+
> The table SCAN on the TITLE field since the TEST_TITLE_ASC_IDX cannot be applied due to any of"
> # The UPPER(TITLE) SQL function on the left-hand side
> # The LIKE pattern starting from % (any symbol)
> +*Actual*+
> The TEST_TITLE_ASC_IDX is used
> SELECT
> __Z0._KEY AS __C0_0
> FROM PUBLIC.TEST __Z0
> /* PUBLIC.TEST_TITLE_ASC_IDX */
> WHERE UPPER(__Z0.TITLE) LIKE '%A%'
--
This message was sent by Atlassian Jira
(v8.3.4#803005)