Project

General

Profile

Bug #11636

SQL syntax error

Added by Heiko Bauke over 2 years ago. Updated over 2 years ago.

Status:
Fixed
Priority:
Low
Assignee:
-
Category:
General
Target version:
Start date:
06/03/2017
Due date:
% Done:

100%

Estimated time:
Affected Version:
git master branch
System:
Ubuntu
bitness:
64-bit
hardware architecture:
amd64/x86

Description

I am getting some error messages that hint to an SQL issue.

sqlite3 error: /home/[...]/src/libs/tools/filmstrip.c:523, function _lib_filmstrip_imgid_in_collection(): near ")": syntax error
sqlite3 error: /home/[...]/src/libs/tools/filmstrip.c:527, function _lib_filmstrip_imgid_in_collection(): near ")": syntax error
sqlite3 error: /home/[...]/src/libs/tools/filmstrip.c:528, function _lib_filmstrip_imgid_in_collection(): near ")": syntax error
sqlite3 error: /home/[...]/src/libs/tools/filmstrip.c:530, function _lib_filmstrip_imgid_in_collection(): near ")": syntax error

Furthermore, opening the darkroom no stars are indicated for some film strip pictures even when the corresponding pictures are given a number of stars. Both issues might be related.

I am sorry that I am not able to elaborate this bug report further as I am not an sqlite expert.

Heiko

Associated revisions

Revision 284f1e83 (diff)
Added by Tobias Ellinghaus over 2 years ago

Fix broken SQL query manipulation

This code was relying on the specific formatting of the collection's
query in order to manipulate the string. That broke in some cases.
Instead we are now using the collection query as a subquery and wrapping
a count() selection around it.
Fixes #11636

History

#1 Updated by Tobias Ellinghaus over 2 years ago

Do you have a relatively complex rule set in your collect module? What happens when you just collect by filmroll? Do you still get those errors?

#2 Updated by Tobias Ellinghaus over 2 years ago

As you are using a git build, please update and show the error message. I updated them to show the failed query, maybe that will shed some light on it.

#3 Updated by Heiko Bauke over 2 years ago

Tobias Ellinghaus wrote:

I updated them to show the failed query, maybe that will shed some light on it.

I applied the mentioned patch. The now more verbose error message reads typically:


sqlite3 error: /home/bauke/[...]/filmstrip.c:523, function _lib_filmstrip_imgid_in_collection(), query "SELECT COUNT(*) FROM main.images WHERE id=?3 AND (flags & 256) != 256 AND ((film_id IN (SELECT id FROM main.film_rolls WHERE folder LIKE '/home/bauke/Bilder/RAW/2017/2017_06_xx')))) AS a LEFT OUTER JOIN main.color_labels AS b ON a.id = b.imgid ORDER BY color DESC, filename, version LIMIT ?1, ?2": near ")": syntax error
sqlite3 error: /home/bauke/[...]/filmstrip.c:527, function _lib_filmstrip_imgid_in_collection(): near ")": syntax error
sqlite3 error: /home/bauke/[...]/filmstrip.c:528, function _lib_filmstrip_imgid_in_collection(): near ")": syntax error
sqlite3 error: /home/bauke/[...]/filmstrip.c:530, function _lib_filmstrip_imgid_in_collection(): near ")": syntax error

If I count correctly there is one closing brace more than opening braces.

Regards, Heiko

#4 Updated by Tobias Ellinghaus over 2 years ago

  • % Done changed from 0 to 50
  • Status changed from New to In Progress

please take a screenshot of the collect module and the filter settings in the top. The query looks unbalanced indeed, but I would like to reproduce it locally. That would make it easier to spot the bad code.

#5 Updated by Tobias Ellinghaus over 2 years ago

Ok, I think I found it. The reason being the braindead way some parts of dt fiddle with sql query strings. Which totally breaks as soon as you order by colorlabels.

#6 Updated by Heiko Bauke over 2 years ago

Tobias Ellinghaus wrote:

Ok, I think I found it. The reason being the braindead way some parts of dt fiddle with sql query strings. Which totally breaks as soon as you order by colorlabels.

Indeed, I sorted images by color labels when the described error appeared.

Heiko

#7 Updated by Tobias Ellinghaus over 2 years ago

  • % Done changed from 50 to 100
  • Status changed from In Progress to Fixed

#8 Updated by Roman Lebedev over 2 years ago

  • Target version changed from Candidate for next patch release to 2.4.0

Also available in: Atom PDF

Go to top