Why an aggregation function doesn't work as expected in an apply block

June 1, 2022

WITH
    a (v)
    AS
        (    SELECT LEVEL
               FROM DUAL
         CONNECT BY LEVEL < 5),
    b (v)
    AS
        (    SELECT LEVEL
               FROM DUAL
         CONNECT BY LEVEL < 5)
SELECT a.v
  FROM a
       CROSS APPLY (SELECT MIN (b.v)
                      FROM b
                     WHERE a.v = b.v) bb

result 1,2,3,4
but expected result would be 1,1,1,1.

code

>Solution :

1,2,3,4 is the correct output.

In the outer query you SELECT a.v which will select the v value from table a CROSS JOIN with the single row aggregation generated by the CROSS APPLY.

For your code, it doesn’t really matter what the output is from the bb sub-query in the CROSS APPLY as you are not outputting the value from bb and the aggregation forces the output to be a single row.

If you want to get the value from the bb sub-query then use:

WITH a (v) AS (
  SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 5
),
b (v) AS (
  SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 5
)
SELECT a.v, bb.min_v
  FROM a
       CROSS APPLY (
         SELECT MIN (b.v) As min_v
         FROM   b
         WHERE  a.v = b.v
       ) bb

Then the output is:

V MIN_V

1 1

2 2

3 3

4 4

V	MIN_V
1	1
2	2
3	3
4	4

If you want all 1s then SELECT the value from bb and use b.v <= a.v as the filter:

WITH a (v) AS (
  SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 5
),
b (v) AS (
  SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 5
)
SELECT a.v, bb.min_v
  FROM a
       CROSS APPLY (
         SELECT MIN (b.v) As min_v
         FROM   b
         WHERE  b.v <= a.v
       ) bb

which outputs: