loadData inserts NULL when CSV contains function

[kdebski85]

Environment

Liquibase Version: 4.2.1

Liquibase Integration & Version: SpringLiquibase 4.2.1

Liquibase Extension(s) & Version:

Database Vendor & Version: H2 1.4.200

Operating System Type & Version: Windows 10

Description

When CSV file used for loadData contains a function (for example "CURRENT_TIMESTAMP"), Liquibase tries to insert NULL for that column instead of using that function.

Steps To Reproduce

FOO table:

  <createTable tableName="FOO">
            <column name="CODE" type="VARCHAR(255)">
                <constraints nullable="false"/>
            </column>
            <column name="CHANGE_DATETIME" type="TIMESTAMP">
                <constraints nullable="false"/>
            </column>
   </createTable>

loadData changeset (failing):

        <loadData encoding="UTF-8" file="foo.csv" relativeToChangelogFile="true" quotchar="&quot;" separator="," tableName="FOO">
            <column header="CODE" name="CODE"/>
            <column header="CHANGE_DATETIME" name="CHANGE_DATETIME" type="TIMESTAMP"/>
        </loadData>

foo.csv content:

"CODE","CHANGE_DATETIME"
"ABC","CURRENT_TIMESTAMP"

Actual Behavior

Liquibase tries to insert NULL to "CHANGE_DATETIME" column which fails due to not-null constraint.

NULL not allowed for column "CHANGE_DATETIME"; SQL statement:
INSERT INTO FOO(CODE, CHANGE_DATETIME) VALUES(?, ?) [23502-200]
        at org.h2.jdbc.JdbcPreparedStatement.executeBatch(JdbcPreparedStatement.java:1235)
        at liquibase.statement.BatchDmlExecutablePreparedStatement.executePreparedStatement(BatchDmlExecutablePreparedStatement.java:64)
        at liquibase.statement.ExecutablePreparedStatementBase.execute(ExecutablePreparedStatementBase.java:80)

Expected/Desired Behavior

Liquibase should use "CURRENT_TIMESTAMP" in insert and the current timestamp should be inserted.

Additional Context

It worked fine in the old Liquibase version that I used (3.4.2).
As a workaround, a custom load data change can be used that disables prepared statements:

@DatabaseChange(name = "loadData", priority = 100, appliesTo = "table", description = "Loads data from a CSV file into an existing table.")
public class CustomLoadDataChange extends LoadDataChange {
    @Override
    protected boolean hasPreparedStatementsImplemented() {
        return false;
    }
}

and then META-INF/services/liquibase.change.Change file must contain the qualified class name of CustomLoadDataChange.

Please also note that disabling of prepared statements for given change with usePreparedStatements=false currently does not work due to #1091

Some issues that might be related:
https://liquibase.jira.com/browse/CORE-3208
https://liquibase.jira.com/browse/CORE-2976

Using different types in loadData instead of type="TIMESTAMP" does not help.
For type="STRING" the error is: Cannot parse "TIMESTAMP" constant "CURRENT_TIMESTAMP"
For type="COMPUTED" the error is the same as for type="TIMESTAMP".

For details for CURRENT_TIMESTAMP function in H2 please see:
http://www.h2database.com/html/functions.html#current_timestamp

[molivasdat]

Hi @kdebski85 Thanks for the excellent writeup of this issue. We will add it to the list of issues to process. Along with #1091 which may help in conjunction with this one.

[rios0rios0]

It's still happening on 4.4.3.

[LaurianeDPX]

Hello all, I ended up with the same problem when I upgraded liquibase from 2.0.5 to 4.7.0. A workaround is to use usePreparedStatements="false" since #1091 has been fixed, but it's only a workaround.

Is there any plan to fix this issue in the near future?

[molivasdat]

Hi @LaurianeDPX Thanks for the update. I believe the current short term plan is to use usePreparedStatements=false as this is not a simple fix to move is back to how it worked in version v2. We can take another look to see what it would take to fix and keep existing functionality too. You can submit a PR as well.

[LaurianeDPX]

Hi, thanks for your answer.

I continued my tests this morning and the weirdest thing happened (weirdest = I don't have an explanation for this, but maybe you do). I just changed the xsd of the databaseChangeLog XML tags from dbchangelog-2.0.xsd to dbchangelog-4.4.xsd, and it worked. The workaround with usePreparedStatements=false is not needed anymore.

If you have an explanation for that behavior, I'd be happy to hear it. But as for this issue, it does not impact me anymore.

[FBurguer]

We seem to be now attempting to load in the value you specified in the CSV file. In this case, it's not a valid datetime value. If you want to allow functions, you can use type="COMPUTED" and we'll pass it as a function, but then all values need to be a valid function or else a database-valid date string that is quoted in the csv file