Diferent results of Contains vs Does not Contain

Avatar

Avatar

Darren_Bowers

MVP

Avatar

Darren_Bowers

MVP

Darren_Bowers
MVP

29-10-2020

Hi All - found something weird that we couldn't explain the other day. Testing out a simple query using Contains and the opposite Does not Contain gives different results.

As you can see below, the full data set is 42,356 records. A query of lowercase(first name) that contains the text string "deceased" returns 7 records (this is correct). However, if we apply the query in the opposite fashion it returns 40,053 records. There was an extra  2,296 records it was excluding from the result set. It turns out that these extra records were blank first names.

So my question is why does the query for Does not Contain exclude blank strings in the result? Surely a blank string Does not contain the string "deceased"

Darren_Bowers_0-1604008345654.png

 

query string
View Entire Topic

Avatar

Avatar

Milan_Vucetic

MVP

Avatar

Milan_Vucetic

MVP

Milan_Vucetic
MVP

30-10-2020

Hi @Darren_Bowers 

this is not related to AC but to underlying database.

 

Contain/does not contain are translated to this:

SELECT * FROM yourTable R0 WHERE (R0.sFirstName LIKE '%' || 'deceased' || '%' ESCAPE '\')
SELECT * FROM yourTable R0 WHERE (R0.sFirstName NOT LIKE '%' || 'deceased' || '%' ESCAPE '\')

 

If you run these queries directly on database the second query will not count records with empty name string.

 

Regards,

Milan