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

OQGRAPH - Filter table using additional where clauses

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 10.4.10
    • None
    • None
    • Arch Linux

    Description

      It would be absolutely fantastic if records inside the base table of a oqgraph queried table could be filtered before applying the graph storage engine logic.

      For example:

      CREATE TABLE mapSolarSystemJumpsGraph
      ENGINE=OQGRAPH 
      data_table='mapSolarSystemJumps' origid='fromSolarSystemID' destid='toSolarSystemID';
      

      SELECT * FROM mapSolarSystemJumpsGraph mssjg
      WHERE latch='breadth_first' AND origid=30001429 AND destid=30001198 
      AND toSolarSystemID <> 30001399 -- pass this through to the base table?
      

      I've tried a few work around methods including targeting the oqgraph creation statement at temp tables and views, these create fine, but when queried give the table not found error. This could also be very useful.

      One workaround which is working, not ideal though, is a transaction based delete on the base table data before running the oqgraph table query:

      BEGIN;
      DELETE FROM mapSolarSystemJumps WHERE toSolarSystemID = 30001399;
      SELECT * FROM mapSolarSystemJumpsGraph mssjg
      WHERE latch='breadth_first' AND origid=30001429 AND destid=30001198;
      ROLLBACK;
      

      Use case here is a routing query /system for a game where solar systems can have may jump gates to other solar systems. OQGRAPH's performance in this area is unmatched at milliseconds for a routed chain of 41 systems having an average of 4 different possible routes each.

      Attachments

        Activity

          People

            Unassigned Unassigned
            caffe1neadd1ct Kevin Andrews
            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.