摘要:
in句子在经过hash join处理优化后, 对于多个字符串的范围的处理，结果不符合预期。
本文记录复现过程, 需要注意对于in句子需要在使用hash join优化后的逻辑处理中。
SQL的DDL:
表结构-字符串和日期类型:
drop table tasks;
CREATE TABLE IF NOT EXISTS tasks (
task_id INT(11) AUTO_INCREMENT,
subject VARCHAR(45) DEFAULT NULL,
start_date DATE DEFAULT NULL,
end_date DATE DEFAULT NULL,
description VARCHAR(200) DEFAULT NULL,
PRIMARY KEY (task_id)
)ENGINE=TIANMU DEFAULT CHARSET=utf8;
插入数据-字符串和日期类型:
INSERT INTO tasks(subject,start_date,end_date,description)
VALUES('task-0','2017-07-21','2017-07-22','Start learning..');
INSERT INTO tasks(subject,start_date,end_date,description)
VALUES ('task-1','2017-01-01','2017-01-02','Description 1'),
('task-2','2017-01-01','2017-01-02','Description 2'),
('task-3','2017-01-01','2017-01-02','Description 3');
表结构-整形数据
CREATE TABLE `t2` (
`id` int(11) DEFAULT NULL,
`val` int(11)
) ENGINE=TIANMU;
插入数据-整形数据:
insert into t2 values(1, 11);
insert into t2 values(2, 22);
insert into t2 values(3, 33);
结果预期测试:
in查询主键整形数据:
查询结果符合预期
mysql> select * from tasks where task_id in (1,2,3);
+---------+---------+------------+------------+------------------+
| task_id | subject | start_date | end_date | description |
+---------+---------+------------+------------+------------------+
| 1 | task-0 | 2017-07-21 | 2017-07-22 | Start learning.. |
| 2 | task-1 | 2017-01-01 | 2017-01-02 | Description 1 |
| 3 | task-2 | 2017-01-01 | 2017-01-02 | Description 2 |
+---------+---------+------------+------------+------------------+
3 rows in set (0.01 sec)
in查询字符串数据, 但只有一个元素:
查询结果符合预期
mysql> select * from tasks where subject in ('task-0');
+---------+---------+------------+------------+------------------+
| task_id | subject | start_date | end_date | description |
+---------+---------+------------+------------+------------------+
| 1 | task-0 | 2017-07-21 | 2017-07-22 | Start learning.. |
+---------+---------+------------+------------+------------------+
1 row in set (0.00 sec)
mysql> select * from tasks where subject in ('task-1');
+---------+---------+------------+------------+---------------+
| task_id | subject | start_date | end_date | description |
+---------+---------+------------+------------+---------------+
| 2 | task-1 | 2017-01-01 | 2017-01-02 | Description 1 |
+---------+---------+------------+------------+---------------+
1 row in set (0.00 sec)
in查询字符串数据, 范围包含多个字符串元素:
查询结果不符合预期
mysql> select * from tasks where subject in ('task-0', 'task-1');
+---------+---------+------------+------------+------------------+
| task_id | subject | start_date | end_date | description |
+---------+---------+------------+------------+------------------+
| 1 | task-0 | 2017-07-21 | 2017-07-22 | Start learning.. |
| 2 | task-1 | 2017-01-01 | 2017-01-02 | Description 1 |
| 3 | task-2 | 2017-01-01 | 2017-01-02 | Description 2 |
| 4 | task-3 | 2017-01-01 | 2017-01-02 | Description 3 |
+---------+---------+------------+------------+------------------+
4 rows in set (0.00 sec)
in查询整形数据, 但只有一个元素:
查询结果符合预期
mysql> select * from t2 where val in(11);
+------+------+
| id | val |
+------+------+
| 1 | 11 |
+------+------+
1 row in set (0.00 sec)
mysql> select * from t2 where val in(22);
+------+------+
| id | val |
+------+------+
| 2 | 22 |
+------+------+
1 row in set (0.00 sec)
in查询整形数据, 范围包含多个整形元素:
查询结果不符合预期
mysql> select * from t2 where val in(11,22);
+------+------+
| id | val |
+------+------+
| 1 | 11 |
| 2 | 22 |
| 3 | 33 |
+------+------+
3 rows in set (0.00 sec)
