Hi,
I have tested views 2.4 and 2.5 on a site where views 2.3 worked well.
With these new views versions some views didn't work and the result message is:

user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= node_users.uid AND node_users.type = 'teacher_profile' LEFT JOIN content_fiel' at line 10 query:

The query is:

SELECT node.nid AS nid,
   node.title AS node_title,
   node_data_field_lastname.field_lastname_value AS node_data_field_lastname_field_lastname_value,
   node_users.nid AS node_users_nid,
   node_users.type AS node_users_type,
   node_users.vid AS node_users_vid
 FROM node node 
 LEFT JOIN content_field_referents node_data_field_referents ON node.vid = node_data_field_referents.vid
 LEFT JOIN users users_node_data_field_referents ON node_data_field_referents.field_referents_uid = users_node_data_field_referents.uid
 LEFT JOIN node node_users ON .uid = node_users.uid AND node_users.type = 'teacher_profile'
 LEFT JOIN content_field_lastname node_data_field_lastname ON node_users.vid = node_data_field_lastname.vid
 WHERE node.type in ('research_sectors')

The problem is in line 10, where you see .uid = node_users.uid where instead it should be users_node_data_field_referents.uid = node_users.uid.
This problem on the table name for joins apparently is the problem for this invalid (not working more) views.
Do you known the origin of this problem? Is this bug simply "patching" or is best to return to views 2.3?
Thanks a lot and regards,
Saxx

Comments

dargrego’s picture

dargrego commented

Hi,

I have similar problem with query in Views 2.5:
INNER JOIN node node_users ON .uid = node_users.uid AND node_users.type = 'customer_data'

In Views 2.3 everything works. I noticed the problem adding relationship - in my case: (Signup user) Segment: Content Profile in the view: signup_status_user_vbo_admin. I am using modules: signup, signup_status, content_profile.

dww’s picture

dww
we/he/they
commented
Title: Bad table name on LEFT JOIN » Fatal SQL errors caused by broken table aliases in some cases in add_relationships()

Ugh. :( The change that caused this error was introduced over at #386986: 2 or more relationships can block a normal table from adding to fix a different bug. It's too late for me to carefully debug this, and frankly, I don't fully understand all the edge cases of how table aliases are supposed to work via relationships, how implicit vs. explicit relationships are handled, etc. I know just enough about all of this to get into a lot of trouble, but not enough to get back out. ;) Giving this a more precise title so merlinofchaos knows what this is about, and wanted to add a pointer to the issue where this bug was introduced so everyone understands we can't just revert to the 6.x-2.3 code, either...

dww’s picture

dww
we/he/they
commented

Marked the following as duplicates with this:
#437960: SQL error from missing table alias (".nid") after upgrading to 2.4 or 2.5
#442776: views 2.5 sql error "ON .uid = node_users.uid" ... has empty table.
#445000: Views and Voting API Error

Flying Drupalist’s picture

Flying Drupalist commented

Thanks for clarifying the issue.

phl3tch’s picture

phl3tch commented

Subscribing

bobgeier’s picture

bobgeier commented

Subscribing.

This change effectively broke a lot of views that I was using in conjunction with contentprofile.module (using a user reference to then pull some field from the content profile node). Critical for our stuff.

merlinofchaos’s picture

merlinofchaos commented
Status: Active » Fixed

I've been trying to dup this but I cannot. I believe this was fixed by http://drupal.org/node/317271

phl3tch’s picture

phl3tch commented

Downloaded the development snapshot, installed, updated. No change -- the error is the same.

phl3tch’s picture

phl3tch commented

Traced my problem to a bug in Content Profile, where it was always assuming the left table to be users. There's a fix here:

http://drupal.org/node/471708#comment-1673322

Hope this helps someone else...

bobgeier’s picture

bobgeier commented

Thanks, Crackwilding. Really appreciate the info and link. We were stuck on just reversing the Views patch that was causing this problem.

Been running into a few of these Views/Content Profile interactions. Since Content Profile is the most likely source of complex Views user relationships, it would be nice if merlin or someone helping to maintain Views ran quickchecks against CP-type relationships before committing Views upgrades... or otherwise collaborated with the CP team.

merlinofchaos’s picture

merlinofchaos commented

I'm sorry, even suggesting that with all the things I have to deal with in Views I now need to be responsible for contrib modules that I have never looked at it is just ridiculous. content_profile is not my problem nor responsibility.

SaxxIng’s picture

SaxxIng commented

I can confirm that the problem (on my site) has solved! (with views 2.6 and content-profile 1.0-beta4).
Thanks to all for this important bugfix!
Best regards,
Saxx

Status: Fixed » Closed (fixed)

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