system.public.table_statistics table definition has discrepancy after upgrading from a old version

[Xiang-Gu]

When we upgrade CRDB binary version running in nodes in a cluster, migration logic will be triggered to make sure tables in system database are "consistent" (i.e. same definition) as that of a newly bootstrapped cluster.

A recent roachtest showed there is discrepancy in the system.public.table_statistics table. Namely, the actual return of CREATE TABLE after upgrading is`

	CREATE TABLE public.table_statistics (	
	"tableID" INT8 NOT NULL,	
	"statisticID" INT8 NOT NULL DEFAULT unique_rowid(),	
	name STRING NULL,	
	"columnIDs" INT8[] NOT NULL,	
	"createdAt" TIMESTAMP NOT NULL DEFAULT now():::TIMESTAMP,	
	"rowCount" INT8 NOT NULL,	
	"distinctCount" INT8 NOT NULL,	
	"nullCount" INT8 NOT NULL,	
	histogram BYTES NULL,	
	"avgSize" INT8 NOT NULL DEFAULT 0:::INT8,	
	CONSTRAINT "primary" PRIMARY KEY ("tableID" ASC, "statisticID" ASC),	
	FAMILY "fam_0_tableID_statisticID_name_columnIDs_createdAt_rowCount_distinctCount_nullCount_histogram" ("tableID", "statisticID", name, "columnIDs", "createdAt", "rowCount", "distinctCount", "nullCount", histogram),	
	FAMILY "fam_10_avgSize" ("avgSize")	
);

while the expected output (as hard-coded in systemschema) is

CREATE TABLE public.table_statistics (
	"tableID" INT8 NOT NULL,
	"statisticID" INT8 NOT NULL DEFAULT unique_rowid(),
	name STRING NULL,
	"columnIDs" INT8[] NOT NULL,
	"createdAt" TIMESTAMP NOT NULL DEFAULT now():::TIMESTAMP,
	"rowCount" INT8 NOT NULL,
	"distinctCount" INT8 NOT NULL,
	"nullCount" INT8 NOT NULL,
	"avgSize" INT8 NOT NULL DEFAULT 0:::INT8,
	histogram BYTES NULL,
	CONSTRAINT "primary" PRIMARY KEY ("tableID" ASC, "statisticID" ASC),
	FAMILY "fam_0_tableID_statisticID_name_columnIDs_createdAt_rowCount_distinctCount_nullCount_avgSize_histogram" ("tableID", "statisticID", name, "columnIDs", "createdAt", "rowCount", "distinctCount", "nullCount", "avgSize", histogram)
);

Two things need to be done to close this issue:

1. Change systemschema to make sure the ordering of the last two columns are corrected;
2. Investigate and make sure the newly added column (avgSize) does not have a family of its own. Instead, it should be placed into the existing family.

Jira issue: CRDB-13880

[blathers-crl]

Hi @Xiang-Gu, please add branch-* labels to identify which branch(es) this release-blocker affects.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[rytaft]

@rharding6373 I'm giving you this one since it relates to avgSize