This is an automated email from the ASF dual-hosted git repository.
mihaibudiu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new b81f46f4ca [CALCITE-7761] Preserve OFFSET and FETCH when rewriting
outer ORDER BY
b81f46f4ca is described below
commit b81f46f4ca42a904cfdeef265012f45fa18f6cc3
Author: Kirill Tkalenko <[email protected]>
AuthorDate: Mon Sep 7 07:05:09 2026 +0300
[CALCITE-7761] Preserve OFFSET and FETCH when rewriting outer ORDER BY
---
.../calcite/sql/validate/SqlValidatorImpl.java | 18 ++++---
.../org/apache/calcite/test/SqlValidatorTest.java | 57 ++++++++++++++++++++++
2 files changed, 69 insertions(+), 6 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
index 129fd91252..fdf94b9f4d 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
@@ -1904,9 +1904,12 @@ && requireNonNull(((SqlNumericLiteral)
node).bigDecimalValue())
if (orderBy.query instanceof SqlSelect) {
SqlSelect select = (SqlSelect) orderBy.query;
- // Don't clobber existing ORDER BY. It may be needed for
- // an order-sensitive function like RANK.
- if (select.getOrderList() == null) {
+ // Don't clobber existing ORDER BY, OFFSET, or FETCH. ORDER BY may be
+ // needed for an order-sensitive function like RANK; OFFSET and FETCH
+ // must remain on the query where they were introduced.
+ if (select.getOrderList() == null
+ && select.getOffset() == null
+ && select.getFetch() == null) {
// push ORDER BY into existing select
select.setOrderBy(orderBy.orderList);
select.setOffset(orderBy.offset);
@@ -1919,9 +1922,12 @@ && requireNonNull(((SqlNumericLiteral)
node).bigDecimalValue())
SqlWith with = (SqlWith) orderBy.query;
SqlSelect select = (SqlSelect) with.body;
- // Don't clobber existing ORDER BY. It may be needed for
- // an order-sensitive function like RANK.
- if (select.getOrderList() == null) {
+ // Don't clobber existing ORDER BY, OFFSET, or FETCH. ORDER BY may be
+ // needed for an order-sensitive function like RANK; OFFSET and FETCH
+ // must remain on the query where they were introduced.
+ if (select.getOrderList() == null
+ && select.getOffset() == null
+ && select.getFetch() == null) {
// push ORDER BY into existing select
select.setOrderBy(orderBy.orderList);
select.setOffset(orderBy.offset);
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index 6390c8ca7e..7ba6a4d389 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -7561,6 +7561,30 @@ public boolean isBangEqualAllowed() {
sql("select * from emp as e, dept as d order by e.empno").ok();
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7761";>[CALCITE-7761]
+ * Preserve OFFSET and FETCH when rewriting outer ORDER BY</a>. */
+ @Test void testOrderByPreservesFetchAddedByRewrite() {
+ final SqlValidatorFixture fixture = fixture()
+ .withFactory(f -> f.withValidator(RownumRewritingValidator::new));
+
+ fixture.withSql("select empno from emp where rownum <= 5 order by empno")
+ .rewritesTo("SELECT *\n"
+ + "FROM (SELECT `EMPNO`\n"
+ + "FROM `EMP`\n"
+ + "FETCH NEXT 5 ROWS ONLY)\n"
+ + "ORDER BY `EXPR$0`.`EMPNO`");
+
+ fixture.withSql("with e as (select deptno as empno from dept)\n"
+ + "select empno from e where rownum <= 5 order by empno")
+ .rewritesTo("SELECT *\n"
+ + "FROM (WITH `E` AS (SELECT `DEPTNO` AS `EMPNO`\n"
+ + "FROM `DEPT`) SELECT `EMPNO`\n"
+ + "FROM `E`\n"
+ + "FETCH NEXT 5 ROWS ONLY)\n"
+ + "ORDER BY `EXPR$0`.`EMPNO`");
+ }
+
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-633";>[CALCITE-633]
* WITH ... ORDER BY cannot find table</a>. */
@@ -15262,6 +15286,39 @@ private static SqlIdentifier
rewriteIdentifier(SqlIdentifier sqlIdentifier) {
}
}
+ /** Validator that simulates a rewrite performed by an external application.
+ * Calcite's parser does not produce a {@link SqlSelect} with FETCH and a
null
+ * order list, but an external rewrite such as replacing a ROWNUM predicate
+ * with FETCH can produce that tree shape. */
+ private static class RownumRewritingValidator extends SqlValidatorImpl {
+ RownumRewritingValidator(SqlOperatorTable opTab,
+ SqlValidatorCatalogReader catalogReader,
+ RelDataTypeFactory typeFactory, Config config) {
+ super(opTab, catalogReader, typeFactory, config);
+ }
+
+ @Override protected SqlNode performUnconditionalRewrites(SqlNode node,
+ boolean underFrom) {
+ node = super.performUnconditionalRewrites(node, underFrom);
+ if (node instanceof SqlSelect) {
+ final SqlSelect select = (SqlSelect) node;
+ final SqlNode where = select.getWhere();
+ if (where instanceof SqlCall
+ && where.getKind() == SqlKind.LESS_THAN_OR_EQUAL) {
+ final SqlCall call = (SqlCall) where;
+ final SqlNode left = call.operand(0);
+ if (left instanceof SqlIdentifier
+ && ((SqlIdentifier) left).isSimple()
+ && ((SqlIdentifier)
left).getSimple().equalsIgnoreCase("ROWNUM")) {
+ select.setWhere(null);
+ select.setFetch(call.operand(1));
+ }
+ }
+ }
+ return node;
+ }
+ }
+
/** Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-7582";>[CALCITE-7582]
* Type validation errors should use SQL type names</a>. */
@Test public void testErrorMessage() {
