Source Postres: Connector provides wrong values for some datatypes

[etsybaev]

Found by comprehensive tests #3562

Expected Behavior

A connector should transform source data into an Airbyte datatype without value/meaning losses.

Current Behavior

General source implementation:

• bit. Returns values true, false instead of 1, 0. Also can't handle values like this "B'101'"
• Postgres source date includes time, and cannot handle BC dates #8903
• airbytehq/airbyte-internal-issues#358
• 'money'. The Money type fails when amount is > 1,000. at the JdbcUtils-> rowToJson -> r.getObject(i); with the reason "Bad value for type double : 1,000.01". The reason is that in jdbc implementation money type is tried to get as Double (jdbc internal implementation for rs.getObject(). JDBC driver also returns Double for getObjectType(), but "Money" in "getMetaData()" ). Meanwhile Max values for Money type in Postres : "-92233720368547758.08", "92233720368547758.07"
• 'numeric', 'decomal'. Numeric and decimal types in Postres may contain 'Nan' type, but in JdbcUtils-> rowToJson we currently try to map it like this, so it fails
  "case NUMERIC, DECIMAL -> o.put(columnName, nullIfInvalid(() -> r.getBigDecimal(i)));"
• 'time', 'timetz'. Time only (ex. 04:05:06) would be represented like "1970-01-01T04:05:06Z" which is incorrect. This causes as JdbcUtils-> DATE_FORMAT is set as ""yyyy-MM-dd'T'HH:mm:ss'Z'"" for both Date and Time types.

Steps to Reproduce

Short way:

1. Checkout master branch
2. Go to CdcPostgresSourceComprehensiveTest.java and "PostresSourceComprehensiveTest.java" and find test with appropriate types. Use example values from comment to insert to addInsertValues and addExpectedValues
3. Run the test.
4. 💣
   NOTES: To REMEMBER while fixing this bug: Most of the cases would need to be corrected at JdbcUtils, but this is based class that is used by all others JDBC DBs, i.e. all other JDBC DBs will be affected

Severity of the bug for you

High

Airbyte Version

0.24.7-alpha

Connector Version

0.3.3

[cgardens]

@etsybaev can this issue be closed? it looks like the related PR was merged.

[etsybaev]

Hi @cgardens.

This is a follow-up ticket. The initial ticket was related to adding new tests only and it took some time for documentation review and finding the edge cases. Fixing this ticket would affect lots of other DBs, as lots of them uses the same core part. I believe we have similar tickets for almost all of others DBs.

[tuliren]

Most type problems should be resolved in #8726.

Separate issues have been created for those that are not resolved.