Here is the code. This table joins ti itself X times to filter out relevant values.
// $left - alias of the left table in the join
// $right - alias of the right table in the join
$query->join('taxonomy_index', $left, "$left.nid = $right.nid AND $right.tid IN (:tids)", array(":tids" => $terms));
If you call it many times in a cycle (with different $terms obviously) the first call arguments will be
used in all subsequent calls. I.e, if you will get something like that if the first value of $terms was array(3):
SELECT DISTINCT n.nid AS nid FROM node n INNER JOIN taxonomy_index ti ON n.nid = ti.nid INNER JOIN taxonomy_index ti1 ON ti.nid = ti1.nid AND ti1.tid IN ('3') INNER JOIN taxonomy_index ti2 ON ti1.nid = ti2.nid AND ti2.tid IN ('3') INNER JOIN taxonomy_index ti3 ON ti2.nid = ti3.nid AND ti3.tid IN ('3') WHERE (n.type = 'my_type') AND (n.status = '1')
This can be workarounded by giving different name to placeholder (i.e :tids1, :tids2 etc ) that is quite normal in a cycle. However, developer shouldn't care about this, because these are not real variables, just placeholder.
Comments
Comment #1
lex0r commentedThe bug *could* be a documentation issue, and programmers are supposed to ensure unique names for their placeholders regardless of the way they build the query. Also, I believe it is not fixed in 7.14
Comment #3
poker10 commentedIt seems like this is still an issue in Drupal 7.x-dev and also in Drupal 11.x-dev, so we should probably move it for 11.x-dev (as per backport policy).
I personally think this is by design and that it is only a documentation issue.
Relevant documentation page is probably this: https://www.drupal.org/docs/8/api/database-api/dynamic-queries/expressions
Thanks!
(closed a newer duplicate issue in favour of this).