CTE CONCAT not joining strings

Question

I'm new to SQL. I'm using MySQL Ver 15.1 Distrib 10.3.27-MariaDB. For the following. I'm trying to compute paths in a graph like this: a->b.

create table edges
(
    start varchar(4),
    end   varchar(4)
);
insert edges(start, end) values ("a", "b");

with recursive cte (path, last) as
(
    select "a", "a"
    union all
    select CONCAT(path, "->", edges.end), edges.end from cte, edges
           where cte.last = edges.start
)
select * from cte;

I was expected to get two rows like this [("a", "a"), ("a->b", "b")] but instead got [("a", "a"), ("a", "b")]. Why was there no concatenation?

So you are actually using mariaDB and not MySQL. They started quite similiar but now are not at all similiar — RiggsFolly
– RiggsFolly, Commented Feb 14 at 8:32

P.Salmon · Accepted Answer · 2025-02-14 08:47:50Z

0

datatype and length are derived from the first select you should be seeing an error

try

with recursive cte (path, last) as
(
    select cast("a" as char(100)), cast("a" as char(100))
    union all
    select CONCAT(path, "->", edges.end), edges.end from cte, edges
           where cte.last = edges.start
)
select * from cte;

https://dbfiddle.uk/WAz2Jjg0

answered Feb 14 at 8:47

P.Salmon

17.7k2 gold badges14 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

CTE CONCAT not joining strings

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related