Search code examples
mariadbreserved-words

Mariadb / Mysql BUG? : group by `master` in subquery

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.

Solution

  • 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.

    https://jira.mariadb.org/browse/MDEV-25714