Bug Report

Database cache problems with WHERE statements
Date:	08/06/2008	Severity:	Minor
Status:	Resolved	Reporter:	maxez
Version:	1.6.3
Keywords:	Libraries, Database Class
Forum Thread:	Not reported in the forum.

Description

When where statements are being cached and mixed with other live where statements, concatenation breaks after the first database execution (there is a missing “AND” on the concatenation), even when the second command is the exact same as the first one. Please see code for clear example. Obviously to reproduce, you will have to use your own table names/fields for your schema.

Code Sample

//Demonstration of cache error
        $this->db->start_cache();
        $this->db->where(array('c.created>='=>'2008-3-1', 'c.created<='=>'2008-6-1'));
        $this->db->stop_cache();
        
        //THIS WORKS
        $this->db->select_sum('c.amount', 'total');
        $this->db->from('transactions c');
        $this->db->where("c.type","Payment");
        $query = $this->db->get();
        print $this->db->last_query();
        //RETURNS: SELECT SUM(c.amount) AS `total` FROM (`transactions` c) WHERE c.created>= '2008-3-1' AND c.created<= '2008-6-1' AND c.type = 'Payment'

        //EXACT SAME COMMAND EXECUTED AGAIN, DOESN'T WORK. MISSING 'AND' IN WHERE STATEMENT!
        $this->db->select_sum('c.amount', 'total');
        $this->db->from('transactions c');
        $this->db->where("c.type","Payment");
        $query = $this->db->get();
        print $this->db->last_query();
        //RETURNS: SELECT SUM(c.amount) AS `total` FROM (`transactions` c) WHERE c.type = 'Payment' c.created>= '2008-3-1' AND c.created<= '2008-6-1' 

Expected Result

The cache engine should render the same where statement, regardless of number of executions.

Actual Result

Where statements break after first DB execution of a cached WHERE statement.

Comment on Bug Report

Page 1 of 1 pages

Posted by: maxez on 6 August 2008 7:09pm

I found the problem and fixed it. The problem is twofold:

- The function _merge_cache (DB_active_rec.php:1651) was merging the arrays with the live where statement FIRST, and the cached where statement LAST. This is inverted since the cached WHERE statement has been declared first. It also brought the problem that obviously the cached statement didn’t had the “AND” prefix since it was declared before anything else. Line 1659 of this function has been changed to:

$this->$ar_item = array_unique(array_merge($this->$ar_cache_item, $this->$ar_item));

- Also, when building the prefix string on the function _where (DB_active_rec.php:419), the amount of items on the cache variable (ar_cache_where) weren’t being taken into consideration, so a missing AND resulted. The like 428 was changed to this to correct the problem:

$prefix = (count($this->ar_where) == 0 && count($this->ar_cache_where) == 0) ? ‘’ : $type;

_where_in was fixed by changing line 578 to:
$prefix = (count($this->ar_where) == 0 && count($this->ar_cache_where) == 0) ? ‘’ : $type;

_like was fixed by changing line 702 to:
$prefix = (count($this->ar_like) == 0 && count($this->ar_cache_like) == 0) ? ‘’ : $type;

_having was fixed by changing line 841 to:
$prefix = (count($this->ar_having) == 0 && count($this->ar_cache_having) == 0) ? ‘’ : $type;

***** WARNING *****
I have only tested the WHERE functionality fix successfully. The other fixes should work though since the same methodology is used to cache/concatenate the statements. ALSO, PLEASE BE AWARE THAT THIS DEFECT MIGHT AFFECT *ALL* CACHED STATEMENTS. I ONLY CACHE WHERE AND THAT IS THE EXTENT OF MY TEST. For example, if you cache a SELECT statement, and later you have a live select statement added, the following MAY occur:

SELECT value article (missing comma).

You might want to restest the cache functionality thoroughly.

Thank you!
Max