Problem with Views Module when using Contextual Filters.

The problem here is :field_data_field_term_type_field_term_type_tid that becomes :L#2253...
because it is too long for oracle identifiers. It looks like oracle does not like # in bind variable
names. This is indeed a driver bug..

By the driver. And of course we have to rename it.. just no time to do it now, why don't you try to contribute a patch?

i think the better approach is simply to change the long identifier prefix to replace # with a bind variable supported character...

wow.. ok, so what about cleanupArgs? in database.inc.. this function can change the key of the args array..
and it is already called by the query() function ... right?

Any progress on this?

fixed in 1.9

I still have the issue.

To reproduce: enable taxonomy term view, edit, try preview for some term.

ok, i got an error:

SELECT COUNT(*) AS expression FROM (SELECT 1 AS expression FROM {node} node WHERE (( (node.status = 1 OR (node.uid = 1 AND 1 <> 0 AND 1 = 1) OR 1 = 1) AND (node.nid IN (SELECT tn.nid AS nid FROM {taxonomy_index} tn WHERE ( (tn.tid = :db_condition_placeholder_1) ))) ))) subquery (prepared: SELECT COUNT(*) AS expression FROM (SELECT 1 AS expression FROM "NODE" node WHERE (( (node.status = 1 OR (node."UID" = 1 AND 1 <> 0 AND 1 = 1) OR 1 = 1) AND (node.nid IN (SELECT tn.nid AS nid FROM "TAXONOMY_INDEX" tn WHERE ( (tn.tid = :db_condition_placeholder_1) ))) ))) subquery ) e: SQLSTATE[HY000]: General error 1036: OCIBindByName: ORA-01036: illegal variable name/number (/usr/src/php-5.3.5/ext/pdo_oci/oci_statement.c:308) args: Array ( [:db_condition_placeholder_0_0] => 1 ) 

but is different from the one oshman was reporting... Here the problem is not a long identifier but the fact that db_condition_placeholder_0_0 is passed instead of db_condition_placeholder_1... don't know if it is a views or core or oracle bug actually... i've to dig deeper...

I'm getting the same type of error (I originally posted this message: http://drupal.org/node/1300442 , but found out that this error here is probably the same as what I've been having):

SELECT * FROM (SELECT TAB.*, ROWNUM RWN_TO_REMOVE FROM (SELECT mq.* FROM (SELECT q1.nid AS nid, MAX(n.title) AS title, MAX(q1.sku) AS sku, SUM(q1.qty) AS qty_pending, MAX(q1.threshold) AS threshold, MAX(q1.stock)+SUM(q1.qty) AS inventory, MAX(q1.stock) AS stock FROM (SELECT pStock.nid AS nid, pStock.sku AS sku, pStock.stock AS stock, pStock.threshold AS threshold, pStock.active AS active, ord.order_id AS order_id, NVL(ord.qty,0) AS qty FROM "UC_PRODUCT_STOCK" pStock LEFT OUTER JOIN (SELECT ord.order_id AS order_id, prods.nid AS nid, prods.model AS model, prods.qty AS qty FROM "UC_ORDERS" ord INNER JOIN "UC_ORDER_PRODUCTS" prods ON prods.order_id = ord.order_id WHERE (ord.order_status = :db_condition_placeholder_1) ) ord ON ord.nid = pStock.nid) q1 LEFT OUTER JOIN "NODE" n ON q1.nid = n.nid WHERE (n.title IS NULL ) GROUP BY q1.nid) mq ORDER BY sku ASC) TAB) WHERE RWN_TO_REMOVE BETWEEN 1 AND 10 
e: SQLSTATE[HY000]: General error 1036: OCIBindByName: ORA-01036: illegal variable name/number (ext\pdo_oci\oci_statement.c:308) args: Array ( [:db_condition_placeholder_0] => pending ) in PagerDefault->execute() (line 79 of \Data\Website\drupal7\includes\pager.inc).

It appears the wrong identifier is being put in via the Oracle driver. Instead of "db_condition_placeholder_0" being utilized, it utilized "db_condition_placeholder_1" in the select instead.
e.g. - If I add another condition to the db_select, here is what we get:

SELECT COUNT(*) AS expression FROM (SELECT 1 AS expression FROM (SELECT q1.nid AS nid, MAX(n.title) AS title, MAX(q1.sku) AS sku, SUM(q1.qty) AS qty_pending, MAX(q1.threshold) AS threshold, MAX(q1.stock)+SUM(q1.qty) AS inventory, MAX(q1.stock) AS stock FROM (SELECT pStock.nid AS nid, pStock.sku AS sku, pStock.stock AS stock, pStock.threshold AS threshold, pStock.active AS active, ord.order_id AS order_id, NVL(ord.qty,0) AS qty FROM "UC_PRODUCT_STOCK" pStock LEFT OUTER JOIN (SELECT ord.order_id AS order_id, prods.nid AS nid, prods.model AS model, prods.qty AS qty FROM "UC_ORDERS" ord INNER JOIN "UC_ORDER_PRODUCTS" prods ON prods.order_id = ord.order_id WHERE (ord.order_status = :db_condition_placeholder_0) ) ord ON ord.nid = pStock.nid) q1 LEFT OUTER JOIN "NODE" n ON q1.nid = n.nid WHERE (q1.active = :db_condition_placeholder_0) AND (n.title IS NULL ) GROUP BY q1.nid) mq) subquery
e: SQLSTATE[HY000]: General error 1036: OCIBindByName: ORA-01036: illegal variable name/number (ext\pdo_oci\oci_statement.c:308) args: Array ( [:db_condition_placeholder_0] => 1 [:db_condition_placeholder_1] => pending ) in PagerDefault->execute() (line 74 of C:\Data\Website\drupal7\includes\pager.inc).

In this example, we are using only the ":db_condition_placeholder_0" and not both that one and ":db_condition_placeholder_1" which then gives us an error.

Anything I can do to help fix this?
This is the only thing holding me back with getting my site up and running.

Thanks!

can u post the php code that is producing this behavior?

Here is the PHP:

	$orderQuery = db_select('uc_orders', 'ord');
	$orderQuery->fields('ord', array('order_id'));
	$orderQuery->fields('prods', array('nid', 'model', 'qty'));
	$orderQuery->condition('ord.order_status', 'pending');
	$orderQuery->join('uc_order_products', 'prods', 'prods.order_id = ord.order_id');
	
	$stockQuery = db_select('uc_product_stock', 'pStock');
	$stockQuery->fields('pStock', array('nid', 'sku', 'stock', 'threshold', 'active'));
	$stockQuery->fields('ord', array('order_id'));
	$stockQuery->leftJoin($orderQuery, 'ord', 'ord.nid = pStock.nid');
	$stockQuery->addExpression('NVL(ord.qty,0)', 'qty');
	
	$mainQuery = db_select($stockQuery, 'q1');
	$mainQuery->fields('q1', array('nid'));
	$mainQuery->addExpression('MAX(n.title)', 'title');
	$mainQuery->addExpression('MAX(q1.sku)', 'sku');
	$mainQuery->addExpression('SUM(q1.qty)', 'qty_pending');
	$mainQuery->addExpression('MAX(q1.threshold)', 'threshold');
	$mainQuery->addExpression('MAX(q1.stock)+SUM(q1.qty)', 'inventory');
	$mainQuery->addExpression('MAX(q1.stock)', 'stock');
	$mainQuery->leftJoin('node', 'n', 'q1.nid = n.nid');
	$mainQuery->condition('q1.active', 1);
	$mainQuery->condition('n.title');
	$mainQuery->groupBy('q1.nid');

	if (arg(4) == 'threshold') {
		$mainQuery->condition('q1.threshold', 'q1.stock', '>=');
	} 

	$query = db_select($mainQuery, 'mq')->extend('PagerDefault')->extend('TableSort');
	$query->fields('mq'); // Select all fields
	$query->orderByHeader($header);
	$result = $query->execute();

if you comment out the SelectQuery_oracle.__toString() method in the select.inc it work better?

No, I still get the same error

you should try to investigate where the placeholder for the query is changed from _0 to _1... probably in the core on the compile method of the condition.... the oracle driver does not build those placeholders so the error must be somewhere in the core.. may be some assumption is made that work with mysql and not with oracle... No time actually to look at it.

Gotcha, I'll investigate that and let you know.

Thanks!

This occurs with dynamic queries that have conditions on multiple sub-queries.
I believe it's a core 7.8 issue or generated by the way drivers are written for 7.8.

The compile method is called twice, the first time around the placeholders are not colliding, the second time around it's called from select.inc __toString and seems to be redundant.

I removed the two call occurrences to compile and got rid of the collision. Can one of the maintainers please confirm that this is not affecting other functionality?

Thanks

i'm going to run simpletests on it...

Thank you,

calling __toString before execute is broken, but that seems to be broken anyway in Drupal 7.8:

<?php
Recoverable fatal error: Method DatabaseCondition::__toString() must return a string value in SelectQuery_oracle->__toString() (line 64 of /opt/workspace/drupal/includes/database/oracle/select.inc).
?>

That fixed my error I was getting.

Thanks Sergiu!

It worked for the initial problem, but then we get the error that Sergiu was describing.

<?php
Recoverable fatal error: Method DatabaseCondition::__toString() must return a string value in SelectQuery_oracle->__toString() (line 64 of /opt/workspace/drupal/includes/database/oracle/select.inc).
?>

Any way to get around that?

Thanks!

Ca you please post the callstack for this error?
I need to see what is calling __toString.

Do you get the error without the patch? I suspect that you are in the view UI and have a query that doesn't return any data, it will then try to do a __toString and print your query.

I'm not sure how to get the callstack for the error, since I really don't get anything back.
I had put your patch into Select.inc, and was testing out another install of Drupal. That is when it bombed out, when I tried to setup Drupal via install.php.

Once I took out the patch, Drupal installed fine. However, the illegal variable name/number error still comes up as expected (without the patch).

Let me know if I can provide anymore information to help.

Thanks

I tested the patch file provided by sionescu and I can confirm that the error is gone now (not for Oracle, but SQL Server, see: http://drupal.org/node/1286792). After some first initial tests, it seems like all is working fine.

Tom,
Is it causing the __toString issue with the SQL Server driver?

Some Database conditions may not be precompiled so we check and compile only if required.
This also fixes the __toString() issue.
I think that this could be the final fix, can you please re-validate this?

Sergiu,

I haven't been able to invoke the __toString error with the sqlsrv-driver. At this moment, I applied your latest patch and it seems to do the trick also (tested on 2 different websites, both with the sqlsrv driver).

Is there a reason for checking wether the 'where'-clause is compiled in the having-section?

  if(!$this->where->compiled()){          // is this correct? because $this->having->compiled() also exists.
    $this->having->compile($this->connection, $this);
  }

Good catch there,
it was supposed to be $this->having->compiled().

I didn't get to test the having condition against these cases.

Thanks!

Thought so :-). As far as I have seen already, is your patch the resolution for this problem. Thank you!
Perhaps some more reviewing is needed, since I am not familiarised with the way the Drupal database layer works.

Ideally someone that worked at the Drupal core database layer could take a look at this - as this issue was not present with Drupal 7.7. They may come up with a different solution to the underlying problem.

Until then i hope this fixes most of the issues although it's not so elegant:
If you take a look at the compile() method is does check to see if the query has changed:

<?php
if ($this->changed || isset($this->queryPlaceholderIdentifier) && ($this->queryPlaceholderIdentifier != $queryPlaceholder->uniqueIdentifier())) {
?>

and

<?php
 public function compiled() {
    return !$this->changed;
  }
?>

So it's the second half of that first condition that seems to cause the problems.

releasing it in 1.10