The problem can be demonstrated with MyISAM as well with the test case:
CREATE TABLE t1 (a int); INSERT INTO t1 VALUES (0), (0);
CREATE TABLE t2 (a int, b int, c int); INSERT INTO t2 VALUES (10,7,0), (0,7,0);
CREATE TABLE t3 (a int, b int); INSERT INTO t3 VALUES (10,7), (0,7);
SELECT SUM(DISTINCT b), (SELECT t2.a FROM t1 JOIN t2 ON t2.c != 0 WHERE t.a != 0 AND t2.a != 0) FROM (SELECT * FROM t3) AS t GROUP BY 2;
The problem can be demonstrated with MyISAM as well with the test case:
CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES (0), (0);
CREATE TABLE t2 (a int, b int, c int);
INSERT INTO t2 VALUES (10,7,0), (0,7,0);
CREATE TABLE t3 (a int, b int);
INSERT INTO t3 VALUES (10,7), (0,7);
SELECT SUM(DISTINCT b),
(SELECT t2.a FROM t1 JOIN t2 ON t2.c != 0
WHERE t.a != 0 AND t2.a != 0)
FROM (SELECT * FROM t3) AS t
GROUP BY 2;