MariaDB 10.0.36中IF与INNER JOIN组合致小数显示异常问题

这是MariaDB 10.0.36版本的类型推断BUG：

• 当IF()函数与INNER JOIN同时使用时，优化器对IF()返回值的类型处理逻辑异常。
• IF()的两个返回分支分别是带小数的数值类型和空字符串类型，单独使用IF()或INNER JOIN时，类型推断能正常保留小数；但两者结合后，针对≥100的数值，优化器错误将其强制转换为整数类型，导致小数部分丢失。

以下几种方案可规避该BUG，无需升级版本：

1. 显式转换数值为字符串 #

强制将数值类型转换为字符串，确保IF()的两个分支返回相同类型，避免优化器错误推断：

select VIS.idVisite as 'key',
       if(VIS.weightVisite is not NULL, CAST(VIS.weightVisite AS CHAR), '') as 'weight' 
from visite as VIS 
inner join client as CLI on VIS.idClientVisite=CLI.idClient 
where VIS.idVisite in (1,2,3,4) 
order by VIS.idVisite

如果需要保留固定小数位数，可改用精度控制的转换：

if(VIS.weightVisite is not NULL, CAST(VIS.weightVisite AS DECIMAL(10,2)), '')

2. 替换IF为CASE语句 #

CASE语句的类型推断逻辑相对更稳定，可替代IF函数：

select VIS.idVisite as 'key',
       CASE WHEN VIS.weightVisite IS NOT NULL THEN CAST(VIS.weightVisite AS CHAR) ELSE '' END as 'weight'
from visite as VIS 
inner join client as CLI on VIS.idClientVisite=CLI.idClient 
where VIS.idVisite in (1,2,3,4) 
order by VIS.idVisite

3. 先子查询处理IF，再执行JOIN #

将单独使用IF的逻辑放到子查询中，再与client表关联，绕开优化器的异常逻辑：

SELECT sub.idVisite as 'key', sub.weight as 'weight'
FROM (
    select VIS.idVisite, 
           if(VIS.weightVisite is not NULL, VIS.weightVisite, '') as 'weight',
           VIS.idClientVisite
    from visite as VIS 
    where VIS.idVisite in (1,2,3,4)
) sub
inner join client as CLI on sub.idClientVisite=CLI.idClient 
order by sub.idVisite

内容的提问来源于stack exchange，提问作者Juan