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

scalar subqueries in a comparison produced unexpected result

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.3.12, 5.5.40, 10.0.14
    • 5.5.48
    • Optimizer
    • 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

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

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              kennethcruz Kenneth Cruz
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.