Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-12-16 Thread Jan Wieck
On 12/14/2012 3:20 PM, Robert Haas wrote: On Fri, Dec 14, 2012 at 2:11 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: ... In more than ten years of working with PostgreSQL, I've never encountered where the restriction at issue here prevented a bug. It's only

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-12-16 Thread Simon Riggs
On 27 November 2012 22:41, Tom Lane t...@sss.pgh.pa.us wrote: Merlin Moncure mmonc...@gmail.com writes: ... I think if you relaxed the function sigs of a few functions on this page (http://www.postgresql.org/docs/9.2/interactive/functions-string.html), most reported problems would go away.

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-12-14 Thread Robert Haas
On Tue, Dec 11, 2012 at 12:59 AM, Jeff Davis pg...@j-davis.com wrote: For every developer who says wow, that mysql query just worked without modification there is another one who says oh, I forgot to test with option XYZ... postgres is too complex to support, I'm going to drop it from the list

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-12-14 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: ... In more than ten years of working with PostgreSQL, I've never encountered where the restriction at issue here prevented a bug. It's only annoyed me and broken my application code (when moving from PostgreSQL 8.2 to PostgreSQL 8.3, never mind any

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-12-14 Thread Robert Haas
On Fri, Dec 14, 2012 at 2:11 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: ... In more than ten years of working with PostgreSQL, I've never encountered where the restriction at issue here prevented a bug. It's only annoyed me and broken my application

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-12-13 Thread Jeff Davis
On Tue, 2012-11-06 at 10:57 -0500, Robert Haas wrote: I did some experimentation with this. It seems that what Tom proposed here is a lot cleaner than what I proposed previously, while still increasing usability in many real-world cases. For example, in unpatched master: It looks like we

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-12-13 Thread Jeff Davis
On Tue, 2012-11-27 at 14:24 -0800, Jeff Davis wrote: On Tue, 2012-11-27 at 15:41 -0500, Robert Haas wrote: I can't quite see how a non-overloaded flag would work, unless we get rid of schemas. It may work to pick the first schema in the search path that has any functions by that name, and

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-12-10 Thread Robert Haas
On Tue, Nov 27, 2012 at 4:36 PM, Peter Eisentraut pete...@gmx.net wrote: On 11/25/12 6:36 PM, Robert Haas wrote: I think that is true. But for whatever it's worth, and at the risk of beating a horse that seems not to be dead yet in spite of the fact that I feel I've already administered one

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-12-10 Thread Pavel Stehule
Hello 2012/12/10 Robert Haas robertmh...@gmail.com: On Tue, Nov 27, 2012 at 4:36 PM, Peter Eisentraut pete...@gmx.net wrote: On 11/25/12 6:36 PM, Robert Haas wrote: I think that is true. But for whatever it's worth, and at the risk of beating a horse that seems not to be dead yet in spite of

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-12-10 Thread Jeff Davis
On Mon, 2012-12-10 at 14:07 -0500, Robert Haas wrote: And we not only don't give them the behavior they want; we don't even have a meaningful way to give the option of opting into that behavior at initdb or create-database time. I strongly object to offering options that change the language

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-12-10 Thread Darren Duncan
I agree with Jeff. Options that change the language at initdb or create-database time just fragment the language. It is best to just have 1 language where options are providable either dynamically per connection or otherwise lexically, so that then they are really just shorthands for the

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-12-09 Thread Jan Wieck
I am aware that in the case at hand, the call to make_fn_arguments() is with the only possible candidate function, so changing COERCE_IMPLICIT to COERCE_ASSIGNMENT inside of make_fn_arguments() is correct. But I wonder if this may have any unwanted side effects for other code paths to

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-27 Thread Jeff Davis
On Sun, 2012-11-25 at 21:08 -0500, Robert Haas wrote: That, however, is a separate question from what's under discussion here, because the case at issue for the proposed patch is the one in which only one possible candidate exists, and the question is whether we ought to allow the use of

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-27 Thread Jeff Davis
On Wed, 2012-11-21 at 15:27 +, Simon Riggs wrote: It would be useful if we issued a NOTICE when an ambiguity is introduced, rather than when using it. Like Bison's reporting of reduce conflicts. This brings up a very important point, which is that a lot of the code is frozen in

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-27 Thread Pavel Stehule
Hello all 2012/11/27 Jeff Davis pg...@j-davis.com: On Wed, 2012-11-21 at 15:27 +, Simon Riggs wrote: It would be useful if we issued a NOTICE when an ambiguity is introduced, rather than when using it. Like Bison's reporting of reduce conflicts. This brings up a very important point,

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-27 Thread Bruce Momjian
On Tue, Nov 27, 2012 at 01:59:04AM -0800, Jeff Davis wrote: On Wed, 2012-11-21 at 15:27 +, Simon Riggs wrote: It would be useful if we issued a NOTICE when an ambiguity is introduced, rather than when using it. Like Bison's reporting of reduce conflicts. This brings up a very

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-27 Thread Pavel Stehule
2012/11/27 Bruce Momjian br...@momjian.us: On Tue, Nov 27, 2012 at 01:59:04AM -0800, Jeff Davis wrote: On Wed, 2012-11-21 at 15:27 +, Simon Riggs wrote: It would be useful if we issued a NOTICE when an ambiguity is introduced, rather than when using it. Like Bison's reporting of

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-27 Thread Merlin Moncure
On Tue, Nov 27, 2012 at 10:52 AM, Pavel Stehule pavel.steh...@gmail.com wrote: it is a basic problem - PostgreSQL has unique possibilities - polymorphic parameters and almost all databases doesn't support overloading Speaking of polymorphism, why not just implement lpad()'s first argument as

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-27 Thread Robert Haas
On Tue, Nov 27, 2012 at 4:46 AM, Jeff Davis pg...@j-davis.com wrote: Let's say you have only one function foo. All your queries are coded into your application, and everything works fine, using assignment casts where necessary. Then the user is foolish enough to CREATE FUNCTION foo... and now

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-27 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: I admit that there are cases where this could happen, and that it will happen a little more than it does now. But, as you say, this can happen now, and yet we get very few if any complaints about it, whereas we get regular complaints about the need to

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-27 Thread Jeff Davis
On Tue, 2012-11-27 at 12:19 -0500, Robert Haas wrote: I admit that there are cases where this could happen, and that it will happen a little more than it does now. But, as you say, this can happen now, and yet we get very few if any complaints about it, whereas we get regular complaints about

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-27 Thread Robert Haas
On Tue, Nov 27, 2012 at 12:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I admit that there are cases where this could happen, and that it will happen a little more than it does now. But, as you say, this can happen now, and yet we get very few if any

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-27 Thread Robert Haas
On Tue, Nov 27, 2012 at 1:45 PM, Jeff Davis pg...@j-davis.com wrote: On Tue, 2012-11-27 at 12:19 -0500, Robert Haas wrote: I admit that there are cases where this could happen, and that it will happen a little more than it does now. But, as you say, this can happen now, and yet we get very

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-27 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: But as to your point about the system catalogs, it is true that adding an additional function could reduce the number of cases where things work today. But I think in many cases it would eliminate the need for overloading that we already have, and

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-27 Thread Jeff Davis
On Tue, 2012-11-27 at 14:13 -0500, Robert Haas wrote: I do think that applying some kind of explicit flag to the function indicating whether it should allow implicit assignment casting/implicit casting to text/overloading/whatever is a possibly interesting alternative. It seems clear from our

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-27 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: I do think that applying some kind of explicit flag to the function indicating whether it should allow implicit assignment casting/implicit casting to text/overloading/whatever is a possibly interesting alternative. That idea seems possibly worth

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-27 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: It also affects developers, in that adding a new overloaded version of a system function (that previously wasn't overloaded) could actually reduce the number of cases for which the function works without an explicit cast. We have got to be really

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-27 Thread Robert Haas
On Tue, Nov 27, 2012 at 2:26 PM, Tom Lane t...@sss.pgh.pa.us wrote: Uh, no, not really, and I think that assertion just goes to show that this area is more subtle than you think. quote_literal() for instance presently works for any datatype that has an explicit cast to text. That doesn't

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-27 Thread Robert Haas
On Tue, Nov 27, 2012 at 2:59 PM, Jeff Davis pg...@j-davis.com wrote: What user action are you concerned about? If we (eventually) made the non-overloaded case the default, would that resolve your concerns? I can't quite see how a non-overloaded flag would work, unless we get rid of schemas.

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-27 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Tue, Nov 27, 2012 at 2:26 PM, Tom Lane t...@sss.pgh.pa.us wrote: After making the change you propose above, it would only work for types for which the cast was assignment-grade or less. ...but that's everything, because there's a hardcoded

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-27 Thread Robert Haas
On Tue, Nov 27, 2012 at 3:21 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I do think that applying some kind of explicit flag to the function indicating whether it should allow implicit assignment casting/implicit casting to text/overloading/whatever is a

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-27 Thread Peter Eisentraut
On 11/25/12 6:36 PM, Robert Haas wrote: I think that is true. But for whatever it's worth, and at the risk of beating a horse that seems not to be dead yet in spite of the fact that I feel I've already administered one hell of a beating, the LPAD case is unambiguous, and therefore it is hard

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-27 Thread Peter Eisentraut
On 11/25/12 7:21 PM, Robert Haas wrote: Sure, in theory that is true, but no other RDBMS that I know about feels a need to error out in that situation. I'm skeptical of the contention that we're smarter than everyone else. Well, I think in most programming languages that have typed function

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-27 Thread Peter Eisentraut
On 11/27/12 12:07 PM, Merlin Moncure wrote: Speaking of polymorphism, why not just implement lpad()'s first argument as 'anyelement'? One of the arguments made here was that lpad(not-text) *should* fail. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-27 Thread Merlin Moncure
On Tue, Nov 27, 2012 at 4:09 PM, Peter Eisentraut pete...@gmx.net wrote: On 11/27/12 12:07 PM, Merlin Moncure wrote: Speaking of polymorphism, why not just implement lpad()'s first argument as 'anyelement'? One of the arguments made here was that lpad(not-text) *should* fail. Well, sure. My

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-27 Thread Jeff Davis
On Tue, 2012-11-27 at 15:41 -0500, Robert Haas wrote: I can't quite see how a non-overloaded flag would work, unless we get rid of schemas. It may work to pick the first schema in the search path that has any functions by that name, and then choose the overloaded (or not) candidate from among

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-27 Thread Tom Lane
Merlin Moncure mmonc...@gmail.com writes: ... I think if you relaxed the function sigs of a few functions on this page (http://www.postgresql.org/docs/9.2/interactive/functions-string.html), most reported problems would go away. That's an interesting way of approaching it. Do we have any

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-27 Thread Merlin Moncure
On Tue, Nov 27, 2012 at 4:41 PM, Tom Lane t...@sss.pgh.pa.us wrote: Merlin Moncure mmonc...@gmail.com writes: ... I think if you relaxed the function sigs of a few functions on this page (http://www.postgresql.org/docs/9.2/interactive/functions-string.html), most reported problems would go

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-27 Thread John R Pierce
On 11/27/12 2:41 PM, Tom Lane wrote: Tweaking the casting rules could have a lot of unforeseen consequences. understatement of the year. IMHO. $0.02 worth etc. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-27 Thread Pavel Stehule
2012/11/28 Merlin Moncure mmonc...@gmail.com: On Tue, Nov 27, 2012 at 4:41 PM, Tom Lane t...@sss.pgh.pa.us wrote: Merlin Moncure mmonc...@gmail.com writes: ... I think if you relaxed the function sigs of a few functions on this page

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-25 Thread Robert Haas
On Wed, Nov 21, 2012 at 5:10 PM, Peter Eisentraut pete...@gmx.net wrote: Because a strongly-typed system should not cast numbers to strings implicitly. Does the equivalent of the lpad case work in any other strongly-typed programming language? Does any other strongly-typed programming

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-25 Thread Robert Haas
On Thu, Nov 22, 2012 at 10:17 AM, Tom Lane t...@sss.pgh.pa.us wrote: The argument here is basically between ease of use and ability to detect common programming mistakes. It's not clear to me that there is any principled way to make such a tradeoff, because different people can reasonably put

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-25 Thread Josh Berkus
I'd be fine with that if we had a system that allows users to pick the behavior that they want in their particular environment, but in fact our existing system is extremely inflexible. If you start adding additional implicit casts to the system, you get failures trying to invoke

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-25 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Thu, Nov 22, 2012 at 10:17 AM, Tom Lane t...@sss.pgh.pa.us wrote: The argument here is basically between ease of use and ability to detect common programming mistakes. It's not clear to me that there is any principled way to make such a tradeoff,

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-25 Thread Peter Geoghegan
On 25 November 2012 23:31, Robert Haas robertmh...@gmail.com wrote: The only other programming language I know of in which you can define what it means to cast between two data types is C++, and it's not generally considered one of that languages better features. AFAICT, they have implicit

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-25 Thread Robert Haas
On Sun, Nov 25, 2012 at 6:46 PM, Tom Lane t...@sss.pgh.pa.us wrote: I think we're talking past each other here. It is unarguable that (as long as there's only one LPAD function) there is only one possible non-error interpretation. However, you are ignoring the real possibility that perhaps

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-25 Thread Robert Haas
On Sun, Nov 25, 2012 at 7:05 PM, Peter Geoghegan pe...@2ndquadrant.com wrote: Well, you can make your class copy-constructable by providing a constructor (and a copy-assignment operator) whose only argument is, say, an int. In additional to that, you could potentially define a conversion

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-25 Thread Peter Geoghegan
On 26 November 2012 00:24, Robert Haas robertmh...@gmail.com wrote: I remember this sort of thing un-fondly from my C++ days, but it doesn't make me like our current behavior any better. You can also make a constructor with a single argument explicit, and thereby prevent implicit conversions.

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-25 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: So I think the answer to your question is probably no, purely on the grounds that we have set a new world record for byzantine casting systems. A more fair comparison might be to look at what other SQL systems allow. Oracle, MySQL, and SQL Server

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-25 Thread Robert Haas
On Sun, Nov 25, 2012 at 7:36 PM, Peter Geoghegan pe...@2ndquadrant.com wrote: On 26 November 2012 00:24, Robert Haas robertmh...@gmail.com wrote: I remember this sort of thing un-fondly from my C++ days, but it doesn't make me like our current behavior any better. You can also make a

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-25 Thread Robert Haas
On Sun, Nov 25, 2012 at 6:46 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I think we're talking past each other here. It is unarguable that (as long as there's only one LPAD function) there is only one possible non-error interpretation. However, you are

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-25 Thread Robert Haas
On Sun, Nov 25, 2012 at 7:39 PM, Tom Lane t...@sss.pgh.pa.us wrote: I think this is ignoring the fact that we have an extensible type system, and thus a lot more room for problems if we allow too many implicit casts. I don't deny that. It might also be worth noting that some of this

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-22 Thread Tom Lane
Peter Eisentraut pete...@gmx.net writes: On 11/21/12 9:42 AM, Robert Haas wrote: On Mon, Nov 19, 2012 at 6:19 PM, Peter Eisentraut pete...@gmx.net wrote: I continue to be of the opinion that allowing this second case to work is not desirable. 1. Why? Because a strongly-typed system should

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-21 Thread Robert Haas
On Mon, Nov 19, 2012 at 6:19 PM, Peter Eisentraut pete...@gmx.net wrote: On Tue, 2012-11-06 at 10:57 -0500, Robert Haas wrote: But, with the attached patch: rhaas=# create function xyz(smallint) returns smallint as $$select $1$$ language sql; CREATE FUNCTION rhaas=# select xyz(5); xyz

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-21 Thread Simon Riggs
On 29 August 2012 23:39, Tom Lane t...@sss.pgh.pa.us wrote: The main downside I can see is that code that used to work is likely to stop working as soon as someone creates a potential overloading situation. Worse, the error message could be pretty confusing, since if you had been

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-21 Thread Tom Lane
Simon Riggs si...@2ndquadrant.com writes: It would be useful if we issued a NOTICE when an ambiguity is introduced, rather than when using it. I think that's pie in the sky, since whether there is an ambiguity will depend not only on what set of functions exists, but what the caller's

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-21 Thread Peter Eisentraut
On 11/21/12 9:42 AM, Robert Haas wrote: On Mon, Nov 19, 2012 at 6:19 PM, Peter Eisentraut pete...@gmx.net wrote: On Tue, 2012-11-06 at 10:57 -0500, Robert Haas wrote: But, with the attached patch: rhaas=# create function xyz(smallint) returns smallint as $$select $1$$ language sql; CREATE

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-19 Thread Peter Eisentraut
On Tue, 2012-11-06 at 10:57 -0500, Robert Haas wrote: But, with the attached patch: rhaas=# create function xyz(smallint) returns smallint as $$select $1$$ language sql; CREATE FUNCTION rhaas=# select xyz(5); xyz - 5 (1 row) rhaas=# create table abc (a int); CREATE TABLE

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-11-06 Thread Robert Haas
On Thu, Aug 30, 2012 at 9:13 AM, Robert Haas robertmh...@gmail.com wrote: Upthread you were complaining about how we'd reject calls even when there was only one possible interpretation. I wonder whether there'd be any value in taking that literally: that is, allow use of assignment rules when

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-08-30 Thread David Fetter
On Wed, Aug 29, 2012 at 06:39:37PM -0400, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On the more general issue, I continue to see minimal risk of harm in allowing things like LPAD() to implicitly cast the first argument to text. Well, I see your point about LPAD(), but the

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-08-30 Thread Robert Haas
On Wed, Aug 29, 2012 at 6:39 PM, Tom Lane t...@sss.pgh.pa.us wrote: Well, I see your point about LPAD(), but the problem is how to tell the difference between a harmless cast omission and an actual mistake that the user will be very grateful if we point out. If we allow implicit casts to text

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-08-29 Thread Benedikt Grundmann
On Tue, Aug 28, 2012 at 9:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Tue, Aug 28, 2012 at 2:55 PM, Tom Lane t...@sss.pgh.pa.us wrote: Oh, I'd forgotten that worked that way. Frankly, that makes me quite a bit more concerned about this proposal

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-08-29 Thread Robert Haas
On Tue, Aug 28, 2012 at 11:23 PM, Tom Lane t...@sss.pgh.pa.us wrote: That argument would hold water if we got rid of every single usage of overloading in the system-defined operators/functions, which as you well know is not an attractive idea. Since that's not going to happen, arguing for

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-08-29 Thread Robert Haas
On Wed, Aug 29, 2012 at 12:27 AM, Tom Lane t...@sss.pgh.pa.us wrote: To put some concreteness into what so far has been a pretty hand-wavy discussion, I experimented with the attached patch. I'm not sure that it exactly corresponds to what you proposed, but I think this is the only place the

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-08-29 Thread Gavin Flower
On 29/08/12 23:34, Robert Haas wrote: On Wed, Aug 29, 2012 at 12:27 AM, Tom Lane t...@sss.pgh.pa.us wrote: To put some concreteness into what so far has been a pretty hand-wavy discussion, I experimented with the attached patch. I'm not sure that it exactly corresponds to what you proposed, but

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-08-29 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: The upshot here appears to be that we're kind of schizophrenic about what we want. With things like text || anyelement, anyelement || text, and concat(variadic any) we are basically asserting that we want to treat anything that we don't recognize as a

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-08-29 Thread Peter Eisentraut
On 8/29/12 11:40 AM, Tom Lane wrote: regression=# select lpad(42,8); ERROR: failed to find conversion function from integer to text I'm not sure why that's happening, either, but evidently some additional coercion laxity would required. This, however, is a trivial problem; make_fn_arguments

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-08-29 Thread Tom Lane
Peter Eisentraut pete...@gmx.net writes: On 8/29/12 11:40 AM, Tom Lane wrote: regression=# select lpad(42,8); We are not seriously intending to make the above query work, are we? Well, *I* don't want to, but apparently Robert does. I don't really want to go much further than finding a way to

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-08-29 Thread Robert Haas
On Wed, Aug 29, 2012 at 3:54 PM, Tom Lane t...@sss.pgh.pa.us wrote: Peter Eisentraut pete...@gmx.net writes: On 8/29/12 11:40 AM, Tom Lane wrote: regression=# select lpad(42,8); We are not seriously intending to make the above query work, are we? Well, *I* don't want to, but apparently

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-08-29 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On the more general issue, I continue to see minimal risk of harm in allowing things like LPAD() to implicitly cast the first argument to text. Well, I see your point about LPAD(), but the problem is how to tell the difference between a harmless cast

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-08-28 Thread Robert Haas
On Mon, Aug 27, 2012 at 7:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I agree that redefining the lexer behavior is a can of worms. What I don't understand is why f(2+2) can't call f(smallint) when that's the only extant f. It seems to me that we could

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-08-28 Thread Greg Stark
On Mon, Aug 27, 2012 at 10:03 PM, Robert Haas robertmh...@gmail.com wrote: We really ought to put some effort into solving this problem. I've seen a few Oracle-migration talks at conferences, and *every one* of them has mentioned the smallint problem. It hits our customers, too. I'm kind of

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-08-28 Thread Tom Lane
Greg Stark st...@mit.edu writes: Perhaps just a warning on CREATE FUNCTION when one of the arguments doesn't have an implicit cast from the canonical data type of that hierarchy saying perhaps you should consider using that data type and let Postgres convert instead of the more specific data

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-08-28 Thread Robert Haas
On Tue, Aug 28, 2012 at 1:06 PM, Tom Lane t...@sss.pgh.pa.us wrote: Also, right at the moment it's not clear to me whether there are any other cases besides integer literal vs smallint argument. I think that's the only particularly surprising case within the numeric hierarchy --- and for

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-08-28 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Tue, Aug 28, 2012 at 1:06 PM, Tom Lane t...@sss.pgh.pa.us wrote: Also, right at the moment it's not clear to me whether there are any other cases besides integer literal vs smallint argument. I think that's the only particularly surprising case

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-08-28 Thread Jim Nasby
On 8/27/12 5:19 PM, Greg Sabino Mullane wrote: Tom Lane replied: Come on, really? Note that the above example works without casts if you use int*or* bigint*or* numeric, but not smallint. That could be fixed by causing sufficiently-small integers to lex as smallints, Is there any general

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-08-28 Thread Robert Haas
On Tue, Aug 28, 2012 at 1:39 PM, Tom Lane t...@sss.pgh.pa.us wrote: The other case that comes up regularly is someone trying to pass some kind of number to a function such as LPAD(). There is only one LPAD() so no ambiguity exists, but PostgreSQL doesn't even see that there's a candidate.

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-08-28 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Tue, Aug 28, 2012 at 1:39 PM, Tom Lane t...@sss.pgh.pa.us wrote: There still won't be a candidate for that one, unless you're proposing to allow explicit-only coercions to be applied implicitly. [ not so, see kluge in find_coercion_pathway() ] Oh,

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-08-28 Thread Robert Haas
On Tue, Aug 28, 2012 at 2:55 PM, Tom Lane t...@sss.pgh.pa.us wrote: Oh, I'd forgotten that worked that way. Frankly, that makes me quite a bit more concerned about this proposal than I was before. I do *not* want to re-introduce silent cross-category casts to text, not even if there's no

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-08-28 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Tue, Aug 28, 2012 at 2:55 PM, Tom Lane t...@sss.pgh.pa.us wrote: Oh, I'd forgotten that worked that way. Frankly, that makes me quite a bit more concerned about this proposal than I was before. I do *not* want to re-introduce silent cross-category

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-08-28 Thread Craig Ringer
On 08/29/2012 01:32 AM, Robert Haas wrote: On Tue, Aug 28, 2012 at 1:06 PM, Tom Lane t...@sss.pgh.pa.us wrote: Also, right at the moment it's not clear to me whether there are any other cases besides integer literal vs smallint argument. I think that's the only particularly surprising case

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-08-28 Thread Robert Haas
On Tue, Aug 28, 2012 at 4:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: Well, I think that when there is only one LPAD function, there is also very little chance that the results will come out differently than the user expected. [ shrug... ] I'm having a hard time resisting the temptation to

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-08-28 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Tue, Aug 28, 2012 at 4:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: The real point here though is that the proposed behavior change will affect all functions, not only the cases where you think there is only one sane behavior. And features such as

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-08-28 Thread Tom Lane
I wrote: Robert Haas robertmh...@gmail.com writes: That problem is dead. The reason it's dead is that we killed it in 8.3. I don't want it coming back to life, but I think that that will be exactly the outcome if we let any implicit casts to text get back into the rules for

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-08-27 Thread Bruce Momjian
On Fri, Feb 17, 2012 at 02:52:20PM -0500, Robert Haas wrote: Here's yet another case where the current rules are thoroughly disagreeable. rhaas=# create or replace function z(smallint) returns smallint as $$select $1+1$$ language sql; ERROR: return type mismatch in function declared to

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-08-27 Thread Tom Lane
Bruce Momjian br...@momjian.us writes: On Fri, Feb 17, 2012 at 02:52:20PM -0500, Robert Haas wrote: Come on, really? Note that the above example works without casts if you use int *or* bigint *or* numeric, but not smallint. That could be fixed by causing sufficiently-small integers to lex as

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-08-27 Thread Bruce Momjian
On Mon, Aug 27, 2012 at 04:03:05PM -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: On Fri, Feb 17, 2012 at 02:52:20PM -0500, Robert Haas wrote: Come on, really? Note that the above example works without casts if you use int *or* bigint *or* numeric, but not smallint. That

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-08-27 Thread Robert Haas
On Mon, Aug 27, 2012 at 4:03 PM, Tom Lane t...@sss.pgh.pa.us wrote: Bruce Momjian br...@momjian.us writes: On Fri, Feb 17, 2012 at 02:52:20PM -0500, Robert Haas wrote: Come on, really? Note that the above example works without casts if you use int *or* bigint *or* numeric, but not smallint.

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-08-27 Thread Greg Sabino Mullane
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Tom Lane replied: Come on, really? Note that the above example works without casts if you use int *or* bigint *or* numeric, but not smallint. That could be fixed by causing sufficiently-small integers to lex as smallints, Is there any

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-08-27 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: I agree that redefining the lexer behavior is a can of worms. What I don't understand is why f(2+2) can't call f(smallint) when that's the only extant f. It seems to me that we could do that without breaking anything that works today: if you look for

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-02-18 Thread Greg Sabino Mullane
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 The time I got bitten by this was actually with LPAD(), rather than LIKE. +1. This is one of the functions that gave some of our clients real trouble when 8.3 came out. If we really believed that implicit casts any form were evil, we

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-02-18 Thread Rob Wultsch
On Fri, Feb 17, 2012 at 4:12 PM, Josh Berkus j...@agliodbs.com wrote: On 2/17/12 12:04 PM, Robert Haas wrote: The argument isn't about whether the user made the right design choices; it's about whether he should be forced to insert an explicit type cast to get the query to do what it is

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-02-18 Thread Don Baccus
On Feb 18, 2012, at 12:57 PM, Rob Wultsch wrote: Where first_name is string the queries above have very different behaviour in MySQL. The first does a full table scan and coerces first_name to an integer (so '5adfs' - 5) Oh my, I can't wait to see someone rise to the defense of *this*

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-02-18 Thread Christopher Browne
On Sat, Feb 18, 2012 at 4:12 PM, Don Baccus dhog...@pacifier.com wrote: On Feb 18, 2012, at 12:57 PM, Rob Wultsch wrote: Where first_name is string the queries above have very different behaviour in MySQL. The first does a full table scan and coerces first_name to an integer (so '5adfs' - 5)

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-02-18 Thread Don Baccus
On Feb 18, 2012, at 1:43 PM, Christopher Browne wrote: On Sat, Feb 18, 2012 at 4:12 PM, Don Baccus dhog...@pacifier.com wrote: On Feb 18, 2012, at 12:57 PM, Rob Wultsch wrote: Where first_name is string the queries above have very different behaviour in MySQL. The first does a full table

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-02-18 Thread Dimitri Fontaine
Don Baccus dhog...@pacifier.com writes: A hierarchy like the following is perfectly logical: - to 0999 :: Cash accounts [1] Your example is actually a good argument for storing account ids as text, because '' like '0%' *will* match. FWIW, I too think that if you want to process your

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-02-18 Thread Andrew Dunstan
On 02/18/2012 05:34 PM, Don Baccus wrote: On Feb 18, 2012, at 1:43 PM, Christopher Browne wrote: On Sat, Feb 18, 2012 at 4:12 PM, Don Baccusdhog...@pacifier.com wrote: On Feb 18, 2012, at 12:57 PM, Rob Wultsch wrote: Where first_name is string the queries above have very different

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-02-18 Thread Christopher Browne
On Sat, Feb 18, 2012 at 5:34 PM, Don Baccus dhog...@pacifier.com wrote: On Feb 18, 2012, at 1:43 PM, Christopher Browne wrote: A hierarchy like the following is perfectly logical: - to 0999 :: Cash accounts [1] I asked earlier if anyone would expect 01 like '0%' to match … Apparently

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-02-18 Thread Robert Haas
On Sat, Feb 18, 2012 at 4:12 PM, Don Baccus dhog...@pacifier.com wrote: On Feb 18, 2012, at 12:57 PM, Rob Wultsch wrote: Where first_name is string the queries above have very different behaviour in MySQL. The first does a full table scan and coerces first_name to an integer (so '5adfs' - 5)

Re: [HACKERS] MySQL search query is not executing in Postgres DB

2012-02-18 Thread Robert Haas
On Fri, Feb 17, 2012 at 7:12 PM, Josh Berkus j...@agliodbs.com wrote: On 2/17/12 12:04 PM, Robert Haas wrote: The argument isn't about whether the user made the right design choices; it's about whether he should be forced to insert an explicit type cast to get the query to do what it is

  1   2   >