Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-5156

Support implicit number type cast for IN Sub-query

    XMLWordPrintableJSON

Details

    • Bug
    • Status: In Progress
    • Major
    • Resolution: Unresolved
    • 1.30.0
    • None
    • core

    Description

      The SQL:

      select * from dept where deptno + 20 in (select deptno from dept);

      Calcite returns the wrong answer.

      but the SQL

       

      select * from dept where deptno + 20 in (select cast(deptno as integer) from dept);

      Calcite returns the correct answer.

      So when we generate the RelNode, we can add the type cast.

      Before the type cast:

      LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2])
  LogicalFilter(condition=[IN(+($0, 20), {
LogicalProject(DEPTNO=[$0])
  LogicalTableScan(table=[[scott, DEPT]])
})])
    LogicalTableScan(table=[[scott, DEPT]])

      After the type cast:

      LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2])
  LogicalFilter(condition=[IN(+($0, 20), {
LogicalProject(EXPR$0=[CAST($0):INTEGER NOT NULL])
  LogicalTableScan(table=[[scott, DEPT]])
})])
    LogicalTableScan(table=[[scott, DEPT]])

      Same SQL includes:

      select *
from dept
where deptno in (select sal-780 from emp)

      Attachments

        Issue Links

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. CALCITE-5930 When sqlToRel is converted from in to join, condition '=' on both sides will have inconsistent data types.

          • Major - Major loss of function.
          • Closed
          is related to

          Improvement - An improvement or enhancement to an existing feature or task. CALCITE-2302 Implicit type cast support

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. CALCITE-5743 Query gives incorrect result when COUNT appears in the correlated subquery select list

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. CALCITE-5210 "type mismatch" litmus test failure during during SqlToRelConverter for group-by on `case` having `in` expression predicates exceeding SqlRelConverter.Config InSubQueryThreshold with nullable left-hand-side

          • Major - Major loss of function.
          • Closed
          links to

          Web Link GitHub Pull Request #2811

          Activity

            People

              nobigo xiong duan
              nobigo xiong duan
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 0.5h
                  0.5h