Bug with JSON columns on >=1.15.0

[ryespresso]

If a column has this data:

["1", "5", "11155111", "42161", "42170", "43114", "8453", "56", "7700", "42220", "250", "10", "137", "1101", "59144", "1284", "1285", "534352", "7777777"]

And you upgrade to or past 1.15.0

You get this in return instead:

{"0":91,"1":34,"2":49,"3":34,"4":44,"5":32,"6":34,"7":52,"8":50,"9":49,"10":54,"11":49,"12":34,"13":44,"14":32,"15":34,"16":52,"17":50,"18":49,"19":55,"20":48,"21":34,"22":44,"23":32,"24":34,"25":52,"26":51,"27":49,"28":49,"29":52,"30":34,"31":44,"32":32,"33":34,"34":56,"35":52,"36":53,"37":51,"38":34,"39":44,"40":32,"41":34,"42":53,"43":54,"44":34,"45":44,"46":32,"47":34,"48":52,"49":50,"50":50,"51":50,"52":48,"53":34,"54":44,"55":32,"56":34,"57":50,"58":53,"59":48,"60":34,"61":44,"62":32,"63":34,"64":49,"65":48,"66":48,"67":34,"68":44,"69":32,"70":34,"71":49,"72":48,"73":34,"74":44,"75":32,"76":34,"77":49,"78":51,"79":55,"80":34,"81":44,"82":32,"83":34,"84":49,"85":49,"86":48,"87":49,"88":34,"89":44,"90":32,"91":34,"92":53,"93":57,"94":49,"95":52,"96":52,"97":34,"98":44,"99":32,"100":34,"101":49,"102":50,"103":56,"104":52,"105":34,"106":44,"107":32,"108":34,"109":49,"110":50,"111":56,"112":53,"113":34,"114":44,"115":32,"116":34,"117":53,"118":51,"119":52,"120":51,"121":53,"122":50,"123":34,"124":44,"125":32,"126":34,"127":55,"128":55,"129":55,"130":55,"131":55,"132":55,"133":55,"134":34,"135":93}

Possibly introduced in #164 @mattrobenolt @ayrton ?

[mattrobenolt]

Can you post the schema? But that serialization you're seeing is just a Uint8Array of the binary data, so something about the schema is causing us to treat it as raw binary.

[ryespresso]

@mattrobenolt - This is specifically happening when reading the column in a View

CREATE TABLE `sample` (
  `address` varchar(66) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `chains` json NOT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

[mattrobenolt]

And I'm guessing the column you're referring to is chains?

[ryespresso]

That is correct @mattrobenolt

[ryespresso]

Any updates here? @mattrobenolt
Pretty blocked on upgrading

[ayrton]

No update yet but it's on our radar to triage. In the meantime it is safe to pin to 1.14 (unless you're app specifically works with binary data).

We'll follow up on this issue once we know more.

[ryespresso]

@ayrton pinned at 1.14 for the time being. Let me know if you need any other information.

[ayrton]

@ryespresso I've setup a test database and a little test app and cannot reproduce your issue.

import 'dotenv/config'
import { Client } from '@planetscale/database'

const config = {
  host: process.env.DATABASE_HOST,
  username: process.env.DATABASE_USERNAME,
  password: process.env.DATABASE_PASSWORD
}

async function mainPlanetscale() {
  const client = new Client(config)
  const result = await client.execute('SELECT * FROM sample LIMIT 1')

  console.log(result, result.rows[0])
}

mainPlanetscale().catch((e) => {
  console.error(e)
  process.exit(1)
})

1.14.0:

$ node app.mjs
{
  headers: [ 'address', 'chains', 'updated_at' ],
  types: { address: 'VARCHAR', chains: 'JSON', updated_at: 'DATETIME' },
  fields: [
    {
      name: 'address',
      type: 'VARCHAR',
      table: 'sample',
      orgTable: 'sample',
      database: 'database-js',
      orgName: 'address',
      columnLength: 264,
      charset: 255,
      flags: 20483
    },
    {
      name: 'chains',
      type: 'JSON',
      table: 'sample',
      orgTable: 'sample',
      database: 'database-js',
      orgName: 'chains',
      columnLength: 4294967295,
      charset: 63,
      flags: 4241
    },
    {
      name: 'updated_at',
      type: 'DATETIME',
      table: 'sample',
      orgTable: 'sample',
      database: 'database-js',
      orgName: 'updated_at',
      columnLength: 19,
      charset: 63,
      flags: 128
    }
  ],
  rows: [
    {
      address: 'Gold St.',
      chains: [Array],
      updated_at: '2024-04-05 14:54:54'
    }
  ],
  rowsAffected: 0,
  insertId: '0',
  size: 1,
  statement: 'SELECT * FROM sample LIMIT 1',
  time: 2.4092599999999997
} {
  address: 'Gold St.',
  chains: [
    '1',       '5',     '11155111',
    '42161',   '42170', '43114',
    '8453',    '56',    '7700',
    '42220',   '250',   '10',
    '137',     '1101',  '59144',
    '1284',    '1285',  '534352',
    '7777777'
  ],
  updated_at: '2024-04-05 14:54:54'
}

1.16.0:

$ node app.mjs
{
  headers: [ 'address', 'chains', 'updated_at' ],
  types: { address: 'VARCHAR', chains: 'JSON', updated_at: 'DATETIME' },
  fields: [
    {
      name: 'address',
      type: 'VARCHAR',
      table: 'sample',
      orgTable: 'sample',
      database: 'database-js',
      orgName: 'address',
      columnLength: 264,
      charset: 255,
      flags: 20483
    },
    {
      name: 'chains',
      type: 'JSON',
      table: 'sample',
      orgTable: 'sample',
      database: 'database-js',
      orgName: 'chains',
      columnLength: 4294967295,
      charset: 63,
      flags: 4241
    },
    {
      name: 'updated_at',
      type: 'DATETIME',
      table: 'sample',
      orgTable: 'sample',
      database: 'database-js',
      orgName: 'updated_at',
      columnLength: 19,
      charset: 63,
      flags: 128
    }
  ],
  rows: [
    {
      address: 'Gold St.',
      chains: [Array],
      updated_at: '2024-04-05 14:54:54'
    }
  ],
  rowsAffected: 0,
  insertId: '0',
  size: 1,
  statement: 'SELECT * FROM sample LIMIT 1',
  time: 1.496781
} {
  address: 'Gold St.',
  chains: [
    '1',       '5',     '11155111',
    '42161',   '42170', '43114',
    '8453',    '56',    '7700',
    '42220',   '250',   '10',
    '137',     '1101',  '59144',
    '1284',    '1285',  '534352',
    '7777777'
  ],
  updated_at: '2024-04-05 14:54:54'
}

The output of 1.14.0 and 1.16.0 is identical with the exception of result.time

[ryespresso]

@ayrton This is specifically happening when querying this column from a View. Let me know if you can't replicate and I will build a reproducible when I get a chance.

[ayrton]

@ryespresso that'd be helpful. Currently I cannot reproduce see #171 (comment) above.

[ryespresso]

@ayrton It only happens when your view creates the JSON and you query the view. Will try and find time to make a reproducible.

[mattrobenolt]

I strongly suspect this was fixed with #174, but if it doesn't, we're in a good position to easily write a test to cover this if we have a query to reproduce exactly.