How can we help you today? How can we help you today?

IGNORE NULLS and RESPECT NULLS not recognized for LAG and LEAD

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.
HugoKornelis
0

Comments

5 comments

Sort by
  • HugoKornelis
    Also, WOW! Those code samples, pasted in using the "Code" format, are really totally displayed wrong!
    HugoKornelis
    0
  • JoshH
    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. 
    JoshH
    0
  • JoshH
    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!
    JoshH
    0
  • pitterson
    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
    pitterson
    0
  • pitterson
    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

    pitterson
    0

Add comment

Please sign in to leave a comment.