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
Date: Thu, 15 Mar 2018 11:27:44 -0400
Message-ID: <15944.1521127664@sss.pgh.pa.us> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tgl@sss.pgh.pa.us, pgsql-hackers@lists.postgresql.org
Subject: Re: "SELECT ... FROM DUAL" is not quite as silly as it appears
In-Reply-To: <15944.1521127664@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox