Funny replication breakage of Friday, January 13

A funny replication breakage kept me at the office longer than expected today (Friday 13 is not kind with me).

So question of the day: can you guess what the below UPDATE statement does (or what is wrong with it) ?

      > CREATE TABLE test_jfg (
           id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
           status ENUM('a','b') NOT NULL DEFAULT 'a',
           txt TEXT);
      Query OK, 0 rows affected (0.00 sec)

      > INSERT INTO test_jfg (txt) VALUES ('hello world');
      Query OK, 1 row affected (0.04 sec)

      > SELECT * FROM test_jfg;
      +----+--------+-------------+
      | id | status | txt         |
      +----+--------+-------------+
      |  1 | a      | hello world |
      +----+--------+-------------+
      1 row in set (0.00 sec)

      > UPDATE test_jfg SET txt='' AND status='b' WHERE id=1;
      ...

Hint: it works on MariaDB 10.0 (but probably does not do what you think/want), but fails on MariaDB 10.1.

This hit me this afternoon: my MariaDB 10.1 slaves have replication broken, but this passes through MariaDB 10.0 intermediate masters and executes OK-ish on the master.

Note: this replication chain is running statement-based replication (I think row-based replication would not have broken, which might make me start to prefer RBR to SBR).

So this is what you get on MariaDB 10.0:

      > UPDATE test_jfg SET txt='' AND status='b' WHERE id=1;
      Query OK, 1 row affected, 1 warning (0.12 sec)
      Rows matched: 1  Changed: 1  Warnings: 1

      > SELECT * FROM test_jfg;
      +----+--------+------+
      | id | status | txt  |
      +----+--------+------+
      |  1 | a      | 0    |
      +----+--------+------+
      1 row in set (0.00 sec)

But wait, there was a warning there, was does it say?

      > UPDATE test_jfg SET txt='' AND status='b' WHERE id=1;
      Query OK, 1 row affected, 1 warning (0.00 sec)
      Rows matched: 1  Changed: 1  Warnings: 1

      > SHOW WARNINGS;
      +---------+------+---------------------------------------+
      | Level   | Code | Message                               |
      +---------+------+---------------------------------------+
      | Warning | 1292 | Truncated incorrect INTEGER value: '' |
      +---------+------+---------------------------------------+
      1 row in set (0.00 sec)

And as you saw above, the expected value in the status and txt columns are not good.

So what is the problem ?  A simple SQL error: there should be a comma instead of an AND!  Because of this error, the query evaluated as follows:

      > UPDATE test_jfg SET txt= ( '' AND status='b' ) WHERE id=1;

Just for information, this is the error you get on a MariaDB 10.1 slave:

      Last_Error: Error 'Truncated incorrect DOUBLE value: ''' on query.
      Default database: 'test'.
      Query: 'UPDATE test_jfg SET txt='' AND status='b' WHERE id=1'

And this is the error you get when running the statement on MariaDB 10.1:

      > UPDATE test_jfg SET txt='' AND status='b' WHERE id=1;
      ERROR 1292 (22007): Truncated incorrect DOUBLE value: ''

The moral or this story, please do not ignore warnings, and this will keep the DBAs happy!

I now need to chase the guy that pushed this statement on the master, and I need to keep telling people to always investigating/fixing warnings in MySQL/MariaDB.

Side note: I am happy that MariaDB 10.1 will not accept this syntax, but I do not know if this is a bug in 10.0 (parser too permissive) or in 10.1 (parser rejecting valid SQL).  Thoughts?

Final note: I have not tested this with MySQL 5.6, 5.7 and 8.0.  If someone does, please be kind enough to post results in the comments.  Now I need to leave the office and go home.

UPDATE: some results of this with Oracle MySQL in a new post.

UPDATE2: MDEV-11787 - Inconsistent behavior on bad query between MariaDB 10.0 and 10.1 - is opened to track this issue.