Error using cte on HIVE SQL - java.lang

Question

From https://cwiki.apache.org/confluence/display/Hive/Common+Table+Expression, I see that CTEs are supported in HIVE. However, I get the following error when trying to execute a simple CTE

An error occurred while calling o60.sql. : java.lang.StackOverflowError at java.lang.ThreadLocal.set(ThreadLocal.java:201) 

I get this error when I try to execute the query below to retrieve all parents of the node destination

  nodelist = sqlContext.sql(""" SELECT node,src from known """) nodelist.registerTempTable("nodelist") pathcalc = sqlContext.sql(""" WITH nodeMaster AS ( SELECT p.node, p.src FROM nodelist p WHERE p.node = """+dest+""" UNION ALL SELECT c.node, c.src FROM nodeMaster cte INNER JOIN nodelist c ON c.node = cte.src ) SELECT node FROM nodeMaster m """) 

Answer 1

You reference your CTE tableMaster node inside it WITH. According to https://cwiki.apache.org/confluence/display/Hive/Common+Table+Expression , this recursive link is not supported.

Instead, you can do something like

  WITH nodedest AS ( SELECT p.node, p.src FROM nodelist p WHERE p.node = {dest} ) SELECT nodelist.node FROM nodedest INNER JOIN nodelist ON nodelist.node = nodedest.src