? GNUmakefile ? between.diff.txt ? config.log ? config.status ? contrib/spi/.deps ? src/Makefile.global ? src/backend/postgres ? src/backend/access/common/.deps ? src/backend/access/gist/.deps ? src/backend/access/hash/.deps ? src/backend/access/heap/.deps ? src/backend/access/index/.deps ? src/backend/access/nbtree/.deps ? src/backend/access/rtree/.deps ? src/backend/access/transam/.deps ? src/backend/bootstrap/.deps ? src/backend/catalog/.deps ? src/backend/catalog/postgres.bki ? src/backend/catalog/postgres.description ? src/backend/commands/.deps ? src/backend/commands/tablecmds.c.mystuff ? src/backend/executor/.deps ? src/backend/lib/.deps ? src/backend/libpq/.deps ? src/backend/main/.deps ? src/backend/nodes/.deps ? src/backend/optimizer/geqo/.deps ? src/backend/optimizer/path/.deps ? src/backend/optimizer/plan/.deps ? src/backend/optimizer/prep/.deps ? src/backend/optimizer/util/.deps ? src/backend/parser/.deps ? src/backend/port/.deps ? src/backend/postmaster/.deps ? src/backend/regex/.deps ? src/backend/rewrite/.deps ? src/backend/storage/buffer/.deps ? src/backend/storage/file/.deps ? src/backend/storage/freespace/.deps ? src/backend/storage/ipc/.deps ? src/backend/storage/large_object/.deps ? src/backend/storage/lmgr/.deps ? src/backend/storage/page/.deps ? src/backend/storage/smgr/.deps ? src/backend/tcop/.deps ? src/backend/utils/.deps ? src/backend/utils/adt/.deps ? src/backend/utils/cache/.deps ? src/backend/utils/error/.deps ? src/backend/utils/fmgr/.deps ? src/backend/utils/hash/.deps ? src/backend/utils/init/.deps ? src/backend/utils/mb/.deps ? src/backend/utils/misc/.deps ? src/backend/utils/mmgr/.deps ? src/backend/utils/sort/.deps ? src/backend/utils/time/.deps ? src/bin/initdb/initdb ? src/bin/initlocation/initlocation ? src/bin/ipcclean/ipcclean ? src/bin/pg_config/pg_config ? src/bin/pg_ctl/pg_ctl ? src/bin/pg_dump/.deps ? src/bin/pg_dump/pg_dump ? src/bin/pg_dump/pg_dumpall ? src/bin/pg_dump/pg_restore ? src/bin/pg_encoding/.deps ? src/bin/pg_encoding/pg_encoding ? src/bin/pg_id/.deps ? src/bin/pg_id/pg_id ? src/bin/psql/.deps ? src/bin/psql/psql ? src/bin/scripts/createlang ? src/include/pg_config.h ? src/include/stamp-h ? src/interfaces/ecpg/lib/.deps ? src/interfaces/ecpg/lib/libecpg.so.3 ? src/interfaces/ecpg/preproc/.deps ? src/interfaces/ecpg/preproc/ecpg ? src/interfaces/libpgeasy/.deps ? src/interfaces/libpgeasy/libpgeasy.so.2 ? src/interfaces/libpq/.deps ? src/interfaces/libpq/libpq.so.2 ? src/interfaces/libpq++/.deps ? src/interfaces/libpq++/libpq++.so.4 ? src/pl/plpgsql/src/.deps ? src/pl/plpgsql/src/libplpgsql.so.1 ? src/test/regress/.deps ? src/test/regress/log ? src/test/regress/pg_regress ? src/test/regress/regression.diffs ? src/test/regress/regression.out ? src/test/regress/results ? src/test/regress/tmp_check ? src/test/regress/expected/constraints.out ? src/test/regress/expected/copy.out ? src/test/regress/expected/create_function_1.out ? src/test/regress/expected/create_function_2.out ? src/test/regress/expected/misc.out ? src/test/regress/sql/constraints.sql ? src/test/regress/sql/copy.sql ? src/test/regress/sql/create_function_1.sql ? src/test/regress/sql/create_function_2.sql ? src/test/regress/sql/misc.sql Index: src/backend/executor/execQual.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/backend/executor/execQual.c,v retrieving revision 1.94 diff -c -r1.94 execQual.c *** src/backend/executor/execQual.c 2002/06/20 20:29:27 1.94 --- src/backend/executor/execQual.c 2002/06/27 10:27:30 *************** *** 60,65 **** --- 60,67 ---- static Datum ExecEvalOr(Expr *orExpr, ExprContext *econtext, bool *isNull); static Datum ExecEvalCase(CaseExpr *caseExpr, ExprContext *econtext, bool *isNull, ExprDoneCond *isDone); + static Datum ExecEvalBetweenExpr(BetweenExpr *btest, ExprContext *econtext, + bool *isNull, ExprDoneCond *isDone); static Datum ExecEvalNullTest(NullTest *ntest, ExprContext *econtext, bool *isNull, ExprDoneCond *isDone); static Datum ExecEvalBooleanTest(BooleanTest *btest, ExprContext *econtext, *************** *** 1110,1115 **** --- 1112,1182 ---- } /* ---------------------------------------------------------------- + * ExecEvalBetweenExpr + * + * Evaluate a BetweenExpr node. Result is + * a boolean. If any of the three expression + * parameters are NULL, result is NULL. + * ---------------------------------------------------------------- + */ + static Datum + ExecEvalBetweenExpr(BetweenExpr *btest, + ExprContext *econtext, + bool *isNull, + ExprDoneCond *isDone) + { + Datum expr_result; + Datum lexpr_result; + Datum rexpr_result; + + /* Evaluate subexpressons and test for NULL parameters */ + expr_result = ExecEvalExpr(btest->expr, econtext, isNull, isDone); + if (*isNull) { + *isNull = true; + return (Datum) 0; + } + lexpr_result = ExecEvalExpr(btest->lexpr, econtext, isNull, isDone); + if (*isNull) { + *isNull = true; + return (Datum) 0; + } + rexpr_result = ExecEvalExpr(btest->rexpr, econtext, isNull, isDone); + if (*isNull) { + *isNull = true; + return (Datum) 0; + } + + /* Make sure return value is what we think it is */ + *isNull = false; + + /* Now, depending on the symmetry, evaluate the + BETWEEN expression */ + + if (btest->symmetric) + { + /* @@ This is pseudocode - how do I copare the results? @@ */ + if ((expr_result >= lexpr_result && + expr_result <= rexpr_result) || + (expr_result >= rexpr_result && + expr_result <= lexpr_result)) + { + return BoolGetDatum(true); + } + else + return BoolGetDatum(false); + } + else { + if (expr_result >= lexpr_result && + expr_result <= rexpr_result) + { + return BoolGetDatum(true); + } + else + return BoolGetDatum(false); + } + } + + /* ---------------------------------------------------------------- * ExecEvalNullTest * * Evaluate a NullTest node. *************** *** 1397,1402 **** --- 1464,1475 ---- econtext, isNull, isDone); + break; + case T_BetweenExpr: + retDatum = ExecEvalBetweenExpr((BetweenExpr *) expression, + econtext, + isNull, + isDone); break; case T_NullTest: retDatum = ExecEvalNullTest((NullTest *) expression, Index: src/backend/nodes/copyfuncs.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v retrieving revision 1.191 diff -c -r1.191 copyfuncs.c *** src/backend/nodes/copyfuncs.c 2002/06/20 20:29:29 1.191 --- src/backend/nodes/copyfuncs.c 2002/06/27 10:27:31 *************** *** 1000,1005 **** --- 1000,1025 ---- } /* ---------------- + * _copyBetweenExpr + * ---------------- + */ + static BetweenExpr * + _copyBetweenExpr(BetweenExpr *from) + { + BetweenExpr *newnode = makeNode(BetweenExpr); + + /* + * copy remainder of node + */ + Node_Copy(from, newnode, expr); + newnode->symmetric = from->symmetric; + Node_Copy(from, newnode, lexpr); + Node_Copy(from, newnode, rexpr); + + return newnode; + } + + /* ---------------- * _copyCaseWhen * ---------------- */ *************** *** 3043,3048 **** --- 3063,3071 ---- break; case T_CaseExpr: retval = _copyCaseExpr(from); + break; + case T_BetweenExpr: + retval = _copyBetweenExpr(from); break; case T_CaseWhen: retval = _copyCaseWhen(from); Index: src/backend/nodes/equalfuncs.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v retrieving revision 1.138 diff -c -r1.138 equalfuncs.c *** src/backend/nodes/equalfuncs.c 2002/06/20 20:29:29 1.138 --- src/backend/nodes/equalfuncs.c 2002/06/27 10:27:31 *************** *** 1752,1757 **** --- 1752,1772 ---- } static bool + _equalBetweenExpr(BetweenExpr *a, BetweenExpr *b) + { + if (!equal(a->expr, b->expr)) + return false; + if (a->symmetric != b->symmetric) + return false; + if (!equal(a->lexpr, b->lexpr)) + return false; + if (!equal(a->rexpr, b->rexpr)) + return false; + + return true; + } + + static bool _equalCaseWhen(CaseWhen *a, CaseWhen *b) { if (!equal(a->expr, b->expr)) *************** *** 2198,2203 **** --- 2213,2221 ---- break; case T_CaseExpr: retval = _equalCaseExpr(a, b); + break; + case T_BetweenExpr: + retval = _equalBetweenExpr(a, b); break; case T_CaseWhen: retval = _equalCaseWhen(a, b); Index: src/backend/nodes/outfuncs.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/backend/nodes/outfuncs.c,v retrieving revision 1.160 diff -c -r1.160 outfuncs.c *** src/backend/nodes/outfuncs.c 2002/06/20 20:29:29 1.160 --- src/backend/nodes/outfuncs.c 2002/06/27 10:27:32 *************** *** 1466,1471 **** --- 1466,1494 ---- _outNode(str, node->defresult); } + /* + * BetweenExpr + */ + static void + _outBetweenExpr(StringInfo str, BetweenExpr *node) + { + appendStringInfo(str, " :expr "); + _outNode(str, node->expr); + + appendStringInfo(str, " BETWEEN "); + if (node->symmetric) + appendStringInfo(str, "SYMMETRIC "); + /* We don't write out ASYMMETRIC, as it's the default */ + + appendStringInfo(str, " :lexpr "); + _outNode(str, node->lexpr); + + appendStringInfo(str, " AND "); + + appendStringInfo(str, " :rexpr "); + _outNode(str, node->rexpr); + } + static void _outCaseWhen(StringInfo str, CaseWhen *node) { *************** *** 1759,1764 **** --- 1782,1790 ---- break; case T_CaseExpr: _outCaseExpr(str, obj); + break; + case T_BetweenExpr: + _outBetweenExpr(str, obj); break; case T_CaseWhen: _outCaseWhen(str, obj); Index: src/backend/parser/gram.y =================================================================== RCS file: /projects/cvsroot/pgsql/src/backend/parser/gram.y,v retrieving revision 2.334 diff -c -r2.334 gram.y *** src/backend/parser/gram.y 2002/06/22 02:04:45 2.334 --- src/backend/parser/gram.y 2002/06/27 10:27:35 *************** *** 234,240 **** %type extract_list, overlay_list, position_list %type substr_list, trim_list ! %type opt_interval %type overlay_placing, substr_from, substr_for %type opt_instead, opt_cursor --- 234,240 ---- %type extract_list, overlay_list, position_list %type substr_list, trim_list ! %type opt_interval, opt_symmetry %type overlay_placing, substr_from, substr_for %type opt_instead, opt_cursor *************** *** 321,327 **** /* ordinary key words in alphabetical order */ %token ABORT_TRANS, ABSOLUTE, ACCESS, ACTION, ADD, AFTER, AGGREGATE, ALL, ALTER, ANALYSE, ANALYZE, AND, ANY, AS, ASC, ! ASSERTION, ASSIGNMENT, AT, AUTHORIZATION, BACKWARD, BEFORE, BEGIN_TRANS, BETWEEN, BIGINT, BINARY, BIT, BOTH, BOOLEAN, BY, --- 321,327 ---- /* ordinary key words in alphabetical order */ %token ABORT_TRANS, ABSOLUTE, ACCESS, ACTION, ADD, AFTER, AGGREGATE, ALL, ALTER, ANALYSE, ANALYZE, AND, ANY, AS, ASC, ! ASSERTION, ASSIGNMENT, ASYMMETRIC, AT, AUTHORIZATION, BACKWARD, BEFORE, BEGIN_TRANS, BETWEEN, BIGINT, BINARY, BIT, BOTH, BOOLEAN, BY, *************** *** 380,386 **** SERIALIZABLE, SESSION, SESSION_USER, SET, SETOF, SHARE, SHOW, SIMILAR, SIMPLE, SMALLINT, SOME, STABLE, START, STATEMENT, STATISTICS, STDIN, STDOUT, STORAGE, STRICT, SUBSTRING, ! SYSID, TABLE, TEMP, TEMPLATE, TEMPORARY, THEN, TIME, TIMESTAMP, TO, TOAST, TRAILING, TRANSACTION, TRIGGER, TRIM, TRUE_P, --- 380,386 ---- SERIALIZABLE, SESSION, SESSION_USER, SET, SETOF, SHARE, SHOW, SIMILAR, SIMPLE, SMALLINT, SOME, STABLE, START, STATEMENT, STATISTICS, STDIN, STDOUT, STORAGE, STRICT, SUBSTRING, ! SYMMETRIC, SYSID, TABLE, TEMP, TEMPLATE, TEMPORARY, THEN, TIME, TIMESTAMP, TO, TOAST, TRAILING, TRANSACTION, TRIGGER, TRIM, TRUE_P, *************** *** 5433,5449 **** b->booltesttype = IS_NOT_UNKNOWN; $$ = (Node *)b; } ! | a_expr BETWEEN b_expr AND b_expr %prec BETWEEN { ! $$ = (Node *) makeA_Expr(AND, NIL, ! (Node *) makeSimpleA_Expr(OP, ">=", $1, $3), ! (Node *) makeSimpleA_Expr(OP, "<=", $1, $5)); ! } ! | a_expr NOT BETWEEN b_expr AND b_expr %prec BETWEEN ! { ! $$ = (Node *) makeA_Expr(OR, NIL, ! (Node *) makeSimpleA_Expr(OP, "<", $1, $4), ! (Node *) makeSimpleA_Expr(OP, ">", $1, $6)); } | a_expr IN_P in_expr { --- 5433,5446 ---- b->booltesttype = IS_NOT_UNKNOWN; $$ = (Node *)b; } ! | a_expr BETWEEN opt_symmetry b_expr AND b_expr %prec BETWEEN { ! BetweenExpr *n = makeNode(BetweenExpr); ! n->expr = $1; ! n->symmetric = $3; ! n->lexpr = $4; ! n->rexpr = $6; ! $$ = (Node *)n; } | a_expr IN_P in_expr { *************** *** 5519,5524 **** --- 5516,5526 ---- { $$ = $1; } ; + opt_symmetry: SYMMETRIC { $$ = TRUE; } + | ASYMMETRIC { $$ = FALSE; } + | /* EMPTY */ { $$ = FALSE; /* default */ } + ; + /* * Restricted expressions * *************** *** 6844,6849 **** --- 6846,6852 ---- | ANY | AS | ASC + | ASYMMETRIC | BOTH | CASE | CAST *************** *** 6868,6882 **** | FOR | FOREIGN | FROM ! | GRANT ! | GROUP_P ! | HAVING ! | INITIALLY ! | INTERSECT ! | INTO ! | LEADING ! | LIMIT ! | LOCALTIME | LOCALTIMESTAMP | NEW | NOT --- 6871,6885 ---- | FOR | FOREIGN | FROM ! | GRANT ! | GROUP_P ! | HAVING ! | INITIALLY ! | INTERSECT ! | INTO ! | LEADING ! | LIMIT ! | LOCALTIME | LOCALTIMESTAMP | NEW | NOT *************** *** 6894,6899 **** --- 6897,6903 ---- | SELECT | SESSION_USER | SOME + | SYMMETRIC | TABLE | THEN | TO Index: src/backend/parser/keywords.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/backend/parser/keywords.c,v retrieving revision 1.117 diff -c -r1.117 keywords.c *** src/backend/parser/keywords.c 2002/06/22 02:04:45 1.117 --- src/backend/parser/keywords.c 2002/06/27 10:27:35 *************** *** 45,50 **** --- 45,51 ---- {"asc", ASC}, {"assertion", ASSERTION}, {"assignment", ASSIGNMENT}, + {"asymmetric", ASYMMETRIC}, {"at", AT}, {"authorization", AUTHORIZATION}, {"backward", BACKWARD}, *************** *** 271,276 **** --- 272,278 ---- {"storage", STORAGE}, {"strict", STRICT}, {"substring", SUBSTRING}, + {"symmetric", SYMMETRIC}, {"sysid", SYSID}, {"table", TABLE}, {"temp", TEMP}, Index: src/include/nodes/nodes.h =================================================================== RCS file: /projects/cvsroot/pgsql/src/include/nodes/nodes.h,v retrieving revision 1.109 diff -c -r1.109 nodes.h *** src/include/nodes/nodes.h 2002/06/20 20:29:51 1.109 --- src/include/nodes/nodes.h 2002/06/27 10:27:36 *************** *** 225,230 **** --- 225,231 ---- T_GroupClause, T_NullTest, T_BooleanTest, + T_BetweenExpr, T_CaseExpr, T_CaseWhen, T_FkConstraint, Index: src/include/nodes/parsenodes.h =================================================================== RCS file: /projects/cvsroot/pgsql/src/include/nodes/parsenodes.h,v retrieving revision 1.182 diff -c -r1.182 parsenodes.h *** src/include/nodes/parsenodes.h 2002/06/20 20:29:51 1.182 --- src/include/nodes/parsenodes.h 2002/06/27 10:27:37 *************** *** 174,179 **** --- 174,192 ---- } A_Const; /* + * BetweenExpr - an SQL99 BETWEEN expression + */ + + typedef struct BetweenExpr + { + NodeTag type; + Node *expr; /* Expression to check */ + int symmetric; /* True if SYMMETRIC, false if ASYMMETRIC */ + Node *lexpr; /* First bound */ + Node *rexpr; /* Second bound */ + } BetweenExpr; + + /* * TypeCast - a CAST expression * * NOTE: for mostly historical reasons, A_Const parsenodes contain Index: src/test/regress/expected/select.out =================================================================== RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/select.out,v retrieving revision 1.10 diff -c -r1.10 select.out *** src/test/regress/expected/select.out 2001/07/16 05:07:00 1.10 --- src/test/regress/expected/select.out 2002/06/27 10:27:38 *************** *** 430,432 **** --- 430,579 ---- mary | 8 (58 rows) + -- + -- Test between syntax + -- + SELECT 2 BETWEEN 1 AND 3; + ?column? + ---------- + t + (1 row) + + SELECT 2 BETWEEN 3 AND 1; + ?column? + ---------- + f + (1 row) + + SELECT 2 BETWEEN ASYMMETRIC 1 AND 3; + ?column? + ---------- + t + (1 row) + + SELECT 2 BETWEEN ASYMMETRIC 3 AND 1; + ?column? + ---------- + f + (1 row) + + SELECT 2 BETWEEN SYMMETRIC 1 AND 3; + ?column? + ---------- + t + (1 row) + + SELECT 2 BETWEEN SYMMETRIC 3 AND 1; + ?column? + ---------- + t + (1 row) + + SELECT 2 NOT BETWEEN 1 AND 3; + ?column? + ---------- + f + (1 row) + + SELECT 2 NOT BETWEEN 3 AND 1; + ?column? + ---------- + t + (1 row) + + SELECT 2 NOT BETWEEN ASYMMETRIC 1 AND 3; + ?column? + ---------- + f + (1 row) + + SELECT 2 NOT BETWEEN ASYMMETRIC 3 AND 1; + ?column? + ---------- + t + (1 row) + + SELECT 2 NOT BETWEEN SYMMETRIC 1 AND 3; + ?column? + ---------- + f + (1 row) + + SELECT 2 NOT BETWEEN SYMMETRIC 3 AND 1; + ?column? + ---------- + f + (1 row) + + SELECT -4 BETWEEN -1 AND -3; + ?column? + ---------- + f + (1 row) + + SELECT -4 BETWEEN -3 AND -1; + ?column? + ---------- + f + (1 row) + + SELECT -4 BETWEEN ASYMMETRIC -1 AND -3; + ?column? + ---------- + f + (1 row) + + SELECT -4 BETWEEN ASYMMETRIC -3 AND -1; + ?column? + ---------- + f + (1 row) + + SELECT -4 BETWEEN SYMMETRIC -1 AND -3; + ?column? + ---------- + f + (1 row) + + SELECT -4 BETWEEN SYMMETRIC -3 AND -1; + ?column? + ---------- + f + (1 row) + + SELECT -4 NOT BETWEEN -1 AND -3; + ?column? + ---------- + t + (1 row) + + SELECT -4 NOT BETWEEN -3 AND -1; + ?column? + ---------- + t + (1 row) + + SELECT -4 NOT BETWEEN ASYMMETRIC -1 AND -3; + ?column? + ---------- + t + (1 row) + + SELECT -4 NOT BETWEEN ASYMMETRIC -3 AND -1; + ?column? + ---------- + t + (1 row) + + SELECT -4 NOT BETWEEN SYMMETRIC -1 AND -3; + ?column? + ---------- + t + (1 row) + + SELECT -4 NOT BETWEEN SYMMETRIC -3 AND -1; + ?column? + ---------- + t + (1 row) + Index: src/test/regress/sql/select.sql =================================================================== RCS file: /projects/cvsroot/pgsql/src/test/regress/sql/select.sql,v retrieving revision 1.6 diff -c -r1.6 select.sql *** src/test/regress/sql/select.sql 2001/07/16 05:07:00 1.6 --- src/test/regress/sql/select.sql 2002/06/27 10:27:39 *************** *** 103,105 **** --- 103,133 ---- -- SELECT p.name, p.age FROM person* p ORDER BY age using >, name; + -- + -- Test between syntax + -- + SELECT 2 BETWEEN 1 AND 3; + SELECT 2 BETWEEN 3 AND 1; + SELECT 2 BETWEEN ASYMMETRIC 1 AND 3; + SELECT 2 BETWEEN ASYMMETRIC 3 AND 1; + SELECT 2 BETWEEN SYMMETRIC 1 AND 3; + SELECT 2 BETWEEN SYMMETRIC 3 AND 1; + SELECT 2 NOT BETWEEN 1 AND 3; + SELECT 2 NOT BETWEEN 3 AND 1; + SELECT 2 NOT BETWEEN ASYMMETRIC 1 AND 3; + SELECT 2 NOT BETWEEN ASYMMETRIC 3 AND 1; + SELECT 2 NOT BETWEEN SYMMETRIC 1 AND 3; + SELECT 2 NOT BETWEEN SYMMETRIC 3 AND 1; + SELECT -4 BETWEEN -1 AND -3; + SELECT -4 BETWEEN -3 AND -1; + SELECT -4 BETWEEN ASYMMETRIC -1 AND -3; + SELECT -4 BETWEEN ASYMMETRIC -3 AND -1; + SELECT -4 BETWEEN SYMMETRIC -1 AND -3; + SELECT -4 BETWEEN SYMMETRIC -3 AND -1; + SELECT -4 NOT BETWEEN -1 AND -3; + SELECT -4 NOT BETWEEN -3 AND -1; + SELECT -4 NOT BETWEEN ASYMMETRIC -1 AND -3; + SELECT -4 NOT BETWEEN ASYMMETRIC -3 AND -1; + SELECT -4 NOT BETWEEN SYMMETRIC -1 AND -3; + SELECT -4 NOT BETWEEN SYMMETRIC -3 AND -1; +