php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #67537 Incorrect result on nested grouped statement
Submitted: 2014-06-29 12:43 UTC Modified: 2014-06-29 13:40 UTC
From: cpuidle at gmx dot de Assigned:
Status: Closed Package: PDO MySQL
PHP Version: 5.5.14 OS: Win7 32bit
Private report: No CVE-ID: None
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: cpuidle at gmx dot de
New email:
PHP Version: OS:

 

 [2014-06-29 12:43 UTC] cpuidle at gmx dot de
Description:
------------
Values returned by PDO for MIN/MAX query are wrong compared to actual mysql output. Either a bug in PDO or the MySQL libraries used by PDO?

Test script:
---------------
$pdo = new PDO($dsn, $user, $pass);
$pdo->query('drop table test');
$pdo->query('create table test (i int not null auto_increment, ts bigint(20), primary key (i))');
$pdo->query('insert into test (ts) value (3600000),(10800000),(14400000)');

$sql = '
SELECT
	MIN(agg.prev_ts),
	MAX(agg.prev_ts),
	LEAST(MIN(agg.prev_ts),	MAX(agg.prev_ts)),
	MAX(agg.ts) - MIN(agg.prev_ts)
FROM (
	SELECT ts, @row:=@row+1 AS row,
		IF (@prev_ts != 0, @prev_ts, NULL) AS prev_ts,
		@prev_ts := ts
	FROM test
	CROSS JOIN (SELECT @prev_ts := 0, @row := 1) AS vars
	ORDER BY ts ASC
) AS agg
GROUP BY (row DIV 3)
ORDER BY ts ASC
';
foreach($pdo->query($sql, \PDO::FETCH_ASSOC) as $row) {
	if ($row['MIN(agg.prev_ts)']) print_r($row); // show 2nd row only
}

Expected result:
----------------
Array
(
    [MIN(agg.prev_ts)] => 3600000
    [MAX(agg.prev_ts)] => 10800000
    [LEAST(MIN(agg.prev_ts),    MAX(agg.prev_ts))] => 3600000
    [MAX(agg.ts) - MIN(agg.prev_ts)] => 10800000
)

Actual result:
--------------
Array
(
    [MIN(agg.prev_ts)] => 10800000
    [MAX(agg.prev_ts)] => 3600000
    [LEAST(MIN(agg.prev_ts),    MAX(agg.prev_ts))] => 10800000
    [MAX(agg.ts) - MIN(agg.prev_ts)] => 3600000
)

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2014-06-29 13:40 UTC] cpuidle at gmx dot de
-Status: Open +Status: Closed
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Wed Dec 04 20:01:30 2024 UTC