GROUP BY in CTE causes ELSE in outer query to be prematurely evaluated - Mailing list pgsql-bugs

If you have the query

WITH FOO AS (
         SELECT 0 AS GROUPING
     )
SELECT CASE
    WHEN GROUPING >= 0 THEN 'non-negative'
    ELSE CAST((1 / 0) AS VARCHAR)
END
FROM FOO;

it works successfully, having the ELSE as a safeguard against having coded a bug, having forgotten a WHEN branch, so it fails fast.

So if you have

WITH FOO AS (
         SELECT -1 AS GROUPING
     )
SELECT CASE
    WHEN GROUPING >= 0 THEN 'non-negative'
    ELSE CAST((1 / 0) AS VARCHAR)
END
FROM FOO;

it fails with a division by zero error.

But if you have the query

WITH FOO AS (
         SELECT 0 AS GROUPING
         GROUP BY 1
     )
SELECT CASE
    WHEN GROUPING >= 0 THEN 'non-negative'
    ELSE CAST((1 / 0) AS VARCHAR)
END
FROM FOO;

then it always fails with division by zero error, even though the result should still be 'non-negative'.

Cheers

Björn