Are RTREE virtual tables supposed to treat NULL values as 0.0?
So, I was looking at some triggers to update an RTREE virtual table that someone
else wrote. I noticed that the trigger didn't handle NULLs. I was curious, and
decided to see what happened if you tried to insert NULL values into an RTREE.
Actually, I rather expected it to throw an error. Instead, the values (aside
from the id which is a separate issue) became 0.0 like so:
CREATE VIRTUAL TABLE nulltest USING RTREE (pkid,v1,v2);
INSERT INTO nulltest DEFAULT VALUES;
SELECT * FROM nulltest;
pkid v1 v2
---------- ---------- ----------
1 0.0 0.0
This is not actually an ideal result, since 0.0 could either be a legitimate
value, which means a search of the rtree table could produce a false positive;
or it could be a completely unexpected value and cause who know what sort of
problems.
Mind you, while I use RTREEs in SQLite myself, this isn't a problem for me, as I
always supply legitimate values.
When I looked at the SQLite source, you can see that there is no check for NULL
in the rtree code except for ids, so the calls to sqlite3_value_double in
rtreeValueDown and rtreeValueUp are just going to return 0.0 when NULLs are
supplied. It seems to me that logically, they should probably throw constraint
violations instead, but at this point that would be an issue for backwards
compatibility.
Peter
(Continue reading)