Search code examples
subquerymariadbgroup-concat

MariaDB: group_concat with subquery failing

With the exact same database structure and data on MySQL 5.6.34 (my new dev server) and MariaDB 10.2.8 (my new production server, where I thought I was finally deploying the code today - sigh!), MySQL is working and MariaDB is not. This is code that has been working fine for years on MySQL 5.0.95. I have simplified my query to the minimum example that shows the problem - it seems that GROUP_CONCAT() and subqueries do not mix. Here is the query:

SELECT person.PersonID,
GROUP_CONCAT(CategoryID ORDER BY CategoryID SEPARATOR ',') AS categories
FROM person LEFT JOIN percat ON person.PersonID=percat.PersonID
WHERE person.PersonID IN (SELECT PersonID FROM action WHERE ActionTypeID=3)
GROUP BY person.PersonID

And here is a composite image of screenshots that show the structure of all three tables involved:

enter image description here

On MySQL, it works fine, as it has worked for years. Here is the result and EXPLAIN:

MySQL 5.6.34

And this is the crazy result I get on MariaDB:

enter image description here

I don't know the inner workings of the DB engine well enough to follow EXPLAIN, but I assume the clue is in there somewhere. I found this bug report that sounds related, but I don't really understand what they're saying about it, and more importantly, what I should do about it.

Solution

  • This is a bug, apparently it is not quite the same as the one that you have found (because the test case from the mentioned bug report works all right on 10.2.8, and yours, indeed, does not). Please feel free to report a new one at MariaDB JIRA.

    Meanwhile, I think you should be able to work around it by setting 

    optimizer_switch=orderby_uses_equalities=off

    in your cnf file. It's a newly enabled optimization, obviously not flawless.

    UPDATE: The bug is now reported as https://jira.mariadb.org/browse/MDEV-13694