Bug #67537 [Opn->Csd]: Incorrect result on nested grouped statement
Edit report at https://bugs.php.net/bug.php?id=67537&edit=1
ID: 67537
User updated by: cpuidle at gmx dot de
Reported by: cpuidle at gmx dot de
Summary: Incorrect result on nested grouped statement
-Status: Open
+Status: Closed
Type: Bug
Package: PDO MySQL
Operating System: Win7 32bit
PHP Version: 5.5.14
Block user comment: N
Private report: N
New Comment:
Most likely a MySQL issue rather than PDO according to http://stackoverflow.com/questions/24457442/how-to-find-previous-record-n-per-group-maxtimestamp-timestamp/24459821?noredirect=1#comment37884393_24459821
Previous Comments:
------------------------------------------------------------------------
[2014-06-29 12:43:38] 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
)
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=67537&edit=1
Thread (2 messages)