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 7abc3480a65 Fix firebird order by nulls (#40179)
7abc3480a65 is described below
commit 7abc3480a6580dc7d7fd4ccb0fc36e828cd398b3
Author: Maxim Igoshin <[email protected]>
AuthorDate: Sat Oct 17 04:56:22 2026 +0300
Fix firebird order by nulls (#40179)
* Support NULLS FIRST and NULLS LAST in Firebird ORDER BY
* Fix CODE_OF_CONDUCT violations in Firebird ORDER BY NULLS parsing
---
.../src/main/antlr4/imports/firebird/BaseRule.g4 | 4 +-
.../antlr4/imports/firebird/FirebirdKeyword.g4 | 4 ++
.../statement/FirebirdStatementVisitor.java | 17 ++++++--
.../main/resources/case/dml/select-order-by.xml | 48 ++++++++++++++++++++++
.../sql/supported/dml/select-order-by.xml | 3 ++
5 files changed, 71 insertions(+), 5 deletions(-)
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 333ffdfa9ef..8901ee0789f 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
@@ -76,7 +76,7 @@ unreservedWord
| FORTRAN
| LENGTH
| MESSAGE_LENGTH | MESSAGE_OCTET_LENGTH | MESSAGE_TEXT | MORE92 | MUMPS
- | NAME | NULLABLE | NUMBER
+ | NAME | NULLABLE | NULLS | NUMBER
| ORDER
| PASCAL | PLI
| REPEATABLE | RETURNED_LENGTH | RETURNED_OCTET_LENGTH | RETURNED_SQLSTATE
| ROW_COUNT
@@ -394,7 +394,7 @@ orderByClause
;
orderByItem
- : (numberLiterals | expr) (ASC | DESC)?
+ : (numberLiterals | expr) (ASC | DESC)? (NULLS FIRST | NULLS LAST)?
;
limitClause
diff --git
a/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/FirebirdKeyword.g4
b/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/FirebirdKeyword.g4
index 238b894e458..efaf7108d31 100644
---
a/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/FirebirdKeyword.g4
+++
b/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/FirebirdKeyword.g4
@@ -507,6 +507,10 @@ NULLIF
: N U L L I F
;
+NULLS
+ : N U L L S
+ ;
+
NUMERIC
: N U M E R I C
;
diff --git
a/parser/sql/engine/dialect/firebird/src/main/java/org/apache/shardingsphere/sql/parser/engine/firebird/visitor/statement/FirebirdStatementVisitor.java
b/parser/sql/engine/dialect/firebird/src/main/java/org/apache/shardingsphere/sql/parser/engine/firebird/visitor/statement/FirebirdStatementVisitor.java
index d03f6c83248..17cd8598154 100644
---
a/parser/sql/engine/dialect/firebird/src/main/java/org/apache/shardingsphere/sql/parser/engine/firebird/visitor/statement/FirebirdStatementVisitor.java
+++
b/parser/sql/engine/dialect/firebird/src/main/java/org/apache/shardingsphere/sql/parser/engine/firebird/visitor/statement/FirebirdStatementVisitor.java
@@ -24,6 +24,7 @@ import org.antlr.v4.runtime.ParserRuleContext;
import org.antlr.v4.runtime.Token;
import org.antlr.v4.runtime.misc.Interval;
import org.antlr.v4.runtime.tree.TerminalNode;
+import
org.apache.shardingsphere.database.connector.core.metadata.database.enums.NullsOrderType;
import org.apache.shardingsphere.database.connector.core.type.DatabaseType;
import org.apache.shardingsphere.sql.parser.api.ASTNode;
import
org.apache.shardingsphere.sql.parser.autogen.FirebirdStatementBaseVisitor;
@@ -116,6 +117,7 @@ import
org.apache.shardingsphere.sql.parser.statement.core.value.literal.impl.Nu
import
org.apache.shardingsphere.sql.parser.statement.core.value.literal.impl.OtherLiteralValue;
import
org.apache.shardingsphere.sql.parser.statement.core.value.literal.impl.StringLiteralValue;
import
org.apache.shardingsphere.sql.parser.statement.core.value.parametermarker.ParameterMarkerValue;
+import org.jspecify.annotations.Nullable;
import java.util.Collection;
import java.util.Collections;
@@ -604,20 +606,29 @@ public abstract class FirebirdStatementVisitor extends
FirebirdStatementBaseVisi
@Override
public final ASTNode visitOrderByItem(final OrderByItemContext ctx) {
OrderDirection orderDirection = null == ctx.DESC() ?
OrderDirection.ASC : OrderDirection.DESC;
+ NullsOrderType nullsOrderType = generateNullsOrderType(ctx);
if (null != ctx.numberLiterals()) {
return new
IndexOrderByItemSegment(ctx.numberLiterals().getStart().getStartIndex(),
ctx.numberLiterals().getStop().getStopIndex(),
- SQLUtils.getExactlyNumber(ctx.numberLiterals().getText(),
10).intValue(), orderDirection, null);
+ SQLUtils.getExactlyNumber(ctx.numberLiterals().getText(),
10).intValue(), orderDirection, nullsOrderType);
} else {
ASTNode expr = visitExpr(ctx.expr());
if (expr instanceof ColumnSegment) {
- return new ColumnOrderByItemSegment((ColumnSegment) expr,
orderDirection, null);
+ return new ColumnOrderByItemSegment((ColumnSegment) expr,
orderDirection, nullsOrderType);
} else {
return new
ExpressionOrderByItemSegment(ctx.expr().getStart().getStartIndex(),
- ctx.expr().getStop().getStopIndex(),
getOriginalText(ctx.expr()), orderDirection, null, (ExpressionSegment) expr);
+ ctx.expr().getStop().getStopIndex(),
getOriginalText(ctx.expr()), orderDirection, nullsOrderType,
(ExpressionSegment) expr);
}
}
}
+ @Nullable
+ private NullsOrderType generateNullsOrderType(final OrderByItemContext
ctx) {
+ if (null == ctx.FIRST() && null == ctx.LAST()) {
+ return null;
+ }
+ return null == ctx.FIRST() ? NullsOrderType.LAST :
NullsOrderType.FIRST;
+ }
+
@Override
public final ASTNode visitDataType(final DataTypeContext ctx) {
if (null != ctx.blobDataType()) {
diff --git a/test/it/parser/src/main/resources/case/dml/select-order-by.xml
b/test/it/parser/src/main/resources/case/dml/select-order-by.xml
index c59feb7dcaf..9a0dde4b60f 100644
--- a/test/it/parser/src/main/resources/case/dml/select-order-by.xml
+++ b/test/it/parser/src/main/resources/case/dml/select-order-by.xml
@@ -218,6 +218,54 @@
</order-by>
</select>
+ <select sql-case-id="select_order_by_asc_nulls_last_firebird">
+ <from>
+ <simple-table name="t_order" alias="o" start-index="23"
stop-index="31" />
+ </from>
+ <projections start-index="7" stop-index="16">
+ <column-projection name="order_id" start-index="7" stop-index="16">
+ <owner name="o" start-index="7" stop-index="7" />
+ </column-projection>
+ </projections>
+ <order-by>
+ <column-item name="order_id" start-index="42" stop-index="51"
order-direction="ASC" nulls-order-type="LAST">
+ <owner name="o" start-index="42" stop-index="42" />
+ </column-item>
+ </order-by>
+ </select>
+
+ <select sql-case-id="select_order_by_desc_nulls_first_firebird">
+ <from>
+ <simple-table name="t_order" alias="o" start-index="23"
stop-index="31" />
+ </from>
+ <projections start-index="7" stop-index="16">
+ <column-projection name="order_id" start-index="7" stop-index="16">
+ <owner name="o" start-index="7" stop-index="7" />
+ </column-projection>
+ </projections>
+ <order-by>
+ <column-item name="order_id" start-index="42" stop-index="51"
order-direction="DESC" nulls-order-type="FIRST">
+ <owner name="o" start-index="42" stop-index="42" />
+ </column-item>
+ </order-by>
+ </select>
+
+ <select sql-case-id="select_order_by_nulls_column_firebird">
+ <from>
+ <simple-table name="t_order" alias="o" start-index="20"
stop-index="28" />
+ </from>
+ <projections start-index="7" stop-index="13">
+ <column-projection name="nulls" start-index="7" stop-index="13">
+ <owner name="o" start-index="7" stop-index="7" />
+ </column-projection>
+ </projections>
+ <order-by>
+ <column-item name="nulls" start-index="39" stop-index="45"
order-direction="ASC" nulls-order-type="FIRST">
+ <owner name="o" start-index="39" stop-index="39" />
+ </column-item>
+ </order-by>
+ </select>
+
<select sql-case-id="select_order_by_with_multiple_stars">
<from>
<simple-table name="t_order" alias="o" start-index="29"
stop-index="37" />
diff --git
a/test/it/parser/src/main/resources/sql/supported/dml/select-order-by.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select-order-by.xml
index 3d64efc9dfc..f38cce0a85b 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select-order-by.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select-order-by.xml
@@ -24,6 +24,9 @@
<sql-case id="select_order_by_with_date" value="SELECT i.* FROM t_order o,
t_order_item i WHERE o.order_id = i.order_id AND o.status = 'init' ORDER BY
i.creation_date DESC, o.order_id DESC, i.item_id" />
<sql-case id="select_order_by_for_nulls_first" value="SELECT o.order_id AS
gen_order_id_ FROM t_order o ORDER BY o.order_id NULLS FIRST"
db-types="PostgreSQL,openGauss,Oracle" />
<sql-case id="select_order_by_for_nulls_last" value="SELECT o.order_id AS
gen_order_id_ FROM t_order o ORDER BY o.order_id ASC NULLS LAST"
db-types="PostgreSQL,openGauss,Oracle" />
+ <sql-case id="select_order_by_asc_nulls_last_firebird" value="SELECT
o.order_id FROM t_order o ORDER BY o.order_id ASC NULLS LAST"
db-types="Firebird" />
+ <sql-case id="select_order_by_desc_nulls_first_firebird" value="SELECT
o.order_id FROM t_order o ORDER BY o.order_id DESC NULLS FIRST"
db-types="Firebird" />
+ <sql-case id="select_order_by_nulls_column_firebird" value="SELECT o.nulls
FROM t_order o ORDER BY o.nulls NULLS FIRST" db-types="Firebird" />
<sql-case id="select_order_by_with_multiple_stars" value="SELECT *,
order_id, o.* FROM t_order o ORDER BY o.order_id" db-types="MySQL, SQLServer,
PostgreSQL,openGauss" />
<sql-case id="select_order_by_desc" value="SELECT * FROM employees WHERE
job_id = 'PU_CLERK' ORDER BY salary DESC" db-types="Oracle" />
<sql-case id="select_order_by_asc_desc" value="SELECT salary, last_name
FROM employees ORDER BY salary ASC, last_name DESC" db-types="Oracle" />
