What does ER_WARN_FIELD_RESOLVED mean?

Question

When SHOW WARNINGS after an EXPLAIN EXTENDED shows a

Note 1276 Field or reference 'test.foo.bar' of SELECT #2 was resolved in SELECT #1

What exactly does that mean and what impact does it have? In my case, it prevents MySQL from using what seems to be a perfectly good index. But it's not about fixing that specific query (as it is an irrelevant test).

I found http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html but

Error: 1276 SQLSTATE: HY000 (ER_WARN_FIELD_RESOLVED)
Message: Field or reference '%s%s%s%s%s' of SELECT #%d was resolved in SELECT #%d

Isn't much of an explanation?

Answer 1

You may want to use EXPLAIN in JSON format, using:

EXPLAIN FORMAT=JSON SELECT ...

It gives you a better picture how MySQL interprets the query, the JSON structure is hierarchical. But the outcome highly depends on the query and table structure.

In my case the warning was in perfectly fine WHERE clause in EXISTS subquery. This over-simplified query still produces the warning:

EXPLAIN SELECT a.id
FROM a
WHERE a.id = 1
AND EXISTS(SELECT 1 FROM c WHERE c.id = a.id)

What I figured out is, EXPLAIN was having problem with the c.id = a.id, because I already established, that a.id = 1, so the proper EXISTS, according to my ancient MySQL 5.7.9, would be:

EXISTS(SELECT 1 FROM c WHERE c.id = 1)