citusdata / citusdata/citus

Query with window function, hstore condition and outer join causes segmentation fault

Open
#8,327 7 comments 0 reactions 0 assignees View on GitHub
bug upstream
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.