Support MYSQL_TYPE_NEWDECIMAL

thecheatah commented 4 years ago

Adds support for MYSQL_TYPE_NEWDECIMAL conversion on MySQLData (#19, fixes #18).

tanner0101 commented 4 years ago

newdecimal appears to be a length encoded string as documented here: https://mariadb.com/kb/en/resultset-row/#decimal-binary-encoding From observation a 0 is represented as a nil buffer. 1 is represented as the string 1. From the documentation above, numbers can also be 123.456.

From: https://github.com/vapor/mysql-nio/issues/18

DECIMAL has no fixed size, so will be encoded as string. An DECIMAL(10,2) with a value of -15.5 is stored as

From: https://mariadb.com/kb/en/resultset-row/#decimal-binary-encoding

I don't see any mention here that NULL should be interpreted as 0. Additionally, MySQL's result set specifies whether each field is null or not using a separate bitmap. In other words, calculating null and length-encoded strings are separate from each other.

I ran the queries from this test in TablePlus and the client there gives back NULL for the SUM column which is what I would expect. There are no columns to aggregate so it's not possible to derive a sum.

Screen Shot 2020-03-19 at 10 56 57 AM

thecheatah commented 4 years ago

@tanner0101 I deleted my previous two comments. I had mixed count with sum that's why I was getting different results. You might be right where if a new decimal has a nil buffer it might just be a nil. I have to try to get an example of a 0 represented as a new decimal

thecheatah commented 4 years ago

@tanner0101, I have rolled back the changes that mapped the empty buffer to a nil. I have kept the remaining decoding changes for newdecimal. I have also added tests for sum with and without rows.

Example encoding of 1 as a new decimal

(lldb) po self
▿ <MYSQL_TYPE_NEWDECIMAL>
  ▿ type : MYSQL_TYPE_NEWDECIMAL
    - rawValue : 246
  - format : MySQLNIO.MySQLData.Format.binary
  ▿ buffer : Optional<ByteBuffer>
    ▿ some : ByteBuffer { readerIndex: 0, writerIndex: 1, readableBytes: 1, capacity: 1, slice: _ByteBufferSlice { 7..<8 }, storage: 0x00000001018d8600 (32768 bytes) }
      ▿ _storage : <_Storage: 0x100cad320>
      - _readerIndex : 0
      - _writerIndex : 1
      ▿ _slice : _ByteBufferSlice { 7..<8 }
        - upperBound : 8
        ▿ _begin : 7
          - b12 : 7
          - b3 : 0
  - isUnsigned : false

(lldb) po buffer.readString(length: buffer.readableBytes)
▿ Optional<String>
  - some : "1"

tanner0101 commented 4 years ago

These changes are now available in 1.0.0-rc.1.1

vapor / mysql-nio

Support MYSQL_TYPE_NEWDECIMAL #19