| Bug #85418 | DML with where clause without column name change all data | ||
|---|---|---|---|
| Submitted: | 11 Mar 2017 3:45 | Modified: | 15 Mar 2017 5:24 |
| Reporter: | Shinya Sugiyama | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.7.x | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | DML | ||
[13 Mar 2017 10:54]
MySQL Verification Team
Thank you for the report and feedback.
[13 Mar 2017 12:35]
Shinya Sugiyama
Hi Umesh, Thank you for your confirmation. Additional Note: If user put 0, it doesn't update any data. root@localhost [CONFIRM]> update T_DML_WITHOUT_COLUMN set data = 'confirmation 2nd' where 1; Query OK, 4 rows affected (0.00 sec) Rows matched: 4 Changed: 4 Warnings: 0 root@localhost [CONFIRM]> select * from T_DML_WITHOUT_COLUMN; +----+------------------+ | id | data | +----+------------------+ | 1 | confirmation 2nd | | 2 | confirmation 2nd | | 3 | confirmation 2nd | | 4 | confirmation 2nd | +----+------------------+ 4 rows in set (0.01 sec) root@localhost [CONFIRM]> update T_DML_WITHOUT_COLUMN set data = 'confirmation 2nd' where 0; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 root@localhost [CONFIRM]> select * from T_DML_WITHOUT_COLUMN; +----+------------------+ | id | data | +----+------------------+ | 1 | confirmation 2nd | | 2 | confirmation 2nd | | 3 | confirmation 2nd | | 4 | confirmation 2nd | +----+------------------+ 4 rows in set (0.00 sec) root@localhost [CONFIRM]>
[15 Mar 2017 5:24]
Shinya Sugiyama
■ Original Data mysql> select * from T_DML_WITHOUT_COLUMN; +----+--------+ | id | data | +----+--------+ | 1 | blue | | 2 | green | | 3 | red | | 4 | yellow | +----+--------+ 4 rows in set (0.00 sec) ■ select without values after row. (id = numeric row) mysql> select * from T_DML_WITHOUT_COLUMN where id; +----+--------+ | id | data | +----+--------+ | 1 | blue | | 2 | green | | 3 | red | | 4 | yellow | +----+--------+ 4 rows in set (0.00 sec) ■ select without values after row. (id = string row) mysql> select * from T_DML_WITHOUT_COLUMN where data; Empty set, 4 warnings (0.00 sec) Warning (Code 1292): Truncated incorrect INTEGER value: 'blue' Warning (Code 1292): Truncated incorrect INTEGER value: 'green' Warning (Code 1292): Truncated incorrect INTEGER value: 'red' Warning (Code 1292): Truncated incorrect INTEGER value: 'yellow' mysql> ■ select without column name after where with '0' numeric value. mysql> select * from T_DML_WITHOUT_COLUMN where 0; Empty set (0.00 sec) ■ select without column name after where with non '0' numeric value. mysql> select * from T_DML_WITHOUT_COLUMN where 1; +----+--------+ | id | data | +----+--------+ | 1 | blue | | 2 | green | | 3 | red | | 4 | yellow | +----+--------+ 4 rows in set (0.00 sec) ■ select without column name after where with non '0' numeric value. mysql> select * from T_DML_WITHOUT_COLUMN where 1234; +----+--------+ | id | data | +----+--------+ | 1 | blue | | 2 | green | | 3 | red | | 4 | yellow | +----+--------+ 4 rows in set (0.00 sec) It seems just checking True or false. (0 or 1) ■ In case forgot value after colum that is numeric row. mysql> update T_DML_WITHOUT_COLUMN set data = 'forgot' where id; Query OK, 4 rows affected (0.02 sec) Rows matched: 4 Changed: 4 Warnings: 0 mysql> select * from T_DML_WITHOUT_COLUMN where 1; +----+--------+ | id | data | +----+--------+ | 1 | forgot | | 2 | forgot | | 3 | forgot | | 4 | forgot | +----+--------+ 4 rows in set (0.00 sec) mysql> ■ In case forgot value after column that is string row. mysql> update T_DML_WITHOUT_COLUMN set data = 'forgot2' where data; ERROR 1292 (22007): Truncated incorrect INTEGER value: 'forgot' mysql> This is operation related problem; however, output should be error. Best Regards Shinya
Description: If user forgot to put column name after where clause, all data will be changed. How to repeat: root@localhost [CONFIRM]> show create table T_DML_WITHOUT_COLUMN\G *************************** 1. row *************************** Table: T_DML_WITHOUT_COLUMN Create Table: CREATE TABLE `T_DML_WITHOUT_COLUMN` ( `id` int(11) NOT NULL AUTO_INCREMENT, `data` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) root@localhost [CONFIRM]> root@localhost [CONFIRM]> select * from T_DML_WITHOUT_COLUMN; +----+--------+ | id | data | +----+--------+ | 1 | blue | | 2 | green | | 3 | red | | 4 | yellow | +----+--------+ 4 rows in set (0.00 sec) root@localhost [CONFIRM]> select * from T_DML_WITHOUT_COLUMN where 12345; +----+--------+ | id | data | +----+--------+ | 1 | blue | | 2 | green | | 3 | red | | 4 | yellow | +----+--------+ 4 rows in set (0.00 sec) root@localhost [CONFIRM]> update T_DML_WITHOUT_COLUMN set data = 'confirmation' where 1; Query OK, 4 rows affected (0.01 sec) Rows matched: 4 Changed: 4 Warnings: 0 root@localhost [CONFIRM]> select * from T_DML_WITHOUT_COLUMN; +----+--------------+ | id | data | +----+--------------+ | 1 | confirmation | | 2 | confirmation | | 3 | confirmation | | 4 | confirmation | +----+--------------+ 4 rows in set (0.00 sec) root@localhost [CONFIRM]> Suggested fix: This might be user mistake but this should be become error. In case if user forgot to put column name, it should became error since it is not right sql.