Syntax error or access violation: 1064 in selector with OR-groups

[Zeka]

Hi.

I'm getting this error 

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.id 
		FROM `pages` 
		JOIN field_banner_categories AS field_banner_categories O' at line 7

with this selector

$this->wire('pages')->find("banner_position=sidebar, (banner_categories|banner_sections=$page), (banner_custom_pages=$page)");

banner_position - Select Options field

banner_categories, banner_sections, banner_custom_pages - Page reference fields. 

This selector produces such SQL queries:

banner_categories|banner_sections=1057
----------------------------
SELECT SQL_NO_CACHE pages.id 
FROM `pages` 
LEFT JOIN field_banner_categories AS field_banner_categories ON field_banner_categories.pages_id=pages.id AND ((((field_banner_categories.data='1057') ) )) 
LEFT JOIN field_banner_sections AS field_banner_sections ON field_banner_sections.pages_id=pages.id AND ((((field_banner_sections.data='1057') ) )) 
WHERE ((((field_banner_categories.data='1057') ) ) 
OR (((field_banner_sections.data='1057') ) ) )

banner_custom_pages=1057
----------------------------
SELECT SQL_NO_CACHE pages.id 
FROM `pages` 
JOIN field_banner_custom_pages AS field_banner_custom_pages ON field_banner_custom_pages.pages_id=pages.id AND ((((field_banner_custom_pages.data='1057') ) ))

banner_position=sidebar, =(banner_categories|banner_sections=1057), =(banner_custom_pages=1057), status<1024
----------------------------
SELECT SQL_NO_CACHE pages.id,pages.parent_id,pages.templates_id 
FROM `pages` 
JOIN field_banner_position AS field_banner_position ON field_banner_position.pages_id=pages.id AND (((field_banner_position.data='1' ) )) 
WHERE (pages.status<1024) 
AND (
pages.id IN (
SELECT SQL_NO_CACHE pages.id 
FROM `pages` 
LEFT JOIN field_banner_categories AS field_banner_categories ON field_banner_categories.pages_id=pages.id AND ((((field_banner_categories.data='1057') ) )) 
LEFT JOIN field_banner_sections AS field_banner_sections ON field_banner_sections.pages_id=pages.id AND ((((field_banner_sections.data='1057') ) )) 
WHERE ((((field_banner_categories.data='1057') ) ) 
OR (((field_banner_sections.data='1057') ) ) ) 
) 
OR pages.id IN (
SELECT SQL_NO_CACHE pages.id 
FROM `pages` 
JOIN field_banner_custom_pages AS field_banner_custom_pages ON field_banner_custom_pages.pages_id=pages.id AND ((((field_banner_custom_pages.data='1057') ) )) 
)
) 
GROUP BY pages.id

I thought that it can be relative to "OR" selector

banner_categories|banner_sections=$page

So I tried 

$this->wire('pages')->find("banner_position=sidebar, (banner_sections=$page), (banner_custom_pages=$page)");

It also produces the same error. 

Any thought? 

Thanks. 

[flydev]

Hi Zeka,

No mystery here, you get an error because you have SQL_NO_CACHE in more than one statement. It's also not permitted in sub-queries.

 

What is your MySQL server version ?

[Zeka]

@flydev Thank. Not a guru in SQL. 

MySQL 5.7.16 on a remote server. 

On the local server, I have 5.7.19 and I don't remember that I have such error.

Is this error server specific? 

[flydev]

3 hours ago, Zeka said:

Is this error server specific? 

Yes.

 

3 hours ago, Zeka said:

I don't remember that I have such error.

can depend on the server config.

[Zeka]

@flydev Thank a lot. You brought me to mind that I have been testing the performance of some queries and declared in the config.

$config->dbCache = false;

?‍♂️