RTREE only understands floating-point numbers (or integers if you use "rtree_i32" instead of "rtree"). It does not do NULLs or strings or blobs. If you give it one of these other values, it will try to convert that value into a floating-point number as best it can.
The best it can do with a NULL is convert it into 0.0. On Thu, May 23, 2013 at 3:09 PM, Peter Aronson <pbaron...@att.net> wrote: > 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 > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
