WIP Fix concat function definition for SybaseSQLAnywhere10Dialect

[hazzik]

[bahusoid]

From https://github.com/nhibernate/nhibernate-core/pull/2966#discussion_r777159675:

• behaves differently than || and requires explicit conversion to string first. It should be ||, but then definition becomes the same as in base dialect. So I just removed the overload.

Yes it behaves differently, but why you think it's incorrect behavior.

From what I see in docs https://www.sqlines.com/sybase-asa/functions/string. With ||

NULL value in any parameter is treated as '' (empty string):

And with +

If any value is NULL, the result is NULL

Maybe + was used for more consistent behavior across dialects as most of them return NULL if any param value is NULL

[hazzik]

From what I see in docs

The behavior of || is consistent and expected to what we need.

but why you think it's incorrect behavior.

Because of this: SELECT '1' + '2' + 3 would be 15.000000. Wait... What?

And SELECT '1' || '2' || 3 would be '123', and this is exactly how concat should behave.

So, basically, to make + work we need to cast all it's arguments to strings, and convert NULLs to '' (the cast does this).

[hazzik]

and this is exactly how concat should behave.

Or maybe not... Let's see. I've added test to check with other DBs.

[hazzik]

That's kind-of unexpected really.