| Bug #74311 | scalar subqueries in a comparison produced unexpected result | ||
|---|---|---|---|
| Submitted: | 10 Oct 2014 9:35 | Modified: | 10 Oct 2014 12:08 |
| Reporter: | Kenneth Cruz | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.6.17 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
[10 Oct 2014 12:08]
MySQL Verification Team
Thank you for the bug report. Please check latest released version. I couldn't repeat with source build:
C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --debug-info --prompt="mysql 5.6 > "
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.22 Source distribution
Copyright (c) 2000, 2014, 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 > --
mysql 5.6 > -- Database dump: `test_mdb`
mysql 5.6 > --
mysql 5.6 > CREATE DATABASE `test_mdb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
Query OK, 1 row affected (0.02 sec)
mysql 5.6 > USE `test_mdb`;
Database changed
mysql 5.6 >
mysql 5.6 > -- --------------------------------------------------------
mysql 5.6 >
mysql 5.6 > --
mysql 5.6 > -- Table structure for table `projects`
mysql 5.6 > --
mysql 5.6 > CREATE TABLE IF NOT EXISTS `projects` (
-> `project_number` varchar(50) NOT NULL,
-> PRIMARY KEY (`project_number`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.09 sec)
mysql 5.6 >
mysql 5.6 > --
mysql 5.6 > -- Dumping data for table `projects`
mysql 5.6 > --
mysql 5.6 >
mysql 5.6 > INSERT INTO `projects` (`project_number`) VALUES
-> ('aaa'),
-> ('bbb');
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql 5.6 >
mysql 5.6 > -- --------------------------------------------------------
mysql 5.6 >
mysql 5.6 > --
mysql 5.6 > -- Table structure for table `projects_history`
mysql 5.6 > --
mysql 5.6 >
mysql 5.6 > CREATE TABLE IF NOT EXISTS `projects_history` (
-> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
-> `project_number` varchar(50) NOT NULL,
-> `history_date` date NOT NULL,
-> `country` varchar(50) NOT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
Query OK, 0 rows affected (0.06 sec)
mysql 5.6 >
mysql 5.6 > --
mysql 5.6 > -- Dumping data for table `projects_history`
mysql 5.6 > --
mysql 5.6 >
mysql 5.6 > INSERT INTO `projects_history` (`id`, `project_number`, `history_date`, `country`) VALUES
-> (1, 'aaa', '2014-08-09', 'france'),
-> (2, 'aaa', '2014-09-09', 'singapore');
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql 5.6 >
mysql 5.6 > -- --------------------------------------------------------
mysql 5.6 >
mysql 5.6 > --
mysql 5.6 > -- Table structure for table `region`
mysql 5.6 > --
mysql 5.6 >
mysql 5.6 > CREATE TABLE IF NOT EXISTS `region` (
-> `region` varchar(50) NOT NULL,
-> `country` varchar(50) NOT NULL
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.06 sec)
mysql 5.6 >
mysql 5.6 > --
mysql 5.6 > -- Dumping data for table `region`
mysql 5.6 > --
mysql 5.6 >
mysql 5.6 > INSERT INTO `region` (`region`, `country`) VALUES
-> ('apac', 'singapore'),
-> ('eame', 'france');
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql 5.6 > SELECT a.project_number FROM projects a WHERE ( SELECT z.country FROM projects_history z WHERE z.project_number = a.project_number AND z.history_date <= '2014-09-01' ORDER BY z.id DESC LIMIT 1 ) IN ( SELECT r.coun
try FROM region r WHERE r.region = 'eame' );
+----------------+
| project_number |
+----------------+
| aaa |
+----------------+
1 row in set (0.03 sec)
mysql 5.6 >
Description: I wrote a SQL statement with scalar subquery (with "ORDER BY" and "LIMIT 1") and use that subquery in a comparison. When I executed that SQL statement using MySQL 5.6.17, it returns an empty set. Please see below for the SQL statement, DB dump used, Actual and Expected Results and Explain info. Please note that executing the SQL statement in MySQL 5.0.96 will produce the expected result. SQL statement: "SELECT a.project_number FROM projects a WHERE ( SELECT z.country FROM projects_history z WHERE z.project_number = a.project_number AND z.history_date <= '2014-09-01' ORDER BY z.id DESC LIMIT 1 ) IN ( SELECT r.country FROM region r WHERE r.region = 'eame' )" Expected Result: +----------------+ | project_number | +----------------+ | aaa | +----------------+ Actual Result from MySQL (5.6.17) without "SQL_NO_CACHE": <Empty set> Actual Result from MySQL (5.6.17) with "SQL_NO_CACHE": +----------------+ | project_number | +----------------+ | aaa | | bbb | +----------------+ EXPLAIN from MySQL (5.6.17): +------+--------------------+-------------+--------+---------------+--------------+---------+------+------+-------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------------+-------------+--------+---------------+--------------+---------+------+------+-------------------------------------------------------------------+ | 1 | PRIMARY | a | index | NULL | PRIMARY | 52 | NULL | 2 | Using index | | 1 | PRIMARY | r | ALL | NULL | NULL | NULL | NULL | 2 | Using where; FirstMatch(a); Using join buffer (Block Nested Loop) | | 2 | DEPENDENT SUBQUERY | z | index | NULL | PRIMARY | 4 | NULL | 1 | Using where | +------+--------------------+-------------+--------+---------------+--------------+---------+------+------+-------------------------------------------------------------------+ How to repeat: Import the database structure and data (please refer below). Then, execute the SQL statement mentioned in the description. -- -- Database dump: `test_mdb` -- CREATE DATABASE `test_mdb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; USE `test_mdb`; -- -------------------------------------------------------- -- -- Table structure for table `projects` -- CREATE TABLE IF NOT EXISTS `projects` ( `project_number` varchar(50) NOT NULL, PRIMARY KEY (`project_number`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table `projects` -- INSERT INTO `projects` (`project_number`) VALUES ('aaa'), ('bbb'); -- -------------------------------------------------------- -- -- Table structure for table `projects_history` -- CREATE TABLE IF NOT EXISTS `projects_history` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `project_number` varchar(50) NOT NULL, `history_date` date NOT NULL, `country` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; -- -- Dumping data for table `projects_history` -- INSERT INTO `projects_history` (`id`, `project_number`, `history_date`, `country`) VALUES (1, 'aaa', '2014-08-09', 'france'), (2, 'aaa', '2014-09-09', 'singapore'); -- -------------------------------------------------------- -- -- Table structure for table `region` -- CREATE TABLE IF NOT EXISTS `region` ( `region` varchar(50) NOT NULL, `country` varchar(50) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table `region` -- INSERT INTO `region` (`region`, `country`) VALUES ('apac', 'singapore'), ('eame', 'france');