Teiid is returning null for query which has concatenation expression if one of the field used in concatenation operation is null

[rujutas]

Hi team ,

We observed Teiid is returning null result for query which has concatenation expression if one of the field used in concatenation operation is null.

I am using teiid-9.1.3 version.

My Query: It should provide AddressLine1 , AddressLine2 ,Address Line 3 and concatenation of all  (AddressLine1+ AddressLine2+Address Line 3)

SELECT ETL_ORACLE.DQORA.et_hospital.ADDRESS_LINE_1,ETL_ORACLE.DQORA.et_hospital.ADDRESS_LINE_2, ETL_ORACLE.DQORA.et_hospital.ADDRESS_LINE_3,CONCAT( ETL_ORACLE.DQORA.et_hospital.ADDRESS_LINE_1,CONCAT(' ', CONCAT( ETL_ORACLE.DQORA.et_hospital.ADDRESS_LINE_2,CONCAT(' ', ETL_ORACLE.DQORA.et_hospital.ADDRESS_LINE_3)))) FROM   ETL_ORACLE.DQORA.et_patient   INNER JOIN ETL_ORACLE.DQORA.et_visit ON ETL_ORACLE.DQORA.et_patient.PATIENT_ID = ETL_ORACLE.DQORA.et_visit.PATIENT_ID   INNER JOIN ETL_ORACLE.DQORA.et_hospital ON ETL_ORACLE.DQORA.et_visit.HOSPITAL_ID = ETL_ORACLE.DQORA.et_hospital.HOSPITAL_ID   INNER JOIN ETL_ORACLE.DQORA.et_doctor ON ETL_ORACLE.DQORA.et_visit.DOCTOR_ID = ETL_ORACLE.DQORA.et_doctor.DOCTOR_ID WHERE   ETL_ORACLE.DQORA.et_patient.id <= '10000' AND ETL_ORACLE.DQORA.et_visit.id <= '10000' limit 1500;

Result:

ADDRESS_LINE_1, ADDRESS_LINE_2, ADDRESS_LINE_3, expr,

1: VALLEY, WEST, CENTER ,VALLEY WEST CENTER

2: WASHINGTON ,TOWN ,HALL ,WASHINGTON TOWN HALL

3: RUBIX, CENTER, null, null

4: ROCKFELLER,GARDEN,null,null

5: KEVIN,HEIGHT,null,null

Regards,

Rujuta S

[shawkins]

> We observed Teiid is returning null result for query which has concatenation expression if one of the field used in concatenation operation is null.

That is the expected / ANSI SQL behavior.  There is a concat2 function that will treat null as effectively the empty string.