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

That sounds like it should work.  I was also wondering whether it
might be possible to do this more generally with some kind of "let"
internal node:

(let var = x in (var >= y and var <= z)

I don't know enough about the planner or the SQL standard to know
whether this would work, but it does seem a little nicer than
special-casing BETWEEN to me.  Is the issue there that the extra layer
of indirection would cause problems or create artificial optimization
boundaries?

On Tue, Feb 18, 2014 at 10:39 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I wrote:
>> Joshua Yanovski <pythonesque@gmail.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



--
Josh