Improper precision, scale reported by MockSyncStream::get_result_set_struct for queries with decimal/double/float columns

[Ovais Tariq]

So pt-upgrade has this function MockSyncStream::get_result_set_struct which detects the table structure from a mysql statement, its detecting the wrong precision, scale values for double column. MySQL 5.5 supports a precision of 0-65 and scale of 0-30, but this function for the following statement:

SELECT SUM(total) FROM payment_success_log WHERE customers_id = 88430028 AND date >= '2000-01-01 00:00:00'

detects the structure to be:

TABLE `percona`.`mk_upgrade_right` ( `SUM(total)` double(23,31) )

If I try to emulate what the script is doing, by doing it in MySQL as follows:

CREATE TABLE percona.tmp AS SELECT SUM(total) FROM payment_success_log WHERE customers_id = 88430028 AND date >= '2000-01-01 00:00:00';

then it creates a table with the following definition:

Create Table: CREATE TABLE `tmp` ( `SUM(total)` double DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1

See no precision or scale is specified.

Is it necessary to detect the structure from the query, why not let MySQL do that by issuing CREATE TABLE AS SELECT ...

[Daniel Nichter (daniel-nichter)]

I tested on DBD::mysql 4.018 and the result is valid: double(31,23). Which version of DBD::mysql are you using? Run perl -MDBD::mysql -e 'print $DBD::mysql::VERSION, "\n";'

[Daniel Nichter (daniel-nichter)]

The root of this bug is bug 926598, specifically point #1 in comment #2. Therefore, I'm going to mark this bug as a duplicate of the other.