| Bug #88817 | Field or reference of SELECT #2 was resolved in SELECT #1 | ||
|---|---|---|---|
| Submitted: | 7 Dec 2017 14:26 | Modified: | 19 Jan 2018 11:17 |
| Reporter: | Christoffer Lindberg | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.7.17 | OS: | Linux (AWS RDS) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | explain, reference, subquery, warning | ||
[8 Dec 2017 8:26]
Øystein Grøvlen
The "warnings" is the Notes that before 5.7 was only shown when doing EXPLAIN EXTENDED. In 5.7, EXPLAIN works as EXPLAIN EXTENDED and EXPLAIN EXTENDED has been deprecated. These notes gives additional information about the query plan, and do not indicate that there is anything wrong about the query. If you do not want to be warned about notes, you can set sql_notes=0: mysql> warnings; Show warnings enabled. mysql> EXPLAIN SELECT n.id, (SELECT email FROM users u WHERE u.id = n.author_user_id) AS author_email FROM news n LIMIT 10; +----+--------------------+-------+------------+--------+---------------+----------------+---------+-----------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+--------+---------------+----------------+---------+-----------------------+------+----------+-------------+ | 1 | PRIMARY | n | NULL | index | NULL | author_user_id | 4 | NULL | 1 | 100.00 | Using index | | 2 | DEPENDENT SUBQUERY | u | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.n.author_user_id | 1 | 100.00 | NULL | +----+--------------------+-------+------------+--------+---------------+----------------+---------+-----------------------+------+----------+-------------+ 2 rows in set, 2 warnings (0,00 sec) Note (Code 1276): Field or reference 'test.n.author_user_id' of SELECT #2 was resolved in SELECT #1 Note (Code 1003): /* select#1 */ select `test`.`n`.`id` AS `id`,(/* select#2 */ select `test`.`u`.`email` from `test`.`users` `u` where (`test`.`u`.`id` = `test`.`n`.`author_user_id`)) AS `author_email` from `test`.`news` `n` limit 10 mysql> set sql_notes=0; Query OK, 0 rows affected (0,00 sec) mysql> EXPLAIN SELECT n.id, (SELECT email FROM users u WHERE u.id = n.author_user_id) AS author_email FROM news n LIMIT 10; +----+--------------------+-------+------------+--------+---------------+----------------+---------+-----------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+--------+---------------+----------------+---------+-----------------------+------+----------+-------------+ | 1 | PRIMARY | n | NULL | index | NULL | author_user_id | 4 | NULL | 1 | 100.00 | Using index | | 2 | DEPENDENT SUBQUERY | u | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.n.author_user_id | 1 | 100.00 | NULL | +----+--------------------+-------+------------+--------+---------------+----------------+---------+-----------------------+------+----------+-------------+ 2 rows in set (0,00 sec)
[8 Dec 2017 10:06]
Christoffer Lindberg
Status changed to open.
[19 Jan 2018 11:17]
MySQL Verification Team
Status changed to !bug how was clearly handled by developer.
Description: Since moving to 5.7 I have had the issues with queries containing subqueries in fields and from clauses, when doing EXPLAIN. I assumed there is something flawed in my subquery format since it produces a warning, and I am not sure how to get it to go away other than rewriting it as a JOIN, but I can see no measurable performance gains by doing that, in my tests, and I am aware of no changes in 5.7 that would advise or discourage the use of subqueries. Other than the warnings, the queries produce the expected results. mysql> EXPLAIN -> SELECT n.id, (SELECT email FROM users u WHERE u.id = n.author_user_id) AS author_email FROM news n LIMIT 10; +----+--------------------+-------+------------+--------+---------------+----------------+---------+-----------------------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+--------+---------------+----------------+---------+-----------------------------------+------+----------+-------------+ | 1 | PRIMARY | n | NULL | index | NULL | author_user_id | 4 | NULL | 1 | 100.00 | Using index | | 2 | DEPENDENT SUBQUERY | u | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testdb.n.author_user_id | 1 | 100.00 | NULL | +----+--------------------+-------+------------+--------+---------------+----------------+---------+-----------------------------------+------+----------+-------------+ 2 rows in set, 2 warnings (0.13 sec) mysql> SHOW WARNINGS; Field or reference 'testdb.n.author_user_id' of SELECT #2 was resolved in SELECT #1 /* select#1 */ select `testdb`.`n`.`id` AS `id`,(/* select#2 */ select `testdb`.`u`.`email` from `testdb`.`users` `u` where (`testdb`.`u`.`id` = `testdb`.`n`.`author_user_id`)) AS `author_email` from `testdb`.`news` `n` limit 10 When run on a MySQL 5.6 server there are no warnings. (5.6.22) I have tried altering the log_error_verbosity setting (from 3 to 2 or 1) as suggested by a comment on a forum with this asked as a question, but to no avail. How to repeat: # Table structure: CREATE TABLE `news` ( `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `title` VARCHAR(150) NOT NULL, `body` MEDIUMTEXT NOT NULL, `author_user_id` INT(11) UNSIGNED NOT NULL DEFAULT '0', `created` DATETIME NOT NULL, `modified` DATETIME NOT NULL, PRIMARY KEY (`id`), INDEX `author_user_id` (`author_user_id`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB ; CREATE TABLE `users` ( `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `email` VARCHAR(128) NOT NULL, PRIMARY KEY (`id`), UNIQUE INDEX `email` (`email`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB ; # Sample data, not seemingly relevant to the bug INSERT INTO `news` (`id`, `title`, `body`, `author_user_id`, `created`, `modified`) VALUES (1, 'Test Title', 'In an unsurprising move...', 1, '2017-12-05 00:00:00', '0000-00-00 00:00:00'); INSERT INTO `users` (`id`, `email`) VALUES (1, 'email@example.com'); EXPLAIN SELECT n.id, (SELECT email FROM users u WHERE u.id = n.author_user_id) AS author_email FROM news n LIMIT 10; Suggested fix: Incorporate the verbosity of warnings such as this into a setting. Assuming this is a bug and not just an improperly written subquery, in which I case I think improving the warning message would be a good solution.