[MDEV-12959] ORDER BY is mixing up rows Created: 2017-05-31  Updated: 2017-09-14  Resolved: 2017-09-14

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.2.6
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Christian Rishøj Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Environment:

10.2.6-MariaDB-10.2.6+maria~xenial-log on Ubuntu 16.04.2 LTS running Linux 4.4.0-57 x86_64

Issue Links:
Duplicate
duplicates MDEV-13390 Identity server Db Select Statement o... Closed

 Description   

Consider the output of the following query:

MariaDB [music]> SELECT o.id FROM orders o 
WHERE EXISTS(SELECT 1 FROM payments p WHERE p.orderId = o.id AND p.paymentTypeId = 95);
 
+---------+
 
| id      |
 
+---------+
 
| 2009186 |
 
| 2017037 |
 
+---------+
 
2 rows in set (0.00 sec)

Now, if I add an ORDER BY clause, the query still returns two results, but the latter row is repeated (and the former row gone):

MariaDB [music]> SELECT o.id FROM orders o 
WHERE EXISTS(SELECT 1 FROM payments p WHERE p.orderId = o.id AND p.paymentTypeId = 95) 
ORDER BY o.id;
 
+---------+
 
| id      |
 
+---------+
 
| 2017037 |
 
| 2017037 |
 
+---------+
 
2 rows in set (0.00 sec)

I have attempted to recreate a minimal test case for the issue, but with not success.



 Comments   
Comment by Christian Rishøj [ 2017-05-31 ]

If I furthermore add a GROUP BY clause, the result is not garbled:

MariaDB [music]> SELECT o.id FROM orders o 
WHERE EXISTS(SELECT 1 FROM payments p WHERE p.orderId = o.id AND p.paymentTypeId = 95) 
GROUP BY o.id 
ORDER BY o.id;
 
+---------+
 
| id      |
 
+---------+
 
| 2009186 |
 
| 2017037 |
 
+---------+
 
2 rows in set (0.00 sec)

Comment by Elena Stepanova [ 2017-06-04 ]

Could you please provide whatever (non-minimal) test case that you have?
It would be great if you could upload the datadump (to ftp.askmonty.org/private).
If you can't do it, please at least paste the output of

SHOW CREATE TABLE orders;
 
SHOW INDEX IN orders;
 
SHOW CREATE TABLE payments;
 
SHOW INDEX IN payments;

and please also attach you cnf file(s).

Comment by Elena Stepanova [ 2017-07-03 ]

If you can provide the requested information, please comment and the report will be re-opened.

Comment by Sam Hemelryk [ 2017-09-13 ]

Hi elenst,

We countered this issue or at least one very similar this week, and have worked out a simple set of SQL statements to replicate our observations.

To reproduce

# Environment setup
 
CREATE DATABASE IF NOT EXISTS mdev_12959;
 
DROP TABLE IF EXISTS a;
 
DROP TABLE IF EXISTS b;
 
CREATE TABLE a (aid INT, PRIMARY KEY (aid));
 
CREATE TABLE b (bid INT);
 
# Minimal dataset
 
INSERT INTO a (aid) VALUES (1), (2), (3);
 
INSERT INTO b (bid) VALUES (1), (3);
 
# Test 1: Expected outcome 1,3 :: Actual outcome 1,3 == Success
 
SELECT aid FROM a WHERE EXISTS (SELECT 0 FROM b WHERE bid = aid);
 
# Test 2: Expected outcome 1,3 :: Actual outcome 3,3 == Failure
 
SELECT aid FROM a WHERE EXISTS (SELECT 0 FROM b WHERE bid = aid) ORDER BY aid;
 
# Test 3: Expected outcome 1,3 :: Actual outcome 1,3 == Success
 
SELECT aid FROM a WHERE EXISTS (SELECT 0 FROM b WHERE bid = aid ORDER BY aid) ORDER BY aid;

We believe the outcome of test 2 is the failure, in that we expect it to return the 1,3 but it actually returns 3,3.

Notes

  • We have reproduced this on 10.2.5, 10.2.6, 10.2.7, and 10.3.1
  • If bid is made a primary key test 2 gives us the expected result 1,3.
  • If the primary key on aid is removed test 2 gives us the expected result 1,3.
  • If bid is made primary, and primary is removed from aid test 2 gives us the expected result 1,3.
Comment by Alice Sherepa [ 2017-09-14 ]

This is a duplicate of MDEV-13390
Suggested workaround is to set optimizer_switch='materialization=off'

SELECT aid FROM a WHERE EXISTS (SELECT 0 FROM b WHERE bid = aid) ORDER BY aid;
 
+-----+
 
| aid |
 
+-----+
 
|   3 |
 
|   3 |
 
+-----+
 
 
 
explain extended SELECT aid FROM a WHERE EXISTS (SELECT 0 FROM b WHERE bid = aid) ORDER BY aid;
 
+------+--------------+-------------+--------+---------------+---------+---------+------------+------+----------+----------------+
 
| id   | select_type  | table       | type   | possible_keys | key     | key_len | ref        | rows | filtered | Extra          |
 
+------+--------------+-------------+--------+---------------+---------+---------+------------+------+----------+----------------+
 
|    1 | PRIMARY      | <subquery2> | ALL    | distinct_key  | NULL    | NULL    | NULL       |    2 |   100.00 | Using filesort |
 
|    1 | PRIMARY      | a           | eq_ref | PRIMARY       | PRIMARY | 4       | test.b.bid |    1 |   100.00 | Using index    |
 
|    2 | MATERIALIZED | b           | ALL    | NULL          | NULL    | NULL    | NULL       |    2 |   100.00 | Using where    |
 
+------+--------------+-------------+--------+---------------+---------+---------+------------+------+----------+----------------+
 
Note (Code 1276): Field or reference 'test.a.aid' of SELECT #2 was resolved in SELECT #1
 
Note (Code 1003): select `test`.`a`.`aid` AS `aid` from `test`.`a` semi join (`test`.`b`) where `test`.`a`.`aid` = `test`.`b`.`bid` order by `test`.`a`.`aid`

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