Tidb: support recursive common table expression (CTE)

[gang0713]

Mysql in the business use of custom recursive query, TIDB currently does not support, if change to the business side, the efficiency is too low, can you provide support recursive functions. This requirement is very common.

Hope to be able to solve our problems as soon as possible to facilitate more people to access TIDB。

[gang0713]

Support for custom function.

[zz-jason]

@gang0713 For "custom recursive query", do you mean the common table expression? This is on our roadmap now.

[gang0713]

The next version can support? About which version support?

[zz-jason]

@gang0713 CTE is a very big feature, sorry that we can not deliver this feature in the next version, but we'll try our best to develop it.

[gang0713]

@zz-jason @morgo Without the full support, can give priority to support several commonly used, such as recursive,MAX and MIN

[gang0713]

Want to support statements commonly used functions, such as MAX and MIN

[gang0713]

Now do you have a support mysql some commonly used function of the plan? Like MAX, recursive functions, etc. We want to cut into TIDB, but some involves data access, use recursion, also some kinds of statistics report to process.

[morgo]

TiDB supports most of MySQL's built-in functions. You can track which ones are not present by following the sub-links from issue https://github.com/pingcap/tidb/issues/158

CTEs and Recursive CTEs (a MySQL 8.0 feature) are not yet supported, but are on our roadmap for the near term.

[u0x01]

has any way doing recursive query?
I tryed no one working...
give us some temporary solutions least, please:

    WITH RECURSIVE cte AS (
       SELECT id, pid, name, 1 AS level
       FROM   table
       WHERE  id = 668400

       UNION  ALL
       SELECT t.id, t.pid, t.name, c.level + 1
       FROM   cte      c
       JOIN   table t ON t.id = c.pid
       )
    SELECT name
    FROM   cte
    ORDER BY level dec;

cte not supported.

        T2.id 
    FROM
        (
        SELECT
            @r AS _id,
            ( SELECT @r := parent_id FROM table WHERE id = _id ) AS parent_id,
            @l := @l + 1 AS lvl 
        FROM
            ( SELECT @r := 5, @l := 0 ) vars,
            table h 
        WHERE
            @r <> 0 
            AND parent_id > 0 
        ) T1
        JOIN table T2 ON T1._id = T2.id 
    ORDER BY
        T1.lvl DESC

error: Unknown column '_id' in 'where clause'

    CREATE FUNCTION `getParentList`(rootId varchar(100))   
    RETURNS varchar(1000)   
    BEGIN   
    DECLARE fid varchar(100) default '';   
    DECLARE str varchar(1000) default rootId;   

    WHILE rootId is not null  do   
        SET fid =(SELECT parent_id FROM table WHERE id = rootId);   
        IF fid is not null THEN   
            SET str = concat(str, ',', fid);   
            SET rootId = fid;   
        ELSE   
            SET rootId = fid;   
        END IF;   
    END WHILE;   
    return str;  
    END

[morgo]

@u0x01 stored procedures and functions are unsupported. They are also not on the immediate roadmap.

CTEs will be added soon. Please be patient :-)

[vkingnew]

@morgo How about the progress for supporting CTEs?Up to Now.

[morgo]

We are currently working on stabilizing 3.0. CTEs are planned for version 3.1.

[git001]

The link from https://github.com/pingcap/tidb/issues/6824#issuecomment-465232578 https://pingcap.com/docs/sql/mysql-compatibility/ should be https://pingcap.com/docs/v3.0/reference/mysql-compatibility/ or a more generic url.

Is there still no plan to add "Stored procedures and functions or User-defined functions " into tidb?

[sstubbs]

Is there any idea when this will be available? I really like tidb but I can't use it without recursive CTE support.

[nullnotnil]

Is there any idea when this will be available? I really like tidb but I can't use it without recursive CTE support.

There is an initial design ready for non-recursive CTEs - I expect it will ready for TiDB 5.0. We do not have a current ETA for recursive CTEs yet. I expect the design will follow shortly after non-recursive.

[sstubbs]

I see ok thanks.