Thread: UNION/INTERSECT in subselects

Hi, I'm using 7.0.2 and I have a problem with UNION in some subselects,
namely the following works:select * from a where a.f in (select 1)
but this doesn't:select * from a where a.f in (select 1 union select 2)

In the grammar we have : in_expr:  SubSelect | ...
but SubSelect doesn't allow UNIONs, only select_clause does.

Could in_expr be changed to use select_clause instead without adverse
ill effects ?

In fact SubSelect is used all over the place, so maybe it's better to
switch select_clause and SubSelect in the definitions for SelectStmt,
select_clause and SubSelect ?


Thanks,
Florent

(Please Cc: me in your answers)

Florent Guillaume <efge@mail.com> writes:
> In the grammar we have :
>   in_expr:  SubSelect | ...
> but SubSelect doesn't allow UNIONs, only select_clause does.

> Could in_expr be changed to use select_clause instead without adverse
> ill effects ?

Unfortunately the problems with union/intersect/except go a lot deeper
than the grammar.  Take a look at the rewriter and the planner, if
you have a strong stomach.  They're just not built to deal with these
constructs except at the top level of a query.  (The executor would
likely work just fine, if only the upstream modules would give it a
valid plan ...)

I'm hoping to see this stuff cleaned up during the much-talked-of
querytree redesign that we plan for the 7.2 cycle.  AFAICS there is
no way to fix it without some pretty serious hacking on the querytree
representation of union etc.
        regards, tom lane