Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11.8
-
Ubuntu 24.04 LTS
Description
First of all, my apologies if I've overlooked something trivial or if this has already been reported. I'm a little shaky on proper terminology when it comes to "behind the curtain" of MariaDB so I may have missed something.
I attach here a mysqldump (problem_db.sql) of three tables, along with a query (query.sql) which is returning incorrect results.
(please note: the table stock_history is intentionally empty)
Expected result:
code | po_detail_id | po_id | qty | company_id |
---|---|---|---|---|
236ae23b1f | 4828949 | 9936 | 100 | null |
8a7e75b224 | 4828956 | 9936 | 20 | null |
fb02266724 | 4828961 | 9936 | 6 | null |
8c87f5ef33 | 4829293 | 9936 | 6 | null |
274e049393 | 4829437 | 9936 | 12 | null |
748ad89040 | 4829839 | 9936 | 6 | null |
f04b3a1572 | 4829900 | 9936 | 6 | null |
Returned result:
code | po_detail_id | po_id | qty | company_id |
---|---|---|---|---|
236ae23b1f | 4828949 | 9936 | 100 | null |
8a7e75b224 | 4828956 | 9936 | null | null |
fb02266724 | 4828961 | 9936 | null | null |
8c87f5ef33 | 4829293 | 9936 | null | null |
274e049393 | 4829437 | 9936 | null | null |
748ad89040 | 4829839 | 9936 | null | null |
f04b3a1572 | 4829900 | 9936 | null | null |
As you can see, the qty column is null for all but the first row, which is incorrect. It should have a value in all rows.
(As an aside, variations of this query/dataset can result in this bug returning null for all but the first row regardless of ORDER BY clause; the query/dataset presented here as a minimised case do not seem to do this)
I have found a number of actions that will each cause the bug to be hidden and the expected results to be returned:
- Modifying the WHERE clause in query.sql to "po_id >= 9936" will return the correct results for the rows presented above. I'm not sure if other rows are incorrect (you can always add LIMIT 7 to avoid seeing the other rows anyway)
- Deleting the index on po_detail.po_id
- Deleting the foreign key from po_queue.po_detail_id to po_detail.po_detail_id
- Removing the SUM aggregate and GROUP BY in the query's derived table "po_added"
- Removing the MAX aggregate and GROUP BY in the query's derived table "stock"
- Removing the derived table "stock" from the query
I have verified this bug on two systems (one under WSL) although both were using the same platform and MariaDB package.
Thanks in advance for your help!