| Bug #80088 | Where in subquery referencing md5 computed column in view causes duplicate rows | ||
|---|---|---|---|
| Submitted: | 21 Jan 2016 0:46 | Modified: | 26 Jan 2016 20:54 |
| Reporter: | Michael Thomas | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.6/5.7 | OS: | Other (Amazon RDS and OSX) |
| Assigned to: | CPU Architecture: | Any | |
[21 Jan 2016 0:46]
Michael Thomas
[21 Jan 2016 0:57]
Michael Thomas
This is not limited to the MD5 function. When any function in the view (even addition) causes the same issue. For example: drop table if exists account; create table account as select 1 as id union select 2 as id union select 3 as id; drop table if exists account_item; create table account_item as select 1 as id, 1 as account_id union select 1 as id, 1 as account_id union select 2 as id, 2 as account_id union select 3 as id, 2 as account_id union select 4 as id, 3 as account_id; drop view if exists account_view; create view account_view as select id + 1 as id from account; select * FROM account_item as item where (item.account_id + 1) in (select id from account_view);
[22 Jan 2016 14:28]
Michael Thomas
The last part of the steps to reproduce should have been select count(*) FROM account_item item where md5(item.account_id) in (select id from account_view); -- Expect to get "4" as the result but instead get "12"
[26 Jan 2016 20:51]
Miguel Solorzano
C:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.97-Win X64 Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.0 > use test
Database changed
mysql 5.0 > create table account as
-> select 1 as id
-> union select 2 as id
-> union select 3 as id;
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql 5.0 >
mysql 5.0 > drop table if exists account_item;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql 5.0 > create table account_item as
-> select 1 as id, 1 as account_id
-> union select 1 as id, 1 as account_id
-> union select 2 as id, 2 as account_id
-> union select 3 as id, 2 as account_id
-> union select 4 as id, 3 as account_id;
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql 5.0 >
mysql 5.0 > drop view if exists account_view;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql 5.0 > create view account_view as
-> select md5(id) as id
-> from account;
Query OK, 0 rows affected (0.01 sec)
mysql 5.0 >
mysql 5.0 > select *
-> FROM account_item item
-> where md5(item.account_id) in (select id from account_view);
+----+------------+
| id | account_id |
+----+------------+
| 1 | 1 |
| 2 | 2 |
| 3 | 2 |
| 4 | 3 |
+----+------------+
4 rows in set (0.00 sec)
C:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --debug-info --prompt="mysql 5.1 > "
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.74-Win X64 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.1 > use test
Database changed
mysql 5.1 > create table account as
-> select 1 as id
-> union select 2 as id
-> union select 3 as id;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql 5.1 >
mysql 5.1 > drop table if exists account_item;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql 5.1 > create table account_item as
-> select 1 as id, 1 as account_id
-> union select 1 as id, 1 as account_id
-> union select 2 as id, 2 as account_id
-> union select 3 as id, 2 as account_id
-> union select 4 as id, 3 as account_id;
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql 5.1 >
mysql 5.1 > drop view if exists account_view;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql 5.1 > create view account_view as
-> select md5(id) as id
-> from account;
Query OK, 0 rows affected (0.01 sec)
mysql 5.1 >
mysql 5.1 > select *
-> FROM account_item item
-> where md5(item.account_id) in (select id from account_view);
+----+------------+
| id | account_id |
+----+------------+
| 1 | 1 |
| 2 | 2 |
| 3 | 2 |
| 4 | 3 |
+----+------------+
4 rows in set (0.00 sec)
C:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > "
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.49 Source distribution PULL: 2016-JAN-14
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.5 > use test
Database changed
mysql 5.5 > create table account as
-> select 1 as id
-> union select 2 as id
-> union select 3 as id;
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql 5.5 >
mysql 5.5 > drop table if exists account_item;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql 5.5 > create table account_item as
-> select 1 as id, 1 as account_id
-> union select 1 as id, 1 as account_id
-> union select 2 as id, 2 as account_id
-> union select 3 as id, 2 as account_id
-> union select 4 as id, 3 as account_id;
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql 5.5 >
mysql 5.5 > drop view if exists account_view;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql 5.5 > create view account_view as
-> select md5(id) as id
-> from account;
Query OK, 0 rows affected (0.01 sec)
mysql 5.5 >
mysql 5.5 > select *
-> FROM account_item item
-> where md5(item.account_id) in (select id from account_view);
+----+------------+
| id | account_id |
+----+------------+
| 1 | 1 |
| 2 | 2 |
| 3 | 2 |
| 4 | 3 |
+----+------------+
4 rows in set (0.00 sec)
[26 Jan 2016 20:54]
Miguel Solorzano
Thank you for the bug report.
C:\dbs>56
C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > "
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.30 Source distribution PULL: 2016-JAN-14
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.6 > use test
Database changed
mysql 5.6 > drop table if exists account;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql 5.6 > create table account as
-> select 1 as id
-> union select 2 as id
-> union select 3 as id;
Query OK, 3 rows affected (0.39 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql 5.6 >
mysql 5.6 > drop table if exists account_item;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql 5.6 > create table account_item as
-> select 1 as id, 1 as account_id
-> union select 1 as id, 1 as account_id
-> union select 2 as id, 2 as account_id
-> union select 3 as id, 2 as account_id
-> union select 4 as id, 3 as account_id;
Query OK, 4 rows affected (0.05 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql 5.6 >
mysql 5.6 > drop view if exists account_view;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql 5.6 > create view account_view as
-> select md5(id) as id
-> from account;
Query OK, 0 rows affected (0.05 sec)
mysql 5.6 >
mysql 5.6 > select *
-> FROM account_item item
-> where md5(item.account_id) in (select id from account_view);
+----+------------+
| id | account_id |
+----+------------+
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 2 | 2 |
| 2 | 2 |
| 2 | 2 |
| 3 | 2 |
| 3 | 2 |
| 3 | 2 |
| 4 | 3 |
| 4 | 3 |
| 4 | 3 |
+----+------------+
12 rows in set (0.06 sec)
mysql 5.6 > exit
Bye
C:\dbs>57
C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.12 Source distribution PULL: 2016-JAN-14
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.7 > use test
Database changed
mysql 5.7 > drop table if exists account;
Query OK, 0 rows affected (0.14 sec)
mysql 5.7 > create table account as
-> select 1 as id
-> union select 2 as id
-> union select 3 as id;
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql 5.7 >
mysql 5.7 > drop table if exists account_item;
Query OK, 0 rows affected (0.03 sec)
mysql 5.7 > create table account_item as
-> select 1 as id, 1 as account_id
-> union select 1 as id, 1 as account_id
-> union select 2 as id, 2 as account_id
-> union select 3 as id, 2 as account_id
-> union select 4 as id, 3 as account_id;
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql 5.7 >
mysql 5.7 > drop view if exists account_view;
Query OK, 0 rows affected (0.00 sec)
mysql 5.7 > create view account_view as
-> select md5(id) as id
-> from account;
Query OK, 0 rows affected (0.02 sec)
mysql 5.7 >
mysql 5.7 > select *
-> FROM account_item item
-> where md5(item.account_id) in (select id from account_view);
+----+------------+
| id | account_id |
+----+------------+
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 2 | 2 |
| 2 | 2 |
| 2 | 2 |
| 3 | 2 |
| 3 | 2 |
| 3 | 2 |
| 4 | 3 |
| 4 | 3 |
| 4 | 3 |
+----+------------+
12 rows in set (0.01 sec)
mysql 5.7 >
[27 Jan 2016 8:29]
Øystein Grøvlen
Posted by developer: A work-around is to turn off Semijoin Materialization: mysql> select * FROM account_item as item where (item.account_id + 1) in (select id from account_view); +----+------------+ | id | account_id | +----+------------+ | 1 | 1 | | 1 | 1 | | 1 | 1 | | 2 | 2 | | 2 | 2 | | 2 | 2 | | 3 | 2 | | 3 | 2 | | 3 | 2 | | 4 | 3 | | 4 | 3 | | 4 | 3 | +----+------------+ 12 rows in set (0,00 sec) mysql> set optimizer_switch='materialization=off'; Query OK, 0 rows affected (0,00 sec) mysql> select * FROM account_item as item where (item.account_id + 1) in (select id from account_view); +----+------------+ | id | account_id | +----+------------+ | 1 | 1 | | 2 | 2 | | 3 | 2 | | 4 | 3 | +----+------------+ 4 rows in set (0,00 sec)
