So, I forgot I had emails from the list turned off....
Integrity check shows no results.
The query fails from the CLI
The schema (of a very much cut down database) is attached.
And if it does still crash, please provide the database schema to help with
debugging.
-----Original Message-----
From: sqlite-users [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On
Behalf Of Simon Slavin
Sent: Tuesday, March 05, 2019 3:14 PM
To: SQLite mailing list
Subject: Re: [sqlite] Segmentation fault running a query
On 5 Mar 2019, at 8:06pm, Peter Hardman <peter at somborneshetlands.co.uk>
wrote:
> If i run the following query from Python 3.7 using the sqlite3 DBAPI adapter
or from sqlitebrowser I get a segmentation fault.
Please find the SQLite command-line tool on your computer. If you don't already
have one you can download one for your platform in the section "Precompiled
Binaries" on
<https://sqlite.org/download.html>
Using that program, please run
PRAGMA integrity_check;
If that reports no problems (should return no results) then please run your
SELECT query in that program just to verify that it gets the same result your
own code does.
--
Peter Hardman
('CREATE TABLE version (\nmajor_version INTEGER NOT NULL PRIMARY
KEY,\nminor_version INTEGER NOT NULL,\nparadox_upload TEXT,\nconverted
TIMESTAMP NOT NULL,\ncreated TIMESTAMP NOT NULL)',)
('CREATE TABLE mem_class (\nclass_name VARCHAR(16) NOT NULL PRIMARY
KEY,\ndescription VARCHAR(32) NOT NULL UNIQUE,\nbase_subscription INTEGER NOT
NULL,\ndiscount INTEGER NOT NULL,\nrenewal_period INTEGER NOT
NULL,\njoining_fee INTEGER NOT NULL,\nvotes INTEGER NOT NULL,\nrenewal_notice
INTEGER NOT NULL CHECK(renewal_notice in (0, 1)),\nis_active INTEGER NOT NULL
CHECK(is_active in (0, 1)),\nchange_reason VARCHAR(32) NOT NULL,\nlast_changed
TIMESTAMP NOT NULL)',)
('CREATE TABLE mem_dates (\nrenewal_date DATE NOT NULL PRIMARY
KEY,\ndiscount_end_date DATE NOT NULL,\nmembership_ceases DATE NOT
NULL,\njoiner_rollfwd_date DATE NOT NULL,\nlast_renewal_date DATE NOT
NULL,\nnext_renewal_date DATE NOT NULL,\nchange_reason VARCHAR(32) NOT
NULL,\nlast_changed TIMESTAMP NOT NULL)',)
('CREATE TABLE pmt_method (\npmt_method VARCHAR(16) NOT NULL PRIMARY
KEY,\nmethod_code VARCHAR(8) NOT NULL UNIQUE,\nchange_reason VARCHAR(32) NOT
NULL,\nlast_changed TIMESTAMP NOT NULL)',)
('CREATE TABLE mem_source (\nsource VARCHAR(32) NOT NULL PRIMARY
KEY,\nsource_code VARCHAR(8) NOT NULL UNIQUE,\nchange_reason VARCHAR(32) NOT
NULL,\nlast_changed TIMESTAMP NOT NULL)',)
('CREATE TABLE nonren_reason (\nreason VARCHAR(64) NOT NULL PRIMARY
KEY,\nreason_code VARCHAR(8) NOT NULL UNIQUE,\nchange_reason VARCHAR(32) NOT
NULL,\nlast_changed TIMESTAMP NOT NULL)',)
('CREATE TABLE phone_type (\nphone_type VARCHAR(16) NOT NULL PRIMARY
KEY,\nchange_reason VARCHAR(32) NOT NULL,\nlast_changed TIMESTAMP NOT NULL)',)
('CREATE TABLE role_type (\nrole_type VARCHAR(32) NOT NULL PRIMARY
KEY,\nrole_code VARCHAR(1) NOT NULL,\nchange_reason VARCHAR(32) NOT
NULL,\nlast_changed TIMESTAMP NOT NULL)',)
('CREATE TABLE county (\ncounty VARCHAR(32) NOT NULL PRIMARY KEY,\ncounty_code
VARCHAR(2) NOT NULL UNIQUE,\nchange_reason VARCHAR(32) NOT NULL,\nlast_changed
TIMESTAMP NOT NULL)',)
('CREATE TABLE adj_county (\nrec_id INTEGER NOT NULL PRIMARY KEY
AUTOINCREMENT,\ncounty VARCHAR(32) NOT NULL REFERENCES county (county) ON
UPDATE RESTRICT ON DELETE RESTRICT,\nadj_county VARCHAR(32) NOT NULL REFERENCES
county (county) ON UPDATE RESTRICT ON DELETE RESTRICT,\nchange_reason
VARCHAR(32) NOT NULL,\nlast_changed TIMESTAMP NOT NULL,\nCONSTRAINT
adj_county_key UNIQUE (county, adj_county))',)
('CREATE TABLE sqlite_sequence(name,seq)',)
("CREATE TABLE country (\ncountry VARCHAR(32) NOT NULL PRIMARY
KEY,\ncountry_code VARCHAR(2) NOT NULL UNIQUE,\ncalling_code VARCHAR(4) NOT
NULL CHECK(calling_code REGEXP '^[0-9 ]*$'),\nchange_reason VARCHAR(32) NOT
NULL,\nlast_changed TIMESTAMP NOT NULL)",)
('CREATE TABLE region (\nregion VARCHAR(32) NOT NULL PRIMARY KEY,\nregion_code
VARCHAR(2) NOT NULL UNIQUE,\nchange_reason VARCHAR(32) NOT NULL,\nlast_changed
TIMESTAMP NOT NULL)',)
('CREATE TABLE in_region (\nrec_id INTEGER NOT NULL PRIMARY KEY
AUTOINCREMENT,\nregion VARCHAR(32) NOT NULL REFERENCES region (region) ON
UPDATE RESTRICT ON DELETE RESTRICT,\ncounty VARCHAR(32) NOT NULL REFERENCES
county (county) ON UPDATE RESTRICT ON DELETE RESTRICT,\ncountry VARCHAR(32) NOT
NULL REFERENCES country (country) ON UPDATE RESTRICT ON DELETE
RESTRICT,\nchange_reason VARCHAR(32) NOT NULL,\nlast_changed TIMESTAMP NOT
NULL,\nCONSTRAINT in_region_key UNIQUE (region, county, country))',)
('CREATE TABLE post_area (\noutward_code VARCHAR(4) NOT NULL PRIMARY
KEY,\narea_name VARCHAR(32) NOT NULL UNIQUE,\nlatitude FLOAT NOT
NULL,\nlongitude FLOAT NOT NULL,\neasting INTEGER NOT NULL,\nnorthing INTEGER
NOT NULL,\ngrid_ref CHARACTER(8) NOT NULL,\nchange_reason VARCHAR(32) NOT
NULL,\nlast_changed TIMESTAMP NOT NULL)',)
('CREATE TABLE person (\nperson_id INTEGER NOT NULL PRIMARY KEY,\nactive
INTEGER NOT NULL CHECK(active in (0, 1)),\ntitle VARCHAR(20) NOT
NULL,\ninitials VARCHAR(30) NOT NULL,\nforename VARCHAR(40) NOT NULL,\nsurname
VARCHAR(40) NOT NULL,\ncreated TIMESTAMP NOT NULL,\nchange_reason VARCHAR(32)
NOT NULL,\nlast_changed TIMESTAMP NOT NULL)',)
('CREATE TABLE member (\nmember_no VARCHAR(6) NOT NULL PRIMARY
KEY,\nprimary_person_id INTEGER NOT NULL REFERENCES person (person_id) ON
UPDATE RESTRICT ON DELETE RESTRICT,\naddress_1 VARCHAR(30) NOT NULL,\naddress_2
VARCHAR(30) NOT NULL,\naddress_3 VARCHAR(30) NOT NULL,\npost_town VARCHAR(24)
NOT NULL,\ncounty VARCHAR(32) NOT NULL REFERENCES county (county) ON UPDATE
RESTRICT ON DELETE RESTRICT,\npost_code VARCHAR(16) NOT NULL,\ncountry
VARCHAR(32) NOT NULL REFERENCES country (country) ON UPDATE RESTRICT ON DELETE
RESTRICT,\nshare_info CHAR(8) NOT NULL,\njoined DATE NOT NULL,\nnon_renewal
VARCHAR(64) NOT NULL,\nsource VARCHAR(32) NOT NULL,\nnote VARCHAR(256) NOT
NULL,\nchange_reason VARCHAR(32) NOT NULL,\nlast_changed TIMESTAMP NOT NULL)',)
('CREATE TABLE mem_pers (\nperson_id INTEGER NOT NULL PRIMARY KEY REFERENCES
person (person_id) ON UPDATE RESTRICT ON DELETE RESTRICT,\nmember_no VARCHAR(6)
NOT NULL REFERENCES member (member_no) ON UPDATE RESTRICT ON DELETE
RESTRICT,\nchange_reason VARCHAR(32) NOT NULL,\nlast_changed TIMESTAMP NOT
NULL)',)
('CREATE TABLE mem_pmts (\npayment_id INTEGER NOT NULL PRIMARY KEY
AUTOINCREMENT,\nmember_no VARCHAR(6) NOT NULL REFERENCES member (member_no) ON
UPDATE RESTRICT ON DELETE RESTRICT,\nclass_name VARCHAR(16) NOT NULL REFERENCES
mem_class (class_name) ON UPDATE RESTRICT ON DELETE RESTRICT,\nrenewed DATE NOT
NULL,\nexpires DATE NOT NULL,\npmt_method VARCHAR(16) NOT NULL REFERENCES
pmt_method (pmt_method) ON UPDATE RESTRICT ON DELETE RESTRICT,\npmt_ref
VARCHAR(18) NOT NULL,\ngift_aid INTEGER NOT NULL CHECK(gift_aid in (0,
1)),\nchange_reason VARCHAR(32) NOT NULL,\nlast_changed TIMESTAMP NOT NULL)',)
("CREATE TABLE phone (\nphone_id INTEGER NOT NULL PRIMARY KEY
AUTOINCREMENT,\nperson_id INTEGER NOT NULL REFERENCES person (person_id) ON
UPDATE RESTRICT ON DELETE RESTRICT,\ncalling_code VARCHAR(4) NOT NULL
CHECK(calling_code REGEXP '^[0-9 ]*$'),\nnumber VARCHAR(14) NOT NULL
CHECK(number REGEXP '^[0-9 ]*$'),\nphone_type VARCHAR(16) NOT NULL REFERENCES
phone_type (phone_type) ON UPDATE RESTRICT ON DELETE RESTRICT,\ncomment
VARCHAR(64) NOT NULL,\ndate_assigned TIMESTAMP NOT NULL,\nchange_reason
VARCHAR(32) NOT NULL,\nlast_changed TIMESTAMP NOT NULL,\nCONSTRAINT
phone_assigned UNIQUE (person_id, date_assigned))",)
('CREATE TABLE email (\nemail_id INTEGER NOT NULL PRIMARY KEY
AUTOINCREMENT,\nperson_id INTEGER NOT NULL REFERENCES person (person_id) ON
UPDATE RESTRICT ON DELETE RESTRICT,\naddress VARCHAR(254) NOT NULL,\ncomment
VARCHAR(64) NOT NULL,\ndate_assigned TIMESTAMP NOT NULL,\nchange_reason
VARCHAR(32) NOT NULL,\nlast_changed TIMESTAMP NOT NULL,\nCONSTRAINT
email_assigned UNIQUE (person_id, date_assigned))',)
('CREATE TABLE roles (\nrec_id INTEGER NOT NULL PRIMARY KEY
AUTOINCREMENT,\nperson_id INTEGER NOT NULL REFERENCES person (person_id) ON
UPDATE RESTRICT ON DELETE RESTRICT,\nrole VARCHAR(32) NOT NULL REFERENCES
role_type (role_type) ON UPDATE RESTRICT ON DELETE RESTRICT,\nexpires
DATE,\nchange_reason VARCHAR(32) NOT NULL,\nlast_changed TIMESTAMP NOT
NULL,\nCONSTRAINT roles_key UNIQUE (person_id, role))',)
('CREATE TABLE reps_area (\nrec_id INTEGER NOT NULL PRIMARY KEY
AUTOINCREMENT,\nperson_id INTEGER NOT NULL REFERENCES person (person_id) ON
UPDATE RESTRICT ON DELETE RESTRICT,\narea VARCHAR(32) NOT NULL,\nchange_reason
VARCHAR(32) NOT NULL,\nlast_changed TIMESTAMP NOT NULL,\nCONSTRAINT
reps_area_key UNIQUE (person_id, area))',)
('CREATE TABLE sel_pers (\nrec_id INTEGER NOT NULL PRIMARY KEY
AUTOINCREMENT,\nperson_id INTEGER NOT NULL REFERENCES person (person_id) ON
UPDATE RESTRICT ON DELETE RESTRICT,\nreason VARCHAR(32) NOT
NULL,\nchange_reason VARCHAR(32) NOT NULL,\nlast_changed TIMESTAMP NOT NULL)',)
('CREATE INDEX mem_pers_mem_no_index ON mem_pers (member_no)',)
('CREATE INDEX mem_pmts_mem_no_index ON mem_pmts (member_no)',)
('CREATE INDEX last_change_index ON mem_pmts (last_changed)',)
('CREATE INDEX email_assigned_index ON email (date_assigned)',)
('CREATE INDEX phone_assigned_index ON phone (date_assigned)',)
('CREATE INDEX sel_pers_index ON sel_pers (person_id)',)
("CREATE VIEW current_member_status AS\n SELECT latest.payment_id,
latest.member_no, latest.class_name, latest.renewed, \n latest.expires,
latest.pmt_method, latest.pmt_ref, latest.gift_aid, \n latest.change_reason,
latest.last_changed\n FROM mem_pmts latest \n WHERE NOT EXISTS (SELECT
'x' FROM mem_pmts even_later\n WHERE even_later.member_no =
latest.member_no AND \n
even_later.last_changed > latest.last_changed)",)
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
