5

My query follows this structure:

WITH CTE AS (
    SELECT t1.x, COUNT(t1.y) AS count
    FROM table1 t1
    GROUP BY t1.x
)
SELECT CTE.x, CTE.count AS newCount, t2.count AS oldCount
FROM table2 t2 JOIN CTE ON t2.x = CTE.x
WHERE t2.count != CTE.count;

I get the following error: [42803] ERROR: aggregate functions are not allowed in WHERE

It looks like the CTE.count is the aggregate that triggers this error. Aren't CTEs supposed to be calculated before the main query? How to rewrite the query to avoid this?

PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit

Andrew Vershinin
  • 1,958
  • 11
  • 16
  • What happens if you skip the CTE, and instead JOIN the subquery? – jarlh Jan 13 '22 at 12:46
  • @jarlh Same error – Andrew Vershinin Jan 13 '22 at 12:47
  • 2
    Might be a side effect of the CTE optimization (which flattens the CTE). You can try `with cte as MATERIALIZED (..) ...` - if that fixes the problem I would consider it a bug that should be reported (after you checked the most recent minor version of whatever major version you are using) –  Jan 13 '22 at 12:48
  • @a_horse_with_no_name Unfortunately, same result – Andrew Vershinin Jan 13 '22 at 12:50
  • Sometimes giving the optimizer a *row goal* can help, eg adding *limit 1000000* – Stu Jan 13 '22 at 12:51
  • @Stu That doesn't seem to work too – Andrew Vershinin Jan 13 '22 at 12:56
  • 1
    Just a guess, count is a keyword, name your alias differently.. AS sth_that_is_not_count – Andreas Jan 13 '22 at 12:59
  • This [fiddle](https://dbfiddle.uk/?rdbms=postgres_14&fiddle=7a8778d840b41593c48d91846a4e1bb1) has no issues – Stu Jan 13 '22 at 13:02
  • Hmm, [works for me](https://dbfiddle.uk/?rdbms=postgres_13&fiddle=378aa1f3d780dbf964e36f2c83d905cb) –  Jan 13 '22 at 13:07
  • ANSI/ISO SQL and Teradata has COUNT as reserved word, but all(?) others seem to have it as a non-reserved keyword. https://en.wikipedia.org/wiki/SQL_reserved_words – jarlh Jan 13 '22 at 13:10
  • That wiki page is clearly wrong, *count* is an aggregate function in all of them! – Stu Jan 13 '22 at 13:13
  • It was a typo after all – Andrew Vershinin Jan 13 '22 at 13:25
  • @Stu, wrong?!? Builtin functions can be named using reserved or non-reserved keywords - ANSI/ISO SQL says count is reserved, but most vendors don't. – jarlh Jan 13 '22 at 13:40
  • Ah yes ok I misunderstood what you meant by reserved, it's whether it requires it to be specifically delimted, eg in some databases I could alias a column as "count" and it be fine but not "key". – Stu Jan 13 '22 at 14:37

2 Answers2

4

The t2.count is being interpreted as an aggregate COUNT() function, and your t2 table does not have a column called count.

Make sure that your table does actually have a count column, or make sure to compute it's aggregate count on another CTE before joining, and then comparing the the results. Also avoid using the alias "count", like the following:

WITH CTE AS (
    SELECT t1.x, COUNT(t1.y) AS total
    FROM table1 t1
    GROUP BY t1.x
),
CTE2 AS (
    SELECT t2.x, COUNT(t2.y) AS total
    FROM table2 t2
    GROUP BY t2.x
)
SELECT 
    CTE1.x, 
    CTE1.total AS newCount, 
    CTE2.total AS oldCount
FROM 
    CTE2 
    JOIN CTE1 ON CTE2.x = CTE1.x
WHERE 
    CTE2.total != CTE1.total;
EzLo
  • 13,780
  • 10
  • 33
  • 38
3

Looks like it is the "t2.count" that causes the issue.

On dbfiddle I can reproduce the issue ONLY when there is no column named "count" in the table2.

In other words, the error occurs only when table 2 defined like that:

create table table2 (x int, y int);

However if I added the "count" column, the error is gone

create table table2 (x int, y int, count int);

I believe when there is no such column, the postgres handles "count" as an aggregate function and throws the error.

So, my solution would be to check if such column is present and to never use preserved keywords as column names

ekochergin
  • 4,109
  • 2
  • 12
  • 19