Seems like I forgot to answer my own question for a while. Apparently I have discovered quite a serious bug in the MySQL/MariaDB engine and/or optimizer.

This bug has been reported (both to Oracle and Maria) and reproduced (confirmed) by MariaDB staff (Elena Stepanova), who already assigned it to one of their lead developers (Sergei Petrunia) and Varun Gupta. I believe it will probably be fixed quite soon, as the report was made on 2016 and their patch seems to be in final stages ("In Review" at the time of writing this): https://jira.mariadb.org/browse/MDEV-9959

With Oracle tho, well, a different story entirely...

Seems like I forgot to answer my own question for a while. Apparently I have discovered quite a serious bug in the MySQL/MariaDB engine and/or optimizer.

This bug has been reported (both to Oracle and Maria) and reproduced (confirmed) by MariaDB staff (Elena Stepanova), who already assigned it to one of their lead developers (Sergei Petrunia) and Varun Gupta. I believe it will probably be fixed quite soon, as the report was made on 2016 and their patch seems to be in final stages ("In Review" at the time of writing this): https://jira.mariadb.org/browse/MDEV-9959

With Oracle tho, well, a different story entirely...

UPDATE (November 20th, 2019): As of MariaDB 10.3.15 & 10.4.5 this bug is finally fixed.