Bad alias table choice joining CTE
----------------------------------
Key: CORE-3829
URL: http://tracker.firebirdsql.org/browse/CORE-3829
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 2.5.1
Environment: Windows and LInux
Reporter: Umberto Masotti
Following query incorrectly report sum (t1.qq) in column sum(t2.qq) when in CTE
corresponding field is result of count(*), count(1) or sum(1).
Workaround is in CTE sum(iif(<anycondition>, 1, 1)).
Don't know if already solved, as found nothing related in tracker.
I'll upload test database.
with totalk (kk1, variant, tt, qq, mm, ff, f1, f2, f3, f4, f5) as
(select ll.kk1, ll.variant,
sum (iif(ll.selector_y_n='Y', 1, 0)),
count(*), -- <<<< problem here
-- count(1), -- <<<< this doesn't work either
-- sum(1), -- <<<<< this neither
-- sum(iif(ll.variant=1,1,1)), -- <<<< workaround
sum (iif(ll.selector_m_f='M' and ll.selector_y_n='Y', 1, 0)),
sum (iif(ll.selector_m_f='F' and ll.selector_y_n='Y', 1, 0)),
sum(iif(ll.selector_1_5='1' and ll.selector_y_n='Y', 1, 0)),
sum(iif(ll.selector_1_5='2' and ll.selector_y_n='Y', 1, 0)),
sum(iif(ll.selector_1_5='3' and ll.selector_y_n='Y', 1, 0)),
sum(iif(ll.selector_1_5='4' and ll.selector_y_n='Y', 1, 0)),
sum(iif(ll.selector_1_5='5' and ll.selector_y_n='Y', 1, 0))
from testcte ll
group by 1, 2 )
select
ff.kk1, ff.descrkk,
sum(t1.tt) "TT 1",
sum(t2.tt) "TT 2",
sum(t1.qq) "QQ 1", -- <<<< check this value
sum(t2.qq) "QQ 2", -- <<<< and this value
sum(t1.mm) "MM 1",
sum(t2.mm) "MM 2",
sum(t1.ff) "FF 1",
sum(t2.ff) "FF 2",
sum(t1.f1) "G1 1",
sum(t2.f1) "G1 2",
sum(t1.f2) "G2 1",
sum(t2.f2) "G2 2",
sum(t1.f3) "G3 1",
sum(t2.f3) "G3 2",
sum(t1.f4) "G4 1",
sum(t2.f4) "G4 2",
sum(t1.f5) "G5 1",
sum(t2.f5) "G5 2"
from testmain ff left outer join
totalk t1 on t1.kk1=ff.kk1 and t1.variant = 1
left outer join
totalk t2 on t2.kk1=ff.kk1 and t2.variant = 2
group by 1, 2;
--
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
------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
