Search code examples
sqlapache-drill

How do we perform recursive query on apache drill

In apache drill how do we perform recursive queries using CTE with clause?

Solution

  • Apache Drill does not support recursive queries. Usage Notes section:

    Drill can only reference a table defined by a WITH clause subquery in the scope of the SELECT query that the WITH clause begins. For example, you can reference such a table in the FROM clause of a subquery in the SELECT list, WHERE clause, or HAVING clause. You cannot use a WITH clause in a subquery and reference its table in the FROM clause of the main query or another subquery.

    Try rewriting your query without recursion. There was also a discussion on dev mailing list, it might have useful suggestions or workarounds.