Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.5, 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
None
Description
Originally reported at StackOverflow
Test case |
CREATE TABLE person ( |
PersonID MEDIUMINT(8) UNSIGNED AUTO_INCREMENT,
|
FullName VARCHAR(100), |
Furigana VARCHAR(100), |
Sex ENUM('M','F'), |
HouseholdID MEDIUMINT(8) UNSIGNED DEFAULT 0, |
Relation VARCHAR(6), |
Title VARCHAR(6), |
CellPhone VARCHAR(30), |
Email VARCHAR(70), |
Birtdate DATE DEFAULT '0000-00-00', |
Country VARCHAR(30), |
URL VARCHAR(150), |
Organization TINYINT(1) DEFAULT 0, |
Remarks TEXT,
|
Photo TINYINT(1) DEFAULT 0, |
UpdDate DATE DEFAULT '0000-00-00', |
PRIMARY KEY (PersonID), |
INDEX(Furigana), |
INDEX(FullName), |
INDEX(Email), |
INDEX(Organization,Furigana) |
) ;
|
 |
CREATE TABLE percat ( |
PersonID MEDIUMINT(8) DEFAULT 0, |
CategoryID MEDIUMINT(8) DEFAULT 0, |
PRIMARY KEY (PersonID, CategoryID), |
INDEX (CategoryID) |
) ;
|
 |
CREATE TABLE action ( |
ActionID MEDIUMINT(8) UNSIGNED AUTO_INCREMENT,
|
PersonID MEDIUMINT(8) UNSIGNED DEFAULT 0, |
ActionTypeID MEDIUMINT(8) UNSIGNED DEFAULT 0, |
ActionDate DATE DEFAULT '0000-00-00', |
Description TEXT,
|
PRIMARY KEY (ActionID), |
INDEX (PersonID), |
INDEX (ActionDate), |
INDEX (ActionTypeID) |
) ;
|
 |
INSERT INTO person (PersonID) VALUES |
(58),(96),(273),(352);
|
 |
INSERT INTO percat VALUES |
(58,9),(273,1),(273,9),(273,14),(352,1),(352,13);
|
 |
INSERT INTO action (PersonID, ActionTypeID) VALUES |
(58,3),(96,3),(273,3),(352,3);
|
 |
SELECT person.PersonID, |
GROUP_CONCAT(CategoryID ORDER BY CategoryID SEPARATOR ',') AS categories |
FROM person LEFT JOIN percat ON person.PersonID=percat.PersonID |
WHERE person.PersonID IN (SELECT PersonID FROM action WHERE ActionTypeID=3) |
GROUP BY person.PersonID; |
 |
DROP TABLE action, percat, person; |
Result with orderby_uses_equalities=on |
SELECT person.PersonID, |
GROUP_CONCAT(CategoryID ORDER BY CategoryID SEPARATOR ',') AS categories |
FROM person LEFT JOIN percat ON person.PersonID=percat.PersonID |
WHERE person.PersonID IN (SELECT PersonID FROM action WHERE ActionTypeID=3) |
GROUP BY person.PersonID; |
PersonID categories
|
352 1,1,1,1,13,13,13,13
|
Result with orderby_uses_equalities=off |
SELECT person.PersonID, |
GROUP_CONCAT(CategoryID ORDER BY CategoryID SEPARATOR ',') AS categories |
FROM person LEFT JOIN percat ON person.PersonID=percat.PersonID |
WHERE person.PersonID IN (SELECT PersonID FROM action WHERE ActionTypeID=3) |
GROUP BY person.PersonID; |
PersonID categories
|
58 9
|
96 NULL |
273 1,9,14
|
352 1,13
|
The second result is correct.
Attachments
Issue Links
- relates to
-
MDEV-8306 Complete cost-based optimization for ORDER BY with LIMIT
- Stalled
-
MDEV-13390 Identity server Db Select Statement order by issue.
- Closed
-
MDEV-13704 Nested query does not give same result as subquery and an outer query
- Closed