At Thu, 03 Aug 2017 19:29:40 -0400, Tom Lane wrote in
<28993.1501802...@sss.pgh.pa.us>
> I wrote:
> > In short, therefore, it's looking to me like analyzeCTE() is wrong here.
> > It should allow the case where the recursive result has typmod -1 while
> > the non-recursive output column has some more-specific typmod, so long
> > as they match on type OID. That would correspond to what we do in
> > regular non-recursive UNION situations.
>
> Oh, scratch that. I was thinking that we could simply relax the error
> check, but that really doesn't work at all. The problem is that by now,
> we have probably already generated Vars referencing the outputs of the
> recursive CTE, and those will have the more-specific typmod, which is
> wrong in this scenario. Usually that wouldn't matter too much, but
> there are cases where it would matter.
>
> We could imagine dealing with this by re-parse-analyzing the recursive
> term using typmod -1 for the CTE output column, but I don't much want
> to go there. It wouldn't be cheap, and I'm not quite sure it's guaranteed
> to converge anyway.
Agreed.
> What's seeming like an attractive compromise is to change the HINT
> to recommend manually coercing the recursive term, instead of the
> non-recursive one. Adjusting the error cursor to point to that side
> might be a bit painful, but it's probably doable.
>
> Thoughts?
I agree to the direction, but if I'm not missing anything
transformSetOperationTree has the enough information and we won't
get the expected pain there. (The movement itself might be the
pain, though..)
| ERROR: recursive query "foo" column 1 has type numeric(7,3) in non-recursive
term but type numeric overall
| LINE 4: select f1+1 from foo
|^
| HINT: Cast the output of the recursive term to the type of the non-recursive
term.
# The hint gets a bit longer..
By the way a wrong collation still results in the previous hint
but it won't be a problem.
| ERROR: recursive query "foo" column 1 has collation "it_IT" in non-recursive
term but collation "ja_JP" overall
| LINE 2: select a from bar
|^
| HINT: Use the COLLATE clause to set the collation of the non-recursive term.
regards,
--
Kyotaro Horiguchi
NTT Open Source Software Center
*** a/src/backend/parser/analyze.c
--- b/src/backend/parser/analyze.c
***
*** 42,47
--- 42,49
#include "parser/parse_target.h"
#include "parser/parsetree.h"
#include "rewrite/rewriteManip.h"
+ #include "utils/builtins.h"
+ #include "utils/lsyscache.h"
#include "utils/rel.h"
***
*** 1796,1801 transformSetOperationTree(ParseState *pstate, SelectStmt *stmt,
--- 1798,1804
bool isTopLevel, List **targetlist)
{
bool isLeaf;
+ int varattno;
Assert(stmt && IsA(stmt, SelectStmt));
***
*** 1980,1985 transformSetOperationTree(ParseState *pstate, SelectStmt *stmt,
--- 1983,1989
op->colTypmods = NIL;
op->colCollations = NIL;
op->groupClauses = NIL;
+ varattno = 0;
forboth(ltl, ltargetlist, rtl, rtargetlist)
{
TargetEntry *ltle = (TargetEntry *) lfirst(ltl);
***
*** 2008,2013 transformSetOperationTree(ParseState *pstate, SelectStmt *stmt,
--- 2012,2019
else
rescoltypmod = -1;
+ varattno++;
+
/*
* Verify the coercions are actually possible. If not, we'd fail
* later anyway, but we want to fail now while we have sufficient
***
*** 2110,2115 transformSetOperationTree(ParseState *pstate, SelectStmt *stmt,
--- 2116,2161
}
/*
+ * Verify that the previously determined output column types and
+ * collations match what the query really produced. We have to
+ * check this because the recursive term could have overridden the
+ * non-recursive term, and we don't have any easy way to fix that.
+ */
+ if (isTopLevel &&
+ pstate->p_parent_cte &&
+ pstate->p_parent_cte->cterecursive)
+ {
+ Oid lcolcoll = exprCollation((Node *)ltle->expr);
+
+ /*
+ * This might somewhat confusing but we suggest to fix
+ * recursive term since non-recursive term may have the same
+ * type without typemod.
+ */
+ if (rescoltype != lcoltype || rescoltypmod != lcoltypmod)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("recursive query \"%s\" column %d has type %s in non-recursive term but type %s overall",
+ pstate->p_parent_cte->ctename, varattno,
+ format_type_with_typemod(lcoltype,
+ lcoltypmod),
+ format_type_with_typemod(rescoltype,
+ rescoltypmod)),
+ errhint("Cast the output of the recursive term to the type of the non-recursive term."),
+ parser_errposition(pstate,
+ exprLocation((Node *)rtle->expr;
+ if (rescolcoll != lcolcoll)
+ ereport(ERROR,
+ (errcode(ERRCODE_COLLATION_MISMATCH),
+