How do I use IFNULL with window function in MySQL

How do I use ifnull with a window function?

Let's say I have this query result below, lag1 is just the budget column shifted down using the window function, because of that, the value is null. But I want to replace that null with a zero so I can calculate the difference between budget and lag1.

select id, budget,
    lag(budget) over (order by id) as lag1
    from projects;

+----+---------+---------+
| id | budget  | lag1    |
+----+---------+---------+    
|  1 | 1000000 |    NULL |  
|  2 |  100000 | 1000000 |  
|  3 |     100 |  100000 |
+----+---------+---------+

I tried the following two examples but it doesn't work:

select id, budget,
ifnull(lag(budget),0) over (order by id) as lag1
from projects;

select id, budget,
ifnull((lag(budget) over (order by id) as lag1),0)
from projects;

I guess your mysql version lower than 8.0 because your second solution had been worked.

select id, budget, ifnull(lag(budget) over (order by id),0) as lag1
from projects;

sqlfiddle

if you mysql version lower than 8.0, you need to write a subquery in select to get before row data by ID

TESTDDL

CREATE TABLE projects(
  ID INT,
   budget INT
);
INSERT INTO projects VALUES (1,1000000);
INSERT INTO projects VALUES (2,100000);
INSERT INTO projects VALUES (3,100);

Query

SELECT *,IFNULL((select budget FROM projects t1 WHERE t.id > t1.id order by t1.id desc limit 1 ) ,0) lag1
FROM projects t

[Results]:

| ID |  budget |    lag1 |
|----|---------|---------|
|  1 | 1000000 |       0 |
|  2 |  100000 | 1000000 |
|  3 |     100 |  100000 |

sqlfiddle