Oracle outer-join rewrite returns the wrong row for ISNULL on a nullable table

MariaDB 12.3.3 on Linux x86_64 returns a wrong result when Oracle outer-join syntax moves an ISNULL() predicate involving a table made nullable by an earlier outer join. The equivalent ANSI LEFT JOIN and prepared Oracle statement return the correct result.

Reproducer

Run the following in one MariaDB client session:

CREATE DATABASE outer_join_test;

USE outer_join_test;

SET SESSION sql_mode='ORACLE';

CREATE TABLE t1 (a INT NOT NULL);

CREATE TABLE t2 (b INT NOT NULL);

CREATE TABLE t3 (c INT NOT NULL);

INSERT INTO t1 VALUES (1),(2);

INSERT INTO t2 VALUES (2);

INSERT INTO t3 VALUES (0),(1);

SELECT 'ansi' AS phase;

SELECT a.a,b.b,c.c FROM t1 a

LEFT JOIN t2 b ON a.a=b.b

LEFT JOIN t3 c ON ISNULL(b.b)=c.c ORDER BY a.a;

SELECT 'oracle' AS phase;

SELECT a.a,b.b,c.c FROM t1 a,t2 b,t3 c

WHERE a.a=b.b(+) AND ISNULL(b.b)=c.c(+) ORDER BY a.a;

PREPARE stmt FROM

'SELECT a.a,b.b,c.c FROM t1 a,t2 b,t3 c WHERE a.a=b.b(+) AND ISNULL(b.b)=c.c(+) ORDER BY a.a';

SELECT 'prepared1' AS phase;

EXECUTE stmt;

SELECT 'prepared2' AS phase;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

SELECT 'plan' AS phase;

EXPLAIN EXTENDED SELECT a.a,b.b,c.c FROM t1 a,t2 b,t3 c

WHERE a.a=b.b(+) AND ISNULL(b.b)=c.c(+) ORDER BY a.a;

SHOW WARNINGS;

DROP DATABASE outer_join_test;

Actual result

The ANSI query and both executions of the prepared Oracle query return:

a    b     c

1    NULL  1

2    2     0

The same Oracle query executed directly instead returns:

a    b     c

1    NULL  0

2    2     0

EXPLAIN EXTENDED shows that the moved NULL test has been cached as false in the second outer join condition:

c.c = <cache>(/*always not null*/ 1 is null)

Expected result

All three forms are semantically equivalent and should return:

1    NULL  1

2    2     0

For the row where a=1, the first outer join produces b=NULL. ISNULL(b.b) therefore evaluates to 1, so the second outer join must match the row where c=1. Returning c=0 demonstrates that the NULL test was evaluated using incorrect nullability information.

The evidence supports stale expression metadata during Oracle outer-join conversion as the cause: the rewrite makes the joined table nullable, but the moved ISNULL() expression is treated as if its argument remained non-nullable.

This occurs when Oracle outer-join syntax combines consecutive outer joins with an ISNULL() predicate on the table made nullable by the first join.