带子查询的 MySQL 谜题

Question

编辑:看起来像一个索引问题，在问题底部更新

我有以下查询 + 子查询，其结果我无法解释。我从这个最小的输入数据集开始(这里的应用程序正在捕获数据变化，PK 是 id + tx_id)。

mysql> select * from tag_version;
+----+-------------------+------------+-------+----------------+
| id | name              | article_id | tx_id | operation_type |
+----+-------------------+------------+-------+----------------+
|  1 | some tag          |          1 |     1 |              0 |
|  1 | updated tag       |          1 |     2 |              1 |
|  1 | updated again tag |          1 |     3 |              1 |
|  2 | other tag         |          1 |     2 |              0 |
+----+-------------------+------------+-------+----------------+
4 rows in set (0.00 sec)

独立的子查询

SELECT max(f.tx_id) as max_tx_id, f.id
from tag_version f
WHERE f.tx_id <= 2
GROUP BY f.id

结果是

+-----------+----+
| max_tx_id | id |
+-----------+----+
|         2 |  1 |
|         2 |  2 |
+-----------+----+
2 rows in set (0.00 sec)

查询，我在其中手动注入(inject)子查询结果，注意它们与上面的相等

select t.*
from tag_version t
where t.article_id = 1
AND (t.tx_id, t.id) IN (
    (2,1),
    (2,2)
)

有预期的结果

+----+-------------+------------+-------+----------------+
| id | name        | article_id | tx_id | operation_type |
+----+-------------+------------+-------+----------------+
|  1 | updated tag |          1 |     2 |              1 |
|  2 | other tag   |          1 |     2 |              0 |
+----+-------------+------------+-------+----------------+
2 rows in set (0.00 sec)

最后，使用子查询代替元组...

select t.*
from tag_version t
where t.article_id = 1
AND (t.tx_id, t.id) IN (
    SELECT max(f.tx_id) as tx_id, f.id
    from tag_version f
    WHERE f.tx_id <= 2
    GROUP BY f.id
)

结果是Empty set (0.00 sec) !有人可以解释一下吗？当我使用 EXISTS 重新编写查询时，我得到相同的空结果而不是 IN

我注意到当我删除行 WHERE f.tx_id <= 2 时从子查询中，我实际上得到了结果(尽管是错误的):

+----+-------------------+------------+-------+----------------+
| id | name              | article_id | tx_id | operation_type |
+----+-------------------+------------+-------+----------------+
|  1 | updated again tag |          1 |     3 |              1 |
|  2 | other tag         |          1 |     2 |              0 |
+----+-------------------+------------+-------+----------------+
2 rows in set (0.00 sec)

用 JOIN 替换子查询实际上返回预期的正确结果

SELECT t.*
FROM tag_version t
JOIN (
    SELECT max(f.tx_id) as max_tx_id, f.id
    from tag_version f
    WHERE f.tx_id <= 2
    GROUP BY f.id
) as max_ids
ON max_ids.max_tx_id = t.tx_id
AND max_ids.id = t.id
where t.article_id = 1

结果:

+----+-------------+------------+-------+----------------+
| id | name        | article_id | tx_id | operation_type |
+----+-------------+------------+-------+----------------+
|  1 | updated tag |          1 |     2 |              1 |
|  2 | other tag   |          1 |     2 |              0 |
+----+-------------+------------+-------+----------------+
2 rows in set (0.00 sec)

此外，使用 PostgreSQL 和 SQLite 在同一数据集上运行相同的查询+子查询会给出预期的正确结果。

我的 MySQL 版本是 Server version: 5.5.40-0ubuntu0.14.04.1 (Ubuntu) .

我认为弄清楚发生了什么的线索是，当我删除 WHERE 时，我实际上得到了结果。来自子查询，但我不能从中得到有用的东西。

编辑:更新了输入数据集

编辑:添加表格信息

建表语句如下

CREATE TABLE `tag_version` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `article_id` int(11) DEFAULT NULL,
  `tx_id` bigint(20) NOT NULL,
  `operation_type` smallint(6) NOT NULL,
  PRIMARY KEY (`id`,`tx_id`),
  KEY `ix_tag_version_operation_type` (`operation_type`),
  KEY `ix_tag_version_tx_id` (`tx_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

还有数据填充

insert into tag_version (id, name, article_id, tx_id, operation_type) VALUES 
(1, 'some tag', 1, 1, 0),
(1, 'updated tag', 1, 2, 1),
(1, 'updated again tag', 1, 3, 1),
(2, 'other tag', 1, 2, 0)
;

当我删除 ix_tag_version_tx_id索引，查询返回正确的结果......解释为什么会有用。

Answer 1

我相信您在显示第一个代码(子查询)的结果时犯了错误。

此查询的输出:

SELECT max(f.tx_id) as qwer, f.id
from tag_version f
WHERE f.tx_id <= 2
GROUP BY f.id

-不是:

+--------------+----+
| max(f.tx_id) | id |
+--------------+----+
|            2 |  1 |
|            2 |  2 |
+--------------+----+

它是:

+------+----+
| qwer | id |
+------+----+
|   2  |  1 |
|   2  |  2 |
+------+----+

(*注意:代码行max(f.tx_id) as qwer *)

现在为扩展输出尝试这段代码。选择max(f.tx_id)时会有变化。

select t.*
from tag_version t
where t.`article_id` = 1
AND t.`operation_type` != 2
AND (t.`tx_id`, t.`id`) IN (
    SELECT max(f.`tx_id`) as `tx_id`, f.`id`
    from tag_version f
    WHERE f.`tx_id` <= 2
    GROUP BY f.`id`
)

如果这给了你结果或任何其他错误，请告诉我。