From 443175822942ef1f15cd047cda58990a089ef180 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 9 Jan 2007 02:14:16 +0000 Subject: [PATCH] Support ORDER BY ... NULLS FIRST/LAST, and add ASC/DESC/NULLS FIRST/NULLS LAST per-column options for btree indexes. The planner's support for this is still pretty rudimentary; it does not yet know how to plan mergejoins with nondefault ordering options. The documentation is pretty rudimentary, too. I'll work on improving that stuff later. Note incompatible change from prior behavior: ORDER BY ... USING will now be rejected if the operator is not a less-than or greater-than member of some btree opclass. This prevents less-than-sane behavior if an operator that doesn't actually define a proper sort ordering is selected. --- doc/src/sgml/catalogs.sgml | 33 ++- doc/src/sgml/queries.sgml | 15 +- doc/src/sgml/ref/create_index.sgml | 64 ++++- doc/src/sgml/ref/select.sgml | 23 +- doc/src/sgml/ref/select_into.sgml | 4 +- doc/src/sgml/sql.sgml | 4 +- doc/src/sgml/xindex.sgml | 20 +- src/backend/access/nbtree/README | 7 +- src/backend/access/nbtree/nbtcompare.c | 8 +- src/backend/access/nbtree/nbtsearch.c | 68 ++++-- src/backend/access/nbtree/nbtsort.c | 54 +++-- src/backend/access/nbtree/nbtutils.c | 168 ++++++++++--- src/backend/bootstrap/bootparse.y | 4 +- src/backend/catalog/index.c | 19 +- src/backend/catalog/toasting.c | 8 +- src/backend/commands/analyze.c | 14 +- src/backend/commands/indexcmds.c | 66 ++++- src/backend/executor/nodeAgg.c | 4 +- src/backend/executor/nodeSort.c | 5 +- src/backend/nodes/copyfuncs.c | 11 +- src/backend/nodes/equalfuncs.c | 9 +- src/backend/nodes/outfuncs.c | 11 +- src/backend/nodes/readfuncs.c | 4 +- src/backend/optimizer/path/allpaths.c | 4 +- src/backend/optimizer/path/indxpath.c | 4 +- src/backend/optimizer/path/pathkeys.c | 58 +++-- src/backend/optimizer/plan/createplan.c | 59 +++-- src/backend/optimizer/plan/planagg.c | 35 ++- src/backend/optimizer/util/clauses.c | 6 +- src/backend/optimizer/util/plancat.c | 56 +++-- src/backend/parser/analyze.c | 4 +- src/backend/parser/gram.y | 54 +++-- src/backend/parser/keywords.c | 3 +- src/backend/parser/parse_clause.c | 148 ++++++++---- src/backend/parser/parser.c | 31 ++- src/backend/utils/adt/ruleutils.c | 51 +++- src/backend/utils/adt/selfuncs.c | 21 +- src/backend/utils/cache/lsyscache.c | 79 +++++- src/backend/utils/cache/relcache.c | 39 ++- src/backend/utils/sort/tuplesort.c | 267 ++++++--------------- src/include/access/nbtree.h | 14 +- src/include/catalog/catversion.h | 4 +- src/include/catalog/index.h | 3 +- src/include/catalog/pg_am.h | 62 ++--- src/include/catalog/pg_attribute.h | 7 +- src/include/catalog/pg_index.h | 18 +- src/include/nodes/parsenodes.h | 43 +++- src/include/nodes/plannodes.h | 3 +- src/include/nodes/relation.h | 23 +- src/include/parser/parse_clause.h | 8 +- src/include/utils/lsyscache.h | 3 +- src/include/utils/rel.h | 3 +- src/include/utils/tuplesort.h | 31 +-- src/test/regress/expected/circle.out | 2 +- src/test/regress/expected/create_index.out | 16 +- src/test/regress/expected/geometry.out | 2 +- src/test/regress/expected/geometry_1.out | 2 +- src/test/regress/expected/geometry_2.out | 2 +- src/test/regress/expected/point.out | 4 +- src/test/regress/expected/select.out | 216 +++++++++++++++++ src/test/regress/sql/circle.sql | 2 +- src/test/regress/sql/create_index.sql | 12 +- src/test/regress/sql/geometry.sql | 2 +- src/test/regress/sql/point.sql | 4 +- src/test/regress/sql/select.sql | 39 +++ 65 files changed, 1475 insertions(+), 592 deletions(-) diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 2d42280f50..8a22a7b812 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1,4 +1,4 @@ - + @@ -369,7 +369,17 @@ int2 Zero if the index offers no sort order, otherwise the strategy - number of the strategy operator that describes the sort order + number of the strategy operator that describes the default + (ASC) sort order + + + + amdescorder + int2 + + Zero if the index offers no sort order, otherwise the strategy + number of the strategy operator that describes the DESC + sort order @@ -2453,8 +2463,8 @@ indisprimary bool - If true, this index represents the primary key of the table. - (indisunique should always be true when this is true.) + If true, this index represents the primary key of the table + (indisunique should always be true when this is true) @@ -2487,7 +2497,7 @@ of 1 3 would mean that the first and the third table columns make up the index key. A zero in this array indicates that the corresponding index attribute is an expression over the table columns, - rather than a simple column reference. + rather than a simple column reference @@ -2496,12 +2506,23 @@ oidvector pg_opclass.oid - For each column in the index key this contains the OID of + For each column in the index key, this contains the OID of the operator class to use. See pg_opclass for details + + indoption + int2vector + + + This is an array of indnatts values that + store per-column flag bits. The meaning of the bits is defined by + the index's access method + + + indexprs text diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml index 6fc003bc83..5c48ae1d62 100644 --- a/doc/src/sgml/queries.sgml +++ b/doc/src/sgml/queries.sgml @@ -1,4 +1,4 @@ - + Queries @@ -1224,7 +1224,8 @@ SELECT DISTINCT ON (expression , SELECT select_list FROM table_expression - ORDER BY sort_expression1 ASC | DESC , sort_expression2 ASC | DESC ... + ORDER BY sort_expression1 ASC | DESC NULLS { FIRST | LAST } + , sort_expression2 ASC | DESC NULLS { FIRST | LAST } ... The sort expression(s) can be any expression that would be valid in the query's select list. An example is @@ -1253,6 +1254,14 @@ SELECT a, b FROM table1 ORDER BY a + b, c; + + The NULLS FIRST and NULLS LAST options can be + used to determine whether nulls appear before or after non-null values + in the sort ordering. By default, null values sort as if larger than any + non-null value; that is, NULLS FIRST is the default for + DESC order, and NULLS LAST otherwise. + + For backwards compatibility with the SQL92 version of the standard, a sort_expression can instead be the name or number @@ -1301,7 +1310,7 @@ SELECT a + b AS sum, c FROM table1 ORDER BY sum + c; -- wrong SELECT select_list FROM table_expression - ORDER BY sort_expression1 ASC | DESC , sort_expression2 ASC | DESC ... + ORDER BY ... LIMIT { number | ALL } OFFSET number diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml index b0d984335f..d64901d2e9 100644 --- a/doc/src/sgml/ref/create_index.sgml +++ b/doc/src/sgml/ref/create_index.sgml @@ -1,5 +1,5 @@ @@ -21,7 +21,7 @@ PostgreSQL documentation CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name ON table [ USING method ] - ( { column | ( expression ) } [ opclass ] [, ...] ) + ( { column | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ) [ WITH ( storage_parameter = value [, ... ] ) ] [ TABLESPACE tablespace ] [ WHERE predicate ] @@ -187,6 +187,44 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name + + ASC + + + Specifies ascending sort order (which is the default). + + + + + + DESC + + + Specifies descending sort order. + + + + + + NULLS FIRST + + + Specifies that nulls sort before non-nulls. This is the default + when DESC is specified. + + + + + + NULLS LAST + + + Specifies that nulls sort after non-nulls. This is the default + when DESC is not specified. + + + + storage_parameter @@ -363,6 +401,21 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name. + + For index methods that support ordered scans (currently, only B-tree), + the optional clauses ASC, DESC, NULLS + FIRST, and/or NULLS LAST can be specified to reverse + the normal sort direction of the index. Since an ordered index can be + scanned either forward or backward, it is not normally useful to create a + single-column DESC index — that sort ordering is already + available with a regular index. The value of these options is that + multicolumn indexes can be created that match the sort ordering requested + by a mixed-ordering query, such as SELECT ... ORDER BY x ASC, y + DESC. The NULLS options are useful if you need to support + nulls sort low behavior, rather than the default nulls + sort high, in queries that depend on indexes to avoid sorting steps. + + Use to remove an index. @@ -403,6 +456,13 @@ CREATE INDEX lower_title_idx ON films ((lower(title))); + + To create an index with non-default sort ordering of nulls: + +CREATE INDEX title_idx_nulls_low ON films (title NULLS FIRST); + + + To create an index with non-default fill factor: diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index dd5acde6b5..0af1fbb07a 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -1,5 +1,5 @@ @@ -27,7 +27,7 @@ SELECT [ ALL | DISTINCT [ ON ( expressionexpression [, ...] ] [ HAVING condition [, ...] ] [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ] - [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ] + [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ] [ LIMIT { count | ALL } ] [ OFFSET start ] [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ] @@ -642,7 +642,7 @@ HAVING condition The optional ORDER BY clause has this general form: -ORDER BY expression [ ASC | DESC | USING operator ] [, ...] +ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] expression can be the name or ordinal number of an output column @@ -652,8 +652,8 @@ ORDER BY expression [ ASC | DESC | The ORDER BY clause causes the result rows to - be sorted according to the specified expressions. If two rows are - equal according to the leftmost expression, the are compared + be sorted according to the specified expression(s). If two rows are + equal according to the leftmost expression, they are compared according to the next expression and so on. If they are equal according to all specified expressions, they are returned in an implementation-dependent order. @@ -697,6 +697,8 @@ SELECT name FROM distributors ORDER BY code; ORDER BY clause. If not specified, ASC is assumed by default. Alternatively, a specific ordering operator name may be specified in the USING clause. + An ordering operator must be a less-than or greater-than + member of some btree operator family. ASC is usually equivalent to USING < and DESC is usually equivalent to USING >. (But the creator of a user-defined data type can define exactly what the @@ -705,9 +707,14 @@ SELECT name FROM distributors ORDER BY code; - The null value sorts higher than any other value. In other words, - with ascending sort order, null values sort at the end, and with - descending sort order, null values sort at the beginning. + If NULLS LAST is specified, null values sort after all + non-null values; if NULLS FIRST is specified, null values + sort before all non-null values. If neither is specified, the default + behavior is NULLS LAST when ASC is specified + or implied, and NULLS FIRST when DESC is specified + (thus, the default is to act as though nulls are larger than non-nulls). + When USING is specified, the default nulls ordering depends + on whether the operator is a less-than or greater-than operator. diff --git a/doc/src/sgml/ref/select_into.sgml b/doc/src/sgml/ref/select_into.sgml index 50e964f9b1..8780771201 100644 --- a/doc/src/sgml/ref/select_into.sgml +++ b/doc/src/sgml/ref/select_into.sgml @@ -1,5 +1,5 @@ @@ -28,7 +28,7 @@ SELECT [ ALL | DISTINCT [ ON ( expressionexpression [, ...] ] [ HAVING condition [, ...] ] [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ] - [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ] + [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ] [ LIMIT { count | ALL } ] [ OFFSET start ] [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ] diff --git a/doc/src/sgml/sql.sgml b/doc/src/sgml/sql.sgml index ebed42f31d..3e3c32b89f 100644 --- a/doc/src/sgml/sql.sgml +++ b/doc/src/sgml/sql.sgml @@ -1,4 +1,4 @@ - + SQL @@ -860,7 +860,7 @@ SELECT [ ALL | DISTINCT [ ON ( expressionexpression [, ...] ] [ HAVING condition [, ...] ] [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ] - [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ] + [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ] [ LIMIT { count | ALL } ] [ OFFSET start ] [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ] diff --git a/doc/src/sgml/xindex.sgml b/doc/src/sgml/xindex.sgml index a66dd3c4ee..a95c8e7ac0 100644 --- a/doc/src/sgml/xindex.sgml +++ b/doc/src/sgml/xindex.sgml @@ -1,4 +1,4 @@ - + Interfacing Extensions To Indexes @@ -289,13 +289,17 @@ - By the way, the amorderstrategy column - in pg_am tells whether - the index method supports ordered scans. Zero means it doesn't; if it - does, amorderstrategy is the strategy - number that corresponds to the ordering operator. For example, B-tree - has amorderstrategy = 1, which is its - less than strategy number. + By the way, the amorderstrategy and + amdescorder columns in pg_am tell + whether the index method supports ordered scans. Zeroes mean it doesn't; + if it does, amorderstrategy is the strategy + number that corresponds to the default ordering operator, and + amdescorder is the strategy number for the + ordering operator of an index column that has the DESC option. + For example, B-tree has amorderstrategy = 1, + which is its less than strategy number, and + amdescorder = 5, which is its + greater than strategy number. diff --git a/src/backend/access/nbtree/README b/src/backend/access/nbtree/README index 53fda66572..34fb90e436 100644 --- a/src/backend/access/nbtree/README +++ b/src/backend/access/nbtree/README @@ -1,4 +1,4 @@ -$PostgreSQL: pgsql/src/backend/access/nbtree/README,v 1.15 2006/12/28 23:16:39 tgl Exp $ +$PostgreSQL: pgsql/src/backend/access/nbtree/README,v 1.16 2007/01/09 02:14:10 tgl Exp $ This directory contains a correct implementation of Lehman and Yao's high-concurrency B-tree management algorithm (P. Lehman and S. Yao, @@ -483,5 +483,6 @@ Notes to operator class implementors With this implementation, we require each supported combination of datatypes to supply us with a comparison procedure via pg_amproc. This procedure must take two nonnull values A and B and return an int32 < 0, -0, or > 0 if A < B, A = B, or A > B, respectively. See nbtcompare.c for -examples. +0, or > 0 if A < B, A = B, or A > B, respectively. The procedure must +not return INT_MIN for "A < B", since the value may be negated before +being tested for sign. See nbtcompare.c for examples. diff --git a/src/backend/access/nbtree/nbtcompare.c b/src/backend/access/nbtree/nbtcompare.c index 27caa8495c..97489c6036 100644 --- a/src/backend/access/nbtree/nbtcompare.c +++ b/src/backend/access/nbtree/nbtcompare.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/access/nbtree/nbtcompare.c,v 1.53 2007/01/05 22:19:23 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/access/nbtree/nbtcompare.c,v 1.54 2007/01/09 02:14:10 tgl Exp $ * * NOTES * @@ -22,10 +22,10 @@ * * The result is always an int32 regardless of the input datatype. * - * NOTE: although any negative int32 is acceptable for reporting "<", - * and any positive int32 is acceptable for reporting ">", routines + * Although any negative int32 (except INT_MIN) is acceptable for reporting + * "<", and any positive int32 is acceptable for reporting ">", routines * that work on 32-bit or wider datatypes can't just return "a - b". - * That could overflow and give the wrong answer. Also, one should not + * That could overflow and give the wrong answer. Also, one must not * return INT_MIN to report "<", since some callers will negate the result. * * NOTE: it is critical that the comparison function impose a total order diff --git a/src/backend/access/nbtree/nbtsearch.c b/src/backend/access/nbtree/nbtsearch.c index 2da75f547c..fc8b18a2e9 100644 --- a/src/backend/access/nbtree/nbtsearch.c +++ b/src/backend/access/nbtree/nbtsearch.c @@ -8,7 +8,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/access/nbtree/nbtsearch.c,v 1.110 2007/01/05 22:19:23 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/access/nbtree/nbtsearch.c,v 1.111 2007/01/09 02:14:10 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -376,12 +376,17 @@ _bt_compare(Relation rel, { if (isNull) result = 0; /* NULL "=" NULL */ + else if (scankey->sk_flags & SK_BT_NULLS_FIRST) + result = -1; /* NULL "<" NOT_NULL */ else result = 1; /* NULL ">" NOT_NULL */ } else if (isNull) /* key is NOT_NULL and item is NULL */ { - result = -1; /* NOT_NULL "<" NULL */ + if (scankey->sk_flags & SK_BT_NULLS_FIRST) + result = 1; /* NOT_NULL ">" NULL */ + else + result = -1; /* NOT_NULL "<" NULL */ } else { @@ -390,16 +395,15 @@ _bt_compare(Relation rel, * the sk_argument as right arg (they might be of different * types). Since it is convenient for callers to think of * _bt_compare as comparing the scankey to the index item, we have - * to flip the sign of the comparison result. - * - * Note: curious-looking coding is to avoid overflow if comparison - * function returns INT_MIN. There is no risk of overflow for - * positive results. + * to flip the sign of the comparison result. (Unless it's a DESC + * column, in which case we *don't* flip the sign.) */ result = DatumGetInt32(FunctionCall2(&scankey->sk_func, datum, scankey->sk_argument)); - result = (result < 0) ? 1 : -result; + + if (!(scankey->sk_flags & SK_BT_DESC)) + result = -result; } /* if the keys are unequal, return the difference */ @@ -617,11 +621,12 @@ _bt_first(IndexScanDesc scan, ScanDirection dir) * in the first row member makes the condition unmatchable, just * like qual_ok = false. */ - cur = (ScanKey) DatumGetPointer(cur->sk_argument); - Assert(cur->sk_flags & SK_ROW_MEMBER); - if (cur->sk_flags & SK_ISNULL) + ScanKey subkey = (ScanKey) DatumGetPointer(cur->sk_argument); + + Assert(subkey->sk_flags & SK_ROW_MEMBER); + if (subkey->sk_flags & SK_ISNULL) return false; - memcpy(scankeys + i, cur, sizeof(ScanKeyData)); + memcpy(scankeys + i, subkey, sizeof(ScanKeyData)); /* * If the row comparison is the last positioning key we accepted, @@ -632,21 +637,46 @@ _bt_first(IndexScanDesc scan, ScanDirection dir) * even if the row comparison is of ">" or "<" type, because the * condition applied to all but the last row member is effectively * ">=" or "<=", and so the extra keys don't break the positioning - * scheme. + * scheme. But, by the same token, if we aren't able to use all + * the row members, then the part of the row comparison that we + * did use has to be treated as just a ">=" or "<=" condition, + * and so we'd better adjust strat_total accordingly. */ if (i == keysCount - 1) { - while (!(cur->sk_flags & SK_ROW_END)) + bool used_all_subkeys = false; + + Assert(!(subkey->sk_flags & SK_ROW_END)); + for(;;) { - cur++; - Assert(cur->sk_flags & SK_ROW_MEMBER); - if (cur->sk_attno != keysCount + 1) + subkey++; + Assert(subkey->sk_flags & SK_ROW_MEMBER); + if (subkey->sk_attno != keysCount + 1) break; /* out-of-sequence, can't use it */ - if (cur->sk_flags & SK_ISNULL) + if (subkey->sk_strategy != cur->sk_strategy) + break; /* wrong direction, can't use it */ + if (subkey->sk_flags & SK_ISNULL) break; /* can't use null keys */ Assert(keysCount < INDEX_MAX_KEYS); - memcpy(scankeys + keysCount, cur, sizeof(ScanKeyData)); + memcpy(scankeys + keysCount, subkey, sizeof(ScanKeyData)); keysCount++; + if (subkey->sk_flags & SK_ROW_END) + { + used_all_subkeys = true; + break; + } + } + if (!used_all_subkeys) + { + switch (strat_total) + { + case BTLessStrategyNumber: + strat_total = BTLessEqualStrategyNumber; + break; + case BTGreaterStrategyNumber: + strat_total = BTGreaterEqualStrategyNumber; + break; + } } break; /* done with outer loop */ } diff --git a/src/backend/access/nbtree/nbtsort.c b/src/backend/access/nbtree/nbtsort.c index 945f20729c..a917e27a76 100644 --- a/src/backend/access/nbtree/nbtsort.c +++ b/src/backend/access/nbtree/nbtsort.c @@ -57,7 +57,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/access/nbtree/nbtsort.c,v 1.109 2007/01/05 22:19:23 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/access/nbtree/nbtsort.c,v 1.110 2007/01/09 02:14:10 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -728,39 +728,45 @@ _bt_load(BTWriteState *wstate, BTSpool *btspool, BTSpool *btspool2) ScanKey entry; Datum attrDatum1, attrDatum2; - bool isFirstNull, - isSecondNull; + bool isNull1, + isNull2; + int32 compare; entry = indexScanKey + i - 1; - attrDatum1 = index_getattr(itup, i, tupdes, - &isFirstNull); - attrDatum2 = index_getattr(itup2, i, tupdes, - &isSecondNull); - if (isFirstNull) + attrDatum1 = index_getattr(itup, i, tupdes, &isNull1); + attrDatum2 = index_getattr(itup2, i, tupdes, &isNull2); + if (isNull1) { - if (!isSecondNull) - { - load1 = false; - break; - } + if (isNull2) + compare = 0; /* NULL "=" NULL */ + else if (entry->sk_flags & SK_BT_NULLS_FIRST) + compare = -1; /* NULL "<" NOT_NULL */ + else + compare = 1; /* NULL ">" NOT_NULL */ + } + else if (isNull2) + { + if (entry->sk_flags & SK_BT_NULLS_FIRST) + compare = 1; /* NOT_NULL ">" NULL */ + else + compare = -1; /* NOT_NULL "<" NULL */ } - else if (isSecondNull) - break; else { - int32 compare; - compare = DatumGetInt32(FunctionCall2(&entry->sk_func, attrDatum1, attrDatum2)); - if (compare > 0) - { - load1 = false; - break; - } - else if (compare < 0) - break; + + if (entry->sk_flags & SK_BT_DESC) + compare = -compare; } + if (compare > 0) + { + load1 = false; + break; + } + else if (compare < 0) + break; } } else diff --git a/src/backend/access/nbtree/nbtutils.c b/src/backend/access/nbtree/nbtutils.c index d82a93a63c..d453a93caf 100644 --- a/src/backend/access/nbtree/nbtutils.c +++ b/src/backend/access/nbtree/nbtutils.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/access/nbtree/nbtutils.c,v 1.81 2007/01/05 22:19:23 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/access/nbtree/nbtutils.c,v 1.82 2007/01/09 02:14:10 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -30,6 +30,7 @@ static bool _bt_compare_scankey_args(IndexScanDesc scan, ScanKey op, ScanKey leftarg, ScanKey rightarg, bool *result); +static void _bt_mark_scankey_with_indoption(ScanKey skey, int16 *indoption); static void _bt_mark_scankey_required(ScanKey skey); static bool _bt_check_rowcompare(ScanKey skey, IndexTuple tuple, TupleDesc tupdesc, @@ -49,10 +50,12 @@ _bt_mkscankey(Relation rel, IndexTuple itup) ScanKey skey; TupleDesc itupdesc; int natts; + int16 *indoption; int i; itupdesc = RelationGetDescr(rel); natts = RelationGetNumberOfAttributes(rel); + indoption = rel->rd_indoption; skey = (ScanKey) palloc(natts * sizeof(ScanKeyData)); @@ -61,6 +64,7 @@ _bt_mkscankey(Relation rel, IndexTuple itup) FmgrInfo *procinfo; Datum arg; bool null; + int flags; /* * We can use the cached (default) support procs since no cross-type @@ -68,8 +72,9 @@ _bt_mkscankey(Relation rel, IndexTuple itup) */ procinfo = index_getprocinfo(rel, i + 1, BTORDER_PROC); arg = index_getattr(itup, i + 1, itupdesc, &null); + flags = (null ? SK_ISNULL : 0) | (indoption[i] << SK_BT_INDOPTION_SHIFT); ScanKeyEntryInitializeWithInfo(&skey[i], - null ? SK_ISNULL : 0, + flags, (AttrNumber) (i + 1), InvalidStrategy, InvalidOid, @@ -96,23 +101,27 @@ _bt_mkscankey_nodata(Relation rel) { ScanKey skey; int natts; + int16 *indoption; int i; natts = RelationGetNumberOfAttributes(rel); + indoption = rel->rd_indoption; skey = (ScanKey) palloc(natts * sizeof(ScanKeyData)); for (i = 0; i < natts; i++) { FmgrInfo *procinfo; + int flags; /* * We can use the cached (default) support procs since no cross-type * comparison can be needed. */ procinfo = index_getprocinfo(rel, i + 1, BTORDER_PROC); + flags = SK_ISNULL | (indoption[i] << SK_BT_INDOPTION_SHIFT); ScanKeyEntryInitializeWithInfo(&skey[i], - SK_ISNULL, + flags, (AttrNumber) (i + 1), InvalidStrategy, InvalidOid, @@ -157,7 +166,13 @@ _bt_freestack(BTStack stack) * the number of input keys, so->numberOfKeys gets the number of output * keys (possibly less, never greater). * - * The primary purpose of this routine is to discover how many scan keys + * The output keys are marked with additional sk_flag bits beyond the + * system-standard bits supplied by the caller. The DESC and NULLS_FIRST + * indoption bits for the relevant index attribute are copied into the flags. + * Also, for a DESC column, we commute (flip) all the sk_strategy numbers + * so that the index sorts in the desired direction. + * + * One key purpose of this routine is to discover how many scan keys * must be satisfied to continue the scan. It also attempts to eliminate * redundant keys and detect contradictory keys. (If the index opfamily * provides incomplete sets of cross-type operators, we may fail to detect @@ -219,6 +234,7 @@ _bt_preprocess_keys(IndexScanDesc scan) { BTScanOpaque so = (BTScanOpaque) scan->opaque; int numberOfKeys = scan->numberOfKeys; + int16 *indoption = scan->indexRelation->rd_indoption; int new_numberOfKeys; int numberOfEqualCols; ScanKey inkeys; @@ -254,7 +270,8 @@ _bt_preprocess_keys(IndexScanDesc scan) */ if (cur->sk_flags & SK_ISNULL) so->qual_ok = false; - memcpy(outkeys, inkeys, sizeof(ScanKeyData)); + _bt_mark_scankey_with_indoption(cur, indoption); + memcpy(outkeys, cur, sizeof(ScanKeyData)); so->numberOfKeys = 1; /* We can mark the qual as required if it's for first index col */ if (cur->sk_attno == 1) @@ -407,6 +424,9 @@ _bt_preprocess_keys(IndexScanDesc scan) memset(xform, 0, sizeof(xform)); } + /* apply indoption to scankey (might change sk_strategy!) */ + _bt_mark_scankey_with_indoption(cur, indoption); + /* check strategy this key's operator corresponds to */ j = cur->sk_strategy - 1; @@ -486,6 +506,11 @@ _bt_preprocess_keys(IndexScanDesc scan) * Note: op always points at the same ScanKey as either leftarg or rightarg. * Since we don't scribble on the scankeys, this aliasing should cause no * trouble. + * + * Note: this routine needs to be insensitive to any DESC option applied + * to the index column. For example, "x < 4" is a tighter constraint than + * "x < 5" regardless of which way the index is sorted. We don't worry about + * NULLS FIRST/LAST either, since the given values are never nulls. */ static bool _bt_compare_scankey_args(IndexScanDesc scan, ScanKey op, @@ -498,6 +523,7 @@ _bt_compare_scankey_args(IndexScanDesc scan, ScanKey op, optype, opcintype, cmp_op; + StrategyNumber strat; /* * The opfamily we need to worry about is identified by the index column. @@ -538,11 +564,18 @@ _bt_compare_scankey_args(IndexScanDesc scan, ScanKey op, * operator. (This cannot result in infinite recursion, since no * indexscan initiated by syscache lookup will use cross-data-type * operators.) + * + * If the sk_strategy was flipped by _bt_mark_scankey_with_indoption, + * we have to un-flip it to get the correct opfamily member. */ + strat = op->sk_strategy; + if (op->sk_flags & SK_BT_DESC) + strat = BTCommuteStrategyNumber(strat); + cmp_op = get_opfamily_member(rel->rd_opfamily[leftarg->sk_attno - 1], lefttype, righttype, - op->sk_strategy); + strat); if (OidIsValid(cmp_op)) { RegProcedure cmp_proc = get_opcode(cmp_op); @@ -561,6 +594,48 @@ _bt_compare_scankey_args(IndexScanDesc scan, ScanKey op, return false; } +/* + * Mark a scankey with info from the index's indoption array. + * + * We copy the appropriate indoption value into the scankey sk_flags + * (shifting to avoid clobbering system-defined flag bits). Also, if + * the DESC option is set, commute (flip) the operator strategy number. + * + * This function is applied to the *input* scankey structure; therefore + * on a rescan we will be looking at already-processed scankeys. Hence + * we have to be careful not to re-commute the strategy if we already did it. + * It's a bit ugly to modify the caller's copy of the scankey but in practice + * there shouldn't be any problem, since the index's indoptions are certainly + * not going to change while the scankey survives. + */ +static void +_bt_mark_scankey_with_indoption(ScanKey skey, int16 *indoption) +{ + int addflags; + + addflags = indoption[skey->sk_attno - 1] << SK_BT_INDOPTION_SHIFT; + if ((addflags & SK_BT_DESC) && !(skey->sk_flags & SK_BT_DESC)) + skey->sk_strategy = BTCommuteStrategyNumber(skey->sk_strategy); + skey->sk_flags |= addflags; + + if (skey->sk_flags & SK_ROW_HEADER) + { + ScanKey subkey = (ScanKey) DatumGetPointer(skey->sk_argument); + + for (;;) + { + Assert(subkey->sk_flags & SK_ROW_MEMBER); + addflags = indoption[subkey->sk_attno - 1] << SK_BT_INDOPTION_SHIFT; + if ((addflags & SK_BT_DESC) && !(subkey->sk_flags & SK_BT_DESC)) + subkey->sk_strategy = BTCommuteStrategyNumber(subkey->sk_strategy); + subkey->sk_flags |= addflags; + if (subkey->sk_flags & SK_ROW_END) + break; + subkey++; + } + } +} + /* * Mark a scankey as "required to continue the scan". * @@ -568,7 +643,8 @@ _bt_compare_scankey_args(IndexScanDesc scan, ScanKey op, * directions or just one. Also, if the key is a row comparison header, * we have to mark the appropriate subsidiary ScanKeys as required. In * such cases, the first subsidiary key is required, but subsequent ones - * are required only as long as they correspond to successive index columns. + * are required only as long as they correspond to successive index columns + * and match the leading column as to sort direction. * Otherwise the row comparison ordering is different from the index ordering * and so we can't stop the scan on the basis of those lower-order columns. * @@ -616,9 +692,10 @@ _bt_mark_scankey_required(ScanKey skey) for (;;) { Assert(subkey->sk_flags & SK_ROW_MEMBER); - Assert(subkey->sk_strategy == skey->sk_strategy); if (subkey->sk_attno != attno) break; /* non-adjacent key, so not required */ + if (subkey->sk_strategy != skey->sk_strategy) + break; /* wrong direction, so not required */ subkey->sk_flags |= addflags; if (subkey->sk_flags & SK_ROW_END) break; @@ -731,15 +808,32 @@ _bt_checkkeys(IndexScanDesc scan, if (isNull) { - /* - * Since NULLs are sorted after non-NULLs, we know we have reached - * the upper limit of the range of values for this index attr. On - * a forward scan, we can stop if this qual is one of the "must - * match" subset. On a backward scan, however, we should keep - * going. - */ - if ((key->sk_flags & SK_BT_REQFWD) && ScanDirectionIsForward(dir)) - *continuescan = false; + if (key->sk_flags & SK_BT_NULLS_FIRST) + { + /* + * Since NULLs are sorted before non-NULLs, we know we have + * reached the lower limit of the range of values for this + * index attr. On a backward scan, we can stop if this qual is + * one of the "must match" subset. On a forward scan, + * however, we should keep going. + */ + if ((key->sk_flags & SK_BT_REQBKWD) && + ScanDirectionIsBackward(dir)) + *continuescan = false; + } + else + { + /* + * Since NULLs are sorted after non-NULLs, we know we have + * reached the upper limit of the range of values for this + * index attr. On a forward scan, we can stop if this qual is + * one of the "must match" subset. On a backward scan, + * however, we should keep going. + */ + if ((key->sk_flags & SK_BT_REQFWD) && + ScanDirectionIsForward(dir)) + *continuescan = false; + } /* * In any case, this indextuple doesn't match the qual. @@ -809,7 +903,6 @@ _bt_check_rowcompare(ScanKey skey, IndexTuple tuple, TupleDesc tupdesc, bool isNull; Assert(subkey->sk_flags & SK_ROW_MEMBER); - Assert(subkey->sk_strategy == skey->sk_strategy); datum = index_getattr(tuple, subkey->sk_attno, @@ -818,16 +911,32 @@ _bt_check_rowcompare(ScanKey skey, IndexTuple tuple, TupleDesc tupdesc, if (isNull) { - /* - * Since NULLs are sorted after non-NULLs, we know we have reached - * the upper limit of the range of values for this index attr. On - * a forward scan, we can stop if this qual is one of the "must - * match" subset. On a backward scan, however, we should keep - * going. - */ - if ((subkey->sk_flags & SK_BT_REQFWD) && - ScanDirectionIsForward(dir)) - *continuescan = false; + if (subkey->sk_flags & SK_BT_NULLS_FIRST) + { + /* + * Since NULLs are sorted before non-NULLs, we know we have + * reached the lower limit of the range of values for this + * index attr. On a backward scan, we can stop if this qual is + * one of the "must match" subset. On a forward scan, + * however, we should keep going. + */ + if ((subkey->sk_flags & SK_BT_REQBKWD) && + ScanDirectionIsBackward(dir)) + *continuescan = false; + } + else + { + /* + * Since NULLs are sorted after non-NULLs, we know we have + * reached the upper limit of the range of values for this + * index attr. On a forward scan, we can stop if this qual is + * one of the "must match" subset. On a backward scan, + * however, we should keep going. + */ + if ((subkey->sk_flags & SK_BT_REQFWD) && + ScanDirectionIsForward(dir)) + *continuescan = false; + } /* * In any case, this indextuple doesn't match the qual. @@ -859,6 +968,9 @@ _bt_check_rowcompare(ScanKey skey, IndexTuple tuple, TupleDesc tupdesc, datum, subkey->sk_argument)); + if (subkey->sk_flags & SK_BT_DESC) + cmpresult = -cmpresult; + /* Done comparing if unequal, else advance to next column */ if (cmpresult != 0) break; diff --git a/src/backend/bootstrap/bootparse.y b/src/backend/bootstrap/bootparse.y index 7cc6100c2a..2e17fb7b2b 100644 --- a/src/backend/bootstrap/bootparse.y +++ b/src/backend/bootstrap/bootparse.y @@ -9,7 +9,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/bootstrap/bootparse.y,v 1.85 2007/01/05 22:19:24 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/bootstrap/bootparse.y,v 1.86 2007/01/09 02:14:11 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -312,6 +312,8 @@ boot_index_param: n->name = LexIDStr($1); n->expr = NULL; n->opclass = list_make1(makeString(LexIDStr($2))); + n->ordering = SORTBY_DEFAULT; + n->nulls_ordering = SORTBY_NULLS_DEFAULT; $$ = n; } ; diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 25f1f8a16e..d75efdcc84 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/catalog/index.c,v 1.275 2007/01/05 22:19:24 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/catalog/index.c,v 1.276 2007/01/09 02:14:11 tgl Exp $ * * * INTERFACE ROUTINES @@ -73,6 +73,7 @@ static void AppendAttributeTuples(Relation indexRelation, int numatts); static void UpdateIndexRelation(Oid indexoid, Oid heapoid, IndexInfo *indexInfo, Oid *classOids, + int16 *coloptions, bool primary, bool isvalid); static void index_update_stats(Relation rel, bool hasindex, bool isprimary, @@ -336,11 +337,13 @@ UpdateIndexRelation(Oid indexoid, Oid heapoid, IndexInfo *indexInfo, Oid *classOids, + int16 *coloptions, bool primary, bool isvalid) { int2vector *indkey; oidvector *indclass; + int2vector *indoption; Datum exprsDatum; Datum predDatum; Datum values[Natts_pg_index]; @@ -350,13 +353,14 @@ UpdateIndexRelation(Oid indexoid, int i; /* - * Copy the index key and opclass info into arrays (should we make the - * caller pass them like this to start with?) + * Copy the index key, opclass, and indoption info into arrays (should we + * make the caller pass them like this to start with?) */ indkey = buildint2vector(NULL, indexInfo->ii_NumIndexAttrs); - indclass = buildoidvector(classOids, indexInfo->ii_NumIndexAttrs); for (i = 0; i < indexInfo->ii_NumIndexAttrs; i++) indkey->values[i] = indexInfo->ii_KeyAttrNumbers[i]; + indclass = buildoidvector(classOids, indexInfo->ii_NumIndexAttrs); + indoption = buildint2vector(coloptions, indexInfo->ii_NumIndexAttrs); /* * Convert the index expressions (if any) to a text datum @@ -408,6 +412,7 @@ UpdateIndexRelation(Oid indexoid, values[Anum_pg_index_indisvalid - 1] = BoolGetDatum(isvalid); values[Anum_pg_index_indkey - 1] = PointerGetDatum(indkey); values[Anum_pg_index_indclass - 1] = PointerGetDatum(indclass); + values[Anum_pg_index_indoption - 1] = PointerGetDatum(indoption); values[Anum_pg_index_indexprs - 1] = exprsDatum; if (exprsDatum == (Datum) 0) nulls[Anum_pg_index_indexprs - 1] = 'n'; @@ -445,6 +450,7 @@ UpdateIndexRelation(Oid indexoid, * accessMethodObjectId: OID of index AM to use * tableSpaceId: OID of tablespace to use * classObjectId: array of index opclass OIDs, one per index column + * coloptions: array of per-index-column indoption settings * reloptions: AM-specific options * isprimary: index is a PRIMARY KEY * isconstraint: index is owned by a PRIMARY KEY or UNIQUE constraint @@ -465,6 +471,7 @@ index_create(Oid heapRelationId, Oid accessMethodObjectId, Oid tableSpaceId, Oid *classObjectId, + int16 *coloptions, Datum reloptions, bool isprimary, bool isconstraint, @@ -618,7 +625,7 @@ index_create(Oid heapRelationId, * ---------------- */ UpdateIndexRelation(indexRelationId, heapRelationId, indexInfo, - classObjectId, isprimary, !concurrent); + classObjectId, coloptions, isprimary, !concurrent); /* * Register constraint and dependencies for the index. @@ -1639,7 +1646,7 @@ validate_index(Oid heapId, Oid indexId, Snapshot snapshot) ivinfo.num_heap_tuples = -1; state.tuplesort = tuplesort_begin_datum(TIDOID, - TIDLessOperator, + TIDLessOperator, false, maintenance_work_mem, false); state.htups = state.itups = state.tups_inserted = 0; diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c index ce65fda5d1..47f5e25fd2 100644 --- a/src/backend/catalog/toasting.c +++ b/src/backend/catalog/toasting.c @@ -8,7 +8,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/catalog/toasting.c,v 1.4 2007/01/05 22:19:25 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/catalog/toasting.c,v 1.5 2007/01/09 02:14:11 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -112,6 +112,7 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid) char toast_idxname[NAMEDATALEN]; IndexInfo *indexInfo; Oid classObjectId[2]; + int16 coloptions[2]; ObjectAddress baseobject, toastobject; @@ -223,11 +224,14 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid) classObjectId[0] = OID_BTREE_OPS_OID; classObjectId[1] = INT4_BTREE_OPS_OID; + coloptions[0] = 0; + coloptions[1] = 0; + toast_idxid = index_create(toast_relid, toast_idxname, toastIndexOid, indexInfo, BTREE_AM_OID, rel->rd_rel->reltablespace, - classObjectId, (Datum) 0, + classObjectId, coloptions, (Datum) 0, true, false, true, false, false); /* diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c index 102be0d96f..c568f04284 100644 --- a/src/backend/commands/analyze.c +++ b/src/backend/commands/analyze.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/commands/analyze.c,v 1.102 2007/01/05 22:19:25 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/commands/analyze.c,v 1.103 2007/01/09 02:14:11 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -1297,7 +1297,7 @@ typedef struct typedef struct { FmgrInfo *cmpFn; - SortFunctionKind cmpFnKind; + int cmpFlags; int *tupnoLink; } CompareScalarsContext; @@ -1747,8 +1747,8 @@ compute_scalar_stats(VacAttrStatsP stats, bool is_varwidth = (!stats->attr->attbyval && stats->attr->attlen < 0); double corr_xysum; - RegProcedure cmpFn; - SortFunctionKind cmpFnKind; + Oid cmpFn; + int cmpFlags; FmgrInfo f_cmpfn; ScalarItem *values; int values_cnt = 0; @@ -1763,7 +1763,7 @@ compute_scalar_stats(VacAttrStatsP stats, tupnoLink = (int *) palloc(samplerows * sizeof(int)); track = (ScalarMCVItem *) palloc(num_mcv * sizeof(ScalarMCVItem)); - SelectSortFunction(mystats->ltopr, &cmpFn, &cmpFnKind); + SelectSortFunction(mystats->ltopr, false, &cmpFn, &cmpFlags); fmgr_info(cmpFn, &f_cmpfn); /* Initial scan to find sortable values */ @@ -1833,7 +1833,7 @@ compute_scalar_stats(VacAttrStatsP stats, /* Sort the collected values */ cxt.cmpFn = &f_cmpfn; - cxt.cmpFnKind = cmpFnKind; + cxt.cmpFlags = cmpFlags; cxt.tupnoLink = tupnoLink; qsort_arg((void *) values, values_cnt, sizeof(ScalarItem), compare_scalars, (void *) &cxt); @@ -2203,7 +2203,7 @@ compare_scalars(const void *a, const void *b, void *arg) CompareScalarsContext *cxt = (CompareScalarsContext *) arg; int32 compare; - compare = ApplySortFunction(cxt->cmpFn, cxt->cmpFnKind, + compare = ApplySortFunction(cxt->cmpFn, cxt->cmpFlags, da, false, db, false); if (compare != 0) return compare; diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index e35d6479db..bbaa34f758 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/commands/indexcmds.c,v 1.151 2007/01/05 22:19:26 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/commands/indexcmds.c,v 1.152 2007/01/09 02:14:11 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -49,10 +49,13 @@ /* non-export function prototypes */ static void CheckPredicate(Expr *predicate); -static void ComputeIndexAttrs(IndexInfo *indexInfo, Oid *classOidP, +static void ComputeIndexAttrs(IndexInfo *indexInfo, + Oid *classOidP, + int16 *colOptionP, List *attList, Oid relId, char *accessMethodName, Oid accessMethodId, + bool amcanorder, bool isconstraint); static Oid GetIndexOpClass(List *opclass, Oid attrType, char *accessMethodName, Oid accessMethodId); @@ -115,8 +118,10 @@ DefineIndex(RangeVar *heapRelation, Relation rel; HeapTuple tuple; Form_pg_am accessMethodForm; + bool amcanorder; RegProcedure amoptions; Datum reloptions; + int16 *coloptions; IndexInfo *indexInfo; int numberOfAttributes; List *old_xact_list; @@ -290,6 +295,8 @@ DefineIndex(RangeVar *heapRelation, errmsg("access method \"%s\" does not support multicolumn indexes", accessMethodName))); + amcanorder = (accessMethodForm->amorderstrategy > 0); + amoptions = accessMethodForm->amoptions; ReleaseSysCache(tuple); @@ -419,9 +426,10 @@ DefineIndex(RangeVar *heapRelation, indexInfo->ii_Concurrent = concurrent; classObjectId = (Oid *) palloc(numberOfAttributes * sizeof(Oid)); - ComputeIndexAttrs(indexInfo, classObjectId, attributeList, + coloptions = (int16 *) palloc(numberOfAttributes * sizeof(int16)); + ComputeIndexAttrs(indexInfo, classObjectId, coloptions, attributeList, relationId, accessMethodName, accessMethodId, - isconstraint); + amcanorder, isconstraint); heap_close(rel, NoLock); @@ -439,7 +447,7 @@ DefineIndex(RangeVar *heapRelation, indexRelationId = index_create(relationId, indexRelationName, indexRelationId, indexInfo, accessMethodId, tablespaceId, classObjectId, - reloptions, primary, isconstraint, + coloptions, reloptions, primary, isconstraint, allowSystemTableMods, skip_build, concurrent); if (!concurrent) @@ -585,13 +593,19 @@ CheckPredicate(Expr *predicate) errmsg("functions in index predicate must be marked IMMUTABLE"))); } +/* + * Compute per-index-column information, including indexed column numbers + * or index expressions, opclasses, and indoptions. + */ static void ComputeIndexAttrs(IndexInfo *indexInfo, Oid *classOidP, + int16 *colOptionP, List *attList, /* list of IndexElem's */ Oid relId, char *accessMethodName, Oid accessMethodId, + bool amcanorder, bool isconstraint) { ListCell *rest; @@ -605,6 +619,9 @@ ComputeIndexAttrs(IndexInfo *indexInfo, IndexElem *attribute = (IndexElem *) lfirst(rest); Oid atttype; + /* + * Process the column-or-expression to be indexed. + */ if (attribute->name != NULL) { /* Simple index attribute */ @@ -674,10 +691,49 @@ ComputeIndexAttrs(IndexInfo *indexInfo, errmsg("functions in index expression must be marked IMMUTABLE"))); } + /* + * Identify the opclass to use. + */ classOidP[attn] = GetIndexOpClass(attribute->opclass, atttype, accessMethodName, accessMethodId); + + /* + * Set up the per-column options (indoption field). For now, this + * is zero for any un-ordered index, while ordered indexes have DESC + * and NULLS FIRST/LAST options. + */ + colOptionP[attn] = 0; + if (amcanorder) + { + /* default ordering is ASC */ + if (attribute->ordering == SORTBY_DESC) + colOptionP[attn] |= INDOPTION_DESC; + /* default null ordering is LAST for ASC, FIRST for DESC */ + if (attribute->nulls_ordering == SORTBY_NULLS_DEFAULT) + { + if (attribute->ordering == SORTBY_DESC) + colOptionP[attn] |= INDOPTION_NULLS_FIRST; + } + else if (attribute->nulls_ordering == SORTBY_NULLS_FIRST) + colOptionP[attn] |= INDOPTION_NULLS_FIRST; + } + else + { + /* index AM does not support ordering */ + if (attribute->ordering != SORTBY_DEFAULT) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("access method \"%s\" does not support ASC/DESC options", + accessMethodName))); + if (attribute->nulls_ordering != SORTBY_NULLS_DEFAULT) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("access method \"%s\" does not support NULLS FIRST/LAST options", + accessMethodName))); + } + attn++; } } diff --git a/src/backend/executor/nodeAgg.c b/src/backend/executor/nodeAgg.c index cfe8acede5..74a00ac892 100644 --- a/src/backend/executor/nodeAgg.c +++ b/src/backend/executor/nodeAgg.c @@ -61,7 +61,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/executor/nodeAgg.c,v 1.147 2007/01/05 22:19:28 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/executor/nodeAgg.c,v 1.148 2007/01/09 02:14:11 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -269,7 +269,7 @@ initialize_aggregates(AggState *aggstate, peraggstate->sortstate = tuplesort_begin_datum(peraggstate->inputType, - peraggstate->sortOperator, + peraggstate->sortOperator, false, work_mem, false); } diff --git a/src/backend/executor/nodeSort.c b/src/backend/executor/nodeSort.c index 73ee19cb35..97b5c4ff15 100644 --- a/src/backend/executor/nodeSort.c +++ b/src/backend/executor/nodeSort.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/executor/nodeSort.c,v 1.59 2007/01/05 22:19:28 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/executor/nodeSort.c,v 1.60 2007/01/09 02:14:11 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -84,8 +84,9 @@ ExecSort(SortState *node) tuplesortstate = tuplesort_begin_heap(tupDesc, plannode->numCols, - plannode->sortOperators, plannode->sortColIdx, + plannode->sortOperators, + plannode->nullsFirst, work_mem, node->randomAccess); node->tuplesortstate = (void *) tuplesortstate; diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index e1117179ef..08817e54a9 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -15,7 +15,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.359 2007/01/05 22:19:29 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.360 2007/01/09 02:14:11 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -510,6 +510,7 @@ _copySort(Sort *from) COPY_SCALAR_FIELD(numCols); COPY_POINTER_FIELD(sortColIdx, from->numCols * sizeof(AttrNumber)); COPY_POINTER_FIELD(sortOperators, from->numCols * sizeof(Oid)); + COPY_POINTER_FIELD(nullsFirst, from->numCols * sizeof(bool)); return newnode; } @@ -1283,6 +1284,7 @@ _copyPathKeyItem(PathKeyItem *from) COPY_NODE_FIELD(key); COPY_SCALAR_FIELD(sortop); + COPY_SCALAR_FIELD(nulls_first); return newnode; } @@ -1432,6 +1434,7 @@ _copySortClause(SortClause *from) COPY_SCALAR_FIELD(tleSortGroupRef); COPY_SCALAR_FIELD(sortop); + COPY_SCALAR_FIELD(nulls_first); return newnode; } @@ -1443,6 +1446,7 @@ _copyGroupClause(GroupClause *from) COPY_SCALAR_FIELD(tleSortGroupRef); COPY_SCALAR_FIELD(sortop); + COPY_SCALAR_FIELD(nulls_first); return newnode; } @@ -1597,7 +1601,8 @@ _copySortBy(SortBy *from) { SortBy *newnode = makeNode(SortBy); - COPY_SCALAR_FIELD(sortby_kind); + COPY_SCALAR_FIELD(sortby_dir); + COPY_SCALAR_FIELD(sortby_nulls); COPY_NODE_FIELD(useOp); COPY_NODE_FIELD(node); @@ -1646,6 +1651,8 @@ _copyIndexElem(IndexElem *from) COPY_STRING_FIELD(name); COPY_NODE_FIELD(expr); COPY_NODE_FIELD(opclass); + COPY_SCALAR_FIELD(ordering); + COPY_SCALAR_FIELD(nulls_ordering); return newnode; } diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 8e14dafc28..e1b3bbbbaa 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -18,7 +18,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.293 2007/01/05 22:19:30 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.294 2007/01/09 02:14:12 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -600,6 +600,7 @@ _equalPathKeyItem(PathKeyItem *a, PathKeyItem *b) { COMPARE_NODE_FIELD(key); COMPARE_SCALAR_FIELD(sortop); + COMPARE_SCALAR_FIELD(nulls_first); return true; } @@ -1634,7 +1635,8 @@ _equalTypeCast(TypeCast *a, TypeCast *b) static bool _equalSortBy(SortBy *a, SortBy *b) { - COMPARE_SCALAR_FIELD(sortby_kind); + COMPARE_SCALAR_FIELD(sortby_dir); + COMPARE_SCALAR_FIELD(sortby_nulls); COMPARE_NODE_FIELD(useOp); COMPARE_NODE_FIELD(node); @@ -1666,6 +1668,8 @@ _equalIndexElem(IndexElem *a, IndexElem *b) COMPARE_STRING_FIELD(name); COMPARE_NODE_FIELD(expr); COMPARE_NODE_FIELD(opclass); + COMPARE_SCALAR_FIELD(ordering); + COMPARE_SCALAR_FIELD(nulls_ordering); return true; } @@ -1745,6 +1749,7 @@ _equalSortClause(SortClause *a, SortClause *b) { COMPARE_SCALAR_FIELD(tleSortGroupRef); COMPARE_SCALAR_FIELD(sortop); + COMPARE_SCALAR_FIELD(nulls_first); return true; } diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index 8f341daf9d..1ffaa08dfe 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/nodes/outfuncs.c,v 1.291 2007/01/05 22:19:30 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/nodes/outfuncs.c,v 1.292 2007/01/09 02:14:12 tgl Exp $ * * NOTES * Every node type that can appear in stored rules' parsetrees *must* @@ -510,6 +510,10 @@ _outSort(StringInfo str, Sort *node) appendStringInfo(str, " :sortOperators"); for (i = 0; i < node->numCols; i++) appendStringInfo(str, " %u", node->sortOperators[i]); + + appendStringInfo(str, " :nullsFirst"); + for (i = 0; i < node->numCols; i++) + appendStringInfo(str, " %s", booltostr(node->nullsFirst[i])); } static void @@ -1265,6 +1269,7 @@ _outPathKeyItem(StringInfo str, PathKeyItem *node) WRITE_NODE_FIELD(key); WRITE_OID_FIELD(sortop); + WRITE_BOOL_FIELD(nulls_first); } static void @@ -1499,6 +1504,8 @@ _outIndexElem(StringInfo str, IndexElem *node) WRITE_STRING_FIELD(name); WRITE_NODE_FIELD(expr); WRITE_NODE_FIELD(opclass); + WRITE_ENUM_FIELD(ordering, SortByDir); + WRITE_ENUM_FIELD(nulls_ordering, SortByNulls); } static void @@ -1565,6 +1572,7 @@ _outSortClause(StringInfo str, SortClause *node) WRITE_UINT_FIELD(tleSortGroupRef); WRITE_OID_FIELD(sortop); + WRITE_BOOL_FIELD(nulls_first); } static void @@ -1574,6 +1582,7 @@ _outGroupClause(StringInfo str, GroupClause *node) WRITE_UINT_FIELD(tleSortGroupRef); WRITE_OID_FIELD(sortop); + WRITE_BOOL_FIELD(nulls_first); } static void diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c index e4872b5814..b90c8dd713 100644 --- a/src/backend/nodes/readfuncs.c +++ b/src/backend/nodes/readfuncs.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/nodes/readfuncs.c,v 1.200 2007/01/05 22:19:30 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/nodes/readfuncs.c,v 1.201 2007/01/09 02:14:12 tgl Exp $ * * NOTES * Path and Plan nodes do not have any readfuncs support, because we @@ -201,6 +201,7 @@ _readSortClause(void) READ_UINT_FIELD(tleSortGroupRef); READ_OID_FIELD(sortop); + READ_BOOL_FIELD(nulls_first); READ_DONE(); } @@ -215,6 +216,7 @@ _readGroupClause(void) READ_UINT_FIELD(tleSortGroupRef); READ_OID_FIELD(sortop); + READ_BOOL_FIELD(nulls_first); READ_DONE(); } diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c index c2859aba94..01178d93dd 100644 --- a/src/backend/optimizer/path/allpaths.c +++ b/src/backend/optimizer/path/allpaths.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/optimizer/path/allpaths.c,v 1.155 2007/01/05 22:19:30 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/optimizer/path/allpaths.c,v 1.156 2007/01/09 02:14:12 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -924,7 +924,7 @@ qual_is_pushdown_safe(Query *subquery, Index rti, Node *qual, /* If subquery uses DISTINCT or DISTINCT ON, check point 4 */ if (subquery->distinctClause != NIL && - !targetIsInSortList(tle, subquery->distinctClause)) + !targetIsInSortList(tle, InvalidOid, subquery->distinctClause)) { /* non-DISTINCT column, so fail */ safe = false; diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c index 8906471fb7..3fd52d4850 100644 --- a/src/backend/optimizer/path/indxpath.c +++ b/src/backend/optimizer/path/indxpath.c @@ -9,7 +9,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/optimizer/path/indxpath.c,v 1.214 2007/01/05 22:19:31 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/optimizer/path/indxpath.c,v 1.215 2007/01/09 02:14:12 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -347,7 +347,7 @@ find_usable_indexes(PlannerInfo *root, RelOptInfo *rel, * how many of them are actually useful for this query. This is not * relevant unless we are at top level. */ - index_is_ordered = OidIsValid(index->ordering[0]); + index_is_ordered = OidIsValid(index->fwdsortop[0]); if (index_is_ordered && istoplevel && outer_rel == NULL) { index_pathkeys = build_index_pathkeys(root, index, diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c index 2af4cf7f9e..4fc5a5f125 100644 --- a/src/backend/optimizer/path/pathkeys.c +++ b/src/backend/optimizer/path/pathkeys.c @@ -11,7 +11,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/optimizer/path/pathkeys.c,v 1.80 2007/01/05 22:19:31 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/optimizer/path/pathkeys.c,v 1.81 2007/01/09 02:14:12 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -30,7 +30,8 @@ #include "utils/memutils.h" -static PathKeyItem *makePathKeyItem(Node *key, Oid sortop, bool checkType); +static PathKeyItem *makePathKeyItem(Node *key, Oid sortop, bool nulls_first, + bool checkType); static void generate_outer_join_implications(PlannerInfo *root, List *equi_key_set, Relids *relids); @@ -53,7 +54,7 @@ static Var *find_indexkey_var(PlannerInfo *root, RelOptInfo *rel, * create a PathKeyItem node */ static PathKeyItem * -makePathKeyItem(Node *key, Oid sortop, bool checkType) +makePathKeyItem(Node *key, Oid sortop, bool nulls_first, bool checkType) { PathKeyItem *item = makeNode(PathKeyItem); @@ -78,6 +79,7 @@ makePathKeyItem(Node *key, Oid sortop, bool checkType) item->key = key; item->sortop = sortop; + item->nulls_first = nulls_first; return item; } @@ -102,9 +104,11 @@ add_equijoined_keys(PlannerInfo *root, RestrictInfo *restrictinfo) Expr *clause = restrictinfo->clause; PathKeyItem *item1 = makePathKeyItem(get_leftop(clause), restrictinfo->left_sortop, + false, /* XXX nulls_first? */ false); PathKeyItem *item2 = makePathKeyItem(get_rightop(clause), restrictinfo->right_sortop, + false, /* XXX nulls_first? */ false); List *newset; ListCell *cursetlink; @@ -903,7 +907,7 @@ get_cheapest_fractional_path_for_pathkeys(List *paths, * Build a pathkeys list that describes the ordering induced by an index * scan using the given index. (Note that an unordered index doesn't * induce any ordering; such an index will have no sortop OIDS in - * its "ordering" field, and we will return NIL.) + * its sortops arrays, and we will return NIL.) * * If 'scandir' is BackwardScanDirection, attempt to build pathkeys * representing a backwards scan of the index. Return NIL if can't do it. @@ -924,30 +928,37 @@ build_index_pathkeys(PlannerInfo *root, bool canonical) { List *retval = NIL; - int *indexkeys = index->indexkeys; - Oid *ordering = index->ordering; ListCell *indexprs_item = list_head(index->indexprs); + int i; - while (*ordering != InvalidOid) + for (i = 0; i < index->ncolumns; i++) { - PathKeyItem *item; Oid sortop; + bool nulls_first; + int ikey; Node *indexkey; + PathKeyItem *item; List *cpathkey; - sortop = *ordering; if (ScanDirectionIsBackward(scandir)) { - sortop = get_commutator(sortop); - if (sortop == InvalidOid) - break; /* oops, no reverse sort operator? */ + sortop = index->revsortop[i]; + nulls_first = !index->nulls_first[i]; } + else + { + sortop = index->fwdsortop[i]; + nulls_first = index->nulls_first[i]; + } + + if (!OidIsValid(sortop)) + break; /* no more orderable columns */ - if (*indexkeys != 0) + ikey = index->indexkeys[i]; + if (ikey != 0) { /* simple index column */ - indexkey = (Node *) find_indexkey_var(root, index->rel, - *indexkeys); + indexkey = (Node *) find_indexkey_var(root, index->rel, ikey); } else { @@ -959,7 +970,7 @@ build_index_pathkeys(PlannerInfo *root, } /* OK, make a sublist for this sort key */ - item = makePathKeyItem(indexkey, sortop, true); + item = makePathKeyItem(indexkey, sortop, nulls_first, true); cpathkey = make_canonical_pathkey(root, item); /* Eliminate redundant ordering info if requested */ @@ -967,9 +978,6 @@ build_index_pathkeys(PlannerInfo *root, retval = list_append_unique_ptr(retval, cpathkey); else retval = lappend(retval, cpathkey); - - indexkeys++; - ordering++; } return retval; @@ -1115,6 +1123,7 @@ convert_subquery_pathkeys(PlannerInfo *root, RelOptInfo *rel, /* Found a representation for this sub_key */ outer_item = makePathKeyItem(outer_expr, sub_item->sortop, + sub_item->nulls_first, true); /* score = # of mergejoin peers */ score = count_canonical_peers(root, outer_item); @@ -1230,7 +1239,8 @@ make_pathkeys_for_sortclauses(List *sortclauses, PathKeyItem *pathkey; sortkey = get_sortgroupclause_expr(sortcl, tlist); - pathkey = makePathKeyItem(sortkey, sortcl->sortop, true); + pathkey = makePathKeyItem(sortkey, sortcl->sortop, sortcl->nulls_first, + true); /* * The pathkey becomes a one-element sublist, for now; @@ -1274,7 +1284,9 @@ cache_mergeclause_pathkeys(PlannerInfo *root, RestrictInfo *restrictinfo) { oldcontext = MemoryContextSwitchTo(GetMemoryChunkContext(restrictinfo)); key = get_leftop(restrictinfo->clause); - item = makePathKeyItem(key, restrictinfo->left_sortop, false); + item = makePathKeyItem(key, restrictinfo->left_sortop, + false, /* XXX nulls_first? */ + false); restrictinfo->left_pathkey = make_canonical_pathkey(root, item); MemoryContextSwitchTo(oldcontext); } @@ -1282,7 +1294,9 @@ cache_mergeclause_pathkeys(PlannerInfo *root, RestrictInfo *restrictinfo) { oldcontext = MemoryContextSwitchTo(GetMemoryChunkContext(restrictinfo)); key = get_rightop(restrictinfo->clause); - item = makePathKeyItem(key, restrictinfo->right_sortop, false); + item = makePathKeyItem(key, restrictinfo->right_sortop, + false, /* XXX nulls_first? */ + false); restrictinfo->right_pathkey = make_canonical_pathkey(root, item); MemoryContextSwitchTo(oldcontext); } diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 7328f41a39..8f1d8e81cc 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -10,7 +10,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/optimizer/plan/createplan.c,v 1.219 2007/01/05 22:19:31 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/optimizer/plan/createplan.c,v 1.220 2007/01/09 02:14:12 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -117,7 +117,7 @@ static MergeJoin *make_mergejoin(List *tlist, Plan *lefttree, Plan *righttree, JoinType jointype); static Sort *make_sort(PlannerInfo *root, Plan *lefttree, int numCols, - AttrNumber *sortColIdx, Oid *sortOperators); + AttrNumber *sortColIdx, Oid *sortOperators, bool *nullsFirst); static Sort *make_sort_from_pathkeys(PlannerInfo *root, Plan *lefttree, List *pathkeys); @@ -734,7 +734,9 @@ create_unique_plan(PlannerInfo *root, UniquePath *best_path) Assert(tle != NULL); sortList = addTargetToSortList(NULL, tle, sortList, subplan->targetlist, - SORTBY_ASC, NIL, false); + SORTBY_DEFAULT, + SORTBY_NULLS_DEFAULT, + NIL, false); } plan = (Plan *) make_sort_from_sortclauses(root, sortList, subplan); plan = (Plan *) make_unique(plan, sortList); @@ -2359,11 +2361,12 @@ make_mergejoin(List *tlist, /* * make_sort --- basic routine to build a Sort plan node * - * Caller must have built the sortColIdx and sortOperators arrays already. + * Caller must have built the sortColIdx, sortOperators, and nullsFirst + * arrays already. */ static Sort * make_sort(PlannerInfo *root, Plan *lefttree, int numCols, - AttrNumber *sortColIdx, Oid *sortOperators) + AttrNumber *sortColIdx, Oid *sortOperators, bool *nullsFirst) { Sort *node = makeNode(Sort); Plan *plan = &node->plan; @@ -2383,6 +2386,7 @@ make_sort(PlannerInfo *root, Plan *lefttree, int numCols, node->numCols = numCols; node->sortColIdx = sortColIdx; node->sortOperators = sortOperators; + node->nullsFirst = nullsFirst; return node; } @@ -2397,14 +2401,23 @@ make_sort(PlannerInfo *root, Plan *lefttree, int numCols, * max possible number of columns. Return value is the new column count. */ static int -add_sort_column(AttrNumber colIdx, Oid sortOp, - int numCols, AttrNumber *sortColIdx, Oid *sortOperators) +add_sort_column(AttrNumber colIdx, Oid sortOp, bool nulls_first, + int numCols, AttrNumber *sortColIdx, + Oid *sortOperators, bool *nullsFirst) { int i; for (i = 0; i < numCols; i++) { - if (sortColIdx[i] == colIdx) + /* + * Note: we check sortOp because it's conceivable that "ORDER BY + * foo USING <, foo USING <<<" is not redundant, if <<< distinguishes + * values that < considers equal. We need not check nulls_first + * however because a lower-order column with the same sortop but + * opposite nulls direction is redundant. + */ + if (sortColIdx[i] == colIdx && + sortOperators[numCols] == sortOp) { /* Already sorting by this col, so extra sort key is useless */ return numCols; @@ -2414,6 +2427,7 @@ add_sort_column(AttrNumber colIdx, Oid sortOp, /* Add the column */ sortColIdx[numCols] = colIdx; sortOperators[numCols] = sortOp; + nullsFirst[numCols] = nulls_first; return numCols + 1; } @@ -2441,6 +2455,7 @@ make_sort_from_pathkeys(PlannerInfo *root, Plan *lefttree, List *pathkeys) int numsortkeys; AttrNumber *sortColIdx; Oid *sortOperators; + bool *nullsFirst; /* * We will need at most list_length(pathkeys) sort columns; possibly less @@ -2448,6 +2463,7 @@ make_sort_from_pathkeys(PlannerInfo *root, Plan *lefttree, List *pathkeys) numsortkeys = list_length(pathkeys); sortColIdx = (AttrNumber *) palloc(numsortkeys * sizeof(AttrNumber)); sortOperators = (Oid *) palloc(numsortkeys * sizeof(Oid)); + nullsFirst = (bool *) palloc(numsortkeys * sizeof(bool)); numsortkeys = 0; @@ -2527,13 +2543,15 @@ make_sort_from_pathkeys(PlannerInfo *root, Plan *lefttree, List *pathkeys) * So enter it only once in the sort arrays. */ numsortkeys = add_sort_column(tle->resno, pathkey->sortop, - numsortkeys, sortColIdx, sortOperators); + pathkey->nulls_first, + numsortkeys, + sortColIdx, sortOperators, nullsFirst); } Assert(numsortkeys > 0); return make_sort(root, lefttree, numsortkeys, - sortColIdx, sortOperators); + sortColIdx, sortOperators, nullsFirst); } /* @@ -2551,6 +2569,7 @@ make_sort_from_sortclauses(PlannerInfo *root, List *sortcls, Plan *lefttree) int numsortkeys; AttrNumber *sortColIdx; Oid *sortOperators; + bool *nullsFirst; /* * We will need at most list_length(sortcls) sort columns; possibly less @@ -2558,6 +2577,7 @@ make_sort_from_sortclauses(PlannerInfo *root, List *sortcls, Plan *lefttree) numsortkeys = list_length(sortcls); sortColIdx = (AttrNumber *) palloc(numsortkeys * sizeof(AttrNumber)); sortOperators = (Oid *) palloc(numsortkeys * sizeof(Oid)); + nullsFirst = (bool *) palloc(numsortkeys * sizeof(bool)); numsortkeys = 0; @@ -2572,13 +2592,15 @@ make_sort_from_sortclauses(PlannerInfo *root, List *sortcls, Plan *lefttree) * redundantly. */ numsortkeys = add_sort_column(tle->resno, sortcl->sortop, - numsortkeys, sortColIdx, sortOperators); + sortcl->nulls_first, + numsortkeys, + sortColIdx, sortOperators, nullsFirst); } Assert(numsortkeys > 0); return make_sort(root, lefttree, numsortkeys, - sortColIdx, sortOperators); + sortColIdx, sortOperators, nullsFirst); } /* @@ -2591,8 +2613,8 @@ make_sort_from_sortclauses(PlannerInfo *root, List *sortcls, Plan *lefttree) * This might look like it could be merged with make_sort_from_sortclauses, * but presently we *must* use the grpColIdx[] array to locate sort columns, * because the child plan's tlist is not marked with ressortgroupref info - * appropriate to the grouping node. So, only the sortop is used from the - * GroupClause entries. + * appropriate to the grouping node. So, only the sort direction info + * is used from the GroupClause entries. */ Sort * make_sort_from_groupcols(PlannerInfo *root, @@ -2606,6 +2628,7 @@ make_sort_from_groupcols(PlannerInfo *root, int numsortkeys; AttrNumber *sortColIdx; Oid *sortOperators; + bool *nullsFirst; /* * We will need at most list_length(groupcls) sort columns; possibly less @@ -2613,6 +2636,7 @@ make_sort_from_groupcols(PlannerInfo *root, numsortkeys = list_length(groupcls); sortColIdx = (AttrNumber *) palloc(numsortkeys * sizeof(AttrNumber)); sortOperators = (Oid *) palloc(numsortkeys * sizeof(Oid)); + nullsFirst = (bool *) palloc(numsortkeys * sizeof(bool)); numsortkeys = 0; @@ -2627,14 +2651,16 @@ make_sort_from_groupcols(PlannerInfo *root, * redundantly. */ numsortkeys = add_sort_column(tle->resno, grpcl->sortop, - numsortkeys, sortColIdx, sortOperators); + grpcl->nulls_first, + numsortkeys, + sortColIdx, sortOperators, nullsFirst); grpno++; } Assert(numsortkeys > 0); return make_sort(root, lefttree, numsortkeys, - sortColIdx, sortOperators); + sortColIdx, sortOperators, nullsFirst); } Material * @@ -2871,7 +2897,6 @@ make_unique(Plan *lefttree, List *distinctList) * distinctList is a list of SortClauses, identifying the targetlist items * that should be considered by the SetOp filter. */ - SetOp * make_setop(SetOpCmd cmd, Plan *lefttree, List *distinctList, AttrNumber flagColIdx) diff --git a/src/backend/optimizer/plan/planagg.c b/src/backend/optimizer/plan/planagg.c index fa980bb915..bce3b1ac44 100644 --- a/src/backend/optimizer/plan/planagg.c +++ b/src/backend/optimizer/plan/planagg.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/optimizer/plan/planagg.c,v 1.24 2007/01/05 22:19:32 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/optimizer/plan/planagg.c,v 1.25 2007/01/09 02:14:13 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -37,6 +37,7 @@ typedef struct Expr *target; /* expression we are aggregating on */ IndexPath *path; /* access path for index scan */ Cost pathcost; /* estimated cost to fetch first row */ + bool nulls_first; /* null ordering direction matching index */ Param *param; /* param for subplan's output */ } MinMaxAggInfo; @@ -277,6 +278,7 @@ build_minmax_path(PlannerInfo *root, RelOptInfo *rel, MinMaxAggInfo *info) { IndexPath *best_path = NULL; Cost best_cost = 0; + bool best_nulls_first = false; ListCell *l; foreach(l, rel->indexlist) @@ -377,11 +379,16 @@ build_minmax_path(PlannerInfo *root, RelOptInfo *rel, MinMaxAggInfo *info) { best_path = new_path; best_cost = new_cost; + if (ScanDirectionIsForward(indexscandir)) + best_nulls_first = index->nulls_first[indexcol]; + else + best_nulls_first = !index->nulls_first[indexcol]; } } info->path = best_path; info->pathcost = best_cost; + info->nulls_first = best_nulls_first; return (best_path != NULL); } @@ -390,29 +397,30 @@ build_minmax_path(PlannerInfo *root, RelOptInfo *rel, MinMaxAggInfo *info) * Does an aggregate match an index column? * * It matches if its argument is equal to the index column's data and its - * sortop is either a LessThan or GreaterThan member of the column's opfamily. + * sortop is either the forward or reverse sort operator for the column. * - * We return ForwardScanDirection if match a LessThan member, - * BackwardScanDirection if match a GreaterThan member, + * We return ForwardScanDirection if match the forward sort operator, + * BackwardScanDirection if match the reverse sort operator, * and NoMovementScanDirection if there's no match. */ static ScanDirection match_agg_to_index_col(MinMaxAggInfo *info, IndexOptInfo *index, int indexcol) { - int strategy; + ScanDirection result; + + /* Check for operator match first (cheaper) */ + if (info->aggsortop == index->fwdsortop[indexcol]) + result = ForwardScanDirection; + else if (info->aggsortop == index->revsortop[indexcol]) + result = BackwardScanDirection; + else + return NoMovementScanDirection; /* Check for data match */ if (!match_index_to_operand((Node *) info->target, indexcol, index)) return NoMovementScanDirection; - /* Look up the operator in the opfamily */ - strategy = get_op_opfamily_strategy(info->aggsortop, - index->opfamily[indexcol]); - if (strategy == BTLessStrategyNumber) - return ForwardScanDirection; - if (strategy == BTGreaterStrategyNumber) - return BackwardScanDirection; - return NoMovementScanDirection; + return result; } /* @@ -458,6 +466,7 @@ make_agg_subplan(PlannerInfo *root, MinMaxAggInfo *info) sortcl = makeNode(SortClause); sortcl->tleSortGroupRef = assignSortGroupRef(tle, subparse->targetList); sortcl->sortop = info->aggsortop; + sortcl->nulls_first = info->nulls_first; subparse->sortClause = list_make1(sortcl); /* set up LIMIT 1 */ diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index d1ac0740c6..3250beafb6 100644 --- a/src/backend/optimizer/util/clauses.c +++ b/src/backend/optimizer/util/clauses.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/optimizer/util/clauses.c,v 1.227 2007/01/05 22:19:32 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/optimizer/util/clauses.c,v 1.228 2007/01/09 02:14:13 tgl Exp $ * * HISTORY * AUTHOR DATE MAJOR EVENT @@ -1147,7 +1147,7 @@ has_distinct_on_clause(Query *query) continue; /* we can ignore unsorted junk cols */ return true; /* definitely not in DISTINCT list */ } - if (targetIsInSortList(tle, query->distinctClause)) + if (targetIsInSortList(tle, InvalidOid, query->distinctClause)) { if (tle->resjunk) return true; /* junk TLE in DISTINCT means DISTINCT ON */ @@ -1158,7 +1158,7 @@ has_distinct_on_clause(Query *query) /* This TLE is not in DISTINCT list */ if (!tle->resjunk) return true; /* non-junk, non-DISTINCT, so DISTINCT ON */ - if (targetIsInSortList(tle, query->sortClause)) + if (targetIsInSortList(tle, InvalidOid, query->sortClause)) return true; /* sorted, non-distinct junk */ /* unsorted junk is okay, keep looking */ } diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c index 353f2debe4..9150f1d936 100644 --- a/src/backend/optimizer/util/plancat.c +++ b/src/backend/optimizer/util/plancat.c @@ -9,7 +9,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/optimizer/util/plancat.c,v 1.130 2007/01/05 22:19:33 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/optimizer/util/plancat.c,v 1.131 2007/01/09 02:14:13 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -142,7 +142,6 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent, IndexOptInfo *info; int ncolumns; int i; - int16 amorderstrategy; /* * Extract info from the relation descriptor for the index. @@ -169,12 +168,15 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent, info->ncolumns = ncolumns = index->indnatts; /* - * Need to make opfamily and ordering arrays large enough to put - * a terminating 0 at the end of each one. + * Need to make opfamily array large enough to put a terminating + * zero at the end. */ info->indexkeys = (int *) palloc(sizeof(int) * ncolumns); info->opfamily = (Oid *) palloc0(sizeof(Oid) * (ncolumns + 1)); - info->ordering = (Oid *) palloc0(sizeof(Oid) * (ncolumns + 1)); + /* initialize these to zeroes in case index is unordered */ + info->fwdsortop = (Oid *) palloc0(sizeof(Oid) * ncolumns); + info->revsortop = (Oid *) palloc0(sizeof(Oid) * ncolumns); + info->nulls_first = (bool *) palloc0(sizeof(bool) * ncolumns); for (i = 0; i < ncolumns; i++) { @@ -189,22 +191,42 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent, /* * Fetch the ordering operators associated with the index, if any. */ - amorderstrategy = indexRelation->rd_am->amorderstrategy; - if (amorderstrategy > 0) + if (indexRelation->rd_am->amorderstrategy > 0) { - int oprindex = amorderstrategy - 1; - - /* - * Index AM must have a fixed set of strategies for it to - * make sense to specify amorderstrategy, so we need not - * allow the case amstrategies == 0. - */ - Assert(oprindex < indexRelation->rd_am->amstrategies); + int nstrat = indexRelation->rd_am->amstrategies; for (i = 0; i < ncolumns; i++) { - info->ordering[i] = indexRelation->rd_operator[oprindex]; - oprindex += indexRelation->rd_am->amstrategies; + int16 opt = indexRelation->rd_indoption[i]; + int fwdstrat; + int revstrat; + + if (opt & INDOPTION_DESC) + { + fwdstrat = indexRelation->rd_am->amdescorder; + revstrat = indexRelation->rd_am->amorderstrategy; + } + else + { + fwdstrat = indexRelation->rd_am->amorderstrategy; + revstrat = indexRelation->rd_am->amdescorder; + } + /* + * Index AM must have a fixed set of strategies for it + * to make sense to specify amorderstrategy, so we + * need not allow the case amstrategies == 0. + */ + if (fwdstrat > 0) + { + Assert(fwdstrat <= nstrat); + info->fwdsortop[i] = indexRelation->rd_operator[i * nstrat + fwdstrat - 1]; + } + if (revstrat > 0) + { + Assert(revstrat <= nstrat); + info->revsortop[i] = indexRelation->rd_operator[i * nstrat + revstrat - 1]; + } + info->nulls_first[i] = (opt & INDOPTION_NULLS_FIRST) != 0; } } diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 270332812b..f4b566cb6d 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -6,7 +6,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/backend/parser/analyze.c,v 1.354 2007/01/05 22:19:33 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/parser/analyze.c,v 1.355 2007/01/09 02:14:13 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -1629,6 +1629,8 @@ transformIndexConstraints(ParseState *pstate, CreateStmtContext *cxt) iparam->name = pstrdup(key); iparam->expr = NULL; iparam->opclass = NIL; + iparam->ordering = SORTBY_DEFAULT; + iparam->nulls_ordering = SORTBY_NULLS_DEFAULT; index->indexParams = lappend(index->indexParams, iparam); } diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index e3eae427de..6abe0d6795 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -11,7 +11,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.572 2007/01/05 22:19:33 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.573 2007/01/09 02:14:14 tgl Exp $ * * HISTORY * AUTHOR DATE MAJOR EVENT @@ -175,7 +175,7 @@ static Node *makeXmlExpr(XmlExprOp op, char *name, List *named_args, List *args) simple_select values_clause %type alter_column_default opclass_item alter_using -%type add_drop +%type add_drop opt_asc_desc opt_nulls_order %type alter_table_cmd alter_rel_cmd %type alter_table_cmds alter_rel_cmds @@ -397,7 +397,7 @@ static Node *makeXmlExpr(XmlExprOp op, char *name, List *named_args, List *args) KEY - LANCOMPILER LANGUAGE LARGE_P LAST_P LEADING LEAST LEFT LEVEL + LANCOMPILER LANGUAGE LARGE_P LAST_P LEADING LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION LOCK_P LOGIN_P @@ -405,7 +405,7 @@ static Node *makeXmlExpr(XmlExprOp op, char *name, List *named_args, List *args) NAME_P NAMES NATIONAL NATURAL NCHAR NEW NEXT NO NOCREATEDB NOCREATEROLE NOCREATEUSER NOINHERIT NOLOGIN_P NONE NOSUPERUSER - NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF NUMERIC + NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF NULLS_P NUMERIC OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OR ORDER OUT_P OUTER_P OVERLAPS OVERLAY OWNED OWNER @@ -449,7 +449,7 @@ static Node *makeXmlExpr(XmlExprOp op, char *name, List *named_args, List *args) * list and so can never be entered directly. The filter in parser.c * creates these tokens when required. */ -%token WITH_CASCADED WITH_LOCAL WITH_CHECK +%token NULLS_FIRST NULLS_LAST WITH_CASCADED WITH_LOCAL WITH_CHECK /* Special token types, not actually keywords - see the "lex" file */ %token IDENT FCONST SCONST BCONST XCONST Op @@ -3712,26 +3712,32 @@ index_params: index_elem { $$ = list_make1($1); } * expressions in parens. For backwards-compatibility reasons, we allow * an expression that's just a function call to be written without parens. */ -index_elem: ColId opt_class +index_elem: ColId opt_class opt_asc_desc opt_nulls_order { $$ = makeNode(IndexElem); $$->name = $1; $$->expr = NULL; $$->opclass = $2; + $$->ordering = $3; + $$->nulls_ordering = $4; } - | func_expr opt_class + | func_expr opt_class opt_asc_desc opt_nulls_order { $$ = makeNode(IndexElem); $$->name = NULL; $$->expr = $1; $$->opclass = $2; + $$->ordering = $3; + $$->nulls_ordering = $4; } - | '(' a_expr ')' opt_class + | '(' a_expr ')' opt_class opt_asc_desc opt_nulls_order { $$ = makeNode(IndexElem); $$->name = NULL; $$->expr = $2; $$->opclass = $4; + $$->ordering = $5; + $$->nulls_ordering = $6; } ; @@ -3740,6 +3746,17 @@ opt_class: any_name { $$ = $1; } | /*EMPTY*/ { $$ = NIL; } ; +opt_asc_desc: ASC { $$ = SORTBY_ASC; } + | DESC { $$ = SORTBY_DESC; } + | /*EMPTY*/ { $$ = SORTBY_DEFAULT; } + ; + +opt_nulls_order: NULLS_FIRST { $$ = SORTBY_NULLS_FIRST; } + | NULLS_LAST { $$ = SORTBY_NULLS_LAST; } + | /*EMPTY*/ { $$ = SORTBY_NULLS_DEFAULT; } + ; + + /***************************************************************************** * * QUERY: @@ -5810,32 +5827,36 @@ sortby_list: | sortby_list ',' sortby { $$ = lappend($1, $3); } ; -sortby: a_expr USING qual_all_Op +sortby: a_expr USING qual_all_Op opt_nulls_order { $$ = makeNode(SortBy); $$->node = $1; - $$->sortby_kind = SORTBY_USING; + $$->sortby_dir = SORTBY_USING; + $$->sortby_nulls = $4; $$->useOp = $3; } - | a_expr ASC + | a_expr ASC opt_nulls_order { $$ = makeNode(SortBy); $$->node = $1; - $$->sortby_kind = SORTBY_ASC; + $$->sortby_dir = SORTBY_ASC; + $$->sortby_nulls = $3; $$->useOp = NIL; } - | a_expr DESC + | a_expr DESC opt_nulls_order { $$ = makeNode(SortBy); $$->node = $1; - $$->sortby_kind = SORTBY_DESC; + $$->sortby_dir = SORTBY_DESC; + $$->sortby_nulls = $3; $$->useOp = NIL; } - | a_expr + | a_expr opt_nulls_order { $$ = makeNode(SortBy); $$->node = $1; - $$->sortby_kind = SORTBY_ASC; /* default */ + $$->sortby_dir = SORTBY_DEFAULT; + $$->sortby_nulls = $2; $$->useOp = NIL; } ; @@ -8613,6 +8634,7 @@ unreserved_keyword: | NOTHING | NOTIFY | NOWAIT + | NULLS_P | OBJECT_P | OF | OIDS diff --git a/src/backend/parser/keywords.c b/src/backend/parser/keywords.c index e6a4f9a7eb..e1592736f0 100644 --- a/src/backend/parser/keywords.c +++ b/src/backend/parser/keywords.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/parser/keywords.c,v 1.180 2007/01/05 22:19:33 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/parser/keywords.c,v 1.181 2007/01/09 02:14:14 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -242,6 +242,7 @@ static const ScanKeyword ScanKeywords[] = { {"nowait", NOWAIT}, {"null", NULL_P}, {"nullif", NULLIF}, + {"nulls", NULLS_P}, {"numeric", NUMERIC}, {"object", OBJECT_P}, {"of", OF}, diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c index 663273df48..6db3fce837 100644 --- a/src/backend/parser/parse_clause.c +++ b/src/backend/parser/parse_clause.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/parser/parse_clause.c,v 1.161 2007/01/05 22:19:33 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/parser/parse_clause.c,v 1.162 2007/01/09 02:14:14 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -33,6 +33,7 @@ #include "parser/parse_target.h" #include "rewrite/rewriteManip.h" #include "utils/guc.h" +#include "utils/lsyscache.h" #define ORDER_CLAUSE 0 @@ -1305,13 +1306,15 @@ findTargetlistEntry(ParseState *pstate, Node *node, List **tlist, int clause) } static GroupClause * -make_group_clause(TargetEntry *tle, List *targetlist, Oid sortop) +make_group_clause(TargetEntry *tle, List *targetlist, + Oid sortop, bool nulls_first) { GroupClause *result; result = makeNode(GroupClause); result->tleSortGroupRef = assignSortGroupRef(tle, targetlist); result->sortop = sortop; + result->nulls_first = nulls_first; return result; } @@ -1380,8 +1383,9 @@ transformGroupClause(ParseState *pstate, List *grouplist, tle_list = list_delete_cell(tle_list, tl, prev); - /* Use the sort clause's sorting operator */ - gc = make_group_clause(tle, *targetlist, sc->sortop); + /* Use the sort clause's sorting information */ + gc = make_group_clause(tle, *targetlist, + sc->sortop, sc->nulls_first); result = lappend(result, gc); found = true; break; @@ -1408,12 +1412,18 @@ transformGroupClause(ParseState *pstate, List *grouplist, GroupClause *gc; Oid sort_op; - /* avoid making duplicate grouplist entries */ - if (targetIsInSortList(tle, result)) + /* + * Avoid making duplicate grouplist entries. Note that we don't + * enforce a particular sortop here. Along with the copying of sort + * information above, this means that if you write something like + * "GROUP BY foo ORDER BY foo USING <<<", the GROUP BY operation + * silently takes on the equality semantics implied by the ORDER BY. + */ + if (targetIsInSortList(tle, InvalidOid, result)) continue; sort_op = ordering_oper_opid(exprType((Node *) tle->expr)); - gc = make_group_clause(tle, *targetlist, sort_op); + gc = make_group_clause(tle, *targetlist, sort_op, false); result = lappend(result, gc); } @@ -1447,7 +1457,8 @@ transformSortClause(ParseState *pstate, sortlist = addTargetToSortList(pstate, tle, sortlist, *targetlist, - sortby->sortby_kind, + sortby->sortby_dir, + sortby->sortby_nulls, sortby->useOp, resolveUnknown); } @@ -1553,7 +1564,9 @@ transformDistinctClause(ParseState *pstate, List *distinctlist, { *sortClause = addTargetToSortList(pstate, tle, *sortClause, *targetlist, - SORTBY_ASC, NIL, true); + SORTBY_DEFAULT, + SORTBY_NULLS_DEFAULT, + NIL, true); /* * Probably, the tle should always have been added at the end @@ -1601,8 +1614,9 @@ addAllTargetsToSortList(ParseState *pstate, List *sortlist, if (!tle->resjunk) sortlist = addTargetToSortList(pstate, tle, sortlist, targetlist, - SORTBY_ASC, NIL, - resolveUnknown); + SORTBY_DEFAULT, + SORTBY_NULLS_DEFAULT, + NIL, resolveUnknown); } return sortlist; } @@ -1610,8 +1624,7 @@ addAllTargetsToSortList(ParseState *pstate, List *sortlist, /* * addTargetToSortList * If the given targetlist entry isn't already in the ORDER BY list, - * add it to the end of the list, using the sortop with given name - * or the default sort operator if opname == NIL. + * add it to the end of the list, using the given sort ordering info. * * If resolveUnknown is TRUE, convert TLEs of type UNKNOWN to TEXT. If not, * do nothing (which implies the search for a sort operator will fail). @@ -1623,49 +1636,89 @@ addAllTargetsToSortList(ParseState *pstate, List *sortlist, List * addTargetToSortList(ParseState *pstate, TargetEntry *tle, List *sortlist, List *targetlist, - int sortby_kind, List *sortby_opname, - bool resolveUnknown) + SortByDir sortby_dir, SortByNulls sortby_nulls, + List *sortby_opname, bool resolveUnknown) { + Oid restype = exprType((Node *) tle->expr); + Oid sortop; + Oid cmpfunc; + bool reverse; + + /* if tlist item is an UNKNOWN literal, change it to TEXT */ + if (restype == UNKNOWNOID && resolveUnknown) + { + tle->expr = (Expr *) coerce_type(pstate, (Node *) tle->expr, + restype, TEXTOID, -1, + COERCION_IMPLICIT, + COERCE_IMPLICIT_CAST); + restype = TEXTOID; + } + + /* determine the sortop */ + switch (sortby_dir) + { + case SORTBY_DEFAULT: + case SORTBY_ASC: + sortop = ordering_oper_opid(restype); + reverse = false; + break; + case SORTBY_DESC: + sortop = reverse_ordering_oper_opid(restype); + reverse = true; + break; + case SORTBY_USING: + Assert(sortby_opname != NIL); + sortop = compatible_oper_opid(sortby_opname, + restype, + restype, + false); + /* + * Verify it's a valid ordering operator, and determine + * whether to consider it like ASC or DESC. + */ + if (!get_op_compare_function(sortop, &cmpfunc, &reverse)) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("operator %s is not a valid ordering operator", + strVal(llast(sortby_opname))), + errhint("Ordering operators must be \"<\" or \">\" members of btree operator families."))); + break; + default: + elog(ERROR, "unrecognized sortby_dir: %d", sortby_dir); + sortop = InvalidOid; /* keep compiler quiet */ + reverse = false; + break; + } + /* avoid making duplicate sortlist entries */ - if (!targetIsInSortList(tle, sortlist)) + if (!targetIsInSortList(tle, sortop, sortlist)) { SortClause *sortcl = makeNode(SortClause); - Oid restype = exprType((Node *) tle->expr); - - /* if tlist item is an UNKNOWN literal, change it to TEXT */ - if (restype == UNKNOWNOID && resolveUnknown) - { - tle->expr = (Expr *) coerce_type(pstate, (Node *) tle->expr, - restype, TEXTOID, -1, - COERCION_IMPLICIT, - COERCE_IMPLICIT_CAST); - restype = TEXTOID; - } sortcl->tleSortGroupRef = assignSortGroupRef(tle, targetlist); - switch (sortby_kind) + sortcl->sortop = sortop; + + switch (sortby_nulls) { - case SORTBY_ASC: - sortcl->sortop = ordering_oper_opid(restype); + case SORTBY_NULLS_DEFAULT: + /* NULLS FIRST is default for DESC; other way for ASC */ + sortcl->nulls_first = reverse; break; - case SORTBY_DESC: - sortcl->sortop = reverse_ordering_oper_opid(restype); + case SORTBY_NULLS_FIRST: + sortcl->nulls_first = true; break; - case SORTBY_USING: - Assert(sortby_opname != NIL); - sortcl->sortop = compatible_oper_opid(sortby_opname, - restype, - restype, - false); + case SORTBY_NULLS_LAST: + sortcl->nulls_first = false; break; default: - elog(ERROR, "unrecognized sortby_kind: %d", sortby_kind); + elog(ERROR, "unrecognized sortby_nulls: %d", sortby_nulls); break; } sortlist = lappend(sortlist, sortcl); } + return sortlist; } @@ -1701,13 +1754,23 @@ assignSortGroupRef(TargetEntry *tle, List *tlist) /* * targetIsInSortList * Is the given target item already in the sortlist? + * If sortop is not InvalidOid, also test for a match to the sortop. + * + * It is not an oversight that this function ignores the nulls_first flag. + * We check sortop when determining if an ORDER BY item is redundant with + * earlier ORDER BY items, because it's conceivable that "ORDER BY + * foo USING <, foo USING <<<" is not redundant, if <<< distinguishes + * values that < considers equal. We need not check nulls_first + * however, because a lower-order column with the same sortop but + * opposite nulls direction is redundant. Also, we can consider + * ORDER BY foo ASC, foo DESC redundant, so check for a commutator match. * * Works for both SortClause and GroupClause lists. Note that the main * reason we need this routine (and not just a quick test for nonzeroness * of ressortgroupref) is that a TLE might be in only one of the lists. */ bool -targetIsInSortList(TargetEntry *tle, List *sortList) +targetIsInSortList(TargetEntry *tle, Oid sortop, List *sortList) { Index ref = tle->ressortgroupref; ListCell *l; @@ -1720,7 +1783,10 @@ targetIsInSortList(TargetEntry *tle, List *sortList) { SortClause *scl = (SortClause *) lfirst(l); - if (scl->tleSortGroupRef == ref) + if (scl->tleSortGroupRef == ref && + (sortop == InvalidOid || + sortop == scl->sortop || + sortop == get_commutator(scl->sortop))) return true; } return false; diff --git a/src/backend/parser/parser.c b/src/backend/parser/parser.c index c007613cc4..b9c0b9a985 100644 --- a/src/backend/parser/parser.c +++ b/src/backend/parser/parser.c @@ -14,7 +14,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/parser/parser.c,v 1.70 2007/01/06 19:14:17 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/parser/parser.c,v 1.71 2007/01/09 02:14:14 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -97,8 +97,35 @@ filtered_base_yylex(void) /* Do we need to look ahead for a possible multiword token? */ switch (cur_token) { - case WITH: + case NULLS_P: + /* + * NULLS FIRST and NULLS LAST must be reduced to one token + */ + cur_yylval = base_yylval; + cur_yylloc = base_yylloc; + next_token = base_yylex(); + switch (next_token) + { + case FIRST_P: + cur_token = NULLS_FIRST; + break; + case LAST_P: + cur_token = NULLS_LAST; + break; + default: + /* save the lookahead token for next time */ + lookahead_token = next_token; + lookahead_yylval = base_yylval; + lookahead_yylloc = base_yylloc; + have_lookahead = true; + /* and back up the output info to cur_token */ + base_yylval = cur_yylval; + base_yylloc = cur_yylloc; + break; + } + break; + case WITH: /* * WITH CASCADED, LOCAL, or CHECK must be reduced to one token * diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 34d7b66743..3c217c98ed 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -2,7 +2,7 @@ * ruleutils.c - Functions to convert stored expressions/querytrees * back to source text * - * $PostgreSQL: pgsql/src/backend/utils/adt/ruleutils.c,v 1.240 2006/12/29 16:44:28 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/utils/adt/ruleutils.c,v 1.241 2007/01/09 02:14:14 tgl Exp $ **********************************************************************/ #include "postgres.h" @@ -615,8 +615,10 @@ pg_get_indexdef_worker(Oid indexrelid, int colno, int prettyFlags) int keyno; Oid keycoltype; Datum indclassDatum; + Datum indoptionDatum; bool isnull; oidvector *indclass; + int2vector *indoption; StringInfoData buf; char *str; char *sep; @@ -634,11 +636,15 @@ pg_get_indexdef_worker(Oid indexrelid, int colno, int prettyFlags) indrelid = idxrec->indrelid; Assert(indexrelid == idxrec->indexrelid); - /* Must get indclass the hard way */ + /* Must get indclass and indoption the hard way */ indclassDatum = SysCacheGetAttr(INDEXRELID, ht_idx, Anum_pg_index_indclass, &isnull); Assert(!isnull); indclass = (oidvector *) DatumGetPointer(indclassDatum); + indoptionDatum = SysCacheGetAttr(INDEXRELID, ht_idx, + Anum_pg_index_indoption, &isnull); + Assert(!isnull); + indoption = (int2vector *) DatumGetPointer(indoptionDatum); /* * Fetch the pg_class tuple of the index relation @@ -707,6 +713,7 @@ pg_get_indexdef_worker(Oid indexrelid, int colno, int prettyFlags) for (keyno = 0; keyno < idxrec->indnatts; keyno++) { AttrNumber attnum = idxrec->indkey.values[keyno]; + int16 opt = indoption->values[keyno]; if (!colno) appendStringInfoString(&buf, sep); @@ -746,12 +753,28 @@ pg_get_indexdef_worker(Oid indexrelid, int colno, int prettyFlags) keycoltype = exprType(indexkey); } - /* - * Add the operator class name - */ + /* Add the operator class name */ if (!colno) get_opclass_name(indclass->values[keyno], keycoltype, &buf); + + /* Add options if relevant */ + if (amrec->amorderstrategy > 0) + { + /* if it supports sort ordering, report DESC and NULLS opts */ + if (opt & INDOPTION_DESC) + { + appendStringInfo(&buf, " DESC"); + /* NULLS FIRST is the default in this case */ + if (!(opt & INDOPTION_NULLS_FIRST)) + appendStringInfo(&buf, " NULLS LAST"); + } + else + { + if (opt & INDOPTION_NULLS_FIRST) + appendStringInfo(&buf, " NULLS FIRST"); + } + } } if (!colno) @@ -1905,14 +1928,30 @@ get_select_query_def(Query *query, deparse_context *context, typentry = lookup_type_cache(sortcoltype, TYPECACHE_LT_OPR | TYPECACHE_GT_OPR); if (srt->sortop == typentry->lt_opr) - /* ASC is default, so emit nothing */ ; + { + /* ASC is default, so emit nothing for it */ + if (srt->nulls_first) + appendStringInfo(buf, " NULLS FIRST"); + } else if (srt->sortop == typentry->gt_opr) + { appendStringInfo(buf, " DESC"); + /* DESC defaults to NULLS FIRST */ + if (!srt->nulls_first) + appendStringInfo(buf, " NULLS LAST"); + } else + { appendStringInfo(buf, " USING %s", generate_operator_name(srt->sortop, sortcoltype, sortcoltype)); + /* be specific to eliminate ambiguity */ + if (srt->nulls_first) + appendStringInfo(buf, " NULLS FIRST"); + else + appendStringInfo(buf, " NULLS LAST"); + } sep = ", "; } } diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c index 31cc62d68b..875c7c524a 100644 --- a/src/backend/utils/adt/selfuncs.c +++ b/src/backend/utils/adt/selfuncs.c @@ -15,7 +15,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/adt/selfuncs.c,v 1.218 2007/01/05 22:19:42 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/utils/adt/selfuncs.c,v 1.219 2007/01/09 02:14:14 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -5101,7 +5101,7 @@ btcostestimate(PG_FUNCTION_ARGS) if (get_attstatsslot(tuple, InvalidOid, 0, STATISTIC_KIND_CORRELATION, - index->ordering[0], + index->fwdsortop[0], NULL, NULL, &numbers, &nnumbers)) { double varCorrelation; @@ -5116,6 +5116,23 @@ btcostestimate(PG_FUNCTION_ARGS) free_attstatsslot(InvalidOid, NULL, 0, numbers, nnumbers); } + else if (get_attstatsslot(tuple, InvalidOid, 0, + STATISTIC_KIND_CORRELATION, + index->revsortop[0], + NULL, NULL, &numbers, &nnumbers)) + { + double varCorrelation; + + Assert(nnumbers == 1); + varCorrelation = numbers[0]; + + if (index->ncolumns > 1) + *indexCorrelation = - varCorrelation * 0.75; + else + *indexCorrelation = - varCorrelation; + + free_attstatsslot(InvalidOid, NULL, 0, numbers, nnumbers); + } ReleaseSysCache(tuple); } diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c index 82dbca9e11..6379e25812 100644 --- a/src/backend/utils/cache/lsyscache.c +++ b/src/backend/utils/cache/lsyscache.c @@ -7,7 +7,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/cache/lsyscache.c,v 1.141 2007/01/05 22:19:43 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/utils/cache/lsyscache.c,v 1.142 2007/01/09 02:14:14 tgl Exp $ * * NOTES * Eventually, the index information should go through here, too. @@ -16,6 +16,7 @@ #include "postgres.h" #include "access/hash.h" +#include "access/nbtree.h" #include "bootstrap/bootstrap.h" #include "catalog/pg_amop.h" #include "catalog/pg_amproc.h" @@ -192,7 +193,7 @@ get_op_mergejoin_info(Oid eq_op, Oid left_sortop, if (op_form->amopstrategy != BTEqualStrategyNumber) continue; - /* See if sort operator is also in this opclass with OK semantics */ + /* See if sort operator is also in this opfamily with OK semantics */ opfamily_id = op_form->amopfamily; op_strategy = get_op_opfamily_strategy(left_sortop, opfamily_id); if (op_strategy == BTLessStrategyNumber || @@ -284,6 +285,78 @@ get_op_mergejoin_info(Oid eq_op, Oid *left_sortop, } #endif +/* + * get_op_compare_function + * Get the OID of the datatype-specific btree comparison function + * associated with an ordering operator (a "<" or ">" operator). + * + * *cmpfunc receives the comparison function OID. + * *reverse is set FALSE if the operator is "<", TRUE if it's ">" + * (indicating the comparison result must be negated before use). + * + * Returns TRUE if successful, FALSE if no btree function can be found. + * (This indicates that the operator is not a valid ordering operator.) + */ +bool +get_op_compare_function(Oid opno, Oid *cmpfunc, bool *reverse) +{ + bool result = false; + CatCList *catlist; + int i; + + /* ensure outputs are set on failure */ + *cmpfunc = InvalidOid; + *reverse = false; + + /* + * Search pg_amop to see if the target operator is registered as the "<" + * or ">" operator of any btree opfamily. It's possible that it might be + * registered both ways (if someone were to build a "reverse sort" + * opfamily); assume we can use either interpretation. (Note: the + * existence of a reverse-sort opfamily would result in uncertainty as + * to whether "ORDER BY USING op" would default to NULLS FIRST or NULLS + * LAST. Since there is no longer any particularly good reason to build + * reverse-sort opfamilies, we don't bother expending any extra work to + * make this more determinate. In practice, because of the way the + * syscache search works, we'll use the interpretation associated with + * the opfamily with smallest OID, which is probably determinate enough.) + */ + catlist = SearchSysCacheList(AMOPOPID, 1, + ObjectIdGetDatum(opno), + 0, 0, 0); + + for (i = 0; i < catlist->n_members; i++) + { + HeapTuple tuple = &catlist->members[i]->tuple; + Form_pg_amop aform = (Form_pg_amop) GETSTRUCT(tuple); + + /* must be btree */ + if (aform->amopmethod != BTREE_AM_OID) + continue; + + if (aform->amopstrategy == BTLessStrategyNumber || + aform->amopstrategy == BTGreaterStrategyNumber) + { + /* Found a suitable opfamily, get matching support function */ + *reverse = (aform->amopstrategy == BTGreaterStrategyNumber); + *cmpfunc = get_opfamily_proc(aform->amopfamily, + aform->amoplefttype, + aform->amoprighttype, + BTORDER_PROC); + if (!OidIsValid(*cmpfunc)) /* should not happen */ + elog(ERROR, "missing support function %d(%u,%u) in opfamily %u", + BTORDER_PROC, aform->amoplefttype, aform->amoprighttype, + aform->amopfamily); + result = true; + break; + } + } + + ReleaseSysCacheList(catlist); + + return result; +} + /* * get_op_hash_function * Get the OID of the datatype-specific hash function associated with @@ -298,9 +371,9 @@ get_op_mergejoin_info(Oid eq_op, Oid *left_sortop, Oid get_op_hash_function(Oid opno) { + Oid result = InvalidOid; CatCList *catlist; int i; - Oid result = InvalidOid; /* * Search pg_amop to see if the target operator is registered as the "=" diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c index baa45447a2..c43846cd57 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/cache/relcache.c,v 1.253 2007/01/05 22:19:43 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/utils/cache/relcache.c,v 1.254 2007/01/09 02:14:15 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -925,8 +925,10 @@ RelationInitIndexAccessInfo(Relation relation) HeapTuple tuple; Form_pg_am aform; Datum indclassDatum; + Datum indoptionDatum; bool isnull; oidvector *indclass; + int2vector *indoption; MemoryContext indexcxt; MemoryContext oldcontext; int natts; @@ -1019,6 +1021,9 @@ RelationInitIndexAccessInfo(Relation relation) relation->rd_supportinfo = NULL; } + relation->rd_indoption = (int16 *) + MemoryContextAllocZero(indexcxt, natts * sizeof(int16)); + /* * indclass cannot be referenced directly through the C struct, because it * comes after the variable-width indkey field. Must extract the @@ -1041,6 +1046,17 @@ RelationInitIndexAccessInfo(Relation relation) relation->rd_opfamily, relation->rd_opcintype, amstrategies, amsupport, natts); + /* + * Similarly extract indoption and copy it to the cache entry + */ + indoptionDatum = fastgetattr(relation->rd_indextuple, + Anum_pg_index_indoption, + GetPgIndexDescriptor(), + &isnull); + Assert(!isnull); + indoption = (int2vector *) DatumGetPointer(indoptionDatum); + memcpy(relation->rd_indoption, indoption->values, natts * sizeof(int16)); + /* * expressions and predicate cache will be filled later */ @@ -3237,6 +3253,7 @@ load_relcache_init_file(void) Oid *operator; RegProcedure *support; int nsupport; + int16 *indoption; /* Count nailed indexes to ensure we have 'em all */ if (rel->rd_isnailed) @@ -3304,7 +3321,7 @@ load_relcache_init_file(void) rel->rd_operator = operator; - /* finally, read the vector of support procedures */ + /* next, read the vector of support procedures */ if ((nread = fread(&len, 1, sizeof(len), fp)) != sizeof(len)) goto read_failed; support = (RegProcedure *) MemoryContextAlloc(indexcxt, len); @@ -3313,6 +3330,16 @@ load_relcache_init_file(void) rel->rd_support = support; + /* finally, read the vector of indoption values */ + if ((nread = fread(&len, 1, sizeof(len), fp)) != sizeof(len)) + goto read_failed; + + indoption = (int16 *) MemoryContextAlloc(indexcxt, len); + if ((nread = fread(indoption, 1, len, fp)) != len) + goto read_failed; + + rel->rd_indoption = indoption; + /* set up zeroed fmgr-info vectors */ rel->rd_aminfo = (RelationAmInfo *) MemoryContextAllocZero(indexcxt, sizeof(RelationAmInfo)); @@ -3336,6 +3363,7 @@ load_relcache_init_file(void) Assert(rel->rd_operator == NULL); Assert(rel->rd_support == NULL); Assert(rel->rd_supportinfo == NULL); + Assert(rel->rd_indoption == NULL); } /* @@ -3525,10 +3553,15 @@ write_relcache_init_file(void) relform->relnatts * (am->amstrategies * sizeof(Oid)), fp); - /* finally, write the vector of support procedures */ + /* next, write the vector of support procedures */ write_item(rel->rd_support, relform->relnatts * (am->amsupport * sizeof(RegProcedure)), fp); + + /* finally, write the vector of indoption values */ + write_item(rel->rd_indoption, + relform->relnatts * sizeof(int16), + fp); } /* also make a list of their OIDs, for RelationIdIsInInitFile */ diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c index f410aedda2..63c2fec28e 100644 --- a/src/backend/utils/sort/tuplesort.c +++ b/src/backend/utils/sort/tuplesort.c @@ -91,7 +91,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/sort/tuplesort.c,v 1.72 2007/01/05 22:19:47 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/utils/sort/tuplesort.c,v 1.73 2007/01/09 02:14:15 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -315,7 +315,6 @@ struct Tuplesortstate */ TupleDesc tupDesc; ScanKey scanKeys; /* array of length nKeys */ - SortFunctionKind *sortFnKinds; /* array of length nKeys */ /* * These variables are specific to the IndexTuple case; they are set by @@ -330,9 +329,8 @@ struct Tuplesortstate * tuplesort_begin_datum and used only by the DatumTuple routines. */ Oid datumType; - Oid sortOperator; FmgrInfo sortOpFn; /* cached lookup data for sortOperator */ - SortFunctionKind sortFnKind; + int sortFnFlags; /* equivalent to sk_flags */ /* we need typelen and byval in order to know how to copy the Datums. */ int datumTypeLen; bool datumTypeByVal; @@ -515,8 +513,8 @@ tuplesort_begin_common(int workMem, bool randomAccess) Tuplesortstate * tuplesort_begin_heap(TupleDesc tupDesc, - int nkeys, - Oid *sortOperators, AttrNumber *attNums, + int nkeys, AttrNumber *attNums, + Oid *sortOperators, bool *nullsFirstFlags, int workMem, bool randomAccess) { Tuplesortstate *state = tuplesort_begin_common(workMem, randomAccess); @@ -543,19 +541,19 @@ tuplesort_begin_heap(TupleDesc tupDesc, state->tupDesc = tupDesc; /* assume we need not copy tupDesc */ state->scanKeys = (ScanKey) palloc0(nkeys * sizeof(ScanKeyData)); - state->sortFnKinds = (SortFunctionKind *) - palloc0(nkeys * sizeof(SortFunctionKind)); for (i = 0; i < nkeys; i++) { - RegProcedure sortFunction; + Oid sortFunction; + bool reverse; - AssertArg(sortOperators[i] != 0); AssertArg(attNums[i] != 0); + AssertArg(sortOperators[i] != 0); - /* select a function that implements the sort operator */ - SelectSortFunction(sortOperators[i], &sortFunction, - &state->sortFnKinds[i]); + if (!get_op_compare_function(sortOperators[i], + &sortFunction, &reverse)) + elog(ERROR, "operator %u is not a valid ordering operator", + sortOperators[i]); /* * We needn't fill in sk_strategy or sk_subtype since these scankeys @@ -566,6 +564,12 @@ tuplesort_begin_heap(TupleDesc tupDesc, InvalidStrategy, sortFunction, (Datum) 0); + + /* However, we use btree's conventions for encoding directionality */ + if (reverse) + state->scanKeys[i].sk_flags |= SK_BT_DESC; + if (nullsFirstFlags[i]) + state->scanKeys[i].sk_flags |= SK_BT_NULLS_FIRST; } MemoryContextSwitchTo(oldcontext); @@ -610,12 +614,13 @@ tuplesort_begin_index(Relation indexRel, Tuplesortstate * tuplesort_begin_datum(Oid datumType, - Oid sortOperator, + Oid sortOperator, bool nullsFirstFlag, int workMem, bool randomAccess) { Tuplesortstate *state = tuplesort_begin_common(workMem, randomAccess); MemoryContext oldcontext; - RegProcedure sortFunction; + Oid sortFunction; + bool reverse; int16 typlen; bool typbyval; @@ -636,13 +641,19 @@ tuplesort_begin_datum(Oid datumType, state->readtup = readtup_datum; state->datumType = datumType; - state->sortOperator = sortOperator; - /* select a function that implements the sort operator */ - SelectSortFunction(sortOperator, &sortFunction, &state->sortFnKind); - /* and look up the function */ + /* lookup the ordering function */ + if (!get_op_compare_function(sortOperator, + &sortFunction, &reverse)) + elog(ERROR, "operator %u is not a valid ordering operator", + sortOperator); fmgr_info(sortFunction, &state->sortOpFn); + /* set ordering flags */ + state->sortFnFlags = reverse ? SK_BT_DESC : 0; + if (nullsFirstFlag) + state->sortFnFlags |= SK_BT_NULLS_FIRST; + /* lookup necessary attributes of the datum type */ get_typlenbyval(datumType, &typlen, &typbyval); state->datumTypeLen = typlen; @@ -2083,106 +2094,26 @@ markrunend(Tuplesortstate *state, int tapenum) /* - * This routine selects an appropriate sorting function to implement - * a sort operator as efficiently as possible. The straightforward - * method is to use the operator's implementation proc --- ie, "<" - * comparison. However, that way often requires two calls of the function - * per comparison. If we can find a btree three-way comparator function - * associated with the operator, we can use it to do the comparisons - * more efficiently. We also support the possibility that the operator - * is ">" (descending sort), in which case we have to reverse the output - * of the btree comparator. - * - * Possibly this should live somewhere else (backend/catalog/, maybe?). + * Set up for an external caller of ApplySortFunction. This function + * basically just exists to localize knowledge of the encoding of sk_flags + * used in this module. */ void SelectSortFunction(Oid sortOperator, - RegProcedure *sortFunction, - SortFunctionKind *kind) + bool nulls_first, + Oid *sortFunction, + int *sortFlags) { - CatCList *catlist; - int i; - HeapTuple tuple; - Form_pg_operator optup; - Oid opfamily = InvalidOid; - Oid opinputtype = InvalidOid; + bool reverse; - /* - * Search pg_amop to see if the target operator is registered as a "<" - * or ">" operator of any btree opfamily. It's possible that it might be - * registered both ways (eg, if someone were to build a "reverse sort" - * opfamily); prefer the "<" case if so. If the operator is registered the - * same way in multiple opfamilies, assume we can use the associated - * comparator function from any one. - */ - catlist = SearchSysCacheList(AMOPOPID, 1, - ObjectIdGetDatum(sortOperator), - 0, 0, 0); - - for (i = 0; i < catlist->n_members; i++) - { - Form_pg_amop aform; + if (!get_op_compare_function(sortOperator, + sortFunction, &reverse)) + elog(ERROR, "operator %u is not a valid ordering operator", + sortOperator); - tuple = &catlist->members[i]->tuple; - aform = (Form_pg_amop) GETSTRUCT(tuple); - - /* must be btree */ - if (aform->amopmethod != BTREE_AM_OID) - continue; - /* mustn't be cross-datatype, either */ - if (aform->amoplefttype != aform->amoprighttype) - continue; - - if (aform->amopstrategy == BTLessStrategyNumber) - { - opfamily = aform->amopfamily; - opinputtype = aform->amoplefttype; - *kind = SORTFUNC_CMP; - break; /* done looking */ - } - else if (aform->amopstrategy == BTGreaterStrategyNumber) - { - opfamily = aform->amopfamily; - opinputtype = aform->amoplefttype; - *kind = SORTFUNC_REVCMP; - /* keep scanning in hopes of finding a BTLess entry */ - } - } - - ReleaseSysCacheList(catlist); - - if (OidIsValid(opfamily)) - { - /* Found a suitable opfamily, get the matching comparator function */ - *sortFunction = get_opfamily_proc(opfamily, - opinputtype, - opinputtype, - BTORDER_PROC); - Assert(RegProcedureIsValid(*sortFunction)); - return; - } - - /* - * Can't find a comparator, so use the operator as-is. Decide whether it - * is forward or reverse sort by looking at its name (grotty, but this - * only matters for deciding which end NULLs should get sorted to). XXX - * possibly better idea: see whether its selectivity function is - * scalargtcmp? - */ - tuple = SearchSysCache(OPEROID, - ObjectIdGetDatum(sortOperator), - 0, 0, 0); - if (!HeapTupleIsValid(tuple)) - elog(ERROR, "cache lookup failed for operator %u", sortOperator); - optup = (Form_pg_operator) GETSTRUCT(tuple); - if (strcmp(NameStr(optup->oprname), ">") == 0) - *kind = SORTFUNC_REVLT; - else - *kind = SORTFUNC_LT; - *sortFunction = optup->oprcode; - ReleaseSysCache(tuple); - - Assert(RegProcedureIsValid(*sortFunction)); + *sortFlags = reverse ? SK_BT_DESC : 0; + if (nulls_first) + *sortFlags |= SK_BT_NULLS_FIRST; } /* @@ -2213,74 +2144,42 @@ myFunctionCall2(FmgrInfo *flinfo, Datum arg1, Datum arg2) /* * Apply a sort function (by now converted to fmgr lookup form) * and return a 3-way comparison result. This takes care of handling - * NULLs and sort ordering direction properly. + * reverse-sort and NULLs-ordering properly. We assume that DESC and + * NULLS_FIRST options are encoded in sk_flags the same way btree does it. */ static inline int32 -inlineApplySortFunction(FmgrInfo *sortFunction, SortFunctionKind kind, +inlineApplySortFunction(FmgrInfo *sortFunction, int sk_flags, Datum datum1, bool isNull1, Datum datum2, bool isNull2) { - switch (kind) - { - case SORTFUNC_LT: - if (isNull1) - { - if (isNull2) - return 0; - return 1; /* NULL sorts after non-NULL */ - } - if (isNull2) - return -1; - if (DatumGetBool(myFunctionCall2(sortFunction, datum1, datum2))) - return -1; /* a < b */ - if (DatumGetBool(myFunctionCall2(sortFunction, datum2, datum1))) - return 1; /* a > b */ - return 0; - - case SORTFUNC_REVLT: - /* We reverse the ordering of NULLs, but not the operator */ - if (isNull1) - { - if (isNull2) - return 0; - return -1; /* NULL sorts before non-NULL */ - } - if (isNull2) - return 1; - if (DatumGetBool(myFunctionCall2(sortFunction, datum1, datum2))) - return -1; /* a < b */ - if (DatumGetBool(myFunctionCall2(sortFunction, datum2, datum1))) - return 1; /* a > b */ - return 0; - - case SORTFUNC_CMP: - if (isNull1) - { - if (isNull2) - return 0; - return 1; /* NULL sorts after non-NULL */ - } - if (isNull2) - return -1; - return DatumGetInt32(myFunctionCall2(sortFunction, - datum1, datum2)); + int32 compare; - case SORTFUNC_REVCMP: - if (isNull1) - { - if (isNull2) - return 0; - return -1; /* NULL sorts before non-NULL */ - } - if (isNull2) - return 1; - return -DatumGetInt32(myFunctionCall2(sortFunction, - datum1, datum2)); + if (isNull1) + { + if (isNull2) + compare = 0; /* NULL "=" NULL */ + else if (sk_flags & SK_BT_NULLS_FIRST) + compare = -1; /* NULL "<" NOT_NULL */ + else + compare = 1; /* NULL ">" NOT_NULL */ + } + else if (isNull2) + { + if (sk_flags & SK_BT_NULLS_FIRST) + compare = 1; /* NOT_NULL ">" NULL */ + else + compare = -1; /* NOT_NULL "<" NULL */ + } + else + { + compare = DatumGetInt32(myFunctionCall2(sortFunction, + datum1, datum2)); - default: - elog(ERROR, "unrecognized SortFunctionKind: %d", (int) kind); - return 0; /* can't get here, but keep compiler quiet */ + if (sk_flags & SK_BT_DESC) + compare = -compare; } + + return compare; } /* @@ -2288,11 +2187,11 @@ inlineApplySortFunction(FmgrInfo *sortFunction, SortFunctionKind kind, * C99's brain-dead notions about how to implement inline functions... */ int32 -ApplySortFunction(FmgrInfo *sortFunction, SortFunctionKind kind, +ApplySortFunction(FmgrInfo *sortFunction, int sortFlags, Datum datum1, bool isNull1, Datum datum2, bool isNull2) { - return inlineApplySortFunction(sortFunction, kind, + return inlineApplySortFunction(sortFunction, sortFlags, datum1, isNull1, datum2, isNull2); } @@ -2316,8 +2215,7 @@ comparetup_heap(const SortTuple *a, const SortTuple *b, Tuplesortstate *state) CHECK_FOR_INTERRUPTS(); /* Compare the leading sort key */ - compare = inlineApplySortFunction(&scanKey->sk_func, - state->sortFnKinds[0], + compare = inlineApplySortFunction(&scanKey->sk_func, scanKey->sk_flags, a->datum1, a->isnull1, b->datum1, b->isnull1); if (compare != 0) @@ -2341,8 +2239,7 @@ comparetup_heap(const SortTuple *a, const SortTuple *b, Tuplesortstate *state) datum1 = heap_getattr(<up, attno, tupDesc, &isnull1); datum2 = heap_getattr(&rtup, attno, tupDesc, &isnull2); - compare = inlineApplySortFunction(&scanKey->sk_func, - state->sortFnKinds[nkey], + compare = inlineApplySortFunction(&scanKey->sk_func, scanKey->sk_flags, datum1, isnull1, datum2, isnull2); if (compare != 0) @@ -2457,8 +2354,7 @@ comparetup_index(const SortTuple *a, const SortTuple *b, Tuplesortstate *state) CHECK_FOR_INTERRUPTS(); /* Compare the leading sort key */ - compare = inlineApplySortFunction(&scanKey->sk_func, - SORTFUNC_CMP, + compare = inlineApplySortFunction(&scanKey->sk_func, scanKey->sk_flags, a->datum1, a->isnull1, b->datum1, b->isnull1); if (compare != 0) @@ -2484,14 +2380,9 @@ comparetup_index(const SortTuple *a, const SortTuple *b, Tuplesortstate *state) datum1 = index_getattr(tuple1, nkey, tupDes, &isnull1); datum2 = index_getattr(tuple2, nkey, tupDes, &isnull2); - /* see comments about NULLs handling in btbuild */ - - /* the comparison function is always of CMP type */ - compare = inlineApplySortFunction(&scanKey->sk_func, - SORTFUNC_CMP, + compare = inlineApplySortFunction(&scanKey->sk_func, scanKey->sk_flags, datum1, isnull1, datum2, isnull2); - if (compare != 0) return compare; /* done when we find unequal attributes */ @@ -2617,7 +2508,7 @@ comparetup_datum(const SortTuple *a, const SortTuple *b, Tuplesortstate *state) /* Allow interrupting long sorts */ CHECK_FOR_INTERRUPTS(); - return inlineApplySortFunction(&state->sortOpFn, state->sortFnKind, + return inlineApplySortFunction(&state->sortOpFn, state->sortFnFlags, a->datum1, a->isnull1, b->datum1, b->isnull1); } diff --git a/src/include/access/nbtree.h b/src/include/access/nbtree.h index 991fc14588..435826cf45 100644 --- a/src/include/access/nbtree.h +++ b/src/include/access/nbtree.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/access/nbtree.h,v 1.107 2007/01/05 22:19:51 momjian Exp $ + * $PostgreSQL: pgsql/src/include/access/nbtree.h,v 1.108 2007/01/09 02:14:15 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -327,7 +327,11 @@ typedef struct xl_btree_newroot /* * Operator strategy numbers for B-tree have been moved to access/skey.h, * because many places need to use them in ScanKeyInit() calls. + * + * The strategy numbers are chosen so that we can commute them by + * subtraction, thus: */ +#define BTCommuteStrategyNumber(strat) (BTMaxStrategyNumber + 1 - (strat)) /* * When a new operator class is declared, we require that the user @@ -458,11 +462,15 @@ typedef struct BTScanOpaqueData typedef BTScanOpaqueData *BTScanOpaque; /* - * We use these private sk_flags bits in preprocessed scan keys + * We use some private sk_flags bits in preprocessed scan keys. We're allowed + * to use bits 16-31 (see skey.h). The uppermost bits are copied from the + * index's indoption[] array entry for the index attribute. */ #define SK_BT_REQFWD 0x00010000 /* required to continue forward scan */ #define SK_BT_REQBKWD 0x00020000 /* required to continue backward scan */ - +#define SK_BT_INDOPTION_SHIFT 24 /* must clear the above bits */ +#define SK_BT_DESC (INDOPTION_DESC << SK_BT_INDOPTION_SHIFT) +#define SK_BT_NULLS_FIRST (INDOPTION_NULLS_FIRST << SK_BT_INDOPTION_SHIFT) /* * prototypes for functions in nbtree.c (external entry points for btree) diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index fd67dfd27a..98a625cfbf 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -37,7 +37,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.370 2007/01/05 22:19:52 momjian Exp $ + * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.371 2007/01/09 02:14:15 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 200701021 +#define CATALOG_VERSION_NO 200701081 #endif diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h index 8848ba12d6..32c704b71e 100644 --- a/src/include/catalog/index.h +++ b/src/include/catalog/index.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/index.h,v 1.72 2007/01/05 22:19:52 momjian Exp $ + * $PostgreSQL: pgsql/src/include/catalog/index.h,v 1.73 2007/01/09 02:14:15 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -35,6 +35,7 @@ extern Oid index_create(Oid heapRelationId, Oid accessMethodObjectId, Oid tableSpaceId, Oid *classObjectId, + int16 *coloptions, Datum reloptions, bool isprimary, bool isconstraint, diff --git a/src/include/catalog/pg_am.h b/src/include/catalog/pg_am.h index a0dbc20f40..80aad73130 100644 --- a/src/include/catalog/pg_am.h +++ b/src/include/catalog/pg_am.h @@ -8,7 +8,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_am.h,v 1.48 2007/01/05 22:19:52 momjian Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_am.h,v 1.49 2007/01/09 02:14:15 tgl Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -39,15 +39,18 @@ CATALOG(pg_am,2601) { NameData amname; /* access method name */ - int2 amstrategies; /* total NUMBER of strategies (operators) by + int2 amstrategies; /* total number of strategies (operators) by * which we can traverse/search this AM. * Zero if AM does not have a fixed set of * strategy assignments. */ - int2 amsupport; /* total NUMBER of support functions that this + int2 amsupport; /* total number of support functions that this * AM uses */ int2 amorderstrategy;/* if this AM has a sort order, the strategy - * number of the sort operator. Zero if AM is - * not ordered. */ + * number of the default (ASC) sort operator. + * Zero if AM is not ordered. */ + int2 amdescorder; /* if this AM has a sort order, the strategy + * number of the DESC sort operator. + * Zero if AM is not ordered. */ bool amcanunique; /* does AM support UNIQUE indexes? */ bool amcanmulticol; /* does AM support multi-column indexes? */ bool amoptionalkey; /* can query omit key for the first column? */ @@ -80,46 +83,47 @@ typedef FormData_pg_am *Form_pg_am; * compiler constants for pg_am * ---------------- */ -#define Natts_pg_am 23 +#define Natts_pg_am 24 #define Anum_pg_am_amname 1 #define Anum_pg_am_amstrategies 2 #define Anum_pg_am_amsupport 3 #define Anum_pg_am_amorderstrategy 4 -#define Anum_pg_am_amcanunique 5 -#define Anum_pg_am_amcanmulticol 6 -#define Anum_pg_am_amoptionalkey 7 -#define Anum_pg_am_amindexnulls 8 -#define Anum_pg_am_amstorage 9 -#define Anum_pg_am_amclusterable 10 -#define Anum_pg_am_aminsert 11 -#define Anum_pg_am_ambeginscan 12 -#define Anum_pg_am_amgettuple 13 -#define Anum_pg_am_amgetmulti 14 -#define Anum_pg_am_amrescan 15 -#define Anum_pg_am_amendscan 16 -#define Anum_pg_am_ammarkpos 17 -#define Anum_pg_am_amrestrpos 18 -#define Anum_pg_am_ambuild 19 -#define Anum_pg_am_ambulkdelete 20 -#define Anum_pg_am_amvacuumcleanup 21 -#define Anum_pg_am_amcostestimate 22 -#define Anum_pg_am_amoptions 23 +#define Anum_pg_am_amdescorder 5 +#define Anum_pg_am_amcanunique 6 +#define Anum_pg_am_amcanmulticol 7 +#define Anum_pg_am_amoptionalkey 8 +#define Anum_pg_am_amindexnulls 9 +#define Anum_pg_am_amstorage 10 +#define Anum_pg_am_amclusterable 11 +#define Anum_pg_am_aminsert 12 +#define Anum_pg_am_ambeginscan 13 +#define Anum_pg_am_amgettuple 14 +#define Anum_pg_am_amgetmulti 15 +#define Anum_pg_am_amrescan 16 +#define Anum_pg_am_amendscan 17 +#define Anum_pg_am_ammarkpos 18 +#define Anum_pg_am_amrestrpos 19 +#define Anum_pg_am_ambuild 20 +#define Anum_pg_am_ambulkdelete 21 +#define Anum_pg_am_amvacuumcleanup 22 +#define Anum_pg_am_amcostestimate 23 +#define Anum_pg_am_amoptions 24 /* ---------------- * initial contents of pg_am * ---------------- */ -DATA(insert OID = 403 ( btree 5 1 1 t t t t f t btinsert btbeginscan btgettuple btgetmulti btrescan btendscan btmarkpos btrestrpos btbuild btbulkdelete btvacuumcleanup btcostestimate btoptions )); +DATA(insert OID = 403 ( btree 5 1 1 5 t t t t f t btinsert btbeginscan btgettuple btgetmulti btrescan btendscan btmarkpos btrestrpos btbuild btbulkdelete btvacuumcleanup btcostestimate btoptions )); DESCR("b-tree index access method"); #define BTREE_AM_OID 403 -DATA(insert OID = 405 ( hash 1 1 0 f f f f f f hashinsert hashbeginscan hashgettuple hashgetmulti hashrescan hashendscan hashmarkpos hashrestrpos hashbuild hashbulkdelete hashvacuumcleanup hashcostestimate hashoptions )); +DATA(insert OID = 405 ( hash 1 1 0 0 f f f f f f hashinsert hashbeginscan hashgettuple hashgetmulti hashrescan hashendscan hashmarkpos hashrestrpos hashbuild hashbulkdelete hashvacuumcleanup hashcostestimate hashoptions )); DESCR("hash index access method"); #define HASH_AM_OID 405 -DATA(insert OID = 783 ( gist 0 7 0 f t t t t t gistinsert gistbeginscan gistgettuple gistgetmulti gistrescan gistendscan gistmarkpos gistrestrpos gistbuild gistbulkdelete gistvacuumcleanup gistcostestimate gistoptions )); +DATA(insert OID = 783 ( gist 0 7 0 0 f t t t t t gistinsert gistbeginscan gistgettuple gistgetmulti gistrescan gistendscan gistmarkpos gistrestrpos gistbuild gistbulkdelete gistvacuumcleanup gistcostestimate gistoptions )); DESCR("GiST index access method"); #define GIST_AM_OID 783 -DATA(insert OID = 2742 ( gin 0 4 0 f f f f t f gininsert ginbeginscan gingettuple gingetmulti ginrescan ginendscan ginmarkpos ginrestrpos ginbuild ginbulkdelete ginvacuumcleanup gincostestimate ginoptions )); +DATA(insert OID = 2742 ( gin 0 4 0 0 f f f f t f gininsert ginbeginscan gingettuple gingetmulti ginrescan ginendscan ginmarkpos ginrestrpos ginbuild ginbulkdelete ginvacuumcleanup gincostestimate ginoptions )); DESCR("GIN index access method"); #define GIN_AM_OID 2742 diff --git a/src/include/catalog/pg_attribute.h b/src/include/catalog/pg_attribute.h index 413ec569b5..463e10a5da 100644 --- a/src/include/catalog/pg_attribute.h +++ b/src/include/catalog/pg_attribute.h @@ -8,7 +8,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_attribute.h,v 1.128 2007/01/05 22:19:52 momjian Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_attribute.h,v 1.129 2007/01/09 02:14:15 tgl Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -465,7 +465,8 @@ DATA(insert ( 1259 tableoid 26 0 4 -7 0 -1 -1 t p i t f f t 0)); { 0, {"indisvalid"}, 16, -1, 1, 7, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0 }, \ { 0, {"indkey"}, 22, -1, -1, 8, 1, -1, -1, false, 'p', 'i', true, false, false, true, 0 }, \ { 0, {"indclass"}, 30, -1, -1, 9, 1, -1, -1, false, 'p', 'i', true, false, false, true, 0 }, \ -{ 0, {"indexprs"}, 25, -1, -1, 10, 0, -1, -1, false, 'x', 'i', false, false, false, true, 0 }, \ -{ 0, {"indpred"}, 25, -1, -1, 11, 0, -1, -1, false, 'x', 'i', false, false, false, true, 0 } +{ 0, {"indoption"}, 22, -1, -1, 10, 1, -1, -1, false, 'p', 'i', true, false, false, true, 0 }, \ +{ 0, {"indexprs"}, 25, -1, -1, 11, 0, -1, -1, false, 'x', 'i', false, false, false, true, 0 }, \ +{ 0, {"indpred"}, 25, -1, -1, 12, 0, -1, -1, false, 'x', 'i', false, false, false, true, 0 } #endif /* PG_ATTRIBUTE_H */ diff --git a/src/include/catalog/pg_index.h b/src/include/catalog/pg_index.h index d88b9c58e7..31c6e25fb0 100644 --- a/src/include/catalog/pg_index.h +++ b/src/include/catalog/pg_index.h @@ -8,7 +8,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_index.h,v 1.42 2007/01/05 22:19:52 momjian Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_index.h,v 1.43 2007/01/09 02:14:15 tgl Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -46,6 +46,7 @@ CATALOG(pg_index,2610) BKI_WITHOUT_OIDS /* VARIABLE LENGTH FIELDS: */ int2vector indkey; /* column numbers of indexed cols, or 0 */ oidvector indclass; /* opclass identifiers */ + int2vector indoption; /* per-column flags (AM-specific meanings) */ text indexprs; /* expression trees for index attributes that * are not simple column references; one for * each zero entry in indkey[] */ @@ -64,7 +65,7 @@ typedef FormData_pg_index *Form_pg_index; * compiler constants for pg_index * ---------------- */ -#define Natts_pg_index 11 +#define Natts_pg_index 12 #define Anum_pg_index_indexrelid 1 #define Anum_pg_index_indrelid 2 #define Anum_pg_index_indnatts 3 @@ -74,7 +75,16 @@ typedef FormData_pg_index *Form_pg_index; #define Anum_pg_index_indisvalid 7 #define Anum_pg_index_indkey 8 #define Anum_pg_index_indclass 9 -#define Anum_pg_index_indexprs 10 -#define Anum_pg_index_indpred 11 +#define Anum_pg_index_indoption 10 +#define Anum_pg_index_indexprs 11 +#define Anum_pg_index_indpred 12 + +/* + * Index AMs that support ordered scans must support these two indoption + * bits. Otherwise, the content of the per-column indoption fields is + * open for future definition. + */ +#define INDOPTION_DESC 0x0001 /* values are in reverse order */ +#define INDOPTION_NULLS_FIRST 0x0002 /* NULLs are first instead of last */ #endif /* PG_INDEX_H */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index e1fe6c0ddb..d11f9ae6ea 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.337 2007/01/05 22:19:55 momjian Exp $ + * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.338 2007/01/09 02:14:15 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -36,6 +36,22 @@ typedef enum OnCommitAction ONCOMMIT_DROP /* ON COMMIT DROP */ } OnCommitAction; +/* Sort ordering options for ORDER BY and CREATE INDEX */ +typedef enum SortByDir +{ + SORTBY_DEFAULT, + SORTBY_ASC, + SORTBY_DESC, + SORTBY_USING /* not allowed in CREATE INDEX ... */ +} SortByDir; + +typedef enum SortByNulls +{ + SORTBY_NULLS_DEFAULT, + SORTBY_NULLS_FIRST, + SORTBY_NULLS_LAST +} SortByNulls; + /* * Grantable rights are encoded so that we can OR them together in a bitmask. @@ -348,14 +364,11 @@ typedef struct ResTarget /* * SortBy - for ORDER BY clause */ -#define SORTBY_ASC 1 -#define SORTBY_DESC 2 -#define SORTBY_USING 3 - typedef struct SortBy { NodeTag type; - int sortby_kind; /* see codes above */ + SortByDir sortby_dir; /* ASC/DESC/USING */ + SortByNulls sortby_nulls; /* NULLS FIRST/LAST */ List *useOp; /* name of op to use, if SORTBY_USING */ Node *node; /* expression to sort on */ } SortBy; @@ -443,6 +456,8 @@ typedef struct IndexElem char *name; /* name of attribute to index, or NULL */ Node *expr; /* expression to index, or NULL */ List *opclass; /* name of desired opclass; NIL = default */ + SortByDir ordering; /* ASC/DESC/default */ + SortByNulls nulls_ordering; /* FIRST/LAST/default */ } IndexElem; /* @@ -614,7 +629,8 @@ typedef struct RangeTblEntry * * tleSortGroupRef must match ressortgroupref of exactly one entry of the * associated targetlist; that is the expression to be sorted (or grouped) by. - * sortop is the OID of the ordering operator. + * sortop is the OID of the ordering operator (a "<" or ">" operator). + * nulls_first does about what you'd expect. * * SortClauses are also used to identify targets that we will do a "Unique" * filter step on (for SELECT DISTINCT and SELECT DISTINCT ON). The @@ -627,16 +643,21 @@ typedef struct SortClause { NodeTag type; Index tleSortGroupRef; /* reference into targetlist */ - Oid sortop; /* the sort operator to use */ + Oid sortop; /* the ordering operator ('<' op) */ + bool nulls_first; /* do NULLs come before normal values? */ } SortClause; /* * GroupClause - * representation of GROUP BY clauses * - * GroupClause is exactly like SortClause except for the nodetag value - * (it's probably not even really necessary to have two different - * nodetags...). We have routines that operate interchangeably on both. + * GroupClause is exactly like SortClause except for the nodetag value. + * We have routines that operate interchangeably on both. + * + * XXX SortClause overspecifies the semantics so far as GROUP BY is concerned + * (ditto for DISTINCT). It'd be better to specify an equality operator not + * an ordering operator. However, the two implementations are tightly entwined + * at the moment ... breaking them apart is work for another day. */ typedef SortClause GroupClause; diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index acb73a39ed..44002a9d45 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/nodes/plannodes.h,v 1.87 2007/01/05 22:19:55 momjian Exp $ + * $PostgreSQL: pgsql/src/include/nodes/plannodes.h,v 1.88 2007/01/09 02:14:15 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -385,6 +385,7 @@ typedef struct Sort int numCols; /* number of sort-key columns */ AttrNumber *sortColIdx; /* their indexes in the target list */ Oid *sortOperators; /* OIDs of operators to sort them by */ + bool *nullsFirst; /* NULLS FIRST/LAST directions */ } Sort; /* --------------- diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h index 75fb572de0..4e285a765a 100644 --- a/src/include/nodes/relation.h +++ b/src/include/nodes/relation.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/nodes/relation.h,v 1.130 2007/01/05 22:19:56 momjian Exp $ + * $PostgreSQL: pgsql/src/include/nodes/relation.h,v 1.131 2007/01/09 02:14:15 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -300,19 +300,23 @@ typedef struct RelOptInfo * and indexes, but that created confusion without actually doing anything * useful. So now we have a separate IndexOptInfo struct for indexes. * - * opfamily[], indexkeys[], and ordering[] have ncolumns entries. + * opfamily[], indexkeys[], fwdsortop[], revsortop[], and nulls_first[] + * each have ncolumns entries. Note: for historical reasons, the + * opfamily array has an extra entry that is always zero. Some code + * scans until it sees a zero entry, rather than looking at ncolumns. + * * Zeroes in the indexkeys[] array indicate index columns that are * expressions; there is one element in indexprs for each such column. * - * Note: for historical reasons, the opfamily and ordering arrays have - * an extra entry that is always zero. Some code scans until it sees a - * zero entry, rather than looking at ncolumns. + * For an unordered index, the sortop arrays contains zeroes. Note that + * fwdsortop[] and nulls_first[] describe the sort ordering of a forward + * indexscan; we can also consider a backward indexscan, which will + * generate sort order described by revsortop/!nulls_first. * * The indexprs and indpred expressions have been run through * prepqual.c and eval_const_expressions() for ease of matching to - * WHERE clauses. indpred is in implicit-AND form. + * WHERE clauses. indpred is in implicit-AND form. */ - typedef struct IndexOptInfo { NodeTag type; @@ -328,7 +332,9 @@ typedef struct IndexOptInfo int ncolumns; /* number of columns in index */ Oid *opfamily; /* OIDs of operator families for columns */ int *indexkeys; /* column numbers of index's keys, or 0 */ - Oid *ordering; /* OIDs of sort operators for each column */ + Oid *fwdsortop; /* OIDs of sort operators for each column */ + Oid *revsortop; /* OIDs of sort operators for backward scan */ + bool *nulls_first; /* do NULLs come first in the sort order? */ Oid relam; /* OID of the access method (in pg_am) */ RegProcedure amcostestimate; /* OID of the access method's cost fcn */ @@ -360,6 +366,7 @@ typedef struct PathKeyItem Node *key; /* the item that is ordered */ Oid sortop; /* the ordering operator ('<' op) */ + bool nulls_first; /* do NULLs come before normal values? */ /* * key typically points to a Var node, ie a relation attribute, but it can diff --git a/src/include/parser/parse_clause.h b/src/include/parser/parse_clause.h index f59f031db3..0630961368 100644 --- a/src/include/parser/parse_clause.h +++ b/src/include/parser/parse_clause.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/parser/parse_clause.h,v 1.47 2007/01/05 22:19:57 momjian Exp $ + * $PostgreSQL: pgsql/src/include/parser/parse_clause.h,v 1.48 2007/01/09 02:14:15 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -38,9 +38,9 @@ extern List *addAllTargetsToSortList(ParseState *pstate, bool resolveUnknown); extern List *addTargetToSortList(ParseState *pstate, TargetEntry *tle, List *sortlist, List *targetlist, - int sortby_kind, List *sortby_opname, - bool resolveUnknown); + SortByDir sortby_dir, SortByNulls sortby_nulls, + List *sortby_opname, bool resolveUnknown); extern Index assignSortGroupRef(TargetEntry *tle, List *tlist); -extern bool targetIsInSortList(TargetEntry *tle, List *sortList); +extern bool targetIsInSortList(TargetEntry *tle, Oid sortop, List *sortList); #endif /* PARSE_CLAUSE_H */ diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h index 41e0c5162f..15d2b8a06d 100644 --- a/src/include/utils/lsyscache.h +++ b/src/include/utils/lsyscache.h @@ -6,7 +6,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/utils/lsyscache.h,v 1.110 2007/01/05 22:19:59 momjian Exp $ + * $PostgreSQL: pgsql/src/include/utils/lsyscache.h,v 1.111 2007/01/09 02:14:16 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -37,6 +37,7 @@ extern Oid get_opfamily_member(Oid opfamily, Oid lefttype, Oid righttype, int16 strategy); extern bool get_op_mergejoin_info(Oid eq_op, Oid *left_sortop, Oid *right_sortop, Oid *opfamily); +extern bool get_op_compare_function(Oid opno, Oid *cmpfunc, bool *reverse); extern Oid get_op_hash_function(Oid opno); extern void get_op_btree_interpretation(Oid opno, List **opfamilies, List **opstrats); diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h index ae81b7483e..c8b78b9542 100644 --- a/src/include/utils/rel.h +++ b/src/include/utils/rel.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/utils/rel.h,v 1.94 2007/01/05 22:19:59 momjian Exp $ + * $PostgreSQL: pgsql/src/include/utils/rel.h,v 1.95 2007/01/09 02:14:16 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -189,6 +189,7 @@ typedef struct RelationData Oid *rd_operator; /* OIDs of index operators */ RegProcedure *rd_support; /* OIDs of support procedures */ FmgrInfo *rd_supportinfo; /* lookup info for support procedures */ + int16 *rd_indoption; /* per-column AM-specific flags */ List *rd_indexprs; /* index expression trees, if any */ List *rd_indpred; /* index predicate tree, if any */ void *rd_amcache; /* available for use by index AM */ diff --git a/src/include/utils/tuplesort.h b/src/include/utils/tuplesort.h index 2ee315d855..cea50b4836 100644 --- a/src/include/utils/tuplesort.h +++ b/src/include/utils/tuplesort.h @@ -13,7 +13,7 @@ * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/utils/tuplesort.h,v 1.24 2007/01/05 22:20:00 momjian Exp $ + * $PostgreSQL: pgsql/src/include/utils/tuplesort.h,v 1.25 2007/01/09 02:14:16 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -45,14 +45,14 @@ typedef struct Tuplesortstate Tuplesortstate; */ extern Tuplesortstate *tuplesort_begin_heap(TupleDesc tupDesc, - int nkeys, - Oid *sortOperators, AttrNumber *attNums, + int nkeys, AttrNumber *attNums, + Oid *sortOperators, bool *nullsFirstFlags, int workMem, bool randomAccess); extern Tuplesortstate *tuplesort_begin_index(Relation indexRel, bool enforceUnique, int workMem, bool randomAccess); extern Tuplesortstate *tuplesort_begin_datum(Oid datumType, - Oid sortOperator, + Oid sortOperator, bool nullsFirstFlag, int workMem, bool randomAccess); extern void tuplesort_puttupleslot(Tuplesortstate *state, @@ -84,28 +84,17 @@ extern void tuplesort_rescan(Tuplesortstate *state); extern void tuplesort_markpos(Tuplesortstate *state); extern void tuplesort_restorepos(Tuplesortstate *state); -/* - * This routine selects an appropriate sorting function to implement - * a sort operator as efficiently as possible. - */ -typedef enum -{ - SORTFUNC_LT, /* raw "<" operator */ - SORTFUNC_REVLT, /* raw "<" operator, but reverse NULLs */ - SORTFUNC_CMP, /* -1 / 0 / 1 three-way comparator */ - SORTFUNC_REVCMP /* 1 / 0 / -1 (reversed) 3-way comparator */ -} SortFunctionKind; - -extern void SelectSortFunction(Oid sortOperator, - RegProcedure *sortFunction, - SortFunctionKind *kind); +/* Setup for ApplySortFunction */ +extern void SelectSortFunction(Oid sortOperator, bool nulls_first, + Oid *sortFunction, + int *sortFlags); /* * Apply a sort function (by now converted to fmgr lookup form) * and return a 3-way comparison result. This takes care of handling - * NULLs and sort ordering direction properly. + * reverse-sort and NULLs-ordering properly. */ -extern int32 ApplySortFunction(FmgrInfo *sortFunction, SortFunctionKind kind, +extern int32 ApplySortFunction(FmgrInfo *sortFunction, int sortFlags, Datum datum1, bool isNull1, Datum datum2, bool isNull2); diff --git a/src/test/regress/expected/circle.out b/src/test/regress/expected/circle.out index 0f8cf741e8..a63e348aca 100644 --- a/src/test/regress/expected/circle.out +++ b/src/test/regress/expected/circle.out @@ -81,7 +81,7 @@ SELECT '' AS four, f1 FROM CIRCLE_TBL WHERE diameter(f1) >= 10; SELECT '' as five, c1.f1 AS one, c2.f1 AS two, (c1.f1 <-> c2.f1) AS distance FROM CIRCLE_TBL c1, CIRCLE_TBL c2 WHERE (c1.f1 < c2.f1) AND ((c1.f1 <-> c2.f1) > 0) - ORDER BY distance, one USING < , two USING < ; + ORDER BY distance, area(c1.f1), area(c2.f1); five | one | two | distance ------+----------------+----------------+------------------ | <(100,200),10> | <(100,1),115> | 74 diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index c19794e67e..fff65adfb6 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -62,11 +62,11 @@ SET enable_indexscan = OFF; SET enable_bitmapscan = OFF; SELECT * FROM fast_emp4000 WHERE home_base @ '(200,200),(2000,1000)'::box - ORDER BY home_base USING <; + ORDER BY (home_base[0])[0]; home_base ----------------------- - (1444,403),(1346,344) (337,455),(240,359) + (1444,403),(1346,344) (2 rows) SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; @@ -76,14 +76,14 @@ SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; (1 row) SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon - ORDER BY f1 USING <<; + ORDER BY (poly_center(f1))[0]; f1 --------------------- ((2,0),(2,4),(0,0)) (1 row) SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) - ORDER BY f1 USING <; + ORDER BY area(f1); f1 --------------- <(1,2),3> @@ -112,11 +112,11 @@ SET enable_bitmapscan = ON; -- changes too often for me to want to put an EXPLAIN in the test...) SELECT * FROM fast_emp4000 WHERE home_base @ '(200,200),(2000,1000)'::box - ORDER BY home_base USING <; + ORDER BY (home_base[0])[0]; home_base ----------------------- - (1444,403),(1346,344) (337,455),(240,359) + (1444,403),(1346,344) (2 rows) SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; @@ -126,14 +126,14 @@ SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; (1 row) SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon - ORDER BY f1 USING <<; + ORDER BY (poly_center(f1))[0]; f1 --------------------- ((2,0),(2,4),(0,0)) (1 row) SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) - ORDER BY f1 USING <; + ORDER BY area(f1); f1 --------------- <(1,2),3> diff --git a/src/test/regress/expected/geometry.out b/src/test/regress/expected/geometry.out index 79763f8100..f307788cf1 100644 --- a/src/test/regress/expected/geometry.out +++ b/src/test/regress/expected/geometry.out @@ -506,7 +506,7 @@ SELECT '' AS two, circle(f1) SELECT '' AS twentyfour, c1.f1 AS circle, p1.f1 AS point, (p1.f1 <-> c1.f1) AS distance FROM CIRCLE_TBL c1, POINT_TBL p1 WHERE (p1.f1 <-> c1.f1) > 0 - ORDER BY distance, circle using <, point using <<; + ORDER BY distance, area(c1.f1), p1.f1[0]; twentyfour | circle | point | distance ------------+----------------+------------+--------------- | <(1,2),3> | (-3,4) | 1.472135955 diff --git a/src/test/regress/expected/geometry_1.out b/src/test/regress/expected/geometry_1.out index 81e6b535ef..3c7234b2b4 100644 --- a/src/test/regress/expected/geometry_1.out +++ b/src/test/regress/expected/geometry_1.out @@ -506,7 +506,7 @@ SELECT '' AS two, circle(f1) SELECT '' AS twentyfour, c1.f1 AS circle, p1.f1 AS point, (p1.f1 <-> c1.f1) AS distance FROM CIRCLE_TBL c1, POINT_TBL p1 WHERE (p1.f1 <-> c1.f1) > 0 - ORDER BY distance, circle using <, point using <<; + ORDER BY distance, area(c1.f1), p1.f1[0]; twentyfour | circle | point | distance ------------+----------------+------------+--------------- | <(1,2),3> | (-3,4) | 1.472135955 diff --git a/src/test/regress/expected/geometry_2.out b/src/test/regress/expected/geometry_2.out index bcc405e8c7..7daddc4a42 100644 --- a/src/test/regress/expected/geometry_2.out +++ b/src/test/regress/expected/geometry_2.out @@ -506,7 +506,7 @@ SELECT '' AS two, circle(f1) SELECT '' AS twentyfour, c1.f1 AS circle, p1.f1 AS point, (p1.f1 <-> c1.f1) AS distance FROM CIRCLE_TBL c1, POINT_TBL p1 WHERE (p1.f1 <-> c1.f1) > 0 - ORDER BY distance, circle using <, point using <<; + ORDER BY distance, area(c1.f1), p1.f1[0]; twentyfour | circle | point | distance ------------+----------------+------------+--------------- | <(1,2),3> | (-3,4) | 1.472135955 diff --git a/src/test/regress/expected/point.out b/src/test/regress/expected/point.out index 552be515d6..96bcc6d695 100644 --- a/src/test/regress/expected/point.out +++ b/src/test/regress/expected/point.out @@ -108,7 +108,7 @@ SELECT '' AS six, p.f1, p.f1 <-> point '(0,0)' AS dist SET geqo TO 'off'; SELECT '' AS thirtysix, p1.f1 AS point1, p2.f1 AS point2, p1.f1 <-> p2.f1 AS dist FROM POINT_TBL p1, POINT_TBL p2 - ORDER BY dist, point1 using <<, point2 using <<; + ORDER BY dist, p1.f1[0], p2.f1[0]; thirtysix | point1 | point2 | dist -----------+------------+------------+------------------ | (-10,0) | (-10,0) | 0 @@ -190,7 +190,7 @@ SELECT '' AS thirty, p1.f1 AS point1, p2.f1 AS point2 SELECT '' AS fifteen, p1.f1 AS point1, p2.f1 AS point2, (p1.f1 <-> p2.f1) AS distance FROM POINT_TBL p1, POINT_TBL p2 WHERE (p1.f1 <-> p2.f1) > 3 and p1.f1 << p2.f1 - ORDER BY distance, point1 using <<, point2 using <<; + ORDER BY distance, p1.f1[0], p2.f1[0]; fifteen | point1 | point2 | distance ---------+------------+------------+------------------ | (-3,4) | (0,0) | 5 diff --git a/src/test/regress/expected/select.out b/src/test/regress/expected/select.out index 6fcc88860d..0b3f546bdf 100644 --- a/src/test/regress/expected/select.out +++ b/src/test/regress/expected/select.out @@ -513,3 +513,219 @@ SELECT * FROM int8_tbl; 4567890123456789 | -4567890123456789 (9 rows) +-- +-- Test ORDER BY options +-- +CREATE TEMP TABLE foo (f1 int); +INSERT INTO foo VALUES (42),(3),(10),(7),(null),(null),(1); +SELECT * FROM foo ORDER BY f1; + f1 +---- + 1 + 3 + 7 + 10 + 42 + + +(7 rows) + +SELECT * FROM foo ORDER BY f1 ASC; -- same thing + f1 +---- + 1 + 3 + 7 + 10 + 42 + + +(7 rows) + +SELECT * FROM foo ORDER BY f1 NULLS FIRST; + f1 +---- + + + 1 + 3 + 7 + 10 + 42 +(7 rows) + +SELECT * FROM foo ORDER BY f1 DESC; + f1 +---- + + + 42 + 10 + 7 + 3 + 1 +(7 rows) + +SELECT * FROM foo ORDER BY f1 DESC NULLS LAST; + f1 +---- + 42 + 10 + 7 + 3 + 1 + + +(7 rows) + +-- check if indexscans do the right things +CREATE INDEX fooi ON foo (f1); +SET enable_sort = false; +SELECT * FROM foo ORDER BY f1; + f1 +---- + 1 + 3 + 7 + 10 + 42 + + +(7 rows) + +SELECT * FROM foo ORDER BY f1 NULLS FIRST; + f1 +---- + + + 1 + 3 + 7 + 10 + 42 +(7 rows) + +SELECT * FROM foo ORDER BY f1 DESC; + f1 +---- + + + 42 + 10 + 7 + 3 + 1 +(7 rows) + +SELECT * FROM foo ORDER BY f1 DESC NULLS LAST; + f1 +---- + 42 + 10 + 7 + 3 + 1 + + +(7 rows) + +DROP INDEX fooi; +CREATE INDEX fooi ON foo (f1 DESC); +SELECT * FROM foo ORDER BY f1; + f1 +---- + 1 + 3 + 7 + 10 + 42 + + +(7 rows) + +SELECT * FROM foo ORDER BY f1 NULLS FIRST; + f1 +---- + + + 1 + 3 + 7 + 10 + 42 +(7 rows) + +SELECT * FROM foo ORDER BY f1 DESC; + f1 +---- + + + 42 + 10 + 7 + 3 + 1 +(7 rows) + +SELECT * FROM foo ORDER BY f1 DESC NULLS LAST; + f1 +---- + 42 + 10 + 7 + 3 + 1 + + +(7 rows) + +DROP INDEX fooi; +CREATE INDEX fooi ON foo (f1 DESC NULLS LAST); +SELECT * FROM foo ORDER BY f1; + f1 +---- + 1 + 3 + 7 + 10 + 42 + + +(7 rows) + +SELECT * FROM foo ORDER BY f1 NULLS FIRST; + f1 +---- + + + 1 + 3 + 7 + 10 + 42 +(7 rows) + +SELECT * FROM foo ORDER BY f1 DESC; + f1 +---- + + + 42 + 10 + 7 + 3 + 1 +(7 rows) + +SELECT * FROM foo ORDER BY f1 DESC NULLS LAST; + f1 +---- + 42 + 10 + 7 + 3 + 1 + + +(7 rows) + diff --git a/src/test/regress/sql/circle.sql b/src/test/regress/sql/circle.sql index fe229b3b2c..c0284b2b59 100644 --- a/src/test/regress/sql/circle.sql +++ b/src/test/regress/sql/circle.sql @@ -42,4 +42,4 @@ SELECT '' AS four, f1 FROM CIRCLE_TBL WHERE diameter(f1) >= 10; SELECT '' as five, c1.f1 AS one, c2.f1 AS two, (c1.f1 <-> c2.f1) AS distance FROM CIRCLE_TBL c1, CIRCLE_TBL c2 WHERE (c1.f1 < c2.f1) AND ((c1.f1 <-> c2.f1) > 0) - ORDER BY distance, one USING < , two USING < ; + ORDER BY distance, area(c1.f1), area(c2.f1); diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql index a4bd1db915..70d17ec68c 100644 --- a/src/test/regress/sql/create_index.sql +++ b/src/test/regress/sql/create_index.sql @@ -92,15 +92,15 @@ SET enable_bitmapscan = OFF; SELECT * FROM fast_emp4000 WHERE home_base @ '(200,200),(2000,1000)'::box - ORDER BY home_base USING <; + ORDER BY (home_base[0])[0]; SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon - ORDER BY f1 USING <<; + ORDER BY (poly_center(f1))[0]; SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) - ORDER BY f1 USING <; + ORDER BY area(f1); SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon; @@ -115,15 +115,15 @@ SET enable_bitmapscan = ON; -- changes too often for me to want to put an EXPLAIN in the test...) SELECT * FROM fast_emp4000 WHERE home_base @ '(200,200),(2000,1000)'::box - ORDER BY home_base USING <; + ORDER BY (home_base[0])[0]; SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box; SELECT * FROM polygon_tbl WHERE f1 ~ '((1,1),(2,2),(2,1))'::polygon - ORDER BY f1 USING <<; + ORDER BY (poly_center(f1))[0]; SELECT * FROM circle_tbl WHERE f1 && circle(point(1,-2), 1) - ORDER BY f1 USING <; + ORDER BY area(f1); SELECT count(*) FROM gpolygon_tbl WHERE f1 && '(1000,1000,0,0)'::polygon; diff --git a/src/test/regress/sql/geometry.sql b/src/test/regress/sql/geometry.sql index c53d9116fa..523bcdb76d 100644 --- a/src/test/regress/sql/geometry.sql +++ b/src/test/regress/sql/geometry.sql @@ -152,4 +152,4 @@ SELECT '' AS two, circle(f1) SELECT '' AS twentyfour, c1.f1 AS circle, p1.f1 AS point, (p1.f1 <-> c1.f1) AS distance FROM CIRCLE_TBL c1, POINT_TBL p1 WHERE (p1.f1 <-> c1.f1) > 0 - ORDER BY distance, circle using <, point using <<; + ORDER BY distance, area(c1.f1), p1.f1[0]; diff --git a/src/test/regress/sql/point.sql b/src/test/regress/sql/point.sql index efbfe6905f..1f5f7635d1 100644 --- a/src/test/regress/sql/point.sql +++ b/src/test/regress/sql/point.sql @@ -59,7 +59,7 @@ SET geqo TO 'off'; SELECT '' AS thirtysix, p1.f1 AS point1, p2.f1 AS point2, p1.f1 <-> p2.f1 AS dist FROM POINT_TBL p1, POINT_TBL p2 - ORDER BY dist, point1 using <<, point2 using <<; + ORDER BY dist, p1.f1[0], p2.f1[0]; SELECT '' AS thirty, p1.f1 AS point1, p2.f1 AS point2 FROM POINT_TBL p1, POINT_TBL p2 @@ -69,7 +69,7 @@ SELECT '' AS thirty, p1.f1 AS point1, p2.f1 AS point2 SELECT '' AS fifteen, p1.f1 AS point1, p2.f1 AS point2, (p1.f1 <-> p2.f1) AS distance FROM POINT_TBL p1, POINT_TBL p2 WHERE (p1.f1 <-> p2.f1) > 3 and p1.f1 << p2.f1 - ORDER BY distance, point1 using <<, point2 using <<; + ORDER BY distance, p1.f1[0], p2.f1[0]; -- put distance result into output to allow sorting with GEQ optimizer - tgl 97/05/10 SELECT '' AS three, p1.f1 AS point1, p2.f1 AS point2, (p1.f1 <-> p2.f1) AS distance diff --git a/src/test/regress/sql/select.sql b/src/test/regress/sql/select.sql index 2c813a9d8e..f23cccd24f 100644 --- a/src/test/regress/sql/select.sql +++ b/src/test/regress/sql/select.sql @@ -138,3 +138,42 @@ UNION ALL SELECT 2+2, 57 UNION ALL SELECT * FROM int8_tbl; + +-- +-- Test ORDER BY options +-- + +CREATE TEMP TABLE foo (f1 int); + +INSERT INTO foo VALUES (42),(3),(10),(7),(null),(null),(1); + +SELECT * FROM foo ORDER BY f1; +SELECT * FROM foo ORDER BY f1 ASC; -- same thing +SELECT * FROM foo ORDER BY f1 NULLS FIRST; +SELECT * FROM foo ORDER BY f1 DESC; +SELECT * FROM foo ORDER BY f1 DESC NULLS LAST; + +-- check if indexscans do the right things +CREATE INDEX fooi ON foo (f1); +SET enable_sort = false; + +SELECT * FROM foo ORDER BY f1; +SELECT * FROM foo ORDER BY f1 NULLS FIRST; +SELECT * FROM foo ORDER BY f1 DESC; +SELECT * FROM foo ORDER BY f1 DESC NULLS LAST; + +DROP INDEX fooi; +CREATE INDEX fooi ON foo (f1 DESC); + +SELECT * FROM foo ORDER BY f1; +SELECT * FROM foo ORDER BY f1 NULLS FIRST; +SELECT * FROM foo ORDER BY f1 DESC; +SELECT * FROM foo ORDER BY f1 DESC NULLS LAST; + +DROP INDEX fooi; +CREATE INDEX fooi ON foo (f1 DESC NULLS LAST); + +SELECT * FROM foo ORDER BY f1; +SELECT * FROM foo ORDER BY f1 NULLS FIRST; +SELECT * FROM foo ORDER BY f1 DESC; +SELECT * FROM foo ORDER BY f1 DESC NULLS LAST; -- 2.40.0