|
The following fails to produce the expected output.
(using example table from https://mariadb.com/kb/en/mariadb/oqgraph-examples/ )
CREATE TABLE oq_backing (
|
origid INT UNSIGNED NOT NULL,
|
destid INT UNSIGNED NOT NULL,
|
PRIMARY KEY (origid, destid),
|
KEY (destid)
|
);
|
INSERT INTO oq_backing(origid, destid)
|
VALUES (1,2), (2,3), (3,4), (4,5), (2,6), (5,6);
|
CREATE TABLE oq_graph (
|
latch VARCHAR(32) NULL,
|
origid BIGINT UNSIGNED NULL,
|
destid BIGINT UNSIGNED NULL,
|
weight DOUBLE NULL,
|
seq BIGINT UNSIGNED NULL,
|
linkid BIGINT UNSIGNED NULL,
|
KEY (latch, origid, destid) USING HASH,
|
KEY (latch, destid, origid) USING HASH
|
)
|
ENGINE=OQGRAPH
|
data_table='oq_backing' origid='origid' destid='destid';
|
select * from oq_backing;
|
+--------+--------+
|
| origid | destid |
|
+--------+--------+
|
| 1 | 2 |
|
| 2 | 3 |
|
| 2 | 6 |
|
| 3 | 4 |
|
| 4 | 5 |
|
| 5 | 6 |
|
+--------+--------+
|
|
This works:
SELECT GROUP_CONCAT(linkid ORDER BY seq) AS path FROM oq_graph WHERE latch='breadth_first' AND origid=4;
|
This fails:
select ob.origid, (select group_concat(pt.linkid order by pt.seq desc separator '>') from oq_graph as pt where pt.latch='breadth_first' and pt.origid=ob.origid) from oq_backing as ob;
|
|
+--------+------------------------------------------------------------------------------------------------------------------------------------------------+
|
| origid | (select group_concat(pt.linkid order by pt.seq desc separator '>') from oq_graph as pt where pt.latch='breadth_first' and pt.origid=ob.origid) |
|
+--------+------------------------------------------------------------------------------------------------------------------------------------------------+
|
| 1 | NULL |
|
| 2 | NULL |
|
| 2 | NULL |
|
| 3 | NULL |
|
| 4 | NULL |
|
| 5 | NULL |
|
+--------+------------------------------------------------------------------------------------------------------------------------------------------------+
|
6 rows in set (0.00 sec)
|
|
Ultimately what I'm trying to accomplish is to perform a query in a table that has a "location" field (the location is tree), I need to perform a subquery to return a "path" for each row.
Here is my "real world" query example:
select group_concat(l.Name order by pt.seq desc separator '>')
|
from Loc_graph as pt
|
join Location as l on l.id = pt.linkid
|
where pt.latch = 'breadth_first' and pt.origid=6;
|
Which returns:
However, when I embed the above as a subquery:
select loc.id,
|
( select group_concat(l.Name order by pt.seq desc separator '>')
|
from Loc_graph as pt
|
join Location as l on l.id = pt.linkid
|
where pt.latch = 'breadth_first' and pt.origid=loc.id) as path
|
from Location as loc;
|
I get the following
+----+------+
|
| id | path |
|
+----+------+
|
| 1 | NULL |
|
| 2 | NULL |
|
| 3 | NULL |
|
| 4 | NULL |
|
| 5 | NULL |
|
| 6 | NULL |
|
| 7 | NULL |
|
| 8 | NULL |
|
| 9 | NULL |
|
| 10 | NULL |
|
| 11 | NULL |
|
| 12 | NULL |
|
| 13 | NULL |
|
| 14 | NULL |
|
| 15 | NULL |
|
| 16 | NULL |
|
| 17 | NULL |
|
| 18 | NULL |
|
| 19 | NULL |
|
| 20 | NULL |
|
| 21 | NULL |
|
| 22 | NULL |
|
| 23 | NULL |
|
| 24 | NULL |
|
| 25 | NULL |
|
| 26 | NULL |
|
| 27 | NULL |
|
| 28 | NULL |
|
| 29 | NULL |
|
| 30 | NULL |
|
| 31 | NULL |
|
+----+------+
|
|