Uploaded image for project: 'Phoenix'
  1. Phoenix
  2. PHOENIX-5388

Incorrect current_date()/now() when query involves subquery

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 4.15.0, 5.1.0
    • Labels:
      None

      Description

      Following query fails in the month of December:-

      select  NOW(),  MONTH(NOW()) m, 
      CASE 
       WHEN MONTH(NOW()) = 12 THEN TO_TIME(YEAR(NOW()) || '-12-31 23:59:59.999') 
       ELSE TO_TIME(YEAR(NOW()) || '-' || ( MONTH(NOW()) + 1  ) || '-01 23:59:59.999') - 1 
      END  AS this_month_end
      

      It is due to an optimization we have during compilation where we evaluate the expression if they result in to constant so that we don't need to do it for every row.
      Currently parsing stack evaluates every expression if possible without considering any condition, resulting in evaluation of all three expression for CASE node, MONTH(NOW()) = 12 , TO_TIME(YEAR(NOW()) || '12-31 23:59:59.999') ,TO_TIME(YEAR(NOW()) || '' || ( MONTH(NOW()) + 1 ) || '-01 23:59:59.999') - 1) but evaluation of 3rd one will fail because of invalid month.

      Workaround: For the particular use-case , Following query though help in preventing the expressions of WHEN CASE to be evaluated to a constant at compile time.

        Attachments

        1. PHOENIX-5388_v2.patch
          3 kB
          Rajeshbabu Chintaguntla
        2. PHOENIX-5388.patch
          3 kB
          Ankit Singhal

          Activity

            People

            • Assignee:
              ankit@apache.org Ankit Singhal
              Reporter:
              ankit@apache.org Ankit Singhal
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: