[DB-SIG] Parameter substitution and "IN" operator

[Gerhard Häring]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Alex Willmer wrote:
> On Tue, 2008-03-18 at 00:14 +0100, Gerhard Häring wrote:
>> This is not the way SQL parameter binding works. SQL parameter binding
>> only works with scalars (*). If tuples seem to work, then that's just
>> a side-effect of the implementation of your particular DB-API module.
> 
>> >From here, it seems like native SQL parameter binding for tuples is a
> good thing. Do relational databases not implement it merely because no
> one has written the code yet, or is it missing from the SQL standard/a
> bad idea in principle?

It's missing from the SQL standard. And it's not implemented because
it would make the main idea of parameter binding useless: reusing
query plans.

I agree that in theory the "SQL-to-virtual-machine compiler" on the
database backends could be smarter like this, but it would probably
add too much complexity.

If you really really need the functionality with "IN" and want native
parameter binding, you can fake it using temporary tables.

Pseudo-code:

<fill table tmp_table>
select ... from table1 where col1 in (select col2 from tmp_table).

or

<fill table tmp_table>
select ... from table1 where exists (select 1 from tmp_table where
col2=table1.col1)

The EXISTS version is better with some databases (certainly it was for
Oracle 8, which just "cut off" values in "IN" clauses if you had more
than 255 or something).

- -- Gerhard
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFH3wpzdIO4ozGCH14RAmPuAJ9QNEgiBU4pcKqTc+k3/MBTe5pFBgCfS5oA
VRizXimLdIj6hExBIvaOObI=
=Cj32
-----END PGP SIGNATURE-----