Skip to content
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

Closed
firebird-automations opened this issue May 27, 2013 · 16 comments
Closed

Comments

@firebird-automations
Copy link
Collaborator

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

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: Attila Molnár (e_pluribus_unum)

Simplified select.
group by problem.

@firebird-automations
Copy link
Collaborator Author

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,
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 = 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,
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 http://T.ID = A.GL_ANYK_TET_ID
left join GL_ANYK_NYOMTATVANY_TET NYTD on http://NYTD.id = T.NYOMTATVANY_TET_ID
left join UGYFEL U on http://U.ID = L.B_UGYF_ID
left join PU_BIZ PB on http://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 http://T2.ID = A2.GL_ANYK_TET_ID
left join GL_ANYK_NYOMTATVANY_TET NYT2 on http://NYT2.ID = T2.NYOMTATVANY_TET_ID
where NYT2.GYSOR in (52, 77))))

--/*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 : */ and 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 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?

=>

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 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 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

An artificial test case:

select T.VAL1,
(
select 'something' from rdb$database where 2 = http://T.ID
union
select null from rdb$database where 1 = 0
) as VAL2
from (
select 1 as VAL1, 1 as ID from rdb$database
union all
select 2 as VAL1, 2 as ID from rdb$database
) as T
group by 1
-- produces {1, 'something'}, {2, 'something'} instead of {1, null}, {2, 'something'}
-- expected result is an error

select T.VAL1,
min((
select 'something' from rdb$database where 2 = http://T.ID
union
select null from rdb$database where 1 = 0
)) as VAL2
from (
select 1 as VAL1, 1 as ID from rdb$database
union all
select 2 as VAL1, 2 as ID from rdb$database
) as T
group by 1
-- works correctly

@firebird-automations
Copy link
Collaborator Author

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,
(
select 'something' from rdb$database where 2 = http://T.ID
union
select null from rdb$database where 1 = 0
) as VAL2
from (
select 1 as VAL1, 1 as ID from rdb$database
union all
select 2 as VAL1, 2 as ID from rdb$database
) as T

yields: \{1, null\} \{2, "Something"\}

The expected result

but,

select T.VAL1,
(
select 'something' from rdb$database where 2 = http://T.ID
union
select null from rdb$database where 1 = 0
) as VAL2
from (
select 1 as VAL1, 1 as ID from rdb$database
union all
select 2 as VAL1, 2 as ID from rdb$database
) as T
GROUP BY 1, 2

 yields: \{1, "Something"\} \{2, "Something"\}

which is not expected

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: In Progress [ 3 ] => Open [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 2.5.3 [ 10461 ]

Fix Version: 2.1.6 [ 10460 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 RC 1 [ 10584 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment