Re: subselects

> > > Note, that there are no OP_IN, OP_NOTIN in OpType-s for Expr. We need in
> > > IN, NOTIN in A_Expr (parser node), but both of them have to be transferred
> > > by parser into corresponding ANY and ALL. At the moment we can do:
> > >
> > > IN --> = ANY, NOT IN --> <> ALL
> > >
> > > but this will be "known bug": this breaks OO-nature of Postgres, because of
> > > operators can be overrided and '=' can mean  s o m e t h i n g (not equality).
> > > Example: box data type. For boxes, = means equality of _areas_ and =~
> > > means that boxes are the same ==> =~ ANY should be used for IN.
> >
> > That is interesting, to use =~ for ANY.

If I understand the discussion, I would think is is fine to make an assumption about
which operator is used to implement a subselect expression. If someone remaps an
operator to mean something different, then they will get a different result (or a
nonsensical one) from a subselect.

I'd be happy to remap existing operators to fit into a convention which would work
with subselects (especially if I got to help choose :).

> > Subselects are SQL standard, and are never going to be over-ridden by a
> > user.  Same with UNION.  They want UNION, they get UNION.  They want
> > Subselect, we are going to spin through the Query structure and give
> > them what they want.
>
> PostgreSQL is a robust, next-generation, Object-Relational DBMS (ORDBMS),
> derived from the Berkeley Postgres database management system. While
> PostgreSQL retains the powerful object-relational data model, rich data types and
>            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> easy extensibility of Postgres, it replaces the PostQuel query language with an
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> extended subset of SQL.
> ^^^^^^^^^^^^^^^^^^^^^^
>
> Should we say users that subselect will work for standard data types only ?
> I don't see why subquery can't be used with ~, ~*, @@, ... operators, do you ?
> Is there difference between handling = ANY and ~ ANY ? I don't see any.
> Currently we can't get IN working properly for boxes (and may be for others too)
> and I don't like to try to resolve these problems now, but hope that someday
> we'll be able to do this. At the moment - just convert IN into = ANY and
> NOT IN into <> ALL in parser.
>
> (BTW, do you know how DISTINCT is implemented ? It doesn't use = but
> use type_out funcs and uses strcmp()... DISTINCT is standard SQL thing...)

?? I didn't know that. Wouldn't we want it to eventually use "=" through a sorted
list? That would give more consistant behavior...

> > I have been reading some postings, and it seems to me that subselects
> > are the litmus test for many evaluators when deciding if a database
> > engine is full-featured.
> >
> > Sorry to be so straightforward, but I want to keep hashing this around
> > until we get a conclusion, so coding can start.
> >
> > My suggestions have been, I believe, trying to get subselects working
> > with the fullest functionality by adding the least amount of code, and
> > keeping the logic clean.
> >
> > Have you checked out the UNION code?  It is very small, but it works.  I
> > think it could make a good sample for subselects.
>
> There is big difference between subqueries and queries in UNION -
> there are not dependences between UNION queries.
>
> Ok, opened issues:
>
> 1. Is using upper query' vars in all subquery levels in standard ?

I'm not certain. Let me know if you do not get an answer from someone else and I will
research it.

> 2. Is (a, b, c) OP (subselect) in standard ?

Yes. In fact, it _is_ the standard, and "a OP (subselect)" is a special case where
the parens are allowed to be omitted from a one element list.

> 3. What types of expressions (Var, Const, ...) are allowed on the left
>    side of operator with subquery on the right ?

I think most expressions are allowed. The "constant OP (subselect)" case you were
asking about is just a simplified case since "(a, b, constant) OP (subselect)" where
a and b are column references should be allowed. Of course, our optimizer could
perhaps change this to "(a, b) OP (subselect where x = constant)", or for the first
example "EXISTS (subselect where x = constant)".

> 4. What types of operators should we support (=, >, ..., like, ~, ...) ?
>    (My vote for all boolean operators).

Sounds good. But I'll vote with Bruce (and I'll bet you already agree) that it is
important to get an initial implementation for v6.3 which covers a little, some, or
all of the usual SQL subselect constructs. If we have to revisit this for v6.4 then
we will have the benefit of feedback from others in practical applications which
always uncovers new things to consider.

> And - did we get consensus on presentation subqueries stuff in Query,
> Expr and Var ?
> I would like to have something done in parser near Jan 17 to get
> subqueries working by Feb 1. I vote for support of all standard
> things (1. - 3.) in parser right now - if there will be no time
> to implement something like (a, b, c) then optimizer will callelog(WARN) (oh,
> sorry, - elog(ERROR)).

Great. I'd like to help with the remaining parser issues; at the moment "row_expr"
does the right thing with expression comparisions but just parses then ignores
subselect expressions. Let me know what structures you want passed back and I'll put
them in, or if you prefer put in the first one and I'll go through and clean up and
add the rest.

                                                  - Tom