dimanche 26 juin 2016

GROUP BY clause parsed before or after SELECT clause


I'm trying to understand the logical order of execution of the SELECT query in the MySQL DBMS.

Reading this and this questions it came out that the order can be:

  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  5. GROUP BY
  6. WITH CUBE or WITH ROLLUP
  7. HAVING
  8. SELECT
  9. DISTINCT
  10. ORDER BY
  11. TOP

Or:

  1. FROM clause
  2. WHERE clause
  3. SELECT clause
  4. GROUP BY clause
  5. HAVING clause
  6. ORDER BY clause

As you can see the order is inverse in the two, so I started fiddling with MySQL.

The table (PROGETTO) was the following:

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| NOME_P   | varchar(15) | NO   | UNI | NULL    |       |
| NUMERO_P | int(11)     | NO   | PRI | NULL    |       |
| SEDE_P   | varchar(15) | YES  |     | NULL    |       |
| NUM_D    | int(11)     | NO   | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+

the dataset was:

+-----------------+----------+-----------+-------+
| NOME_P          | NUMERO_P | SEDE_P    | NUM_D |
+-----------------+----------+-----------+-------+
| ProdottoX       |        1 | Bellaire  |     5 |
| ProdottoY       |        2 | Sugarland |     5 |
| ProdottoZ       |        3 | Houston   |     5 |
| Informatizzazio |       10 | Stafford  |     4 |
| Riorganizzazion |       20 | Houston   |     1 |
| Nuove opportuni |       30 | Stafford  |     4 |
+-----------------+----------+-----------+-------+

So, I launched the first query:

mysql> SELECT NOME_P FROM PROGETTO GROUP BY NUM_D;
+-----------------+
| NOME_P          |
+-----------------+
| Riorganizzazion |
| Informatizzazio |
| ProdottoX       |
+-----------------+

and I thought, "well the order must be the first one, but let's try another time..." and I executed:

mysql> SELECT NOME_P AS NNN FROM PROGETTO GROUP BY  NNN;
+-----------------+
| NNN             |
+-----------------+
| Informatizzazio |
| Nuove opportuni |
| ProdottoX       |
| ProdottoY       |
| ProdottoZ       |
| Riorganizzazion |
+-----------------+

...and this totally made me confused.


Aucun commentaire:

Enregistrer un commentaire