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

Incorrect query result when combining DISTINCT, LEFT JOIN and subquery with constant column

    XMLWordPrintable

Details

    • 5.5.55

    Description

      There appears to be a problem with the results returned from a query when DISTINCT, LEFT JOIN and a subquery which contains a constant value are combined.

      I've boiled down the problem into a simple example of how it fails:

      SELECT DISTINCT realtable.id AS realid, virtualtable.id AS virtualid, virtualtable.recordexists
      FROM realtable
      LEFT JOIN (SELECT realtable.id, "yes" AS recordexists FROM realtable) virtualtable
      ON realtable.id = virtualtable.id

      If you create a table called "realtable" with columns int "id" and string "data", insert two records (1, "yes") and (2, "yes") and run the query then you'll get:

      realid, virtualid, recordexists
      1, 1, null
      2, 2, null

      which is incorrect. The value "yes" should be returned in the recordexists column. Obviously the "virtual" records are being joined correctly to the "real" records, because the virtualid returns the correct value, but the constant "yes" is missing. If you remove DISTINCT, change LEFT JOIN to just JOIN, or change "yes" to realtable.data then you get the correct results:

      realid, virtualid, recordexists
      1, 1, yes
      2, 2, yes

      I also confirmed that this is NOT caused by using the same table in both the main and sub queries.

      I've got a query structured like this which is working in Postgress and MySQL, and when delivered to a client who tried it in MariaDB it started failing. This could probably be considered a data loss bug.

      Attachments

        Activity

          People

            igor Igor Babaev
            nathanl Nathan Lewis
            Votes:
            1 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.