SQL查询在最新版本的MySQL中不起作用

Question

我想使用以下查询获取特定的行：

$wanted_term = $wpdb->get_row("
      SELECT allterms.* FROM (
      SELECT @rownum := @rownum + 1 AS rownum, terms.* FROM (
      SELECT DISTINCT t.*, tt.taxonomy, tt.term_taxonomy_id, tm.meta_key, tm.meta_value, tr.object_id
      FROM wp_terms AS t
      INNER JOIN wp_termmeta AS tm ON ( t.term_id = tm.term_id )
      INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id
      INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id
      WHERE tt.taxonomy IN ('release') /* replace taxonomy */
      AND tr.object_id IN ('.$post_id.') /* replace object_ids */
      AND ( tm.meta_key = 'timestamp' )
      ORDER BY tm.meta_value+0 DESC
      ) AS terms, (SELECT @rownum := 0) AS r
      ) AS allterms WHERE allterms.term_id='.$term_id.' /* replace term_id */");

但是，由于升级到最新版本的MySQL，这不起作用。数据库未更改。在后来的MySQL版本中，有没有什么标准发生了变化，导致这个查询无法正常工作？

Answer 1

尝试设置LEFT JOIN以验证问题是否为JOIN条件所致。

 SELECT allterms.* FROM (
      SELECT @rownum := @rownum + 1 AS rownum, terms.* FROM (
      SELECT DISTINCT t.*, tt.taxonomy, tt.term_taxonomy_id, tm.meta_key, tm.meta_value, tr.object_id
      FROM wp_terms AS t
      LEFT JOIN wp_termmeta AS tm ON ( t.term_id = tm.term_id ) AND ( tm.meta_key = 'timestamp' )
      LEFT JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id AND tt.taxonomy IN ('release') /* replace taxonomy */
      LEFT JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id AND tr.object_id IN ('.$post_id.') /* replace object_ids */
      ORDER BY tm.meta_value+0 DESC
      ) AS terms, (SELECT @rownum := 0) AS r
      ) AS allterms WHERE allterms.term_id='.$term_id.' /* replace term_id */