So I was informed today that UNION types integer and text cannot be
matched. Alright, but it failed to tell which particular expressions
in this 3-branch, 30-columns-each UNION clause in a 100-line statement
it was talking about. So I made the attached patch to give some better
pointers. Example:
peter=# values(0,1), (1::bigint,2), ('text'::text,3);
ERROR: 42804: VALUES types bigint at position 2 and text at position 3
cannot be matched in instance 1
I'm not sure about the terminology "position" and "instance"; they're
just two coordinates to get at the problem.
None of this will help if you have multiple unrelated clauses that
invoke select_common_type(), but that might be better handled using the
parser location mechanism.
Comments?
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
diff -ur ../cvs-pgsql/src/backend/parser/analyze.c ./src/backend/parser/analyze.c
--- ../cvs-pgsql/src/backend/parser/analyze.c 2007-03-13 10:59:04.000000000 +0100
+++ ./src/backend/parser/analyze.c 2007-04-23 21:23:23.000000000 +0200
@@ -2195,7 +2195,7 @@
*/
for (i = 0; i < sublist_length; i++)
{
- coltypes[i] = select_common_type(coltype_lists[i], "VALUES");
+ coltypes[i] = select_common_type(coltype_lists[i], "VALUES", sublist_length > 1 ? i + 1 : 0);
}
newExprsLists = NIL;
@@ -2632,6 +2632,7 @@
ListCell *lcm;
ListCell *rcm;
const char *context;
+ int count;
context = (stmt->op == SETOP_UNION ? "UNION" :
(stmt->op == SETOP_INTERSECT ? "INTERSECT" :
@@ -2665,6 +2666,7 @@
/* don't have a "foreach4", so chase two of the lists by hand */
lcm = list_head(lcoltypmods);
rcm = list_head(rcoltypmods);
+ count = 0;
forboth(lct, lcoltypes, rct, rcoltypes)
{
Oid lcoltype = lfirst_oid(lct);
@@ -2674,9 +2676,12 @@
Oid rescoltype;
int32 rescoltypmod;
+ count++;
+
/* select common type, same as CASE et al */
rescoltype = select_common_type(list_make2_oid(lcoltype, rcoltype),
- context);
+ context,
+ list_length(lcoltypes) > 1 ? count : 0);
/* if same type and same typmod, use typmod; else default */
if (lcoltype == rcoltype && lcoltypmod == rcoltypmod)
rescoltypmod = lcoltypmod;
diff -ur ../cvs-pgsql/src/backend/parser/parse_clause.c ./src/backend/parser/parse_clause.c
--- ../cvs-pgsql/src/backend/parser/parse_clause.c 2007-02-03 14:08:51.000000000 +0100
+++ ./src/backend/parser/parse_clause.c 2007-04-23 21:13:14.000000000 +0200
@@ -965,7 +965,7 @@
{
outcoltype = select_common_type(list_make2_oid(l_colvar->vartype,
r_colvar->vartype),
- "JOIN/USING");
+ "JOIN/USING", 0);
outcoltypmod = -1; /* ie, unknown */
}
else if (outcoltypmod != r_colvar->vartypmod)
diff -ur ../cvs-pgsql/src/backend/parser/parse_coerce.c ./src/backend/parser/parse_coerce.c
--- ../cvs-pgsql/src/backend/parser/parse_coerce.c 2007-04-03 22:26:05.000000000 +0200
+++ ./src/backend/parser/parse_coerce.c 2007-04-23 21:52:52.000000000 +0200
@@ -932,23 +932,31 @@
* typeids is a nonempty list of type OIDs. Note that earlier items
* in the list will be preferred if there is doubt.
* 'context' is a phrase to use in the error message if we fail to select
- * a usable type.
+ * a usable type. 'instance_no' is to decorate the error message in case
+ * there are multiple invocations of this function for a clause.
*/
Oid
-select_common_type(List *typeids, const char *context)
+select_common_type(List *typeids, const char *context, int instance_no)
{
Oid ptype;
CATEGORY pcategory;
ListCell *type_item;
+ int ptype_pos;
+ int ntype_pos;
Assert(typeids != NIL);
ptype = getBaseType(linitial_oid(typeids));
+ ptype_pos = 1;
pcategory = TypeCategory(ptype);
+ ntype_pos = 1;
+
for_each_cell(type_item, lnext(list_head(typeids)))
{
Oid ntype = getBaseType(lfirst_oid(type_item));
+ ntype_pos++;
+
/* move on to next one if no new information... */
if ((ntype != InvalidOid) && (ntype != UNKNOWNOID) && (ntype != ptype))
{
@@ -956,6 +964,7 @@
{
/* so far, only nulls so take anything... */
ptype = ntype;
+ ptype_pos = ntype_pos;
pcategory = TypeCategory(ptype);
}
else if (TypeCategory(ntype) != pcategory)
@@ -968,10 +977,20 @@
/*------
translator: first %s is name of a SQL construct, eg CASE */
- errmsg("%s types %s and %s cannot be matched",
- context,
- format_type_be(ptype),
- format_type_be(ntype))));
+ (instance_no > 0
+ ? errmsg("%s types %s at position %d and %s at position %d cannot be matched in instance %d",
+ context,
+ format_type_be(ptype),
+ ptype_pos,
+ format_type_be(ntype),
+ ntype_pos,
+ instance_no)
+ : errmsg("%s types %s at position %d and %s at position %d cannot be matched",
+ context,
+ format_type_be(ptype),
+ ptype_pos,
+ format_type_be(ntype),
+ ntype_pos))));
}
else if (!IsPreferredType(pcategory, ptype) &&
can_coerce_type(1, &ptype, &ntype, COERCION_IMPLICIT) &&
@@ -982,6 +1001,7 @@
* other way; but if we have a preferred type, stay on it.
*/
ptype = ntype;
+ ptype_pos = ntype_pos;
pcategory = TypeCategory(ptype);
}
}
diff -ur ../cvs-pgsql/src/backend/parser/parse_expr.c ./src/backend/parser/parse_expr.c
--- ../cvs-pgsql/src/backend/parser/parse_expr.c 2007-04-03 22:26:05.000000000 +0200
+++ ./src/backend/parser/parse_expr.c 2007-04-23 21:13:00.000000000 +0200
@@ -870,7 +870,7 @@
* LHS' type is first in the list, it will be preferred when there is
* doubt (eg, when all the RHS items are unknown literals).
*/
- scalar_type = select_common_type(typeids, "IN");
+ scalar_type = select_common_type(typeids, "IN", 0);
/* Do we have an array type to use? */
array_type = get_array_type(scalar_type);
@@ -1074,7 +1074,7 @@
*/
typeids = lcons_oid(exprType((Node *) newc->defresult), typeids);
- ptype = select_common_type(typeids, "CASE");
+ ptype = select_common_type(typeids, "CASE", 0);
Assert(OidIsValid(ptype));
newc->casetype = ptype;
@@ -1249,7 +1249,7 @@
}
/* Select a common type for the elements */
- element_type = select_common_type(typeids, "ARRAY");
+ element_type = select_common_type(typeids, "ARRAY", 0);
/* Coerce arguments to common type if necessary */
foreach(element, newelems)
@@ -1325,7 +1325,7 @@
typeids = lappend_oid(typeids, exprType(newe));
}
- newc->coalescetype = select_common_type(typeids, "COALESCE");
+ newc->coalescetype = select_common_type(typeids, "COALESCE", 0);
/* Convert arguments if necessary */
foreach(args, newargs)
@@ -1363,7 +1363,7 @@
typeids = lappend_oid(typeids, exprType(newe));
}
- newm->minmaxtype = select_common_type(typeids, "GREATEST/LEAST");
+ newm->minmaxtype = select_common_type(typeids, "GREATEST/LEAST", 0);
/* Convert arguments if necessary */
foreach(args, newargs)
diff -ur ../cvs-pgsql/src/include/parser/parse_coerce.h ./src/include/parser/parse_coerce.h
--- ../cvs-pgsql/src/include/parser/parse_coerce.h 2007-04-01 10:57:50.000000000 +0200
+++ ./src/include/parser/parse_coerce.h 2007-04-23 21:08:47.000000000 +0200
@@ -59,7 +59,7 @@
Oid targetTypeId,
const char *constructName);
-extern Oid select_common_type(List *typeids, const char *context);
+extern Oid select_common_type(List *typeids, const char *context, int instance_no);
extern Node *coerce_to_common_type(ParseState *pstate, Node *node,
Oid targetTypeId,
const char *context);
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
