MySQL: cannot add constant field at start of SELECT. Error 1064

Using MariaDB v. 10, I try to add a custom constant as the first column of my SELECT query, before all other columns *. This results in the generic syntax error 1064.

For example:

# mysql -u root -p$mypass -D mysql -e "SELECT 'X', * FROM user;"
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '* FROM user' at line 1

If I select only some columns, it works:

# mysql -u root -p$mypass -D mysql -e "SELECT 'X', host, user FROM user;"
+---+-----------------+------------------+
| X | host            | user             |
+---+-----------------+------------------+
| X | 127.0.0.1       | root             |
| X | ::1             | root             |
...

And it also works if I use * to select all columns, but put my constant column after it:

# mysql -u root -p$mypass -D mysql -e "SELECT *, 'X' FROM user;"  
... [long, correct output] ...

How can I do a SELECT 'some constant', * FROM my_table to avoid this strange error?

Answer

You can only use SELECT * if it’s the first thing in the SELECT list. If it’s mixed in with other values, you need to use a table name or alias, so do

SELECT 'X', user.* FROM user;

Advertisement

Answer