Re: BUG #8242: No way to debug "subquery must return only one column" error
От | Борис Ромашов |
---|---|
Тема | Re: BUG #8242: No way to debug "subquery must return only one column" error |
Дата | |
Msg-id | CAJh38TMFPWkg1x1-axDmOewV+YmutKKp6PLvEu0LnwKHFV7Onw@mail.gmail.com обсуждение исходный текст |
Ответ на | BUG #8242: No way to debug "subquery must return only one column" error (boraldomaster@gmail.com) |
Ответы |
Re: BUG #8242: No way to debug "subquery must return only one column" error
|
Список | pgsql-bugs |
>> Why do you want to know the exact row due to which this happens, and what you want to do with it?
Suppose I have a query that should select something and this query has some subquery that is (possibly by error) supposed to return only one row, i.e. I mean that it fetches smth unique. But for some row it appears not to be unique.>> I don't think there is any way, you can know exactly for which this error occurred.
2013/6/24 Amit Kapila <amit.kapila@huawei.com>
On Friday, June 21, 2013 1:24 PM Борис Ромашов wrote:I don't think there is any way, you can know exactly for which this error occurred.
> 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@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
> 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.
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=# select 1 In (select generate_series(1,2));
?column?
----------
t
(1 row)
postgres=# select 4 In (select generate_series(1,2));
?column?
----------
f
(1 row)
postgres=# select 1 = (select generate_series(1,2));ERROR: more than one row returned by a subquery used as an expressionpostgres=#
Why do you want to know the exact row due to which this happens, and what you want to do with it?
With Regards,
Amit Kapila.
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Amit KapilaДата:
Сообщение: Re: BUG #8242: No way to debug "subquery must return only one column" error
Следующее
От: Amit KapilaДата:
Сообщение: Re: BUG #8242: No way to debug "subquery must return only one column" error