Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.9, 10.2(EOL)
-
None
-
CentOS7 x64
Description
Test case:
CREATE TABLE _authors ( |
id MEDIUMINT(8) UNSIGNED AUTO_INCREMENT,
|
name VARCHAR(100), |
some_field MEDIUMINT(8) UNSIGNED,
|
PRIMARY KEY (id), |
index(some_field) |
);
|
|
CREATE TABLE _books ( |
id MEDIUMINT(8) UNSIGNED AUTO_INCREMENT,
|
title VARCHAR(100), |
PRIMARY KEY (id) |
);
|
CREATE TABLE _books2authors ( |
author_id MEDIUMINT(8) DEFAULT 0, |
book_id MEDIUMINT(8) DEFAULT 0, |
index(author_id), |
index(book_id) |
);
|
|
INSERT INTO _authors (name, some_field) VALUES |
('author1', 1),('author2', 2),('author3', 3); |
|
INSERT INTO _books (title) VALUES |
('book1'),('book2'),('book3'); |
|
INSERT INTO _books2authors (author_id, book_id) VALUES |
(2,1),(3,2),(3,3);
|
|
SELECT |
A.id, GROUP_CONCAT(B.title ORDER BY B.title DESC SEPARATOR ',') AS books, 0.1 + some_field AS having_field |
FROM |
_authors A
|
LEFT JOIN |
_books2authors B2A
|
FORCE INDEX(author_id) |
ON B2A.author_id = A.id |
LEFT JOIN |
_books B ON B.id = B2A.book_id |
WHERE |
1
|
GROUP BY |
A.id
|
HAVING |
having_field < 2
|
ORDER BY |
having_field ASC |
;
|
|
DROP TABLE _authors, _books, _books2authors; |
Expected:
id books having_field
1 null 1,1
Received empty result
M-A-X,
Thanks for the report and test case.
Reproducible on all 10.2 versions, with MyISAM and InnoDB. Not reproducible on 5.5-10.1 or MySQL 5.7.
10.2 ANALYZE JSON
ANALYZE
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 0.4398,
"const_condition": "1",
"having_condition": "having_field < 2",
"filesort": {
"sort_key": "0.1 + A.some_field",
"r_loops": 1,
"r_total_time_ms": 0.0373,
"r_used_priority_queue": false,
"r_output_rows": 0,
"r_buffer_size": "210",
"temporary_table": {
"read_sorted_file": {
"r_rows": 3,
"filesort": {
"sort_key": "A.`id`",
"r_loops": 1,
"r_total_time_ms": 0.035,
"r_used_priority_queue": false,
"r_output_rows": 3,
"r_buffer_size": "234",
"table": {
"table_name": "A",
"access_type": "ALL",
"r_loops": 1,
"rows": 3,
"r_rows": 3,
"r_total_time_ms": 0.0077,
"filtered": 100,
"r_filtered": 1
}
}
},
"table": {
"table_name": "B2A",
"access_type": "ref",
"possible_keys": ["author_id"],
"key": "author_id",
"key_length": "4",
"used_key_parts": ["author_id"],
"ref": ["test.A.id"],
"r_loops": 3,
"rows": 2,
"r_rows": 1,
"r_total_time_ms": 0.0212,
"filtered": 100,
"r_filtered": 100,
"attached_condition": "trigcond(B2A.author_id = A.`id`)"
},
"table": {
"table_name": "B",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "3",
"used_key_parts": ["id"],
"ref": ["test.B2A.book_id"],
"r_loops": 4,
"rows": 1,
"r_rows": 0.75,
"r_total_time_ms": 0.0119,
"filtered": 100,
"r_filtered": 100,
"attached_condition": "trigcond(B.`id` = B2A.book_id and trigcond(B2A.book_id is not null))"
}
}
}
}
}
10.1 ANALYZE JSON
ANALYZE
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 0.6897,
"const_condition": "1",
"having_condition": "(having_field < 2)",
"filesort": {
"r_loops": 1,
"r_total_time_ms": 0.0493,
"r_used_priority_queue": false,
"r_output_rows": 1,
"r_buffer_size": "273",
"filesort": {
"r_loops": 1,
"r_total_time_ms": 0.053,
"r_used_priority_queue": false,
"r_output_rows": 3,
"r_buffer_size": "234",
"temporary_table": {
"table": {
"table_name": "A",
"access_type": "ALL",
"r_loops": 1,
"rows": 3,
"r_rows": 3,
"r_total_time_ms": 0.0073,
"filtered": 100,
"r_filtered": 100
},
"table": {
"table_name": "B2A",
"access_type": "ref",
"possible_keys": ["author_id"],
"key": "author_id",
"key_length": "4",
"used_key_parts": ["author_id"],
"ref": ["test.A.id"],
"r_loops": 3,
"rows": 2,
"r_rows": 1,
"r_total_time_ms": 0.0234,
"filtered": 100,
"r_filtered": 100,
"attached_condition": "trigcond((B2A.author_id = A.`id`))"
},
"table": {
"table_name": "B",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "3",
"used_key_parts": ["id"],
"ref": ["test.B2A.book_id"],
"r_loops": 4,
"rows": 1,
"r_rows": 0.75,
"r_total_time_ms": 0.0137,
"filtered": 100,
"r_filtered": 100,
"attached_condition": "trigcond(((B.`id` = B2A.book_id) and trigcond((B2A.book_id is not null))))"
}
}
}
}
}
}