Thread: Collations versus record-returning functions
I'm making pretty good progress on the task of splitting input and output collations for expression nodes. There remains one case in the regression tests that is giving a non-expected result. It involves this function: CREATE FUNCTION dup (f1 anyelement, f2 out anyelement, f3 out anyarray) AS 'select $1, array[$1,$1]' LANGUAGE sql; which is used like this: SELECT a, (dup(b)).* FROM collate_test3 ORDER BY 2; where the column collate_test3.b is declared with a nondefault collation. In HEAD, the output of dup() inherits that collation from the b Var, and then so does the field selected from it, and so the ORDER BY sorts according to the column collation. With my patch as it stands, you get sorting by the default collation. The reason is that the output of dup() is of type RECORD, which is a noncollatable type according to pg_type, so the function result is labeled noncollatable even though it has collatable inputs. The FieldSelect node then has to choose the default collation since there's nothing else available. ISTM there are basically three things we might do about this: (1) Decide that the patch's behavior is correct and what's embodied in the regression expected file is wrong. (2) Allow collations to propagate up through nodes that deliver noncollatable outputs. (3) Decide that composite types are collatable. I don't much care for #2, mainly because it seems likely to cause a lot of unnecessary collation conflicts. It has a lot of flavor of action at a distance in most cases, too, even though it might seem natural in this particular example. #3 has some appeal but it also seems fraught with definitional issues, for instance: * Something likerow('a' collate "C", 'b' collate "en_US") doesn't have a resolvable collation, but I don't think we want it to throw an error. * If composites are collatable, one would logically expect a table's rowtype to inherit the common collation of its columns (if there is one). This is problematic because then the rowtype collation can change after ALTER TABLE ADD COLUMN/DROP COLUMN/ALTER COLUMN TYPE, which is something we can't support even if it seemed like a good idea. Also, I fail to see any support for #3 in the SQL standard. So far as I can find, the standard only considers that string types are collatable. By the same token, it doesn't look to me like the standard is expecting #2 to happen, since only string types can carry collation. Thoughts? regards, tom lane
I wrote: > ISTM there are basically three things we might do about this: > (1) Decide that the patch's behavior is correct and what's embodied in > the regression expected file is wrong. > (2) Allow collations to propagate up through nodes that deliver > noncollatable outputs. > (3) Decide that composite types are collatable. I thought of another possibility, which is to special-case field selection from a function-returning-composite, ie make it look through the function node and use the function's input collation. FieldSelect needs to be a special case in the collation assignment code anyway because of the possibility of taking the collation from the field declaration instead of the input, so this is not *quite* as ugly as it first sounds. It's still ugly, but it makes that regression test pass with only a very localized change. So I will do it like that for now until someone comes up with an argument for another choice. regards, tom lane
On Fri, Mar 18, 2011 at 10:40:53PM -0400, Tom Lane wrote: > (2) Allow collations to propagate up through nodes that deliver > noncollatable outputs. I don't think this is the goal. Only strings types are collatable, as you point out. > * Something like > row('a' collate "C", 'b' collate "en_US") > doesn't have a resolvable collation, but I don't think we want it > to throw an error. Well, it doesn't. But it returns a rowtype and the string columns in the row type would carry a collation and I don't think they'd have to be the same. row is not a function really. > * If composites are collatable, one would logically expect a table's > rowtype to inherit the common collation of its columns (if there is > one). This is problematic because then the rowtype collation can change > after ALTER TABLE ADD COLUMN/DROP COLUMN/ALTER COLUMN TYPE, which is > something we can't support even if it seemed like a good idea. Indeed not. A rowtype has an order, determined by the fields within it. Those fields may be strings and so may have a collation. Doesn't seem particularly magical to me. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patriotism is when love of your own people comes first; nationalism, > when hate for people other than your own comes first. > - Charles de Gaulle
On sön, 2011-03-20 at 20:26 +0100, Martijn van Oosterhout wrote: > A rowtype has an order, determined by the fields within it. Those > fields may be strings and so may have a collation. Doesn't seem > particularly magical to me. Yeah, that's answer #4. The composite types themselves are not considered collatable, but the fields in them carry collation individually. That's what the test case in question represents, and I think it must work like that if you maintain the analogy between composite types and tables (which have columns that carry collation individually).