Project

General

Profile

Bug #7838

sqlValidate does not work with inner query

Added by Nicola Chiapolini over 1 year ago. Updated about 1 year ago.

Status:
Closed
Priority:
Normal
Target version:
-
Start date:
07.02.2019
Due date:
% Done:

0%

Estimated time:
Discuss:

Description

QFQ-Version: 19.01.1

I do have a beforeSave element with the following validation setup:

sqlValidate={{!SELECT id FROM `phd_milestone_subrecords` WHERE milestone_id='{{id:R0}}' LIMIT {{SELECT subrecords FROM _milestone_types WHERE id='{{type:R0}}' }} }}
expectRecords = {{SELECT subrecords FROM _milestone_types WHERE id='{{type:R0}}' }}
messageFail = Wrong number of entries.

This does not raise the error even if the needed number of subrecords are missing. When replacing the inner query with 1 everything works.
Other places where I use inner queries seem to work correctly.

The problem can be avoided with:

sqlValidate={{!SELECT id FROM `phd_milestone_subrecords` WHERE milestone_id='{{id:R0}}' }}
expectRecords = {{SELECT GROUP_CONCAT(seq) FROM (SELECT seq FROM seq_0_to_30) AS positions WHERE seq >= (SELECT subrecords FROM _milestone_types WHERE id='{{type:R0}}') }}
messageFail = Wrong number of entries.

I have not investigated this much further, but am happy to create a Minimal Not-Working Example if needed :-)


Related issues

Related to QFQ - Support #9074: fillStoreVar: seems not to parse nested QFQ queryClosed2019-09-092019-09-09

History

#1 Updated by Carsten Rose over 1 year ago

  • Status changed from New to Feedback
  • Assignee set to Carsten Rose

Hallo Nicola

ich weiss nicht ob es das Problem ist , aber

  • wenn die Inner Query keinen Record selektiert steht nur "... LIMIT"
  • wenn die Inner Query mehrere Records selektiert (ist vermutlich nicht der Fall) waere fuer 'subrecords'=1|23|66 das Ergebnis '12366'

Lass dir mal das Ergebnis in anzeigen mit:

messageFail=Wrong number of entries. {{SELECT subrecords FROM _milestone_types WHERE id='{{type:R0}}' }}

Steht dort in allen Konstellationen die gewuenschte Zahl?

CU
Carsten

#2 Updated by Nicola Chiapolini over 1 year ago

Mit

sqlValidate={{!SELECT id FROM `phd_milestone_subrecords` WHERE milestone_id='{{id:R0}}' LIMIT 1 }}
expectRecords = 1
messageFail = Wrong number of entries. {{SELECT subrecords FROM _milestone_types WHERE id='{{type:R0}}' }}

und keinem Record bekomme ich die Fehlermeldung

Wrong number of entries. >>1<<

Ebenso für

sqlValidate={{!SELECT id FROM `phd_milestone_subrecords` WHERE milestone_id='{{id:R0}}' LIMIT 1 }}
expectRecords = {{SELECT subrecords FROM _milestone_types WHERE id='{{type:R0}}' }}
messageFail = Wrong number of entries. >>{{SELECT subrecords FROM _milestone_types WHERE id='{{type:R0}}' }}<<

Das Problem scheint also wirklich beim Umgang mit dem Inner-Query.

#3 Updated by Nicola Chiapolini about 1 year ago

tested and problem still exists

#4 Updated by Carsten Rose about 1 year ago

  • Assignee changed from Carsten Rose to Nicola Chiapolini

Kannst Du mal laufen lassen:

sqlValidate={{!SELECT id, unknown.column FROM `phd_milestone_subrecords` WHERE milestone_id='{{id:R0}}' LIMIT {{SELECT subrecords FROM _milestone_types WHERE id='{{type:R0}}' }} }}

Und die Fehlermeldung posten - dort steht hoffentlich was QFQ wie ersetzt hat.

Danke

#5 Updated by Nicola Chiapolini about 1 year ago

Habe das getestet, das gibt keine Fehlermeldung -> sqlValidate wird gar nicht ausgeführt.

Wenn ich das LIMIT-Subquery durch 1 ersetze, bekomme ich den erwarteten Fehler. Wenn ich das LIMIT-Subquery durch {{ SELECT 1 }} ersetzen, wird das Query ebenfalls nicht ausgeführt.

#6 Updated by Carsten Rose about 1 year ago

  • Related to Support #9074: fillStoreVar: seems not to parse nested QFQ query added

#7 Updated by Carsten Rose about 1 year ago

  • Status changed from Feedback to Closed

Fixed in #9074 / 19.9.1

Also available in: Atom PDF