BigDecimal returned from SELECT on decimal column results in value 0 on INSERT

[lhz]

I have a script to copy rows from a table in one database to an identical table in another. The table has 4 columns, one of which is of type decimal(15, 0).

The code to copy a batch of rows looks like this:

  select = @db1.prepare('SELECT * FROM my_table WHERE id BETWEEN ? AND ? LIMIT ?')
  insert = @db2.prepare('INSERT INTO my_table VALUES (?, ?, ?, ?)')
  select.execute(min_id, max_id, batch_size).each do |row|
    insert.execute *row.values
  end

The value of the decimal column will be returned as a BigDecimal object. When including this object in the arguments passed to exectue on the prepared insert statement, this always results in the value 0 for that column in the row inserted, regardless of the actual value.

I can circumvent the issue by casting the BigDecimal to Integer before insertion (or by changing the column type to integer, but I don't have the luxury of changing the schema in this case). Still, it seems contraintuitive that you can not directly insert the same value you got out of a select on the same column type.

[sodabrew]

May be resolved by #563, where the SELECT is returning an Integer because there are zero decimal places. On the subsequent INSERT the data is getting lost.

[mperrando]

I have the same issue while importing data form another database.

Take a look at #722.

[casrep]

I believe I'm experiencing this issue as well.

For example, a value stored in the decimal(65,2) column of one of my rows contains a value of 7.20. When I query the column of that value's row, I get a return of 0.72E1.

[sodabrew]

Please try mysql2 gem version 0.4.3 and let me know if this is resolved?

[casrep]

I apologize for the delay. I am still seeing this issue. I uninstalled 0.4.2 and specifically installed 0.4.3.

This is the object I'm receiving from the decimal() column's value: #<BigDecimal:257b910,'0.72E1',18(18)>

[sodabrew]

That data looks suspiciously invalid, doesn't it.

[mperrando]

@casrep what issue are you talking about? It seems we are loosing the focus.

This issue is about the problem that performing an INSERT with a BigDecimal parameter results in inserting a 0 into the target table, and this was happening because there where no code in the library to treat the BigDecimal in INSERT statement (issue resolved in 0.4.3, see #722).

You are instead complaining about the fact that a column with a value of 7.20 is read into a BigInteger having a value of 0.72E1. Let me allow to notice that 7.20 and 0.72E1 are exactly the same number (@sodabrew what seems suspicious?), and that, as far as I can see, this behavior is directly related to the implementation of the BigInteger class, and not to the mysql2 library, see below

2.2.3 :006 > n = BigDecimal.new(7.20, 2)
 => #<BigDecimal:7fc54b2d5cb8,'0.72E1',18(27)>
2.2.3 :007 > puts n
0.72E1

Thus I can't understand what is bothering you!
Have you tried to do an INSERT with this BigInteger value into a target table and verify what value is inserted into the database? Is it zero? In the case, please post the snippet of code, and specify exactly what kind of databases (source and target) and version of ruby you are using, please.

[sodabrew]

Thank you for the larger explanation, I misread the followup. @lhz Thanks again for reporting the issue and @mperrando for providing a PR to fix it. @lhz could you also confirm that this works for you now?