[MDEV-15006] OQGraph doesn't produce next step links Created: 2018-01-19  Updated: 2022-09-12  Resolved: 2022-09-12

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - OQGRAPH
Affects Version/s: 10.0
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Mr A Kitson Assignee: Andrew McDonnell
Resolution: Won't Fix Votes: 0
Labels: None
Environment:

Linux - Ubuntu 16.04


 Description   

Hi, I'm prototyping an accounting system based around OQGraph but I've hit a stop

Here's my data:
simple select on my graph table:
latch, origid, destid, weight, seq, linkid
,0,1,1,,
,1,2,1,,
,2,3,1,,
,3,4,1,,
,4,5,1,,
,4,6,1,,
,2,7,1,,
,7,8,1,,
,8,9,1,,
,7,10,1,,
,10,11,1,,
,1,12,1,,
,12,13,1,,
,13,14,1,,
,13,15,1,,
,12,16,1,,
,16,17,1,,
,16,18,1,,
,16,19,1,,
,16,20,1,,
,16,21,1,,
,16,22,1,,

SELECT linkid
FROM sa_coa_graph
WHERE latch = '1'
AND linkid > 0
AND destid = 22;

gives me

linkid
1
12
16
22

which is expected and works. Perfect for updating all the parent accounts in one go.

but

select linkid from sa_coa_graph where latch = ''
and origid = 0;

gives nothing. Not what I'm expecting. In this case I'd expect a result containing linkid == 1 i.e. the first hop link. Setting origid to any other 'parent' gives no first hop links as described in docs at https://mariadb.com/kb/en/library/oqgraph-examples/

In this case I want to use OQGraph to help me reconstruct the tree back into my parent program (PHP).As there is no functionality to get the linkage in any form of tree, I assumed you'd intended that this mechanism was a step towards doing so.

Am I missing something?

Kind regards

 Comments   
Comment by Elena Stepanova [ 2018-01-23 ]

Which version are you using? What is the engine of the backing table? Could you please paste the structures and attach the cnf file?
I'm getting linkid == 1:

CREATE TABLE oq_backing (
 
  origid INT UNSIGNED NOT NULL, 
  destid INT UNSIGNED NOT NULL,  
  PRIMARY KEY (origid, destid), 
  KEY (destid)
 
);
 
 
 
insert into oq_backing values 
(0,1),(1,2),(2,3),(3,4),(4,5),(4,6),(2,7),(7,8),(8,9),
 
(7,10),(10,11),(1,12),(12,13),(13,14),(13,15),(12,16),
 
(16,17),(16,18),(16,19),(16,20),(16,21),(16,22);
 
 
 
CREATE TABLE sa_coa_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';


MariaDB [test]> select * from sa_coa_graph;
 
+-------+--------+--------+--------+------+--------+
 
| latch | origid | destid | weight | seq  | linkid |
 
+-------+--------+--------+--------+------+--------+
 
| NULL  |      0 |      1 |      1 | NULL |   NULL |
 
| NULL  |      1 |      2 |      1 | NULL |   NULL |
 
| NULL  |      1 |     12 |      1 | NULL |   NULL |
 
| NULL  |      2 |      3 |      1 | NULL |   NULL |
 
| NULL  |      2 |      7 |      1 | NULL |   NULL |
 
| NULL  |      3 |      4 |      1 | NULL |   NULL |
 
| NULL  |      4 |      5 |      1 | NULL |   NULL |
 
| NULL  |      4 |      6 |      1 | NULL |   NULL |
 
| NULL  |      7 |      8 |      1 | NULL |   NULL |
 
| NULL  |      7 |     10 |      1 | NULL |   NULL |
 
| NULL  |      8 |      9 |      1 | NULL |   NULL |
 
| NULL  |     10 |     11 |      1 | NULL |   NULL |
 
| NULL  |     12 |     13 |      1 | NULL |   NULL |
 
| NULL  |     12 |     16 |      1 | NULL |   NULL |
 
| NULL  |     13 |     14 |      1 | NULL |   NULL |
 
| NULL  |     13 |     15 |      1 | NULL |   NULL |
 
| NULL  |     16 |     17 |      1 | NULL |   NULL |
 
| NULL  |     16 |     18 |      1 | NULL |   NULL |
 
| NULL  |     16 |     19 |      1 | NULL |   NULL |
 
| NULL  |     16 |     20 |      1 | NULL |   NULL |
 
| NULL  |     16 |     21 |      1 | NULL |   NULL |
 
| NULL  |     16 |     22 |      1 | NULL |   NULL |
 
+-------+--------+--------+--------+------+--------+
 
22 rows in set (0.00 sec)


MariaDB [test]> SELECT linkid
 
    -> FROM sa_coa_graph
 
    -> WHERE latch = '1'
 
    -> AND linkid > 0
 
    -> AND destid = 22;
 
+--------+
 
| linkid |
 
+--------+
 
|      1 |
 
|     12 |
 
|     16 |
 
|     22 |
 
+--------+
 
4 rows in set (0.00 sec)


MariaDB [test]> select linkid from sa_coa_graph where latch = ''
 
    -> and origid = 0;
 
+--------+
 
| linkid |
 
+--------+
 
|      1 |
 
+--------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select @@version;
 
+-----------------+
 
| @@version       |
 
+-----------------+
 
| 10.2.12-MariaDB |
 
+-----------------+
 
1 row in set (0.00 sec)

Comment by Mr A Kitson [ 2018-01-23 ]

Hi Elena

I figured a different way to do it, but you can find the code at https://github.com/chippyash/simple-accounts-3/tree/master/src/sql. The code I developed to get round the problem is in https://github.com/chippyash/simple-accounts-3/blob/master/src/sql/build-procs.sql sa_sp_get_tree function, which works just fine.

It runs on mariadb 10.0. You can see build result (of the running code) at https://travis-ci.org/chippyash/simple-accounts-3

I'd still be interested as to why

select linkid from sa_coa_graph where latch = ''
-> and origid = 0;

failed to perform as expected.

Comment by Elena Stepanova [ 2018-10-05 ]

It appears that the result depends on the indexes in the backing table.
You essentially had this (I removed unrelated columns):

DROP TABLE `sa_coa_graph`, `sa_coa_ledger`;
 
 
 
CREATE TABLE `sa_coa_ledger` (
 
  `id` int(10) unsigned NOT NULL,
 
  `prntId` int(10) unsigned NOT NULL DEFAULT 0,
 
  PRIMARY KEY (`id`)
 
) ENGINE=InnoDB;
 
 
 
insert into `sa_coa_ledger` (prntId,id) values 
(0,1),(1,2),(2,3),(3,4),(4,5),(4,6),(2,7),(7,8),(8,9),
 
(7,10),(10,11),(1,12),(12,13),(13,14),(13,15),(12,16),
 
(16,17),(16,18),(16,19),(16,20),(16,21),(16,22);
 
 
 
CREATE TABLE `sa_coa_graph` (
 
  `latch` varchar(32) DEFAULT NULL,
 
  `origid` bigint(20) unsigned DEFAULT NULL,
 
  `destid` bigint(20) unsigned DEFAULT NULL,
 
  `weight` double DEFAULT NULL,
 
  `seq` bigint(20) unsigned DEFAULT NULL,
 
  `linkid` bigint(20) unsigned DEFAULT NULL,
 
  KEY `latch` (`latch`,`origid`,`destid`) USING HASH,
 
  KEY `latch_2` (`latch`,`destid`,`origid`) USING HASH
 
) ENGINE=OQGRAPH `data_table`='sa_coa_ledger' `origid`='prntId' `destid`='id';
 
 
 
SELECT linkid FROM sa_coa_graph WHERE latch = '1' AND linkid > 0 AND destid = 22;
 
select linkid from sa_coa_graph where latch = '' and origid = 0;

10.0.36

 
MariaDB [test]> SELECT linkid FROM sa_coa_graph WHERE latch = '1' AND linkid > 0 AND destid = 22;
 
+--------+
 
| linkid |
 
+--------+
 
|      1 |
 
|     12 |
 
|     16 |
 
|     22 |
 
+--------+
 
4 rows in set (0.00 sec)
 
 
 
MariaDB [test]> select linkid from sa_coa_graph where latch = '' and origid = 0;
 
Empty set (0.00 sec)

But if you add a key on prntId, it works as you expect:

DROP TABLE `sa_coa_graph`, `sa_coa_ledger`;
 
 
 
CREATE TABLE `sa_coa_ledger` (
 
  `id` int(10) unsigned NOT NULL,
 
  `prntId` int(10) unsigned NOT NULL DEFAULT 0,
 
  PRIMARY KEY (`id`),
 
  KEY (`prntId`)
 
) ENGINE=InnoDB;
 
 
 
insert into `sa_coa_ledger` (prntId,id) values 
(0,1),(1,2),(2,3),(3,4),(4,5),(4,6),(2,7),(7,8),(8,9),
 
(7,10),(10,11),(1,12),(12,13),(13,14),(13,15),(12,16),
 
(16,17),(16,18),(16,19),(16,20),(16,21),(16,22);
 
 
 
CREATE TABLE `sa_coa_graph` (
 
  `latch` varchar(32) DEFAULT NULL,
 
  `origid` bigint(20) unsigned DEFAULT NULL,
 
  `destid` bigint(20) unsigned DEFAULT NULL,
 
  `weight` double DEFAULT NULL,
 
  `seq` bigint(20) unsigned DEFAULT NULL,
 
  `linkid` bigint(20) unsigned DEFAULT NULL,
 
  KEY `latch` (`latch`,`origid`,`destid`) USING HASH,
 
  KEY `latch_2` (`latch`,`destid`,`origid`) USING HASH
 
) ENGINE=OQGRAPH `data_table`='sa_coa_ledger' `origid`='prntId' `destid`='id';
 
 
 
SELECT linkid FROM sa_coa_graph WHERE latch = '1' AND linkid > 0 AND destid = 22;
 
select linkid from sa_coa_graph where latch = '' and origid = 0;

10.0.36

 
MariaDB [test]> SELECT linkid FROM sa_coa_graph WHERE latch = '1' AND linkid > 0 AND destid = 22;
 
+--------+
 
| linkid |
 
+--------+
 
|      1 |
 
|     12 |
 
|     16 |
 
|     22 |
 
+--------+
 
4 rows in set (0.00 sec)
 
 
 
MariaDB [test]> select linkid from sa_coa_graph where latch = '' and origid = 0;
 
+--------+
 
| linkid |
 
+--------+
 
|      1 |
 
+--------+
 
1 row in set (0.00 sec)

I'm not sure if it's by design, the current documentation doesn't say it explicitly. Assigning to andymc73 to confirm (or not).

pprkut, or should it be for you?

Comment by Sergei Golubchik [ 2022-09-12 ]

10.0 was EOLed in March 2019

Generated at Thu Feb 08 08:17:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.