AI & Agent Dev Bug Sandbox logo
AI & Agent Dev Bug Sandbox
Back to Radar

Pg-Meta IS Filter Fails To Normalize Uppercase SQL Keywords

Filtering with the `is` operator and an uppercase value like 'NULL', 'NOT NULL', 'TRUE', or 'FALSE' generates invalid SQL because the value is compared case-sensitively and then treated as a literal string instead of a SQL keyword.

highConfidence 92%Supabase/pg-Meta

Origin Analysis

`isFilterSql` in `packages/pg-meta/src/query/Query.utils.ts` compares the raw filter value directly against lowercase literals (`'null'`, `'true'`, `'false'`, `'not null'`). Any uppercase or mixed-case input fails the comparison, falls through to the quoted-string branch, and produces SQL like `where email is 'NULL'` instead of `where email is null`.
1. Use the pg-meta query builder with `.filter('email', 'is', 'NULL')`; 2. Call `.toSql()` to generate the SQL string; 3. Observe that the output is `select * from public.users where email is 'NULL';` instead of `select * from public.users where email is null;`. The same issue occurs for 'NOT NULL', 'TRUE', and 'FALSE'.

Fixing Code Block

Edge Case Audit

This change could theoretically affect users who intentionally filter for the literal string 'NULL' (lowercase) or 'null' using the `is` operator; such queries would still be interpreted as SQL NULL, though that behavior already existed for lowercase input. If the value is not a string (e.g., numeric or boolean), `String(value)` will coerce it, but that matches typical usage. The fix is localized and unlikely to affect concurrency, multi-threading, or cross-platform behavior since it is a pure string transformation. Rollback is straightforward: revert to the previous case-sensitive comparison. To avoid future regressions, consider centralizing keyword normalization in a shared utility and adding unit tests for all keyword spellings.

Ecosystem Topology