MySQL可正常执行的SQL查询在SQLite中执行失败无数据返回

以下SQL语句在MySQL中可以正常检索数据：

SELECT spot_lat AS lat, spot_lng AS lng, city, place AS title, wiki_img, wiki_url, id AS spot_id, categorie, period
FROM MY_TABLE 
WHERE
    (CASE WHEN 42.7394 < 45.14886
                THEN spot_lat BETWEEN 42.7394 AND 45.14886
                ELSE spot_lat BETWEEN 45.14886 AND 42.7394
    END) 
    AND
    (CASE WHEN 5.1278 < 6.1278
                THEN spot_lng BETWEEN 5.1278 AND 6.1278
                ELSE spot_lng BETWEEN 6.1278 AND 5.1278
    END) 

但在SQLite中执行时无法获取到数据，求原因及解决办法。

原因分析 #

SQLite与MySQL对CASE表达式的返回值处理存在差异：

• MySQL中，CASE可以直接返回BETWEEN判断的布尔结果（TRUE/FALSE），WHERE子句能直接识别并使用这个布尔值过滤数据。
• SQLite中，CASE表达式不会返回布尔类型，而是将BETWEEN的判断结果转换为整数（1代表真，0代表假）。虽然理论上SQLite会把0视为假、非0值视为真，但在这种嵌套判断的场景下，SQLite的执行逻辑无法正确将整数结果转换为WHERE子句需要的布尔条件，导致过滤逻辑失效，从而没有数据返回。

解决办法 #

用LEAST()和GREATEST()函数替代CASE逻辑，这种写法兼容MySQL和SQLite，且逻辑更简洁：

SELECT spot_lat AS lat, spot_lng AS lng, city, place AS title, wiki_img, wiki_url, id AS spot_id, categorie, period
FROM MY_TABLE 
WHERE
    spot_lat >= LEAST(42.7394, 45.14886) 
    AND spot_lat <= GREATEST(42.7394, 45.14886)
    AND spot_lng >= LEAST(5.1278, 6.1278)
    AND spot_lng <= GREATEST(5.1278, 6.1278)

LEAST()会返回两个值中的较小值，GREATEST()返回较大值，无论输入的两个数值顺序如何，都能正确实现范围过滤。

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