Re: BUG #8242: No way to debug "subquery must return only one column" error - Mailing list pgsql-bugs

<div dir="ltr">Amit, where should I post to force developing this feature ?<br /></div><div class="gmail_extra"><br
/><br/><div class="gmail_quote">2013/6/24 Amit Kapila <span dir="ltr"><<a href="mailto:amit.kapila@huawei.com"
target="_blank">amit.kapila@huawei.com</a>></span><br/><blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px#ccc solid;padding-left:1ex"><div class="im">On Monday, June 24, 2013 1:23 PM Борис Ромашов
wrote:<br/> >> Why do you want to know the exact row due to which this happens, and what you want to do with
it?<br/> > 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
beunique.<br /> > I will get that error. But I don't know about my error, I still suppose this to be unique.<br />
>In this case - how can I debug this ? I don't know which row was corrupting uniqueness.<br /> > Moreover, let's
assumeI have more than one subquery. In this case this is even more complicated to debug error, because I need to check
eachsubquery<br /> > for each row.<br /></div>   It is not straightforward, but you can know by trying some logic
likebelow:<br />    Declare the cursor with corresponding subquery<br />    For i In 1..10 Loop  -- this loop is
correspondingto outer query values<br />      While(Fetch new row)<br />      {<br />        If fetch returns row more
thanonce then print it.<br />      }<br /><br />    For the part which subquery is giving problem, you might need to
breakthe query into smaller parts and check.<br /><br />    At the moment I am not able to think of any other better
way.<br/><div class="im"><br /> >> I don't think there is any way, you can know exactly for which this error
occurred.<br/> > Why? Query executor knows what it executes and which row is now. Why cannot it log this info ?<br
/></div>  What I mean was that AFAIK currently there is no way to know that, if we enhance the way you are suggesting,
thenit can possible.<br />    PostgreSQL does something similar for duplicate key, it prints the value for which
duplicationhappens.<br />    postgres=# insert into tbl values(4,2);<br />    ERROR:  duplicate key value violates
uniqueconstraint "tbl_c1_idx"<br />    DETAIL:  Key (c1)=(4) already exists.<br /><br /><br /> With Regards,<br /> Amit
Kapila.<br/><div class="HOEnZb"><div class="h5"><br /><br /> 2013/6/24 Amit Kapila <<a
href="mailto:amit.kapila@huawei.com">amit.kapila@huawei.com</a>><br/> On Friday, June 21, 2013 1:24 PM Борис Ромашов
wrote:<br/> > I just realized that I wanted to ask about another error.<br /> > more than one row returned by a
subqueryused as an expression<br /> > not about<br /> > subquery must return only one column<br /><br /> >
2013/6/21Борис Ромашов <<a href="mailto:boraldomaster@gmail.com">boraldomaster@gmail.com</a>><br /> > Tom,
supposeyou haven't understood what the problem I'm facing with.<br /> > Let me explain deeper.<br /> > Try to
executethe following 2 queries.<br /> > select (select generate_series(1,2));<br /> > select (select
generate_series(1,1));<br/> > They differ only in data, both of them are well-written, so there is not the problem
inparsing.<br /> > But first query gives (even in psql)<br /> > ERROR:  more than one row returned by a subquery
usedas an expression<br /><br /> > Certainly - instead of generate_series I could write any usual query that fetches
somedata from database.<br /> > And if this query returns one row - everything is correct.<br /> > And certainly,
insteadof selecting from dual (that is how it is called in Oracle) - I could construct more complex external query such
thatsubquery<br /> > could return "more than one row" for just in some exact row (not in each row) of external
recordset.<br /> > Example<br /> > select id, (select <a href="http://friend.id" target="_blank">friend.id</a>
fromuser friend where <a href="http://friend.id" target="_blank">friend.id</a> = <a href="http://user.id"
target="_blank">user.id</a>)user  from user<br /> > This query fetches all users with their friends assuming that
everyuser has only one friend.<br /> > But if some of them will have 2 friends - this query will fail with<br />
>ERROR:  more than one row returned by a subquery used as an expression<br /> > And I will have no chance to
guess- which user exactly this happened for.<br />   I don't think there is any way, you can know exactly for which
thiserror occurred.<br />   The main reason is that this error occurs when an expression subquery returns more than one
rowwhen it is not expected.<br />   In some cases it is okay even if subquery expression returns more than one row, for
example:<br/> postgres=# select 1 In (select generate_series(1,2));<br />  ?column?<br /> ----------<br />  t<br /> (1
row)<br/><br /><br /> postgres=# select 4 In (select generate_series(1,2));<br />  ?column?<br /> ----------<br />
 f<br/> (1 row)<br /><br /><br /> postgres=# select 1 = (select generate_series(1,2));<br /> ERROR:  more than one row
returnedby a subquery used as an expression<br /> postgres=#<br /><br /> Why do you want to know the exact row due to
whichthis happens, and what you want to do with it?<br /><br /> With Regards,<br /> Amit Kapila.<br /><br /><br
/></div></div></blockquote></div><br/></div>