Download & Extend
Views » Issues

Provide workaround for core DISTINCT bug in db_rewrite_sql

Posted by neclimdul on June 25, 2009 at 3:17am
Project:Views
Version:6.x-3.x-dev
Component:Code
Category:bug report
Priority:normal
Assigned:Unassigned
Status:closed (duplicate)

Issue Summary

Views is commonly hit by a bug in db_rewrite_sql covered in the Core bug #284392: db_rewrite_sql causing issues with DISTINCT.

Rather than have each module using views implementing a workaround, this patch provides a workaround in views to deal with the problem.

I think it needs to look to see the base table is node still but it seems to work.

AttachmentSize
views_node_access_distinct_bypass.patch620 bytes
Login or register to post comments

Comments

#1

Posted by neclimdul on June 25, 2009 at 3:29am

Try 2 with a base_table check.

AttachmentSize
views_node_access_distinct_bypass.patch 684 bytes

#2

Posted by neclimdul on June 25, 2009 at 4:00pm
Status:active» needs review

Fixed bug where false wasn't set when set_distinct was called and an access module was present. Better docs.

AttachmentSize
views_node_access_distinct_bypass.patch 946 bytes

#3

Posted by neclimdul on June 25, 2009 at 4:44pm

Accidently fudged the parameters of the outer if at some point. replace them.

AttachmentSize
views_node_access_distinct_bypass.patch 864 bytes

#4

Posted by KarenS on June 25, 2009 at 5:43pm
Status:needs review» reviewed & tested by the community

Looks good to me!! I'll be happy to see this nasty bug get a work-around :)

#5

Posted by merlinofchaos on June 25, 2009 at 5:50pm
Version:6.x-2.x-dev» 6.x-3.x-dev
Status:reviewed & tested by the community» needs work

Committed to 2.x. We're going to need a reroll against the 3.x branch.

#6

Posted by dereine on June 26, 2009 at 5:24am
Status:needs work» needs review

here is a fast rerole

AttachmentSize
views_node_access_distinct_bypass.patch 1.18 KB

#7

Posted by igama on June 27, 2009 at 7:05pm
Version:6.x-3.x-dev» 6.x-2.x-dev

I have:
Latest Drupal 6
Views 6.x-2.x-dev 2009-Jun-26
Workflow 6.x-1.1

I had the SQL error when I would try and activate the Distinct ( SELECT DISTINCT(node.nid) AS DISTINCT(node.nid)....) , and then I updated to the latest dev View and now I get no error, but the duplicates are still there.

There is no "DISTINCT(node.nid)" in the SQL.

SELECT node.nid AS nid,
node_data_field_image.field_image_fid AS node_data_field_image_field_image_fid,
node_data_field_image.field_image_list AS node_data_field_image_field_image_list,
node_data_field_image.field_image_data AS node_data_field_image_field_image_data,
node.type AS node_type,
node.vid AS node_vid,
term_data.name AS term_data_name,
term_data.vid AS term_data_vid,
term_data.tid AS term_data_tid,
node.title AS node_title,
vocabulary.name AS vocabulary_name,
node_revisions.body AS node_revisions_body,
node_revisions.format AS node_revisions_format,
node.sticky AS node_sticky,
node_data_field_date.field_date_value AS node_data_field_date_field_date_value,
DATE_FORMAT((FROM_UNIXTIME(node.created) + INTERVAL 28800 SECOND), '%Y%m%d%H%i') AS node_created_minute
FROM node node
LEFT JOIN workflow_node workflow_node ON node.nid = workflow_node.nid
LEFT JOIN content_field_image node_data_field_image ON node.vid = node_data_field_image.vid
LEFT JOIN term_node term_node ON node.vid = term_node.vid
LEFT JOIN term_data term_data ON term_node.tid = term_data.tid
LEFT JOIN vocabulary vocabulary ON term_data.vid = vocabulary.vid
LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid
LEFT JOIN content_field_date node_data_field_date ON node.vid = node_data_field_date.vid
WHERE (node.type in ('equipamento')) AND (node.status <> 0 OR (node.uid = ***CURRENT_USER*** AND ***CURRENT_USER*** <> 0) OR ***ADMINISTER_NODES*** = 1) AND (workflow_node.sid = 4)
ORDER BY node_sticky DESC, node_data_field_date_field_date_value DESC, node_created_minute DESC, node_title ASC

#8

Posted by merlinofchaos on June 28, 2009 at 8:21am
Version:6.x-2.x-dev» 6.x-3.x-dev

From the help text on the distinct setting:

This will make the view display only distinct items. If there are multiple identical items, each will be displayed only once. You can use this to try and remove duplicates from a view, though it does not always work.

Please actually read stuff before you post and change the status of issues around. It doesn't always work. It says so. There's nothing I can do about it. If DISTINCT doesn't do the trick then you need a new approach.

#9

Posted by stormsweeper on June 29, 2009 at 4:17pm

#7: With that query, if you have more than one term or file on a node you will get multiple rows for that node, and each will be distinct as far as the db is concerned.

#10

Posted by merlinofchaos on June 30, 2009 at 5:21pm
Status:needs review» fixed

Committed to 3.x line too. Thanks!

#11

Posted by System Message on July 14, 2009 at 5:30pm
Status:fixed» closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

#12

Posted by agentrickard on September 18, 2009 at 6:39pm
Status:closed (fixed)» active

I think 6.14 may have broken this. See #284392: db_rewrite_sql causing issues with DISTINCT, comments after #154.

#13

Posted by bl444137 on September 18, 2009 at 11:09pm

Patch for Views 6.x-2.6.

AttachmentSize
views_node_access_distinct_bypass.patch 828 bytes

#14

Posted by bl444137 on September 19, 2009 at 3:05am

A cleaner implementation of #13. Patch for Views 6.x-2.6.

#15

Posted by bl444137 on September 19, 2009 at 3:08am

A cleaner implementation of #13. Patch for Views 6.x-2.6.

AttachmentSize
views_node_access_distinct_bypass.patch 645 bytes

#16

Posted by mathias2711 on September 19, 2009 at 1:43pm

It works!!! very nice work..

The weekend can start!

#17

Posted by Frank Steiner on September 21, 2009 at 12:56pm

Works fine for the problen with 6.14 and -2.x.

#18

Posted by izmeez on September 21, 2009 at 2:43pm

subscribe

#19

Posted by merlinofchaos on September 21, 2009 at 4:16pm

I need a patch that works with both 6.13 and 6.14 -- because I can't guarantee that anybody will be running a particular version.

#20

Posted by merlinofchaos on September 21, 2009 at 4:17pm

Actually: Please post the patch in http://drupal.org/node/579892 instead and return this to its original state.

#21

Posted by agentrickard on September 21, 2009 at 4:46pm

6.14 was a security release, so we can force people to be on that version, yes?

#22

Posted by izmeez on September 21, 2009 at 7:42pm

I applied the patch in #15 to the latest 6.x-2.x-dev version and it has solved the problems of duplicates and triplicates in Drupal 6.14

Thanks,

Izzy

#23

Posted by bl444137 on September 22, 2009 at 1:50am
Status:active» closed (fixed)

Closing this issue since these workarounds are for versions of Drupal before 6.14.

If you are using Drupal 6.14, please see Authors and only authors see dupplicate items after upgrading Drupal to 6.14 and db_rewrite_sql causing issues with DISTINCT.

#24

Posted by Damien Tournoud on September 23, 2009 at 7:09am
Status:closed (fixed)» active

I do not get the patch that went in (#3): why *in hell* do you prevent distinct for being set if the base table is not node?

#25

Posted by R.Hendel on September 27, 2009 at 11:25am

subscribe

#26

Posted by Ole Martin on October 10, 2009 at 3:35pm

And where to put this patch ?

#27

Posted by merlinofchaos on November 2, 2009 at 10:54pm

DamZ is right. It should prevent distinct from being set if the base table is 'node'.

The original patch here needs to be reverted.

#28

Posted by merlinofchaos on November 2, 2009 at 11:05pm

Original patch reverted from 2.x and 3.x versions.

#29

Posted by DanielTheViking on November 12, 2009 at 7:01pm

Subscribing.

#30

Posted by Damien Tournoud on November 12, 2009 at 7:06pm
Status:active» closed (duplicate)

This is now taken care of in #579892: Authors and only authors see duplicate items after upgrading Drupal to 6.14.

nobody click here