Wrong data while retrieving from CTEs with same column names ------------------------------------------------------------
Key: CORE-3611 URL: http://tracker.firebirdsql.org/browse/CORE-3611 Project: Firebird Core Issue Type: Bug Components: Engine Affects Versions: 2.5.0 Environment: Win32 Reporter: arni Priority: Minor Two same queries (only with different column name in a CTE) return different result sets. SQL> with CON> FIELDS_ABC as ( CON> select rdb$relation_name, count(*) CNT CON> from rdb$relation_fields CON> where rdb$field_name<'RDB$D' CON> group by 1 CON> ), CON> FIELDS_OTHER as ( CON> select rdb$relation_name, count(*) CNT CON> from rdb$relation_fields CON> where rdb$field_name>='RDB$D' CON> group by 1 CON> ) CON> CON> select cast(substring(r.rdb$relation_name from 1 for 5) as char(5)), CON> sum(f1.CNT) SUM_ABC, sum(f2.CNT) SUM_OTHER CON> from rdb$relations r CON> left join FIELDS_ABC f1 on f1.rdb$relation_name=r.rdb$relation_name CON> left join FIELDS_OTHER f2 on f2.rdb$relation_name=r.rdb$relation_name CON> where r.rdb$flags is null CON> group by 1; CAST SUM_ABC SUM_OTHER ====== ===================== ===================== MON$A 14 14 MON$C 13 13 MON$D 19 19 MON$I 6 6 MON$M 6 6 MON$R 10 10 MON$S 7 7 MON$T 13 13 RDB$B 2 2 RDB$C 9 9 RDB$D 1 1 RDB$E <null> <null> RDB$F 8 8 RDB$G <null> <null> RDB$I <null> <null> RDB$L <null> <null> RDB$P 1 1 RDB$R 7 7 RDB$S 1 1 RDB$T <null> <null> CAST SUM_ABC SUM_OTHER ====== ===================== ===================== RDB$U <null> <null> RDB$V 1 1 SQL> with CON> FIELDS_ABC as ( CON> select rdb$relation_name, count(*) CNT CON> from rdb$relation_fields CON> where rdb$field_name<'RDB$D' CON> group by 1 CON> ), CON> FIELDS_OTHER as ( CON> select rdb$relation_name, count(*) CNT____________ CON> from rdb$relation_fields CON> where rdb$field_name>='RDB$D' CON> group by 1 CON> ) CON> CON> select cast(substring(r.rdb$relation_name from 1 for 5) as char(5)), CON> sum(f1.CNT) SUM_ABC, sum(f2.CNT____________) SUM_OTHER CON> from rdb$relations r CON> left join FIELDS_ABC f1 on f1.rdb$relation_name=r.rdb$relation_name CON> left join FIELDS_OTHER f2 on f2.rdb$relation_name=r.rdb$relation_name CON> where r.rdb$flags is null CON> group by 1; CAST SUM_ABC SUM_OTHER ====== ===================== ===================== MON$A 14 <null> MON$C 13 <null> MON$D 19 <null> MON$I 6 <null> MON$M 6 <null> MON$R 10 <null> MON$S 7 <null> MON$T 13 <null> RDB$B 2 4 RDB$C 9 11 RDB$D 1 8 RDB$E <null> 5 RDB$F 8 58 RDB$G <null> 4 RDB$I <null> 17 RDB$L <null> 6 RDB$P 1 31 RDB$R 7 44 RDB$S 1 2 RDB$T <null> 24 CAST SUM_ABC SUM_OTHER ====== ===================== ===================== RDB$U <null> 8 RDB$V 1 3 -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira ------------------------------------------------------------------------------ All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2dcopy1 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel