Is this issue with optimizer?

[Karol Bieniaszewski]

Hi

 

In my real query i got hash join. Below sample on system tables.

 

SELECT

                *

FROM

    RDB$RELATIONS R

    INNER JOIN RDB$DATABASE D ON 1=1

    INNER JOIN RDB$RELATION_FIELDS RF ON RF.RDB$RELATION_NAME = COALESCE(R.RDB$RELATION_NAME, D.RDB$CHARACTER_SET_NAME)  

 WHERE R.RDB$RELATION_NAME='RDB$DATABASE'

 

I see here:

 

PLAN JOIN (D NATURAL, R INDEX (RDB$INDEX_0), RF NATURAL)

 

Select Expression

    -> Filter

        -> Nested Loop Join (inner)

            -> Filter

                -> Table "RDB$DATABASE" as "D" Full Scan

            -> Filter

                -> Table "RDB$RELATIONS" as "R" Access By ID

                    -> Bitmap

                        -> Index "RDB$INDEX_0" Unique Scan

            -> Table "RDB$RELATION_FIELDS" as "RF" Full Scan

 

As you can see we have here „RF NATURAL” but index should be used.

When i change COALESCE(R.RDB$RELATION_NAME, D.RDB$CHARACTER_SET_NAME)  to simple R.RDB$RELATION_NAME index is used.

 

Regards,

Karol Bieniaszewski

 

[Dmitry Yemanov]

13.12.2020 19:14, Karol Bieniaszewski wrote:
>
> When i change COALESCE(R.RDB$RELATION_NAME, D.RDB$CHARACTER_SET_NAME)
>  to simple R.RDB$RELATION_NAME index is used.

Looks like CORE-2975.


Dmitry

[liviuslivius]

Yes, looks exactly the same.

I have added watch to that core.

Regards,

Karol Bieniaszewski