Combine length/precision/scale/collation... from both operands in type inference

[roji]

When e.g. a concatenation operation happens over two string operands which have a type mapping (e.g. two columns), the left one is arbitrarily picked; this means that the inferred type mapping for [p].[ThreeCharacterProperty] + ';' + [p].[FiveCharacterProperty] will be char(3). This is incorrect and can cause bugs such as #32325, where the inferred type mapping is applied to the result of OPENJSON, and therefore incorrectly truncates.

We should improve our type mapping inference logic to take facets into account: in this case we'd add the MaxLength facets together for the resulting MaxLength (3 + 1 = 5 = 9). This requires some thinking since different logic applies e.g. for MaxLength and for Precision/Scale, etc. Also, we need to have logic handilng concatenation with constant/parameter - for the former we can just add the constant's length, but for the latter we'd need to switch to e.g. nvarchar(max).

Note the relationship with #15586, which is about a compatibility chart between different types; this issue is only about proper handling of facets on the same base type.

[carden-jeremy]

A minor correction:
Instead of MaxLength (3 + 5 = 8)
It should be MaxLength (3 + 1 + 5 = 9) because there is a semicolon in that concatenation.

I confirmed this by running the query in SSMS with char(8) and got no results, char(9) got the expected results.

[roji]

Thanks, fixed.

[roji]

Note - this should include bubbling up the collation, once it's part of the type mapping (#29620). Note that in my early investigations, all databases fail once there are two explicit collations involved (since it's impossible to know which one to use), but it's a good idea to confirm this.

[clement911]

I find the sp_describe_first_result_set procedure quite useful for this.

Given an arbitrary statement, it shows the exact resulting type that sql server has inferred, including the max length, precision, scale and collation.

Example:

This might be useful for some integration tests that compare the EF inferred type with the SQL inferred type.