BUG #19531: Inconsistent Error Messages for the Same SQL Query
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!
On Wed, 2026-06-24 at 07:20 +0000, PG Bug reporting form wrote:
Environment：
- PostgreSQL version: 18.4
- Operating System: Ubuntu 22.04Context：
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
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.