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

Left Join Yields All Nulls Instead of Appropriate Matches #2

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.4, 5.5.33, 5.3.12
    • Fix Version/s: 5.5.34, 10.0.6, 5.3.13
    • Component/s: None
    • Labels:
    • Environment:
      All (Win 7 64-bit; Ubuntu 12.10 64-bit)

      Description

      Querying the attached data with:

      left_join.sql

      SELECT 
          p.filename, r.recipient_grantee_id
      FROM
          gt_asc_grantee_recipient r
              LEFT JOIN gt_vw_grantee_page p
                  ON r.recipient_grantee_id = p.grantee_id
      WHERE
          r.grantee_id = 220;

      For comparison, results from MySQL 5.6.14:

      mysql_5_6_14.results.txt

      +-----------+----------------------+
      | filename  | recipient_grantee_id |
      +-----------+----------------------+
      | NULL      |                 NULL |
      | 16059_foo |                 2431 |
      | NULL      |                 NULL |
      | NULL      |                 NULL |
      | 16079_foo |                 3257 |
      | 16080_foo |                 3357 |
      | 16590_foo |                  469 |
      | 16093_foo |                 4165 |
      | 15943_foo |                 1368 |
      | 16751_foo |                  639 |
      | NULL      |                 NULL |
      | 15950_foo |                 1381 |
      | NULL      |                 NULL |
      | 16113_foo |                 4828 |
      | 16122_foo |                 5069 |
      | NULL      |                 NULL |
      | NULL      |                 NULL |
      | NULL      |                 NULL |
      | NULL      |                 NULL |
      | 15679_foo |                 1073 |
      | NULL      |                 NULL |
      | 16146_foo |                 6097 |
      | NULL      |                 NULL |
      | 16196_foo |                13431 |
      | 15838_foo |                 1238 |
      +-----------+----------------------+
      25 rows in set (0.01 sec)

      MariaDB 5.5.32 results (incorrect):

      mariadb_5_5_32.results.txt

      +----------+----------------------+
      | filename | recipient_grantee_id |
      +----------+----------------------+
      | NULL     |                 NULL |
      | NULL     |                 2431 |
      | NULL     |                 NULL |
      | NULL     |                 NULL |
      | NULL     |                 3257 |
      | NULL     |                 3357 |
      | NULL     |                  469 |
      | NULL     |                 4165 |
      | NULL     |                 1368 |
      | NULL     |                  639 |
      | NULL     |                 NULL |
      | NULL     |                 1381 |
      | NULL     |                 NULL |
      | NULL     |                 4828 |
      | NULL     |                 5069 |
      | NULL     |                 NULL |
      | NULL     |                 NULL |
      | NULL     |                 NULL |
      | NULL     |                 NULL |
      | NULL     |                 1073 |
      | NULL     |                 NULL |
      | NULL     |                 6097 |
      | NULL     |                 NULL |
      | NULL     |                13431 |
      | NULL     |                 1238 |
      +----------+----------------------+
      25 rows in set (0.01 sec)

      Note: Unlike MDEV-5107, switching

      set optimizer_switch='derived_merge=off';

      does not have any affect.

        Attachments

          Activity

            People

            Assignee:
            sanja Oleksandr Byelkin
            Reporter:
            Jamie Jackson Jamie Jackson
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: