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

scalar subqueries in a comparison produced unexpected result

    XMLWordPrintable

    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: