Thread: Polymorphic arguments and composite types
I have a few questions from recent attempts to perform a join between
two tables, where one table has an integer array in it. Join is of the
form:
select ... from t1 where col1 = any (select col2 from t2);
Not sure whether these are bugs, intentional, incomplete functionality.
I've solved the problem, but not in a very straightforward manner.
Here's a simpler example that shows the problem I hit.
postgres=# \d c Table "public.c"Column | Type | Modifiers
--------+-----------+-----------col1 | integer | col2 | integer[] |
postgres=# select * from c;col1 | col2
------+------- 1 | {1,2}
(1 row)
postgres=# select * from c where col1 = any ('{1,2}');col1 | col2
------+------- 1 | {1,2}
(1 row)
postgres=# select * from c where col1 = any (col2);col1 | col2
------+------- 1 | {1,2}
(1 row)
...which is fine on just one table, but I want to join...
postgres=# select * from c where col1 = any (select col2 from c);
ERROR: operator does not exist: integer = integer[]
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.
postgres=# select * from c where col1 = any (ARRAY(select col2 from c));
ERROR: could not find array type for data type integer[]
Q1: Why not?? In the SELECT clause a sub-select returning a single
column is allowed, but it seems not here. Maybe a composite type issue?
Doesn't appear to be, since it knows type is integer[]
postgres=# select col1, (select col2 from c) as col2 from c;col1 | col2
------+------- 1 | {1,2}
(1 row)
So we now try to create a function to do this instead...
postgres=# create function func() returns anyarray as $$
declareval integer[];
beginselect col2 into val from c;return val;
end;
$$ language plpgsql;
ERROR: cannot determine result data type
DETAIL: A function returning a polymorphic type must have at least one
polymorphic argument.
Q2: Why does a function returning a polymorphic type have to have at
least one polymorphic argument? It's easy to create a function that
returns a polymorphic result yet has no linkage at all to the input.
postgres=# create function func(inval anyelement) returns anyarray as $$
declareval integer[];
beginselect col2 into val from c;return val;
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# select func(1);func
-------{1,2}
(1 row)
postgres=# select * from c where col1 = any (select func(1));
ERROR: operator does not exist: integer = integer[]
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.
...same error, which is good news I suppose
postgres=# select * from c where col1 = any (func(1));col1 | col2
------+------- 1 | {1,2}
(1 row)
Q3: Why is a composite type with just one attribute not the same type as
the attribute? We know this is possible in the SELECT list, but we don't
know its the same thing in other contexts.
-- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
Simon Riggs <simon@2ndquadrant.com> writes:
> ...which is fine on just one table, but I want to join...
> postgres=# select * from c where col1 = any (select col2 from c);
> ERROR: operator does not exist: integer = integer[]
That isn't a join. Are you looking for something like
select * from c, c as c2 where c.col1 = any (c2.col2)
regards, tom lane
On Fri, 2007-10-05 at 10:52 -0400, Tom Lane wrote: > Simon Riggs <simon@2ndquadrant.com> writes: > > ...which is fine on just one table, but I want to join... > > > postgres=# select * from c where col1 = any (select col2 from c); > > ERROR: operator does not exist: integer = integer[] > > That isn't a join. Are you looking for something like I guess I was looking for the sub-select way of doing it, because I actually wanted to exclude rows. > select * from c, c as c2 where c.col1 = any (c2.col2) That works, thanks. As I said, I already solved the problem a different way. I was looking to understand the 3 questions I raised along the way. Can you throw any light on those questions? 1. Why doesn't the subselect work? 2. Why does a function returning a polymorphic type have to have at least one polymorphic argument? It's easy to create a function that returns a polymorphic result yet has no linkage at all to the input. 3. Why is a composite type with just one attribute not the same type as the attribute? We know this is possible in the SELECT list, but we don't know its the same thing in other contexts. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
Simon Riggs <simon@2ndquadrant.com> writes:
> 1. Why doesn't the subselect work?
Because x = ANY (SELECT y FROM ...) is defined by the SQL standard to
involve performing x = y at each row of the SELECT output. There's
no wiggle room there.
The standard does not specify any meaning for x = ANY (not-a-SELECT)
and we've shoehorned some array behavior into that gap, but it's
completely different semantics.
> 2. Why does a function returning a polymorphic type have to have at
> least one polymorphic argument?
So that the parser can figure out what type a particular call is
supposed to return.
> 3. Why is a composite type with just one attribute not the same type as
> the attribute?
Why in the world would you expect these to be the same? It'd be akin to
claiming that a one-element array is the same as the element type.
regards, tom lane
On Fri, 2007-10-05 at 16:04 +0100, Simon Riggs wrote: > > select * from c, c as c2 where c.col1 = any (c2.col2) > > That works, thanks. > > As I said, I already solved the problem a different way. I was looking > to understand the 3 questions I raised along the way. > > Can you throw any light on those questions? > 1. Why doesn't the subselect work? > You could do something like: SELECT * FROM c AS c1, c AS c2 WHERE ARRAY[c1.col1] <@ ANY(SELECT c2.col2); Regards,Jeff Davis
Jeff Davis <pgsql@j-davis.com> writes:
> You could do something like:
> SELECT * FROM c AS c1, c AS c2 WHERE ARRAY[c1.col1] <@ ANY(SELECT c2.col2);
Good point --- actually he could convert it back to the original
subselect style, as long as he's using the correct operator:
SELECT * FROM c WHERE ARRAY[col1] <@ ANY(SELECT col2 FROM c);
The one-element-array trick seems a bit awkward though. I wonder
why we don't have an "anyelement <@ anyarray" kind of operator...
regards, tom lane
On Fri, 2007-10-05 at 12:29 -0400, Tom Lane wrote: > Jeff Davis <pgsql@j-davis.com> writes: > > You could do something like: > > SELECT * FROM c AS c1, c AS c2 WHERE ARRAY[c1.col1] <@ ANY(SELECT c2.col2); > > Good point --- actually he could convert it back to the original > subselect style, as long as he's using the correct operator: > > SELECT * FROM c WHERE ARRAY[col1] <@ ANY(SELECT col2 FROM c); You're right, that's a better example. > > The one-element-array trick seems a bit awkward though. I wonder > why we don't have an "anyelement <@ anyarray" kind of operator... > I thought we did -- until I decided to test my example in psql before hitting "send". It certainly makes sense to me that we should have it. Regards,Jeff Davis
On Fri, 2007-10-05 at 09:46 -0700, Jeff Davis wrote: > On Fri, 2007-10-05 at 12:29 -0400, Tom Lane wrote: > > The one-element-array trick seems a bit awkward though. I wonder > > why we don't have an "anyelement <@ anyarray" kind of operator... > > I thought we did -- until I decided to test my example in psql before > hitting "send". It certainly makes sense to me that we should have it. It does make sense to have one. (Thanks for the additional examples, guys). -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
On Fri, 2007-10-05 at 11:42 -0400, Tom Lane wrote: > Simon Riggs <simon@2ndquadrant.com> writes: > > 1. Why doesn't the subselect work? > > Because x = ANY (SELECT y FROM ...) is defined by the SQL standard to > involve performing x = y at each row of the SELECT output. There's > no wiggle room there. > > The standard does not specify any meaning for x = ANY (not-a-SELECT) > and we've shoehorned some array behavior into that gap, but it's > completely different semantics. OK, so we should document it as not being possible. > > 2. Why does a function returning a polymorphic type have to have at > > least one polymorphic argument? > > So that the parser can figure out what type a particular call is > supposed to return. The parser can look at the datatype of the RETURNS clause, it doesn't need to look at the datatype of the *input* arguments. That error looks like a bug to me. In my example the input datatype differed from the returns datatype, plus the input and output were totally disconnected. => Bug. > > 3. Why is a composite type with just one attribute not the same type as > > the attribute? > > Why in the world would you expect these to be the same? It'd be akin to > claiming that a one-element array is the same as the element type. Because we already do exactly that here: select 1, (select col2 from c), 3; The inner select returns a ROW, yet we treat it as a single column value. I'll look at documenting that. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
Simon Riggs <simon@2ndquadrant.com> writes:
> I'll look at documenting that.
I think the problem here is you've not bothered to read the manual,
because all of these behaviors *are* documented; two of them are
furthermore required by the SQL standard.
regards, tom lane
On Fri, 5 Oct 2007, Simon Riggs wrote: > Because we already do exactly that here: > > select 1, (select col2 from c), 3; > > The inner select returns a ROW, yet we treat it as a single column > value. The inner select does not return a row. It's not a <row subquery>, it's a <scalar subquery>.
On Fri, 2007-10-05 at 13:18 -0400, Tom Lane wrote: > Simon Riggs <simon@2ndquadrant.com> writes: > > I'll look at documenting that. > > I think the problem here is you've not bothered to read the manual, > because all of these behaviors *are* documented; two of them are > furthermore required by the SQL standard. Your thoughts aren't correct, but why so touchy? There is no comment in the manual here http://developer.postgresql.org/pgdocs/postgres/arrays.html#AEN6096 which is the logical place for it to live, but I explored other places too. Why would you object to improving the manual? -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
On Fri, 2007-10-05 at 10:32 -0700, Stephan Szabo wrote: > On Fri, 5 Oct 2007, Simon Riggs wrote: > > > Because we already do exactly that here: > > > > select 1, (select col2 from c), 3; > > > > The inner select returns a ROW, yet we treat it as a single column > > value. > > The inner select does not return a row. It's not a <row subquery>, it's a > <scalar subquery>. Thanks Stephan, Tom already explained that. My comments above were in response to "Why would you think that?" -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
On Fri, 5 Oct 2007, Simon Riggs wrote: > On Fri, 2007-10-05 at 10:32 -0700, Stephan Szabo wrote: > > On Fri, 5 Oct 2007, Simon Riggs wrote: > > > > > Because we already do exactly that here: > > > > > > select 1, (select col2 from c), 3; > > > > > > The inner select returns a ROW, yet we treat it as a single column > > > value. > > > > The inner select does not return a row. It's not a <row subquery>, it's a > > <scalar subquery>. > > Thanks Stephan, Tom already explained that. > > My comments above were in response to "Why would you think that?" Right, but I guess I couldn't see why you would consider that the same as treating a rowtype as a scalar, because when I look at that my brain converts that to a scalar subquery, so I guess I simply see the scalar. If we supported select 1, (select 2,3), select 4 giving something like (1,(2,3),4), I'd also have confusion over the case, but that should error.
On Fri, 2007-10-05 at 10:59 -0700, Stephan Szabo wrote: > On Fri, 5 Oct 2007, Simon Riggs wrote: > > > On Fri, 2007-10-05 at 10:32 -0700, Stephan Szabo wrote: > > > On Fri, 5 Oct 2007, Simon Riggs wrote: > > > > > > > Because we already do exactly that here: > > > > > > > > select 1, (select col2 from c), 3; > > > > > > > > The inner select returns a ROW, yet we treat it as a single column > > > > value. > > > > > > The inner select does not return a row. It's not a <row subquery>, it's a > > > <scalar subquery>. > > > > Thanks Stephan, Tom already explained that. > > > > My comments above were in response to "Why would you think that?" > > Right, but I guess I couldn't see why you would consider that the same as > treating a rowtype as a scalar, because when I look at that my brain > converts that to a scalar subquery, so I guess I simply see the scalar. > If we supported select 1, (select 2,3), select 4 giving something like > (1,(2,3),4), I'd also have confusion over the case, but that should error. Well, my brain didn't... All I've said was that we should document it, to help those people that don't know they're SQL standard as good as the best people on this list. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
On Fri, 5 Oct 2007, Simon Riggs wrote: > On Fri, 2007-10-05 at 10:59 -0700, Stephan Szabo wrote: > > On Fri, 5 Oct 2007, Simon Riggs wrote: > > > > > On Fri, 2007-10-05 at 10:32 -0700, Stephan Szabo wrote: > > > > On Fri, 5 Oct 2007, Simon Riggs wrote: > > > > > > > > > Because we already do exactly that here: > > > > > > > > > > select 1, (select col2 from c), 3; > > > > > > > > > > The inner select returns a ROW, yet we treat it as a single column > > > > > value. > > > > > > > > The inner select does not return a row. It's not a <row subquery>, it's a > > > > <scalar subquery>. > > > > > > Thanks Stephan, Tom already explained that. > > > > > > My comments above were in response to "Why would you think that?" > > > > Right, but I guess I couldn't see why you would consider that the same as > > treating a rowtype as a scalar, because when I look at that my brain > > converts that to a scalar subquery, so I guess I simply see the scalar. > > If we supported select 1, (select 2,3), select 4 giving something like > > (1,(2,3),4), I'd also have confusion over the case, but that should error. > > Well, my brain didn't... All I've said was that we should document it, > to help those people that don't know they're SQL standard as good as the > best people on this list. Where would you document this beyond 4.2 though? While I don't exactly like the wording of 4.2.9, it seems like it's already trying to say that.
On Fri, 2007-10-05 at 11:24 -0700, Stephan Szabo wrote: > On Fri, 5 Oct 2007, Simon Riggs wrote: > > > On Fri, 2007-10-05 at 10:59 -0700, Stephan Szabo wrote: > > > On Fri, 5 Oct 2007, Simon Riggs wrote: > > > > > > > On Fri, 2007-10-05 at 10:32 -0700, Stephan Szabo wrote: > > > > > On Fri, 5 Oct 2007, Simon Riggs wrote: > > > > > > > > > > > Because we already do exactly that here: > > > > > > > > > > > > select 1, (select col2 from c), 3; > > > > > > > > > > > > The inner select returns a ROW, yet we treat it as a single column > > > > > > value. > > > > > > > > > > The inner select does not return a row. It's not a <row subquery>, it's a > > > > > <scalar subquery>. > > > > > > > > Thanks Stephan, Tom already explained that. > > > > > > > > My comments above were in response to "Why would you think that?" > > > > > > Right, but I guess I couldn't see why you would consider that the same as > > > treating a rowtype as a scalar, because when I look at that my brain > > > converts that to a scalar subquery, so I guess I simply see the scalar. > > > If we supported select 1, (select 2,3), select 4 giving something like > > > (1,(2,3),4), I'd also have confusion over the case, but that should error. > > > > Well, my brain didn't... All I've said was that we should document it, > > to help those people that don't know they're SQL standard as good as the > > best people on this list. > > Where would you document this beyond 4.2 though? While I don't exactly > like the wording of 4.2.9, it seems like it's already trying to say that. Yeh, it does, but you're forgetting that my original complaint was that you couldn't use it in an ANY clause, which 4.2 does not exclude. Bearing in mind you can use a scalar subquery in lots of places, I thought it worth reporting. The ANY clause at 9.19.4 mentions a subquery, but doesn't say it can't be a scalar subquery; it doesn't restrict this to non-scalar subqueries. Searching in Arrays, 8.14.5 doesn't say it can't be a subquery either. I was aware of the potential for a scalar subquery, hence my surprise you couldn't use it there. Maybe others less familiar would not have tried the query formulation I did. Section 9.20.3 mentions ANY (array expression). The term "array expression" is not defined nor is there a link to where it is defined, nor is the term indexed. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
On Fri, 5 Oct 2007, Simon Riggs wrote: > On Fri, 2007-10-05 at 11:24 -0700, Stephan Szabo wrote: > > On Fri, 5 Oct 2007, Simon Riggs wrote: > > > > > On Fri, 2007-10-05 at 10:59 -0700, Stephan Szabo wrote: > > > > On Fri, 5 Oct 2007, Simon Riggs wrote: > > > > > > > > > On Fri, 2007-10-05 at 10:32 -0700, Stephan Szabo wrote: > > > > > > On Fri, 5 Oct 2007, Simon Riggs wrote: > > > > > > > > > > > > > Because we already do exactly that here: > > > > > > > > > > > > > > select 1, (select col2 from c), 3; > > > > > > > > > > > > > > The inner select returns a ROW, yet we treat it as a single column > > > > > > > value. > > > > > > > > > > > > The inner select does not return a row. It's not a <row subquery>, it's a > > > > > > <scalar subquery>. > > > > > > > > > > Thanks Stephan, Tom already explained that. > > > > > > > > > > My comments above were in response to "Why would you think that?" > > > > > > > > Right, but I guess I couldn't see why you would consider that the same as > > > > treating a rowtype as a scalar, because when I look at that my brain > > > > converts that to a scalar subquery, so I guess I simply see the scalar. > > > > If we supported select 1, (select 2,3), select 4 giving something like > > > > (1,(2,3),4), I'd also have confusion over the case, but that should error. > > > > > > Well, my brain didn't... All I've said was that we should document it, > > > to help those people that don't know they're SQL standard as good as the > > > best people on this list. > > > > Where would you document this beyond 4.2 though? While I don't exactly > > like the wording of 4.2.9, it seems like it's already trying to say that. > > Yeh, it does, but you're forgetting that my original complaint was that > you couldn't use it in an ANY clause, which 4.2 does not exclude. > Bearing in mind you can use a scalar subquery in lots of places, I > thought it worth reporting. Well, but I'd argue that we're now talking about separate issues. The first is how scalar subqueries act, as far as not being a rowtype. The second is related to the question of ANY and scalar subqueries specifically. The third is related to where you can use scalar subqueries. > The ANY clause at 9.19.4 mentions a subquery, but doesn't say it can't > be a scalar subquery; it doesn't restrict this to non-scalar subqueries. While it's true that it isn't a scalar subquery (although it's not a restriction on the kind of subquery, it's the definition of what "(select ...)" turn into when used there), I don't see how the text doesn't basically say that op ANY (subquery returning a single array) works the way it currently does. I think it'd be more applicable to mention in the array one that using a subquery as the right hand side turns it into the other form. I'm not convinced it's necessary, but also I'd think that one general mention would likely be better than separate ones in each of ANY and ALL. It might be reasonable to try to note where subqueries are scalar subqueries, but I think that'll be prone to being wrong or misinterpreted as well. > Searching in Arrays, 8.14.5 doesn't say it can't be a subquery either. True, although I don't know if it's right to mention there since that section appears to link to the other section saying that the other section describes the method. > Section 9.20.3 mentions ANY (array expression). The term "array > expression" is not defined nor is there a link to where it is defined, > nor is the term indexed. I'm not sure why we're using a separate term for that.
On Sat, 2007-10-06 at 10:15 -0700, Stephan Szabo wrote: > > Yeh, it does, but you're forgetting that my original complaint was that > > you couldn't use it in an ANY clause, which 4.2 does not exclude. > > Bearing in mind you can use a scalar subquery in lots of places, I > > thought it worth reporting. > > Well, but I'd argue that we're now talking about separate issues. It's simpler than that. I asked a question because the manual isn't specific on my original point. I'll do a doc patch to make sure nobody makes the same mistake I did and we record all the good points people have made. > > Section 9.20.3 mentions ANY (array expression). The term "array > > expression" is not defined nor is there a link to where it is defined, > > nor is the term indexed. > > I'm not sure why we're using a separate term for that. The term "array expression" is used in the manual, but not defined. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
On Sat, 6 Oct 2007, Simon Riggs wrote: > On Sat, 2007-10-06 at 10:15 -0700, Stephan Szabo wrote: > > > > Yeh, it does, but you're forgetting that my original complaint was that > > > you couldn't use it in an ANY clause, which 4.2 does not exclude. > > > Bearing in mind you can use a scalar subquery in lots of places, I > > > thought it worth reporting. > > > > Well, but I'd argue that we're now talking about separate issues. > > It's simpler than that. I asked a question because the manual isn't > specific on my original point. I'll do a doc patch to make sure nobody > makes the same mistake I did and we record all the good points people > have made. > > > > Section 9.20.3 mentions ANY (array expression). The term "array > > > expression" is not defined nor is there a link to where it is defined, > > > nor is the term indexed. > > > > I'm not sure why we're using a separate term for that. > > The term "array expression" is used in the manual, but not defined. Right. I meant, if those are the only uses, why did we use a specific term "array expression" rather than relying on saying that the expression given must have array type.