Re: Common Table Expressions (WITH RECURSIVE) patch - Mailing list pgsql-hackers
| From | Tatsuo Ishii |
|---|---|
| Subject | Re: Common Table Expressions (WITH RECURSIVE) patch |
| Date | |
| Msg-id | 20080925.111902.44985068.t-ishii@sraoss.co.jp Whole thread Raw |
| In response to | Common Table Expressions (WITH RECURSIVE) patch (Jeff Davis <pgsql@j-davis.com>) |
| List | pgsql-hackers |
Tom,
> > WithClause node may need a location field, and almost certainly has to
> > be handled somehow in exprLocation().
> >
> > The error reports in parse_cte.c *desperately* need error locations.
Included is a patch for this against your cte-0923.patch.gz. Most
errors now have error locations, but some do not. I'm going to think
more to enhance this.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
*** pgsql/src/backend/parser/parse_cte.c 2008-09-25 11:06:12.000000000 +0900
--- pgsql.patched/src/backend/parser/parse_cte.c 2008-09-25 10:46:41.000000000 +0900
***************
*** 239,245 **** if (query->intoClause) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("subquery in WITH cannot have SELECT INTO"))); /* Compute the derived fields if not done
yet*/ if (!cte->cterecursive)
--- 239,247 ---- if (query->intoClause) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("subquery in WITH cannot have SELECT INTO"),
! parser_errposition(pstate,
! exprLocation((Node *) query->intoClause)))); /* Compute the derived fields
ifnot done yet */ if (!cte->cterecursive)
***************
*** 561,625 **** lresult != NON_RECURSIVE) ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("Left hand side of UNION ALL must be a non-recursive term in a recursive query")));
else if (stmt->op == SETOP_INTERSECT) ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("non-recursive term and recursive term must not be combined with INTERSECT")));
else if (stmt->op == SETOP_EXCEPT) ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("non-recursive term and recursive term must not be combined with EXCEPT")));
else if (stmt->op == SETOP_UNION && stmt->all != true) ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("non-recursive term and recursive term must be combined with UNION ALL")));
else if (stmt->op == SETOP_UNION && stmt->all == true && rarg->op == SETOP_UNION)
ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("Right hand side of UNION ALL must not contain UNION operation"))); else
if(stmt->sortClause) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("ORDER BY in a recursive query not allowed"))); else if
(stmt->limitOffset|| stmt->limitCount) ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("LIMIT OFFSET in a recursive query not allowed"))); else if
(stmt->lockingClause) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("FOR UPDATE in a recursive query not allowed"))); else if (lresult ==
NON_RECURSIVE&& rresult == RECURSIVE_SELF) { if (larg->distinctClause)
ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("DISTINCT in a non recursive term not allowed"))); if
(rarg->distinctClause) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("DISTINCT in a recursive term not allowed"))); if
(rarg->groupClause) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("GROUP BY in a recursive term not allowed"))); if
(rarg->havingClause) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("HAVING in a recursive term not allowed"))); /*
*Save non_recursive_term.
--- 563,646 ---- lresult != NON_RECURSIVE) ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("Left hand side of UNION ALL must be a non-recursive term in a recursive query"),
! parser_errposition(cstate->pstate, cte->location))); else if (stmt->op ==
SETOP_INTERSECT) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("non-recursive term and recursive term must not be combined with INTERSECT"),
! parser_errposition(cstate->pstate, cte->location))); else if (stmt->op ==
SETOP_EXCEPT) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("non-recursive term and recursive term must not be combined with EXCEPT"),
! parser_errposition(cstate->pstate, cte->location))); else if (stmt->op ==
SETOP_UNION&& stmt->all != true) ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("non-recursive term and recursive term must be combined with UNION ALL"),
! parser_errposition(cstate->pstate, cte->location))); else if (stmt->op ==
SETOP_UNION&& stmt->all == true && rarg->op == SETOP_UNION) ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("Right hand side of UNION ALL must not contain UNION operation"),
! parser_errposition(cstate->pstate, cte->location))); else if (stmt->sortClause)
ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("ORDER BY in a recursive query not allowed"),
! parser_errposition(cstate->pstate,
! exprLocation((Node *) stmt->sortClause)))); else if
(stmt->limitOffset|| stmt->limitCount) ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("LIMIT OFFSET in a recursive query not allowed"),
! parser_errposition(cstate->pstate,
! exprLocation((Node *) stmt->limitCount)))); else if
(stmt->lockingClause) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("FOR UPDATE in a recursive query not allowed"),
! parser_errposition(cstate->pstate,
! exprLocation((Node *) stmt->lockingClause)))); else if
(lresult== NON_RECURSIVE && rresult == RECURSIVE_SELF) { if (larg->distinctClause)
ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("DISTINCT in a non recursive term not allowed"),
! parser_errposition(cstate->pstate,
! exprLocation((Node *) larg->distinctClause)))); if
(rarg->distinctClause) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("DISTINCT in a recursive term not allowed"),
! parser_errposition(cstate->pstate,
! exprLocation((Node *) rarg->distinctClause)))); if
(rarg->groupClause) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("GROUP BY in a recursive term not allowed"),
! parser_errposition(cstate->pstate,
! exprLocation((Node *) rarg->groupClause)))); if
(rarg->havingClause) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("HAVING in a recursive term not allowed"),
! parser_errposition(cstate->pstate,
! exprLocation((Node *) rarg->havingClause)))); /*
* Save non_recursive_term.
***************
*** 668,674 **** if (checkCteTargetList(cstate, n->targetList, myindex) != NON_RECURSIVE)
ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
! (errmsg("target list having subquery which uses recursive name in a recursive term not
allowed")))); } if (n->fromClause)
--- 689,697 ---- if (checkCteTargetList(cstate, n->targetList, myindex) != NON_RECURSIVE)
ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
! (errmsg("target list having subquery which uses recursive name in a recursive term not
allowed"),
! parser_errposition(cstate->pstate,
! exprLocation((Node *) n->targetList))))); } if (n->fromClause)
***************
*** 680,687 **** if (checkCteWhereClause(cstate, n->whereClause, myindex) != NON_RECURSIVE)
ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
! (errmsg("WHERE clause having subqury which uses recursive name in a recursive term not
allowed"))));
! } return r;
--- 703,711 ---- if (checkCteWhereClause(cstate, n->whereClause, myindex) != NON_RECURSIVE)
ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
! (errmsg("WHERE clause having subqury which uses recursive name in a recursive term not
allowed"),
! parser_errposition(cstate->pstate,
! exprLocation((Node *) n->whereClause))))); } return r;
*** pgsql/src/test/regress/expected/recursive.out 2008-09-25 11:06:12.000000000 +0900
--- pgsql.patched/src/test/regress/expected/recursive.out 2008-09-25 11:11:47.000000000 +0900
***************
*** 404,423 ****
--- 404,433 ---- WITH RECURSIVE x(n) AS (SELECT 1 UNION SELECT n+1 FROM x) SELECT * FROM x; ERROR: non-recursive
termand recursive term must be combined with UNION ALL
+ LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 UNION SELECT n+1 FROM x)
+ ^ -- INTERSECT WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x) SELECT * FROM
x;ERROR: non-recursive term and recursive term must not be combined with INTERSECT
+ LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x...
+ ^ WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FROM x) SELECT * FROM x;
ERROR: non-recursive term and recursive term must not be combined with INTERSECT
+ LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FR...
+ ^ -- EXCEPT WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x) SELECT * FROM x;
ERROR: non-recursive term and recursive term must not be combined with EXCEPT
+ LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x)
+ ^ WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM x) SELECT * FROM x; ERROR:
non-recursiveterm and recursive term must not be combined with EXCEPT
+ LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM ...
+ ^ -- no non-recursive term WITH RECURSIVE x(n) AS (SELECT n FROM x) SELECT * FROM x;
***************
*** 428,433 ****
--- 438,445 ---- WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1) SELECT * FROM x; ERROR: Left hand
sideof UNION ALL must be a non-recursive term in a recursive query
+ LINE 1: WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
+ ^ CREATE TEMPORARY TABLE y (a INTEGER); INSERT INTO y SELECT generate_series(1, 10); -- LEFT
JOIN
***************
*** 453,470 ****
--- 465,490 ---- WHERE n IN (SELECT * FROM x)) SELECT * FROM x; ERROR: WHERE clause having
subqurywhich uses recursive name in a recursive term not allowed
+ LINE 2: WHERE n IN (SELECT * FROM x))
+ ^ WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x
WHERE n = 1 AND n IN (SELECT * FROM x)) SELECT * FROM x; ERROR: WHERE clause having subqury which uses
recursivename in a recursive term not allowed
+ LINE 2: WHERE n = 1 AND n IN (SELECT * FRO...
+ ^ -- GROUP BY WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x
GROUPBY n) SELECT * FROM x; ERROR: GROUP BY in a recursive term not allowed
+ LINE 1: ...VE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x GROUP BY n)
+ ^ -- HAVING WITH RECURSIVE x(n) AS (SELECT 1
UNIONALL SELECT n+1 FROM x HAVING n < 10) SELECT * FROM x; ERROR: HAVING in a recursive term not allowed
+ LINE 1: ...x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x HAVING n < 10)
+ ^ -- aggregate functions WITH RECURSIVE x(n) AS
(SELECT1 UNION ALL SELECT count(*) FROM x) SELECT * FROM x;
***************
*** 485,494 ****
--- 505,518 ---- WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x ORDER BY 1) SELECT * FROM x; ERROR:
ORDERBY in a recursive query not allowed
+ LINE 1: ...VE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x ORDER BY 1)
+ ^ -- LIMIT/OFFSET WITH RECURSIVE x(n) AS (SELECT
1UNION ALL SELECT n+1 FROM x LIMIT 10 OFFSET 1) SELECT * FROM x; ERROR: LIMIT OFFSET in a recursive query not
allowed
+ LINE 1: ...n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x LIMIT 10 OFFSET ...
+ ^ -- FOR UPDATE WITH RECURSIVE x(n) AS (SELECT 1 UNION
ALLSELECT n+1 FROM x FOR UPDATE) SELECT * FROM x;
***************
*** 499,504 ****
--- 523,530 ---- SELECT (SELECT * FROM x) FROM x WHERE id < 5 ) SELECT * FROM x; ERROR: target list having
subquerywhich uses recursive name in a recursive term not allowed
+ LINE 3: SELECT (SELECT * FROM x) FROM x WHERE id < 5
+ ^ -- mutual recursive query WITH RECURSIVE x (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM y
WHEREid < 5),
pgsql-hackers by date: