Bug #73357 Wrong result on query involving information_schema
Submitted: 22 Jul 2014 14:42 Modified: 22 Jul 2014 19:09
Reporter: Elena Stepanova Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.6, 5.7 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[22 Jul 2014 14:42] Elena Stepanova
Description:
Initially reported as https://mariadb.atlassian.net/browse/MDEV-6289

Actual result of the provided test case:

MySQL [test]> SELECT `db` FROM `test1` WHERE `db` IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA) ORDER BY `db` DESC;
Empty set (0.00 sec)

Expected result:

MySQL [test]> SELECT `db` FROM `test1` WHERE `db` IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA) ORDER BY `db` DESC;
+-------+
| db    |
+-------+
| test4 |
| test3 |
| test2 |
| test1 |
+-------+
4 rows in set (0.00 sec)

How to repeat:
DROP TABLE IF EXISTS `test1`;
CREATE TABLE `test1` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `db` varchar(254) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `db` (`db`)
) DEFAULT CHARSET=utf8;
INSERT INTO `test1` (`db`) VALUES ('test1'),('test2'),('test3'),('test4');

create database test1;
create database test2;
create database test3;
create database test4;

SELECT `db` FROM `test1` WHERE `db` IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA) ORDER BY `db` DESC;
[22 Jul 2014 14:55] Peter Laursen
These queries behave as expected:
SELECT `db` FROM `test1` WHERE `db` LIKE 'test1';
SELECT `db` FROM `test1` WHERE `db` = 'test1';

This is hillarious IMO! :-(
And "S3" is too low severity IMO.

-- Peter
-- not a MySQL/Oracle person
[22 Jul 2014 19:09] Sveta Smirnova
Thank you for the report.

Verified as described. Not repeatable with 5.5.39-