Mysql Coalesce returning 0 instead of actual value

Question

INSERT INTO 'rozliczenia' ('userid', 'data', 'stawka') VALUES ('1', '2015-11-01', NULL) ON DUPLICATE KEY UPDATE stawka = COALESCE(VALUES('stawka'), 'stawka');

It should check if value is NULL. If it is then don't update premia column(I don't have any null values in table), but it updates it to 0. Why? Everything worked fine yesterday.

Before update:

Premia 200

After

Premia 0, though it should be 200

Indexes in the table:

Primary - Id

Unique - data

Unique - userid

IT IS NOT ABOUT QUOTES, WHEN CHANGE THEM IT BEHAVES THE SAME WAY

Answer 1

You have single quotes around table and column names, so your query shouldn't work.

INSERT INTO rozliczenia(userid, data, stawka)
    VALUES ('1', '2015-11-01', NULL)
    ON DUPLICATE KEY UPDATE stawka = COALESCE(VALUES(stawka), stawka);

The reason why coalesce() returns 0 is because you are passing in a string and then using it in a numeric context. A string that doesn't start with a digit ends up being 0 in such a context.

Repeat until you understand it thoroughly: "I will only use single quotes for string and date constants."