MySql column without aggregates and group by clause

Question

Since I am coming from MSSQL world I am confused, what happening in queries where you have group by clause and other columns in select which do not go through aggregates functions.

I was shocked when MySql executed query like this

select * from myTable
group by columnThatIsNotPk

Or fiddle like this http://sqlfiddle.com/#!9/b30bf5/1

My questions are:

1. Is there an explanation which data I am going to get for columns that not exists in group by clause?
2. Is it ok to use queries like that?
3. Do I lose or gain performances with queries like this?
4. Does MySql implements First and Last aggregates as it does JetEngine in ACCESS, and unfortunately missing feature in MSSQL engine?

Answer 1

For the question about aggregates: See only_full_group_by, which was added in MySQL 5.7.5. Before that, you got random values for the columns not based on the GROUP BY column(s). After that, the statement is in error (unless you turn off that flag).

As for whether there is a performance gain? Who cares, if you get garbage? SELECT * is probably never faster (other than to type) than SELECT ... with specific columns.

As for FIRST, LAST, and other "Windowing functions", they are available in MariaDB 10.2 and MySQL 8.0 (but not yet ready for prime time). There are workarounds, often using LIMIT 1.

Another thing to learn about if you are moving to MySQL: Learn about AUTO_INCREMENT as a non-drop-in replacement SEQUENCEs.