どうも列の別名にアンダースコアを使うと、挙動が変わるみたい。
- アンスコなし
mysql> SELECT date_format(datetime, '%Y%c') as yearmonth -> FROM journals -> WHERE (date_format(datetime, '%c') = '8') -> GROUP BY yearmonth ORDER BY yearmonth -> ; +-----------+ | yearmonth | +-----------+ | 20078 | +-----------+ 1 row in set (0.01 sec)
- アンスコあり
mysql> SELECT date_format(datetime, '%Y%c') as year_month -> FROM journals -> WHERE (date_format(datetime, '%c') = '8') -> GROUP BY year_month ORDER BY year_month -> -> ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'year_month FROM journals WHERE (date_format(datetime, '%c') = '8') GROUP BY yea' at line 1 mysql>
- アンスコありだけどシングルクォート囲み
mysql> SELECT date_format(datetime, '%Y%c') as 'year_month' -> FROM journals -> WHERE (date_format(datetime, '%c') = '8') -> GROUP BY 'year_month' ORDER BY 'year_month' -> ; +------------+ | year_month | +------------+ | 20078 | +------------+ 1 row in set (0.00 sec)
なんでだろお。