Restoring data(views) failed with error "GROUP BY position -1 is not in select list (SQLSTATE 42P10)"

Products

• VMware Tanzu Greenplum

Issue/Introduction

When we restore the data via gprestore, sometimes we might see the below error when restoring a view:

20231225:10:38:19 gprestore:gpadmin:gp-aio-rocky8:002282-[INFO]:-Restoring pre-data metadata
Pre-data objects restored:  0 / 2 [--------------------------------------------------------]   0.00%
20231225:10:38:19 gprestore:gpadmin:gp-aio-rocky8:002282-[CRITICAL]:-ERROR: GROUP BY position -1 is not in select list (SQLSTATE 42P10)

this could be due to a known issue in the GPDB version lower than 6.27.0, please refer to the details in below.

Root cause:

-- When we create a view with (-1) in the SQL code, the DB might store the wrong DDL like the below example shows:

# create table t1 (a int,b int,c int);
# create view v1 as select distinct a,b,c, -1::int from t1;

gpadmin=# \d+ v1;
                    View "public.v1"
  Column  |  Type   | Modifiers | Storage | Description
----------+---------+-----------+---------+-------------
 a        | integer |           | plain   |
 b        | integer |           | plain   |
 c        | integer |           | plain   |
 ?column? | integer |           | plain   |
View definition:
 SELECT t1.a,
    t1.b,
    t1.c,
    - 1
   FROM t1
  GROUP BY t1.a, t1.b, t1.c, - 1;

-- As above example shows, the DDL of the view contains "group by -1"
-- When backup the view, the problematic DDL will be written into the backup metadata:

# gpbackup --dbname gpadmin --backup-dir /tmp/backup/ --include-table public.v1  --metadata-only
# cat /tmp/backup/gpdb_6.24.6_-1/backups/20231225/20231225102823/gpbackup_20231225102823_metadata.sql
...
CREATE VIEW public.v1 AS  SELECT t1.a,
    t1.b,
    t1.c,
    (- 1)
   FROM public.t1
  GROUP BY t1.a, t1.b, t1.c, (- 1);

-- As a result, if we restore the data via this backup, it will fail like below

gprestore --backup-dir /tmp/backup/ --timestamp 20231225102823
20231225:10:38:19 gprestore:gpadmin:gp-aio-rocky8:002282-[INFO]:-Restore Key = 20231225102823
20231225:10:38:19 gprestore:gpadmin:gp-aio-rocky8:002282-[INFO]:-gpbackup version = 1.30.2
20231225:10:38:19 gprestore:gpadmin:gp-aio-rocky8:002282-[INFO]:-gprestore version = 1.30.2
20231225:10:38:19 gprestore:gpadmin:gp-aio-rocky8:002282-[INFO]:-Greenplum Database Version = 6.24.6 build commit:65583e9f5cccae53d49944762bb1ab1302647321
20231225:10:38:19 gprestore:gpadmin:gp-aio-rocky8:002282-[INFO]:-Restoring pre-data metadata
Pre-data objects restored:  0 / 2 [--------------------------------------------------------]   0.00%
20231225:10:38:19 gprestore:gpadmin:gp-aio-rocky8:002282-[CRITICAL]:-ERROR: GROUP BY position -1 is not in select list (SQLSTATE 42P10)

Environment

Product Version: 6.25

Resolution

R&D has already confirmed the root cause and the issue will be fixed under GPDB 6.27.0