MySQL含聚合函数的SELECT查询拼接非聚合列返回0原因求解

我使用Viescas所著《SQL Queries for Mere Mortals》的配套数据集。

运行书中提供的如下代码：

select customers.CustFirstName || " " || customers.CustLastName as "Name", 
customers.CustStreetAddress || "," || customers.CustZipCode || "," || customers.CustState as "Address",
count(engagements.EntertainerID) as "Number of Contracts",
sum(engagements.ContractPrice) as "Total Price",
max(engagements.ContractPrice) as "Max Price"
from customers
inner join engagements
on customers.CustomerID = engagements.CustomerID
group by customers.CustFirstName, customers.custlastname,
customers.CustStreetAddress,customers.CustState,customers.CustZipCode
order by customers.CustFirstName, customers.custlastname;

得到的查询结果如下：

按照预期，首行Name列应输出Carol Viescas，Address列应输出对应地址，但实际均返回数值，请问出现该问题的原因是什么？

该问题由不同数据库的字符串拼接语法不兼容导致：

• 书中示例SQL适配的是Oracle、PostgreSQL、SQLite等数据库，这类数据库默认将||作为字符串拼接运算符，所以代码中的字段拼接逻辑可以正常输出拼接后的姓名、地址。
• 如果你使用的是MySQL数据库，默认配置下||是逻辑或运算符，不是字符串拼接符。代码中的customers.CustFirstName || " " || customers.CustLastName会被当做布尔运算处理：非空字符串做逻辑或运算的结果为真，转换为数值输出就是1，如果参与运算的字段存在空值，运算结果为假，输出就是0，完全匹配你看到的查询结果。

修复方案 #

如果要在MySQL中正常运行这段SQL，把所有||拼接逻辑替换为CONCAT函数即可，修改后的查询语句如下：

select CONCAT(customers.CustFirstName, " ", customers.CustLastName) as "Name", 
CONCAT(customers.CustStreetAddress, ",", customers.CustZipCode, ",", customers.CustState) as "Address",
count(engagements.EntertainerID) as "Number of Contracts",
sum(engagements.ContractPrice) as "Total Price",
max(engagements.ContractPrice) as "Max Price"
from customers
inner join engagements
on customers.CustomerID = engagements.CustomerID
group by customers.CustFirstName, customers.custlastname,
customers.CustStreetAddress,customers.CustState,customers.CustZipCode
order by customers.CustFirstName, customers.custlastname;

也可以执行SET sql_mode = 'PIPES_AS_CONCAT';临时修改当前会话的SQL模式，让MySQL支持||作为字符串拼接符。

内容的提问来源于stack exchange，提问作者An old man in the sea.