MySQL Bugs: #14518: wrong results using variables

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:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.13	OS:	Windows (Win98SE)
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
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!

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

jorge-/my/mysql_code>

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;
  else
    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);
      }
      mysql_free_result(res);
      return 0;
    }
  return -1;
}

int main ()
{
  MYSQL *mysql;

  mysql= mysql_init(NULL);
  mysql_real_connect(mysql,"localhost","root",NULL,"test",0,NULL,0);

  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

  mysql_close(mysql);
  return 0;
}

Marek, I would really appreciate if you can provide the case with that incorrect result.

i mean 0.49999... as a result of calculations

It's NOT A BUG actually, but i think we should make sure this fact is mentioned in documentation.

So.
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)
  */

I've noted in the manual that user variables are returned
to clients as strings.

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