Figure out type semantics to use for concatenation and similar cases

[smitpatel]

Suppose we have
var i = 5; //int parameter from closure
c.CustomerID which is mapped in database as nchar(5)
and when you have c.CustomerID + i in expression tree, we try to infer type.

• Should type of parameter i be nchar(5)?
  • what if the value does not fit in length 5?
• What should be type of resulting expression?
  • It cannot be nchar(5) because result of concatenation would be larger than that.
• The translation generates this [c].[CustomerID] + CAST(@__i_0 AS nchar(5)) so we need to figure out exact type to use for casting purposes.

Few ideas around

• Ignore length facets for string?
• Ignore Fixed length too?

[smitpatel]

Related #14555

[roji]

Should type of parameter i be nchar(5)?

• what if the value does not fit in length 5?

I think it makes sense for it to be nchar(5) (regular inference rules no?). If the value doesn't fit, I think the database behavior is generally to truncate, no? If the database behavior is to error instead, that seems OK too...

What should be type of resulting expression?

• It cannot be nchar(5) because result of concatenation would be larger than that.

Similar to the above, seem like EF Core should type this as nchar(5), and the database would do its think (truncate, error or ignore... erroring makes sense to me a bit like int overflow in checked arithmetic).

A quick test on PostgreSQL (|| is the PG string concat operator):

roji=# select 'hel'::char(5) || 'abcd'::char(5);
 ?column?
----------
 helabcd
(1 row)

roji=# select pg_typeof('hel'::char(5) || 'abcd'::char(5));
 pg_typeof
-----------
 text
(1 row)

roji=# select ('hel'::char(5) || 'abcd'::char(5))::char(5);
 bpchar
--------
 helab
(1 row)

So the type of char(5) || char(5) is text (so unbounded), but if you cast a long text to char(5) it silently truncates. I don't think that means that EF Core should implicitly drop length facets though...

[ajcvickers]

@smitpatel to consider more and bring to design meeting.