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

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 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 = 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
*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.

2013/6/20 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>

> boraldomaster(at)gmail(dot)com writes:
> > When I get this message I cannot guess from it's description what really
> > causes this error.
> > I would like to see exactly the subquery that returned more than one
> column
> > and the row where this happened.
>
> That's a parse-time error, so it's nonsensical to ask for "the row where
> it happened". AFAICS, the parser should give back a syntax-error
> pointer for this error; for example, when I try to provoke the error in
> psql, I get
>
> =# select * from table1 where id = any(array(select c1,c2 from table2));
> ERROR: subquery must return only one column
> LINE 1: select * from table1 where id = any(array(select c1,c2 ...
> ^
>
> which shows me that the problem is associated with the ARRAY() construct
> not accepting multiple input columns. If you're not seeing such an
> error pointer, it's the fault of whatever client-side software you're
> working in.
>
> regards, tom lane
>