Weird SQL request with unknown field

Question

I have a weird result for SQL request, i gave in my subrequest an unknown field "alert_event_load_time" for "alerts" table, but exists on "alert_events" table, but instead of an error, i have a result :

"select count(*) FROM rc.alert_events WHERE 
alert_id in 
(select alert_id from rc.alerts where alert_event_load_time like '2015-08-04%');"

+----------+
| count(*) |
+----------+
|   237634 |
+----------+

And it returns me the same result as the request :

select count(*) FROM rc.alert_events WHERE alert_event_load_time like '2015-08-04%' ;"

+----------+
| count(*) |
+----------+
|   237634 |
+----------+

Could it be a bug ?

Answer 1

Let's put in some table aliases. You think the query is:

SELECT count(*)
FROM rc.alert_events ae
WHERE ae.alert_id in (select a.alert_id from rc.alerts a
                      where a.alert_event_load_time like '2015-08-04%')

However, alerts.alert_event_load_time doesn't exist. So, SQL (in general) looks in the next outer scope to resolve the column alias. And it finds it. So, the query is:

SELECT count(*)
FROM rc.alert_events ae
WHERE ae.alert_id in (select a.alert_id from rc.alerts a
                      where ae.alert_event_load_time like '2015-08-04%')

No error, but you still get a result set.

This use of scoping is considered a "feature" of the SQL language. My recommendation is to never use this feature. Always use table aliases and qualified column names (okay, if there is only one table in the query, then you can relax a bit).