Reading a little more about how the vdbe works and a little of the source code,
I then examined the output of explain. Explain shows that the statement:
select distinct x, abs(x) from T ;
First loops over my T table and builds an Ephemeral table, which has an
index(?) that will allow sqlite to compute the distinct rows. It evaluates the
select terms x, abs(x) as it inserts the value of T into the Ephemeral table.
It then loops over the distinct values in the Ephemeral table to generate the
output result. The problem is that it uses the same select terms ( x, abs(x) )
to create the result, instead of just using the already computed values in the
Ephemeral table.
Is it possible to fix this so that functions that have side effects or are time
consuming to compute are called only once?
John
On Feb 28, 2011, at 6:08 PM, John wrote:
>
> That doesn't really make any sense. I'm not calling "fun" from the tcl body,
> just in the select statement. It didn't make any difference either.
>
> Any Ideas?
>
> John
>
> On Feb 28, 2011, at 8:49 AM, Black, Michael (IS) wrote:
>
>> I think your problem may be that fun() in your eval is being called both
>> from tcl and from sqlite.
>> Name your proc fun2 and the problem will probably go away.
>>
>>
>> Michael D. Black
>> Senior Scientist
>> NG Information Systems
>> Advanced Analytics Directorate
>>
>>
>>
>> ________________________________________
>> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
>> behalf of John [j...@rkroll.com]
>> Sent: Sunday, February 27, 2011 8:37 PM
>> To: General Discussion of SQLite Database
>> Subject: EXT :[sqlite] "select disctict" calles tcl functions twice?
>>
>> Hi All,
>>
>> In my example code here the function "fun" is called twice for each row.
>> The first set of calls is made in the initial evaluation of the sql
>> statement, then "fun" is called again as the tcl body is evaluated for each
>> row. I wasn't expecting this.
>>
>> Thanks,
>>
>> John
>>
>> ------------------------
>> lappend auto_path /home/john/lib
>>
>> package require sqlite3
>>
>> sqlite3 db data.db
>>
>>
>> proc fun {} {
>> puts "Fun Called"
>> incr ::count
>> }
>> db function fun fun
>>
>> db eval { select distinct *, fun() as H from T } T { puts "$T(x), $T(H)" }
>>
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
