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

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)
Subscription	Your email:

Comments Add Comment Add patch

Anyone can comment on a bug. Have a simpler test case? Does it work for you on a different platform? Let us know! Just going to say 'Me too!'? Don't clutter the database with that please !

Your email address: MUST BE VALID
Solve the problem : 12 + 19 = ?

[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!