[digikam] [Bug 371726] New: Dates view empty using MYSQL due to SQL query error (fix supplied)

[bugzilla_noreply at kde.org]

https://bugs.kde.org/show_bug.cgi?id=371726

            Bug ID: 371726
           Summary: Dates view empty using MYSQL due to SQL query error
                    (fix supplied)
           Product: digikam
           Version: 5.2.0
          Platform: Ubuntu Packages
                OS: Linux
            Status: UNCONFIRMED
          Severity: normal
          Priority: NOR
         Component: general
          Assignee: digikam-devel at kde.org
          Reporter: david.varnes at gmail.com
  Target Milestone: ---

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 ?

-- 
You are receiving this mail because:
You are the assignee for the bug.