| Bug #110003 | DELETE query does ot work for BIT columns | ||
|---|---|---|---|
| Submitted: | 9 Feb 10:37 | Modified: | 9 Feb 10:52 |
| Reporter: | Sveta Smirnova (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 8.0.32, 5.7, 5.6 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[9 Feb 10:38]
Sveta Smirnova
test case for MTR
Attachment: PT-2114.test (application/octet-stream, text), 696 bytes.
[9 Feb 10:52]
MySQL Verification Team
Hello Sveta, Thank you for the report and feedback. regards, Umesh
[13 Feb 12:16]
huahua xu
The optimizer does not check the result of store_key::copy(), which could return an error. about the select case, store_key::copy() got the TYPE_WARN_OUT_OF_RANGE error produced in `Field_bit_as_char::store` method caused by `val = '1'`: mysqld.exe!Field::set_warning(Sql_condition::enum_severity_level level, unsigned int code, int cut_increment, const char * view_db_name, const char * view_name) mysqld.exe!Field_bit_as_char::store(const char * from, unsigned __int64 length, const charset_info_st * cs) mysqld.exe!Item::save_str_value_in_field(Field * field, String * result) mysqld.exe!Item_string::save_in_field_inner(Field * field, bool no_conversions) mysqld.exe!Item::save_in_field(Field * field, bool no_conversions) mysqld.exe!store_key_item::copy_inner() mysqld.exe!store_key_const_item::copy_inner() mysqld.exe!store_key::copy() mysqld.exe!create_ref_for_key(JOIN * join, JOIN_TAB * j, Key_use * org_keyuse, unsigned __int64 used_tables) mysqld.exe!JOIN::extract_func_dependent_tables() mysqld.exe!JOIN::make_join_plan() mysqld.exe!JOIN::optimize() mysqld.exe!st_select_lex::optimize(THD * thd) mysqld.exe!handle_query(THD * thd, LEX * lex, Query_result * result, unsigned __int64 added_options, unsigned __int64 removed_options) mysqld.exe!execute_sqlcom_select(THD * thd, TABLE_LIST * all_tables) mysqld.exe!mysql_execute_command(THD * thd, bool first_level) mysqld.exe!mysql_parse(THD * thd, Parser_state * parser_state) mysqld.exe!dispatch_command(THD * thd, const COM_DATA * com_data, enum_server_command command) mysqld.exe!do_command(THD * thd) mysqld.exe!handle_connection(void * arg)
[13 Feb 12:29]
huahua xu
In addition, the SELECT statement can find rows because `Field_bit_as_char::store` stores the maximum value when exceeding the range of the column.
For example, when default column `val` as bit(2):
mysql> CREATE TABLE `test`.`t1` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `val` bit(2) NOT NULL DEFAULT b'0',
-> PRIMARY KEY (`id`,`val`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=122 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO test.t1 (val) VALUES (0),(1);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO test.t1 (val) SELECT val FROM test.t1 ;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO test.t1 (val) SELECT val FROM test.t1 ;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from `test`.`t1` WHERE (`id` = '123' AND `val` = '1') ;
Empty set (0.00 sec)
mysql> select count(*) from `test`.`t1`;
+----------+
| count(*) |
+----------+
| 8 |
+----------+
1 row in set (0.00 sec)
mysql> delete from `test`.`t1` WHERE (`id` = '123' AND `val` = '1') ;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from `test`.`t1`;
+----------+
| count(*) |
+----------+
| 8 |
+----------+
1 row in set (0.00 sec)
Description: If the BIT column is used in the search condition for DELETE, it cannot find rows. While the SELECT statement with the same WHERE conditions returns rows. How to repeat: mysql🐬> CREATE TABLE `test`.`t1` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> `val` bit(1) NOT NULL DEFAULT b'0', -> PRIMARY KEY (`id`,`val`) -> ) ENGINE=InnoDB AUTO_INCREMENT=122 DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected, 1 warning (0,05 sec) mysql🐬> INSERT INTO test.t1 (val) VALUES (0),(1); Query OK, 2 rows affected (0,01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql🐬> INSERT INTO test.t1 (val) SELECT val FROM test.t1 ; Query OK, 2 rows affected (0,01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql🐬> INSERT INTO test.t1 (val) SELECT val FROM test.t1 ; Query OK, 4 rows affected (0,01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql🐬> explain select * from `test`.`t1` WHERE (`id` = '123' AND `val` = '1') ; +----+-------------+-------+------------+-------+---------------+---------+---------+-------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | const | PRIMARY | PRIMARY | 5 | const,const | 1 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+---------+---------+-------------+------+----------+-------------+ 1 row in set, 1 warning (0,00 sec) mysql🐬> select * from `test`.`t1` WHERE (`id` = '123' AND `val` = '1') ; +-----+-----+ | id | val | +-----+-----+ | 123 | | +-----+-----+ 1 row in set (0,00 sec) mysql🐬> select count(*) from `test`.`t1`; +----------+ | count(*) | +----------+ | 8 | +----------+ 1 row in set (0,00 sec) mysql🐬> explain delete from `test`.`t1` WHERE (`id` = '123' AND `val` = '1') ; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ | 1 | DELETE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ 1 row in set, 1 warning (0,00 sec) mysql🐬> delete from `test`.`t1` WHERE (`id` = '123' AND `val` = '1') ; Query OK, 0 rows affected (0,00 sec) mysql🐬> select count(*) from `test`.`t1`; +----------+ | count(*) | +----------+ | 8 | +----------+ 1 row in set (0,00 sec) Suggested fix: Query delete from `test`.`t1` WHERE (`id` = '123' AND `val` = 1); (without apostrophes around 1) works as expected (removes row). So this could be bug either in how MySQL handles SELECT or DELETE. Fix any of them but be consistent.