yuqi1129 commented on code in PR #14643:
URL: https://github.com/apache/gravitino/pull/14643#discussion_r4508801497


##########
spark-connector/spark-common/src/main/java/org/apache/gravitino/spark/connector/jdbc/SparkJdbcPushDownPolicy.java:
##########
@@ -0,0 +1,345 @@
+/*
+ * 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.gravitino.spark.connector.jdbc;
+
+import com.google.common.collect.ImmutableSet;
+import java.time.LocalDateTime;
+import java.util.HashSet;
+import java.util.Locale;
+import java.util.Set;
+import org.apache.gravitino.rel.Column;
+import org.apache.gravitino.rel.types.Type;
+import org.apache.gravitino.rel.types.Types;
+import org.apache.spark.sql.connector.expressions.Cast;
+import org.apache.spark.sql.connector.expressions.Expression;
+import org.apache.spark.sql.connector.expressions.Extract;
+import org.apache.spark.sql.connector.expressions.Literal;
+import org.apache.spark.sql.connector.expressions.NamedReference;
+import org.apache.spark.sql.connector.expressions.filter.Predicate;
+import org.apache.spark.sql.jdbc.JdbcDialect;
+import org.apache.spark.sql.types.ArrayType;
+import org.apache.spark.sql.types.BinaryType;
+import org.apache.spark.sql.types.CharType;
+import org.apache.spark.sql.types.DataType;
+import org.apache.spark.sql.types.DataTypes;
+import org.apache.spark.sql.types.MapType;
+import org.apache.spark.sql.types.StringType;
+import org.apache.spark.sql.types.StructField;
+import org.apache.spark.sql.types.StructType;
+import org.apache.spark.sql.types.TimestampNTZType;
+import org.apache.spark.sql.types.TimestampType;
+import org.apache.spark.sql.types.VarcharType;
+
+/**
+ * Decides which expressions a JDBC source may evaluate without changing Spark 
query results.
+ *
+ * <p>An expression stays in Spark when it has a known difference from Spark. 
These are:
+ *
+ * <ul>
+ *   <li>string-like or binary columns, also inside arrays, maps, and structs;
+ *   <li>string or binary literals and casts;
+ *   <li>casts to a timestamp type, and casts or date parts of a Spark {@code 
TIMESTAMP} column;
+ *   <li>date parts other than year, quarter, month, day, hour, and minute;
+ *   <li>{@code TIMESTAMP_NTZ} literals the dialect cannot write in SQL.
+ * </ul>
+ *
+ * <p>Null checks on a column itself are exact. Simple positive 
PostgreSQL/MySQL string predicates
+ * can prefilter rows while Spark rechecks them; see {@link 
#canPrefilter(Predicate)}.
+ *
+ * <p>Aggregates also stay in Spark for timestamp columns whose reader type 
differs from the type
+ * Spark sees, because the reader types the pushed aggregate result.
+ */
+class SparkJdbcPushDownPolicy {
+
+  private static final Set<String> PUSHABLE_EXTRACT_FIELDS =
+      ImmutableSet.of("YEAR", "QUARTER", "MONTH", "DAY", "HOUR", "MINUTE");
+
+  private final Set<String> sensitiveColumns;
+  private final Set<String> columns;
+  private final Set<String> stringColumns;
+  private final boolean stringPrefilterSupported;
+  private final Set<String> readerTypeMismatchColumns;
+  private final Set<String> zonedTimestampColumns;
+  private final boolean ntzLiteralSupported;
+
+  SparkJdbcPushDownPolicy(
+      Column[] columns,
+      StructType readerSchema,
+      StructType sparkSchema,
+      JdbcDialect dialect,
+      String jdbcUrl) {
+    this.sensitiveColumns = new HashSet<>();
+    this.columns = new HashSet<>();
+    this.stringColumns = new HashSet<>();
+    String url = jdbcUrl.toLowerCase(Locale.ROOT);
+    // These sources can return extra collation matches, which Spark removes 
with a residual.
+    // Other JDBC sources retain their existing conservative policy until 
validated separately.
+    this.stringPrefilterSupported =
+        url.startsWith("jdbc:postgresql:") || url.startsWith("jdbc:mysql:");
+    for (Column column : columns) {
+      this.columns.add(normalize(column.name()));
+      Type type = column.dataType();
+      if ((type instanceof Types.StringType
+              || type instanceof Types.VarCharType
+              || type instanceof Types.FixedCharType)
+          && DataTypes.StringType.equals(fieldType(sparkSchema, 
column.name()))) {
+        stringColumns.add(normalize(column.name()));
+      }
+      if (isSensitive(column.dataType())) {
+        sensitiveColumns.add(normalize(column.name()));
+      }
+    }
+    this.readerTypeMismatchColumns = new HashSet<>();
+    this.zonedTimestampColumns = new HashSet<>();
+    for (StructField field : sparkSchema.fields()) {
+      if (field.dataType() instanceof TimestampType) {
+        zonedTimestampColumns.add(normalize(field.name()));
+      }
+      DataType readerType = fieldType(readerSchema, field.name());
+      // Only timestamp differences change pushed aggregate results; Spark 
widens numeric results
+      // safely. catalogString ignores nullability, which does not change 
pushed results.
+      if (readerType == null
+          || 
(!readerType.catalogString().equals(field.dataType().catalogString())
+              && (containsTimestamp(readerType) || 
containsTimestamp(field.dataType())))) {
+        readerTypeMismatchColumns.add(normalize(field.name()));
+      }
+    }
+    // Spark 3.5 leaves LocalDateTime literals unquoted, while Spark 4 quotes 
them. Check the
+    // dialect itself instead of a Spark version or provider name.
+    this.ntzLiteralSupported =
+        dialect.compileValue(LocalDateTime.of(2000, 1, 1, 0, 0)) instanceof 
String;
+  }
+
+  /**
+   * Returns whether a predicate, sort, or group-by expression may be 
evaluated by the source.
+   *
+   * @param expression the expression to check
+   * @return true if pushing it keeps Spark results
+   */
+  boolean canPush(Expression expression) {
+    if (expression instanceof Predicate) {
+      Predicate predicate = (Predicate) expression;
+      Expression[] children = predicate.children();
+      if (("IS_NULL".equals(predicate.name()) || 
"IS_NOT_NULL".equals(predicate.name()))
+          && children.length == 1
+          && isColumn(children[0], columns)) {

Review Comment:
   Fixed in 738684a9c23d9a55b73df76c73effe54586e0a86, with case-collision 
follow-up 80bbe53ad63d0b2d95bec751ddd3e5bfbb26e342. NULL checks and ordinary 
column counts now share a null-preserving column set that recursively excludes 
`ExternalType` through lists, map keys/values, and struct fields. 
Case-insensitive name matching also conservatively excludes a same-spelling 
external column regardless of column order. This keeps inferred `isnotnull(p)` 
in Spark as well, so equality no longer drops partially-null composite values.
   
   The live PostgreSQL 16 fixture covers `ROW(1,NULL)`, `ROW(NULL,NULL)`, 
`ROW(1,2)`, and SQL NULL on Spark 3.5.3 and 4.0.3. It checks results and plans 
for `IS NULL`, `IS NOT NULL`, equality, NULL-filtered `count(*)`, and 
`count(p)`. The new regression failed against the prior implementation, and 
re-enabling external columns in the shared set also made its unit test fail. 
All tests pass with the guard restored. The docs and PR description now state 
the external-type exclusion.
   
   The follow-up also excludes normalized duplicate names from string 
prefiltering. A case-sensitive PostgreSQL table with `value text` and `"VALUE" 
pair` is covered, including a literal that is invalid as a composite. Removing 
the name-collision guard made the real PostgreSQL query fail; with the guard it 
stays in Spark and returns the expected rows.
   



-- 
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]

Reply via email to