I have a simple table (loaded from a view on top of files) with Countries and Zip codes in BigQuery.
For some strange reason when both Country and Zip code are empty ... concat returns null instead of an empty string... to pass the "is null" test. :(
select *
from `TEST_business`.`zip` as z
where concat(country, zip) is null
Returns one line as shown in below image:
BUT if I run it with a separator
select *
from `TEST_business`.`zip` as z
where concat(country, '-', zip) is null
it returns no lines, as no country or zip are effectively null, as below image shows:
Lastly, let's be sure there are no NULL in any of the two columns
select *
from `TEST_business`.`zip` as z
where country is null or zip is null
Image shows that BQ itself could not find any row with contry or zip equal to null:
So I am baffled at why this could happen, it looks like a bug to me... or I miss the logic of some strange empty string to null conversion from BQ.
To complement the info: I know that the data coming into the zip table sometimes has null values for country, so country is coalesced into an empty string when loading the zip table (that currently recreated at every run)... and this behaviour has not happened before despite being long time that we run this same test after every load... and we have the same rows that have a null country that get coalesced into an empty string since months.
with dt as (select '' as country, '' as zip) select * from dt where concat(country, zip) =""is returning the row whilewith dt as (select '' as country, '' as zip) select * from dt where concat(country, zip) is nullhas no results. Yes, maybe you're right, it can be related to how the file is being parsed