Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
10.0(EOL)
-
None
Description
The following bad UPDATE leads to a "warning" and truncated data on 10.0, but it fails on 10.1.
UPDATE `t1` SET `txt`='' AND `status`='b' WHERE `id`=1;
|
I say it is "bad" because in theory, there should be a "," in place of "AND". However, perhaps it is possible that this is valid SQL, due to "AND".
I'm not sure what change led to this, if it is expected, if 10.0 is correct in allowing this as SQL, or is 10.1 correct in blocking this?
Note that in 10.0, the query throws a "warning", but partially changes the row.
In 10.1, the query fails, and thus the partial update does not occur.
Also, in 10.1, the SHOW WARNINGS after the failed query results in 2 results (warning for "Truncated incorrect INTEGER" and error for "Truncated incorrect DOUBLE") as opposed to only the 1 row in 10.0 (warning for "Truncated incorrect INTEGER").
Please advise which is actually correct. And assuming 10.1 is correct behavior, please advise which change caused this. And could this be considered an incompatibility between 10.0 and 10.1 that needs documented?
Test Case:
CREATE TABLE `t1` (
|
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
`status` ENUM('a','b') NOT NULL DEFAULT 'a',
|
`txt` TEXT) engine=InnODB;
|
INSERT INTO `t1` (`txt`) VALUES ('hello world');
|
SELECT * FROM `t1`;
|
UPDATE `t1` SET `txt`='' AND `status`='b' WHERE `id`=1;
|
SHOW WARNINGS;
|
SELECT * FROM `t1`;
|
Output for 10.0:
mysql> SELECT @@SQL_MODE, @@VERSION;
|
+--------------------------------------------+-----------------+
|
| @@SQL_MODE | @@VERSION |
|
+--------------------------------------------+-----------------+
|
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | 10.0.29-MariaDB |
|
+--------------------------------------------+-----------------+
|
1 row in set (0.00 sec)
|
mysql> use test;
|
Database changed
|
mysql> CREATE TABLE `t1` (
|
-> `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
-> `status` ENUM('a','b') NOT NULL DEFAULT 'a',
|
-> `txt` TEXT) engine=InnODB;
|
Query OK, 0 rows affected (0.13 sec)
|
mysql> INSERT INTO `t1` (`txt`) VALUES ('hello world');
|
Query OK, 1 row affected (0.02 sec)
|
mysql> SELECT * FROM `t1`;
|
+----+--------+-------------+
|
| id | status | txt |
|
+----+--------+-------------+
|
| 1 | a | hello world |
|
+----+--------+-------------+
|
1 row in set (0.00 sec)
|
mysql> UPDATE `t1` 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
|
mysql> SHOW WARNINGS;
|
+---------+------+---------------------------------------+
|
| Level | Code | Message |
|
+---------+------+---------------------------------------+
|
| Warning | 1292 | Truncated incorrect INTEGER value: '' |
|
+---------+------+---------------------------------------+
|
1 row in set (0.00 sec)
|
mysql> SELECT * FROM `t1`;
|
+----+--------+------+
|
| id | status | txt |
|
+----+--------+------+
|
| 1 | a | 0 |
|
+----+--------+------+
|
1 row in set (0.00 sec)
|
Output for 10.1:
mysql> SELECT @@SQL_MODE, @@VERSION;
|
+--------------------------------------------+-----------------+
|
| @@SQL_MODE | @@VERSION |
|
+--------------------------------------------+-----------------+
|
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | 10.1.20-MariaDB |
|
+--------------------------------------------+-----------------+
|
1 row in set (0.00 sec)
|
mysql> CREATE TABLE `t1` (
|
-> `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
-> `status` ENUM('a','b') NOT NULL DEFAULT 'a',
|
-> `txt` TEXT) engine=InnODB;
|
Query OK, 0 rows affected (0.05 sec)
|
mysql> INSERT INTO `t1` (`txt`) VALUES ('hello world');
|
Query OK, 1 row affected (0.02 sec)
|
mysql> SELECT * FROM `t1`;
|
+----+--------+-------------+
|
| id | status | txt |
|
+----+--------+-------------+
|
| 1 | a | hello world |
|
+----+--------+-------------+
|
1 row in set (0.00 sec)
|
mysql> UPDATE `t1` SET `txt`='' AND `status`='b' WHERE `id`=1;
|
ERROR 1292 (22007): Truncated incorrect DOUBLE value: ''
|
mysql> SHOW WARNINGS;
|
+---------+------+---------------------------------------+
|
| Level | Code | Message |
|
+---------+------+---------------------------------------+
|
| Warning | 1292 | Truncated incorrect INTEGER value: '' |
|
| Error | 1292 | Truncated incorrect DOUBLE value: '' |
|
+---------+------+---------------------------------------+
|
2 rows in set (0.00 sec)
|
mysql> SELECT * FROM `t1`;
|
+----+--------+-------------+
|
| id | status | txt |
|
+----+--------+-------------+
|
| 1 | a | hello world |
|
+----+--------+-------------+
|
1 row in set (0.00 sec)
|