Re: BETWEEN Help

"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> The exact bits I need the advice on really are bits like this:

>  if (expr_result >= lexpr_result &&
>                                 expr_result <= rexpr_result)

> I know this won't work, but how do I make it work?

You'll need to look up the appropriate comparison operators and call
them.

My original thoughts about this involved storing the OIDs of the
comparison operators in the BETWEEN node (probably in the form of
Oper sub-nodes, to ease using the existing eval machinery).
But that isn't the whole story; in some cases, it'll be necessary to
coerce the inputs into comparable datatypes.

In the existing implementation of BETWEEN, ie explicit conversion to
    (A >= B) AND (A <= C)
it is entirely possible for B and C to be different datatypes and
even possible for A to be coerced to two different datatypes to make
the comparisons.  This strikes me as a tad silly.  If we were to
restrict the allowable cases so that A must have the same coerced
datatype for both comparisons, then it'd be possible to put the
coercions (if any) into the input subnodes of BETWEEN, and then
you'd only need to worry about actually executing the comparison
Opers, not about performing on-the-fly datatype conversions.

We could make that restriction directly (just resolve the operators,
and then complain if the resolver chooses different types for A),
but it might make more sense to first coerce B and C to be the same
datatype, eg using the same rules that UNION/CASE do.  If we did that
then BETWEEN would only need to keep track of two comparison operators
not four (the asymmetric case requires four distinct comparison ops
if B and C are not the same datatype).

Comments anyone?

> BTW, I'm just making a BETWEEN node.  I see no need to create a NOT BETWEEN
> node, as there is already a NOT node that should just be able to take a
> BETWEEN node?

Should work; the spec defines

         5) "X NOT BETWEEN Y AND Z" is equivalent to "NOT ( X BETWEEN Y AND
            Z )".

so there's no functional reason not to transform the first into the
second.

BTW, would you go screaming off into the night if I pointed out that
SQL92 expects BETWEEN to support row-wise comparisons?  Really this
project should include upgrading our support of row-wise comparisons
to be spec-compliant.  Right now, gram.y transforms them into something
(see makeRowExpr) that looks plausible but does not actually implement
the spec-required semantics.  I suspect we need RowComparison nodes to
go along with Between nodes.

            regards, tom lane