| Bug #55486 | Wrong result with DISTINCT inside a correlated subquery | ||
|---|---|---|---|
| Submitted: | 22 Jul 2010 16:08 | Modified: | 15 Oct 2012 13:45 |
| Reporter: | Philip Stoev | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.1.50-bzr, 5.5.6-m3-debug | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[22 Jul 2010 16:08]
Philip Stoev
[22 Jul 2010 17:47]
Valeriy Kravchuk
Thank you for the bug report. Verified on Mac OS X:
macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.50-debug Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE `CC` (
-> `pk` int(11) NOT NULL AUTO_INCREMENT,
-> `col_int_key` int(11) DEFAULT NULL,
-> `col_varchar_key` varchar(1) DEFAULT NULL,
-> `col_varchar_nokey` varchar(1) DEFAULT NULL,
-> PRIMARY KEY (`pk`),
-> KEY `col_int_key` (`col_int_key`),
-> KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
-> ) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,8,'v','v');
INSERT INTO `CC` VALUES (11,9,'r','r');
INSERT INTO `CC` VALUES (12,9,'a','a');
INSERT INTO `CC` VALUES (13,186,'m','m');
INSERT INTO `CC` VALUES (14,NULL,'y','y');
INSERT INTO `CC` VALUES (15,2,'j','j');
INSERT INTO `CC` VALUES (16,3,'d','d');
INSERT INTO `CC` VALUES (17,0,'z','z');
INSERT INTO `CC` VALUES (18,133,'e','e');
INSERT INTO `CC` VALUES (19,1,'h','h');
INSERT INTO `CC` VALUES (20,8,'b','b');
INSERT INTO `CC` VALUES (21,5,'s','s');
INSERT INTO `CC` VALUES (22,5,'e','e');
INSERT INTO `CC` VALUES (23,8,'j','j');
INSERT INTO `CC` VALUES (24,6,'e','e');
INSERT INTO `CC` VALUES (25,51,'f','f');
INSERT INTO `CC` VALUES (26,4,'v','v');
INSERT INTO `CC` VALUES (27,7,'x','x');
INSERT INTO `CC` VALUES (28,6,'m','m');
INSERT INTO `CC` VALUES (29,4,'c','c');
Query OK, 0 rows affected (0.07 sec)
mysql> INSERT INTO `CC` VALUES (10,8,'v','v');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `CC` VALUES (11,9,'r','r');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `CC` VALUES (12,9,'a','a');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `CC` VALUES (13,186,'m','m');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `CC` VALUES (14,NULL,'y','y');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `CC` VALUES (15,2,'j','j');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `CC` VALUES (16,3,'d','d');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `CC` VALUES (17,0,'z','z');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `CC` VALUES (18,133,'e','e');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `CC` VALUES (19,1,'h','h');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `CC` VALUES (20,8,'b','b');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `CC` VALUES (21,5,'s','s');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `CC` VALUES (22,5,'e','e');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `CC` VALUES (23,8,'j','j');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `CC` VALUES (24,6,'e','e');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `CC` VALUES (25,51,'f','f');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `CC` VALUES (26,4,'v','v');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `CC` VALUES (27,7,'x','x');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `CC` VALUES (28,6,'m','m');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `CC` VALUES (29,4,'c','c');
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLE `C` (
-> `pk` int(11) NOT NULL AUTO_INCREMENT,
-> `col_int_key` int(11) DEFAULT NULL,
-> `col_varchar_key` varchar(1) DEFAULT NULL,
-> `col_varchar_nokey` varchar(1) DEFAULT NULL,
-> PRIMARY KEY (`pk`),
-> KEY `col_int_key` (`col_int_key`),
-> KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
-> ) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,2,'w','w');
INSERT INTO `C` VALUES (2,9,'m','m');
INSERT INTO `C` VALUES (3,3,'m','m');
INSERT INTO `C` VALUES (4,9,'k','k');
INSERT INTO `C` VALUES (5,NULL,'r','r');
INSERT INTO `C` VALUES (6,9,'t','t');
INSERT INTO `C` VALUES (7,3,'j','j');
INSERT INTO `C` VALUES (8,8,'u','u');
INSERT INTO `C` VALUES (9,8,'h','h');
INSERT INTO `C` VALUES (10,53,'o','o');
INSERT INTO `C` VALUES (11,0,NULL,NULL);
INSERT INTO `C` VALUES (12,5,'k','k');
INSERT INTO `C` VALUES (13,166,'e','e');
INSERT INTO `C` VALUES (14,3,'n','n');
INSERT INTO `C` VALUES (15,0,'t','t');
INSERT INTO `C` VALUES (16,1,'c','c');
INSERT INTO `C` VALUES (17,9,'m','m');
INSERT INTO `C` VALUES (18,5,'y','y');
INSERT INTO `C` VALUES (19,6,'f','f');
INSERT INTO `C` VALUES (20,2,'d','d');
Query OK, 0 rows affected (0.68 sec)
mysql> INSERT INTO `C` VALUES (1,2,'w','w');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `C` VALUES (2,9,'m','m');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `C` VALUES (3,3,'m','m');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `C` VALUES (4,9,'k','k');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `C` VALUES (5,NULL,'r','r');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `C` VALUES (6,9,'t','t');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `C` VALUES (7,3,'j','j');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `C` VALUES (8,8,'u','u');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `C` VALUES (9,8,'h','h');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `C` VALUES (10,53,'o','o');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `C` VALUES (11,0,NULL,NULL);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `C` VALUES (12,5,'k','k');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `C` VALUES (13,166,'e','e');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `C` VALUES (14,3,'n','n');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `C` VALUES (15,0,'t','t');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `C` VALUES (16,1,'c','c');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `C` VALUES (17,9,'m','m');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `C` VALUES (18,5,'y','y');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `C` VALUES (19,6,'f','f');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `C` VALUES (20,2,'d','d');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT col_varchar_key FROM C SUBQUERY1_t1 WHERE EXISTS ( SELECT DISTINCT
-> `col_varchar_key` FROM CC WHERE `col_varchar_key` < SUBQUERY1_t1 .`col_varchar_nokey`
-> );
+-----------------+
| col_varchar_key |
+-----------------+
| w |
| m |
| m |
| k |
| r |
| t |
| j |
| u |
+-----------------+
8 rows in set (0.00 sec)
mysql> SELECT col_varchar_key FROM C SUBQUERY1_t1 WHERE EXISTS ( SELECT DISTINCT
-> `col_varchar_key` FROM CC IGNORE KEY (col_varchar_key) WHERE `col_varchar_key` <
-> SUBQUERY1_t1 .`col_varchar_nokey` );
+-----------------+
| col_varchar_key |
+-----------------+
| w |
| m |
| m |
| k |
| r |
| t |
| j |
| u |
| h |
| o |
| k |
| e |
| n |
| t |
| c |
| m |
| y |
| f |
| d |
+-----------------+
19 rows in set (0.00 sec)
[15 Oct 2012 13:45]
Erlend Dahl
Fixed in 5.6.5
