mysql update is more strict on data type

[ahs10]

i recently updated my mysql server to 5.1.8 and noticed that after i did one of the queries in a php file no longer worked.  the problem is that i stored a value as the decimal data type in my database, but the php had quotes around this value in the query... treating it like a string. 

 

obviously i'm aware of how to fix the issue, but i am wanting to confirm... this was because of the update right?  i swear it worked before.... well, i think i swear it worked before.  any other changes of this nature that anyone is aware of?  my old version was 4.1.22 by the way.

 

any productive input is greatly appreciated, cheers!

[jskywalker]

mysql> desc getal;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| getal | decimal(10,2) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into getal values('10.8');
Query OK, 1 row affected (0.00 sec)

mysql> insert into getal values('10.82');
Query OK, 1 row affected (0.00 sec)

mysql> insert into getal values('10.824');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from getal where getal>=10.8;
+-------+
| getal |
+-------+
| 10.80 |
| 10.82 |
| 10.82 |
+-------+
3 rows in set (0.00 sec)

mysql 5.0.67 on Linux

 

 

mysql> create table getal (getal decimal(10,2));
Query OK, 0 rows affected (0.05 sec)

mysql> insert getal values('10.8');
Query OK, 1 row affected (0.01 sec)

mysql> insert getal values('10.82');
Query OK, 1 row affected (0.00 sec)

mysql> insert getal values('10.824');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from getal;
+-------+
| getal |
+-------+
| 10.80 |
| 10.82 |
| 10.82 |
+-------+
3 rows in set (0.00 sec)

mysql 5.1.33 on WindowsXP

 

[PFMaBiSmAd]

The DECIMAL datatype was changed a couple of times in gong from mysql 4.1 to the current version. However, just putting quotes around the value in the query string should not trigger any problem (other than that an extra handling that is necessary because a quoted number is first converted to a float before it is used.)

 

Since you did not state exactly what problem, error, or unexpected result you are getting, it is not directly possible to help you. There are however a section in each version of the documentation that describes what changes were made to the DECIMAL datatype.

[ahs10]

thanks for the response.  there is no error, searching for the an integer in quotes yields no results.  searching for the same value without quotes gets a result.  so if.....

just putting quotes around the value in the query string should not trigger any problem

... what's wrong here?  thanks again for your input and help, greatly appreciated.  cheers!

 

mysql> desc db.table;
+--------------------------+---------------------------------------------------------------------------------------------------------------------+------+-----+-----------------------+----------------+
| Field                    | Type                                                                                                                | Null | Key | Default               | Extra          |
+--------------------------+---------------------------------------------------------------------------------------------------------------------+------+-----+-----------------------+----------------+
| temp_id                  | decimal(65,0)                                                                                                       | YES  |     | NULL                  |                | 
+--------------------------+---------------------------------------------------------------------------------------------------------------------+------+-----+-----------------------+----------------+
1 row in set (0.00 sec)

mysql> select temp_id from db.table where temp_id = 1234567890123456789;
+---------------------+
| temp_id             |
+---------------------+
| 1234567890123456789 | 
+---------------------+
1 row in set (0.05 sec)

mysql> select temp_id from db.table where temp_id = '1234567890123456789';
Empty set (0.04 sec)

 

 

[jskywalker]

mysql> explain select * from temp where temp_id = '1234567890123456789';
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.01 sec)

[ahs10]

thanks for the explain query.  got that.  so my question was... this was caused by the update?  i'm trying to verify that it could have worked before and doesn't work now because of the update.

 

i forgot in my last reply too... i'm aware of the change logs too.  to be quite honest, if every question asked in this forum was removed if the answer could be found on mysql.com... there wouldn't be a forum at all.  that's the point of these forums... to be the number one resource, right?  i'm not asking a ridiculous question and my title contains keywords to someone in the future could find an answer to the same problem... without wading through change logs.  imho, you shouldn't post an answer if the only answer you can provide is that the answer is somewhere is else.  that's retarded, no matter how knowledgeable you are.  just saying.....

[jskywalker]

basically:

 

* if it worked before the update (and you are claiming it did!) than, YES it's cause through the update

* MySQL4.x is too old, so i'm not going to check for you if this worked in 4.x....

* If you know how to fix it in the 'current' version of MySQL, please do so, and stop wondering why it's suddenly broken.

* i dont see what  you are getting, when you will know its caused through differences in 4.x -> 5.1.x (sorry, my fault i dont see that )

* be aware that things might change again when updating to 6.x (not sure, but there IS a possibility.....

[ahs10]

let me sum up your reply for you.... "i have no clue".  quite a lot of bullet points just to say that and a waste of a reply, again imho.

 

i didn't state i knew it worked before the update... in fact i stated that i wasn't sure at all.

 

i can't believe i'm having to defend this question.  it's clear to me this forum attracts people who feel they need to belittle the person posting if they don't know an answer off the top of their head.  how about just not posting a reply?

 

if you can't see the value in knowing whether current code worked before an update, then mysql isn't the only area you have an extreme lack of knowledge there killer =)

[Mchl]

As MySQL 4 is not supported any longer even by its authors, why should anyone here bother? It's just good for you, that you decided to upgrade, because getting any help for MySQL 4.x will be tougher and tougher (I tend to ignore all questions regarding these versions - I just don't feel like playing documentation archeologist).

 

The issue you're experiencing is most likely result of this:

MySQL 5.1 stores DECIMAL and NUMERIC values in binary format. Before MySQL 5.0.3, they were stored as strings.

That's just guessing though...

 

For your information, TIMESTAMP handling also changed a lot. Now it's actually useful.

[jskywalker]

let me sum up your reply for you....

 

i'm so sorry i even TRIED to help you........