Bug #119545 sql using in subquery execute unexpectly successful

• Submitted: 12 Dec 7:27
• Modified: 12 Dec 8:41
• Reporter: ximin liang (OCA)
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 9.5.0
• OS: Any
• CPU Architecture: Any

[12 Dec 7:27] ximin liang

Description:
Hello mysql team:
  Here is a case seems sql using in subquery execute unexpectly successful.

How to repeat:
  CREATE TABLE shared_vehicles (id INT PRIMARY KEY, vehicle_type VARCHAR(20), trip_count INT);

  INSERT IGNORE INTO shared_vehicles (id, vehicle_type, trip_count) VALUES (1, 'ebike', 1200), (2, 'escooter', 800), (3, 'car', 1500);

  CREATE TABLE city_data (city VARCHAR(20) PRIMARY KEY, shared_ebikes BOOLEAN, shared_escooters BOOLEAN, shared_cars BOOLEAN);

  INSERT IGNORE INTO city_data (city, shared_ebikes, shared_escooters, shared_cars) VALUES ('San Francisco', true, true, true), ('Houston', false, false, true), ('Boston', true, true, true);

  SELECT city FROM city_data WHERE (shared_ebikes = true OR shared_escooters = true OR shared_cars = true) AND city IN (SELECT city FROM (SELECT city, SUM(trip_count) AS total_trips FROM shared_vehicles WHERE (vehicle_type = 'ebike' OR vehicle_type = 'escooter' OR vehicle_type = 'car') GROUP BY city) AS shared_vehicles WHERE total_trips > 2000)
;

  Notes that shared_vehicles does not contain field `city` but we actually execute successful. MySQL 5.6 reports error while executes this sql.

[12 Dec 8:19] Øystein Grøvlen

Hi Ximin,

Thank you for the bug report, but this is not a bug.  As can be seen from the EXPLAIN warning, city will refer to the column of city_data in the outermost query:

Note (Code 1276): Field or reference 'test.city_data.city' of SELECT #3 was resolved in SELECT #1

This behavior is according to the SQL standard.

[12 Dec 8:41] ximin liang

Thank you for your reply, I will update my information. ^_^