MySQL subquery strange issue

MySQL: 5.7.16

There are two tables:

1. rule_conf table:

CREATE TABLE `rule_conf`  (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `app_id` bigint(20) UNSIGNED NOT NULL,
  `app_log_id` bigint(20) UNSIGNED NOT NULL,
  `r_type` tinyint(2) UNSIGNED NOT NULL,
  `r_status` tinyint(2) UNSIGNED NOT NULL DEFAULT 0,
   ...
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_app_log_id`(`app_log_id`) USING BTREE,
  INDEX `idx_app_id`(`app_id`) USING BTREE
) ENGINE = InnoDB

2. app_log_conf table:

CREATE TABLE `app_log_conf`  (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `app_id` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
   ...
  INDEX `idx_app_id`(`app_id`) USING BTREE
) ENGINE = InnoDB

There is a SQL:

select app_id from rule_conf
where app_log_id = (
   select max(id) from app_log_conf where app_id = 123 and r_status = 1 and r_type = 1
) 

this SQL return an empty result, not throw an error, WHY?

In my expectation, this SQL should throw an exception like Unknown Column, but it did not.

When I add a condition in subquery aaa = 1, then executing the holistic SQL, I got an Error: 1054 - Unknown column 'aaa' in 'where clause, that's my expectation, because table app_log_conf indeed not including column aaa.

And when I executing the subquery separately, I got an Error: 1054 - Unknown column 'r_status' in 'where clause, that's also my expectation.

Is there any possibility because the table 'rule_conf' in main query including those columns?

So, anyone encountered such issue, or can someone explain WHY?

can someone explain why

The subquery knows the columns of the main query. If there is the same column name in your main query table and your subquery table and you don't qualify which you want in the subquery, the subquery table's column has precedence over the main query table's one. Your query hence translates to:

select rule_conf.app_id 
from rule_conf
where rule_conf.app_log_id = 
(
  select max(app_log_conf.id)
  from app_log_conf
  where app_log_conf.app_id = 123
  and rule_conf.r_status = 1
  and rule_conf.r_type = 1
);

When working with more than one table, you should qualify all column names with their table in order to avoid mistakes. Using alias table names helps with this.

That the subquery knows the columns of the main query is necessary sometimes, when we want to write a correlated subquery, e.g.

select rc.app_id 
from rule_conf rc
where rc.app_log_id = 
(
  select max(alc.id)
  from app_log_conf alc
  where alc.app_id = rc.app_id
);