From 1aab783b59d56af304cd652d2036ba45769122ec Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Sun, 23 Sep 2001 03:39:01 +0000 Subject: [PATCH] Implement TODO item: * Change LIMIT val,val to offset,limit to match MySQL Documentation updates too. --- doc/TODO | 8 ++++---- doc/src/sgml/ref/select.sgml | 20 ++++++++++++-------- doc/src/sgml/sql.sgml | 5 +++-- src/backend/parser/gram.y | 6 +++--- 4 files changed, 22 insertions(+), 17 deletions(-) diff --git a/doc/TODO b/doc/TODO index 963c17618e..e7053aae28 100644 --- a/doc/TODO +++ b/doc/TODO @@ -74,10 +74,10 @@ TYPES o Allow nulls in arrays o Allow arrays to be ORDER'ed o fix array handling in ECPG -BINARY DATA +lfBINARY DATA o -Add non-large-object binary field (already exists -- bytea) o -Make binary interface for TOAST columns (base64) - o Improve vacuum of large objects (/contrib/vacuumlo) + o Improve vacuum of large objects, like /contrib/vacuumlo o Add security checking for large objects o Make file in/out interface for TOAST columns, similar to large object interface (force out-of-line storage and no compression) @@ -105,7 +105,7 @@ INDEXES * Allow CREATE INDEX zman_index ON test (date_trunc( 'day', zman ) datetime_ops) fails index can't store constant parameters * Add FILLFACTOR to index creation -* Order duplicate index entries by tid +* Order duplicate index entries by tid for faster heap lookups * -Re-enable partial indexes * -Prevent pg_attribute from having duplicate oids for indexes (Tom) * Allow inherited tables to inherit index, UNIQUE constraint, and primary @@ -124,7 +124,7 @@ INDEXES non-consecutive keys or OR clauses, so fewer heap accesses * Allow SELECT * FROM tab WHERE int2col = 4 to use int2col index, int8, float4, numeric/decimal too [optimizer] -* Use indexes with CIDR '<<' (contains) operator +* -Use indexes with CIDR '<<' (contains) operator * Allow LIKE indexing optimization for non-ASCII locales * Be smarter about insertion of already-ordered data into btree index * -Gather more accurate dispersion statistics using indexes (Tom) diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index 2e1e552f26..e8dc02498c 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -1,5 +1,5 @@ @@ -29,7 +29,8 @@ SELECT [ ALL | DISTINCT [ ON ( expressionselect ] [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ] [ FOR UPDATE [ OF tablename [, ...] ] ] - [ LIMIT { count | ALL } [ { OFFSET | , } start ]] + [ LIMIT [ start , ] { count | ALL } ] + [ OFFSET start ] where from_item can be: @@ -613,7 +614,8 @@ SELECT name FROM distributors ORDER BY code; table_query UNION [ ALL ] table_query [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ] - [ LIMIT { count | ALL } [ { OFFSET | , } start ]] + [ LIMIT [ start , ] { count | ALL } ] + [ OFFSET start ] where @@ -662,7 +664,8 @@ SELECT name FROM distributors ORDER BY code; table_query INTERSECT [ ALL ] table_query [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ] - [ LIMIT { count | ALL } [ { OFFSET | , } start ]] + [ LIMIT [ start , ] { count | ALL } ] + [ OFFSET start ] where @@ -702,7 +705,8 @@ SELECT name FROM distributors ORDER BY code; table_query EXCEPT [ ALL ] table_query [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ] - [ LIMIT { count | ALL } [ { OFFSET | , } start ]] + [ LIMIT [ start , ] { count | ALL } ] + [ OFFSET start ] where @@ -738,7 +742,7 @@ SELECT name FROM distributors ORDER BY code; - LIMIT { count | ALL } [ { OFFSET | , } start ] + LIMIT [ start , ] { count | ALL } OFFSET start @@ -761,14 +765,14 @@ SELECT name FROM distributors ORDER BY code; constrains the result rows into a unique order. Otherwise you will get an unpredictable subset of the query's rows---you may be asking for the tenth through twentieth rows, but tenth through twentieth in what - ordering? You don't know what ordering, unless you specified ORDER BY. + ordering? You don't know what ordering unless you specify ORDER BY. As of Postgres 7.0, the query optimizer takes LIMIT into account when generating a query plan, so you are very likely to get different plans (yielding different row - orders) depending on what you give for LIMIT and OFFSET. Thus, using + orders) depending on what you use for LIMIT and OFFSET. Thus, using different LIMIT/OFFSET values to select different subsets of a query result will give inconsistent results unless you enforce a predictable result ordering with ORDER BY. This is not diff --git a/doc/src/sgml/sql.sgml b/doc/src/sgml/sql.sgml index d0eef867ab..223aee1bc6 100644 --- a/doc/src/sgml/sql.sgml +++ b/doc/src/sgml/sql.sgml @@ -1,5 +1,5 @@ @@ -864,7 +864,8 @@ SELECT [ ALL | DISTINCT [ ON ( expressionselect ] [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ] [ FOR UPDATE [ OF class_name [, ...] ] ] - [ LIMIT { count | ALL } [ { OFFSET | , } start ]] + [ LIMIT [ start , ] { count | ALL } ] + [ OFFSET start ] diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 24407902bd..64d71d28bd 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -11,7 +11,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.252 2001/09/20 14:20:27 petere Exp $ + * $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.253 2001/09/23 03:39:01 momjian Exp $ * * HISTORY * AUTHOR DATE MAJOR EVENT @@ -3587,8 +3587,8 @@ OptUseOp: USING all_Op { $$ = $2; } ; -select_limit: LIMIT select_limit_value ',' select_offset_value - { $$ = makeList2($4, $2); } +select_limit: LIMIT select_offset_value ',' select_limit_value + { $$ = makeList2($2, $4); } | LIMIT select_limit_value OFFSET select_offset_value { $$ = makeList2($4, $2); } | LIMIT select_limit_value -- 2.11.0