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

When SOME sub-query is SqlNodeList and converted to VALUES, Calcite returns incorrect result

    XMLWordPrintableJSON

Details

    Description

      As discussed in the comments: The SQL

      select * from "scott".emp where sal > some (4000, 2000)
      

      is illegal SQL. So we should throw an exception instead of return a wrong result.

      The SQL include:

      SOME and ALL:

      1: select deptno from emp where deptno > some (23, 56);
      2: select deptno from emp where deptno > some (cast(23 as integer), cast(56 as integer));
      3: select deptno from emp where deptno > all (cast(23 as integer), cast(56 as integer));
      4: select deptno from emp where deptno > all (23, 56)

      NOT IN:

      select deptno from emp where deptno not in (60, 56); 
      DEPTNO
      10
      10
      20
      30
      30
      50
      50
      null
      !ok

      This case can't handle NULL value. 

      But when we use the VALUES expression, SQL always can return the correct result. The SQL like:

      select deptno from emp where deptno > some (VALUES (23), (56));
      DEPTNO
      30
      30
      50
      50
      60
      !ok

      Attachments

        Issue Links

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

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