| From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
|---|---|
| To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
| Cc: | tharakan(at)gmail(dot)com |
| Subject: | BUG #19916: UNION over hash-only and sort-only columns: could not devise a query plan |
| Date: | 2026-11-18 22:24:14 |
| Message-ID: | 19916-8097ca71b8aa4085@postgresql.org |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 19916
Logged by: Robins Tharakan
Email address: tharakan(at)gmail(dot)com
PostgreSQL version: Unsupported/Unknown
Operating system: Debian 13 (x86_64)
Description:
A UNION whose columns can't all be sorted and can't all be hashed fails
with an internal error:
ERROR: XX000: could not devise a query plan for the given query
LOCATION: set_cheapest, pathnode.c:279
tsvector has a btree opclass but no hash opclass, and aclitem has a
hash opclass but no btree one, so neither strategy works for the query
below. Up to 16 this was reported properly, and INTERSECT and EXCEPT
over the same columns still are, on every branch:
ERROR: 0A000: could not implement UNION
with the DETAIL "Some of the datatypes only support hashing, while
others only support sorting."
generate_union_paths() adds a hashed path only when every column can
be hashed, and a sorted one only when every column can be sorted, so
for these columns it adds no path at all, and set_cheapest() raises the
error. Up to 16, make_union_unique() called choose_hashed_setop(),
which raised "could not implement UNION" for this case. 12933dc6048
replaced make_union_unique() and dropped that check, while
generate_nonunion_paths() still makes it for INTERSECT and EXCEPT. At
12933dc6048 itself the query returns a row with no error; it fails from
c37267162e8, which made generate_union_paths() set groupList even when
the columns can't be sorted.
It reproduces on master through REL_17_STABLE. REL_16_STABLE raises
the 0A000 error. It's an elog(ERROR), not an assertion: a release
build (no cassert, -O2) fails the same way.
SQL
===
SELECT NULL::tsvector, NULL::aclitem[] UNION SELECT NULL, NULL;
-- the same with xid (hash only) and tsvector (btree only):
SELECT '123'::xid, ''::tsvector UNION SELECT '123'::xid, ''::tsvector;
Commit
======
Started: c37267162e8 (2024-05-21)
Affects: REL_17_STABLE 162cf60d925 (2026-11-18)
REL_18_STABLE 7c19a14bbef (2026-11-18)
REL_19_STABLE 19aa5713be2 (2026-11-18)
Master: 8c15c2cb5c4 (2026-11-18), the newest commit tested
Built with --enable-cassert --enable-debug (gcc 14.2.0, Debian 13,
x86_64).
Error Log
=========
ERROR: XX000: could not devise a query plan for the given query
LOCATION: set_cheapest, pathnode.c:279
STATEMENT: SELECT NULL::tsvector, NULL::aclitem[] UNION SELECT NULL, NULL
Found using SQLsmith, SQLReduce and Claude.
-
Robins Tharakan
Databricks
| From | Date | Subject | |
|---|---|---|---|
| Next Message | PG Bug reporting form | 2026-11-19 00:37:24 | BUG #19917: nbtree skip scan leaks one copy of a pass-by-reference skip array element per rescan |
| Previous Message | David Rowley | 2026-11-18 21:29:27 | Re: BUG #19915: Qual over function-wrapped grouping column pushed past DISTINCT ON returns wrong rows |