Is it possible that the order of the results of a SELECT * query (no ORDER BY) is always the same provided that the same DBMS is used as Metastore?
So, as long as MySQL is used as Metastore, the order of the results for a SELECT *; query will always be the same. If Postgres is used, the order will be always the same on the same data, but different from when MySQL is used. I am talking about the same data.
Maybe it all boils down to the question of what is the default order of results and why is it different for MySQL and Postgres Metastore.
There is no such thing as default order of rows, without ORDER BY the order is not guaranteed. This fact is not connected with Metastore database used.
In general data is being read in parallel by many processes (mappers), after splits were calculated, each process starts reading some piece of file or few files, depending on splits calculated. All parallel processes can process different volume of data and running on different nodes, the load is not the same each time, so they start returning rows and finishing at different times, depending on too many factors, such as node load, network load, volume of data per process, etc, etc. Removing all this factors you can increase the order prediction accuracy. Say, single thread sequential file read will return rows in the same order as they are in the file. But this is not how the database works.
Also according to Codd's relational theory, the order of columns and rows is immaterial to the database.