GROUPING() on current time in HAVING clause in subquery and outer query grouping

timurib

Hello!

This topic is based on the relevant SO question: https://stackoverflow.com/q/48869357/1220930

In short, the following query fails with strange error message:

SELECT * FROM (
  SELECT
    DATEDIFF('year', event_timestamp, NOW())
  FROM 
    events
  GROUP BY CUBE(
    DATEDIFF('year', event_timestamp, NOW())
  )
  HAVING GROUPING(
    DATEDIFF('year', event_timestamp, NOW())
  ) = 0
) AS subqueries GROUP BY 1;

[42803][7182] [Vertica]VJDBC ERROR: Grouping function arguments need to be group by expressions

But argument of the GROUPING is same with GROUP BY and NOW() is stable function. Solution is to wrap the NOW() in the extra subquery. More details are in linked SO topic (should i copy it here?). The solution works, but looks like a bug workaround, so I just want to report and clarify it.

Jim_Knicely

Hi,

I opened a support ticket on this issue and will keep you updated on its progress.

Thanks for reporting this!

timurib

As proposed in the SO answer (https://stackoverflow.com/a/48878902/1220930), I replaced the NOW() by subquery (SELECT NOW()). It works in most cases, but unfortunately in some cases it causes the following internal error:

[VX001]INTERNAL 3594: Internal Optimizer Error (11)
DETAIL: handlingHavingSubLinkWithAggregates
HINT: Please report this error to Vertica; try restating your query

Restarting the query does not help. The minimal example query:

SELECT
  c.type,
  CASE WHEN DATEDIFF('year', c.date_of_birth, (SELECT NOW())) < 18 
    THEN '< 18' 
    ELSE '> 18' 
  END,
  COUNT(*)
FROM events INNER JOIN client AS c USING(client_id)
GROUP BY CUBE(
  c.type,
  CASE WHEN DATEDIFF('year', c.date_of_birth, (SELECT NOW())) < 18 
    THEN '< 18' 
    ELSE '> 18' 
  END
)
HAVING
  GROUPING(c.type) <> 1 
  OR
  CASE WHEN DATEDIFF('year', c.date_of_birth, (SELECT NOW())) < 18 
    THEN '< 18' 
    ELSE '> 18' 
  END IS NOT NULL

The query works fine when I replace SELECT NOW() by just NOW(). Also it works when I remove first condition (by c.type) in the HAVING clause.

Looks like it related with initial issue: something wrong with NOW() function in a grouping context. Hope this helps to fix this bug.

Ramesh

One of my customers just upgraded to 8.1 and hitting this error. It works on their 7.1 environment. Any update on this yet? I am yet to look into the Cases.

                 select CASE grouping_DEAL WHEN 1 THEN  NVL(A.DEAL::varchar,'~') else A.DEAL::varchar  END as DEAL,
                 grouping_DEAL,
                 SUM((  CASE WHEN (A.grouping_DEAL=0 ) OR (A.grouping_DEAL=1 AND NVL(A.DEAL::varchar,'~')='~' ) THEN A.PCT_SUM_currentActualUPB ELSE 0 END)) denom_currentActualUPB_DEAL 
                 from 
                 (
                       Select NVL(TRIM(crt_data.DEAL),'') "DEAL",grouping(NVL(TRIM(crt_data.DEAL),'')) grouping_DEAL,
                       SUM(crt_data.currentActualUPB) "PCT_SUM_currentActualUPB" 
                       from crt.crt_data crt_data   
                       WHERE  NVL(crt_data.currentmonth,'190001')='201707' 
                       GROUP BY CUBE (NVL(TRIM(crt_data.DEAL),'')) 
                       having grouping(NVL(TRIM(crt_data.DEAL),'')) =1  
                 ) A 
                 group by A.DEAL,A.grouping_DEAL 
                 order by A.DEAL,A.grouping_DEAL

Error: ERROR: Grouping function arguments need to be group by expressions
SQLState: 42803
ErrorCode: 0

Thanks,
Ramesh Narayanan
Vertica PS

Jim_Knicely

@Ramesh,

This is fixed in Vertica 8.1.1-20.

See VER-62854:
https://my.vertica.com/docs/ReleaseNotes/8.1.x/Vertica_8.1.x_Release_Notes.htm#8.1.1-20