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:

  1. A new view showing fields, with a content type that includes taxonomy terms.
  2. Build a filter with two filter groups
  3. 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 0

A 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.

CommentFileSizeAuthor
WorkAround.png137.38 KBga_dostal
DupTaxonomyFilterQuery.png137.47 KBga_dostal

Comments

ga_dostal created an issue. See original summary.

mustanggb’s picture

Priority: Normal » Major
steinmb’s picture

Title: Duplicate Taxonomy Field Filter results in Syntax error or access violation: 1064 » 7.x-3.21 Duplicate Taxonomy Field Filter results in Syntax error or access violation: 1064
Version: 7.x-3.21 » 7.x-3.x-dev
damienmckenna’s picture

damienmckenna’s picture

Issue tags: +Needs tests

Let's work on adding tests so we're sure what we're fixing.

feyp’s picture

I 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.

ga_dostal’s picture

Status: Active » Closed (duplicate)

The 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!

damienmckenna’s picture

I'm very happy to hear it, thank you ga_dostal and FeyP!