Unexpected results when using FIRST_VALUE() in SQL Server 2012

Question

When I use FIRST_VALUE on a data set that I construct by hand I get one result, and when I use it on a data set that results from a left join, I get a different result - even though the data sets appear to me to contain the exact same data values. I've reproduced the issue with a simple data set below.

Can someone tell me if I have misunderstood something?

This SQL produces the expected result, that FIRST_VALUE is NULL and LAST_VALUE is 30.

SELECT
  agroup,
  aval,
  FIRST_VALUE(aval) OVER (PARTITION BY agroup ORDER BY aval ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) fv,
  LAST_VALUE(aval) OVER (PARTITION BY agroup ORDER BY aval ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) lv
FROM
(
  SELECT 1 agroup, 10 aval
  UNION ALL SELECT 1, NULL
  UNION ALL SELECT 1, 30
) T

This SQL uses a LEFT JOIN that results in the same data set as above, but FIRST_VALUE appears to ignore the NULL.

SELECT 
  agroup,
  aval,
  FIRST_VALUE(aval) OVER (PARTITION BY agroup ORDER BY aval ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) fv,
  LAST_VALUE(aval) OVER (PARTITION BY agroup ORDER BY aval ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) lv
FROM
(
  SELECT 
    T1.agroup,
    T1.akey,
    T2.aval 
  FROM 
  (
    SELECT 1 agroup, 1 akey
    UNION ALL SELECT 1, 2
    UNION ALL SELECT 1, 3
  ) T1
  LEFT JOIN
  (
    SELECT 1 akey, 10 aval
    UNION ALL SELECT 3,30
  ) T2 ON T1.akey = T2.akey
) T

I can also show that the left join behavior is different when using a table variable vs. a CTE. When using a CTE to generate the data, FIRST_VALUE ignores the NULL. Using the exact same SQL but putting the results in a table variable or a temporary table results in the NULL being taken into account.

With a CTE the SQL Server results don't include NULL in the FIRST_VALUE determination:

WITH T AS
(
  SELECT 
    T1.agroup,
    T1.akey,
    T2.aval 
  FROM 
  (
    SELECT 1 agroup, 1 akey
    UNION ALL SELECT 1, 2
    UNION ALL SELECT 1, 3
  ) T1
  LEFT JOIN
  (
    SELECT 1 akey, 10 aval
    UNION ALL SELECT 3,30
  ) T2 ON T1.akey = T2.akey
)

SELECT 
  agroup,
  aval,
  FIRST_VALUE(aval) OVER (PARTITION BY agroup ORDER BY aval ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) fv,
  LAST_VALUE(aval) OVER (PARTITION BY agroup ORDER BY aval ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) lv
FROM
 T

But with a table variable, it does:

DECLARE @T TABLE (agroup INT,akey INT,aval INT)

INSERT INTO
  @T
SELECT 
  T1.agroup,
  T1.akey,
  T2.aval 
FROM 
(
  SELECT 1 agroup, 1 akey
  UNION ALL SELECT 1, 2
  UNION ALL SELECT 1, 3
) T1
LEFT JOIN
(
  SELECT 1 akey, 10 aval
  UNION ALL SELECT 3,30
) T2 ON T1.akey = T2.akey


SELECT 
agroup,
aval,
FIRST_VALUE(aval) OVER (PARTITION BY agroup ORDER BY aval ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) fv,
LAST_VALUE(aval) OVER (PARTITION BY agroup ORDER BY aval ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) lv
FROM
@T

The [SQLfiddle](http://sqlfiddle.com/#!6/d41d8/6873) agrees with you. — ypercubeᵀᴹ, Sep 11 '13 at 22:58
ypercube, is there any way this is correct/expected behavior? I couldn't find anything about this online, but surely I'm not the first person in the world to use FIRST_VALUE against a CTE with a left join in it. — Salah at Soap Hope, Sep 11 '13 at 23:21
Looks like a bug and I see some Connect items regarding `FIRST_VALUE()` and `LAST_VALUE()` but not anything related to `ORDER BY` and nulls. What is the exact version of SQL-Server you use? Have you done all the updates? — ypercubeᵀᴹ, Sep 11 '13 at 23:23
@ypercube, I am using SQL Server 2012 Express. To be honest, I don't know if all the updates have been done or even how to tell; I'm not an expert, and just do/learn enough for our little company's internal management application to work. I will research the server installation and find out if there are any updates I should install. I did run the same query using the SQLfiddle setup you made, but using PostgreSQL, and I got the expected results, so to me it looks like a possible bug. Is there somewhere I should report this? — Salah at Soap Hope, Sep 11 '13 at 23:29
Yes, the Connect site. See a similar [Connect item, regarding LAST_VALUE() and CTEs](http://connect.microsoft.com/SQLServer/feedback/details/712918/last-value-returns-current-row-when-used-inside-a-cte) — ypercubeᵀᴹ, Sep 11 '13 at 23:33
You can run `SELECT @@version;` to see all the details about the version installed. You'll see something like `Microsoft SQL Server 2012 - 11.0.2100.60 ...` — ypercubeᵀᴹ, Sep 11 '13 at 23:33
(the above is the 2012 RM version.) If you have `11.0.3000` or higher, you have SP1. See [Microsoft SQL Server Builds](http://sqlserverbuilds.blogspot.gr/) — ypercubeᵀᴹ, Sep 11 '13 at 23:42
I see you have opened a new connect item about this. [link for reference](https://connect.microsoft.com/SQLServer/feedback/details/800316/unexpected-results-when-using-first-value-against-a-cte-or-subquery-with-a-left-join-that-produces-nulls) — Martin Smith, Sep 12 '13 at 12:25
I agree with ypercube and MartinSmith, this is is very likely that rarest of inquiries, a newly discovered bug in the product. Excellent work, +1. — RBarryYoung, Sep 12 '13 at 16:02
I got a notice today from Microsoft that this bug will not be fixed, with no further comment. This means that SQL Server produces absolutely incorrect results when using the LAST_VALUE operator. Honestly I am kind of shocked that their development team can live with this - they now know that their supposed top-tier database server produces completely wrong results for a perfectly valid query, but choose not to fix it or even issue a warning about it. Hopefully no one uses this function for anything mission critical. — Salah at Soap Hope, Oct 15 '14 at 15:23
This is a NASTY bug. It looks like Microsoft just patched something in SQL Azure around Nov 13th 2016, with no warning, and no indication of what changed anywhere, that completely altered the behavior of one of our queries in production. — Triynko, Nov 27 '16 at 18:27

score 7 · Accepted Answer · edited Jun 20 '20 at 09:12

The provided examples show very clearly that there is an inconsistency in the implementation of the FIRST_VALUE() analytic function.

Depending on whether the underlying table in the FROM clause is a base table (or temporary or a table variable or even a derived table created on the fly) in one case and a derived table (or cte) created by the LEFT JOIN of two created on the fly tables in the second case, the results are different. Seems like the NULL values are ignored in the 2nd case or treated as high values.

And they shouldn't be different, because the result of a SQL query should not depend on how the FROM clause gets the values of the table it provides to the SELECT clause and also because documentation of the OVER clause clearly states how NULL values should be treated:

order_by_expression

Specifies a column or expression on which to sort. order_by_expression can only refer to columns made available by the FROM clause. An integer cannot be specified to represent a column name or alias.

...

ASC | DESC

Specifies that the values in the specified column should be sorted in ascending or descending order. ASC is the default sort order. Null values are treated as the lowest possible values.

So, the correct results - according to the SQL-Server documentation - are the ones that do not ignore NULL values. Any other result should not happen and since it does happen, it is a bug.

I suggested you test in the most recent version (and not only in the RTM) as it may have been identified and corrected in some service pack or update and if it's still there (or if you don't have a newrer version available) to submit this as a bug in the Connect site.

Update

For future reference, the bug was submitted by the OP. The link is: Connect item and (our) @Aaron Bertrand has commented there that it also appears in most current SQL 2014 builds.

@GSerg, I was pretty disappointed when Microsoft declined to fix this bug, since it means that SQL Server produces known incorrect output when used correctly. Hopefully no one is using this feature for mission critical results. Even if the fix is too difficult, the system could at least emit a warning that the output from the query is known to be incorrect. — Salah at Soap Hope, Apr 10 '15 at 21:24
This may have been fixed very, very recently, like Nov 13th, 2016. One of our queries in production in SQL Azure suddenly started yielding NULLs from the FIRST_VALUE function where it didn't before. Upon close inspection, the yielding of nulls was actually the correct behavior, so I had to update the partition by clause to exclude null values with a case statement. Like the OP suggests, I cannot produce the problem on small, manual data sets, and it seems to manifest itself based on how the data is generated and what value you're selecting from FIRST_VALUE. — Triynko, Nov 27 '16 at 18:31

score -1 · Answer 2 · answered Apr 10 '15 at 04:23

-1

A bit of a late answer to this post, but one to share nevertheless.

You can use the order by flag to "demote" the null values.

So in your case ... you can use

... FIRST_VALUE(aval) OVER (PARTITION BY agroup ORDER BY (iif(aval is null, 1,0)), aval ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) fv ...

(Note that I use the value 1 for null values as it should sort the field ascending, so the non-null values will take precedence)

Cheers - LA.

answered Apr 10 '15 at 04:23

user4772103

1

"Demoting" nulls is erroneous behaviour to begin with. The correct behaviour is to expose nulls, but because of a bug SQL Server doesn't do this in certain situations. Your query will hide nulls, and in an arguably bad way that will prevent correct use of indices if I'm not mistaken. – GSerg Apr 10 '15 at 10:38
Yeah, it's not only dependent on how the data is generated in the from, but also on what field you're selecting in the FIRST_VALUE function. For example, given a data set partitioned by some category, if you order by field C in that category such that null values in field A should come first and should be selected, SQL server will instead choose the first non-null value in field A, but only if the from clause is a join or CTE. Likewise, if you choose field B with similar properties, it will again fail to produce null from the correct row and will instead choose the first non-null value. – Triynko Nov 27 '16 at 18:34

Unexpected results when using FIRST_VALUE() in SQL Server 2012

2 Answers2