Greenplum Database PANIC: Server Crash on CTE Queries Using ORCA Optimizer (Implicit Casts or IS NULL on Numeric Columns)

Products

• VMware Tanzu Data Suite
• VMware Tanzu Greenplum
• VMware Tanzu Greenplum / Gemfire

Issue/Introduction

Customers may experience a server crash (SIGSEGV / PANIC) when executing queries that use Common Table Expressions (CTEs) with the ORCA optimizer enabled (optimizer=on).

The crash typically occurs during the optimization phase and appears in the database logs as:

PANIC","XX000","Unexpected internal error: Master process received signal SIGSEGV"

Example stack trace from the database logs:

"PANIC","XX000","Unexpected internal error: Master process received signal SIGSEGV",,,,,,,0,,,,"

1    0x7f73c0a5dd10 libpthread.so.0 <symbol not found> + 0xc0a5dd10
2    0xfb8626 postgres _ZN5gpopt23CExpressionPreprocessor32RemoveRedundantCTEConsumersPredsEPN4gpos11CMemoryPoolEPNS_11CExpressionEPKNS1_8CHashMapIjNS1_16CDynamicPtrArrayIS4_XadL_ZNS1_14CleanupReleaseIS4_EEvPT_EEEEXadL_ZNS1_9HashValueIjEEjPKS9_EEXadL_ZNS1_6EqualsIjEEbSE_SE_EEXadL_ZNS1_13CleanupDeleteIjEEvSA_EEXadL_ZNS8_ISB_EEvSA_EEEERKSt3setIjSt4lessIjENS1_19MemoryPoolAllocatorIjEEE (CExpressionPreprocessor.cpp:2412)
3    0xfb83a2 postgres _ZN5gpopt23CExpressionPreprocessor32RemoveRedundantCTEConsumersPredsEPN4gpos11CMemoryPoolEPNS_11CExpressionEPKNS1_8CHashMapIjNS1_16CDynamicPtrArrayIS4_XadL_ZNS1_14CleanupReleaseIS4_EEvPT_EEEEXadL_ZNS1_9HashValueIjEEjPKS9_EEXadL_ZNS1_6EqualsIjEEbSE_SE_EEXadL_ZNS1_13CleanupDeleteIjEEvSA_EEXadL_ZNS8_ISB_EEvSA_EEEERKSt3setIjSt4lessIjENS1_19MemoryPoolAllocatorIjEEE (discriminator 2)
4    0xfb83a2 postgres _ZN5gpopt23CExpressionPreprocessor32RemoveRedundantCTEConsumersPredsEPN4gpos11CMemoryPoolEPNS_11CExpressionEPKNS1_8CHashMapIjNS1_16CDynamicPtrArrayIS4_XadL_ZNS1_14CleanupReleaseIS4_EEvPT_EEEEXadL_ZNS1_9HashValueIjEEjPKS9_EEXadL_ZNS1_6EqualsIjEEbSE_SE_EEXadL_ZNS1_13CleanupDeleteIjEEvSA_EEXadL_ZNS8_ISB_EEvSA_EEEERKSt3setIjSt4lessIjENS1_19MemoryPoolAllocatorIjEEE (discriminator 2)
5    0xfb83a2 postgres _ZN5gpopt23CExpressionPreprocessor32RemoveRedundantCTEConsumersPredsEPN4gpos11CMemoryPoolEPNS_11CExpressionEPKNS1_8CHashMapIjNS1_16CDynamicPtrArrayIS4_XadL_ZNS1_14CleanupReleaseIS4_EEvPT_EEEEXadL_ZNS1_9HashValueIjEEjPKS9_EEXadL_ZNS1_6EqualsIjEEbSE_SE_EEXadL_ZNS1_13CleanupDeleteIjEEvSA_EEXadL_ZNS8_ISB_EEvSA_EEEERKSt3setIjSt4lessIjENS1_19MemoryPoolAllocatorIjEEE (discriminator 2)
6    0xfb83a2 postgres _ZN5gpopt23CExpressionPreprocessor32RemoveRedundantCTEConsumersPredsEPN4gpos11CMemoryPoolEPNS_11CExpressionEPKNS1_8CHashMapIjNS1_16CDynamicPtrArrayIS4_XadL_ZNS1_14CleanupReleaseIS4_EEvPT_EEEEXadL_ZNS1_9HashValueIjEEjPKS9_EEXadL_ZNS1_6EqualsIjEEbSE_SE_EEXadL_ZNS1_13CleanupDeleteIjEEvSA_EEXadL_ZNS8_ISB_EEvSA_EEEERKSt3setIjSt4lessIjENS1_19MemoryPoolAllocatorIjEEE (discriminator 2)
7    0xfb83a2 postgres _ZN5gpopt23CExpressionPreprocessor32RemoveRedundantCTEConsumersPredsEPN4gpos11CMemoryPoolEPNS_11CExpressionEPKNS1_8CHashMapIjNS1_16CDynamicPtrArrayIS4_XadL_ZNS1_14CleanupReleaseIS4_EEvPT_EEEEXadL_ZNS1_9HashValueIjEEjPKS9_EEXadL_ZNS1_6EqualsIjEEbSE_SE_EEXadL_ZNS1_13CleanupDeleteIjEEvSA_EEXadL_ZNS8_ISB_EEvSA_EEEERKSt3setIjSt4lessIjENS1_19MemoryPoolAllocatorIjEEE (discriminator 2)
8    0xfb83a2 postgres _ZN5gpopt23CExpressionPreprocessor32RemoveRedundantCTEConsumersPredsEPN4gpos11CMemoryPoolEPNS_11CExpressionEPKNS1_8CHashMapIjNS1_16CDynamicPtrArrayIS4_XadL_ZNS1_14CleanupReleaseIS4_EEvPT_EEEEXadL_ZNS1_9HashValueIjEEjPKS9_EEXadL_ZNS1_6EqualsIjEEbSE_SE_EEXadL_ZNS1_13CleanupDeleteIjEEvSA_EEXadL_ZNS8_ISB_EEvSA_EEEERKSt3setIjSt4lessIjENS1_19MemoryPoolAllocatorIjEEE (discriminator 2)
9    0xfb83a2 postgres _ZN5gpopt23CExpressionPreprocessor32RemoveRedundantCTEConsumersPredsEPN4gpos11CMemoryPoolEPNS_11CExpressionEPKNS1_8CHashMapIjNS1_16CDynamicPtrArrayIS4_XadL_ZNS1_14CleanupReleaseIS4_EEvPT_EEEEXadL_ZNS1_9HashValueIjEEjPKS9_EEXadL_ZNS1_6EqualsIjEEbSE_SE_EEXadL_ZNS1_13CleanupDeleteIjEEvSA_EEXadL_ZNS8_ISB_EEvSA_EEEERKSt3setIjSt4lessIjENS1_19MemoryPoolAllocatorIjEEE (discriminator 2)
10   0xfb83a2 postgres _ZN5gpopt23CExpressionPreprocessor32RemoveRedundantCTEConsumersPredsEPN4gpos11CMemoryPoolEPNS_11CExpressionEPKNS1_8CHashMapIjNS1_16CDynamicPtrArrayIS4_XadL_ZNS1_14CleanupReleaseIS4_EEvPT_EEEEXadL_ZNS1_9HashValueIjEEjPKS9_EEXadL_ZNS1_6EqualsIjEEbSE_SE_EEXadL_ZNS1_13CleanupDeleteIjEEvSA_EEXadL_ZNS8_ISB_EEvSA_EEEERKSt3setIjSt4lessIjENS1_19MemoryPoolAllocatorIjEEE (discriminator 2)
11   0xfb83a2 postgres _ZN5gpopt23CExpressionPreprocessor32RemoveRedundantCTEConsumersPredsEPN4gpos11CMemoryPoolEPNS_11CExpressionEPKNS1_8CHashMapIjNS1_16CDynamicPtrArrayIS4_XadL_ZNS1_14CleanupReleaseIS4_EEvPT_EEEEXadL_ZNS1_9HashValueIjEEjPKS9_EEXadL_ZNS1_6EqualsIjEEbSE_SE_EEXadL_ZNS1_13CleanupDeleteIjEEvSA_EEXadL_ZNS8_ISB_EEvSA_EEEERKSt3setIjSt4lessIjENS1_19MemoryPoolAllocatorIjEEE (discriminator 2)
12   0xfb83a2 postgres _ZN5gpopt23CExpressionPreprocessor32RemoveRedundantCTEConsumersPredsEPN4gpos11CMemoryPoolEPNS_11CExpressionEPKNS1_8CHashMapIjNS1_16CDynamicPtrArrayIS4_XadL_ZNS1_14CleanupReleaseIS4_EEvPT_EEEEXadL_ZNS1_9HashValueIjEEjPKS9_EEXadL_ZNS1_6EqualsIjEEbSE_SE_EEXadL_ZNS1_13CleanupDeleteIjEEvSA_EEXadL_ZNS8_ISB_EEvSA_EEEERKSt3setIjSt4lessIjENS1_19MemoryPoolAllocatorIjEEE (discriminator 2)
13   0xfb83a2 postgres _ZN5gpopt23CExpressionPreprocessor32RemoveRedundantCTEConsumersPredsEPN4gpos11CMemoryPoolEPNS_11CExpressionEPKNS1_8CHashMapIjNS1_16CDynamicPtrArrayIS4_XadL_ZNS1_14CleanupReleaseIS4_EEvPT_EEEEXadL_ZNS1_9HashValueIjEEjPKS9_EEXadL_ZNS1_6EqualsIjEEbSE_SE_EEXadL_ZNS1_13CleanupDeleteIjEEvSA_EEXadL_ZNS8_ISB_EEvSA_EEEERKSt3setIjSt4lessIjENS1_19MemoryPoolAllocatorIjEEE (discriminator 2)
14   0xfb895b postgres _ZN5gpopt23CExpressionPreprocessor22AddPredsToCTEProducersEPN4gpos11CMemoryPoolEPNS_11CExpressionE (CExpressionPreprocessor.cpp:2517)
15   0xf68b93 postgres _ZN5gpopt13CQueryContextC1EPN4gpos11CMemoryPoolEPNS_11CExpressionEPNS_13CReqdPropPlanEPNS1_16CDynamicPtrArrayINS_7CColRefEXadL_ZNS1_11CleanupNULLIS9_EEvPT_EEEEPNS8_IN4gpmd7CMDNameEXadL_ZNS1_13CleanupDeleteISG_EEvSC_EEEEb (CQueryContext.cpp:73)
16   0xf69138 postgres _ZN5gpopt13CQueryContext11PqcGenerateEPN4gpos11CMemoryPoolEPNS_11CExpressionEPNS1_16CDynamicPtrArrayIjXadL_ZNS1_13CleanupDeleteIjEEvPT_EEEEPNS6_IN4gpmd7CMDNameEXadL_ZNS7_ISD_EEvS9_EEEEb (discriminator 2)
17   0x101f660 postgres _ZN5gpopt10COptimizer13PdxlnOptimizeEPN4gpos11CMemoryPoolEPNS_11CMDAccessorEPKN5gpdxl8CDXLNodeEPKNS1_16CDynamicPtrArrayIS7_XadL_ZNS1_14CleanupReleaseIS7_EEvPT_EEEESG_PNS_19IConstExprEvaluatorEjjjPNSA_INS_12CSearchStageEXadL_ZNS1_13CleanupDeleteISJ_EEvSD_EEEEPNS_16COptimizerConfigEPKc (discriminator 1)
18   0x1123fa2 postgres _ZN9COptTasks12OptimizeTaskEPv (COptTasks.cpp:991)
19   0xe01c5e postgres _ZN4gpos5CTask7ExecuteEv (CTask.cpp:130)
20   0xe02a48 postgres _ZN4gpos7CWorker7ExecuteEPNS_5CTaskE (CWorker.cpp:81)
21   0xe01439 postgres _ZNK4gpos14CAutoTaskProxy7ExecuteEPNS_5CTaskE (CAutoTaskProxy.cpp:302)
22   0xe03464 postgres gpos_exec (CAutoP.h:109)
23   0x1120d11 postgres _ZN9COptTasks7ExecuteEPFPvS0_ES0_ (COptTasks.cpp:265)
24   0x1121a5e postgres _ZN9COptTasks18GPOPTOptimizedPlanEP5QueryP11SOptContext (COptTasks.cpp:1190)
25   0x11251bf postgres _ZN12CGPOptimizer18GPOPTOptimizedPlanEP5QueryPb (CGPOptimizer.cpp:60)
26   0xaa5fe6 postgres optimize_query (orca.c:58)
27   0xa9b4ef postgres standard_planner (planner.c:381)
28   0xa9b8b5 postgres planner (planner.c:339)
29   0xb8c073 postgres pg_plan_queries (postgres.c:1025)
30   0xb8ccde postgres <symbol not found> (postgres.c:1857)

 

This issue is generally seen in CTE queries that include:

• Implicit casts between incompatible numeric types (e.g., smallint vs. integer)

• IS NULL predicates on uncasted numeric columns, especially inside UNION or UNION ALL constructs

The problem occurs only when using the GPORCA optimizer and does not reproduce when using the legacy PostgreSQL planner (optimizer=off).

 

Example to reproduce:

-- Create schema for isolation
CREATE SCHEMA orca_cte_repro;
SET search_path TO orca_cte_repro;

-- Test table
CREATE TABLE data_filter_test (
    id integer,
    category smallint
) DISTRIBUTED RANDOMLY;

INSERT INTO data_filter_test
SELECT g, (g % 20)::smallint FROM generate_series(1, 1000) g;

-- Query causing crash when optimizer=on
EXPLAIN
WITH t_active AS (
    SELECT category, id FROM data_filter_test WHERE id <> 0
),
t_all AS (
    SELECT category, id FROM data_filter_test
)
SELECT category FROM t_active a WHERE category <> 15
UNION ALL
SELECT a.category FROM t_active a
INNER JOIN t_all USING (category, id)
WHERE a.category = 15;

Environment

Greenplum Database 7.5.x

Optimizer: GPORCA enabled (SET optimizer = on)

 

Resolution

Workaround 1:

Temporarily disable ORCA for the session or the specific query:

SET optimizer = off;

 

This forces the legacy planner to execute the same query safely (at a potential performance cost).

 

Workaround 2:

Add explicit casts to all numeric columns or constants used in filter conditions or IS NULL predicates.

For example:

Change "WHERE category <> 15" in your query

to "WHERE category::int <> 15"

 

Permanent Fix:

This issue will be fixed Greenplum 7.7.0.