Error using cte on HIVE SQL - java.lang

Question

As per https://cwiki.apache.org/confluence/display/Hive/Common+Table+Expression i see that CTEs are supported in HIVE. However, i get the below 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 trying the below query to fetch all parents of a destination node

  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 table nodeMaster inside it's WITH clause. According to https://cwiki.apache.org/confluence/display/Hive/Common+Table+Expression, this recursive reference is not supported.

Instead you may want to 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