You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@ignite.apache.org by "Alexey Kukushkin (Jira)" <ji...@apache.org> on 2020/11/12 10:55:00 UTC
[jira] [Updated] (IGNITE-13698) SQL EXPLAIN Shows Impossible Index

     [ https://issues.apache.org/jira/browse/IGNITE-13698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Alexey Kukushkin updated IGNITE-13698:
--------------------------------------
    Description: 
+*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%'}}

  was:
+*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%'}}


> 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)