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


##########
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:
   The new `IS NULL`/`IS NOT NULL` early return in `canPush` checks `columns`, 
which includes external-type columns, so it now pushes null checks that 
`d7a2ba21f` kept in Spark. That is wrong for PostgreSQL composite types: 
PostgreSQL's row-value `IS NULL` looks at the fields, while Spark reads the 
value as a non-null string such as `(1,)`. With `p pair` holding `ROW(1,NULL)`, 
`ROW(NULL,NULL)`, `ROW(1,2)` and `NULL`, on 3.5.3 and 4.0.3 `WHERE p IS NOT 
NULL` returns only id 3 instead of 1, 2 and 3, and `count(*)` gives 1 instead 
of 3. Because Spark infers `isnotnull(p)` from equality, `WHERE p = '(1,)'` 
also comes back empty. `d7a2ba21f` returned the right rows for all of these 
(checked on 4.0.3).
   
   This comes from my earlier comment saying null checks on the column could be 
pushed as they are; that was too broad. Excluding columns whose Gravitino type 
is or contains `ExternalType` from that early return would fix it, and a 
composite-type case in `TestSparkJdbcStringPushDown` would catch it.



##########
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)) {
+        // Nullness of the column itself is unchanged by string collation or 
literal compilation.
+        return true;
+      }
+    }
+    if (expression instanceof NamedReference) {
+      return !sensitiveColumns.contains(columnName((NamedReference) 
expression));
+    }
+    if (expression instanceof Literal) {
+      DataType type = ((Literal<?>) expression).dataType();
+      return !isSensitive(type) && (ntzLiteralSupported || !containsNtz(type));
+    }
+    if (expression instanceof Cast) {
+      Cast cast = (Cast) expression;
+      // Casts to or from TIMESTAMP depend on the time zone: Spark uses the 
session time zone, and
+      // the source uses its own. Casts to TIMESTAMP_NTZ also stay in Spark, 
which is conservative.
+      if (isSensitive(cast.dataType())
+          || containsTimestamp(cast.dataType())
+          || referencesZonedTimestamp(cast.expression())) {
+        return false;
+      }
+    }
+    if (expression instanceof Extract) {
+      Extract extract = (Extract) expression;
+      // Date parts of a TIMESTAMP depend on the time zone. Other fields, such 
as SECOND (fractions
+      // in PostgreSQL) or WEEK (MySQL week mode on Spark 3.5), can differ 
from Spark at the source.
+      if 
(!PUSHABLE_EXTRACT_FIELDS.contains(extract.field().toUpperCase(Locale.ROOT))
+          || referencesZonedTimestamp(extract.source())) {
+        return false;
+      }
+    }
+    for (Expression child : expression.children()) {
+      if (!canPush(child)) {
+        return false;
+      }
+    }
+    return true;
+  }
+
+  /**
+   * Returns whether an aggregate function or group-by expression may be 
evaluated by the source.
+   *
+   * @param expression the expression to check
+   * @return true if pushing it keeps Spark results and result types
+   */
+  boolean canPushAggregate(Expression expression) {
+    if (expression instanceof NamedReference
+        && readerTypeMismatchColumns.contains(columnName((NamedReference) 
expression))) {
+      return false;
+    }
+    if (!canPush(expression)) {

Review Comment:
   Non-distinct `count(s)` on a string column still stays in Spark: 
`canPushAggregate` passes the `Count` to `canPush`, which rejects the string 
column. On the 1M-row PostgreSQL table, `SELECT count(s)` takes 298-320 ms on 
3.5.3 and 4.0.3, while base pushes `COUNT(s)` and takes 110-130 ms. On MySQL, 
base also pushes `COUNT(s)` and this PR doesn't. I missed this one in my 
earlier comment.
   
   A non-distinct count only depends on nullness, so it is exact at the source 
for the same columns where the null-check early return is safe. Returning true 
in `canPushAggregate` for a `Count` with `isDistinct() == false` over such a 
column, before the `canPush` call and the child loop, would bring it back; 
`count(DISTINCT s)` should stay in Spark because it depends on the collation.



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