DBD::SQLite Placeholder for integer value in HAVING clause-CodePudding

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.

CodePudding user response：

I don't know why it doesn't work as-is, but both of the following work:

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);

CodePudding user response：

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.