[jira] [Created] (IGNITE-19881) Sql. Queries that access columns of type REAL may require explicit casts in order to pick an index.

["Maksim Zhuravkov (Jira)" <ji...@apache.org>]

Maksim Zhuravkov created IGNITE-19881:
-----------------------------------------

             Summary: Sql. Queries that access columns of type REAL may require explicit casts in order to pick an index.
                 Key: IGNITE-19881
                 URL: https://issues.apache.org/jira/browse/IGNITE-19881
             Project: Ignite
          Issue Type: Bug
          Components: sql
    Affects Versions: 3.0.0-beta1
            Reporter: Maksim Zhuravkov


When a column is compared with a non-integer numeric literal, index is not picked :
{code:java}
 sql("CREATE TABLE t_real (ID INTEGER PRIMARY KEY, VAL REAL)");
        sql("CREATE INDEX t_real_idx ON t_real(VAL)");

        sql("INSERT INTO t_real VALUES (1, 42)");

        // 1 fails
        assertQuery("SELECT id FROM t_real WHERE val = 42.0")
                .matches(containsIndexScan("PUBLIC", "T_REAL", "T_REAL_IDX"))
                .returns(1)
                .check();

        // 2 passes
        assertQuery("SELECT id FROM t_real WHERE val = 42.0::REAL")
                .matches(containsIndexScan("PUBLIC", "T_REAL", "T_REAL_IDX"))
                .returns(1)
                .check();
{code}
*Expected behaviour*: Query#1 should use an index w/o a cast.

*Additional information*: When a literal is an integer, sql-engine picks an index:
{code:java}
        assertQuery("SELECT id FROM t_real WHERE val = 42")
                .matches(containsIndexScan("PUBLIC", "T_REAL", "T_REAL_IDX"))
                .returns(1)
                .check();
{code}








--
This message was sent by Atlassian Jira
(v8.20.10#820010)