isnum() vs. NaN

[bochmann]

Has anyone run into the interesting effect that isnum() thinks that "NaN" is a number?

So

isnum("NaN") is true
"NaN" * 2 = "NaN"

but

tonumber("NaN") is NULL

Are there any other odd, uh, numbers besides Not a Number?

I made up the following silly query as an illustration:

| makeresults
| eval num="blubb;NaN;100;0.5;0,5;-0;NULL;"
| makemv delim=";" allowempty=true num 
| mvexpand num
| eval isnum=if(isnum(num),"true","false")
| eval isint=if(isint(num),"true","false")
| eval isnull=if(isnull(num),"true","false")
| eval calcnum=num*2
| eval isnumcalcnum=if(isnum(calcnum),"true","false")
| eval isnullcalcnum=if(isnull(calcnum),"true","false")
| eval numnum=tonumber(num)
| eval isnumnum=if(isnum(numnum),"true","false")
| eval isnullnumnum=if(isnull(numnum),"true","false")
| table num,isnum,isint,isnull,calcnum,isnumcalcnum,isnullcalcnum,numnum,isnumnum,isnullnumnum

 which results in

num	isnum	isint	isnull	calcnum	isnumcalcnum	isnullcalcnum	numnum	isnumnum	isnullnumnum
blubb	false	false	false		false	true		false	true
NaN	true	false	false	NaN	true	false		false	true
100	true	true	false	200	true	false	100	true	false
0.5	true	false	false	1	true	false	0.5	true	false
0,5	false	false	false		false	true		false	true
-0	true	true	false	-0	true	false	-0	true	false
NULL	false	false	false		false	true		false	true
	false	false	false		false	true		false	true

(Post moved over from the Splunk Enterprise group.)

[bochmann]

So, I've been talking to Splunk support, which directed me to the documentation at SearchReference/Eval  that kind of mentions that NaN is special, and also pointed to typeof() as alternative.

Initially, this seemed like a good idea, but unfortunately typeof() is even more interesting:

| makeresults
| eval t=typeof("NaN")
| eval num="NaN"
| eval tnum=typeof(num)

...returns

t = String
tnum = Number

Oh well....?

[yuanliu]

Support is correct that documented behavior is not a bug.  That explains your original observation about tonumber.

Your observation about typeof is also normal.  Imagine you are the interpreter.  In typeof("NaN"), you are given a string literal.  Of course you say that's of type String.  In typeof(num), you are given a variable whose value is documented as a number.  You say that's of type Number.

[bochmann]

Well, Splunk doesn't treat inf and -inf, mentioned in that same section, as a number either.

Anyways, I need to add additional logic to sanitize inputs that might have fields with the text "NaN" (does occasionally happen when the source is a SQL query) either way - for most purposes it just isn't a number, and tends to cause problems in further processing.

[yuanliu]

the text "NaN" (does occasionally happen when the source is a SQL query) either 

---

This explains it.  I was wondering why typeof(num) should be Number when num had value "NaN". Whoever wrote that code in typeof must have SQL in mind.  A SQL query only returns "NaN" when the data type is numeric.  If you are programming against results from a SQL query in any language, you always need to write a logic for this possible return.

[richgalloway]

Smells like a bug to me.  Consider reporting it to Splunk Support and/or https://ideas.splunk.com

[kgorzynski]

Hi Splunk community!

I just want to let you know that I worked with one of our most senior engineers to update information in Splunk docs about NaN -- take a look at the info about NaN in the isnum and isstr sections in Informational functions in the Splunk platform Search Reference.  

I know that playing around with NaN is irresistible, especially for our techiest Splunk experts, but the general advice from the sr engineer is to avoid using NaN in Splunk searches if possible unless you really really know what you're doing. 🙂 

--Kristina

[yuanliu]

Thank you @kgorzynski! Updated information provides much needed clarity.