Re-enable error for "SELECT ... OFFSET -1".

tglsfdc · tglsfdc · commit 6306d07122d8 · 2014-07-22T13:30:14.000-04:00
The executor has thrown errors for negative OFFSET values since 8.4 (see commit bfce56e), but in a moment of brain fade I taught the planner that OFFSET with a constant negative value was a no-op (commit 1a1832e). Reinstate the former behavior by only discarding OFFSET with a value of exactly 0. In passing, adjust a planner comment that referenced the ancient behavior. Back-patch to 9.3 where the mistake was introduced.
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
@@ -2127,7 +2127,7 @@ preprocess_limit(PlannerInfo *root, double tuple_fraction,
 			{
 				*offset_est = DatumGetInt64(((Const *) est)->constvalue);
 				if (*offset_est < 0)
-					*offset_est = 0;	/* less than 0 is same as 0 */
+					*offset_est = 0;	/* treat as not present */
 			}
 		}
 		else
@@ -2288,9 +2288,8 @@ limit_needed(Query *parse)
 			{
 				int64		offset = DatumGetInt64(((Const *) node)->constvalue);
 
-				/* Executor would treat less-than-zero same as zero */
-				if (offset > 0)
-					return true;	/* OFFSET with a positive value */
+				if (offset != 0)
+					return true;	/* OFFSET with a nonzero value */
 			}
 		}
 		else

Original file line number	Diff line number	Diff line change
`@@ -2127,7 +2127,7 @@ preprocess_limit(PlannerInfo *root, double tuple_fraction,`
`2127`	`2127`	`{`
`2128`	`2128`	`offset_est = DatumGetInt64(((Const ) est)->constvalue);`
`2129`	`2129`	`if (*offset_est < 0)`
`2130`		`- offset_est = 0; / less than 0 is same as 0 */`
	`2130`	`+ offset_est = 0; / treat as not present */`
`2131`	`2131`	`}`
`2132`	`2132`	`}`
`2133`	`2133`	`else`
`@@ -2288,9 +2288,8 @@ limit_needed(Query *parse)`
`2288`	`2288`	`{`
`2289`	`2289`	`int64 offset = DatumGetInt64(((Const *) node)->constvalue);`
`2290`	`2290`
`2291`		`- /* Executor would treat less-than-zero same as zero */`
`2292`		`- if (offset > 0)`
`2293`		`- return true; /* OFFSET with a positive value */`
	`2291`	`+ if (offset != 0)`
	`2292`	`+ return true; /* OFFSET with a nonzero value */`
`2294`	`2293`	`}`
`2295`	`2294`	`}`
`2296`	`2295`	`else`