Since 10.3.29-1, MariaDb is not able to complete correctly LEFT JOIN in SUBQUERY that have multiple ON clause columns coming from different tables.

The only way to make it works is to substitute all INNER JOIN with the "STRAIGHT_JOIN" on all tables that have the columns needed by "ON clause" of the LEFT JOIN , this will fix results but on large datasets will take likely 30 - 40 seconds instead of few microseconds.

So the only way it works back again is to downgrade to 10.3.28.

I have to said that this is really disappointing: this is likely the 3rd critical bug in less than 1 year.
APPs suddenly stop to works and it tooks hours to understand that the issue is not the query itself but instead the latest MariaDb's update.
This is becoming really too much frequent.