Re: Hierarchical queries a la Oracle. Patch.

Evgen Potemkin <evgent(at)ns(dot)terminal(dot)ru> writes:
> Trick with Const/Var+varfake is to add to result a new column which
> behave like var, but not fetched from tables by subplan.
> For Var+varfake nodes everything is same as for usual Var nodes, so no need
> to check them specially everywhere , with exclusion of some particular
> place where it is checked. It's not a neglect.

I think it would be cleaner to invent a new node type, or perhaps you
couid use Param. Const nodes that are not really constant, in
particular, are a seriously BAD idea, as the constant-folder is likely
to fold expressions containing them.

>> I'm also wondering what you may have broken in altering the handling of
>> HAVING clauses in planner.c. What's going on there?

> I add this for posibility of qualification rows after connecting them.
> Like HAVING with GROUP case. I'm leave WHERE clause for subplan, because it
> can considerably descrease amount of rows which is needed to connect, and
> thus improve performance of query. But also there is need to qual rows after
> connecting, to check _level_ column for example. This is HAVING for.

The stuff I found on the web last night suggested that Oracle considers
the WHERE clause to be executed after connecting. That sounds pretty
brain-dead to me too, but if you are going to implement this feature
using Oracle syntax then you'd better use Oracle semantics too.

Personally I'd prefer to forget Oracle's syntax --- it looks to me like
it's at least as badly designed as their outer-join syntax, if not worse
--- and use SQL99's recursive-query syntax for this sort of thing.
Have you looked at that?

regards, tom lane