From: Marc Lehmann
Date: February 13 2012 3:01am
Subject: bind parameter guessing corrupts results
List-Archive: http://lists.mysql.com/perl/4418
Message-Id: <20120213030134.GA6111@schmorp.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Hi!

We today upgraded DBD::mysql to 4.020, and some of our scripts started to
work very slowly.

The reason was quickly found to be an integer that was stringified in the
current version of DBD::mysql.

In old versions of DBD::mysql, one could get reliable behaviour by using e.g.

   $num*1   # for numbers
   "string" # for strings

it seems in current versions of DBD::mysql there is some autoguessing
going on, which seems to be wrong in many cases, and doesn't seem to be
documented at all.

Worse, whoeveer wrote the documentation seems to think this is a
performance issue in mysql only, however, this is a correctness issue, and
in our case, this even corrupts the results.

For example, in (my-)sql, the following statements do not have the same
semantics:

   col < 10
   col < "10"

For col = 5, the first is true, the second is false, and an update statement
using newer DBD::mysql would update the wrong rows, apart from potentially
being slower.

Looking at the changes, the type of guessing applied seems to change every
so-and-so many releases, which is, I think, rather hazardous.

Could there be a mode implemented that goes by the deduced type of the
perl scalar (e.g. SvPOKp => string, otherwise number)? That works reliably
if the user is consistent in the arguments he/she supplied.

That would have the advantage of being stable, and easy to use from the perl
side, without relying on guesswork by the mysql driver.

If I read the dbdimp code correctly, right now, you either always get
"string", or you randomly get a string or a number, depending on how the
string looks like.

The means you can choose between always wrong with numbers, or being wrong
only for some numbers or some strings.

The only way to correctly use numbers with current DBD::mysql seems to be
to always use a cast expression on the sql level, which seems like total
overkill.

-- 
                The choice of a       Deliantra, the free code+content MORPG
      -----==-     _GNU_              http://www.deliantra.net
      ----==-- _       generation
      ---==---(_)__  __ ____  __      Marc Lehmann
      --==---/ / _ \/ // /\ \/ /      schmorp@stripped
      -=====/_/_//_/\_,_/ /_/\_\