Query with duplicate placeholders throws PDO Exception

I don't see how is this related to Advanced Forum. Try in Views queue...

Maybe someone from the mssql-driver team can explain the syntax error, i'm not sure whether this is really a problem of views, but a problem of mssql.

same error with another module, workflow:

PDOException: SQLSTATE[07002]: [Microsoft][SQL Server Native Client 10.0]COUNT field incorrect or syntax error: (SELECT t.tid, t.target_sid as state_id, s.state AS state_name, s.weight AS state_weight FROM {workflow_transitions} t INNER JOIN {workflow_states} s ON s.sid = t.target_sid WHERE t.sid = :sid AND s.status = 1 ORDER BY state_weight) UNION (SELECT s.sid as tid, s.sid as state_id, s.state as state_name, s.weight as state_weight FROM {workflow_states} s WHERE s.sid = :sid AND s.status = 1) ORDER BY state_weight, state_id; Array ( [:sid] => 1 ) in workflow_allowable_transitions() (linea 1336 di C:\inetpub\wwwroot\turismo\sites\all\modules\workflow\workflow.module)

Hi,

I'm still getting this error:

PDOException: SQLSTATE[07002]: [Microsoft][SQL Server Native Client 11.0]COUNT field incorrect or syntax error: SELECT n.nid, COUNT(c.cid) as num_comments FROM {node} n INNER JOIN {comment} c ON n.nid = c.nid LEFT JOIN {history} h ON h.nid = n.nid AND h.uid = :h_uid WHERE n.nid IN (:nids_0) AND c.changed > GREATEST(COALESCE(h.timestamp, :timestamp), :timestamp) AND c.status = :status GROUP BY n.nid ; Array ( [:status] => 1 [:h_uid] => 1 [:timestamp] => 1362300887 [:nids_0] => 1 ) in views_handler_field_node_new_comments->pre_render() (line 82 of ...\sites\all\modules\views\modules\comment\views_handler_field_node_new_comments.inc).

With Advanced Forum -> "advanced_forum 7.x-2.0+50-dev"
When i'm adding a new topic with 1-2 comments and then try to open: http://localhost/forum/1

My setup:
Drupal 7.21 + sqlsrv 7.x-1.2+6-dev + ctools 7.x-1.2 + views 7.x-3.6+4-dev
Microsoft SQL Server 2008 R2 with SP1 (will SP2 fix this bug?)
Microsoft IIS/7.5
PHP 5.4.13

Any suggestions?

Thanks thorsten

I have the same error.

any help? tanks...

@thorston #5, I see that you said that you have sqlsrv driver v1.2, however, please take a look at includes/database/sqlsrv/database.inc at around line 288 and see if you have 'GREATEST' on or near that line. If you don't then your driver is probably out of date. You need the code that replaces function names with 'dbo.$function'.

The problem was corrected when I placed the 'dbo.' in front of 'GREATEST'. This suggests that you have some missing code in your driver.

Resulting query works:

SELECT n.nid, 
	COUNT(c.cid) as num_comments 
FROM node n 
	INNER JOIN comment c ON n.nid = c.nid 
	LEFT JOIN history h ON h.nid = n.nid 
		AND h.uid = 1 /* :h_uid */
WHERE n.nid IN ( 1 /* :nids_0 */ ) 
	AND c.changed > dbo.GREATEST(COALESCE(h.timestamp, 1362300887 /* :timestamp */ ), 1362300887 /* :timestamp */ ) 
	AND c.status = 1 /* :status */
GROUP BY n.nid

/* ; Array ( [:status] => 1 [:h_uid] => 1 [:timestamp] => 1362300887 [:nids_0] => 1 ) */ 

that is exactly the problem
any idea to solve it?

tanks...

@Surf12, #8: Yes, if it's the same problem that many have been seeing for a while now I do.

Search through ~/includes/database/sqlsrv/database.inc

Look for the word 'GREATEST' which should be in an array $functions at around line 288 in the driver version 1.2.

If you have an earlier version of the driver, I would recommend that you move to the new one. The changes are really minimal.

Whether or not this resolves the issue please do post back here so that others will know what happened.

I already upgrade to that version and still have the problem. I search in /includes/database/sqlsrv/database.inc on line 288 and I have the word 'GREATEST', but nothing happens

tanks

I'll install the module and see if I can get it to create this problem. I suspect that this is a combination of driver and module, but I have to find the exact code that's creating the query.

I also see that there's a new version of advanced_forum (the above lists 2.0 and the current one is 2.1). That doesn't mean that the error exists or is fixed there, though. I'll look deeper.

One of my upcoming projects will need advanced_forum and probably a couple of new modules as well. So, I might as well look into this now rather than later. LOL

I have the latest version of advanced_forum even the problem persists. when I disable it the problem disappears

I hope you can find a solution or the problem
tanks

Dear all,

I'm still getting the same error:

PDOException: SQLSTATE[07002]: [Microsoft][SQL Server Native Client 11.0]COUNT field incorrect or syntax error: SELECT n.nid, COUNT(c.cid) as num_comments FROM {node} n INNER JOIN {comment} c ON n.nid = c.nid LEFT JOIN {history} h ON h.nid = n.nid AND h.uid = :h_uid WHERE n.nid IN (:nids_0) AND c.changed > GREATEST(COALESCE(h.timestamp, :timestamp), :timestamp) AND c.status = :status GROUP BY n.nid ; Array ( [:status] => 1 [:h_uid] => 1 [:timestamp] => 1362300887 [:nids_0] => 1 ) in views_handler_field_node_new_comments->pre_render() (line 82 of ...\sites\all\modules\views\modules\comment\views_handler_field_node_new_comments.inc).

With Advanced Forum -> "advanced_forum 7.x-2.0+54-dev" or "advanced_forum 7.x-2.1"
When i'm adding a new topic with 1-2 comments and then try to open: http://localhost/forum/1

Drupal 7.22 + sqlsrv 7.x-1.2+8-dev + ctools 7.x-1.3+3-dev + views 7.x-3.7+3-dev
Microsoft SQL Server 2008 R2 with SP1 (will SP2 fix this bug?)
Microsoft IIS/7.5
PHP 5.4.13

Any suggestions?

Thanks thorsten

This issue seems to be caused by the duplicate use of the :timestamp placeholder, or more generally, the use of the same placeholder more than once in a query. I intend on working on this issue today.

* updated for clarity...

To reference other issues that my have origin in the same problem:

https://drupal.org/node/1905324 (twitter module)

https://drupal.org/node/2009880 (search api database search)

After some digging in to this issue a bit....

Per the Drupal database placeholder documentation, "A query may have any number of placeholders, but all must have unique names even if they have the same value.".

I included a patch to change the placeholder names in the affected code.

Crap, I hoped this will end up being a database driver problem.

Issues will keep poping up because module coders are usually not aware of this restriction and it is very difficult to trace back the problem (I would have blamed thousands of other things before thinking of this restriction).

Besides, It looks like people running mySQL are not experiencing this issues.

Thanks @omegamonk! Patch works for me!

But i'm getting these warnings:

•Warning: date_timezone_set() expects parameter 1 to be DateTime, boolean given in format_date() (line 2014 of ...\includes\common.inc).
•Warning: date_format() expects parameter 1 to be DateTime, boolean given in format_date() (line 2024 of ...\includes\common.inc).

Patch in git diff format...

As far as the warning that you are seeing. I did some digging into that issue and it appears that the implementation of GREATEST in sqlsrv needs some love. I created an #2013028: GREATEST function does not behave equally in all database engines in the sqlsrv queue to address that.

This looks ready to me and the changes are minor. Marking RTBC.

Yeah this totally makes sense, will commit when I will find time.

Ported to Drupal 8.

Thanks

Uploaded wrong patch before.

Back to RTBC after reroll and passed tests.

By own definitions of bugs this is just a normal. This issue is a normal bug fix, and doesn't include any disruptive changes, so it is allowed per https://www.drupal.org/core/beta-changes. Committed b4858c7 and pushed to 8.0.x. Thanks!

Does this need backport to views 7.x?

Backport was already tested and RTBC long time ago.

Any chance this can get on 7.x?

Patch in #21.

We've recently switched our testing from the old qa.drupal.org to DrupalCI. Because of a bug in the new system, #2623840: Views (D7) patches not being tested, older patches must be re-uploaded. On re-uploading the patch, please set the status to "Needs Review" so that the test bot will add it to its queue.

If all tests pass, change the Status back to "Reviewed & tested by the community". We'll most likely commit the patch immediately without having to go through another round of peer review.

We apologize for the trouble, and appreciate your patience.