Liquibase LoadData where Computed value fails

Question

I have a Liqiuibase changeset that uses a CSV file to load data, with the data being computed as the result of a SELECT from other tables.

I am finding that if the other data exists it all works fine, but when it doesn't the computed value returned == NULL and this causes issues on the insert because the table it's doing into is a JOIN table with non-NULL columns.

The Liquibase changeset is as follows:

    <changeSet author="s.davis" id="0000000000053-1">
        <loadData tableName="PRODUCT_V2_REF_PRODUCT_JOURNEY_TYPE"
                        file="db/liquibase/data/product_journey_type_initialisation.csv">
            <column name="PRODUCT_V2_ID" type="computed"/>
            <column name="REF_PRODUCT_JOURNEY_TYPE_ID" type="computed"/>
        </loadData>
    </changeSet>

with the loaded CSV content as follows:

PRODUCT_V2_ID,REF_PRODUCT_JOURNEY_TYPE_ID
(select ID from PRODUCT_V2 where PRODUCT_CODE='no-such-code-so-returns-null-id'),(select ID from REF_PRODUCT_JOURNEY_TYPE where TYPE='Type1')
... other rows

The issue is that the

select ID from PRODUCT_V2 where PRODUCT_CODE='no-such-code-so-returns-null-id'

always returns NULL as the PRODUCT_CODE doesn't exist (I have just done this to show the issue, other products that do exist all work fine).

I have tried a few things such as :

1. Setting a defaultValue, defaultValueComputed and defaultValueNumeric on the column in the changeset
2. Changing the SQL to COALESCE() but given that it's the WHERE clause that's failing rather than the row being there but no data in the column it still returns NULL
3. Setting the "onFail" == "CONTINUE" for a <preConditions> tag inside the changeset

Is there any way to overcome this?

Answer 1

To answer my own question, I ended up changing the SQL in the CSV computed row columns as follows to ensure that a column and value were always returned even if they didn't exist in the DB.

(select ID from PRODUCT_V2 where PRODUCT_CODE='no-such-code-so-returns-null-id' UNION SELECT 0 AS ID LIMIT 1),(select ID from REF_PRODUCT_JOURNEY_TYPE where TYPE='Type1' UNION SELECT 0 AS ID LIMIT 1)