Segmentation fault running a query

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Segmentation fault running a query

Peter Hardman
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

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

schema.txt (11K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Segmentation fault running a query

Richard Hipp-3
On 3/6/19, Peter Hardman <[hidden email]> wrote:
>
> The schema (of a very much cut down database) is attached.

I translated the attached schema into SQL and appended the query.  I
uploaded the result to https://sqlite.org/tmp/peter-hardman-001.txt

I have run the resulting script through every version of SQLite I have
at hand, and none of them given any trouble.  I also ran them under
Valgrind.  No problems detected.

Can anybody else reproduce the problem?

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Segmentation fault running a query

Keith Medcalf

I was not able to reproduce it.  I arrived at the following decoded schema and query:

CREATE TABLE version (
    major_version INTEGER NOT NULL PRIMARY KEY,
    minor_version INTEGER NOT NULL,
    paradox_upload TEXT,
    converted TIMESTAMP NOT NULL,
    created TIMESTAMP NOT NULL
);
CREATE TABLE mem_class (
    class_name VARCHAR(16) NOT NULL PRIMARY KEY,
    description VARCHAR(32) NOT NULL UNIQUE,
    base_subscription INTEGER NOT NULL,
    discount INTEGER NOT NULL,
    renewal_period INTEGER NOT NULL,
    joining_fee INTEGER NOT NULL,
    votes INTEGER NOT NULL,
    renewal_notice INTEGER NOT NULL CHECK(renewal_notice in (0, 1)),
    is_active INTEGER NOT NULL CHECK(is_active in (0, 1)),
    change_reason VARCHAR(32) NOT NULL,
    last_changed TIMESTAMP NOT NULL
);
CREATE TABLE mem_dates (
    renewal_date DATE NOT NULL PRIMARY KEY,
    discount_end_date DATE NOT NULL,
    membership_ceases DATE NOT NULL,
    joiner_rollfwd_date DATE NOT NULL,
    last_renewal_date DATE NOT NULL,
    next_renewal_date DATE NOT NULL,
    change_reason VARCHAR(32) NOT NULL,
    last_changed TIMESTAMP NOT NULL
);
CREATE TABLE pmt_method (
    pmt_method VARCHAR(16) NOT NULL PRIMARY KEY,
    method_code VARCHAR(8) NOT NULL UNIQUE,
    change_reason VARCHAR(32) NOT NULL,
    last_changed TIMESTAMP NOT NULL
);
CREATE TABLE mem_source (
    source VARCHAR(32) NOT NULL PRIMARY KEY,
    source_code VARCHAR(8) NOT NULL UNIQUE,
    change_reason VARCHAR(32) NOT NULL,
    last_changed TIMESTAMP NOT NULL
);
CREATE TABLE nonren_reason (
    reason VARCHAR(64) NOT NULL PRIMARY KEY,
    reason_code VARCHAR(8) NOT NULL UNIQUE,
    change_reason VARCHAR(32) NOT NULL,
    last_changed TIMESTAMP NOT NULL
);
CREATE TABLE phone_type (
    phone_type VARCHAR(16) NOT NULL PRIMARY KEY,
    change_reason VARCHAR(32) NOT NULL,
    last_changed TIMESTAMP NOT NULL
);
CREATE TABLE role_type (
    role_type VARCHAR(32) NOT NULL PRIMARY KEY,
    role_code VARCHAR(1) NOT NULL,
    change_reason VARCHAR(32) NOT NULL,
    last_changed TIMESTAMP NOT NULL
);
CREATE TABLE county (
    county VARCHAR(32) NOT NULL PRIMARY KEY,
    county_code VARCHAR(2) NOT NULL UNIQUE,
    change_reason VARCHAR(32) NOT NULL,
    last_changed TIMESTAMP NOT NULL
);
CREATE TABLE adj_county (
    rec_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    county VARCHAR(32) NOT NULL REFERENCES county (county) ON UPDATE RESTRICT ON DELETE RESTRICT,
    adj_county VARCHAR(32) NOT NULL REFERENCES county (county) ON UPDATE RESTRICT ON DELETE RESTRICT,
    change_reason VARCHAR(32) NOT NULL,
    last_changed TIMESTAMP NOT NULL,
    CONSTRAINT adj_county_key UNIQUE (county, adj_county)
);
CREATE TABLE country (
    country VARCHAR(32) NOT NULL PRIMARY KEY,
    country_code VARCHAR(2) NOT NULL UNIQUE,
    calling_code VARCHAR(4) NOT NULL CHECK(calling_code REGEXP '^[0-9 ]*$'),
    change_reason VARCHAR(32) NOT NULL,
    last_changed TIMESTAMP NOT NULL
);
CREATE TABLE region (
    region VARCHAR(32) NOT NULL PRIMARY KEY,
    region_code VARCHAR(2) NOT NULL UNIQUE,
    change_reason VARCHAR(32) NOT NULL,
    last_changed TIMESTAMP NOT NULL
);
CREATE TABLE in_region (
    rec_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    region VARCHAR(32) NOT NULL REFERENCES region (region) ON UPDATE RESTRICT ON DELETE RESTRICT,
    county VARCHAR(32) NOT NULL REFERENCES county (county) ON UPDATE RESTRICT ON DELETE RESTRICT,
    country VARCHAR(32) NOT NULL REFERENCES country (country) ON UPDATE RESTRICT ON DELETE RESTRICT,
    change_reason VARCHAR(32) NOT NULL,
    last_changed TIMESTAMP NOT NULL,
    CONSTRAINT in_region_key UNIQUE (region, county, country)
);
CREATE TABLE post_area (
    outward_code VARCHAR(4) NOT NULL PRIMARY KEY,
    area_name VARCHAR(32) NOT NULL UNIQUE,
    latitude FLOAT NOT NULL,
    longitude FLOAT NOT NULL,
    easting INTEGER NOT NULL,
    northing INTEGER NOT NULL,
    grid_ref CHARACTER(8) NOT NULL,
    change_reason VARCHAR(32) NOT NULL,
    last_changed TIMESTAMP NOT NULL
);
CREATE TABLE person (
    person_id INTEGER NOT NULL PRIMARY KEY,
    active INTEGER NOT NULL CHECK(active in (0, 1)),
    title VARCHAR(20) NOT NULL,
    initials VARCHAR(30) NOT NULL,
    forename VARCHAR(40) NOT NULL,
    surname VARCHAR(40) NOT NULL,
    created TIMESTAMP NOT NULL,
    change_reason VARCHAR(32) NOT NULL,
    last_changed TIMESTAMP NOT NULL
);
CREATE TABLE member (
    member_no VARCHAR(6) NOT NULL PRIMARY KEY,
    primary_person_id INTEGER NOT NULL REFERENCES person (person_id) ON UPDATE RESTRICT ON DELETE RESTRICT,
    address_1 VARCHAR(30) NOT NULL,
    address_2 VARCHAR(30) NOT NULL,
    address_3 VARCHAR(30) NOT NULL,
    post_town VARCHAR(24) NOT NULL,
    county VARCHAR(32) NOT NULL REFERENCES county (county) ON UPDATE RESTRICT ON DELETE RESTRICT,
    post_code VARCHAR(16) NOT NULL,
    country VARCHAR(32) NOT NULL REFERENCES country (country) ON UPDATE RESTRICT ON DELETE RESTRICT,
    share_info CHAR(8) NOT NULL,
    joined DATE NOT NULL,
    non_renewal VARCHAR(64) NOT NULL,
    source VARCHAR(32) NOT NULL,
    note VARCHAR(256) NOT NULL,
    change_reason VARCHAR(32) NOT NULL,
    last_changed TIMESTAMP NOT NULL
);
CREATE TABLE mem_pers (
    person_id INTEGER NOT NULL PRIMARY KEY REFERENCES person (person_id) ON UPDATE RESTRICT ON DELETE RESTRICT,
    member_no VARCHAR(6) NOT NULL REFERENCES member (member_no) ON UPDATE RESTRICT ON DELETE RESTRICT,
    change_reason VARCHAR(32) NOT NULL,
    last_changed TIMESTAMP NOT NULL
);
CREATE TABLE mem_pmts (
    payment_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    member_no VARCHAR(6) NOT NULL REFERENCES member (member_no) ON UPDATE RESTRICT ON DELETE RESTRICT,
    class_name VARCHAR(16) NOT NULL REFERENCES mem_class (class_name) ON UPDATE RESTRICT ON DELETE RESTRICT,
    renewed DATE NOT NULL,
    expires DATE NOT NULL,
    pmt_method VARCHAR(16) NOT NULL REFERENCES pmt_method (pmt_method) ON UPDATE RESTRICT ON DELETE RESTRICT,
    pmt_ref VARCHAR(18) NOT NULL,
    gift_aid INTEGER NOT NULL CHECK(gift_aid in (0, 1)),
    change_reason VARCHAR(32) NOT NULL,
    last_changed TIMESTAMP NOT NULL
);
CREATE TABLE phone (
    phone_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    person_id INTEGER NOT NULL REFERENCES person (person_id) ON UPDATE RESTRICT ON DELETE RESTRICT,
    calling_code VARCHAR(4) NOT NULL CHECK(calling_code REGEXP '^[0-9 ]*$'),
    number VARCHAR(14) NOT NULL CHECK(number REGEXP '^[0-9 ]*$'),
    phone_type VARCHAR(16) NOT NULL REFERENCES phone_type (phone_type) ON UPDATE RESTRICT ON DELETE RESTRICT,
    comment VARCHAR(64) NOT NULL,
    date_assigned TIMESTAMP NOT NULL,
    change_reason VARCHAR(32) NOT NULL,
    last_changed TIMESTAMP NOT NULL,
    CONSTRAINT phone_assigned UNIQUE (person_id, date_assigned)
);
CREATE TABLE email (
    email_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    person_id INTEGER NOT NULL REFERENCES person (person_id) ON UPDATE RESTRICT ON DELETE RESTRICT,
    address VARCHAR(254) NOT NULL,
    comment VARCHAR(64) NOT NULL,
    date_assigned TIMESTAMP NOT NULL,
    change_reason VARCHAR(32) NOT NULL,
    last_changed TIMESTAMP NOT NULL,
    CONSTRAINT email_assigned UNIQUE (person_id, date_assigned)
);
CREATE TABLE roles (
    rec_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    person_id INTEGER NOT NULL REFERENCES person (person_id) ON UPDATE RESTRICT ON DELETE RESTRICT,
    role VARCHAR(32) NOT NULL REFERENCES role_type (role_type) ON UPDATE RESTRICT ON DELETE RESTRICT,
    expires DATE,
    change_reason VARCHAR(32) NOT NULL,
    last_changed TIMESTAMP NOT NULL,
    CONSTRAINT roles_key UNIQUE (person_id, role)
);
CREATE TABLE reps_area (
    rec_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    person_id INTEGER NOT NULL REFERENCES person (person_id) ON UPDATE RESTRICT ON DELETE RESTRICT,
    area VARCHAR(32) NOT NULL,
    change_reason VARCHAR(32) NOT NULL,
    last_changed TIMESTAMP NOT NULL,
    CONSTRAINT reps_area_key UNIQUE (person_id, area)
);
CREATE TABLE sel_pers (
    rec_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    person_id INTEGER NOT NULL REFERENCES person (person_id) ON UPDATE RESTRICT ON DELETE RESTRICT,
    reason VARCHAR(32) NOT NULL,
    change_reason VARCHAR(32) NOT NULL,
    last_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 SELECT latest.payment_id, latest.member_no, latest.class_name, latest.renewed,
          latest.expires, latest.pmt_method, latest.pmt_ref, latest.gift_aid,
          latest.change_reason, latest.last_changed
     FROM mem_pmts latest
    WHERE NOT EXISTS (SELECT 'x' FROM mem_pmts even_later
                       WHERE even_later.member_no = latest.member_no
                         AND even_later.last_changed > latest.last_changed);

And the query was:

select  m.member_no, p.title, p.initials, p.forename, p.surname,
m.address_1, m.address_2, m.address_3, m.post_town, m.county,
m.post_code, m.country, s.class_name, m.joined, s.renewed,
s.expires, s.pmt_method, m.non_renewal, m.share_info,
m.last_changed, p.person_id, r.region
from person p join mem_pers mp on p.person_id = mp.person_id
join member m on mp.member_no = m.member_no
join current_member_status s on m.member_no = s.member_no
left join in_region r on m.county = r.county
  join mem_dates d on s.expires > d.renewal_date where m.non_renewal = ''
and p.person_id in
(200,270,271,355,427,484,512,598,685,724,847,922,973,1095,1189,1371,1421,1483,1516,1523,1592,1799,1866,2024,2076,2077);


Plan is generated, no crash, but no data so the generated VDBE code did not actually run ...

sqlite> .eqp on
sqlite> select  m.member_no, p.title, p.initials, p.forename, p.surname,
   ...> m.address_1, m.address_2, m.address_3, m.post_town, m.county,
   ...> m.post_code, m.country, s.class_name, m.joined, s.renewed,
   ...> s.expires, s.pmt_method, m.non_renewal, m.share_info,
   ...> m.last_changed, p.person_id, r.region
   ...> from person p join mem_pers mp on p.person_id = mp.person_id
   ...> join member m on mp.member_no = m.member_no
   ...> join current_member_status s on m.member_no = s.member_no
   ...> left join in_region r on m.county = r.county
   ...>   join mem_dates d on s.expires > d.renewal_date where m.non_renewal = ''
   ...> and p.person_id in
   ...> (200,270,271,355,427,484,512,598,685,724,847,922,973,1095,1189,1371,1421,1483,1516,1523,1592,1799,1866,2024,2076,2077)
   ...> ;
QUERY PLAN
|--SEARCH TABLE mem_pers AS mp USING INTEGER PRIMARY KEY (rowid=?) (~26 rows)
|--SEARCH TABLE person AS p USING INTEGER PRIMARY KEY (rowid=?) (~1 row)
|--SEARCH TABLE member AS m USING INDEX sqlite_autoindex_member_1 (member_no=?) (~1 row)
|--SEARCH TABLE mem_pmts AS latest USING INDEX mem_pmts_mem_no_index (member_no=?) (~8 rows)
|--CORRELATED SCALAR SUBQUERY 1
|  `--SEARCH TABLE mem_pmts AS even_later USING INDEX mem_pmts_mem_no_index (member_no=?) (~9 rows)
|--SEARCH TABLE in_region AS r USING AUTOMATIC COVERING INDEX (county=?) (~20 rows)
`--SEARCH TABLE mem_dates AS d USING COVERING INDEX sqlite_autoindex_mem_dates_1 (renewal_date<?) (~262144 rows)
sqlite> .exit

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Richard Hipp
>Sent: Wednesday, 6 March, 2019 04:31
>To: SQLite mailing list
>Subject: Re: [sqlite] Segmentation fault running a query
>
>On 3/6/19, Peter Hardman <[hidden email]> wrote:
>>
>> The schema (of a very much cut down database) is attached.
>
>I translated the attached schema into SQL and appended the query.  I
>uploaded the result to https://sqlite.org/tmp/peter-hardman-001.txt
>
>I have run the resulting script through every version of SQLite I
>have
>at hand, and none of them given any trouble.  I also ran them under
>Valgrind.  No problems detected.
>
>Can anybody else reproduce the problem?
>
>--
>D. Richard Hipp
>[hidden email]
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

schema.txt (12K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Segmentation fault running a query

Dan Kennedy-4
In reply to this post by Peter Hardman

On 6/3/62 16:37, Peter Hardman wrote:
> 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.


Can you post the results of running the ".fullschema" command in the
shell tool?

Thanks,

Dan.



>
>
>
> 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.
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Segmentation fault running a query

Peter Hardman
OK. I've anonymised the database and cut it down so that only the records
extracted by the query are present. I have this feeling I've done something
stupid with the data - but all the other queries I run against this database
work fine.

It still gives a segfault when I run the query.

The SQLite3 version is SQLite 3.27.1 2019-02-08 13:17:39, built with zlib
version 1.2.11 and gcc-8.2.1 20181127.

I've attached the database file (300K).



Dan Kennedy wrote on 06/03/2019 12:07:

>
> On 6/3/62 16:37, Peter Hardman wrote:
>> 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.
>
>
> Can you post the results of running the ".fullschema" command in the shell tool?
>
> Thanks,
>
> Dan.
>
>
>
>>
>>
>>
>> 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.
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

--

Peter Hardman
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Segmentation fault running a query

Richard Hipp-3
On 3/6/19, Peter Hardman <[hidden email]> wrote:
>
> I've attached the database file (300K).
>

Attachments are stripped by the mailing list.  Please send via private
email directly to me.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users