Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8796

Delete with sub query with information_schema.TABLES deletes too many rows

    Details

      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
      ;
      ;

        Attachments

          Activity

            People

            • Assignee:
              serg Sergei Golubchik
              Reporter:
              water P.R. Water
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: