GDAL/OGR SQL Can't select correct records (Python)

Question

I have two attributes that contain a value of ' ', among others, which are not selected when I apply an SQL statement in my python script. Trying the same statement in ArcMap does result in them being selected.

I have an attribute "BYGNSTYPE" which has as distinct values:

• ' ' <-
• 'Bygning' <-
• 'Drivhus' <-
• 'Ikke tildelt'
• 'Pelsdyr Opdræt' <-
• 'Tank/Silo'

The values with an arrow next to them are the ones I want. When I use the WHERE clause: BYGNSTYPE <> 'Ikke tildelt' AND BYGNSTYPE <> 'Tank/Silo', ArcMap returns: 4,517,327 records.

When I apply the clause in my script using GDAL it returns 4,514,343 records.

sql =  "SELECT * FROM BYGNING WHERE BYGNSTYPE <> 'Ikke tildelt' AND BYGNSTYPE <> 'Tank/Silo'"
selectData = inData.ExecuteSQL(sql)
print sql
if selectData is None:
    print 'No data is selected...'
    sys.exit(1)
else:
    print 'Finished selecting data'
print "Selected features: %s " % selectData.GetFeatureCount()

When providing the oposite statement it also does not select records where BYGNSTYPE = ' '. How can I select ' ' values in Python GDAL using SQL?

FIXED:

Adding 'OR BYGNSTYPE IS NULL' to the SQL statement in Python/GDAL results in the same records being selected.

Answer 1

In SQL language NULL has a special meaning and OGR seems to behave correctly when it does not include NULLs when you make a search as "something else than 'Ikke tildelt' or 'Tank/Silo'. You must explicitly add NULL values to the query as

SELECT * FROM BYGNING WHERE (BYGNSTYPE <> 'Ikke tildelt' AND BYGNSTYPE <> 'Tank/Silo') OR BYGNSTYPE IS NULL;

ArcMap seems to deal with NULLs in a different way which is OK if it does not claim that it is using SQL. See reference https://www.w3schools.com/sql/sql_null_values.asp.