From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-hackers@lists.postgresql.org
Subject: "SELECT ... FROM DUAL" is not quite as silly as it appears
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <15943.1521127664.1@sss.pgh.pa.us>
Date: Thu, 15 Mar 2018 11:27:44 -0400
Message-ID: <15944.1521127664@sss.pgh.pa.us>
Precedence: bulk

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.

I'm not planning to do anything about this soon (ie, not before v12),
but I thought I'd get the ideas down on electrons before they vanish.

			regards, tom lane