This is an automated email from the ASF dual-hosted git repository.
mbudiu 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 a56d069f76 [CALCITE-6221] JDBC adapter generates invalid query when
the same table is joined multiple times
a56d069f76 is described below
commit a56d069f76b26ef9619968846313d00a34ab8d1d
Author: Ulrich Kramer <[email protected]>
AuthorDate: Thu Feb 1 14:52:51 2024 +0100
[CALCITE-6221] JDBC adapter generates invalid query when the same table is
joined multiple times
---
.../calcite/rel/rel2sql/RelToSqlConverter.java | 34 +++++-
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 118 ++++++++++++++++++++-
.../org/apache/calcite/test/JdbcAdapterTest.java | 46 ++++++++
3 files changed, 195 insertions(+), 3 deletions(-)
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 f80c358bac..353e2fa3a4 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
@@ -110,6 +110,7 @@ import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.Deque;
+import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
@@ -121,6 +122,7 @@ import java.util.stream.Stream;
import static com.google.common.base.Preconditions.checkArgument;
import static org.apache.calcite.rex.RexLiteral.stringValue;
+import static org.apache.calcite.util.Util.last;
import static java.util.Objects.requireNonNull;
@@ -441,11 +443,41 @@ public class RelToSqlConverter extends SqlImplementor
final Result x = visitInput(e, 0, Clause.WHERE);
parseCorrelTable(e, x);
final Builder builder = x.builder(e);
+ if (input instanceof Join) {
+ final Context context = x.qualifiedContext();
+ if (selectListRequired(context)) {
+ final ImmutableList.Builder<SqlNode> selectList =
ImmutableList.builder();
+ // Fieldnames are unique since they are created by
SqlValidatorUtil.deriveJoinRowType()
+ final List<String> uniqueFieldNames =
input.getRowType().getFieldNames();
+ for (int i = 0; i < context.fieldCount; i++) {
+ final SqlNode field = context.field(i);
+ final String fieldName = uniqueFieldNames.get(i);
+ selectList.add(
+ SqlStdOperatorTable.AS.createCall(POS, field,
+ new SqlIdentifier(fieldName, POS)));
+ }
+ builder.setSelect(new SqlNodeList(selectList.build(), POS));
+ }
+ }
builder.setWhere(builder.context.toSql(null, e.getCondition()));
return builder.result();
}
}
+ private static boolean selectListRequired(Context context) {
+ Set<String> uniqueFieldNames = new HashSet<>();
+ for (SqlNode node : context.fieldList()) {
+ if (node instanceof SqlIdentifier) {
+ SqlIdentifier field = (SqlIdentifier) node;
+ if (uniqueFieldNames.contains(last(field.names))) {
+ return true;
+ }
+ uniqueFieldNames.add(last(field.names));
+ }
+ }
+ return false;
+ }
+
/** Visits a Project; called by {@link #dispatch} via reflection. */
public Result visit(Project e) {
// If the input is a Sort, wrap SELECT is not required.
@@ -1114,7 +1146,7 @@ public class RelToSqlConverter extends SqlImplementor
final int nUpdateFiled = updateColumnList.size();
if (nUpdateFiled != 0) {
final SqlNodeList expressionList =
- Util.last(selectList, nUpdateFiled).stream()
+ last(selectList, nUpdateFiled).stream()
.collect(SqlNodeList.toList());
update =
new SqlUpdate(POS, sqlTargetTable,
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 a9701591a9..2349380ed2 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
@@ -3871,7 +3871,64 @@ class RelToSqlConverterTest {
+ " on p.\"product_class_id\" = pc.\"product_class_id\"\n"
+ "where c.\"city\" = 'San Francisco'\n"
+ "and pc.\"product_department\" = 'Snacks'\n";
- final String expected = "SELECT *\n"
+ final String expected = "SELECT "
+ + "\"sales_fact_1997\".\"product_id\" AS \"product_id\", "
+ + "\"sales_fact_1997\".\"time_id\" AS \"time_id\", "
+ + "\"sales_fact_1997\".\"customer_id\" AS \"customer_id\", "
+ + "\"sales_fact_1997\".\"promotion_id\" AS \"promotion_id\", "
+ + "\"sales_fact_1997\".\"store_id\" AS \"store_id\", "
+ + "\"sales_fact_1997\".\"store_sales\" AS \"store_sales\", "
+ + "\"sales_fact_1997\".\"store_cost\" AS \"store_cost\", "
+ + "\"sales_fact_1997\".\"unit_sales\" AS \"unit_sales\", "
+ + "\"customer\".\"customer_id\" AS \"customer_id0\", "
+ + "\"customer\".\"account_num\" AS \"account_num\", "
+ + "\"customer\".\"lname\" AS \"lname\", "
+ + "\"customer\".\"fname\" AS \"fname\", "
+ + "\"customer\".\"mi\" AS \"mi\", "
+ + "\"customer\".\"address1\" AS \"address1\", "
+ + "\"customer\".\"address2\" AS \"address2\", "
+ + "\"customer\".\"address3\" AS \"address3\", "
+ + "\"customer\".\"address4\" AS \"address4\", "
+ + "\"customer\".\"city\" AS \"city\", "
+ + "\"customer\".\"state_province\" AS \"state_province\", "
+ + "\"customer\".\"postal_code\" AS \"postal_code\", "
+ + "\"customer\".\"country\" AS \"country\", "
+ + "\"customer\".\"customer_region_id\" AS \"customer_region_id\", "
+ + "\"customer\".\"phone1\" AS \"phone1\", "
+ + "\"customer\".\"phone2\" AS \"phone2\", "
+ + "\"customer\".\"birthdate\" AS \"birthdate\", "
+ + "\"customer\".\"marital_status\" AS \"marital_status\", "
+ + "\"customer\".\"yearly_income\" AS \"yearly_income\", "
+ + "\"customer\".\"gender\" AS \"gender\", "
+ + "\"customer\".\"total_children\" AS \"total_children\", "
+ + "\"customer\".\"num_children_at_home\" AS \"num_children_at_home\", "
+ + "\"customer\".\"education\" AS \"education\", "
+ + "\"customer\".\"date_accnt_opened\" AS \"date_accnt_opened\", "
+ + "\"customer\".\"member_card\" AS \"member_card\", "
+ + "\"customer\".\"occupation\" AS \"occupation\", "
+ + "\"customer\".\"houseowner\" AS \"houseowner\", "
+ + "\"customer\".\"num_cars_owned\" AS \"num_cars_owned\", "
+ + "\"customer\".\"fullname\" AS \"fullname\", "
+ + "\"product\".\"product_class_id\" AS \"product_class_id\", "
+ + "\"product\".\"product_id\" AS \"product_id0\", "
+ + "\"product\".\"brand_name\" AS \"brand_name\", "
+ + "\"product\".\"product_name\" AS \"product_name\", "
+ + "\"product\".\"SKU\" AS \"SKU\", "
+ + "\"product\".\"SRP\" AS \"SRP\", "
+ + "\"product\".\"gross_weight\" AS \"gross_weight\", "
+ + "\"product\".\"net_weight\" AS \"net_weight\", "
+ + "\"product\".\"recyclable_package\" AS \"recyclable_package\", "
+ + "\"product\".\"low_fat\" AS \"low_fat\", "
+ + "\"product\".\"units_per_case\" AS \"units_per_case\", "
+ + "\"product\".\"cases_per_pallet\" AS \"cases_per_pallet\", "
+ + "\"product\".\"shelf_width\" AS \"shelf_width\", "
+ + "\"product\".\"shelf_height\" AS \"shelf_height\", "
+ + "\"product\".\"shelf_depth\" AS \"shelf_depth\", "
+ + "\"product_class\".\"product_class_id\" AS \"product_class_id0\", "
+ + "\"product_class\".\"product_subcategory\" AS
\"product_subcategory\", "
+ + "\"product_class\".\"product_category\" AS \"product_category\", "
+ + "\"product_class\".\"product_department\" AS \"product_department\",
"
+ + "\"product_class\".\"product_family\" AS \"product_family\"\n"
+ "FROM \"foodmart\".\"sales_fact_1997\"\n"
+ "INNER JOIN \"foodmart\".\"customer\" "
+ "ON \"sales_fact_1997\".\"customer_id\" = \"customer\""
@@ -5800,7 +5857,64 @@ class RelToSqlConverterTest {
+ " up as up.\"net_weight\" > prev(up.\"net_weight\")\n"
+ " ) mr order by MR.\"net_weight\"";
final String expected = "SELECT *\n"
- + "FROM (SELECT *\n"
+ + "FROM (SELECT "
+ + "\"sales_fact_1997\".\"product_id\" AS \"product_id\", "
+ + "\"sales_fact_1997\".\"time_id\" AS \"time_id\", "
+ + "\"sales_fact_1997\".\"customer_id\" AS \"customer_id\", "
+ + "\"sales_fact_1997\".\"promotion_id\" AS \"promotion_id\", "
+ + "\"sales_fact_1997\".\"store_id\" AS \"store_id\", "
+ + "\"sales_fact_1997\".\"store_sales\" AS \"store_sales\", "
+ + "\"sales_fact_1997\".\"store_cost\" AS \"store_cost\", "
+ + "\"sales_fact_1997\".\"unit_sales\" AS \"unit_sales\", "
+ + "\"customer\".\"customer_id\" AS \"customer_id0\", "
+ + "\"customer\".\"account_num\" AS \"account_num\", "
+ + "\"customer\".\"lname\" AS \"lname\", "
+ + "\"customer\".\"fname\" AS \"fname\", "
+ + "\"customer\".\"mi\" AS \"mi\", "
+ + "\"customer\".\"address1\" AS \"address1\", "
+ + "\"customer\".\"address2\" AS \"address2\", "
+ + "\"customer\".\"address3\" AS \"address3\", "
+ + "\"customer\".\"address4\" AS \"address4\", "
+ + "\"customer\".\"city\" AS \"city\", "
+ + "\"customer\".\"state_province\" AS \"state_province\", "
+ + "\"customer\".\"postal_code\" AS \"postal_code\", "
+ + "\"customer\".\"country\" AS \"country\", "
+ + "\"customer\".\"customer_region_id\" AS \"customer_region_id\", "
+ + "\"customer\".\"phone1\" AS \"phone1\", "
+ + "\"customer\".\"phone2\" AS \"phone2\", "
+ + "\"customer\".\"birthdate\" AS \"birthdate\", "
+ + "\"customer\".\"marital_status\" AS \"marital_status\", "
+ + "\"customer\".\"yearly_income\" AS \"yearly_income\", "
+ + "\"customer\".\"gender\" AS \"gender\", "
+ + "\"customer\".\"total_children\" AS \"total_children\", "
+ + "\"customer\".\"num_children_at_home\" AS \"num_children_at_home\", "
+ + "\"customer\".\"education\" AS \"education\", "
+ + "\"customer\".\"date_accnt_opened\" AS \"date_accnt_opened\", "
+ + "\"customer\".\"member_card\" AS \"member_card\", "
+ + "\"customer\".\"occupation\" AS \"occupation\", "
+ + "\"customer\".\"houseowner\" AS \"houseowner\", "
+ + "\"customer\".\"num_cars_owned\" AS \"num_cars_owned\", "
+ + "\"customer\".\"fullname\" AS \"fullname\", "
+ + "\"product\".\"product_class_id\" AS \"product_class_id\", "
+ + "\"product\".\"product_id\" AS \"product_id0\", "
+ + "\"product\".\"brand_name\" AS \"brand_name\", "
+ + "\"product\".\"product_name\" AS \"product_name\", "
+ + "\"product\".\"SKU\" AS \"SKU\", "
+ + "\"product\".\"SRP\" AS \"SRP\", "
+ + "\"product\".\"gross_weight\" AS \"gross_weight\", "
+ + "\"product\".\"net_weight\" AS \"net_weight\", "
+ + "\"product\".\"recyclable_package\" AS \"recyclable_package\", "
+ + "\"product\".\"low_fat\" AS \"low_fat\", "
+ + "\"product\".\"units_per_case\" AS \"units_per_case\", "
+ + "\"product\".\"cases_per_pallet\" AS \"cases_per_pallet\", "
+ + "\"product\".\"shelf_width\" AS \"shelf_width\", "
+ + "\"product\".\"shelf_height\" AS \"shelf_height\", "
+ + "\"product\".\"shelf_depth\" AS \"shelf_depth\", "
+ + "\"product_class\".\"product_class_id\" AS \"product_class_id0\", "
+ + "\"product_class\".\"product_subcategory\" AS
\"product_subcategory\", "
+ + "\"product_class\".\"product_category\" AS \"product_category\", "
+ + "\"product_class\".\"product_department\" AS \"product_department\",
"
+ + "\"product_class\".\"product_family\" AS \"product_family\"\n"
+ "FROM \"foodmart\".\"sales_fact_1997\"\n"
+ "INNER JOIN \"foodmart\".\"customer\" "
+ "ON \"sales_fact_1997\".\"customer_id\" =
\"customer\".\"customer_id\"\n"
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
index 171dea060c..7a35870d12 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
@@ -1378,6 +1378,52 @@ class JdbcAdapterTest {
});
}
+ /**
+ * Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6221";>[CALCITE-6221]</a>.*/
+ @Test void testUnknownColumn() {
+ CalciteAssert.model(JdbcTest.SCOTT_MODEL)
+ .query("SELECT\n"
+ + " \"content-format-owner\",\n"
+ + " \"content-owner\"\n"
+ + "FROM\n"
+ + " (\n"
+ + " SELECT\n"
+ + " d1.dname AS \"content-format-owner\",\n"
+ + " d2.dname || ' ' AS \"content-owner\"\n"
+ + " FROM\n"
+ + " scott.emp e1\n"
+ + " left outer join scott.dept d1 on e1.deptno =
d1.deptno\n"
+ + " left outer join scott.dept d2 on e1.deptno =
d2.deptno\n"
+ + " left outer join scott.emp e2 on e1.deptno =
e2.deptno\n"
+ + " GROUP BY\n"
+ + " d1.dname,\n"
+ + " d2.dname\n"
+ + " )\n"
+ + "WHERE\n"
+ + " \"content-owner\" IN (?)")
+ .planHasSql("SELECT "
+ + "\"t2\".\"DNAME\" AS \"content-format-owner\", "
+ + "\"t2\".\"DNAME0\" || ' ' AS \"content-owner\"\n"
+ + "FROM (SELECT \"t\".\"DEPTNO\" AS \"DEPTNO\", "
+ + "\"t0\".\"DEPTNO\" AS \"DEPTNO0\", "
+ + "\"t0\".\"DNAME\" AS \"DNAME\", "
+ + "\"t1\".\"DEPTNO\" AS \"DEPTNO1\", "
+ + "\"t1\".\"DNAME\" AS \"DNAME0\"\n"
+ + "FROM (SELECT \"DEPTNO\"\n"
+ + "FROM \"SCOTT\".\"EMP\") AS \"t\"\n"
+ + "LEFT JOIN (SELECT \"DEPTNO\", \"DNAME\"\n"
+ + "FROM \"SCOTT\".\"DEPT\") AS \"t0\" ON \"t\".\"DEPTNO\" =
\"t0\".\"DEPTNO\"\n"
+ + "LEFT JOIN (SELECT \"DEPTNO\", \"DNAME\"\n"
+ + "FROM \"SCOTT\".\"DEPT\") AS \"t1\" "
+ + "ON \"t\".\"DEPTNO\" = \"t1\".\"DEPTNO\"\n"
+ + "WHERE \"t1\".\"DNAME\" || ' ' = ?) AS \"t2\"\n"
+ + "LEFT JOIN (SELECT \"DEPTNO\"\n"
+ + "FROM \"SCOTT\".\"EMP\") AS \"t3\" "
+ + "ON \"t2\".\"DEPTNO\" = \"t3\".\"DEPTNO\"\n"
+ + "GROUP BY \"t2\".\"DNAME\", \"t2\".\"DNAME0\"")
+ .runs();
+ }
/** Acquires a lock, and releases it when closed. */
static class LockWrapper implements AutoCloseable {
private final Lock lock;
