PostgreSQL Discussion - How to use result column names in having cause

Author

How to use result column names in having cause

Andrus

2006-03-31, 7:33 am

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.

chris smith

2006-03-31, 7:33 am

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/

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Andrus

2006-03-31, 7:33 am

>> In real application I have long expression instead of 123 and do'nt want
>
> 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.

Andrus

2006-03-31, 7:33 am

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)

chris smith

2006-03-31, 7:33 am

On 3/31/06, Andrus <eetasoft@online.ee> wrote:
>
> 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/

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Robert Treat

2006-03-31, 9:31 am

On Friday 31 March 2006 08:30, chris smith wrote:
> On 3/31/06, Andrus <eetasoft@online.ee> wrote:
>
> 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/T...olumn-Names.pdf

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Stephan Szabo

2006-03-31, 9:31 am

On Fri, 31 Mar 2006, Andrus wrote:

>
> 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).

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Tom Lane

2006-03-31, 11:28 am

"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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Sponsored Links