pg.ddx.io  GitHub: pgjdbc/pgjdbc (issues + PRs as email threads)  
help / color / mirror / Atom feed
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.java index 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.java index 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.java index 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', ?)}");

Message-ID: <gh-pgjdbc-pgjdbc-4524@github.com>
Permalink:  ../gh-pgjdbc-pgjdbc-4524@github.com/

 ·  · 


This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox