I have full paths of a set of resources in a database, and am using
substrings to find arbitrary hierarchical resources. Worked fine on
Windows using Sqlite3 3.6.5, but on Linux with 3.5.9 the query fails.
The schema includes tables:
paths:(integer id, string path)
common_details:(integer path_id (fkey), string type, string name)
select p3.id, cd3.name, cd2.name, p2.path as p2, length(p1.path) as len_p1,
p1.path as p1, substr(p2.path, length(p1.path) + 1, 1) as p1Char
from paths as p1, paths as p2, paths as p3,
common_details as cd3, common_details as cd2, common_details as cd1
where cd1.name = "Abbreviations" and cd1.type = "folder"
and cd2.type = "folder" and (cd2.name like "Perl" or cd2.name like
"General")
and cd1.path_id = p1.id
and cd2.path_id = p2.id
and substr(p2.path, 1, length(p1.path)) = p1.path
and (substr(p2.path, length(p1.path) + 1, 1) == "/"
or substr(p2.path, length(p1.path) + 1, 1) != "/")
and cd3.type = "snippet" and cd3.name like "fore"
and cd3.path_id = p3.id
and substr(p3.path, 1, length(p2.path)) = p2.path ;
Notice the clause in the middle of the query:
and (substr(p2.path, length(p1.path) + 1, 1) = "/"
or substr(p2.path, length(p1.path) + 1, 1) != "/")
If I comment out this full clause, the query returns the expected result
set.
But if I leave it uncommented, the query returns an empty set. But the
clause
should be tautologically true -- either the character after the prefix is
"/" or it
isn't.
Looks like a bug to me. The code ran fine on Windows (where I used
only the '=' test, not the '!=' test. I do have a workaround -- it's to
return paths
in my result set and to the substring test in Python instead of sqlite. But
I'd prefer having sqlite run this code.
- Eric
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
