Syntax error when schema has hyphen

[gleberb]

Environment

• Airbyte version: 0.40.26
• OS Version / Instance: AWS EC2 AmazonLinux2
• Deployment: Kubernetes deploy env
• Source Connector and version: PostgreSQL version 1.0.35
• Destination Connector and version: S3 version 0.3.18
• Step where error happened: Sync job

Current Behavior

I've just upgrade Airbyte to version 0.40.26 and PostgreSQL Connector to version 1.0.36 and since then we are getting the following error message: org.postgresql.util.PSQLException: ERROR: syntax error at or near "-"
Position: 252

Expected Behavior

The sync should run with success.

Logs

Stack Trace: org.postgresql.util.PSQLException: ERROR: syntax error at or near "-" Position: 252 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408) at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:329) at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:315) at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:291) at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:243) at com.zaxxer.hikari.pool.ProxyStatement.executeQuery(ProxyStatement.java:110) at com.zaxxer.hikari.pool.HikariProxyStatement.executeQuery(HikariProxyStatement.java) at io.airbyte.integrations.source.postgres.PostgresSource.lambda$verifyCursorColumnValues$12(PostgresSource.java:532) at io.airbyte.db.jdbc.DefaultJdbcDatabase.bufferedResultSetQuery(DefaultJdbcDatabase.java:55) at io.airbyte.integrations.source.postgres.PostgresSource.verifyCursorColumnValues(PostgresSource.java:532) at io.airbyte.integrations.source.postgres.PostgresSource.verifyCursorColumnValues(PostgresSource.java:78) at io.airbyte.integrations.source.relationaldb.AbstractDbSource.validateCursorFieldForIncrementalTables(AbstractDbSource.java:219) at io.airbyte.integrations.source.relationaldb.AbstractDbSource.read(AbstractDbSource.java:162) at io.airbyte.integrations.source.postgres.PostgresSource.read(PostgresSource.java:330) at io.airbyte.integrations.base.ssh.SshWrappedSource.read(SshWrappedSource.java:64) at io.airbyte.integrations.base.IntegrationRunner.runInternal(IntegrationRunner.java:138) at io.airbyte.integrations.base.IntegrationRunner.run(IntegrationRunner.java:98) at io.airbyte.integrations.base.adaptive.AdaptiveSourceRunner$Runner.run(AdaptiveSourceRunner.java:86) at io.airbyte.integrations.source.postgres.PostgresSourceRunner.main(PostgresSourceRunner.java:15)

Steps to Reproduce

1. Create a PostgreSQL Database
2. Create a schema with hyphen, for exemple, "my-new-schema"
3. Create a table for this schema
4. At Airbyte, create a new PostgreSQL source using version 1.0.35 and configuring the new PostgreSQL database and the new schema
5. At Airbyte, create a new destination, for example, S3
6. At Airbyte, create a new connection between the source created at step 4 and step 5
7. Run sync

Are you willing to submit a PR?

I will not be able to submit a PR, but I've compare the version 1.0.30 and 1.0.35 of connector and I think the problem is in class
io.airbyte.integrations.source.postgres.PostgresSource at the declaration of static variable NULL_CURSOR_VALUE_WITH_SCHEMA:
public static final String NULL_CURSOR_VALUE_WITH_SCHEMA = """ SELECT (EXISTS (SELECT FROM information_schema.columns WHERE table_schema = '%s' AND table_name = '%s' AND is_nullable = 'YES' AND column_name = '%s')) AND (EXISTS (SELECT from %s.\"%s\" where \"%s\" IS NULL LIMIT 1)) AS %s """;

Notice that at code (EXISTS (SELECT from %s.\"%s\" where \"%s\" IS NULL LIMIT 1)) AS %s there is no scape character for schema like in table name.
I think it should be: (EXISTS (SELECT from \"%s\".\"%s\" where \"%s\" IS NULL LIMIT 1)) AS %s.

Thank you very much.

[sh4sh]

A user in Slack has reported that this behaviour occurs in connector version 1.31 and up: (link to thread)

[sh4sh]

This issue has been fixed in #21051. To resolve this behaviour, update the source-postgres connector to version 1.0.38