Summary
The issue involves AWS Query Editor 2 failing to display tables when filtering by table names. This occurs because the resource filter is case-sensitive, unlike SQL query execution in Redshift. Users experience frustration due to the discrepancy between expected behavior (case-insensitive filtering) and actual implementation.
Root Cause
The root cause is design implementation specifics in Query Editor 2:
- The “Filter Resources” box performs exact case-sensitive matching against table names in metadata.
- Table names stored in
pg_tablesretain their original casing if created with double quotes (e.g.,MyTable), while default unquoted names become lowercase (mytable). - Filtering doesn’t normalize case or leverage database-managed pattern-matching logic.
Why This Happens in Real Systems
This discrepancy arises due to common engineering trade-offs:
- Metadata rendering limitations: UI resource filters are often decoupled from database query logic for performance, operating on cached metadata.
- Redshift specifics: Case sensitivity rules differ between DDL creation (
CREATE TABLE "MyTable"vs.CREATE TABLE mytable) and query execution. - UI/backend separation: Frontend filtering avoids expensive live database queries by prototyping w/ loaded schema data.
- Inconsistent client behaviors: Database tools vary; some normalize case client-side (e.g., DBeaver), while others do not.
Real-World Impact
Productivity ramifications include:
- Delayed troubleshooting: Engineers waste time verifying table existence due to unexpected filtering failures.
- Confusion across environments: Inconsistent behavior between Query Editor 2 and other tools (e.g., psql) causes workflow interruptions.
- Metadata misunderstandings: Users misinterpret case sensitivity rules when creating objects.
- Fragmented workarounds: Teams bypass AWS-native tooling for third-party SQL clients.