Re: SELECT CAST(123 AS char) -> 1

Richard Huxton wrote:

> What I couldn't figure out was what type MySQL was using. I mean, what
> type is this?
>
> mysql> SELECT cast(a as char) as achar FROM tt;
> +-------+
> | achar |
> +-------+
> | 1 |
> | 10 |
> +-------+
> 2 rows in set (0.00 sec)
>
> Is it char(2)?
>
> mysql> CREATE TEMPORARY TABLE ttchar0 AS SELECT cast(a as char) as achar
> FROM tt;
> Query OK, 2 rows affected (0.00 sec)
> Records: 2 Duplicates: 0 Warnings: 0
>
> mysql> describe ttchar0;
> +-------+-------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +-------+-------------+------+-----+---------+-------+
> | achar | varchar(11) | YES | | NULL | |
> +-------+-------------+------+-----+---------+-------+
>
>
> TA DAH! It looks like you are casting to varchar anyway Ken, it's just
> that MySQL isn't letting you spell it properly. So does an unconstrained
> "char" just map to varchar with MySQL then?
>

I think the issue here (subjective) is: is unconstrained CAST(n AS
char), a DDL statement in the storage sense, or in the return
type/function sense? Thats how PG and the other's CAST differ, anyway.
While the spec doesn't seem to qualify that and it's safe to assume
'char' type should just behave the same even in the context of cast +
number, both Ms and My's CAST treat numeric inputs as auto-size on
select stmts.

Add to that, that Mysql does the auto-trim thing which might be
affecting/confusing some operations (inserts for starters). I don't
know. Surprisingly I've never had a user complain about that trim
spec-deviation, nor case-insens compares (though I always teach/code
case-folding for portability).

Richard, when you say "casting to varchar anyway", it's not possible by
syntax (shortcoming in their current cast impl), so using char was a
next best thing to try. Your test ultimately applies I think to a table
DDL and not select.

Anyway, there are to many barriers (real or imagined) for my users to
migrate to PG from My and Ms (AS-less labels, result set metadata,
auto-generated keys, now stricter typing) so I've already resolved to
check back and see how things look when 8.4 comes out. Maybe it'll be
more spec compliant... a good thing, for one DB ..or another..

I'm patient and an old pro at this, I've been evaluating PG for about 9
years now, and despite the amazing number of features and advantages
it's always had, customers come out in favor of the other DBs because
they only need a small subset of them, and rate convenience (ala
autocast) over specs which they know nothing of. Well, at least we have
standard_conforming_strings now. I digress and am touching on another
thread someone brought up.