headers
Marc Lehmann | 13 Feb 2012 04:01
Picon
Favicon

bind parameter guessing corrupts results

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.
(Continue reading)

Permalink | Reply |
headers
Neubauer, Ralf | 22 Feb 2012 17:05
Picon

RE: bind parameter guessing corrupts results

Hi!

> -----Original Message-----
> From: Marc Lehmann [mailto:schmorp <at> schmorp.de]
> Sent: Monday, February 13, 2012 4:02 AM

> 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.

I just tried it (with 5.5.10 and 5.1.44):

select t.*,
  i < 15, s < 15, i < '15', s < '15'
from (
  select i, concat(i) s
  from (
    select 1 i union select 2 union select 3 union select 4 union select
5 union select 6
    union select 7 union select 8 union select 9 union select 10 union
select 11
(Continue reading)

Permalink | Reply |
headers
Marc Lehmann | 23 Feb 2012 04:27
Picon
Favicon

Re: bind parameter guessing corrupts results

On Wed, Feb 22, 2012 at 05:05:46PM +0100, "Neubauer, Ralf" <Ralf.Neubauer <at> wido.bv.aok.de> wrote:
> > 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.

First, thanks for replying, although the way you format your e-mail makes
me dizzy reading it - couldn't you set your mua to a fixed line width
instead of alternating long and short lines?

> As you can see, everything is ok. Quoted numbers are converted to
> numbers by MySQL, if

No, the rules are much more complicated:
http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html

> the column they are compared with is numeric. On the other hand, if the
> column is a
> string and the constant is a number, the column is forced to be a number
> (which can be a

No, the only column types for which there are magical behaviours are
TIMESTAMP and DATETIME. And this also doesn't work for IN. In reality
(Continue reading)

Permalink | Reply |
headers
Selke, Gisbert W. | 28 Feb 2012 22:20
Picon

AW: bind parameter guessing corrupts results

> -----Ursprüngliche Nachricht-----
> Von: Marc Lehmann [mailto:schmorp <at> schmorp.de]
> Gesendet: Donnerstag, 23. Februar 2012 04:28
> It would be great if this were actually implemented, but the current code
> is data-dependent and quotes some numbers but not others.
Same, btw, for Gisle Aas's data::Dump -- it quotes some numbers but not others (unless you use quote(), in
which case it consistently quotes all numbers).
That's the price you pay for dwimmyness: sometimes it bites you. Especially when you least expect it.

\Gisbert

--

-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/perl
Permalink | Reply |
headers
Marc Lehmann | 29 Feb 2012 11:28
Picon
Favicon

Re: AW: bind parameter guessing corrupts results

On Tue, Feb 28, 2012 at 10:20:34PM +0100, "Selke, Gisbert W." <Gisbert.Selke <at> wido.bv.aok.de> wrote:
> > -----Ursprüngliche Nachricht-----
> > Von: Marc Lehmann [mailto:schmorp <at> schmorp.de]
> > Gesendet: Donnerstag, 23. Februar 2012 04:28
> > It would be great if this were actually implemented, but the current code
> > is data-dependent and quotes some numbers but not others.
> Same, btw, for Gisle Aas's data::Dump -- it quotes some numbers but not others (unless you use quote(), in
which case it consistently quotes all numbers).
> That's the price you pay for dwimmyness: sometimes it bites you. Especially when you least expect it.

Well, the solution I outlined has the advantage of being dwim but at
the same time doesn't suffer from arbitraryness - that is, there is a
consistent way of marking numbers as numbers, even with perl.

So your argument is wrong - the problem is not dwim here.

The problem is the arbitraryness - even if perl thinks something is a
number and doesn't even have alternatives, dbd::mysql would still quote it
if it becomes too big, and this behaviour isn't even clearly documented.

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

--

-- 
MySQL Perl Mailing List
(Continue reading)

Permalink | Reply |

Gmane