mysql insert update on duplicate key - strange behaviour when col has default null

Question

I have a table my_table:

col_a varchar(20) not null, no default
col_b varchar(10) not null, no default
col c varchar(5) null, default null

there is a unique index on col_a, col_b

there is an existing row where: col_a = 'ABCDEF-123' and col_b = '1543' and col_c = 'Apples'

if I execute the following command, the error code is 0 but no rows are updated.

insert into my_table (col_a,col_b,col_c) values ('ABCDEF-123','1543','Oranges') ON DUPLICATE KEY UPDATE col_c=concat(col_c,',','Oranges');

If I alter the table and make col_c not null with no default the command works perfectly and updates the existing row by appending ',Oranges' to col_c.

--> IS this expected behaviour for a non-index column with default null and why? It looks like a bug to me. MySQL is version 5.0.95

MySQL 5.0 is pretty old and not maintained anymore. You should update to 5.5 or later. See https://en.wikipedia.org/wiki/MySQL#Versions — David, Nov 17 '15 at 07:42

David · Answer 1 · 2015-11-17T15:04:17.963

CONCAT returns NULL if any argument is NULL.

You col_c is too short so it will never update as the value is not different then before.

CREATE TABLE my_table (
    col_a VARCHAR(20) NOT NULL,
    col_b VARCHAR(10) NOT NULL,
    col_c VARCHAR(5) NULL DEFAULT NULL,
    UNIQUE INDEX (col_a, col_b)
);

INSERT INTO my_table (col_a,col_b,col_c) VALUES ('ABCDEF-123','1543','Apples');
Query OK, 1 row affected, 1 warning (0.06 sec)

We got "1 warning":

SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1265 | Data truncated for column 'col_c' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

col_c allows only 5 characters so values will be truncated on insert. We can verify this as we check the table:

SELECT * FROM my_table;
+------------+-------+-------+
| col_a      | col_b | col_c |
+------------+-------+-------+
| ABCDEF-123 | 1543  | Apple |
+------------+-------+-------+
1 row in set (0.00 sec)

Ok, now try to insert another line:

insert into my_table (col_a,col_b,col_c) values ('ABCDEF-123','1543','Oranges') ON DUPLICATE KEY UPDATE col_c=concat(col_c,',','Oranges');
Query OK, 0 rows affected, 2 warnings (0.00 sec)

It says "2 warnings". So let's check:

SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1265 | Data truncated for column 'col_c' at row 1 |
| Warning | 1265 | Data truncated for column 'col_c' at row 1 |
+---------+------+--------------------------------------------+
2 rows in set (0.00 sec)

The CONCAT value would be "Apple,Oranges" which will be truncated to "Apple" which is the same as before.

Typo on my part - col_c is 50 and wide enough. Your original answer about concat of anything with null returning null was correct.I just updated my table to have no nulls and it works perfectly. — user5568662, Nov 17 '15 at 07:36

mysql insert update on duplicate key - strange behaviour when col has default null

1 Answers1