[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-bugs
Subject:    Re: [BUGS] BUG #8242: No way to debug "subquery must return only one column" error
From:       Amit Kapila <amit.kapila () huawei ! com>
Date:       2013-06-25 3:53:59
Message-ID: 006601ce7157$82137610$863a6230$ () kapila () huawei ! com
[Download RAW message or body]

On Monday, June 24, 2013 8:59 PM Борис � омашов 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 Борис � омашов 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=# \
insert into tbl values(4,2);  ERROR:  duplicate key value violates unique constraint \
"tbl_c1_idx"  DETAIL:  Key (c1)=(4) already exists.


With Regards,
Amit Kapila.


2013/6/24 Amit Kapila <amit.kapila@huawei.com>
On Friday, June 21, 2013 1:24 PM Борис � омашов 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 Борис � омашов <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.
  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=# 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 expression
postgres=#

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.





-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs


[prev in list] [next in list] [prev in thread] [next in thread]