26.03.2013 3:33, Thomas Beckmann wrote: > It seems to me we have a bug in nested joins: > > Consider the statement below. It's supposed to show foreign key fields > which it does nicely with FB2.5. With 3.0 you'll get the error "Column > unknown. RF.RDB$FIELD_NAME. At line 11, column 32." - this is the > postponed on clause (i.RDB$RELATION_NAME = rf.RDB$RELATION_NAME): > > select > rf.RDB$RELATION_NAME, > rf.RDB$FIELD_NAME, > s.RDB$INDEX_NAME > from RDB$RELATION_FIELDS rf > left join (RDB$INDICES i > join RDB$INDEX_SEGMENTS s > on i.RDB$INDEX_NAME = s.RDB$INDEX_NAME and s.RDB$FIELD_NAME = > rf.RDB$FIELD_NAME > join RDB$RELATION_CONSTRAINTS rc > on rc.RDB$INDEX_NAME = s.RDB$INDEX_NAME and rc.RDB$CONSTRAINT_TYPE = > 'FOREIGN KEY') > on i.RDB$RELATION_NAME = rf.RDB$RELATION_NAME > where > rf.RDB$FIELD_SOURCE starting with 'D_FK_' > > Did I miss something or does this turn out to be a bug?
This was an intended change which probably became an overkill. I will report back after closer investigation. Nevertheless, your query has another mistake that FB3 will bark at and this time it's expected. By the SQL spec, A join B on <condition> implies a derived table, so that <condition> is allowed to reference only A and B, not any outer stream. So s.RDB$FIELD_NAME = rf.RDB$FIELD_NAME is not going to work anymore in FB3. Dmitry ------------------------------------------------------------------------------ Own the Future-Intel® Level Up Game Demo Contest 2013 Rise to greatness in Intel's independent game demo contest. Compete for recognition, cash, and the chance to get your game on Steam. $5K grand prize plus 10 genre and skill prizes. Submit your demo by 6/6/13. http://p.sf.net/sfu/intel_levelupd2d Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
