title: SQL Server : Graph-DB with soft delete isn't working as expected
body: <p>I try to use the graph functionality in SQL Server. Now I have a problem with soft-delete.</p>
  <p>I have the following graph</p>
  <pre><code>[1] -&gt; [2] -&gt; [9 (deleted)] -&gt; [4]
  </code></pre>
  <p>When I run the following script</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE MyNode 
  (
      [Id] [bigint] NOT NULL,
      [IsDeleted] [bit] NOT NULL,
  ) AS NODE;
  
  CREATE TABLE MyEdge 
  (
      State [int] NOT NULL
  ) AS EDGE;
  
  INSERT INTO MyNode (Id, IsDeleted)
  VALUES (1, 0), (2, 0), (4, 0), (9, 1);
  
  INSERT INTO MyEdge
  VALUES
  ( (SELECT $node_id FROM MyNode WHERE Id = 1), (SELECT $node_id FROM MyNode WHERE Id = 2), 1),
  ( (SELECT $node_id FROM MyNode WHERE Id = 2), (SELECT $node_id FROM MyNode WHERE Id = 9), 1),
  ( (SELECT $node_id FROM MyNode WHERE Id = 9), (SELECT $node_id FROM MyNode WHERE Id = 4), 1)
  ;
  
  SELECT
      src.Id ID_SOURCE
  ,   LAST_VALUE(trgt.Id) WITHIN GROUP (GRAPH PATH) AS ID_TARGET
  ,   STRING_AGG(trgt.Id, '-&gt;') WITHIN GROUP (GRAPH PATH) AS ID_CHAIN
  --, STRING_AGG(compare.State, '-&gt;') WITHIN GROUP (GRAPH PATH) AS STATE_CHAIN
  --, STRING_AGG(trgt.IsDeleted, '-&gt;') WITHIN GROUP (GRAPH PATH) AS DELETED_CHAIN
  FROM
      MyNode AS src
  ,   (   SELECT
              *
          FROM
              MyEdge
          WHERE
              State = 1
      ) FOR PATH AS compare
  ,   (   SELECT
              *
          FROM
              MyNode
          WHERE
              IsDeleted = 0
      ) FOR PATH AS trgt
  WHERE
      MATCH ( SHORTEST_PATH( src(-(compare)-&gt;trgt)+ ) )
      AND src.Id = 1;
  
  SELECT
      src.Id AS SOURCE_ID
  ,   ed.State AS EDGE_STATE
  ,   trgt.Id AS TARGET_ID
  FROM
      MyNode AS src
  ,   MyEdge AS ed
  ,   MyNode AS trgt
  WHERE
      MATCH( src-(ed)-&gt;trgt )
      AND src.Id = 2;
  
  
  DROP TABLE MyNode;
  DROP TABLE MyEdge;
  </code></pre>
  <p>(Hint: this only works on SQL-Server 2019)</p>
  <p>I get the following result</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ID_SOURCE</th>
  <th>ID_TARGET</th>
  <th>ID_CHAIN</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>2</td>
  <td>2</td>
  </tr>
  <tr>
  <td>1</td>
  <td>4</td>
  <td>2-&gt;4</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>There is no edge <code>2-&gt;4</code> but <code>2-&gt;9-&gt;4</code>. But the node 9 is deleted so it is used for the graph traversal but is suppressed in the output.</p>
  <p>Is this a bug in SQL-Server or am I doing something wrong?</p>
  <p>Or is there another way I should use soft delete with Graph-DB?</p>