为什么此递归concat产生:数据太长 [英] Why this recursive concat produces: Data too long

问题描述

我在MySQL 8上有此表:

I have this table on MySQL 8:

create table tbl(id varchar(2), val int);
insert into tbl values ('A',  1), ('B',  2), ('C',  3), ('D',  4), ('E',  5);

以下查询应找出哪些记录集的值加起来不超过6(不考虑顺序的重要性):

The following query should find out which record sets have values that add up to a value not greater than 6 (without importance of order):

with recursive cte(greatest_id, ids, total) as (
    select     id,
               concat(id, ''), 
               val
    from       tbl
    union all
    select     tbl.id,
               concat(cte.ids, tbl.id),
               cte.total + tbl.val
    from       cte 
    inner join tbl 
            on tbl.id > cte.greatest_id
           and cte.total + tbl.val <= 6
) 
select ids, total from cte

运行它会导致以下错误:

错误:ER_DATA_TOO_LONG:第7行第concat(id, '')列的数据太长

MySQL为什么会产生此错误?

有关信息，所需的输出如下:

For info, the desired output is below:

 IDS | TOTAL
 ----+------
 A   |  1
 B   |  2
 C   |  3
 D   |  4
 E   |  5
 AB  |  3
 AC  |  4
 AD  |  5
 AE  |  6
 BC  |  5
 BD  |  6
 ABC |  6

我想知道MySQL在哪个(记录的)规则上产生此错误.

I want to know by which (documented?) rule MySQL produces this error here.

通过比较，该查询在PostgreSQL和Oracle上运行良好(使用它们的语法变体)，所以我并不真正理解为什么 MySQL出现问题.

By comparison, the query works fine on PostgreSQL and Oracle (using their syntax variation), so I don't really understand why MySQL is having a problem with it.

推荐答案

在MySQL 8 CTE

A long way down the MySQL 8 CTE manual page is an example that shows the problem you are having. Basically the problem is that your ids column is too narrow for the ABC value being assigned to it as it gets its width from the non-recursive part of the CTE (which is effectively the length of id i.e. 2 characters). You can solve that problem with a CAST to a big enough width to fit all the results e.g.:

with recursive cte(greatest_id, ids, total) as (
    select     id,
               CAST(id AS CHAR(5)) AS ids, 
               val
    from       tbl
    union all
    select     tbl.id,
               concat(cte.ids, tbl.id),
               cte.total + tbl.val
    from       cte 
    inner join tbl 
            on tbl.id > cte.greatest_id
           and cte.total + tbl.val <= 6
) 
select ids, total from cte

演示更新

这篇关于为什么此递归concat产生:数据太长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！