[Explained Plan]Transformation of outer join into inner

[livius2]

As new optimizer feature arised: https://github.com/FirebirdSQL/firebird/issues/6992

Will be good to see such situation in Explained Plan I do not know how it should looks like, but maybe like this? Nested Loop Join (inner transformed)

[dyemanov]

Transformation happens before optimization, so the deeper layers have no idea whether the join is real or transformed.

[dyemanov]

In fact, we have plenty of cases where the query is being rewritten before optimization, join transformation is just one of them.

[livius2]

It will be really interesting. I can then show im my query and plan analyzer tool warning about such transformation. As you know here are two cases:

1. Query should be really INNER JOIN.
2. Query should not be an INNER JOIN and someone write it wrongly, wrong where clause. Someone wrote DET.FIELD2 LIKE 'abc%' and miss that it should be (DET.FIELD1 IS NULL OR DET.FIELD2 LIKE 'abc%')

PS.

where the query is being rewritten before optimization

which transformations do you have in mind?

[dyemanov]

1. A IN (1) => A = 1
2. NOT A > 1 => A <= 1
3. A NOT IN (SELECT ...) => complex expression with multiple subqueries

And something may also be changed inside the optimizer:

1. A BETWEEN B AND C => A >= B AND A <= C
2. A LIKE 'ABC%' => A STARTING WITH 'ABC' AND A LIKE 'ABC%'

[livius2]

I don't know what is behind point 3, but all other mentioned transformations/optimizations are "normal" and i suppose don't deserve to be included in explained plan. Thank you for bring light about them.