mysql select field determines order of result set

Question

I am currently experiencing a (to me) very strange behaviour for one of my mysql 5.6 queries.

I have a given system I am trying to optimize. One step is to only select the fields necessary for the next operation.

The given query looks as follows:

SELECT oxv_oxcategories_6_fr.*
FROM oxv_oxobject2category_6 AS oxobject2category
  LEFT JOIN oxv_oxcategories_6_fr ON oxv_oxcategories_6_fr.oxid = 
oxobject2category.oxcatnid
WHERE oxobject2category.oxobjectid = '<hashed id>'
  AND oxv_oxcategories_6_fr.oxid IS NOT NULL
  AND (oxv_oxcategories_6_fr.oxactive = 1
       AND oxv_oxcategories_6_fr.oxhidden = '0')
ORDER BY oxobject2category.oxtime

I have taken the libery to use more sensible naming in my own query:

SELECT
  category_view.*
FROM oxv_oxobject2category_6 category_mapping_view
  LEFT JOIN oxv_oxcategories_6_fr category_view ON category_view.OXID = 
category_mapping_view.OXCATNID
WHERE category_mapping_view.OXOBJECTID = '<hashed id>'
  AND category_view.OXID IS NOT NULL
  AND (category_view.OXACTIVE = 1
       AND category_view.OXHIDDEN = '0')
ORDER BY category_mapping_view.OXTIME

As you can see, there is not much difference, only the naming is different. So far, everything works as expected. Now I am trying to only select the values I need. So the query looks like this:

SELECT
  category_view.OXID,
  category_view.OXTITLE
FROM oxv_oxobject2category_6 category_mapping_view
  LEFT JOIN oxv_oxcategories_6_fr category_view ON category_view.OXID = 
category_mapping_view.OXCATNID
WHERE category_mapping_view.OXOBJECTID = '<hashed id>'
  AND category_view.OXID IS NOT NULL
  AND (category_view.OXACTIVE = 1
       AND category_view.OXHIDDEN = '0')
ORDER BY category_mapping_view.OXTIME;

This also works as expected. But, I also need the field OXPARENTID, so I change the SELECT statement to

category_view.OXID,
category_view.OXTITLE,
category_view.OXPARENTID

Now the order of the items is different and I cannot seem to find out why that is. The new as well as the original query both sort for OXTIME without that field being present in the final result set. There are about 10 entries where OXTIME is 0, and it is those items that get turned around (ordering-wise) as soon as I query for OXPARENTID.

In the original query, OXPARENTID is present as well, so why does it make a difference now? I am guessing that there is some sort of ordering logic going on I do not yet know about.

Mind, that both joined tables are actually views, maybe that has something to do with it. Also, OXID and OXPARENTID are both md5 hashed values.

Any help would be greatly appreciated.

EDIT

In order to clarify, I know that the fact that multiple entries have OXTIME equal 0 makes it impossible to predict beforehand, which entry will be the top one. However, I still expected the order of the entries to be the same every time I call the query (regardless of what I am selecting).

One answer (@GordonLinoff) explains, that

[...] the same query can return the results in different order on different runs

Where does this "randomness" come from?

Answer 1

Your ordering is:

ORDER BY category_mapping_view.OXTIME;

And then you state:

There are about 10 entries where OXTIME is 0, and it is those items that get turned around (ordering-wise) as soon as I query for OXPARENTID.

What you have are ties in the keys. The results can be in any order -- and the same query can return the results in different order on different runs. Technically, the ordering in SQL is unstable.

You can fix this by including another column in the ORDER BY so each row is uniquely defined by the ORDER BY keys. Perhaps that is OXID:

ORDER BY category_mapping_view.OXTIME, category_view.OXID;

By the way, it is "obvious" that sorting in SQL is unstable. Why? SQL tables represent unordered sets. There is no ordering to fall back on when the keys are the same.