0

I have added the SQL query used for view to MySQL and receive error 1064

Here is the SQL I have added 

SELECT field_data_field_session_start_date.delta AS field_data_field_session_start_date_delta, field_data_field_session_start_date.entity_id AS date_id_date_filter, field_data_field_session_start_date.delta AS date_delta_date_filter, node.title AS node_title, node.nid AS nid, node.language AS node_language, field_data_field_session_start_date.field_session_start_date_value AS field_data_field_session_start_date_field_session_start_date, 'node' AS field_data_field_session_start_date_node_entity_type
FROM 
{node} node
LEFT JOIN {field_data_field_session_start_date} field_data_field_session_start_date 
ON node.nid = field_data_field_session_start_date.entity_id AND (field_data_field_session_start_date.entity_type = 'node' AND field_data_field_session_start_date.deleted = '0')
WHERE (( (node.status = '1') )AND(( (DATE_FORMAT(field_data_field_session_start_date.field_session_start_date_value, '%Y-%m-%d') > '2014-03-24') )))
ORDER BY field_data_field_session_start_date_field_session_start_date ASC
LIMIT 3 OFFSET 0

Why is this error occurring

Improve this question
1
  • Have you looked up in the MySQL documentation what error 1064 could mean (hint: it's there in the docs...)? Commented Mar 24, 2014 at 15:29

1 Answer 1

Reset to default
1

For MySQL, don't enclose identifiers in curly braces " { } ". (MySQL does allow for curly braces as part of some bizarre outer join syntax {OJ }, but there's really no call to use that syntax. The actual text of the 1064 message gives an indication as to actual location in the statement where MySQL sees the problem. (Often, this isn't exactly the location where the real problem is, but it's a clue.)

Your SQL statement is difficult to decipher; no one can read it.

All those unnecessary parenthesis in the WHERE clause don't eliminate any ambiguity, they are just adding confusion.

Curiously, your query specifies an OUTER join, but the "outerness" of the join is negated by the predicate in the WHERE clause, so it's really an INNER join. It's misleading.

Shorter table aliases really do make for SQL is easier to read, or at least, SQL that is more easily deciphered.

On another note, to enable indexes to be used for range scans on DATETIME columns, don't wrap the date columns with functions... that disables MySQL's ability to use an index range scan.

For example, MySQL can't use an index range scan operation to satisfy this predicate:

 WHERE DATE_FORMAT( mydatetimecol, '%Y-%m-%d') > '2103-03-24'

but MySQL can use an index range scan to satisfy the equivalent:

 WHERE mydatetimecol >= '2103-03-24' + INTERVAL 1 DAY

Consider this SQL returns an equivalent resultset...

SELECT f.delta      AS field_data_field_session_start_date_delta
     , f.entity_id  AS date_id_date_filter
     , f.delta      AS date_delta_date_filter
     , n.title      AS node_title
     , n.nid        AS nid
     , n.language   AS node_language
     , f.field_session_start_date_value AS field_data_field_session_start_date_field_session_start_date
     , 'node'       AS field_data_field_session_start_date_node_entity_type
  FROM `node` n
  JOIN `field_data_field_session_start_date` f
    ON f.entity_id = n.nid
   AND f.entity_type = 'node'
   AND f.deleted = '0'
   AND f.field_session_start_date_value >= '2014-03-24' + INTERVAL 1 DAY
 WHERE n.status = '1'
 ORDER
    BY f.field_session_start_date_value ASC
 LIMIT 3 OFFSET 0
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.