MartijnVisser commented on code in PR #29932:
URL: https://github.com/apache/flink/pull/29932#discussion_r4597652190
##########
flink-table/flink-table-planner/src/test/scala/org/apache/flink/table/planner/plan/common/PartialInsertTest.scala:
##########
@@ -172,6 +173,64 @@ class PartialInsertTest(isBatch: Boolean) extends
TableTestBase {
}
}
+ @TestTemplate
+ def testPartialInsertWithOrderByOrdinal(): Unit = {
+ // the ordinal refers to the select list of the query, not to the sink's
column order
+ val insert =
+ "INSERT INTO partitioned_sink (e,a,g,f,c,d) " +
+ "SELECT e,a,456,123,c,d FROM MyTable ORDER BY 1"
+ if (isBatch) {
+ util.verifyRelPlanInsert(insert)
+ } else {
+ assertThatThrownBy(() => util.verifyRelPlanInsert(insert))
+ .hasMessageContaining(
+ "requires the primary sort key to be a time attribute in ascending
order")
+ }
+ }
+
+ @TestTemplate
+ def testPartialInsertWithInvalidStaticPartitionValue(): Unit = {
+ util.tableEnv.executeSql(
+ "CREATE TABLE date_partitioned_sink (a INT, dt DATE) PARTITIONED BY (dt)
" +
+ "WITH ('connector' = 'filesystem', 'path' = '/non', 'format' =
'testcsv')")
+ assertThatThrownBy(
+ () =>
+ util.verifyRelPlanInsert(
+ "INSERT INTO date_partitioned_sink PARTITION (dt = TRUE) SELECT 1"))
+ .hasMessageContaining("Cast function cannot convert value of type
BOOLEAN to type DATE")
Review Comment:
Done. The `line 0, column 0` comes from `SqlValidatorImpl#maybeCast`, which
creates the CAST at `SqlParserPos.ZERO`. Master reports the same position.
##########
flink-table/flink-table-planner/src/test/scala/org/apache/flink/table/planner/plan/common/PartialInsertTest.scala:
##########
@@ -172,6 +173,64 @@ class PartialInsertTest(isBatch: Boolean) extends
TableTestBase {
}
}
+ @TestTemplate
+ def testPartialInsertWithOrderByOrdinal(): Unit = {
+ // the ordinal refers to the select list of the query, not to the sink's
column order
+ val insert =
+ "INSERT INTO partitioned_sink (e,a,g,f,c,d) " +
+ "SELECT e,a,456,123,c,d FROM MyTable ORDER BY 1"
+ if (isBatch) {
+ util.verifyRelPlanInsert(insert)
+ } else {
+ assertThatThrownBy(() => util.verifyRelPlanInsert(insert))
+ .hasMessageContaining(
+ "requires the primary sort key to be a time attribute in ascending
order")
+ }
+ }
+
+ @TestTemplate
+ def testPartialInsertWithInvalidStaticPartitionValue(): Unit = {
+ util.tableEnv.executeSql(
+ "CREATE TABLE date_partitioned_sink (a INT, dt DATE) PARTITIONED BY (dt)
" +
+ "WITH ('connector' = 'filesystem', 'path' = '/non', 'format' =
'testcsv')")
+ assertThatThrownBy(
+ () =>
+ util.verifyRelPlanInsert(
+ "INSERT INTO date_partitioned_sink PARTITION (dt = TRUE) SELECT 1"))
+ .hasMessageContaining("Cast function cannot convert value of type
BOOLEAN to type DATE")
+ .isInstanceOf(classOf[ValidationException])
+ }
+
+ @TestTemplate
+ def testPartialInsertPadsStructuredAndRawColumns(): Unit = {
+ util.tableEnv.createTemporaryTable(
+ "typed_sink",
+ TableDescriptor
+ .forConnector("values")
+ .schema(
+ Schema
+ .newBuilder()
+ .column("a", DataTypes.INT())
+ .column("s", DataTypes.STRUCTURED("MyUserPojo",
DataTypes.FIELD("i", DataTypes.INT())))
+ .column("r", DataTypes.RAW(classOf[String],
StringSerializer.INSTANCE))
+ .build())
+ .option("sink-insert-only", "false")
+ .build()
+ )
+ util.verifyRelPlanInsert("INSERT INTO typed_sink (a) SELECT a FROM
MyTable")
+ }
+
+ @TestTemplate
+ def testPartialInsertWithStaticPartitionColumnInColumnList(): Unit = {
+ assertThatThrownBy(
+ () =>
+ util.verifyRelPlanInsert(
+ "INSERT INTO partitioned_sink PARTITION(`c`='2021') (c,e) " +
+ "SELECT c,e FROM MyTable"))
+ .hasMessageContaining("Target column 'c' is assigned more than once")
Review Comment:
Done.
##########
flink-table/flink-table-planner/src/test/scala/org/apache/flink/table/planner/plan/common/PartialInsertTest.scala:
##########
@@ -172,6 +173,64 @@ class PartialInsertTest(isBatch: Boolean) extends
TableTestBase {
}
}
+ @TestTemplate
+ def testPartialInsertWithOrderByOrdinal(): Unit = {
+ // the ordinal refers to the select list of the query, not to the sink's
column order
+ val insert =
+ "INSERT INTO partitioned_sink (e,a,g,f,c,d) " +
+ "SELECT e,a,456,123,c,d FROM MyTable ORDER BY 1"
+ if (isBatch) {
+ util.verifyRelPlanInsert(insert)
+ } else {
+ assertThatThrownBy(() => util.verifyRelPlanInsert(insert))
+ .hasMessageContaining(
+ "requires the primary sort key to be a time attribute in ascending
order")
+ }
+ }
+
+ @TestTemplate
+ def testPartialInsertWithInvalidStaticPartitionValue(): Unit = {
+ util.tableEnv.executeSql(
+ "CREATE TABLE date_partitioned_sink (a INT, dt DATE) PARTITIONED BY (dt)
" +
+ "WITH ('connector' = 'filesystem', 'path' = '/non', 'format' =
'testcsv')")
+ assertThatThrownBy(
+ () =>
+ util.verifyRelPlanInsert(
+ "INSERT INTO date_partitioned_sink PARTITION (dt = TRUE) SELECT 1"))
+ .hasMessageContaining("Cast function cannot convert value of type
BOOLEAN to type DATE")
+ .isInstanceOf(classOf[ValidationException])
Review Comment:
Done, also in the other new tests.
##########
flink-table/flink-table-planner/src/test/scala/org/apache/flink/table/planner/plan/common/PartialInsertTest.scala:
##########
@@ -172,6 +173,64 @@ class PartialInsertTest(isBatch: Boolean) extends
TableTestBase {
}
}
+ @TestTemplate
+ def testPartialInsertWithOrderByOrdinal(): Unit = {
+ // the ordinal refers to the select list of the query, not to the sink's
column order
+ val insert =
+ "INSERT INTO partitioned_sink (e,a,g,f,c,d) " +
+ "SELECT e,a,456,123,c,d FROM MyTable ORDER BY 1"
+ if (isBatch) {
+ util.verifyRelPlanInsert(insert)
+ } else {
+ assertThatThrownBy(() => util.verifyRelPlanInsert(insert))
+ .hasMessageContaining(
+ "requires the primary sort key to be a time attribute in ascending
order")
+ }
+ }
+
+ @TestTemplate
+ def testPartialInsertWithInvalidStaticPartitionValue(): Unit = {
+ util.tableEnv.executeSql(
+ "CREATE TABLE date_partitioned_sink (a INT, dt DATE) PARTITIONED BY (dt)
" +
+ "WITH ('connector' = 'filesystem', 'path' = '/non', 'format' =
'testcsv')")
+ assertThatThrownBy(
+ () =>
+ util.verifyRelPlanInsert(
+ "INSERT INTO date_partitioned_sink PARTITION (dt = TRUE) SELECT 1"))
+ .hasMessageContaining("Cast function cannot convert value of type
BOOLEAN to type DATE")
+ .isInstanceOf(classOf[ValidationException])
+ }
+
+ @TestTemplate
+ def testPartialInsertPadsStructuredAndRawColumns(): Unit = {
Review Comment:
None of the existing sinks has a STRUCTURED or RAW column, and adding them
to one would change the plans of the other tests. Same for
`date_partitioned_sink`: all existing partition columns are STRING.
##########
flink-table/flink-table-planner/src/main/java/org/apache/flink/table/planner/operations/converters/PartialInsertUtil.java:
##########
@@ -0,0 +1,188 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.flink.table.planner.operations.converters;
+
+import org.apache.flink.annotation.Internal;
+import org.apache.flink.sql.parser.SqlProperty;
+import org.apache.flink.sql.parser.dml.RichSqlInsert;
+import org.apache.flink.table.api.ValidationException;
+import org.apache.flink.table.catalog.ResolvedSchema;
+import org.apache.flink.table.planner.calcite.FlinkCalciteSqlValidator;
+import org.apache.flink.table.planner.calcite.FlinkPlannerImpl;
+import org.apache.flink.table.planner.calcite.FlinkTypeFactory;
+import org.apache.flink.table.planner.operations.PlannerQueryOperation;
+import org.apache.flink.table.planner.utils.OperationConverterUtils;
+import org.apache.flink.table.types.logical.RowType;
+
+import org.apache.calcite.plan.RelOptCluster;
+import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.rel.core.RelFactories;
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rel.type.RelDataTypeFactory;
+import org.apache.calcite.rex.RexBuilder;
+import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.sql.SqlIdentifier;
+import org.apache.calcite.sql.SqlLiteral;
+import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlNodeList;
+import org.apache.calcite.sql.SqlUtil;
+import org.apache.calcite.tools.RelBuilder;
+
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+import java.util.stream.Collectors;
+
+import static org.apache.calcite.util.Static.RESOURCE;
+import static org.apache.flink.util.Preconditions.checkState;
+
+/**
+ * Projects the source query of an {@code INSERT INTO} with static partitions
or a target column
+ * list onto the sink's persisted columns, after validation. Static partition
columns become their
+ * value cast to the column type, unlisted columns become {@code NULL}. The
target columns are
+ * validated before that in {@link
org.apache.flink.table.planner.calcite.PreValidateReWriter}.
+ */
+@Internal
+public final class PartialInsertUtil {
+
+ private PartialInsertUtil() {}
+
+ /**
+ * Returns the query projected onto the sink's persisted columns, or the
query itself when the
+ * statement has no static partitions and its target column list neither
reorders nor pads it.
+ */
+ public static PlannerQueryOperation alignWithSink(
+ FlinkPlannerImpl planner,
+ RichSqlInsert insert,
+ ResolvedSchema sinkSchema,
+ PlannerQueryOperation query) {
+ final SqlNodeList targetColumnList = insert.getTargetColumnList();
+ final SqlNodeList staticPartitions = insert.getStaticPartitions();
+ if (targetColumnList == null && staticPartitions.isEmpty()) {
+ return query;
+ }
+
+ final RelNode queryRel = query.getCalciteTree();
+ final int queryColumnCount = queryRel.getRowType().getFieldCount();
+ final RelOptCluster cluster = queryRel.getCluster();
+ final RexBuilder rexBuilder = cluster.getRexBuilder();
+ final FlinkTypeFactory typeFactory = (FlinkTypeFactory)
cluster.getTypeFactory();
+ final RowType sinkRowType = (RowType)
sinkSchema.toSinkRowDataType().getLogicalType();
+
+ final Map<String, SqlLiteral> partitionValues = new HashMap<>();
+ for (SqlNode node : staticPartitions) {
+ final SqlProperty property = (SqlProperty) node;
+ partitionValues.put(property.getKey().getSimple(), (SqlLiteral)
property.getValue());
+ }
+
+ final List<String> targetColumns;
+ if (targetColumnList == null) {
+ targetColumns = null;
+ } else {
+ targetColumns =
+ targetColumnList.stream()
+ .map(column -> ((SqlIdentifier)
column).getSimple())
+ .collect(Collectors.toList());
+ // PreValidateReWriter can only check this before validation for
sources whose column
+ // count is known syntactically, so not for a SELECT *.
+ if (targetColumns.size() != queryColumnCount) {
+ throw validationError(
+ SqlUtil.newContextException(
+ insert.getSource().getParserPosition(),
+ RESOURCE.columnCountMismatch()));
+ }
+ }
+
+ // PreValidateReWriter resolved these names against the same schema.
An unresolved name
+ // here would pad the sink column with NULL and drop the query column.
+ final List<String> sinkFieldNames = sinkRowType.getFieldNames();
+ checkState(
+ sinkFieldNames.containsAll(partitionValues.keySet())
+ && (targetColumns == null ||
sinkFieldNames.containsAll(targetColumns)),
+ "Unresolved target column for sink columns %s",
+ sinkFieldNames);
+
+ final List<RexNode> projects = new ArrayList<>();
+ final List<String> fieldNames = new ArrayList<>();
+ boolean projectionNeeded = !partitionValues.isEmpty();
+ int nextQueryColumn = 0;
+ for (RowType.RowField field : sinkRowType.getFields()) {
+ final RelDataType fieldType =
+
typeFactory.createFieldTypeFromLogicalType(field.getType());
+ final SqlLiteral partitionValue =
partitionValues.get(field.getName());
+ if (partitionValue != null) {
+ projects.add(convertStaticPartitionValue(planner,
partitionValue, fieldType));
+ } else if (targetColumns != null) {
+ final int queryColumn = targetColumns.indexOf(field.getName());
Review Comment:
Done. For a 10k column sink with a reversed column list, the column list now
adds 0.35-0.57 s to parse, validate and convert, against 0.6 s before this push
and 1.6 s on master.
##########
flink-table/flink-table-planner/src/main/java/org/apache/flink/table/planner/operations/converters/PartialInsertUtil.java:
##########
@@ -0,0 +1,188 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.flink.table.planner.operations.converters;
+
+import org.apache.flink.annotation.Internal;
+import org.apache.flink.sql.parser.SqlProperty;
+import org.apache.flink.sql.parser.dml.RichSqlInsert;
+import org.apache.flink.table.api.ValidationException;
+import org.apache.flink.table.catalog.ResolvedSchema;
+import org.apache.flink.table.planner.calcite.FlinkCalciteSqlValidator;
+import org.apache.flink.table.planner.calcite.FlinkPlannerImpl;
+import org.apache.flink.table.planner.calcite.FlinkTypeFactory;
+import org.apache.flink.table.planner.operations.PlannerQueryOperation;
+import org.apache.flink.table.planner.utils.OperationConverterUtils;
+import org.apache.flink.table.types.logical.RowType;
+
+import org.apache.calcite.plan.RelOptCluster;
+import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.rel.core.RelFactories;
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rel.type.RelDataTypeFactory;
+import org.apache.calcite.rex.RexBuilder;
+import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.sql.SqlIdentifier;
+import org.apache.calcite.sql.SqlLiteral;
+import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlNodeList;
+import org.apache.calcite.sql.SqlUtil;
+import org.apache.calcite.tools.RelBuilder;
+
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+import java.util.stream.Collectors;
+
+import static org.apache.calcite.util.Static.RESOURCE;
+import static org.apache.flink.util.Preconditions.checkState;
+
+/**
+ * Projects the source query of an {@code INSERT INTO} with static partitions
or a target column
+ * list onto the sink's persisted columns, after validation. Static partition
columns become their
+ * value cast to the column type, unlisted columns become {@code NULL}. The
target columns are
+ * validated before that in {@link
org.apache.flink.table.planner.calcite.PreValidateReWriter}.
+ */
+@Internal
+public final class PartialInsertUtil {
+
+ private PartialInsertUtil() {}
+
+ /**
+ * Returns the query projected onto the sink's persisted columns, or the
query itself when the
+ * statement has no static partitions and its target column list neither
reorders nor pads it.
+ */
+ public static PlannerQueryOperation alignWithSink(
+ FlinkPlannerImpl planner,
+ RichSqlInsert insert,
+ ResolvedSchema sinkSchema,
+ PlannerQueryOperation query) {
+ final SqlNodeList targetColumnList = insert.getTargetColumnList();
+ final SqlNodeList staticPartitions = insert.getStaticPartitions();
+ if (targetColumnList == null && staticPartitions.isEmpty()) {
+ return query;
+ }
+
+ final RelNode queryRel = query.getCalciteTree();
+ final int queryColumnCount = queryRel.getRowType().getFieldCount();
+ final RelOptCluster cluster = queryRel.getCluster();
+ final RexBuilder rexBuilder = cluster.getRexBuilder();
+ final FlinkTypeFactory typeFactory = (FlinkTypeFactory)
cluster.getTypeFactory();
+ final RowType sinkRowType = (RowType)
sinkSchema.toSinkRowDataType().getLogicalType();
+
+ final Map<String, SqlLiteral> partitionValues = new HashMap<>();
+ for (SqlNode node : staticPartitions) {
+ final SqlProperty property = (SqlProperty) node;
+ partitionValues.put(property.getKey().getSimple(), (SqlLiteral)
property.getValue());
+ }
+
+ final List<String> targetColumns;
+ if (targetColumnList == null) {
+ targetColumns = null;
+ } else {
+ targetColumns =
+ targetColumnList.stream()
+ .map(column -> ((SqlIdentifier)
column).getSimple())
+ .collect(Collectors.toList());
+ // PreValidateReWriter can only check this before validation for
sources whose column
+ // count is known syntactically, so not for a SELECT *.
+ if (targetColumns.size() != queryColumnCount) {
+ throw validationError(
+ SqlUtil.newContextException(
+ insert.getSource().getParserPosition(),
+ RESOURCE.columnCountMismatch()));
+ }
+ }
+
+ // PreValidateReWriter resolved these names against the same schema.
An unresolved name
+ // here would pad the sink column with NULL and drop the query column.
+ final List<String> sinkFieldNames = sinkRowType.getFieldNames();
+ checkState(
+ sinkFieldNames.containsAll(partitionValues.keySet())
+ && (targetColumns == null ||
sinkFieldNames.containsAll(targetColumns)),
+ "Unresolved target column for sink columns %s",
+ sinkFieldNames);
Review Comment:
No good reason, it is a lookup now. That showed the names were compared
case-sensitively even on a case-insensitive planner, so `(B, A)` failed with an
`IllegalStateException`. Fixed, see
`testPartialInsertWithCaseInsensitiveColumnNames`. The test's plan still shows
`targetColumns=[[-1],[-1]]`: `getTargetColumnIndices` has the same case
problem, also on master. That is FLINK-41525.
##########
flink-table/flink-table-planner/src/main/scala/org/apache/flink/table/planner/calcite/PreValidateReWriter.scala:
##########
@@ -122,102 +92,88 @@ object PreValidateReWriter {
if (table == null) {
// There is no table exists in current catalog,
// just skip to let other validation error throw.
- return source
+ return
}
- val rewriterUtils = new SqlRewriterUtils(validator)
val targetRowType = createTargetRowType(typeFactory, table)
- // validate partition fields first.
- val assignedFields = new util.LinkedHashMap[Integer, SqlNode]
val relOptTable = table match {
case t: RelOptTable => t
case _ => null
}
- for (node <- partitions.getList) {
- val sqlProperty = node.asInstanceOf[SqlProperty]
- val id = sqlProperty.getKey
- validateUnsupportedCompositeColumn(id)
- val targetField = SqlValidatorUtil.getTargetField(
- targetRowType,
- typeFactory,
- id,
- calciteCatalogReader,
- relOptTable)
- validateField(idx => !assignedFields.contains(idx), id, targetField)
- val value = sqlProperty.getValue.asInstanceOf[SqlLiteral]
- assignedFields.put(
- targetField.getIndex,
- validator.maybeCast(value, value.createSqlType(typeFactory),
targetField.getType))
- }
+ val assignedFields = new util.HashSet[Integer]
- // validate partial insert columns.
+ // validate partition fields first.
+ val partitionColumns = partitions.getList.map {
+ node =>
+ val id = node.asInstanceOf[SqlProperty].getKey
+ validateUnsupportedCompositeColumn(id)
+ val targetField = SqlValidatorUtil.getTargetField(
+ targetRowType,
+ typeFactory,
+ id,
+ calciteCatalogReader,
+ relOptTable)
+ validateField(assignedFields.add, id, targetField)
+ targetField
+ }
- // the columnList may reorder fields (compare with fields of sink)
- val targetPosition = new util.ArrayList[Int]()
+ if (sqlInsert.getTargetColumnList == null) {
+ return
+ }
- if (sqlInsert.getTargetColumnList != null) {
- val targetFields = new util.HashSet[Integer]
- val targetColumns =
- sqlInsert.getTargetColumnList.getList
- .map(
- id => {
- val identifier = id.asInstanceOf[SqlIdentifier]
- validateUnsupportedCompositeColumn(identifier)
- val targetField = SqlValidatorUtil.getTargetField(
- targetRowType,
- typeFactory,
- identifier,
- calciteCatalogReader,
- relOptTable)
- validateField(targetFields.add, id.asInstanceOf[SqlIdentifier],
targetField)
- targetField
- })
+ // validate partial insert columns.
+ val targetColumns = sqlInsert.getTargetColumnList.getList.map {
+ id =>
+ val identifier = id.asInstanceOf[SqlIdentifier]
+ validateUnsupportedCompositeColumn(identifier)
+ val targetField = SqlValidatorUtil.getTargetField(
+ targetRowType,
+ typeFactory,
+ identifier,
+ calciteCatalogReader,
+ relOptTable)
+ validateField(assignedFields.add, identifier, targetField)
+ targetField
+ }
- val partitionColumns =
- partitions.getList
- .map(
- property =>
- SqlValidatorUtil.getTargetField(
- targetRowType,
- typeFactory,
- property.asInstanceOf[SqlProperty].getKey,
- calciteCatalogReader,
- relOptTable))
+ // unlisted columns are padded with NULL after validation, which requires
them to be nullable
+ for (targetField <- targetRowType.getFieldList) {
+ if (
+ !partitionColumns.contains(targetField) &&
!targetColumns.contains(targetField)
Review Comment:
Yes, that was on master too. It checks `assignedFields` now, which already
holds every listed and partition column.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]