legacy optimizer report "ERROR: operator 37 is not a valid ordering operator (pathkeys.c:579)"

[lmzzzzz1]

Bug Report

In GPDB, expression subquery is promoted to join, e.g. select * from T where a > (select 10*avg(x) from R where T.b=R.y); in postgres, the plan is:

                          QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on t  (cost=0.00..80364.30 rows=753 width=8)
   Filter: ((a)::numeric > (SubPlan 1))
   SubPlan 1
     ->  Aggregate  (cost=35.53..35.54 rows=1 width=32)
           ->  Seq Scan on r  (cost=0.00..35.50 rows=10 width=4)
                 Filter: (t.b = y)

while in GPDB, the subquery is promoted to join:

                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)
   ->  Hash Join  (cost=477.00..969.11 rows=9567 width=8)
         Hash Cond: (t.b = "Expr_SUBQUERY".csq_c0)
         Join Filter: ((t.a)::numeric > "Expr_SUBQUERY".csq_c1)
         ->  Seq Scan on t  (cost=0.00..321.00 rows=28700 width=8)
         ->  Hash  (cost=472.83..472.83 rows=333 width=36)
               ->  Subquery Scan on "Expr_SUBQUERY"
                     ->  HashAggregate
                           Group Key: r.y
                           ->  Seq Scan on r
 Optimizer: Postgres query optimizer

But if the T.b and R.y are different type, it will report error like:
ERROR:  operator 37 is not a valid ordering operator (pathkeys.c:579)

Greenplum version or build

7.0 and 6X

OS version and uname -a

linux

autoconf options used ( config.status --config )

Installation information ( pg_config )

Expected behavior

Actual behavior

Step to reproduce the behavior

create table t(a int, b int); create table r(x int, y bigint); set optimizer = off;

select * from T where a > (select 10*avg(x) from R where T.b=R.y);