I have a list of employees working in different departments.
create table emp
(
emp_name varchar(15) primary key,
birthdate date not null,
dept_id integer not null
);
insert into emp
values ('Erna', '1969-01-17', 1),
('Peter', '1970-02-13', 1),
('Urs', '1998-11-07', 1),
('Ulli', '1969-04-15', 1),
('Andreas', '2004-06-23', 2),
('Vera', '1995-12-02', 2),
('Herta', '2006-03-03', 3),
('Django', '1997-10-09', 3);
Now I want a list of them in the order they are having birthday each year, including the following person who is next to have birthday. Since the last person in the year has no successor, the first person should be named. All this works by department.
with cte1 as
(
select
emp_name, dept_id, birthdate,
count(*) over abt as anz_in_abt,
coalesce(lead(emp_name) over abt,
first_value(emp_name) over abt) as ausr,
coalesce(lead(birthdate) over abt,
first_value(birthdate) over abt) as bd_ausr
from
emp
window abt as (
partition by dept_id
order by month(birthdate), day(birthdate)
-- rows between unbounded preceding and unbounded following
)
)
select *
from cte1
order by dept_id, month(birthdate), day(birthdate);
SQL Server doesn't like the "rows between" clause, so I commented it out. In PostgreSQL and SQLite it works with or without this clause, but SQL Server doesn't show the first person as the successor of the last one in the year for the first two departments, but NULL values instead.
emp_name|dept_id|birthdate |anz_in_abt|ausr |bd_ausr |
--------+-------+----------+----------+------+----------+
Erna | 1|1969-01-17| 1|Peter |1970-02-13|
Peter | 1|1970-02-13| 2|Ulli |1969-04-15|
Ulli | 1|1969-04-15| 3|Urs |1998-11-07|
Urs | 1|1998-11-07| 4| | |
Andreas | 2|2004-06-23| 1|Vera |1995-12-02|
Vera | 2|1995-12-02| 2| | |
Herta | 3|2006-03-03| 1|Django|1997-10-09|
Django | 3|1997-10-09| 2|Herta |2006-03-03|
Since the syntax for extracting day and month from a date is different in PostgreSQL, I added two functions to make SQL Server's code work.
create or replace function month(d date) returns smallint
begin atomic
select extract(month from d);
end;
create or replace function year(d date) returns smallint
begin atomic
select extract(year from d);
end;
SQLite extracts day and month with strftime(), therefore the code is not exactly the same.
with cte1 as (
select emp_name, dept_id, birthdate, count(*) over abt as anz_in_abt,
coalesce(lead(emp_name) over abt, first_value(emp_name) over abt) as ausr,
coalesce(lead(birthdate) over abt, first_value(birthdate) over abt) as bd_ausr
from emp
window abt as (
partition by dept_id
order by strftime('%m', birthdate), strftime('%d', birthdate)
rows between unbounded preceding and unbounded following
)
)
select *
from cte1
order by dept_id, strftime('%m', birthdate), strftime('%d', birthdate);
The correct result given by PostgreSQL and SQLite is
emp_name | dept_id | birthdate | anz_in_abt | ausr | bd_ausr
----------+---------+------------+------------+---------+------------
Erna | 1 | 1969-01-17 | 1 | Peter | 1970-02-13
Peter | 1 | 1970-02-13 | 2 | Ulli | 1969-04-15
Ulli | 1 | 1969-04-15 | 3 | Urs | 1998-11-07
Urs | 1 | 1998-11-07 | 4 | Erna | 1969-01-17
Andreas | 2 | 2004-06-23 | 1 | Vera | 1995-12-02
Vera | 2 | 1995-12-02 | 2 | Andreas | 2004-06-23
Herta | 3 | 2006-03-03 | 1 | Django | 1997-10-09
Django | 3 | 1997-10-09 | 2 | Herta | 2006-03-03
So, is this a bug in SQL Server 2022?
Yes, is it a bug. You should report it.
To workaround it, don't use COALESCE/CASE in the same scope as the WINDOW clause, don't use a WINDOW clause, or use ISNULL.