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

SELECT STRAIGHT_JOIN is ignored for views

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • 10.4.11, 10.1.48, 10.6.5
    • N/A
    • Optimizer
    • 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

        1. create.sql
          0.8 kB
          Roy Bellingan

        Activity

          People

            Unassigned Unassigned
            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.