[PR] [SPARK-59852][SQL] Classify only syntax error SQLSTATEs as syntax errors in H2 and Derby dialects [spark]

["SEPURI-SAI-KRISHNA (via GitHub)" <gi...@apache.org>]

SEPURI-SAI-KRISHNA opened a new pull request, #59130:
URL: https://github.com/apache/spark/pull/59130

   ### What changes were proposed in this pull request?
   
   This PR makes `H2Dialect` and `DerbyDialect` classify only the SQLSTATEs their drivers use for syntax errors as syntax errors, instead of every SQLSTATE of class 42:
   - H2: `42000` and `42001` (`SYNTAX_ERROR_1` and `SYNTAX_ERROR_2` in `org.h2.api.ErrorCode`)
   - Derby: `42X01` and `42X02` (syntax error and lexical error)
   
   This is a follow-up of SPARK-59336, which made the same change for PostgreSQL, and covers the H2 and Derby part of [this review comment](https://github.com/apache/spark/pull/58621#discussion_r3968178897) on it.
   
   ### Why are the changes needed?
   
   `JdbcDialect.isSyntaxErrorBestEffort` promises that a `true` result is guaranteed to be a syntax error. Class 42 also covers errors such as table not found (H2 `42S02`, Derby `42X05`), column not found (H2 `42S22`, Derby `42X04`) and missing privileges (Derby `42502`). These were wrapped as `JDBC_EXTERNAL_ENGINE_SYNTAX_ERROR`, which hides what actually went wrong.
   
   For example, before this PR:
   
   ```scala
   spark.read.format("jdbc").option("url", h2Url).option("dbtable", "NO_SUCH_TABLE").load()
   // SparkException: [JDBC_EXTERNAL_ENGINE_SYNTAX_ERROR.DURING_OUTPUT_SCHEMA_RESOLUTION] ...
   //   caused by: Table "NO_SUCH_TABLE" not found
   ```
   
   After this PR the driver's own exception (`Table "NO_SUCH_TABLE" not found`, SQLSTATE `42S02`) is thrown, as it already is for other dialects that classify syntax errors precisely. Real syntax errors are still reported as `JDBC_EXTERNAL_ENGINE_SYNTAX_ERROR`.
   
   ### Does this PR introduce _any_ user-facing change?
   
   Yes. With H2 and Derby, errors that are not syntax errors, such as a missing table or column, are no longer reported as `JDBC_EXTERNAL_ENGINE_SYNTAX_ERROR`; the original driver exception is thrown instead. Syntax errors are reported as before.
   
   ### How was this patch tested?
   
   - New unit tests in `JDBCSuite` for both dialects, and an end-to-end test in `JDBCSuite` that reads a missing H2 table and column (and checks that a real syntax error is still wrapped). The end-to-end test fails without this change.
   - Checked the SQLSTATEs against the driver versions Spark uses (H2 2.3.232, Derby 10.16.1.1), both from their error code definitions and by running syntax and non-syntax error cases against in-memory databases.
   - Ran the tests under `org.apache.spark.sql.jdbc`, `org.apache.spark.sql.execution.datasources.jdbc` and `org.apache.spark.sql.execution.datasources.v2.jdbc`.
   
   ### Was this patch authored or co-authored using generative AI tooling?
   
   Generated-by: Claude Code (Claude Opus 5.5)
   


-- 
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: reviews-unsubscribe@spark.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


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

Re: [PR] [SPARK-59852][SQL] Classify only syntax error SQLSTATEs as syntax errors in H2 and Derby dialects [spark]

["SEPURI-SAI-KRISHNA (via GitHub)" <gi...@apache.org>]

SEPURI-SAI-KRISHNA commented on PR #59130:
URL: https://github.com/apache/spark/pull/59130#issuecomment-5884671573

   @uros-b, this follows up on your comment on #58621 for the H2 and Derby dialects.


-- 
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: reviews-unsubscribe@spark.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


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