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:

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,

NO_AUTO_CREATE_USER (administrational security!)
PAD_CHAR_TO_FULL_LENGTH (well, maybe not this one)

, 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.

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