[pkg-firebird-general] Bug#693196: aliased derived fields not optimized via an index

[Damyan Ivanov]

Source: firebird2.5
Version: 2.5.1
Severity: important
Tags: upstream fixed-upstream
Forwarded: http://tracker.firebirdsql.org/browse/CORE-3902

This is a performance regression compared to 2.1 series.

Quoting from upstream bug report:

select rdb$database.rdb$relation_id
from rdb$database
  left outer join
  (
    select
      rdb$relations.rdb$relation_id as tempid
    from rdb$relations
  ) temp (tempid)
  on temp.tempid = rdb$database.rdb$relation_id

n Firebird 2.1 the plan is good, i.e. an Index join to RDB$RELATIONS.
PLAN JOIN (RDB$DATABASE NATURAL, TEMP RDB$RELATIONS INDEX (RDB$INDEX_1))

In Firebird 2.5 the plan becomes an natural read join to RDB$RELATIONS.
PLAN JOIN (RDB$DATABASE NATURAL, TEMP RDB$RELATIONS NATURAL)

However, the interesting thing is if I remove either the 'as tempid' or 
'(tempid)' alias, or don't use alias at all, the plan goes back to the 2.1 
version.

Clearly, using an alias should not have caused a change in execution plan.

Upstream patches are at 
http://firebird.svn.sourceforge.net/viewvc/firebird/firebird/branches/B2_5_Release/src/dsql/gen.cpp?pathrev=57052&view=diff&r1=57052&r2=57051&diff_format=h 
and 
http://firebird.svn.sourceforge.net/viewvc/firebird/firebird/branches/B2_5_Release/src/dsql/gen.cpp?pathrev=57244&view=diff&r1=57244&r2=57243&diff_format=h


-- System Information:
Debian Release: wheezy/sid
  APT prefers unstable
  APT policy: (500, 'unstable'), (500, 'testing'), (500, 'stable'), (1, 'experimental')
Architecture: amd64 (x86_64)
Foreign Architectures: i386

Kernel: Linux 3.2.0-4-amd64 (SMP w/4 CPU cores)
Locale: LANG=bg_BG.UTF-8, LC_CTYPE=bg_BG.UTF-8 (charmap=UTF-8)
Shell: /bin/sh linked to /bin/dash