Concat expression might present inconsistent behavior

[rlaiola]

What is this issue about?

The concat function (expression) may present an inconsistent behavior. It seems that it is possible to have a non-string argument only if it is listed as the very first one. Otherwise, an error message is displayed.

NOTE: To test the examples refer to http://dbis-uibk.github.io/relax/calc/local/uibk/local/0

• Example 1: concat expression with only one non-string argument

pi concat(a)->x (R)

or

SELECT concat(a) AS x FROM R;

results in

x
'1'
'3'
'4'
'5'
'6'

• Example 2: concat expression with multiple non-string arguments

pi concat(a, a)->x (R)

or

SELECT concat(a, a) AS x FROM R;

results in

Error: at line 1: CONCAT() expects all arguments to be of the same type

• Example 3: concatenate non-string with string arguments (non-string as the first listed)

pi concat(a, b, c)->x (R)

or

SELECT concat(a, b, c) AS x FROM R;

results in

x
'1ad'
'3cc'
'4df'
'5db'
'6ef'

• Example 4: concatenate string with non-string argument

pi concat(b, a)->x (R)

or

SELECT concat(b, a) AS x FROM R;

results in

Error: at line 1: CONCAT() expects all arguments to be of the same type

Bottom line: If a non-string argument is listed as the first in the concat expression it is implicitly converted to string and then concatenated. In case a non-string argument shows up in another position, an error message is displayed.

How is this implemented on RDBMS?

• MySQL (since version 5.1)

CONCAT(str1,str2,...): Returns the string that results from concatenating the arguments. May have one or more arguments. If all arguments are nonbinary strings, the result is a nonbinary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent nonbinary string form. CONCAT() returns NULL if any argument is NULL.

• PosgreSQL (since version 9.1)

concat ( val1 "any" [, val2 "any" [, ...] ] ) → text: Concatenates the text representations of all the arguments. NULL arguments are ignored.

• SQL Server (since version 2012)

CONCAT ( string_value1, string_value2 [, string_valueN ] ): CONCAT takes a variable number of string arguments and concatenates them into a single string. It requires a minimum of two input values; otherwise, an error is raised. All arguments are implicitly converted to string types and then concatenated. Null values are implicitly converted to an empty string. If all the arguments are null, an empty string of type varchar(1) is returned.

References:

• https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_concat
• https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_concat
• https://www.postgresql.org/docs/9.1/functions-string.html
• https://www.postgresql.org/docs/15/functions-string.html
• https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2012/hh231515(v=sql.110)
• https://learn.microsoft.com/en-us/sql/t-sql/functions/concat-transact-sql?view=sql-server-ver16
• https://www.oreilly.com/library/view/sql-in-a/9780596155322/re92.html