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 ') subquery' at line 1

This error occurs in the view edit page above the preview. Latest Views. D7.9. There are no subqueries anything like the error anywhere in the page according to the SQL list in Devel. The following SQL is generated for the view. If I run the SQL in phpMyAdmin, I get a completely different error, suggesting the error is in Views, not in the displayed SQL.

What is causing the displayed error?

Is there a way to make Views display the SQL for the error?

This is the SQL displayed for the view. The query is a join from the visitors table to the user table. There is no subquery. The subquery error must be somewhere else in Views.

SELECT visitors.visitors_date_time AS visitors_visitors_date_time, visitors.visitors_title AS visitors_visitors_title, visitors.visitors_premium AS visitors_visitors_premium, DATE_FORMAT((DATE_ADD('19700101', INTERVAL visitors.visitors_date_time SECOND) + INTERVAL 39600 SECOND), '%Y%m%d') AS visitors_visitors_date_time_day
FROM 
{visitors} visitors
LEFT JOIN {user} user_visitors ON visitors.uid = user_visitors.
ORDER BY visitors_visitors_date_time_day DESC, visitors_visitors_title ASC

An unrelated issue is the rubbish query generated by Views. The following manual edit of the SQL is what I want. I tried heaps of variations in the data and table hooks but they all produce the subquery error, another indication the error happens before the query displayed by Views for the view.

SELECT visitors.visitors_date_time AS visitors_visitors_date_time, visitors.visitors_title AS visitors_visitors_title, visitors.visitors_premium AS visitors_visitors_premium, DATE_FORMAT( (
DATE_ADD( '19700101', INTERVAL visitors.visitors_date_time
SECOND ) + INTERVAL 39600
SECOND ) , '%Y%m%d'
) AS visitors_visitors_date_time_day
FROM visitors
LEFT JOIN users ON users.uid = visitors.visitors_id

A search for SQLSTATE[42000] and subquery produced several issues and none applied. I am happy to change code on my test system to display the SQL for the subquery that is breaking. Where is the message display?

Comments

peterx’s picture

peterx commented

The error disappeared when I removed the following code from hook-views_data. The relationship handler must be generating a subquery separate from the view query. Why use a subquery when the query already has a join? Weird.

  $data['visitors']['uid'] = array(
    'title' => t('User id'),
    'help' => t('User id for someone who is logged in.'),
    'relationship' => array(
      'base' => 'user',
      'field' => 'uid',
      'handler' => 'views_handler_relationship',
      'label' => t('User'),
    ),
  );
peterx’s picture

peterx commented
Priority: Normal » Minor

For anyone that happens to read this and wants to create the join, the following code worked in hook_views_data.

$data['users']['table']['join']['visitors'] = array(
    'field' => 'uid',
    'left_field' => 'visitors_id',
  );

I changed the priority to minor because I know how to get around the error.

dawehner’s picture

dawehner
Primary language German
commented 

This is the SQL displayed for the view. The query is a join from the visitors table to the user table. There is no subquery. The subquery error must be somewhere else in Views.

I think that this sql error is caused by some module which uses hook_query_alter, probably node module with it's node_access functionality.

dawehner’s picture

dawehner
Primary language German
commented
Status: Active » Postponed (maintainer needs more info)

Is this issue still open?

peterx’s picture

peterx commented
Status: Postponed (maintainer needs more info) » Closed (cannot reproduce)

I added some modules, switched some off, and updated a lot of modules to newer releases. The error is not occuring.