Thread
-
BUG #19531: Inconsistent Error Messages for the Same SQL Query
PG Bug reporting form <noreply@postgresql.org> — 2026-06-24T07:20:28Z
The following bug has been logged on the website: Bug reference: 19531 Logged by: Wang Hao Email address: 1804981203@qq.com PostgreSQL version: 18.4 Operating system: Ubuntu22.04 Description: Inconsistent Error Messages for the Same SQL Query in PostgreSQL Environment： - PostgreSQL version: 18.4 - Operating System: Ubuntu 22.04 Context： My application relies on the error messages returned by SQL statement execution to perform subsequent operations. During development, I discovered that the same SQL query can return different error messages . I'm not sure if this is a bug, but it does affect my application's ability to reliably handle errors. Steps to Reproduce: Example 1: Triggering different errors by controlling the execution plan test=# CREATE TABLE t (c1 text DEFAULT '', c2 smallint DEFAULT 0); CREATE TABLE test=# INSERT INTO t (c1, c2) VALUES ('jkwdot0re', 0); INSERT 0 1 test=# INSERT INTO t (c1, c2) VALUES ('', -1000); INSERT 0 1 test=# SELECT c2 / c2 AS e0, c2 * c2 AS e1 FROM t GROUP BY c1, c2; ERROR: division by zero test=# explain(verbose) SELECT c2 / c2 AS e0, c2 * c2 AS e1 FROM t GROUP BY c1, c2; QUERY PLAN ------------------------------------------------------------------- HashAggregate (cost=29.65..32.65 rows=200 width=38) Output: (c2 / c2), (c2 * c2), c1, c2 Group Key: t.c1, t.c2 -> Seq Scan on public.t (cost=0.00..23.10 rows=1310 width=34) Output: c1, c2 (5 rows) test=# SET enable_hashagg = off; SET test=# SELECT c2 / c2 AS e0, c2 * c2 AS e1 FROM t GROUP BY c1, c2; ERROR: smallint out of range test=# explain(verbose) SELECT c2 / c2 AS e0, c2 * c2 AS e1 FROM t GROUP BY c1, c2; QUERY PLAN ------------------------------------------------------------------------- Group (cost=90.93..101.75 rows=200 width=38) Output: (c2 / c2), (c2 * c2), c1, c2 Group Key: t.c1, t.c2 -> Sort (cost=90.93..94.20 rows=1310 width=34) Output: c1, c2 Sort Key: t.c1, t.c2 -> Seq Scan on public.t (cost=0.00..23.10 rows=1310 width=34) Output: c1, c2 (8 rows) Example 2:Triggering different errors due to statistics changes test=# CREATE TABLE t (c1 text DEFAULT '', c2 smallint DEFAULT 0); CREATE TABLE test=# INSERT INTO t (c1, c2) VALUES ('jkwdot0re', 0); INSERT 0 1 test=# INSERT INTO t (c1, c2) VALUES ('', -1000); INSERT 0 1 test=# SELECT c2 / c2 AS e0, c2 * c2 AS e1 FROM t GROUP BY c1, c2; ERROR: division by zero test=# explain(verbose) SELECT c2 / c2 AS e0, c2 * c2 AS e1 FROM t GROUP BY c1, c2; QUERY PLAN ------------------------------------------------------------------- HashAggregate (cost=29.65..32.65 rows=200 width=38) Output: (c2 / c2), (c2 * c2), c1, c2 Group Key: t.c1, t.c2 -> Seq Scan on public.t (cost=0.00..23.10 rows=1310 width=34) Output: c1, c2 (5 rows) test=# ANALYZE t; ANALYZE test=# SELECT c2 / c2 AS e0, c2 * c2 AS e1 FROM t GROUP BY c1, c2; ERROR: smallint out of range test=# explain(verbose) SELECT c2 / c2 AS e0, c2 * c2 AS e1 FROM t GROUP BY c1, c2; QUERY PLAN -------------------------------------------------------------------- Group (cost=1.03..1.05 rows=2 width=11) Output: (c2 / c2), (c2 * c2), c1, c2 Group Key: t.c1, t.c2 -> Sort (cost=1.03..1.03 rows=2 width=7) Output: c1, c2 Sort Key: t.c1, t.c2 -> Seq Scan on public.t (cost=0.00..1.02 rows=2 width=7) Output: c1, c2 (8 rows) Observation: The same SQL query SELECT c2 / c2 AS e0, c2 * c2 AS e1 FROM t GROUP BY c1, c2; executed against the same data returns two different error messages: - ERROR: division by zero - ERROR: smallint out of range The query contains two expressions that can each trigger an error on the same data: - c2 / c2: division by zero when c2 = 0 - c2 * c2: smallint overflow when c2 = -1000 (since (-1000)² = 1,000,000 exceeds the smallint range) In Example 1, changing enable_hashagg switches the execution plan from HashAggregate to Group.In Example 2, running ANALYZE updates table statistics I'm not certain whether this qualifies as a bug, but it does create a real problem for applications that depend on consistent error reporting from the database.Thanks for reading my report! -
Re: BUG #19531: Inconsistent Error Messages for the Same SQL Query
Laurenz Albe <laurenz.albe@cybertec.at> — 2026-06-24T08:19:52Z
On Wed, 2026-06-24 at 07:20 +0000, PG Bug reporting form wrote: > Environment： > - PostgreSQL version: 18.4 > - Operating System: Ubuntu 22.04 > > Context： > My application relies on the error messages returned by SQL statement > execution to perform subsequent operations. During development, I discovered > that the same SQL query can return different error messages . I'm not sure > if this is a bug, but it does affect my application's ability to reliably > handle errors. I would say that this is not a bug. Your statement contains two errors and it depends on the execution plan which of the two errors you are hitting first. If your program reacts differently based on the exact error, both reactions would be correct, because both errors are present in the query. I would compare the situation to an INSERT statement into a table with two constraints. Depending on which data are present in the database, you could hit a constraint violation error for either constraint. Yours, Laurenz Albe
-
Re: BUG #19531: Inconsistent Error Messages for the Same SQL Query
Francisco Olarte <folarte@peoplecall.com> — 2026-06-24T08:20:59Z
On Wed, 24 Jun 2026 at 10:00, PG Bug reporting form <noreply@postgresql.org> wrote: ... > Inconsistent Error Messages for the Same SQL Query in PostgreSQL ... > Context： > My application relies on the error messages returned by SQL statement > execution to perform subsequent operations. During development, I discovered > that the same SQL query can return different error messages . I'm not sure > if this is a bug, but it does affect my application's ability to reliably > handle errors. From your posted code only, it seems error MESSAGES are consistent, it is ERRORS that are different. And given you are changing the context via set, this is expected. If I do "select a/a, a*a from t" and I have just a zero row, I get division by zero, -1000, in smallint, I get overflow, 1 i get 1,1. I can get the three results from the same 1 row table varying the context by "update set a=" before the select. Your example is just doing the same thing on a more convoluted way. ... > Observation: > The same SQL query SELECT c2 / c2 AS e0, c2 * c2 AS e1 FROM t GROUP BY c1, > c2; executed against the same data returns two different error messages: ... two different errors.. > - ERROR: division by zero > - ERROR: smallint out of range ... > I'm not certain whether this qualifies as a bug, but it does create a real > problem for applications that depend on consistent error reporting from the > database.Thanks for reading my report! Wiser people may say other thing, but the reporting is consistent. The errors are consistent too, same options, same error always. The behaviour you have shown is different results ( loosely defining "resultset or raised error" as result ) for the same query in different contexts. IMO this is normal. It is like writing a parameterless function which raises A if seqscan is enabled ( reading the setting directly ) or B if not and complaining that it does not always raise the same error when calling in the same way. Francisco Olarte.