user_relationships_load() return no result

I am encountering the same issue. The query can be solved by removing DISTINCT or changing USING ( rtid ) to ON ur.rtid = urt.rtid. The second option is a more similar query. Although I can't explain the difference, this simple changes fixes the problem and leaves the SQL essentially the same.

Here is my resulting query -

SELECT DISTINCT ur.rid,ur.*,urt.* FROM {user_relationships} ur INNER JOIN {user_relationship_types} urt ON ur.rtid = urt.rtid WHERE (ur.requester_id = %d OR ((ur.approved <> 1 OR urt.is_oneway <> 0) AND ur.requestee_id = %d)) AND %s.%s = %d

I would definitely say this is critical. I wonder if this is an issue with an exact MySQL version (I am using 5.1.53).

The D7 has already changed to ON because USING is not supported by SelectQuery.

They only indication I could find is that USING might not use the correct table to join on under some circumstances.

See I've got two way relationship that needs approval (typical case like "friends" on every possible social network).
"Remove relationship" button at some point calls

_user_relationships_generate_query($param, $options);
/*
where $param = array('rid' => 1898);
and $options = array('sort' => 'rid');
*/

And that leads to the query like this:

SELECT DISTINCT ur.rid, ur.*, urt.*
FROM user_relationships ur
INNER JOIN user_relationship_types urt
ON  ur.rtid = urt.rtid
WHERE ur.rid = 1898
AND (urt.is_oneway <> 0 OR ur.approved <> 1 OR ur.requester_id < ur.requestee_id)

Part that goes after AND in WHERE clause returns FALSE actually on every operator:

• it's two way
• it is approved
• requester_id is greater than requestee_id (which is the most weird to me, I mean why we even check for this?)

And the relationship object is never created which makes impossible to remove a friend.
The condition (urt.is_oneway <> 0 OR ur.approved <> 1 OR ur.requester_id < ur.requestee_id) comes from issue #479486: "/relationships" page doesn't show all users in relationship but it brakes work-flow of such a typical case like I described above (two way "friends" with approval).

I got bit by this, too -- a call to user_relationships_load with a "user" argument failed to return anything, which thoroughly pooched my use of user_relationships_node_access. @erikwebb's patch in #2 worked fine (so far, anyway...); thanks much!

Closed #1282484: u_r_node_access blocking non-followers, but not allowing access to followers as a duplicate of this issue.

Looks like #2 just needs to be re-rolled git style for the testbot to pass.

Here is a re-roll the test bot should like.

ih2502mk just so I'm clear you began experiencing the problem you describe after applying this patch?

Change looks good and as I said before, that's what I already did in D7 anyway.

Committed to 6.x-1.x