Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6.15, 10.11.5
-
official docker image
Description
Hello,
after enabling jemalloc, we started to get random missing rows from result - sometimes 4 rows, sometimes 3 rows, sometime 2 rows ....
I was able to create minimal reproducible that shows error both with mariadb:10.6.15 and current master quay.io/mariadb-foundation/mariadb-devel:10.6 with jemalloc turned on. Without jemalloc everything works.
This is preview of repro: https://dbfiddle.uk/rXJGvk_h - e-shop with order items, every item has link to product and each product has more sections
The main cause seems to be this part of query. We use subselect in JOIN to join always only one section with highest priority:
...
LEFT JOIN sections s ON s.id = (
SELECT s2.id
FROM sections s2
LEFT JOIN products_sections ps ON s2.id = ps.id_section
WHERE ps.id_product = p.id
ORDER BY s2.priority DESC
LIMIT 1
)
...
when I remove ORDER BY it behaves normally. The problem seems to be that subselect matches two rows with same priority, so it picks random row. And sometimes it leads to completely missing row in result - it is an error, LEFT JOIN should never remove rows from result. When I run this query 10 times, I get 5 times 4 rows, 5 times only 3 rows.
I'm able to test this on newer Mariadb versions if it will help.
Attachments
Activity
I had the latest 10.4 from github. And a debug build — that's just what I happened to have ready.
May be it depends on the compiler? I've got gcc 13.2.1 20230826.
It doesn't matter much where it repeats, when we'll have the fix, we'll see if it applies to 10.4 too or only to 10.5 and up
We really need to try jemalloc to avoid OOMs on our 10.6.12 MariaDB Servers.
Now we are scared to use it in production, because of this bug. Less because of this specific query, but more of the possibility that there are more querys that give wrong results if we use jemalloc.
Did maybe someone from MariaDB already look into this issue, found the reason and can therefore say if its really only this specific edge case query with left join, subselect, order by and limit?
I just tried to run your mtr test on 10.4.31 (on Ubuntu 20.04) to be sure that 10.4 is really affected, but I can't reproduce it on this version, what patch version of mariadb and OS did you use @Sergei?
main.b 'innodb' [ 1998 pass ] 84main.b 'innodb' [ 1999 pass ] 78main.b 'innodb' [ 2000 pass ] 113--------------------------------------------------------------------------The servers were restarted 0 timesSpent 167.609 of 317 seconds executing testcasesCompleted: All 2000 tests were successful.Then I tested on two other versions (10.6, 11.2) to be sure that I tested correctly:
main.b 'innodb' [ 196 pass ] 49main.b 'innodb' [ 197 fail ]Test ended at 2023-10-12 17:05:48CURRENT_TEST: main.c--- /usr/share/mysql/mysql-test/main/b.result 2023-10-12 16:56:55.047760483 +0200+++ /usr/share/mysql/mysql-test/main/b.reject 2023-10-12 17:05:47.912103398 +0200@@ -21,6 +21,5 @@p1 s1 s21 # #2 # #-3 # #4 # #drop tables ps,s,o,p;mysqltest: Result length mismatch- skipping '/usr/share/mysql/mysql-test/var/log/main.b-innodb/'main.b 'innodb' [ 198 pass ] 53main.b 'innodb' [ 199 pass ] 50main.b 'innodb' [ 200 pass ] 58--------------------------------------------------------------------------The servers were restarted 1 timesSpent 9.841 of 50 seconds executing testcasesCompleted: Failed 35/200 tests, 82.50% were successful.