[MDEV-13694] Wrong result upon GROUP BY with orderby_uses_equalities=on Created: 2017-08-31  Updated: 2020-09-23  Resolved: 2020-07-09

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.6.0

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Varun Gupta (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-8306 Complete cost-based optimization for ... Stalled
relates to MDEV-13390 Identity server Db Select Statement o... Closed
relates to MDEV-13704 Nested query does not give same resul... Closed

 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.

 Comments   
Comment by Sergei Petrunia [ 2017-09-12 ] 

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;

Comment by Varun Gupta (Inactive) [ 2017-09-12 ] 

explain
 
SELECT person.PersonID 
FROM person LEFT JOIN percat ON person.PersonID=percat.PersonID
 
WHERE person.PersonID IN (SELECT action.PersonID FROM action WHERE ActionTypeID=3)
 
GROUP BY person.PersonID;
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
1       PRIMARY <subquery2>     ALL     distinct_key    NULL    NULL    NULL    3       Using temporary; Using filesort
 
1       PRIMARY person  eq_ref  PRIMARY PRIMARY 3       test.action.PersonID    1       Using index
 
1       PRIMARY percat  ref     PRIMARY PRIMARY 3       test.action.PersonID    1       Using where; Using index
 
2       MATERIALIZED    action  ref     PersonID,ActionTypeID   ActionTypeID    4       const   3       Using where

Comment by Varun Gupta (Inactive) [ 2017-09-12 ] 

set optimizer_switch='orderby_uses_equalities=off';
 
explain
 
SELECT person.PersonID,
 
GROUP_CONCAT(CategoryID) AS categories
 
FROM person LEFT JOIN percat ON person.PersonID=percat.PersonID
 
WHERE person.PersonID IN (SELECT action.PersonID FROM action WHERE ActionTypeID=3)
 
GROUP BY person.PersonID;
 
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
1       PRIMARY <subquery2>     ALL     distinct_key    NULL    NULL    NULL    3       Using temporary; Using filesort
 
1       PRIMARY person  eq_ref  PRIMARY PRIMARY 3       test.action.PersonID    1       Using index
 
1       PRIMARY percat  ref     PRIMARY PRIMARY 3       test.action.PersonID    1       Using where; Using index
 
2       MATERIALIZED    action  ref     PersonID,ActionTypeID   ActionTypeID    4       const   3       Using where

Comment by Sergei Petrunia [ 2017-09-12 ]

This might be related to MDEV-13390

Comment by Varun Gupta (Inactive) [ 2017-09-16 ]

Simplified query

SELECT 
  person.PersonID,
 
  GROUP_CONCAT(person.PersonID) AS categories
 
FROM person
 
  WHERE person.PersonID IN (SELECT action.PersonID FROM action WHERE ActionTypeID=3)
 
GROUP BY person.PersonID;

Comment by Varun Gupta (Inactive) [ 2017-09-16 ] 

explain
 
SELECT person.PersonID, 
GROUP_CONCAT(person.PersonID) AS categories
 
FROM person
 
WHERE person.PersonID IN (SELECT action.PersonID FROM action WHERE ActionTypeID=3)
 
GROUP BY person.PersonID;
 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 
1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	Using filesort
 
1	PRIMARY	person	eq_ref	PRIMARY	PRIMARY	3	test.action.PersonID	1	Using index
 
2	MATERIALIZED	action	ref	PersonID,ActionTypeID	ActionTypeID	4	const	3	Using where

Comment by Varun Gupta (Inactive) [ 2017-09-19 ]

Idea that is decided to be implemented

  • let the sort key be person.personID
  • let filesort use rr_sequential_and_unpack() (or something similar) so that when filesort is reading <subquery2> temp.table, the value of person.personID also gets updated.
Comment by Varun Gupta (Inactive) [ 2017-12-20 ]

Decided during the optimizer call to fix this in 10.3 rather than fixing in stable versions

Comment by Varun Gupta (Inactive) [ 2018-05-04 ]

Patch
http://lists.askmonty.org/pipermail/commits/2018-January/011786.html

Comment by Varun Gupta (Inactive) [ 2019-09-21 ]

Changed priority to Critical as this is required for MDEV-8306

Comment by Sergei Petrunia [ 2019-09-24 ]

Review input: http://lists.askmonty.org/pipermail/commits/2019-September/014011.html

Needs to be addressed.

Comment by Varun Gupta (Inactive) [ 2019-09-28 ]

Patch addressing the review
http://lists.askmonty.org/pipermail/commits/2019-September/014014.html

Comment by Sergei Petrunia [ 2019-10-10 ]

Ok to push the last version of the patch.

Comment by Varun Gupta (Inactive) [ 2020-07-09 ]

On version earlier than 10.6, the query plan is like

MariaDB [test]> EXPLAIN SELECT t1.a
 
    -> FROM t1
 
    -> WHERE t1.a IN (SELECT a FROM t2 WHERE b=3)
 
    -> ORDER BY t1.a DESC;
 
+------+--------------+-------------+--------+---------------+---------+---------+-----------+------+---------------------------------+
 
| id   | select_type  | table       | type   | possible_keys | key     | key_len | ref       | rows | Extra                           |
 
+------+--------------+-------------+--------+---------------+---------+---------+-----------+------+---------------------------------+
 
|    1 | PRIMARY      | <subquery2> | ALL    | distinct_key  | NULL    | NULL    | NULL      |    3 | Using temporary; Using filesort |
 
|    1 | PRIMARY      | t1          | eq_ref | PRIMARY       | PRIMARY | 4       | test.t2.a |    1 | Using index                     |
 
|    2 | MATERIALIZED | t2          | ALL    | NULL          | NULL    | NULL    | NULL      |    3 | Using where                     |
 
+------+--------------+-------------+--------+---------------+---------+---------+-----------+------+---------------------------------+
 
3 rows in set (0.00 sec)

With 10.6 onwards, the query plan is

MariaDB [test]> EXPLAIN SELECT t1.a
 
    -> FROM t1
 
    -> WHERE t1.a IN (SELECT a FROM t2 WHERE b=3)
 
    -> ORDER BY t1.a DESC;
 
+------+--------------+-------------+--------+---------------+---------+---------+-----------+------+----------------+
 
| id   | select_type  | table       | type   | possible_keys | key     | key_len | ref       | rows | Extra          |
 
+------+--------------+-------------+--------+---------------+---------+---------+-----------+------+----------------+
 
|    1 | PRIMARY      | <subquery2> | ALL    | distinct_key  | NULL    | NULL    | NULL      | 3    | Using filesort |
 
|    1 | PRIMARY      | t1          | eq_ref | PRIMARY       | PRIMARY | 4       | test.t2.a | 1    | Using index    |
 
|    2 | MATERIALIZED | t2          | ALL    | NULL          | NULL    | NULL    | NULL      | 3    | Using where    |
 
+------+--------------+-------------+--------+---------------+---------+---------+-----------+------+----------------+
 
3 rows in set (0.004 sec)

The sjm scan table uses filesort. There is no usage of temp table from 10.6 onwards for sorting

Comment by Elena Stepanova [ 2020-09-22 ]

The initially reported wrong result was fixed in 10.2+ by the patch for MDEV-13994.

Comment by Varun Gupta (Inactive) [ 2020-09-23 ]

So a bit more summary regarding the changes introduces in this mdev.
Commit hash for MDEV-13994: dcbf2823c7d64380f06372d77d1522e97fb8f066

On 10.2 before MDEV-13994 was pushed

MariaDB [test]> EXPLAIN SELECT t1.a, group_concat(t1.b) FROM t1  WHERE t1.a IN (SELECT a FROM t2 WHERE b=3) GROUP BY t1.a DESC;
 
+------+--------------+-------------+--------+---------------+---------+---------+-----------+------+----------------+
 
| id   | select_type  | table       | type   | possible_keys | key     | key_len | ref       | rows | Extra          |
 
+------+--------------+-------------+--------+---------------+---------+---------+-----------+------+----------------+
 
|    1 | PRIMARY      | <subquery2> | ALL    | distinct_key  | NULL    | NULL    | NULL      |    3 | Using filesort |
 
|    1 | PRIMARY      | t1          | eq_ref | PRIMARY       | PRIMARY | 4       | test.t2.a |    1 |                |
 
|    2 | MATERIALIZED | t2          | ALL    | NULL          | NULL    | NULL    | NULL      |    3 | Using where    |
 
+------+--------------+-------------+--------+---------------+---------+---------+-----------+------+----------------+
 
3 rows in set (0.00 sec)


MariaDB [test]> SELECT t1.a, group_concat(t1.b) FROM t1  WHERE t1.a IN (SELECT a FROM t2 WHERE b=3) GROUP BY t1.a DESC;
 
+-----+--------------------+
 
| a   | group_concat(t1.b) |
 
+-----+--------------------+
 
| 273 | 3,3,3              |
 
+-----+--------------------+
 
1 row in set (0.00 sec)

The plan here is using filesort on the SJM scan table.
This returns incorrect results on 10.2 onwards.

On 10.2 after patch of MDEV-13994

MariaDB [test]> EXPLAIN SELECT t1.a, group_concat(t1.b) FROM t1  WHERE t1.a IN (SELECT a FROM t2 WHERE b=3) GROUP BY t1.a DESC;
 
+------+--------------+-------------+--------+---------------+---------+---------+-----------+------+---------------------------------+
 
| id   | select_type  | table       | type   | possible_keys | key     | key_len | ref       | rows | Extra                           |
 
+------+--------------+-------------+--------+---------------+---------+---------+-----------+------+---------------------------------+
 
|    1 | PRIMARY      | <subquery2> | ALL    | distinct_key  | NULL    | NULL    | NULL      |    3 | Using temporary; Using filesort |
 
|    1 | PRIMARY      | t1          | eq_ref | PRIMARY       | PRIMARY | 4       | test.t2.a |    1 |                                 |
 
|    2 | MATERIALIZED | t2          | ALL    | NULL          | NULL    | NULL    | NULL      |    3 | Using where                     |
 
+------+--------------+-------------+--------+---------------+---------+---------+-----------+------+---------------------------------+
 
3 rows in set (0.03 sec)
 



MariaDB [test]> SELECT t1.a, group_concat(t1.b) FROM t1  WHERE t1.a IN (SELECT a FROM t2 WHERE b=3) GROUP BY t1.a DESC;
 
+-----+--------------------+
 
| a   | group_concat(t1.b) |
 
+-----+--------------------+
 
| 273 | 3                  |
 
|  96 | 2                  |
 
|  58 | 1                  |
 
+-----+--------------------+
 
3 rows in set (0.00 sec)

The plan here use temp table for filesort. So here the fix was to disable using filesort on the first table if the first table was a SJM scan table.
This fixed the wrong results as we are using a different execution path now.

On 10.6 after MDEV-13694 was pushed

MariaDB [test]> EXPLAIN SELECT t1.a, group_concat(t1.b) FROM t1  WHERE t1.a IN (SELECT a FROM t2 WHERE b=3) GROUP BY t1.a DESC;
 
+------+--------------+-------------+--------+---------------+---------+---------+-----------+------+----------------+
 
| id   | select_type  | table       | type   | possible_keys | key     | key_len | ref       | rows | Extra          |
 
+------+--------------+-------------+--------+---------------+---------+---------+-----------+------+----------------+
 
|    1 | PRIMARY      | <subquery2> | ALL    | distinct_key  | NULL    | NULL    | NULL      | 3    | Using filesort |
 
|    1 | PRIMARY      | t1          | eq_ref | PRIMARY       | PRIMARY | 4       | test.t2.a | 1    |                |
 
|    2 | MATERIALIZED | t2          | ALL    | NULL          | NULL    | NULL    | NULL      | 3    | Using where    |
 
+------+--------------+-------------+--------+---------------+---------+---------+-----------+------+----------------+
 
3 rows in set (0.002 sec)
 



MariaDB [test]> SELECT t1.a, group_concat(t1.b) FROM t1  WHERE t1.a IN (SELECT a FROM t2 WHERE b=3) GROUP BY t1.a DESC;
 
+-----+--------------------+
 
| a   | group_concat(t1.b) |
 
+-----+--------------------+
 
| 273 | 3                  |
 
|  96 | 2                  |
 
|  58 | 1                  |
 
+-----+--------------------+
 
3 rows in set (0.003 sec)

On 10.6 the plan uses filesort on the SJM scan table and also gives the correct result.
This would ensure performance gains if the ORDER BY clause is resolved by the first table.

Generated at Thu Feb 08 08:07:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.