MS SQL CONCAT vs "+" query difference (with Doctrine 2)

Question

I am using the Doctrine 2 interface to access MS SQL database. I found the problem that is pretty interesting on both sides: Doctrine 2 and the MS SQL.

Doctrine 2 for some reason changing query

$this->getEntityManager()->createQueryBuilder()
            ->from(table::class, 'il')
            ->select('CONCAT(il.col1,il.col2,il.col3) AS concated')

changing to something like (it is using own aliases):

SELECT (t0_.col1+ t0_.col2+ t0_.col3) AS sclr_0 FROM tableName t0_

The most important - it changing the CONCAT into expression with "+". But it works fine (or it looks like it does) till I would like to compare concated string to value in other table...

$targetQueryBuilder->getEntityManager()->createQueryBuilder()
            ->from($namespace, 's')
            ->select('1')
            ->where('CONCAT(il1.col1, il1.col2, il1.col3) = s.controlValue')
            ->andWhere('s.tableName = :tablename')
            ->andWhere('s.tableRowIdentifier='.$rowIdentifier);

(I am writting this query from memory, if there is a something wrong - it doesn't matter - it is to visualise the issue) Executing raw query in SQL Studio works absolutely fine with CONCAT function. With "+" it doesn't. All concated columns having nvarchar type. The question is: Where is a difference?

I checked a huge amount of combinations in doctrine to skip mapping CONCAT into "+" but no success, I had to wrote some dummy lexer extension to force the CONCAT function to be applied to the final query.

Additional (maybe ms sql server specific) issue is: When I was testing

SELECT
(i1_.NameUnternehmen + i1_.Land + i1_.Ort + i1_.Straße + i1_.Postleitzahl) AS a,
CONCAT(i1_.NameUnternehmen , i1_.Land , i1_.Ort , i1_.Straße , i1_.Postleitzahl) AS c
FROM tablename

(the Straße colum name looks like is not important in this issue) When using the WHERE statement to compare concated string with the control value, a select result is different.

->where('CONCAT(il1.col1, il1.col2, il1.col3) = s.controlValue') - everything is correct

->where('(il1.col1 + il1.col2 + il1.col3) = s.controlValue') - result in column "a" is null. I just tested and on other tables it is not null sometimes. I don't know why. The "c" column is always correct.

Answer 1

CONCAT and + don't operate the same. If any expression has the value NULL when using +, then NULL will be returned. For CONCAT, it will implicitly replace NULL with '', so NULL won't be returned.

CONCAT will also implicitly convert any values in the expression to an (n)varchar, where as + uses Data Type Precedence to determine the datatype and implicitly converts other values in the expression to the data type of the highest precedence.

Examples:

SELECT c1,
       c2,
       c3,
       c4,
       c1 + c2 + c3 + c4, --NULL 
       CONCAT(c1, c2, c3, c4) --'abcdefxyc'
FROM (VALUES('abc','def',NULL,'xyz'))V(c1, c2, c3, c4);

Implicit conversions:

SELECT c1 + c2 + c3 + c4
FROM (VALUES('abc','def',7,'xyz'))V(c1, c2, c3, c4);

Msg 245, Level 16, State 1, Line 9
Conversion failed when converting the varchar value 'abcdef' to data type int.

SELECT CONCAT(c1, c2, c3, c4) --'abcdef7xyz'
FROM (VALUES('abc','def',7,'xyz'))V(c1, c2, c3, c4);

Interesting but expected behaviours:

SELECT c1 + c2 + c3 + c4 --NULL
FROM (VALUES('abc','def',NULL,7))V(c1, c2, c3, c4);

SELECT c1 + c2 + c3 + c4 --123456790
FROM (VALUES('123','456','789',1))V(c1, c2, c3, c4);

The reason for this behaviour is because the expressions are evaluated left to right. For the first, you therefore have 'abc' + 'def' = 'abcdef' then 'abcdef' + NULL = NULL (varchar). Finally you have NULL + 7 = NULL, which is fine as NULL can be implicitly converted to an int.

For the latter, you have '123' + '456' = '123456' then '123456' + '789' = '123456789'. Finally you have '123456789' + 1 = 123456789 + 1 = 123456790. notice in the final expression the varchar is first implicitly cast to an int, and then the + operator acts as addition, rather than concatenation.