BETWEEN patch
A patch to gram.y in src/backend/parser
Provides for the SQL99 expected behavior of
select * from foo where fo_num between 1 and 5
yields the same result as
select * from foo where fo_num between 5 and 1
Granted this is brute force and not very elegant, however it does provide
the correct behavior. Optimally it would be nice to do a comparison on the
values after between and then sort the two limiters and do a single
rewrite leaving only one pass or scan.
In other words in pseudo SQL:
select * from foo where fo_num between a and b
becomes
select * from foo where ((fo_num >= min_value(a, b)) and (fo_num <=
max_value(a,b))
This would yield only two comparisons or resolutions and then a single
sequential or index scan to find the correct tuples.
This was done against beta1...
Attachments:
between.patchapplication/octet-stream; name=between.patchDownload
--- src/backend/parser/gram.y Sun Dec 3 08:50:54 2000
+++ /usr/src/postgresql-7.1beta1/src/backend/parser/gram.y Tue Jan 23 12:49:45 2001
@@ -4462,15 +4462,23 @@
}
| a_expr BETWEEN b_expr AND b_expr
{
- $$ = makeA_Expr(AND, NULL,
- makeA_Expr(OP, ">=", $1, $3),
- makeA_Expr(OP, "<=", $1, $5));
+ $$ = makeA_Expr(OR, NULL,
+ (makeA_Expr(AND, NULL,
+ makeA_Expr(OP, ">=", $1, $3),
+ makeA_Expr(OP, "<=", $1, $5))),
+ (makeA_Expr(AND, NULL,
+ makeA_Expr(OP, ">=", $1, $5),
+ makeA_Expr(OP, "<=", $1, $3))));
}
| a_expr NOT BETWEEN b_expr AND b_expr
{
- $$ = makeA_Expr(OR, NULL,
- makeA_Expr(OP, "<", $1, $4),
- makeA_Expr(OP, ">", $1, $6));
+ $$ = makeA_Expr(AND, NULL,
+ (makeA_Expr(OR, NULL,
+ makeA_Expr(OP, "<", $1, $4),
+ makeA_Expr(OP, ">", $1, $6))),
+ (makeA_Expr(OR, NULL,
+ makeA_Expr(OP, "<", $1, $6),
+ makeA_Expr(OP, ">", $1, $4))));
}
| a_expr IN '(' in_expr ')'
{
Thomas Swan <tswan@ics.olemiss.edu> writes:
A patch to gram.y in src/backend/parser
Provides for the SQL99 expected behavior of
select * from foo where fo_num between 1 and 5
yields the same result as
select * from foo where fo_num between 5 and 1
This is NOT correct under either SQL92 or SQL99. Read the spec again.
regards, tom lane
At 1/24/2001 10:19 AM, Tom Lane wrote:
Thomas Swan <tswan@ics.olemiss.edu> writes:
A patch to gram.y in src/backend/parser
Provides for the SQL99 expected behavior of
select * from foo where fo_num between 1 and 5
yields the same result as
select * from foo where fo_num between 5 and 1This is NOT correct under either SQL92 or SQL99. Read the spec again.
regards, tom lane
After sending it... I realized that it was not correct either. So, I'm
back to figuring how to do it... so, um, ignore the previous patch...
Thanks..
Thomas Swan <tswan@ics.olemiss.edu> writes:
select * from foo where fo_num between 1 and 5
yields the same result as
select * from foo where fo_num between 5 and 1This is NOT correct under either SQL92 or SQL99. Read the spec again.
After sending it... I realized that it was not correct either. So, I'm
back to figuring how to do it... so, um, ignore the previous patch...
Someone (Easter, IIRC) already submitted a patch to support the SQL99
SYMMETRIC BETWEEN option. I think we're sitting on it till 7.2, on the
grounds of "no new features in beta". Check the list archives for the
last few weeks if you need it now.
regards, tom lane