Change character literal treatment and char/varchar coercion

[findepi]

• '...' literals should be of type char(n), not varchar(n) as they are today
• char(n) should coerce to varchar(n), not the other way around
  • this effectively reverts 3d9d004, 7662f71, but the change to character literals should address same needs.
  • this also fixes the problem with varchar(n > 65536) being coerced to char(65536) which is lossy (Incorrect query results when comparing char values with varchar values longer than 65536 #9030)

to be considered

• what should be the type of VARCHAR 'abc'? Currently it's unbounded varchar, but maybe it should be varchar(3) so that there is a concise literal form for varchar(n) values.
• what about literals with length exceeding CharType.MAX_LENGTH (65536)?

Follows https://github.com/trinodb/trino/pull/8984/files#diff-b2201e0a7ed30a2cb81a3672435281f5822574fd25e127010e3aaa58ee62b04dR483

cc @martint @sopel39 @kasiafi @losipiuk @weiatwork

[sopel39]

Also very importantly, current comparison semantics (trailing space insensitive between char/varchars) should be preserved. Some old design doc about it: https://docs.google.com/document/d/1UngGirjV_I7nPnIGU6G7SDS0cY8j7N_Jb3Wk3EoHChc/edit?usp=sharing.

[findepi]

Some old design doc about it: https://docs.google.com/document/d/1UngGirjV_I7nPnIGU6G7SDS0cY8j7N_Jb3Wk3EoHChc/edit?usp=sharing.

The doc seem to focus on INSERT, which today is handled explicitly see #2061 cc @kasiafi

@sopel39 Are there other parts of this doc, which i should still read carefully?

[sopel39]

The doc seem to focus on INSERT, which today is handled explicitly see #2061 cc @kasiafi

There is section about COMPARISON_ARGUMENT<T1,T2,X>

[martint]

Related: #4394

[cshao239]

More on this topic, Concat previously can take free combinations of char(n) and varchar within the limit of the number of parameters. In ConcatFunction.java class, Trino constructs a FunctionMetadata with signature that has input type of varchar, and output type of varchar, and the number of inputs can be varied, and not exceeding 254. In SignatureBinder.java, expandVarargFormalTypeSignature method would generate 253 (2,3,4,....254) different TypeSignatures for inputs and binding to functions.
If we want to simulate the functionality of char -> varchar auto coercion by adding more function signature, we will have to create (65538^254 + 65538^253 + 65538^252 + ..... 65538^2) signatures for concat function: each of the parameter could be varchar, char(0,1,2...65536), total of 65538 possibilities; and there are 253 different lengths of arguments (2, 3, .... 254). From the memory point view, This is not a good idea. Plus, in order to implement this, significant refactoring is needed to support such Type-Interpolation in Signature class.
So it would be better to bring the char -> varchar auto coercion back