DBD::SQLite Placeholder for integer value in HAVING clause

user_18171916 0
Viewed 136

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.

dbi prepared-statement perl casting sqlite
2 Answers

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

A ? placeholder is always replaced in the prepared statement by a string (like '2'), which for SQLite has no affinity.

From Datatypes In SQLite/Affinity Of Expressions is derived that an expression like count(artist.cd), although it is documented that it returns an integer, has no affinity.

Also, from Datatypes In SQLite/Type Conversions Prior To Comparison is derived that 2 operands with no affinity are compared without any implicit data type conversion.

For SQLite the expression 2 = '2' returns false.

This is why you must apply data type conversion to the ? placeholder to convert it to a numeric value, which you can do with:

HAVING count( artist.cd ) = ? + 0

Also, not related to your problem, the WHERE clause in your query makes the LEFT join behave like an INNER join, because it is applied to a column of the right table and filters out any non matching rows.

user_104988280
Related