Config:
drupal 7
iis 7
ms sql 2008 express
windows

page with trouble: http://www.klepa.ru/showroom

task: put any node with votes.
I have in module views related field contents:vote results. It`s working if I don`t use function of agregate. But I see several copies of teasers of the same node. If function "agregate" is on then I see the first page. All next pages are clean.

error in SQL: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'function'.
SQL Request with error:
SELECT * FROM (
SELECT sub2.*, ROW_NUMBER() OVER(ORDER BY sub2.__line2) AS __line3 FROM (
SELECT 1 AS __line2, sub1.* FROM (SELECT TOP(18) node_comment_statistics.[comment_count] AS [node_comment_statistics_comment_count], votingapi_cache_node_points_average.[value] AS [votingapi_cache_node_points_average_value], node.[nid] AS [nid], node.[title] AS [node_title], node.[language] AS [node_language], users_node.[name] AS [users_node_name], users_node.[uid] AS [users_node_uid], node.[created] AS [node_created], 'node' AS field_data_field_picture_preview_node_entity_type
FROM
{node} node
LEFT JOIN {users} users_node ON node.uid = users_node.uid
LEFT JOIN {votingapi_cache} votingapi_cache_node_points_average ON node.nid = votingapi_cache_node_points_average.entity_id AND (votingapi_cache_node_points_average.entity_type = 'node' AND votingapi_cache_node_points_average.value_type = 'points' AND votingapi_cache_node_points_average.function = 'average')
INNER JOIN {node_comment_statistics} node_comment_statistics ON node.nid = node_comment_statistics.nid
WHERE ( (( ([node].[status] = '1') AND ([node].[type] IN ('pictures')) )) )
ORDER BY node_created DESC) AS sub1
) as sub2
) AS sub3
WHERE __line3 BETWEEN 10 AND 18

SQL Request without error:
SELECT TOP(9) node_comment_statistics.[comment_count] AS [node_comment_statistics_comment_count], votingapi_cache_node_points_average.[value] AS [votingapi_cache_node_points_average_value], node.[nid] AS [nid], node.[title] AS [node_title], node.[language] AS [node_language], users_node.[name] AS [users_node_name], users_node.[uid] AS [users_node_uid], node.[created] AS [node_created], 'node' AS field_data_field_picture_preview_node_entity_type
FROM
{node} node
LEFT JOIN {users} users_node ON node.uid = users_node.uid
LEFT JOIN {votingapi_cache} votingapi_cache_node_points_average ON node.nid = votingapi_cache_node_points_average.entity_id AND (votingapi_cache_node_points_average.entity_type = 'node' AND votingapi_cache_node_points_average.value_type = 'points' AND votingapi_cache_node_points_average.function = 'average')
INNER JOIN {node_comment_statistics} node_comment_statistics ON node.nid = node_comment_statistics.nid
WHERE ( (( ([node].[status] = '1') AND ([node].[type] IN ('pictures')) )) )
ORDER BY node_created DESC

Comments

TR’s picture

Status: Active » Fixed

@denis.pimenov: When you assign an issue to yourself, that means that YOU are going to answer the question or fix the issue. I don't think that's what you intended.

This is a problem with your database/database driver. I don't know what the status of Drupal support for MS SQL Express was back when you posted this issue, but I do know that Drupal core did NOT support that DB - it was supported by a contributed module database driver (written by Damien Tournoud, I believe). I suspect this was a known issue with the database driver back then - I hope you found a solution in the issue queue for that driver.

For reference, 'function' is a keyword in MS SQL Express 2008, so the error arises because the Voting API module uses 'function' as a column name, which is not allowed with keywords.

See https://www.ibm.com/support/knowledgecenter/SSEQTP_8.5.5/com.ibm.etools....

Because 'function' is not a keyword with MySql or PostgreSql, the problem does not happen with databases officially supported by Drupal. The fix is really to change the database driver to escape column and table names so that they are not mistaken for keywords.

Closing this because it has been 7 years without an answer - I doubt you're still waiting.

TR’s picture

Status: Fixed » Closed (fixed)
TR’s picture

Assigned: denis.pimenov » Unassigned