Обсуждение: BUG #8242: No way to debug "subquery must return only one column" error
BUG #8242: No way to debug "subquery must return only one column" error
От
boraldomaster@gmail.com
Дата:
The following bug has been logged on the website: Bug reference: 8242 Logged by: boraldomaster Email address: boraldomaster@gmail.com PostgreSQL version: 9.1.2 Operating system: any Description: When I get this message I cannot guess from it's description what really causes this error. I would like to see exactly the subquery that returned more than one column and the row where this happened.
boraldomaster@gmail.com writes:
> When I get this message I cannot guess from it's description what really
> causes this error.
> I would like to see exactly the subquery that returned more than one column
> and the row where this happened.
That's a parse-time error, so it's nonsensical to ask for "the row where
it happened". AFAICS, the parser should give back a syntax-error
pointer for this error; for example, when I try to provoke the error in
psql, I get
=# select * from table1 where id = any(array(select c1,c2 from table2));
ERROR: subquery must return only one column
LINE 1: select * from table1 where id = any(array(select c1,c2 ...
^
which shows me that the problem is associated with the ARRAY() construct
not accepting multiple input columns. If you're not seeing such an
error pointer, it's the fault of whatever client-side software you're
working in.
regards, tom lane
Tom, suppose you haven't understood what the problem I'm facing with.
Let me explain deeper.select (select generate_series(1,2));
select (select generate_series(1,1));
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
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
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.
2013/6/20 Tom Lane <tgl@sss.pgh.pa.us>
boraldomaster@gmail.com writes:
> When I get this message I cannot guess from it's description what really
> causes this error.
> I would like to see exactly the subquery that returned more than one column
> and the row where this happened.
That's a parse-time error, so it's nonsensical to ask for "the row where
it happened". AFAICS, the parser should give back a syntax-error
pointer for this error; for example, when I try to provoke the error in
psql, I get
=# select * from table1 where id = any(array(select c1,c2 from table2));
ERROR: subquery must return only one column
LINE 1: select * from table1 where id = any(array(select c1,c2 ...
^
which shows me that the problem is associated with the ARRAY() construct
not accepting multiple input columns. If you're not seeing such an
error pointer, it's the fault of whatever client-side software you're
working in.
regards, tom lane
I just realized that I wanted to ask about another error.
more than one row returned by a subquery used as an expression
not aboutmore than one row returned by a subquery used as an expression
subquery must return only one column
2013/6/21 Борис Ромашов <boraldomaster@gmail.com>
But first query gives (even in psql)They differ only in data, both of them are well-written, so there is not the problem in parsing.Try to execute the following 2 queries.Tom, suppose you haven't understood what the problem I'm facing with.Let me explain deeper.
select (select generate_series(1,2));
select (select generate_series(1,1));
ERROR: more than one row returned by a subquery used as an expressionCertainly - 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.ExampleThis 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 expressionAnd I will have no chance to guess - which user exactly this happened for.2013/6/20 Tom Lane <tgl@sss.pgh.pa.us>boraldomaster@gmail.com writes:
> When I get this message I cannot guess from it's description what really
> causes this error.
> I would like to see exactly the subquery that returned more than one column
> and the row where this happened.
That's a parse-time error, so it's nonsensical to ask for "the row where
it happened". AFAICS, the parser should give back a syntax-error
pointer for this error; for example, when I try to provoke the error in
psql, I get
=# select * from table1 where id = any(array(select c1,c2 from table2));
ERROR: subquery must return only one column
LINE 1: select * from table1 where id = any(array(select c1,c2 ...
^
which shows me that the problem is associated with the ARRAY() construct
not accepting multiple input columns. If you're not seeing such an
error pointer, it's the fault of whatever client-side software you're
working in.
regards, tom lane
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=20 > 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=20 > 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.=20 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));=20 ?column?=20 ----------=20 t=20 (1 row)=20 postgres=3D# select 4 In (select generate_series(1,2));=20 ?column?=20 ----------=20 f=20 (1 row)=20 postgres=3D# select 1 =3D (select generate_series(1,2));=20 ERROR: more than one row returned by a subquery used as an expression=20 postgres=3D# Why do you want to know the exact row due to which this happens, and = what you want to do with it? =20 With Regards, Amit Kapila.
>> 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.
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=20
> 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);=20
ERROR: duplicate key value violates unique constraint "tbl_c1_idx"=20
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.
<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>
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.