On MariaDB 10.4.10, the SELECT query below returns the wrong result:

CREATE TABLE tabX (pk_colXA nvarchar(255) NOT NULL, colXB_n nvarchar(255), PRIMARY KEY(pk_colXA));

CREATE TABLE tabZ (pk_colZA nvarchar(255) NOT NULL, pk_colZB nvarchar(255) NOT NULL, PRIMARY KEY(pk_colZA, pk_colZB));

INSERT INTO tabX (pk_colXA, colXB_n) VALUES ('X1', 'a');

INSERT INTO tabX (pk_colXA, colXB_n) VALUES ('X4', 'b');

INSERT INTO tabZ (pk_colZA, pk_colZB) VALUES ('Z1', 'X1');

-- The bug appears if derived_merge=on, as is the default.

-- SET optimizer_switch='derived_merge=off';

SELECT * FROM (

  SELECT pk_colXA, pk_colZA, constCol

  FROM tabX LEFT JOIN (

    SELECT pk_colZA, pk_colZB, 1 AS constCol FROM tabZ

  ) t1 ON pk_colZB = pk_colXA

) t2 ORDER BY pk_colXA, pk_colZA;

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