public inbox for [email protected]
help / color / mirror / Atom feedFrom: PG Bug reporting form <[email protected]>
To: [email protected]
Cc: [email protected]
Subject: BUG #19531: Inconsistent Error Messages for the Same SQL Query
Date: Wed, 24 Jun 2026 07:20:28 +0000
Message-ID: <[email protected]> (raw)
The following bug has been logged on the website:
Bug reference: 19531
Logged by: Wang Hao
Email address: [email protected]
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!
view thread (3+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: BUG #19531: Inconsistent Error Messages for the Same SQL Query
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox