| Summary: | Dates view empty using MYSQL due to SQL query error (fix supplied) | ||
|---|---|---|---|
| Product: | [Applications] digikam | Reporter: | david.varnes |
| Component: | Plugin-Generic-Calendar | Assignee: | Digikam Developers <digikam-bugs-null> |
| Status: | RESOLVED FIXED | ||
| Severity: | normal | CC: | caulier.gilles, metzpinguin |
| Priority: | NOR | ||
| Version First Reported In: | 5.2.0 | ||
| Target Milestone: | --- | ||
| Platform: | Ubuntu | ||
| OS: | Linux | ||
| Latest Commit: | Version Fixed/Implemented In: | 6.0.0 | |
| Sentry Crash Report: | |||
Thanks for you investigation. Can you provide a patch against current code please ? Gilles Caulier *** This bug has been marked as a duplicate of bug 370744 *** Patch submitted to duplicate bug 370744 Not reproducible with 6.0.0 |
Some time in the 4.x release series, the Dates view stopped showing any images when using an external MYSQL server. I have been checking every release since about 4.10. Using 5.2.0 (phillip5 PPA packages on Ubuntu 16.10) I see the following error on the console whenever the Dates view is selected. digikam.dbengine: Failure executing query: "SELECT DISTINCT Images.id, Images.name, Images.album, Albums.albumRoot, ImageInformation.rating, Images.category, ImageInformation.format, ImageInformation.creationDate, Images.modificationDate, Images.fileSize, ImageInformation.width, ImageInformation.height FROM Images LEFT JOIN ImageInformation ON Images.id=ImageInformation.imageid INNER JOIN Albums ON Albums.id=Images.album WHERE Images.status=1 AND ImageInformation.creationDate < '2016-11-01T00:00:00' AND ImageInformation.creationDate >= '2016-10-01T00:00:00' ORDER BY Albums.id;" Error messages: "QMYSQL: Unable to execute query" "Expression #1 of ORDER BY clause is not in SELECT list, references column 'kam_core_5.Albums.id' which is not in SELECT list; this is incompatible with DISTINCT" 3065 2 Bound values: (QVariant(QString, "2016-11-01T00:00:00"), QVariant(QString, "2016-10-01T00:00:00")) Clearly from the error message the ORDER BY is incorrect (at least for MYSQL). I have not checked if this syntax works with SQLITE, I can only imagine it does otherwise this bug would have been raised already! FIX Changing the ORDER BY clause to use Images.album (which IS in the select list): ORDER BY Images.album; works perfectly when executed as a direct query against the database. As a bonus request: Can we have a DatesView component in the bug tracker ?