edespino opened a new issue, #1399:
URL: https://github.com/apache/cloudberry/issues/1399
### Apache Cloudberry version
Cloudberry 3.0.0-devel+dev.2141.g468b1e67dc8
### What happened
Cloudberry Database crashes with assertion failure when executing a query
that combines a Common Table Expression (CTE) with `SELECT INTO TEMPORARY
TABLE`.
## Environment
- **Database:** Cloudberry 3.0.0-devel+dev.2141.g468b1e67dc8
- **PostgreSQL Base:** PostgreSQL 14.4
- **Platform:** Rocky Linux 9
- **Context:** Discovered during PostGIS regression testing
## Bug Details
### Assertion Failure
```
FATAL: Unexpected internal error (assert.c:48)
DETAIL: FailedAssertion("pstate->p_ctenamespace == NIL", File:
"parse_cte.c", Line: 117)
```
### Stack Trace
```
#0 __pthread_kill_implementation () from /lib64/libc.so.6
#1 raise () from /lib64/libc.so.6
#2 abort () from /lib64/libc.so.6
#3 ExceptionalCondition (conditionName="pstate->p_ctenamespace == NIL",
errorType="FailedAssertion", fileName="parse_cte.c", lineNumber=117) at
assert.c:48
#4 transformWithClause (pstate=0x85255e8, withClause=0x8525080) at
parse_cte.c:117
#5 transformSelectStmt (pstate=0x85255e8, stmt=0x8525440) at analyze.c:1409
#6 transformStmt (pstate=0x85255e8, parseTree=0x8525440) at analyze.c:423
#7 transformCreateTableAsStmt (pstate=0x85255e8, stmt=0x8525700) at
analyze.c:3398
#8 transformStmt (pstate=0x85255e8, parseTree=0x8525700) at analyze.c:452
#9 transformOptionalSelectInto (pstate=0x85255e8, parseTree=0x8525700) at
analyze.c:365
#10 transformTopLevelStmt (pstate=0x85255e8, parseTree=0x8525558) at
analyze.c:267
#11 parse_analyze () at analyze.c:176
```
### Root Cause Analysis
The crash occurs in `transformWithClause()` at parse_cte.c:117, where the
code asserts that `pstate->p_ctenamespace == NIL`. This assertion fails when:
1. A query contains a CTE (WITH clause)
2. The main query uses `SELECT INTO TEMPORARY TABLE` syntax
3. The parser transforms the statement through `transformCreateTableAsStmt()`
The issue appears to be that when `SELECT INTO` is transformed into a
`CREATE TABLE AS` statement, the parser state's CTE namespace is not being
properly initialized or cleaned up before calling `transformWithClause()` on
the inner SELECT statement.
## Minimal Reproduction Case
```sql
-- Create test table
CREATE TABLE test_table (
id INTEGER,
name TEXT,
data JSONB
) DISTRIBUTED BY (id);
INSERT INTO test_table VALUES
(1, 'test1', '{"value": 100}'),
(2, 'test2', '{"value": 200}'),
(3, 'test3', '{"value": 300}');
-- This query triggers the crash
WITH cte_data AS (
SELECT id, name, data
FROM test_table
WHERE id > 0
)
SELECT * INTO TEMPORARY TABLE temp_result
FROM cte_data;
```
## Expected Behavior
The query should execute successfully, creating a temporary table
`temp_result` containing the rows from the CTE.
## Actual Behavior
The database backend crashes with an assertion failure, terminating the
connection.
## Impact
- **Severity:** High - causes backend crash and connection termination
- **Scope:** Affects any query combining CTEs with `SELECT INTO TEMPORARY
TABLE` syntax
- **Data Loss:** No data loss, but active transactions are aborted
- **Discovered In:** PostGIS regression test suite (test ticket #5139)
## Additional Notes
This bug is **not related to PostGIS**. It was discovered during PostGIS
regression testing but is a core Cloudberry query parser issue. The bug affects
any query with this pattern, regardless of the data types or extensions
involved.
The assertion suggests this may be a regression introduced during
Greenplum/Cloudberry development, as the code expects CTE namespace state to be
clean at a certain point in query transformation.
## Suggested Fix Areas
1. **parse_cte.c:117** - Review the assertion condition and CTE namespace
lifecycle
2. **analyze.c:3398** - `transformCreateTableAsStmt()` may need to
initialize parser state
3. **analyze.c:365** - `transformOptionalSelectInto()` may need to preserve
CTE context properly
The fix likely involves ensuring that when `SELECT INTO` is transformed into
`CREATE TABLE AS`, the CTE context is properly propagated or the parser state
is correctly initialized before processing the WITH clause.
## Test Case for Regression Testing
Once fixed, add this test case to Cloudberry's regression test suite to
prevent recurrence:
```sql
-- Test CTE with SELECT INTO TEMPORARY TABLE
CREATE TABLE cte_select_into_test (id int, val text);
INSERT INTO cte_select_into_test VALUES (1, 'a'), (2, 'b'), (3, 'c');
WITH filtered AS (SELECT * FROM cte_select_into_test WHERE id > 1)
SELECT * INTO TEMPORARY TABLE cte_select_into_result FROM filtered;
SELECT * FROM cte_select_into_result ORDER BY id;n
-- Expected: 2 rows (id=2, id=3)
DROP TABLE cte_select_into_test;
DROP TABLE cte_select_into_result;
```
### What you think should happen instead
_No response_
### How to reproduce
-- Create test table
CREATE TABLE test_table (
id INTEGER,
name TEXT,
data JSONB
) DISTRIBUTED BY (id);
INSERT INTO test_table VALUES
(1, 'test1', '{"value": 100}'),
(2, 'test2', '{"value": 200}'),
(3, 'test3', '{"value": 300}');
-- This query triggers the crash
WITH cte_data AS (
SELECT id, name, data
FROM test_table
WHERE id > 0
)
SELECT * INTO TEMPORARY TABLE temp_result
FROM cte_data;
### Operating System
Rocky Linux 9
### Anything else
_No response_
### Are you willing to submit PR?
- [ ] Yes, I am willing to submit a PR!
### Code of Conduct
- [x] I agree to follow this project's [Code of
Conduct](https://github.com/apache/cloudberry/blob/main/CODE_OF_CONDUCT.md).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
