Re: [sqlite] possible json1 bug?

[Richard Hipp]

On 8/18/17, Jose Manuel  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=ci

-- 
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] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users