Measures: Use outer join for in-out degrees computation JIRA: MADLIB-1073
Commit 06788cc added the graph measure functions described in the JIRA. This commit fixes a bug from that commit in the graph_vertex_degrees function. The bug led to results not containing vertices that either had 0 in-degree or out-degree. Closes #173 Project: http://git-wip-us.apache.org/repos/asf/incubator-madlib/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-madlib/commit/d5d5a26b Tree: http://git-wip-us.apache.org/repos/asf/incubator-madlib/tree/d5d5a26b Diff: http://git-wip-us.apache.org/repos/asf/incubator-madlib/diff/d5d5a26b Branch: refs/heads/master Commit: d5d5a26b4b38fc4455009d52d1fdb297ccc46d95 Parents: 2c6045f Author: Rahul Iyer <ri...@apache.org> Authored: Fri Aug 18 16:19:39 2017 -0700 Committer: Rahul Iyer <ri...@apache.org> Committed: Fri Aug 18 20:53:43 2017 -0700 ---------------------------------------------------------------------- src/ports/postgres/modules/graph/measures.py_in | 14 +++++++++----- src/ports/postgres/modules/graph/measures.sql_in | 2 +- 2 files changed, 10 insertions(+), 6 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-madlib/blob/d5d5a26b/src/ports/postgres/modules/graph/measures.py_in ---------------------------------------------------------------------- diff --git a/src/ports/postgres/modules/graph/measures.py_in b/src/ports/postgres/modules/graph/measures.py_in index f5c38b4..eb79adb 100644 --- a/src/ports/postgres/modules/graph/measures.py_in +++ b/src/ports/postgres/modules/graph/measures.py_in @@ -215,8 +215,8 @@ class Graph(object): SELECT {grouping_cols_comma} in_q.vertex as {self.vertex_id_col}, - indegree, - outdegree + coalesce(indegree, 0) as indegree, + coalesce(outdegree, 0) as outdegree FROM ( SELECT @@ -224,18 +224,22 @@ class Graph(object): {e.dest} as vertex, count(*) as indegree FROM {self.edge_table} - WHERE {e.src} != {e.dest} + WHERE {e.src} != {e.dest} AND + {e.src} IS NOT NULL AND + {e.dest} IS NOT NULL GROUP BY {grouping_cols_comma} {e.dest} ) as in_q - JOIN + FULL OUTER JOIN ( SELECT {grouping_cols_comma} {e.src} as vertex, count(*) as outdegree FROM {self.edge_table} - WHERE {e.src} != {e.dest} + WHERE {e.src} != {e.dest} AND + {e.src} IS NOT NULL AND + {e.dest} IS NOT NULL GROUP BY {grouping_cols_comma} {e.src} ) as out_q http://git-wip-us.apache.org/repos/asf/incubator-madlib/blob/d5d5a26b/src/ports/postgres/modules/graph/measures.sql_in ---------------------------------------------------------------------- diff --git a/src/ports/postgres/modules/graph/measures.sql_in b/src/ports/postgres/modules/graph/measures.sql_in index 1992139..b92f683 100644 --- a/src/ports/postgres/modules/graph/measures.sql_in +++ b/src/ports/postgres/modules/graph/measures.sql_in @@ -734,7 +734,7 @@ SELECT * FROM degrees ORDER BY id; 4 | 1 | 1 5 | 1 | 1 6 | 2 | 1 -(7 rows) + 7 | 1 | 0 </pre> -# Create a graph with 2 groups and find degrees for each group:
