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

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 Борис Ромашов <boraldomaster(at)gmail(dot)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 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
>>
>
>