Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-12517

oqgraph returns empty resultset if used within a subquery

    XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.1.22
    • 10.1
    • None
    • 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

          Activity

            People

              andymc73 Andrew McDonnell
              CheyenneWills Cheyenne Wills
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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