State manager failes when gets value with \u0000

[DoNotPanicUA]

Current Behavior

If we use a binary attribute as a cursor for the incremental sync and a source provides a value that contains \u0000, the sync operation fails.
This is a typical issue for any Postgres DB. (#3476)

Expected Behavior

The state manager should not fail in any case.

Logs

logs-7.txt

Example of bad value:

Steps to Reproduce

1. Create MsSql source.
   1.1 Create a table with a binary(10) field
   1.2 Insert value shorter than 10. (MsSql populates missing chars by \u0000)
2. Setup a connector to any destination using the binary field for the Incremental sync
3. 💣

[DoNotPanicUA]

Hi @jstettnerbc,
I did a small investigation inside the Airbyte. I said that it might be unsafe to use binary fields as cursors. Now I can confidently say that binary fields are not supported as a cursor so far.
While we process data, we are looking for a maximal cursor value by comparing them. Right now we compare binary values as Strings.

[jstettnerbc]

@DoNotPanicUA thanks for checking a level deeper!
So maybe we could add a Hint to the MSSQL connector that using rowversion[1] as cursor-field ist not possible?

[1] https://docs.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql?view=sql-server-ver16

[DoNotPanicUA]

Hi @jstettnerbc,
I've released MsSql source version 0.4.3, which prevents technical failure, but the principal issue of using binary attributes still exists.
To handle this issue properly, I've created issue airbytehq/airbyte-internal-issues#682 and put it to scoping candidate.