siwen-yu opened a new issue, #12790: URL: https://github.com/apache/seatunnel/issues/12790
# [Bug] [connector-jdbc] Reading PostgreSQL numeric NaN/Infinity aborts the job with "Bad value for type BigDecimal" --- ### Search before asking - [x] I had searched in the [issues](https://github.com/apache/seatunnel/issues?q=is%3Aissue+label%3A%22bug%22) and found no similar issues. ### What happened When a PostgreSQL (or a PostgreSQL-derived database such as Kingbase) `numeric`/`decimal` column contains one of the special numeric values `NaN`, `Infinity` or `-Infinity`, the JDBC source fails to read the row and **the whole job is aborted**, even though the value could safely be read as `null`. `NaN` has long been accepted by the `numeric` type, and `Infinity` / `-Infinity` for `numeric` have been supported since PostgreSQL 14. None of them has a `BigDecimal` representation, so `ResultSet#getBigDecimal(int)` blows up at the driver level: - **text transfer mode**: `org.postgresql.util.PSQLException: Bad value for type BigDecimal : NaN` - **binary transfer mode**: `java.lang.ClassCastException: java.lang.Double cannot be cast to java.math.BigDecimal` (in binary mode the pgjdbc driver decodes the special value into a `Double`) The exception is thrown from `JdbcFieldTypeUtils#getBigDecimal(ResultSet, int)`, which is called by the PostgreSQL row converter and, through the shared base converter, by the other affected dialects: - `PostgresJdbcRowConverter` (connector-jdbc) - `KingbaseJdbcRowConverter` (connector-jdbc) - `AbstractJdbcRowConverter` (connector-jdbc) - `OceanBaseMysqlJdbcRowConverter` (connector-jdbc) Because there is no guard around this call, a single `NaN`/`Infinity` cell fails the entire SeaTunnel job. The expected behavior is to make it possible to read such a special value as `null` (and log a warning) instead of aborting the pipeline, so that data flows and users can decide how to handle these values downstream. ### SeaTunnel Version <!-- e.g. 2.3.13, running with the Zeta engine --> ### SeaTunnel Config ```conf env { parallelism = 1 job.mode = "BATCH" } source { Jdbc { url = "jdbc:postgresql://127.0.0.1:5432/test" driver = "org.postgresql.Driver" user = "postgres" password = "postgres" query = "SELECT id, amount FROM t_numeric_special" } } sink { Console {} } ``` <!-- table used above, for reference: CREATE TABLE t_numeric_special (id int primary key, amount numeric(10,2)); INSERT INTO t_numeric_special VALUES (1, 123.45), (2, 'NaN'); -- PostgreSQL 14+: INSERT INTO t_numeric_special VALUES (3, 'Infinity'), (4, '-Infinity'); --> ### Running Command ```shell ./bin/seatunnel.sh --config ./config/v2.batch.config.template -m local ``` ### Error Exception ```log Caused by: org.postgresql.util.PSQLException: Bad value for type BigDecimal : NaN at org.postgresql.jdbc.PgResultSet.toBigDecimal(PgResultSet.java:2986) at org.postgresql.jdbc.PgResultSet.getBigDecimal(PgResultSet.java:2512) at org.apache.seatunnel.connectors.seatunnel.jdbc.utils.JdbcFieldTypeUtils.getBigDecimal(JdbcFieldTypeUtils.java) at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.dialect.psql.PostgresJdbcRowConverter.toInternal(PostgresJdbcRowConverter.java) ... ``` <!-- In binary transfer mode (prepareThreshold/reWriteBatchedInserts or binary=true) the failure is instead: java.lang.ClassCastException: class java.lang.Double cannot be cast to class java.math.BigDecimal --> ### Zeta or Flink or Spark Version <!-- e.g. Zeta 2.3.13 --> ### Java or Scala Version <!-- e.g. Java 8 --> ### Screenshots N/A ### Are you willing to submit PR? - [x] Yes I am willing to submit a PR! ### Root cause & suggested fix `JdbcFieldTypeUtils#getBigDecimal(ResultSet, int)` calls `resultSet.getBigDecimal(columnIndex)` with no handling for the PostgreSQL special numeric values. To avoid changing the default behavior, the fix is proposed as an opt-in source option `numeric_special_value_as_null` (default `false`), following the existing `decimal_type_narrowing` / `int_type_narrowing` pattern. When enabled, the driver error (`SQLException` in text mode, `ClassCastException` in binary mode) is caught, the raw cell is read as a string, and if it is one of `NaN` / `Infinity` / `-Infinity` a warning is logged and `null` is returned; otherwise the original exception is rethrown so unrelated failures are not swallowed. Normal values keep their current behavior, and the option only takes effect for the PostgreSQL dialect (including `postgresLow` compatible mode). ```java public static BigDecimal getBigDecimal( ResultSet resultSet, int columnIndex, boolean numericSpecialValueAsNull) throws SQLException { try { return resultSet.getBigDecimal(columnIndex); } catch (SQLException | ClassCastException e) { if (numericSpecialValueAsNull) { String value = getStringQuietly(resultSet, columnIndex); if (isNumericSpecialValue(value)) { log.warn( "The numeric special value '{}' at column {} cannot be represented as BigDecimal, reading it as null", value, columnIndex); return null; } } throw e; } } ``` Example config with the option enabled: ```conf source { Jdbc { url = "jdbc:postgresql://127.0.0.1:5432/test" numeric_special_value_as_null = true query = "SELECT id, amount FROM t_numeric_special" } } ``` I have a fix prepared locally together with unit tests covering `NaN`, `Infinity`, `-Infinity`, the binary-mode `ClassCastException` path, the "option disabled keeps the error" case and the "unrelated error is still rethrown" case, and will open a PR to `dev` referencing this issue. -- 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]
