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

SELECT STRAIGHT_JOIN is ignored for views

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 10.4.11, 10.1.48, 10.6.5
    • Fix Version/s: N/A
    • Component/s: Optimizer
    • Labels:
    • Environment:
      Suse 15.3 and Tumbleweed

      Description

      When using SELECT STRAIGHT_JOIN the optimizer will NOT follow the order of the JOIN as expressed in the sql when accessed as a VIEW.

      I tried to look in the documentation but there is not mention about if is ok or not.
      I would totally expect such optimization switch to be kept like all the other (force index ecc)

      (see attached code to prepare)
       
      analyze select * from noCondition;
      +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------------+
      | id   | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | r_rows | filtered | r_filtered | Extra       |
      +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------------+
      |    1 | SIMPLE      | a     | const | PRIMARY       | PRIMARY | 4       | const | 1    | NULL   |   100.00 |       NULL | Using index |
      |    1 | SIMPLE      | b     | ALL   | NULL          | NULL    | NULL    | NULL  | 1    | 1.00   |   100.00 |     100.00 | Using where |
      +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------------+
       
      analyze select * from forceConditionU;
      +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------------+
      | id   | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | r_rows | filtered | r_filtered | Extra       |
      +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------------+
      |    1 | SIMPLE      | a     | const | PRIMARY       | PRIMARY | 4       | const | 1    | NULL   |   100.00 |       NULL | Using index |
      |    1 | SIMPLE      | b     | ALL   | NULL          | NULL    | NULL    | NULL  | 1    | 1.00   |   100.00 |     100.00 | Using where |
      +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------------+
       
      analyze select * from forceConditionM;
      +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------------+
      | id   | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | r_rows | filtered | r_filtered | Extra       |
      +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------------+
      |    1 | SIMPLE      | a     | const | PRIMARY       | PRIMARY | 4       | const | 1    | NULL   |   100.00 |       NULL | Using index |
      |    1 | SIMPLE      | b     | ALL   | NULL          | NULL    | NULL    | NULL  | 1    | 1.00   |   100.00 |     100.00 | Using where |
      +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------------+
       
      analyze select straight_join `a`.`id` AS `id` from (`b` join `a` on(`b`.`notIndexed` = `a`.`id`)) where `a`.`id` = 1;
      +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------------+
      | id   | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | r_rows | filtered | r_filtered | Extra       |
      +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------------+
      |    1 | SIMPLE      | b     | ALL   | NULL          | NULL    | NULL    | NULL  | 1    | 1.00   |   100.00 |     100.00 | Using where |
      |    1 | SIMPLE      | a     | const | PRIMARY       | PRIMARY | 4       | const | 1    | 1.00   |   100.00 |     100.00 | Using index |
      +------+-------------+-------+-------+---------------+---------+---------+-------+------+--------+----------+------------+-------------+
      

      I will try to dig a bit in the code if I can figure out something about that.

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            RoyBellingan Roy Bellingan
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.