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@sss.pgh.pa.us>

boraldomaster@gmail.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