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