Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: 8.0.11-TiDB-v7.5.0版本 执行SQL报错：runtime error: index out of range [1] with length 0
|
username: CAICAI
Version: 8.0.11-TiDB-v7.5.0
Environment: Test Environment
The SQL is just to concatenate some statements for partition tables, all using system tables.
Providing a table structure:
CREATE TABLE `advertise_timing_order` (
`ruleUid` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Unique code for scheduled rule data',
`matching` char(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Matching object: campaign=activity, adgroup=ad group, keyword=keyword, target=targeting',
`state` int NOT NULL DEFAULT '0' COMMENT 'Start/Pause 10=Start 20=Pause',
`matchUid` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Unique ID of the matching object',
`time` datetime NOT NULL COMMENT 'Modification time',
`user` char(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Operating user',
PRIMARY KEY (`ruleUid`,`matchUid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Amazon Ads - Registered Scheduled Tasks'
/*!50100 PARTITION BY KEY (ruleUid)
PARTITIONS 251 */;
Error 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( /*Concatenate partition body*/
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, /*Subpartition*/
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 /*Field change proof*/
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`, /*Subpartition description*/
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`, /*Subpartition 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
Strangely, commenting out the ORDER BY in GROUP_CONCAT executes without issues, SQL as follows:
SELECT
`data`.TABLE_NAME,
CONCAT(
'PARTITION BY ',
`data`.PARTITION_METHOD,
'(',
`data`.PARTITION_EXPRESSION,
') PARTITIONS ',
MAX(`data`.PARTITION_ORDINAL_POSITION),
`data`.sub_var,
' (',
GROUP_CONCAT( /*Concatenate partition body*/
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, /*Subpartition*/
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 /*Field change proof*/
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`, /*Subpartition description*/
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`, /*Subpartition 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
Also, commenting out some other parts of the SQL, it executes without errors:
SELECT
# `data`.TABLE_NAME,
# CONCAT(
# 'PARTITION BY ',
# `data`.PARTITION_METHOD,
# '(',
# `data`.PARTITION_EXPRESSION,
# ') PARTITIONS ',
# MAX(`data`.PARTITION_ORDINAL_POSITION),
# `data`.sub_var,
# ' (',
GROUP_CONCAT( /*Concatenate partition body*/
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, /*Subpartition*/
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 /*Field change proof*/
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`, /*Subpartition description*/
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`, /*Subpartition 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
Is this a bug?
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: