MySQL execute statement returns 'NULL'

Question

Could you please advise why this query returns 'NULL' on BASH shell? Basically I need to script it so could you please also advise what's the best practices to execute queries in BASH script?

MySQL CLI

mysql> SELECT CONCAT(mail_name,"@",name) AS email_address,accounts.password 
 > FROM mail 
 > LEFT JOIN domains ON domains.id=mail.dom_id
 > LEFT JOIN accounts ON accounts.id=mail.account_id LIMIT 2;
+-------------------------------------+----------+
| email_address | password |
+-------------------------------------+----------+
| [email protected] | password1| 
| [email protected] | password2|
+-------------------------------------+----------+
2 rows in set (0.00 sec)

BASH Shell

# mysql test -e "SELECT CONCAT(mail_name,"@",name) AS email_address,accounts.password FROM mail LEFT JOIN domains ON domains.id=mail.dom_id LEFT JOIN accounts ON accounts.id=mail.account_id LIMIT 2;"
+---------------+----------+
| email_address | password |
+---------------+----------+
| NULL | password1| 
| NULL | password2| | 
+---------------+----------+

• Andriy M: LIMIT without ORDER BY = no guarantee that same two rows will always be selected.
• Andriy M: You mean you don't know why you query can return NULLs at all?
• HTF: No, it was returning NULL instead of emails on BASH Shell.
• Andriy M: It appears @Jimmy Stenke has got it right. I thought it had to do with non-matching rows, since you've got outer joins in your query.

Answer 1

Whenever you are trying to concatenate a string with NULL, NULL is always returned, and for some reason @ is interpreted as NULL in MySQL. That is why the query returns NULL.

Since you wrap the string in the shell with " and you use it inside the query, the ones inside the query will be removed (literally, it is 3 strings that is being passed to MySQL).

You need to escape the quotation marks inside the query.

Like this:

# mysql test -e "SELECT CONCAT(mail_name,\"@\",name) AS email_address,accounts.password FROM mail LEFT JOIN domains ON domains.id=mail.dom_id LEFT JOIN accounts ON accounts.id=mail.account_id LIMIT 2;"

or

# mysql test -e "SELECT CONCAT(mail_name,'@',name) AS email_address,accounts.password FROM mail LEFT JOIN domains ON domains.id=mail.dom_id LEFT JOIN accounts ON accounts.id=mail.account_id LIMIT 2;"