Hi Roberto,
Is there any particular reason why you expect the first comparison should work? (Did you find it documented somewhere, or something?)
Otherwise, you are comparing a numeric value to a string, there is no "conversion" as far as I can see.
Try to use CAST if you need to convert the value.
no just because this:
SELECT * FROM tmp WHERE id=1 AND datahora="1340977500" -> return 1 row
SELECT * FROM tmp WHERE id=1 AND datahora="1340977500.0" -> return 1 row
SELECT * FROM tmp WHERE id=1 AND datahora="1340977500.00" -> return 1 row
SELECT * FROM tmp WHERE id=1 AND datahora="1340977500.000" -> return 1 row
SELECT * FROM tmp WHERE id=1 AND datahora="1340977500.0000" -> return 1 row
SELECT * FROM tmp WHERE id=1 AND datahora="1340977500.00000" -> return 1 row
SELECT * FROM tmp WHERE id=1 AND datahora="1340977500.000000" -> return 1 row
SELECT * FROM tmp WHERE id=1 AND datahora="1340977500.0000000" -> return 1 row
SELECT * FROM tmp WHERE id=1 AND datahora="1340977500.00000000" -> return 1 row
SELECT * FROM tmp WHERE id=1 AND datahora="1340977500.000000000" -> return 1 row
SELECT * FROM tmp WHERE id=1 AND datahora="1340977500.0000000000" -> return 1 row
SELECT * FROM tmp WHERE id=1 AND datahora="1340977500.00000000000" -> return 1 row
SELECT * FROM tmp WHERE id=1 AND datahora="1340977500.000000000000" -> don´t return (i don´t know why)
since 'datahora' field is DECIMAL(17,6) i don´t understand some things...
1) if there´s no 'conversion' from string "" to decimal to get rows at datahora fiel why any of this don´t returned a error?
2) ok, let´s say the 'conversion' is something that works, why it didn´t stop working at .0000000 (7 numbers) like decimal(17,6) definition?
3) there´s a reason why this work at .,00000000000 (11 numbers) and 12 numbers not ?
i din´t saw documentation , just using and trying =)
some more info:
1)
explain extended select * from tmp where datahora="1340977500.00000000000" (this one work)
id;select_type;table;type;possible_keys;key;key_len;ref;rows;filtered;Extra
1;SIMPLE;tmp;system;NULL;NULL;NULL;NULL;1;100.00;
show warnings:
Level;Code;Message
Note;1003;select 1 AS `id`,1340977500.000000 AS `datahora`,1.0000000000 AS `value` from `33_sensors_log`.`tmp` where (1340977500.000000 = '1340977500.00000000000')
2)
explain extended
select * from tmp where datahora="1340977500.000000000000" (don´t work)
id;select_type;table;type;possible_keys;key;key_len;ref;rows;filtered;Extra
1;SIMPLE;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL;Impossible WHERE noticed after reading const tables
show warnings:
Level;Code;Message
Note;1003;select 1 AS `id`,1340977500.000000 AS `datahora`,1.0000000000 AS `value` from `33_sensors_log`.`tmp` where (1340977500.000000 = '1340977500.000000000000')
a bit more info
INSERT INTO `tmp` (`id`, `datahora`) VALUES (1, 1234);
1)
explain extended
select * from tmp where datahora="1340977500.00000000000" (this work)
id;select_type;table;type;possible_keys;key;key_len;ref;rows;filtered;Extra
1;SIMPLE;tmp;ALL;NULL;NULL;NULL;NULL;2;100.00;Using where
show warnings
Level;Code;Message
Note;1003;select `33_sensors_log`.`tmp`.`id` AS `id`,`33_sensors_log`.`tmp`.`datahora` AS `datahora`,`33_sensors_log`.`tmp`.`value` AS `value` from `33_sensors_log`.`tmp` where (`33_sensors_log`.`tmp`.`datahora` = '1340977500.00000000000')
2)
explain extended
select * from tmp where datahora="1340977500.000000000000" (don´t work)
id;select_type;table;type;possible_keys;key;key_len;ref;rows;filtered;Extra
1;SIMPLE;tmp;ALL;NULL;NULL;NULL;NULL;2;100.00;Using where
show warnings
Level;Code;Message
Note;1003;select `33_sensors_log`.`tmp`.`id` AS `id`,`33_sensors_log`.`tmp`.`datahora` AS `datahora`,`33_sensors_log`.`tmp`.`value` AS `value` from `33_sensors_log`.`tmp` where (`33_sensors_log`.`tmp`.`datahora` = '1340977500.000000000000')
more information....
this last two posts was in version 5.3.2
i´m testing this in 5.5.23 now.... and it works, so ... sorry =) not a bug (for currently versions)... i will update the old server to a new version, tahnks 
just to post here if someone need this info and get this page...
this 'should' work with any string size (right pad "0")
select * from tmp where datahora=
"1340977500.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"
-> return 1 row as expected... (i tested with 1MB of "0" and worked too)
>> i din´t saw documentation , just using and trying =)
Roberto,
Please do try to check documentation, it might save a lot of time. You can start from here http://dev.mysql.com/doc/refman/5.1/en/type-conversion.html, and browse further, depending on what you are curious about.
If after reading it you find out that the server doesn't work as expected, please point out at the exact inconsistency.
Thanks.
in docs have some interesting informations...
mysql> SELECT '18015376320243458' = 18015376320243458;
-> 1
mysql> SELECT '18015376320243459' = 18015376320243459;
-> 0
but in my case, the string (the constant part) is in the right part and the field is in left (i don´t know if this change...)
i didn´t found information in my situation at this doc page... :/
since it doesnt matther (it is a old version of mariadb...) i think we can close bug, and leave here just for information ...
sorry again...
there´s information, but i think it´s (old version 5.3.2) working in a diferent way (isn´t of float it convert to double or to decimal(17,6)... i dont know, just what i checked)
at doc: http://dev.mysql.com/doc/refman/5.5/en/type-conversion.html
*If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value. <<<<<<<--- not my case since one is decimal and other string, right?
*In all other cases, the arguments are compared as floating-point (real) numbers. <<<<<<<<<<----- my case?
Hi Roberto,
Apparently so, if there is no more specific logic for your case described.
Anyway, since it's a MySQL 5.1 legacy issue, and has been fixed in 5.5, can we close the bug report?
yes
just a doubt, in this case, is the "=" being used as a <float-point "=" float-point> instead of a <decimal "=" decimal>?
The effect is not observed in MariaDB or MySQL 5.5, only in 5.1-based versions.
>> just a doubt, in this case, is the "=" being used as a <float-point "=" float-point> instead of a <decimal "=" decimal>?
Roberto,
For a general discussion, it would be more productive for you to use IRC, #mysql or #maria channel, they have a much bigger audience and suit better for talks or questions.
nice thanks!