Repository: vitessio/vitess
Pull request #10480 (2022-06-09 09:18:31)

fix: aggregation empty row on join with grouping and aggregations

<!--
  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

This PR fixes an aggregation-grouping bug in gen4 that does non-scalar aggregates on a top of a join. 
This happens when the left side of the join has data and no data is present on the right hand side.
If the right hand side of the join only contains an aggregation then even though the table has no data it will return count(*) as `0` and that will get mapped to the join output.
To avoid such scenario the change adds a grouping condition on the right hand side using literal 1 so that the rows from right hand side is only returned when the join condition matches.

<!-- 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. -->

## Related Issue(s)

<!-- List related issues and pull requests. If this PR fixes an issue, please add it using Fixes #????  -->

## Checklist

-   [X] "Backport me!" label has been added if this change should be backported 14.0
-   [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/schemadiff/schema.go b/go/vt/schemadiff/schema.go
index 5e1f6fbef7..abe307cf98 100644
--- a/go/vt/schemadiff/schema.go
+++ b/go/vt/schemadiff/schema.go
@@ -293,14 +293,17 @@ func (s *Schema) ViewNames() []string {
 // like the other. It returns a list of diffs.
 func (s *Schema) Diff(other *Schema, hints *DiffHints) (diffs []EntityDiff, err error) {
 	// dropped entities
+	var dropDiffs []EntityDiff
 	for _, e := range s.Entities() {
 		if _, ok := other.named[e.Name()]; !ok {
 			// other schema does not have the entity
-			diffs = append(diffs, e.Drop())
+			dropDiffs = append(dropDiffs, e.Drop())
 		}
 	}
 	// We iterate by order of "other" schema because we need to construct queries that will be valid
 	// for that schema (we need to maintain view dependencies according to target, not according to source)
+	var alterDiffs []EntityDiff
+	var createDiffs []EntityDiff
 	for _, e := range other.Entities() {
 		if fromEntity, ok := s.named[e.Name()]; ok {
 			// entities exist by same name in both schemas. Let's diff them.
@@ -313,8 +316,8 @@ func (s *Schema) Diff(other *Schema, hints *DiffHints) (diffs []EntityDiff, err
 				// hence the error.
 				// But in our schema context, we know better. We know we should DROP the one, CREATE the other.
 				// We proceed to do that, and implicitly ignore the error
-				diffs = append(diffs, fromEntity.Drop())
-				diffs = append(diffs, e.Create())
+				dropDiffs = append(dropDiffs, fromEntity.Drop())
+				createDiffs = append(createDiffs, e.Create())
 				// And we're good. We can move on to comparing next entity.
 			case err != nil:
 				// Any other kind of error
@@ -322,18 +325,95 @@ func (s *Schema) Diff(other *Schema, hints *DiffHints) (diffs []EntityDiff, err
 			default:
 				// No error, let's check the diff:
 				if diff != nil && !diff.IsEmpty() {
-					diffs = append(diffs, diff)
+					alterDiffs = append(alterDiffs, diff)
 				}
 			}
 		} else { // !ok
 			// Added entity
 			// this schema does not have the entity
-			diffs = append(diffs, e.Create())
+			createDiffs = append(createDiffs, e.Create())
 		}
 	}
+	dropDiffs, createDiffs, renameDiffs := s.heuristicallyDetectTableRenames(dropDiffs, createDiffs, hints)
+	diffs = append(diffs, dropDiffs...)
+	diffs = append(diffs, alterDiffs...)
+	diffs = append(diffs, createDiffs...)
+	diffs = append(diffs, renameDiffs...)
+
 	return diffs, err
 }
 
+func (s *Schema) heuristicallyDetectTableRenames(
+	dropDiffs []EntityDiff,
+	createDiffs []EntityDiff,
+	hints *DiffHints,
+) (
+	updatedDropDiffs []EntityDiff,
+	updatedCreateDiffs []EntityDiff,
+	renameDiffs []EntityDiff,
+) {
+	renameDiffs = []EntityDiff{}
+
+	findRenamedTable := func() bool {
+		// What we're doing next is to try and identify a table RENAME.
+		// We do so by cross referencing dropped and created tables.
+		// The check is heuristic, and looks like this:
+		// We consider a table renamed iff:
+		// - the DROP and CREATE table definitions are identical other than the table name
+		// In the case where multiple dropped tables have identical schema, and likewise multiple created tables
+		// have identical schemas, schemadiff makes an arbitrary match.
+		// Once we heuristically decide that we found a RENAME, we cancel the DROP,
+		// cancel the CREATE, and inject a RENAME in place of both.
+
+		// findRenamedTable cross references dropped and created tables to find a single renamed table. If such is found:
+		// we remove the entry from DROPped tables, remove the entry from CREATEd tables, add an entry for RENAMEd tables,
+		// and return 'true'.
+		// Successive calls to this function will then find the next heuristic RENAMEs.
+		// the function returns 'false' if it is unable to heuristically find a RENAME.
+		for iDrop, drop1 := range dropDiffs {
+			for iCreate, create2 := range createDiffs {
+				dropTableDiff, ok := drop1.(*DropTableEntityDiff)
+				if !ok {
+					continue
+				}
+				createTableDiff, ok := create2.(*CreateTableEntityDiff)
+				if !ok {
+					continue
+				}
+				if !dropTableDiff.from.identicalOtherThanName(createTableDiff.to) {
+					continue
+				}
+				// Yes, it looks like those tables have the exact same spec, just with different names.
+				dropDiffs = append(dropDiffs[0:iDrop], dropDiffs[iDrop+1:]...)
+				createDiffs = append(createDiffs[0:iCreate], createDiffs[iCreate+1:]...)
+				renameTable := &sqlparser.RenameTable{
+					TablePairs: []*sqlparser.RenameTablePair{
+						{FromTable: dropTableDiff.from.Table, ToTable: createTableDiff.to.Table},
+					},
+				}
+				renameTableEntityDiff := &RenameTableEntityDiff{
+					from:        dropTableDiff.from,
+					to:          createTableDiff.to,
+					renameTable: renameTable,
+				}
+				renameDiffs = append(renameDiffs, renameTableEntityDiff)
+				return true
+			}
+		}
+		return false
+	}
+	switch hints.TableRenameStrategy {
+	case TableRenameAssumeDifferent:
+		// do nothing
+	case TableRenameHeuristicStatement:
+		for findRenamedTable() {
+			// Iteratively detect all RENAMEs
+		}
+	}
+
+	return dropDiffs, createDiffs, renameDiffs
+}
+
 // Entity returns an entity by name, or nil if nonexistent
 func (s *Schema) Entity(name string) Entity {
 	return s.named[name]
@@ -476,6 +556,21 @@ func (s *Schema) apply(diffs []EntityDiff) error {
 			if !found {
 				return &ApplyViewNotFoundError{View: diff.from.ViewName.Name.String()}
 			}
+		case *RenameTableEntityDiff:
+			// We expect the table to exist
+			found := false
+			for i, t := range s.tables {
+				if name := t.Table.Name.String(); name == diff.from.Table.Name.String() {
+					s.tables[i] = diff.to
+					delete(s.named, name)
+					s.named[diff.to.Table.Name.String()] = diff.to
+					found = true
+					break
+				}
+			}
+			if !found {
+				return &ApplyTableNotFoundError{Table: diff.from.Table.Name.String()}
+			}
 		default:
 			return &UnsupportedApplyOperationError{Statement: diff.CanonicalStatementString()}
 		}
diff --git a/go/vt/schemadiff/table.go b/go/vt/schemadiff/table.go
index acf6f263aa..20792ff6d0 100644
--- a/go/vt/schemadiff/table.go
+++ b/go/vt/schemadiff/table.go
@@ -110,6 +110,7 @@ func (d *AlterTableEntityDiff) addSubsequentDiff(diff *AlterTableEntityDiff) {
 
 //
 type CreateTableEntityDiff struct {
+	to          *CreateTableEntity
 	createTable *sqlparser.CreateTable
 }
 
@@ -221,6 +222,64 @@ func (d *DropTableEntityDiff) SubsequentDiff() EntityDiff {
 func (d *DropTableEntityDiff) SetSubsequentDiff(EntityDiff) {
 }
 
+//
+type RenameTableEntityDiff struct {
+	from        *CreateTableEntity
+	to          *CreateTableEntity
+	renameTable *sqlparser.RenameTable
+}
+
+// IsEmpty implements EntityDiff
+func (d *RenameTableEntityDiff) IsEmpty() bool {
+	return d.Statement() == nil
+}
+
+// Entities implements EntityDiff
+func (d *RenameTableEntityDiff) Entities() (from Entity, to Entity) {
+	return d.from, d.to
+}
+
+// Statement implements EntityDiff
+func (d *RenameTableEntityDiff) Statement() sqlparser.Statement {
+	if d == nil {
+		return nil
+	}
+	return d.renameTable
+}
+
+// CreateTable returns the underlying sqlparser.CreateTable that was generated for the diff.
+func (d *RenameTableEntityDiff) RenameTable() *sqlparser.RenameTable {
+	if d == nil {
+		return nil
+	}
+	return d.renameTable
+}
+
+// StatementString implements EntityDiff
+func (d *RenameTableEntityDiff) StatementString() (s string) {
+	if stmt := d.Statement(); stmt != nil {
+		s = sqlparser.String(stmt)
+	}
+	return s
+}
+
+// CanonicalStatementString implements EntityDiff
+func (d *RenameTableEntityDiff) CanonicalStatementString() (s string) {
+	if stmt := d.Statement(); stmt != nil {
+		s = sqlparser.CanonicalString(stmt)
+	}
+	return s
+}
+
+// SubsequentDiff implements EntityDiff
+func (d *RenameTableEntityDiff) SubsequentDiff() EntityDiff {
+	return nil
+}
+
+// SetSubsequentDiff implements EntityDiff
+func (d *RenameTableEntityDiff) SetSubsequentDiff(EntityDiff) {
+}
+
 // CreateTableEntity stands for a TABLE construct. It contains the table's CREATE statement.
 type CreateTableEntity struct {
 	sqlparser.CreateTable
@@ -1337,11 +1396,11 @@ func heuristicallyDetectColumnRenames(
 		// At any case, once we heuristically decide that we found a RENAME, we cancel the DROP,
 		// cancel the ADD, and inject a RENAME in place of both.
 
-		// findRenamedColumn cross referenced dropped and added columns to find a single renamed column. If such is found:
+		// findRenamedColumn cross references dropped and added columns to find a single renamed column. If such is found:
 		// we remove the entry from DROPped columns, remove the entry from ADDed columns, add an entry for RENAMEd columns,
 		// and return 'true'.
 		// Successive calls to this function will then find the next heuristic RENAMEs.
-		// the function returns 'false' if it is unabl eto heuristically find a RENAME.
+		// the function returns 'false' if it is unable to heuristically find a RENAME.
 		for iDrop, dropCol1 := range dropColumns {
 			for iAdd, addCol2 := range addColumns {
 				col1Details := t1ColumnsMap[dropCol1.Name.Name.Lowered()]
@@ -1378,7 +1437,7 @@ func heuristicallyDetectColumnRenames(
 
 // Create implements Entity interface
 func (c *CreateTableEntity) Create() EntityDiff {
-	return &CreateTableEntityDiff{createTable: &c.CreateTable}
+	return &CreateTableEntityDiff{to: c, createTable: &c.CreateTable}
 }
 
 // Drop implements Entity interface
@@ -2014,3 +2073,19 @@ func (c *CreateTableEntity) validate() error {
 	}
 	return nil
 }
+
+// identicalOtherThanName reutrns true when this CREATE TABLE and the given one, are identical
+// other than in table's name. We assume both have been normalized.
+func (c *CreateTableEntity) identicalOtherThanName(other *CreateTableEntity) bool {
+	if other == nil {
+		return false
+	}
+	return tableWithMaskedName(&c.CreateTable) == tableWithMaskedName(&other.CreateTable)
+}
+
+// tableWithMaskedName returns the CREATE TABLE statement but with table's name replaced with a constant arbitrary name
+func tableWithMaskedName(createTable *sqlparser.CreateTable) string {
+	createTable = sqlparser.CloneRefOfCreateTable(createTable)
+	createTable.Table.Name = sqlparser.NewTableIdent("mask")
+	return sqlparser.CanonicalString(createTable)
+}
diff --git a/go/vt/schemadiff/types.go b/go/vt/schemadiff/types.go
index 4d5a6d25d4..b6de2c49f1 100644
--- a/go/vt/schemadiff/types.go
+++ b/go/vt/schemadiff/types.go
@@ -76,6 +76,11 @@ const (
 	ColumnRenameHeuristicStatement
 )
 
+const (
+	TableRenameAssumeDifferent = iota
+	TableRenameHeuristicStatement
+)
+
 // DiffHints is an assortment of rules for diffing entities
 type DiffHints struct {
 	StrictIndexOrdering     bool
@@ -83,4 +88,5 @@ type DiffHints struct {
 	RangeRotationStrategy   int
 	ConstraintNamesStrategy int
 	ColumnRenameStrategy    int
+	TableRenameStrategy     int
 }
diff --git a/go/vt/vtgate/planbuilder/aggregation_pushing.go b/go/vt/vtgate/planbuilder/aggregation_pushing.go
index 7f496ab1f0..88bcfbb309 100644
--- a/go/vt/vtgate/planbuilder/aggregation_pushing.go
+++ b/go/vt/vtgate/planbuilder/aggregation_pushing.go
@@ -17,6 +17,8 @@ limitations under the License.
 package planbuilder
 
 import (
+	"strconv"
+
 	vtrpcpb "vitess.io/vitess/go/vt/proto/vtrpc"
 	"vitess.io/vitess/go/vt/sqlparser"
 	"vitess.io/vitess/go/vt/vterrors"
@@ -162,7 +164,7 @@ func pushAggrOnRoute(
 			pos = newOffset(groupingCols[idx])
 		}
 
-		if ctx.SemTable.NeedsWeightString(expr.Inner) {
+		if expr.WeightStrExpr != nil && ctx.SemTable.NeedsWeightString(expr.Inner) {
 			wsExpr := weightStringFor(expr.WeightStrExpr)
 			wsCol, _, err := addExpressionToRoute(ctx, plan, &sqlparser.AliasedExpr{Expr: wsExpr}, true)
 			if err != nil {
@@ -274,6 +276,22 @@ func (hp *horizonPlanning) pushAggrOnJoin(
 		return nil, nil, err
 	}
 
+	// If the rhs has no grouping column then a count(*) will return 0 from the query and will get mapped to the record from left hand side.
+	// This is an incorrect behaviour as the join condition has not matched, so we add a literal 1 to the select query and also group by on it.
+	// So that only if join condition matches the records will be mapped and returned.
+	if len(rhsGrouping) == 0 && len(rhsAggrs) != 0 {
+		l := sqlparser.NewIntLiteral("1")
+		aExpr := &sqlparser.AliasedExpr{
+			Expr: l,
+		}
+		offset, _, err := pushProjection(ctx, aExpr, join.Right, true, true, false)
+		if err != nil {
+			return nil, nil, err
+		}
+		l = sqlparser.NewIntLiteral(strconv.Itoa(offset + 1))
+		rhsGrouping = append(rhsGrouping, abstract.GroupBy{Inner: l})
+	}
+
 	// Next we push the aggregations to both sides
 	newLHS, lhsOffsets, lhsAggrOffsets, _, err := hp.filteredPushAggregation(ctx, join.Left, lhsGrouping, lhsAggrs, true)
 	if err != nil {


Test patch:
diff --git a/go/test/endtoend/vtgate/queries/aggregation/aggregation_test.go b/go/test/endtoend/vtgate/queries/aggregation/aggregation_test.go
index 668c9885c5..dd237cf0c8 100644
--- a/go/test/endtoend/vtgate/queries/aggregation/aggregation_test.go
+++ b/go/test/endtoend/vtgate/queries/aggregation/aggregation_test.go
@@ -354,4 +354,17 @@ func TestEmptyTableAggr(t *testing.T) {
 			mcmp.AssertMatches(" select /*vt+ PLANNER=gen4 */ t1.`name`, count(*) from t1 inner join t2 on (t1.t1_id = t2.id) where t1.value = 'foo' group by t1.`name`", "[]")
 		})
 	}
+
+	mcmp.Exec("insert into t1(t1_id, `name`, `value`, shardkey) values(1,'a1','foo',100), (2,'b1','foo',200), (3,'c1','foo',300), (3,'a1','foo',100), (3,'b1','bar',200)")
+
+	for _, workload := range []string{"oltp", "olap"} {
+		t.Run(workload, func(t *testing.T) {
+			utils.Exec(t, mcmp.VtConn, fmt.Sprintf("set workload = %s", workload))
+			mcmp.AssertMatches(" select /*vt+ PLANNER=gen4 */ count(*) from t1 inner join t2 on (t1.t1_id = t2.id) where t1.value = 'foo'", "[[INT64(0)]]")
+			mcmp.AssertMatches(" select /*vt+ PLANNER=gen4 */ count(*) from t2 inner join t1 on (t1.t1_id = t2.id) where t1.value = 'foo'", "[[INT64(0)]]")
+			mcmp.AssertMatches(" select /*vt+ PLANNER=gen4 */ t1.`name`, count(*) from t1 inner join t2 on (t1.t1_id = t2.id) where t1.value = 'foo' group by t1.`name`", "[]")
+			mcmp.AssertMatches(" select /*vt+ PLANNER=gen4 */ t1.`name`, count(*) from t2 inner join t1 on (t1.t1_id = t2.id) where t1.value = 'foo' group by t1.`name`", "[]")
+		})
+	}
+
 }
diff --git a/go/vt/schemadiff/diff_test.go b/go/vt/schemadiff/diff_test.go
index c1858df56e..ab9d8aaf9c 100644
--- a/go/vt/schemadiff/diff_test.go
+++ b/go/vt/schemadiff/diff_test.go
@@ -290,6 +290,7 @@ func TestDiffSchemas(t *testing.T) {
 		diffs       []string
 		cdiffs      []string
 		expectError string
+		tableRename int
 	}{
 		{
 			name: "identical tables",
@@ -452,6 +453,71 @@ func TestDiffSchemas(t *testing.T) {
 				"CREATE TABLE `t4` (\n\t`id` int PRIMARY KEY\n)",
 			},
 		},
+		{
+			name: "identical tables: drop and create",
+			from: "create table t1(id int primary key); create table t2(id int unsigned primary key);",
+			to:   "create table t1(id int primary key); create table t3(id int unsigned primary key);",
+			diffs: []string{
+				"drop table t2",
+				"create table t3 (\n\tid int unsigned primary key\n)",
+			},
+			cdiffs: []string{
+				"DROP TABLE `t2`",
+				"CREATE TABLE `t3` (\n\t`id` int unsigned PRIMARY KEY\n)",
+			},
+		},
+		{
+			name: "identical tables: heuristic rename",
+			from: "create table t1(id int primary key); create table t2a(id int unsigned primary key);",
+			to:   "create table t1(id int primary key); create table t2b(id int unsigned primary key);",
+			diffs: []string{
+				"rename table t2a to t2b",
+			},
+			cdiffs: []string{
+				"RENAME TABLE `t2a` TO `t2b`",
+			},
+			tableRename: TableRenameHeuristicStatement,
+		},
+		{
+			name: "identical tables: drop and create",
+			from: "create table t1a(id int primary key); create table t2a(id int unsigned primary key); create table t3a(id smallint primary key); ",
+			to:   "create table t1b(id bigint primary key); create table t2b(id int unsigned primary key); create table t3b(id int primary key); ",
+			diffs: []string{
+				"drop table t1a",
+				"drop table t2a",
+				"drop table t3a",
+				"create table t1b (\n\tid bigint primary key\n)",
+				"create table t2b (\n\tid int unsigned primary key\n)",
+				"create table t3b (\n\tid int primary key\n)",
+			},
+			cdiffs: []string{
+				"DROP TABLE `t1a`",
+				"DROP TABLE `t2a`",
+				"DROP TABLE `t3a`",
+				"CREATE TABLE `t1b` (\n\t`id` bigint PRIMARY KEY\n)",
+				"CREATE TABLE `t2b` (\n\t`id` int unsigned PRIMARY KEY\n)",
+				"CREATE TABLE `t3b` (\n\t`id` int PRIMARY KEY\n)",
+			},
+		},
+		{
+			name: "identical tables: multiple heuristic rename",
+			from: "create table t1a(id int primary key); create table t2a(id int unsigned primary key); create table t3a(id smallint primary key); ",
+			to:   "create table t1b(id bigint primary key); create table t2b(id int unsigned primary key); create table t3b(id int primary key); ",
+			diffs: []string{
+				"drop table t3a",
+				"create table t1b (\n\tid bigint primary key\n)",
+				"rename table t1a to t3b",
+				"rename table t2a to t2b",
+			},
+			cdiffs: []string{
+				"DROP TABLE `t3a`",
+				"CREATE TABLE `t1b` (\n\t`id` bigint PRIMARY KEY\n)",
+				"RENAME TABLE `t1a` TO `t3b`",
+				"RENAME TABLE `t2a` TO `t2b`",
+			},
+			tableRename: TableRenameHeuristicStatement,
+		},
+		// Views
 		{
 			name: "identical views",
 			from: "create table t(id int); create view v1 as select * from t",
@@ -536,23 +602,25 @@ func TestDiffSchemas(t *testing.T) {
 				"drop table t1",
 				"drop view v1",
 				"alter table t2 modify column id bigint primary key",
-				"create table t4 (\n\tid int primary key\n)",
 				"alter view v2 as select id from t2",
+				"create table t4 (\n\tid int primary key\n)",
 				"create view v0 as select * from v2, t2",
 			},
 			cdiffs: []string{
 				"DROP TABLE `t1`",
 				"DROP VIEW `v1`",
 				"ALTER TABLE `t2` MODIFY COLUMN `id` bigint PRIMARY KEY",
-				"CREATE TABLE `t4` (\n\t`id` int PRIMARY KEY\n)",
 				"ALTER VIEW `v2` AS SELECT `id` FROM `t2`",
+				"CREATE TABLE `t4` (\n\t`id` int PRIMARY KEY\n)",
 				"CREATE VIEW `v0` AS SELECT * FROM `v2`, `t2`",
 			},
 		},
 	}
-	hints := &DiffHints{}
 	for _, ts := range tt {
 		t.Run(ts.name, func(t *testing.T) {
+			hints := &DiffHints{
+				TableRenameStrategy: ts.tableRename,
+			}
 			diffs, err := DiffSchemasSQL(ts.from, ts.to, hints)
 			if ts.expectError != "" {
 				require.Error(t, err)
diff --git a/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt b/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt
index 5dfd795fd1..7d127c123a 100644
--- a/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/aggr_cases.txt
@@ -3332,7 +3332,7 @@ Gen4 plan same as above
           {
             "OperatorType": "Join",
             "Variant": "Join",
-            "JoinColumnIndexes": "L:0,R:0",
+            "JoinColumnIndexes": "L:0,R:1",
             "TableName": "`user`_user_extra",
             "Inputs": [
               {
@@ -3353,8 +3353,8 @@ Gen4 plan same as above
                   "Name": "user",
                   "Sharded": true
                 },
-                "FieldQuery": "select count(*) from user_extra where 1 != 1",
-                "Query": "select count(*) from user_extra",
+                "FieldQuery": "select 1, count(*) from user_extra where 1 != 1 group by 1",
+                "Query": "select 1, count(*) from user_extra group by 1",
                 "Table": "user_extra"
               }
             ]
@@ -3443,7 +3443,7 @@ Gen4 plan same as above
           {
             "OperatorType": "Join",
             "Variant": "Join",
-            "JoinColumnIndexes": "L:1,L:2,L:0,R:0",
+            "JoinColumnIndexes": "L:1,L:2,L:0,R:1",
             "TableName": "`user`_user_extra",
             "Inputs": [
               {
@@ -3465,8 +3465,8 @@ Gen4 plan same as above
                   "Name": "user",
                   "Sharded": true
                 },
-                "FieldQuery": "select count(*) from user_extra where 1 != 1",
-                "Query": "select count(*) from user_extra",
+                "FieldQuery": "select 1, count(*) from user_extra where 1 != 1 group by 1",
+                "Query": "select 1, count(*) from user_extra group by 1",
                 "Table": "user_extra"
               }
             ]
@@ -3501,7 +3501,7 @@ Gen4 plan same as above
           {
             "OperatorType": "Join",
             "Variant": "Join",
-            "JoinColumnIndexes": "L:1,L:2,L:0,R:0",
+            "JoinColumnIndexes": "L:1,L:2,L:0,R:1",
             "TableName": "`user`_user_extra",
             "Inputs": [
               {
@@ -3523,8 +3523,8 @@ Gen4 plan same as above
                   "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",
+                "FieldQuery": "select 1, count(user_extra.a) from user_extra where 1 != 1 group by 1",
+                "Query": "select 1, count(user_extra.a) from user_extra group by 1",
                 "Table": "user_extra"
               }
             ]
@@ -4013,7 +4013,7 @@ Gen4 plan same as above
           {
             "OperatorType": "Join",
             "Variant": "Join",
-            "JoinColumnIndexes": "L:0,L:1,L:2,R:0,R:1",
+            "JoinColumnIndexes": "L:0,L:1,L:2,R:1,R:2",
             "JoinVars": {
               "user_col": 0
             },
@@ -4038,8 +4038,8 @@ Gen4 plan same as above
                   "Name": "user",
                   "Sharded": true
                 },
-                "FieldQuery": "select min(user_extra.foo), max(user_extra.bar) from user_extra where 1 != 1",
-                "Query": "select min(user_extra.foo), max(user_extra.bar) from user_extra where user_extra.bar = :user_col",
+                "FieldQuery": "select 1, min(user_extra.foo), max(user_extra.bar) from user_extra where 1 != 1 group by 1",
+                "Query": "select 1, min(user_extra.foo), max(user_extra.bar) from user_extra where user_extra.bar = :user_col group by 1",
                 "Table": "user_extra"
               }
             ]
@@ -4110,7 +4110,7 @@ Gen4 error: aggregate functions take a single argument 'count(distinct user_id,
           {
             "OperatorType": "Join",
             "Variant": "Join",
-            "JoinColumnIndexes": "L:0,L:1,L:2,R:0",
+            "JoinColumnIndexes": "L:0,L:1,L:2,R:1",
             "TableName": "`user`_user_extra",
             "Inputs": [
               {
@@ -4131,8 +4131,8 @@ Gen4 error: aggregate functions take a single argument 'count(distinct user_id,
                   "Name": "user",
                   "Sharded": true
                 },
-                "FieldQuery": "select count(*) from user_extra where 1 != 1",
-                "Query": "select count(*) from user_extra",
+                "FieldQuery": "select 1, count(*) from user_extra where 1 != 1 group by 1",
+                "Query": "select 1, count(*) from user_extra group by 1",
                 "Table": "user_extra"
               }
             ]
@@ -4598,7 +4598,7 @@ Gen4 error: aggregate functions take a single argument 'count(distinct user_id,
           {
             "OperatorType": "Join",
             "Variant": "Join",
-            "JoinColumnIndexes": "L:1,L:2,L:0,R:0",
+            "JoinColumnIndexes": "L:1,L:2,L:0,R:1",
             "JoinVars": {
               "user_col": 0
             },
@@ -4623,8 +4623,8 @@ Gen4 error: aggregate functions take a single argument 'count(distinct user_id,
                   "Name": "user",
                   "Sharded": true
                 },
-                "FieldQuery": "select count(*) from user_extra where 1 != 1",
-                "Query": "select count(*) from user_extra where user_extra.col = :user_col",
+                "FieldQuery": "select 1, count(*) from user_extra where 1 != 1 group by 1",
+                "Query": "select 1, count(*) from user_extra where user_extra.col = :user_col group by 1",
                 "Table": "user_extra"
               }
             ]
@@ -4734,7 +4734,7 @@ Gen4 error: aggregate functions take a single argument 'count(distinct user_id,
           {
             "OperatorType": "Join",
             "Variant": "Join",
-            "JoinColumnIndexes": "L:0,L:1,L:0,R:0",
+            "JoinColumnIndexes": "L:0,L:1,L:0,R:1",
             "TableName": "`user`_user_extra",
             "Inputs": [
               {
@@ -4756,8 +4756,8 @@ Gen4 error: aggregate functions take a single argument 'count(distinct user_id,
                   "Name": "user",
                   "Sharded": true
                 },
-                "FieldQuery": "select count(*) from user_extra where 1 != 1",
-                "Query": "select count(*) from user_extra",
+                "FieldQuery": "select 1, count(*) from user_extra where 1 != 1 group by 1",
+                "Query": "select 1, count(*) from user_extra group by 1",
                 "Table": "user_extra"
               }
             ]
diff --git a/go/vt/vtgate/planbuilder/testdata/tpch_cases.txt b/go/vt/vtgate/planbuilder/testdata/tpch_cases.txt
index c9f0286c25..4e4bd07cab 100644
--- a/go/vt/vtgate/planbuilder/testdata/tpch_cases.txt
+++ b/go/vt/vtgate/planbuilder/testdata/tpch_cases.txt
@@ -70,7 +70,7 @@ Gen4 error: unsupported: cross-shard correlated subquery
                           {
                             "OperatorType": "Join",
                             "Variant": "Join",
-                            "JoinColumnIndexes": "L:3,L:5,L:4,L:6,L:1,R:0",
+                            "JoinColumnIndexes": "L:3,L:5,L:4,L:6,L:1,R:1",
                             "JoinVars": {
                               "o_custkey": 0
                             },
@@ -98,8 +98,8 @@ Gen4 error: unsupported: cross-shard correlated subquery
                                   "Name": "main",
                                   "Sharded": true
                                 },
-                                "FieldQuery": "select count(*) from customer where 1 != 1",
-                                "Query": "select count(*) from customer where c_mktsegment = 'BUILDING' and c_custkey = :o_custkey",
+                                "FieldQuery": "select 1, count(*) from customer where 1 != 1 group by 1",
+                                "Query": "select 1, count(*) from customer where c_mktsegment = 'BUILDING' and c_custkey = :o_custkey group by 1",
                                 "Table": "customer",
                                 "Values": [
                                   ":o_custkey"
@@ -318,7 +318,7 @@ Gen4 error: unsupported: cross-shard correlated subquery
                       {
                         "OperatorType": "Join",
                         "Variant": "Join",
-                        "JoinColumnIndexes": "L:3,L:4,L:1,R:0",
+                        "JoinColumnIndexes": "L:3,L:4,L:1,R:1",
                         "JoinVars": {
                           "n_regionkey": 0
                         },
@@ -346,8 +346,8 @@ Gen4 error: unsupported: cross-shard correlated subquery
                               "Name": "main",
                               "Sharded": true
                             },
-                            "FieldQuery": "select count(*) from region where 1 != 1",
-                            "Query": "select count(*) from region where r_name = 'ASIA' and r_regionkey = :n_regionkey",
+                            "FieldQuery": "select 1, count(*) from region where 1 != 1 group by 1",
+                            "Query": "select 1, count(*) from region where r_name = 'ASIA' and r_regionkey = :n_regionkey group by 1",
                             "Table": "region",
                             "Values": [
                               ":n_regionkey"
@@ -664,7 +664,7 @@ Gen4 error: aggregation on columns from different sources not supported yet
                           {
                             "OperatorType": "Join",
                             "Variant": "Join",
-                            "JoinColumnIndexes": "L:1,L:1,L:4,L:2,R:0",
+                            "JoinColumnIndexes": "L:1,L:1,L:4,L:2,R:1",
                             "JoinVars": {
                               "o_orderkey": 0
                             },
@@ -688,8 +688,8 @@ Gen4 error: aggregation on columns from different sources not supported yet
                                   "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",
+                                "FieldQuery": "select 1, sum(l_extendedprice * (1 - l_discount)) as revenue from lineitem where 1 != 1 group by 1",
+                                "Query": "select 1, sum(l_extendedprice * (1 - l_discount)) as revenue from lineitem where l_returnflag = 'R' and l_orderkey = :o_orderkey group by 1",
                                 "Table": "lineitem",
                                 "Values": [
                                   ":o_orderkey"
@@ -998,7 +998,7 @@ Gen4 error: unsupported: group by on: *planbuilder.joinGen4
           {
             "OperatorType": "Join",
             "Variant": "Join",
-            "JoinColumnIndexes": "L:4,R:0",
+            "JoinColumnIndexes": "L:4,R:1",
             "JoinVars": {
               "l_partkey": 0,
               "l_quantity": 1,
@@ -1025,8 +1025,8 @@ Gen4 error: unsupported: group by on: *planbuilder.joinGen4
                   "Name": "main",
                   "Sharded": true
                 },
-                "FieldQuery": "select count(*) from part where 1 != 1",
-                "Query": "select count(*) from part where p_partkey = :l_partkey and p_brand = 'Brand#12' and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') and :l_quantity \u003e= 1 and :l_quantity \u003c= 1 + 10 and p_size between 1 and 5 and :l_shipmode in ('AIR', 'AIR REG') and :l_shipinstruct = 'DELIVER IN PERSON' or p_partkey = :l_partkey and p_brand = 'Brand#23' and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') and :l_quantity \u003e= 10 and :l_quantity \u003c= 10 + 10 and p_size between 1 and 10 and :l_shipmode in ('AIR', 'AIR REG') and :l_shipinstruct = 'DELIVER IN PERSON' or p_partkey = :l_partkey and p_brand = 'Brand#34' and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') and :l_quantity \u003e= 20 and :l_quantity \u003c= 20 + 10 and p_size between 1 and 15 and :l_shipmode in ('AIR', 'AIR REG') and :l_shipinstruct = 'DELIVER IN PERSON'",
+                "FieldQuery": "select 1, count(*) from part where 1 != 1 group by 1",
+                "Query": "select 1, count(*) from part where p_partkey = :l_partkey and p_brand = 'Brand#12' and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') and :l_quantity \u003e= 1 and :l_quantity \u003c= 1 + 10 and p_size between 1 and 5 and :l_shipmode in ('AIR', 'AIR REG') and :l_shipinstruct = 'DELIVER IN PERSON' or p_partkey = :l_partkey and p_brand = 'Brand#23' and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') and :l_quantity \u003e= 10 and :l_quantity \u003c= 10 + 10 and p_size between 1 and 10 and :l_shipmode in ('AIR', 'AIR REG') and :l_shipinstruct = 'DELIVER IN PERSON' or p_partkey = :l_partkey and p_brand = 'Brand#34' and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') and :l_quantity \u003e= 20 and :l_quantity \u003c= 20 + 10 and p_size between 1 and 15 and :l_shipmode in ('AIR', 'AIR REG') and :l_shipinstruct = 'DELIVER IN PERSON' group by 1",
                 "Table": "part"
               }
             ]
@@ -1089,7 +1089,7 @@ Gen4 error: unsupported: cross-shard correlated subquery
                           {
                             "OperatorType": "Join",
                             "Variant": "Join",
-                            "JoinColumnIndexes": "L:1,L:1,L:4,L:2,R:0",
+                            "JoinColumnIndexes": "L:1,L:1,L:4,L:2,R:1",
                             "JoinVars": {
                               "l1_l_orderkey": 0
                             },
@@ -1113,8 +1113,8 @@ Gen4 error: unsupported: cross-shard correlated subquery
                                   "Name": "main",
                                   "Sharded": true
                                 },
-                                "FieldQuery": "select count(*) as numwait from orders where 1 != 1",
-                                "Query": "select count(*) as numwait from orders where o_orderstatus = 'F' and exists (select 1 from lineitem as l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey != l1.l_suppkey limit 1) and not exists (select 1 from lineitem as l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey != l1.l_suppkey and l3.l_receiptdate \u003e l3.l_commitdate limit 1) and o_orderkey = :l1_l_orderkey",
+                                "FieldQuery": "select 1, count(*) as numwait from orders where 1 != 1 group by 1",
+                                "Query": "select 1, count(*) as numwait from orders where o_orderstatus = 'F' and exists (select 1 from lineitem as l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey != l1.l_suppkey limit 1) and not exists (select 1 from lineitem as l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey != l1.l_suppkey and l3.l_receiptdate \u003e l3.l_commitdate limit 1) and o_orderkey = :l1_l_orderkey group by 1",
                                 "Table": "orders",
                                 "Values": [
                                   ":l1_l_orderkey"
@@ -1126,7 +1126,7 @@ Gen4 error: unsupported: cross-shard correlated subquery
                           {
                             "OperatorType": "Join",
                             "Variant": "Join",
-                            "JoinColumnIndexes": "L:3,L:4,L:1,R:0",
+                            "JoinColumnIndexes": "L:3,L:4,L:1,R:1",
                             "JoinVars": {
                               "s_nationkey": 0
                             },
@@ -1154,8 +1154,8 @@ Gen4 error: unsupported: cross-shard correlated subquery
                                   "Name": "main",
                                   "Sharded": true
                                 },
-                                "FieldQuery": "select count(*) as numwait from nation where 1 != 1",
-                                "Query": "select count(*) as numwait from nation where n_name = 'SAUDI ARABIA' and exists (select 1 from lineitem as l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey != l1.l_suppkey limit 1) and not exists (select 1 from lineitem as l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey != l1.l_suppkey and l3.l_receiptdate \u003e l3.l_commitdate limit 1) and n_nationkey = :s_nationkey",
+                                "FieldQuery": "select 1, count(*) as numwait from nation where 1 != 1 group by 1",
+                                "Query": "select 1, count(*) as numwait from nation where n_name = 'SAUDI ARABIA' and exists (select 1 from lineitem as l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey != l1.l_suppkey limit 1) and not exists (select 1 from lineitem as l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey != l1.l_suppkey and l3.l_receiptdate \u003e l3.l_commitdate limit 1) and n_nationkey = :s_nationkey group by 1",
                                 "Table": "nation",
                                 "Values": [
                                   ":s_nationkey"