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

Wrong result with small join_buffer_space_limit and hash join

Details

    • Bug
    • Status: Stalled (View Workflow)
    • Critical
    • Resolution: Unresolved
    • 10.4(EOL), 10.5, 10.6, 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL)
    • 10.5, 10.6, 10.11, 11.4
    • Optimizer
    • None

    Description

      If join_buffer_space_limit is small enough and join_cache_level is set to 4 then queries with left joins may return wrong results.
      The following test case demonstrates this problem:

      set join_cache_level=4;
      set join_buffer_space_limit=4000;
      create table t1 (pk varchar(512), a varchar(512));
      insert into t1 select seq, seq from seq_1_to_10;
      create table t2 (pk varchar(512), a varchar(512));
      insert into t2 select seq, seq from seq_1_to_10;
      create table t3 (pk varchar(512), a varchar(512));
      insert into t3 select seq, seq from seq_1_to_5;
      create table t4 (pk varchar(512), a varchar(512), key(a));
      insert into t4 select seq, seq from seq_1_to_5;
      select count(*) from t1,t2,t3 left join t4 on t3.pk=t4.a where t1.a=t2.pk and t2.pk=t3.a;
      

      MariaDB [test]> select count(*) from t1,t2,t3 left join t4 on t3.pk=t4.a where t1.a=t2.pk and t2.pk=t3.a;
      +----------+
      | count(*) |
      +----------+
      |       25 |
      +----------+
      

      With bigger enough join_buffer_space_limit the expected result is returned:

      MariaDB [test]> set join_buffer_space_limit=10000;
       
      MariaDB [test]> select count(*) from t1,t2,t3 left join t4 on t3.pk=t4.a where t1.a=t2.pk and t2.pk=t3.a;
      +----------+
      | count(*) |
      +----------+
      |        5 |
      +----------+
      

      Attachments

        Issue Links

          Activity

            igor Igor Babaev added a comment -

            Let's use the settings:

            set join_cache_level=4;
            set join_buffer_space_limit;
            

            and look at the output of EXPLAIN for the query:

            MariaDB [test]> explain select count(*) from t1,t2,t3 left join t4 on t3.pk=t4.a where t1.a=t2.pk and t2.pk=t3.a;
            +------+-------------+-------+------------+---------------+-----------+---------+------------+------+---------------------------------------------------------------+
            | id   | select_type | table | type       | possible_keys | key       | key_len | ref        | rows | Extra                                                         |
            +------+-------------+-------+------------+---------------+-----------+---------+------------+------+---------------------------------------------------------------+
            |    1 | SIMPLE      | t3    | ALL        | NULL          | NULL      | NULL    | NULL       | 5    | Using where                                                   |
            |    1 | SIMPLE      | t4    | hash_index | a             | #hash#a:a | 515:515 | test.t3.pk | 5    | Using where; Using index; Using join buffer (flat, BNLH join) |
            |    1 | SIMPLE      | t1    | hash_ALL   | NULL          | #hash#$hj | 515     | test.t3.a  | 10   | Using where                                                   |
            |    1 | SIMPLE      | t2    | hash_ALL   | NULL          | #hash#$hj | 515     | test.t3.a  | 10   | Using where                                                   |
            MariaDB [test]> explain select count(*) from t1,t2,t3 left join t4 on t3.pk=t4.a where t1.a=t2.pk and t2.pk=t3.a;
            +------+-------------+-------+------------+---------------+-----------+---------+------------+------+---------------------------------------------------------------+
            | id   | select_type | table | type       | possible_keys | key       | key_len | ref        | rows | Extra                                                         |
            +------+-------------+-------+------------+---------------+-----------+---------+------------+------+---------------------------------------------------------------+
            |    1 | SIMPLE      | t3    | ALL        | NULL          | NULL      | NULL    | NULL       | 5    | Using where                                                   |
            |    1 | SIMPLE      | t4    | hash_index | a             | #hash#a:a | 515:515 | test.t3.pk | 5    | Using where; Using index; Using join buffer (flat, BNLH join) |
            |    1 | SIMPLE      | t1    | hash_ALL   | NULL          | #hash#$hj | 515     | test.t3.a  | 10   | Using where                                                   |
            |    1 | SIMPLE      | t2    | hash_ALL   | NULL          | #hash#$hj | 515     | test.t3.a  | 10   | Using where                                                   |
            +------+-------------+-------+------------+---------------+-----------+---------+------------+------+---------------------------------------------------------------+
            +------+-------------+-------+------------+---------------+-----------+---------+------------+------+---------------------------------------------------------------+
            

            A strange thing that we can observe here is that t1 and t2 are joined with hash join but no usage of join buffer is shown for these joins.

            igor Igor Babaev added a comment - Let's use the settings: set join_cache_level=4; set join_buffer_space_limit; and look at the output of EXPLAIN for the query: MariaDB [test]> explain select count(*) from t1,t2,t3 left join t4 on t3.pk=t4.a where t1.a=t2.pk and t2.pk=t3.a; +------+-------------+-------+------------+---------------+-----------+---------+------------+------+---------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------------+---------------+-----------+---------+------------+------+---------------------------------------------------------------+ | 1 | SIMPLE | t3 | ALL | NULL | NULL | NULL | NULL | 5 | Using where | | 1 | SIMPLE | t4 | hash_index | a | #hash#a:a | 515:515 | test.t3.pk | 5 | Using where; Using index; Using join buffer (flat, BNLH join) | | 1 | SIMPLE | t1 | hash_ALL | NULL | #hash#$hj | 515 | test.t3.a | 10 | Using where | | 1 | SIMPLE | t2 | hash_ALL | NULL | #hash#$hj | 515 | test.t3.a | 10 | Using where | MariaDB [test]> explain select count(*) from t1,t2,t3 left join t4 on t3.pk=t4.a where t1.a=t2.pk and t2.pk=t3.a; +------+-------------+-------+------------+---------------+-----------+---------+------------+------+---------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------------+---------------+-----------+---------+------------+------+---------------------------------------------------------------+ | 1 | SIMPLE | t3 | ALL | NULL | NULL | NULL | NULL | 5 | Using where | | 1 | SIMPLE | t4 | hash_index | a | #hash#a:a | 515:515 | test.t3.pk | 5 | Using where; Using index; Using join buffer (flat, BNLH join) | | 1 | SIMPLE | t1 | hash_ALL | NULL | #hash#$hj | 515 | test.t3.a | 10 | Using where | | 1 | SIMPLE | t2 | hash_ALL | NULL | #hash#$hj | 515 | test.t3.a | 10 | Using where | +------+-------------+-------+------------+---------------+-----------+---------+------------+------+---------------------------------------------------------------+ +------+-------------+-------+------------+---------------+-----------+---------+------------+------+---------------------------------------------------------------+ A strange thing that we can observe here is that t1 and t2 are joined with hash join but no usage of join buffer is shown for these joins.
            igor Igor Babaev added a comment -

            The output from EXPLAIN FPRMAT=JSON shows why the query returns a wrong result:

            MariaDB [test]> explain format=json select count(*) from t1,t2,t3 left join t4 on t3.pk=t4.a where t1.a=t2.pk and t2.pk=t3.a;
            +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
            +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            | {
              "query_block": {
                "select_id": 1,
                "table": {
                  "table_name": "t3",
                  "access_type": "ALL",
                  "rows": 5,
                  "filtered": 100,
                  "attached_condition": "t3.a is not null and t3.a is not null"
                },
                "block-nl-join": {
                  "table": {
                    "table_name": "t4",
                    "access_type": "hash_index",
                    "possible_keys": ["a"],
                    "key": "#hash#a:a",
                    "key_length": "515:515",
                    "used_key_parts": ["a"],
                    "ref": ["test.t3.pk"],
                    "rows": 5,
                    "filtered": 20,
                    "using_index": true
                  },
                  "buffer_type": "flat",
                  "buffer_size": "4Kb",
                  "join_type": "BNLH",
                  "attached_condition": "trigcond(trigcond(t3.pk is not null))"
                },
                "table": {
                  "table_name": "t1",
                  "access_type": "hash_ALL",
                  "key": "#hash#$hj",
                  "key_length": "515",
                  "used_key_parts": ["a"],
                  "ref": ["test.t3.a"],
                  "rows": 10,
                  "filtered": 100,
                  "attached_condition": "t1.a = t3.a"
                },
                "table": {
                  "table_name": "t2",
                  "access_type": "hash_ALL",
                  "key": "#hash#$hj",
                  "key_length": "515",
                  "used_key_parts": ["pk"],
                  "ref": ["test.t3.a"],
                  "rows": 10,
                  "filtered": 100,
                  "attached_condition": "t2.pk = t3.a"
                }
              }
            } |
            +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
            

            We see that condition t3.pk=t4.a was lost.

            igor Igor Babaev added a comment - The output from EXPLAIN FPRMAT=JSON shows why the query returns a wrong result: MariaDB [test]> explain format=json select count(*) from t1,t2,t3 left join t4 on t3.pk=t4.a where t1.a=t2.pk and t2.pk=t3.a; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "table": { "table_name": "t3", "access_type": "ALL", "rows": 5, "filtered": 100, "attached_condition": "t3.a is not null and t3.a is not null" }, "block-nl-join": { "table": { "table_name": "t4", "access_type": "hash_index", "possible_keys": ["a"], "key": "#hash#a:a", "key_length": "515:515", "used_key_parts": ["a"], "ref": ["test.t3.pk"], "rows": 5, "filtered": 20, "using_index": true }, "buffer_type": "flat", "buffer_size": "4Kb", "join_type": "BNLH", "attached_condition": "trigcond(trigcond(t3.pk is not null))" }, "table": { "table_name": "t1", "access_type": "hash_ALL", "key": "#hash#$hj", "key_length": "515", "used_key_parts": ["a"], "ref": ["test.t3.a"], "rows": 10, "filtered": 100, "attached_condition": "t1.a = t3.a" }, "table": { "table_name": "t2", "access_type": "hash_ALL", "key": "#hash#$hj", "key_length": "515", "used_key_parts": ["pk"], "ref": ["test.t3.a"], "rows": 10, "filtered": 100, "attached_condition": "t2.pk = t3.a" } } } | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ We see that condition t3.pk=t4.a was lost.
            JIraAutomate JiraAutomate added a comment -

            Automated message:
            ----------------------------
            Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.

            JIraAutomate JiraAutomate added a comment - Automated message: ---------------------------- Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.

            People

              igor Igor Babaev
              igor Igor Babaev
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.