PostgreSQL查询结果错误一例

来源：互联网发布：淘宝和京东的相同点编辑：程序博客网时间：2024/06/10 02:01

一创建表

CREATE TABLE t1 (a int, b int);
CREATE TABLE t2 (c int, d int);
CREATE TABLE t3 (e int);

INSERT INTO t1 VALUES
(1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);
INSERT INTO t2 VALUES
(2,10), (2,20), (4,10), (5,10), (3,20), (2,40);
INSERT INTO t3 VALUES (10), (30), (10), (20) ;

二 PostgreSQL 版本

test=# select version();
version
-------------------------------------------------------------
PostgreSQL 9.3.4, compiled by Visual C++ build 1700, 64-bit
(1 row)

三执行查询(按SQL的语义,应该得到的结果是a列值为1和2,没有3和4)

test=# SELECT t1.a FROM t1
test-# GROUP BY t1.a
test-#   HAVING t1.a < ALL(
test(#     SELECT t2.c FROM t2
test(#     GROUP BY t2.c
test(#       HAVING EXISTS(
test(#         SELECT t3.e FROM t3
test(#         GROUP BY t3.e
test(#           HAVING SUM(t1.a+t2.c) < t3.e/4));
a
---
4
1
3
2
(4 rows)

四查看查询执行计划

test=# EXPLAIN
test-# SELECT t1.a FROM t1
test-# GROUP BY t1.a
test-#   HAVING t1.a < ALL(
test(#     SELECT t2.c FROM t2
test(#     GROUP BY t2.c
test(#       HAVING EXISTS(
test(#         SELECT t3.e FROM t3
test(#         GROUP BY t3.e
test(#           HAVING SUM(t1.a+t2.c) < t3.e/4));
                                QUERY PLAN
---------------------------------------------------------------------------
HashAggregate (cost=36.75..969979.25 rows=200 width=4)
   Filter: (SubPlan 2)
   -> Seq Scan on t1 (cost=0.00..31.40 rows=2140 width=4)
   SubPlan 2
     -> HashAggregate (cost=47.45..9651.45 rows=200 width=4)
           Filter: (SubPlan 1)
           -> Seq Scan on t2 (cost=0.00..31.40 rows=2140 width=4)
           SubPlan 1
             -> HashAggregate (cost=48.00..48.67 rows=67 width=4)
                   -> Seq Scan on t3 (cost=0.00..46.00 rows=800 width=4)
                         Filter: (sum((t1.a + t2.c)) < (e / 4))
(11 rows)

五 MySQL的执行结果

mysql> SELECT t1.a FROM t1
    -> GROUP BY t1.a
    ->   HAVING t1.a < ALL(
    ->     SELECT t2.c FROM t2
    ->     GROUP BY t2.c
    ->       HAVING EXISTS(
    ->         SELECT t3.e FROM t3
    ->         GROUP BY t3.e
    ->           HAVING SUM(t1.a+t2.c) < t3.e/4));
+------+
| a    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.01 sec)

================
附记: 有朋友联系我说:
PostgreSQL查询结果错误一例 HAVING SUM(t1.a+t2.c) < t3.e/4));   postgres 和mysql在除法操作 / 处理不一样，如果都是int，pg会截断；而mysql不会； select 10/4; 和 select 10/4.0 结果不一样
这话确实对. 所以进一步解释如下(PostgreSQL 1到3例, 4例是MySQL):
(1)把t3.e/4变为t3.e/4.0,这样,后者比前者更大一些(更容易使得条件满足),但是得到的结果是:a为1和2
test=# SELECT t1.a FROM t1
test-# GROUP BY t1.a
test-#   HAVING t1.a < ALL(
test(#     SELECT t2.c FROM t2
test(#     GROUP BY t2.c
test(#       HAVING EXISTS(
test(#         SELECT t3.e FROM t3
test(#         GROUP BY t3.e
test(#           HAVING SUM(t1.a+t2.c) < t3.e/4.0));
a
---
1
2
(2 rows)

(2) 而 SUM(t1.a+t2.c) < t3.e/4<t3.e/4.0 , 结果得到a列值为4,1,3,2; 这显然不对(对比上例).
test=# SELECT t1.a FROM t1
test-# GROUP BY t1.a
test-#   HAVING t1.a < ALL(
test(#     SELECT t2.c FROM t2
test(#     GROUP BY t2.c
test(#       HAVING EXISTS(
test(#         SELECT t3.e FROM t3
test(#         GROUP BY t3.e
test(#           HAVING SUM(t1.a+t2.c) < t3.e/4));
a
---
4
1
3
2
(4 rows)

(3) 验证t3.e/4, t3.e/4.0的大小关系
test=# select t3.e/4, t3.e/4.0 from t3;
?column? |      ?column?
----------+--------------------
        2 | 2.5000000000000000
        7 | 7.5000000000000000
        2 | 2.5000000000000000
        5 | 5.0000000000000000
(4 rows)

(4) MySQL一例
mysql> select 10/4, 10/4.0;
+--------+--------+
| 10/4   | 10/4.0 |
+--------+--------+
| 2.5000 | 2.5000 |
+--------+--------+
1 row in set (0.00 sec)

0 0