enterprise-support team mailing list archive

[Sveta Smirnova <sveta@xxxxxxxxxxxxx>]

Upstream bug closed as "Not a Bug".

Reason described in this comment:

 [22 Dec 6:36] Sreeharsha Ramanavarapu

For the sake of simplicity, I will be dealing with the following query.
It is similar to the one in the bug page. NOT IN has been replaced by an IN
and I also got rid of the derived table.

CREATE TABLE `ContactEmail` (
  `EmailAddress` varchar(255) NOT NULL DEFAULT '',
  `ContactId` int(11) NOT NULL DEFAULT '0',
  `Id` int(10) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`Id`),
  KEY `EmailAddress` (`EmailAddress`),
  KEY `ContactId` (`ContactId`)
);

insert into ContactEmail values ('someaddress@xxxxxxxxxxx',993,551),
('someaddress@xxxxxxxxxxx',993,3363);

In 5.5:
mysql> SELECT *
    -> FROM ContactEmail
    -> WHERE ContactId = 993 AND
    ->       Id IN (SELECT Id
    ->              FROM ContactEmail
    ->              WHERE ContactId = 993
    ->              GROUP BY EmailAddress
    ->             );
+-------------------------+-----------+-----+
| EmailAddress            | ContactId | Id  |
+-------------------------+-----------+-----+
| someaddress@xxxxxxxxxxx |       993 | 551 |
+-------------------------+-----------+-----+
1 row in set (0.01 sec)

In 5.6+ (with full group_by mode turned off):
mysql> SELECT *
    -> FROM ContactEmail
    -> WHERE ContactId = 993 AND
    ->       Id IN (SELECT Id
    ->              FROM ContactEmail
    ->              WHERE ContactId = 993
    ->              GROUP BY EmailAddress
    ->             );
+-------------------------+-----------+------+
| EmailAddress            | ContactId | Id   |
+-------------------------+-----------+------+
| someaddress@xxxxxxxxxxx |       993 |  551 |
| someaddress@xxxxxxxxxxx |       993 | 3363 |
+-------------------------+-----------+------+
2 rows in set (0.00 sec)

A few things to notice:
1) Notice that in full group_by mode, the above query fails with the error :
"ERROR 1055 (42000): 'test.ContactEmail.Id' isn't in GROUP BY"

2) The extra row in 5.6+ is due to two reasons:
   a) Before converting the IN subquery to a semijoin, the inner query's
GROUP BY is eliminated since it is irrelevant (see WL#5953). So both rows are
delivered in 5.6.
   b) If we only use the inner query, without the group by:
      SELECT Id FROM ContactEmail WHERE ContactId = 993 ;
       +------+
       | Id   |
       +------+
       |  551 |
       | 3363 |
       +------+
     2 rows in set (0.00 sec)

There is no reason for group by to pick 551 over 3363. See the
following:

http://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html
"this is useful primarily when all values in each nonaggregated column not
named in the GROUP BY are the same for each group. The server is free to
choose any value from each group, so unless they are the same, the values
chosen are indeterminate. "

There are two approaches the user can take in 5.5:
1) Use 'ONLY_FULL_GROUP_BY' to reject such queries.
2) Modify the query to the following. Adding a min/max will make sure that
only one row (deterministic) will emerge:

SELECT *
FROM ContactEmail
WHERE ContactId = 993 AND
      Id IN (SELECT MIN(Id)
             FROM ContactEmail
             WHERE ContactId = 993
             GROUP BY EmailAddress
            );

In 5.7 they can also use any_value.

A similar issue was pointed out by Guilhem :
https://bugs.mysql.com/bug.php?id=68254

** Changed in: percona-server/5.6
       Status: Triaged => Invalid

** Bug watch added: MySQL Bug System #68254
   http://bugs.mysql.com/bug.php?id=68254

-- 
You received this bug notification because you are a member of Ubuntu
Server/Client Support Team, which is subscribed to MySQL.
Matching subscriptions: Ubuntu Server/Client Support Team
https://bugs.launchpad.net/bugs/1453337

Title:
  5.6 incompatible change in DELETE  ... WHERE  NOT IN (subquery)

To manage notifications about this bug go to:
https://bugs.launchpad.net/mysql-server/+bug/1453337/+subscriptions