Details
-
Bug
-
Status: Open
-
Major
-
Resolution: Unresolved
-
2.9
-
None
-
None
-
Release Notes Required
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%'