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


##########
spark-connector/spark-common/src/main/java/org/apache/gravitino/spark/connector/jdbc/SparkJdbcTable.java:
##########
@@ -31,14 +34,26 @@
 import org.apache.spark.sql.connector.catalog.Identifier;
 import org.apache.spark.sql.connector.write.LogicalWriteInfo;
 import org.apache.spark.sql.connector.write.WriteBuilder;
+import org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions;
+import org.apache.spark.sql.execution.datasources.v2.jdbc.JDBCScanBuilder;
 import org.apache.spark.sql.execution.datasources.v2.jdbc.JDBCTable;
 import org.apache.spark.sql.execution.datasources.v2.jdbc.JDBCTableCatalog;
+import org.apache.spark.sql.jdbc.JdbcDialects;
+import org.apache.spark.sql.types.DataTypes;
+import org.apache.spark.sql.types.StructField;
 import org.apache.spark.sql.types.StructType;
+import org.apache.spark.sql.util.CaseInsensitiveStringMap;
+import scala.collection.JavaConverters;
 
 public class SparkJdbcTable extends JDBCTable {
 
+  private static final String[] UNSAFE_OPTIONS = {
+    "pushDownPredicate", "pushDownAggregate", "pushDownLimit", "pushDownOffset"

Review Comment:
   Fixed in 64d3a4e8b, with follow-ups in 5203e8c79, 0bd290403, and 46c62e900. 
`SparkJdbcTable.newScanBuilder` now returns `SparkJdbcScanBuilder`, so pushdown 
is decided per expression instead of per table:
   - `pushPredicates` keeps only the unsafe predicates in Spark.
   - `pushAggregation`, `supportCompletePushDown`, and `pushTopN` reject 
expressions that involve string or binary columns (also inside arrays), string 
or binary literals or casts, `TIMESTAMP_NTZ` literals the dialect cannot write, 
time-zone-dependent casts and date parts, or date parts whose source semantics 
differ (such as `second()` or `weekofyear()`).
   - LIMIT and OFFSET are unchanged.
   - The user's `pushDown*` options are no longer overridden.
   
   `TestSparkJdbcReadCorrectness` now checks the plans on a PostgreSQL 16 table 
with string columns. `id = 1`, `LIMIT 2`, `count(*)`, and `ORDER BY id LIMIT 2` 
are pushed. The string filter, string Top-N, `min/max(s)`, `ORDER BY tags`, and 
`max(tags)` stay in Spark. With a string filter in Spark, LIMIT and `count(*)` 
also stay in Spark. Thanks for the detailed reproduction.
   



##########
spark-connector/spark-common/src/main/java/org/apache/gravitino/spark/connector/jdbc/SparkJdbcTypeConverter.java:
##########
@@ -44,7 +64,7 @@ public DataType toSparkType(Type gravitinoType) {
           "Reading type {} as a string, Gravitino cannot represent it",
           ((Types.ExternalType) gravitinoType).catalogString());
       return DataTypes.StringType;
-    } else if (gravitinoType instanceof Types.TimestampType) {
+    } else if (gravitinoType instanceof Types.TimestampType && 
!useTimestampNtz) {

Review Comment:
   Fixed in 64d3a4e8b. As you suggested, the NTZ mapping is now an opt-in 
catalog option: `preferTimestampNTZ`, set as a Spark catalog option or as the 
Gravitino property `spark.bypass.preferTimestampNTZ`. It is off by default, so 
the default path keeps the old `TimestampType` getter and the `infinity` clamp. 
The docs list the `long overflow` on `infinity`/`-infinity` as a known 
limitation when the option is on. I did not register an extra dialect, because 
of the `AggregatedDialect` and JVM-wide registry problems you found. The clamp 
belongs in Spark's `convertJavaTimestampToTimestampNTZ` upstream.
   



##########
spark-connector/spark-common/src/main/java/org/apache/gravitino/spark/connector/jdbc/SparkJdbcTypeConverter.java:
##########
@@ -44,7 +64,7 @@ public DataType toSparkType(Type gravitinoType) {
           "Reading type {} as a string, Gravitino cannot represent it",
           ((Types.ExternalType) gravitinoType).catalogString());
       return DataTypes.StringType;
-    } else if (gravitinoType instanceof Types.TimestampType) {
+    } else if (gravitinoType instanceof Types.TimestampType && 
!useTimestampNtz) {

Review Comment:
   Fixed in 64d3a4e8b. `SparkJdbcTable` now uses the reader's element type for 
timestamp arrays (`alignTimestampArrays`): `TIMESTAMP` on Spark 3.5 and 
`TIMESTAMP_NTZ` on 4.0, while top-level columns keep the converter's type. 
`TestSparkJdbcReadCorrectness` now reads a PostgreSQL `timestamp[]` column on 
both versions, and 
`TestSparkJdbcTableWriteValidation.testTimestampArraysUseTheReaderElementType` 
covers the mapping. The docs say that on 3.5, array elements stay `TIMESTAMP`.
   



##########
docs/spark-connector/spark-connector.md:
##########
@@ -152,3 +152,24 @@ these columns are rejected before JDBC execution, 
including writes of NULL value
 INSERT OVERWRITE. The error identifies the unsupported column. Spark binds 
these columns
 as VARCHAR, which does not preserve the PostgreSQL target type. Use a native 
PostgreSQL
 writer for these tables. Other PostgreSQL column types retain their existing 
write behavior.
+
+### JDBC query consistency
+
+Some JDBC tables are read without pushdown. For these tables, Spark evaluates 
predicates,
+aggregates, Top-N, limits, and offsets itself. This avoids result differences 
caused by the
+column types below. A table is affected when it has a column of one of these 
types:
+
+- String, char, varchar, binary, UUID, or an external type. The source 
database can compare
+  strings with a different collation, and Spark cannot always write binary 
literals in SQL.
+- A column that maps to Spark `TIMESTAMP_NTZ` (today, only a PostgreSQL 
timestamp without time
+  zone), when the JDBC dialect cannot write `TIMESTAMP_NTZ` literals in SQL. 
This is the case
+  in Spark 3.5, not in Spark 4.0.
+
+The rule applies to the whole table. Filters on numeric columns are not pushed 
down either, so
+Spark can read more data from the source. You cannot turn pushdown back on 
with the
+`pushDownPredicate`, `pushDownAggregate`, `pushDownLimit`, or `pushDownOffset` 
options. Other
+tables keep their normal pushdown settings.
+
+For PostgreSQL catalogs, a timestamp without time zone maps to Spark 
`TIMESTAMP_NTZ`. The

Review Comment:
   Fixed in 64d3a4e8b. NTZ is now opt-in and off by default, so neither 
behavior changes for existing jobs. For users who turn it on, the docs now list:
   - writes use the Spark session time zone instead of the JVM time zone;
   - a timestamp column cannot be `partitionColumn`;
   - `infinity` reads fail with `long overflow`;
   - on Spark 3.5, timestamp arrays stay `TIMESTAMP`.
   
   The PR description repeats these items for the 1.3.x release notes.
   



##########
spark-connector/spark-common/src/main/java/org/apache/gravitino/spark/connector/jdbc/SparkJdbcTable.java:
##########
@@ -99,4 +145,51 @@ public StructType schema() {
   public Map<String, String> properties() {
     return gravitinoTableInfoHelper.properties();
   }
+
+  private static boolean hasUnsafePushDown(JDBCTable table, Table source) {
+    for (Column column : source.columns()) {
+      Type type = column.dataType();
+      if (type instanceof Types.StringType
+          || type instanceof Types.VarCharType
+          || type instanceof Types.FixedCharType
+          || type instanceof Types.BinaryType
+          || type instanceof Types.ExternalType
+          || type instanceof Types.UUIDType) {
+        return true;
+      }
+    }
+    for (StructField field : table.schema().fields()) {

Review Comment:
   Fixed in 64d3a4e8b, narrowed in 5203e8c79. The NTZ-literal check is now per 
expression: only predicates with a `TIMESTAMP_NTZ` literal the dialect cannot 
write stay in Spark, so `WHERE id = 1` on an `int` + `timestamptz` table is 
pushed again on 3.5.3. Following your suggestion, aggregates stay in Spark on 
columns whose reader type differs from the Spark-visible type. 5203e8c79 limits 
this rule to timestamp types, because numeric widening, such as MySQL 
`SMALLINT` read as `int` on 3.5, keeps correct pushed results. 
`testZonedTimestampTableKeepsPushdownAndAggregateTypes` covers a `timestamptz` 
table on 3.5 and 4.0: `id = 1` is pushed, and `max(tz)` is correct in three 
session time zones.
   



##########
spark-connector/spark-common/src/main/java/org/apache/gravitino/spark/connector/jdbc/GravitinoJdbcCatalog.java:
##########
@@ -53,6 +53,9 @@ protected TableCatalog createAndInitSparkCatalog(
     Map<String, String> all =
         getPropertiesConverter().toSparkCatalogProperties(options, properties);
     applyJdbcCredential(gravitinoCatalogClient, all);
+    if (useTimestampNtz()) {
+      all.put("preferTimestampNTZ", "true");

Review Comment:
   Fixed in 64d3a4e8b. One value now drives both the reader flag and the 
converter. `GravitinoJdbcCatalog` resolves `preferTimestampNTZ` from the Spark 
catalog option first, then from the Gravitino property 
`spark.bypass.preferTimestampNTZ` (5203e8c79 aligned the prefix with 
`PropertiesConverter`). It removes every spelling of the key before writing the 
resolved value, and 0bd290403 rejects values other than `true`/`false`. 
`TestGravitinoJdbcCatalog` covers key spellings, precedence, and invalid 
values. `testNtzOptionKeepsOnlyOneSpelling` checks the key cleanup directly, so 
it does not depend on the Scala map order.
   



##########
spark-connector/spark-common/src/main/java/org/apache/gravitino/spark/connector/jdbc/SparkJdbcTable.java:
##########
@@ -99,4 +145,51 @@ public StructType schema() {
   public Map<String, String> properties() {
     return gravitinoTableInfoHelper.properties();
   }
+
+  private static boolean hasUnsafePushDown(JDBCTable table, Table source) {
+    for (Column column : source.columns()) {
+      Type type = column.dataType();
+      if (type instanceof Types.StringType

Review Comment:
   Fixed in 64d3a4e8b. The check now recurses into list, map, and struct 
element types, so a `text[]` column is treated like a `text` column. 
`TestSparkJdbcPushDownPolicy` covers the `tags` array column, and 
`TestSparkJdbcReadCorrectness` reproduces your case with `text[] COLLATE 
"en-US-x-icu"`: `ORDER BY tags LIMIT 1` returns id 2, and `max(tags)` returns 
`[a]`, as Spark does.
   



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