[MDEV-5028] Wrong result (missing row) on LEFT JOIN with a view, DISTINCT - JIRA

Details

Type: Bug
Status: Closed
Priority: Major
Resolution: Fixed
Affects Version/s: 10.0.4, 5.5.33, 5.3.13
Fix Version/s: 5.5.34, 10.0.6, 5.3.13
Component/s: None
Labels:
None

Description

The problem appeared in the 5.3 tree with the following revision:

revno: 3645 [merge]
revision-id: igor@askmonty.org-20130327155816-ocjsyxyqgcsraf0s
    ------------------------------------------------------------
    revno: 3639.1.1
    revision-id: igor@askmonty.org-20130323043306-2cteo3ndk22af921
    message:
      Fixed bug mdev-4318.
      In some cases, when using views the optimizer incorrectly determined
      possible join orders for queries with nested outer and inner joins.
      This could lead to invalid execution plans for such queries.

With the data provided in the test case, the test query produces a result set consisting of 31 rows:

SELECT DISTINCT alias1.id 
FROM t1 AS alias1 LEFT JOIN v1 AS alias2 LEFT JOIN t2 AS alias3
ON alias2.id = alias3.i ON alias1.i1 = alias2.i1
WHERE alias3.i != 3;
id
7
9
18
20
24
50
51
61
71
94
97
100
104
114
119
128
129
140
8
43
45
64
74
77
78
93
101
107
132
133
156

However, a similar query with an extended select list (but the same joins and WHERE clause) produces 32 rows:

SELECT DISTINCT alias1.id, alias2.id, alias3.i, alias1.i1, alias2.i1 
FROM t1 AS alias1 LEFT JOIN v1 AS alias2 LEFT JOIN t2 AS alias3
ON alias2.id = alias3.i ON alias1.i1 = alias2.i1
WHERE alias3.i != 3;
id      id      i       i1      i1
7       7       7       6       6
9       7       7       6       6
18      7       7       6       6
20      7       7       6       6
24      7       7       6       6
50      7       7       6       6
51      7       7       6       6
61      7       7       6       6
71      7       7       6       6
94      7       7       6       6
97      7       7       6       6
100     7       7       6       6
104     7       7       6       6
114     7       7       6       6
119     7       7       6       6
128     7       7       6       6
129     7       7       6       6
140     7       7       6       6
8       8       8       3       3
43      8       8       3       3
45      8       8       3       3
64      8       8       3       3
74      8       8       3       3
77      8       8       3       3
78      8       8       3       3
93      8       8       3       3
101     8       8       3       3
107     8       8       3       3
132     8       8       3       3
133     8       8       3       3
156     7       7       6       6
151     8       8       3       3

The last row, with alias1.id=151, is missing from the first list. It seems to satisfy the condition, so I presume 32 rows is the correct result and 31 rows is not.
SELECT COUNT and other variations of the query also return 32 rows. With join_cache_level=0, the initial query also returns 32 rows.

Test case:

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);

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

EXPLAIN:

EXPLAIN EXTENDED
SELECT DISTINCT alias1.id FROM t1 AS alias1 LEFT JOIN v1 AS alias2 LEFT JOIN t2 AS alias3
ON alias2.id = alias3.i ON alias1.i1 = alias2.i1
WHERE alias3.i != 3;
id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
1       PRIMARY alias1  ALL     NULL    NULL    NULL    NULL    159     100.00  Using where; Using temporary
1       PRIMARY <derived2>      ref     key0    key0    5       test.alias1.i1  10      100.00  Using where; Distinct
1       PRIMARY alias3  ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Distinct; Using join buffer (flat, BNL join)
2       DERIVED t1      ALL     NULL    NULL    NULL    NULL    159     100.00
Warnings:
Note    1003    select distinct `test`.`alias1`.`id` AS `id` from `test`.`t1` `alias1` join `test`.`v1` `alias2` join `test`.`t2` `alias3` where ((`test`.`alias3`.`i` = `alias2`.`id`) and (`alias2`.`i1` = `test`.`alias1`.`i1`) and (`alias2`.`id` <> 3))

Gliffy Diagrams

Attachments

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Igor Babaev added a comment - 2013-10-01 19:35 - edited

Before the fix for mdev-4318 (rev 3639)
the query had the following execution plan:
MariaDB [test]> EXPLAIN EXTENDED SELECT DISTINCT alias1.id FROM t1 AS alias1 LEFT JOIN v1 AS alias2 LEFT JOIN t2 AS alias3 ON alias2.id = alias3.i ON alias1.i1 = alias2.i1 WHERE alias3.i != 3;
-----------------------------------------------------------------------------------------------------------------------------

id

select_type

table

type

possible_keys

key

key_len

ref

rows

filtered

Extra

-----------------------------------------------------------------------------------------------------------------------------

1	PRIMARY	alias3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using temporary
1	PRIMARY	<derived2>	ref	key1	key1	5	test.alias3.i	10	100.00
1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	159	100.00	Using where; Using join buffer (flat, BNL join)
2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	159	100.00

-----------------------------------------------------------------------------------------------------------------------------

Show

Igor Babaev added a comment - 2013-10-01 19:35 - edited Before the fix for mdev-4318 (rev 3639) the query had the following execution plan: MariaDB [test] > EXPLAIN EXTENDED SELECT DISTINCT alias1.id FROM t1 AS alias1 LEFT JOIN v1 AS alias2 LEFT JOIN t2 AS alias3 ON alias2.id = alias3.i ON alias1.i1 = alias2.i1 WHERE alias3.i != 3; --- ----------- ---------- ---- ------------- ---- ------- ------------- ---- -------- ------------------------------------------------ id select_type table type possible_keys key key_len ref rows filtered Extra --- ----------- ---------- ---- ------------- ---- ------- ------------- ---- -------- ------------------------------------------------ 1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary 1 PRIMARY <derived2> ref key1 key1 5 test.alias3.i 10 100.00 1 PRIMARY alias1 ALL NULL NULL NULL NULL 159 100.00 Using where; Using join buffer (flat, BNL join) 2 DERIVED t1 ALL NULL NULL NULL NULL 159 100.00 --- ----------- ---------- ---- ------------- ---- ------- ------------- ---- -------- ------------------------------------------------

Hide

Permalink

Igor Babaev added a comment - 2013-10-02 23:45

Both left joins of the query a converted into inner joins:
MariaDB [test]> EXPLAIN EXTENDED SELECT DISTINCT alias1.id FROM t1 AS alias1 LEFT JOIN v1 AS alias2 LEFT JOIN t2 AS alias3 ON alias2.id = alias3.i ON alias1.i1 = alias2.i1 WHERE alias3.i != 3;
----------------------------------------------------------------------------------------------------------------------------------------

id

select_type

table

type

possible_keys

key

key_len

ref

rows

filtered

Extra

----------------------------------------------------------------------------------------------------------------------------------------

1	PRIMARY	alias1	ALL	idx	NULL	NULL	NULL	159	100.00	Using where; Using temporary
1	PRIMARY	<derived2>	ref	key0	key0	5	test.alias1.i1	10	100.00	Using where; Distinct
1	PRIMARY	alias3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Distinct; Using join buffer (flat, BNL join)
2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	159	100.00

----------------------------------------------------------------------------------------------------------------------------------------
4 rows in set, 1 warning (0.01 sec)

MariaDB [test]> show warnings;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Level

Code

Message

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Note

1003

select distinct `test`.`alias1`.`id` AS `id` from `test`.`t1` `alias1` join `test`.`v1` `alias2` join `test`.`t2` `alias3` where ((`test`.`alias3`.`i` = `alias2`.`id`) and (`alias2`.`i1` = `test`.`alias1`.`i1`) and (`alias2`.`id` <> 3))

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

Show

Igor Babaev added a comment - 2013-10-02 23:45 Both left joins of the query a converted into inner joins: MariaDB [test] > EXPLAIN EXTENDED SELECT DISTINCT alias1.id FROM t1 AS alias1 LEFT JOIN v1 AS alias2 LEFT JOIN t2 AS alias3 ON alias2.id = alias3.i ON alias1.i1 = alias2.i1 WHERE alias3.i != 3; --- ----------- ---------- ---- ------------- ---- ------- -------------- ---- -------- ---------------------------------------------------------- id select_type table type possible_keys key key_len ref rows filtered Extra --- ----------- ---------- ---- ------------- ---- ------- -------------- ---- -------- ---------------------------------------------------------- 1 PRIMARY alias1 ALL idx NULL NULL NULL 159 100.00 Using where; Using temporary 1 PRIMARY <derived2> ref key0 key0 5 test.alias1.i1 10 100.00 Using where; Distinct 1 PRIMARY alias3 ALL NULL NULL NULL NULL 2 100.00 Using where; Distinct; Using join buffer (flat, BNL join) 2 DERIVED t1 ALL NULL NULL NULL NULL 159 100.00 --- ----------- ---------- ---- ------------- ---- ------- -------------- ---- -------- ---------------------------------------------------------- 4 rows in set, 1 warning (0.01 sec) MariaDB [test] > show warnings; ------ ---- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Level Code Message ------ ---- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Note 1003 select distinct `test`.`alias1`.`id` AS `id` from `test`.`t1` `alias1` join `test`.`v1` `alias2` join `test`.`t2` `alias3` where ((`test`.`alias3`.`i` = `alias2`.`id`) and (`alias2`.`i1` = `test`.`alias1`.`i1`) and (`alias2`.`id` <> 3)) ------ ---- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 row in set (0.00 sec)

Hide

Permalink

Igor Babaev added a comment - 2013-10-02 23:51

The following query without left joins display the same problems:

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;

MariaDB [test]> set join_buffer_size=256;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SELECT DISTINCT alias1.id FROM t1 AS alias1 LEFT JOIN v1 AS alias2 LEFT 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
93
94
97
100
101
104
107
114
119
128
129
132
140
151
156

------
28 rows in set (0.03 sec)

MariaDB [test]> set join_buffer_size=default;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> 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
50
51
61
71
94
97
100
104
114
119
128
129
140
8
43
45
64
74
77
78
93
101
107
132
133
156

------
31 rows in set (0.03 sec)

We see that the number of rows in the result set depends on the setting for @@join_buffer_size.

Show

Igor Babaev added a comment - 2013-10-02 23:51 The following query without left joins display the same problems: 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; MariaDB [test] > set join_buffer_size=256; Query OK, 0 rows affected (0.00 sec) MariaDB [test] > SELECT DISTINCT alias1.id FROM t1 AS alias1 LEFT JOIN v1 AS alias2 LEFT 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 93 94 97 100 101 104 107 114 119 128 129 132 140 151 156 ------ 28 rows in set (0.03 sec) MariaDB [test] > set join_buffer_size=default; Query OK, 0 rows affected (0.00 sec) MariaDB [test] > 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 50 51 61 71 94 97 100 104 114 119 128 129 140 8 43 45 64 74 77 78 93 101 107 132 133 156 ------ 31 rows in set (0.03 sec) We see that the number of rows in the result set depends on the setting for @@join_buffer_size.

Hide

Permalink

Igor Babaev added a comment - 2013-10-03 04:04

The same problem can be observed in mysql-5.6
I reported the bug http://bugs.mysql.com/70491.

Show

Igor Babaev added a comment - 2013-10-03 04:04 The same problem can be observed in mysql-5.6 I reported the bug http://bugs.mysql.com/70491 .

Hide

Permalink

Igor Babaev added a comment - 2013-10-04 16:59

The fix for this bug was pushed into the 5.3 tree.

Show

Igor Babaev added a comment - 2013-10-04 16:59 The fix for this bug was pushed into the 5.3 tree.

Wrong result (missing row) on LEFT JOIN with a view, DISTINCT

Details

Description

Gliffy Diagrams

Attachments

Activity

People

Dates

Agile