0

Same exact query. Same exact dataset. Two different MySQL servers. TWO DIFFERENT RESULTS. Why?

Query:

SELECT DISTINCT term_taxonomy_id FROM (
  SELECT * FROM azEw_term_relationships 
  WHERE `term_taxonomy_id` IN 
   (SELECT term_taxonomy_id FROM azEw_term_taxonomy WHERE taxonomy = 'series')
ORDER BY object_id DESC) `series_term_ids`;

My expectation is that if series_term_ids table is sorted, then SELECT DISTINCT will grab the first row for each term_taxonomy_id.

Results

Server 1 mysql Ver 14.14 Distrib 5.6.35, for osx10.9 (x86_64) using EditLine wrapper returns 29 rows in the desired order:

61424
2221
3529
10404
21993
33
[etc]

Server 2 mysql Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1 returns the same 29 rows by SORTED ASC.

21
23
30
33
1013
1027
1042
2221
[etc]

This is part of a WordPress query where I'm trying to get a list of taxonomy terms sorted by most recent post in each term. Using the post_meta object ID is a close enough approximation of the date of the latest post (I'm aware it's actually the order of the most recent addition to that term, but that's close enough and avoids a join to the wp_posts table). The WordPress query is here (totally open to modifying it):

WordPress template

<?php
  global $wpdb;
  $query = "
    SELECT DISTINCT term_taxonomy_id
    FROM ( SELECT *
    FROM $wpdb->term_relationships
    WHERE `term_taxonomy_id` IN
    ( SELECT term_taxonomy_id
    FROM $wpdb->term_taxonomy
    WHERE taxonomy = 'series' )
    ORDER BY object_id DESC ) `series_term_ids`";
  $output = 'ARRAY_A';
  $series_array = $wpdb->get_results( $query, $output );


  $s_terms = get_terms( array(
   'taxonomy' => 'series',
   'orderby' => 'include',
   'include' => array_column( $series_array, 'term_taxonomy_id' ),
   'hide_empty' => 1
  ) );
?>
Slam
  • 3,125
  • 1
  • 15
  • 24
  • I think you've answered your own question in respect of your expectations – Strawberry May 16 '18 at 19:31
  • Not that MySQL guarantees to persist a subquery's ORDER beyond it (and iirc sometimes even ignores it if it feels it is irrelevant), but... Is object_id unique in `azEw_term_relationships`? – Uueerdo May 16 '18 at 19:34
  • Variables for table names is often indicative of a bad db design. – Uueerdo May 16 '18 at 19:41
  • Object_id is unique. And what do you mean by variables for table names? In this code I'm trying to extract table data into a variable, but the table names are fixed. – Slam May 16 '18 at 20:55
  • My php is admittedly rudimentary but the use of `$wpdb->term_relationships` and `$wpdb->term_taxonomy` looks a lot like variables in place of table names to me. – Uueerdo May 16 '18 at 20:57

1 Answers1

1

My expectation is that if series_term_ids table is sorted, then SELECT DISTINCT will grab the first row for each term_taxonomy_id.

MySQL does not guarantee order in such circumstances.

You're better off being a little more literal and doing something like:

SELECT A
FROM (...) AS theSubQ
GROUP BY A
ORDER BY MAX(B) DESC
;
Uueerdo
  • 15,723
  • 1
  • 16
  • 21
  • That's exactly where my research was leading me. But I find it fascinating that in three different servers I've no got three different results. Obviously DISTINCT can't be trusted to preserve order. – Slam May 16 '18 at 20:53
  • This was the correct answer. The difference in sort order was between MySQL and MariaDB. – Slam Mar 29 '19 at 21:56