sql: refactor casts to be more like postgresql

[otan]

Our current method of handling casts and operators are a bit of a mess.

Casts are defined in a massive switch statement, and their volatility in a separate map (validCasts).

In large parts, AdjustValueFor[Column]Type should be folded into here.

We should refactor casts and parse to be more like Postgres: there should be a map that roughly goes map[fromType oid.Oid]map[toType oid.Oid]castOps that defines casts from and to. They should be able to re-use the parse function -- strings should automatically be populated into these cast maps using the parse definitions. In postgres, this is handled by a <datatype>_in command.

Doing this will also greatly simplify adding new types.

Jira issue: CRDB-3696

[mgartner]

@otan I'm considering making this refactor to make some upcoming changes to casting easier. I had a couple questions:

there should be a map that roughly goes map[fromType oid.Oid]map[toType oid.Oid]castOps that defines casts from and to

What's the reasoning behind using an OID -> OID map instead of a Family -> Family map? It seems like OID -> OID could cause a lot of duplicate code and boilerplate.

They should be able to re-use the parse function -- strings should automatically be populated into these cast maps using the parse definitions.

Can you explain more what you mean by this?

[otan]

It seems like OID -> OID could cause a lot of duplicate code and boilerplate.

Well, it doesn't if you have implicit/assignment casts :). OID -> OID is most postgres-like, and the Family:Family stuff is a huge mess when you look at the various different times of String-family OIds we have. However, unless we do the type system refactor I think Family:Family makes sense (for now).

They should be able to re-use the parse function -- strings should automatically be populated into these cast maps using the parse definitions.

I phrased this badly, let me rephrase. This is in the context of string casts, which IIUC goes through ParseAndRequireString anyway.

In PG, type I/O input/output functions depend on typin and typout in pg_type which I believe calls into the C code using some dlopen magic: https://github.com/postgres/postgres/blob/537ca68dbb2463f7b1c44e9466b8fbdd7505b2e1/src/backend/utils/adt/int.c#L265-L283

What I believe I meant was that the casts should re-use the ParseD.... functions to most strongly simulate this, but I think ::string and string:: related casts already have some variation of this magic (which of course is wrong for e.g. char types which fail to truncate or what not).

[mgartner]

Well, it doesn't if you have implicit/assignment casts :)

I don't follow. OID -> OID would require a map that contains casting details for each OID pair. Family -> Family would by definition be a smaller map.

However, unless we do the type system refactor I think Family:Family makes sense (for now).

Sounds good, I'll give it a shot.

[otan]

Family -> Family would by definition be a smaller map.

smaller map, a lot more if conditions based on oids ;)

[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!