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
