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

Amit, where should I post to force developing this feature ?

2013/6/24 Amit Kapila <amit(dot)kapila(at)huawei(dot)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(dot)kapila(at)huawei(dot)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(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.
> 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.
>
>
>