Обсуждение: Implicit casting and JOIN syntax constraints

>> HOWEVER.....look at this:
>> EXPLAIN ANALYZE select batchdetailid from batchdetail where batchdetailid = 27321::bigint;
>>  Index Scan using batchdetail_pkey on batchdetail  (cost=0.00..4.13 rows=1 width=8) (actual time=0.03..0.03 rows=1
loops=1)
>>    Index Cond: (batchdetailid = 27321::bigint)
>>  Total runtime: 0.07 msec

> Jeff Trout wrote:
> We had this happen to us - we had a serial8 column (int8) and our query
> was straight forward where id = 12345; which ran craptacularly.  After
> much head banging and cursing I had tried where id = '12345' and it
> magically worked. I think the parser is interpreting a "number" to be an
> int4 instead of int8.  (instead of quotes you can also cast via
> 12345::int8 like you did)

> Perhaps this should go on the TODO - when one side is an int8 and the
> other is a literal number assume the number to be int8 instead of int4?

It seems to me that this should absolutely go on the TODO list.  Why does the planner require an explicit cast when the
implicitcast is so obvious?  Does Oracle do this?  I can assure you that MSSQL does not.  
 
 
If getting more people to migrate to PostgreSQL is a major goal these days, it's got to be relatively easy.  I think
thatalmost everyone coming from a MSSQL or Access background is going to have big problems with this.  And the other
issueof the JOIN syntax constraining the planner - you've got to be able to turn that off too.  I've been writing SQL
queriesfor 10 years in FoxPro, Access, SQL Server, MySQL, and Sybase.  I have never come across this very confusing
"feature"until now.  
 
 
How do we go about voting an issue onto the TODO list?  These two get my vote for sure!
 
Roman

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
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110