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 |
[11 Mar 2017 3:45]
Shinya Sugiyama
[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