[#MDEV-27589] OQGRAPH returns a different result set if backing table has descending PK

I am not sure at all that it is a problem. The meaning of linkid value seems obscure; according to some ancient OQGraph presentation I've found online, its meaning "varies depending on latch", and for breadth_first it is "returned as connected vertex", which doesn't help me much. In any case, apparently it's not just an ordinal number as it appears to be at the first glance. In the unlikely case that somebody is ever interested in a combination of OQGraph and descending keys on backing tables, they are welcome to investigate it further and close as not-a-bug or fix, depending on the outcome. Otherwise I am just recording it for posterity.

--source include/have_innodb.inc

INSTALL SONAME 'ha_oqgraph';

CREATE TABLE oq_backing (

  origid INT UNSIGNED NOT NULL,

  destid INT UNSIGNED NOT NULL,

  PRIMARY KEY (origid, destid desc),

  KEY (destid)

);

INSERT INTO oq_backing VALUES

(1,2),(1,3),(2,1),(3,1),(3,4),(4,3);

CREATE TABLE oq_graph ENGINE=OQGRAPH

data_table='oq_backing' origid='origid' destid='destid';

SELECT * FROM oq_graph WHERE latch = 'breadth_first' AND origid = 3;

DROP TABLE oq_backing;

DROP TABLE oq_graph;

The above test case has a DESC part in the primary key, and it returns this dataset:

preview-10.8-MDEV-13756-desc-indexes c10e10c6
MariaDB [test]> SELECT * FROM oq_graph WHERE latch = 'breadth_first' AND origid = 3;
+---------------+--------+--------+--------+------+--------+
\| latch \| origid \| destid \| weight \| seq \| linkid \|
+---------------+--------+--------+--------+------+--------+
\| breadth_first \| 3 \| NULL \| 2 \| 4 \| 2 \|
\| breadth_first \| 3 \| NULL \| 1 \| 3 \| 1 \|
\| breadth_first \| 3 \| NULL \| 1 \| 2 \| 4 \|
\| breadth_first \| 3 \| NULL \| 0 \| 1 \| 3 \|
+---------------+--------+--------+--------+------+--------+

The differenc e
With the same query and same data, but with an ASC PK on the backing table, it is

MariaDB [test]> SELECT * FROM oq_graph WHERE latch = 'breadth_first' AND origid = 3;

+---------------+--------+--------+--------+------+--------+

| latch         | origid | destid | weight | seq  | linkid |

+---------------+--------+--------+--------+------+--------+

| breadth_first |      3 |   NULL |      2 |    4 |      2 |

| breadth_first |      3 |   NULL |      1 |    3 |      4 |

| breadth_first |      3 |   NULL |      1 |    2 |      1 |

| breadth_first |      3 |   NULL |      0 |    1 |      3 |

+---------------+--------+--------+--------+------+--------+

4 rows in set (0.002 sec)

That is, the 2nd and 3rd rows have linkid swapped.

Reproducible with at least InnoDB and MyISAM for the backing table.

[MDEV-27589] OQGRAPH returns a different result set if backing table has descending PK Created: 2022-01-23 Updated: 2023-11-28
Status:	Open
Project:	MariaDB Server
Component/s:	Storage Engine - OQGRAPH
Affects Version/s:	10.8
Fix Version/s:	10.11

[MDEV-27589] OQGRAPH returns a different result set if backing table has descending PK Created: 2022-01-23 Updated: 2023-11-28