wrong resultset (subquery + derived table + union)
--------------------------------------------------
Key: CORE-4107
URL: http://tracker.firebirdsql.org/browse/CORE-4107
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 2.5.2 Update 1
Reporter: Attila Molnár
Hi!
This is the select which produce wrong resultset
select 1 I1, NYTD.GYSOR GL_ANYK_NYOMTATVANY_TET__GYSOR, U.KULSO_KOD
UGYFEL__KULSO_KOD, PB.KDAT PU_BIZ__KDAT,
L.B_KELT PU_BIZ__KELT, L.B_TELJ PU_BIZ__TELJ,
L.B_BIZSZAM,
cast((select first 1 EL.BIZSZAM
from (select B1x.BIZSZAM
from PU_BIZ B2x
inner join PU_BIZKAPCS Kx on Kx.PBIZ_ID2 = B2x.ID
inner join PU_BIZ B1x on B1x.ID = Kx.PBIZ_ID1
where B2x.ID = pb.id
union
select B1.BIZSZAM
from PU_BIZ B2
inner join PU_BTET T2 on T2.PBIZ_ID = B2.ID
inner join PU_BIZKAPCS K on K.PBIZ_ID2 = T2.ID
inner join PU_BTET T1 on T1.ID = K.PBIZ_ID1
inner join PU_BIZ B1 on B1.ID = T1.PBIZ_ID
where B2.ID = pb.ID) EL
order by el.bizszam nulls last) as varchar(20)) KAPCS_BIZ,
L.B_HIVSZAM PU_BIZ__HIVSZAM,
L.B_ADOSZAM UGYFEL__ADOSZAM, L.B_CSASZ UGYFEL__CSASZ,
sum(L.T_ALAP) PU_BTET__ALAP, sum(L.T_ADO) PU_BTET__ADO
from PU_AFA_LISTA(114255076, null, 'T', null) L
left join PU_AFA_ARCHIV_65A A on ((A.PU_AFATET_ID = L.T_ID) or
(A.PU_AFA_ARCHIV_T_ID = L.T_ID))
left join GL_ANYK_TET T on T.ID = A.GL_ANYK_TET_ID
left join GL_ANYK_NYOMTATVANY_TET NYTD on NYTD.id = T.NYOMTATVANY_TET_ID
left join UGYFEL U on U.ID = L.B_UGYF_ID
left join PU_BIZ PB on PB.ID = L.B_PU_BIZ_ID
where L.T_SV = 'S' and
((NYTD.GYSOR <> 66) or (not exists(select first 1 1
from PU_AFA_LISTA(114255076, null, 'T',
't.id=' || L.T_ID) L2
left join PU_AFA_ARCHIV_65A A2 on
((A2.PU_AFATET_ID = L2.T_ID) or (A2.PU_AFA_ARCHIV_T_ID = L2.T_ID))
left join GL_ANYK_TET T2 on T2.ID =
A2.GL_ANYK_TET_ID
left join GL_ANYK_NYOMTATVANY_TET NYT2
on NYT2.ID = T2.NYOMTATVANY_TET_ID
where NYT2.GYSOR in (52, 77))))
--/*1. case : */ and pb.id in (114268418)--kapcs_biz value is NULL : this is OK
--/*2. case : */ and pb.id in (114258644)--kapcs_biz value is T612-17/2013 :
this is OK
--/*3. case : */ and pb.id in (114268418, 114258644)--kapcs_biz values are
T612-17/2013 in both line : this is WRONG. for 114268418 (b_bizszam=5918929) it
should be NULL
group by 1, 2, 3, 4, 5, 6, 7, 8, 9,10,11
The database is big (861,5 M - rar compressed). How can I send it?
--
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
------------------------------------------------------------------------------
Try New Relic Now & We'll Send You this Cool Shirt
New Relic is the only SaaS-based application performance monitoring service
that delivers powerful full stack analytics. Optimize and monitor your
browser, app, & servers with just a few lines of code. Try New Relic
and get this awesome Nerd Life shirt! http://p.sf.net/sfu/newrelic_d2d_may
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
