create table t1(dt datetime);
|
insert into t1 values ('2017-05-17'), ('2017-05-18');
|
create view v1 as select dt, lead(dt) over (order by dt) from t1;
|
create view v2 as select dt, ifnull(lead(dt) over (order by dt), '9999-12-31 12:00:00') from t1;
|
|
select * from v1;
|
+---------------------+-----------------------------+
|
| dt | lead(dt) over (order by dt) |
|
+---------------------+-----------------------------+
|
| 2017-05-17 00:00:00 | 2017-05-18 00:00:00 |
|
| 2017-05-18 00:00:00 | NULL |
|
+---------------------+-----------------------------+
|
2 rows in set (0.00 sec)
|
|
select * from v2;
|
ERROR 1356 (HY000): View 'genesis.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
|
|
select dt, ifnull(lead(dt) over (order by dt), '9999-12-31 12:00:00') from t1;
|
+---------------------+------------------------------------------------------------+
|
| dt | ifnull(lead(dt) over (order by dt), '9999-12-31 12:00:00') |
|
+---------------------+------------------------------------------------------------+
|
| 2017-05-17 00:00:00 | 2017-05-18 00:00:00 |
|
| 2017-05-18 00:00:00 | 9999-12-31 12:00:00 |
|
+---------------------+------------------------------------------------------------+
|
2 rows in set (0.00 sec)
|
|