[Bug 5431] New: DD item count does not match returned items

[bugzilla-daemon (at) xaraya.com]

Please do not reply directly to this message. All additional comments should
be made in the comments box of this bug report.

http://bugs.xaraya.com/show_bug.cgi?id=5431

           Summary: DD item count does not match returned items
           Product: App - Core
           Version: Weekly Tarball
          Platform: All
        OS/Version: Linux
            Status: NEW
          Severity: normal
          Priority: P3 - Standard
         Component: DynamicData
        AssignedTo: mikespub (at) xaraya.com
        ReportedBy: judgej (at) xaraya.com


Xaraya Version:    1.0
PHP version:       4.3.10
WebServer:         Apache 2.0

This happens when DD uses the DD module to hold items for an object. It does 
not happen when using a dedicated table for the same object.

In particular, if a where-clause for a DD 'getitems' contains both 'and' 
and 'or' sections, then the itemcount will usually be higher than the action 
records returned.

For example, "( col1 = 1 or col2 = 2 ) and col3 = 3"

Reproducible: Always
Steps to Reproduce:
Without going into too many details, the where-clause looked like the 
following:

( title like '%international%' or summary like '%international%' or body 
like '%international%' ) and item_id = 8
Actual Results:  
The item select query generated by DD:

SELECT xar_dd_itemid , MAX(CASE WHEN xar_dd_propid = 303 THEN xar_dd_value 
ELSE '' END) AS dd_303 , MAX(CASE WHEN xar_dd_propid = 304 THEN xar_dd_value 
ELSE '' END) AS dd_304 , MAX(CASE WHEN xar_dd_propid = 307 THEN xar_dd_value 
ELSE '' END) AS dd_307 , MAX(CASE WHEN xar_dd_propid = 308 THEN xar_dd_value 
ELSE '' END) AS dd_308
FROM xar_dynamic_data
WHERE xar_dd_propid IN (303, 304, 308)
GROUP BY xar_dd_itemid
HAVING (dd_307 like '%international%' or dd_304 like '%international%' or 
dd_308 like '%international%') and (dd_303 = 8)

The item count query:

SELECT COUNT(DISTINCT xar_dd_itemid)
FROM xar_dynamic_data
WHERE (xar_dd_propid = 307 AND xar_dd_value like '%international%') or 
(xar_dd_propid = 304 AND xar_dd_value like '%international%') or 
(xar_dd_propid = 308 AND xar_dd_value like '%international%') and 
(xar_dd_propid = 303 AND xar_dd_value = 8) 

Notice some of the parenthesis has been lost from the count version of the 
query. Adding that parenthesis just results in a count of zero, since the 
query is simply not constructed in the right way for complex where-clauses (it 
needs grouping to handle both the ANDs and the ORs in one query).

Expected Results:  
I expectd the same number of rows from both queries. The first query returned 
1 item (correct) but the count was 2 (incorrect).

-- 
Configure bugmail: http://bugs.xaraya.com/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are on the CC list for the bug, or are watching someone who is.
_______________________________________________
Xaraya_bugs mailing list
Xaraya_bugs (at) xaraya.com
http://xaraya.com/mailman/listinfo/xaraya_bugs