mysql嵌套子查询Materialization（物化）影响
问题
在select嵌套子select时，出现group by 失效的情况
原始数据
| id |
t1 |
t2 |
t3 |
t4 |
created_at |
| 3 |
1 |
11 |
|
|
2021-06-20 21:00:01.540233 |
| 4 |
1 |
22 |
|
|
2021-06-20 21:00:06.557704 |
| 5 |
1 |
33 |
|
|
2021-06-20 21:00:19.692238 |
| 6 |
2 |
11 |
|
|
2021-06-20 21:00:24.792786 |
| 7 |
2 |
22 |
|
|
2021-06-20 21:00:30.355809 |
| 8 |
2 |
33 |
|
|
2021-06-20 21:00:35.397191 |
预计结果
| id |
t1 |
t2 |
t3 |
t4 |
created_at |
| 3 |
1 |
11 |
|
|
2021-06-20 21:00:01.540233 |
| 6 |
2 |
11 |
|
|
2021-06-20 21:00:24.792786 |
验证
SQL
SELECT
*
FROM
test
WHERE
id IN (
SELECT
id
FROM
test
GROUP BY
t1)
结果
| id |
t1 |
t2 |
t3 |
t4 |
created_at |
| 3 |
1 |
11 |
|
|
2021-06-20 21:00:01.540233 |
| 4 |
1 |
22 |
|
|
2021-06-20 21:00:06.557704 |
| 5 |
1 |
33 |
|
|
2021-06-20 21:00:19.692238 |
| 6 |
2 |
11 |
|
|
2021-06-20 21:00:24.792786 |
| 7 |
2 |
22 |
|
|
2021-06-20 21:00:30.355809 |
| 8 |
2 |
33 |
|
|
2021-06-20 21:00:35.397191 |
原因
Materialization（物化），in子查询确实会忽略group/order by，比如加having进去，就会报错
正确SQL
SELECT
*
FROM
test
WHERE
id IN (
SELECT
*
FROM
( SELECT id FROM test GROUP BY t1 ) t)
结果
| id |
t1 |
t2 |
t3 |
t4 |
created_at |
| 3 |
1 |
11 |
|
|
2021-06-20 21:00:01.540233 |
| 6 |
2 |
11 |
|
|
2021-06-20 21:00:24.792786 |
物化相关文章
http://mysql.taobao.org/monthly/2016/07/08/
