[I] Redshift sink: schema check fails for mixed-case, quoted or non-search_path target_table [iggy]

["CloneOfAlex (via GitHub)" <gi...@apache.org>]

CloneOfAlex opened a new issue, #4605:
URL: https://github.com/apache/iggy/issues/4605

   ### Bug description
   
   ### What happens
   
   On `open`, the Redshift sink runs `ensure_schema_match`, which reads the columns of the target and staging tables with `load_columns` (`core/connectors/sinks/redshift_sink/src/lib.rs`):
   
   ```rust
   let query = format!(
       "SELECT \"column\", type FROM pg_table_def WHERE tablename = '{}'",
       table.replace('"', "")
   );
   ```
   
   `table` is the output of `quote_identifier` with the double quotes stripped. Three problems follow:
   
   1. **Mixed case.** Redshift folds ASCII letters in delimited (double-quoted) identifiers to lowercase unless `enable_case_sensitive_identifier` is on ([Names and identifiers](https://docs.aws.amazon.com/redshift/latest/dg/r_names.html)). `CREATE TABLE IF NOT EXISTS "Events"` creates `events`, but the lookup filters on `tablename = 'Events'`, finds no rows, and `open` fails with "Table ... was not found or has no visible columns". Any `target_table` with an uppercase letter fails on a default cluster.
   2. **Single quote.** The name is interpolated into a string literal without escaping `'`. A `target_table` such as `o'brien` passes `quote_identifier` and creates fine, but the generated lookup is invalid SQL, so `open` fails. (`"` handling is also lossy: `quote_identifier` doubles it, and the lookup strips it, so the names no longer match.)
   3. **Schema.** `PG_TABLE_DEF` returns only tables in schemas on the `search_path` ([PG_TABLE_DEF](https://docs.aws.amazon.com/redshift/latest/dg/r_PG_TABLE_DEF.html)), and the lookup has no schema filter, so a table outside the path is reported missing and one with the same name in another path schema can be matched instead.
   
   ### Why the tests pass
   
   The integration mock (`core/integration/tests/connectors/fixtures/redshift/redshift_mock/parser.rs`, `rewrite_pg_table_def`) rewrites the query to `information_schema.columns` on PostgreSQL. PostgreSQL keeps the case of quoted identifiers, so problem 1 never shows. The tests also only use lowercase names such as `messages`.
   
   ### Expected
   
   `open` succeeds for any `target_table` that `ensure_tables_exist` can create. Options to agree on here: bind the name as a query parameter instead of formatting it; compare with Redshift's folding applied (lowercase unless case-sensitive identifiers are enabled); and either document that `target_table` is unqualified and must be on the `search_path`, or support a schema and filter `schemaname` (or query `svv_columns` / `information_schema.columns`, which carry it).
   
   ### Notes
   
   Found during a code review of master `e7bd30d`. Not reproduced against a live Redshift cluster: problems 1 and 3 follow from the AWS docs linked above, problem 2 from the generated SQL.
   
   ### Affected area / component
   
   Connectors
   
   ### Deployment
   
   None
   
   ### Versions
   
   master e7bd30d (iggy_connector_redshift_sink as on master)
   
   ### Hardware / environment
   
   _No response_
   
   ### Sample code
   
   _No response_
   
   ### Logs
   
   _No response_
   
   ### Iggy server config
   
   _No response_
   
   ### Reproduction
   
   1. Use a Redshift cluster with default settings (`enable_case_sensitive_identifier` off).
   2. Configure the Redshift sink with `target_table = "Events"` (or `"o'brien"`).
   3. Start the connectors runtime.
   4. The tables are created, then `open` fails in `ensure_schema_match` with "Table ... was not found or has no visible columns" (or a SQL syntax error for the quote).
   
   Expected from the code and the AWS docs; not yet run against a live cluster.
   
   ### Contribution
   
   - [ ] I'm willing to submit a pull request to fix this bug
   
   ### Good first issue
   
   - [ ] I think this could be a good first issue for a new contributor


-- 
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: commits-unsubscribe@iggy.apache.org.apache.org

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

Re: [I] Redshift sink: schema check fails for mixed-case, quoted or non-search_path target_table [iggy]

["tokencraftdata (via GitHub)" <gi...@apache.org>]

tokencraftdata commented on issue #4605:
URL: https://github.com/apache/iggy/issues/4605#issuecomment-6310266702

   The problem is in core/connectors/sinks/redshift_sink/src/lib.rs, inside load_columns(). I propose:
   
   1. Mixed-case names: normalize the catalog lookup according to Redshift’s enable_case_sensitive_identifier setting. For example, Events should be looked up as events when case sensitivity is disabled.
   
   2. Quoted names: replace string interpolation and replace('"', "") with SQL parameters, so names such as o'brien and names containing double quotes are handled safely.
   
   3. Schemas: replace the PG_TABLE_DEF lookup with a parameterized SVV_COLUMNS query filtered by both schema and table name, allowing the correct table to be found outside search_path.I’ll add tests covering all three cases. For schema support, would you prefer a separate target_schema option or schema.table syntax in target_table?
   
   @hubcio @CloneOfAlex can you review my proposal. I can work on it


-- 
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: commits-unsubscribe@iggy.apache.org

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