FTS5 query results different before/after MERGE command.

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

FTS5 query results different before/after MERGE command.

gaillard
I have a db where I have a query returning results from something I believe is deleted already from a FTS5 table. This is a normal FTS5 table, not a contentless one so I used the normal DELETE.


Only after I run:

INSERT INTO MyFTSTable(MyFTSTable, rank) VALUES('automerge', 0);


INSERT INTO MyFTSTable(MyFTSTable, rank) VALUES('merge', 500);

Does the query then start returning the results without the deleted items.

Is this normal fts behavior?

Thanks!!
_______________________________________________
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: FTS5 query results different before/after MERGE command.

Dan Kennedy-4
On 04/18/2017 02:00 AM, Jonathan Gaillard wrote:

> I have a db where I have a query returning results from something I believe is deleted already from a FTS5 table. This is a normal FTS5 table, not a contentless one so I used the normal DELETE.
>
>
> Only after I run:
>
> INSERT INTO MyFTSTable(MyFTSTable, rank) VALUES('automerge', 0);
>
>
> INSERT INTO MyFTSTable(MyFTSTable, rank) VALUES('merge', 500);
>
> Does the query then start returning the results without the deleted items.
>
> Is this normal fts behavior?

Doesn't sound right. Can you share a database that demonstrates the problem?

Dan.


_______________________________________________
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: FTS5 query results different before/after MERGE command.

gaillard
In reply to this post by gaillard


If you do:

<raw>select rowid, name from filesfts where filesfts match '"upload file漢_5"*';</raw>

you get back:

<raw>
141| upload file漢_5
142| upload file漢_6
152| upload file漢_5
153| upload file漢_6
163| upload file漢_5
164| upload file漢_6
174| upload file漢_5
175| upload file漢_6
185| upload file漢_5
196| upload file漢_5
207| upload file漢_5
218| upload file漢_5
229| upload file漢_5
240| upload file漢_5
</raw>

and then if you run:

<raw>INSERT INTO FilesFTS(FilesFTS, rank) VALUES('merge', -1);</raw>

and the select again, you get:

<raw>
141| upload file漢_5
152| upload file漢_5
163| upload file漢_5
174| upload file漢_5
185| upload file漢_5
196| upload file漢_5
207| upload file漢_5
218| upload file漢_5
229| upload file漢_5
240| upload file漢_5
</raw>

I'm using:
sqlite> .version
SQLite 3.15.2 2016-11-28 19:13:37 bbd85d235f7037c6a033a9690534391ffeacecc8

But i've confirmed it also happens on 3.17.0 and 3.18.0


________________________________
From: Jonathan Gaillard <[hidden email]>
Sent: Monday, April 17, 2017 12:00:09 PM
To: [hidden email]
Subject: FTS5 query results different before/after MERGE command.


I have a db where I have a query returning results from something I believe is deleted already from a FTS5 table. This is a normal FTS5 table, not a contentless one so I used the normal DELETE.


Only after I run:

INSERT INTO MyFTSTable(MyFTSTable, rank) VALUES('automerge', 0);


INSERT INTO MyFTSTable(MyFTSTable, rank) VALUES('merge', 500);

Does the query then start returning the results without the deleted items.

Is this normal fts behavior?

Thanks!!
_______________________________________________
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: FTS5 query results different before/after MERGE command.

Dan Kennedy-4
On 04/20/2017 07:11 AM, Jonathan Gaillard wrote:
>
> If you do:
>
> <raw>select rowid, name from filesfts where filesfts match '"upload file漢_5"*';</raw>

This certainly seems like a bug. Are you able to share the database that
exhibits the problem?

Thanks,
Dan.





> you get back:
>
> <raw>
> 141| upload file漢_5
> 142| upload file漢_6
> 152| upload file漢_5
> 153| upload file漢_6
> 163| upload file漢_5
> 164| upload file漢_6
> 174| upload file漢_5
> 175| upload file漢_6
> 185| upload file漢_5
> 196| upload file漢_5
> 207| upload file漢_5
> 218| upload file漢_5
> 229| upload file漢_5
> 240| upload file漢_5
> </raw>
>
> and then if you run:
>
> <raw>INSERT INTO FilesFTS(FilesFTS, rank) VALUES('merge', -1);</raw>
>
> and the select again, you get:
>
> <raw>
> 141| upload file漢_5
> 152| upload file漢_5
> 163| upload file漢_5
> 174| upload file漢_5
> 185| upload file漢_5
> 196| upload file漢_5
> 207| upload file漢_5
> 218| upload file漢_5
> 229| upload file漢_5
> 240| upload file漢_5
> </raw>
>
> I'm using:
> sqlite> .version
> SQLite 3.15.2 2016-11-28 19:13:37 bbd85d235f7037c6a033a9690534391ffeacecc8
>
> But i've confirmed it also happens on 3.17.0 and 3.18.0
>
>
> ________________________________
> From: Jonathan Gaillard <[hidden email]>
> Sent: Monday, April 17, 2017 12:00:09 PM
> To: [hidden email]
> Subject: FTS5 query results different before/after MERGE command.
>
>
> I have a db where I have a query returning results from something I believe is deleted already from a FTS5 table. This is a normal FTS5 table, not a contentless one so I used the normal DELETE.
>
>
> Only after I run:
>
> INSERT INTO MyFTSTable(MyFTSTable, rank) VALUES('automerge', 0);
>
>
> INSERT INTO MyFTSTable(MyFTSTable, rank) VALUES('merge', 500);
>
> Does the query then start returning the results without the deleted items.
>
> Is this normal fts behavior?
>
> Thanks!!
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
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: FTS5 query results different before/after MERGE command.

gaillard
In reply to this post by gaillard
Here is the db https://ufile.io/q0314

If you do:

<raw>select rowid, name from filesfts where filesfts match '"upload file漢_5"*';</raw>

you get back:

<raw>
141| upload file漢_5
142| upload file漢_6
152| upload file漢_5
153| upload file漢_6
163| upload file漢_5
164| upload file漢_6
174| upload file漢_5
175| upload file漢_6
185| upload file漢_5
196| upload file漢_5
207| upload file漢_5
218| upload file漢_5
229| upload file漢_5
240| upload file漢_5
</raw>

and then if you run:

<raw>INSERT INTO FilesFTS(FilesFTS, rank) VALUES('merge', -1);</raw>

and the select again, you get:

<raw>
141| upload file漢_5
152| upload file漢_5
163| upload file漢_5
174| upload file漢_5
185| upload file漢_5
196| upload file漢_5
207| upload file漢_5
218| upload file漢_5
229| upload file漢_5
240| upload file漢_5
</raw>

I'm using:
sqlite> .version
SQLite 3.15.2 2016-11-28 19:13:37 bbd85d235f7037c6a033a9690534391ffeacecc8

But i've confirmed it also happens on 3.17.0 and 3.18.0


________________________________
From: Jonathan Gaillard <[hidden email]>
Sent: Monday, April 17, 2017 12:00:09 PM
To: [hidden email]
Subject: FTS5 query results different before/after MERGE command.


I have a db where I have a query returning results from something I believe is deleted already from a FTS5 table. This is a normal FTS5 table, not a contentless one so I used the normal DELETE.


Only after I run:

INSERT INTO MyFTSTable(MyFTSTable, rank) VALUES('automerge', 0);


INSERT INTO MyFTSTable(MyFTSTable, rank) VALUES('merge', 500);

Does the query then start returning the results without the deleted items.

Is this normal fts behavior?

Thanks!!
_______________________________________________
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: FTS5 query results different before/after MERGE command.

gaillard
This post has NOT been accepted by the mailing list yet.
In reply to this post by Dan Kennedy-4
Here it is attachedq0314-index.dbformailinglist
Reply | Threaded
Open this post in threaded view
|

Re: FTS5 query results different before/after MERGE command.

Dan Kennedy-4
In reply to this post by gaillard
On 04/21/2017 02:36 AM, Jonathan Gaillard wrote:
> Here is the db https://ufile.io/q0314

Thanks for reporting this. It was a bug in the fts5 prefix query code.
Now fixed here:

   http://www.sqlite.org/src/info/840042cb2bed2924

Dan.






>
> If you do:
>
> <raw>select rowid, name from filesfts where filesfts match '"upload file漢_5"*';</raw>
>
> you get back:
>
> <raw>
> 141| upload file漢_5
> 142| upload file漢_6
> 152| upload file漢_5
> 153| upload file漢_6
> 163| upload file漢_5
> 164| upload file漢_6
> 174| upload file漢_5
> 175| upload file漢_6
> 185| upload file漢_5
> 196| upload file漢_5
> 207| upload file漢_5
> 218| upload file漢_5
> 229| upload file漢_5
> 240| upload file漢_5
> </raw>
>
> and then if you run:
>
> <raw>INSERT INTO FilesFTS(FilesFTS, rank) VALUES('merge', -1);</raw>
>
> and the select again, you get:
>
> <raw>
> 141| upload file漢_5
> 152| upload file漢_5
> 163| upload file漢_5
> 174| upload file漢_5
> 185| upload file漢_5
> 196| upload file漢_5
> 207| upload file漢_5
> 218| upload file漢_5
> 229| upload file漢_5
> 240| upload file漢_5
> </raw>
>
> I'm using:
> sqlite> .version
> SQLite 3.15.2 2016-11-28 19:13:37 bbd85d235f7037c6a033a9690534391ffeacecc8
>
> But i've confirmed it also happens on 3.17.0 and 3.18.0
>
>
> ________________________________
> From: Jonathan Gaillard <[hidden email]>
> Sent: Monday, April 17, 2017 12:00:09 PM
> To: [hidden email]
> Subject: FTS5 query results different before/after MERGE command.
>
>
> I have a db where I have a query returning results from something I believe is deleted already from a FTS5 table. This is a normal FTS5 table, not a contentless one so I used the normal DELETE.
>
>
> Only after I run:
>
> INSERT INTO MyFTSTable(MyFTSTable, rank) VALUES('automerge', 0);
>
>
> INSERT INTO MyFTSTable(MyFTSTable, rank) VALUES('merge', 500);
>
> Does the query then start returning the results without the deleted items.
>
> Is this normal fts behavior?
>
> Thanks!!
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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