[IGNITE-19128] Sql. Custom data types. IgniteIndexScan has incorrect types in searchBounds.

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.0
• Fix Version/s: 3.0
• Component/s: sql
• Labels: ignite-3
• Assignee: Maksim Zhuravkov
• Reporter: Maksim Zhuravkov
• Created: 27/Mar/23 14:03
• Updated: 04/Sep/24 16:22
• Resolved: 03/May/23 10:48

Description

IgniteIndexScanNode has incorrect types in its search bounds although the filter has correct types in it condition.
Example:

 @Test
  public void test() {
        sql("CREATE TABLE tx (id INTEGER PRIMARY KEY, test_key UUID)");
        sql("INSERT INTO tx VALUES(1, ?)", new UUID(2, 0));
        sql("CREATE INDEX tx_test_key_idx ON tx (test_key)");
        String query = format(
                "SELECT * FROM tx WHERE test_key > '{}'::UUID AND test_key < '{}'::UUID ORDER BY id",
                new UUID(1, 0), new UUID(3, 0)
        );
        sql(query);
    }

Error:

Caused by: java.lang.AssertionError: storageType is class java.util.UUID value must also be class java.util.UUID but it was: 00000000-0000-0001-0000-000000000001
	at org.apache.ignite.internal.sql.engine.util.SafeCustomTypeInternalConversion.tryConvertFromInternal(SafeCustomTypeInternalConversion.java:72)
	at org.apache.ignite.internal.sql.engine.util.TypeUtils.fromInternal(TypeUtils.java:330)
	at org.apache.ignite.internal.sql.engine.exec.RowConverter.toByteBuffer(RowConverter.java:141)
	at org.apache.ignite.internal.sql.engine.exec.RowConverter.toBinaryTuplePrefix(RowConverter.java:85)
	at org.apache.ignite.internal.sql.engine.exec.rel.IndexScanNode.toBinaryTuplePrefix(IndexScanNode.java:208)
	at org.apache.ignite.internal.sql.engine.exec.rel.IndexScanNode.partitionPublisher(IndexScanNode.java:146)

Plan:

IgniteExchange(distribution=[single]), id = 344
  IgniteSort(sort0=[$0], dir0=[ASC]), id = 343
    IgniteIndexScan(table=[[PUBLIC, TX]], index=[TX_TEST_KEY_IDX], type=[SORTED], searchBounds=[[RangeBounds [lowerBound=_UTF-8'00000000-0000-0001-0000-000000000000', upperBound=_UTF-8'00000000-0000-0003-0000-000000000000', lowerInclude=false, upperInclude=false]]], filters=[AND(>($t1, CAST(_UTF-8'00000000-0000-0001-0000-000000000000'):UUID NOT NULL), <($t1, CAST(_UTF-8'00000000-0000-0003-0000-000000000000'):UUID NOT NULL))], requiredColumns=[{0, 1}], collation=[[1]]), id = 326

But the following works:

 @Test
 public void test2() {
     sql("CREATE TABLE tx (id INTEGER PRIMARY KEY, test_key UUID)");
     sql("INSERT INTO tx VALUES(1, ?)", new UUID(2, 0));
     sql("CREATE INDEX tx_test_key_idx ON tx (test_key)");
     sql("SELECT * FROM tx WHERE test_key > ? AND test_key < ? ORDER BY id", new UUID(1, 0), new UUID(3, 0));
 }

Working Plan:

IgniteExchange(distribution=[single]), id = 291
  IgniteSort(sort0=[$0], dir0=[ASC]), id = 290
    IgniteIndexScan(table=[[PUBLIC, TX]], index=[TX_TEST_KEY_IDX], type=[SORTED], searchBounds=[[RangeBounds [lowerBound=?0, upperBound=?1, lowerInclude=false, upperInclude=false]]], filters=[AND(>($t1, ?0), <($t1, ?1))], requiredColumns=[{0, 1}], collation=[[1]]), id = 273

Issue Links

• causes: IGNITE-19615 Index is not used while performing SELECT over an indexed column
• is related to: IGNITE-19997 Sql. Enhancing test coverage of type coercion
• links to: GitHub Pull Request #1981
• links to: https://github.com/apache/ignite-3/pull/1981