When 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:
|
1 |
mysql> select count(*) from City where CountryCode in (select CountryCode from Country where Continent = 'Antarctica');<br>+----------+<br>| count(*) |<br>+----------+<br>| 4079 |<br>+----------+<br>1 row in set (0.05 sec) |
Or not finding any cities in Georgia:
|
1 |
mysql> select Name, Language from City join CountryLanguage using (CountryCode) where CountryCode in (select Code from Country where District='Georgia' and Continent='Asia');<br>Empty set (0.18 sec)<br> |
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:
|
1 |
mysql> W<br>Show warnings enabled.<br>mysql> explain extended select count(*) from City where CountryCode in (select CountryCode from Country where Continent = 'Antarctica');<br>+----+--------------------+---------+-------+---------------+-------------+---------+------+------+----------+--------------------------+<br>| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |<br>+----+--------------------+---------+-------+---------------+-------------+---------+------+------+----------+--------------------------+<br>| 1 | PRIMARY | City | index | NULL | CountryCode | 3 | NULL | 4005 | 100.00 | Using where; Using index |<br>| 2 | DEPENDENT SUBQUERY | Country | ALL | NULL | NULL | NULL | NULL | 227 | 100.00 | Using where |<br>+----+--------------------+---------+-------+---------------+-------------+---------+------+------+----------+--------------------------+<br>2 rows in set, 2 warnings (0.01 sec)<br><br>Note (Code 1276): Field or reference 'world.City.CountryCode' of SELECT #2 was resolved in SELECT #1<br>Note (Code 1003): select count(0) AS `count(*)` from `world`.`City` where <in_optimizer>(`world`.`City`.`CountryCode`,<exists>(select 1 from `world`.`Country` where ((`world`.`Country`.`Continent` = 'Antarctica') and (<cache>(`world`.`City`.`CountryCode`) = `world`.`City`.`CountryCode`)))) |
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,
|
1 |
mysql> explain extended select Name, Language from City join CountryLanguage using (CountryCode) where CountryCode in (select Code from Country where District='Georgia' and Continent='Asia');<br>+----+--------------------+-----------------+-----------------+---------------------+-------------+---------+-----------------------------------+------+----------+-------------+<br>| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |<br>+----+--------------------+-----------------+-----------------+---------------------+-------------+---------+-----------------------------------+------+----------+-------------+<br>| 1 | PRIMARY | CountryLanguage | index | PRIMARY,CountryCode | CountryCode | 3 | NULL | 1157 | 100.00 | Using index |<br>| 1 | PRIMARY | City | ref | CountryCode | CountryCode | 3 | world.CountryLanguage.CountryCode | 1 | 100.00 | Using where |<br>| 2 | DEPENDENT SUBQUERY | Country | unique_subquery | PRIMARY | PRIMARY | 3 | func | 1 | 100.00 | Using where |<br>+----+--------------------+-----------------+-----------------+---------------------+-------------+---------+-----------------------------------+------+----------+-------------+<br>3 rows in set, 2 warnings (0.00 sec)<br><br>Note (Code 1276): Field or reference 'world.City.District' of SELECT #2 was resolved in SELECT #1<br>Note (Code 1003): select `world`.`City`.`Name` AS `Name`,`world`.`CountryLanguage`.`Language` AS `Language` from `world`.`City` join `world`.`CountryLanguage` where ((`world`.`City`.`CountryCode` = `world`.`CountryLanguage`.`CountryCode`) and <in_optimizer>(`world`.`City`.`CountryCode`,<exists>(<primary_index_lookup>(<cache>(`world`.`City`.`CountryCode`) in Country on PRIMARY where ((`world`.`Country`.`Continent` = 'Asia') and (`world`.`City`.`District` = 'Georgia') and (<cache>(`world`.`City`.`CountryCode`) = `world`.`Country`.`Code`)))))) |
It is not clear if world.City.District was resolved from table City or CountryLanguage .
EXPLAIN FORMAT=JSON gives us this information.
|
1 |
mysql> explain format=json select Name, Language from City join CountryLanguage using (CountryCode) where CountryCode in (select Code from Country where District='Georgia' and Continent='Asia')G<br>*************************** 1. row ***************************<br>EXPLAIN: {<br> "query_block": {<br> "select_id": 1,<br> "cost_info": {<br> "query_cost": "907.97"<br> },<br> "nested_loop": [<br> {<br> "table": {<br> "table_name": "Country",<br> "access_type": "ALL",<br> "possible_keys": [<br> "PRIMARY"<br> ],<br> "rows_examined_per_scan": 239,<br> "rows_produced_per_join": 34,<br> "filtered": "14.29",<br> "cost_info": {<br> "read_cost": "46.97",<br> "eval_cost": "6.83",<br> "prefix_cost": "53.80",<br> "data_read_per_join": "8K"<br> },<br> "used_columns": [<br> "Code",<br> "Continent"<br> ],<br> "attached_condition": "(`world`.`Country`.`Continent` = 'Asia')"<br> }<br> },<br> {<br> "table": {<br> "table_name": "City",<br> "access_type": "ref",<br> "possible_keys": [<br> "CountryCode"<br> ],<br> "key": "CountryCode",<br> "used_key_parts": [<br> "CountryCode"<br> ],<br> "key_length": "3",<br> "ref": [<br> "world.Country.Code"<br> ],<br> "rows_examined_per_scan": 18,<br> "rows_produced_per_join": 61,<br> "filtered": "10.00",<br> "cost_info": {<br> "read_cost": "616.34",<br> "eval_cost": "12.33",<br> "prefix_cost": "793.40",<br> "data_read_per_join": "4K"<br> },<br> "used_columns": [<br> "Name",<br> "CountryCode",<br> "District"<br> ],<br> "attached_condition": "(`world`.`City`.`District` = 'Georgia')"<br> }<br> },<br> {<br> "table": {<br> "table_name": "CountryLanguage",<br> "access_type": "ref",<br> "possible_keys": [<br> "PRIMARY",<br> "CountryCode"<br> ],<br> "key": "CountryCode",<br> "used_key_parts": [<br> "CountryCode"<br> ],<br> "key_length": "3",<br> "ref": [<br> "world.Country.Code"<br> ],<br> "rows_examined_per_scan": 4,<br> "rows_produced_per_join": 260,<br> "filtered": "100.00",<br> "using_index": true,<br> "cost_info": {<br> "read_cost": "62.51",<br> "eval_cost": "52.06",<br> "prefix_cost": "907.97",<br> "data_read_per_join": "10K"<br> },<br> "used_columns": [<br> "CountryCode",<br> "Language"<br> ]<br> }<br> }<br> ]<br> }<br>}<br>1 row in set, 2 warnings (0.00 sec)<br><br>Note (Code 1276): Field or reference 'world.City.District' of SELECT #2 was resolved in SELECT #1<br>Note (Code 1003): /* select#1 */ select `world`.`City`.`Name` AS `Name`,`world`.`CountryLanguage`.`Language` AS `Language` from `world`.`Country` join `world`.`City` join `world`.`CountryLanguage` where ((`world`.`City`.`CountryCode` = `world`.`Country`.`Code`) and (`world`.`CountryLanguage`.`CountryCode` = `world`.`Country`.`Code`) and (`world`.`Country`.`Continent` = 'Asia') and (`world`.`City`.`District` = 'Georgia'))<br> |
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:
|
1 |
mysql> pager egrep 'table_name|attached_condition'<br>PAGER set to 'egrep 'table_name|attached_condition''<br>mysql> explain format=json select Name, Language from City join CountryLanguage using (CountryCode) where CountryCode in (select Code from Country where District='Georgia' and Continent='Asia')G<br> "table_name": "Country",<br> "attached_condition": "(`world`.`Country`.`Continent` = 'Asia')"<br> "table_name": "City",<br> "attached_condition": "(`world`.`City`.`District` = 'Georgia')"<br> "table_name": "CountryLanguage",<br>1 row in set, 2 warnings (0.00 sec) |
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:
|
1 |
mysql> show create table CountryG<br>*************************** 1. row ***************************<br> Table: Country<br>Create Table: CREATE TABLE `Country` (<br> `Code` char(3) NOT NULL DEFAULT '',<br> `Name` char(52) NOT NULL DEFAULT '',<br> `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',<br> `Region` char(26) NOT NULL DEFAULT '',<br> `SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00',<br> `IndepYear` smallint(6) DEFAULT NULL,<br> `Population` int(11) NOT NULL DEFAULT '0',<br> `LifeExpectancy` float(3,1) DEFAULT NULL,<br> `GNP` float(10,2) DEFAULT NULL,<br> `GNPOld` float(10,2) DEFAULT NULL,<br> `LocalName` char(45) NOT NULL DEFAULT '',<br> `GovernmentForm` char(45) NOT NULL DEFAULT '',<br> `HeadOfState` char(60) DEFAULT NULL,<br> `Capital` int(11) DEFAULT NULL,<br> `Code2` char(2) NOT NULL DEFAULT '',<br> PRIMARY KEY (`Code`)<br>) ENGINE=InnoDB DEFAULT CHARSET=latin1<br>1 row in set (0.05 sec)<br><br>mysql> show create table CityG<br>*************************** 1. row ***************************<br> Table: City<br>Create Table: CREATE TABLE `City` (<br> `ID` int(11) NOT NULL AUTO_INCREMENT,<br> `Name` char(35) NOT NULL DEFAULT '',<br> `CountryCode` char(3) NOT NULL DEFAULT '',<br> `District` char(20) NOT NULL DEFAULT '',<br> `Population` int(11) NOT NULL DEFAULT '0',<br> PRIMARY KEY (`ID`),<br> KEY `CountryCode` (`CountryCode`),<br> CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)<br>) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1<br>1 row in set (0.00 sec) |
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.
Resources
RELATED POSTS
