Обсуждение: 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