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

[Maksim Zhuravkov (Jira)]

     [ 
https://issues.apache.org/jira/browse/IGNITE-19881?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Maksim Zhuravkov updated IGNITE-19881:
--------------------------------------
    Description: 
When REAL column is compared with 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}






  was:
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}







> Sql. Queries that access columns of type REAL may require adding an explicit 
> cast 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
>            Priority: Minor
>              Labels: ignite-3
>
> When REAL column is compared with 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)