MySQL 使用子查询、内连接和 order by 会产生奇怪的结果

Question

我遇到了奇怪的查询行为，但我不知道为什么它没有按我的预期工作。

这是问题的清晰再现:

create table A (
  id int not null auto_increment,
  primary key (id)
) ENGINE=InnoDB;

create table B (
  id int not null auto_increment,
  a_id int not null,
  qty double not null,
  primary key (id),
  key IDX_A (a_id)
) ENGINE=InnoDB;

create table C (
  id int not null auto_increment,
  b_id int not null,
  qty double not null,
  primary key (id),
  key IDX_B (b_id)
) ENGINE=InnoDB;

insert into A (id) values (1);

insert into B (id, a_id, qty) values (1, 1, 10);
insert into B (id, a_id, qty) values (2, 1, 15);
insert into B (id, a_id, qty) values (3, 1, 2);

insert into C (id, b_id, qty) values (1, 1, 7);
insert into C (id, b_id, qty) values (2, 1, 3);
insert into C (id, b_id, qty) values (3, 2, 3);

ALTER TABLE `B` ADD CONSTRAINT FK_BA FOREIGN KEY (a_id) REFERENCES A (id);
ALTER TABLE `C` ADD CONSTRAINT FK_CB FOREIGN KEY (b_id) REFERENCES B (id);

这是查询:

SELECT 
  b.id as b_id,
  b.qty as b_qty
FROM 
  B b
  INNER JOIN A a ON B.a_id = A.id 
WHERE 
  EXISTS (
    SELECT 1 FROM C c 
    WHERE 
      c.b_id = b.id 
    HAVING 
      sum(c.qty) = b.qty
  )
ORDER BY b.id

我期待这个结果:

+------+-------+
| b_id | b_qty |
+------+-------+
|    1 |    10 |
+------+-------+

但是这个查询以某种方式给出了一个空的结果集。

有点兴趣:

当我从 select 子句中删除“b.qty”时，它工作正常。如果我删除内部联接或 order by 子句，它也会起作用。

我失败了还是这是一个错误？

在 5.6.26 和 5.5.34 上测试。

Answer 1

似乎要求 HAVING 子句中的列包含在 SELECT 列表中，以下工作:

SELECT 
  b.id AS b_id,
  b.qty AS b_qty
FROM 
  B b
  INNER JOIN A a ON b.a_id = a.id 
WHERE 
  EXISTS (
    SELECT b.qty,SUM(c.qty) FROM C c 
    WHERE 
      c.b_id = b.id 
    HAVING 
      SUM(c.qty) = b.qty
  )
ORDER BY b.id;

看起来仍然是很奇怪的行为。分析它，我发现如果不包含列，则拉出子选择实际上会给出错误，这会给出错误:

SELECT 1 FROM C c ,B b
WHERE 
c.b_id = b.id 
GROUP BY c.b_id
HAVING 
SUM(c.qty) = b.qty

这不会:

SELECT * FROM C c ,B b
WHERE 
c.b_id = b.id 
GROUP BY c.b_id
HAVING 
SUM(c.qty) = b.qty

所以看起来这个错误是它应该给出错误而不是错误的结果。