Bug #73357 Wrong result on query involving information_schema

• Submitted: 22 Jul 2014 14:42
• Modified: 22 Jul 2014 19:09
• Reporter: Elena Stepanova
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.6, 5.7
• OS: Any
• 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-