sql: match Postgres type conversion behavior

[mgartner]

The behavior of type conversions in CockroachDB behaves differently than Postgres in a handful of cases. The Postgres docs on type conversion are an excellent starting place for understanding how Postgres makes type conversion decisions.

Implicit Casts

Implicit casts are casts that are automatically added so that expressions can be strongly typed. For example, implicit casts allow expressions like 1::INT4 = 1::INT2 to be typed.

From the Postgres docs on type conversion:

All type conversion rules are designed with several principles in mind:

• Implicit conversions should never have surprising or unpredictable outcomes.

• There should be no extra overhead in the parser or executor if a query does not need implicit type conversion. That is, if a query is well-formed and the types already match, then the query should execute without spending extra time in the parser and without introducing unnecessary implicit conversion calls in the query.

• Additionally, if a query usually requires an implicit conversion for a function, and if then the user defines a new function with the correct argument types, the parser should use this new function and no longer do implicit conversion to use the old function.

We currently have logic scattered throughout the code base that loosely mimics implicit casts, but has some rough edges. This is a meta issue to track all the work required in order to fully implement implicit casts to behave as they do in Postgres.

Another Example with Collated Strings

We also need to support implicit casts for collated strings. For example, the SQL below executes successfully in PG, but not in CRDB v22.1.

CREATE TABLE t (t TEXT COLLATE "de_DE");

SELECT * FROM t WHERE t = 'x';

Function overload resolution

The logic for determining the overload of a function is documented here. We currently differ from this behavior in several ways.

TODO

• sql: replace type familes with type categories #75102
• sql: correctly type UNION and CASE expressions based on implicit casts #75103
• sql: type string literals as unknown initially #94718
• Correctly resolve overload and types of functions. See the Postgres docs. Related issue: Implicit typecast literals to correct type (postgres compatibility) #38965.
• Only create valid implicit casts in Placeholder.Eval instead of casts to equivalent types (or perhaps don't create casts at all here if not necessary).
• sql: correctly type CASE expressions that result in tuples #78331
• sql: improve type checking error messages with AnnotateTypeExpr of NULL #78335
• sql: allow typed DNull datums #78358
• sql: fix tuple type-checking #98152
• Errors when parsing string literals should propagate correctly, instead of always causing "unsupported comparison operator" errors. See TODOs in sql: remove unnecessary casts in @@ operator type-checking #100704.
• Review the refactoring proposal here: sql: refactor casts to be more like postgresql #55094

Epic CRDB-2474
Jira issue: CRDB-12461

[rafiss]

Adding the O-support label since this issue periodically comes through support channels. For example: #78083, #125025.