0

I am having an issue with the OQGRAPH storage engine in MariaDB. When I select from it, specifying a hard-coded destid and latch = 'dijkstras', it returns the expected data; however, when I try to use the graph table in a JOIN, it returns no data.

How can I fix this or perform this operation in a different way? The OQGRAPH engine is ideal for my use case, as I need to also perform other types of graph lookups such as descendent leaf lookups, etc.

Reproducible Example

You will need to install OQGRAPH as per https://mariadb.com/kb/en/installing-oqgraph/.

CREATE TABLE Tree (
    id      BIGINT UNSIGNED     NOT NULL,
    parent  BIGINT UNSIGNED     NOT NULL,
    name    VARCHAR(255)        NOT NULL,
    PRIMARY KEY ( id ),
    FOREIGN KEY ( parent ) REFERENCES Tree ( id )
) ENGINE=InnoDB;

/*
    (1)Root
    |
    +-  (2)Foo1
    +-  (3)Foo2
        |
        +-  (4)Bar1
*/
INSERT INTO Tree (id,parent,name) VALUES
    (1,1,'Root'),
    (2,1,'Foo1'),
    (3,1,'Foo2'),
    (4,3,'Bar1');


CREATE TABLE B (
    id      BIGINT UNSIGNED NOT NULL,
    tree_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY ( id ),
    FOREIGN KEY ( tree_id ) REFERENCES Tree ( id )
) ENGINE=InnoDB;

INSERT INTO B (id,tree_id) VALUES
    (1,1),
    (2,2),
    (3,3),
    (4,4);

-- https://mariadb.com/kb/en/oqgraph-overview/
CREATE TABLE TreeGraph (
    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='Tree' origid='parent' destid='id';

-- This query returns the expected hierarchical data
SELECT *
FROM TreeGraph
INNER JOIN Tree ON Tree.id = TreeGraph.linkid
WHERE destid = 4
AND latch = 'dijkstras';

-- This query (with TreeGraph joined instead of selected) returns nothing for TreeGraph
SELECT *
FROM B
LEFT JOIN TreeGraph ON TreeGraph.destid = B.tree_id AND TreeGraph.latch = 'dijkstras';

-- This query (with TreeGraph selected from and filtered in WHERE) also returns nothing for TreeGraph
SELECT *
FROM B, TreeGraph
WHERE TreeGraph.destid = B.tree_id AND TreeGraph.latch = 'dijkstras';
2
  • Try using a force index on one of the keys on the TreeGraph side. If it doesn't end up using the index the magic function of the graph engine gets replaced with a table scan.
    – danblack
    May 3 at 3:57
  • @danblack -- still no luck when using FORCE INDEX FOR JOIN (latch_2) (or latch, for that matter). Not sure how else to get it to not perform a table scan. May 5 at 13:56

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.