bluebaron

About me

Developer Information
Name bluebaron
User since July 15, 2013
Number of add-ons developed 0 add-ons
Average rating of developer's add-ons Not yet rated

My Reviews

SQLite Manager

Rated 1 out of 5 stars

There's a SEVERE error in SQLite. It's actually returning incorrect queries. It happens any time an OR is used in a LEFT JOIN statement. This is a very serious and critical error. If you like you can see my fiddle which demonstrates this issue.
Here's my original issue from the SQLite mailing list:
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



And here's the correct answer:

The bug was fixed for 3.7.17. It's this one:

http://www.sqlite.org/src/info/f2369304e47167e3e


Please fix!!!

This review is for a previous version of the add-on (0.8.0).