First off, let me say I am not 100% certain this is a bug and, if it is, whether it really applies to CCK and not Views.

The other day, I suggested (swedish) that Views be used for a case where the person looking for help was trying to do a family tree of dogs. Each dog was a node of content type 'hund', which had one field for father (node reference, 'field_far') and one for mother ('field_mor'). He didn't get it to work so I tried myself today and was also unsuccessful, because the query returned a MySQL error. So I examined the SQL that Views put together, using CCK's handlers, and it really was faulty.

For some reason, the JOINs produced by the View relations were using another JOIN's alias, which in turn was using the first JOIN's alias. A catch 22. Here is the SQL produced:

SELECT node.nid AS nid, node.title AS node_title, node_node_data_field_far.title AS node_node_data_field_far_title, node_node_data_field_far.nid AS node_node_data_field_far_nid, node_node_data_field_mor.title AS node_node_data_field_mor_title, node_node_data_field_mor.nid AS node_node_data_field_mor_nid, node_node_data_field_far_1.title AS node_node_data_field_far_1_title, node_node_data_field_far_1.nid AS node_node_data_field_far_1_nid, node_node_data_field_mor_1.title AS node_node_data_field_mor_1_title, node_node_data_field_mor_1.nid AS node_node_data_field_mor_1_nid, node_node_data_field_far_2.title AS node_node_data_field_far_2_title, node_node_data_field_far_2.nid AS node_node_data_field_far_2_nid, node_node_data_field_mor_2.title AS node_node_data_field_mor_2_title, node_node_data_field_mor_2.nid AS node_node_data_field_mor_2_nid FROM node node LEFT JOIN content_type_hund node_data_field_far ON node_node_data_field_mor.vid = node_data_field_far.vid LEFT JOIN node node_node_data_field_far ON node_data_field_far.field_far_nid = node_node_data_field_far.nid LEFT JOIN content_type_hund node_data_field_mor ON node_node_data_field_mor.vid = node_data_field_mor.vid LEFT JOIN node node_node_data_field_mor ON node_data_field_mor.field_mor_nid = node_node_data_field_mor.nid LEFT JOIN node node_node_data_field_far_1 ON node_data_field_far.field_far_nid = node_node_data_field_far_1.nid LEFT JOIN node node_node_data_field_mor_1 ON node_data_field_mor.field_mor_nid = node_node_data_field_mor_1.nid LEFT JOIN node node_node_data_field_far_2 ON node_data_field_far.field_far_nid = node_node_data_field_far_2.nid LEFT JOIN node node_node_data_field_mor_2 ON node_data_field_mor.field_mor_nid = node_node_data_field_mor_2.nid WHERE node.nid = 138

And the error message from MySQL:

user warning: Unknown column 'node_node_data_field_mor.vid' in 'on clause'

Here is an example of the catch 22:

LEFT JOIN
	content_type_hund node_data_field_mor
		ON node_node_data_field_mor.vid = node_data_field_mor.vid
LEFT JOIN
	node node_node_data_field_mor
		ON node_data_field_mor.field_mor_nid = node_node_data_field_mor.nid

To reproduce the problem, try this.

1) Create a new view.

2) Add an argument for node nid. Have it pick the nid from URL if argument is absent and validate that it's a 'hund' type of node.

3) Add relationship for Content: Far ('field_far') and name it 'Far'.

4) Add relationship for Content: Mor ('field_mor') and name it 'Mor'.

5) Add relationships for Content: Far ('field_far'), name it 'FarFar' and have it use the 'Far' relationship.

6) Add relationships for Content: Mor ('field_mor'), name it 'FarMor' and have it use the 'Far' relationship.

7) Add relationships for Content: Far ('field_far'), name it 'MorFar' and have it use the 'Mor' relationship.

8) Add relationships for Content: Mor ('field_mor'), name it 'MorMor' and have it use the 'Mor' relationship.

9) Add field Node: Title. One without any and one for each relationship.

The view should now fail with a MySQL error message.