possible json1 bug?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

possible json1 bug?

Jose Manuel
There is a problem whenever you try to define an INDEX over a json column
and then using a Query with a JOIN over the same table.

Here is the code that triggers the problem:



SQLiteConnection _dbc1 = new SQLiteConnection("Data
Source=test.db;Version=3;");
 _dbc1.Open();
 _dbc1.EnableExtensions(true);
 _dbc1.LoadExtension(@"SQLite.Interop.dll", "sqlite3_json_init");
SQLiteCommand sqlcmd1 = _dbc1.CreateCommand();

sqlcmd1.CommandText = "CREATE TABLE docs (id int primary key, type text,
body json NOT NULL);";
sqlcmd1.ExecuteNonQuery();

sqlcmd1.CommandText = "create index index1 on docs (type,
json_extract(body, '$.color'));";
sqlcmd1.ExecuteNonQuery();

sqlcmd1.CommandText = "INSERT INTO docs (id, type, body) VALUES(1,
\"customcolor\", json('{\"typecolor\": \"red\" }'));";
sqlcmd1.ExecuteNonQuery();

sqlcmd1.CommandText = "INSERT INTO docs (id, type, body) VALUES(2, \"car\",
json('{\"color\": 1 }'));";
sqlcmd1.ExecuteNonQuery();

string sql = "SELECT main.body as maindoc FROM docs as main INNER JOIN docs
as jointable ON jointable.id = json_extract(maindoc, '$.color') WHERE
main.type = \"car\" AND json_extract(jointable.body, '$.typecolor') =
\"red\" LIMIT 0,-1";

SQLiteCommand command  = new SQLiteCommand(sql, _dbc1);
*SQLiteDataReader reader = command.ExecuteReader();*
reader.Read();
Debug.WriteLine(reader.GetString(0));





*Error*
*System.AccessViolationException*
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: possible json1 bug?

Richard Hipp-3
On 8/18/17, Jose Manuel <[hidden email]> wrote:
> There is a problem whenever you try to define an INDEX over a json column
> and then using a Query with a JOIN over the same table.

Thanks for the report.  But OSSFuzz already found that bug.  It was
fixed four days ago:

    https://sqlite.org/src/timeline?c=d0da791ba0edfb65&y=ci

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users