Views (others as well?) quite broken on location cck fields because of location_instance have name space conflict

I believe I got the same problem.

Say I have node, and another entity type called package, both use location CCK.

I even let them to use different cck field so the should not affect each other at all. However, the Views query generated has something like

LEFT JOIN location_instance location_instance ON node.vid = location_instance.vid
LEFT JOIN location location ON location_instance.lid = location.lid

so it's only joining based on vid instead of normally with entity API you need

entity_type = "node" AND enitity_id = :id AND revision_id = :rid

On my production I got a lot of nodes and a lot of packages, keep in mind that the entity tables grow their own IDs from 0, so eventually some ID range overlaps as my screenshot shows:

So when my view executes the query on a node, it could accidentally pick a totally unrelated package with the same vid and use that wrong location.

I think this is a critical issue.

Currently I have a workaround, which is to manually increase the other entity's auto increment value to a very large number to avoid the ID conflict in the location instance table.

However, the proposed solution will be to to change the location_instance schema to include entity_type, entity_id and revision_id instead of just nid and vid.

marked #1969526: Location fields- previously entered user data gets printed on Nodes. as a duplicate of this.

Any work around or patch in sight?

Agreed. This is significant problem when a site has locations on different entity types. The problem is very visible on SummerSailstice.com/events - where users are creating event 'nodes', and also registration 'entities' - both have location fields, and the locations are getting mixed up in views.

Let me know if I can help.

more info

Here's a workaround I'm using for this with nodes:

/**
 * Implements hook_views_query_alter().
 */
function mymodule_views_query_alter(&$view, &$query) {
  if ($view->name == "your_view_name" && $view->current_display == 'your_display_name') {
    $query->table_queue['location_instance']['join']->left_field = 'nid';
    $query->table_queue['location_instance']['join']->field = 'nid';
    $query->table_queue['location_instance']['join']->definition['left_field'] = 'nid'; 
    $query->table_queue['location_instance']['join']->definition['field'] = 'nid';
  }
}