Inconsistent handling of NULL fields in mergeColumns

[goughy000]

Search first

• I searched and no similar issues were found

Description

When running the MergeColumns change type, the end result differs depending on the DBMS in use, sometimes resulting in data loss when one of the columns to be merged contains NULL.

The documentation doesn't mention any warning around this, and the example SQL shown uses the CONCAT_WS function. However on SQL Server, the concatenation statement generated by Liquibase uses the + operator, where the output is NULL when one of the input columns is NULL.

Comparing to MySQL, H2, where the CONCAT function is used and NULL in one of the columns does not result in NULL in the end result

Steps To Reproduce

Changelog used:

<?xml version="1.0" encoding="UTF-8"?>  
<databaseChangeLog  
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"  
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
  xmlns:pro="http://www.liquibase.org/xml/ns/pro"  
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.4.xsd
      http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.5.xsd">
    <changeSet author="jack.g" id="001-prereqs">
        <createTable tableName="names">
            <column name="firstname" type="VARCHAR(255)"/>
            <column name="lastname" type="VARCHAR(255)"/>
        </createTable>
        <insert tableName="names">
            <column name="firstname" value="Alice" />
        </insert>
        <insert tableName="names">
            <column name="firstname" value="Bob" />
            <column name="lastname" value="Bobberson" />
        </insert>
        <insert tableName="names">
            <column name="lastname" value="Christmas" />
        </insert>
    </changeSet>
    <changeSet author="jack.g" id="002-merge">
        <mergeColumns
            tableName="names"
            column1Name="firstname"
            column2Name="lastname"
            finalColumnName="fullname"
            finalColumnType="varchar(255)"
            joinString=" "
            />
    </changeSet>
</databaseChangeLog>

SQL Generated and ran by Liquibase:

MySQL:

UPDATE liquibase.names SET fullname = CONCAT_WS(' ', firstname, lastname)

SQL Server:

UPDATE names SET fullname = firstname + ' ' + lastname;

Which results in the following final data in the table:

MySQL:

|fullname     |
|-------------|
|Alice        |
|Bob Bobberson|
|Christmas    |

SQL Server:

|fullname     |
|-------------|
|             |
|Bob Bobberson|
|             |

Expected/Desired Behavior

Expected behaviour is either

1. The MergeColumns change type treats null values consistently across DBMS vendors (i.e. always uses a CONCAT style function to treat NULL as an empty string)

OR

2. The documentation is updated to reflect that this behaviour differs per DBMS

Liquibase Version

4.27.0

Database Vendor & Version

Microsoft Azure SQL Edge Developer (RTM) - 15.0.2000.1574 (ARM64)

Liquibase Integration

Docker

Liquibase Extensions

No response

OS and/or Infrastructure Type/Provider

No response

Additional Context

I'm happy to submit a PR either way or any other suggestions?

Are you willing to submit a PR?

• I'm willing to submit a PR (Thank you!)

[tati-qalified]

Hi @goughy000, thank you for reporting this issue. I agree that the behaviour should be consistent, and I believe this is more a program bug than a documentation error.

Here is the generated SQL script for a few different databases:

DB	Statement
H2	UPDATE PUBLIC.names SET fullname = CONCAT(firstname, CONCAT(' ', lastname));
MSSQL	UPDATE names SET fullname = firstname + ' ' + lastname;
MySQL	UPDATE lbcat.names SET fullname = CONCAT_WS(' ', firstname, lastname);
PostgreSQL	UPDATE public.names SET fullname = firstname

Out of these, only MSSQL isn't using string concatenation, so that is where the code should be modified.
I see that you're willing to submit a PR - our development team is available to provide guidance if needed, so feel free to create the PR and ask any questions you may have.

Thank you,
Tatiana