This is an automated email from the ASF dual-hosted git repository.
zhenchen 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 03ce8f8683 [CALCITE-7157] PostgreSQL does not support string literal
in ORDER BY clause
03ce8f8683 is described below
commit 03ce8f8683e8e4eb145f2961dd4072af2a805621
Author: Zhen Chen <[email protected]>
AuthorDate: Thu Sep 4 14:27:13 2025 +0800
[CALCITE-7157] PostgreSQL does not support string literal in ORDER BY clause
---
.../apache/calcite/rel/rel2sql/SqlImplementor.java | 33 +++++++++++++---------
.../java/org/apache/calcite/sql/SqlDialect.java | 4 +++
.../calcite/sql/dialect/PostgresqlSqlDialect.java | 4 +++
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 16 +++++++++++
4 files changed, 43 insertions(+), 14 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
index 80eb1b2887..bbb1e0e439 100644
--- a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
+++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
@@ -20,9 +20,10 @@
import org.apache.calcite.config.CalciteSystemProperty;
import org.apache.calcite.linq4j.Ord;
import org.apache.calcite.linq4j.tree.Expressions;
+import org.apache.calcite.plan.RelOptRule;
import org.apache.calcite.plan.RelOptUtil;
import org.apache.calcite.plan.hep.HepPlanner;
-import org.apache.calcite.plan.hep.HepProgramBuilder;
+import org.apache.calcite.plan.hep.HepProgram;
import org.apache.calcite.rel.RelCollation;
import org.apache.calcite.rel.RelFieldCollation;
import org.apache.calcite.rel.RelNode;
@@ -34,6 +35,7 @@
import org.apache.calcite.rel.core.Project;
import org.apache.calcite.rel.core.Window;
import org.apache.calcite.rel.rules.AggregateProjectConstantToDummyJoinRule;
+import org.apache.calcite.rel.rules.CoreRules;
import org.apache.calcite.rel.rules.FullToLeftAndRightJoinRule;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
@@ -183,20 +185,23 @@ protected SqlImplementor(SqlDialect dialect) {
/** Visits a relational expression that has no parent. */
public final Result visitRoot(RelNode r) {
- RelNode best;
- if (!this.dialect.supportsGroupByLiteral()
- || !this.dialect.supportsJoinType(JoinRelType.FULL)) {
- HepProgramBuilder hepProgramBuilder = new HepProgramBuilder();
- if (!this.dialect.supportsGroupByLiteral()) {
- hepProgramBuilder.addRuleInstance(
- AggregateProjectConstantToDummyJoinRule.Config.DEFAULT.toRule());
- }
- if (!this.dialect.supportsJoinType(JoinRelType.FULL)) {
- hepProgramBuilder.addRuleInstance(
- FullToLeftAndRightJoinRule.Config.DEFAULT.toRule());
- }
- HepPlanner hepPlanner = new HepPlanner(hepProgramBuilder.build());
+ List<RelOptRule> rules = new ArrayList<>();
+ if (!this.dialect.supportsGroupByLiteral()) {
+
rules.add(AggregateProjectConstantToDummyJoinRule.Config.DEFAULT.toRule());
+ }
+ if (!this.dialect.supportsJoinType(JoinRelType.FULL)) {
+ rules.add(FullToLeftAndRightJoinRule.Config.DEFAULT.toRule());
+ }
+
+ if (!this.dialect.supportsOrderByLiteral()) {
+ rules.add(CoreRules.SORT_REMOVE_CONSTANT_KEYS);
+ }
+
+ RelNode best;
+ if (!rules.isEmpty()) {
+ HepPlanner hepPlanner =
+ new
HepPlanner(HepProgram.builder().addRuleCollection(rules).build());
hepPlanner.setRoot(r);
best = hepPlanner.findBestExp();
} else {
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
index 953e019d15..a148e6ee6c 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
@@ -755,6 +755,10 @@ public boolean supportsGroupByLiteral() {
return true;
}
+ public boolean supportsOrderByLiteral() {
+ return true;
+ }
+
public boolean supportsAggregateFunction(SqlKind kind) {
switch (kind) {
case LITERAL_AGG:
diff --git
a/core/src/main/java/org/apache/calcite/sql/dialect/PostgresqlSqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/dialect/PostgresqlSqlDialect.java
index 6e556b502b..19342fa405 100644
---
a/core/src/main/java/org/apache/calcite/sql/dialect/PostgresqlSqlDialect.java
+++
b/core/src/main/java/org/apache/calcite/sql/dialect/PostgresqlSqlDialect.java
@@ -233,6 +233,10 @@ public PostgresqlSqlDialect(Context context) {
return false;
}
+ @Override public boolean supportsOrderByLiteral() {
+ return false;
+ }
+
@Override public void unparseSqlSetOption(SqlWriter writer,
int leftPrec, int rightPrec, SqlSetOption option) {
String scope = option.getScope();
diff --git
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index bd929ee090..23fcd55036 100644
---
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -10685,6 +10685,22 @@ private void checkLiteral2(String expression, String
expected) {
.ok(expected);
}
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7157";>[CALCITE-7157]
+ * PostgreSQL does not support string literal in ORDER BY clause</a>. */
+ @Test void testSqlDialectOrdreByLiteralSimple() {
+ final String query = "SELECT deptno, job\n"
+ + "FROM emp\n"
+ + "ORDER BY empno, 'abc'";
+ final String expected = "SELECT \"DEPTNO\", \"JOB\"\n"
+ + "FROM \"SCOTT\".\"EMP\"\n"
+ + "ORDER BY \"EMPNO\"";
+ sql(query)
+ .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
+ .withPostgresql()
+ .ok(expected);
+ }
+
/** Fluid interface to run tests. */
static class Sql {
private final CalciteAssert.SchemaSpec schemaSpec;
