The following queries should return identical results, but they do not:

CREATE TABLE t1 (f1 INT, KEY(f1)) ENGINE=MyISAM;

INSERT t1 VALUES (4),(8);

CREATE TABLE t2 (f2 INT, KEY(f2)) ENGINE=MyISAM;

INSERT t2 VALUES (6),(9);

SELECT (SELECT MAX(t2.f2) FROM t1) FROM t2 WHERE f2 = 2;

This one returns NULL

While this one:

CREATE TABLE t1 (f1 INT) ENGINE=MyISAM;

INSERT t1 VALUES (4),(8);

CREATE TABLE t2 (f2 INT) ENGINE=MyISAM;

INSERT t2 VALUES (6),(9);

SELECT (SELECT MAX(t2.f2) FROM t1) FROM t2 WHERE f2 = 2;

Returns: Error: Subquery returns more than 1 row

This test case is from MDEV-7828 Assertion `key_read == 0' failed in TABLE::enable_keyread with SELECT SQ and WHERE SQ

10.0 behaves the same as the last example, with or without KEY being present.

• NOTE * The query succeeds as 5.5 in 10.0 if subquery_cache=off is set.