bug: Unable to cache table after creating geometry column

[jstammers]

What happened?

I'm trying to create a table with a geometry column using given lat/long coodinates.

I can successfully create this column, but when calling .cache(), I get a ParserError.

Here's an example that replicates it

 from ibis.interactive import *
 t = ibis.examples.zones.fetch()
 t = t.mutate(geom=_.x_cent.point(_.y_cent)).cache()

I don't see this behaviour when I do the following

 t = ibis.examples.zones.fetch()
 t = t.cache() #geom already exists in the zones table

The only material difference in the SQL in each case is the use of ST_POINT when creating the geometry

SELECT
  *
  REPLACE (ST_ASWKB("geom") AS "geom")
FROM (
  SELECT
    "t0"."OBJECTID",
    "t0"."Shape_Leng",
    "t0"."Shape_Area",
    "t0"."zone",
    "t0"."LocationID",
    "t0"."borough",
    "t0"."x_cent",
    "t0"."y_cent",
    ST_POINT("t0"."x_cent", "t0"."y_cent") AS "geom"
  FROM "ibis_read_geo_xqclf77uwbhqzmcrvtctt27c34" AS "t0"
)

SELECT
  *
  REPLACE (ST_ASWKB("geom") AS "geom")
FROM (
  SELECT
    *
  FROM "ibis_read_geo_qwgreoxngfbmjdveyqna5ldkfy"
)

What version of ibis are you using?

9.5.0

What backend(s) are you using, if any?

DuckDB

Relevant log output

ParserException: Parser Error: Expected a constant as type modifier
File <command-2809322511957941>, line 2
      1 t = ibis.examples.zones.fetch()
----> 2 t = t.mutate(geom=_.x_cent.point(_.y_cent)).cache()

Code of Conduct

• I agree to follow this project's Code of Conduct

[gforsyth]

Thanks for reporting this, @jstammers !

It looks like we're generating incorrect SQL for the temporary table definition in the mutate case:

CREATE TEMPORARY TABLE "temp"."main"."ibis_cached_ibsdggv2efdinijummdputbqly" ("OBJECTID" INT, "Shape_Leng" DOUBLE, "Shape_Area" DOUBLE, "zone" TEXT, "LocationID" INT, "borough" TEXT, "x_cent" DOUBLE, "y_cent" DOUBLE, "geom" GEOMETRY(POINT))

That should just be "geom" GEOMETRY at the end, I think.

[anjakefala]

/take