Wrong result in GPDB 7.4.0 when comparing a column ::name COLLATE pg_catalog."C" with ::text

Products

• VMware Tanzu Data Suite
• VMware Tanzu Greenplum
• Greenplum

Issue/Introduction

When comparing a text and a column with datatype name COLLATE "C" which is the distribution column of the table returns an empty result in GPDB 7.4.0, but returns a correct result in GPDB 7.3.1, when the optimizer is on:

gpadmin=# CREATE TEMP TABLE t1 as select 'abc'::name COLLATE pg_catalog."C" AS f DISTRIBUTED BY (f);
SELECT 1

gpadmin=# SELECT f FROM t1 WHERE f = 'abc'::text;
 f
---
(0 rows)

gpadmin=# SELECT f FROM t1 WHERE f = 'abc';
  f
-----
 abc
(1 row)

gpadmin=# set optimizer to off; SELECT f FROM t1 WHERE f = 'abc'::text;
SET
  f
-----
 abc
(1 row)

gpadmin=#  set optimizer to on; SELECT f FROM t1 WHERE f = 'abc'::text;
SET
 f
---
(0 rows)

Resolution

Disable direct dispatch via a parameter:

test=# SET optimizer_enable_direct_dispatch=off;
test=# SELECT f FROM t1 WHERE f='abc'::text;
  f
-----
 abc
(1 row)

Additional Information