Home Search Pricing Get inErrata About

Sign in Sign up

Graph/sql-clause-semantics-drift

Pattern

SQL Query Semantics Drift

sql-clause-semantics-drift

SQL statements produce unexpected result sets or syntax behavior because clause semantics differ across constructs (e.g., ROLLUP emitting only total rows, UNION DISTINCT interactions, NOT IN vs subqueries, and quoting/casting differences).

Node Details

Public graph metadata

Updated7/1/2026

Connections

30 linked nodes

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`.

the data will Suspend and get fetched on the server but never be hydrated to the client — If you do forget to prefetch a query when you are using useSuspenseQuery. Tension: the consequences will depend on the framework you are using.

the query does not invalidate after a successful transaction. Tension: unless I manually invalidate it using the React Query DevTools or leave Chrome and come back. Outcome: which then triggers the invalidation (update).

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.

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.

tx.Begin + tx.Commit + tx.Rollback + set_config + core query — on each query you must write something like `set_config('')` or `SET LOCAL` first. Tension: It significantly increases amount of code required on a backend. Outcome: just query.

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

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.

in which the query above is nested. Tension: why is it a must to alias in the second query and not the first ?

I get no results — if I turn around and select from this table using this value such as. Tension: The table column seems to have a 6 millisecond precision that the select statement does not reflect. Outcome: It seems like no matter what I do I'm stuck with a precision of 3 millisecond decimal places when selecting.

sequence [ID_SEQ] defined inconsistent increment-size; found [1] but expecting [100] — Quarkus postgres and liquibase. Tension: those values are inconsistent. Outcome: You have to annotate fields(ex: id) with `sequenceGenerator` not the Entity or class.

Outcome: I also tried a few variations of the same query (got same error).

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

I see the expected results using above query. — Now I want to remove the entries from above result which are in table2. Tension: Is there a more efficient of achieving the same result? and are there any cases that I could be missing?

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.

Implementing cursor-based pagination in Knex/Postgres using a tuple comparison like `(timestamp, id) > (cursor.timestamp, cursor.id)` fails and returns no rows. The attempted Knex query with two separate `where` clauses behaves like an `AND` comparison rather than the intended tuple `>` semantics.

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

String predicate handling still allows arbitrary SQL and information disclosure

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.

When I change the formula to use LEFT, concatenation of three columns and a fallback value — Apache POI 5.2.5. Tension: the evaluator returns an error. Outcome: result.getErrorValue() == 15 (#VALUE! error).

org.hibernate.query.QueryTypeMismatchException: Specified result type — I moved a Hibrenate implementation to another project. From com.mvc.data to com.mvc.frontend. Tension: Notice that it's the same type. Outcome: So the problem was that I was using spring-boot-devtools which has a restart function.

I am not able to get certain results from Java client side. — uses java HLRC client fetching results from Elasticsearch cluster. Tension: when i am firing the underlying query of Java client in kibana devtools I am getting valid results. Outcome: This issue is just happening for a particular [KNOWN error — represented by RootCause "Spring Data JPA does not automatically map native query result columns to Java r" (anchor id=866c5bc6-e8c4-4b07-825c-daa708d304ec)].

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

It works, sort of, but the result adds an extra quote to my string. — I need to annotate a value that is saved in a json field in the same model. Tension: I need it to be cast to text/char in the query because a subsequent search will only work on text/char (trigram similarity).

Referencing path in RETURN (e.g., last(relationships(path)).someProperty) causes 'Variable path not defined' error — after a WITH clause that projects path properties into aliases. Tension: the path variable itself is no longer in scope for the RETURN clause. Outcome: breaking all graph traversal queries.

[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