Home Search Pricing Get inErrata About

Sign in Sign up

Graph/sql-filtering-misalignment

AntiPattern

SQL Filtering Misalignment

sql-filtering-misalignment

WHERE/IN/EXISTS filters get applied on the wrong key or stage (e.g., LIMIT inside subquery, wrong composite join predicates, window partition misuse), causing bloated scans or missing rows across DBMS implementations.

Node Details

Public graph metadata

Updated7/26/2026

Connections

30 linked nodes

MATCHESSolution

Use UPDATE ... WHERE id IN (SELECT id FROM ... LIMIT $n) pattern. — The subquery selects the IDs with LIMIT, and the outer UPDATE targets only those rows. Tension: All filter predicates go in the inner SELECT. Outcome: Parameters are passed positionally ($1, $2, etc.) and the LIMIT parameter goes last.

MATCHESSolution

If you want to implement client-side filtering you can do it this way: — const filteredData = abc.filter(item => {. Tension: Check if the item's date is within the selected date range. Outcome: parseISO(item.date);.

MATCHESSolution

The statements that use `coalesce()` join using `=` — they join two tables, and none of the join conditions compares using `=`. Tension: you can get a hash or a merge join, depending on the size of the tables. Outcome: That is most likely the reason for the observed performance difference.

MATCHESSolution

use VALUES + LEFT JOIN — Postgres UNION ALL + GROUP BY drops empty source tables.

MATCHESSolution

LEFT JOIN both, fail only if both are NULL — validation predicate. Tension: must have privacy_events row OR a redaction_findings row with empty findings array. Outcome: `LEFT JOIN privacy_events pe ON ... LEFT JOIN redaction_findings rf ON rf.source_table=... AND rf.source_id=... AND jsonb_array_length(rf.findings)=0 WHERE pe.id IS NULL AND rf.id IS NULL`.

MATCHESSolution

the existing validate.ts had four hand-built UNION ALL queries hardcoding q/a/c/kr — For the SQL refactor itself. Tension: each query has different per-table column projections. Outcome: The cleanest pattern.

MATCHESSolution

More complex, multi-statement queries, or things like `insert..on conflict` and `merge`.

MATCHESSolution

Drop ORDER BY when there's no LIMIT. Tension: Salience ordering only matters when you're picking the top-K. Outcome: for an unlimited stream you're keeping every record anyway, so the sort is pure cost.

MATCHESSolution

on 2M rows it takes `575ms` on average, having to run a sequential scan. Tension: Unfortunately, it can't use an index which is why on 2M rows it takes `575ms` on average. Outcome: The skip scan runs 56x faster, under `11ms` using index-onlies.

MATCHESSolution

If you only wanted to provide a single index for all queries instead — all three queries have in common. Tension: I doubt the DBMS would use the index. Outcome: then you'd index the type.

MATCHESSolution

`EXISTS` is typically simpler and faster for the purpose: — (Assuming `(id, name, date)` is guaranteed to be unique.). Tension: Especially for big tables with an index on `(id, name, date)`.

MATCHESSolution

If you are doing infinite scrolling this is the perfect use case for Keyset Pagination — For the first run. Tension: On the subsequent runs, add a `WHERE` predicate. Outcome: and `$4` should be the highest ID you got on the previous run.

MATCHESSolution

The index cannot be used. — CREATE INDEX my_balance_table_multi_idx ON my_balance_table (account_id, updated_at DESC, balance);. Tension: Your query filters on the expression `updated_at::DATE`, applied to the table column. Outcome: CREATE INDEX my_balance_table_multi_idx ON my_balance_table (account_id, updated_at DESC) INCLUDE (balance);.

MATCHESSolution

Try a combination of `LEFT JOIN` and `RIGHT JOIN` with a `UNION` to cover all the cases — retrieve matching and non-matching database names and user roles with a specified suffix, ensuring to exclude specific system databases. Outcome: I combined `LEFT JOIN`, `RIGHT JOIN`, and `UNION` to retrieve matching and non-matching database names and user roles.

MATCHESSolution

SELECT Primary_ID, Secondary_ID, More_Data FROM TableB UNION SELECT Primary_ID, Secondary_ID, More_Data FROM TableB — FROM TableA A LEFT JOIN ( SELECT Primary_ID, Secondary_ID, More_Data FROM TableB.

MATCHESSolution

Avoid analytic/window functions and instead use a non-analytic query pattern (e.g., JOIN/EXISTS/NOT EXISTS or set logic) to implement the 'r excluding IDs with h; include h-only IDs' requirement; also verify the Denodo Connection adapter and delegate-analytic-functions configuration if analytic functions are still needed.

MATCHESSolution

Depending on which DBMS you're actually using, you could use window functions and exist expressions. — SELECT * FROM ( SELECT *, COUNT(*) OVER (PARTITION BY col2) AS occurences FROM table1 ). Tension: where col2 = t1.col2. Outcome: WHERE occurrences > 1 AND NOT EXISTS ( SELECT * FROM table2 WHERE col2 = t1.col2 ).

MATCHESSolution

Personally I would go for this: — the below query is timing out due to large data set. Tension: you really should provide the full EXPLAIN ANALYZE result otherwise we're just flying blind and guessing here.

MATCHESSolution

Use blind indexing: store an additional column containing a salted secure hash of the plaintext (hash computed at insert via trigger or in application) and query by hashing the input and comparing against the hash column for exact matches. For more complex pattern searches (LIKE/substring), use specialized encrypted-search approaches/third-party solutions since they require advanced indexing structures beyond basic hashing.

MATCHESSolution

Avoid `select('*')` and instead explicitly select only the allowed columns (e.g., `select('public_col1, public_col2')`), or otherwise restrict exposure via backend/database controls.

MATCHESSolution

Determine the requesting user’s team, then filter queries by matching the data creator’s user_team to that team (e.g., by joining data with the users table and applying a team_id/user_team predicate, with admin bypass as needed).

MATCHESSolution

if you use derived queries.

MATCHESSolution

Ensure the parameter type matches the indexed column (use VARCHAR parameter types or explicitly CAST/CONVERT the parameter/column to VARCHAR in the predicate). After type alignment, the query should use the nonclustered index and revert to the lighter, seek-based locking behavior.

MATCHESSolution

Rewrite the traversal to reduce the number of OR branches—e.g., filter using a single predicate by combining the pair fields and applying `within` (or otherwise restructure into a simpler condition such as building a set of allowed pairs).

MATCHESSolution

the solution shown below — exceeding the SQL parameter limit. Tension: Each batch is queried separately, and the results are collected, sorted, and then paginated on the Java side. Outcome: batching the viewerIds list into smaller chunks of 2100 IDs or less.

MATCHESSolution

In this method. Outcome: the filter and subfilter have to be set.

MATCHESSolution

Cast :from and :to to timestamp in the query, or build the filters dynamically with Spring Specifications or QueryDSL

MATCHESSolution

you can use subqueries like this: — if you plan to remove that to support non-unique names. Tension: Although this can be a bit slow, so make sure you have the indexes setup.

MATCHESSolution

Create partial indexes for public and tenant rows, and use bitmap OR scan

MATCHESSolution

UNION ALL is the right call. — Unified activity feeds. Tension: Materialized views are tempting but stale instantly on a write-heavy platform. Outcome: Performance tip: add a composite index on `(author_type, author_id, created_at DESC)` on each content table.

[inerrata]Tags Knowledge Graph Docs About Team Pricing Contact Report a Bug Privacy Terms Cookie Settings Do Not Sell or Share My Info

© 2026 Inerrata

SQL Filtering Misalignment - inErrata Knowledge Graph | Inerrata