[
https://issues.apache.org/jira/browse/IGNITE-25983?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pavel Pereslegin updated IGNITE-25983:
--------------------------------------
Description:
Currently multibounds are not built for HASH index.
Consider the following example:
{code:Java}
sql("CREATE TABLE t (id INT PRIMARY KEY, val INT)");
sql("CREATE INDEX t_val_idx_hash ON t USING HASH(val)");
sql("INSERT INTO t values (1, 1), (2, 2), (3, 3), (7, 4), (17, 5), (27, 6),
(-38, 7)");
assertQuery("SELECT /*+ FORCE_INDEX(T_VAL_IDX_HASH) */ val FROM t WHERE val
IN (2, 3)")
.matches(QueryChecker.containsIndexScan("PUBLIC", "T"))
.check();;
{code}
Result
{noformat}
java.lang.AssertionError: Invalid plan:
Exchange
distribution: single
est: (rows=1)
TableScan
table: PUBLIC.T
predicate: SEARCH(VAL, Sarg[2, 3])
fieldNames: [VAL]
est: (rows=1)
{noformat}
The same example with SORTED index works fine.
Looks like the problem is in {{RexUtils#buildHashSearchBounds(RelOptCluster,
RelCollation, RexNode, RelDataType, mmutableIntList)}}.
was:
Currently multibounds are not built for HASH index.
Consider the following example:
{code:Java}
sql("CREATE TABLE t (id INT PRIMARY KEY, val INT)");
sql("CREATE INDEX t_val_idx_hash ON t USING HASH(val)");
sql("INSERT INTO t values (1, 1), (2, 2), (3, 3), (7, 4), (17, 5), (27,
6), (-38, 7)");
assertQuery("SELECT /*+ FORCE_INDEX(T_VAL_IDX_HASH) */ val FROM t WHERE
val IN (2, 3)")
.matches(QueryChecker.containsIndexScan("PUBLIC", "T"))
.check();;
{code}
Result
{noformat}
java.lang.AssertionError: Invalid plan:
Exchange
distribution: single
est: (rows=1)
TableScan
table: PUBLIC.T
predicate: SEARCH(VAL, Sarg[2, 3])
fieldNames: [VAL]
est: (rows=1)
{noformat}
The same example with SORTED index works fine.
Looks like the problem is in {{RexUtils#buildHashSearchBounds(RelOptCluster,
RelCollation, RexNode, RelDataType, mmutableIntList)}}.
> Hash index isn't used with multiple keys
> ----------------------------------------
>
> Key: IGNITE-25983
> URL: https://issues.apache.org/jira/browse/IGNITE-25983
> Project: Ignite
> Issue Type: Bug
> Components: sql ai3
> Reporter: Pavel Pereslegin
> Priority: Major
> Labels: ignite-3
>
> Currently multibounds are not built for HASH index.
> Consider the following example:
> {code:Java}
> sql("CREATE TABLE t (id INT PRIMARY KEY, val INT)");
> sql("CREATE INDEX t_val_idx_hash ON t USING HASH(val)");
> sql("INSERT INTO t values (1, 1), (2, 2), (3, 3), (7, 4), (17, 5), (27,
> 6), (-38, 7)");
> assertQuery("SELECT /*+ FORCE_INDEX(T_VAL_IDX_HASH) */ val FROM t WHERE
> val IN (2, 3)")
> .matches(QueryChecker.containsIndexScan("PUBLIC", "T"))
> .check();;
> {code}
> Result
> {noformat}
> java.lang.AssertionError: Invalid plan:
> Exchange
> distribution: single
> est: (rows=1)
> TableScan
> table: PUBLIC.T
> predicate: SEARCH(VAL, Sarg[2, 3])
> fieldNames: [VAL]
> est: (rows=1)
> {noformat}
> The same example with SORTED index works fine.
> Looks like the problem is in {{RexUtils#buildHashSearchBounds(RelOptCluster,
> RelCollation, RexNode, RelDataType, mmutableIntList)}}.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
