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

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

;

;