Repository: vitessio/vitess
Pull request #9167 (2021-11-09 07:08:00)

[12.0] Gen4: Fail cross-shard join query with aggregation and grouping
<!--
  Thank you for your contribution to the Vitess project.
  How to contribute: https://vitess.io/docs/contributing/
  Please first make sure there is an open Issue to discuss the feature/fix suggested in this PR.
  If this is a new feature, please mark the Issue as "RFC".
 -->

<!-- if this PR is Work in Progress please create it as a Draft Pull Request -->

## Description
<!-- A few sentences describing the overall goals of the pull request's commits. -->
<!-- If this is a bug fix and you think the fix should be backported, please write so. -->

Gen4 produces a plan for join queries having aggregation on grouping where the join is handled at VTGate. This was producing the wrong query plan. 
v3 fails for these types of queries already.

Backport of #9161 

## Related Issue(s)
<!-- List related issues and pull requests. If this PR fixes an issue, please add it using Fixes #????  -->

- Fixes https://github.com/vitessio/vitess/issues/9053

## Checklist
- [X] Tests were added or are not required
- [X] Documentation was added or is not required

## Deployment Notes
<!-- Notes regarding deployment of the contained body of work. These should note any db migrations, etc. -->


Patch:
diff --git a/go/vt/vtgate/planbuilder/horizon_planning.go b/go/vt/vtgate/planbuilder/horizon_planning.go
index a865c61db2..63d8d452e5 100644
--- a/go/vt/vtgate/planbuilder/horizon_planning.go
+++ b/go/vt/vtgate/planbuilder/horizon_planning.go
@@ -339,6 +339,10 @@ func (hp *horizonPlanning) planAggregations(ctx *planningContext, plan logicalPl
 		hp.vtgateGrouping = true
 	}
 
+	if joinPlan && hp.qp.HasAggr && len(hp.qp.GroupByExprs) > 0 {
+		return nil, vterrors.Errorf(vtrpcpb.Code_UNIMPLEMENTED, "unsupported: cross-shard query with aggregates")
+	}
+
 	for _, e := range hp.qp.SelectExprs {
 		aliasExpr, err := e.GetAliasedExpr()
 		if err != nil {


Test patch:
diff --git a/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt b/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt
index 6ae7d747a6..cc0a79d753 100644
--- a/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt
@@ -1696,11 +1696,6 @@ Gen4 plan same as above
   }
 }
 
-# scatter aggregate with complex select list (can't build order by)
-"select distinct a+1 from user"
-"generating order by clause: cannot reference a complex expression"
-Gen4 error: unsupported: in scatter query: complex order by expression: a + 1
-
 # scatter aggregate with numbered order by columns
 "select a, b, c, d, count(*) from user group by 1, 2, 3 order by 1, 2, 3"
 {
@@ -2341,59 +2336,6 @@ Gen4 plan same as above
   }
 }
 
-# Aggregate on join
-"select user.a, count(*) from user join user_extra group by user.a"
-"unsupported: cross-shard query with aggregates"
-Gen4 plan same as above
-
-# Aggregate on other table in join
-"select user.a, count(user_extra.a) from user join user_extra group by user.a"
-"unsupported: cross-shard query with aggregates"
-{
-  "QueryType": "SELECT",
-  "Original": "select user.a, count(user_extra.a) from user join user_extra group by user.a",
-  "Instructions": {
-    "OperatorType": "Aggregate",
-    "Variant": "Ordered",
-    "Aggregates": "count(1) AS count(user_extra.a)",
-    "GroupBy": "(0|2)",
-    "ResultColumns": 2,
-    "Inputs": [
-      {
-        "OperatorType": "Join",
-        "Variant": "Join",
-        "JoinColumnIndexes": "-1,1,-2",
-        "TableName": "`user`_user_extra",
-        "Inputs": [
-          {
-            "OperatorType": "Route",
-            "Variant": "SelectScatter",
-            "Keyspace": {
-              "Name": "user",
-              "Sharded": true
-            },
-            "FieldQuery": "select `user`.a, weight_string(`user`.a) from `user` where 1 != 1",
-            "OrderBy": "(0|1) ASC",
-            "Query": "select `user`.a, weight_string(`user`.a) from `user` order by `user`.a asc",
-            "Table": "`user`"
-          },
-          {
-            "OperatorType": "Route",
-            "Variant": "SelectScatter",
-            "Keyspace": {
-              "Name": "user",
-              "Sharded": true
-            },
-            "FieldQuery": "select count(user_extra.a) from user_extra where 1 != 1",
-            "Query": "select count(user_extra.a) from user_extra",
-            "Table": "user_extra"
-          }
-        ]
-      }
-    ]
-  }
-}
-
 # Cannot have more than one aggr(distinct...
 "select count(distinct a), count(distinct b) from user"
 "unsupported: only one distinct aggregation allowed in a select: count(distinct b)"
diff --git a/go/vt/vtgate/planbuilder/testdata/tpch_cases.txt b/go/vt/vtgate/planbuilder/testdata/tpch_cases.txt
index 1f405def6a..c403e96264 100644
--- a/go/vt/vtgate/planbuilder/testdata/tpch_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/tpch_cases.txt
@@ -11,102 +11,7 @@ Gen4 error: unsupported: cross-shard correlated subquery
 # TPC-H query 3
 "select l_orderkey, sum(l_extendedprice * (1 - l_discount)) as revenue, o_orderdate, o_shippriority from customer, orders, lineitem where c_mktsegment = 'BUILDING' and c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate < date('1995-03-15') and l_shipdate > date('1995-03-15') group by l_orderkey, o_orderdate, o_shippriority order by revenue desc, o_orderdate limit 10"
 "unsupported: cross-shard query with aggregates"
-{
-  "QueryType": "SELECT",
-  "Original": "select l_orderkey, sum(l_extendedprice * (1 - l_discount)) as revenue, o_orderdate, o_shippriority from customer, orders, lineitem where c_mktsegment = 'BUILDING' and c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate \u003c date('1995-03-15') and l_shipdate \u003e date('1995-03-15') group by l_orderkey, o_orderdate, o_shippriority order by revenue desc, o_orderdate limit 10",
-  "Instructions": {
-    "OperatorType": "Limit",
-    "Count": 10,
-    "Inputs": [
-      {
-        "OperatorType": "Sort",
-        "Variant": "Memory",
-        "OrderBy": "1 DESC, (2|5) ASC",
-        "ResultColumns": 4,
-        "Inputs": [
-          {
-            "OperatorType": "Aggregate",
-            "Variant": "Ordered",
-            "Aggregates": "sum(1) AS revenue",
-            "GroupBy": "(0|4), (2|5), (3|6)",
-            "Inputs": [
-              {
-                "OperatorType": "Sort",
-                "Variant": "Memory",
-                "OrderBy": "(0|4) ASC, (2|5) ASC, (3|6) ASC",
-                "Inputs": [
-                  {
-                    "OperatorType": "Join",
-                    "Variant": "Join",
-                    "JoinColumnIndexes": "-1,-2,1,2,-3,3,4",
-                    "JoinVars": {
-                      "l_orderkey": 0
-                    },
-                    "TableName": "lineitem_orders_customer",
-                    "Inputs": [
-                      {
-                        "OperatorType": "Route",
-                        "Variant": "SelectScatter",
-                        "Keyspace": {
-                          "Name": "main",
-                          "Sharded": true
-                        },
-                        "FieldQuery": "select l_orderkey, sum(l_extendedprice * (1 - l_discount)) as revenue, weight_string(l_orderkey) from lineitem where 1 != 1",
-                        "Query": "select l_orderkey, sum(l_extendedprice * (1 - l_discount)) as revenue, weight_string(l_orderkey) from lineitem where l_shipdate \u003e date('1995-03-15')",
-                        "Table": "lineitem"
-                      },
-                      {
-                        "OperatorType": "Join",
-                        "Variant": "Join",
-                        "JoinColumnIndexes": "-2,-3,-4,-5",
-                        "JoinVars": {
-                          "o_custkey": 0
-                        },
-                        "TableName": "orders_customer",
-                        "Inputs": [
-                          {
-                            "OperatorType": "Route",
-                            "Variant": "SelectEqualUnique",
-                            "Keyspace": {
-                              "Name": "main",
-                              "Sharded": true
-                            },
-                            "FieldQuery": "select o_custkey, o_orderdate, o_shippriority, weight_string(o_orderdate), weight_string(o_shippriority) from orders where 1 != 1",
-                            "Query": "select o_custkey, o_orderdate, o_shippriority, weight_string(o_orderdate), weight_string(o_shippriority) from orders where o_orderdate \u003c date('1995-03-15') and o_orderkey = :l_orderkey",
-                            "Table": "orders",
-                            "Values": [
-                              ":l_orderkey"
-                            ],
-                            "Vindex": "hash"
-                          },
-                          {
-                            "OperatorType": "Route",
-                            "Variant": "SelectEqualUnique",
-                            "Keyspace": {
-                              "Name": "main",
-                              "Sharded": true
-                            },
-                            "FieldQuery": "select 1 from customer where 1 != 1",
-                            "Query": "select 1 from customer where c_mktsegment = 'BUILDING' and c_custkey = :o_custkey",
-                            "Table": "customer",
-                            "Values": [
-                              ":o_custkey"
-                            ],
-                            "Vindex": "hash"
-                          }
-                        ]
-                      }
-                    ]
-                  }
-                ]
-              }
-            ]
-          }
-        ]
-      }
-    ]
-  }
-}
+Gen4 plan same as above
 
 # TPC-H query 4
 "select o_orderpriority, count(*) as order_count from orders where o_orderdate >= date('1993-07-01') and o_orderdate < date('1993-07-01') + interval '3' month and exists ( select * from lineitem where l_orderkey = o_orderkey and l_commitdate < l_receiptdate ) group by o_orderpriority order by o_orderpriority"
@@ -116,174 +21,7 @@ Gen4 error: unsupported: cross-shard correlated subquery
 # TPC-H query 5 - Gen4 produces plan but the plan output is flaky
 "select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from customer, orders, lineitem, supplier, nation, region where c_custkey = o_custkey and l_orderkey = o_orderkey and l_suppkey = s_suppkey and c_nationkey = s_nationkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'ASIA' and o_orderdate >= date('1994-01-01') and o_orderdate < date('1994-01-01') + interval '1' year group by n_name order by revenue desc"
 "unsupported: cross-shard query with aggregates"
-{
-  "QueryType": "SELECT",
-  "Original": "select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from customer, orders, lineitem, supplier, nation, region where c_custkey = o_custkey and l_orderkey = o_orderkey and l_suppkey = s_suppkey and c_nationkey = s_nationkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'ASIA' and o_orderdate \u003e= date('1994-01-01') and o_orderdate \u003c date('1994-01-01') + interval '1' year group by n_name order by revenue desc",
-  "Instructions": {
-    "OperatorType": "Sort",
-    "Variant": "Memory",
-    "OrderBy": "1 DESC",
-    "ResultColumns": 2,
-    "Inputs": [
-      {
-        "OperatorType": "Aggregate",
-        "Variant": "Ordered",
-        "Aggregates": "sum(1) AS revenue",
-        "GroupBy": "(0|2)",
-        "Inputs": [
-          {
-            "OperatorType": "Sort",
-            "Variant": "Memory",
-            "OrderBy": "(0|2) ASC",
-            "Inputs": [
-              {
-                "OperatorType": "Join",
-                "Variant": "Join",
-                "JoinColumnIndexes": "1,-2,2",
-                "JoinVars": {
-                  "s_nationkey": 0
-                },
-                "TableName": "orders_customer_lineitem_supplier_nation_region",
-                "Inputs": [
-                  {
-                    "OperatorType": "Join",
-                    "Variant": "Join",
-                    "JoinColumnIndexes": "1,2",
-                    "JoinVars": {
-                      "c_nationkey": 1,
-                      "o_orderkey": 0
-                    },
-                    "TableName": "orders_customer_lineitem_supplier",
-                    "Inputs": [
-                      {
-                        "OperatorType": "Join",
-                        "Variant": "Join",
-                        "JoinVars": {
-                          "o_custkey": 0
-                        },
-                        "TableName": "orders_customer",
-                        "Inputs": [
-                          {
-                            "OperatorType": "Route",
-                            "Variant": "SelectScatter",
-                            "Keyspace": {
-                              "Name": "main",
-                              "Sharded": true
-                            },
-                            "FieldQuery": "select o_custkey, o_orderkey from orders where 1 != 1",
-                            "Query": "select o_custkey, o_orderkey from orders where o_orderdate \u003e= date('1994-01-01') and o_orderdate \u003c date('1994-01-01') + interval '1' year",
-                            "Table": "orders"
-                          },
-                          {
-                            "OperatorType": "Route",
-                            "Variant": "SelectEqualUnique",
-                            "Keyspace": {
-                              "Name": "main",
-                              "Sharded": true
-                            },
-                            "FieldQuery": "select c_nationkey from customer where 1 != 1",
-                            "Query": "select c_nationkey from customer where c_custkey = :o_custkey",
-                            "Table": "customer",
-                            "Values": [
-                              ":o_custkey"
-                            ],
-                            "Vindex": "hash"
-                          }
-                        ]
-                      },
-                      {
-                        "OperatorType": "Join",
-                        "Variant": "Join",
-                        "JoinColumnIndexes": "1,-2",
-                        "JoinVars": {
-                          "l_suppkey": 0
-                        },
-                        "TableName": "lineitem_supplier",
-                        "Inputs": [
-                          {
-                            "OperatorType": "Route",
-                            "Variant": "SelectEqualUnique",
-                            "Keyspace": {
-                              "Name": "main",
-                              "Sharded": true
-                            },
-                            "FieldQuery": "select l_suppkey, sum(l_extendedprice * (1 - l_discount)) as revenue from lineitem where 1 != 1",
-                            "Query": "select l_suppkey, sum(l_extendedprice * (1 - l_discount)) as revenue from lineitem where l_orderkey = :o_orderkey",
-                            "Table": "lineitem",
-                            "Values": [
-                              ":o_orderkey"
-                            ],
-                            "Vindex": "lineitem_map"
-                          },
-                          {
-                            "OperatorType": "Route",
-                            "Variant": "SelectEqualUnique",
-                            "Keyspace": {
-                              "Name": "main",
-                              "Sharded": true
-                            },
-                            "FieldQuery": "select s_nationkey from supplier where 1 != 1",
-                            "Query": "select s_nationkey from supplier where s_suppkey = :l_suppkey and s_nationkey = :c_nationkey",
-                            "Table": "supplier",
-                            "Values": [
-                              ":l_suppkey"
-                            ],
-                            "Vindex": "hash"
-                          }
-                        ]
-                      }
-                    ]
-                  },
-                  {
-                    "OperatorType": "Join",
-                    "Variant": "Join",
-                    "JoinColumnIndexes": "-2,-3",
-                    "JoinVars": {
-                      "n_regionkey": 0
-                    },
-                    "TableName": "nation_region",
-                    "Inputs": [
-                      {
-                        "OperatorType": "Route",
-                        "Variant": "SelectEqualUnique",
-                        "Keyspace": {
-                          "Name": "main",
-                          "Sharded": true
-                        },
-                        "FieldQuery": "select n_regionkey, n_name, weight_string(n_name) from nation where 1 != 1",
-                        "Query": "select n_regionkey, n_name, weight_string(n_name) from nation where n_nationkey = :s_nationkey",
-                        "Table": "nation",
-                        "Values": [
-                          ":s_nationkey"
-                        ],
-                        "Vindex": "hash"
-                      },
-                      {
-                        "OperatorType": "Route",
-                        "Variant": "SelectEqualUnique",
-                        "Keyspace": {
-                          "Name": "main",
-                          "Sharded": true
-                        },
-                        "FieldQuery": "select 1 from region where 1 != 1",
-                        "Query": "select 1 from region where r_name = 'ASIA' and r_regionkey = :n_regionkey",
-                        "Table": "region",
-                        "Values": [
-                          ":n_regionkey"
-                        ],
-                        "Vindex": "hash"
-                      }
-                    ]
-                  }
-                ]
-              }
-            ]
-          }
-        ]
-      }
-    ]
-  }
-}
+Gen4 plan same as above
 
 # TPC-H query 6
 "select sum(l_extendedprice * l_discount) as revenue from lineitem where l_shipdate >= date('1994-01-01') and l_shipdate < date('1994-01-01') + interval '1' year and l_discount between 0.06 - 0.01 and 0.06 + 0.01 and l_quantity < 24"
@@ -350,128 +88,7 @@ Gen4 plan same as above
 # TPC-H query 10
 "select c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment from customer, orders, lineitem, nation where c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate >= date('1993-10-01') and o_orderdate < date('1993-10-01') + interval '3' month and l_returnflag = 'R' and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc limit 20"
 "unsupported: cross-shard query with aggregates"
-{
-  "QueryType": "SELECT",
-  "Original": "select c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment from customer, orders, lineitem, nation where c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate \u003e= date('1993-10-01') and o_orderdate \u003c date('1993-10-01') + interval '3' month and l_returnflag = 'R' and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc limit 20",
-  "Instructions": {
-    "OperatorType": "Limit",
-    "Count": 20,
-    "Inputs": [
-      {
-        "OperatorType": "Sort",
-        "Variant": "Memory",
-        "OrderBy": "2 DESC",
-        "ResultColumns": 8,
-        "Inputs": [
-          {
-            "OperatorType": "Aggregate",
-            "Variant": "Ordered",
-            "Aggregates": "sum(2) AS revenue",
-            "GroupBy": "(0|8), (1|9), (3|10), (6|11), (4|12), (5|13), (7|14)",
-            "Inputs": [
-              {
-                "OperatorType": "Sort",
-                "Variant": "Memory",
-                "OrderBy": "(0|8) ASC, (1|9) ASC, (3|10) ASC, (6|11) ASC, (4|12) ASC, (5|13) ASC, (7|14) ASC",
-                "Inputs": [
-                  {
-                    "OperatorType": "Join",
-                    "Variant": "Join",
-                    "JoinColumnIndexes": "1,2,-2,3,4,5,6,7,8,9,10,11,12,13,14",
-                    "JoinVars": {
-                      "o_custkey": 0
-                    },
-                    "TableName": "orders_lineitem_customer_nation",
-                    "Inputs": [
-                      {
-                        "OperatorType": "Join",
-                        "Variant": "Join",
-                        "JoinColumnIndexes": "-2,1",
-                        "JoinVars": {
-                          "o_orderkey": 0
-                        },
-                        "TableName": "orders_lineitem",
-                        "Inputs": [
-                          {
-                            "OperatorType": "Route",
-                            "Variant": "SelectScatter",
-                            "Keyspace": {
-                              "Name": "main",
-                              "Sharded": true
-                            },
-                            "FieldQuery": "select o_orderkey, o_custkey from orders where 1 != 1",
-                            "Query": "select o_orderkey, o_custkey from orders where o_orderdate \u003e= date('1993-10-01') and o_orderdate \u003c date('1993-10-01') + interval '3' month",
-                            "Table": "orders"
-                          },
-                          {
-                            "OperatorType": "Route",
-                            "Variant": "SelectEqualUnique",
-                            "Keyspace": {
-                              "Name": "main",
-                              "Sharded": true
-                            },
-                            "FieldQuery": "select sum(l_extendedprice * (1 - l_discount)) as revenue from lineitem where 1 != 1",
-                            "Query": "select sum(l_extendedprice * (1 - l_discount)) as revenue from lineitem where l_returnflag = 'R' and l_orderkey = :o_orderkey",
-                            "Table": "lineitem",
-                            "Values": [
-                              ":o_orderkey"
-                            ],
-                            "Vindex": "lineitem_map"
-                          }
-                        ]
-                      },
-                      {
-                        "OperatorType": "Join",
-                        "Variant": "Join",
-                        "JoinColumnIndexes": "-2,-3,-4,1,-5,-6,-7,-8,-9,-10,-11,2,-12,-13",
-                        "JoinVars": {
-                          "c_nationkey": 0
-                        },
-                        "TableName": "customer_nation",
-                        "Inputs": [
-                          {
-                            "OperatorType": "Route",
-                            "Variant": "SelectEqualUnique",
-                            "Keyspace": {
-                              "Name": "main",
-                              "Sharded": true
-                            },
-                            "FieldQuery": "select c_nationkey, c_custkey, c_name, c_acctbal, c_address, c_phone, c_comment, weight_string(c_custkey), weight_string(c_name), weight_string(c_acctbal), weight_string(c_phone), weight_string(c_address), weight_string(c_comment) from customer where 1 != 1",
-                            "Query": "select c_nationkey, c_custkey, c_name, c_acctbal, c_address, c_phone, c_comment, weight_string(c_custkey), weight_string(c_name), weight_string(c_acctbal), weight_string(c_phone), weight_string(c_address), weight_string(c_comment) from customer where c_custkey = :o_custkey",
-                            "Table": "customer",
-                            "Values": [
-                              ":o_custkey"
-                            ],
-                            "Vindex": "hash"
-                          },
-                          {
-                            "OperatorType": "Route",
-                            "Variant": "SelectEqualUnique",
-                            "Keyspace": {
-                              "Name": "main",
-                              "Sharded": true
-                            },
-                            "FieldQuery": "select n_name, weight_string(n_name) from nation where 1 != 1",
-                            "Query": "select n_name, weight_string(n_name) from nation where n_nationkey = :c_nationkey",
-                            "Table": "nation",
-                            "Values": [
-                              ":c_nationkey"
-                            ],
-                            "Vindex": "hash"
-                          }
-                        ]
-                      }
-                    ]
-                  }
-                ]
-              }
-            ]
-          }
-        ]
-      }
-    ]
-  }
-}
+Gen4 plan same as above
 
 # TPC-H query 11
 "select ps_partkey, sum(ps_supplycost * ps_availqty) as value from partsupp, supplier, nation where ps_suppkey = s_suppkey and s_nationkey = n_nationkey and n_name = 'GERMANY' group by ps_partkey  having sum(ps_supplycost * ps_availqty) > ( select sum(ps_supplycost * ps_availqty) * 0.00001000000 from partsupp, supplier, nation where ps_suppkey = s_suppkey and s_nationkey = n_nationkey and n_name = 'GERMANY' ) order by value desc"
@@ -481,68 +98,11 @@ Gen4 error: unsupported: in scatter query: complex aggregate expression
 # TPC-H query 12
 "select l_shipmode, sum(case when o_orderpriority = '1-URGENT' or o_orderpriority = '2-HIGH' then 1 else 0 end) as high_line_count, sum(case when o_orderpriority <> '1-URGENT' and o_orderpriority <> '2-HIGH' then 1 else 0 end) as low_line_count from orders, lineitem where o_orderkey = l_orderkey and l_shipmode in ('MAIL', 'SHIP') and l_commitdate < l_receiptdate and l_shipdate < l_commitdate and l_receiptdate >= date('1994-01-01') and l_receiptdate < date('1994-01-01') + interval '1' year group by l_shipmode order by l_shipmode"
 "unsupported: cross-shard query with aggregates"
-{
-  "QueryType": "SELECT",
-  "Original": "select l_shipmode, sum(case when o_orderpriority = '1-URGENT' or o_orderpriority = '2-HIGH' then 1 else 0 end) as high_line_count, sum(case when o_orderpriority \u003c\u003e '1-URGENT' and o_orderpriority \u003c\u003e '2-HIGH' then 1 else 0 end) as low_line_count from orders, lineitem where o_orderkey = l_orderkey and l_shipmode in ('MAIL', 'SHIP') and l_commitdate \u003c l_receiptdate and l_shipdate \u003c l_commitdate and l_receiptdate \u003e= date('1994-01-01') and l_receiptdate \u003c date('1994-01-01') + interval '1' year group by l_shipmode order by l_shipmode",
-  "Instructions": {
-    "OperatorType": "Aggregate",
-    "Variant": "Ordered",
-    "Aggregates": "sum(1) AS high_line_count, sum(2) AS low_line_count",
-    "GroupBy": "(0|3)",
-    "ResultColumns": 3,
-    "Inputs": [
-      {
-        "OperatorType": "Sort",
-        "Variant": "Memory",
-        "OrderBy": "(0|3) ASC",
-        "Inputs": [
-          {
-            "OperatorType": "Join",
-            "Variant": "Join",
-            "JoinColumnIndexes": "1,-2,-3,2",
-            "JoinVars": {
-              "o_orderkey": 0
-            },
-            "TableName": "orders_lineitem",
-            "Inputs": [
-              {
-                "OperatorType": "Route",
-                "Variant": "SelectScatter",
-                "Keyspace": {
-                  "Name": "main",
-                  "Sharded": true
-                },
-                "FieldQuery": "select o_orderkey, sum(case when o_orderpriority = '1-URGENT' or o_orderpriority = '2-HIGH' then 1 else 0 end) as high_line_count, sum(case when o_orderpriority != '1-URGENT' and o_orderpriority != '2-HIGH' then 1 else 0 end) as low_line_count from orders where 1 != 1",
-                "Query": "select o_orderkey, sum(case when o_orderpriority = '1-URGENT' or o_orderpriority = '2-HIGH' then 1 else 0 end) as high_line_count, sum(case when o_orderpriority != '1-URGENT' and o_orderpriority != '2-HIGH' then 1 else 0 end) as low_line_count from orders",
-                "Table": "orders"
-              },
-              {
-                "OperatorType": "Route",
-                "Variant": "SelectEqualUnique",
-                "Keyspace": {
-                  "Name": "main",
-                  "Sharded": true
-                },
-                "FieldQuery": "select l_shipmode, weight_string(l_shipmode) from lineitem where 1 != 1",
-                "Query": "select l_shipmode, weight_string(l_shipmode) from lineitem where l_shipmode in ('MAIL', 'SHIP') and l_commitdate \u003c l_receiptdate and l_shipdate \u003c l_commitdate and l_receiptdate \u003e= date('1994-01-01') and l_receiptdate \u003c date('1994-01-01') + interval '1' year and l_orderkey = :o_orderkey",
-                "Table": "lineitem",
-                "Values": [
-                  ":o_orderkey"
-                ],
-                "Vindex": "lineitem_map"
-              }
-            ]
-          }
-        ]
-      }
-    ]
-  }
-}
+Gen4 plan same as above
 
 # TPC-H query 13
 "select c_count, count(*) as custdist from ( select c_custkey, count(o_orderkey) from customer left outer join orders on c_custkey = o_custkey and o_comment not like '%special%requests%' group by c_custkey ) as c_orders(c_custkey, c_count) group by c_count order by custdist desc, c_count desc"
 "unsupported: column aliases in derived table"
-gen4 does not yet support: column aliases in derived table
 
 # TPC-H query 14
 "select 100.00 * sum(case when p_type like 'PROMO%' then l_extendedprice * (1 - l_discount) else 0 end) /  sum(l_extendedprice * (1 - l_discount)) as promo_revenue from lineitem, part where l_partkey = p_partkey and l_shipdate >= date('1995-09-01') and l_shipdate < date('1995-09-01') + interval '1' month"
diff --git a/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt b/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt
index 9fa7325fe8..69ef57a77c 100644
--- a/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/unsupported_cases.txt
@@ -583,3 +583,18 @@ Gen4 error: Column 'id' in field list is ambiguous
 "unsupported: column aliases in derived table"
 gen4 does not yet support: column aliases in derived table
 
+
+# Aggregate on join
+"select user.a, count(*) from user join user_extra group by user.a"
+"unsupported: cross-shard query with aggregates"
+Gen4 plan same as above
+
+# Aggregate on other table in join
+"select user.a, count(user_extra.a) from user join user_extra group by user.a"
+"unsupported: cross-shard query with aggregates"
+Gen4 plan same as above
+
+# scatter aggregate with complex select list (can't build order by)
+"select distinct a+1 from user"
+"generating order by clause: cannot reference a complex expression"
+Gen4 error: unsupported: in scatter query: complex order by expression: a + 1