maria-discuss team mailing list archive

[Oleg Tsarev <zabivator@xxxxxxxxx>]

Hello,

I don't understand how right work with decimal type.
As you talk, i change column types from CHAR(...) to DECIMAL in my patch:
http://www.percona.com/docs/wiki/patches:response_time_distribution

Now i have trouble with decimal precision/scale.

Here is my definition of system table INFORMATION_SCHEMA.QUERY_RESPONSE_TIME:

ST_FIELD_INFO query_response_time_fields_info[] =
  {
    {"TIME",  MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0,
MY_I_S_UNSIGNED, "time",  SKIP_OPEN_TABLE },
    {"COUNT", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG,     0,
MY_I_S_UNSIGNED, "count", SKIP_OPEN_TABLE },
    {"TOTAL", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0,
MY_I_S_UNSIGNED, "total", SKIP_OPEN_TABLE },
    {0,       0,                           MYSQL_TYPE_STRING,   0, 0,
             0,       SKIP_OPEN_TABLE }
  };

So, i want use DECIMAL(20,6) for columns time and total, and
DECIMAL(14,0) for columns count.

1) Does above definiton correct, or should include some information
about precision/scale?

I collect time of query execution in:
1) Column total - in int64, microseconds count
2) Column count - in int64, just count

Here is source code, for fill this table:
    for(int i= 0, count= m_utility.bound_count() + 1 /* with overflow
*/; count > i; ++i)
    {
      const uint64 time  = m_utility.bound(i);
      const int64 count = m_time.count(i);
      const int64 all   = m_time.all(i);
      my_decimal time_d;
      my_decimal count_d;
      my_decimal all_d;
      my_decimal million_d;
      my_decimal temp_d;
      ulonglong2decimal(MILLION,&million_d);

      ulonglong2decimal(time, &temp_d);
      my_decimal_div(E_DEC_OK,&time_d,&temp_d,&million_d,SCALE);

      longlong2decimal(count,&count_d);

      longlong2decimal(all, &temp_d);
      my_decimal_div(E_DEC_OK,&all_d,&temp_d,&million_d,SCALE);

      fields[0]->store_decimal(&time_d);
      fields[1]->store_decimal(&count_d);
      fields[2]->store_decimal(&all_d);

      if (schema_table_store_record(thd, table))
      {
        DBUG_RETURN(1);
      }
    }

I want store some value to decimal type, than divide by MILLION:
microsecond_count => convert to decimal => / 1000000 = result.

But as result i get:
SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME;
TIME    COUNT   TOTAL
0       0       0
0       0       0
0       1       0
0       0       0
0       1       0
0       0       0
1       11      4
10      8       13
100     0       0
1000    0       0
10000   0       0
......................

Instead of wanted:
SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME;
TIME    COUNT   TOTAL
0.000001       0       0
0.00001        0       0
0.0001         1       0
0.001       0       0
0.01       1       0
0.1       0       0
1       11      4
10      8       13
100     0       0
1000    0       0
10000   0       0
......................

2) What i do incorrect? How fix it?

Best regards, Oleg