attached_condition: How EXPLAIN FORMAT=JSON can spell-check your queries

attached_conditionWhen you work with complicated queries, especially ones which contain subqueries, it is easy to make a typo or misinterpret column name. While in many cases you will receive a  column not found error, sometimes you can get strange results instead.

Like finding 4079 countries in Antarctica:

Or not finding any cities in Georgia:

I used a standard world database for these examples.

The reason for both errors is misplacing the column names in the two tables. While it may look simple when you already know what is wrong, I’ve seen support tickets where users were puzzled by simple queries like these.

I always recommended my customers examine their queries, and use EXPLAIN EXTENDED, followed by SHOW WARNINGS. Then thoroughly examine resulting query.

For example, for the query result mentioned above, we can see something like this:

Note  (Code 1276): Field or reference 'world.City.CountryCode' of SELECT #2 was resolved in SELECT #1 tells us what is wrong, but without knowledge of the table definition it is hard to find out why SELECT #2 was resolved in SELECT #1. It also doesn’t give us much information if SELECT #1 uses more than one table in JOIN. For example,

It is not clear if world.City.District  was resolved from table City or CountryLanguage .

EXPLAIN FORMAT=JSON gives us this information.