List:General Discussion« Previous MessageNext Message »
From:hsv Date:March 16 2013 12:51am
Subject:Re: a little doubt on text about MySQL
View as plain text  
>>>> 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.

Thread
a little doubt on text about MySQLMarcus Vinicius15 Mar
  • Re: a little doubt on text about MySQLReindl Harald15 Mar
  • Re: a little doubt on text about MySQLhsv16 Mar
    • Re: a little doubt on text about MySQLReindl Harald16 Mar
      • Re: a little doubt on text about MySQLhsv17 Mar
        • RE: a little doubt on text about MySQLRick James18 Mar