Why does over() not return null

Question

If over() means it ranges over the entire result set, why it won't return null in "max(column) over()".

Is the reason about something like "null considered as int" concept?

I have tried find official oracle document. Failed.

Tried google other discussion to get some clue. Failed.

Tried test myself with result from adding different arguments to over() like max(c) over(partition by c), max(c) over(order by c) to figure out the mechanism. Failed

[All aggregate functions except COUNT(*), GROUPING, and GROUPING_ID ignore nulls](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Aggregate-Functions.html#GUID-62BE676B-AF18-4E63-BD14-25206FEA0848) — astentx, Jun 07 '23 at 07:49
It doesn'make sense max(c) over(partition by c). Normally if you want max(c), that has to be partitioned over some other column(s) than c. — Bogdan Dincescu, Jun 07 '23 at 07:50
Do you have some sample data and a runnable query with what you expected and what actual results you're getting? Because I'm really not sure what behaviour you're trying to ask about here is. — Damien_The_Unbeliever, Jun 07 '23 at 08:07
My bad. I used my phone to ask here. That's why I didn't put any data sample. MT0 got my point. Thanks — Daniel, Jun 14 '23 at 16:20

MT0 · Accepted Answer · 2023-06-07T08:29:45.307

This is in the Oracle documentation for Aggregate Functions:

All aggregate functions except COUNT(*), GROUPING, and GROUPING_ID ignore nulls. You can use the NVL function in the argument to an aggregate function to substitute a value for a null. COUNT and REGR_COUNT never return null, but return either a number or zero. For all the remaining aggregate functions, if the data set contains no rows, or contains only rows with nulls as arguments to the aggregate function, then the function returns null.

When you look at the documentation for Analytic Functions it states:

Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group.

So an analytic function will follow exactly the same rules for null values as its equivalent aggregate version (and will ignore null values except in those documented cases); and will applying the aggregation over a window and return multiple rows.

For example, if you have the sample data:

CREATE TABLE table_name (
  id          NUMBER GENERATED ALWAYS AS IDENTITY,
  column_name NUMBER
);
INSERT INTO table_name (column_name) VALUES (NULL);
INSERT INTO table_name (column_name) VALUES (NULL);

Then:

SELECT id, MAX(column_name) OVER () FROM table_name;

Outputs:

ID	MAX(COLUMN_NAME)OVER()
1	null
2	null

Then it outputs null as the data-set contains only rows with nulls as arguments (exactly as per the documentation).

If you add a non-null row:

INSERT INTO table_name (column_name) VALUES (42);

Then:

SELECT id, MAX(column_name) OVER () FROM table_name;

Outputs:

ID	MAX(COLUMN_NAME)OVER()
1	42
2	42
3	42

Again, exactly as documented, the null values are ignored and the maximum of the non-null rows is returned.

fiddle

I need to take time to read this, but I think you got what I want according your data sample. I should feed back on weekend. Thanks first — Daniel, Jun 08 '23 at 08:48
Sorry for unexpected delay. I thought there is a Subject or page "over()", and I didn't know about aggregate/analytic function. I learn tons of proper nouns. Thank you — Daniel, Jun 14 '23 at 16:15

score 0 · Answer 2 · answered Jun 07 '23 at 08:04

I do not really understand what your expectation is as to the usage of null in over(partition by ...). Below you may see an example of how partition by works when the column on which you partition has null values:

with t_data (code, val) as (
  select 'A', 13 from dual union all
  select 'A', 8 from dual union all
  select 'A', 0 from dual union all
  select 'B', 121 from dual union all 
  select 'B', 441 from dual union all 
  select '', 7 from dual union all 
  select '', 21 from dual

) select code, val, max(val) over (partition by code) from t_data;

The results should be clear enough. If this is not satisfactory, please explain what you wish to achieve giving a sample of data and the expected results.

Why does over() not return null

2 Answers2