Query with window function, hstore condition and outer join causes segmentation fault
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
Running the following SQL query always causes SEGFAULT in Citus 13.1.0/13.1.1 with PG17.5.
Tthis issue seems similar to #7705, but SEGFAULT with both of HSTORE condition and OUTER JOIN.
Testing environment:
* Rocky Linux 9.5
* Postgres 17.5 (src)
* citus 13.1.0, 13.1.1
* 1 coordinator, 3 workers
Reproduce queries:
```
CREATE SCHEMA segfault_reproduce;
SET search_path TO segfault_reproduce,"$user",public;
DROP TABLE IF EXISTS segfault_reproduce.users CASCADE;
CREATE TABLE segfault_reproduce.users (
tenant_id VARCHAR(10) NOT NULL,
session_id TEXT NOT NULL,
customer_id TEXT,
personal HSTORE
);
SELECT create_distributed_table('users', 'session_id');
SELECT truncate_local_data_after_distributing_table('users');
DROP TABLE IF EXISTS segfault_reproduce.user_sessions CASCADE;
CREATE TABLE segfault_reproduce.user_sessions (
tenant_id VARCHAR(10) NOT NULL,
ts TIMESTAMP NOT NULL,
session_id TEXT NOT NULL
);
SELECT create_distributed_table('user_sessions', 'session_id');
SELECT truncate_local_data_after_distributing_table('user_sessions');
--
-- With the window function and no hstore condition and a subquery including inner join
-- OK
--
SELECT
userlist.tenant_id,
COUNT(userlist.customer_id) OVER (PARTITION BY userlist.tenant_id) AS "all_count"
FROM
users userlist
INNER JOIN
(
SELECT
user_sessions.TENANT_ID,
user_sessions.SESSION_ID
FROM
user_sessions
INNER JOIN
users
ON (user_sessions.TENANT_ID=users.TENANT_ID
AND user_sessions.SESSION_ID=users.SESSION_ID)) search_subquery
ON (userlist.tenant_id=search_subquery.tenant_id
AND search_subquery.session_id=userlist.session_id)
WHERE userlist.tenant_id='1234567890'
GROUP BY
userlist.tenant_id,
userlist.customer_id;
--
-- With the window function and no hstore condition and a subquery including outer join
-- OK
--
SELECT
userlist.tenant_id,
COUNT(userlist.customer_id) OVER (PARTITION BY userlist.tenant_id) AS "all_count"
FROM
users userlist
INNER JOIN
(
SELECT
user_sessions.TENANT_ID,
user_sessions.SESSION_ID
FROM
user_sessions
LEFT OUTER JOIN
users
ON (user_sessions.TENANT_ID=users.TENANT_ID
AND user_sessions.SESSION_ID=users.SESSION_ID)) search_subquery
ON (userlist.tenant_id=search_subquery.tenant_id
AND search_subquery.session_id=userlist.session_id)
WHERE userlist.tenant_id='1234567890'
GROUP BY
userlist.tenant_id,
userlist.customer_id;
--
-- With the window function and a hstore condition and a subquery including inner join
-- OK
--
SELECT
userlist.tenant_id,
COUNT(userlist.customer_id) OVER (PARTITION BY userlist.tenant_id) AS "all_count"
FROM
users userlist
INNER JOIN
(
SELECT
user_sessions.TENANT_ID,
user_sessions.SESSION_ID
FROM
user_sessions
INNER JOIN
users
ON (user_sessions.TENANT_ID=users.TENANT_ID
AND user_sessions.SESSION_ID=users.SESSION_ID)
WHERE users.PERSONAL->'foo'='bar') search_subquery
ON (userlist.tenant_id=search_subquery.tenant_id
AND search_subquery.session_id=userlist.session_id)
WHERE userlist.tenant_id='1234567890'
GROUP BY
userlist.tenant_id,
userlist.customer_id;
--
-- With no window function and a hstore condition and a subquery including outer join
-- OK
--
SELECT
userlist.tenant_id,
COUNT(userlist.customer_id) AS "all_count"
FROM
users userlist
INNER JOIN
(
SELECT
user_sessions.TENANT_ID,
user_sessions.SESSION_ID
FROM
user_sessions
LEFT OUTER JOIN
users
ON (user_sessions.TENANT_ID=users.TENANT_ID
AND user_sessions.SESSION_ID=users.SESSION_ID)
WHERE users.PERSONAL->'foo'='bar') search_subquery
ON (userlist.tenant_id=search_subquery.tenant_id
AND search_subquery.session_id=userlist.session_id)
WHERE userlist.tenant_id='1234567890'
GROUP BY
userlist.tenant_id,
userlist.customer_id;
--
-- With the window function and a hstore condition and a subquery including outer join
-- Segmentation Fault
--
SELECT
userlist.tenant_id,
COUNT(userlist.customer_id) OVER (PARTITION BY userlist.tenant_id) AS "all_count"
FROM
users userlist
INNER JOIN
(
SELECT
user_sessions.TENANT_ID,
user_sessions.SESSION_ID
FROM
user_sessions
LEFT OUTER JOIN
users
ON (user_sessions.TENANT_ID=users.TENANT_ID
AND user_sessions.SESSION_ID=users.SESSION_ID)
WHERE users.PERSONAL->'foo'='bar') search_subquery
ON (userlist.tenant_id=search_subquery.tenant_id
AND search_subquery.session_id=userlist.session_id)
WHERE userlist.tenant_id='1234567890'
GROUP BY
userlist.tenant_id,
userlist.customer_id;
DROP SCHEMA IF EXISTS segfault_reproduce CASCADE;
```
Logs:
```
2025-11-07 14:10:09 JST [633279]: [181-1] user=,db=,app=,client= LOG: server process (PID 571678) was terminated by signal 11: Segmentation fault
2025-11-07 14:10:09 JST [633279]: [182-1] user=,db=,app=,client= DETAIL: Failed process was running: SELECT
userlist.tenant_id,
COUNT(userlist.customer_id) OVER (PARTITION BY userlist.tenant_id) AS "all_count"
FROM
users userlist
INNER JOIN
(
SELECT
user_sessions.TENANT_ID,
user_sessions.SESSION_ID
FROM
user_sessions
LEFT OUTER JOIN
users
ON (user_sessions.TENANT_ID=users.TENANT_ID
AND user_sessions.SESSION_ID=users.SESSION_ID)
WHERE users.PERSONAL->'foo'='bar') search_subquery
ON (userlist.tenant_id=search_subquery.tenant_id
AND search_subquery.session_id=userlist.session_id)
WHERE userlist.tenant_id='1234567890'
GROUP BY
userlist.tenant_id,
userlist.customer_id;
2025-11-07 14:10:09 JST [633279]: [183-1] user=,db=,app=,client= LOG: terminating any other active server processes
2025-11-07 14:10:09 JST [572369]: [1-1] user=[unknown],db=[unknown],app=[unknown],client=[local] LOG: connection received: host=[local]
2025-11-07 14:10:09 JST [572369]: [2-1] user=xdata,db=xdata_dev_db,app=[unknown],client=[local] FATAL: the database system is in recovery mode
2025-11-07 14:10:09 JST [633279]: [184-1] user=,db=,app=,client= LOG: all server processes terminated; reinitializing
````
Contributor guide
Assessment
This issue has not been assessed yet.