SQLite Forum

LIKE optimization - limitation to string literals
> Why is an expression yielding a constant string not acceptable?

I would suspect that this is because there is no way to examine that "constant string" at compile (prepare) time in order to determine whether or not an index will be useful.  

Since this cannot be determined at compile (prepare) time, the statement must be prepared so that it can work under all conditions -- meaning that a scan of all rows must be performed.

Changing this would require that the compile (prepare) be able to "run" the code generating the RHS so that the value can be examined to determine if an index will be useful.  Prepare (compile) does not run (execute) the code it is generating.

The only thing that the code generator could do would be to generate bytecode handling both paths and dynamically choose which one to use at execution time.  

While this would solve the issue you are seeing, you could also solve it by simply passing in the RHS directly and not diddling it in the dark.  Although the prepare could be modified to generate output that works most efficiently even when diddling in the dark occurs, this would make the resulting VDBE program *less efficient* in all cases except in cases where the RHS has been diddled in the dark.

So the question really boils down to whether to have *every* other user of SQLite3 pay for you wanting to "diddle in the dark" whenever the mood strikes, or whether you should merely "diddle in the light" in which case the issue does not exist for anyone.