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 01:20 UTC Modified: 2005-01-27 17:32 UTC
From: kubo at isite dot co dot jp Assigned: quipo
Status: Closed Package: DB_QueryTool
PHP Version: Irrelevant OS: All
Roadmaps: (Not assigned)    
Subscription  


 [2005-01-25 01: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 07:29 UTC] quipo
Was that query generated by [M]DB_QueryTool? Can you send me a reproducing script, please? TIA
 [2005-01-26 21: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 17: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!