colinam1992 colinam1992 - 1 year ago 57
MySQL Question

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

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

Here is the SQL I have added

SELECT AS field_data_field_session_start_date_delta, field_data_field_session_start_date.entity_id AS date_id_date_filter, 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
{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

Why is this error occurring

Answer Source

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      AS field_data_field_session_start_date_delta
     , f.entity_id  AS date_id_date_filter
     ,      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'
    BY f.field_session_start_date_value ASC
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download