Bug #70491 SELECT DISTINCT may return a wrong result if a join buffer is involved
Submitted: 2 Oct 2013 19:04 Modified: 2 Oct 2013 20:34
Reporter: Igor Babaev Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.6.13 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression
Triage: Needs Triage: D2 (Serious)

[2 Oct 2013 19:04] Igor Babaev
Description:
For a join over several tables one of which is 'materialized' view that employs a join buffer SELECT DISTINCT returns a wrong result depending of the settings for
@@join_buffer_size:
mysql> set join_buffer_size=1024;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT DISTINCT alias1.id  FROM t1 AS alias1 STRAIGHT_JOIN (v1 AS alias2 STRAIGHT_JOIN t2 AS alias3  ON alias2.id = alias3.i) ON alias1.i1 = alias2.i1  WHERE alias3.i != 3;
+------+
| id   |
+------+
|    7 |
|    9 |
|   18 |
|   20 |
|   24 |
|   43 |
|   45 |
|   50 |
|   61 |
|   64 |
|   71 |
|   74 |
|   77 |
|   78 |
|   93 |
|   97 |
|  100 |
|  101 |
|  104 |
|  107 |
|  114 |
|  119 |
|  128 |
|  129 |
|  132 |
|  133 |
|  140 |
|  151 |
|  156 |
+------+
29 rows in set (0.02 sec)
mysql> set join_buffer_size=1024*16;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT DISTINCT alias1.id  FROM t1 AS alias1 STRAIGHT_JOIN (v1 AS alias2 STRAIGHT_JOIN t2 AS alias3  ON alias2.id = alias3.i) ON alias1.i1 = alias2.i1  WHERE alias3.i != 3;
+------+
| id   |
+------+
|    7 |
|    9 |
|   18 |
|   20 |
|    8 |
|   24 |
|   43 |
|   50 |
|   51 |
|   61 |
|   64 |
|   71 |
|   74 |
|   77 |
|   78 |
|   94 |
|   97 |
|  100 |
|  104 |
|   93 |
|  101 |
|  107 |
|  114 |
|  119 |
|  128 |
|  129 |
|  140 |
|  132 |
|  133 |
|  151 |
|  156 |
+------+
31 rows in set (0.02 sec)

How to repeat:
Create and populate the tables t1,t2 and the view v1 with the following commands

CREATE TABLE t1 (
  id INT,
  i1 INT,
  i2 INT DEFAULT 0,
  d1 DATE DEFAULT '2000-01-01',
  d2 DATE DEFAULT '2000-01-01',
  t1 TIME DEFAULT '00:00:00',
  t2 TIME DEFAULT '00:00:00',
  dt1 DATETIME NOT NULL DEFAULT '2000-01-01 00:00:00',
  dt2 DATETIME NOT NULL DEFAULT '2000-01-01 00:00:00',
  c1 VARCHAR(1) NOT NULL,
  c2 VARCHAR(1) NOT NULL
) ENGINE=MyISAM;

CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;

INSERT INTO t1 (id,i1,c1,c2) VALUES 
(1,7,'t','t'),(2,4,'k','k'),(3,2,'e','e'),(4,0,'i','i'),(5,1,'t','t'),(6,91,'m','m'),(7,6,'z','z'),(8,3,'c','c'),(9,6,'i','i'),(10,8,'v','v'),
(11,1,'l','l'),(12,4,'j','j'),(13,5,'w','w'),(14,0,'r','r'),(15,7,'o','o'),(16,9,'o','o'),(17,7,'u','u'),(18,6,'f','f'),(19,0,'l','l'),(20,6,'g','g'),
(21,1,'e','e'),(22,7,'y','y'),(23,0,'p','p'),(24,6,'v','v'),(25,5,'d','d'),(26,9,'i','i'),(27,5,'z','z'),(28,2,'q','q'),(29,4,'j','j'),(30,9,'m','m'),
(31,8,'d','d'),(32,5,'r','r'),(33,1,'r','r'),(34,1,'k','k'),(35,4,'p','p'),(36,2,'x','x'),(37,5,'w','w'),(38,0,'k','k'),(39,7,'y','y'),(40,4,'p','p'),
(41,9,'l','l'),(42,2,'u','u'),(43,3,'r','r'),(44,5,'y','y'),(45,3,'u','u'),(46,9,'t','t'),(47,8,'f','f'),(48,2,'f','f'),(49,2,'q','q'),(50,6,'v','v'),
(51,6,'u','u'),(52,0,'b','b'),(53,1,'n','n'),(54,2,'p','p'),(55,0,'y','y'),(56,1,'l','l'),(57,1,'c','c'),(58,0,'d','d'),(59,2,'y','y'),(60,7,'l','l'),
(61,6,'m','m'),(62,9,'q','q'),(63,0,'j','j'),(64,3,'u','u'),(65,4,'w','w'),(66,5,'p','p'),(67,8,'z','z'),(68,5,'u','u'),(69,7,'b','b'),(70,0,'f','f'),
(71,6,'u','u'),(72,1,'i','i'),(73,9,'s','s'),(74,3,'y','y'),(75,5,'s','s'),(76,8,'x','x'),(77,3,'s','s'),(78,3,'l','l'),(79,8,'b','b'),(80,0,'p','p'),
(81,9,'m','m'),(82,5,'k','k'),(83,7,'u','u'),(84,0,'y','y'),(85,2,'x','x'),(86,5,'h','h'),(87,5,'j','j'),(88,5,'o','o'),(89,9,'o','o'),(90,1,'c','c'),
(91,7,'k','k'),(92,9,'t','t'),(93,3,'h','h'),(94,6,'g','g'),(95,9,'r','r'),(96,2,'i','i'),(97,6,'l','l'),(98,4,'a','a'),(99,9,'w','w'),(100,6,'n','n'),
(101,3,'c','c'),(102,1,'a','a'),(103,9,'z','z'),(104,6,'k','k'),(105,7,'a','a'),(106,7,'g','g'),(107,3,'k','k'),(108,8,'h','h'),(109,7,'y','y'),(110,1,'d','d'),
(111,0,'p','p'),(112,4,'b','b'),(113,2,'n','n'),(114,6,'l','l'),(115,0,'s','s'),(116,0,'n','n'),(117,8,'o','o'),(118,9,'f','f'),(119,6,'u','u'),(120,1,'j','j'),
(121,1,'q','q'),(122,8,'y','y'),(123,1,'c','c'),(124,17,'i','i'),(125,1,'v','v'),(126,0,'w','w'),(127,5,'q','q'),(128,6,'n','n'),(129,6,'x','x'),(130,2,'b','b'),
(131,8,'l','l'),(132,3,'i','i'),(133,3,'d','d'),(134,7,'h','h'),(135,0,'i','i'),(136,1,'n','n'),(137,2,'g','g'),(138,7,'u','u'),(139,0,'d','d'),(140,6,'x','x'),
(141,1,'a','a'),(142,0,'q','q'),(143,9,'n','n'),(144,8,'r','r'),(145,4,'r','r'),(146,4,'g','g'),(147,0,'u','u'),(148,1,'j','j'),(149,0,'m','m'),(150,4,'u','u'),
(151,3,'m','m'),(152,9,'p','p'),(153,1,'o','o'),(154,8,'v','v'),(155,9,'f','f'),(156,6,'f','f'),(157,1,'e','e'),(158,5,'l','l'),(159,8,'j','j');

CREATE TABLE t2 (i INT) ENGINE=MyISAM;
INSERT INTO t2 VALUES (7),(8);

Execute the query:

SELECT DISTINCT alias1.id  FROM t1 AS alias1 STRAIGHT_JOIN (v1 AS alias2 STRAIGHT_JOIN t2 AS alias3  ON alias2.id = alias3.i) ON alias1.i1 = alias2.i1  WHERE alias3.i != 3;

with different settings of @@join_buffer_size.
[2 Oct 2013 20:34] Miguel Solorzano
Thank you for the bug report. Verified as described.
[14 Nov 2013 12:43] Hartmut Holzgraefe
5.6 regression, not reproducible on 5.5