cannot create a unique ID for path type: 106 Inbox

Products

• VMware Tanzu Data Intelligence
• VMware Tanzu Greenplum
• VMware Tanzu Greenplum / Gemfire

Issue/Introduction

You are intermittently observing below error when query uses Postgresql Planner either by setting explicitly optimizer=off or ORCA falling back to Postgresql planner.

ERROR: cannot create a unique ID for path type: 106 (cdbpath.c:1994)(File cdbpath.c; Line 1994; Routine cdbpath_dedup_fixup_walker; ), SQLSTATE [S1000]].

Rerun of the same query might successfully complete.

Environment

Greemplum Database 6.32.0 and lower version have this issue.

Cause

Planner failed to take into account MergeAppend nodes (type 106) when performing deduplication fixups in cdbpath_dedup_fixup_walker() during JOIN_DEDUP_SEMI for IN or EXISTS subqueries. This resulted in error:
ERROR: cannot create a unique ID for path type: 106 (cdbpath.c:1994)

This occurs when:

The outer table is partitioned and distributed on a column that is not part of the join key since the distribution mismatch forces DEDUP_SEMI joinover a regular semi-join.
An index scan on the join key with an ORDER BY provides sorted partition output. This allows the merge join to choose a MergeAppend node to merge the pre-sorted partition results over an Append and Sort.
The fix reuses cdbpath_dedup_fixup_append() for T_MergeAppend. This is safe because MergeAppendPath has the same memory layout as AppendPath for the fields the function accesses which are path and subpaths.

Resolution

• Workaround

Do not disable the OPTIMIZER=OFF explicitly as by default ORCA(OPTIMIZER=ON) will be enabled and should not have this issue.

 

• Permanent Solution

This bug will be fixed in future release.