为什么SELECT结果在mysql和sqlite之间有所不同? [英] Why does SELECT results differ between mysql and sqlite?
问题描述
我在问这个问题以简化和扩展的方式.
考虑以下sql语句:
create table foo (id INT, score INT);
insert into foo values (106, 4);
insert into foo values (107, 3);
insert into foo values (106, 5);
insert into foo values (107, 5);
select T1.id, avg(T1.score) avg1
from foo T1
group by T1.id
having not exists (
select T2.id, avg(T2.score) avg2
from foo T2
group by T2.id
having avg2 > avg1);
使用sqlite,select
语句返回:
id avg1
---------- ----------
106 4.5
107 4.0
并且mysql返回:
+------+--------+
| id | avg1 |
+------+--------+
| 106 | 4.5000 |
+------+--------+
据我所知,mysql的结果是正确的,而sqlite的结果是不正确的.我尝试使用sqlite强制转换为real
,如下所示,但它仍返回两条记录:
select T1.id, cast(avg(cast(T1.score as real)) as real) avg1
from foo T1
group by T1.id
having not exists (
select T2.id, cast(avg(cast(T2.score as real)) as real) avg2
from foo T2
group by T2.id
having avg2 > avg1);
为什么sqlite返回两个记录?
快速更新:
我针对最新的sqlite版本(3.7.11)运行了该语句,但仍然获得了两条记录.
另一个更新:
我已将有关此问题的电子邮件发送至sqlite-users@sqlite.org.
我自己,我一直在玩VDBE,发现了一些有趣的东西.我拆分了not exists
的每个循环的执行跟踪(每个avg组一个).
要具有三个平均组,我使用了以下语句:
create table foo (id VARCHAR(1), score INT);
insert into foo values ('c', 1.5);
insert into foo values ('b', 5.0);
insert into foo values ('a', 4.0);
insert into foo values ('a', 5.0);
PRAGMA vdbe_listing = 1;
PRAGMA vdbe_trace=ON;
select avg(score) avg1
from foo
group by id
having not exists (
select avg(T2.score) avg2
from foo T2
group by T2.id
having avg2 > avg1);
我们清楚地看到r:4.5
应该变成了i:5
:
我现在正试图找出原因.
最终编辑:
因此,我一直在充分利用sqlite源代码.我现在对野兽有了更好的了解,尽管我会让原始开发人员像他看来的那样进行整理已经做到了:
http://www.sqlite.org/src/info/430bb59d79
至少对我而言,有趣的是,似乎较新的版本(在我使用的版本之后多次)支持插入上述提交中添加的测试用例中使用的多条记录:
CREATE TABLE t34(x,y);
INSERT INTO t34 VALUES(106,4), (107,3), (106,5), (107,5);
我试图弄乱查询的某些变体.
似乎sqlite在嵌套的 HAVING 表达式中使用先前声明的字段时出错.
在您的示例中,avg1
下的秒数始终等于5.0
看:
select T1.id, avg(T1.score) avg1
from foo T1
group by T1.id
having not exists (
SELECT 1 AS col1 GROUP BY col1 HAVING avg1 = 5.0);
此记录不返回任何内容,但是执行以下查询将返回两个记录:
...
having not exists (
SELECT 1 AS col1 GROUP BY col1 HAVING avg1 <> 5.0);
我在 sqlite票证列表中找不到任何类似的错误.. >
I'm re-asking this question in a simplified and expanded manner.
Consider these sql statements:
create table foo (id INT, score INT);
insert into foo values (106, 4);
insert into foo values (107, 3);
insert into foo values (106, 5);
insert into foo values (107, 5);
select T1.id, avg(T1.score) avg1
from foo T1
group by T1.id
having not exists (
select T2.id, avg(T2.score) avg2
from foo T2
group by T2.id
having avg2 > avg1);
Using sqlite, the select
statement returns:
id avg1
---------- ----------
106 4.5
107 4.0
and mysql returns:
+------+--------+
| id | avg1 |
+------+--------+
| 106 | 4.5000 |
+------+--------+
As far as I can tell, mysql's results are correct, and sqlite's are incorrect. I tried to cast to real
with sqlite as in the following but it returns two records still:
select T1.id, cast(avg(cast(T1.score as real)) as real) avg1
from foo T1
group by T1.id
having not exists (
select T2.id, cast(avg(cast(T2.score as real)) as real) avg2
from foo T2
group by T2.id
having avg2 > avg1);
Why does sqlite return two records?
Quick update:
I ran the statement against the latest sqlite version (3.7.11) and still get two records.
Another update:
I sent an email to sqlite-users@sqlite.org about the issue.
Myself, I've been playing with VDBE and found something interesting. I split the execution trace of each loop of not exists
(one for each avg group).
To have three avg groups, I used the following statements:
create table foo (id VARCHAR(1), score INT);
insert into foo values ('c', 1.5);
insert into foo values ('b', 5.0);
insert into foo values ('a', 4.0);
insert into foo values ('a', 5.0);
PRAGMA vdbe_listing = 1;
PRAGMA vdbe_trace=ON;
select avg(score) avg1
from foo
group by id
having not exists (
select avg(T2.score) avg2
from foo T2
group by T2.id
having avg2 > avg1);
We clearly see that somehow what should be r:4.5
has become i:5
:
I'm now trying to see why that is.
Final edit:
So I've been playing enough with the sqlite source code. I understand the beast much better now, although I'll let the original developer sort it out as he seems to already be doing it:
http://www.sqlite.org/src/info/430bb59d79
Interestingly, to me at least, it seems that the newer versions (some times after the version I'm using) supports inserting multiple records as used in a test case added in the aforementioned commit:
CREATE TABLE t34(x,y);
INSERT INTO t34 VALUES(106,4), (107,3), (106,5), (107,5);
I tried to mess with some variants of query.
It seems, like sqlite has errors in using of previous declared fields in a nested HAVING expressions.
In your example avg1
under second having is always equal to 5.0
Look:
select T1.id, avg(T1.score) avg1
from foo T1
group by T1.id
having not exists (
SELECT 1 AS col1 GROUP BY col1 HAVING avg1 = 5.0);
This one returns nothing, but execution of the following query returns both records:
...
having not exists (
SELECT 1 AS col1 GROUP BY col1 HAVING avg1 <> 5.0);
I can not find any similar bug at sqlite tickets list.
这篇关于为什么SELECT结果在mysql和sqlite之间有所不同?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!