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