IGNORE NULLS and RESPECT NULLS not recognized for LAG and LEAD

HugoKornelisHugoKornelis Posts: 40 Bronze 5
in SQL Prompt
Hey all!

Today I noticed that SQL Prompt seems to be blissfully unaware of the new IGNORE NULLS and RESPECT NULLS optional keywords for the LAG and LEAD functions. However, when used with FIRST_VALUE or LAST_VALUE, they are recongized just fine.
(In your defense, Management Studio itself bothces this up with both LAG and LEAD, as well as FIRST_VALUE and LAST_VALUE, so you still win ;) )

Repro example, on e.g. AdventureWorks2017 demo database.

Start with this query:
SELECT p.ProductID,<br>       p.Name,<br>       p.ListPrice,<br>       p.ProductLine,<br>       LEAD (p.ListPrice, 2) OVER (PARTITION BY p.ProductLine ORDER BY p.ListPrice) AS LaterPrice<br>FROM   Production.Product AS p;<br>
Between LEAD (p.ListPrice, 2) and OVER, type "ignore nulls". Observe that this keyword is not included in the list of syntax suggestions.

Choose Format SQL (Ctrl-K, Ctrl-Y) and notice that now, the keyword is only partially capitalized:

SELECT p.ProductID,<br>       p.Name,<br>       p.ListPrice,<br>       p.ProductLine,<br>       LEAD (p.ListPrice, 2) ignore NULLS OVER (PARTITION BY p.ProductLine ORDER BY p.ListPrice) AS LaterPrice<br>FROM   Production.Product AS p;<br>
Shown with LEAD above, but the exact same happens with LAG.

It would be nice if this can be fixed in a future version.

Oh, and it would also be nice to not have all these options on one long line, I would prefer the Format SQL to produce output like this:

SELECT p.ProductID,<br>       p.Name,<br>       p.ListPrice,<br>       p.ProductLine,<br>       LEAD (p.ListPrice, 2) IGNORE NULLS<br>                             OVER (PARTITION BY p.ProductLine<br>                                   ORDER BY p.ListPrice) AS LaterPrice<br>FROM   Production.Product AS p;<br>
But that is just an afterthought. The lacking support for IGNORE / RESPECT NULLS is what I actually wanted to report.
--
Hugo Kornelis
(SQL Server MVP, 2006-2016 + 2019-now // Friend of Red Gate)
Tagged:
· Share on Twitter

Answers

  • HugoKornelisHugoKornelis Posts: 40 Bronze 5
    Also, WOW! Those code samples, pasted in using the "Code" format, are really totally displayed wrong!
    --
    Hugo Kornelis
    (SQL Server MVP, 2006-2016 + 2019-now // Friend of Red Gate)
    · Share on Twitter
  • JoshHJoshH Posts: 55 Bronze 1
    Hi Hugo, 

    Thanks for bringing this to our attention. I will pass this information on to our developers so they can take a look at it and work on a fix. 
    · Share on Twitter
  • JoshHJoshH Posts: 55 Bronze 1
    Hi Hugo, 

    Our developers have created a ticket for this and have it in their queue for a fix. I don't have a time frame of when that will be, but they do have it on their radar. Thanks again for pointing this out and bringing it to our attention!
    · Share on Twitter
  • pittersonpitterson Posts: 3 New member
    JoshH said:
    Hi Hugo, 

    Our developers have created a ticket for this and have it in their queue for a fix. I don't have a time frame of when that will be, but they do have it on their radar. Thanks again for pointing this out and bringing it to our attention!

    It's good to know the issue is being addressed by the developers. While the timeline isn't clear, it's reassuring that it's on their radar. Thanks for keeping us updated! Null Brawl
    · Share on Twitter
  • pittersonpitterson Posts: 3 New member
    JoshH said:
    Hi Hugo, 

    Our developers have created a ticket for this and have it in their queue for a fix. I don't have a time frame of when that will be, but they do have it on their radar. Thanks again for pointing this out and bringing it to our attention!

    Our developers have prioritized this issue and created a ticket for it. While I can't provide a specific timeline, it's on their radar. Thanks for highlighting it and keeping us informed! Null Brawl

    · Share on Twitter
Sign In or Register to comment.