Bigquery error with GROUP BY and HAVING with ALIAS

Question

I Noticed a weird error in bigquery standard sql. I have a table:

SELECT * FROM ds.sod;
| id | name  |
|----+-------|
| 1  | tom   |
| 2  | dick  |
| 3  | harry |

So if I group by an exporession it works

SELECT MOD(id,2) AS oddeven, COUNT(1) AS cnt
FROM ds.sod GROUP BY MOD(id,2)
| oddeven | cnt |
+---------+-----+
|       1 |   2 |
|       0 |   1 |

But if I add a HAVING clause it fails.

SELECT MOD(id,2) AS oddeven, COUNT(1) AS cnt
FROM ds.sod GROUP BY MOD(id,2) HAVING COUNT(1) > 0
ERROR: SELECT list expression references column id which is neither grouped nor aggregated

Now weirdly it works if I do not alias the column

SELECT MOD(id,2), COUNT(1) AS cnt
FROM ds.sod GROUP BY MOD(id,2) HAVING COUNT(1) > 0
| f0_ | cnt |
+-----+-----+
|   1 |   2 |
|   0 |   1 |

And it also works with the alias, if I do not use a function

SELECT id AS oddeven, COUNT(1) AS cnt
FROM ds.sod GROUP BY id HAVING COUNT(1) > 1
| oddeven | cnt |
+---------+-----+
|       3 |   1 |
|       2 |   1 |
|       1 |   1 |

Am I doing something wrong? Or is this a bug in bigquery standard SQL parsing?

Edit: Just noted it does work if I group by the alias (something I never do becuase did not work back in oracle 7)

SELECT MOD(id,2) AS oddeven, COUNT(1) AS cnt
FROM ds.sod GROUP BY oddeven HAVING COUNT(1) > 0
| oddeven | cnt |
+---------+-----+
|       1 |   2 |
|       0 |   1 |

I would guess this is a bug! maybe someone from BigQuery Team will see this post and clarify — Mikhail Berlyant

Y.K. Y.K. · Accepted Answer · 2019-07-26T08:22:32

or you can use column position

with

sample_data as (
    select
        *
    from
        unnest(
            array[
                struct(1 as id, 'tom' as name),
                struct(2, 'dick'),
                struct(3, 'harry')
            ]
        )
)

select
    mod(id, 2) as oddeven,
    count(*) as cnt
from
    sample_data
group by
    1
having
    count(*) > 0

Bigquery error with GROUP BY and HAVING with ALIAS

1 Answers