Re: [sqlite] Bug in using overloaded functions for virtual tables?
Here are the opcodes generated for each operation using the EXPLAIN command sqlite> explain select offsets(pasta_text), DataPasta from pasta_text where past a_text match 'andamento*'; addr opcode p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 000 1 Goto 0 14000 2 VOpen 0 0 0 vtab:3E6EE8:46A920 00 3 String80 3 0 andamento* 00 4 Integer371 000 5 Integer1 2 000 6 VFilter0 12100 7 VColumn0 35100 8 Function 0 1 4 offsets(1) 01 9 VColumn0 4 500 10ResultRow 4 2 000 11VNext 0 7 000 12Close 0 0 000 13Halt 0 0 000 14Transaction0 0 000 15VerifyCookie 0 297 000 16Goto 0 2 000 sqlite> explain select offsets(pasta_text), DataPasta from pasta_text where past a_text match 'andamento*' group by DataPasta; addr opcode p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 000 1 OpenEphemeral 1 3 0 keyinfo(1,BINARY) 00 2 Integer0 4 000 3 Integer0 3 000 4 Gosub 6 41000 5 Goto 0 45000 6 VOpen 0 0 0 vtab:3E6EE8:46A920 00 7 String80 110 andamento* 00 8 Integer379 000 9 Integer1 10000 10VFilter0 17900 11VColumn0 4 900 12Sequence 1 10000 13VColumn0 3511 00 14MakeRecord 9 3 12 00 15IdxInsert 1 12000 16VNext 0 11000 17Close 0 0 000 18Sort 1 44000 19Column 1 0 800 20Compare7 8 1 keyinfo(1,BINARY) 00 21Jump 222622 00 22Move 8 7 100 23Gosub 5 34000 24IfPos 4 44000 25Gosub 6 41000 26Column 1 2 100 27Column 1 0 200 28Integer1 3 000 29Next 1 19000 30Gosub 5 34000 31Goto 0 44000 32Integer1 4 000 33Return 5 0 000 34IfPos 3 36000 35Return 5 0 000 36SCopy 1 9 000 37Function 0 9 13offsets(1) 01 38SCopy 2 14000 39ResultRow 132 000 40Return 5 0 000 41Null 0 1 000 42Null 0 2 000 43Return 6 0 000 44Halt 0 0 000 45Transaction0 0 000 46VerifyCookie 0 297 000 47Goto 0 6 000 sqlite> -- View this message in context: http://old.nabble.com/Bug-in-using-overloaded-functions-for-virtual-tables--tp32364864p32726667.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in using overloaded functions for virtual tables?
Stepping through execution for queries with and without GROUP BY clauses, I could see that, when preparing a query containing a Group By clause, the parser assigns a TK_AGG_COLUMN type to first argument of the offsets() and snippet() functions, thus breaking out of sqlite3VtabOverloadFunction() that is called from sqlite3ExprCodeTarget() with a TK_FUNCTION op code, finally not being able to look up the overloaded version of the functions, executing the default, sqlite3InvalidFunction implementation. Doing some tests and including the TK_AGG_COLUMN in the conditions that should continue the overload mechanism, the correct implementations are found and assigned, but execution crashes later on, when stepping the resultset - looks like we then have an invalid fulltext cursor. -- View this message in context: http://old.nabble.com/Bug-in-using-overloaded-functions-for-virtual-tables--tp32364864p32681827.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in using overloaded functions for virtual tables?
I experience the same situation on the following query: select offsets(pasta_text), snippet(pasta_text), DataPasta from pasta_text WHERE pasta_text match 'andamento*' group by DataPasta order by DataPasta Which desired output would be all matches grouped by date (the full query is joined from a child table that can return multiple results per parent, but only one parent is desired in the output, along with an arbitrary child snippet) Regards Rafael fbuser wrote: > >>> From: fbu...@web.de >>> To: sqlite-users@sqlite.org >>> Date: Tue, 30 Aug 2011 16:01:24 +0200 (CEST) >>> Subject: Bug in using overloaded functions for virtual tables? >>> >>> SELECT DISTINCT offsets(pages) FROM pages; >>> -or- >>> SELECT offsets(pages),count(*) FROM pages GROUP BY 1 >>> >>> => error message: unable to use function offsets in the requested >>> context >>> >>> Is this is a bug or a restriction, which is not documented? If this is a >>> restriction it should be considered as a feature request to allow using >>> virtual table functions in queries with the DISTINCT keyword or the >>> GROUP BY >>> clause. Otherwise the bug should be fixed :-) > -- View this message in context: http://old.nabble.com/Bug-in-using-overloaded-functions-for-virtual-tables--tp32364864p32674947.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in using overloaded functions for virtual tables?
Von: "Richard Hipp"
Gesendet: 30.08.2011 16:30:28
An: sqlite-users@sqlite.org
Betreff: [sqlite] Bug in using overloaded functions for virtual tables?
>On Tue, Aug 30, 2011 at 10:01 AM, wrote:
>
>> The attached message matched the sqlite-users mailing list's content
>> filtering rules and was prevented from being forwarded on to the list
>> membership.
>> -- Forwarded message --
>> From: fbu...@web.de
>> To: sqlite-users@sqlite.org
>> Date: Tue, 30 Aug 2011 16:01:24 +0200 (CEST)
>> Subject: Bug in using overloaded functions for virtual tables?
>>
>> SELECT DISTINCT offsets(pages) FROM pages;
>> -or-
>> SELECT offsets(pages),count(*) FROM pages GROUP BY 1
>>
>> => error message: unable to use function offsets in the requested context
>>
>> Is this is a bug or a restriction, which is not documented? If this is a
>> restriction it should be considered as a feature request to allow using
>> virtual table functions in queries with the DISTINCT keyword or the GROUP BY
>> clause. Otherwise the bug should be fixed :-)
>>
>
>The offsets() function of FTS4 gives you the locations in each record of
>where your matching search terms are located. But you didn't specify any
>search terms - there is no MATCH operator in the WHERE clause. So what
>where you hoping to get back with these queries?
>
>--
>D. Richard Hipp
I had some troubles to send my mail to the list and somehow the first part of
my mail got lost, when you inserted into the list, so here it is:
>I have problems with using overloaded functions which are defined for virtual
>tables in queries, which includes the DISTINCT keyword or the GROUP BY clause.
>
>Steps to reproduce (only an example) with SQLite 3.7.7 (FTS4 needs to be
>enabled):
>
>CREATE VIRTUAL TABLE pages USING fts4(title, body);
>INSERT INTO pages(docid, title, body) VALUES(53, 'Home Page', 'SQLite is a
>software...');
>INSERT INTO pages(title, body) VALUES('Download', 'All SQLite source code...');
So, the problem is not related to using the offset() function of FTS4, it was
only meant as an easy example to reproduce the problem, which I have, when I
define a function for an own implementation of a virtual table by using
sqlite3_overload_function and xFindFunction in the same way as offset() is
implemented for FTS4.
___
Schon gehört? WEB.DE hat einen genialen Phishing-Filter in die
Toolbar eingebaut! http://produkte.web.de/go/toolbar
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
