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
