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 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),
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
