New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
wrong resultset (subquery + derived table + union) [CORE4107] #4435
Comments
Commented by: @dyemanov Upload to any file-sharing web service (e.g. http://ge.tt) and post a link. Encrypt the file before sharing if it contains any business-critical data and send the key/password via email. |
Commented by: Sean Leyne (seanleyne) This query is a MESS! How about you strip down the query into a more consice issue -- drop tables/joins which are not crucial to the issue. That would also allow for the sample database size to be reduced. |
Commented by: Attila Molnár (e_pluribus_unum) Simplified select. |
Modified by: Attila Molnár (e_pluribus_unum)description: 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, --/*1. case : */ and http://pb.id in (114268418)--kapcs_biz value is NULL : this is OK The database is big (861,5 M - rar compressed). How can I send it? => Hi! This is the select which produce wrong resultset select L.B_BIZSZAM, Without "group by" the resultset is correct. database : ftp://ftp.mve.hu |
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Modified by: @dyemanovstatus: Open [ 1 ] => In Progress [ 3 ] |
Commented by: @dyemanov I'm wondering whether this select should be accepted at all. I'd expect to see error - "Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)", as the non-aggregated item (subquery) references only http://PB.ID and does not contain any aggregating function. After enclosing the second select item into MIN() the query produces a correct result. So IMHO it's more about missing error rather than about wrong result set. |
Modified by: @dyemanovVersion: 2.1.5 Update 1 [ 10522 ] Version: 2.5.2 [ 10450 ] Version: 2.1.5 [ 10420 ] Version: 2.0.7 [ 10390 ] Version: 2.5.1 [ 10333 ] Version: 2.1.4 [ 10361 ] Version: 2.5.0 [ 10221 ] Version: 2.0.6 [ 10303 ] Version: 3.0 Initial [ 10301 ] Version: 2.1.3 [ 10302 ] Version: 2.1.2 [ 10270 ] Version: 2.0.5 [ 10222 ] Version: 2.1.1 [ 10223 ] Version: 2.1.0 [ 10041 ] |
Commented by: @dyemanov An artificial test case: select T.VAL1, select T.VAL1, |
Commented by: Sean Leyne (seanleyne) Should the query be rejected? Good question. I would say it should. What I find interesting is that : select T.VAL1,
but, select T.VAL1,
which is not expected |
Commented by: @dyemanov Yes, the same as for the original query. The problem is in the aggregation handling, so without GROUP BY everything works fine. |
Modified by: @dyemanovstatus: In Progress [ 3 ] => Open [ 1 ] |
Modified by: @dyemanovstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 3.0 RC 1 [ 10584 ] |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Closed [ 6 ] |
Submitted by: Attila Molnár (e_pluribus_unum)
Votes: 1
Hi!
This is the select which produce wrong resultset
select 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 = http://B2x.ID
inner join PU_BIZ B1x on http://B1x.ID = Kx.PBIZ_ID1
where http://B2x.ID = http://pb.id
union
select B1.BIZSZAM
from PU_BIZ B2
inner join PU_BTET T2 on T2.PBIZ_ID = http://B2.ID
inner join PU_BIZKAPCS K on K.PBIZ_ID2 = http://T2.ID
inner join PU_BTET T1 on http://T1.ID = K.PBIZ_ID1
inner join PU_BIZ B1 on http://B1.ID = T1.PBIZ_ID
where http://B2.ID = http://pb.ID) EL
order by el.bizszam nulls last) as varchar(20)) KAPCS_BIZ
from PU_AFA_LISTA(114255076, null, 'T', null) L
left join PU_BIZ PB on http://PB.ID = L.B_PU_BIZ_ID
where
--/*1. case : */ and http://pb.id in (114268418)--kapcs_biz value is NULL : this is OK
--/*2. case : */ and http://pb.id in (114258644)--kapcs_biz value is T612-17/2013 : this is OK
--/*3. case : */ http://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 L.B_BIZSZAM
Without "group by" the resultset is correct.
database : ftp://ftp.mve.hu
Commits: 7f12f4e bc15fe2 92bc35d FirebirdSQL/fbt-repository@46d3699 FirebirdSQL/fbt-repository@99b1e08 FirebirdSQL/fbt-repository@69591a7
The text was updated successfully, but these errors were encountered: