Bug #67537 [NEW]: Incorrect result on nested grouped statement
| From: | cpuidle at gmx dot de | Date: | Sun, 29 Jun 2014 12:43:39 +0000 |
| Subject: | Bug #67537 [NEW]: Incorrect result on nested grouped statement | ||
| Groups: | php.bugs | ||
| Request: | Send a blank email to php-bugs+get-186371@lists.php.net to get a copy of this message | ||
From: cpuidle at gmx dot de
Operating system: Win7 32bit
PHP version: 5.5.14
Package: PDO MySQL
Bug Type: Bug
Bug description:Incorrect result on nested grouped statement
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 bug report at https://bugs.php.net/bug.php?id=67537&edit=1
--