MySQL loadUpdateData using literal values for update

Description

From http://forum.liquibase.org/topic/mysql-loadupdatedata-using-literal-values-for-update

I am having an issue with the loadUpdateData change with MySQL 5.6.12. I am using Liquibase 3.0.2.

I have a CSV file that is initially being loaded with a loadData change - which works fine. I am then wanting to add data to the CSV file and update/insert the existing/new rows using loadChangeData. It seems that Liquibase is generating SQL that is trying to update the columns with the literal values for the column names. Here is the bad SQL:

INSERT INTO ssue (id, intcol, charcol, datecol) VALUES ('11bee7914aa74764975e30c07a496835', 1, 'String', '2013-08-08 12:16:49')
ON DUPLICATE KEY UPDATE intcol = intcol,charcol = 'charcol',datecol = 'datecol';

This is the error where it is using the column name for the value:
Data truncation: Incorrect datetime value: 'datecol' for column 'datecol' at row 1
It is trying to use the literal value of the column name for the the update since it is in single quotes.

If I manually change the SQL to this:
INSERT INTO issue (id, intcol, charcol, datecol) VALUES ('11bee7914aa74764975e30c07a496835', 1, 'String', '2013-08-08 12:16:49')
ON DUPLICATE KEY UPDATE intcol = intcol,charcol = VALUES(charcol),datecol = VALUES(datecol);

it works fine.

Is this a bug in Liquibase or am I doing something wrong with my changeSet?

The databaseChangeLog looks like this:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.0.xsd">
<changeSet author="Andy" id="1376654790871-1">
<createTable tableName="issue">
<column name="id" type="CHAR(32)">
<constraints nullable="false"/>
</column>
<column name="intcol" type="INT(10)"/>
<column name="charcol" type="VARCHAR(45)"/>
<column name="datecol" type="datetime"/>
</createTable>
</changeSet>
<changeSet author="Andy" id="1376654790871-2">
<addPrimaryKey columnNames="id" constraintName="PRIMARY" tableName="issue"/>
</changeSet>
<changeSet author="Andy" id="1376654790871-3">
<loadData encoding="UTF-8"
file="db/changelog/1.0.0/issue.csv"
quotchar="&quot;"
separator=","
tableName="issue">
<column name="id" type="STRING"/>
<column name="intcol" type="NUMERIC"/>
</loadData>
</changeSet>
<changeSet author="Andy" id="1376654790871-4">
<loadUpdateData encoding="UTF-8" primaryKey="id"
file="db/changelog/1.0.0/issue.csv"
quotchar="&quot;"
separator=","
tableName="issue">
<column name="id" type="STRING"/>
<column name="intcol" type="NUMERIC"/>
</loadUpdateData>
</changeSet>
</databaseChangeLog>

This is the CSV file:
id,intcol,charcol,datecol
11bee7914aa74764975e30c07a496835,1,"String","2013-08-08 12:16:49"

Environment

Mysql

Activity

Show:
Lee Butts
November 11, 2014, 10:39 PM

This has caused a regression when inserting using type="NUMERIC". The generated insert statement is now quoting numeric values (e.g. inserting 0/1 into a bit column):

Example CSV:

Nathan Voxland
November 12, 2014, 3:52 PM

Thanks, I created to track the regression fix

Reporter

Nathan Voxland

Fix versions

Affects versions

Priority

Major
Configure