2013/03/15 12:43 -0300, Marcus Vinicius
Does anyone knows the author of this:
http://grimoire.ca/mysql/choose-something-else
Title: "Do Not Pass This Way Again"
Not I
--but, as to automatic type-conversion, I find me in agreement with the author.
When I first began to use MySQL I was dismayed at all the automatic conversion,
some of it decidedly unintuitive, and this one is a good example of particular
badness:
mysql> select 0 = 'banana';
+--+
| 0 = 'banana' |
+--+
|1 |
+--+
1 row in set, 1 warning (0.03 sec)
mysql> show warnings;
+-+--++
| Level | Code | Message|
+-+--++
| Warning | 1292 | Truncated incorrect DOUBLE value: 'banana' |
+-+--++
1 row in set (0.00 sec)
This definitly is an error to show, not let be a warning.
All those 0-values on saving into the table, The developers seem deeply
loath to report error. When the table is not transactional, yes, there is some
reason for it, but a not transactional table is not a full partner in database.
When the table is transactional, to report error is the way to go, although a
big LOAD DATA be aborted, not to make the user learn about all those 0-values.
He makes a point that had not come to me, that type-security somewhat depends
on SQL_MODE, which belongs to the connection. It is, quite rightly, stored in
saved program code, but not in any table. At least these affect table behavior,
ALLOW_INVALID_DATES
NO_AUTO_CREATE_USER (administrational security!)
NO_AUTO_VALUE_ON_ZERO
NO_ZERO_DATE
NO_ZERO_IN_DATE
PAD_CHAR_TO_FULL_LENGTH (well, maybe not this one)
STRICT_ALL_TABLES
STRICT_TRANS_TABLES
, and they belong in the table, even as those that affect parsing & compiling
belong in saved code, not only in the connection.
MySQL s own types, ENUM and SET, which have both string & integer
manifestation, are not well handled. Try this (version 5.5):
create temporary table v (m set ('a','b'));
insert into v value (1),(2),('a'),('b'),('b,a'),(null);
select m,if( m is not null, m, 21) + 1 from v;
select m,ifnull( m, 21) + 1 from v; -- no warning, either
(and the numeric context is DOUBLE!)
What about these?
select m,if( m is not null, m, 21) from v;
select m,ifnull( m, 21) from v;
I feel that the numeric constant sets the context to numeric--the IF[NULL] s
own context is indeterminate--, but not that happens.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
