Re: BUG #8242: No way to debug "subquery must return only one column" error

On Friday, June 21, 2013 1:24 PM =D0=91=D0=BE=D1=80=D0=B8=D1=81 =
=D0=A0=D0=BE=D0=BC=D0=B0=D1=88=D0=BE=D0=B2 wrote:
> I just realized that I wanted to ask about another error.
> more than one row returned by a subquery used as an expression
> not about
> subquery must return only one column

> 2013/6/21 =D0=91=D0=BE=D1=80=D0=B8=D1=81 =
=D0=A0=D0=BE=D0=BC=D0=B0=D1=88=D0=BE=D0=B2 <boraldomaster@gmail.com>
> Tom, suppose you haven't understood what the problem I'm facing with.
> Let me explain deeper.
> Try to execute the following 2 queries.
> select (select generate_series(1,2));
> select (select generate_series(1,1));
> They differ only in data, both of them are well-written, so there is =
not the problem in parsing.
> But first query gives (even in psql)
> ERROR:  more than one row returned by a subquery used as an expression

> Certainly - instead of generate_series I could write any usual query =
that fetches some data from database.
> And if this query returns one row - everything is correct.
> And certainly, instead of selecting from dual (that is how it is =
called in Oracle) - I could construct more complex external query such =
that subquery=20
> could return "more than one row" for just in some exact row (not in =
each row) of external record set.
> Example
> select id, (select friend.id from user friend where friend.id =3D =
user.id) user  from user
> This query fetches all users with their friends assuming that every =
user has only one friend.
> But if some of them will have 2 friends - this query will fail with=20
> ERROR:  more than one row returned by a subquery used as an expression
> And I will have no chance to guess - which user exactly this happened =
for.=20

  I don't think there is any way, you can know exactly for which this =
error occurred.
  The main reason is that this error occurs when an expression subquery =
returns more than one row when it is not expected.
  In some cases it is okay even if subquery expression returns more than =
one row, for example:
postgres=3D# select 1 In (select generate_series(1,2));=20
 ?column?=20
----------=20
 t=20
(1 row)=20


postgres=3D# select 4 In (select generate_series(1,2));=20
 ?column?=20
----------=20
 f=20
(1 row)=20


postgres=3D# select 1 =3D (select generate_series(1,2));=20
ERROR:  more than one row returned by a subquery used as an expression=20
postgres=3D#

Why do you want to know the exact row due to which this happens, and =
what you want to do with it?
 =20
With Regards,
Amit Kapila.