From: Clickin (@Clickin) <noreply+Clickin@github.com>
To: pgjdbc/pgjdbc <noreply+pgjdbc-pgjdbc@github.com>
Subject: [pgjdbc/pgjdbc] PR #4524: fix: support the JDBC {limit rows [offset row_offset]} escape
Date: Sun, 08 Nov 2026 10:51:32 +0000
Message-ID: <gh-pgjdbc-pgjdbc-4524@github.com> (raw)
Fixes #2956
JDBC 4.3 section 13.4.6 defines the `{limit rows [offset row_offset]}` escape, but the driver does not translate it, so `SELECT 1 ORDER BY 1 {limit 1}` fails with `syntax error at or near "{"`.
`Parser` now has an `ESC_LIMIT` state in the escape replacement list, as suggested in the issue. `{limit` becomes `LIMIT ` and the rest is copied up to the closing `}`, including any `OFFSET`, which PostgreSQL accepts as is. The escape is recognized only when its value starts with a digit or a `?` parameter marker.
The scope is the same as the other escapes. Whitespace other than a space after the keyword is not accepted (open PR #4392 generalizes that for every escape), and a `}` inside a comment within the escape ends it.
Tests:
* `ParserTest`: translation of values and `OFFSET`, mixed case and nesting, disabled escape processing, and SQL that must stay unchanged (string literal, quoted identifier, dollar quote, comments, `{limitation 1}`, `{limit -1}`).
* `StatementTest`: the query from the issue, `nativeSQL`, `{limit ? offset ?}` with several value pairs, and `setEscapeProcessing(false)`, where the server still returns `42601`.
Ran the full `:postgresql:test` on Java 21 and `ParserTest` and `StatementTest` on Java 8 against PostgreSQL 17.11 with no failures. `./gradlew styleCheck` passes.
### All Submissions:
* [x] Have you followed the guidelines in our [Contributing](https://github.com/pgjdbc/pgjdbc/blob/master/CONTRIBUTING.md) document?
* [x] Have you checked to ensure there aren't other open [Pull Requests](../../pulls) for the same update/change?
### New Feature Submissions:
1. [x] Does your submission pass tests?
2. [x] Does `./gradlew styleCheck` pass ?
3. [ ] Have you added your new test classes to an existing test suite in alphabetical order? Not applicable, no new test classes.
### Changes to Existing Features:
* [ ] Does this break existing behaviour? If so please explain. No. SQL without `{limit` followed by optional spaces and a digit or `?` is unchanged.
* [x] Have you added an explanation of what your changes do and why you'd like us to include them?
* [x] Have you written new tests for your core changes, as applicable?
* [x] Have you successfully run tests with your changes locally?
Written with AI assistance: OpenAI GPT-6 Astra wrote the change and tests, and Claude Opus 5.5 reviewed them and wrote this description. Both are listed as co-authors in the commit trailers.
🤖 Generated with [Claude Code](https://claude.com/claude-code)
inline patch 1: pgjdbc/src/main/java/org/postgresql/core/Parser.java, pgjdbc/src/test/java/org/postgresql/core/ParserTest.java, pgjdbc/src/test/java/org/postgresql/test/jdbc2/StatementTest.java (+91/-2 lines) download inline-1.patchdiff --git a/pgjdbc/src/main/java/org/postgresql/core/Parser.java b/pgjdbc/src/main/java/org/postgresql/core/Parser.javaindex d39684910b..430cce1396 100644--- a/pgjdbc/src/main/java/org/postgresql/core/Parser.java+++ b/pgjdbc/src/main/java/org/postgresql/core/Parser.java@@ -1430,6 +1430,7 @@ private static int parseSql(char[] sql, int i, StringBuilder newsql, boolean sto
case ESC_TIMESTAMP:
case ESC_OUTERJOIN:
case ESC_ESCAPECHAR:
+ case ESC_LIMIT:
if (c == '}') {
state = SqlParseState.IN_SQLCODE; // end of escape code.
} else {
@@ -1531,6 +1532,7 @@ private static int escapeFunctionArguments(StringBuilder newsql, String function
private static final char[] QUOTE_OR_ALPHABETIC_MARKER = {'\"', '0'};
private static final char[] QUOTE_OR_ALPHABETIC_MARKER_OR_PARENTHESIS = {'\"', '0', '('};
private static final char[] SINGLE_QUOTE = {'\''};
+ private static final char[] DIGIT_OR_PARAMETER_MARKER = {'#', '?'};
// Static variables for parsing SQL when replaceProcessing is true.
@SuppressWarnings("ImmutableEnumChecker")
@@ -1542,7 +1544,8 @@ private enum SqlParseState {
ESC_TIMESTAMP("ts", SINGLE_QUOTE, "TIMESTAMP "),
ESC_FUNCTION("fn", QUOTE_OR_ALPHABETIC_MARKER, null),
ESC_OUTERJOIN("oj", QUOTE_OR_ALPHABETIC_MARKER_OR_PARENTHESIS, null),
- ESC_ESCAPECHAR("escape", SINGLE_QUOTE, "ESCAPE ");+ ESC_ESCAPECHAR("escape", SINGLE_QUOTE, "ESCAPE "),+ ESC_LIMIT("limit", DIGIT_OR_PARAMETER_MARKER, "LIMIT ");
private static final SqlParseState[] VALUES = values();
@@ -1594,7 +1597,8 @@ private int getMatchedPosition(char[] sql, int pos) {
curr = sql[newPos];
}
for (char c : allowedValues) {
- if (curr == c || (c == '0' && Character.isLetter(curr))) {+ if (curr == c || (c == '0' && Character.isLetter(curr))+ || (c == '#' && curr >= '0' && curr <= '9')) {
return newPos - pos;
}
}
diff --git a/pgjdbc/src/test/java/org/postgresql/core/ParserTest.java b/pgjdbc/src/test/java/org/postgresql/core/ParserTest.javaindex fdca32526a..65c8df98d0 100644--- a/pgjdbc/src/test/java/org/postgresql/core/ParserTest.java+++ b/pgjdbc/src/test/java/org/postgresql/core/ParserTest.java@@ -142,6 +142,41 @@ void escapeProcessing() throws Exception {
assertEquals("{obj : 1}", Parser.replaceProcessing("{obj : 1}", true, false));
}
+ @ParameterizedTest+ @ValueSource(strings = {"0", "1", "9", "2147483648", "?", "10 OFFSET 2", "? OFFSET ?"})+ void limitEscape(String clause) throws SQLException {+ assertEquals("SELECT 1 LIMIT " + clause,+ Parser.replaceProcessing("SELECT 1 {limit " + clause + "}", true, true));+ }++ @Test+ void limitEscapeCaseAndNesting() throws SQLException {+ assertEquals("SELECT 1 LIMIT 2 offset 1",+ Parser.replaceProcessing("SELECT 1 {LiMiT 2 offset 1}", true, true));+ assertEquals("SELECT * FROM (SELECT 1 LIMIT 1) q LIMIT 2",+ Parser.replaceProcessing("SELECT * FROM (SELECT 1 {limit 1}) q {limit 2}", true, true));+ }++ @ParameterizedTest+ @ValueSource(strings = {+ "SELECT '{limit 1}'",+ "SELECT \"{limit 1}\"",+ "SELECT $tag${limit 1}$tag$",+ "SELECT 1 /* {limit 1} */",+ "SELECT 1 -- {limit 1}",+ "SELECT 1 {limitation 1}",+ "SELECT 1 {limit -1}"+ })+ void limitEscapeDoesNotChangeOtherSql(String sql) throws SQLException {+ assertEquals(sql, Parser.replaceProcessing(sql, true, true));+ }++ @Test+ void disabledLimitEscapeProcessing() throws SQLException {+ String sql = "SELECT 1 {limit 1}";+ assertEquals(sql, Parser.replaceProcessing(sql, false, true));+ }+
@Test
void timestampAddDiffFracSecondIsRejected() throws Exception {
// SQL_TSI_FRAC_SECOND has no portable size across databases (nanoseconds in ODBC/SQL Server,
diff --git a/pgjdbc/src/test/java/org/postgresql/test/jdbc2/StatementTest.java b/pgjdbc/src/test/java/org/postgresql/test/jdbc2/StatementTest.javaindex 2fcfa6b5d0..9cd3d2821e 100644--- a/pgjdbc/src/test/java/org/postgresql/test/jdbc2/StatementTest.java+++ b/pgjdbc/src/test/java/org/postgresql/test/jdbc2/StatementTest.java@@ -10,6 +10,7 @@
import static org.junit.jupiter.api.Assertions.assertNotEquals;
import static org.junit.jupiter.api.Assertions.assertNotNull;
import static org.junit.jupiter.api.Assertions.assertNull;
+import static org.junit.jupiter.api.Assertions.assertThrows;
import static org.junit.jupiter.api.Assertions.assertTrue;
import static org.junit.jupiter.api.Assertions.fail;
import static org.junit.jupiter.api.Assumptions.assumeTrue;
@@ -227,6 +228,55 @@ void escapeProcessing() throws SQLException {
assertEquals("%found", rs.getString(1));
}
+ @Test+ void limitEscapeStatement() throws SQLException {+ try (Statement stmt = con.createStatement();+ ResultSet rs = stmt.executeQuery("SELECT 1 ORDER BY 1 {limit 1}")) {+ assertTrue(rs.next());+ assertEquals(1, rs.getInt(1));+ assertFalse(rs.next());+ }+ String sql = "SELECT n FROM generate_series(1, 5) n ORDER BY n {limit 2 offset 1}";+ assertEquals("SELECT n FROM generate_series(1, 5) n ORDER BY n LIMIT 2 offset 1",+ con.nativeSQL(sql));+ try (Statement stmt = con.createStatement();+ ResultSet rs = stmt.executeQuery(sql)) {+ assertTrue(rs.next());+ assertEquals(2, rs.getInt(1));+ assertTrue(rs.next());+ assertEquals(3, rs.getInt(1));+ assertFalse(rs.next());+ }+ }++ @Test+ void limitEscapePreparedStatement() throws SQLException {+ try (PreparedStatement stmt = con.prepareStatement(+ "SELECT n FROM generate_series(1, 5) n ORDER BY n {limit ? offset ?}")) {+ for (int[] bounds : new int[][]{{2, 1}, {0, 0}, {5, 10}, {5, 0}}) {+ stmt.setInt(1, bounds[0]);+ stmt.setInt(2, bounds[1]);+ try (ResultSet rs = stmt.executeQuery()) {+ int count = 0;+ while (rs.next()) {+ assertEquals(bounds[1] + ++count, rs.getInt(1));+ }+ assertEquals(Math.min(bounds[0], Math.max(0, 5 - bounds[1])), count);+ }+ }+ }+ }++ @Test+ void disabledLimitEscapeStatement() throws SQLException {+ try (Statement stmt = con.createStatement()) {+ stmt.setEscapeProcessing(false);+ SQLException failure = assertThrows(SQLException.class,+ () -> stmt.executeQuery("SELECT 1 {limit 1}"));+ assertEquals(PSQLState.SYNTAX_ERROR.getState(), failure.getSQLState());+ }+ }+
@Test
void preparedFunction() throws SQLException {
PreparedStatement pstmt = con.prepareStatement("SELECT {fn concat('a', ?)}");