When adding a date contextual filter in views with oracle driver, views displays the following error:

SELECT taxonomy_term_data_node.tid AS taxonomy_term_data_node_tid, node.nid AS nid, node.title AS node_title, node.language AS node_language, field_data_field_date.field_date_value AS field_data_field_date_field_date_value, 'taxonomy_term' AS field_data_field_event_type_icon_taxonomy_term_entity_type, 'node' AS field_data_field_event_type_node_entity_type, 'node' AS field_data_field_date_node_entity_type, 'node' AS field_data_body_node_entity_type, 'node' AS field_data_field_event_location_node_entity_type FROM {node} node LEFT JOIN (SELECT td.*, tn.nid AS nid FROM {taxonomy_term_data} td LEFT JOIN {taxonomy_vocabulary} tv ON td.vid = tv.vid LEFT JOIN {taxonomy_index} tn ON tn.tid = td.tid WHERE (tv.machine_name IN (:db_condition_placeholder_2)) ) taxonomy_term_data_node ON node.nid = taxonomy_term_data_node.nid LEFT JOIN {field_data_field_event_type_icon} taxonomy_term_data_node__field_data_field_event_type_icon ON taxonomy_term_data_node.tid = taxonomy_term_data_node__field_data_field_event_type_icon.entity_id AND (taxonomy_term_data_node__field_data_field_event_type_icon.entity_type = :views_join_condition_ AND taxonomy_term_data_node__field_data_field_event_type_icon.deleted = :views_join_condition_1) LEFT JOIN {file_managed} file_managed_field_data_field_event_type_icon ON taxonomy_term_data_node__field_data_field_event_type_icon.field_event_type_icon_fid = file_managed_field_data_field_event_type_icon.fid LEFT JOIN {field_data_field_date} field_data_field_date ON node.nid = field_data_field_date.entity_id AND (field_data_field_date.entity_type = :views_join_condition_2 AND field_data_field_date.deleted = :views_join_condition_3) WHERE (( ( >= :node_date_argument AND <= :node_date_argument1) )AND(( (node.status = :db_condition_placeholder_0) AND (node.type IN (:db_condition_placeholder_1)) ))) ORDER BY field_data_field_date_field_date_value ASC (prepared: SELECT taxonomy_term_data_node.tid AS taxonomy_term_data_node_tid, node.nid AS nid, node.title AS node_title, node.language AS node_language, field_data_field_date.field_date_value AS L#1248, 'taxonomy_term' AS L#1252, 'node' AS L#1253, 'node' AS L#1250, 'node' AS L#197, 'node' AS L#1251 FROM "NODE" node LEFT JOIN (SELECT td.*, tn.nid AS nid FROM "TAXONOMY_TERM_DATA" td LEFT JOIN "TAXONOMY_VOCABULARY" tv ON td.vid = tv.vid LEFT JOIN "TAXONOMY_INDEX" tn ON tn.tid = td.tid WHERE (tv.machine_name IN (:db_condition_placeholder_2)) ) taxonomy_term_data_node ON node.nid = taxonomy_term_data_node.nid LEFT JOIN "L#1121" L#1254 ON taxonomy_term_data_node.tid = L#1254.entity_id AND (L#1254.entity_type = :views_join_condition_ AND L#1254.deleted = :views_join_condition_1) LEFT JOIN "FILE_MANAGED" L#1255 ON L#1254.field_event_type_icon_fid = L#1255.fid LEFT JOIN "FIELD_DATA_FIELD_DATE" field_data_field_date ON node.nid = field_data_field_date.entity_id AND (field_data_field_date.entity_type = :views_join_condition_2 AND field_data_field_date.deleted = :views_join_condition_3) WHERE (( ( >= :node_date_argument AND <= :node_date_argument1) )AND(( (node.status = :db_condition_placeholder_0) AND (node.type IN (:db_condition_placeholder_1)) ))) ORDER BY L#1248 ASC ) e: SQLSTATE[HY000]: General error: 936 OCIStmtExecute: ORA-00936: missing expression (/root/php-5.5.9/ext/pdo_oci/oci_statement.c:148) args: Array ( [:node_date_argument] => 2014-06 [:node_date_argument1] => 2014-06 [:db_condition_placeholder_0] => 1 [:db_condition_placeholder_1] => events [:db_condition_placeholder_2] => event_type [:views_join_condition_] => taxonomy_term [:views_join_condition_1] => 0 [:views_join_condition_2] => node [:views_join_condition_3] => 0 )

Notice that when using MySQL the views work with no errors.
I have checked issue 1153174 on this, but it seems that a pacth was commited to version 1.9, so this should be a new issue, however still related with the long identifiers or placeholders inserted by the oracle driver.

Comments

ricardoj.m.pinto@gmail.com’s picture

Title: views contextual filter and relationships in oracle » views date contextual filter and relationships in oracle
Issue summary: View changes
Issue tags: -views +#date, +#views

On closer inspection of the issue, the error (...missing expression...) is related with how views handles the datetime format in oracle, perhaps adding oracle date format to handler.inc to function views_get_timezone ? ... will try this.

claudiu.cristea’s picture

@ricard0pinto, It seems to me a Drupal problem. I see this in your the query:

.. WHERE (( ( >= :node_date_argument AND <= :node_date_argument1) )...

The query misses here some terms. Seems more query building issue and that is Drupal, no?

ricardoj.m.pinto@gmail.com’s picture

Title: views date contextual filter and relationships in oracle » views date contextual filter in oracle
Issue summary: View changes
ricardoj.m.pinto@gmail.com’s picture

@claudiu.cristea actually the query doesn't pass the start and end date used in the contextual filter for comparison, the issue is related with the date module, currently it does not provide oracle db_type support, I've submited a patch for review on this issue here - https://www.drupal.org/node/1447808#comment-8952879

ricardoj.m.pinto@gmail.com’s picture

Status: Active » Closed (duplicate)
Related issues: +#1447808: Date is incompatible with Oracle Driver