[MDEV-12517] oqgraph returns empty resultset if used within a subquery - Jira

Details

Type: Bug
Status: Confirmed (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.1.22
Fix Version/s: 10.1(EOL)
Component/s: Storage Engine - OQGRAPH
Labels:
None
Environment:
Linux system (gentoo, 64-bit)

Description

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:

Campus1>BLD4>F11-12

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 |

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

Attachments

Issue Links

relates to

MDEV-627 LP:837496 - Empty resultset when joining OQGRAPH tables

Open

Activity

Ascending order - Click to sort in descending order

Cheyenne Wills created issue - 2017-04-17 21:40

Cheyenne Wills made changes - 2017-04-17 21:40

Field	Original Value	New Value
Link		This issue relates to MDEV-627 [ MDEV-627 ]

Elena Stepanova made changes - 2017-05-01 22:55

Status

Open [ 1 ]

Confirmed [ 10101 ]

Elena Stepanova made changes - 2017-05-01 22:55

Fix Version/s		10.1 [ 16100 ]
Assignee		Andrew McDonnell [ andymc73 ]

Sergei Golubchik made changes - 2021-12-06 21:36

Workflow

MariaDB v3 [ 80342 ]

MariaDB v4 [ 143932 ]

People

Assignee:: Andrew McDonnell

Reporter:: Cheyenne Wills

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2017-04-17 21:40

Updated:: 2017-05-01 22:55

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server