Active record bug? Update() disregards limit(). The value set by limit() is then transfered to a next query in another p

[eluser]Maarten B[/eluser]
Dear all,

I think I hit a bug in CodeIgniter 1.5.3 and 1.5.4.

When I use DB Active Record's update() function in combination with the limit() function, the limit() part is not added to the SQL query.
When I then use the get() function after the before mentioned update() function, the limit() part is added to the SQL query that get() generates, even though I didn't set limit() for the get().

Example:

Code:

$this->CI->db->set("last_page_datetime", date("Y-m-d H:i:s"));
$this->CI->db->set("last_page_ip", $this->CI->input->ip_address());
$this->CI->db->where("user_id", $user_id);
$this->CI->db->limit(1); //setting this will remain the limit for the next query. Huh?!?!?!?!?!?!
$this->CI->db->update("users");
echo $this->CI->db->last_query();

Expected result from echo statement:

Code:

UPDATE users SET last_page_datetime = '2007-09-21 22:30:12', last_page_ip = '127.0.0.1'
WHERE user_id = '2' LIMIT 1

Actual result:

Code:

UPDATE users SET last_page_datetime = '2007-09-21 22:30:12', last_page_ip = '127.0.0.1'
WHERE user_id = '2'

The error: the limit clause is missing.

Now the get():

Code:

$this->CI->db->order_by('lastname', 'ASC');
$query = $this->CI->db->get('users');
echo $this->CI->db->last_query();

Expected result from echo statement:

Code:

SELECT * FROM users ORDER BY lastname ASC

Actual result:

Code:

SELECT * FROM users ORDER BY lastname ASC LIMIT 1

The error: the limit clause from the previous query is added.

Is this a bug? Any thought/reactions are very welcome.
BTW, I saw one other thread which also reported a missing LIMIT in an update query (see second post in that thread).

TIA,


Maarten

P.S. These functions are used in a self written library, hence the "$this->CI->db->...".

[eluser]Maarten B[/eluser]
It gets even weirder.

The update() function of Active Record is used in one page (page A), the get() function in a consecutive page (B). The limit() part in page A is omitted in the query to the database, and on page B the limit() part is added by CI to the get() function in page B...

It looks like the query cache doesn't clean up the limit() part.

Is there a function in CI that clears the query cache, so you are absolutely sure no "rests" of a before query is silently added to a new query? I can't find it in the user guide.

Thanks in advance,


Maarten

[eluser]Maarten B[/eluser]
WORKAROUND

The workaround is to add a

Code:

$this->db->limit(NULL);

AFTER your

Code:

$this->db->update();

query. In this way the limit part is deleted from CodeIgniter's internal "query cache".

I have been looking into the code of CI, and it seems there is a function (something with "write" in the function name, I don't remember exactly?) in db->update() that deletes the query cache after the query is sent to the database. This function does indeed not delete any limit part of the query cache.


Hope this helps someone.


Maarten