PDO Prepared Statement: Fetch NULL instead of the expected Result

i don't get the Problem. I have in PHP a SQL Query. If i fire the query via PDO and prepared statements, but without placeholders, i get my expected result. But the product ID in the query comes from outside, so i will replace it with a placeholder :productID. If I do this, i get NULL as a result.

SQL Query:

SELECT 
COUNT(`stock`.`product`) AS COUNT,
`product`.`name_en` AS NAME,
`product`.`default_consumption` AS `DEFAULT_CONSUM`
FROM `stock`
RIGHT JOIN `product`
ON `stock`.`product` = `product`.`id`
WHERE `product` = :productId

If I replace the placeholder :productId with a valid ID in the database table (for example 1) i get my expected result.

$query = "SELECT 
          COUNT(`stock`.`product`) AS COUNT,
          `product`.`name_en` AS NAME,
          `product`.`default_consumption` AS `DEFAULT_CONSUM`
          FROM `stock`
          RIGHT JOIN `product`
          ON `stock`.`product` = `product`.`id`
          WHERE `product` = 1";
$query = $this->db->prepare($query);
$query->execute();
$response = $query->fetch(PDO::FETCH_ASSOC);
var_dump($response);

Output

array(3) {
  ["COUNT"]=>
  string(1) "0"
  ["NAME"]=>
  string(6) "butter"
  ["DEFAULT_CONSUM"]=>
  string(3) "0.1"
}

Now i will replace it:

$query = "SELECT 
          COUNT(`stock`.`product`) AS COUNT,
          `product`.`name_en` AS NAME,
          `product`.`default_consumption` AS `DEFAULT_CONSUM`
          FROM `stock`
          RIGHT JOIN `product`
          ON `stock`.`product` = `product`.`id`
          WHERE `product` = :productId";

$query = $this->db->prepare($query);
$query->execute(array(':productId' => $productId));
$response = $query->fetch(PDO::FETCH_ASSOC);
var_dump($response);

Output

array(3) {
  ["COUNT"]=>
  string(1) "0"
  ["NAME"]=>
  NULL
  ["DEFAULT_CONSUM"]=>
  NULL
}

$productId is a value from outside. In my test i set it to 1, like in my first example. I get the result NULL for the to fields DEFAULT_CONSUM and name If i set the ID 1 direct in the execute():

$query->execute(array(':productId' => 1));

I get NULL again. If i replace the :productId in the query again to the direct ID of 1, i get the espected result again. I don't get the problem at the moment.

I work with PHP 8.0 and MariaDB. The code above runs in a seperated function. There is in the moment no other code executed.

Thanks for your help. I sure, i'm overlooking something.

CREATE TABLE

CREATE TABLE`product`(
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID of the Product',
    `name_en` VARCHAR(255) NOT NULL COMMENT 'Name of the Product in Englisch',
    `name_de` VARCHAR(255) NULL COMMENT 'Name of the Product in German',
    `name_fr` VARCHAR(255) NULL COMMENT 'Name of the Product in France',
    `group` INT(10) UNSIGNED NULL DEFAULT 1 COMMENT 'ID of the Product Group',
    `unit` INT(10) UNSIGNED NOT NULL DEFAULT 1 COMMENT 'Id of the Unit',
    `default_consumption` FLOAT(10) UNSIGNED NOT NULL DEFAULT 0.5 COMMENT 'Value for Default Consumption',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp of creation Time',
    `changed_at` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'Timestamp of changed',
    `deleted_at` TIMESTAMP NULL COMMENT 'Timestamp of deleted',
    PRIMARY KEY(`id`),
    INDEX `product_name_en_idx`(`name_en`),
    INDEX `product_name_de_idx`(`name_de`),
    INDEX `product_name_fr_idx`(`name_fr`),
    INDEX `product_group_idx`(`group`),
    CONSTRAINT `product_group_fk` 
        FOREIGN KEY(`group`)
        REFERENCES `product_group`(`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL, 
    CONSTRAINT `product_unit_fk` 
        FOREIGN KEY(`unit`)
        REFERENCES `unit`(`id`)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
)ENGINE=InnoDB;

CREATE TABLE `stock`(
    `product` INT(10) UNSIGNED NOT NULL COMMENT 'ID of the Product',
    `user` INT(10) UNSIGNED NOT NULL COMMENT 'ID of the User',
    `stock` FLOAT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Stock Value',
    `individual_consumption` FLOAT(10) UNSIGNED NULL COMMENT 'Individual Consumption Value',
    `ki_value` FLOAT(10) UNSIGNED NULL DEFAULT 0.000 COMMENT 'KI Up Down Value',
    `last_buy` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Timestmap of last Buy',
    INDEX `stock_product_idx`(`product`),
    INDEX `stock_user_idx`(`user`),
    CONSTRAINT `stock_product_fk`
        FOREIGN KEY(`product`)
        REFERENCES `product`(`id`)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT `stock_user_fk`
        FOREIGN KEY(`user`)
        REFERENCES `user`(`id`)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
)ENGINE=InnoDB;

Solution

It looks like you found a bug with the MariaDB SQL optimizer. Please file a bug report with them at your earliest convenience.

The issue is not related to PDO. The reason why you see a different result is that PDO binds all values as strings by default. When you bind it as string then the comparison in the WHERE clause is evaluated properly. When you cast the value to an integer or you use literal 1 then the optimizer compares it to product.id instead of stock.product. Not really sure what is internally happening here, but clearly the behaviour is wrong when using an integer. You could ask on https://dba.stackexchange.com/ to see if someone has an explanation for this behaviour, but most likely this needs to be addressed by MariaDB team as a bug.

Here is an online reproducible version of this bug.