(spark) branch master updated: [SPARK-59336][SQL] Fix syntax error classifier in Postgres connector

[ur...@apache.org]

This is an automated email from the ASF dual-hosted git repository.

uros-b pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/spark.git


The following commit(s) were added to refs/heads/master by this push:
     new 0969874ae571 [SPARK-59336][SQL] Fix syntax error classifier in Postgres connector
0969874ae571 is described below

commit 0969874ae5712791e9edf57c863fee5226557f80
Author: Uros Stankovic <ur...@databricks.com>
AuthorDate: Mon Sep 14 13:42:39 2026 +0000

    [SPARK-59336][SQL] Fix syntax error classifier in Postgres connector
    
    ### What changes were proposed in this pull request?
    
    - Don't classify Postgres 42501 error as syntax error, since that error is permission issue.
    
    https://www.postgresql.org/docs/17/errcodes-appendix.html
    <img width="1268" height="2640" alt="image" src="https://github.com/user-attachments/assets/74e029c5-7edc-44c7-8956-c7a71011cef8" />
    
    ### Why are the changes needed?
    - To properly classify syntax errors in Postgres connector.
    
    ### Does this PR introduce _any_ user-facing change?
    - Yes. For PostgreSQL JDBC queries, Spark now wraps only SQLSTATE `42601` as a syntax error. Other class-42 errors, including `42000` (syntax error or access rule violation), `42501` (insufficient privilege), and `42P01` (undefined table), are propagated as their original PostgreSQL JDBC errors.
    
    ### How was this patch tested?
    - New integration test where we assert no permission error is not classified as SyntaxError (that is behavioural change)
    - New unit tests
    - Existing positive test for syntax error classification here: https://github.com/urosstan-db/spark/blob/6697d711944e4c6cbcf038ce80df8d2272def3a3/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/SharedJDBCIntegrationSuite.scala#L57-L74
    
    ### Was this patch authored or co-authored using generative AI tooling?
    Generated-by: Codex CLI
    
    Closes #58621 from urosstan-db/SPARK-57780-postgres-permission-error.
    
    Authored-by: Uros Stankovic <ur...@databricks.com>
    Signed-off-by: Uros Bojanic <22...@users.noreply.github.com>
---
 .../spark/sql/jdbc/PostgresIntegrationSuite.scala  | 44 ++++++++++++++++++++++
 .../apache/spark/sql/jdbc/PostgresDialect.scala    |  4 +-
 .../spark/sql/jdbc/PostgresDialectSuite.scala      | 12 +++++-
 3 files changed, 58 insertions(+), 2 deletions(-)

diff --git a/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/PostgresIntegrationSuite.scala b/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/PostgresIntegrationSuite.scala
index 78cd846d718c..20e80195091d 100644
--- a/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/PostgresIntegrationSuite.scala
+++ b/connector/docker-integration-tests/src/test/scala/org/apache/spark/sql/jdbc/PostgresIntegrationSuite.scala
@@ -23,6 +23,8 @@ import java.text.SimpleDateFormat
 import java.time.LocalDateTime
 import java.util.Properties
 
+import scala.util.Using
+
 import org.apache.spark.SparkException
 import org.apache.spark.sql.{DataFrame, Row}
 import org.apache.spark.sql.catalyst.util.DateTimeUtils
@@ -30,6 +32,7 @@ import org.apache.spark.sql.functions.lit
 import org.apache.spark.sql.internal.SQLConf
 import org.apache.spark.sql.types._
 import org.apache.spark.tags.DockerTest
+import org.apache.spark.util.Utils
 
 /**
  * To run this test suite for a specific version (e.g., postgres:18.2-alpine):
@@ -382,6 +385,47 @@ class PostgresIntegrationSuite extends SharedJDBCIntegrationSuite {
     assert(sql("select c1, c3 from queryOption").collect().toSet == expectedResult)
   }
 
+  test("SPARK-59336: do not classify missing table as a syntax error") {
+    val postgresError = intercept[SQLException] {
+      spark.read.format("jdbc")
+        .option("url", jdbcUrl)
+        .option("query", "SELECT * FROM table_that_does_not_exist")
+        .load()
+    }
+    assertResult("42P01")(postgresError.getSQLState)
+  }
+
+  test("SPARK-59336: do not classify insufficient privilege as a syntax error") {
+    val restrictedUser = "restricted_user"
+    val restrictedPassword = "restricted_password"
+    val restrictedJdbcUrl = s"jdbc:postgresql://$dockerIp:$externalPort/postgres"
+
+    Utils.tryWithSafeFinally {
+      Using.resource(getConnection()) { conn =>
+        conn.prepareStatement(s"DROP USER IF EXISTS $restrictedUser").executeUpdate()
+        conn.prepareStatement(s"CREATE USER $restrictedUser PASSWORD '$restrictedPassword'")
+          .executeUpdate()
+      }
+
+      val postgresError = intercept[SQLException] {
+        spark.read.format("jdbc")
+          .option("url", restrictedJdbcUrl)
+          .option("dbtable", "bar")
+          .option("user", restrictedUser)
+          .option("password", restrictedPassword)
+          .load()
+      }
+      assertResult("42501")(postgresError.getSQLState)
+      assert(
+        postgresError.getMessage.contains("permission denied"),
+        s"Unexpected PostgreSQL error message: ${postgresError.getMessage}")
+    } {
+      Using.resource(getConnection()) { conn =>
+        conn.prepareStatement(s"DROP USER IF EXISTS $restrictedUser").executeUpdate()
+      }
+    }
+  }
+
   test("write byte as smallint") {
     spark.createDataFrame(Seq((1.toByte, 2.toShort)))
       .write.jdbc(jdbcUrl, "byte_to_smallint_test", new Properties)
diff --git a/sql/core/src/main/scala/org/apache/spark/sql/jdbc/PostgresDialect.scala b/sql/core/src/main/scala/org/apache/spark/sql/jdbc/PostgresDialect.scala
index a85a6ef50c4b..c78d9be492ab 100644
--- a/sql/core/src/main/scala/org/apache/spark/sql/jdbc/PostgresDialect.scala
+++ b/sql/core/src/main/scala/org/apache/spark/sql/jdbc/PostgresDialect.scala
@@ -260,7 +260,9 @@ private case class PostgresDialect()
 
   // See https://www.postgresql.org/docs/current/errcodes-appendix.html
   override def isSyntaxErrorBestEffort(exception: SQLException): Boolean = {
-    Option(exception.getSQLState).exists(_.startsWith("42"))
+    // SQLSTATE 42000 covers both syntax errors and access-rule violations, so preserve the
+    // original exception for that ambiguous state.
+    exception.getSQLState == "42601"
   }
 
   // SHOW INDEX syntax
diff --git a/sql/core/src/test/scala/org/apache/spark/sql/jdbc/PostgresDialectSuite.scala b/sql/core/src/test/scala/org/apache/spark/sql/jdbc/PostgresDialectSuite.scala
index aec5fceb7799..659322fb8f37 100644
--- a/sql/core/src/test/scala/org/apache/spark/sql/jdbc/PostgresDialectSuite.scala
+++ b/sql/core/src/test/scala/org/apache/spark/sql/jdbc/PostgresDialectSuite.scala
@@ -18,7 +18,7 @@
 
 package org.apache.spark.sql.jdbc
 
-import java.sql.{Connection, ResultSet, ResultSetMetaData, Statement, Types}
+import java.sql.{Connection, ResultSet, ResultSetMetaData, SQLException, Statement, Types}
 
 import org.mockito.ArgumentCaptor
 import org.mockito.ArgumentMatchers.anyString
@@ -82,6 +82,16 @@ class PostgresDialectSuite extends SparkFunSuite with MockitoSugar {
     verify(conn).setAutoCommit(false)
   }
 
+  test("SPARK-59336: classify only SQLSTATE 42601 as a syntax error") {
+    assert(dialect.isSyntaxErrorBestEffort(new SQLException("syntax error", "42601")))
+    assert(!dialect.isSyntaxErrorBestEffort(new SQLException("access rule violation", "42000")))
+    assert(!dialect.isSyntaxErrorBestEffort(new SQLException("permission denied", "42501")))
+    assert(!dialect.isSyntaxErrorBestEffort(new SQLException("undefined table", "42P01")))
+    assert(!dialect.isSyntaxErrorBestEffort(new SQLException("undefined column", "42703")))
+    assert(!dialect.isSyntaxErrorBestEffort(new SQLException("undefined function", "42883")))
+    assert(!dialect.isSyntaxErrorBestEffort(new SQLException("error without SQLSTATE")))
+  }
+
   test("updateExtraColumnMeta escapes a single quote in the table and column name") {
     // The array-dimension lookup embeds the table and column name as SQL string literals, so a
     // single quote in either must be escaped to keep the WHERE clause well-formed.


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org