Issue Details (XML | Word | Printable)

Key: CORE-3611
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Minor Minor
Assignee: Adriano dos Santos Fernandes
Reporter: arni
Votes: 0
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Wrong data while retrieving from CTEs (or derived tables) with same column names.

Created: 29/Sep/11 12:50 PM   Updated: 27/Mar/14 01:10 PM
Component/s: Engine
Affects Version/s: 3.0 Initial, 2.5.0, 2.5.1
Fix Version/s: 2.5.2, 3.0 Alpha 1

Time Tracking:
Not Specified

Environment: Win32
Issue Links:
Duplicate
 
Relate
 

Planning Status: Unspecified


 Description  « Hide
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 substring(r.rdb$relation_name from 1 for 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;

SUBSTRING 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>
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 substring(r.rdb$relation_name from 1 for 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;

SUBSTRING 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
RDB$U <null> 8
RDB$V 1 3


 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Adriano dos Santos Fernandes added a comment - 27/Oct/11 11:54 PM
I'm fixing this only in 3.0 cause I'm not very sure of unintended regressions.

Dmitry Yemanov added a comment - 30/Oct/12 07:13 PM
This change seems to fix one more (probably related) bug:

with tab as
(
select 1 as p1
from rdb$relations
)
select f1.p1, f2.p1 as p2
from tab f1 cross join tab f2
group by f1.p1

Priorly, it was perfecty accepted, despite the fact that a non-aggregated field is selected. Now it throws the expected (I believe) error: "invalid expression in the select list".