我有几张table：

product(id)

product_locale(product_id, locale_id)

category_product(category_id, product_id)

我想选择一个类别的所有产品和它的子类别。
以下是我的查询：

SELECT a.id,
       a.added,
       a.seller,
       a.position,
       c.title,
       c.alias
FROM product a
INNER JOIN product_locale c ON a.id = c.product_id
INNER JOIN category_product d ON d.product_id = a.id
WHERE c.locale_id = 1
  AND (d.category_id = 17
       OR d.category_id in
         (SELECT id
          FROM
            (SELECT *
             FROM category
             ORDER BY parent,
                      id) category_sorted,

            (SELECT @pv := 17) initialisation
          WHERE find_in_set(parent, @pv)
            AND length(@pv := concat(@pv, ',', id))))
LIMIT 0,
      10

基本上，我有一个子查询来获取当前类别的所有子类别：

SELECT id
FROM
  (SELECT *
   FROM category
   ORDER BY parent,
            id) category_sorted,

  (SELECT @pv := 17) initialisation
WHERE find_in_set(parent, @pv)
  AND length(@pv := concat(@pv, ',', id))

下面是categories结构：
17 > 19 > 33 > 35
如果我单独运行子查询，我得到了正确的结果（19，33和35），所以我希望查询应该是：

SELECT a.id,
       a.added,
       a.seller,
       a.position,
       c.title,
       c.alias
FROM product a
INNER JOIN product_locale c ON a.id = c.product_id
INNER JOIN category_product d ON d.product_id = a.id
WHERE c.locale_id = 1
  AND (d.category_id = 17
       OR d.category_id in (19,
                            33,
                            35))
LIMIT 0,
      10

如果我运行这个查询，我得到了正确的结果。但是，如果我运行带有子查询的查询，则不会返回任何记录。
如果我使用左连接而不是内部连接，就像下面的查询一样，它可以工作：

SELECT a.id,
       a.added,
       a.seller,
       a.position,
       c.title,
       c.alias
FROM product a
LEFT JOIN product_locale c ON a.id = c.product_id
AND c.locale_id = 1
INNER JOIN category_product d ON d.product_id = a.id
WHERE (d.category_id = 17
       OR d.category_id in
         (SELECT id
          FROM
            (SELECT *
             FROM category
             ORDER BY parent,
                      id) category_sorted,

            (SELECT @pv := 17) initialisation
          WHERE find_in_set(parent, @pv)
            AND length(@pv := concat(@pv, ',', id))))
LIMIT 0,
      10

但是如果我将条件c.locale_id = 1移动到where condition，它就不起作用了：

SELECT a.id,
       a.added,
       a.seller,
       a.position,
       c.title,
       c.alias
FROM product a
LEFT JOIN product_locale c ON a.id = c.product_id
INNER JOIN category_product d ON d.product_id = a.id
WHERE c.locale_id = 1
  AND (d.category_id = 17
       OR d.category_id in
         (SELECT id
          FROM
            (SELECT *
             FROM category
             ORDER BY parent,
                      id) category_sorted,

            (SELECT @pv := 17) initialisation
          WHERE find_in_set(parent, @pv)
            AND length(@pv := concat(@pv, ',', id))))
LIMIT 0,
      10

我期望子查询不会影响查询，然而，似乎它确实如此。