Bug #3290

DB_QueryTool_Query::getCount() method return zero when using an offset

Details

Submitted	2005-01-25 06:20 UTC
From	kubo at isite dot co dot jp
Assigned	quipo
Status	Closed
Package	DB_QueryTool
PHP Version	Irrelevant
OS	All
Roadmaps	(Not assigned)

Comments

[2005-01-25 06:20 UTC] kubo at isite dot co dot jp

Description:
------------
DB_QueryTool_Query::getCount() method return zero when using offset.
At least MySQL and PostgreSQL return empty set using the query as follows.

MySQL:
SELECT
COUNT(*)
FROM
person
LIMIT 25, 25
;

PostgreSQL:
SELECT
COUNT(*)
FROM
person
LIMIT 25
OFFSET 25
;

However if the offset is 0, MySQL and PostgreSQL return correct count of the query.

MySQL:
SELECT
COUNT(*)
FROM
person
LIMIT 0, 25
;

PostgreSQL:
SELECT
COUNT(*)
FROM
person
LIMIT 25
OFFSET 0
;

I hope that getCount() method always return correct count regardless of offset value.

Reproduce code:
---------------
I wrote ad hoc patches.

o DB_QueryTool
http://iteman.typepad.jp/blog/files/DB_QueryTool-0.11.1-isCalledViaGetCount.patch

o MDB_QueryTool
http://iteman.typepad.jp/blog/files/MDB_QueryTool-0.11.1-isCalledViaGetCount.patch

[2005-01-27 02:06 UTC] kubo at isite dot co dot jp

OK, the reproducing script is here.

----------------------------------------------------------------------
<?php
/* Example Table Definition for MySQL

DROP TABLE IF EXISTS person;

CREATE TABLE person (
person_id int(11) NOT NULL DEFAULT '0' auto_increment,
last_name varchar(255) NOT NULL,
first_name varchar(255) NOT NULL,
rdate datetime NOT NULL,
mdate timestamp NOT NULL,
PRIMARY KEY(person_id)
);

INSERT INTO person (last_name, first_name, rdate) VALUES ('Last Name 1', 'First Name 1', CURRENT_TIMESTAMP);
INSERT INTO person (last_name, first_name, rdate) VALUES ('Last Name 2', 'First Name 2', CURRENT_TIMESTAMP);
INSERT INTO person (last_name, first_name, rdate) VALUES ('Last Name 3', 'First Name 3', CURRENT_TIMESTAMP);
INSERT INTO person (last_name, first_name, rdate) VALUES ('Last Name 4', 'First Name 4', CURRENT_TIMESTAMP);
INSERT INTO person (last_name, first_name, rdate) VALUES ('Last Name 5', 'First Name 5', CURRENT_TIMESTAMP);
INSERT INTO person (last_name, first_name, rdate) VALUES ('Last Name 6', 'First Name 6', CURRENT_TIMESTAMP);
INSERT INTO person (last_name, first_name, rdate) VALUES ('Last Name 7', 'First Name 7', CURRENT_TIMESTAMP);
INSERT INTO person (last_name, first_name, rdate) VALUES ('Last Name 8', 'First Name 8', CURRENT_TIMESTAMP);
INSERT INTO person (last_name, first_name, rdate) VALUES ('Last Name 9', 'First Name 9', CURRENT_TIMESTAMP);
INSERT INTO person (last_name, first_name, rdate) VALUES ('Last Name 10', 'First Name 10', CURRENT_TIMESTAMP);
*/

require_once('MDB.php');
require_once('MDB/QueryTool.php');
$dbh = &MDB::singleton(array('phptype' => 'mysql',
'username' => 'hoge',
'password' => 'hoge',
'hostspec' => 'hoge',
'database' => 'hoge'),
array('persistent' => true,
'autofree' => true,
'debug' => false)
);
$queryTool = &new MDB_QueryTool();
$queryTool->setDbInstance($dbh);
$queryTool->setTable('person');

$queryTool->setSelect('
person_id,
last_name,
first_name,
UNIX_TIMESTAMP(rdate) AS rdate,
UNIX_TIMESTAMP(mdate) AS mdate
');

$queryTool->setLimit(0, 5);
$list = $queryTool->getAll();
$count = $queryTool->getCount();

print "A: Expected count is 10.\n";
print 'Actual count is ' . $queryTool->getCount() . ".\n";
var_dump($list);

$queryTool->reset();
$queryTool->setSelect('
person_id,
last_name,
first_name,
UNIX_TIMESTAMP(rdate) AS rdate,
UNIX_TIMESTAMP(mdate) AS mdate
');

$queryTool->setLimit(5, 5);
$list = $queryTool->getAll();

print "B: Expected count is 10.\n";
print 'Actual count is ' . $queryTool->getCount() . ".\n";
var_dump($list);
?>

----------------------------------------------------------------------
original version output is:

A: Expected count is 10.
Actual count is 10.
array(5) {
[0]=>
array(5) {
["person_id"]=>
string(1) "1"
["last_name"]=>
string(11) "Last Name 1"
["first_name"]=>
string(12) "First Name 1"
["rdate"]=>
string(10) "1106790464"
["mdate"]=>
string(10) "1106790464"
}
[1]=>
array(5) {
["person_id"]=>
string(1) "2"
["last_name"]=>
string(11) "Last Name 2"
["first_name"]=>
string(12) "First Name 2"
["rdate"]=>
string(10) "1106790464"
["mdate"]=>
string(10) "1106790464"
}
[2]=>
array(5) {
["person_id"]=>
string(1) "3"
["last_name"]=>
string(11) "Last Name 3"
["first_name"]=>
string(12) "First Name 3"
["rdate"]=>
string(10) "1106790464"
["mdate"]=>
string(10) "1106790464"
}
[3]=>
array(5) {
["person_id"]=>
string(1) "4"
["last_name"]=>
string(11) "Last Name 4"
["first_name"]=>
string(12) "First Name 4"
["rdate"]=>
string(10) "1106790464"
["mdate"]=>
string(10) "1106790464"
}
[4]=>
array(5) {
["person_id"]=>
string(1) "5"
["last_name"]=>
string(11) "Last Name 5"
["first_name"]=>
string(12) "First Name 5"
["rdate"]=>
string(10) "1106790464"
["mdate"]=>
string(10) "1106790464"
}
}
B: Expected count is 10.
Actual count is 0.
array(5) {
[0]=>
array(5) {
["person_id"]=>
string(1) "6"
["last_name"]=>
string(11) "Last Name 6"
["first_name"]=>
string(12) "First Name 6"
["rdate"]=>
string(10) "1106790464"
["mdate"]=>
string(10) "1106790464"
}
[1]=>
array(5) {
["person_id"]=>
string(1) "7"
["last_name"]=>
string(11) "Last Name 7"
["first_name"]=>
string(12) "First Name 7"
["rdate"]=>
string(10) "1106790464"
["mdate"]=>
string(10) "1106790464"
}
[2]=>
array(5) {
["person_id"]=>
string(1) "8"
["last_name"]=>
string(11) "Last Name 8"
["first_name"]=>
string(12) "First Name 8"
["rdate"]=>
string(10) "1106790464"
["mdate"]=>
string(10) "1106790464"
}
[3]=>
array(5) {
["person_id"]=>
string(1) "9"
["last_name"]=>
string(11) "Last Name 9"
["first_name"]=>
string(12) "First Name 9"
["rdate"]=>
string(10) "1106790464"
["mdate"]=>
string(10) "1106790464"
}
[4]=>
array(5) {
["person_id"]=>
string(2) "10"
["last_name"]=>
string(12) "Last Name 10"
["first_name"]=>
string(13) "First Name 10"
["rdate"]=>
string(10) "1106790464"
["mdate"]=>
string(10) "1106790464"
}
}

----------------------------------------------------------------------
A: Expected count is 10.
Actual count is 10.
array(5) {
[0]=>
array(5) {
["person_id"]=>
string(1) "1"
["last_name"]=>
string(11) "Last Name 1"
["first_name"]=>
string(12) "First Name 1"
["rdate"]=>
string(10) "1106790464"
["mdate"]=>
string(10) "1106790464"
}
[1]=>
array(5) {
["person_id"]=>
string(1) "2"
["last_name"]=>
string(11) "Last Name 2"
["first_name"]=>
string(12) "First Name 2"
["rdate"]=>
string(10) "1106790464"
["mdate"]=>
string(10) "1106790464"
}
[2]=>
array(5) {
["person_id"]=>
string(1) "3"
["last_name"]=>
string(11) "Last Name 3"
["first_name"]=>
string(12) "First Name 3"
["rdate"]=>
string(10) "1106790464"
["mdate"]=>
string(10) "1106790464"
}
[3]=>
array(5) {
["person_id"]=>
string(1) "4"
["last_name"]=>
string(11) "Last Name 4"
["first_name"]=>
string(12) "First Name 4"
["rdate"]=>
string(10) "1106790464"
["mdate"]=>
string(10) "1106790464"
}
[4]=>
array(5) {
["person_id"]=>
string(1) "5"
["last_name"]=>
string(11) "Last Name 5"
["first_name"]=>
string(12) "First Name 5"
["rdate"]=>
string(10) "1106790464"
["mdate"]=>
string(10) "1106790464"
}
}
B: Expected count is 10.
Actual count is 10.
array(5) {
[0]=>
array(5) {
["person_id"]=>
string(1) "6"
["last_name"]=>
string(11) "Last Name 6"
["first_name"]=>
string(12) "First Name 6"
["rdate"]=>
string(10) "1106790464"
["mdate"]=>
string(10) "1106790464"
}
[1]=>
array(5) {
["person_id"]=>
string(1) "7"
["last_name"]=>
string(11) "Last Name 7"
["first_name"]=>
string(12) "First Name 7"
["rdate"]=>
string(10) "1106790464"
["mdate"]=>
string(10) "1106790464"
}
[2]=>
array(5) {
["person_id"]=>
string(1) "8"
["last_name"]=>
string(11) "Last Name 8"
["first_name"]=>
string(12) "First Name 8"
["rdate"]=>
string(10) "1106790464"
["mdate"]=>
string(10) "1106790464"
}
[3]=>
array(5) {
["person_id"]=>
string(1) "9"
["last_name"]=>
string(11) "Last Name 9"
["first_name"]=>
string(12) "First Name 9"
["rdate"]=>
string(10) "1106790464"
["mdate"]=>
string(10) "1106790464"
}
[4]=>
array(5) {
["person_id"]=>
string(2) "10"
["last_name"]=>
string(12) "Last Name 10"
["first_name"]=>
string(13) "First Name 10"
["rdate"]=>
string(10) "1106790464"
["mdate"]=>
string(10) "1106790464"
}
}