Document the "reduce duplicates" checkbox side-effects on the UI, to avoid nasty "bugs"

For record - the symptoms: After I converted my views from Views 1, adjusted them, and finally put my upgraded 6.x site online, I ran into a mysterious bug: I have a lot of CCK fields, listed as a table in my view, with exposed filters on these same fields available. On certain fields, when I choose more than one option in the exposed filter (dropdown - cck fields with allowed values, and "is one of" filter), it seemingly finds only posts with the first selected value, plus some posts with that field "unpopulated". But however, after closer look it turns out, that the search is correct - but any value except the first one from the searched filter list shows as empty field, hiding the actual data.

Overall analysis and proposal: This "bug" gave me a hard time to track down across the (very complex and hard-to search) Views and CCK code, and after hacking several places in both to make it work, I finally discovered the source - which is not a bug at all. It's a nasty side effect of a tiny misconfiguration - namely the "reduce duplicates" filter setting used on a field, which is actually not a multiple-values field. As I learned in code, this setting is only compatible with a limited set of fields supporting it in code, which is really not obvious on the UI - especially on CCK fields, where the compatible/incompatible criterion is actually a tiny setting hidden somewhere in CCK (whether the field accepts multiple values or not).

I propose to add a little warning to the description of the checkbox, so that people have a clue which side-effects might be caused by that. People might be thinking just like I did - struggling with duplicates, so setting all the DISTINCT and similar flags everywhere, not getting scared by the performance warning (it was an admin-screen only), and really not expecting a mere DISTINCT (as I understood that setting before) to break the actual display so badly.

Background - my analysis of the problem (or: How I got from UI back to UI, hunting for a non-existent bug, because UI didn't warn me):
The query is built in a completely different way with the "reduce duplicates" setting. Instead of a single JOIN to the CCK data table with a condition some_field IN('first value', 'second value'), it becomes something like this:

SELECT node.nid AS nid, ....other fields....,
node_data_field_myfield_value_0.field_myfield_value AS node_data_field_myfield_value_0_field_myfield_value
FROM drup_node node
LEFT JOIN drup_content_type_content_myccktype node_data_field_myfield_value_0 ON node.vid = node_data_field_myfield_value_0.vid AND node_data_field_myfield_value_0.field_myfield_value = 'first_filter_selection'
LEFT JOIN drup_content_type_content_myccktype node_data_field_myfield_value_1 ON node.vid = node_data_field_myfield_value_1.vid AND node_data_field_myfield_value_1.field_myfield_value = 'second_filter_selection'
....further joins....
WHERE ....various where conditions....  AND (
  node_data_field_myfield_value_0.field_myfield_value = 'first_filter_selection' OR
  node_data_field_myfield_value_1.field_myfield_value = 'second_filter_selection')
ORDER BY ....a filed.... DESC
LIMIT 0, 20

The root of the problem with missing values in display is, that each of the values selected in exposed filter is searched by a separate condition on a separate JOIN, but the really SELECTed filed only comes from the first one. If any other of the selected values match, the query finds the node right, but the value for this field is NULL, as it comes from the first LEFT JOIN which didn't match. (I don't really see how this works in relation to reducing duplicates, but I suppose it does somehow, and that's not the point here.)

After digging a lot, I finally found the place where the JOINs are added (really difficult to search for these OOP functions, where grep doesn't help due to may functions with the same name everywhere, and var_dump() kills both php and browser by infinite loops in Views' objects references), and after many attempts to hack that, I finally learned how this form of the query is optional, and so followed the caller's path back to CCK views' handlers. After analyzing that code, and comparing it to Taxonomy handlers where it seems to work fine, I learned that certain handlers expect this problem, and fix it by fetching the actual data for the field again, in their pre_render function. So after all, the "reduce duplicates" requires explicit support in field handlers code, making it only compatible with a limited set of fields.

After another long hours of hacking CCK, which seemed to just ignore the situation, and skip the pre_render data fixing part (being even successfull to hack it temporarily to show correct display), I finally discovered that it's all triggered by a mismatch between the "reduce duplicates" setting on the view, and "accept multiple values" setting on the CCK field - I completely forgot, that this one field (created back on 4.7.x, and long untouched) is an exception on the site, and doesn't accept multiple values. That makes the "reduce duplicates" setting technically unneeded, but I didn't really care, as performance wasn't a concern on this one view, and I never imagined before, how such a thing might break things.

So, in the end, I fixed it all by just reviewing all my CCK fields for the multiple values setting, and unchecking "reduce duplicates" on the view accordingly. Too easy in the end - but it costed me WAAAAAY too much work and time to discover this relation of two seemingly unrelated settings, by hunting for a nasty non-existent bug. So I think it's worth it, to add a small mention to the checkbox description, to alert people about the fact that this one might be the culprit in similar situations.