Thread: BETWEEN Help
Hi all,
I'm working on making the BETWEEN expression its own node type. I've
handled most of it I think, but I'm having trouble with the actual
execQual.c. ie. I don't know how to do it!
Do I recursively call the functions for evaluating Ands, Ors and Ops??
Attached is my current code. It's sort of half real code and half pseudo.
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?
It's checking that the expression is between the two other expressions.
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?
I have made changes in the following files:
outFuncs.c <-- still figuring out the output format..
readFuncs.c <-- dependent on above I think
copyFuncs.c
equalFuncs.c
parsenodes.h
nodes.h
gram.y
execQual.c
parse_expr.c <-- don't know exactly what changes to make here
Chris
Attachment
"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
Tom Lane wrote: > "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. Is there special optimizer/index handling needed for BETWEEN that wasn't needed before because it was transformed into AND/OR comparisons? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Is there special optimizer/index handling needed for BETWEEN that wasn't
> needed before because it was transformed into AND/OR comparisons?
Yup, that's one place that will need to be taught about it.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Is there special optimizer/index handling needed for BETWEEN that wasn't > > needed before because it was transformed into AND/OR comparisons? > > Yup, that's one place that will need to be taught about it. That was actually a reason I was wondering if it was worth changing our current BETWEEN code. The optimizer already is complicated and trying to do BETWEEN rather than the more simpler statements didn't seem like a win. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> Yup, that's one place that will need to be taught about it.
> That was actually a reason I was wondering if it was worth changing our
> current BETWEEN code. The optimizer already is complicated and trying
> to do BETWEEN rather than the more simpler statements didn't seem like a
> win.
Actually, an explicit representation of BETWEEN will *help* the
optimizer; right now it has to try to recognize range restrictions
by matching up '>' and '<' clauses. That's a mess already, and I
had no intention of trying to extend that logic to recognize the
clause structures that BETWEEN SYMMETRIC would put out if it weren't
a primitive node type. But if it's a node then recognizing it is
a no-brainer. See clausesel.c.
regards, tom lane
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> Yup, that's one place that will need to be taught about it.
>
> > That was actually a reason I was wondering if it was worth changing our
> > current BETWEEN code. The optimizer already is complicated and trying
> > to do BETWEEN rather than the more simpler statements didn't seem like a
> > win.
>
> Actually, an explicit representation of BETWEEN will *help* the
> optimizer; right now it has to try to recognize range restrictions
> by matching up '>' and '<' clauses. That's a mess already, and I
> had no intention of trying to extend that logic to recognize the
> clause structures that BETWEEN SYMMETRIC would put out if it weren't
> a primitive node type. But if it's a node then recognizing it is
> a no-brainer. See clausesel.c.
Oh, good. Another issue is that "nocachefunc() BETWEEN 1 and 2" should
call the function only once, which my AND/OR hack didn't do. I can
imagine random() working strangely with BETWEEN.
I just tried:
test=> select 1 where random() between 0.49999 and 0.5000;
in current CVS and it clearly is printing 1 more often that it should. :-(
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
> 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? *sigh* This is getting a bit out of my league... I seem to have a habit of picking things to work on that turn out to really need a total rethink ;) > 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. I don't quite understand the exact issue, but I'll try to read up on it. However, expect delays in implementation. OK, how about I firstly submit a patch that adds support for SYMMETRIC and ASYMMETRIC using the old style hack. Actually - I think I've already submitted that patch. Then, once that's in, I/we can hack on the new style one at our leisure. Chris