On Thu, 2008-02-14 at 15:55 -0700, Ken Johanson wrote: > Granted, some of the other databases have bugs in their CAST > implementations (see http://bugs.mysql.com/bug.php?id=34562 & > http://bugs.mysql.com/bug.php?id=34564).... bugs which makes writing PG > 8.3 portable code arduous or impossible when dealing with legacy table > designs and program which counted on implicit casts. But even when mysql > gets fixed, one will ask: "should I re-code my apps just so they will > work with PG>=8.3 (and test the code on other DBs), or should I only > support PG<8.3? If postgresql were to revert to 8.2 implicit casting behavior, would that actually improve compatibility with other DBMSs? Every DBMS probably has it's own rules for implicit casting, different from every other DBMS. So are you sure it wouldn't just introduce more compatibility problems somewhere else? Or worse, it could hide the problems during migration/testing, and they could surface after you put it into production. > From > http://www.postgresql.org/docs/8.3/static/release-8-3.html: > > "these expressions formerly worked: > 1) substr(current_date, 1, 4) > 2) 23 LIKE '2%' > but will now draw "function does not exist" and "operator does not > exist" errors respectively. Use an explicit cast instead. > 3) current_date < 2017-11-17 > " > > (the section also prominently cites a non-portable cast syntax) > > Questions: > > For case 1, regarding type safety: we know use of LIKE (and SUBSTR) > requires *implicit or explicit* conversion to a text type. Why require Not this substr() function: create function substr(date, int, int) returns text as $$ begin return 'foo'; end; $$ language plpgsql; Are you saying we make special cases for all of the "obvious" functions, and they should behave differently from user-defined functions? > For case #3, I see "in the presence of automatic casts both sides were > cast to text", but can that not be fixed? Operand 2 yields an integer, > and integer and date compares should failfast, they are not comparable. > (I believe sql requires delimited iso8601 fields, so both 20080414120000 > and '20080414120000' should failfast) > > int compare(datetime a, object b) > { > if (typeof(b=="charsequence") > return compare(cast(b as datetime), a); > if (typeof(b)=="date") > return compare(cast(b as datetime), a); > if (typeof(b)=="time") //allowed? > return compare(cast(b as datetime), a); > throw "cannot compare "+a.getClass()+" and "+b.getClass(); > } > > WHERE (current_date < 2017-11-17) -> FAIL > WHERE (current_date < CAST(2017-11-17 AS datetime)) -> FAIL > WHERE (current_date < '2017-11-17') -> PASS > WHERE (current_date < CAST('2017-11-17' AS datetime)) -> PASS > > Sure, we know stricter typing will help performance, by encouraging I don't think performance was the goal of removing implicit type casts. > more-correct design. For example it may be prudent to ALWAYS failfast > when trying to join key/indexed table columns of differing types (one > char to another's int). But on non-key where conditions? Or make that a > behavior option. Perhaps An SQLWarning could be set when mismatches > occur, yet make a best effort at autocast. A few comments: * Keys should not behave differently from non-keys in comparisons. * I think the distinction you're trying to make is the casting of literals versus the casting of variables. All of the examples you gave involved literals. * If it's making its "best effort" to autocast, and it fails, how could it merely issue a warning? If it can't find a match it needs to error, because there's nothing it can do to continue even if we wanted it to. * It can almost always find a match, the question is whether it is the match that the user intended or not. Regards, Jeff Davis ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
