There are a myriad of reasons for the behaviour you are seeing and they affect only performance and not correctness. In other words, you think that your UDF is more "expensive" to compute than the PPID == 2 test, and therefore the least expensive test should be performed first so that the more expensive operation does not need to be performed where its result would merely be discarded by virtue of the lesser expensive ANDed condition.
The other thing is that the subquery is likely being flattened -- again this depends on the version of SQLite3 you are using. However, assuming that the query is being flattened then: SELECT COUNT(TEMP.ID) FROM (SELECT ID,NAME,PPID FROM AUDIO WHERE PPID=2) AS TEMP WHERE smart_search(NAME,ID) which is exactly the same as (with the aliasing removed as it adds nothing of significance) SELECT COUNT(ID) FROM (SELECT ID, NAME, PPID FROM AUDIO WHERE PPID=2) WHERE smart_search(name,id) which should be flattened to SELECT COUNT(ID) FROM AUDIO WHERE PPID=2 and smart_search(name,id) *See the query flattening rules at https://www.sqlite.org/optoverview.html#subquery_flattening That said, however, I am unable to reproduce with the current tip of trunk. What version of sqlite3 are you using? --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Hegde, Deepakakumar >(D.) >Sent: Monday, 27 August, 2018 23:47 >To: sqlite-users@mailinglists.sqlite.org >Subject: [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 >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
