Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.3.12, 5.5.40, 10.0.14
-
None
-
5.5.44, 5.5.45, 5.5.47-1, 5.5.48-0
Description
A query with scalar subquery in a comparison returns no result when using "ORDER BY" and "LIMIT" in the scalar subquery. (https://mariadb.com/kb/en/sql-99-complete-really/31-searching-with-subqueries/scalar-subqueries/#comment_1325)
Sample SQL statement:
SELECT SQL_NO_CACHE a.project_number FROM projects a WHERE ( SELECT z.country FROM projects_history z WHERE z.project_number = a.project_number AND z.history_date <= '2014-09-01' ORDER BY z.id DESC LIMIT 1 ) IN ( SELECT r.country FROM region r WHERE r.region = 'eame' ); |
The query was executed in MariaDB (10.0.3), MySQL(5.6.17) and MySQL(5.0.96). The MySQL(5.0.96) returns the expected result.
See attached files for details (db dump, explain, actual result and expected result)
Attachments
Issue Links
- links to