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