[HACKERS] Implicit casts with generic arrays
I've looked into cutting back on the implicit casts to text, which exposed the following little gem. The expressions 'abc' || 34 34 || 'abc' would no longer work, with the following error message: ERROR: 22P02: array value must start with "{" or dimension information That's because the best matches are now respectively anyarray || anyelement anyelement || anyarray Now either this is just too bad and users of a system with reduced casts to text will have to live with this odd error message, or coercing any old unknown constant to anyarray isn't such a good idea. Comments? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Implicit casts with generic arrays
Awhile back, I wrote: > Peter Eisentraut <[EMAIL PROTECTED]> writes: >> I've looked into cutting back on the implicit casts to text, which >> exposed the following little gem. >> The expressions >> 'abc' || 34 >> 34 || 'abc' >> would no longer work, with the following error message: >> ERROR: 22P02: array value must start with "{" or dimension information > Hm, that's annoying. Not that the expressions fail --- we want them to > --- but that the error message is so unhelpful. I've looked into this more closely. The problem basically is that the parser sees these alternatives for binary || operators: select oid,oid::regoperator,oprcode from pg_operator where oprname = '||'; oid | oid |oprcode --+-+--- 349 | ||(anyarray,anyelement) | array_append 374 | ||(anyelement,anyarray) | array_prepend 375 | ||(anyarray,anyarray) | array_cat 654 | ||(text,text) | textcat 1797 | ||(bit,bit) | bitcat 2018 | ||(bytea,bytea) | byteacat (6 rows) If there is no implicit cast from int to text, then operator 349 is the *only* candidate that is not immediately eliminated by the lack of any way to cast an integer 34 to its right argument type. So as far as the parser is concerned there is no ambiguity. If we hack things to prevent matching unknown to anyarray, as was suggested in the previous discussion, we'll get "operator does not exist: "unknown" || integer". Which is better than the 22P02 error, but still not great. It furthermore seems that the two operators anyarray || anyelement and anyelement || anyarray are really the only cases where an undesirable match to anyarray might occur. The other operators that take anyarray take it on both sides, which means that they'd not be preferred unless the other operand was discernibly an array. I don't think we want a solution that causes "knownarraycolumn = '{1,2,3}'" to start failing. That argument is even more compelling on the function side, because for instance there isn't a lot of doubt about the user's intent if he writes "array_append('{1,2,3}', 34)". So after reflecting on all that, it doesn't seem like a good idea to hack the type-coercion code to discriminate against matching unknown to anyarray. It looks to me like we have a very narrow problem and we should tailor a very narrow solution. What I am currently thinking we should do is make oper() specifically test for the case of operator 349 with UNKNOWN left input, or operator 374 with UNKNOWN right input, and throw a custom error message hinting that the other operand needs to be cast to text. In the long run maybe we should choose some other name for the array_append and array_prepend operators to avoid the confusion with concatenation. It seems to me that "concatenation" normally implies "stringing together similar objects", which these two operators definitely don't do, and so you could argue that || was a bad name for them from the get-go. But compatibility worries would mean we couldn't eliminate the old names for quite a long time, so maybe it's too late for that. Comments? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Implicit casts with generic arrays
"Tom Lane" <[EMAIL PROTECTED]> writes: > So after reflecting on all that, it doesn't seem like a good idea to > hack the type-coercion code to discriminate against matching unknown > to anyarray. It looks to me like we have a very narrow problem and > we should tailor a very narrow solution. What I am currently thinking > we should do is make oper() specifically test for the case of operator > 349 with UNKNOWN left input, or operator 374 with UNKNOWN right input, > and throw a custom error message hinting that the other operand > needs to be cast to text. Wouldn't that mean that 'foo'||'bar' would *still* fail? It really seems to me that at some point down the line we're going to cave and admit that users do expect 'foo' to be a string first and cast to other types only if the context requires it. That would mean we should be considering matching "unknown" as text first without casting and only if that fails looking for other types. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Implicit casts with generic arrays
I wrote: > It looks to me like we have a very narrow problem and > we should tailor a very narrow solution. What I am currently thinking > we should do is make oper() specifically test for the case of operator > 349 with UNKNOWN left input, or operator 374 with UNKNOWN right input, > and throw a custom error message hinting that the other operand > needs to be cast to text. I've been experimenting with another solution, which is to not add any weird error cases but instead add operators that will capture the problem cases back away from the anyelement||anyarray operators. My current prototype is create function catany(text, anyelement) returns text as $$ select $1 || $2::text $$ language sql; create function catany(anyelement, text) returns text as $$ select $1::text || $2 $$ language sql; create operator || (procedure = catany, leftarg = text, rightarg = anyelement); create operator || (procedure = catany, leftarg = anyelement, rightarg = text); which seems to mostly do the "right" thing. This approach would have one nice property, namely eliminating the single biggest point of push-back we are likely to get from removing the implicit casts to text. I have no doubt that practically the only reasonable use-case for that behavior was to let people concatenate stuff without being too picky about casts, and this mostly preserves that ability. It's not perfect, because it only fixes cases in which at least one operand is either unknown or implicitly coercible to text. But in practice I think that would cover 99% of cases, since typical usages tend to alternate literals and data values. Thoughts? Is this too klugy for words? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Implicit casts with generic arrays
Gregory Stark <[EMAIL PROTECTED]> writes: > "Tom Lane" <[EMAIL PROTECTED]> writes: >> we should do is make oper() specifically test for the case of operator >> 349 with UNKNOWN left input, or operator 374 with UNKNOWN right input, >> and throw a custom error message hinting that the other operand >> needs to be cast to text. > Wouldn't that mean that 'foo'||'bar' would *still* fail? No, because that would preferentially match to text || text, it being a preferred-type case. The current behavior with the implicit casts removed is template1=# select 'abc' || '34'; ?column? -- abc34 (1 row) ie, this was matched to the text || text operator; template1=# select 'abc' || 34; ERROR: array value must start with "{" or dimension information ie, this was matched to the anyarray || anyelement operator --- because it clearly can't match text || text. > It really seems to me that at some point down the line we're going to > cave and admit that users do expect 'foo' to be a string first and > cast to other types only if the context requires it. We already do that to some extent, as shown above; and it's got approximately nothing to do with this problem anyway. The cases where we have got a problem are where the other argument is clearly *not* text. But having said that, I'm currently leaning to the other solution of generalizing the || operator (and only that operator) instead of fooling with the type resolution rules. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Implicit casts with generic arrays
I wrote: > I've been experimenting with another solution, which is to not add any > weird error cases but instead add operators that will capture the > problem cases back away from the anyelement||anyarray operators. > My current prototype is > create function catany(text, anyelement) returns text as > $$ select $1 || $2::text $$ language sql; > create function catany(anyelement, text) returns text as > $$ select $1::text || $2 $$ language sql; > create operator || (procedure = catany, leftarg = text, rightarg = > anyelement); > create operator || (procedure = catany, leftarg = anyelement, rightarg = > text); > which seems to mostly do the "right" thing. This approach would have > one nice property, namely eliminating the single biggest point of > push-back we are likely to get from removing the implicit casts to text. I've been testing this approach more, and finding that it really captures a bit too much: some cases that you'd prefer went to anyelement||anyarray will be captured by the text||anyelement operator. For example in 8.2 this is mapped to array_prepend: regression=# select 'x'::text || array['aa','bb','cc']; ?column? -- {x,aa,bb,cc} (1 row) but with the experimental code you get textcat: catany=# select 'x'::text || array['aa','bb','cc']; ?column? - x{aa,bb,cc} (1 row) Basically the textcat operators will capture any case where the scalar side is implicitly coercible to text, because the type resolution rules will prefer that. There are some hacks we could make to make this less probable (eg, declare the capturing operators as taking varchar instead of text) but I can't find any complete solution short of changing the resolution rules themselves. Which I'm loath to do since it might have unexpected side-effects. What I would like to propose is that we deprecate use of || as the operator name for array_prepend and array_append, and invent new recommended names for them. As I said earlier, these operators aren't exactly concatenation in any normal sense anyway, since they don't treat their operands symmetrically. My first thought is to suggest using the shifting symbols: anyelement >> anyarray anyarray << anyelement but perhaps someone will have a better suggestion. If we do that, then we have a solution for anyone whose array prepend or append operator is unexpectedly captured by text concatenation: use the new names instead. Now this is only going to seem like a good idea if you agree that we should have some capturing operators like these. But if we don't, I think we are going to get a lot of push-back from people whose concatenations of random datatypes suddenly stopped working. Essentially this proposal is putting the compatibility hit of tightening the implicit cast rules onto people who are using array append/prepend instead of people who are using concatenation without explicit casts. I think there are a lot fewer of the former than the latter. Comments? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Implicit casts with generic arrays
Tom Lane wrote: The expressions 'abc' || 34 34 || 'abc' would no longer work, with the following error message: ERROR: 22P02: array value must start with "{" or dimension information Hm, that's annoying. Not that the expressions fail --- we want them to --- but that the error message is so unhelpful. indeed In the long run maybe we should choose some other name for the array_append and array_prepend operators to avoid the confusion with concatenation. It seems to me that "concatenation" normally implies "stringing together similar objects", which these two operators definitely don't do, and so you could argue that || was a bad name for them from the get-go. But compatibility worries would mean we couldn't eliminate the old names for quite a long time, so maybe it's too late for that. Comments? Originally I saw this situation as as requiring the concatenation operator per SQL 2003: ::= | ::= ::= || ::= ::= ::= | ::= ::= | | ::= | | | | | | ::= | | | | | | | | | | | | | | | | | | ::= | ::= | ::= | ::= | | | | | | What I can't decide now is whether all the above means the anyelement in this operation ought to be in parens or not. It seems to me that the anyelement can be any literal _except_ a . In that case the spec seems to require parenthesis. Joe ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Implicit casts with generic arrays
> For example in 8.2 this is mapped to array_prepend: > > regression=# select 'x'::text || array['aa','bb','cc']; >?column? > -- > {x,aa,bb,cc} > (1 row) > > but with the experimental code you get textcat: > > catany=# select 'x'::text || array['aa','bb','cc']; > ?column? > - > x{aa,bb,cc} > (1 row) This is what I would have expected || to give, and not what 8.2 does. So disregarding the rest of the argument I think that array_[pre|ap]pend should have other operators. Andreas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Implicit casts with generic arrays
> For example in 8.2 this is mapped to array_prepend: > > regression=# select 'x'::text || array['aa','bb','cc']; >?column? > -- > {x,aa,bb,cc} > (1 row) > > but with the experimental code you get textcat: > > catany=# select 'x'::text || array['aa','bb','cc']; > ?column? > - > x{aa,bb,cc} > (1 row) This is what I would have expected || to give, and not what 8.2 does. So disregarding the rest of the argument I think that array_[pre|ap]pend should have other operators. Andreas I thing so current behave is more intuitive and practical. Result x{aa,bb,cc} is nonsens. Array concation have to have higher priority than text concation. Pavel ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Implicit casts with generic arrays
Joe Conway <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> In the long run maybe we should choose some other name for the >> array_append and array_prepend operators to avoid the confusion with >> concatenation. It seems to me that "concatenation" normally implies >> "stringing together similar objects", which these two operators >> definitely don't do, and so you could argue that || was a bad name >> for them from the get-go. > Originally I saw this situation as as requiring the concatenation > operator per SQL 2003: Maybe I am missing something, but the only such construct I see in SQL2003 is concatenation of arrays of equal rank. There is nothing corresponding to array_prepend or array_append. I do have a plan B if people don't want to rename the operators, though. It looks to me like we could eliminate the conflict if we invented a new polymorphic pseudotype called "anynonarray" or some such, which would act like anyelement *except* it would not match an array. Then, declaring the capturing operators as text||anynonarray and anynonarray||text would prevent them from matching any case where either side was known to be an array type. But they would (I think) still win out in cases such as scalar || 'unknown literal'. The end result would be that concatenations involving a known-array value would be array concatenation, but you could force them to be text concatenation, if that's what you wanted, by explicitly casting the array value(s) to text. I was a bit hesitant to propose this since I couldn't immediately think of any other use-case for such a pseudotype. It's not a huge amount of added code (cf. anyenum) but it's definitely a visible wart on the type system. Comments? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Implicit casts with generic arrays
Tom Lane wrote: > I do have a plan B if people don't want to rename the operators, though. > It looks to me like we could eliminate the conflict if we invented a new > polymorphic pseudotype called "anynonarray" or some such, which would > act like anyelement *except* it would not match an array. Then, > declaring the capturing operators as text||anynonarray and > anynonarray||text would prevent them from matching any case where either > side was known to be an array type. But they would (I think) still win > out in cases such as scalar || 'unknown literal'. The end result would > be that concatenations involving a known-array value would be array > concatenation, but you could force them to be text concatenation, if > that's what you wanted, by explicitly casting the array value(s) to text. > > I was a bit hesitant to propose this since I couldn't immediately think > of any other use-case for such a pseudotype. It's not a huge amount of > added code (cf. anyenum) but it's definitely a visible wart on the type > system. Comments? On the contrary, I would think that it fits nicely to "close the loop" on the anyarray/anyelement feature set. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Implicit casts with generic arrays
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> I do have a plan B if people don't want to rename the operators, though. >> It looks to me like we could eliminate the conflict if we invented a new >> polymorphic pseudotype called "anynonarray" or some such, which would >> act like anyelement *except* it would not match an array. >> ... >> I was a bit hesitant to propose this since I couldn't immediately think >> of any other use-case for such a pseudotype. It's not a huge amount of >> added code (cf. anyenum) but it's definitely a visible wart on the type >> system. Comments? > On the contrary, I would think that it fits nicely to "close the loop" > on the anyarray/anyelement feature set. OK, I'll go code this up and verify that it behaves like I think it will... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Implicit casts with generic arrays
Tom Lane wrote: Joe Conway <[EMAIL PROTECTED]> writes: Tom Lane wrote: In the long run maybe we should choose some other name for the array_append and array_prepend operators to avoid the confusion with concatenation. It seems to me that "concatenation" normally implies "stringing together similar objects", which these two operators definitely don't do, and so you could argue that || was a bad name for them from the get-go. Originally I saw this situation as as requiring the concatenation operator per SQL 2003: Maybe I am missing something, but the only such construct I see in SQL2003 is concatenation of arrays of equal rank. There is nothing corresponding to array_prepend or array_append. Well, I've never claimed to be particularly good at interpreting the SQL spec, but as an example... ::= || ::= ::= ::= ::= ::= Doesn't this mean that array concatenation should include things like: || e.g. ARRAY[1,2,3] || 42 ? I do have a plan B if people don't want to rename the operators, though. It looks to me like we could eliminate the conflict if we invented a new polymorphic pseudotype called "anynonarray" or some such, which would act like anyelement *except* it would not match an array. Then, declaring the capturing operators as text||anynonarray and anynonarray||text would prevent them from matching any case where either side was known to be an array type. But they would (I think) still win out in cases such as scalar || 'unknown literal'. The end result would be that concatenations involving a known-array value would be array concatenation, but you could force them to be text concatenation, if that's what you wanted, by explicitly casting the array value(s) to text. That sounds reasonable to me. Joe ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Implicit casts with generic arrays
Joe Conway <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Maybe I am missing something, but the only such construct I see in >> SQL2003 is concatenation of arrays of equal rank. There is nothing >> corresponding to array_prepend or array_append. > Well, I've never claimed to be particularly good at interpreting the SQL > spec, but as an example... > ::= > || > ::= > ::= > ::= > ::= > ::= > > Doesn't this mean that array concatenation should include things like: > || No, because the first syntax rule for that is 1) The declared type of shall be an array type. However, assuming that the anynonarray idea works out, we can do that and not worry about touching the array operators. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Implicit casts with generic arrays
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> It looks to me like we could eliminate the conflict if we invented a new >> polymorphic pseudotype called "anynonarray" or some such, which would >> act like anyelement *except* it would not match an array. > ... > On the contrary, I would think that it fits nicely to "close the loop" > on the anyarray/anyelement feature set. OK, I hacked this together and it seems to behave at least as reasonably as 8.2 does. "8.3" here means HEAD + anynonarray + capturing concat operators. I used integer as an example of a type for which 8.2 has an implicit cast to text, and point as an example of a type for which it doesn't: Expression 8.3 8.2 text || texttext concat text concat text || 'unknown' text concat text concat text || text[] array concatarray concat text || non-text array error error text || non-text scalar text concat text concat [1] integer || integer error text concat integer || 'unknown'text concat text concat integer || integer[]array concatarray concat integer || non-integer arrayerror error integer || non-integer scalar error text concat [1] point || point error error point || 'unknown' text concat 'array value must start ...' point || point[]array concatarray concat point || non-point arrayerror error point || non-point scalar error error text[] || text[]array concatarray concat text[] || 'unknown' error error text[] || non-text arrayerror error text[] || non-text scalar error error [1] for types for which 8.2 has an implicit cast to text, else it fails. These are: bigint smallint integer oid real double precision numeric date time without time zone time with time zone timestamp without time zone timestamp with time zone interval (I was interested to find that there were cases where 8.2 would come out with the dreaded "array value must start with "{" or dimension information" error.) I think that the above chart is pretty defensible; the only cases that fail now where they worked before are concatenations where neither side is either text or an unadorned string literal. Frankly, I think this: catany=# select 3 || 0.4; ERROR: operator does not exist: integer || numeric is way preferable to this: regression=# select 3 || 0.4; ?column? -- 30.4 (1 row) which is what 8.2 does --- if you want text concatenation you should make at least *some* effort to signal that, like casting one side to text or at least quoting it. Run-together concatenations like catany=# select 'sin(' || 2 || ')'; ?column? -- sin(2) (1 row) will work as long as at least one of the first two concatenated items is textual or an unadorned string literal. Barring objections I'll clean this up and commit it. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Implicit casts with generic arrays
Peter Eisentraut <[EMAIL PROTECTED]> writes: > I've looked into cutting back on the implicit casts to text, which > exposed the following little gem. > The expressions > 'abc' || 34 > 34 || 'abc' > would no longer work, with the following error message: > ERROR: 22P02: array value must start with "{" or dimension information Hm, that's annoying. Not that the expressions fail --- we want them to --- but that the error message is so unhelpful. Since ANYARRAY is already special to the type system, I don't have a problem with inserting some special case to prevent this, but I'm not sure what the special case should be. Is it too klugy to say "don't implicitly cast unknown to anyarray unless the literal's value starts with { or ["? We've never made casting decisions depend on the contents of strings before, and I'm really loath to make 'em do so now. Seems basically we'd want to not cast unknown to anyarray unless there is some additional bit of context suggesting that that's the right thing. But what should that extra requirement be? Can we go as far as not doing this cast implicitly at all? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Implicit casts with generic arrays
Am Dienstag, 27. Februar 2007 19:50 schrieb Tom Lane: > Seems basically we'd want to not cast unknown to anyarray unless there > is some additional bit of context suggesting that that's the right thing. > But what should that extra requirement be? Can we go as far as not > doing this cast implicitly at all? We could say that unknown is not taken as anyarray input if the entire function/operator argument list consists of anyelement or anyarray. But that might be even harder to comprehend. With the ARRAY[...] syntax available, converting unknown to anyarray might be altogether unnecessary. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq