Support Ticket is inserting query requests in our Views -- and breaking the Views!

I'm unable to duplicate this; is there anything nonstandard about your installation that could help me duplicate this?

I have a similar problem... in this case whenever I attempt to save a node, and this example is not a support ticket node. The node dosnt actually save either so this is a critical bug.

I'm also only using the support module, not the sub-modules.

Here's the error:

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'nid' in where clause is ambiguous: SELECT node.nid AS entity_id, node.vid AS revision_id, node.type AS bundle, :entity_type AS entity_type FROM {node} node LEFT OUTER JOIN {support_ticket} st ON st.nid = node.nid WHERE (nid IN (:db_condition_placeholder_0)) AND (type IN (:db_condition_placeholder_1)) AND( (st.client IN (:db_condition_placeholder_2)) OR (st.client IS NULL ) ); Array ( [:db_condition_placeholder_0] => 19 [:db_condition_placeholder_1] => location [:db_condition_placeholder_2] => 1 [:entity_type] => node ) in EntityFieldQuery->execute()

Thanks

looks like the support_node_load db_query is responsible

I'm also encountering this on 7.x-1.0-rc1.

It's occuring on all of the term pages for two separate taxonomies (used as categories) when logged in as a user without the "View other users tickets" permission.

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 't.uid' in 'where clause': SELECT DISTINCT COUNT(t.nid) AS expression FROM {taxonomy_index} t INNER JOIN {node_access} na ON na.nid = t.nid LEFT OUTER JOIN {support_ticket} st ON st.nid = t.nid WHERE (tid = :db_condition_placeholder_0) AND(( (na.gid = :db_condition_placeholder_1) AND (na.realm = :db_condition_placeholder_2) ))AND (na.grant_view >= :db_condition_placeholder_3) AND(( (st.client IN (:db_condition_placeholder_4)) AND (t.uid = :db_condition_placeholder_5) )OR (st.client IS NULL ) ); Array ( [:db_condition_placeholder_0] => 44 [:db_condition_placeholder_1] => 0 [:db_condition_placeholder_2] => all [:db_condition_placeholder_3] => 1 [:db_condition_placeholder_4] => 1 [:db_condition_placeholder_5] => 81 ) in PagerDefault->execute() (line 74 of /home/<user>/public_html/includes/pager.inc).

Subscribing. Views has been broken. Cannot edit or create nodes. I get similar error:

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'nid' in where clause is ambiguous: SELECT node.nid AS entity_id, node.vid AS revision_id, node.type AS bundle, :entity_type AS entity_type FROM {node} node LEFT OUTER JOIN {support_ticket} st ON st.nid = node.nid WHERE (nid IN (:db_condition_placeholder_0)) AND (type IN (:db_condition_placeholder_1)) AND (node.language IN (:db_condition_placeholder_2, :db_condition_placeholder_3)) AND( (st.client IN (:db_condition_placeholder_4, :db_condition_placeholder_5)) OR (st.client IS NULL ) ); Array ( [:db_condition_placeholder_0] => 7191 [:db_condition_placeholder_1] => lottery [:db_condition_placeholder_2] => trans_en [:db_condition_placeholder_3] => und [:db_condition_placeholder_4] => 1 [:db_condition_placeholder_5] => 2 [:entity_type] => node ) in EntityFieldQuery->execute() (line 1136 of entity.inc).

Same here when an authenticated user views a page created by a view that uses taxonomy. If an anonymous user or admin views the page there is no error. Here's what I get...

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 't.uid' in 'where clause': SELECT COUNT(t.nid) AS expression FROM {taxonomy_index} t LEFT OUTER JOIN {support_ticket} st ON st.nid = t.nid WHERE (tid = :db_condition_placeholder_0) AND ( EXISTS (SELECT na.nid AS nid FROM {node_access} na WHERE (( (na.gid = :db_condition_placeholder_1) AND (na.realm = :db_condition_placeholder_2) )OR( (na.gid = :db_condition_placeholder_3) AND (na.realm = :db_condition_placeholder_4) )OR( (na.gid = :db_condition_placeholder_5) AND (na.realm = :db_condition_placeholder_6) ))AND (na.grant_view >= :db_condition_placeholder_7) AND (t.nid = na.nid) )) AND(( (st.client IN (:db_condition_placeholder_8)) AND (t.uid = :db_condition_placeholder_9) )OR (st.client IS NULL ) ); Array ( [:db_condition_placeholder_0] => 2 [:db_condition_placeholder_1] => 0 [:db_condition_placeholder_2] => all [:db_condition_placeholder_3] => 2 [:db_condition_placeholder_4] => content_access_author [:db_condition_placeholder_5] => 2 [:db_condition_placeholder_6] => content_access_rid [:db_condition_placeholder_7] => 1 [:db_condition_placeholder_8] => 1 [:db_condition_placeholder_9] => 2 ) in PagerDefault->execute() (line 74 of /path to drupal/includes/pager.inc).

Same here with Views, support module injects the following code to any views which relation (doesn't matter to which table)

... LEFT OUTER JOIN {support_ticket} st ON st.nid = node.nid ...
... (st.client IN  ('1')) OR (st.client IS NULL ) ...

support_query_alter is responsible for this problem , so if you disable this function things will become fine. But fixing the issue is a bit tricky, we need to know which queries include support ticket nodes and inject the access query only for those queries. However since support ticket is node! (which i think should have been an entity) there isn't any easy way to figure that out.
If we totally disable the query injection , support access control will be partially disabled too which is a security risk, and if leave it active then it causes lots of problem in difference places.

What i came up with so far is to check the query to see if it contains any node type condition , if its does, and support ticket is not among them then we can safely ignore the inject , otherwise it works as before. Thanks to D7 new database api it's possible but not actually easy!! Here is a potential solution :)

As you can see you should copy it at the begining of support_query_alter function on support.module file.

function support_query_alter(QueryAlterableInterface $query) {

  $support_ticket_related = TRUE;
  $conditions = $query->conditions();
  $meta = $query->getMetaData('view');
  if ($meta) {
    if ($meta->query->where) {
      if ($meta->query->where[1]) {
        if ($meta->query->where[1]['conditions']) {
          $conditions = $meta->query->where[1]['conditions'];
        }
     }
   }
  }
  if ($conditions) {
    foreach ($conditions as $item_d2) {
      if (is_string($item_d2['field']))
        if ($item_d2['field']=='node.type') {
          if (!in_array('support_ticket',$item_d2['value']) && ($item_d2['operator'] == 'in' || $item_d2['operator'] == 'IS NULL')) {
            $support_ticket_related = FALSE;
            break;
          }
        }
    }
  }

  if ($query->hasTag('node_access') && !$query->hasTag('support_search') && $support_ticket_related) {

NOTICE : You much clear that cache once after the change for it to work. For views all you have to do is to add node.type filter and limit and exclude support_ticket nodes from result

The problem I found was that this function was adding the uid column to SELECT statements with tables that did not have a uid column. My suggestion for how to address this is to put a field test into a try statement in the support_query_alter() function on support.module file.

			if (db_field_exists($table, 'uid')) {
				$ticket_alias = $query->leftJoin('support_ticket', 'st', 'st.nid = ' . $nalias . '.nid');
				$query->condition(db_or()
				  ->condition(db_and()
					// Must be on the allowed clients list
					->condition($ticket_alias . '.client', $clients)
					// and must be owned by the user
					->condition($nalias . '.uid', $user->uid))
				  // or must be something other than a support ticket
				  ->condition($ticket_alias . '.client', null));	
				return TRUE;			
			}

(amended to include the great comments from sinasalek ).

That easy to fix, instead of using try which kill the performance (It will run for almost all queries on site) you can look in selected tables. For example when there is no node table then support should ignore altering that query.
Also we have tables schema so it's possible to event check the selected tables' fields

I agree it's a performance hit and a kludge. It's also how query.inc in Drupal 7 assesses if a field exists--

from public function fieldExists($table, $column)
includes\database\mysql\schema.inc, line 524:
$this->connection->queryRange("SELECT $column FROM {" . $table . "}", 0, 1);

No wonder Drupal has performance issues :)
So then i think it's better to rely on fieldExists method. if it gets a performance patch this snippet will benefits from that too
There is a standalone function as well http://api.drupal.org/api/drupal/includes%21database%21database.inc/func...

Great idea! I edited my code (http://drupal.org/node/1376120#comment-6377448) to put db_field_exists($table, 'uid') into the mix.

I too am getting this same issue... If you get Devel to log your individual queries. Even simple ones like display all Published Node of any kind except support tickets, it shows this left join.

I hope a patch gets released for this soon.

-R

For me, this isn't breaking views but my server is already maxed out on memory usage and I imagine that this is just one more drop in the bucket.

Not sure why this is set to Maintainer Needs More Information... It is fairly simple to reproduce. Here is how I reproduce it...

Install Views (I have Views 7.x-3.5)
Install Support Ticketing System (I have 7.x-1.0-rc1)
Enable "Show the SQL query" under "Live preview settings" in the Views module settings.
Look at a random View setup to display nodes that don't involve a Support Ticket.
Witness the undesirable existence of the added query add on in the live preview's SQL LEFT OUTER JOIN {support_ticket} st ON st.nid = node.nid ........... AND( (st.client IN ('2', '1')) OR (st.client IS NULL ) ). This exists for all of my node views whether or not they involve Support Tickets.
Disable Support Ticketing System
Go back to the view and live preview again.
The inserted gook is no longer in the query.

Server is running Apache, PHP5.3.5, and MySQL5.1.56-log.

I don't want to throw any rocks or step on any toes but I'm switching this back to active.

Thanks, nfd. That was quite helpful, I'm able to duplicate...

There's three issues being discussed in this thread:

1. Support is sometimes filtering by uid even when there's no user table in the query, with an error like "Column not found: 1054 Unknown column 't.uid' in 'where clause':"
2. There is a support join in the query and there's breakage with an error like: "Integrity constraint violation: 1052 Column 'nid' in where clause is ambiguous"
3. Support is joining on all node queries even when there couldn't possibly be support tickets in the query

• A likely fix for issue #1 has been committed, based on dewolfe001's patch from #10 above -- if there's no user table in the query, we no longer filter by uid.
• The error in #2 may be from an older version of the module; we always add "ALIAS.nid" to our query when joining node but it's missing in the bug reports -- I can't duplicate or otherwise explain this.
• As for #3, short of completely disabling access control and giving everyone access to all tickets, I don't see an ideal solution here. I tend to run Support on its own installation (ie in a multi-site configuration). We could try and inspect the query and take a best guess as to wether we should be filtering support tickets, but it's replacing one kind of overhead with another and is not always going to work.

http://drupalcode.org/project/support.git/commit/3f197ba

Jeremy thank's for the commit,
Regarding number 3, even when there is a uid it still shouldn't join with support_ticket when the query does not contain any ticket node. However i'm not sure if my solution in comment #9 is generic enough to fix this issue permanently. I spent alot of time figuring out how to this in safe way , so far this solution is the only one i could come up with
It works well with views though

I see your proposed solution; my biggest concern is the performance implication this might have for all queries... my recommendation is to create a new ticket that is explicitly about avoiding joins to the support_ticket table when not needed, and putting your patch there so we can review/test/discuss. This ticket was about breaking Views queries, which I hope is now solved.

I see, i created a new ticket here #1786696: Avoiding joins to the support_ticket table when not needed