Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.2.6
-
None
-
10.2.6-MariaDB-10.2.6+maria~xenial-log on Ubuntu 16.04.2 LTS running Linux 4.4.0-57 x86_64
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.
Attachments
Issue Links
- duplicates
-
-
- Closed
-
Activity
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).
If you can provide the requested information, please comment and the report will be re-opened.
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.
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` |
If I furthermore add a GROUP BY clause, the result is not garbled:
| id || 2009186 || 2017037 |