CODEWITHSUNDEEP
concatenationcommon-table-expressionmariadb-10.3
user2926204
user2926204

Reputation: 113

CTE CONCAT not joining strings

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?

Upvotes: 0

Views: 30

Answers (1)

P.Salmon
P.Salmon

Reputation: 17655

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

Upvotes: 0

Related Questions