Re: BUG #9227: Error on SELECT ROW OVERLAPS ROW with single ROW argument

I wrote:
> Joshua Yanovski <pythonesque(at)gmail(dot)com> writes:
>> Great, thanks. Yeah, I was thinking about that too--I am not sure if
>> there are any other examples of a time where Postgres deliberately
>> duplicates an argument like that (maybe there could be a check for it
>> to be a constexpr or something? But that information isn't available
>> at this point in the analysis process).

> Yeah, BETWEEN is like that. I'd like to fix it sometime, but it's
> kind of problematic because of the risk of losing index optimizations
> (which only understand x >= y and x <= z, not a hypothetical combined
> node).

Actually, it suddenly strikes me that there's a pretty simple answer to
that. Have the parser generate a node representing BETWEEN, with three
arguments. In the planner, *if* the first argument is non-volatile,
replace the BETWEEN with "x >= y AND x <= z"; otherwise, leave it alone,
and execute it as-is. This transformation is semantically correct and
will still expose index-optimizable comparisons in all cases of interest
(since a volatile expression isn't indexable). Moreover we get rid of the
double evaluation risk for volatile first arguments, as well as the
incredible inefficiency of the BETWEEN SYMMETRIC cases.

There are some other issues still to be thought about, since the
parser is currently willing to cast "x" differently in the two comparisons
--- but frankly I think any case where that matters is probably erroneous
SQL code in the first place. (See the thread referenced in the comment in
the grammar for more info.)

regards, tom lane