Details
Major Description
CREATE TABLE t1 (a INT) ENGINE=MyISAM; |
INSERT INTO t1 VALUES (1); |
|
|
CREATE TABLE t2 (b INT) ENGINE=MyISAM; |
INSERT INTO t2 VALUES (1),(1); |
|
|
CREATE TABLE t3 (c INT PRIMARY KEY) ENGINE=MyISAM; |
|
|
ANALYZE TABLE t1, t2, t3 PERSISTENT FOR ALL; # Optional, fails either way |
|
|
SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b = t3.c) ON t1.a = t2.b; |
SELECT COUNT(*) FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b = t3.c) ON t1.a = t2.b; |
|
|
DROP TABLE t1, t2, t3; |
|
10.5 9d388192 |
SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b = t3.c) ON t1.a = t2.b; |
a b c
|
1 NULL NULL |
SELECT COUNT(*) FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b = t3.c) ON t1.a = t2.b; |
COUNT(*) |
2
|
The expected result is (unless I'm mistaken)
a b c
|
1 1 NULL |
1 1 NULL |
so the actual result set is wrong both in the number of rows and in the values, while the COUNT result is correct.
Reproducible on 10.5+. Could not reproduce on 10.4.
|
Plan on 10.5 |
EXPLAIN FORMAT=JSON SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b = t3.c) ON t1.a = t2.b; |
EXPLAIN {
|
"query_block": { |
"select_id": 1, |
"const_condition": "1", |
"table": { |
"table_name": "t1", |
"access_type": "system", |
"rows": 1, |
"filtered": 100 |
},
|
"table": { |
"table_name": "t3", |
"access_type": "const", |
"possible_keys": ["PRIMARY"], |
"rows": 1, |
"filtered": 100, |
"impossible_on_condition": true |
},
|
"table": { |
"table_name": "t2", |
"access_type": "const", |
"rows": 1, |
"filtered": 100, |
"impossible_on_condition": true |
}
|
}
|
}
|