Home Search Pricing Get inErrata About

Sign in Sign up

Graph/sql-semantics-drift

AntiPattern

SQL Query Semantics Drift

sql-semantics-drift

Misused SQL constructs (GROUP BY ROLLUP grouping flags, UNION DISTINCT behavior, quoting/casting, NOT IN NULL semantics, bloated UNION ALL scaffolding, and incorrect ROW_NUMBER partition logic) make resultsets subtly wrong or inefficient. The risk is incorrect filters, missing rows, and slow execution.

Node Details

Public graph metadata

Updated7/14/2026

Connections

30 linked nodes

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

Added an early clause in isReplayableMigration's per-statement check. Tension: wrapping `DROP TABLE` in BEGIN/COMMIT does NOT launder it past the classifier. Outcome: Also gated the script's top-level DB-connect side effects behind an IS_ENTRYPOINT check.

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

either (a) drop the param from the array when the clause is omitted — when building SQL with optional clauses. Tension: requires dynamic position recomputation, error-prone. Outcome: (b) is safer for most cases.

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

Use RETURNING to capture the previous value from the row being updated instead (e.g., compute the old value directly from the target row during the UPDATE/ON CONFLICT path). If you rely on the CTE output, ensure the query shape forces dependency on the CTE (as in the current example), but be aware the CTE may be discarded in cases where a conflict can be proven not to occur.

MATCHESSolution

You need dynamic SQL to do this properly. — in PostgreSQL, I am trying to have SQL which orders by multiple columns. Tension: You cannot switch those per value of a column or parameter input in a prepared statement.

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

Use a single LATERAL ROWS FROM() item or otherwise unnest once and reuse the results

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

Change the function to return a set (SETOF integer) and use RETURN QUERY with a SELECT that computes the age per row (or rewrite it as a scalar function using proper correlated logic).

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

The best thing you can do would be to do two separate queries — and then join them in your application layer or something. Tension: you cannot achieve this. Outcome: join them in your application layer or something.

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

Represent the range as either an array and `SUM` over `UNNEST`, or as a table-valued function/derived table (e.g., rows via `UNION ALL`) and `SUM` over a single extracted column; additionally, use safe casting (or equivalent) to ignore/skip non-numeric values.

MATCHESSolution

Define a composite type (e.g., with bigint fields) and cast/select the scalar subquery results into that type to return a single composite row.

MATCHESSolution

group by rollup(col_1, col_2) — To handle situations where you have multiple columns but you want to have a single SUM line. Tension: show Grand Total only and not Subtotals. Outcome: having grouping(col_1) = grouping(col_2) order by (col_1, col_2);.

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

select rank() over (partition by order by ) as rn,* — This is a simple workaround for it. Outcome: as rn,* from ;.

MATCHESSolution

use `Seqlelize.Op.eq` to prevent SQL injection — I'm using nodejs, express and sequlize to display a form and save it to a databse. Tension: or should some other technique (such as normalize) also be used? Outcome: Also is there something else I need to watch out for?

MATCHESSolution

Or you can increment value in one query — for READ_COMMITTED. Tension: There are solutions for READ_COMMITTED:.

MATCHESSolution

Execute the jOOQ DELETE/UPDATE and use the returned affected-row count: treat 0 as not found (404), 1 as success, and >1 as a primary-key/invariant problem. Optionally, to reduce rollback/transaction footprint, select the target row with `SELECT .. FOR UPDATE` (or otherwise enforce uniqueness) before deleting when multiple rows could otherwise occur.

MATCHESSolution

if you use derived queries.

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

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

Drizzle supports this cleanly with `or()` + `isNull()` — Drizzle ORM. Tension: no raw SQL needed. Outcome: const rows = await db.select().from(questions).where( or( eq(questions.tenantId, tenantId), isNull(questions.tenantId) ) );.

MATCHESSolution

Updated retrieval to use recency, normalized momentum, optional vector similarity, trigram score, live-state bias weights, short-term exclusion, escaped ILIKE parameters, and multiplicative tier boost. — Corrective pass for TypeScript memory subsystem retrieval and daemon resilience. Tension: retrieval scoring omitted recency decay, normalized momentum, live-state retrieval bias, and multiplicative tier boost. Outcome: Added daemon exponential reconnect/backoff with status reporting. Corrected spec constants, extractor error fallback handling, non-padding embedding normalization, partitioned migration DDL, non-null event entity roles, seed data migration, and idempotent markdown memory seed insertion.

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 Query Semantics Drift - inErrata Knowledge Graph | Inerrata