MySQL Bugs: #12840: libMySQL.dll reports wrong MYSQL

Bug #12840	libMySQL.dll reports wrong MYSQL_FIELD details
Submitted:	26 Aug 2005 18:59	Modified:	17 Jan 2006 21:04
Reporter:	Bugs System	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	5.0.11-beta, 5.0.13-BK	OS:	Windows (Windows, Linux)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
mysql.exe:
mysql> SELECT Date('0000-00-00') * 1 AS T;
+------+
| T    |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

But if I execute this Query via libMySQL.dll I receive the following values:

MYSQL_FIELD.Lengths = 27
MYSQL_FIELD.Decimals = 31

... I don't know if the mysql.exe shows a wrong result or if the libMySQL.dll reports wrong values. But since both results are different for the same request I think one of both is wrong...

How to repeat:
SELECT Date('0000-00-00') * 1 AS T;

MYSQL_FIELD.field_type = MYSQL_TYPE_DOUBLE

Could you provide a small reproducing case? (Would be nice)

I think my help based on reporting bugs. I'm not interessed to fix them for you. (Locating and reproducing a bug is a part of fixing I think.)

Please let me know if I should stop reporting bugs while I'm not interessed to fix them?

I said that it would be nice because it will save us time -> more time on the real bugfix (locating the cause and fixing the problem).

Well, indeed it's nice for you if I would make your job. But I have my own job. 

It would be nice if you would make a part of my job, will you? Also it would be nice if you would send some money to me, will you?

... now I'm exited how you will handle my questions - my questions to you are the same like your questions to me! ;-)

Sorry, but I have to remind you a quote from http://bugs.mysql.com/how-to-report.php:

"The basics: what you did, what you wanted to happen, and what actually happened.

Those are the three basic elements of a bug report. You need to tell us exactly what you did (...), what you expected to have happen (...), and what actually happened (...)."

I this case you gave only partial description of what you did, and told nothing about what you expected (what details you expected to see in MYSQL_FIELD? MYSQL_TYPE_<what> as a type value?). That is why more information is requested.

In your "test case" you want to determine the field properties of the result of ("zero") date multiplied by integer constant 1 (strange, but possible case), right? mysql.exe gives you a correct result: date was implicitely casted to decimal 0 (0.00). Do you think that MYSQL_TYPE_DOUBLE is inappropriate? Why? (I was unable to found any explicit reference in the manual on what the type of such a calculated field be).

P.S. We do our job according to the standard operation procedures. If you want to be treated differently, you need a support contract.

I'm not longer interessted to help you fixing this bug.

It's your decision if you are interessed to locate and fix this existing bug or not...

After I learned you are not interessed fixing bugs I will only report bugs if I'm interessed to get a fix. In this case I don't need a fix - I know how I can handle this bug.

A test case created by andrey (with 2 more fields). Are datatypes reported correctly?

Attachment: 12840.c (text/x-csrc), 1.22 KiB.

This is an improved. Shows that by using the normal (non-PS) API the result is string but reported as float.

Attachment: bug12840.c (text/x-csrc), 1.76 KiB.

andrey@lmy004:/work/test> ./bug12840
type=5
Result=[1000]
Result=[1000.00]
[1000]
[len=4][0.00]

Test case 1 is added (12840.c).

It returns:

type=5
length=23 -- (NOT 27 as stated in the original bug report)
decimals=31
type=246
length=6
decimals=2
type=253
length=23
decimals=31

mysql gives the following results:

mysql> select cast(date('0000-00-00') as decimal);
+-------------------------------------+
| cast(date('0000-00-00') as decimal) |
+-------------------------------------+
|                                0.00 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> select date('0000-00-00')*1 as t, 0.00 * 1 as p, cast(date('0000-00-00')
* 1 as binary) as t2;
+------+------+------+
| t    | p    | t2   |
+------+------+------+
|    0 | 0.00 | 0    |
+------+------+------+
1 row in set (0.00 sec)

The question here is: why do we get MYSQL_TYPE_DOUBLE (5) as a type using prepared statements? Why not 253 (MYSQL_TYPE_VAR_STRING), for example? Where is it documented?

And test case 2 (bug12840.c) shows that by using the normal (non-PS) API the result is string but reported as float.

@Valeriy:

> length=23 -- (NOT 27 as stated in the original bug report)

I confirm my mistake. Sorry about this.

The developers are not interessed about our bug messages.

If types of the arguments mismatch, MySQL uses double arithmetics to evaluate the result:
http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html:

 MySQL compares values using the following rules:

    * If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe <=> equality comparison operator.
    * If both arguments in a comparison operation are strings, they are compared as strings.
    * If both arguments are integers, they are compared as integers.
    * Hexadecimal values are treated as binary strings if not compared to a number.
    * If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. Note that this is not done for arguments in IN()! To be safe, always use complete datetime/date/time strings when doing comparisons.
    * In all other cases, the arguments are compared as floating-point (real) numbers.

Datetime types do not have native support in the expression arithmetic. This is why the returned expression type is DOUBLE: first the DATE is converted to string, then, as types of the arguments mismatch, both of them are converted to DOUBLE. Then the expression is evaluated.