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

tglsfdc · tglsfdc · commit 30d6a9858cd8 · 2014-07-22T13:30:09.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
@@ -2334,7 +2334,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
@@ -2495,9 +2495,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
`@@ -2334,7 +2334,7 @@ preprocess_limit(PlannerInfo *root, double tuple_fraction,`
`2334`	`2334`	`{`
`2335`	`2335`	`offset_est = DatumGetInt64(((Const ) est)->constvalue);`
`2336`	`2336`	`if (*offset_est < 0)`
`2337`		`- offset_est = 0; / less than 0 is same as 0 */`
	`2337`	`+ offset_est = 0; / treat as not present */`
`2338`	`2338`	`}`
`2339`	`2339`	`}`
`2340`	`2340`	`else`
`@@ -2495,9 +2495,8 @@ limit_needed(Query *parse)`
`2495`	`2495`	`{`
`2496`	`2496`	`int64 offset = DatumGetInt64(((Const *) node)->constvalue);`
`2497`	`2497`
`2498`		`- /* Executor would treat less-than-zero same as zero */`
`2499`		`- if (offset > 0)`
`2500`		`- return true; /* OFFSET with a positive value */`
	`2498`	`+ if (offset != 0)`
	`2499`	`+ return true; /* OFFSET with a nonzero value */`
`2501`	`2500`	`}`
`2502`	`2501`	`}`
`2503`	`2502`	`else`