Why has this SQLInsert behaviour changed?



Using Mathematica 11.1 to insert into a MySQL table with this schema...

example    Null         indexkey    Record       4          MEDIUMINT UNSIGNED
example    Null         indexkey    ID           -5         BIGINT

produces the following:

SQLInsert[conn, "indexkey", {"record", "id"}, {6631, 20042069300}];

SQLExecute[conn, "select * from indexkey where record = 6631"]

{{6631, -1432767180}}

However using Mathematica 7.0.1 this is the result:

{{6631, 20042069300}}

To get the same result in Mathematica 11.1 requires ToString, e.g.

SQLInsert[conn, "indexkey", {"record", "id"}, {6631, ToString[20042069300]}];

Is this a bug?

Chris Degnen

Posted 2017-03-28T12:05:41.957

Reputation: 27 033

A clue may be the following truth: 1432767179 == FromDigits[Drop[IntegerDigits[20042069300, 2], 4] /. {1 -> 0, 0 -> 1}, 2]. Looks like only the 32 least significant bits are used of your input. The -1432767179 is the two's complement interpretation of the remaining bit pattern. – Sjoerd C. de Vries – 2017-03-28T19:38:57.303

So, the next questions would be: Are you accessing the same database in both situations? If not, are you sure you defined the 11.1 ID as BIGINT? If this does not answer it, you might try another driver. According to the documentation 11.1 comes with 3 MySQL drivers. You could try a different one. – Sjoerd C. de Vries – 2017-03-28T20:03:03.917

@SjoerdC.deVries Yes, it's the same database. Everything's the same except the Matematica version. It looks like a bug to me. – Chris Degnen – 2017-03-28T20:14:44.003

Mathematica has these three connections: JDBCDrivers["MySQL(Connector/J)"], JDBCDrivers["MySQL(Drizzle)"],
JDBCDrivers["MySQL(MariaDB)"]. Which one are you using, and did you try one of the others?
– Sjoerd C. de Vries – 2017-03-28T20:17:38.947

What does SQLColumns[conn,"key"] return? – Edmund – 2017-03-29T02:31:07.850

@Edmund The table is actually called indexkey: {SQLColumn[{"indexkey", "Record"}, "DataTypeName" -> "MEDIUMINT UNSIGNED", "Nullable" -> 0, "DataLength" -> 8], SQLColumn[{"indexkey", "ID"}, "DataTypeName" -> "BIGINT", "Nullable" -> 0, "DataLength" -> 19]} – Chris Degnen – 2017-03-29T08:14:26.833



It looks like a bug to me.

Any input larger than 2^31 -1 is truncated to 32 bits, whereas a BIGINT should be able to hold 64 bit numbers. What you see is the interpretation of that remainder in two's complement.

I've checked this for all three available drivers in V11.1 (Connector/J, Drizzle, and MariaDB), so a driver issue seems unlikely.

Furthermore, if you create a MySQL syntax error on purpose, e.g. by using a keyword (KEY) as table name, as I do below, the following error message is returned:

SQLInsert[conn, "key", {"record", "id"}, {6631, 20042069300}];

JDBC::error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near

'key (record,id) VALUES (6631,-1432767180)' at line 1

So, it looks like the code generated by DatabaseLink already contained the wrong number before it was send to MySQL.

The fact that a raw SQLExecute works correctly supports the hypothesis that the issue lies with SQLInsert

SQLExecute[conn, "
 insert into aap (record,id)
 VALUES (6631, 20042069300)"]
SQLExecute[conn, "select * from aap where record = 6631"]

{{6631, 20042069300}}

I'd suggest reporting this to support@wolfram.com

Sjoerd C. de Vries

Posted 2017-03-28T12:05:41.957

Reputation: 63 549

The table name wasn't 'key' in the original instance. – Chris Degnen – 2017-03-28T21:26:15.090

@chris Yes, but that's not what I'm saying. I used a different name in my testing (i.e., in the creation of the table), but using 'key' in the subsequent query leads to a syntax error, the message of which reveals the location of the problem. – Sjoerd C. de Vries – 2017-03-28T21:30:59.717

The table I created was made with the following code: SQLCreateTable[conndrizzle, "aap", { SQLColumn["Record", "DataTypeName" -> "MEDIUMINT"], SQLColumn["ID", "DataTypeName" -> "BIGINT"] }] – Sjoerd C. de Vries – 2017-03-28T21:34:34.017

Reported to support@wolfram.com – Chris Degnen – 2017-03-29T11:20:30.187

@chris Small update to show that SQLInsert is the culprit. – Sjoerd C. de Vries – 2017-03-29T19:46:36.627