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
View Add Comment Developer Edit
Anyone can comment on a bug. Have a simpler test case? Does it work for you on a different platform? Let us know!
Just going to say 'Me too!'? Don't clutter the database with that please !
Your email address:
MUST BE VALID
Solve the problem:
23 + 18 = ?
Subscribe to this entry?

 
 [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

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2014-06-29 13:40 UTC] cpuidle at gmx dot de
-Status: Open +Status: Closed
 
PHP Copyright © 2001-2019 The PHP Group
All rights reserved.
Last updated: Sun Sep 15 10:01:26 2019 UTC