| Package home | Report new bug | New search | Development Roadmap | Status: Open | Feedback | All | Closed Since Version 1.1.2 |
| Bug #3290 | DB_QueryTool_Query::getCount() method return zero when using an offset | ||
|---|---|---|---|
| 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) | ||
[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
Comments
[2005-01-26 12:29 UTC] quipo
Was that query generated by [M]DB_QueryTool?
Can you send me a reproducing script, please?
TIA
[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"
}
}
[2005-01-27 22:32 UTC] quipo
This bug has been fixed in CVS.
In case this was a documentation problem, the fix will show up at the
end of next Sunday (CET) on pear.php.net.
In case this was a pear.php.net website problem, the change will show
up on the website in short time.
Thank you for the report, and for helping us make PEAR better.
--
many thanks for your patch and reproducing script!