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

SINGLE_VALUE rewrite to wrong SQL when the sub-query returns one not-NULL value and one NULL value in PostgreSQL, MySQL, HSQL dialect

    XMLWordPrintableJSON

Details

    • Improvement
    • Status: Resolved
    • Major
    • Resolution: Fixed
    • 1.37.0
    • 1.38.0
    • core

    Description

      Calcite rewrite the SINGLE_VALUE to different sql :

      For example in HSQL:

      CASE COUNT(*)
      WHEN 0 THEN NULL
      WHEN 1 THEN MIN(<result>)
      ELSE (VALUES 1 UNION ALL VALUES 1)
      END

      This is right. But Calcite will generateļ¼š

      CASE COUNT(result) 
      WHEN 0 THEN NULL
      WHEN 1 THEN MIN(<result>)
      ELSE (VALUES 1 UNION ALL VALUES 1)
      END

      This sql will return wrong result.

      For Example:

      tableA:

      c1 c2
      4 1
      NULL NULL
      NULL NULL
      NULL NULL

      TheSQL:

      select *
      from tableA
      where c1 > (select c2 from tableA);

      will throw : [21000][1242] Subquery returns more than 1 row

      But SQL:

      select *
      from tableA left join (select case count(c2)
      when 0 then null
      when 1 then min(c2)
      else (select cast(null as integer) union all select cast(null as integer)) end as alias
      from tableA) as t1 on true
      where tableA.c1 > t1.alias;

      will return one row value.

      Attachments

        Issue Links

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved: