Something broke between version 3.15 and 3.19.3

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Something broke between version 3.15 and 3.19.3

Balaji Ramanathan
Hi,

    I maintain a personal database on sqlite.  It is quite small, with
about 30 tables, and an equal number of views.  One of these views is an
inner join of the contents of about 15 of these views, producing a summary
view of my data.  The views combined in this summary view contain about
3200 rows each, and the summary view usually runs in about 2 seconds or
less.

    Everything was working fine under 3.15.  I just use the commandline
tool (sqlite.exe) to interact with my database.  I am not a programmer and
don't have a need for programmatic access to this database.  I recently
updated to 3.19.3, and now when I select from that summary view, I get no
results - the query just runs for minutes on end, and I eventually lose
patience and kill the process with a ctrl-c.

    I have produced an anonymized version of my database and loaded it to
https://drive.google.com/open?id=0B5B_T2PA2u7ddTdlc1JST0xyVjg for anybody
to access.  If you load this database (named test.db) into the sqlite
command line shell of version 3.15 and run the command "select * from
TripDetails;", you will see that results appear in under 2 seconds.  If you
load the exact same database into the command line shell of version 3.19.3
and run the exact same select statement, it never produces results (or it
takes so long that I have never had the patience to hang around and see if
it does produce results).

    I have verified that even under version 3.19.3, all the individual
views that contribute to the summary view produce results by themselves.
In fact pretty much everything except this summary view seems to work.  So,
it looks like the massive inner join between these views is the cause of
the delay or failure in the latest version of sqlite.  I am not sure how or
why, but I would appreciate it if others on this list who are more
knowledgeable about these things can take a look and let me know what they
think.

    I am sure my db design leaves a lot to be desired in terms of
normalization, optimization, etc.  I am open to suggestions on those
aspects, but my primary concern is that something that worked fine under a
previous version of sqlite does not work anymore.  Whatever the flaws in
what I have done, I do expect things to not break simply when I upgrade to
the latest version of sqlite from a previous version.  I would be open to
modifying my database in such a way that it is more efficient and faster,
and perhaps that enables me to produce the results I want from this query
in the latest version of sqlite.  But to me that is secondary.  I don't
want to be tweaking my database on an ongoing basis to make it perform well
with each new release of sqlite.  The symptoms point to some kind of
regression in sqlite between 3.15 and 3.19.3, and I would like to see if
there is a fix that does not involve modifying my database.

    Thank you very much.

Balaji Ramanathan
_______________________________________________
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: Something broke between version 3.15 and 3.19.3

Richard Hipp-3
On 6/11/17, Balaji Ramanathan <[hidden email]> wrote:

> Hi,
>
>     I maintain a personal database on sqlite.  It is quite small, with
> about 30 tables, and an equal number of views.  One of these views is an
> inner join of the contents of about 15 of these views, producing a summary
> view of my data.  The views combined in this summary view contain about
> 3200 rows each, and the summary view usually runs in about 2 seconds or
> less.
>
>     Everything was working fine under 3.15.  I just use the commandline
> tool (sqlite.exe) to interact with my database.  I am not a programmer and
> don't have a need for programmatic access to this database.  I recently
> updated to 3.19.3, and now when I select from that summary view, I get no
> results - the query just runs for minutes on end, and I eventually lose
> patience and kill the process with a ctrl-c.

Thanks for the test case!

Bisecting shows that the problem is the optimization introduced here:

   https://www.sqlite.org/src/timeline?c=9e35c89dbe744312

I still do not understand the details.  But we'll be working on it.

--
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: Something broke between version 3.15 and 3.19.3

Bart Smissaert
In reply to this post by Balaji Ramanathan
explain query plan select * from TripDetails

Causes problems as well.

RBS



On Sun, Jun 11, 2017 at 5:27 AM, Balaji Ramanathan <
[hidden email]> wrote:

> Hi,
>
>     I maintain a personal database on sqlite.  It is quite small, with
> about 30 tables, and an equal number of views.  One of these views is an
> inner join of the contents of about 15 of these views, producing a summary
> view of my data.  The views combined in this summary view contain about
> 3200 rows each, and the summary view usually runs in about 2 seconds or
> less.
>
>     Everything was working fine under 3.15.  I just use the commandline
> tool (sqlite.exe) to interact with my database.  I am not a programmer and
> don't have a need for programmatic access to this database.  I recently
> updated to 3.19.3, and now when I select from that summary view, I get no
> results - the query just runs for minutes on end, and I eventually lose
> patience and kill the process with a ctrl-c.
>
>     I have produced an anonymized version of my database and loaded it to
> https://drive.google.com/open?id=0B5B_T2PA2u7ddTdlc1JST0xyVjg for anybody
> to access.  If you load this database (named test.db) into the sqlite
> command line shell of version 3.15 and run the command "select * from
> TripDetails;", you will see that results appear in under 2 seconds.  If you
> load the exact same database into the command line shell of version 3.19.3
> and run the exact same select statement, it never produces results (or it
> takes so long that I have never had the patience to hang around and see if
> it does produce results).
>
>     I have verified that even under version 3.19.3, all the individual
> views that contribute to the summary view produce results by themselves.
> In fact pretty much everything except this summary view seems to work.  So,
> it looks like the massive inner join between these views is the cause of
> the delay or failure in the latest version of sqlite.  I am not sure how or
> why, but I would appreciate it if others on this list who are more
> knowledgeable about these things can take a look and let me know what they
> think.
>
>     I am sure my db design leaves a lot to be desired in terms of
> normalization, optimization, etc.  I am open to suggestions on those
> aspects, but my primary concern is that something that worked fine under a
> previous version of sqlite does not work anymore.  Whatever the flaws in
> what I have done, I do expect things to not break simply when I upgrade to
> the latest version of sqlite from a previous version.  I would be open to
> modifying my database in such a way that it is more efficient and faster,
> and perhaps that enables me to produce the results I want from this query
> in the latest version of sqlite.  But to me that is secondary.  I don't
> want to be tweaking my database on an ongoing basis to make it perform well
> with each new release of sqlite.  The symptoms point to some kind of
> regression in sqlite between 3.15 and 3.19.3, and I would like to see if
> there is a fix that does not involve modifying my database.
>
>     Thank you very much.
>
> Balaji Ramanathan
> _______________________________________________
> 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: Something broke between version 3.15 and 3.19.3

Balaji Ramanathan
In reply to this post by Balaji Ramanathan
Thank you very much, Dr. Hipp.  I am glad I was not imagining a problem
that nobody else could reproduce.  If you need any additional details or if
you can think of any other way I can help with the investigation, please
let me know.

Balaji Ramanathan

PS:  This is going to sound stupid, but I would like to know how to
download older versions of sqlite.  The sqlite.org website seems to have
links only to downloads for the latest version.  I can read the changelog
and other details for older versions, but I can't seem to find a link to
download the older version of the program itself.

For instance, the latest release before the bug was introduced seems to be
version 3.18.0, released on 3/30.  I can read the changelog and bugfixes at
https://www.sqlite.org/releaselog/3_18_0.html, but no link on that page
seems to lead to a download page.  I wanted to downgrade to an older
version while I wait for this to be fixed, but I can't seem to figure out
how to do that.  Thank you in advance for any pointers.

> Hi,
>
>     I maintain a personal database on sqlite.  It is quite small, with
> about 30 tables, and an equal number of views.  One of these views is an
> inner join of the contents of about 15 of these views, producing a summary
> view of my data.  The views combined in this summary view contain about
> 3200 rows each, and the summary view usually runs in about 2 seconds or
> less.
>
>     Everything was working fine under 3.15.  I just use the commandline
> tool (sqlite.exe) to interact with my database.  I am not a programmer and
> don't have a need for programmatic access to this database.  I recently
> updated to 3.19.3, and now when I select from that summary view, I get no
> results - the query just runs for minutes on end, and I eventually lose
> patience and kill the process with a ctrl-c.

Thanks for the test case!

Bisecting shows that the problem is the optimization introduced here:

   https://www.sqlite.org/src/timeline?c=9e35c89dbe744312

I still do not understand the details.  But we'll be working on it.

--
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: Something broke between version 3.15 and 3.19.3

Simon Slavin-3
On 11 Jun 2017, at 2:56pm, Balaji Ramanathan <[hidden email]> wrote:

> This is going to sound stupid, but I would like to know how to
> download older versions of sqlite.

Although the download page gives links only for the current version of SQLite, several older versions are still on the server and you can download them by making up the URL yourself.  So, for instance, one of the links currently on the Download page is

<https://www.sqlite.org/2017/sqlite-dll-win32-x86-3190300.zip>

If you want the version before 3.18.0 you can look it up in

<https://www.sqlite.org/changes.html>

and find that it is 3.17.0 which was released in 2017.  Then you can make up the appropriate URL:

<https://www.sqlite.org/2017/sqlite-dll-win32-x86-3170000.zip>

and it works !  Hope this helps.

Simon.
_______________________________________________
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: Something broke between version 3.15 and 3.19.3

Richard Hipp-3
In reply to this post by Balaji Ramanathan
On 6/11/17, Balaji Ramanathan <[hidden email]> wrote:
>
>     Everything was working fine under 3.15.  I just use the commandline
> tool (sqlite.exe) to interact with my database.  I am not a programmer and
> don't have a need for programmatic access to this database.  I recently
> updated to 3.19.3, and now when I select from that summary view, I get no
> results - the query just runs for minutes on end, and I eventually lose
> patience and kill the process with a ctrl-c.

SQLite is still working.  It is just picking an inefficient query plan.

The fix is here:  https://www.sqlite.org/src/timeline?c=87aceb417a813a29

--
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: Something broke between version 3.15 and 3.19.3

Balaji Ramanathan
In reply to this post by Balaji Ramanathan
Thank you very much, Simon.  I was able to downgrade to 3.18 using your
instructions and everything seems to work now.

Balaji Ramanathan

On 11 Jun 2017, at 2:56pm, Balaji Ramanathan <[hidden email]>
wrote:

> This is going to sound stupid, but I would like to know how to
> download older versions of sqlite.

Although the download page gives links only for the current version of
SQLite, several older versions are still on the server and you can download
them by making up the URL yourself.  So, for instance, one of the links
currently on the Download page is

<https://www.sqlite.org/2017/sqlite-dll-win32-x86-3190300.zip>

If you want the version before 3.18.0 you can look it up in

<https://www.sqlite.org/changes.html>

and find that it is 3.17.0 which was released in 2017.  Then you can make
up the appropriate URL:

<https://www.sqlite.org/2017/sqlite-dll-win32-x86-3170000.zip>

and it works !  Hope this helps.

Simon.
_______________________________________________
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: Something broke between version 3.15 and 3.19.3

Balaji Ramanathan
In reply to this post by Balaji Ramanathan
I am glad you were able to fix it quickly.  I assume the next version of
SQLite (3.19.4 or 3.20 or whatever) will include the fix?  Thank you.

Balaji Ramanathan

On 6/11/17, Balaji Ramanathan <[hidden email]> wrote:
>
>     Everything was working fine under 3.15.  I just use the commandline
> tool (sqlite.exe) to interact with my database.  I am not a programmer and
> don't have a need for programmatic access to this database.  I recently
> updated to 3.19.3, and now when I select from that summary view, I get no
> results - the query just runs for minutes on end, and I eventually lose
> patience and kill the process with a ctrl-c.

SQLite is still working.  It is just picking an inefficient query plan.

The fix is here:  https://www.sqlite.org/src/timeline?c=87aceb417a813a29

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