Bug #68330 Search with clause IN return duplicates records when 'order by' is added

• Submitted: 10 Feb 2013 10:24
• Modified: 4 Mar 2013 15:10
• Reporter: Luis A S Junior Camargo
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 5.6.10
• OS: Windows
• CPU Architecture: Any

[10 Feb 2013 10:24] Luis A S Junior Camargo

Description:
When use a:

Normal results:
select id from tablea where id in(select distinct(color) from tableb)

Result with duplicate records:
select id from tablea where id in(select distinct(color) from tableb) order by id

id is a field with auto-increment value.

How to repeat:
I have two tables, 'products' and 'colors'

The table colors have some record with the same color for the same product.

CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `categ` int(11) DEFAULT '0',
  `color` smallint(6) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `categ` (`categ`),
  KEY `color` (`color`)
) ENGINE=MyISAM AUTO_INCREMENT=11699 DEFAULT CHARSET=latin1;

INSERT INTO `products` VALUES (9700,296,5);
INSERT INTO `products` VALUES (9701,296,4);
INSERT INTO `products` VALUES (9702,296,10);
INSERT INTO `products` VALUES (9703,296,2);
INSERT INTO `products` VALUES (9704,296,20);
INSERT INTO `products` VALUES (9705,296,12);
INSERT INTO `products` VALUES (9706,296,21);
INSERT INTO `products` VALUES (9707,296,1);
INSERT INTO `products` VALUES (9708,296,4);
INSERT INTO `products` VALUES (9709,296,10);
INSERT INTO `products` VALUES (9710,296,12);
INSERT INTO `products` VALUES (9711,296,2);
INSERT INTO `products` VALUES (9712,296,5);
INSERT INTO `products` VALUES (9713,296,20);
INSERT INTO `products` VALUES (9714,296,1);
INSERT INTO `products` VALUES (9715,296,4);
INSERT INTO `products` VALUES (9716,296,5);
INSERT INTO `products` VALUES (9717,296,10);
INSERT INTO `products` VALUES (9718,296,11);
INSERT INTO `products` VALUES (9719,296,12);
INSERT INTO `products` VALUES (9720,296,20);
INSERT INTO `products` VALUES (9721,296,1);
INSERT INTO `products` VALUES (9722,296,2);
INSERT INTO `products` VALUES (9723,296,6);
INSERT INTO `products` VALUES (9724,296,4);
INSERT INTO `products` VALUES (9725,296,2);
INSERT INTO `products` VALUES (9726,296,1);
INSERT INTO `products` VALUES (9727,296,10);
INSERT INTO `products` VALUES (9728,296,3);

CREATE TABLE `colors` (
  `id` int(11) NOT NULL,
  `name` char(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=45 DEFAULT CHARSET=latin1;

INSERT INTO `colors` VALUES (1,'Branco');
INSERT INTO `colors` VALUES (2,'Preto');
INSERT INTO `colors` VALUES (3,'Vermelho');
INSERT INTO `colors` VALUES (4,'Azul');
INSERT INTO `colors` VALUES (5,'Amarelo');
INSERT INTO `colors` VALUES (6,'Verde');
INSERT INTO `colors` VALUES (7,'qqq');
INSERT INTO `colors` VALUES (8,'Coral Claro');
INSERT INTO `colors` VALUES (9,'Verde Menta');
INSERT INTO `colors` VALUES (10,'Pink');
INSERT INTO `colors` VALUES (11,'Azul escuro');
INSERT INTO `colors` VALUES (12,'Laranja');
INSERT INTO `colors` VALUES (13,'Lilás');
INSERT INTO `colors` VALUES (14,'Marrom');
INSERT INTO `colors` VALUES (15,'Off White');
INSERT INTO `colors` VALUES (16,'Estampa 1');
INSERT INTO `colors` VALUES (17,'Estampa 2');
INSERT INTO `colors` VALUES (18,'Estampa 3');
INSERT INTO `colors` VALUES (19,'Estampa 4');
INSERT INTO `colors` VALUES (20,'Verde Menta');
INSERT INTO `colors` VALUES (21,'Vermelho Bordô');
INSERT INTO `colors` VALUES (22,'Azul Marinho');
INSERT INTO `colors` VALUES (23,'Mostarda');
INSERT INTO `colors` VALUES (24,'Verde Claro');
INSERT INTO `colors` VALUES (25,'Laranja Fluorescente');
INSERT INTO `colors` VALUES (26,'Rosa Fluorescente');
INSERT INTO `colors` VALUES (27,'Verde Fluorescente');
INSERT INTO `colors` VALUES (28,'Rosa');
INSERT INTO `colors` VALUES (29,'Verde Escuro');
INSERT INTO `colors` VALUES (30,'Azul claro');
INSERT INTO `colors` VALUES (31,'Rosê');
INSERT INTO `colors` VALUES (32,'Estampa 5');
INSERT INTO `colors` VALUES (33,'Roxo');
INSERT INTO `colors` VALUES (34,'Salmão');
INSERT INTO `colors` VALUES (35,'Cinza');
INSERT INTO `colors` VALUES (36,'Rosa Embaçado');
INSERT INTO `colors` VALUES (37,'Azul Celeste');
INSERT INTO `colors` VALUES (38,'Bege');
INSERT INTO `colors` VALUES (39,'Dourado');
INSERT INTO `colors` VALUES (40,'Marrom Claro');
INSERT INTO `colors` VALUES (41,'Vinho');
INSERT INTO `colors` VALUES (42,'Verde musgo');
INSERT INTO `colors` VALUES (43,'Verde Militar');
INSERT INTO `colors` VALUES (44,'Azul Royal');

=========================

Normal result (without 'order by'):
select id,name from colors where id in(select distinctrow(color) from products where categ=296)
(11 records returned)

Result with duplicate records (with 'order by'):
select id,name from colors where id in(select distinctrow(color) from products where categ=296) order by id
(29 records returned)

Only the 'order by' has added and the result has returning 1 record for each record in the sub-query.

No problem in MySQL 5.5.29, the problem only occurs after upgrade to 5.6.10

thanks

[10 Feb 2013 10:25] Luis A S Junior Camargo

table products

Attachment: products.sql (application/octet-stream, text), 1.81 KiB.

[10 Feb 2013 10:25] Luis A S Junior Camargo

table colors

Attachment: colors.sql (application/octet-stream, text), 2.42 KiB.

[10 Feb 2013 23:59] MySQL Verification Team

Thank you for the bug report. Verified with mysql-trunk source:

mysql 5.7 >select id,name from colors where id in(select distinctrow(color) from products where categ=296) order by id;
+----+----------------+
| id | name           |
+----+----------------+
|  1 | Branco         |
|  1 | Branco         |
|  1 | Branco         |
|  1 | Branco         |
|  2 | Preto          |
|  2 | Preto          |
|  2 | Preto          |
|  2 | Preto          |
|  3 | Vermelho       |
|  4 | Azul           |
|  4 | Azul           |
|  4 | Azul           |
|  4 | Azul           |
|  5 | Amarelo        |
|  5 | Amarelo        |
|  5 | Amarelo        |
|  6 | Verde          |
| 10 | Pink           |
| 10 | Pink           |
| 10 | Pink           |
| 10 | Pink           |
| 11 | Azul escuro    |
| 12 | Laranja        |
| 12 | Laranja        |
| 12 | Laranja        |
| 20 | Verde Menta    |
| 20 | Verde Menta    |
| 20 | Verde Menta    |
| 21 | Vermelho Bord¶ |
+----+----------------+
29 rows in set (0.00 sec)

mysql 5.7 >alter table products drop key categ;
Query OK, 29 rows affected (0.06 sec)
Records: 29  Duplicates: 0  Warnings: 0

mysql 5.7 >alter table products drop key color;
Query OK, 29 rows affected (0.06 sec)
Records: 29  Duplicates: 0  Warnings: 0

mysql 5.7 >select id,name from colors where id in(select distinctrow(color) from products where categ=296) order by id;
+----+----------------+
| id | name           |
+----+----------------+
|  1 | Branco         |
|  2 | Preto          |
|  3 | Vermelho       |
|  4 | Azul           |
|  5 | Amarelo        |
|  6 | Verde          |
| 10 | Pink           |
| 11 | Azul escuro    |
| 12 | Laranja        |
| 20 | Verde Menta    |
| 21 | Vermelho Bord¶ |
+----+----------------+
11 rows in set (0.00 sec)

[11 Feb 2013 7:32] Roy Lyseng

Hi Luis,

a quick workaround for you, please try it out:

set optimizer_switch='loosescan=off';

[11 Feb 2013 11:16] Luis A S Junior Camargo

Hi Roy,

Thank you, now i have tested with:
set optimizer_switch='loosescan=off';

The returning result are correct after this.

[4 Mar 2013 15:10] Paul DuBois

Noted in 5.6.11, 5.7.1 changelogs.

Adding an ORDER BY clause following an IN subquery could cause
duplicate rows to be returned.