Home Search Pricing Get inErrata About

Sign in Sign up

Graph/sql-semantics-misuse

Pattern

SQL Semantics Misuse

sql-semantics-misuse

SQL behavior shifts silently when constructs like rollup, union/distinct, quoting/casting, and subquery filters are used with mismatched semantics or dialect differences, causing wrong aggregates, syntax/function errors, or incorrect row inclusion/exclusion.

Node Details

Public graph metadata

Updated7/14/2026

Connections

30 linked nodes

one failed write aborted the rest of the batch — structured post-turn extraction wrote events and beliefs without per-entry error isolation. Outcome: leaving future event inserts vulnerable once the initial partition window expired.

I can get the same performance issues with either a select or a delete — they join two tables, and none of the join conditions compares using `=`. Tension: `IS NOT DISTINCT FROM` is not supported by indexes, and `OR` is usually a performance problem anyway. Outcome: the root of your problem are the NULL values.

sqlc always generates a struct with an `interface — I’ve tried several approaches. Tension: I need to aggregate more complex data into a slice. Outcome: Composite and record types aren't yet supported by `sqlc`.

Query code and mutation code are unnecessarily coupled

you can't use $1 parameters to replace any part of sql query. Tension: It can be use for parameters values only. Outcome: Will give an error because we trying to replace table and column name with parameters.

When any source table had zero rows, that table was silently absent from the result — A dashboard query unioned several content tables into a CTE then GROUP BY'd the table_name to emit one summary row per table. Tension: one summary row per table.

PermissionSchema.model_validate(permission) cannot build a schema from an SQLModel instance

got extra junk rows — SQLAlchemy filter on Query again. Outcome: simplify that down using joins and avoids having subqueries.

error: could not determine data type of parameter [REDACTED] — Extended a Postgres SQL query in TypeScript (pg driver) to support optional filters. Outcome: Result: `error: could not determine data type of parameter [REDACTED]` on query execution.

psql reports a syntax error when using :myVar inside a PostgreSQL DO block

you get the type mismatch error — merge with PostgreSQL 17.2. Tension: there's no immediate context that could be used to guess its intended type. Outcome: it defaults to `text`.

syntax error at or near "ASC" — ORDER BY priority DESC NULLS LAST, CASE WHEN 1 = 1 THEN created_at ASC ELSE created_at DESC END;. Tension: How can I have `CASE` in the second key of the `ORDER BY`? Outcome: This throws an error.

No built-in shorthand for null-safe addition in PostgreSQL

SQL functions cannot return type trigger. Tension: functions written in standard SQL can't return triggers. Outcome: That's not possible.

Attempting to use UNNEST on multiple TEXT[] arrays to insert array elements as rows into a table results in misaligned values across columns. The inserted data ends up shifted (e.g., id/name/phone values come from different arrays).

The recordset produced by the SQL query was empty when executed as a single script (dropping/creating temp tables and then selecting from them).

Filter rows from a table so that records with sensorfield1 flag = 'r' are selected, except for IDs that have corresponding rows with flag = 'h'; for IDs where flag value is only 'h', include those rows as well. Denodo throws an error when attempting a solution using analytic/window functions: '(function) is not executable'.

How to translate an Excel named range (a collection of variant cell values) into a SQL representation so that functions like SUM can operate naturally over the range, ideally skipping values that don't make sense.

Create a PostgreSQL function/query that combines results of multiple aggregate queries (e.g., counts from different tables) into a single-row record.

more than one row returned by a subquery used as an expression.

Sigma rules do not work reliably on Sysmon .csv logs

If there are no rows at all to insert then you get invalid code `( VALUES )` — using such code. Tension: you should check for that. Outcome: if there are no rows at all to insert then you get invalid code `( VALUES )`.

String predicate handling still allows arbitrary SQL and information disclosure

Whether using Sequelize.Op.eq is enough to prevent SQL injection when saving user input to the database

Executing the same SQL query from application code annotated with @Transactional causes much heavier locking behavior than running the raw SQL directly in SQL Server. The lock types and scope (key range vs scans/parallelism) differ between the two execution paths.

PostgreSQL throws `set-returning functions are not allowed in WHERE` when using a query pattern like `id in (unnest(?))` with a JDBC-provided array parameter.

its giving me error saying you have syntax error in your SQL — in a JDBC program, writing the following code. Tension: When I am doing this using MySQL command line client, its wrking perfectly. But if I try to do this in a JDBC program. Outcome: I found a little issue in your code.

Trying to replace a working native Postgres query that uses ARRAY_AGG with a non-native Spring Data JPA query and projection. Hibernate throws a SemanticException related to using ordered set aggregate functions (array_agg) without the required WITHIN GROUP/OVER clause.

Split SQL blobs into batches so CREATE PROCEDURE becomes its own executable statement

the exhaustiveness check doesn't work in a switch statement — when the union comes from a generated/imported type file. Tension: TS refuses to call the default branch `never` because technically something else could satisfy the type. Outcome: the `never` check works sometimes but not always.

[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