mozilla/moz-sql-parser

Is it possible to support "WITH" clause.

Closed this issue · 3 comments

deryann commented

There are some many cases included "WITH" clause for subquery.
Is it possible to support these case.
like :
WITH dept_count AS (
SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno)
SELECT e.ename AS employee_name,
dc1.dept_count AS emp_dept_count,
m.ename AS manager_name,
dc2.dept_count AS mgr_dept_count
FROM emp e,
dept_count dc1,
emp m,
dept_count dc2
WHERE e.deptno = dc1.deptno
AND e.mgr = m.empno
AND m.deptno = dc2.deptno;

klahnakoski commented

@deryann please review. Some decisions are need about the resulting data structure.

#112

klahnakoski commented

The current PR is

{"with": [
    {"name": "name1", "value": expression1},
    {"name": "name2", "value": expression2},
    ...
    {"name": "nameN", "value": expressionN},
    {
        "select":"*", 
        "from":"temp"
    }
]}

I defined the with clause a while back a little differently:

https://github.com/mozilla/ActiveData/blob/ecb695c6bc9a9e5b057bf817ed6e9a40eff12f81/docs/jx_expressions.md#with-operator

{"with": [
    {name1: expression1},
    {name2: expression2},
    ...
    {nameN: expressionN},
    {
        "select":"*", 
        "from":"temp"
    }
]}

Since the first one looks a lot like a select clause, can we simply add it as a clause to the query? Lets expand query to be {"select":"*", "from":"temp"}, then

{
    "with": [
        {"name": "name1", "value": expression1},
        {"name": "name2", "value": expression2},
        ...
        {"name": "nameN", "value": expressionN},
    ],
    "select":"*", 
    "from":"temp"
}

I think I will go with this last version, while still accepting "name":"expression" for when humans may be composing the query.

Then, any expression can be extended with this new clause:

{
    "mul":["a", "a"],
    "with":{"a":2}
}