[MDEV-8796] Delete with sub query with information_schema.TABLES deletes too many rows - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.0.21, 5.5(EOL), 10.1(EOL)
Fix Version/s: 5.5.46, 10.0.22, 10.1.8
Component/s: Data Manipulation - Delete
Labels:
- regression
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

Attachments

Activity

Ascending order - Click to sort in descending order

P.R. Water created issue - 2015-09-14 09:20

P.R. Water made changes - 2015-09-14 09:22

Field	Original Value	New Value
Description	The following statements show that a delete with sub query with information_schema.TABLES deletes to many rows. {code} -- 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 {code} The code above works fine on Percona server 5.6.25. The following work-a-round works fine: {code} 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 ; {code}	The following statements show that a delete with sub query with information_schema.TABLES deletes to many rows. {code} -- 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 {code} The code above works fine on Percona server 5.6.25. The following work-a-round works fine: {code} 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 ; ; {code}

P.R. Water made changes - 2015-09-14 09:42

Description

The following statements show that a delete with sub query with information_schema.TABLES deletes to many rows.
{code}
-- 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
{code}

The code above works fine on Percona server 5.6.25.

The following work-a-round works fine:
{code}
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
;
;
{code}

The following statements show that a delete with sub query with information_schema.TABLES deletes to many rows.
{code}
-- 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
{code}

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:
{code}
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
;
;
{code}

P.R. Water made changes - 2015-09-15 18:40

Affects Version/s		10.0.21 [ 19406 ]
Affects Version/s	10.0.21-galera [ 19501 ]

Elena Stepanova added a comment - 2015-09-16 01:09

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

Elena Stepanova added a comment - 2015-09-16 01:09 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

Elena Stepanova made changes - 2015-09-16 01:09

Component/s		OTHER [ 10125 ]
Fix Version/s		10.1 [ 16100 ]
Fix Version/s		10.0 [ 16000 ]
Fix Version/s		5.5 [ 15800 ]
Affects Version/s		10.1 [ 16100 ]
Affects Version/s		5.5 [ 15800 ]
Assignee		Sergei Golubchik [ serg ]
Labels		regression

Elena Stepanova made changes - 2015-09-16 01:09

Status

Open [ 1 ]

Confirmed [ 10101 ]

Elena Stepanova made changes - 2015-09-16 01:09

Summary

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

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

Sergei Golubchik made changes - 2015-09-19 11:48

Description

The following statements show that a delete with sub query with information_schema.TABLES deletes to many rows.
{code}
-- 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
{code}

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:
{code}
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
;
;
{code}

The following statements show that a delete with sub query with information_schema.TABLES deletes too many rows.
{code}
-- 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
{code}

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:
{code}
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
;
;
{code}

Elena Stepanova made changes - 2015-09-21 15:36

Priority

Major [ 3 ]

Critical [ 2 ]

Sergei Golubchik made changes - 2015-10-07 18:34

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Sergei Golubchik made changes - 2015-10-07 18:34

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Sergei Golubchik made changes - 2015-10-09 11:00

Fix Version/s		10.0.22 [ 19700 ]
Fix Version/s		10.1.8 [ 19605 ]
Fix Version/s		5.5.46 [ 19603 ]
Fix Version/s	10.0 [ 16000 ]
Fix Version/s	5.5 [ 15800 ]
Fix Version/s	10.1 [ 16100 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2015-10-09 11:00

Component/s

OTHER [ 10125 ]

Sergei Golubchik made changes - 2021-12-06 21:42

Workflow

MariaDB v3 [ 71600 ]

MariaDB v4 [ 149610 ]

People

Assignee:: Sergei Golubchik

Reporter:: P.R. Water

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2015-09-14 09:20

Updated:: 2015-10-09 11:00

Resolved:: 2015-10-09 11:00

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration