Using JDatabaseQuery several times in a row

Today I stumbled on a bug in CComment on Joomla 2.5 (we actually don't support 2.5, but that is another story) and upon investigating I discovered few interesting things and I thought that I'll share them with you. So here is the issue: in order to deliver nested comments in CComment we do 2 queries - one that fetches the parents and one that is supposed to fetch the children. It looks something like this:

$query->select('c.*, u.name AS user_realname, u.username AS user_username');
$query->from('#__comment AS c');
$query->leftJoin('#__users as u ON c.userid = u.id');
$query->where('contentid=' . $db->quote($contentId));
$query->where('component=' . $db->quote($component));

$query->order(CcommentHelperComment::getOrdering($settings));
$db->setQuery($query, $start, $limit);
$comments = $db->loadObjectList();
		
if ($tree)
{
	$query->clear('where');
	$query->clear('limit');
	$query->where('contentid=' . $db->quote($contentId));
	$query->where('component=' . $db->quote($component));
	$query->where('parentid>0');

	$query->clear('order');
	$query->order('id ASC');
}
	$db->setQuery($query);
 

/**
	 * Clear data from the query or a specific clause of the query.
	 *
	 * @param   string  $clause  Optionally, the name of the clause to clear, or nothing to clear the whole query.
	 *
	 * @return  JDatabaseQuery  Returns this object to allow chaining.
	 *
	 * @since   11.1
	 */
	public function clear($clause = null)
	{
		switch ($clause)
		{
			case 'select':
				$this->select = null;
				$this->type = null;
				break;
			case 'delete':
				$this->delete = null;
				$this->type = null;
				break;
			case 'update':
				$this->update = null;
				$this->type = null;
				break;
			case 'insert':
				$this->insert = null;
				$this->type = null;
				$this->autoIncrementField = null;
				break;
			case 'from':
				$this->from = null;
				break;
			case 'join':
				$this->join = null;
				break;
			case 'set':
				$this->set = null;
				break;
			case 'where':
				$this->where = null;
				break;
			case 'group':
				$this->group = null;
				break;
			case 'having':
				$this->having = null;
				break;
			case 'order':
				$this->order = null;
				break;
			case 'columns':
				$this->columns = null;
				break;
			case 'values':
				$this->values = null;
				break;
			default:
				$this->type = null;
				$this->select = null;
				$this->delete = null;
				$this->update = null;
				$this->insert = null;
				$this->from = null;
				$this->join = null;
				$this->set = null;
				$this->where = null;
				$this->group = null;
				$this->having = null;
				$this->order = null;
				$this->columns = null;
				$this->values = null;
				$this->autoIncrementField = null;
				break;
		}
		return $this;
	} 

On joomla 3 the clear function has a case for limit in the clear function so we end up with a correct query. Now the other funny thing that I discovered is the way the offset and limit parameters work on Joomla 2.5 and 3.x. On Joomla 2.5 if you you provide the offset and limit parameters to the setQuery function those are set on the JDatabase object. So, this way you could actually always rely on their values. If you did this:

// A query object
$query = $db->getQuery(true);
// build the query here
// ...
// ...

$db->setQuery($query, 0, 100);
$objects = $db->loadObjectList();

// modify the query in any way
// like $query->clear('select');

$db->setQuery($query, 0, 0);
$objects = $db->loadObjectList(); 

If the code was running on joomla 2.5 then the first query would return just 100 objects and the second query would return unlimited number(all the rows from the table) of objects. Now if you execute the same query on Joomla 3.x your second query won't return unlimited objects it will return just 100. Why is that? Here is the setQuery function on Joomla 3.x

/**
	 * Sets the SQL statement string for later execution.
	 *
	 * @param   mixed    $query   The SQL statement to set either as a JDatabaseQuery object or a string.
	 * @param   integer  $offset  The affected row offset to set.
	 * @param   integer  $limit   The maximum affected rows to set.
	 *
	 * @return  JDatabaseDriver  This object to support method chaining.
	 *
	 * @since   11.1
	 */
	public function setQuery($query, $offset = 0, $limit = 0)
	{
		$this->sql = $query;

		if ($query instanceof JDatabaseQueryLimitable)
		{
			if (!$limit && $query->limit)
			{
				$limit = $query->limit;
			}

			if (!$offset && $query->offset)
			{
				$offset = $query->offset;
			}

			$query->setLimit($limit, $offset);
		}
		else
		{
			$this->limit = (int) max(0, $limit);
			$this->offset = (int) max(0, $offset);
		}

		return $this;
	} 

​As you can see we have a if $query instanceof JDtabaseQueryLimitable, which is always the case on Joomla 3.x when you do $db->getQuery(true); So the first time you do setQuery and pass a limit, the setQuery function will set this limit on the query object. And the second time you execute the setQuery function will do if (!$limit && $query->limit) which since limit is 0 you can read like this !$limit = 0 = true && query->limit = true (it was set the last time we used the query to 100) so we end up with a true here. And our limit will end up being 100 instead of the intended 0.

The conclusion? If you are going to reuse your query object make sure that you also delete the limit from the query. Otherwise you can run into a surprise :)