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

oqgraph returns empty resultset if used within a subquery

Details

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

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-627 LP:837496 - Empty resultset when joining OQGRAPH tables

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Activity

            CheyenneWills Cheyenne Wills added a comment -

            As a work around, I was able to create a stored function:

            CREATE  `LocPath`(loc_id INT) RETURNS TEXT
            		 
                READS SQL DATA
            		 
            BEGIN
            		 
            RETURN (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);
            		 
             
            		 
            END

            CheyenneWills Cheyenne Wills added a comment - As a work around, I was able to create a stored function: CREATE `LocPath`(loc_id INT ) RETURNS TEXT READS SQL DATA BEGIN RETURN ( 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);   END

            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.