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 -- <long list of table
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
