Thread: How to use result column names in having cause

CREATE TEMP TABLE foo( bar integer );

SELECT 123 AS x
  FROM foo
  GROUP BY 1
  HAVING x> AVG(bar)

causes

ERROR:  column "x" does not exist

Why ? How to make this working ?

In real application I have long expression instead of 123 and do'nt want
 repeat this expression in HAVING clause.

In VFP this select works OK.

Andrus.

On 3/31/06, Andrus <eetasoft@online.ee> wrote:
> CREATE TEMP TABLE foo( bar integer );
>
> SELECT 123 AS x
>   FROM foo
>   GROUP BY 1
>   HAVING x> AVG(bar)
>
> causes
>
> ERROR:  column "x" does not exist
>
> Why ? How to make this working ?
>
> In real application I have long expression instead of 123 and do'nt want
>  repeat this expression in HAVING clause.

You have to repeat the expression. "AS" changes the output name, it
can't be used either in the where clause or any other limiting factor
like 'having':

test=# create table t1(a int);
test=# insert into t1(a) values (1);
test=# SELECT a AS x from t1 where x=1;
ERROR:  column "x" does not exist

--
Postgresql & php tutorials
http://www.designmagick.com/

>> In real application I have long expression instead of 123 and do'nt want
>>  repeat this expression in HAVING clause.
>
> You have to repeat the expression. "AS" changes the output name, it
> can't be used either in the where clause or any other limiting factor
> like 'having':

Doc about HAVING condition says:

Each column referenced in condition must unambiguously reference a grouping
colum

HAVING x> AVG(bar) unambiguously references to a grouping column x

Is this bug ? It is very tedious to repeat same column expression in a
multiple times: one time in column expression, and n times in having clause.

Are there plans to fix this?

Andrus.

Here is my problematic query which runs OK in other DBMS.

Only way to run this in Postgres is to duplicate reatasum expression two
times in HAVING clause, right ?

Andrus.



SELECT
  'z' as doktyyp,
  r1.dokumnr,
  r1.kuluobjekt as objekt,
  r1.rid2obj,
  r1.rid3obj,
  r1.rid4obj,
  r1.rid5obj,
  r1.rid6obj,
  r1.rid7obj,
  r1.rid8obj,
  r1.rid9obj,
  dok.tasumata,
  dok.raha,

CASE WHEN ( sum(r2.reasumma)-AVG(dok.doksumma-dok.tasumata)>=
   avg(r1.reasumma) AND avg(r1.reasumma)>=0) OR
   ( sum(r2.reasumma)-AVG(dok.doksumma-dok.tasumata)<
   avg(r1.reasumma) AND avg(r1.reasumma)<0)
THEN
  avg(r1.reasumma)
ELSE
  sum(r2.reasumma)-AVG(dok.doksumma-dok.tasumata)
END as reatasum

FROM dok JOIN reakoond r1 USING (dokumnr)
JOIN reakoond r2 USING (dokumnr)

where
( r1.kuluobjekt::VARCHAR(10)||r1.rid2obj::VARCHAR(10)||
r1.rid3obj::VARCHAR(10)||r1.rid4obj::VARCHAR(10)||
r1.rid5obj::VARCHAR(10)||
r1.rid6obj::VARCHAR(10)||r1.rid7obj::VARCHAR(10)||
r1.rid8obj::VARCHAR(10)||r1.rid9obj::VARCHAR(10))>=
( r2.kuluobjekt::VARCHAR(10)||r2.rid2obj::VARCHAR(10)||
r2.rid3obj::VARCHAR(10)||r2.rid4obj::VARCHAR(10)||
r2.rid5obj::VARCHAR(10)||
r2.rid6obj::VARCHAR(10)||r2.rid7obj::VARCHAR(10)||
r2.rid8obj::VARCHAR(10)||r2.rid9obj::VARCHAR(10) )
group by 1,2,3,4,5,6,7,8,9,10,11,12,13
having (reatasum>0 AND avg(r1.reasumma)>=0) OR
      (reatasum<0 AND avg(r1.reasumma)<0)

On 3/31/06, Andrus <eetasoft@online.ee> wrote:
> >> In real application I have long expression instead of 123 and do'nt want
> >>  repeat this expression in HAVING clause.
> >
> > You have to repeat the expression. "AS" changes the output name, it
> > can't be used either in the where clause or any other limiting factor
> > like 'having':
>
> Doc about HAVING condition says:
>
> Each column referenced in condition must unambiguously reference a grouping
> colum
>
> HAVING x> AVG(bar) unambiguously references to a grouping column x
>
> Is this bug ? It is very tedious to repeat same column expression in a
> multiple times: one time in column expression, and n times in having clause.


But you're not referencing x, you're trying to use AVG(bar) in your expression.


I assume it's this way because the standard says so.. one of the more
knowledgable list members will be able to confirm/deny this.

--
Postgresql & php tutorials
http://www.designmagick.com/

On Friday 31 March 2006 08:30, chris smith wrote:
> On 3/31/06, Andrus <eetasoft@online.ee> wrote:
> > >> In real application I have long expression instead of 123 and do'nt
> > >> want repeat this expression in HAVING clause.
> > >
> > > You have to repeat the expression. "AS" changes the output name, it
> > > can't be used either in the where clause or any other limiting factor
> > > like 'having':
> >
> > Doc about HAVING condition says:
> >
> > Each column referenced in condition must unambiguously reference a
> > grouping colum
> >
> > HAVING x> AVG(bar) unambiguously references to a grouping column x
> >
> > Is this bug ? It is very tedious to repeat same column expression in a
> > multiple times: one time in column expression, and n times in having
> > clause.
>
> But you're not referencing x, you're trying to use AVG(bar) in your
> expression.
>
>
> I assume it's this way because the standard says so.. one of the more
> knowledgable list members will be able to confirm/deny this.
>

Yes, this behavior is driven by the sql standards. There is actually a very
nice paper on this subject if you are interested
http://web.onetel.com/~hughdarwen/TheThirdManifesto/Importance-of-Column-Names.pdf

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

On Fri, 31 Mar 2006, Andrus wrote:

> >> In real application I have long expression instead of 123 and do'nt want
> >>  repeat this expression in HAVING clause.
> >
> > You have to repeat the expression. "AS" changes the output name, it
> > can't be used either in the where clause or any other limiting factor
> > like 'having':
>
> Doc about HAVING condition says:
>
> Each column referenced in condition must unambiguously reference a grouping
> colum
>
> HAVING x> AVG(bar) unambiguously references to a grouping column x

IIRC technically the query is invalid, because group by isn't supposed to
run on the output of select entries (as I think is stated by "Each
<grouping column reference> shall unambiguously reference a column of the
table resulting from the <from clause>.") and I'd guess this is a side
effect of allowing group by to work on the table resulting from the select
list as well.

I think the SQL way of writing this is to use a subselect and do two
levels (ie, generate a subselect that gives the table you want to group
and use it in the from clause of the outer query that does the grouping).

"chris smith" <dmagick@gmail.com> writes:
> I assume it's this way because the standard says so..

Right.  From a logical point of view, the HAVING clause has to be
evaluated before the output expressions are computed, so it doesn't
make any sense to expect the output expressions to be available in
HAVING.  An example of why this must be so is
    SELECT x, 1/avg(y) FROM TAB GROUP BY x HAVING avg(y) > 0
If the HAVING clause isn't executed first this may fail with zero-divide
errors.

The real bug here IMHO is that we don't enforce the same rule for
GROUP BY.  Allowing "GROUP BY 1" to reference an output column is
a violation of the spec, which I think we adopted basically because
some other DBMSes do it too, but it's just as semantically nonsensical
as doing it in HAVING would be.  It's a wart on the language that we
can't really get rid of because of backwards-compatibility
considerations, but we're highly unlikely to add more such warts.

BTW, if you're really intent on not writing your big expression twice,
use a sub-select:
    SELECT x
    FROM (SELECT big_expr AS x FROM ...) AS ss
    GROUP BY ...
    HAVING x > ...

            regards, tom lane