Issue Details (XML | Word | Printable)

Key: CORE-4107
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Attila Molnár
Votes: 1
Watchers: 3
Operations

If you were logged in you would be able to see more operations.
Firebird Core

wrong resultset (subquery + derived table + union)

Created: 27/May/13 03:04 PM   Updated: 14/Jul/15 06:09 AM
Component/s: Engine
Affects Version/s: 2.1.0, 2.1.1, 2.0.5, 2.1.2, 2.1.3, 3.0 Initial, 2.0.6, 2.5.0, 2.1.4, 2.5.1, 2.0.7, 2.1.5, 2.5.2, 2.1.5 Update 1, 2.5.2 Update 1
Fix Version/s: 2.1.6, 2.5.3, 3.0 RC1

QA Status: Done successfully


 Description  « Hide
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 = 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
from PU_AFA_LISTA(114255076, null, 'T', null) L
 left join PU_BIZ PB on PB.ID = L.B_PU_BIZ_ID
where
--/*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 : */ 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

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 27/May/13 03:46 PM
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.

Sean Leyne added a comment - 27/May/13 04:47 PM
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.

Attila Molnár added a comment - 28/May/13 06:57 AM
Simplified select.
group by problem.

Dmitry Yemanov added a comment - 28/May/13 05:52 PM
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 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.

Dmitry Yemanov added a comment - 28/May/13 05:56 PM
An artificial test case:

select T.VAL1,
  (
    select 'something' from rdb$database where 2 = 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 = 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

Sean Leyne added a comment - 28/May/13 06:06 PM - edited
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 = 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 = 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

Dmitry Yemanov added a comment - 28/May/13 06:13 PM
Yes, the same as for the original query. The problem is in the aggregation handling, so without GROUP BY everything works fine.