[Haskell-cafe] Weird behaviour with positional parameters in HDBC-mysql

[Martijn van Steenbergen]

Dear café (CC John and Chris),

I'm having some trouble with getting positional parameters in HDBC-mysql 
to work. Most of the time they work fine, but sometimes (and often 
enough that it's a serious bother) the parameters don't reach the server 
correctly.

Let me first describe my setup:
* Ubuntu 8.10 intrepid (inside VMWare Fusion on Mac OS X Snow Leopard)
* mysql  Ver 14.12 Distrib 5.0.67, for debian-linux-gnu (i486) using 
readline 5.2
* ghc 6.10.3
* HDBC-2.2.4
* HDBC-mysql-0.6.1

I have a database with a single, empty table:

> CREATE TABLE `foo` (
>   `id` int(11) NOT NULL auto_increment,
>   PRIMARY KEY  (`id`)
> ) ENGINE=MyISAM AUTO_INCREMENT=1031 DEFAULT CHARSET=latin1

And I have the following Haskell program:

> import Database.HDBC
> import Database.HDBC.MySQL
> import Data.List
> import Data.Traversable
> import Control.Monad
>
> main :: IO ()
> main = do
>   conn <- connectMySQL defaultMySQLConnectInfo
>     { mysqlUnixSocket = "/var/run/mysqld/mysqld.sock"
>     , mysqlUser = "root"
>     , mysqlDatabase = "db"
>     }
>   for [1..1000] $ \n -> do
>     let sql = "select id from foo where id in ("
>           ++ intercalate "," (replicate (fromInteger n) "?") ++ ")"
>     stmt <- prepare conn sql
>     execute stmt (map SqlInteger [1..n])
>     finish stmt
>   return ()

The program produces no output. All 1000 queries that are sent to the 
database are expected to return 0 results, since table foo is empty. 
This is all fine.

The problem is in the queries that the server receives. If I turn on 
full logging, I expect all queries to have a "where id in (...)" where 
there is an increasing list of integers within the parentheses. But when 
examining the logs, some are small negative numbers, NULLs or just other 
numbers:

> 506 Execute     [980] select id from foo where id in
> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,
> 27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,824,825,826,827,828,829,
> 830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847,
> 848,849,850,851,852,853,854,855,856,857,858,859,860,861,862,863,864,865,
> 866,867,868,869,870,871,872,873,874,875,876,877,878,879,880,881,882,883,
> 884,885,886,887,888,889,890,891,892,893,894,895,896,897,898,899,900,901,
> 902,903,904,905,906,907,908,0,0,34359738368,-5210566582833489576,NULL,
> NULL,0,34359738368,-5210565758199768552,NULL,NULL,0,34359738368,
> -5210564933566047528,NULL,NULL,0,34359738368,-5210564108932326504,NULL,
> NULL,0,34359738368,-5210563284298605480,NULL,NULL,0,34359738368,
> -5210562459664884456,NULL,NULL,158,159,160,161,162,(snip)

The server then faithfully executes the statement and returns the 
results expected for the query it reports to have received, which (in a 
table with actual data) is not what the Haskell program was expecting.

If I compile the program once, the results vary per run.
If I don't use positional parameters but instead render the full SQL 
query myself, there is no problem.

Is this a problem in HDBC or in HDBC-mysql?
What's going on here? How do I fix or work around this?

Many thanks,

Martijn.