I considered reopening Wrong SQL query when submitting multiple values., but thought a new bug might be more beneficial.

I am using the Autocomple textfield element with the Node.Title and Node.Body fields selected for search.

With a delimited value as a space.

Under the submitted values section, I have selected:
Multiple field result combination: Disjunction (OR)
Multiple value result combination: Conjunction. (AND)

Issue:
When I type in a query with multiple values. "question test" for example.

Actual Result:
I am getting results which have either "question" or "test" in either the Body or the Title

When viewing the query, if I set the Multiple field result combination and Multiple value result combination respectively, I am getting the following SQL results:

OR OR

WHERE ((node.type IN ('%s','%s','%s') AND ((node.title LIKE '%%%s%%' OR node_revisions.body LIKE '%%%s%%' OR node.title LIKE '%%%s%%' OR node_revisions.body LIKE '%%%s%%')))) AND node.status = 1 GROUP BY nhier.group_id

OR AND

WHERE ((node.type IN ('%s','%s','%s') AND ((node.title LIKE '%%%s%%' OR node_revisions.body LIKE '%%%s%%' OR node.title LIKE '%%%s%%' OR node_revisions.body LIKE '%%%s%%')))) AND node.status = 1 GROUP BY nhier.group_id

AND AND

WHERE ((node.type IN ('%s','%s','%s') AND ((node.title LIKE '%%%s%%' AND node_revisions.body LIKE '%%%s%%' AND node.title LIKE '%%%s%%' AND node_revisions.body LIKE '%%%s%%')))) AND node.status = 1 GROUP BY nhier.group_id

Expected Result:
For the main scenerio I described above (OR AND), shouldn't I be expecting to see results in which either the Title or the Body has both "question" and "test" in the results. like:


(node.title LIKE '%%%s%%' AND node.title LIKE '%%%s%%') OR (node_revisions.body LIKE '%%%s%%' OR node_revisions.body LIKE '%%%s%%')

Comments

danielb’s picture

The settings you are talking about are not supposed to change the behaviour of autocomplete suggestions, it is a design flaw that is allowing you to even change those from OR to AND. Those settings are meant for what happens when you submit the form.
I think we have a feature missing here, so I'll think about this a bit.

hobbsb’s picture

Sorry for the late response. And please excuse me, as you might have noticed already, there was a typo in the last line of SQL. Corrected:

Expected Result:
For the main scenerio I described above (OR AND), shouldn't I be expecting to see results in which either the Title or the Body has both "question" and "test" in the results. like:

(node.title LIKE '%%%s%%' AND node.title LIKE '%%%s%%') OR (node_revisions.body LIKE '%%%s%%' AND node_revisions.body LIKE '%%%s%%')

Just for clarification, I believe the code I gave was retrieved while submitting the search, not through the autocomplete suggestion itself. As you stated, these options shouldn't effect the Autocomplete, since they are under the "Submitted Values" section of the "Elements" Edit screen.

There definitely might be a missing feature; but as far as I can tell, the "Multiple value result combination" feature that is there does not work. Can you confirm this?

danielb’s picture

Are you sure those are from submitting the form? Do you have your results matching set to 'contains' or something?

danielb’s picture

I think i've replicated the problem

danielb’s picture

Damn this is a pretty big bug, major brainfart on my part when I designed this...

danielb’s picture

Alright I've made changes to this and I'm satisfied the queries are working as expected. I've also clarified the settings around this. I think for your example you will need to choose the 2nd option in the 'nesting order' setting.

danielb’s picture

Status: Active » Needs review
danielb’s picture

Status: Needs review » Active

actually still need to sort out that ambiguity about how the results settings affect the autocomplete suggestions

danielb’s picture

Status: Active » Fixed

I've left the autocomplete fixing to this issue #1198688: Wrong SQL when using multiple search fields
Let me know if there are any more troubles with submitted results.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.