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
>
>1a.mp3 2
>
>2b.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
