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 44a9d5caa5 [CALCITE-5583] JDBC adapter does not generate 'SELECT *'
when duplicate field names
44a9d5caa5 is described below
commit 44a9d5caa5c1b514e9350b62334aac544201ebf1
Author: Zhen Chen <[email protected]>
AuthorDate: Mon Jun 23 11:07:05 2025 +0800
[CALCITE-5583] JDBC adapter does not generate 'SELECT *' when duplicate
field names
---
.../calcite/rel/rel2sql/RelToSqlConverter.java | 3 +-
.../java/org/apache/calcite/sql/SqlDialect.java | 6 +++
.../calcite/sql/dialect/PostgresqlSqlDialect.java | 19 +++++++
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 58 ++++++++++++++++++++++
4 files changed, 85 insertions(+), 1 deletion(-)
diff --git
a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
index 807073618a..4eab979beb 100644
--- a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
+++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
@@ -531,7 +531,8 @@ public Result visit(Project e) {
}
parseCorrelTable(e, x);
final Builder builder = x.builder(e);
- if (!isStar(e.getProjects(), e.getInput().getRowType(), e.getRowType())) {
+ if (!isStar(e.getProjects(), e.getInput().getRowType(), e.getRowType())
+ || !dialect.supportGenerateSelectStar(e.getInput())) {
final List<SqlNode> selectList = new ArrayList<>();
for (RexNode ref : e.getProjects()) {
SqlNode sqlExpr = builder.context.toSql(null, ref);
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 b49d5ff116..d97f71932b 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
@@ -23,6 +23,7 @@
import org.apache.calcite.config.NullCollation;
import org.apache.calcite.linq4j.function.Experimental;
import org.apache.calcite.rel.RelFieldCollation;
+import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.core.JoinRelType;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeSystem;
@@ -797,6 +798,11 @@ public boolean supportBooleanCaseWhen() {
return true;
}
+ /** Returns whether this dialect supports generate 'SELECT *'. */
+ public boolean supportGenerateSelectStar(RelNode relNode) {
+ return true;
+ }
+
/** Converts {@link RexNode} expression to {@link RexNode} expression before
unparse. */
public RexNode prepareUnparse(RexNode rexNode) {
return rexNode;
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 638c7322dd..6e556b502b 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
@@ -18,6 +18,9 @@
import org.apache.calcite.avatica.util.Casing;
import org.apache.calcite.avatica.util.TimeUnitRange;
+import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.rel.core.Join;
+import org.apache.calcite.rel.rules.MultiJoin;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeSystem;
import org.apache.calcite.rel.type.RelDataTypeSystemImpl;
@@ -45,13 +48,16 @@
import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.sql.type.SqlTypeUtil;
+import org.apache.calcite.util.Util;
import com.google.common.collect.ImmutableList;
import org.checkerframework.checker.nullness.qual.Nullable;
import java.util.List;
+import java.util.Locale;
import java.util.Objects;
+import java.util.stream.Collectors;
import static org.apache.calcite.linq4j.Nullness.castNonNull;
@@ -179,6 +185,19 @@ public PostgresqlSqlDialect(Context context) {
return false;
}
+ @Override public boolean supportGenerateSelectStar(RelNode relNode) {
+ // Whether the relNode is a join and whether its inputs have duplicate
field names.
+ // For example, EMP JOIN DEPT, both of which have columns named DEPTNO.
+ if (relNode instanceof Join || relNode instanceof MultiJoin) {
+ final List<String> fieldNames = relNode.getInputs().stream()
+ .flatMap(input -> input.getRowType().getFieldNames().stream())
+ .map(name -> isCaseSensitive() ? name :
name.toLowerCase(Locale.ROOT))
+ .collect(Collectors.toList());
+ return Util.isDistinct(fieldNames);
+ }
+ return true;
+ }
+
@Override public void unparseCall(SqlWriter writer, SqlCall call,
int leftPrec, int rightPrec) {
switch (call.getKind()) {
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 968f3ae80e..f71da9a47c 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
@@ -8843,6 +8843,64 @@ private void checkLiteral2(String expression, String
expected) {
.withOracle(11).ok(expectedVersionLow1);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5583";>[CALCITE-5583]
+ * JDBC adapter does not generate 'SELECT *' when duplicate field names</a>.
*/
+ @Test void testSelectStarWithJoinOn() {
+ final String sql = "SELECT * FROM \"DEPT\" t1 "
+ + "LEFT JOIN (SELECT * FROM (SELECT DEPTNO AS c0 FROM \"DEPT\") t "
+ + "INNER JOIN (SELECT DEPTNO AS c0 FROM \"DEPT\") t0 ON t.c0 = t0.c0)
t2 "
+ + "ON t1.DEPTNO = t2.c0";
+ final String expectedPostgres = "SELECT *\n"
+ + "FROM \"SCOTT\".\"DEPT\"\n"
+ + "LEFT JOIN (SELECT \"t\".\"C0\", \"t0\".\"C0\" AS \"C00\"\n"
+ + "FROM (SELECT \"DEPTNO\" AS \"C0\"\n"
+ + "FROM \"SCOTT\".\"DEPT\") AS \"t\"\n"
+ + "INNER JOIN (SELECT \"DEPTNO\" AS \"C0\"\n"
+ + "FROM \"SCOTT\".\"DEPT\") AS \"t0\""
+ + " ON \"t\".\"C0\" = \"t0\".\"C0\") AS \"t1\""
+ + " ON \"DEPT\".\"DEPTNO\" = \"t1\".\"C0\"";
+ sql(sql)
+ .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
+ .withPostgresql().ok(expectedPostgres);
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5583";>[CALCITE-5583]
+ * JDBC adapter does not generate 'SELECT *' when duplicate field names</a>.
*/
+ @Test void testSelectStarWithJoinUsing() {
+ final String sql = "select * from EMP JOIN DEPT USING (DEPTNO)";
+ final String expectedPostgres = ""
+ + "SELECT COALESCE(\"EMP\".\"DEPTNO\", \"DEPT\".\"DEPTNO\") AS
\"DEPTNO\", "
+ + "\"EMP\".\"EMPNO\", \"EMP\".\"ENAME\", \"EMP\".\"JOB\",
\"EMP\".\"MGR\", "
+ + "\"EMP\".\"HIREDATE\", \"EMP\".\"SAL\", \"EMP\".\"COMM\",
\"DEPT\".\"DNAME\", "
+ + "\"DEPT\".\"LOC\"\n"
+ + "FROM \"SCOTT\".\"EMP\"\n"
+ + "INNER JOIN \"SCOTT\".\"DEPT\" "
+ + "ON \"EMP\".\"DEPTNO\" = \"DEPT\".\"DEPTNO\"";
+ sql(sql)
+ .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
+ .withPostgresql().ok(expectedPostgres);
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5583";>[CALCITE-5583]
+ * JDBC adapter does not generate 'SELECT *' when duplicate field names</a>.
*/
+ @Test void testSelectStarWithJoinNatural() {
+ final String sql = "select * from EMP NATURAL JOIN DEPT";
+ final String expectedPostgres = ""
+ + "SELECT COALESCE(\"EMP\".\"DEPTNO\", \"DEPT\".\"DEPTNO\") AS
\"DEPTNO\", "
+ + "\"EMP\".\"EMPNO\", \"EMP\".\"ENAME\", \"EMP\".\"JOB\",
\"EMP\".\"MGR\", "
+ + "\"EMP\".\"HIREDATE\", \"EMP\".\"SAL\", \"EMP\".\"COMM\",
\"DEPT\".\"DNAME\", "
+ + "\"DEPT\".\"LOC\"\n"
+ + "FROM \"SCOTT\".\"EMP\"\n"
+ + "INNER JOIN \"SCOTT\".\"DEPT\" "
+ + "ON \"EMP\".\"DEPTNO\" = \"DEPT\".\"DEPTNO\"";
+ sql(sql)
+ .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
+ .withPostgresql().ok(expectedPostgres);
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-5265";>[CALCITE-5265]
* JDBC adapter sometimes adds unnecessary parentheses around SELECT in
INSERT</a>. */
