On Wed, Oct 16, 2013 at 12:03 PM, Clemens Ladisch <clem...@ladisch.de>wrote:
> Dominique Devienne wrote:
> > select * from some_table where some_column in (...)
> >
> > 2) In other places we synthesize the query text by splicing list.join(",
> ")
> > in the in (list) where clause.
> >
> > Both solutions are unsatisfactory, because ... 2) constantly reparse and
> > prepare queries, which can get super long if the array to "bind" is big.
> >
> > Any chance SQLite would add true array binding?
>
> The compiled statement depends on the number of elements, so SQLite
> would have to reprepare anyway:
>
But isn't that a consequence of the fact that a in (list) where clause is
necessary bounded and known at parse time?
The same way I can manually transform the in (list) into a join to a temp
table, so can the query optimizer.
It already uses hidden intermediary result-sets for query processing, and
the array would basically be one such internal (anonymous) hidden
"result-set".
The difference with the manual transform-into-join code I'm forced to do
now is that SQLite wouldn't have to name the table and column to create the
temp table, fill it, using, drop it, etc... These activities trigger
authorizer hooks, trace hooks, change the (temp) schema, etc... (i.e. a
bunch of DDL and DML statements) while SQLite itself, would it support
array binding, would generate none of that monitored statement activity.
Notice that I'm inquiring about array-binding for in (list) only, not for
putting into table cells, not selecting them, not joining on them, etc...
I'd love to be able to do that, but that's a different can of worms
entirely.
Thanks, --DD
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
