Thread: HAVING and column alias

I have the following query which gets generated dynamically from
a user-interface, particularly the subselect in the target list.
I cannot rewrite the subselect in the target list as a normal
join expression, since additional target list expressions may be
specified by the user. The below is just an example:

SELECT
SUM(p.qty),
(SELECT date_trunc('day', sales.active)
  FROM sales
  WHERE sales.purchase = p.purchase) AS field1
FROM purchases p
GROUP BY field1;

This works fine, unless the subselect in the target list doesn't
match any rows, in which case NULL is the result of the
subselect. I would like to further qualify the query with a
HAVING clause, but PostgreSQL doesn't like column aliases nor
ordinal values in the HAVING clause:

SELECT
SUM(p.qty),
(SELECT date_trunc('day', sales.active)
  FROM sales
  WHERE sales.purchase = p.purchase) AS field1
FROM purchases p
GROUP BY field1
HAVING (field1 IS NOT NULL);

ERROR: Attribute 'field1' not found

This is PostgreSQL 7.2.1. Any tips?

Mike Mascari
mascarm@mascari.com

Mike Mascari <mascarm@mascari.com> writes:
> SELECT
> SUM(p.qty),
> (SELECT date_trunc('day', sales.active)
>   FROM sales
>   WHERE sales.purchase = p.purchase) AS field1
> FROM purchases p
> GROUP BY field1
> HAVING (field1 IS NOT NULL);

> ERROR: Attribute 'field1' not found

This is definitely illegal per the SQL spec: output column names are not
legal per spec in either GROUP BY or HAVING.  Postgres is lax about this
in GROUP BY (mainly for historical reasons), but not in HAVING --- and
even in GROUP BY, we only recognize an output column name if it is used
by itself, not as part of an expression.  So your HAVING clause would
lose even if we applied GROUP-BY-like rules to it.

If you can't restructure the query, I think you'll have to repeat the
sub-SELECT in the HAVING clause rather than refer to it via the field1
alias.  If you can restructure, consider

SELECT * FROM
(SELECT
 SUM(p.qty),
 (SELECT date_trunc('day', sales.active)
   FROM sales
   WHERE sales.purchase = p.purchase) AS field1
 FROM purchases p
 GROUP BY field1) ss
WHERE (field1 IS NOT NULL);

            regards, tom lane

From: "Tom Lane" <tgl@sss.pgh.pa.us>
> Mike Mascari <mascarm@mascari.com> writes:
> > SELECT
> > SUM(p.qty),
> > (SELECT date_trunc('day', sales.active)
> >   FROM sales
> >   WHERE sales.purchase = p.purchase) AS field1
> > FROM purchases p
> > GROUP BY field1
> > HAVING (field1 IS NOT NULL);
>
> > ERROR: Attribute 'field1' not found
>
> This is definitely illegal per the SQL spec: output column
names are not
> legal per spec in either GROUP BY or HAVING.  Postgres is lax
about this
> in GROUP BY (mainly for historical reasons), but not in
HAVING --- and
> even in GROUP BY, we only recognize an output column name if
it is used
> by itself, not as part of an expression.  So your HAVING
clause would
> lose even if we applied GROUP-BY-like rules to it.

Okay. I wasn't sure. It appears as a 'feature' tested by mySQL's
crashme, which is obviously not a measurement of SQL spec
compliance, to be sure...

> If you can't restructure the query, I think you'll have to
repeat the
> sub-SELECT in the HAVING clause rather than refer to it via
the field1
> alias.

Okay. I'll have to upgrade, then. Repeating the sub-SELECT in
the HAVING clause generated that same error that I reported
earlier when two sub-SELECTs in the target list are identical in
version 7.2.1.

Thanks, Tom

Mike Mascari
mascarm@mascari.com

"Mike Mascari" <mascarm@mascari.com> writes:
> Okay. I'll have to upgrade, then. Repeating the sub-SELECT in
> the HAVING clause generated that same error that I reported
> earlier when two sub-SELECTs in the target list are identical in
> version 7.2.1.

On looking at the code, I realize that this behavior probably changed
as of this post-7.3 patch:

2003-01-16 22:25  tgl

    * src/: backend/optimizer/plan/planner.c,
    backend/optimizer/util/clauses.c, backend/parser/analyze.c,
    backend/parser/parse_agg.c, include/optimizer/clauses.h,
    include/parser/parse_agg.h: Fix parse_agg.c to detect ungrouped
    Vars in sub-SELECTs; remove code that used to do it in planner.
    That was an ancient kluge that was never satisfactory; errors
    should be detected at parse time when possible.  But at the time we
    didn't have the support mechanism (expression_tree_walker et al) to
    make it convenient to do in the parser.

So I was mistaken to tell you that it's fixed in 7.3.*.
Sorry about that...

            regards, tom lane