sql: store column IDs in index predicates instead of column names

[mgartner]

Storing column names in partial index predicates (like CREATE INDEX ... WHERE s = 'foo') has disadvantages, such as maintaining code to handle column renames.

Storing column IDs instead (like `CREATE INDEX ... WHERE @11 = 'foo') would eliminate this issue.

Note that we'd need to convert these column IDs to column names whenever the predicate is shown to users.

More context: #49672 (comment)

Jira issue: CRDB-4199

[jordanlewis]

Some things that I've been talking over with @RichardJCai in this regard:

1. Not relevant here, but for views, we can't just store a column ordinal - we have to also store a table id. So whatever we do, we should make sure to make the syntax for this usable in both situations or risk having extra serialization formats.
2. Truncating a table bumps its table id. Maybe also not relevant here.
3. Column IDs can be modified by alter column type, which replaces a column with another. It's important to use the logical column ID, which should be stable in the face of this kind of operation.

There may be other gotchas. Anything else you noticed, Richard?

[rohany]

@mgartner, also index predicates will need to be using this sort of stable representation when there are user defined types in the predicate -- see #49565. I'll probably be doing the change, but just to keep it on your radar.

[RichardJCai]

Some things that I've been talking over with @RichardJCai in this regard:

1. Not relevant here, but for views, we can't just store a column ordinal - we have to also store a table id. So whatever we do, we should make sure to make the syntax for this usable in both situations or risk having extra serialization formats.
2. Truncating a table bumps its table id. Maybe also not relevant here.
3. Column IDs can be modified by alter column type, which replaces a column with another. It's important to use the logical column ID, which should be stable in the face of this kind of operation.

There may be other gotchas. Anything else you noticed, Richard?

No I think you've covered everything. I don't think table id will be too difficult to update right?

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
10 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!