|
With the following population of the table flights
create table flights
|
(departure varchar(32),
|
arrival varchar(32),
|
carrier varchar(20),
|
flight_number char(7));
|
|
insert into flights values
|
('Seattle', 'Frankfurt', 'Lufthansa', 'LH 491'),
|
('Seattle', 'Chicago', 'American', 'AA 2573'),
|
('Seattle', 'Los Angeles', 'Alaska Air', 'AS 410'),
|
('Chicago', 'New York', 'American', 'AA 375'),
|
('Chicago', 'Montreal', 'Air Canada', 'AC 3053'),
|
('Los Angeles', 'New York', 'Delta', 'DL 1197'),
|
('Moscow', 'Tokyo', 'Aeroflot', 'SU 264'),
|
('New York', 'Paris', 'Air France', 'AF 23'),
|
('Frankfurt', 'Moscow', 'Lufthansa', 'LH 1444'),
|
('Tokyo', 'Seattle', 'ANA', 'NH 178'),
|
('Los Angeles', 'Tokyo', 'ANA', 'NH 175'),
|
('Moscow', 'Los Angeles', 'Aeroflot', 'SU 106'),
|
('Montreal', 'Paris', 'Air Canada', 'AC 870'),
|
('Cairo', 'Paris', 'Air France', 'AF 503'),
|
('New York', 'Seattle', 'American', 'AA 45'),
|
('Paris', 'Chicago', 'Air France', 'AF 6734');
|
the result set of the query
set statement standard_compliant_cte=0 for
|
with recursive destinations (city, legs) as
|
(
|
select a.arrival, 1 from flights a where a.departure='Cairo'
|
union
|
select b.arrival, r.legs + 1 from destinations r, flights b
|
where r.city=b.departure and b.arrival not in (select city from destinations)
|
)
|
select * from destinations;
|
lacks rows with destinations.city='Frankfurt' and destinations.city='Moscow'
MariaDB [test]> set statement standard_compliant_cte=0 for
|
-> with recursive destinations (city, legs) as
|
-> (
|
-> select a.arrival, 1 from flights a where a.departure='Cairo'
|
-> union
|
-> select b.arrival, r.legs + 1 from destinations r, flights b
|
-> where r.city=b.departure and b.arrival not in (select city from destinations)
|
-> )
|
-> select * from destinations;
|
+-------------+------+
|
| city | legs |
|
+-------------+------+
|
| Paris | 1 |
|
| Chicago | 2 |
|
| New York | 3 |
|
| Montreal | 3 |
|
| Seattle | 4 |
|
| Los Angeles | 5 |
|
| Tokyo | 6 |
|
+-------------+------+
|
The following query returns the correct set of cities
MariaDB [test]> with recursive destinations (city) as
|
-> ( select a.arrival from flights a where a.departure='Cairo'
|
-> union
|
-> select b.arrival from destinations r, flights b where r.city=b.departure)
|
-> select * from destinations;
|
+-------------+
|
| city |
|
+-------------+
|
| Paris |
|
| Chicago |
|
| New York |
|
| Montreal |
|
| Seattle |
|
| Frankfurt |
|
| Los Angeles |
|
| Moscow |
|
| Tokyo |
|
+-------------+
|
|