I continue to hope that 8.4 later (or 8.3 patched) will provide behavior
/ compatibility controls..
I personally have requested others in past, but one outstanding one now,
seems to be an ability to turn back-on implicit type conversion. I'm
struggling to see Mysql, Mssql, Oracle, and others following PG and
turning off their implicit conversion. People will have to choose
whether to re-code (explicit casts) their apps for sake of PG.
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?
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
that explicitly? Closet answer I can see, is that for (example #1)
text-formed dates in some servers are not iso8601 format. But, even
explicit datetime->text cast has this same hazard -- it doesn't require
a format argument.
For case #2, explicit or implicit again have same risk: that base-10 and
with/out decimal, should be the default format.
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
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 char to int key comparison affects performance horridly and indicates
(though not always) a potential mis-design, yet it MAY be safe to
autocast the text to numeric before compare, because the narrowing
conversion will assert that only base-10 chars exist.
So, are there other examples of why, esp for runtime/type safety, we
should force explicit conversion?
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
