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