sql: support CREATE TABLE ... LIKE for tables with hidden columns

[mgartner]

The last two statements in the logic tests below fail with column "crdb_region" does not exist. It probably is caused an incorrect assumption here that the system will recreate the crdb_region column, but it does not.:

cockroach/pkg/sql/create_table.go

Lines 2557 to 2561 in 1e0aaa5

	if c.Hidden {
	// Hidden columns automatically get added by the system; we don't need
	// to add them ourselves here.
	continue
	}

statement ok
CREATE DATABASE multi_region_test_db PRIMARY REGION "ca-central-1" REGIONS "ap-southeast-2", "us-east-1" SURVIVE REGION FAILURE

statement ok
USE multi_region_test_db

statement ok
CREATE TABLE regional_by_row_table (
  pk int PRIMARY KEY,
  a int NOT NULL,
  b int NOT NULL,
  j JSON,
  INDEX (a),
  UNIQUE (b),
  INVERTED INDEX (j),
  FAMILY (pk, a, b)
) LOCALITY REGIONAL BY ROW

statement ok
CREATE TABLE copy_indexes (LIKE regional_by_row_table INCLUDING INDEXES)

statement ok
CREATE TABLE copy_all (LIKE regional_by_row_table INCLUDING ALL)

this is also broken for implicit rowid tables, when used with indexes:

[email protected]:26257/defaultdb> create table tbl ();
CREATE TABLE


Time: 6ms total (execution 6ms / network 0ms)

[email protected]:26257/defaultdb> create index on tbl(rowid);
CREATE INDEX


Time: 202ms total (execution 202ms / network 0ms)

[email protected]:26257/defaultdb> CREATE TABLE copy_all (LIKE tbl INCLUDING ALL)
                             -> ;
ERROR: column "rowid" does not exist
SQLSTATE: 42703

or even just HIDDEN columns in general:

[email protected]:26257/defaultdb> create table tbl2 (pk int not visible, primary key (pk));
CREATE TABLE


Time: 19ms total (execution 19ms / network 0ms)

[email protected]:26257/defaultdb> create table tbl2_copy (like tbl2);
CREATE TABLE


Time: 9ms total (execution 9ms / network 0ms)

[email protected]:26257/defaultdb> show create table tbl2_copy;
  table_name |                      create_statement
-------------+--------------------------------------------------------------
  tbl2_copy  | CREATE TABLE public.tbl2_copy (
             |     rowid INT8 NOT VISIBLE NOT NULL DEFAULT unique_rowid(),
             |     CONSTRAINT "primary" PRIMARY KEY (rowid ASC),
             |     FAMILY "primary" (rowid)
             | )
(1 row)


Time: 37ms total (execution 36ms / network 1ms

• Should we copy hidden columns when using LIKE?
• Should we copy localities for LIKE?

Epic CRDB-7217

[mgartner]

I think there's a bigger picture question to answer first: What is the expected behavior of CREATE TABLE ... LIKE on a regional by row or partitioned table?

By default, CREATE TABLE ... LIKE only copies column names, their types, and their not null constraints. It only copies constraints, computed column expressions, and indexes if you specify options like INCLUDING CONSTRAINTS, INCLUDING GENERATED, INCLUDING INDEXES, INCLUDING ALL (more details in the PG docs).

To be consisten, the locality should probably not be copied by default. It currently is copied, but incorrectly:

statement ok
CREATE DATABASE multi_region_test_db PRIMARY REGION "ca-central-1" REGIONS "ap-southeast-2", "us-east-1" SURVIVE REGION FAILURE

statement ok
USE multi_region_test_db

statement ok
CREATE TABLE regional_by_row_table (
  pk int PRIMARY KEY,
  a int NOT NULL,
  b int NOT NULL,
  j JSON,
  INDEX (a),
  UNIQUE (b),
  INVERTED INDEX (j),
  FAMILY (pk, a, b)
) LOCALITY REGIONAL BY ROW

statement ok
CREATE TABLE copy (LIKE regional_by_row_table)

query T
SELECT create_statement FROM [SHOW CREATE TABLE copy]
----
CREATE TABLE public.copy (
  pk INT8 NOT NULL,
  a INT8 NOT NULL,
  b INT8 NOT NULL,
  j JSONB NULL,
  rowid INT8 NOT VISIBLE NOT NULL DEFAULT unique_rowid(),
  CONSTRAINT "primary" PRIMARY KEY (rowid ASC),
  FAMILY "primary" (pk, a, b, j, rowid)
) LOCALITY REGIONAL BY TABLE IN PRIMARY REGION

We could introduce a new option to opt into copying the locality from the source table, INCLUDING LOCALITY:

CREATE TABLE copy (LIKE regional_by_row_table INCLUDING LOCALITY)

[otan]

@awoods187 what is the desired behaviour here?

• should the LIKE copy localities?
• should the LIKE copy over hidden columns, like rowid and crdb_region?

[otan]

not copying hidden columns seems pretty broken to me. for example, implicit rowid columns are broken too:

[email protected]:26257/defaultdb> create table tbl ();
CREATE TABLE


Time: 6ms total (execution 6ms / network 0ms)

[email protected]:26257/defaultdb> create index on tbl(rowid);
CREATE INDEX


Time: 202ms total (execution 202ms / network 0ms)

[email protected]:26257/defaultdb> CREATE TABLE copy_all (LIKE tbl INCLUDING ALL)
                             -> ;
ERROR: column "rowid" does not exist
SQLSTATE: 42703

[otan]

cc @vy-ton i think this is a more general SQL experience issue ^

[awoods187]

It seems like we'd want to copy hidden columns since the contract is to create the same semantics of the table. If someone just wants a table without everything being the same, they can use CREATE TABLE AS...