Re: "SELECT ... FROM DUAL" is not quite as silly as it appears

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: "SELECT ... FROM DUAL" is not quite as silly as it appears
Date: 2018-03-15 15:38:39
Message-ID: CA+TgmobbsR0Uyi3NdV9etHr+4s5KSsPOurhEYaLYo4Cy4RreRw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Thu, Mar 15, 2018 at 11:27 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> We've long made fun of Oracle(TM) for the fact that if you just want
> to evaluate some expressions, you have to write "select ... from dual"
> rather than just "select ...". But I've realized recently that there's
> a bit of method in that madness after all. Specifically, having to
> cope with FromExprs that contain no base relation is fairly problematic
> in the planner. prepjointree.c is basically unable to cope with
> flattening a subquery that looks that way, although we've inserted a
> lot of overly-baroque logic to handle some subsets of the case (cf
> is_simple_subquery(), around line 1500). If memory serves, there are
> other places that are complicated by the case.
>
> Suppose that, either in the rewriter or early in the planner, we were
> to replace such cases with nonempty FromExprs, by adding a dummy RTE
> representing a table with no columns and one row. This would in turn
> give rise to an ordinary Path that converts to a Result plan, so that
> the case is handled without any special contortions later. Then there
> is no case where we don't have a nonempty relids set identifying a
> subquery, so that all that special-case hackery in prepjointree.c
> goes away, and we can simplify whatever else is having a hard time
> with it.

Good idea.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2018-03-15 15:43:06 Re: [HACKERS] Removing [Merge]Append nodes which contain a single subpath
Previous Message Tom Lane 2018-03-15 15:27:44 "SELECT ... FROM DUAL" is not quite as silly as it appears