Re: [sqlite] sqlite3 hangs on query

[Jens-Heiner Rechtien]

On 15/02/2017 23:36, Richard Hipp wrote:

On 2/15/17, Richard Hipp  wrote:

It is an optimization opportunity, not a bug.


That optimization is now on trunk.

Very cool!

Thanks,
- Heiner


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 hangs on query

[Jens-Heiner Rechtien]

Hi SQLite team,

sorry for replaying to my own email, the subscription to this list came 
only just through (got eaten by my SPAM folder), so I had to read your 
friendly replies via the list archive.


@Simon: thanks for pointing out that attachments do not work on the 
list. I should have known this. The dumped database (~800kB) in question 
can be found here:


https://dl.dropboxusercontent.com/u/92394185/db.dump.gz

I've checked the database for corruption with a PRAGMA integrity_check, 
it found nothing. Anyway the problem persists a dump and restore cycle, 
so I doubt that corruption plays a role here.


@Clemens, @Igor, @Richard and @Gunter: I know that the cross join with 
19 tables in itself is utter nonsense. The purpose is to name all tables 
in the database in one query - in the app there is special mechanism in 
place which intercepts queries, parses for the statement for table names 
and updates the tables from another data source upfront, before the 
query is executed. Naming all tables would force this update for whole 
database.


It turned out that the intercepting parser doesn't know about sql 
comments, so a simple "SELECT current_date -- names>" works just as well.


But the problem I reported here is genuine. The query doesn't take that 
long, the database isn't that sizeable. About 0.3s on my Mac if the 
select statement is shortened by the *spacefavorite* table.


Please try the following with the restored database:

The query
*select * from albums, album_asset, cacheReferences, coreInfo, space 
limit 1;*

will hang as well.

The query
*select * from albums, album_asset, cacheReferences, coreInfo, coreMD5 
limit 1;*
on the other hand runs very fast as only the first rows of each table 
are concatenated. Run Time: real 0.005 user 0.000317 sys 0.000248.


Note that the *space* table is empty, so the expected result of the 
cross join in the first query would be an empty result set. With a 
slightly different data set this is just what I get.


Thanks,
- Heiner



On 14/02/2017 13:33, Jens-Heiner Rechtien wrote:


Hi SQLite team,

please consider the attached dump of a sqlite3 database and the 
following - admittedly nonsensical, don't ask - query over a restored 
version of the database:


*SELECT count(*) FROM spacefavorite, album_asset, albums, 
assetProfileLinks, avatarCacheReferences, cacheReferences, comment, 
conflicts, coreInfo, coreMD5, errors, flags, importSource, 
missingBinariesOnOz, profileRegistration, quota_exceeded, 
renditionRevisions, space, space_album LIMIT 1;**

*

On iOS, MacOS and Linux this query will busy hang and never return. 
Happens with the command line tool and if used as a prepared statement 
in our iOS app. Changing certain aspects of the query will resolve the 
problem: leaving the table "spacefavorite" out of the query, or 
replacing count(*) with a just an asterisk.


I tried the following versions:

3.14.0, 3.16.2 (MacOSX Sierra), 3.13.0, 3.17.0 (Linux Fedora 24), 
3.14.0 (iOS 10), always the same behavior.


Many thanks for your consideration and especially for your great tool.

Heiner

--

Jens-Heiner Rechtien <jrech...@adobe.com <mailto:aw...@adobe.com>>

Computer Scientist

Adobe Systems Engineering GmbH

Große Elbstraße 27

22767 Hamburg


Registergericht: Hamburg HRB 745 37

Geschäftsführer: Michael D. Jamrosy, Christian Keim, Thomas Mührke, 
Keith San Felipe







___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3 hangs on query

[Jens-Heiner Rechtien]

Hi SQLite team,

please consider the attached dump of a sqlite3 database and the 
following - admittedly nonsensical, don't ask - query over a restored 
version of the database:


*SELECT count(*) FROM spacefavorite, album_asset, albums, 
assetProfileLinks, avatarCacheReferences, cacheReferences, comment, 
conflicts, coreInfo, coreMD5, errors, flags, importSource, 
missingBinariesOnOz, profileRegistration, quota_exceeded, 
renditionRevisions, space, space_album LIMIT 1;**

*

On iOS, MacOS and Linux this query will busy hang and never return. 
Happens with the command line tool and if used as a prepared statement 
in our iOS app. Changing certain aspects of the query will resolve the 
problem: leaving the table "spacefavorite" out of the query, or 
replacing count(*) with a just an asterisk.


I tried the following versions:

3.14.0, 3.16.2 (MacOSX Sierra), 3.13.0, 3.17.0 (Linux Fedora 24), 3.14.0 
(iOS 10), always the same behavior.


Many thanks for your consideration and especially for your great tool.

Heiner

--

Jens-Heiner Rechtien <jrech...@adobe.com <mailto:aw...@adobe.com>>

Computer Scientist

Adobe Systems Engineering GmbH

Große Elbstraße 27

22767 Hamburg


Registergericht: Hamburg HRB 745 37

Geschäftsführer: Michael D. Jamrosy, Christian Keim, Thomas Mührke, 
Keith San Felipe





___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users