On MariaDB 10.4.10, the SELECT query below returns the wrong result:
The query should return two rows, where constCol=null in the second row. Instead, constCol is 1 in both result rows. This is incorrect, because the second row corresponds to an unmatched row in the left join. Doing 'SET optimizer_switch='derived_merge=off'' causes the result to change and become correct. Similarly, removing the outer "SELECT * FROM" level will cause the result to become correct. So this must be a query optimizer bug.
This bug was triggered by the test suite of Ultorg, a visual query interface for relational databases ( https://twitter.com/ultorg ). This test suite also runs on Oracle, SQL Server, PostgreSQL, and MySQL. The bug was only present on MariaDB. In particular, MySQL 8.0.23 does not exhibit the bug. Older Oracle versions had a similar bug, Oracle bug #6653652, which could be similarly sidestepped by setting 'alter session set "_simple_view_merging" = false'.