Re: Strict-typing benefits/costs

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