Query fails with "ERROR: variable not found in subplan target list (setrefs.c:2176)" in Greenplum

Products

• VMware Tanzu Greenplum

Issue/Introduction

Sometimes if when a query contains many joins, the query can fail with the following error message in Greenplum 4.3.x:

ERROR: variable not found in subplan target list (setrefs.c:2176)

Environment

Product Version: 4.3

Resolution

This error usually only appears when running the query on the legacy planner. Often, running the query with ORCA will resolve this issue you if set optimizer=on;.

If you are running on ORCA and it does not resolve this issue, then check the stats on tables involved in the query and compare the values with a count on the table. Use these queries to verify:

select relname,reltuples from pg_class where relname like '<tablename>%';
select count(*) from <tablename>;

Usually analyzing the tables will fix the issue. However, if it does not, then please log a case with Support.

This issue is also fixed in Greenplum 5.24.0. If you face this issue in earlier releases of Greenplum 5.x, it is recommended that you upgrade.