DBD::SQLite Placeholder for integer value in HAVING clause

Question

Since similar statements using DBD::mysql seem to work fine, where is my mistake in using a placeholder for an integer in a HAVING clause, when using DBD::SQLite as DBI driver?

#!/usr/bin/perl

use 5.012;
use warnings;

use DBI;

my $dbh = DBI->connect("dbi:SQLite:dbname=/tmp/test.sqlite","","", {
    RaiseError => 1,
    sqlite_allow_multiple_statements => 1,
});

$dbh->do( <<'EOT' );
CREATE TABLE cd (
    id    INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT
);

CREATE TABLE artist (
    id   INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    cd   INTEGER,
    FOREIGN KEY (cd) REFERENCES cd (id)
);

INSERT INTO cd (title) VALUES ('foo');
INSERT INTO cd (title) VALUES ('bar');

INSERT INTO artist (name, cd) VALUES ('max fob', 1);
INSERT INTO artist (name, cd) VALUES ('max baz', 1);
EOT

my $sth1 = $dbh->prepare(<<'EOT');
   SELECT cd.title
     FROM cd
LEFT JOIN artist ON artist.cd = cd.id
    WHERE artist.name LIKE ?
 GROUP BY cd.title
   HAVING count( artist.cd ) = 2
EOT

my $sth2 = $dbh->prepare(<<'EOT');
   SELECT cd.title
     FROM cd
LEFT JOIN artist ON artist.cd = cd.id
    WHERE artist.name LIKE ?
 GROUP BY cd.title
   HAVING count( artist.cd ) = ?
EOT

$sth1->execute('max%');
# says 'hit'
say 'sth1: hit' if $sth1->fetch;

$sth2->execute('max%', 2);
# stays silent
say 'sth2: hit' if $sth2->fetch;

Thank you, DDL.

Answer 1

I don't know why the placeholder is treated as a string^[1], but you can get the code to work by making SQLite treat the value as an integer. Both of the following solutions are means of achieving this:

my $sth2 = $dbh->prepare(<<'EOT');
   SELECT cd.title
     FROM cd
LEFT JOIN artist ON artist.cd = cd.id
    WHERE artist.name LIKE ?
 GROUP BY cd.title
   HAVING count( artist.cd ) = ?
EOT

$sth2->bind_param(1, 'max%');
$sth2->bind_param(2, 2, DBI::SQL_INTEGER);
$sth2->execute();

my $sth2 = $dbh->prepare(<<'EOT');
   SELECT cd.title
     FROM cd
LEFT JOIN artist ON artist.cd = cd.id
    WHERE artist.name LIKE ?
 GROUP BY cd.title
   HAVING count( artist.cd ) = CAST( ? AS INTEGER )
EOT

$sth2->execute('max%', 2);

---

1. The other answer claims that placeholders are always treated as strings. Not only is this not an explanation, it's not even true. This unbacked claim of theirs is obviously false since bind_param can cause placeholders to be treated as something other than a string.