List:MySQL and Perl« Previous MessageNext Message »
From:Marc Lehmann Date:February 23 2012 3:27am
Subject:Re: bind parameter guessing corrupts results
View as plain text  
On Wed, Feb 22, 2012 at 05:05:46PM +0100, "Neubauer, Ralf"
<Ralf.Neubauer@stripped> 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:

> 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
mysql uses the types of both operands instead.

> I assume that is what you do in your program, you store numbers in a
> string column and
> want them to be compared as a number. If you do this (e.g. if you use
> VARCHAR(255) as a
> 'generic' column, which can be very useful), you have to cast indeed,
> but in this case
> you can't use indexes also, because you are hiding the real data type
> from the database.

The column in question is:

| mtime | int(10) unsigned                                   | NO   | MUL | 0

and the key is not used for e.g. '1329967147' when quoted, which mysql
does.  It doesn't quote all numbers, which is why this problem went
undetected until the numbers became "large".

> Not quoting numbers would be more dangerous, I think. The only

Well, in many versions of DBD::mysql this behaviour is the default (also

What is dnagerous is the constant changing of the heuristic - the current
code quotes _some_ numbers (if they are "big") but not others for example.

> DBD::mysql would have to parse the Statement and determine the column
> types, so this
> is left to MySQL, which seems to behave reasonable.

Or, alternatively, it could use the hints provided by the programmer.

> > 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.
> This would be consistent in itself, but not consistent to perl's
> duck-typing.
> In my experience all data you get from DBI comes as strings, so if you
> use
> data from one statement to parametrize another one, you have to add 0 to
> all
> numbers to get it right. The same happens when you read CSV files or
> user input.

That is better than the current behaviour, which randomly quotes some but
not all numbers, and the exact behaviour is not even documented.

The only alternative is to switch off the guessing altogether and use
bind_param calls for every single paramater, which is what my code is now

This is of course very slow and also tedious, so it would be nice to specify
the types by more perlish means.

But I cna live with almost anything, but the constant switching of
heuristics is a real issue, and breaks existing programs.

> > 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.
> See above. Use *CHAR() for numeric types and you have to cast. Otherwise
> not.

It would be great if this were actually implemented, but the current code
is data-dependent and quotes some numbers but not others.

                The choice of a       Deliantra, the free code+content MORPG
      -----==-     _GNU_    
      ----==-- _       generation
      ---==---(_)__  __ ____  __      Marc Lehmann
      --==---/ / _ \/ // /\ \/ /      schmorp@stripped
      -=====/_/_//_/\_,_/ /_/\_\
bind parameter guessing corrupts resultsMarc Lehmann15 Feb
  • RE: bind parameter guessing corrupts resultsRalf Neubauer22 Feb
    • Re: bind parameter guessing corrupts resultsMarc Lehmann23 Feb
      • AW: bind parameter guessing corrupts resultsGisbert W. Selke28 Feb
        • Re: AW: bind parameter guessing corrupts resultsMarc Lehmann29 Feb