Issue with the IS NULL function in BigQuery

Question

I am writing as a new follow up that echoes a problem posed in a previous post.

Currently, I am working with a data set to filter out values where there are blanks recorded inside the cell. Specifically, I have the following query:

SELECT visitNumber, visitId, visitStartTime, totals_timeOnSite, trafficSource_medium, hits_transaction_transactionId, fullVisitorId, RIGHT(hits_transaction_transactionId,10) AS Transact_ID
FROM [From_USCC.consolid_GA_oct]
WHERE hits_transaction_transactionId IS NOT NULL

• I understand that there is an issue with the IS NULL function (or there was an issue). Is this issue still persistent?

• When I filter for the IS NOT NULL, will it remove cleanly all the rows that have no data "" in the hits_transaction_transactionId, as well as all the rows with the word 'NULL' inside the its_transaction_transactionId cell?

I am asking because I have a large amount of data from a report that I cannot attribute back to GA & am trying to understand why.

Answer 1

The issue with IS NULL has been fixed; I've updated the question you linked.

An IS [NOT] NULL filter will filter only null values. A null value is different from an empty string, which is different from the string "null".

If you look at the actual json of the query response, you can see the difference. A null field will look like

{"hits_transaction_transactionId": null} 

An empty string will look like:

{"hits_transaction_transactionId": ""} 

And a string with the value of "null" will look like:

{"hits_transaction_transactionId": "null"} 

To find the first, you can use IS NULL, to find the second you can use == "" and to find the third (which probably isn't what you want), you can use == "null".

If you believe that this isn't working correctly, please let us know exactly what is happening and how that differs from expectations and we'll investigate.