Details
-
Type:
Bug
-
Status: Confirmed
-
Priority:
Critical
-
Resolution: Unresolved
-
Affects Version/s: 10.0.21, 10.1, 5.5
-
Component/s: Data Manipulation - Delete, OTHER
-
Labels:
-
Environment:CentOS 7 Linux or-dev02 3.10.0-123.9.2.el7.x86_64
Description
The following statements show that a delete with sub query with information_schema.TABLES deletes too many rows.
-- Create some tables in an empty schema. create table TABLE1(x int ); create table TABLE2(x int ); create table TABLE3(x int ); -- Create a table with all table names. create table TABLE4 AS select table_name from information_schema.TABLES where table_schema = database() and table_type = 'BASE TABLE' ; -- Delete table names that are not in the current schema. -- Expect affected rows is 0, but got 3 affected rows. delete from TABLE4 where table_name not in ( select table_name from information_schema.TABLES where table_schema = database() and table_type = 'BASE TABLE' ) ; -- Select all table names. Expect 4 table names, got only 1. select * from TABLE4 ; -- Result -- TABLE1 -- Expected: -- TABLE1 -- TABLE2 -- TABLE3 -- TABLE4
The code above works fine on Percona Server 5.6.25, MariaDB 10.0.19, but fails on MariaDB 10.0.20 and MariaDB 10.0.21.
The following work-a-round works fine:
delete from t1
using TABLE4 t1
left outer join ( select table_name
from information_schema.TABLES
where table_schema = database()
and table_type = 'BASE TABLE'
) t2 on t2.table_name = t1.table_name
where t2.table_name is NULL
;
;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Thanks for the report and the test case.
The problem appeared with this commit on 5.5 tree:
commit f07b3463e7a4ff32316e1cc94d553b5009ac51f2 Author: Sergei Golubchik <serg@mariadb.org> Date: Fri Jun 5 02:04:32 2015 +0200 do not re-populate I_S tables in subqueries