sadpandajoe opened a new pull request, #43312:
URL: https://github.com/apache/superset/pull/43312
fix(snowflake): quote case-sensitive lowercase column identifiers in
generated SQL
### SUMMARY
Snowflake folds unquoted identifiers to UPPERCASE. A physical table created
with an
explicitly double-quoted, case-sensitive lowercase column (e.g.
`create table t ("id" int)`) is stored exactly as `id`, but Superset's
generated
chart queries referenced it unquoted (`SELECT id ...`). Snowflake then
resolved the
unquoted reference to `ID`, which does not match the physical column, and
the query
failed with a column-not-found error.
Root cause: Superset's Snowflake integration correctly detects the exact
reflected
case of a column via SQLAlchemy's `quoted_name(..., quote=True)`, but that
signal is
lost when the name is persisted into a plain string ORM column
(`TableColumn.column_name`). At query-generation time, Superset falls back to
SQLAlchemy's generic auto-quoting heuristic, which only quotes identifiers
containing
uppercase or special characters — the opposite of Snowflake's own
case-folding
convention, where it's the all-lowercase identifiers that need quoting to
survive.
Fix: add a `prepare_identifier()` hook to `BaseEngineSpec` (default: no-op,
so every
other engine's SQL generation is unchanged) and override it in
`SnowflakeEngineSpec`
to explicitly force-quote the stored identifier via `quoted_name(name,
quote=True)`
whenever the owning dataset has `normalize_columns` disabled (the default) —
in that
case the stored name is already the exact physical identifier, so quoting it
verbatim
is always safe. The hook is applied at the physical-column construction
sites used by
chart SELECT/GROUP BY/ORDER BY/WHERE/metric expressions and timestamp
columns.
A related but separate symptom — a table whose own *name* is an explicitly
quoted lowercase Snowflake identifier can block dataset creation — is a
different code
path (table/schema existence checks, not column query generation) and is
intentionally
out of scope here; it needs its own follow-up.
### BEFORE/AFTER SCREENSHOTS OR ANIMATED GIF
N/A — backend-only SQL generation fix, no UI change. No Snowflake
credentials were
available in this environment to capture a live before/after against a real
warehouse;
see TESTING INSTRUCTIONS for how the fix was verified offline.
### TESTING INSTRUCTIONS
1. `pytest tests/unit_tests/connectors/sqla/models_test.py -k
snowflake_case_sensitive`
2. `pytest tests/unit_tests/models/helpers_test.py -k
snowflake_case_sensitive`
3. `pytest tests/unit_tests/db_engine_specs/test_snowflake.py -k
prepare_identifier`
4. `pytest tests/unit_tests/db_engine_specs/test_base.py -k
prepare_identifier`
Manual verification against a real Snowflake instance (not performed here —
no
credentials available):
1. `create table bug_test ("id" int, "name" varchar);` then insert a few
rows.
2. Connect the database to Superset, create a physical dataset from
`bug_test`.
3. Build an un-aggregated table chart using the `id`/`name` columns.
4. Before this fix: the chart errors with a column-not-found error. After:
it renders.
### ADDITIONAL INFORMATION
- [ ] Has associated issue:
- [ ] Required feature flags:
- [ ] Changes UI
- [ ] Includes DB Migration
- [ ] Introduces new feature or API
- [ ] Removes existing feature or API
--
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]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
