I have 2 tables:
sales:
| id | invoice | valid |
|---|---|---|
| 100 | IV001 | Y |
payment:
| id | master | amount | test |
|---|---|---|---|
| 110 | 100 | 200 | 100 |
| 111 | 100 | 300 | 100 |
| 112 | 101 | 400 | 101 |
Then I made a query:
select master, sum(amount) amount from payment group by master
and give result as expected
| master | amount |
|---|---|
| 100 | 500 |
| 101 | 400 |
But when I run following query:
select s.id, s.invoice, p.amount from sales s join (select `master`, sum(amount) amount from payment group by `master`) p on p.`master`=s.id
I got wrong result like this
| id | invoice | amount |
|---|---|---|
| 100 | IV001 | 900 (should be 500) |
But, if I use column test as group column, result will be ok.
I run this query:
select s.id, s.invoice, p.amount from sales s join (select test, sum(amount) amount from payment group by test) p on p.test=s.id
and everything looked as expected.
I guess this related to column master which is reserved word, and I used backtick (`) already.
My qustion is: If I am still want to use master as colum name, how to solve this? or is it a Mariadb / Mysql bug ?
(I run Mariadb version 10.3 & 10.5 on Ubuntu 21.04)
EDIT
Please watch this video: https://youtu.be/e2EEin1mf-E
Thanks in advance.
This is a bug in Mariadb 10.3.29, 10.4.19, 10.5.10 which has now been fixed but not yet released. We came across the same issue and had to roll back to 10.4.18.