MySQL Bugs: #16257: Decimal data type causing problems in 5.0.18

Bug #16257	Decimal data type causing problems in 5.0.18
Submitted:	6 Jan 2006 16:12	Modified:	23 Mar 2006 13:24
Reporter:	Tony Cise	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51.11	OS:	Windows (XP SP2)
Assigned to:		CPU Architecture:	Any

Description:
I recently upgraded from 4.1.16 o 5.0.18. I then imported some tables from MS/ACCESS. One of the tables resulted in a column being defined as DECIMAL.
When trying to do a "SELECT * FROM accountstbl" in ASP/VBScript, there were no rows returned (though it contains 259). If I selected an individual column (not the decimal one), the query worked. I repeated the queries in DBmanager and also had the same problem. I then tried to look at the table DDL in DBManager pro and it crashed. After some trial and error attempts I changed the column type from DECIMAL to FLOAT and the "SELECT * FROM accountstbl" suddenly started to produce the expected results set. I then created a new MYSQL table and defined a column as decimal and put some data in the table. I had exactly the same problems. This proved it was not an import from DAO error.

I have currently gone back to the 4.1.16 version of MySql.

How to repeat:
As above.

Thank you for a problem report. Are you able to repeat the problem with mysql command line client from 5.0.18?

Please, describe also all steps you perfromed to upgrade.

I re-installed v5 and the Select * worked OK from the command line producing a result set .
I then tried it again in ASP/Vbscript and 0 rows were returned. Also checked the DDL in DBmanager and it crashed again.

Thank you for the additional test. It looks like a MyODBC-related problem then. Please, cpecify the exact version of the ODBC driver used.

(MySQL ODBC 3.51 Driver) Version 3.51.11.00

Have you made any progress with this yet ?

Try the latest ODBC 3.51.12  ... it is supposed to work.

I had a similar problem with  decimal(14,4) when I tried sum() and  average functions when I went from 4.1.15 to 5.0.15.   Went back to 4.1.15 and will wait till MySQL sorts this problem out.

The problem is that it returns it as a character field (enclosed in single quotes) instead of a numeric field, crashing many Windows applications.

I installed the new driver and my ASP page works fine.
DBManager still crashes in Windows.
However, this is good progress !
Thank you

#Creation script
CREATE TABLE test (
ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
SomeDecValue DECIMAL(10,2),
SomeText VARCHAR(10));

INSERT INTO test VALUES (NULL, 10.2, "Should be 1"),(NULL, 20.20, "Should be 2"),
(NULL, 5/2, "Should be 3!"),(NULL, 10/3, "Should be 4!"),(NULL, 30.30, "Should be 5");

server log:
060323 14:06:37	      1 Query       update `test`  set
 `SomeDecValue` = 50.5
where
 `ID` = 5 and
 `SomeDecValue` = 30.3 and
 `SomeText` = 'Should be '
060323 14:06:38	      1 Query       COMMIT
060323 14:07:12	      1 Query       SET AUTOCOMMIT=1
		      1 Query       INSERT INTO test VALUES (NULL, 10/3, "Should be 6")
060323 14:07:14	      1 Query       USE test
060323 14:09:03	      1 Query       SELECT * FROM test
060323 14:09:07	      1 Query       SELECT * FROM test
060323 14:09:12	      1 Query       SHOW COLUMNS FROM test
060323 14:09:32	      1 Query       SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
		      1 Query       SET AUTOCOMMIT=0
060323 14:09:34	      1 Query       update `test`  set
 `SomeDecValue` = 50.25
where
 `ID` = 5 and
 `SomeDecValue` = 50.5 and
 `SomeText` = 'Should be '
060323 14:09:35	      1 Query       COMMIT

Reported behaviour can not be repeated from several ODBC clients.

ODBC client screen shot

Attachment: 16257.jpg (image/jpeg, text), 52.10 KiB.

Version number of this bug report was wrong. I fixed it. It's an MyODBC 3.51.11 bug report.