Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6859

scalar subqueries in a comparison produced unexpected result

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.3.12, 5.5.40, 10.0.14
    • Fix Version/s: 5.5.48
    • Component/s: Optimizer
    • Labels:
      None
    • Sprint:
      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

        1. database_dump_test_mdb.sql
          2 kB
          Kenneth Cruz
        2. test_results.txt
          4 kB
          Kenneth Cruz

          Issue Links

            Activity

              People

              • Assignee:
                psergey Sergei Petrunia
                Reporter:
                kennethcruz Kenneth Cruz
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: