SQLite evaluates the WHERE clause from left to right, which means it needs to evaluate xyz and thereby call json_extract first, even before it can determine that no rows match the second condition.
Maybe the transformation algorithm can be changed to check the "subselect constraints" first? -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Dan Kennedy Gesendet: Freitag, 23. September 2016 11:28 An: sqlite-users@mailinglists.sqlite.org Betreff: Re: [sqlite] Strange problem (pehaps bug) with sqlite + json On 09/23/2016 03:09 PM, Gilles Vollant wrote: > With sqlite.exe from tools zipfile, I reproduce the bug with this script: > > If I add, just after the create table the line > create index if not exists idxtype on records(activitytype) ; > > I have a correct result > If I don't add the index, I've "malformed json" error. > > Can you try reproduce? Interesting one. The problem is that the query is being flattened by the SQLite optimizer. A simpler example is: CREATE TABLE t1(a, b); INSERT INTO t1 VALUES(4, 'nojson'); SELECT json_extract(b, '$.Date') AS xyz FROM (SELECT * FROM t1 WHERE a==22) WHERE xyz = ?; In the above, the SELECT statement is being transformed to the following before being executed: SELECT json_extract(b, '$.Date') AS xyz FROM t1 WHERE xyz=? AND a=22; And so the json_extract() function is executed despite the fact that the only row in the table does not match (a==22). I'm not sure how to work around this. You could use a CASE statement I suppose. i.e. things like: SELECT CASE json_valid(b) THEN json_extract(b, '$.Date') ELSE NULL END AS xyz FROM (SELECT * FROM t1 WHERE a==22) WHERE xyz = ?; Or avoid adding invalid json text to the table in the first place if it's possible. Dan. > > Regards > Gilles > > > Here is the script without double quote > > > > drop table if exists records; > create table if not exists records( activityId integer primary key, > activityDate text, activityType integer,message text) ; > > insert into records values (1,'2016-09-01',22,'{"Date":"09/01/2016 > 02:00:00","CountAnalyzedMails":44} '); insert into records values > (3,'2016-09-02',22,'{"Date":"09/02/2016 > 02:00:00","CountAnalyzedMails":54} '); insert into records values > (5,'2016-09-02',26,'nojson'); select * ,json_valid(message) from > records; > > select activityDate,Date, CountEmails from ( > > select activityDate, jsonstr, json_valid(jsonstr), > (activityDate) ||'_' || json_extract(jsonstr , '$.Date') as multiinfo, > json_extract(jsonstr , '$.Date') as Date, > json_extract(jsonstr , '$.CountAnalyzedMails') as CountEmails > > from ( > select *, length(message) as msglen, > message as jsonstr , > json_valid(message) as is_json_valid > from (select * from records where activitytype=22 ) where > is_json_valid=1) > > ) as allrs0 > > where > multiinfo in > ( > select max(activityDate) ||'_' || date as concat_sel from > > ( > select activityDate, jsonstr, json_valid(jsonstr), > (activityDate) ||'_' || json_extract(jsonstr , '$.Date') as multiinfo, > json_extract(jsonstr , '$.Date') as Date, > json_extract(jsonstr , '$.CountAnalyzedMails') as CountEmails > > from ( > > select *, length(message) as msglen, > message as jsonstr , > json_valid(message) as is_json_valid > from (select * from records where activitytype=22 ) where > is_json_valid=1) > > ) as allrs1 > > group by date order by date); > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
