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

On Monday, June 24, 2013 8:59 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:
> Amit, where should I post to force developing this feature ?

You can post this to pgsql-hackers, but I think it would be more better =
if you can check if any other database support that feature.
I feel you need a more strong case for any developer to work on it and =
community to agree on it. This is just my personal opinion, so please =
feel free to work the way you think is best.

2013/6/24 Amit Kapila <amit.kapila@huawei.com>
On Monday, June 24, 2013 1:23 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:
>> 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 will get that error. But I don't know about my error, I still =
suppose this to be unique.
> In this case - how can I debug this ? I don't know which row was =
corrupting uniqueness.
> Moreover, let's assume I have more than one subquery. In this case =
this is even more complicated to debug error, because I need to check =
each subquery
> for each row.
   It is not straightforward, but you can know by trying some logic like =
below:
   Declare the cursor with corresponding subquery
   For i In 1..10 Loop  -- this loop is corresponding to outer query =
values
     While(Fetch new row)
     {
       If fetch returns row more than once then print it.
     }

   For the part which subquery is giving problem, you might need to =
break the query into smaller parts and check.

   At the moment I am not able to think of any other better way.

>> I don't think there is any way, you can know exactly for which this =
error occurred.
> Why? Query executor knows what it executes and which row is now. Why =
cannot it log this info ?
   What I mean was that AFAIK currently there is no way to know that, if =
we enhance the way you are suggesting, then it can possible.
   PostgreSQL does something similar for duplicate key, it prints the =
value for which duplication happens.
   postgres=3D# insert into tbl values(4,2);
   ERROR:  duplicate key value violates unique constraint "tbl_c1_idx"
   DETAIL:  Key (c1)=3D(4) already exists.


With Regards,
Amit Kapila.


2013/6/24 Amit Kapila <amit.kapila@huawei.com>
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
> 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
> 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.
  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));
 ?column?
----------
 t
(1 row)


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


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

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.