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


##########
spark-connector/spark-common/src/main/java/org/apache/gravitino/spark/connector/jdbc/SparkJdbcTable.java:
##########
@@ -58,6 +67,24 @@ public SparkJdbcTable(
             propertiesConverter,
             sparkTransformConverter,
             sparkTypeConverter);
+    this.sparkSchema = alignTimestampArrays(gravitinoTableInfoHelper.schema(), 
jdbcTable.schema());
+    this.pushDownPolicy =
+        new SparkJdbcPushDownPolicy(
+            gravitinoTable.columns(),
+            jdbcTable.schema(),
+            sparkSchema,
+            JdbcDialects.get(jdbcTable.jdbcOptions().url()));
+  }
+
+  /**
+   * Creates a scan builder that keeps unsafe expressions in Spark.
+   *
+   * @param options the scan options
+   * @return the JDBC scan builder
+   */
+  @Override
+  public JDBCScanBuilder newScanBuilder(CaseInsensitiveStringMap options) {
+    return new SparkJdbcScanBuilder(super.newScanBuilder(options), 
pushDownPolicy);

Review Comment:
   Fixed in 1a43466982c8ae13dbd388b84fee8af3d3e73328. 
`SparkJdbcTable.newScanBuilder` now copies the scan options and removes every 
spelling of `preferTimestampNTZ` before calling Spark's builder. The 
catalog-resolved value therefore continues to control both the fixed table 
schema and reader/aggregate typing. The caller's map and other scan options are 
preserved, and the docs now say this is a catalog-level option.
   
   The PostgreSQL regression covers catalog-on/read-off and 
catalog-off/read-on, mixed-case keys, and row/aggregate consistency with JVM 
Asia/Shanghai and session UTC on Spark 3.5.3 and 4.0.3. Removing the guard made 
both directions fail; restoring it passed.
   



##########
spark-connector/spark-common/src/main/java/org/apache/gravitino/spark/connector/jdbc/SparkJdbcPushDownPolicy.java:
##########
@@ -0,0 +1,267 @@
+/*
+ * 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.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.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>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> readerTypeMismatchColumns;
+  private final Set<String> zonedTimestampColumns;
+  private final boolean ntzLiteralSupported;
+
+  SparkJdbcPushDownPolicy(
+      Column[] columns, StructType readerSchema, StructType sparkSchema, 
JdbcDialect dialect) {
+    this.sensitiveColumns = new HashSet<>();
+    for (Column column : columns) {
+      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 NamedReference) {
+      return !sensitiveColumns.contains(columnName((NamedReference) 
expression));

Review Comment:
   Fixed in 1a43466982c8ae13dbd388b84fee8af3d3e73328. NULL checks on the column 
itself now push exactly. Simple positive PostgreSQL/MySQL string `=` and `IN` 
predicates can prefilter the source, and are also returned to Spark as residual 
predicates. That keeps Spark's collation semantics and prevents LIMIT/aggregate 
pushdown from bypassing the recheck. The builder still respects 
`pushDownPredicate=false` and does not duplicate rejected residuals.
   
   The prefilter is intentionally bounded: raw string columns with Spark's 
default binary comparison and string literals only; negation, compound/cast 
expressions, non-binary Spark collations, backslashes and NUL remain 
conservative. Other JDBC sources retain their existing string policy.
   
   Live PostgreSQL 16/MySQL 8.0 tests on Spark 3.5.3 and 4.0.3 cover results 
and plans for equality/IN, case and trailing-space differences, quotes, NULL 
counts, negation and backslashes, plus retained Spark filters and non-pushed 
LIMIT/aggregates. Temporarily removing the source prefilter or its Spark 
residual each made the regression fail. The documentation and PR description 
now distinguish exact pushdown from prefiltering.
   



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