This is an automated email from the ASF dual-hosted git repository.
terrymanu pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new a4022e9466d Fix Firebird OFFSET/FETCH rewrite by visiting limitClause
(#39955)
a4022e9466d is described below
commit a4022e9466d7966094e53bd1985f88e4fcefdb98
Author: Zhicheng Lin <[email protected]>
AuthorDate: Sun Oct 18 23:02:08 2026 +0800
Fix Firebird OFFSET/FETCH rewrite by visiting limitClause (#39955)
* Fix Firebird OFFSET/FETCH rewrite by visiting limitClause
Firebird select statements that use OFFSET ... ROWS FETCH FIRST ... ROWS
ONLY
were never rewritten, because the pagination information never reached a
LimitSegment:
- FirebirdDMLStatementVisitor.visitSelectClause handled only firstSkipClause
(FIRST n / SKIP n, the Firebird-specific syntax) and ignored the
limitClause
that selectClause declares in DMLStatement.g4.
- orderByClause also carried a trailing limitClause?. ANTLR's greedy
prediction
consumed the clause inside orderByClause, so for a statement with ORDER
BY the
outer ctx.limitClause() was null. orderByClause is reused by window
specifications (OVER (...)) and GROUP_CONCAT(...), where accepting a
LIMIT is
wrong regardless, and every statement that wants one already declares its
own
limitClause? (DML / DAL / DDL), so the nested one is removed.
With both changes, SELECT * FROM t_order ORDER BY order_id OFFSET 100000
ROWS
FETCH FIRST 10 ROWS ONLY parses to a LimitSegment with offset=100000 and
rowCount=10, and the sharding pagination rewrite can apply.
Anchors in visitLimitClause are taken from the matched context, and the
rowsClause branch guards the optional TO so ROWS n cannot run past the end
of
the expression list.
Tests: the existing select_pagination_with_offset_fetch case is limited to
SQLServer, PostgreSQL and openGauss and never covered Firebird, which is why
this went unnoticed. Added two Firebird cases to sql/supported/dml and the
matching expectations to case/dml, one without ORDER BY and the one from the
report with ORDER BY.
Verified locally: InternalFirebirdParserIT passes 238/238 with both changes.
With only the visitor change (grammar untouched) the ORDER BY case still
fails
with "Offset should not exist"; with only the grammar change both cases fail
with "Actual limit segment should exist", so both halves are required.
Closes #39926
* Fix Firebird pagination parameter marker indexes per review
* Format Firebird visitor per Spotless
* Complete Firebird pagination after merging shard results
* Polish Firebird pagination builder formatting
* Fix registerBlob call missing boolean argument in Firebird blob test
* Visit WITH clause in SQL order and exercise Firebird SPI in pagination
tests
- Visit withClause before combineClause so parameter markers are numbered
in SQL order when a CTE precedes OFFSET/FETCH, and attach parameter
markers after the with-segment rebuild so they are not dropped
- Add Firebird parser IT with a preceding parameterized CTE
- Add shardingsphere-database-connector-firebird test dependency so the
pagination builder test resolves the actual Firebird database type
- Load the builder via DatabaseTypedSPILoader and assert the registration
path instead of direct construction
- Move the cross-shard merge decoration assertion into
ShardingDQLResultMergerTest and drop the handwritten MergedResult stub
* Polish Firebird pagination tests per review standards
- Merge the short SelectStatementContext declaration into one line in
ShardingDQLResultMergerTest
- Rename the sole builder test to assertBuild per CODE_OF_CONDUCT.md
---------
Co-authored-by: lzcGeek <[email protected]>
---
features/sharding/core/pom.xml | 6 +
...birdPaginationDecoratorMergedResultBuilder.java | 42 +++++++
....builder.PaginationDecoratorMergedResultBuilder | 1 +
.../merge/dql/ShardingDQLResultMergerTest.java | 26 +++++
...PaginationDecoratorMergedResultBuilderTest.java | 48 ++++++++
.../src/main/antlr4/imports/firebird/BaseRule.g4 | 2 +-
.../type/FirebirdDMLStatementVisitor.java | 53 ++++++++-
.../main/resources/case/dml/select-pagination.xml | 126 +++++++++++++++++++++
.../sql/supported/dml/select-pagination.xml | 5 +
9 files changed, 303 insertions(+), 6 deletions(-)
diff --git a/features/sharding/core/pom.xml b/features/sharding/core/pom.xml
index 4245824fd64..6bd37aef29e 100644
--- a/features/sharding/core/pom.xml
+++ b/features/sharding/core/pom.xml
@@ -139,6 +139,12 @@
<version>${project.version}</version>
<scope>test</scope>
</dependency>
+ <dependency>
+ <groupId>org.apache.shardingsphere</groupId>
+ <artifactId>shardingsphere-database-connector-firebird</artifactId>
+ <version>${project.version}</version>
+ <scope>test</scope>
+ </dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-test-it-yaml</artifactId>
diff --git
a/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/merge/dql/pagination/builder/dialect/FirebirdPaginationDecoratorMergedResultBuilder.java
b/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/merge/dql/pagination/builder/dialect/FirebirdPaginationDecoratorMergedResultBuilder.java
new file mode 100644
index 00000000000..15649fcb727
--- /dev/null
+++
b/features/sharding/core/src/main/java/org/apache/shardingsphere/sharding/merge/dql/pagination/builder/dialect/FirebirdPaginationDecoratorMergedResultBuilder.java
@@ -0,0 +1,42 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package
org.apache.shardingsphere.sharding.merge.dql.pagination.builder.dialect;
+
+import
org.apache.shardingsphere.infra.binder.context.segment.select.pagination.PaginationContext;
+import org.apache.shardingsphere.infra.merge.result.MergedResult;
+import
org.apache.shardingsphere.infra.merge.result.impl.decorator.DecoratorMergedResult;
+import
org.apache.shardingsphere.sharding.merge.dql.pagination.LimitDecoratorMergedResult;
+import
org.apache.shardingsphere.sharding.merge.dql.pagination.builder.PaginationDecoratorMergedResultBuilder;
+
+import java.sql.SQLException;
+
+/**
+ * Pagination decorator merged result builder for Firebird.
+ */
+public final class FirebirdPaginationDecoratorMergedResultBuilder implements
PaginationDecoratorMergedResultBuilder {
+
+ @Override
+ public DecoratorMergedResult build(final MergedResult mergedResult, final
PaginationContext paginationContext) throws SQLException {
+ return new LimitDecoratorMergedResult(mergedResult, paginationContext);
+ }
+
+ @Override
+ public String getDatabaseType() {
+ return "Firebird";
+ }
+}
diff --git
a/features/sharding/core/src/main/resources/META-INF/services/org.apache.shardingsphere.sharding.merge.dql.pagination.builder.PaginationDecoratorMergedResultBuilder
b/features/sharding/core/src/main/resources/META-INF/services/org.apache.shardingsphere.sharding.merge.dql.pagination.builder.PaginationDecoratorMergedResultBuilder
index 4ddcd32bdec..8db9c30581e 100644
---
a/features/sharding/core/src/main/resources/META-INF/services/org.apache.shardingsphere.sharding.merge.dql.pagination.builder.PaginationDecoratorMergedResultBuilder
+++
b/features/sharding/core/src/main/resources/META-INF/services/org.apache.shardingsphere.sharding.merge.dql.pagination.builder.PaginationDecoratorMergedResultBuilder
@@ -20,3 +20,4 @@
org.apache.shardingsphere.sharding.merge.dql.pagination.builder.dialect.PostgreS
org.apache.shardingsphere.sharding.merge.dql.pagination.builder.dialect.OpenGaussPaginationDecoratorMergedResultBuilder
org.apache.shardingsphere.sharding.merge.dql.pagination.builder.dialect.OraclePaginationDecoratorMergedResultBuilder
org.apache.shardingsphere.sharding.merge.dql.pagination.builder.dialect.SQLServerPaginationDecoratorMergedResultBuilder
+org.apache.shardingsphere.sharding.merge.dql.pagination.builder.dialect.FirebirdPaginationDecoratorMergedResultBuilder
diff --git
a/features/sharding/core/src/test/java/org/apache/shardingsphere/sharding/merge/dql/ShardingDQLResultMergerTest.java
b/features/sharding/core/src/test/java/org/apache/shardingsphere/sharding/merge/dql/ShardingDQLResultMergerTest.java
index bcd547a2d8e..877373a8a98 100644
---
a/features/sharding/core/src/test/java/org/apache/shardingsphere/sharding/merge/dql/ShardingDQLResultMergerTest.java
+++
b/features/sharding/core/src/test/java/org/apache/shardingsphere/sharding/merge/dql/ShardingDQLResultMergerTest.java
@@ -100,6 +100,8 @@ class ShardingDQLResultMergerTest {
private final DatabaseType sqlserverDatabaseType =
TypedSPILoader.getService(DatabaseType.class, "SQLServer");
+ private final DatabaseType firebirdDatabaseType =
TypedSPILoader.getService(DatabaseType.class, "Firebird");
+
@Test
void assertBuildIteratorStreamMergedResult() throws SQLException {
ShardingDQLResultMerger resultMerger = new
ShardingDQLResultMerger(mysqlDatabaseType);
@@ -197,6 +199,30 @@ class ShardingDQLResultMergerTest {
assertFalse(actual.next());
}
+ @Test
+ void assertBuildIteratorStreamMergedResultWithFirebirdOffsetFetch() throws
SQLException {
+ ShardingDQLResultMerger resultMerger = new
ShardingDQLResultMerger(firebirdDatabaseType);
+ ShardingSphereDatabase database = mock(ShardingSphereDatabase.class,
RETURNS_DEEP_STUBS);
+ SelectStatement selectStatement =
buildSelectStatement(firebirdDatabaseType);
+ selectStatement = withProjections(selectStatement, new
ProjectionsSegment(0, 0));
+ selectStatement = withLimit(selectStatement, new LimitSegment(0, 0,
new NumberLiteralLimitValueSegment(0, 0, 2L), new
NumberLiteralLimitValueSegment(0, 0, 2L)));
+ SelectStatementContext selectStatementContext = new
SelectStatementContext(selectStatement, createShardingSphereMetaData(database),
"foo_db", Collections.emptyList());
+ MergedResult actual = resultMerger.merge(createFourQueryResults(),
selectStatementContext, createDialectDatabase(firebirdDatabaseType),
mock(ConnectionContext.class));
+ assertTrue(actual.next());
+ assertTrue(actual.next());
+ assertFalse(actual.next());
+ }
+
+ private List<QueryResult> createFourQueryResults() throws SQLException {
+ List<QueryResult> result = new LinkedList<>();
+ for (int i = 0; i < 4; i++) {
+ QueryResult queryResult = createQueryResult();
+ when(queryResult.next()).thenReturn(true, true, false);
+ result.add(queryResult);
+ }
+ return result;
+ }
+
@Test
void assertBuildOrderByStreamMergedResult() throws SQLException {
ShardingDQLResultMerger resultMerger = new
ShardingDQLResultMerger(mysqlDatabaseType);
diff --git
a/features/sharding/core/src/test/java/org/apache/shardingsphere/sharding/merge/dql/pagination/builder/dialect/FirebirdPaginationDecoratorMergedResultBuilderTest.java
b/features/sharding/core/src/test/java/org/apache/shardingsphere/sharding/merge/dql/pagination/builder/dialect/FirebirdPaginationDecoratorMergedResultBuilderTest.java
new file mode 100644
index 00000000000..0802e622d37
--- /dev/null
+++
b/features/sharding/core/src/test/java/org/apache/shardingsphere/sharding/merge/dql/pagination/builder/dialect/FirebirdPaginationDecoratorMergedResultBuilderTest.java
@@ -0,0 +1,48 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package
org.apache.shardingsphere.sharding.merge.dql.pagination.builder.dialect;
+
+import
org.apache.shardingsphere.database.connector.core.spi.DatabaseTypedSPILoader;
+import org.apache.shardingsphere.database.connector.core.type.DatabaseType;
+import
org.apache.shardingsphere.infra.binder.context.segment.select.pagination.PaginationContext;
+import org.apache.shardingsphere.infra.merge.result.MergedResult;
+import org.apache.shardingsphere.infra.spi.type.typed.TypedSPILoader;
+import
org.apache.shardingsphere.sharding.merge.dql.pagination.LimitDecoratorMergedResult;
+import
org.apache.shardingsphere.sharding.merge.dql.pagination.builder.PaginationDecoratorMergedResultBuilder;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.pagination.limit.NumberLiteralLimitValueSegment;
+import org.junit.jupiter.api.Test;
+
+import java.sql.SQLException;
+import java.util.Collections;
+
+import static org.hamcrest.MatcherAssert.assertThat;
+import static org.hamcrest.Matchers.isA;
+import static org.mockito.Mockito.mock;
+
+class FirebirdPaginationDecoratorMergedResultBuilderTest {
+
+ private final DatabaseType databaseType =
TypedSPILoader.getService(DatabaseType.class, "Firebird");
+
+ @Test
+ void assertBuild() throws SQLException {
+ PaginationContext paginationContext = new PaginationContext(new
NumberLiteralLimitValueSegment(0, 0, 10L), new
NumberLiteralLimitValueSegment(0, 0, 10L), Collections.emptyList());
+ PaginationDecoratorMergedResultBuilder builder =
DatabaseTypedSPILoader.getService(PaginationDecoratorMergedResultBuilder.class,
databaseType);
+ assertThat(builder,
isA(FirebirdPaginationDecoratorMergedResultBuilder.class));
+ assertThat(builder.build(mock(MergedResult.class), paginationContext),
isA(LimitDecoratorMergedResult.class));
+ }
+}
diff --git
a/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/BaseRule.g4
b/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/BaseRule.g4
index ae1d3b57f45..5871ae36469 100644
---
a/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/BaseRule.g4
+++
b/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/BaseRule.g4
@@ -389,7 +389,7 @@ subquery
;
orderByClause
- : ORDER BY orderByItem (COMMA_ orderByItem)* limitClause?
+ : ORDER BY orderByItem (COMMA_ orderByItem)*
;
orderByItem
diff --git
a/parser/sql/engine/dialect/firebird/src/main/java/org/apache/shardingsphere/sql/parser/engine/firebird/visitor/statement/type/FirebirdDMLStatementVisitor.java
b/parser/sql/engine/dialect/firebird/src/main/java/org/apache/shardingsphere/sql/parser/engine/firebird/visitor/statement/type/FirebirdDMLStatementVisitor.java
index 7c66c049d50..affb013abed 100644
---
a/parser/sql/engine/dialect/firebird/src/main/java/org/apache/shardingsphere/sql/parser/engine/firebird/visitor/statement/type/FirebirdDMLStatementVisitor.java
+++
b/parser/sql/engine/dialect/firebird/src/main/java/org/apache/shardingsphere/sql/parser/engine/firebird/visitor/statement/type/FirebirdDMLStatementVisitor.java
@@ -44,8 +44,12 @@ import
org.apache.shardingsphere.sql.parser.autogen.FirebirdStatementParser.Inse
import
org.apache.shardingsphere.sql.parser.autogen.FirebirdStatementParser.InsertValuesClauseContext;
import
org.apache.shardingsphere.sql.parser.autogen.FirebirdStatementParser.JoinSpecificationContext;
import
org.apache.shardingsphere.sql.parser.autogen.FirebirdStatementParser.JoinedTableContext;
+import
org.apache.shardingsphere.sql.parser.autogen.FirebirdStatementParser.LimitClauseContext;
import
org.apache.shardingsphere.sql.parser.autogen.FirebirdStatementParser.MergeContext;
+import
org.apache.shardingsphere.sql.parser.autogen.FirebirdStatementParser.NumberLiteralsContext;
+import
org.apache.shardingsphere.sql.parser.autogen.FirebirdStatementParser.OffsetDefinitionContext;
import
org.apache.shardingsphere.sql.parser.autogen.FirebirdStatementParser.OrderByItemContext;
+import
org.apache.shardingsphere.sql.parser.autogen.FirebirdStatementParser.ParameterMarkerContext;
import
org.apache.shardingsphere.sql.parser.autogen.FirebirdStatementParser.ProjectionContext;
import
org.apache.shardingsphere.sql.parser.autogen.FirebirdStatementParser.ProjectionsContext;
import
org.apache.shardingsphere.sql.parser.autogen.FirebirdStatementParser.QualifiedShorthandContext;
@@ -257,11 +261,12 @@ public final class FirebirdDMLStatementVisitor extends
FirebirdStatementVisitor
@Override
public ASTNode visitSelect(final SelectContext ctx) {
+ WithSegment withSegment = null == ctx.withClause() ? null :
(WithSegment) visit(ctx.withClause());
SelectStatement result = (SelectStatement) visit(ctx.combineClause());
- result.addParameterMarkers(getParameterMarkerSegments());
- if (null != ctx.withClause()) {
- result = createSelectStatementBuilder(result).with((WithSegment)
visit(ctx.withClause())).build();
+ if (null != withSegment) {
+ result =
createSelectStatementBuilder(result).with(withSegment).build();
}
+ result.addParameterMarkers(getParameterMarkerSegments());
return result;
}
@@ -334,6 +339,9 @@ public final class FirebirdDMLStatementVisitor extends
FirebirdStatementVisitor
if (null != ctx.havingClause()) {
selectStatementBuilder.having((HavingSegment)
visit(ctx.havingClause()));
}
+ if (null != ctx.limitClause()) {
+ selectStatementBuilder.limit((LimitSegment)
visit(ctx.limitClause()));
+ }
return selectStatementBuilder.build();
}
@@ -352,6 +360,40 @@ public final class FirebirdDMLStatementVisitor extends
FirebirdStatementVisitor
return new LimitSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), offset, rowCount);
}
+ @Override
+ public ASTNode visitLimitClause(final LimitClauseContext ctx) {
+ PaginationValueSegment rowCount = null;
+ PaginationValueSegment offset = null;
+ if (null != ctx.rowsClause()) {
+ offset = (PaginationValueSegment)
visitLimitValue(ctx.rowsClause().expr(0));
+ if (ctx.rowsClause().expr().size() > 1) {
+ rowCount = (PaginationValueSegment)
visitLimitValue(ctx.rowsClause().expr(1));
+ }
+ } else {
+ OffsetDefinitionContext offsetDefinition = ctx.offsetDefinition();
+ if (null != offsetDefinition.offsetClause()) {
+ offset = (PaginationValueSegment)
visitLimitValue(offsetDefinition.offsetClause().limitOffset());
+ }
+ if (null != offsetDefinition.fetchClause()) {
+ rowCount = (PaginationValueSegment)
visitLimitValue(offsetDefinition.fetchClause().limitRowCount());
+ }
+ }
+ return new LimitSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), offset, rowCount);
+ }
+
+ private ASTNode visitLimitValue(final ParserRuleContext ctx) {
+ if (ctx.getChild(0) instanceof NumberLiteralsContext) {
+ return new
NumberLiteralLimitValueSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), ((NumberLiteralValue)
visit(ctx.getChild(0))).getValue().longValue());
+ }
+ if (ctx.getChild(0) instanceof ParameterMarkerContext) {
+ ParameterMarkerSegment result =
+ new
ParameterMarkerLimitValueSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), ((ParameterMarkerValue)
visit(ctx.getChild(0))).getValue());
+ getParameterMarkerSegments().add(result);
+ return result;
+ }
+ return null;
+ }
+
@Override
public ASTNode visitFirstValue(final FirstValueContext ctx) {
// TODO Add expression support for firstSkipClause
@@ -615,9 +657,10 @@ public final class FirebirdDMLStatementVisitor extends
FirebirdStatementVisitor
@Override
public ASTNode visitSubquery(final SubqueryContext ctx) {
+ WithSegment withSegment = null == ctx.withClause() ? null :
(WithSegment) visit(ctx.withClause());
SelectStatement result = (SelectStatement) visit(ctx.combineClause());
- if (null != ctx.withClause()) {
- result = createSelectStatementBuilder(result).with((WithSegment)
visit(ctx.withClause())).build();
+ if (null != withSegment) {
+ result =
createSelectStatementBuilder(result).with(withSegment).build();
}
return result;
}
diff --git a/test/it/parser/src/main/resources/case/dml/select-pagination.xml
b/test/it/parser/src/main/resources/case/dml/select-pagination.xml
index e13f58797ea..6a7151f244c 100644
--- a/test/it/parser/src/main/resources/case/dml/select-pagination.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-pagination.xml
@@ -2084,6 +2084,35 @@
</from>
</select>
+ <select sql-case-id="select_pagination_with_offset_and_fetch_firebird">
+ <limit start-index="22" stop-index="64">
+ <offset value="100000" start-index="29" stop-index="34" />
+ <row-count value="10" start-index="53" stop-index="54" />
+ </limit>
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from start-index="14" stop-index="20">
+ <simple-table name="t_order" start-index="14" stop-index="20" />
+ </from>
+ </select>
+
+ <select
sql-case-id="select_pagination_with_order_by_offset_and_fetch_firebird">
+ <limit start-index="40" stop-index="82">
+ <offset value="100000" start-index="47" stop-index="52" />
+ <row-count value="10" start-index="71" stop-index="72" />
+ </limit>
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from start-index="14" stop-index="20">
+ <simple-table name="t_order" start-index="14" stop-index="20" />
+ </from>
+ <order-by>
+ <column-item name="order_id" start-index="31" stop-index="38" />
+ </order-by>
+ </select>
+
<select
sql-case-id="select_pagination_with_first_skip_parameters_firebird"
parameters="1, 2">
<limit start-index="7" stop-index="20">
<row-count value="1" parameter-index="0" start-index="13"
stop-index="13" />
@@ -2097,6 +2126,103 @@
</from>
</select>
+ <select
sql-case-id="select_pagination_with_where_parameter_offset_and_fetch_firebird"
parameters="1, 2, 3">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from start-index="14" stop-index="20">
+ <simple-table name="t_order" start-index="14" stop-index="20" />
+ </from>
+ <where start-index="22" stop-index="38">
+ <expr>
+ <binary-operation-expression start-index="28" stop-index="38">
+ <left>
+ <column name="user_id" start-index="28"
stop-index="34" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="1" start-index="38"
stop-index="38" />
+ <parameter-marker-expression parameter-index="0"
start-index="38" stop-index="38" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ <order-by>
+ <column-item name="order_id" start-index="49" stop-index="56" />
+ </order-by>
+ <limit start-index="58" stop-index="94" literal-start-index="58"
literal-stop-index="94">
+ <offset value="2" parameter-index="1" start-index="65"
stop-index="65" literal-start-index="65" literal-stop-index="65" />
+ <row-count value="3" parameter-index="2" start-index="84"
stop-index="84" literal-start-index="84" literal-stop-index="84" />
+ </limit>
+ </select>
+
+ <select
sql-case-id="select_pagination_with_where_parameter_fetch_firebird"
parameters="1, 2">
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ <from start-index="14" stop-index="20">
+ <simple-table name="t_order" start-index="14" stop-index="20" />
+ </from>
+ <where start-index="22" stop-index="38">
+ <expr>
+ <binary-operation-expression start-index="28" stop-index="38">
+ <left>
+ <column name="user_id" start-index="28"
stop-index="34" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="1" start-index="38"
stop-index="38" />
+ <parameter-marker-expression parameter-index="0"
start-index="38" stop-index="38" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ <limit start-index="40" stop-index="62" literal-start-index="40"
literal-stop-index="62">
+ <row-count value="2" parameter-index="1" start-index="52"
stop-index="52" literal-start-index="52" literal-stop-index="52" />
+ </limit>
+ </select>
+
+ <select
sql-case-id="select_pagination_with_cte_where_parameter_offset_and_fetch_firebird"
parameters="7, 8, 9">
+ <with start-index="0" stop-index="56">
+ <common-table-expression name="c" start-index="5" stop-index="56">
+ <subquery-expression start-index="5" stop-index="56">
+ <select>
+ <projections start-index="18" stop-index="24">
+ <column-projection name="user_id" start-index="18"
stop-index="24" />
+ </projections>
+ <from start-index="31" stop-index="37">
+ <simple-table name="t_order" start-index="31"
stop-index="37" />
+ </from>
+ <where start-index="39" stop-index="55">
+ <expr>
+ <binary-operation-expression start-index="45"
stop-index="55">
+ <left>
+ <column name="user_id"
start-index="45" stop-index="51" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="7"
start-index="55" stop-index="55" />
+ <parameter-marker-expression
parameter-index="0" start-index="55" stop-index="55" />
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+ </subquery-expression>
+ </common-table-expression>
+ </with>
+ <projections start-index="65" stop-index="65">
+ <shorthand-projection start-index="65" stop-index="65" />
+ </projections>
+ <from start-index="72" stop-index="72">
+ <simple-table name="c" start-index="72" stop-index="72" />
+ </from>
+ <limit start-index="74" stop-index="109" literal-start-index="74"
literal-stop-index="109">
+ <offset value="8" parameter-index="1" start-index="81"
stop-index="81" literal-start-index="81" literal-stop-index="81" />
+ <row-count value="9" parameter-index="2" start-index="99"
stop-index="99" literal-start-index="99" literal-stop-index="99" />
+ </limit>
+ </select>
+
<select sql-case-id="select_limit_offset_doris">
<from>
<simple-table name="t_order" start-index="14" stop-index="20" />
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-pagination.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-pagination.xml
index 757dffda74e..feb156ea114 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-pagination.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-pagination.xml
@@ -42,6 +42,11 @@
<sql-case id="select_pagination_with_first_skip" value="SELECT FIRST 1
SKIP 1 1 a FROM t_order" db-types="Firebird" />
<sql-case id="select_pagination_with_skip" value="SELECT SKIP 1 1 a FROM
t_order" db-types="Firebird" />
<sql-case id="select_pagination_with_first_skip_parameters_firebird"
value="SELECT FIRST ? SKIP ? user_id FROM t_order" db-types="Firebird" />
+ <sql-case id="select_pagination_with_offset_and_fetch_firebird"
value="SELECT * FROM t_order OFFSET 100000 ROWS FETCH FIRST 10 ROWS ONLY"
db-types="Firebird" />
+ <sql-case id="select_pagination_with_order_by_offset_and_fetch_firebird"
value="SELECT * FROM t_order ORDER BY order_id OFFSET 100000 ROWS FETCH FIRST
10 ROWS ONLY" db-types="Firebird" />
+ <sql-case
id="select_pagination_with_where_parameter_offset_and_fetch_firebird"
value="SELECT * FROM t_order WHERE user_id = ? ORDER BY order_id OFFSET ? ROWS
FETCH FIRST ? ROWS ONLY" db-types="Firebird" />
+ <sql-case id="select_pagination_with_where_parameter_fetch_firebird"
value="SELECT * FROM t_order WHERE user_id = ? FETCH FIRST ? ROWS ONLY"
db-types="Firebird" />
+ <sql-case
id="select_pagination_with_cte_where_parameter_offset_and_fetch_firebird"
value="WITH c AS (SELECT user_id FROM t_order WHERE user_id = ?) SELECT * FROM
c OFFSET ? ROWS FETCH NEXT ? ROWS ONLY" db-types="Firebird" />
<sql-case id="select_limit_offset_doris" value="SELECT * FROM t_order
LIMIT 5 OFFSET 2" db-types="Doris" />
<sql-case id="select_limit_only_doris" value="SELECT * FROM t_order LIMIT
5" db-types="Doris" />
<sql-case id="select_limit_with_params_doris" value="SELECT * FROM t_order
LIMIT ?, ?" db-types="Doris,Hive,openGauss" />
