[MDEV-15006] OQGraph doesn't produce next step links - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Won't Fix
Affects Version/s: 10.0(EOL)
Fix Version/s: N/A
Component/s: Storage Engine - OQGRAPH
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

Attachments

Activity

Ascending order - Click to sort in descending order

Elena Stepanova added a comment - 2018-01-23 21:15

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)

Elena Stepanova added a comment - 2018-01-23 21:15 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)

Mr A Kitson added a comment - 2018-01-23 23:10

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.

Mr A Kitson added a comment - 2018-01-23 23:10 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.

Elena Stepanova added a comment - 2018-10-05 16:39 - edited

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?

Elena Stepanova added a comment - 2018-10-05 16:39 - edited 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?

Sergei Golubchik added a comment - 2022-09-12 09:51

10.0 was EOLed in March 2019

Sergei Golubchik added a comment - 2022-09-12 09:51 10.0 was EOLed in March 2019

MariaDB Server

OQGraph doesn't produce next step links

Details

Description

Attachments

Activity

People

Dates

Git Integration