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

Left Join Yields All Nulls Instead of Appropriate Matches

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.4, 5.3.12, 5.5.33a
    • 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 v.filename, t6.id
      FROM gt_tbl_contact t6 
      LEFT JOIN (
      SELECT d.attributeValue as grantee_id
                      , c.filename as filename,
          d.baseId
      FROM tclassextend e
      JOIN tclassextendsets es on es.subTypeID = e.subTypeID
      JOIN tclassextendattributes a on a.extendSetID = es.extendSetID
      JOIN tclassextenddata d on d.attributeID = a.attributeID
      JOIN tcontent c on c.contentHistID = d.baseID
      WHERE 
      c.active = 1 
                      AND c.approved = 1 
                      AND c.display = 1
                      AND e.subType = 'Grantee') v ON v.grantee_id = t6.contact_orgid;

      Yields:

      For comparison, results from MySQL 5.1.69:

      mysql5_1_69_results.txt

      result:
       
      +-----------+----+
      | filename  | id |
      +-----------+----+
      | NULL      |  1 |
      | NULL      |  2 |
      | NULL      |  3 |
      | NULL      |  4 |
      | NULL      | 12 |
      | NULL      | 14 |
      | NULL      | 16 |
      | 17944.pdf |  5 |
      | 17942.pdf |  9 |
      | 17941.pdf |  7 |
      | 17940.pdf |  6 |
      | 17965.pdf |  8 |
      | 17965.pdf | 10 |
      | 17965.pdf | 11 |
      | 17965.pdf | 13 |
      | 17965.pdf | 15 |
      +-----------+----+
       
      extended explain:
       
      +----+-------------+------------+--------+---------------------------------------------------------------------------+-------------------------+---------+----------------------------+------+----------+-------------+
      | id | select_type | table      | type   | possible_keys                                                             | key                     | key_len | ref                        | rows | filtered | Extra       |
      +----+-------------+------------+--------+---------------------------------------------------------------------------+-------------------------+---------+----------------------------+------+----------+-------------+
      |  1 | PRIMARY     | t6         | index  | NULL                                                                      | fk_gt_tbl_contact_8_idx | 5       | NULL                       |   16 |   100.00 | Using index |
      |  1 | PRIMARY     | <derived2> | ALL    | NULL                                                                      | NULL                    | NULL    | NULL                       |   22 |   100.00 |             |
      |  2 | DERIVED     | c          | ref    | IX_TContent_1,IX_tcontent_approved,IX_tcontent_active,IX_tcontent_display | IX_tcontent_approved    | 2       |                            |   11 |   100.00 | Using where |
      |  2 | DERIVED     | d          | ref    | Index_2,Index_3                                                           | Index_2                 | 105     | thingtest2.c.ContentHistID |    3 |   100.00 |             |
      |  2 | DERIVED     | a          | eq_ref | PRIMARY,Index_2                                                           | PRIMARY                 | 4       | thingtest2.d.attributeID   |    1 |   100.00 |             |
      |  2 | DERIVED     | es         | eq_ref | PRIMARY,Index_2                                                           | PRIMARY                 | 105     | thingtest2.a.extendSetID   |    1 |   100.00 |             |
      |  2 | DERIVED     | e          | eq_ref | PRIMARY                                                                   | PRIMARY                 | 105     | thingtest2.es.subTypeID    |    1 |   100.00 | Using where |
      +----+-------------+------------+--------+---------------------------------------------------------------------------+-------------------------+---------+----------------------------+------+----------+-------------+

      Results from MariaDB 5.5.32:

      mariadb5_5_32_results.txt

      result:
       
      +----------+----+
      | filename | id |
      +----------+----+
      | NULL     |  1 |
      | NULL     |  2 |
      | NULL     |  3 |
      | NULL     |  4 |
      | NULL     | 12 |
      | NULL     | 14 |
      | NULL     | 16 |
      | NULL     |  5 |
      | NULL     |  9 |
      | NULL     |  7 |
      | NULL     |  6 |
      | NULL     |  8 |
      | NULL     | 10 |
      | NULL     | 11 |
      | NULL     | 13 |
      | NULL     | 15 |
      +----------+----+
       
      extended explain:
       
      +------+-------------+-------+--------+---------------------------------------------------------------------------+-------------------------+---------+----------------------------+------+----------+-------------+
      | id   | select_type | table | type   | possible_keys                                                             | key                     | key_len | ref                        | rows | filtered | Extra       |
      +------+-------------+-------+--------+---------------------------------------------------------------------------+-------------------------+---------+----------------------------+------+----------+-------------+
      |    1 | SIMPLE      | t6    | index  | NULL                                                                      | fk_gt_tbl_contact_8_idx | 5       | NULL                       |   16 |   100.00 | Using index |
      |    1 | SIMPLE      | c     | ref    | IX_TContent_1,IX_tcontent_approved,IX_tcontent_active,IX_tcontent_display | IX_tcontent_approved    | 2       | const                      |    2 |   100.00 | Using where |
      |    1 | SIMPLE      | d     | ref    | Index_2,Index_3                                                           | Index_2                 | 105     | thingtest2.c.ContentHistID |    6 |   100.00 | Using where |
      |    1 | SIMPLE      | a     | eq_ref | PRIMARY,Index_2                                                           | PRIMARY                 | 4       | thingtest2.d.attributeID   |    1 |   100.00 | Using where |
      |    1 | SIMPLE      | es    | eq_ref | PRIMARY,Index_2                                                           | PRIMARY                 | 105     | thingtest2.a.extendSetID   |    1 |   100.00 | Using where |
      |    1 | SIMPLE      | e     | eq_ref | PRIMARY                                                                   | PRIMARY                 | 105     | thingtest2.es.subTypeID    |    1 |   100.00 | Using where |
      +------+-------------+-------+--------+---------------------------------------------------------------------------+-------------------------+---------+----------------------------+------+----------+-------------+

      Note: Switching

      set optimizer_switch='derived_merge=off';

      yields correct results.

        Attachments

          Activity

            People

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

              Dates

              • Created:
                Updated:
                Resolved: