In the sqlite shell, enter the .explain command and then EXPLAIN QUERY PLAN <your query>
for an explanation of the plan, and EXPLAIN <your query> for the generated bytecode. This usually helps to understand what sqlite is thinking (although maybe not why). Note that WHERE constraints are applied to the input set whereas HAVING constraints are applied to the output set, so perhaps changing the "outer" constraint to HAVING smart_search() will yield the desired effect. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:[email protected]] Im Auftrag von Hegde, Deepakakumar (D.) Gesendet: Dienstag, 28. August 2018 07:47 An: [email protected] Betreff: [EXTERNAL] [sqlite] Query on TEMP view. Hi All, I am facing a problem where in defined function registered to sqlite is called multiple time withput considering the filter. Ex: Table and entry: ID NAME PPID 1 a.mp3 2 2 b.mp3 3 Query: SELECT COUNT(TEMP.ID) FROM (SELECT ID,NAME,PPID FROM AUDIO WHERE PPID=2) AS TEMP WHERE smart_search(NAME,ID) OutPut count = 1 (This is fine) Here smart_search() is defined and given to sqlite DB. We are expecting smart_search() to be called from sqlite only for the entry with PPID 2 and that will be for ID 1 a.mp3. But we see that this is called 2 times and for both the entry. As per my understanding. filter of PPID is added for the inner query smart_search() should have called only for one entry. Is this the expected behavior? If i change the query as below then the smart_search() is called for only one entry. SELECT COUNT(TEMP.ID) FROM (SELECT ID,NAME,PPID FROM AUDIO WHERE PPID=2) AS TEMP WHERE PPID=2 AND smart_search(NAME,ID) Please help to understand on this. Thanks and Regards Deepak _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
