[BUG] NULL value filtering not working correctly

[em-daniil-terentyev]

Hi everybody!

When I import data from cosmos db into databricks, and create temporary view from this data, filter "someField IS NOT NULL" is not working correctly. The only way to make it work is to add one more condition "LOWER(CAST(someField AS STRING)) <> 'null'", but it's not correct, because for this field there is not data with string value, it contains json object or NULL.

`connectionConfig = {
"spark.cosmos.accountEndpoint" : "endpoint",
"spark.cosmos.accountKey" : "key",
"spark.cosmos.database" : "database",
"spark.cosmos.container" : "containter",
"spark.cosmos.read.inferSchema.enabled" : "true"
}

spark
.read
.format("cosmos.oltp")
.options(**connectionConfig)
.load()
.createOrReplaceTempView("tmp_cosmos_data")`

Apache Spark 3.1.1
Library: com.azure.cosmos.spark:azure-cosmos-spark_3-1_2-12:4.2.0
Operating System: Ubuntu 18.04.5 LTS
Java: Zulu 8.52.0.23-CA-linux64 (build 1.8.0_282-b08)

If you can't reproduce it on your side with simple execution of mentioned actions please let me know. To fill all the requirements mentioned in this bug report and to meet privacy requirements it's needed to create separate instance of Cosmos DB and so on. Please let me know if it's necessary.

Thanks in advance.

Regards,
Daniil.

[joshfree]

@kushagraThapar could you please help route this?

[kushagraThapar]

@moderakh - can please take a look at this issue ?

[moderakh]

@em-daniil-terentyev could you please provide more info on this?

filter "someField IS NOT NULL" is not working correctly. The only way to make it work is to add one more condition "LOWER(CAST(someField AS STRING)) <> 'null'", but it's not correct, because for this field there is not data with string value, it contains json object or NULL.

• what's the expected behviour
• what behaviour you see?
• (you provided code for dataframe load) could you provide the code for the filtering which you are using as well?

[em-daniil-terentyev]

Hi, @moderakh,

Thanks for your response.

Here are answers for your questions. I hope it helps.

1. Expected behavior is to filter all objects, that doesn't have someField, or that assigned as null such as:
   { lala : "123", lolo : "123", someField : null }, { lala : "123", lolo : "123" }

In other words make it work in a correct way as it was in previous version of library com.microsoft.azure.cosmosdb.spark.

2. I see that if i just use filter "someField IS NOT NULL" query returns objects, that doesn't have someField. If i add "LOWER(CAST(someField AS STRING)) <> 'null'" it returns the same correct result as returned by com.microsoft.azure.cosmosdb.spark with only "someField IS NOT NULL" filter

3. SELECT COUNT(*) FROM tmp_cosmos_data WHERE someField IS NOT NULL AND LOWER(CAST(someField AS STRING)) <> 'null'

Looking forward to new version of library with correct treating of NULL values.

Thanks in advance.

Regards,
Daniil.

[em-daniil-terentyev]

Hi, @moderakh.

Are there any news about this issue?
May be i can help somehow with this?

Regards,
Daniil.

[em-daniil-terentyev]

@moderakh, thanks a lot! :)