In GRASS GIS db.execute IN statement works, but NOT IN doesn't

Question

Here is my command for GRASS GIS database (SQL Lite driver used):

db.execute sql="UPDATE streams_order SET factor=next_stream WHERE number IS NOT NULL AND next_stream IN (SELECT number FROM streams_order)"

But.. I want

db.execute sql="UPDATE streams_order SET factor=next_stream WHERE number IS NOT NULL AND next_stream NOT IN (SELECT number FROM streams_order)"

And result should be:

+---------+--------------+--------+---------+
| stream | next_stream factor | number |
+-------------------------------------------+
 370 -1 
 372 370 370 372
 374 440 
 376 372 376
 380 374 374 380
.............................................

it doesn't work without any error: "factor" column is empty after running the command. If I put IN it works well. Column types are all INT.

Sandbox with real data is here https://dbfiddle.uk/zg8_xJUz

• Nikolay Yasinskiy: I can't find SQL Lite tag
• Nikolay Yasinskiy: You mean this update?
• Nikolay Yasinskiy: I am not sure if 5 tip is useful here, as it is not about query, but about execute a grass internal database command.
• Nikolay Yasinskiy: I have it all in one database sqllite.db... Is it because of grass particularities
• Nikolay Yasinskiy: Sorry. I can export following formats: AmigoCloud, BAG, BNA, CSV, Carto, Cloudant, CouchDB, DGN, DXF, ESRI_Shapefile, Elasticsearch, FITS, FlatGeobuf, GML, GMLAS, GPKG, GPSBabel, GPSTrackMaker, GPX, GeoJSON, GeoJSONSeq, GeoRSS, Geoconcept, Interlis_1, Interlis_2, JML, JP2OpenJPEG, KML, LIBKML, MBTiles, MSSQLSpatial, MVT, MapInfo_File, MapML, Memory, MySQL, NGW, ODBC, ODS, OGR_GMT, PCIDSK, PDF, PDS4, PGDUMP, PostgreSQL, S57, SQLite, Selafin, TIGER, VDV, VICAR, WAsP, XLSX, netCDF

Answer 1

Your problem is related to the presence on NULL values in the number column.

NOT IN always fails when the list has NULL values because NULL comparisons cannot be true or false.

More precisely, in SQL logic NULL doesn't mean "empty", it means "undetermined". A comparison val = NULL will always result NULL, even if val is also NULL. This is why, to check if something is NULL, we need to use the special operator IS NULL.

The expression val IN ('A', NULL) will be translated as val='A' OR val=NULL.

If val is equal to 'A' that will become TRUE OR NULL, which evaluates to TRUE. (and NOT IN evaluates to FALSE). But if val is equal to 'B' that expression becomes FALSE OR NULL, which evaluates to NULL (and NOT IN is still NULL).

So, in a list with null elements, val IN (...) will evaluates to TRUE if one of the not null element equals to val, and NULL otherwise, while val NOT IN (...) will evaluate to FALSE if one of the not null elements equals to val and NULL otherwise, but it will never evaluates to TRUE, so your update will never fire.

See also this question.

Workaround:

UPDATE streams_order SET factor=next_stream 
WHERE number IS NOT NULL AND 
 next_stream NOT IN (SELECT number FROM streams_order WHERE number IS NOT NULL)

• Nikolay Yasinskiy: I like your solution more, it's elegant, and you gave good explanations. Thanks.

Answer 2

if IN is supported, so NOT IN should be also supported

If something is unsupported you will be facing with some error , like you have in your previous question DBMI-SQLite driver error: Error in sqlite3_prepare(): near ".": syntax error in db.execute command.

To debug the issue before running the update statement , run a select.

If I run the same update query transformed in select statement you will see that no record is returned.

select factor2_new,next_stream,number 
from stream_order
WHERE number IS NOT NULL AND next_stream NOT IN (SELECT number FROM stream_order)

Now the problem stands in the query.

I would suggest using not exists rather than not in , see SQL "select where not in subquery" returns no results for the differences between exists and in.

Change your select query to,

SELECT *
FROM stream_order
WHERE number IS NOT NULL 
AND NOT EXISTS (SELECT NULL
 FROM stream_order so
 WHERE so.number = stream_order.next_stream )

Result,

stream next_stream factor factor2 next_stream2 number factor_new factor2_new next2
 111 222 null -1 -1 333 null null null
 370 -1 null -1 -1 370 null null null
 398 394 null 394 394 398 null null null
 404 402 null 402 402 404 null null null
 420 416 null 416 416 420 null null null
 458 454 null 454 454 458 null null null

Update query,

UPDATE stream_order
SET factor2_new=next_stream 
WHERE number IS NOT NULL 
AND NOT EXISTS (SELECT NULL
 FROM stream_order so
 WHERE so.number = stream_order.next_stream )

See example here

• Nikolay Yasinskiy: I works well too.