I ran some queries and came across something that, in my opinion, looks like a MySQL error. I executed the following query:
select s.id, a.name as a_name, s.label, st.name as st_name, -- substr(f.name FROM 1 FOR locate('cl', f.name)-2), -- substr(f.name FROM locate('cl', f.name)-2 FOR 1), substr(f.name FROM locate('cl', f.name)), count(1) from table_sf sf, table_f f, table_s s, table_a a, table_st st where f.id = sf.f_id and s.id = sf.s_id and s.a_id = a.id and s.st_id = st.id group by 1, 2, 3, 4, 5 having count(1) != 2;
By default, MySQL assigns column names if you do not specify them in the calculated fields. Usually this is just a field “formula”, for example, count(1) for the last field in the request above. However, it seems that adding comments inside the query produces MySQL. The results are correct, but the field name is completely incorrect. These are the column headers that I get:
id name label name -- substr(f.name FROM 1 FOR locate('cl', f.name)-2), count(1)
Note that the 5th column receives the first comment as a name, not even related to it. And he admitted that there are two comments because he assigned only the first column name for the next calculated field without an alias. Is this expected behavior? Or is it a MySQL error? I am running MySQL 5.1.63 using SequelPro as a client in OS X.
Update: I also tried this on installing MySQL 5.4.3, and the field is displayed correctly there. Maybe this is an error in the 5.1.x database?