Gregory Stark <stark@xxxxxxxxxxxxxxxx> writes: > The query r_triggers.c is executing is (after simplifying a bit): > SELECT * > FROM t2 fk > LEFT OUTER JOIN t1 pk ON ( pk.id = fk.t1id::character) > WHERE pk.id IS NULL > AND fk.t1id IS NOT NULL > Note the cast. Really that cast should be char(5). It may be that we have to > carry the typmod here. Bingo --- what's happening is that that's a cast to character(1): regression=# select 'abcde'::varchar(5)::character; bpchar -------- a (1 row) > But really I don't understand why we put casts here at all. The whole point of > using the opfamily to find the operator in advance was so that we could be > sure to find the "right" operator. Yeah, we've found the "right" operator, but now we have to make sure that the parser finds the same one. Casting ensures that there is a unique exact match and we aren't risking an unexpected result from the ambiguous-operator resolution heuristics. If we weren't generating a textual representation of the query, but building a post-parse-analysis form directly, we wouldn't need these pushups because we could just store the desired operator's OID into the data structure. But from a readability and maintainability standpoint, the text format is probably safest. Anyway I certainly don't care to risk back-patching such a large change as that would be. > So I think the fix is just to remove the four lines responsible for putting > the casts there at all. I'm too lazy to generate a counterexample right now, but this *will* break things. We need to fix the typmod issue instead. After a few moment's thought, I'm tempted by the idea of not using format_type_be here, but always emitting the type name in the format "schema"."typname" --- this will guarantee that the parser doesn't insert any default typmod associated with the weird SQL-spec rules for certain special type names. That would fix things for character, and also bit which has the same kind of issue, but can anyone think of a case it would make worse? regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
