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

[firebird-automations]

Submitted by: arni (arni)

Is duplicated by CORE3829
Is related to QA490

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

Commits: 2da8379 9ae117e

[firebird-automations]

Modified by: arni (arni)

description: 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

=>

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

[firebird-automations]

Modified by: @asfernandes

Version: 3.0 Initial [ 10301 ]

assignee: Adriano dos Santos Fernandes [ asfernandes ]

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

[firebird-automations]

Commented by: @asfernandes

I'm fixing this only in 3.0 cause I'm not very sure of unintended regressions.

[firebird-automations]

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

[firebird-automations]

Modified by: Sean Leyne (seanleyne)

Link: This issue is duplicated by CORE3829 [ CORE3829 ]

[firebird-automations]

Modified by: @asfernandes

Version: 2.5.1 [ 10333 ]

Fix Version: 2.5.2 [ 10450 ]

[firebird-automations]

Commented by: @dyemanov

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".

[firebird-automations]

Modified by: @pcisar

Link: This issue is related to QA490 [ QA490 ]

[firebird-automations]

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done successfully

[firebird-automations]

Commented by: @pcisar

Test created.

[firebird-automations]

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]