Bug #14518 wrong results using variables
Submitted: 31 Oct 2005 19:33 Modified: 8 Jul 2006 17:25
Reporter: Marek Woch Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.13 OS:Windows (Win98SE)
Assigned to: Alexey Botchkov CPU Architecture:Any

[31 Oct 2005 19:33] Marek Woch
When calculating using variables I'm receiving wrong or strange results with newdecimal type of fields

How to repeat:
select @a:=23.00    'resulting field type 246
select @b:=23.50      ' resulting field type 246
select @a               'resulting field type 253 !!!
select @b-@a        'resulting field type 246 but it gives 0.50000000000000000......

sometimes results (with numbers from real database) give not only such a long results but also are incorrect  ie. "in lieu" of for example 0.50 I find 0.4999999999995 what may be fatal in calculations!
[1 Nov 2005 23:50] Jorge del Conde
Thank you for your bug report.  I was able to reproduce this bug with 5.0.16bk under FC4:

jorge-/my/mysql_code> ./main
Query: SELECT @a:=23.00
Result: 23.00
Field Type: 246

Query: SELECT @b:=23.50
Result: 23.50
Field Type: 246

Query: SELECT @a
Result: 23.00
Field Type: 253

Query: SELECT @b-@a
Result: 0.500000000000000000000000000000
Field Type: 246

[1 Nov 2005 23:51] Jorge del Conde
How to repeat:

#include <stdio.h>
#include <mysql.h>

int do_select(MYSQL *mysql, const char *qry)
  MYSQL_RES *res;
  MYSQL_FIELD *field;
  MYSQL_ROW row;

  if ((mysql_query(mysql, qry) != 0))
    return -1;
    if ((res = mysql_store_result(mysql)))
      printf("Query: %s\n", qry);
      while ((row = mysql_fetch_row(res)))
        field = mysql_fetch_field(res);
        printf ("Result: %s\n", row[0]);
        printf ("Field Type: %d\n\n", field->type);
      return 0;
  return -1;

int main ()
  MYSQL *mysql;

  mysql= mysql_init(NULL);

  do_select(mysql, "SELECT @a:=23.00");  // field->type == MYSQL_TYPE_NEWDECIMAL
  do_select(mysql, "SELECT @b:=23.50");  // field->type == MYSQL_TYPE_NEWDECIMAL
  do_select(mysql, "SELECT @a");         // field->type == MYSQL_TYPE_VAR_STRING
  do_select(mysql, "SELECT @b-@a");      // field->type == MYSQL_TYPE_NEWDECIMAL

  return 0;
[4 Jul 2006 9:00] Alexey Botchkov
Marek, I would really appreciate if you can provide the case with that incorrect result.
[4 Jul 2006 9:01] Alexey Botchkov
i mean 0.49999... as a result of calculations
[4 Jul 2006 9:08] Alexey Botchkov
It's NOT A BUG actually, but i think we should make sure this fact is mentioned in documentation.

The value of user variable is always returned as VARSTRING.
Here is the comment i found in the source code:

    We must always return variables as strings to guard against selects of type
    select @t1:=1,@t1,@t:="hello",@t from foo where (@t1:= t2.b)
[7 Jul 2006 19:12] Paul DuBois
I've noted in the manual that user variables are returned
to clients as strings.
[8 Jul 2006 17:25] Marek Woch
Such a behaviour is not very convenient, but.... It seems to me that the type of a variable should be always the one when was created. In such a way it will be clear and simple:-).

Wrong results disappeared when I recreated table (from old decimal type) with a server version 5.0.18