I'm getting unexpected and differing results between WebSQL(SQLite) and SQLite. The query produces the correct results in WebSQL.
This is the script that I'm running. It's designed to allow us to filter out, over several tables, data that we decide is not interesting and therefore whitelisting it. First here's the link to the fiddles I've created demonstrating this issue. The correct result is one row; SQLite returns all rows. http://sqlfiddle.com/#!7/74e01/1 WebSQL(SQLite) http://sqlfiddle.com/#!5/74e01/1 SQLite(SQL.js) (Click "Cancel" if prompted.) CREATE TABLE scans ( scan_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL); CREATE TABLE hosts( host_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, scan_id INTEGER NULL); CREATE TABLE programs( program_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, host_id INTEGER NULL, name varchar NULL, publisher varchar NULL); CREATE TABLE whitelist( whitelist_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, scan_id INTEGER NULL, value1 varchar NULL, value2 varchar NULL ); insert into hosts(host_id) VALUES (1); insert into programs(program_id,host_id,name,publisher) VALUES (1,1,"foo", "foo"); insert into programs(program_id,host_id,name,publisher) VALUES (2,1,"bar", "bar"); insert into programs(program_id,host_id,name,publisher) VALUES (3,1,"abc", "def"); insert into programs(program_id,host_id,name,publisher) VALUES (4,1,"ghi", "jkl"); insert into programs(program_id,host_id,name,publisher) VALUES (5,1,"mno", "jkl"); insert into programs(program_id,host_id,name,publisher) VALUES (6,1,"pqr", "stu"); insert into whitelist(whitelist_id,value1,value2) VALUES (1,"foo","foo"); insert into whitelist(whitelist_id,value1,value2) VALUES (2,"bar","bar"); insert into whitelist(whitelist_id,value1,value2) VALUES (3,"abc",""); ---The Query--- SELECT * FROM programs p LEFT JOIN hosts h ON p.host_id = h.host_id LEFT JOIN whitelist w ON (w.value1 = p.name AND p.publisher = w.value2) OR ( w.value1 = '' AND p.publisher = w.value2 ) OR ( w.value2 = '' AND p.name = w.value1 ) WHERE w.whitelist_id IS NULL _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
