Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 387C7475F39 for ; Fri, 17 Jan 2003 10:41:06 -0500 (EST) Received: from mail.libertyrms.com (unknown [209.167.124.227]) by postgresql.org (Postfix) with ESMTP id A1522475EBF for ; Fri, 17 Jan 2003 10:41:05 -0500 (EST) Received: from andrew by mail.libertyrms.com with local (Exim 3.22 #3 (Debian)) id 18ZYcI-0006cl-00 for ; Fri, 17 Jan 2003 10:41:10 -0500 Date: Fri, 17 Jan 2003 10:41:10 -0500 From: Andrew Sullivan To: pgsql-performance@postgresql.org Subject: Re: Implicit casting and JOIN syntax constraints Message-ID: <20030117104110.G23422@mail.libertyrms.com> Mail-Followup-To: Andrew Sullivan , pgsql-performance@postgresql.org References: <9B1C77393DED0D4B9DAA1AA1742942DA0E4C0B@pos_pdc.posportal.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5i In-Reply-To: <9B1C77393DED0D4B9DAA1AA1742942DA0E4C0B@pos_pdc.posportal.com>; from rfail@posportal.com on Fri, Jan 17, 2003 at 06:48:28AM -0800 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/183 X-Sequence-Number: 830 On Fri, Jan 17, 2003 at 06:48:28AM -0800, Roman Fail wrote: > It seems to me that this should absolutely go on the TODO list. > Why does the planner require an explicit cast when the implicit > cast is so obvious? Does Oracle do this? I can assure you that > MSSQL does not. The reason it happens is because of the flexible datatype system in PostgreSQL. Because it's easy to add a datatype, you pay in other ways. The problem is coming up with a nice, clean set of rules for coercion. See the link that Tom Lane posted, and the thousands of other discussions around this in the archives. Yes, it's a pain. Everyone knows that. A complete solution is what's missing. > too. I've been writing SQL queries for 10 years in FoxPro, Access, > SQL Server, MySQL, and Sybase. I have never come across this very > confusing "feature" until now. Well, there are differences between every system. Indeed, the "SQL" of MySQL is so far from anything resembling the standard that one could argue it doesn't comply at all. You're right that it means a steep learning curve for some things, and the problems can be frustrating. But that doesn't mean you want to throw the baby out with the bathwater. The ability to give the planner hints through the JOIN syntax is, frankly, a real help when you're faced with certain kinds of performance problems. Some systems don't give you a knob to tune there at all. Is it different from other systems? Sure. Is that automatically a reason to pitch the feature? No. (Further discussion of this probably belongs on -general, if anywhere, by the way.) A ---- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada M2P 2A8 +1 416 646 3304 x110