Multiple outer apply clauses don't return the right result?

Question

The following query should return all not-null values for column n.c because C is not nullable.

select  distinct A, B, a.C, n.C
from    o
        outer apply ( -- Get first C which Status = 1, if there is any
            select C 
            from o i where i.A = o.A and i.B = o.B and STATUS = 1 and ROWNUM = 1) a
        outer apply ( -- Get the latest C otherwise (ID is identity column)
            select C
            from o i where i.A = o.A and i.B = o.B and ROWNUM = 1
            order by ID desc) n

However, n.C will be null if a.C is null. And it will return the desired value if I remove the outer apply of a.

select  distinct A, B, n.C
from    o
        outer apply (
            select C
            from o i where i.A = o.A and i.B = o.B and ROWNUM = 1
            order by ID desc) n

Is it a bug of Oracle?

BTW, it works as expected if I switch two outer apply?

select  distinct A, B, a.C, n.C
from    o
        outer apply (
            select C
            from o i where i.A = o.A and i.B = o.B and ROWNUM = 1
            order by ID desc) n
        outer apply (
            select C 
            from o i where i.A = o.A and i.B = o.B and STATUS = 1 and ROWNUM = 1) a

Oracle version

Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0  Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

Test data

CN of 2, 2 is null:

with o(ID, A, B, C, Status) as (
    select 1, 1, 1, 1, 1 from dual union all
    select 2, 2, 2, 2, 0 from dual union all
    select 3, 2, 2, 2, 0 from dual
    )
select  distinct A, B, a.C Ca, n.C Cn
from    o
        outer apply ( -- Get first C which Status = 1, if there is any
            select C 
            from o i where i.A = o.A and i.B = o.B and STATUS = 1 and ROWNUM = 1) a
        outer apply ( -- Get the latest C otherwise (ID is identity column)
            select C
            from o i where i.A = o.A and i.B = o.B and ROWNUM = 1
            order by ID desc) n

returns

A   B   CA  CN
1   1   1   1
2   2   NULL    NULL

CN of 2, 2 is not null after moved outer apply (...) n:

with o(ID, A, B, C, Status) as (
    select 1, 1, 1, 1, 1 from dual union all
    select 2, 2, 2, 2, 0 from dual union all
    select 3, 2, 2, 2, 0 from dual
    )
select  distinct A, B, a.C Ca, n.C Cn
from    o
        outer apply ( -- Get the latest C otherwise (ID is identity column)
            select C
            from o i where i.A = o.A and i.B = o.B and ROWNUM = 1
            order by ID desc) n
        outer apply ( -- Get first C which Status = 1, if there is any
            select C 
            from o i where i.A = o.A and i.B = o.B and STATUS = 1 and ROWNUM = 1) a

returns

A   B   CA  CN
1   1   1   1
2   2   NULL    2

And the following query (which try to make ROWNUM more determind) still got the wrong result.

with o(ID, A, B, C, Status) as (
    select 1, 1, 1, 1, 1 from dual union all
    select 2, 2, 2, 2, 0 from dual union all
    select 3, 2, 2, 2, 0 from dual
    )
select  distinct A, B, a.C Ca, n.C Cn
from    o
        outer apply ( -- Get first C which Status = 1, if there is any
            select C 
            from o i where i.A = o.A and i.B = o.B and STATUS = 1 and ROWNUM = 1) a
        outer apply ( -- Get the latest C otherwise (ID is identity column)
            select * from (
                select C
                from o i where i.A = o.A and i.B = o.B 
                order by ID desc) x
            where ROWNUM = 1) n

Sample data, current and expected results might be useful. As might your exact Oracle version and patch level, just in case it is a known bug. Your `order by` looks suspicious, incidentally - it isn't doing anything, as it's applied after the `rownum` filter, so there is then only one row left to order. Which single row you get is indeterminate. Also, how can "if a.C is null" ever be true if "C is not nullable"? Or are there multiple tables involved? — Alex Poole, Dec 27 '18 at 18:10
Well, bug 20356733 might be relevant depending on your patch level, but I don't have an unpatched DB to try this against - or data to try to recreate anyway. But I still think the `order by` looks suspicious. You said that subquery should "Get the latest C" but as I said the one you actually get is indeterminate - not necessarily the one with the highest ID. You need [something like this](https://stackoverflow.com/a/3451577/266304) for it to be deterministic. (Again, without data I have no idea if that is actually your issue or just an observation...) — Alex Poole, Dec 27 '18 at 18:28
I've added some testable example at the end of the question. — ca9163d9, Dec 27 '18 at 18:39
I get the second result for both queries, on 12.2.0.1 and 12.1.0.2 - but I have a recent CPU applied, so bug 20356733 wouldn't affect me any more if it is that. If you're on an unpatched 12.1 install then you might be hitting that bug. If you have an Oracle contract you could patch or raise an SR to confirm first; but otherwise you're a bit stuck. — Alex Poole, Dec 27 '18 at 18:49
Possibly. The bug description is vague in My Oracle Support, but refers to outer apply and "Wrong results due to incorrect order of join evaluation", which could be affected by the order they appear in the query. But it might also only be an issue if there are other join types, and it might not be reliable - I guess it's down to how the optimiser handles it, which can change over time. The MOS note suggests a workaround of adding parentheses, not sure if that might help here. — Alex Poole, Dec 27 '18 at 19:01
If you want to get the first row and the ordering matters, then use a `fetch first` clause and not rownum. — William Robertson, Dec 28 '18 at 08:52

Sentinel · Answer 1 · 2018-12-27T20:09:48.077

In your second outer apply statement you limit the results to ROWNUM=1 but also have an order by clause, however, the ROWNUM is independent of the sort order. It represents order in which Oracle retrieved the rows from the DB, which may not correspond to the requested display order. By using ROWNUM=1 you've effectively eliminated your order by. Instead you will want to change your second outer apply use a generated row number based on your desired sort and filter criteria.

with o(ID, A, B, C, Status) as (
    select 1, 1, 1, 1, 1 from dual union all
    select 2, 2, 2, 2, 0 from dual union all
    select 3, 2, 2, 2, 0 from dual
    )
select  distinct A, B, a.C CA, n.C CN
from    o
        outer apply ( -- Get first C which Status = 1, if there is any
            select C 
            from o i where i.A = o.A and i.B = o.B and STATUS = 1 and ROWNUM = 1) a
        outer apply ( -- Get the latest C otherwise (ID is identity column)
            select C
            from (select o.*, row_number() over (partition by a, b order by id desc) rn from o) i 
            where i.A = o.A and i.B = o.B and rn = 1
            order by ID desc) n

Note, however, that with ID=2 the record identified as RN=1 is indeterminate, it could be either the first or second record since the only sort criteria is ID. Not that it matters with your sample data as both records are identical, however, if there were differences in C, the result could be unpredictable.

A simpler approach that returns the same data might be to use this query instead which completely avoids the whole outer apply issue:

select a, b
     , max(case status when 1 then C end) keep (DENSE_RANK FIRST ORDER BY status desc, rownum) CA
     , max(c) keep (DENSE_RANK LAST ORDER by id) CN
  from o
 group by a,b;

The results of both queries are the same:

A   B   CA  CN
1   1   1   1
2   2       2

The ID of the third row should be 3. I've updated the question. Your code still got null for `n.c` on my oracle server. It may be a bug of Oracle according to the comments of the questions. — ca9163d9, Dec 27 '18 at 19:46
I've updated my answer based on the new data, however, it's not clear from your question what your expected result is. — Sentinel, Dec 27 '18 at 20:06
I'm not familiar with Oracle's `sum() keep` part. I replaced one of the `outer apply` with `first_value(C) over (partition by A, B order by ID desc)` and it seems working. I will also need to get some other columns from `a`. — ca9163d9, Dec 27 '18 at 20:06
`first_value()` is an analytic function where as the `keep` clause provides similar functionality for aggregate functions, by the way my use of `sum` was a typo that I've since corrected, though as long as the `keep` clause identifies only one record to keep, then the choice between `sum`, `min` and `max` is relatively arbitrary. — Sentinel, Dec 27 '18 at 20:14

Multiple outer apply clauses don't return the right result?

1 Answers1