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.