Oracle outer join syntax (+): some NULLs missing from result of the query with derived tables and limit

set SQL_MODE= oracle;

create table t2 (b int);

insert into t2 values (3),(7),(1);

create table t3 (c int);

insert into t3 values (3),(1);

create table t1 (a int);

insert into t1 values (1),(2),(7),(1);

select * from

(

  select * from

    (select 'Z' as z, t1.a from t1) dt1

    left join 

      (select 'Y' as y, t2.b from t2) dt2

      left join

      (select 'X' as x, t3.c from t3) dt3

      on dt2.b=dt3.c

    on dt1.a=dt2.b

  order by z, a, y, b, x, c

  limit 9

) dt;

select * from

(

  select * from

    (select 'Z' as z, t1.a from t1) dt1

    ,(select * from

      (select 'Y' as y, t2.b from t2) dt2

      ,

      (select 'X' as x, t3.c from t3) dt3

      where dt2.b=dt3.c(+)

    ) tdt2

    where dt1.a=tdt2.b(+)

    order by z, a, y, b, x, c

  limit 9

) dt;

drop table t1, t2, t3;

explain extended

select * from

(

select * from

(select 'Z' as z, t1.a from t1) dt1

left join 

(select 'Y' as y, t2.b from t2) dt2

left join

(select 'X' as x, t3.c from t3) dt3

on dt2.b=dt3.c

on dt1.a=dt2.b

order by z, a, y, b, x, c

limit 9

) dt;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	9	100.00	

2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	Using temporary; Using filesort

2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)

2	DERIVED	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)

Warnings:

Note	1003	/* select#1 */ select "dt"."z" AS "z","dt"."a" AS "a","dt"."y" AS "y","dt"."b" AS "b","dt"."x" AS "x","dt"."c" AS "c" from (/* select#2 */ select 'Z' AS "z","test"."t1"."a" AS "a",'Y' AS "y","test"."t2"."b" AS "b",'X' AS "x","test"."t3"."c" AS "c" from "test"."t1" left join ("test"."t2" left join ("test"."t3") on("test"."t3"."c" = "test"."t1"."a")) on("test"."t2"."b" = "test"."t1"."a") where 1 order by 'Z',"test"."t1"."a",'Y',"test"."t2"."b",'X',"test"."t3"."c" limit 9) "dt"

explain extended

select * from

(

select * from

(select 'Z' as z, t1.a from t1) dt1

,(select * from

(select 'Y' as y, t2.b from t2) dt2

,

(select 'X' as x, t3.c from t3) dt3

where dt2.b=dt3.c(+)

) tdt2

where dt1.a=tdt2.b(+)

order by z, a, y, b, x, c

limit 9

) dt;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	9	100.00	

2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	Using temporary; Using filesort

2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)

2	DERIVED	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)

Warnings:

Note	1003	/* select#1 */ select "dt"."z" AS "z","dt"."a" AS "a","dt"."y" AS "y","dt"."b" AS "b","dt"."x" AS "x","dt"."c" AS "c" from (/* select#2 */ select 'Z' AS "z","test"."t1"."a" AS "a",'Y' AS "y","test"."t2"."b" AS "b",'X' AS "x","test"."t3"."c" AS "c" from "test"."t1" left join ("test"."t2" left join ("test"."t3") on("test"."t3"."c" = "test"."t1"."a")) on("test"."t2"."b" = "test"."t1"."a") where 1 order by 'Z',"test"."t1"."a",'Y',"test"."t2"."b",'X',"test"."t3"."c" limit 9) "dt"

select * from

(

select * from

(select 'Z' as z, t1.a from t1 order by z) dt1

left join 

(select 'Y' as y, t2.b from t2 order by y) dt2

left join

(select 'X' as x, t3.c from t3 order by x) dt3

on dt2.b=dt3.c

on dt1.a=dt2.b

order by z, a, y, b, x, c

limit 9

) dt;

z	a	y	b	x	c

Z	1	Y	1	X	1

Z	1	Y	1	X	1

Z	2	NULL	NULL	NULL	NULL

Z	7	Y	7	NULL	NULL

select * from

(

select * from

(select 'Z' as z, t1.a from t1 order by z) dt1

,(select * from

(select 'Y' as y, t2.b from t2 order by y) dt2

,

(select 'X' as x, t3.c from t3 order by x) dt3

where dt2.b=dt3.c(+) order by y, x

) tdt2

where dt1.a=tdt2.b(+)

order by z, a, y, b, x, c

limit 9

) dt;

z	a	y	b	x	c

Z	1	Y	1	X	1

Z	1	Y	1	X	1

Z	2		NULL		NULL

Z	7	Y	7		NULL