时间戳

首页 > 解决方案 > 内部选择语句的不同结果

sqlite - 内部选择语句的不同结果

问题描述

这个查询给了我 7 个结果：

sqlite> SELECT DISTINCT film.id FROM film 
INNER JOIN film_release ON film_release.fk_film = film.id 
INNER JOIN film_release_files ON film_release.id = film_release_files.fk_film_release 
INNER JOIN file ON file.id = film_release_files.fk_file 
ORDER BY download_date DESC LIMIT 7;
id        
----------
4525      
4530      
4529      
4528      
4527      
4524      
4526

如果我通过将它们放在 IN 子句中来选择这 7 个 ID，我只会得到 6 个结果：

sqlite> SELECT DISTINCT film.id FROM film 
INNER JOIN film_details ON film.id = film_details.fk_film WHERE film.id in (
   SELECT DISTINCT film.id FROM film 
   INNER JOIN film_release ON film_release.fk_film = film.id 
   INNER JOIN film_release_files ON film_release.id = film_release_files.fk_film_release 
   INNER JOIN file ON file.id = film_release_files.fk_file 
   ORDER BY download_date DESC LIMIT 7
) 
ORDER BY film.title, film.year;
id        
----------
4525      
4524      
4527      
4528      
4529      
4530

但是，如果我将内部查询的结果复制并粘贴为列表，而不是 select 语句，我会得到全部 7 个：

sqlite> SELECT DISTINCT film.id FROM film INNER JOIN film_details ON film.id = film_details.fk_film WHERE film.id in (
   4525, 4530, 4529, 4528, 4527, 4524, 4526
) ORDER BY film.title, film.year;
id        
----------
4525      
4524      
4526      
4527      
4528      
4529      
4530

这是怎么回事？有没有解决方法来解决这个问题？我在这里误解了什么，还是一个错误？

我在 3.8.7.1 2014-10-29 13:59:56 3b7b72c4685aa5cf5e675c2c47ebec10d9704221 中看到了这一点。但我在 3.8.2 2013-12-06 14:53:30 27392118af4c38c5203a04b8013e1afdb1cebd0d 中没有看到它与同一个数据库

标签： sqlite

解决方案

Is is probably this issue https://www.sqlite.org/src/info/db87229497 which was introduced in version 3.8.6

This bug can be hit when an IN operator has a subquery on its right-hand side and the subquery contains both a DISTINCT keyword and a LIMIT clause.

it is fixed in version 3.8.7.2.

A workaround for version 3.8.7.1 would be not to use DISTINCT and LIMIT combined in IN statements.

Nevertheless 3.8.7.2 is from 2014 and you should really switch to the most recent version if possible.

Found searching on this site https://www.sqlite.org/changes.html

推荐阅读