sql: identity information under information_schema.columns is inconsistent with postgres

[hedwigz]

Describe the problem
The table information_schema.columns should reflect the information about an identity column and the underlying sequence information.

To Reproduce
cockroach:

CREATE TABLE t (
  id INT8 NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 4294967296),
  PRIMARY KEY (id)
);

=> select identity_start, identity_increment, identity_maximum, identity_minimum, identity_cycle from information_schema.columns where table_name = 't' and column_name='id';
 identity_start | identity_increment | identity_maximum | identity_minimum | identity_cycle 
----------------+--------------------+------------------+------------------+----------------
                |                    |                  |                  |  
(1 row)

postgres:

=> CREATE TABLE t (
  id INT8 NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 4294967296),
  PRIMARY KEY (id)
);

=> select identity_start, identity_increment, identity_maximum, identity_minimum, identity_cycle from information_schema.columns where table_name = 't' and column_name='id';
 identity_start | identity_increment |  identity_maximum   | identity_minimum | identity_cycle 
----------------+--------------------+---------------------+------------------+----------------
 4294967296     | 1                  | 9223372036854775807 | 1                | NO 
(1 row)

Expected behavior
The above select statement should return the information about the sequence

Environment:

• CockroachDB version: v21.2.11
• Server OS: Linux
• Client app: DataGrip

Jira issue: CRDB-16195
Epic CRDB-14049

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I was unable to automatically find someone to ping.

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

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

[rafiss]

looks like we have a TODO about this:

cockroach/pkg/sql/information_schema.go

Lines 519 to 525 in b6f42c2

	// TODO(janexing): parse the GeneratedAsIdentitySequenceOption to
	// fill out these "identity_x" columns.
	tree.DNull, // identity_start
	tree.DNull, // identity_increment
	tree.DNull, // identity_maximum
	tree.DNull, // identity_minimum
	tree.DNull, // identity_cycle

[ZhouXing19]

To solve this we may need to solve #83208 first. I'll open a PR for it.