pdo_mysql error "Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause"

[francoisjacquet]

Description

I am trying to use the pdo_mysql PHP extension with emulation of prepared statements disabled.

The code is equivalent to the following:

$db_connection = new PDO(
    $prefix . ':dbname=' . $DatabaseName . ';host=' . $DatabaseServer . ';port=' . $DatabasePort . ';charset=utf8mb4',
    $DatabaseUsername,
    $DatabasePassword,
    [
        // @link https://www.php.net/manual/en/pdo.setattribute.php
        PDO::ATTR_EMULATE_PREPARES => false,
        PDO::ATTR_ERRMODE => PDO::ERRMODE_SILENT,
        PDO::ATTR_CASE => PDO::CASE_UPPER,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_STRINGIFY_FETCHES => true,
    ]
);

$sql = "SELECT AVG(CUM_WEIGHTED_GPA) AS CUM_WEIGHTED_GPA,
    AVG(UNWEIGHTED_GPA) AS CUM_UNWEIGHTED_GPA
    FROM transcript_grades
    WHERE SYEAR='2023'
    AND SCHOOL_ID='1'
    AND MARKING_PERIOD_ID='4'";

$stmt = $db_connection->query( $sql );

if ( $stmt )
{
    $stmt->execute();

    var_dump( $stmt->errorInfo() );
}

The following error is returned:

42000 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

Here are the SQL modes set in MariaDB 10.11.6:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

The same query is fine when PDO::ATTR_EMULATE_PREPARES => true or when run from the MariaDB console.

Please note, the SQL query is not a prepared statement.

Please also note, transcript_grades is a VIEW.

I am filing a bug report as I received no answer on DBA stackexchange. Please bear with me if this is not an actual driver bug.

PHP Version

PHP 8.3.10

Operating System

Debian bookworm

[francoisjacquet]

The PDO::query() function both prepares and executes an SQL statement.

So there is no need to call the PDOStatement::execute() function, and the following line should be removed from the code in the message above:

$stmt->execute();