CODEWITHSUNDEEP
sqlgroup-byvertica
Timurib
Timurib

Reputation: 2768

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

I'm trying to execute the following query, but get the error:

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;

The query looks strange, because it is just MCVE. The real query consists of multiple subqueries under grouping in the outer query and has more complex conditions in the HAVING clause.

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

But as you can see the GROUPING() arguments are the same as CUBE() and the SELECT clauses.

If the NOW() function is replaced by some fixed value, then the query works well:

SELECT * FROM (
  SELECT
    DATEDIFF('year', event_timestamp, '2018-01-01 00:00:00')
  FROM 
    events
  GROUP BY CUBE(
    DATEDIFF('year', event_timestamp, '2018-01-01 00:00:00')
  )
  HAVING GROUPING(
    DATEDIFF('year', event_timestamp, '2018-01-01 00:00:00')
  ) = 0
) AS subqueries GROUP BY 1;
-- ok

Does it prohibited to use NOW() because it can return different values for different parts of query exection (i.e. when GROUP BY and HAVING will be actually processed)?
If it's true, then why the following modifications of base query also make it work, regardless using of NOW():

Only unmodified subquery beyond the outer query:

SELECT
  DATEDIFF('year', event_timestamp, NOW())
FROM 
  events
GROUP BY CUBE(
  DATEDIFF('year', event_timestamp, NOW())
)
HAVING GROUPING(
  DATEDIFF('year', event_timestamp, NOW())
) = 0
-- ok

Outer query without GROUP BY clause:

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;
-- ok

I can work around the problem by getting current time at the application side, but I just want to understand the issue. Do I miss some fundamental knowledge about SQL or it's a edge case of the Vertica?

Vertica version: 8.1

update 2018-07-04: the bug is fixed in Vertica 8.1.1-20 (see this comment).

Upvotes: 2

Views: 217

Answers (1)

mauro
mauro

Reputation: 5950

You can try with:

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

NOW() is considered a STABLE function: it will return exactly the same value in subsequent calls within the same transaction.

Update

This workaround is fine in most cases, but in some cases it causes the Vertica's internal error. See the relevant discussion at the Vertica forum: https://forum.vertica.com/discussion/239469/

Upvotes: 2

Related Questions