Home Search Pricing Get inErrata About

Sign in Sign up

Graph/query-shape-misalignment

AntiPattern

Query Shape Misalignment

query-shape-misalignment

Database query structure and parameter access often mismatch the engine’s expectations—index predicates, rollup/grand-total grouping rules, derived-table aliasing, and UNION distinct behavior diverge from what the developer assumes, yielding incorrect results or unusable plans.

Node Details

Public graph metadata

Updated8/4/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

Define a TypeAlias with Annotated[..., Field(union_mode='left_to_right')] and use it instead of a bare Union

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

If you run a straight `SELECT/UPDATE/DELETE` query, then the profiling information is written to the file. Tension: If you run an `EXPLAIN` or `EXPLAIN ANALYZE` query, the profiling information will not be written to the desired file. Outcome: and the result is the result of the query.

MATCHESSolution

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

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

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

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

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

You can separate them by different executing path — assume you are using liquibase script. Tension: The SQL is different between these 2 DBMS. Outcome: there is similar way to separate them.

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

To determine if an index is unused or not — query the view on the primary and all standby servers. Tension: the transactional and analytical queries are very different. Outcome: query the view on the primary and all standby servers.

MATCHESSolution

Normalize the schema by moving actionServiceIds out of the array into a separate junction table (e.g., permission_services with one row per id/serviceId) so indexes can support filtering and distinct counting; then query distinct counts over the normalized table instead of unnesting the array.

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

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

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

you get the same exception that you would get if the parser encountered unsupported syntax — After reading the files, do some heuristics where you remove any `create function` or similar statement. Tension: I cannot modify these DDL files. Outcome: A pragmatic solution here is to match `create( or replace)? function.*?\\$\\$.*?\\$\\$.*?;` or similar.

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

Fix the entity-to-table mapping so the column names exactly match the database schema (e.g., rename the column or properly escape/quote it and adjust the `@Column(name=...)` mapping) so JPA generates an UPDATE with the correct parameter bindings.

MATCHESSolution

After debugging the QueryRunner code — PreparedStatement.getParameterMetaData. Tension: something in your client API (not Oracle) is dropping the first letter of each word. Outcome: have found out that the error happens here (↓) in `PreparedStatement.getParameterMetaData`.

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

the relationship types not matching your MERGE patterns — If GDS IS available. Tension: Using `UNDIRECTED` orientation is critical for PageRank on knowledge graphs. Outcome: Using `UNDIRECTED` orientation is critical for PageRank on knowledge graphs.

MATCHESSolution

What relationships actually exist? — MATCH ()-[r]->() RETURN type(r), count(r) ORDER BY count(r) DESC. Tension: What your projection expects vs what exists. Outcome: check your projection.

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