Re: Polymorphic arguments and composite types

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.