版本：8.0.11-TiDB-v7.5.0
环境：测试环境
SQL只是为了拼接分区表的一些语句，用的都是系统表
提供一个表结构：
CREATE TABLE `advertise_timing_order` (
`ruleUid` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '定时规则数据唯一编码',
`matching` char(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '匹配对象: campaign=活动,adgroup=广告组,keyword=关键词,target=定位',
`state` int NOT NULL DEFAULT '0' COMMENT '开启暂停10=开启20=暂停',
`matchUid` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '匹配对象 唯一ID',
`time` datetime NOT NULL COMMENT '修改时间',
`user` char(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '操作用户',
PRIMARY KEY (`ruleUid`,`matchUid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='亚马逊广告-定时任务已注册任务'
/*!50100 PARTITION BY KEY (ruleUid)
PARTITIONS 251 */;
报错 SQL：
SELECT
`data`.TABLE_NAME,
CONCAT(
'PARTITION BY ',
`data`.PARTITION_METHOD,
'(',
`data`.PARTITION_EXPRESSION,
') PARTITIONS ',
MAX(`data`.PARTITION_ORDINAL_POSITION),
`data`.sub_var,
' (',
GROUP_CONCAT( /*拼出分区主体*/
CONCAT(
'PARTITION `',
REPLACE(`data`.PARTITION_NAME, '`', '``'),
'`',
IF(
`data`.PARTITION_METHOD = 'RANGE',
CONCAT(' VALUES LESS THAN (', `data`.PARTITION_DESCRIPTION, ')'),
IF(
`data`.PARTITION_METHOD = 'LIST',
CONCAT(' VALUES IN (', `data`.PARTITION_DESCRIPTION, ')'),
''
)
),
IFNULL(
`data`.sub_body, /*子分区*/
CONCAT(
' COMMENT="',
REPLACE(`data`.PARTITION_COMMENT, '"', '\\"'),
'"'
)
)
)
ORDER BY `data`.PARTITION_ORDINAL_POSITION
SEPARATOR ', '
),
')'
) PARTITION_SQL,
CONCAT(
`data`.PARTITION_EXPRESSION, ',',
IFNULL(`data`.SUBPARTITION_EXPRESSION, '')
) COLUMNS_COMPARE /*字段变化证明*/
FROM
(SELECT
IF(
ISNULL(`PARTITIONS`.SUBPARTITION_ORDINAL_POSITION),
'',
CONCAT(
' SUBPARTITION BY ',
`PARTITIONS`.SUBPARTITION_METHOD,
'(',
`PARTITIONS`.SUBPARTITION_EXPRESSION,
') SUBPARTITIONS ',
MAX(`PARTITIONS`.SUBPARTITION_ORDINAL_POSITION)
)
) `sub_var`, /*子分区说明*/
IF(
ISNULL(`PARTITIONS`.SUBPARTITION_ORDINAL_POSITION),
NULL,
CONCAT(
' (',
GROUP_CONCAT(
CONCAT(
'SUBPARTITION `',
REPLACE(`PARTITIONS`.SUBPARTITION_NAME, '`', '``'),
'` COMMENT="',
REPLACE(`PARTITIONS`.PARTITION_COMMENT, '"', '\\"'),
'"'
)
ORDER BY `PARTITIONS`.SUBPARTITION_ORDINAL_POSITION
SEPARATOR ', '
),
')'
)
) `sub_body`, /*子分区主体*/
`PARTITIONS`.TABLE_NAME,
`PARTITIONS`.PARTITION_NAME,
`PARTITIONS`.PARTITION_ORDINAL_POSITION,
`PARTITIONS`.PARTITION_METHOD,
`PARTITIONS`.PARTITION_EXPRESSION,
`PARTITIONS`.PARTITION_DESCRIPTION,
`PARTITIONS`.PARTITION_COMMENT,
`PARTITIONS`.SUBPARTITION_EXPRESSION
FROM
`information_schema`.`PARTITIONS`
WHERE
`PARTITIONS`.TABLE_SCHEMA='test'
AND `PARTITIONS`.TABLE_NAME IN ('advertise_timing_order')
AND `PARTITIONS`.PARTITION_ORDINAL_POSITION IS NOT NULL
GROUP BY
`PARTITIONS`.TABLE_NAME, `PARTITIONS`.PARTITION_NAME) `data`
GROUP BY
`data`.TABLE_NAME
奇怪的是：注释掉GROUP_CONCAT里面的order by 执行又没问题，SQL如下：
SELECT
`data`.TABLE_NAME,
CONCAT(
'PARTITION BY ',
`data`.PARTITION_METHOD,
'(',
`data`.PARTITION_EXPRESSION,
') PARTITIONS ',
MAX(`data`.PARTITION_ORDINAL_POSITION),
`data`.sub_var,
' (',
GROUP_CONCAT( /*拼出分区主体*/
CONCAT(
'PARTITION `',
REPLACE(`data`.PARTITION_NAME, '`', '``'),
'`',
IF(
`data`.PARTITION_METHOD = 'RANGE',
CONCAT(' VALUES LESS THAN (', `data`.PARTITION_DESCRIPTION, ')'),
IF(
`data`.PARTITION_METHOD = 'LIST',
CONCAT(' VALUES IN (', `data`.PARTITION_DESCRIPTION, ')'),
''
)
),
IFNULL(
`data`.sub_body, /*子分区*/
CONCAT(
' COMMENT="',
REPLACE(`data`.PARTITION_COMMENT, '"', '\\"'),
'"'
)
)
)
#ORDER BY `data`.PARTITION_ORDINAL_POSITION
SEPARATOR ', '
),
')'
) PARTITION_SQL,
CONCAT(
`data`.PARTITION_EXPRESSION, ',',
IFNULL(`data`.SUBPARTITION_EXPRESSION, '')
) COLUMNS_COMPARE /*字段变化证明*/
FROM
(SELECT
IF(
ISNULL(`PARTITIONS`.SUBPARTITION_ORDINAL_POSITION),
'',
CONCAT(
' SUBPARTITION BY ',
`PARTITIONS`.SUBPARTITION_METHOD,
'(',
`PARTITIONS`.SUBPARTITION_EXPRESSION,
') SUBPARTITIONS ',
MAX(`PARTITIONS`.SUBPARTITION_ORDINAL_POSITION)
)
) `sub_var`, /*子分区说明*/
IF(
ISNULL(`PARTITIONS`.SUBPARTITION_ORDINAL_POSITION),
NULL,
CONCAT(
' (',
GROUP_CONCAT(
CONCAT(
'SUBPARTITION `',
REPLACE(`PARTITIONS`.SUBPARTITION_NAME, '`', '``'),
'` COMMENT="',
REPLACE(`PARTITIONS`.PARTITION_COMMENT, '"', '\\"'),
'"'
)
ORDER BY `PARTITIONS`.SUBPARTITION_ORDINAL_POSITION
SEPARATOR ', '
),
')'
)
) `sub_body`, /*子分区主体*/
`PARTITIONS`.TABLE_NAME,
`PARTITIONS`.PARTITION_NAME,
`PARTITIONS`.PARTITION_ORDINAL_POSITION,
`PARTITIONS`.PARTITION_METHOD,
`PARTITIONS`.PARTITION_EXPRESSION,
`PARTITIONS`.PARTITION_DESCRIPTION,
`PARTITIONS`.PARTITION_COMMENT,
`PARTITIONS`.SUBPARTITION_EXPRESSION
FROM
`information_schema`.`PARTITIONS`
WHERE
`PARTITIONS`.TABLE_SCHEMA='test'
AND `PARTITIONS`.TABLE_NAME IN ('advertise_timing_order')
AND `PARTITIONS`.PARTITION_ORDINAL_POSITION IS NOT NULL
GROUP BY
`PARTITIONS`.TABLE_NAME, `PARTITIONS`.PARTITION_NAME) `data`
GROUP BY
`data`.TABLE_NAME
同时注释部分其他SQL，执行也不报错：
SELECT
# `data`.TABLE_NAME,
# CONCAT(
# 'PARTITION BY ',
# `data`.PARTITION_METHOD,
# '(',
# `data`.PARTITION_EXPRESSION,
# ') PARTITIONS ',
# MAX(`data`.PARTITION_ORDINAL_POSITION),
# `data`.sub_var,
# ' (',
GROUP_CONCAT( /*拼出分区主体*/
CONCAT(
'PARTITION `',
REPLACE(`data`.PARTITION_NAME, '`', '``'),
'`',
IF(
`data`.PARTITION_METHOD = 'RANGE',
CONCAT(' VALUES LESS THAN (', `data`.PARTITION_DESCRIPTION, ')'),
IF(
`data`.PARTITION_METHOD = 'LIST',
CONCAT(' VALUES IN (', `data`.PARTITION_DESCRIPTION, ')'),
''
)
),
IFNULL(
`data`.sub_body, /*子分区*/
CONCAT(
' COMMENT="',
REPLACE(`data`.PARTITION_COMMENT, '"', '\\"'),
'"'
)
)
)
ORDER BY `data`.PARTITION_ORDINAL_POSITION
SEPARATOR ', '
) aa
# ')'
#) PARTITION_SQL,
#CONCAT(
# `data`.PARTITION_EXPRESSION, ',',
# IFNULL(`data`.SUBPARTITION_EXPRESSION, '')
#) COLUMNS_COMPARE /*字段变化证明*/
FROM
(SELECT
IF(
ISNULL(`PARTITIONS`.SUBPARTITION_ORDINAL_POSITION),
'',
CONCAT(
' SUBPARTITION BY ',
`PARTITIONS`.SUBPARTITION_METHOD,
'(',
`PARTITIONS`.SUBPARTITION_EXPRESSION,
') SUBPARTITIONS ',
MAX(`PARTITIONS`.SUBPARTITION_ORDINAL_POSITION)
)
) `sub_var`, /*子分区说明*/
IF(
ISNULL(`PARTITIONS`.SUBPARTITION_ORDINAL_POSITION),
NULL,
CONCAT(
' (',
GROUP_CONCAT(
CONCAT(
'SUBPARTITION `',
REPLACE(`PARTITIONS`.SUBPARTITION_NAME, '`', '``'),
'` COMMENT="',
REPLACE(`PARTITIONS`.PARTITION_COMMENT, '"', '\\"'),
'"'
)
ORDER BY `PARTITIONS`.SUBPARTITION_ORDINAL_POSITION
SEPARATOR ', '
),
')'
)
) `sub_body`, /*子分区主体*/
`PARTITIONS`.TABLE_NAME,
`PARTITIONS`.PARTITION_NAME,
`PARTITIONS`.PARTITION_ORDINAL_POSITION,
`PARTITIONS`.PARTITION_METHOD,
`PARTITIONS`.PARTITION_EXPRESSION,
`PARTITIONS`.PARTITION_DESCRIPTION,
`PARTITIONS`.PARTITION_COMMENT,
`PARTITIONS`.SUBPARTITION_EXPRESSION
FROM
`information_schema`.`PARTITIONS`
WHERE
`PARTITIONS`.TABLE_SCHEMA='test'
AND `PARTITIONS`.TABLE_NAME IN ('advertise_timing_order')
AND `PARTITIONS`.PARTITION_ORDINAL_POSITION IS NOT NULL
GROUP BY
`PARTITIONS`.TABLE_NAME, `PARTITIONS`.PARTITION_NAME) `data`
GROUP BY
`data`.TABLE_NAME
请问这是踩到BUG了 ？
