After updating to views 7.x-3.21 including a filter using the same field and values twice will produce an SQL error. "SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax…"
In my view I utilize the filter grouping around the OR operator and so I have to duplicate some fields in my filter. I have been able to duplicate this error on two different views using two different content types and taxonomies.
My steps to duplicate:
- A new view showing fields, with a content type that includes taxonomy terms.
- Build a filter with two filter groups
- In each group include the taxonomy field using the same criteria.
Here's the resulting query, note the empty AND() at the end.
SELECT node.title AS node_title, node.nid AS nid, node.created AS node_created
FROM
{node} node
LEFT JOIN {field_data_field_communico_age_group} field_data_field_communico_age_group_value_0 ON node.nid = field_data_field_communico_age_group_value_0.entity_id AND (field_data_field_communico_age_group_value_0.entity_type = 'node' AND field_data_field_communico_age_group_value_0.deleted = '0' AND field_data_field_communico_age_group_value_0.field_communico_age_group_tid = '253')
WHERE (( (node.status = '1') AND (node.type IN ('news_article')) AND( (field_data_field_communico_age_group_value_0.field_communico_age_group_tid IS NULL ) ))OR( (node.status = 1 OR (node.uid = 54 AND 54 <> 0 AND 1 = 1) OR 1 = 1) AND (node.type IN ('news_article')) AND()))
ORDER BY node_created DESC
LIMIT 10 OFFSET 0A potential workaround appears possible by including the targeted taxonomy field in one filter group, then by including 'Content: Has Taxonomy Term' based on the same taxonomy criteria in the second group.
| Comment | File | Size | Author |
|---|---|---|---|
| WorkAround.png | 137.38 KB | ga_dostal | |
| DupTaxonomyFilterQuery.png | 137.47 KB | ga_dostal |
Comments
Comment #2
mustanggb commentedComment #3
steinmb commentedComment #4
damienmckennaComment #5
damienmckennaLet's work on adding tests so we're sure what we're fixing.
Comment #6
feyp commentedI believe I ran into this and fixed this as part of #3040391: 3.21 Regression, malformed SQL: 'Is none of' within grouped exposed filter. Could you please try the latest patch there (patch #40) and see, if it fixes the problem for you? If you want to apply it to 7.x-3.21 instead of 7.x-3.x-dev, you need to apply this patch first, otherwise the patch won't apply. If it works, we could close this one as a duplicate.
Comment #7
ga_dostal commentedThe patch from issue #3040391: 3.21 Regression, malformed SQL: 'Is none of' within grouped exposed filter does appear to fix my issue. The malformed SQL query is now corrected when two of the same taxonomy field filters are applied.
I tested this against Views 3.21, on php 7.1.27 & MySQL 5.7.25. Thank you @FeyP for your suggestion on applying the views handler patch first. Worked like a charm!
Comment #8
damienmckennaI'm very happy to hear it, thank you ga_dostal and FeyP!