Geometry binary data appears to be incorrect value

[dylel]

For some context i'm trying to interface with drizzle orm. I'm trying to parse the response for a geometry field that I'm receiving from the driver, i'm deliberately not using ST_AsBinary() as the will be how it will end up getting called by drizzle. My understanding is that MySql should return the SRID + WKB as the default response when not providing ST_AsText, or ST_AsBinary

The geometry in my database: LINESTRING(0 0,1 1,2 2), 4326

db.Execute('SELECT Top(1) geom from polys')

The response from the driver:

\u0010\u0000\u0000\u0001\u0002\u0000\u0000\u0000\u0003\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000\u0000 ?\u0000\u0000\u0000\u0000\u0000\u0000 ?\u0000\u0000\u0000\u0000\u0000\u0000\u0000@\u0000\u0000\u0000\u0000\u0000\u0000\u0000@

I'm then reading it into a buffer and slicing off the first 4 bytes for the SRID. However doing so produces a marginally different output than expected.

I'm expecting (from MySqlWorkBench):

01020000000300000000000000000000000000000000000000000000000000F03F000000000000F03F00000000000000400000000000000040

Receiving (after reading from buffer):

01020000000300000000000000000000000000000000000000000000000000fd3f000000000000fd3f00000000000000400000000000000040

its very close to what it should be

MySqlWorkBench for reference:

Is there something i'm missing here?

Codesandbox without pscale hooked up

[mattrobenolt]

That's interesting. I wonder if this is a bug in Vitess. I'll help try and investigate.

[dylel]

Think i've figured out where it's coming from, i've added my own blank type cast for a geometry field:

 // create the connection
  const connection = connect({
    url: env.DATABASE_URL,
    cast: (field, value) => {
      if (field.type === "geometry") {
      }
      return value;
    },
  });

and now i receive the exact same hex as expected when reading from the buffer and can successfully extract the srid and it all matches what its supposed to be. So i suspect that the driver must be slightly altering the geometry field inside the default cast function

[mattrobenolt]

Oh, you're right. Our default case is to decode the binary into text. Which is likely why this is happening. We should handle this and explicitly return the raw binary.

Thanks for pointing this out! I definitely don't have much experience with geometry types personally.

[nmajor]

Following closely in the footsteps of @dylel, I was able to get geometry columns working with drizzle-orm and database-js with this code for my casting:

const connection = connect({
  url: env.DATABASE_URL,
  cast: (field, value) => {
    if (field.type === "GEOMETRY") {
      return value;
    }
    return cast(field, value);
  },
});

Later in the flow, I also had to manually remove the first 4 bytes from the value before using the wkx lib to convert it into geoJson.

Thanks @dylel

[iheanyi]

Hey y'all, we've fixed a bug with this in v1.8.0. This now returns the geometry data as a raw value, letting the caller handle it!