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.

All the details are in the  attached_condition  element, which contains part of the  WHERE clause, attached to this particular table.

Unfortunately the  EXPLAIN output cannot be saved in a variable, which can then later be proceeded by JSON functions, but we can set a pager to filter the  table_name  and attached_condition  keywords:

Knowing that JSON structure is hierarchical,we clearly see what condition (`world`.`City`.`District` = 'Georgia') was resolved from table City  while condition (`world`.`Country`.`Continent` = 'Asia')  belongs to table Country.  We can conclude that table Country  probably does not have a field named District, while table City  does. The  SHOW CREATE TABLE  statements confirm this guess:

This is the reason why the field District  was resolved from the table City, and not Country.

Conclusion: EXPLAIN FORMAT=JSON  can help you find if the column names are mixed up in the query, even without knowledge of the table definitions.

Share this post

Comments (2)

  • Øystein Grøvlen

    Hi Sveta,

    Thanks for a great example of the value of EXPLAIN FORMAT=JSON! I just want to comment that EXPLAIN EXTENDED has been deprecated. Starting with MySQL 5.7, the basic EXPLAIN command will include the extra columns and warnings that you earlier got from EXPLAIN EXTENDED.

    December 9, 2015 at 3:02 am
  • Sergei Petrunia

    And MariaDB will show it as :

    “attached_condition”: “(Country.Continent = ‘Asia’)”

    that is, without excessive qouting or excessive use of “dbname.” everywhere (dbname is removed if it denotes the current database).

    On the topic itself: the problem with attached_condition is that EXPLAIN (in both MariaDB and MySQL) does a lot of transformations before the query is printed back. I think, CREATE VIEW and SHOW CREATE VIEW will do a better job here. These commands will show which table the field is from, without applying any optimizations to the WHERE clause.

    December 9, 2015 at 11:57 am

Comments are closed.