Update Fri May 30 08:29:59 CEST 2008: I have been fooled by MySQL's Type Conversion in Expression Evaluation:
Comparisons that use floating-point numbers (or values that are converted to floating-point numbers) are approximate because such numbers are inexact. This might lead to results that appear inconsistent: 
mysql> SELECT '18015376320243458' = 18015376320243458;
        -> 1
mysql> SELECT '18015376320243459' = 18015376320243459;
        -> 0

[download]
This underlines the importance of using 
$sth->bind_param(1, $n, { TYPE => SQL_INTEGER });

[download]
rather than 
$sth->bind_param(1, $n);

[download]
when $n is a number, otherwise the value will be treated as a string and there may be conversion errors.

Update Wed May 28 18:35:30 CEST 2008: This is most likely a bug in MySQL server on x86_64.

The environment: 

perl, v5.8.8 built for x86_64-linux-thread-multi
DBI is up to date (1.603).
DBD::mysql is up to date (4.006).
MySQL Server version: 5.0.33 Source distribution
Linux 2.6.9-42.ELsmp x86_64 x86_64 x86_64 GNU/Linux

[download]
I have a MySQL table defined as below, intended for 19-digit account numbers taken from a financial institution. 
create table foo(i decimal(23,0) primary key);

[download]
I have noticed that for the first of the two following statements, the subsequent $sth->execute() will return 0 rows for certain 19-digit values of $n, while the second will return 1 row: 
$sth->bind_param(1, $n);
$sth->bind_param(1, $n, { TYPE => SQL_INTEGER });

[download]
Interestingly, the difference disappears when the MySQL table is defined as 
create table foo(i bigint primary key);

[download]
Note that bigint can hold values up to 9223372036854775807 only (and thus useless to me since I cannot guarantee the values to be less than this limit). The test script: 
use strict;
use warnings;

use Test::More;
use Data::Dumper;
use DBI qw(:sql_types);
use ExtUtils::Installed;

my %modules = map { $_ => 1 } qw (DBI DBD::mysql);

my $instmod = ExtUtils::Installed->new();
foreach my $m ($instmod->modules()) {
  diag "$m: " . $instmod->version($m) . "\n" if $modules{$m};
}

my $dbh = undef;

sub delete_all {
  my $sth = $dbh->prepare(q{delete from foo})
    or die "prepare failed";
  my $retval = $sth->execute()
    or die "execute failed";
  return 1;
}

sub insert_range {
  my ($n, $i) = @_;
  my $sth = $dbh->prepare(q{insert into foo values (?)})
    or die "prepare failed";

  my $count = 0;
  foreach my $j (1 .. $i) {
    my $retval = $sth->execute($n + $j)
      or die "execute failed";
    $count++;
  }
  return $count;
}

sub dynamic_sql {
  my $n   = shift;
  my $sth = $dbh->prepare(qq{select i from foo where i=$n})
    or die "prepare failed";

  my $retval = $sth->execute()
    or die "execute failed";

  my $count = 0;
  $count++ while (my $row = $sth->fetchrow_arrayref());
  return $count;
}

sub bindparams_no_sql_type {
  my $n   = shift;
  my $sth = $dbh->prepare(q{select i from foo where i=?})
    or die "prepare failed";

  $sth->bind_param(1, $n);

  my $retval = $sth->execute()
    or die "execute failed";

  my $count = 0;
  $count++ while (my $row = $sth->fetchrow_arrayref());
  return $count;
}

sub bindparams_sql_type {
  my $n   = shift;
  my $sth = $dbh->prepare(q{select i from foo where i=?})
    or die "prepare failed";

  $sth->bind_param(1, $n, { TYPE => SQL_INTEGER });

  my $retval = $sth->execute()
    or die "execute failed";

  my $count = 0;
  $count++ while (my $row = $sth->fetchrow_arrayref());
  return $count;
}

# ------ main ------
my @args = qw (DBI:mysql:test foo bar);

$dbh = DBI->connect(@args)
  or die "connect failed";

my @data = ();

my $start = 6700000000000000000;
my $range = 1000;

plan tests => 2 + 3 * $range;

# ------ tests ------
cmp_ok(delete_all(), q{==}, 1, q{Expect delete_all to return 1});
cmp_ok(insert_range($start, $range), q[==], $range,
  qq[Expect $range insert(s)]);

for (1 .. $range) {
  my $i = $start + $_;
  cmp_ok(dynamic_sql($i), q{==}, 1, qq{Expect 1 row from dynamic_sql($
+i)});
  cmp_ok(bindparams_sql_type($i), q{==}, 1,
    qq{Expect 1 row from bindparams_sql_type($i)});
  cmp_ok(bindparams_no_sql_type($i),
    q{==}, 1, qq{Expect 1 row from bindparams_no_sql_type($i)});
}

__END__

[download]
The test output: 
$ prove bind_param.pl
bind_param.......# DBD::mysql: 4.006
# DBI: 1.603
bind_param.......2/3002
#   Failed test 'Expect 1 row from bindparams_no_sql_type(670000000000
+0000500)'
#   at bind_param.pl line 105.
#          got: 0
#     expected: 1

#   Failed test 'Expect 1 row from bindparams_no_sql_type(670000000000
+0000501)'
#   at bind_param.pl line 105.
#          got: 0
#     expected: 1

#   Failed test 'Expect 1 row from bindparams_no_sql_type(670000000000
+0000502)'
#   at bind_param.pl line 105.
#          got: 0
#     expected: 1

#   Failed test 'Expect 1 row from bindparams_no_sql_type(670000000000
+0000503)'
#   at bind_param.pl line 105.
#          got: 0
#     expected: 1

#   Failed test 'Expect 1 row from bindparams_no_sql_type(670000000000
+0000504)'
#   at bind_param.pl line 105.
#          got: 0
#     expected: 1

#   Failed test 'Expect 1 row from bindparams_no_sql_type(670000000000
+0000505)'
#   at bind_param.pl line 105.
#          got: 0
#     expected: 1

#   Failed test 'Expect 1 row from bindparams_no_sql_type(670000000000
+0000506)'
#   at bind_param.pl line 105.
#          got: 0
#     expected: 1

#   Failed test 'Expect 1 row from bindparams_no_sql_type(670000000000
+0000507)'
#   at bind_param.pl line 105.
#          got: 0
#     expected: 1

#   Failed test 'Expect 1 row from bindparams_no_sql_type(670000000000
+0000508)'
#   at bind_param.pl line 105.
#          got: 0
#     expected: 1

#   Failed test 'Expect 1 row from bindparams_no_sql_type(670000000000
+0000509)'
#   at bind_param.pl line 105.
#          got: 0
#     expected: 1

#   Failed test 'Expect 1 row from bindparams_no_sql_type(670000000000
+0000510)'
#   at bind_param.pl line 105.
#          got: 0
#     expected: 1

#   Failed test 'Expect 1 row from bindparams_no_sql_type(670000000000
+0000511)'
#   at bind_param.pl line 105.
#          got: 0
#     expected: 1

#   Failed test 'Expect 1 row from bindparams_no_sql_type(670000000000
+0000512)'
#   at bind_param.pl line 105.
#          got: 0
#     expected: 1
bind_param.......1563/? # Looks like you failed 13 tests of 3002.
bind_param....... Dubious, test returned 13 (wstat 3328, 0xd00)
 Failed 13/3002 subtests

Test Summary Report
-------------------
bind_param.pl (Wstat: 3328 Tests: 3002 Failed: 13)
  Failed tests:  1502, 1505, 1508, 1511, 1514, 1517, 1520
                1523, 1526, 1529, 1532, 1535, 1538
  Non-zero exit status: 13
Files=1, Tests=3002,  2 wallclock secs ( 0.82 usr  0.03 sys +  1.90 cu
+sr  0.14 csys =  2.89 CPU)
Result: FAIL
Failed 1/1 test programs. 13/3002 subtests failed.
$

[download]
By increasing the value of $range=10000, I can see that the following ranges are affected (and note that the results are reproducible, i.e. identical between successive runs): 
6700000000000000500 - 6700000000000000512
6700000000000001536 - 6700000000000002499
6700000000000002561 - 6700000000000003583
6700000000000004400 - 6700000000000004608
6700000000000005632 - 6700000000000005999
6700000000000006500 - 6700000000000007679
6700000000000008500 - 6700000000000008704
6700000000000009400 - 6700000000000009727

[download]
I have no explanation to why this affects only certain values. What do the values above have in common? Attempts to reproduce the same behavior and/or explain it are very welcome.

Update 2008-05-28 14:56:44: It's probably a bug in MySQL 5.0.33 that has been fixed in MySQL 5.0.51a:

I have reproduced the same error on MySQL 5.0.51b on x86_64. MySQL 5.0.51a on a i386 returns the expected 1 row for each of the select queries below, while MySQL 5.0.51b on a x86_64 does not: 

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.51b   | 
+-----------+
1 row in set (0.00 sec)

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> drop table if exists foo;
Query OK, 0 rows affected (0.00 sec)

mysql> create table foo(i decimal(23,0) primary key);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into foo values(6700000000000000498);
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo values(6700000000000000499);
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo values(6700000000000000500);
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo values(6700000000000000501);
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo values(6700000000000000502);
Query OK, 1 row affected (0.00 sec)

mysql> select i from foo where i=6700000000000000498;
+---------------------+
| i                   |
+---------------------+
| 6700000000000000498 | 
+---------------------+
1 row in set (0.00 sec)

mysql> select i from foo where i='6700000000000000498';
+---------------------+
| i                   |
+---------------------+
| 6700000000000000498 | 
+---------------------+
1 row in set (0.00 sec)

mysql> select i from foo where i=6700000000000000499;
+---------------------+
| i                   |
+---------------------+
| 6700000000000000499 | 
+---------------------+
1 row in set (0.00 sec)

mysql> select i from foo where i='6700000000000000499';
+---------------------+
| i                   |
+---------------------+
| 6700000000000000499 | 
+---------------------+
1 row in set (0.00 sec)

mysql> select i from foo where i=6700000000000000500;
+---------------------+
| i                   |
+---------------------+
| 6700000000000000500 | 
+---------------------+
1 row in set (0.00 sec)

mysql> select i from foo where i='6700000000000000500';
Empty set (0.00 sec)

mysql> select i from foo where i=6700000000000000501;
+---------------------+
| i                   |
+---------------------+
| 6700000000000000501 | 
+---------------------+
1 row in set (0.00 sec)

mysql> select i from foo where i='6700000000000000501';
Empty set (0.00 sec)

mysql> select i from foo where i=6700000000000000502;
+---------------------+
| i                   |
+---------------------+
| 6700000000000000502 | 
+---------------------+
1 row in set (0.00 sec)

mysql> select i from foo where i='6700000000000000502';
Empty set (0.00 sec)

[download]
--
No matter how great and destructive your problems may seem now, remember, you've probably only seen the tip of them. [1]

In reply to Typeless bind fails for certain large numbers with MySQL decimal by andreas1234567

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.