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