MySQL Bugs: #13515: Ms Visual Foxpro read 0.0 AS character field rather then decimal

Bug #13515	Ms Visual Foxpro read 0.0 AS character field rather then decimal
Submitted:	27 Sep 2005 5:42	Modified:	30 Sep 2005 17:53
Reporter:	bullish168 bullish168	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.12-nt, 5.0.13	OS:	Windows (WIN XP)
Assigned to:		CPU Architecture:	Any

Description:
I currenly tested the following syntax in both mysql4 and mysql5

SELECT 0.0 AS zero 

In ver4.1, the Visual FoxPro correctly read the data as decimal  throught Mysql   ODBC.

But in ver5, VFP read the data as character.

I test in Sqlyog, I found mySql4 present the decimal data in right Alignment format, same as int data type, while in the mySql5, the data is presented in left Alignment format same as text data type.

Hope MySql develop team will fix the problem.  Because there are a lot of VFP developer use Mysql as their backend database server.

Thanks and regards.

How to repeat:
SELECT 0.0 AS zero

Yes, presentation in 5.0.x differs, even in mysql client, so it may even has nothing to do with ODBC:

mysql> select version();
+----------------+
| version()      |
+----------------+
| 5.0.12-beta-nt |
+----------------+
1 row in set (0.02 sec)

mysql> select 0.0 as zero_field;
+------------+
| zero_field |
+------------+
| 0.0        |
+------------+
1 row in set (0.00 sec)

mysql> select cast(0.0 as decimal) as zero_field;
+------------+
| zero_field |
+------------+
| 0.00       |
+------------+
1 row in set (0.00 sec)

mysql> exit
Bye

D:\Documents and Settings\openxs>mysql -uroot -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.14-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select 0.0 as zero_field;
+------------+
| zero_field |
+------------+
|        0.0 |
+------------+
1 row in set (0.00 sec)

Moreover, type of "0.0" really changed, as shown below:

mysql> create table t3 as select 0.0 as zero_field;
Query OK, 1 row affected (0.09 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc t3;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| zero_field | double(3,1) |      |     | 0.0     |       |
+------------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.14-nt |
+-----------+
1 row in set (0.00 sec)

mysql> exit
Bye

D:\Documents and Settings\openxs>mysql -uroot -p -P3307
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.12-beta-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Database changed
mysql> create table t3 as select 0.0 as zero_field;
Query OK, 1 row affected (0.08 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc t3;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| zero_field | decimal(2,1) | NO   |     | 0.0     |       |
+------------+--------------+------+-----+---------+-------+
1 row in set (0.03 sec)

I've found no explicit description of this behaviour in documentation. So, it is at least a documentation request, as I marked it.

a duplicate of bug#13557

I think that the odbc driver is responsible for the problem.

Actually even if you do a select 0 as tst (from vfp - visual foxpro)  the resulting field is of character type. Even though from a mysql front end the field is numeric. 

I've tried with MySql 5.21a an MyOdbc (2.50, 3.51.11, 3.51.12) and the result is the same... If the same sql command is executed with another known sql server the field is numeric (that's what it should be).  Is there a different place where we should report this thing ? Perhaps to the people that have created the MyOdbc driver???