Re: Query Planner GROUP BY and HAVING clauses optimization ?

Thank you Dominic and Keith for your replies

The reason i asked this is that i have a query in wich one condition 
filtering the recordset involves
an UDF and this UDF needs to be processed after all table filters have 
been applied

Illustration :

additionnal table :

CREATE TABLE t2 (
a TEXT PRIMARY KEY,
b INTEGER,
c REAL);

UDF :
proc CallUDF {key} {
    if{[dict exists $myDict $key]} {
       dict get $myDict $key  /* returns a value stored in myDict */
    } else {error}
}
db function CallUDF -deterministic CallUDF

If i do :

SELECT *
FROM t1 CROSS JOIN t2
WHERE t1.b > 10
   AND t2.a = t1.a and t2.c < 0
   AND CallUDF(t2.a) <> 0

Even though "CROSS JOIN" garanties that t2 is traversed after t1 has 
been filtered on "t1.b > 10",
it seems there is no garanty when t2 is traversed, that the filter 
CallUDF(t2.a) <> 0 is evaluated *after*
filters "t2.b = t1.b and t2.c < 0".

In this case the sole solution we found is :

SELECT *
FROM t1, t2
WHERE t1.b > 10
   AND t2.a = t1.a and t2.c < 0
GROUP BY t2.a
HAVING CallUDF(t2.a) <> 0

Thank again
Jean-Baptiste
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users