I disagree about as vehemently as possible. You should get a warning on comparisons between incompatible types that cause float conversions. You get unexpected wrong results otherwise. The MySQL warning is therefor critical.

Not warning for this is just as stupid as not having ONLY_FULL_GROUP_BY on by default, which I think is downright sinful.

--Swany

On Wed, Dec 11, 2013 at 12:23 PM, Sergei Golubchik serg@mariadb.org wrote:

Hi, Federico!

On Dec 11, Federico Razzoli wrote:

...

Hi! MySQL 5.6 returns a warning for wrong datatypes, with any SQL_MODE:

mysql> CREATE TABLE t (c TIMESTAMP) ENGINE=InnoDB; Query OK, 0 rows affected (0,31 sec)

mysql> SELECT c FROM t WHERE c = 1; Empty set, 1 warning (0,00 sec)

...

Warning (Code 1292): Incorrect datetime value: '1' for column 'c' at row

1

...

Will this be done in MariaDB 10 too? Currently, no warning is issued.

This is intentional. You get a warning if you do, for example

INSERT t VALUES (1);

because in this case a decimal value 1 is converted into a TIMESTAMP.

But for comparison purposes you only need to know whether c is equal to 1 or not. You did not request any conversion, you only asked the server whether these values are equal. Thus you should not expect any warnings either.

Regards, Sergei

_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

Hi,

How interesting.

This behavior yields some strange results: Database changed

mysql> CREATE TABLE t (c TIMESTAMP) ENGINE=InnoDB; Query OK, 0 rows affected (0.06 sec)

mysql> insert into t values (1); Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> show warnings; +---------+------+----------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------+ | Warning | 1265 | Data truncated for column 'c' at row 1 | +---------+------+----------------------------------------+ 1 row in set (0.00 sec)

mysql> select * from t; +---------------------+ | c | +---------------------+ | 0000-00-00 00:00:00 | +---------------------+ 1 row in set (0.00 sec)

mysql> select * from t where c = 1; Empty set, 1 warning (0.00 sec)

mysql> show warnings; +---------+------+-------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------+ | Warning | 1292 | Incorrect datetime value: '1' for column 'c' at row 1 | +---------+------+-------------------------------------------------------+ 1 row in set (0.00 sec)

On Wed, Dec 11, 2013 at 2:08 PM, Sergei Golubchik serg@mariadb.org wrote:

Hi, Justin!

On Dec 11, Justin Swanhart wrote:

...

I disagree about as vehemently as possible. You should get a warning on comparisons between incompatible types that cause float conversions. You get unexpected wrong results otherwise. The MySQL warning is therefor critical.

That would be true if you get unexpected wrong results otherwise. But here you don't (and no float conversion either, as far as I know).

In this query no explicit type conversion is requested and internal type conversons for comparison purposes should not generate warnings as long as they stay completely internal. Wrong results means that the internal implementation is leaking into the user space - and that's when you start needing a warning.

Regards, Sergei