*** 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 if not 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 term and 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-recursive term 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 side of 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 subqury which 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 recursive name 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 GROUP BY 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 UNION ALL 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 (SELECT 1 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: ORDER BY 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 1 UNION 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 ALL SELECT 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 subquery which 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 WHERE id < 5),