By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,822 Members | 1,023 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,822 IT Pros & Developers. It's quick & easy.

[PgSQL 7.4.1] Problem with subquery

P: n/a
Hi,

Yesterday i had upgrade my PostgreSQL server from version 7.2.4
to 7.4.1.

There are troubles with a subquery after the upgrade.

Here is the query:

SELECT team_naam, team_id, wpim, (
SELECT count(thuis_uitslag)
FROM wedstrijden
WHERE (thuis_wed = t.team_id
OR uit_wed = t.team_id)
AND seizoen_id = 3 AND klasse_id = 1)
AS wd, (
SELECT count(t.team_id)
FROM wedstrijden w
WHERE (thuis_wed = t.team_id AND thuis_uitslag >
uit_uitslag OR uit_wed = t.team_id
AND thuis_uitslag < uit_uitslag)
AND seizoen_id = 3
AND klasse_id = 1 )
AS gw
FROM teams t
WHERE seizoen_id = 3 AND klasse_id = 1
GROUP BY t.team_naam, t.team_id, t.team_id, t.wpim

The SQL server returns:
-----------------------
SQL error:
ERROR: more than one row returned by a subquery used as an expression

This query works perfectly in PgSQL v7.2.4, but not in 7.4.1
Does anybody know what's wrong here?

--
Edwin Pauli

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

Nov 22 '05 #1
Share this Question
Share on Google+
2 Replies

P: n/a
Edwin Pauli <ed***@epauli.dyndns.org> writes:
There are troubles with a subquery after the upgrade. SELECT team_naam, team_id, wpim, (
SELECT count(thuis_uitslag)
FROM wedstrijden
WHERE (thuis_wed = t.team_id
OR uit_wed = t.team_id)
AND seizoen_id = 3 AND klasse_id = 1)
AS wd, (
SELECT count(t.team_id)
FROM wedstrijden w
WHERE (thuis_wed = t.team_id AND thuis_uitslag >
uit_uitslag OR uit_wed = t.team_id
AND thuis_uitslag < uit_uitslag)
AND seizoen_id = 3
AND klasse_id = 1 )
AS gw
FROM teams t
WHERE seizoen_id = 3 AND klasse_id = 1
GROUP BY t.team_naam, t.team_id, t.team_id, t.wpim ERROR: more than one row returned by a subquery used as an expression


Since t.team_id is a variable of the outer query, count(t.team_id) is an
aggregate of the outer query according to the SQL spec, and accordingly
it is just a constant from the perspective of your second sub-SELECT.
That sub-SELECT is therefore not doing any aggregation of its own and
is trying to return multiple rows.

We didn't implement this fine point per-spec before 7.4, but we do now.
See http://archives.postgresql.org/pgsql...6/msg00070.php

Solution: use count(*) instead. AFAICS you have no need to check
whether t.team_id is nonnull, since the WHERE clause could not succeed
anyway if it's null.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 22 '05 #2

P: n/a
Tom Lane wrote:
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org


No. I'm searching in the archive now.
When i can't find a solution, i'll try your suggestion : count(*).
--
Edwin Pauli

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

Nov 22 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.