CODEWITHSUNDEEP
google-bigquery
Ben Kim
Ben Kim

Reputation: 397

IS NULL function not working

I've tested IS NULL function provided by Google BigQuery, but it doesn't seems to give correct results.

SAMPLE DATA:

id   age   gender   password
1,   11,    NULL,     NULL
1,   11,   "NULL",   "NULL"
1,   11,   "null",   "null"
1,     ,    NULL,     NULL

QUERY: 

SELECT id, age, gender, password, id IS NULL, age IS NULL, gender IS NULL, password IS NULL 
FROM privatedata.testnull

OUTPUT:

Row id  age gender  password f0_    f1_      f2_     f3_     
1   1   11   NULL    NULL   false   false   false   false    
2   1   11   NULL    NULL   false   false   false   false    
3   1   11   null    null   false   false   false   false    
4   1   0    NULL    NULL   false   false   false   false

But this SQL works: 

SELECT NULL IS NULL, COUNT(*) FROM privatedata:testnull

So I'm not sure if IS NULL is working or not. Also I'm confused with how I would insert null data in string format and numeric format.

Upvotes: 0

Views: 2812

Answers (1)

Jordan Tigani
Jordan Tigani

Reputation: 26617

EDITED ANSWER:

For very old tables there was an issue where we did not preserve the between an empty field and a NULL one. This issue should by fixed for any table that was created after March, 2013.

OLD ANSWER:

I believe the issue is that for string fields, we're interpreting the NULL as the string "NULL". What if you just use , , for the string values?

The one that is surprising is that age in row 4 should be showing up as null. It looks like something is wrong with the IS_NULL function. I've filed a bug internally. The IS_EXPLICITLY_DEFINED() function, however, should return what you expect (e.g. IS_EXPLICITLY_DEFINED for age in row 4 would return fasle).

Upvotes: 1

Related Questions