On Sun, Nov 13, 2005 at 07:30:58AM -0500, [EMAIL PROTECTED] wrote:
> Nathan Kurz <[EMAIL PROTECTED]> wrote:
> >
> > SELECT uid, match("complex", "function", vector) AS match FROM vectors
> > ORDER BY match DESC LIMIT 20;
>
> SELECT uid, mx FROM
> (SELECT uid, match(...) AS mx FROM vectors LIMIT -1)
> ORDER BY mx DESC LIMIT 20;
>
> The LIMIT -1 on the subquery is to fake out the optimizer and prevent
> it from folding the subquery back into the main query, resulting in the
> same statement you started with. A "LIMIT -1" is effectively a no-op.
> It does no limiting. But subqueries that contain a limit will not be
> folded into outer queries that also contain a limit.
Thanks! I would not have thought to try that on my own. It does
indeed prevent the double function call, but unfortunately makes the
rest of the query more expensive by creating an extra temp table.
>From what I can tell about the VDBE execution in both cases:
SELECT function() AS func FROM table ORDER BY func DESC LIMIT 10;
-> cycle through every row calling function() twice per row
-> put results directly into an sorted temp table
SELECT func FROM (SELECT function() AS func FROM table LIMIT -1)
ORDER BY func DESC LIMIT 10;
-> only calls function() once per row
-> puts results into temp table 1 (unsorted)
-> inserts all rows from temp table 1 into sorted temp table 2
Is there any way to combine the best of both of these worlds: only
calling function() once but only creating one temp table? Or is it
the case (as it seems) that SQLite only copies the expression for the
function() when parsing an AS and never goes back to determine that
the expression has already been solved for that row?
Or even better, is there any way to write a user defined function that
could do the ordering and limiting internally to reduce the data set
early? I suppose I could do it as a aggregate function that returns a
text string and then reparse that into a second query using IN, but it
would wonderful if there was a way to 'explode' the return value of a
function into multiple rows. Something like:
SELECT function_returning_multiple_rows(id, vector, limit) FROM vectors;
> Hint: The output of EXPLAIN is much easier to read if you do ".explain"
> first to set up the output formatting.
Thanks for the hint! It does indeed.
--nate
ps. A couple Pathological cases that superficially look like bugs
because of this double execution of the function call. Are
these bugs, features, or just the way things currently are?
sqlite> SELECT random(1) AS func FROM test ORDER BY func DESC LIMIT 5;
func
----
-141
7787
-823
1453
-654
sqlite> SELECT random() AS func FROM test WHERE func > 10 LIMIT 5;
func
----
-853
5973
-232
-217
9849
pps. Despite any apparent complaints, I'm really enjoying SQLite.
