[MDEV-17382] Hash join algorithm should not be used to join materialized derived table / view by equality - Jira

Details

Type: Task
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Fix Version/s: 5.5.62
Component/s: Optimizer
Labels:
None

Description

CREATE TABLE t1 (

  id int NOT NULL,

  amount decimal DEFAULT NULL,

PRIMARY KEY (id)

)  ENGINE=MyISAM;

CREATE TABLE t2 (

  id int NOT NULL,

  name varchar(50) DEFAULT NULL,

PRIMARY KEY (id)

) ENGINE=MyISAM;

INSERT INTO t1 VALUES

(1, 10.0000), (2, 20.0000), (3, 30.0000), (4, 40.0000),

(5, NULL), (6, NULL), (7, 70.0000), (8, 80.0000);

INSERT INTO t2 VALUES

(1,'A'), (2,'B'), (3,'C'), (4,'D'), (5, NULL), (6, NULL),

(7,'E'), (8,'F'), (9,'G'), (10,'H'), (11, NULL), (12, NULL);

set join_cache_level=4;

EXPLAIN

SELECT t2.id,t2.name,t.total_amt

  FROM  t2

        LEFT JOIN

        (SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t

        ON t2.id=t.id

  WHERE t2.id < 3;

j
We see that hash join algorithm is applied for this query:

MariaDB [test]> EXPLAIN

    -> SELECT t2.id,t2.name,t.total_amt

    ->   FROM  t2

    ->         LEFT JOIN

    ->         (SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t

    ->         ON t2.id=t.id

    ->   WHERE t2.id < 3;

+------+-------------+------------+----------+---------------+------------+---------+------------+------+-------------------------------------+

| id   | select_type | table      | type     | possible_keys | key        | key_len | ref        | rows | Extra                               |

+------+-------------+------------+----------+---------------+------------+---------+------------+------+-------------------------------------+

|    1 | PRIMARY     | t2         | range    | PRIMARY       | PRIMARY    | 4       | NULL       |    2 | Using where                         |

|    1 | PRIMARY     | <derived2> | hash_ALL | key0          | #hash#key0 | 5       | test.t2.id |    8 | Using join buffer (flat, BNLH join) |

|    2 | DERIVED     | t1         | index    | NULL          | PRIMARY    | 4       | NULL       |    8 |                                     |

+------+-------------+------------+----------+---------------+------------+---------+------------+------+-------------------------------------+

Attachments

Issue Links

blocks

MDEV-17381 Wrong query result with LATERAL DERIVED optimization and join_cache_level=6

Closed

Activity

Ascending order - Click to sort in descending order

Igor Babaev (Inactive) created issue - 2018-10-06 22:29

Igor Babaev (Inactive) made changes - 2018-10-06 22:31

Field	Original Value	New Value
Fix Version/s		5.5 [ 15800 ]

Igor Babaev (Inactive) made changes - 2018-10-06 22:39

Link

This issue blocks ~~MDEV-17381~~ [ ~~MDEV-17381~~ ]

Igor Babaev (Inactive) made changes - 2018-10-06 22:42

Description

When a materialized derived table is joined using an equality predicate such that one of his parts is a field of the materialized table then hash join algorithm should not be used, because in this case a hash key is created for the table by which it is supposed to be accessed.
No we see that is not so, for example, for the following test case:
{code:sql}
CREATE TABLE t1 (
  id int NOT NULL,
  amount decimal DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE t2 (
  id int NOT NULL,
  name varchar(50) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO t1 VALUES
(1, 10.0000), (2, 20.0000), (3, 30.0000), (4, 40.0000),
(5, NULL), (6, NULL), (7, 70.0000), (8, 80.0000);

INSERT INTO t2 VALUES
(1,'A'), (2,'B'), (3,'C'), (4,'D'), (5, NULL), (6, NULL),
(7,'E'), (8,'F'), (9,'G'), (10,'H'), (11, NULL), (12, NULL);

set join_cache_level=4;

EXPLAIN
SELECT t2.id,t2.name,t.total_amt
  FROM t2
        LEFT JOIN
        (SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t
        ON t2.id=t.id
  WHERE t2.id < 3;
{code}j
We see that hash join algorithm is applied for this query:
{noformat}
MariaDB [test]> EXPLAIN
    -> SELECT t2.id,t2.name,t.total_amt
    -> FROM t2
    -> LEFT JOIN
    -> (SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t
    -> ON t2.id=t.id
    -> WHERE t2.id < 3;
+------+-------------+------------+----------+---------------+------------+---------+------------+------+-------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+------------+----------+---------------+------------+---------+------------+------+-------------------------------------+
| 1 | PRIMARY | t2 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where |
| 1 | PRIMARY | <derived2> | hash_ALL | key0 | #hash#key0 | 5 | test.t2.id | 8 | Using join buffer (flat, BNLH join) |
| 2 | DERIVED | t1 | index | NULL | PRIMARY | 4 | NULL | 8 | |
+------+-------------+------------+----------+---------------+------------+---------+------------+------+-------------------------------------+
{noformat}

When a materialized derived table is joined using an equality predicate such that one of his parts is a field of the materialized table then hash join algorithm should not be used, because in this case a hash key is created for the table by which it is supposed to be accessed.
No we see that is not so, for example, for the following test case:
{code:sql}
CREATE TABLE t1 (
  id int NOT NULL,
  amount decimal DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM;

CREATE TABLE t2 (
  id int NOT NULL,
  name varchar(50) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM;

INSERT INTO t1 VALUES
(1, 10.0000), (2, 20.0000), (3, 30.0000), (4, 40.0000),
(5, NULL), (6, NULL), (7, 70.0000), (8, 80.0000);

INSERT INTO t2 VALUES
(1,'A'), (2,'B'), (3,'C'), (4,'D'), (5, NULL), (6, NULL),
(7,'E'), (8,'F'), (9,'G'), (10,'H'), (11, NULL), (12, NULL);

set join_cache_level=4;

EXPLAIN
SELECT t2.id,t2.name,t.total_amt
  FROM t2
        LEFT JOIN
        (SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t
        ON t2.id=t.id
  WHERE t2.id < 3;
{code}j
We see that hash join algorithm is applied for this query:
{noformat}
MariaDB [test]> EXPLAIN
    -> SELECT t2.id,t2.name,t.total_amt
    -> FROM t2
    -> LEFT JOIN
    -> (SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t
    -> ON t2.id=t.id
    -> WHERE t2.id < 3;
+------+-------------+------------+----------+---------------+------------+---------+------------+------+-------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+------------+----------+---------------+------------+---------+------------+------+-------------------------------------+
| 1 | PRIMARY | t2 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where |
| 1 | PRIMARY | <derived2> | hash_ALL | key0 | #hash#key0 | 5 | test.t2.id | 8 | Using join buffer (flat, BNLH join) |
| 2 | DERIVED | t1 | index | NULL | PRIMARY | 4 | NULL | 8 | |
+------+-------------+------------+----------+---------------+------------+---------+------------+------+-------------------------------------+
{noformat}

Igor Babaev (Inactive) made changes - 2018-10-06 22:42

Status

Open [ 1 ]

In Progress [ 3 ]

Igor Babaev (Inactive) made changes - 2018-10-07 16:50

Summary

Hash join algorithm should not be used for equi-join of materialized derived table or view

Hash join algorithm should not be used to join materialized derived table / view by equality

Igor Babaev (Inactive) added a comment - 2018-10-09 12:31

A fix for this problem was pushed into 5.5 and cherry-picked into 10.0,10.1,10.2,10.3.

Igor Babaev (Inactive) added a comment - 2018-10-09 12:31 A fix for this problem was pushed into 5.5 and cherry-picked into 10.0,10.1,10.2,10.3.

Igor Babaev (Inactive) made changes - 2018-10-09 12:31

Fix Version/s		5.5.62 [ 22915 ]
Fix Version/s	5.5 [ 15800 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Richard Stracke made changes - 2018-10-09 13:35

Link

This issue is blocked by TODO-1522 [ TODO-1522 ]

Sergei Golubchik made changes - 2021-12-06 21:23

Workflow

MariaDB v3 [ 89935 ]

MariaDB v4 [ 133709 ]

People

Assignee:: Igor Babaev (Inactive)

Reporter:: Igor Babaev (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2018-10-06 22:29

Updated:: 2018-10-17 11:37

Resolved:: 2018-10-09 12:31

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration