Orca optimizer fails to define varchar limits during CREATE TABLE AS SELECT (CTAS) operations

Products

• VMware Tanzu Greenplum
• VMware Tanzu Greenplum / Gemfire

Issue/Introduction

When creating a temporary table from a SELECT statement (CTAS) while the Orca optimizer is enabled (optimizer=on), columns cast to a specific varchar length (e.g., varchar(50)) are incorrectly created as unbounded character varying.

Symptoms:

• Expected results: A column defined as varchar(50) should truncate values exceeding 50 characters.
• Actual results: The column is created as character varying without a length limit.
• The issue does not occur if a LIMIT clause is added to the SELECT statement or if the Orca optimizer is disabled.

Cause

The issue is caused by a product defect in the Orca optimizer's logic for processing column metadata during CREATE TABLE AS SELECT operations.

Under certain execution plans, the optimizer fails to propagate the specific length constraints of character varying types to the table definition unless explicitly triggered by other operators like LIMIT.

Resolution

A permanent fix for this defect is scheduled for a future release.

 

Workaround: To ensure varchar limits are correctly defined in the resulting table, use one of the following methods:

• Disable Orca for the session:
   

  SET optimizer = off;

• Add a LIMIT clause: Include a LIMIT row in the SELECT statement used for table creation.