SQLite Forum

Insert into geoply table seg faults
Login

Insert into geoply table seg faults

(1) By anonymous on 2022-08-01 12:13:33 [source]

When i insert a row into a geopoly table the shell crashes with a segfault.

Here is the .schema for the tables:
CREATE TABLE mt_locations(
  mt_id INT,
  locations_id TEXT,
  stop_name TEXT,
  stop_desc TEXT,
  comment TEXT,
  UNIQUE(mt_id, locations_id)
);
CREATE TABLE IF NOT EXISTS "mt_area_rowid"(rowid INTEGER PRIMARY KEY,nodeno,a0,a1,a2,a3);
CREATE TABLE IF NOT EXISTS "mt_area_node"(nodeno INTEGER PRIMARY KEY,data);
CREATE TABLE IF NOT EXISTS "mt_area_parent"(nodeno INTEGER PRIMARY KEY,parentnode);
CREATE VIRTUAL TABLE mt_area USING geopoly(
  mt_id INT,
  locations_id TEXT,
  FOREIGN KEY(mt_id, locations_id) REFERENCES mt_locations(mt_id, locations_id)
)
/* mt_area(
  _shape,
  mt_id,
  locations_id
) */;

Here is the series of statements in my test script:
BEGIN TRANSACTION;
INSERT OR REPLACE INTO mt_locations (mt_id,locations_id,stop_name,stop_desc,comment) VALUES ('19','loc_1',
'Plymouth Metrolink','City of Plymouth',' ');
DELETE FROM mt_area WHERE mt_id=19 and locations_id='loc_1';
INSERT INTO mt_area (mt_id,locations_id,_shape) VALUES (19,'loc_1','[[45.065712,-93.445484],[45.065710,-93.461458],
[45.065584,-93.522074],[45.065712,-93.445484]]');
Commit;

It is the insert into mt_area statement that is crashing.

gdb reports 
#0  0x080937bb in sqlite3_bind_value ()
#1  0x08115002 in geopolyUpdate ()
#2  0x080df1f0 in sqlite3VdbeExec ()
#3  0x080e078f in sqlite3_step ()

I am running SQLite 3.39.2 built from the amalgamation on Centos 6.

Can another set of eyes see what may be wrong with the insert statement?

Thank you.

(2) By Richard Hipp (drh) on 2022-08-01 12:55:10 in reply to 1 [link] [source]

Thanks for the bug report. Simplified test case:

CREATE VIRTUAL TABLE t1 USING geopoly(a,b,CHECK(a>0));
INSERT INTO t1(a,b,_shape) VALUES(1,2,'[[0,0],[1,0],[1,1],[0,0]]');

Your work-around until I get a bug fix in place is to omit the FOREIGN KEY constraint from your schema. Geopoly does not understand foreign keys, so it is pointless to include it in the schema. The geopoly virtual table is confusing the FOREIGN KEY constraint for an auxiliary column, and then segfaulting when it tries to update that column which does not exist.

(3) By Richard Hipp (drh) on 2022-08-01 13:18:58 in reply to 1 [link] [source]

The problem should now be resolved both on trunk and on branch-3.39.

(4) By anonymous on 2022-08-02 15:40:46 in reply to 3 [link] [source]

Thank you. Removing the foreign key from the schema eliminated the crash.