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

Alvaro Herrera wrote:
> Ken Johanson wrote:
>> Alvaro Herrera wrote:
>>> If you are arguing that the spec's definition of the CHARACTER type is
>>> not really very useful, I think you are going to find a lot of
>>> supporters. You can send your complaints to the SQL committee; but
>>> then, it is unlikely that this is going to change anytime soon because
>>> of the fear of breaking backwards compatibility.
>> Agreed. There may be allot of users who in the case of CAST, need to
>> extract the leftmost digit, and for those database and their users that
>> do this by default, it would cause harm.
>
> Right. And those that don't need that are certainly using a cast to
> CHARACTER VARYING.

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?

mysql> CREATE TEMPORARY TABLE ttx (c char);
Query OK, 0 rows affected (0.00 sec)

mysql> describe ttx;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c | char(1) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> INSERT INTO ttx VALUES (123);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> SELECT * FROM ttx;
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

Hmm - looks like a cast to char doesn't produce values that fit into a
column defined as char. I'll say this for MySQL - always something new
to learn!

Looks like you have to choose between convenience and sanity Ken.

--
Richard Huxton
Archonet Ltd