NULLRef Exception while parsing function inside functions for coalesce

[cptb]

Hi Bruce,
Me again.
Recently, while parsing this simple statement:
SELECT COALESCE(added_by, RIGHT(user, LEN(user)-5)) AS changed_by FROM my_table

Calling the ParserStaements method will throw this exception below:
System.NullReferenceException: 'Object reference not set to an instance of an object.'

I realised that COALESCE may contain multiple number of parameters and each of them could also be a function. So I suspect the current logic requires a bit update to cater for these cases.

What is your opinion on this case?

Thank you in advance.

Paul

[bruce-dunwiddie]

As always, thanks for reporting.

The current parsing logic is recursive, so the embedded functions shouldn't be the problem.

I'll try to replicate and go from there.

Bruce

[bruce-dunwiddie]

I've been able to confirm the issue.

The issue is that "RIGHT" is a keyword, e.g. "RIGHT JOIN", and there's currently no parsing branch for dealing with keywords when recursively parsing function arguments.

It should be a quick fix, but I'm going to have to do some expanded testing for different keyword use cases to make sure I don't end up adding new bugs.

Bruce

[cptb]

Thank you for investigating. Yeah, the string function RIGHT & LEFT would have the same effect. There is just so many combinations to cover. I see you are slowly closing the gaps. Great work!

Paul

[bruce-dunwiddie]

It ended up being a little more interesting and painful around how to provide the helpful named collections with useful types, when the parsed token ends up being a SQLKeyword instead of a SQLIdentifier, so I had to let it soak in my head for a bit to make sure how I wanted to handle it.

https://www.nuget.org/packages/TSQL.Parser/2.6.0

Bruce

[cptb]

Thank you very much Bruce! We will update the package and run all our unit tests and update you later.