Re: [sqlite] Strange problem (pehaps bug) with sqlite + json

[Dominique Devienne]

On Fri, Sep 23, 2016 at 11:58 AM, Hick Gunter  wrote:

> 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?
>

Would be very clever indeed, to leverage the short-circuit semantic in that
case. Great idea! --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange problem (pehaps bug) with sqlite + json

[Hick Gunter]

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

Re: [sqlite] Strange problem (pehaps bug) with sqlite + json

[Dan Kennedy]

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

Re: [sqlite] Strange problem (pehaps bug) with sqlite + json

[Gilles Vollant]

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?

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

Re: [sqlite] Strange problem (pehaps bug) with sqlite + json

[Gilles Vollant]

On 9/20/16, Richard Hipp wrote:

> Maybe don't double the double-quotes.  JSON expects just a single
> double-quote a either end of a string.

The tools I user (sqlite explorer) wanted it.
And  after I do
update  records set message=replace(message,'""','"');

So there is NO double quote in the database.

Regards
Gilles Vollant

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange problem (pehaps bug) with sqlite + json

[Richard Hipp]

On 9/20/16, Gilles Vollant  wrote:
>
> delete from records;
> insert into records values (1,'2016-09-01',22,'{""Date"":""09/01/2016
> 02:00:00"",""CountAnalyzedMails"":44} ');

Maybe don't double the double-quotes.  JSON expects just a single
double-quote a either end of a string.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Strange problem (pehaps bug) with sqlite + json

[Gilles Vollant]

Hello,
I see a strange problem in sqlite with json and pehaps a bug.
I've a table with somes json record (when activityType=22) and not json
record (when activityType is not 22)

I need made somes complex request on json value.
And the request below give me an error "malformed JSON".

If we add , after the create table, the index creation below:
create index if not exists idxtype on records(activitytype);

We don't have the error and we have the wanted result.

Here is the sql code, to create and fill a small sample table, then
(complex) request:


drop table if exists records;
create table if not exists records(activityId integer primary key,
activityDate text, activityType integer,message text)   ;

delete from records;
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');
update  records set message=replace(message,'""','"');
select * ,json_valid(message) from records;

drop index if exists idxtype;


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);



Best regards
Gilles Vollant

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users